MySQL知识点总结

📝MySQL知识点总结。

存储引擎

MySQL可使用的存储引擎由InnoDB和MyISAM，两者的比对如下：

对比项	MyISAM	InnoDB
存储结构	表信息分为3个文件：frm表定义信息、MYD表数据信息和MYI索引信息	表信息为1个文件
文件格式	数据文件(.MYD)和索引文件(.MYI)分开	数据和索引集中存储(.ibd)
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
支持锁级别	表锁	行锁、表锁
索引实现	B+树索引	B+数索引
哈希索引	支持	不支持
全文索引	支持	不支持

其他区别有：

1. InnoDB使用聚簇索引，MyISAM使用非聚簇索引；
2. InnoDB主键索引的叶子节点存储行数据，而MyISAM叶子节点存储行数据地址，需要再寻址一次才能得到数据；
3. InnDB非主键索引的叶子节点存储主键和其他索引列的数据查询时做到索引覆盖十分高效。

索引

索引时方便快速定位数据的一种数据结构，在实现上为一个文件。索引优点是可以加快数据检索速度，缺点是在增删改数据时需要维护以及占用物理空间。

索引类型

1. 从数据结构角度：B+树索引、hash索引、全文索引；
2. 从物理存储角度：聚簇索引、非聚簇索引(二级索引、辅助索引)；
3. 从字段特性角度：主键索引、唯一索引、普通缩影、前缀索引；
4. 从字段个数角度：单列索引、联合索引。

其中 5. 主键索引：数据列不能重复，不能为null，一个表只能有一个主键索引； 6. 唯一索引：数据列不能重复，可以为null，一个表允许创建多个唯一索引； 7. 普通索引：数据列可以重复，可以为null，没有限制。

其他的类型还包括前缀索引：当索引字段为字符串时，为了减少索引大小，使用其前n位为作为索引；全文索引：用于文本搜索。

索引数据结构

一般索引的数据结构有B+树和hash表两种。

B树和B+树区别

1. B树所有节点都存放键值对，而B+树非叶子节点只存放键没有值，只有叶子节点才同时存放键和值；
2. B+树叶子节点之间相连形成链表，而B树叶子节点没有。

由于上述两者结构的不同，把频繁访问数据放在靠近B树根节点时可以加快热点数据的查询效率，而B+数非叶子节点只存放键，相同条件下一次读取可以获取更多键，可以更快地缩小查询范围。由于叶子节点形成链表，在全局访问时可以先用O(logN)时间找到最小节点，然后用O(N)时间顺序遍历即可；但B数需要遍历每一层，需要更多的内存置换次数。总地来说：

1. B树只适合随机检索，B+树同时支持随机检索和顺序检索；
2. B+树空间利用率更高，减少IO次数；
3. B+树随机查询效率更稳定，B树检索可能在非叶子节点结束，B+树随机检索时都需要从根节点走到叶子节点，路径长度相同，因此查询效率相当；
4. B+树顺序查询效率高，适合范围查询；
5. 增删节点时B+树效率更高
6. B+树在满足聚簇索引和索引覆盖是不需要回表查询数据

对于具有N个节点的B+树，其搜索复杂度为$O(log_dN)$，其中d为B+树的度，实际中d值大于100，即使数据达到千万级别B+树的高度依然维持在3-4层左右，保证3-4次的磁盘IO就可以查到数据。而红黑树是二叉树，其搜索复杂度为$(OlogN)$，树层数多导致磁盘IO也多，严重影响查询性能。

聚簇索引与回表

聚簇索引：按照每张表的主键构建一个B+树，该树的叶子节点存放行数据。InnoDB通过主键建立聚簇索引，如果表没有主键，则使用非空的唯一索引，如果没有这样的索引，则隐式地生成一个主键来作为聚簇索引。聚簇索引的优点是数据访问快，对主键的排序查找和范围查找速度快，缺点是插入速度依赖于插入顺序，更新主键开销大。

非聚簇索引：又称辅助索引、二级索引，也是一个B+树，但叶子节点存放的是主键值，因此通过非聚簇索引找到主键后，再通过聚簇索引获取行数据，该过程称之为回表。回表包含2次B+树的遍历，因此效率更低。注意非聚簇索引不一定发生回表，如果查询字段就是索引字段，此时不需要回表。

索引覆盖

当查询字段都建立索引，引擎直接返回索引表中查询结果而不会二次访问，该行为称之为索引覆盖。索引覆盖加快查询速度，因此尽可能在select字段列表中给出必要的字段。

事务

事务特性：ACID

• 原子性(Atomicity)：事务中的操作不可分割，要么全都完成，要么全都取消；
• 一致性(Consistency)：事务前后的数据保持一致；
• 隔离性(Isolation)：多个事务互不干扰；
• 持久性(Durability)：事务提交后产生的影响是永久的。

通过start或begin命令开启事务，commit命令提交事务，rollback命令回滚事务。MySQL默认自动开启事务提交，即每执行一条语句都会提交事务。

并发问题与隔离级别

InnoDB支持事务，但是并发事务会导致如下问题：

• 脏读：事务B读取事务A未提交的数据；
• 不可重复读：事务B读取事务A已提交的修改数据；
• 幻读：事务B读取事务A已提交的新增数据。

为了解决如上并发问题，数据库提供如下4种隔离级别：

• 读未提交：什么问题也不能解决，相当于什么都不做；
• 读已提交：事务A提交后事务B才能看到其修改的数据，解决脏读问题(Oracle默认隔离级别)；
• 可重复读：事务A执行过程中看到的数据和该事务启动时看到的保持一致，解决脏读、不可重复读问题(MySQL默认隔离级别)；
• 串行化：同一行记录读加读锁、写加写锁，读写锁冲突时必须等前一个事务提交才能继续执行，解决脏读、不可重复读和幻读问题。

事务隔离级别越高，解决的并发问题也越多，但是并发性能也越低。实际应用中很少用到串行化。

LBCC和MVCC

MySQL默认事务隔离级别为可重复读，为了解决该级别下出现的幻读问题，使用LBCC和MVCC两种方法。

LBCC

LBCC是基于锁的并发控制(Lock-Based Concurrent Control)缩写。InnoDB中的锁按模式分为写锁、读锁和意向锁，按照锁粒度分为记录锁(Record Lock)、间隙锁(Gap Lock)和临键锁(Next-Key Lock)，其中临键锁可以解决可重复读下的幻读问题。

记录锁

记录锁对表中的一行记录加锁，也称行锁。使用select .... for update开启，并且对select语句有如下要求：

1. 必须使用精准匹配(如=或者IN)，而不能是范围匹配；
2. where过滤的列必须有唯一索引或者主键索引。

也可以使用update操作开启，记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。

间隙锁

间隙锁对范围加锁，如下图所示。使用select * for update开启间隙锁，有如下要求：

1. 进行范围查询，匹配字段有索引，并且没有数据返回；
2. 或者等值查询，匹配字段有唯一索引，没有数据返回。

间隙锁的缺点在于锁定范围后，即使不存在的键也会被锁定，示例如下：

select * from user where id > 15 for update;
//插入失败，因为id20大于15，不难理解
insert into user values(20,'20');
//插入失败，原因是间隙锁锁的是记录间隙，而不是sql，也就是说`select`语句的锁范围是（11，+∞），而13在这个区间中，所以也失败。
insert into user values(13,'13');

间隙锁锁定的是记录中间的空隙，而不是sql语句中给出的空隙，间隙和锁不存在冲突关系。通过将innodb_locks_unsafe_for_binlog设置为OFF来开启间隙锁。

临键锁

临键锁同时对命中记录和间隙加锁，相当于记录锁和间隙锁的组合(左开右闭加锁)，临键锁阻止该范围内新纪录插入，即防止幻读。使用select ... for update下以下情况开启锁：

1. 使用范围查询有返回值，匹配列有索引；
2. 使用等值查询不管有无返回值，匹配列有索引。

加锁总结

1. 如果没有查询没有命中索引，则退化为表锁；
2. 如果等值查询唯一索引且命中，则退化为行锁；
3. 如果等值查询唯一索引但没命中，则退化为间隙锁；
4. 如果等值查询非唯一索引但没命中，退化为间隙锁，如果命中，则锁定所有命中行的临键锁，同时锁定最大记录行下一个区间的间隙锁；
5. 如果范围查询命中索引，则锁定所有命中行的临键锁，同时锁定最大记录行下一个区间的间隙锁；
6. 如果范围查询没有命中纪律，退化为临近结果的间隙锁。

MVCC

LBCC由于锁的粒度过大会导致性能下降，InnoDB使用MVCC来解决幻读问题。MVCC实现依赖于：3个隐藏字段、undo log和read view，其核心思想是只能查找事务id小于等于当前事务id的行，只能查找删除时间大于等于当前事务id的行或者未删除的行。

隐藏字段

MySQL为每一行记录生成如下4个隐藏字段：

1. DB_TRX_ID：事务id，根据事务产生时间自动递增；
2. DB_ROLL_PTR：回滚指针，指向记录对应undo log位置，大小7字节；
3. DB_ROW_ID：行标识(单调自增id)，如果表没有主键，自动生成并以其产生聚簇索引，大小6字节；
4. flag：标识记录是否删除。

undo log

当对记录进行增删改操作时，生成如下类型的undo log：

• insert undo log：插入一条记录时，记下该记录主键，回滚时删除该主键对应的记录；
• delete undo log：删除一条记录时，记下该记录的所有字段值，回滚时重新插入这些字段值；
• update undo log：修改一条记录时，记下修改前的旧值，回滚时重新更新为旧值。

每次对记录进行增删改就会形成一条undo日志，每条undo log也有一个DB_ROLL_PTR，指向上一条日志形成版本链，该链的头结点即记录的最新值。undo log并不是从物理磁盘上将数据恢复到上一个版本，它只是将数据库逻辑恢复到上一版本(逻辑日志)，因为并发情况下恢复物理页会影响其他事务。

read view

read view是使用普通select查询时产生的一致性读视图，也称快照读，它由执行查询时所有未提交事务id数组(记最小id为min_id)和已经提交事务的最大id(记为max_id)组成。执行查询时根据记录的事务id与min_id、max_id的大小关系返回结果：

1. 如果DB_TRX_ID < min_id，表示该版本是已提交事务生成的，记录可以返回；
2. 如果DB_TRX_ID > max_id，表示该版本还未提交，记录不可见；
3. 如果min_id < DB_TRX_ID < max_id：
   1. DB_TRX_ID在id数组中，表示该版本是还未提交事务生成，不可见，如果是自己的事务则可见；
   2. DB_TRX_ID不在id数组中，表示该版本是已提交事务生成，可见。

快照读不会看到其他事务插入的数据，因此幻读只会在当前读下出现。

MySQL日志

MySQL日志包括错误日志、查询日志、慢查询日志、事务日志和二进制日志。重点介绍二进制日志binlog和事务日志redo log和undo log。

binlog

binlog以二进制形式记录数据库执行的写入操作，它是逻辑日志，使用任何存储引擎的MySQL都会记录binlog日志。Bin log主要使用场景有：

1. 主从复制：在Master节点开启bin log，然后将其发送到Slave端，Slave端重放bin log实现主从一致；
2. 数据恢复：通过mysqlbinlog工具来恢复数据。

binlog通过追加方式写入日志文件，参数max_binlog_size控制binlog文件大小，达到该值后生成新日志文件，通过sync_binlog参数控制写入时机：

1. 0：由系统自行判断何时写入磁盘；
2. 1：每次commint提交都将binlog写入磁盘；
3. N：每N个事务将binlog写入磁盘。

MySQL 5.7.7起默认值为1，调大该值可以提升性能但会牺牲数据一致性。

日志格式

binlog日志格式分为如下3种，通过参数binlog-format指定：

1. statement：基于SQL语句的复制(statement-based replication, SBR)，将修改数据的sql记录到binlog；
   1. 优点：不需要记录每一行变化，减少日志量；
   2. 缺点：某些sql语句会导致主从数据不一致，如sysdate()、sleep()等。
2. row：基于行的复制(row-based replication, RBR)，不记录sql语句上下文信息，仅记录哪条数据被修改
   1. 优点：不会出现存储过程指定导致无法被正确复制的问题；
   2. 缺点：会产生大量日志，尤其alter table时。
3. mixed：以上两者的融合，一般复制使用statement，处理不了的使用row模式。

MySQL 5.7.7前默认为statement，之后默认为row。

redo log

为了实现事务的持久性，最简单的方式就是在每次事务提交后将修改数据全部刷新到磁盘中，但这么做会导致两个问题：

1. InnoDB以页为单位刷新磁盘，一个事务可能只修改了数据页中的几个字节，此时将完整页写回磁盘浪费资源；
2. 一个事务可能修改多个数据页，而这些数据页在物理上并不连续，使用随机IO写回性能太差。

因此MySQL实现redo log来记录事务对数据页进行了哪些修改，解决修改写回的性能问题。

写入机制

redo log包括内存中的日志缓冲redo log buffer和磁盘中的日志文件redo log file。每次执行DML语句，先将记录写入redo log buffer，然后在某个时刻一次性将多条记录写入到redo log file，这种先写内存再写磁盘的方式称为写前日志(Write Ahead Loggin，WAL)。操作系统中用户空间的缓冲区数据是无法直接写入到磁盘中，中间必须经过操作系统缓存区os buffer，因此redo buffer写入redo lof file是先写入到os buffer再通过fsync系统调用写到redo log file。redo log写入策略由参数innodb_flush_log_at_trx_commit控制，可选值及含义如下：

• 0(延迟写)：事务提交不会将redo log buffer写入到os buffer，而是每秒写入os buffer然后再通过fsync写入到redo log file，即每隔1秒写回磁盘，因此故障时会丢失1秒内的数据；
• 1(实时写，实时刷)：每次事务提交即写入os buffer并调用fsync刷到redo log file，故障时不会丢失数据但IO性能差；
• 2(实时写，延迟刷)：每次事务提交写入到os buffer，然后每隔1秒调用fsync刷到redo log file。

日志对比

比较项	redo log	bin log
文件大小	大小固定	大小通过参数设置
实现层级	InnDB引擎实现	Server层实现，所有引擎都可以使用
记录方式	循环写	追加写，文件大小超过设定值时记录到新文件上
使用场景	崩溃恢复	主从复制和数据恢复

Redo log是物理日志，记录数据页的变化，而bin log和undo log是逻辑日志，记录执行语句。Redo log实现事务持久性，undo log实现日志原子性。在恢复数据的效率上，redo log优于bin log。