MySQL

MySQL

1 MySQL基本架构

MySQL架构如下，主要分为三部分：客户端、Server层和存储引擎。

一条SQL语句的执行流程如下：

需要注意的是，我们通常不建议用缓存，因为只要表里有一个数据被修改，整个缓存就会失效。如果是一张静态表，则可以使用。

2 日志：RedoLog、BinLog和UndoLog

RedoLog（保证持久性）：RedoLog记录了数据的物理更改，保证了crash-safe、加快数据更新速度；

crash-safe：RedoLog记录了数据的更改，比如某数据页某行某列被从a改为了b，崩溃后只需要重放RedoLog就能恢复数据。

加快更新速度：每次更新redolog只需要在末尾追加即可，然后在合适的时机将数据从redolog更新到数据库中。如果不使用redolog，那么每次更新完数据后都需要进行随机访问查找数据应该插入的位置，增加了瞬时IO负担，且IO时间更长。

BinLog（数据库回溯和主从同步）：BinLog记录的是更改逻辑，即SQL，主要用于主从数据同步和数据库回溯。

主从同步：从数据库可以根据binlog记录的执行逻辑再次执行相同逻辑，完成和主数据库的同步，但是很显然这个同步有延迟。

数据库回溯：binlog可以结合某次数据库的全备份来让数据库回到某一时刻。

UndoLog（操作回滚、MVCC）：针对插入操作UndoLog记录了主键ID用于回滚时删除，针对更新操作UndoLog记录了更新的列的旧值用于恢复，针对删除操作需要存储所有内容用于重新插入。UndoLog会将修改记录串成链，回滚时查找旧版本。

长事务风险：如果有很老的事务未提交，那么为了能够正常回滚，从当前版本到老版本的undolog都要保留，会占用大量内存。

如果一行被多次修改了，那么就会生成一条由新修改到老修改的链，链上存了各个版本执行的修改，这些修改叫undolog。为了获得之前版本记录的信息，需要根据undolog重新计算得到旧版本的数据。

2.1 两阶段提交

下图中在更新完数据后，redolog先更新了自己状态为prepare，等binlog写入完成后再更新为commit状态，这一过程就叫两阶段提交。

那么为什么需要两阶段提交呢？为什么不能写完redolog后直接就进入commit状态呢？

主要是为了保证两份日志记录的东西同步。第二阶段的提交能够保证redolog和binlog都完成了自己的写入工作，如果直接改为在第一阶段就进入commit，那么当写binlog崩溃时，根据redolog，数据库会恢复这条更新（因为已经是commit状态了）；而binlog没有记录这条更新，那么从数据库就无法同步到这条更新，造成主从不一致。

如果我们使用了两阶段提交，当系统崩溃后想要恢复时发现redolog还处于prepare状态，那么我们就知道，binlog还没记录这次修改，需要丢弃本次修改。

2.2 各日志刷盘时间

2.2.1 Buffer Pool

Buffer Pool是一个用来减少IO次数的在内存中的缓存，当数据页被从磁盘中读出来再修改后，不会立马写回磁盘，而是先存入BufferPool中，由后台程序决定何时写入磁盘。这样做的好处是不用每次修改都去磁盘写数据，并且可以作为缓存。Buffer Pool存储内容类型如下：

2.2.2 RedoLog

RedoLog默认是事务提交之后马上刷盘，还可以设置成写入RedoLog Buffer（用户空间，进程崩溃数据丢失）；也可以设置成写入内存中的pagecache（系统空间，系统崩溃数据才丢失）。以上两种非立刻刷盘的设置都是每秒刷一次盘。

2.2.3 BinLog

事务执行过程中MySQL将日志写入Server层的binlog cache（每个线程有自己的一部分binlog cache），事务提交后刷盘（一个事务要一次全部写入binlog，保证原子性）。

2.2.4 UndoLog

UndoLog刷盘机制与数据页刷盘机制一样，即修改后先存在缓存在Buffer Pool中，然后由后台线程决定何时刷入磁盘。同时undolog修改被记录在RedoLog中，由RedoLog保证持久化。

3 事务隔离

读未提交：事务还没提交，其他事务就能读取该事务进行的修改；

读已提交：只有事务提交后，其他事务才能读取到该事务进行的修改；

可重复读：事务读到的数据，总与启动时保持逻辑上一致（即自身修改是会被自己看到的）；

串行化：对于同一行记录，读写都会加锁，发生锁冲突时，需要等待。

3.1 Read View

隐藏列：每一行数据除了用户指定的数据外，还有两个隐藏列trx_id和roll_pointer，其中trx_id是执行此次修改的事务的id，roll_pointer是这次修改之前的UndoLog记录的地址。

在执行快照读（快照读指的是不加锁的读，与之相对的是当前读——在in share mode、for update、update、insert、delete时会采用当前读，因为不读最新版本来进行更新的话，之前的修改就丢失了）时会生成一个ReadView（在事务开启后执行第一条SQL语句时才创建），ReadView记录的东西如下图所示，主要包括：创建该ReadView的事务ID、创建ReadView时所有还没结束的事务的ID、没结束的事务中最小的ID、创建该ReadView时在当前所有事务之后的下一个事务的ID。

3.2 多版本并发控制（Multi-Version Concurrency Control，MVCC）

MVCC的作用就是提高数据库性能，在尽量不加锁的前提下，解决读写冲突。

当事务启动时，会创建一个ReadView，然后之后该事务的读的版本都是最小事务ID之前的版本。具体来说，就是根据行记录中的隐藏列查看最新修改该行的事务ID，如果该事务ID大于ReadView中最小ID，那么再根据UndoLog的指针查找旧版本信息，直到事务ID小于ReadView中最小ID。当然，如果行记录中的事务ID为当前事务的ID也是可见的。

MVCC（可重复读级别）下的脏读和幻读现象？

MVCC（可重复读级别）可以避免脏读。

为了避免幻读，MySQL采用临键锁来锁住正在当前读的行，比如select * from T where id>10 for update就会锁住id>10的所有行，只有当前事务完成后，其他事务才可以插入，但是即使如此还是会出现幻读。

MVCC下的幻读主要由当前读和隐藏列的事务ID修改引起。

情况1——幻读

幻读即同一事务执行同一查询时，得到的行不一样。下面的幻读还是由于当前读引起，当事务B提交后，事务A采用当前读会读取各行最新版本，那么就多出来一条记录。

事务A

事务B

select age where id=1（假设无记录）

——

——

insert age=1 id=1

——

commit

select age where id=1 for update（此时出现B插入的记录）

——

情况2——幻读

下面幻读的原因是，事务A采用当前读更新了id=1的记录，此时隐藏列的事务ID就是A本身的ID，因此再次查询时会查询到B插入的行。

事务A

事务B

——

insert age=1 id=1

——

commit

set age=3 where id=1

——

select age where id=1 for update（此时出现B插入的记录）

——

解决方式？

可以在事务开始前，立刻开启当前读为需要查询的区间加锁，这样其他事务就无法修改了。

4 索引

分类方式

索引

数据结构

B+树、Hash索引（InnoDB不支持，但用到了hash的机制）、Full-Text索引

物理存储

聚簇索引（即叶子结点保存了整个行的数据，InnoDB中主键索引就是一种聚簇索引）、非聚簇索引（叶子结点只存主键ID，二级索引采用非聚簇索引）

索引字段

主键索引、唯一索引（在值要求唯一段字段建立索引）、普通索引（对字段无要求）、前缀索引（选择某字段固定长度作为索引键，因此如果前缀都一样，前缀索引就意义不大）

字段数量

单列索引、联合索引（多个字段组合为一个索引，覆盖索引就是通过联合索引实现的，因为覆盖索引的定义是查到叶子时不需要回表就能拿到我们要的数据）

上述各索引中联合索引有一些需要注意的地方，因此以下着重介绍联合索引。

4.1 联合索引

联合索引是否生效的判断依据要从数据结构来看，联合索引同样使用B+ Tree来实现，以下建立了联合索引(num, name)，其有序性是优先num然后才是name。因此name只在同一个num值下才有序，整体上是无序的。

我们判断联合索引是否生效的底层原理就是这个查询是否能利用到某个字段的局部有序性。

4.1.1 最左匹配原则

在进行查询时，需要存在联合索引左侧的字段，才能使用右侧字段的索引（查询时对于字段顺序的书写没有要求，因为优化器会自动调整）。

举个例子：

前提：存在联合索引(a, b, c);

select * from T where b=2 and c=1无法使用联合索引——因为要使用b的索引，首先得有a的才行。

select * from T where c=2 and a=3中，a的索引可以用到，但c不行，因为没有b给c圈定局部范围。

select * from T where b=2 and a=3则可以使用到a和b的联合索引。

4.1.2 更多的例子

select * from T where a>1 and b=2

a字段可以使用，b字段不能，原因是在a>1的结果中b字段是无序的。但会使用索引下推加速。即先在server层判断b是否为2，再回表查询完整数据（由于b也在联合索引中，因此不用回表就能拿到b 的值）

select * from T where a>=1 and b=2

a字段可以使用，b字段在a=1时可以使用，原因是a=1这个局部中b有序，同样使用索引下推加速。只有a能够使用索引（如果b字段自己建立了索引也可能走b的索引）

select * from T where a between 1 and 2 and b=2

between类似>=和<=，因此同上一条。能够使用联合索引。

select * from T where a > 1 and a<2 and b=2

select * from T where a like “j%” and b=1

a字段会按字典序排序，如果有a的完整值为j，那么为j的这一片可以用联合索引匹配b

总的来说，遇到左侧字段等值查询就能用上后续右边的联合索引。

4.1.3 索引下推

对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，在联合索引的 B+Tree 找到第一个满足条件a>1的主键值后，还需要判断其他条件是否满足（看 b 是否等于 2）。

在 MySQL 5.6 之前，只能在找到第一个a>1的记录后，拿主键值开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。

4.1.4 索引区分度

索引区分度 = 索引字段无重复数/索引字段总数

当索引区分度过低（低于30%）时，不如没有索引（索引占空间还要随着修改而调整），MySQL会忽略索引进行全表扫描。

4.2 索引的适用/不适用场景

判断索引的适用或不适用场景之前，应该先知道索引的特点：

由于索引基于B+树实现，因此索引的创建和更新都是比较耗时的（在插入和删除数据时，需要更新索引）。

索引需要占用实际的磁盘空间来维护。

4.2.1 适用场景

字段为唯一字段时，使用索引能大大提高查询速度。

字段经常被用来作为order by或group by的条件时可以建立索引来避免频繁分组和排序。

需要经常作为where判断条件的字段。

4.2.2 不适用场景

需要频繁插入或删除的表，使用索引会花大量时间来更新索引。

重复值较多的字段（索引区分度低）不适合建立索引，比如性别字段建立索引并不能加快查询。此外，当索引区分度较低时，MySQL会自动忽略索引进行扫表。

数据量较小时没有必要创建索引，具体是多小需要根据索引带来的速度提升和维护成本来衡量。

4.3 索引优化

4.3.1 主键索引优化

当存在多个字段可用作主键时，使用较短的主键值能够降低每个节点的大小，提升空间利用率。

4.3.2 排序优化

在执行下面带排序的查询时，如果建立了(status, create_time)的联合索引能够加速查询过程。原因是，如果只有status有索引，那么查到对应记录后，还需要根据create_time临时进行排序，如果建立了联合索引，那么这个排序就被避免了。

select * from order where status = 1 order by create_time asc

4.3.3 前缀索引优化

优点：前缀索引可以降低索引字段大小，可以增加一个索引页中存储的索引值，提高索引的查询速度。

缺点：order by无法使用前缀索引；前缀索引无法作为覆盖索引。

优化：应该根据数据特点选择合适的前缀长度，要选择数据区分度大的长度。可以采用如下的方式查看多长的前缀长度比较合适：

– 下面SQL语句的作用是取email字段从左往右的长度为4、5、6、7的部分，统计这些段的区分度
select
count(distinct left(email,4)) as l4
count(distinct left(email,5)) as l5
count(distinct left(email,6)) as l6
count(distinct left(email,7)) as l7
from user

小技巧：对于如身份证号这种前面重复多的字段，可以逆序存储，或者添加一个对应的hash值字段用于索引。

4.3.4 索引失效优化(explain查看执行计划)

索引失效的常见情况有：

对索引字段使用了函数/或者计算式（如果是对字段的输入参数使用则无影响）；

当存在多个非索引字段时，使用“like %xxx”进行查询；

当where使用or来判断时，有一个字段没有索引，那么就会走全表扫描；

联合索引在不符合最左匹配原则时，会发生索引失效。

除了上述常见情况外，还会出现其他意料之外的索引失效情况，这时我们需要通过explain命令来查看执行计划，具体用法是在查询语句前加上explain：

explain selec * from T where id = 1

执行结果如下：

id：查询中每个SELECT子句的标识符。id的值越大，优先级越高。一个更大的id意味着这个查询/子查询先被执行。

select_type：查询的类型，指示查询的主要类型和子查询的类型。常见值包括：

SIMPLE: 简单的SELECT查询（不包含子查询或UNION）。

PRIMARY: 最外层的SELECT。

SUBQUERY: 子查询中的第一个SELECT。

DERIVED: 派生表（子查询在FROM子句中）。

UNION: UNION中的第二个或后续SELECT语句。

UNION RESULT: UNION的结果。

table：被访问的表。

partitions：显示查询匹配的分区信息（如果存在分区表）。

type：连接类型，显示了联接表的类型。性能从最好到最差的顺序为：system 、 const 、 eq_reg 、 ref 、 range 、 index 、 ALL。

system: 连接类型的特例，查询的表为系统表。

const: 使用主键或者唯一索引，且匹配的结果只有一条记录。

eq_ref: 在join查询中使用PRIMARY KEY or UNIQUE NOT NULL索引关联。

ref: 使用非唯一索引查找数据。

range: 索引范围查找。

index: 全索引扫描。

ALL: 全表扫描。

possible_keys：显示查询可能使用的索引。

key：实际使用的索引。如果查询没有使用索引，则显示为NULL。

key_len：使用的索引的长度。在索引的每个部分上，越短越好。此列显示MySQL决定实际使用的索引部分的长度。

ref：显示索引用于查找的列或常量。如果使用MySQL联接，那么显示所使用的列。

rows：MySQL认为它执行查询时必须检查的行数，是一种估算值。

filtered：表示返回结果的行数占被读取行数的百分比。用于评估过滤条件的效率。

extra：给一些额外的信息，帮助理解查询方式。常见的值包括：

Using index: 使用覆盖索引（索引包含了查询所需的所有值）。

Using where: 使用WHERE子句来限定哪些行将被读取。

Using temporary: 查询使用临时表保存中间结果。

Using filesort: 查询需要额外的排序操作，而不是按索引顺序读取结果。

5 锁

5.1 全局锁

全局锁一般用来进行数据库的备份，防止数据出现不一致的现象。但是这样做会让整个数据库都处于只读状态，严重影响使用。

因此备份数据库一般不会使用全局锁，而是利用MVCC在可重复读的隔离级别下，开启一个备份数据库的事务来保证数据的一致性。

那你可能会问既然能够通过MVCC实现备份，为什么还需要全局锁？那是因为并不是所有存储引擎都支持MVCC，比如InnoDB支持MVCC，但是MyISAM不支持。

5.2 表级锁

5.2.1 普通读写表锁

为了防止出现数据的脏读，但是颗粒度太大，一般使用行级锁代替。

5.2.2 元数据锁（MDL）

为了防止在增删改查时表结构发生改变，因此在进行所有增删改查操作时都会加上一个MDL，不需要显式声明，直到事务结束才会释放。

一个重要的点是：MDL的写锁优先级高于读锁，因此如果有一个写MDL被阻塞时，后续的所有读操作都会被阻塞，因为读的时候会加读MDL，但他们优先级在写MDL后面，高优先的都被阻塞了，那低优先的当然也会被阻塞，但是实际上如果没有MDL，读MDL相互之间是可以共享的。因此我们在进行操作时，在保证逻辑正确的前提下，应该注意将写MDL放在读MDL后面，减少对业务影响；同时，也应该尽量避免长事务，减少阻塞的发生。

5.2.3 意向锁

意向锁的目的是方便快速知道，能不能给表加一个表级锁。在进行插入、删除和更新操作时除了加行级锁，还会给整个表加上意向锁；普通的查询不会加意向锁，但是可以给select加锁来自动加上意向锁。

意向锁的兼容性如下：

IS

IX

S

X

IS

兼容

兼容

兼容

不兼容

IX

兼容

兼容

不兼容

不兼容

S

兼容

不兼容

兼容

不兼容

X

不兼容

不兼容

不兼容

不兼容

意向共享锁（IS）：表示即将对某些行加共享锁（S 锁）。

意向排他锁（IX）：表示即将对某些行加排他锁（X 锁）。

5.2.4 Auto-Inc-Lock（AIL）

Auto-Inc-Lock是在字段自增时加的锁，防止在并发情况下自增值被覆盖（自增不是原子操作）。

AIL分为三个级别，可以通过设置控制：

0级/传统模式：给整个表加锁，下一个插入操作需要等待上一个插入操作的事务结束才能拿到锁进一步将字段自增。

1级/连续模式：单条Insert时，只给自增字段加锁，当获取到自增值后就释放锁；但批量Insert时，还是采用传统模式。

2级/交错模式：一直都只给自增字段加锁，获取到自增值后，就允许其他插入操作。

交错模式会引起主从数据库不一致，具体如下：

使用AIL配置为2级，BinLog的记录类型为Stattement（即记录每次执行的SQL）的配置组合时，线程A和B同时向表T插入数据。

线程A：插入ID为1，2的数据；线程B：插入ID为3，4的数据；

线程A在事务还没提交时，崩溃，数据回滚，事务B成功提交。

那么此时表T的两条数据ID分别为3，4。但是BinLog中只记录了两条插入SQL。从库同步数据时，会按照这两条SQL插入数据，但此时的ID为1，2，这就造成了数据不一致。

解决方法是：当使用2级AIL时，BinLog记录类型应该设置为Row（直接记录数据行的变更）。

5.3 行级锁

5.3.1 行锁（记录锁， Record Lock）

行锁有读锁和写锁，只锁住一行。行锁是为了防止出现脏读现象。

– 下面SQL只给id=1这一行加读锁
select * from T where id = 1 in share mode

5.3.2 间隙锁（Gap Lock）

间隙锁是在可重复读的隔离级别下才有的，目的是防止幻读。当加锁的（包括读锁和写锁）查询时，如果涉及到一个范围，那么这个范围会被加上间隙锁，其他事务无法插入或删除这个范围的数据。间隙锁之间是可以共存的，因为无论读还是写都是为了保证这个范围的记录不被更改。

下面的例子中，无论id为1，2，3的数据是否存在，都无法插入，但是可以修改id=1和id=3的记录：

START TRANSACTION;
SELECT * FROM employees WHERE id>1 AND id<3 FOR UPDATE;

5.3.3 临键锁（Next-key Lock）

临键锁是记录锁+间隙锁。下面例子id在[3,4]的记录都无法修改，也无法插入/删除。

START TRANSACTION;
SELECT * FROM employees WHERE id BETWEEN 3 AND 4 FOR SHARE;

易错点整理

1 使用“select * from T where name like %xx”（name字段建立了索引）是否一定会走全表扫描？

不一定，是否走全表扫描由优化器决定。当表T中只存在主键和name字段时，优化器会选择在name的二级索引中搜索；如果还存在其他非索引字段，那么就会进行全表扫描。

原因：我们知道，MySQL中一行数据除了用户定义的字段外，还有一些隐藏字段。因此，当没有其他非索引字段时，二级索引覆盖了所有需要的数据（能使用到覆盖索引）；当存在其他非索引字段时，为了获取全部数据需要回表查询，此时不如直接全表扫描。

2 为什么建议使用自增的主键？

当主键的值为随机值时，每次插入新的数据都可能造成数据页中数据的移动，影响查询性能；另外，数据移动可能会造成页分裂问题，即原本存储在一个数据页中的数据分裂到两个数据页中，这会形成磁盘碎片，降低空间利用率。

3 MySQL如何存储NULL值？

如果表中某字段允许为null，那么这个表的每一行记录都需要用位图标记这些字段是否为空（1为null，0非null），且只要存在一个字段允许为null就需要1个字节来作为空值列表，当允许为空的字段数超过8个时，就需要新增1个字节来记录多出的字段，如果还有更多的允许空值的字段，要继续增加空值列表。

另外，空值列表存储空值时是逆序存储的：

4 一个表建议的存储量是多少？

网上的一个建议是2000W，但这个是假设一行数据大小为1k时得到的结果。1行数据占用的内存越多，这个建议的存储量就越小。直观来说，当1行数据的占用空间增加之后，1个数据页能够存储的记录数就少，需要的节点就更多，造成B+树的层数就越高，磁盘IO次数增加，最终引起查询时间增加。

5 如何防止update导致全表锁住？

当使用update进行条件更新时，where使用的字段没有索引，就会触发全表扫描。

另外，由于update是更新操作，所以会给要查找的范围加上锁防止幻读。

结合这两点，就会锁住整个表。

为了防止这种事情，可以设置sql_safe_updates=1。

6 当一个字段可以使用唯一索引和普通索引时选哪个？

选普通索引。

原因是，如果我们选择唯一索引，如果数据页不去内存中，那么MySQL必须需要去数据库中读出数据页进行更新；

而普通索引只需要在changebuffer中更新即可。

7 如何避免死锁？

1. 固定任务的顺序访问，避免循环等待；

2. 大事务更容易死锁，将大事务拆成小事务；

3. 同一个事务中一次性获得所有的锁；

4. 降低隔离级别，如果业务允许，将隔离级别从RR调为RC，可以避免掉很多因为gap锁导致的死锁；

5. 为表添加合理的索引，如果判断字段没有索引所有行都会加锁，增加了死锁的概率。