数据库系统原理

发表于 | 分类于 |

关系型数据库

关系型数据库是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系(一对一，一对多，多对多)。

在关系型数据库中，我们的数据的都被存放在了各种表中，表中的每一行就存放着一条数据。

大部分关系型数据库都使用SQL来操作数据库。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

常见的关系型数据库

MySQL、PostgreSQL、Oracle、SQL Server…

MySQL介绍

MySQL是一种关系型数据库，主要用于持久化存储我们系统中的一些数据。

MySQL是开源免费并且比较成熟的数据库，因此，MySQL被大量的使用。任何人都可以在GPL的许可下下载并根据个性化的需要对其进行修改。MySQL的默认端口号为3306。

存储引擎

存储引擎相关的命令

查看MySQL提供的所有存储引擎

1
SHOW ENGINES;

在5.7的版本中MySQL默认的存储引擎是InnoDB，并且只有InnoDB是支持事务的。

查看MySQL当前默认的存储引擎

1
SHOW VARIABLES LIKE '%storage_engine%';

查看表的存储引擎

1
SHOW TABLE STATUS LIKE `table_name`;

MyISAM和InnoDB的区别

在MySQL5.5之前，MyISAM是MySQL默认的存储引擎。

MyISAM的不支持事务和行级锁，最大的缺陷就是崩溃后无法安全恢复。

在5.5版本之后，MySQL引入了InnoDB，MySQL5.5版本后默认的存储引擎为InnoBD。

是否支持行级锁

MyISAM只有表级锁，而InnoDB支持行级锁和表级锁。默认为行级锁。也就是说MyISAM锁的就是整张表，在并发时性能比较低。

是否支持事务

MyISAM不提供事务支持。InnoDB提供事务支持，具有提交和回滚事务的能力。

是否支持外键

MyISAM不支持，InnoDB支持。

拓展：一般不建议在数据库中使用外键，应用层可以解决。

是否支持数据库异常崩溃后的安全恢复

MyISAM不支持，而InnoDB支持。使用InnoDB的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于redo log。

拓展：

  • MySQL InnoDB引擎使用redo log(重做日志)保证事务的持久性，使用undo log(回滚日志)来保证事务的原子性；
  • MySQL InnoDB引擎通过锁机制、MVCC等手段来保证事务的隔离性。(默认支持的隔离级别是REPEATABLE-READ)；
  • 保证了事务的原子性、持久性、隔离性之后，一致性才能得到保证。

是否支持MVCC

MyISAM不支持，而InnoBD支持。MVCC可以看作行级锁的一个升级，可以有效减少加锁操作，提供性能。

关于MyISAM和InnoDB的选择问题

大多数时候我们使用的都是InnoDB存储引擎，在某些读密集的情况下，使用MyISAM也适合。前提是，项目中不介意MyISAM不支持事务、崩溃恢复等缺点。

《MySQL高性能》中写道：不要轻易相信MyISAM比InnoDB快之类的经验之谈，这个结论往往不是绝对的。在很多已知场景中，InnoDB的速度都可以让MyISAM望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

一般情况下我们选择InnoDB都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要支持事务，也不需要在乎崩溃后的安全恢复问题的话，选择MyISAM也是一个不错的选择。但在一般情况下，我们都需要考虑到这些问题。

锁机制与InnoDB锁算法

MyISAM和InnoDB存储引擎使用的锁

  • MyISAM采用表级锁；
  • InnoDB支持行级锁和表级锁，默认为行级锁

表级锁和行级锁对比

  • 表级锁：MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低。MyISAM和InnoDB都支持表级锁；
  • 行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

封锁类型

  • 读写锁
    • 互斥锁(Exclusive)，简写为X锁，又称写锁；
    • 共享锁(Shared)，简写为S锁，又称读锁。

有以下两个规定：

  • 一个事务对数据对象A加了X锁，就可以对A进行读取和跟新。加锁期间其它事务不能对A加任何锁；
  • 一个事务对数据对象A加了S锁，可以对A进行读取操作，但是不能进行跟新操作。加锁期间其它事务能对A加S锁，但是不能加X锁。

锁的兼容关系：

X S
X ❌ ❌
S ❌ ✔
  • 意向锁

意向锁(Intention Locks)可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下，事务T想要对表A加X锁，就需要检测是否有其它事务对表A或者表A中的任意一行加了锁，那么就需要对表A的每一行都检测一次，这是非常耗时的。

意向锁在原来X/S锁之上引入了IX/IS，IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁。有以下两个规定：

  • 一个事务在获取某个数据行对象的S锁之前，必须先获取表的IS锁或者更强的锁；
  • 一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁。

通过加入意向锁，事务T想要对表A加X锁，只需要先检测是否有其它事务对表A加了X/IX/S/IS，如果加了就表示有其他事务正在使用这个表或者表中的某一行的锁，因此事务T加X锁失败。

各种锁的兼容关系如下：

X IX S IS
X ❌ ❌ ❌ ❌
IX ❌ ✔ ❌ ✔
S ❌ ❌ ✔ ✔
IS ❌ ✔ ✔ ✔

解释如下：

  • 任意IS/IX锁之间都是兼容的，因为它们表示想要对表加锁，而不是正真加锁；
  • 这里的兼容关系针对的是表级锁，而表锁的IX锁和行级的X锁兼容，两个事务可以对两个数据行加X锁。(事务T1想要对数据行R1加X锁，事务T2想要对同一个表的数据行R2加X锁，两个事务都需要对该表加IX锁，但是IX锁是兼容的，并且IX锁与行级的X锁是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改)

封锁协议

三级封锁协议

目的：在不同程序上保证数据的一致性

  • 一级封锁协议：事务T要修改数据A时必须加X锁，直到T结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

  • 二级封锁协议：在一级的基础上，要求读取数据A时必须加上S锁，读取完马上释放S锁。

可以解决脏读数据问题，因为如果一个事务在对数据A进行修改，根据一级封锁协议，会加X锁，那么就不能再加S锁了，也就是不会读入数据。

  • 三级封锁协议：在二级的基础上，要求读取数据A时必须加上S锁，直到事务结束才能释放S锁。

可以解决不可重复读的问题，因为读A时，其它事务不能对A加X锁，从而避免了在读期间数据发生改变。

两段锁协议

目的：保证并发调度的正确性。

加锁和解锁分为两个阶段进行。

两段锁协议规定所有事物应遵循的规则：

  • 在对任何数据进行读、写操作之前，首先要申请并获取该数据的封锁；
  • 在释放一个封锁之后，事物不能再申请和获得其它任何封锁。

即事物的执行分为两个阶段：

  • 第一阶段是获得封锁的阶段，称为扩展阶段
  • 第二阶段是释放封锁的阶段，称为收缩阶段

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。

事务遵循两段锁协议是保证客可串行化调度的充分条件。

InnoDB存储引擎的锁的算法

  • Record lock记录锁：单个行记录上的锁；
  • Gap lock间隙锁：锁定一个范围，不包括记录本身；
  • Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身。

乐观锁

什么是乐观锁？

假设为最好的情况，读取时不会上锁，但是在更新的时候会判断一下在此期间这些数据有无被更新过。

乐观锁如何实现？

版本号机制(MVCC)、CAS算法

版本号机制：数据表上加上一个version字段，表示数据被修改的次数，当数据被修改时，version值+1。读取数据时，version会一同读出。在提交更新时，判断version与数据库中的version值是否相同，相等才更新，同时version+1。

版本号机制和MVCC很相似，不同的一点是MVCC使用了行级锁。

CAS算法：无锁算法，不使用锁的情况下，实现多线程之间的变量同步。CAS有三个操作数：需要读写的内存值V，进行比较的值A，拟写入的新值B，当且仅当V=A时，CAS才会以原子的方式用B来更新V。

缺点：1.ABA问题。变量初次为A，再次使用时还是A值，但在读取到使用的期间变量可能被改为B，又改回了A值。CAS就会当作其没有修改过。2.循环时间开销大，如果CAS不成功会不断重试，给CPU带来很大的执行开销。3.只能保证一个共享变量的原子操作。CAS只对单个共享变量有效，当操作涉及跨多个共享变量时CAS无效。

乐观锁的适用场景

多读的场景，这样可以提高吞吐量。

悲观锁

什么是悲观锁？

假设最坏的情况，拿的时候会上锁。

悲观锁是如何实现的？

在数据库中的锁机制：行锁、表锁，读锁，写锁，都是在操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

悲观锁的使用场景

synchronized适用于写比较多的情况下(冲突比较多)。如果对于资源竞争较少的情况，使用synchronized同步锁进行线程的阻塞和唤醒切换以及用户态内核状态间的切换操作额外浪费消耗CPU资源；而CAS基于硬件实现，不需要进入内核，不需要切换线程，操作自旋几率较少，因此可以获得更高的性能。而对于资源竞争严重的情况，CAS自旋的几率会比较大，从而浪费CPU资源，效率低于synchronized。

悲观锁的热点问题

每秒一万次抢这个锁，对一行数据进行读写，会导致什么问题？同一时间，只能有一个请求获得锁，对数据进行操作。某些线程可能永远抢不到锁，请求就会失效。

悲观锁的使用

首先关闭mysql中的autocommit属性，然后对数据进行查询修改，最后commit提交事务。

1
set autocommit = 0;
1
2
-- 窗口1
SELECT * from user;
1
2
-- 窗口2
UPDATE USER SET price = price - 100 WHERE id = 1;

执行上面操作的时候，会显示等待状态，一直等到窗口1执行commit提交事务才会出现下面的显示结果。

查询缓存

执行查询语句的时候，会先查询缓存。不过，MySQL8.0版本后移除，因为这个功能不太实用。在my.cnf加入以下配置，重启MySQL开启查询缓存：

1
2
query_cache_type=1
query_cache_size=600000

MySQL执行以下命令也可以开启查询缓存：

1
2
set global  query_cache_type=1;
set global  query_cache_size=600000;

若开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。其中查询条件包括：查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果也不会被缓存。

缓存建立后，MySQL查询缓存系统会跟踪查询中涉及的每张表，如果这些表发生变化，那么和这张表相关的所有的缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来的额外的开销，每次查询后都要做一些缓存操作，失效后还要销毁。因此，开启缓存要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：

1
select sql_no_cache count(*) from usr;

事务

何为事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

可以通过Commit提交一个事务，也可以使用Rollback进行回滚。

何为数据库事务

数据库事务是日常开发中接触最多了，如果你的项目属于单体架构，接触的往往就是数据库事务。如果没有特指分布式事务，往往指的是数据库事务。

数据库的事务有什么用

简单地说：数据库事务可以保证多个对数据库的操作构成一个逻辑整体。构成逻辑上的整体的这些数据库操作遵循：要么全部执行，要么全部不执行。

数据库事务都有ACID特性：原子性、一致性、隔离性、持久性。

何为ACID特性

  • 原子性(Atomicity)：事务是最小的执行单元，不允许分割。事物的原子性保证动作要么全部完成，要么全部不完成；
  • 一致性(Consistency)：事务执行前后，数据保持一致；
  • 隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性(Durabilily)：一个事务被提交之后，它对数据库的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

数据库事务的实现原理

MySQL InnoDB引擎使用redo log(重做日志)保证事务的持久性，使用undo log(回滚日志)来保证事务的原子性；通过锁机制和MVCC等手段来保证事务的隔离性(默认支持的隔离级别是REPEATABLE-READ)。

保证了事物的持久性、原子性、隔离性之后，一致性才能得到保证。

并发事务带来的哪些问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任何(多个用户对同一数据进行操作)。并发虽然是必须的，但会导致以下的问题：

  • 丢失修改(Lost of modify)：一个事务读修改了数据后，第二个事务也修改了同一个数据。这样第一个事务内修改结果就被丢失了，因此成为丢失修改。
  • 脏读(Dirty read)：当一个事务正在访问数据并对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。恰巧第一个事务进行了回滚，那么第二个事务读取到的数据根本不被承认的。
  • 不可重复读(Unrepeatable read)：指在一个事务中多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读取数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在同一个事务内两次读取的数据是不一样的，因此称为不可重复读。
  • 幻读(Phantom read)幻读与不可重复读类似。它发生在一个事务中读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改；幻读的重点是在于新增和删除比如多次读取一条记录发现记录增多或减少了。

事务隔离级别

SQL标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-REAB(可重复读)：对同一字段的多次读取结构都是一致的，除非数据是被事务自身所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事物依次逐个执行，这样事物之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED ✔ ✔ ✔
READ-COMMITTED ❌ ✔ ✔
REPEATABLE-REAB ❌ ❌ ✔
SERIALIZABLE ❌ ❌ ❌

MySQL的默认隔离级别是什么

MySQL InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-REAB(可重复读)。

我们可以通过命令：

1
SELECT @@tx_isolation;

来查看，MySQL8.0该命令改为

1
SELECT @@transaction_isolation;

MySQL InnoDB的REPEATABLE-READ(可重复读)并不保证避免幻读，需要应用使用枷锁来保证。而这个加锁度使用到的机制就是Next-Key Locks。

InnoDB存储引擎分布式事务的情况下一般会到SERIALIZABLE(可串行化)隔离级别。

多版本并发控制

多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用MVCC无法实现。

基本思想

加锁能解决多个事务同时执行出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而MVCC利用了多版本的思想，写操作跟新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和CopyOnWrite类似。

在MVCC中事务的修改操作(DELETE、INSERT、UPDATE)会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。

版本号

  • 系统版本号SYS_ID：是一个递增的数字，每开始一个新的事物，系统版本号会自动递增；
  • 事物版本号：TRX_ID：事物开始时的系统版本号。

Undo日志

MVCC的多版本指的是多个版本的快照，快照存储在Undo日志中，该日志通过回滚指针ROLL_PTR把一个数据行的所有快照连接起来。

例如在MySQL创建一个表t，包含主键id和一个字段x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

1
2
3
INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用START TRANSACTION将上面的操作当成一个事务来执行，根据MySQL的AUTOCOMMIT机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号TRX_ID和操作之外，还记录了一个bit的DEL字段，用于标记是否被删除。

INSERT、UPDATE、DELETE操作会创建一个日志，并将事务版本TRX_ID写入。DELETE可以看成是一个特殊的UPDATE，还会额外将DEL字段设置为1。

索引

使用索引的优点：提高数据检索的效率；通过索引列对数据进行排序，降低CPU的消耗。

使用索引的缺点：索引会占据磁盘空间；索引会降低更新表的效率，因为更新数据的时候也会更新索引。

B+Tree原理

数据结构

B Tree指的是Balance Tree，也就是平衡树。平衡树是一棵查找树，并且所有叶子节点位于同一层。

B+ Tree是基于B Tree和叶子节点顺序访问指针进行实现，它具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在B+Tree中，一个节点中的key从左到右非递减排列，如果某个指针的左右相邻key分别是$key_i$和$key_{i+1}$，且不为null，则该指针指向节点的所有key大于等于$key_i$且小于等于$key_{i+1}$​

操作

进行查找操作时，首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在所指向的节点进行查找。直到查找到叶子节点，然后再叶子节点上进行二分查找，找出key所对应的data。

插入删除操作会破坏平衡树的平衡性，因此在进行插入删除操作之后，需要对树进行分裂、合并、旋转等操作来维护平衡性。

与红黑树的比较

红黑树等平衡树也可以用来实现索引，但文件系统及数据库系统普遍采用B+ Tree作为索引结构，这是因为使用B+ Tree访问磁盘数据有更高的性能。

  • B+树有更低的树高

平衡树的树高$O(h)=O(log_{d}^{N})$，其中d为每个节点的出度。红黑树的出度为2，而B+ Tree的出度一般都非常大，所以红黑树的树高h很明显比B+ Tree大非常多。

  • 磁盘访问原理

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次I/O就能完全载入一个节点。

如果数据不在同一磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+树相当于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘上进行访问只需要很短的磁盘旋转时间，所以B+树更适合磁盘数据的读写。

  • 磁盘预读特性

为了减少磁盘I/O操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻节点也能够被预先载入。

MySQL索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

  • B+ Tree索引

是大多数MySQL存储引擎的默认索引类型。

因为不需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

因为B+Tree的有序性，所以除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB的B+ Tree索引分为主索引和辅助索引。主索引的叶子节点data域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的data域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

  • 哈希索引

哈希索引能以$O(1)$时间进行查找，但是失去了有序性：

  • 无法用于排序与分组；
  • 只支持精确查找，无法用于部分查找和范围查找。

InnoDB存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在B+ Tree索引之上再创建一个哈希索引，这样就让B+ Tree索引具有哈希索引的一些优点，比如快速的哈希查找。

  • 全文索引

MyISAM存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用MATCH AGAINST，而不是普通的WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB存储引擎在MySQL5.6.4版本中也开始支持全文索引。

  • 空间数据索引

MyISAM存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用GIS相关的函数来维护数据。

索引类型

  • 主键索引：索引列中的值必须是唯一的，不允许有空值
  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值
  • 普通索引：无限制，允许在定义索引的列中插入重复值和空值
  • 全文索引：只能在文本类型的char、varchar、text类型的字段上创建全文索引

按照索引列数量分类：

  • 单列索引
  • 组合索引(主键+联合)(遵从最左前缀匹配原则)

索引优化

  • 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。例如下面的查询不能使用actor_id列的索引：

1
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
  • 多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把actor_id和film_id设置为多列索引。

1
2
SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;
  • 索引列的顺序

让选择性最强的索引列放在前面。索引的选择性是指：不重复的索引值和记录总数的比值。最大为1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如下面显示的结果中customer_id的选择性比staff_id更高，因此最好把customer_id列放在多列索引的前面。

1
2
3
4
5
6
7
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
-- staff_id_selectivity: 0.0001
-- customer_id_selectivity: 0.0373
               -- COUNT(*): 16049
  • 前缀索引

对于BLOB、TEXT、VARCHAR类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。

  • 覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点：

  • 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
  • 一些存储引擎(例如MyISAM)在内存中只缓存索引，而数据依赖操作系统来缓存。因此，只访问索引可以不使用系统调用。
  • 对于InnoDB引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的优点

  • 大大减少了服务器需要扫描的数据行
  • 帮助服务器避免进行排序和分组，以及避免创建临时表(B+ Tree索引是有序的，可以用于ORDER BY和GROUP BY操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表)。
  • 将随机I/O变为顺序I/O(B+ Tree索引是有序的，会将相邻的数据都存储在一起)。

索引的使用条件

  • 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
  • 对于中到大型的表，索引就非常有效；
  • 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

查询性能优化

使用Explain进行分析

Explacin用来分析SELECT查询语句，开发人员可以通过分析Explain结果来优化查询语句。比较重要的字段有：

  • select_type：查询类型、有简单查询、联合查询、子查寻等
  • key：使用的索引
  • rows：扫描的行数

优化数据访问

减少请求的数据量：

  • 只返回必要的列：最好不要使用SELECT*语句；
  • 只返回必要的行：使用LIMIT语句来限制返回的数据；
  • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数：

  • 最有效的方式是使用索引来覆盖查询

重构查询方式

切分大查询：

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

1
2
3
4
5
6
7
DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
-------------
rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

分解大连接查询：

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生了变化，对其它表的查询缓存依然可以使用；
  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其他查询使用到，从而减少冗余记录的查询；
  • 减少锁竞争；
  • 在应用层进行连接，可以很容易对数据库进行拆分，从而更容易做到高性能和可伸缩；
  • 查询本身效率也可能会有所提升。
1
2
3
4
5
6
7
8
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
--
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

存储引擎

InnoDB

是MySQL默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其他存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+Next-Key Locking防止幻读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其他存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

聚簇索引

B+树构建，叶子结点存储的数据是整行记录。创建规则：有主键时，则将主键索引用于聚簇索引；无主键时，会选择第一个不为null的唯一索引列用作聚簇索引；上面两个都没有时，则会使用一个隐式字段来构建聚簇索引。

辅助索引

辅助索引的叶子结点存储的数据是该行的主键值。用辅助索引查询时，首先检索辅助索引获得主键，然后使用主键到主索引中检索记录。

联合索引

联合索引(a,b,c)：查询过程：先比a列，从左开找，相等则找b，继续找c。联合索引创建规则：把频繁使用的列，区分度高的列放前面。

组合索引：相当于主键索引+联合索引，查寻按照最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>，<，between，like)就停止匹配。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据或表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较

  • 事务：InnoDB是事务型的，可以使用Commit和Rollback语句
  • 并发：MyISAM只支持表级锁，而InnoDB还支持行级锁。
  • 外键：InnoDB支持外键。
  • 备份：InnoDB支持在线热备份。
  • 崩溃恢复：MyISAM崩溃后发生的损坏的概率比InnoDB高很多，而且恢复的速度也更慢。
  • 其他特性：MyISAM支持压缩表和空间数据索引。

数据类型

整型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8，16，24，32，64位存储空间，一般情况下越小的列越好。

INT(11)中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT和DOUBLE为浮点类型，DECIMAL为高精度小数类型。CPU原生支持浮点运算，但不支持DECIMAL类型的计算，因此DECIMAL的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE和DECIMAL都可以指定列宽，例如DECIMAL(18,9)表示总共18位，取9位存储小数部分，剩下9位存储整数部分。

字符串

主要有CHAR和VARCHAR两种类型，一种是定长的一种是变长的。VARCHAR这种变长类型能够节省空间，因为只需要存储必要的内容。但在执行UPDATE时可能会使行变得比原来长，当超出一页所能容纳的大小时，就需要执行额外的操作。MyISAM会将行拆成不同的片段存储，而InnoDB则需要分裂页来使行放进页内。

在进行存储和检索时，会保留VARCHAR末尾的空格，而会删除CHAR末尾的空格。

时间和日期

MySQL提供了两种类似的日期时间类型：DATETIME和TIMESTAMP。

DATETIME

能够保存从1000年到9999年的日期和时间，精度为秒，使用8字节的存储空间。

它与时区无关。

默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值，例如：”2008-01-16 22\:\37\:\08”，这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP

和UNIX时间戳相同，保存从1970年1月1日(格林威治时间)以来的秒数，使用4个字节，只能表示从1970年到2038年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换为日期，并提供UNIX_TIMESTAMP()函数把日期转换为UNIX时间戳。

默认情况下，如果插入时没有指定TIMESTAMP列的值，会将这个值设置为当前时间。

应该尽量使用TIMESTAMP，因为它比BATETIME空间效率更高。

切分

水平切分

水平切分又称为Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被用使用的列和不经常被使用的列切分到不同的表中。

Sharding策略

  • 哈希取模：hash(key) %N
  • 范围：可以是ID范围也可以是时间范围；
  • 映射表：使用单独的一个数据库来存储映射关系。

Sharding存在的问题

事务问题

使用分布式事务来解决，比如XA接口。

连接

可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。

ID唯一性

  • 使用全局唯一ID(GUID)
  • 为每个分片指定一个ID范围
  • 分布式ID生成器(如Twitter的Snowflake算法)

复制

什么是主从复制？

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的业务数据库。

主从复制的作用和好处？

  • 做数据备份，当主数据库服务器故障后，可切换从数据库工作；
  • 多库存储，便于架构的拓展；
  • 读写分离，使数据库能支持更大的并发，实现负载均衡。

主从复制

数据库有一个bin-log二进制文件，记录所有sql语句；

把主数据库的bin-log文件的sql语句复制过来；

让其他在从数据库的relay-log重做日志文件中再执行一次这些sql语句即可。

主要涉及三个线程：binlog线程、I/O线程和SQL线程。

  • binlog线程：负责将主服务器上的数据更改写入二进制日志中(Binary log)中。
  • I/O线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志(Relay log)。
  • SQL线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放(Replay)。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以使用MyISAM，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

数据库连接池的作用

维护一定数量的连接，减少创建连接的时间，更快的响应时间，统一的管理。

关系型数据库和非关系型数据库

关系型数据库

优点：支持SQL，可用于复杂查询；支持事务

缺点：读写性能差，固定表结构，不支持高并发读写，不支持海量数据的高效率读写

非关系型数据库

优点：读写性能高；基于键值对，数据无耦合，易于拓展；

缺点：不支持sql

日志

一共有七种日志：

  • 重做日志(redo log)
  • 回滚日志(undo log)
  • 二进制日志(bin log)
  • 错误日志(error log)：记录MySQL的启动和停止
  • 慢查询日志(slow query log)：记录执行时间过长和没用索引的查询语句
  • 一般查询日志(general log)：记录服务器接受的每一条指令
  • 中继日志(relay log)：读取bin log内容，作用于服务器，保持主从一致。

重做日志(redo log)

作用

确保事务的持久性。例如，MySQL故障，但尚有脏页未写入磁盘，重启MySQL服务时，会按redo log进行重做，从而达到事务一致性的目的。

内容

物理格式的日志，记录的是物理数据页面的修改的信息，其中redo log是顺序写入redo log file的物理文件中去的。

产生

事务开始之后就产生了redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

释放

当对应事务的脏页写入到磁盘之后，redo log的使用也就完成了，重做日志占用的空间就可以重用(被覆盖)。

重做日志何时写入磁盘

重做日志有一个缓存区Innodb_log_buffer(默认8M)，Innodb存储引擎会先将重做日志写入Innodb_log_buffer缓冲区中，再通过以下三种方式将缓冲区日志写入磁盘：

  • Master Thread以1s1次执行刷新Innodb_log_buffer到redo log file；
  • 事务Commit时，redo log会刷新到redo log文件中。
  • 当Innodb_log_buffer可用空间少于一半时，重做日志缓存被刷新到redo log File中。

回滚日志(undo log)

作用

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读(MVCC)，也即非锁定读。

内容

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，不同于redo log。

产生

事务开始之前，当前版本生成undo log，undo也会产生redo来保证undo log的可靠性。

释放

当事务提交后，undo log并不能立马删除，而是放入待清理的链表，由purge线程判断删除版本号是否小于当前版本号，是则删除，来决定是否可以清理undo log的日志空间。

二进制日志(bin log)

作用

用于复制，在主从复制中，从库利用主库上的bin log进行复制，实现主从同步。用于数据库的基于时间点的还原。

内容

逻辑格式的日志，就是执行过的事务中的sql语句，同时也包括了执行的sql语句(曾删改)反向的信息。即delete对应着delete和insert；update对应着update执行前后的版本信息；insert对应着delete和insert本身的信息。

产生

事务提交时候，一次性将事务中的sql语句按照一定的格式记录到bin log中。(在开启bin log的情况下，较大的事务提交，可能变慢)。

释放

由参数expire_logs_days配置，指定过期天数。

注：和redo log有区别，redo log保证事务的持久性，是事务层面的，bin log作为还原功能，是数据库层面的；redo log是物理日志，是数据页面的修改之后的物理记录，bin log是逻辑日志，可以简单认为记录的就是sql语句；恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的bin log。

为了保证主从复制时候的主从一致，redo log和bin log的写入顺序，要严格一致。先写redo log，再写bin log，两个日志都提交成功，事务才算真正的完成。

一个表中最大记录、列数、索引是多少

  • InnoDB：最多创建1017列，最多64个二级索引，单个索引最多含16列，索引最大1000字节，行最大65536字节。
  • MyISAM：最多4096列，最多64个二级索引，单个索引最多16列，索引最大1000字节，行最大65536字节。

最大记录数：建议单表行数超过500万或容量超过2G时，分表分库。

B+树结构的索引存储数据量

在MySQL中InnoDB数据页大小为16KB。假设数据类型为bigint，大小为8bit，还有6bit用于存储下一页索引地址，所以一个数据就是14bit空间。而一个结点就是一个数据页，为16kb，故一个节点能够存储16K/14bit=1170个数据。第二层能存储1170*1170=1368900个数据，而B+数的叶子节点存储的是整条sql数据，可以假设一条数据大小为1kb，每个节点为16kb，所以每个节点能存储16条数据，可存储的数据为1170*1170*16=21902400两千多万条。

表的空间大小及查询

表的空间大小与存入其中的数据及索引有关，想要查询的话，在mysql中有一个默认的数据表information_schema，这张数据表保存了MySQL服务器所有的数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等，可使用命令查看表的大小。

命令查看表大小的具体使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 切换数据库
USE `information_schema`;

-- 查看数据库使用大小
SELECT CONCAT(ROUND(SUM(data_length/1024/1024),2),'MB') 
AS data 
FROM tables
WHERE table_schema='DB_Name';

-- 查看表使用大小
SELECT CONCAT(ROUND(SUM(data_length/1024/1024),2),'MB')
AS data
FROM tables
WHERE table_schema='DB_Name'
AND table_schema='Table_Name';

表碎片整理

清理表碎片的命令

  • InnoDB表：alter table 表名 engine = InnoDB
  • MyISAM表：optimize table 表名

清除碎片操作会暂时锁表，数据量越大，耗费的时间越长。

为什么会产生表碎片

每当mysql删除一行内容，该空间就被留空。而删除操作多的话，留空的空间会很多。当mysql执行插入操作的时候，会尝试使用空白空间，如果某个空白空间一直没有合适的数据大小占用，就形成了碎片。当mysql对数据进行扫描时，它扫描的是列表的容量上限。

MySQL死锁问题

场景一：事务之间对资源访问顺序的交替

一个用户A访问表A(锁住了表A)，然后又访问表B；另一个用户B访问表B(锁住了表B)，然后企图访问表A，这样死锁就产生了。

解决方法

这种死锁是比较常见的，是由于程序的BUG产生的，除了调整程序的逻辑外，没有其他办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表，总是按先A后B的顺序处理，必须同时锁定两个资源，要保证在任何时刻都应该按照相同的顺序来锁定资源。

场景二：并发修改同一记录

用户A查询一条记录，然后修改该条记录；这时用户B修改该条记录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的的独占锁也就不可能释放掉共享锁，于是出现了死锁。

一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

解决方法

使用乐观锁进行控制，乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统整体性能表现。由于乐观锁机制是在我们系统中实现，来自外部系统的用户更新操作不受我们系统控制，因此可能会造成脏读数据被更新到数据库中。

使用悲观锁进行控制。依靠数据库的锁机制，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往是无法承受。所以，采用悲观锁进行控制时一定要考虑清楚。

场景三：索引不当导致全表扫描

如果在事务中执行了一条不满足条件的语句，执行了全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。

解决方法

SQL语句中不要使用太复杂的关联多表的查询；对于有全表扫描的SQL语句，应当建立相应的索引进行优化。

SQL注入

SQL注入攻击是什么

SQL注入攻击通过构建特殊的输入作为参数传入Web应用程序，进而执行攻击者所要的操作。

SQL注入如何产生

当访问动态网页时, Web 服务器会向数据访问层发起 Sql 查询请求，如果权限验证通过就会执行 Sql 语句。这种网站内部直接发送的Sql请求一般不会有危险。但实际情况是很多时候需要结合用户的输入数据动态构造 Sql 语句，如果用户输入的数据被构造成恶意 Sql 代码，Web 应用又未对动态构造的 Sql 语句使用的参数进行审查，则会带来意想不到的危险。

SQL注入的威胁

猜解后台数据，盗取敏感信息。

绕过认证。

注入可以借助数据库的存储过程进行提权等操作。

判断是否存在SQL注入

通常情况下，可能存在 Sql 注入漏洞的 Url 是类似这种形式 ：http://xxx.xxx.xxx/abcd.php?id=XX 对 Sql 注入的判断，主要有两个方面：

​ 判断该带参数的 Url 是否存在 Sql 注入？

​ 如果存在 Sql 注入，那么属于哪种 Sql 注入？

最为经典的单引号判断法： 在参数后面加上单引号,比如:

​ http://xxx/abc.php?id=1‘

如果页面返回错误，则存在 Sql 注入。

原因是无论字符型还是整数型都会因为单引号个数不匹配而报错。

判断SQL注入漏洞的类型

通常 Sql 注入漏洞分为 2 种类型：数字型、字符型。

数字型判断

当输入的参 x 为整型时，通常 abc.php 中 Sql 语句类型大致如下： select * from <表名> where id = x 这种类型可以使用经典的 and 1=1 和 and 1=2 来判断：

 Url 地址中输入 http://xxx/abc.php?id= x and 1=1 页面依旧运行正常，继续进行下一步。

 Url 地址中继续输入 http://xxx/abc.php?id= x and 1=2 页面运行错误，则说明此 Sql 注入为数字型注入。

原因如下： 当输入 and 1=1时，后台执行 Sql 语句：select from <表名> where id = x and 1=1没有语法错误且逻辑判断为正确，所以返回正常。当输入 and 1=2时，后台执行 Sql 语句：select from <表名> where id = x and 1=2没有语法错误但是逻辑判断为假，所以返回错误。

我们再使用假设法：如果这是字符型注入的话，我们输入以上语句之后应该出现如下情况：select from <表名> where id = ‘x and 1=1’ select from <表名> where id = ‘x and 1=2’ 查询语句将 and 语句全部转换为了字符串，并没有进行 and 的逻辑判断，所以不会出现以上结果，故假设是不成立的。

字符型判断

当输入的参 x 为字符型时，通常 abc.php 中 SQL 语句类型大致如下： select * from <表名> where id = ‘x’ 这种类型我们同样可以使用 and ‘1’=’1 和 and ‘1’=’2来判断：

Url 地址中输入 http://xxx/abc.php?id= x’ and ‘1’=’1 页面运行正常，继续进行下一步。

Url 地址中继续输入 http://xxx/abc.php?id= x’ and ‘1’=’2 页面运行错误，则说明此 Sql 注入为字符型注入。原因如下： 当输入 and ‘1’=’1时，后台执行 Sql 语句： select from <表名> where id = ‘x’ and ‘1’=’1’语法正确，逻辑判断正确，所以返回正确。当输入 and ‘1’=’2时，后台执行 Sql 语句：select from <表名> where id = ‘x’ and ‘1’=’2’ 语法正确，但逻辑判断错误，所以返回错误。

预防SQL注入

用JDBC（或者其他数据持久层）提供的如：PreparedStatement就可以 ，不要用拼接字符串的方法。

Donate comment here