MySQL 笔记

MySQL 的存储引擎有哪些？它们之间有什么区别？

首先要明确 MySQL 和 存储引擎 的关系，在工作中，MySQL 负责解析 SQL，用户权限管理，用户连接处理等工作，存储引擎主要负责对数据的操作，事务安全等

存储引擎对于 MySQL 相当于发动机之于汽车，重点是可更换，用户可以根据不同场景选择不同的存储引擎

常见的存储引擎：InnoDB，MyISAM，MEMORY，Archive，NDB，他们的功能见下表

特性	InnoDB	MyISAM	Memory	Archive	NDB
事务支持	Yes	No	No	No	Yes
锁粒度	行锁	表锁	表锁	行锁	行锁
MVCC	Yes	No	No	No	No
外键	Yes	No	No	No	Yes
聚簇索引	Yes	No	No	No	No
B+树索引	Yes	Yes	Yes	No	No
哈希索引	No	No	Yes	No	Yes
全文索引	Yes	Yes	No	No	No
数据压缩	Yes	Yes	No	Yes	No
存储上限	64TB	256TB	受内存限制	无限制	384EB

InnoDB：最常用的数据引擎，他甚至是 MySQL 在 5.5 之后的默认引擎，适合百分之99的场景，支持事务，使用行锁保证并发，更是使用 MVCC 进行读写分类，增加效率，支持多种索引

MyISAM：老版本引擎，使用表锁保证并发安全，不支持事务，效率低，但是读性能不错，适用于读多写少场景

MEMORY：见名知义，数据存放于内存中，速度快，重启后数据消失，适合临时缓存

Archive：只支持 insert 和 select，数据压缩率高，所以适合归档数据，比如历史订单，日志，这种不会变更的信息存储场景更合适

NDB：MySQL Cluster 用的引擎，支持高可用，适合做数据库集群

数据库引擎各有所长，InnoDB 通用性最高，但是其他引擎也各有合适的场景，针对不同的需求，可以选择不同的引擎

MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

区别就是索引的叶子结点是否存放完整的行数据，聚簇索引存储完整的行数据，非聚簇引擎存储索引值和主键，前者一张表只能有一个，默认就是主键索引，没有的话就生成一个隐藏字段用于做聚簇索引，非聚簇索引可以有多个

非聚簇索引查询的时候如果查询值不是索引值，需要再次回表查询，这个开销不低，所以实际中建议使用主键查询

比如在 B+ 树中的区别就是聚簇索引叶子结点不仅存储双向连接，更存储整行数据，非聚簇索引只存储双向连接和索引数据

MySQL 的索引类型有哪些？

索引的分类条件有多个维度比如其数据结构，索引性质，索引字段等

数据结构：

• B+树索引：使用多层平衡树，叶子结点相互引用作为链表，既支持索引快速定位，又支持线性范围扫描
• 哈希索引：通过哈希值查询每行的数据，查询效率为 O（1），但是不支持范围和排序
• 全文索引：文本分词建立的倒排索引，类似于做全文搜索的操作，适合做文章的检索
• 空间索引：基于 R 树实现，适合存储地理坐标数据，支持区域查询，距离计算等

索引性质：

• 主键索引：使用主键建立的索引，唯一
• 唯一索引：保证列值不重复，允许多个 NULL
• 普通索引：普普通通的索引，没有任何特点，仅仅就是为了加速查询
• 联合索引：多列值组成的索引，遵循最左原则，用于优化多条件查询
• 全文索引：全文搜索使用
• 空间索引：存储地理坐标

InnoDB索引：

• 聚簇索引：主键索引就是聚簇索引，每张表只有一个，叶子结点存储整行数据
• 非聚簇索引：二级索引，叶子结点不存储整行数据，如果查询结果不命中索引值，还需要一次回表操作

为什么 MySQL 选yez择使用 B+ 树作为索引结构？

走索引磁盘 IO 少，磁盘随机 IO 速度比 内存慢很多，减少 IO 次数是提高数据库效率的有效手段

B+ 树主要有三个优点

• 树高低：B+ 树是多叉树，每个非叶子节点只存储索引值，一个节点可以存储很多数据，通常情况下，三层的 B+ 树就可以存储两千万行的数据，随机查找进需要三次 IO，如果使用二叉树（比如红黑树）做索引，存储两千万的数据就需要二十多层，IO 开销不可忽视
• "Key 和指针紧凑：非叶子节点只存储K和指针，这使得每个内存页存储的索引数据很多，缓存命中率高，效率更高
• 叶子节点形成链表：B+ 树的叶子结点相互连接，形成双向链表，这使得范围查询这类扫描类的查询变的高效，毕竟顺序 IO 比随机 IO 效率高

MySQL 索引的最左前缀匹配原则是什么？

最左前缀匹配原则是针对联合索引的，指的是使用多个查询条件的 SQL 的时候，要尽量遵循联合索引的顺序，从左开始

因为 B+ 树天然从左到右排序，如果条件查询和联合索引匹配。就可以使用叶子结点组合的链表进行线性查询，效率更高，反之就得从头开始再次扫描一遍 B+ 树。效率不高

MySQL 三层 B+ 树能存多少数据？

通常情况下是两千万行数据，计算过程大概是这样：

• 首先 InnoDB 默认每个页大小是 16KB，也就是 16*1024 字节，通常情况下，主键是 BigInt，为 8 字节，指针是 6 字节，也就是 14 字节，那么一页可以存储大概：16 * 1024 / 14 ≈ 1170 个索引
• B+ 树的非叶子节点只存储索引和指针，三层的话就是 1 * 1170 *1170 个节点
• B+ 树的叶子节点是如果一行数据大小是 1KB，一页就可以存储 16 / 1 = 16 行数据，那么三高的 B+ 树可以存储 1 * 1170 * 1170 * 16 = 21,902,400

足足有 两千一百九十万零两千四 行数据，1KB 还是比较大的，像一些用户表之类的行比较小的，存储的更是多

MySQL 中的回表是什么？

回表的目的就是解决二级索引拿不到查询数据的问题，二级索引就是非聚簇索引，他的叶子节点只存储索引值和主键，如果查询的列不在叶子节点，就需要拿主键去聚簇索引找，这就是回表

回表的开销不小，需要拿主键到聚簇索引重新 IO，磁盘开销太大，而且拿到的主键不是连续的，每次都可能访问不同的页，随机 IO 开销更大

如何避免/减少回表？

• 查询值覆盖索引：如果查询的值都在二级索引中，在叶子节点就可以拿到完整的数据，就不需要回去了
• 索引下推：5.6 之后引入了索引下推操作，目的是减少引擎层向 Service 层抛出过多数据，自己就能依靠索引处理，减少回表次数
• 减少使用 select *：如果只需要部分字段，尽量写明，防止回表

什么时候 MySQL 会放弃索引直接全表扫描？

回表次数太多的时候就可能会去进行全表扫描，因为回表产生的是随机 IO，与其产生大量的随机 IO，还不如直接进行一次全表扫描，因为全表扫描是顺序 IO，虽然多，但是也比大量的随机 IO 开销少

经验值大概是查询返回的数据量超过表的 15%-30%，就要进行全表扫描操作

MySQL 中使用索引一定有效吗？如何排查索引效果？

不一定，首先有了索引也不一定使用，使用了一不一定快

首先有了索引也不一定用：MYSQL 自带一个优化器，他会进行进行全表扫描和走索引的开销大小比较，哪个便宜用哪个，有时候表行数很小，进行一次全表扫描开销很低，就直接扫描了

使用了也不一定快：有时候查询值没完全被二级索引包括，就要进行回表，优化器误判导致依然使用索引，回表开销更高，有时候还不如全表

实际中可以查看 SQL 的执行计划来判断有没有用索引，到底有没有用

索引失效的场景？

索引失效通常有两种情况导致：查询条件不符合索引规则，优化器认为走索引开销太大

• 查询条件不符合最左前缀：联合索引：idx_name_age_id如果查询条件只有 age，id两个字段，最左前缀匹配失败，B+ 树的有序性就用不上了，就失效了
• 索引列做运算：如果 where id + 3 = 8这种写法，也没办法走索引，因为他得把每一个 id 拿出来进行计算之后，才能判断是否符合条件
• 通配条件左侧有通配符：where %大%这样就不行，因为左侧不能确定起始点了，就没办法走索引
• 查询条件用了函数：函数运算过后的结果一般不在索引中，所以也不能走索引
• OR 条件链接非索引字段：很显然，如果有非索引字段加入判断，也不能使用索引了
• 隐式类型转换：如果用索引值和不同类型的数据比较，就相当于用转换函数处理了一遍，也不能参与索引，相同的情况也在字段编码不一致时会出现
• 优化器认为索引操作不划算：如果查询数据需要回表太多次，与其产生大量的随机 IO，还不如直接进行全表扫描快
• order by 使用非索引字段：很显然，如果使用非索引字段也需要回表，索引也不好用

为什么索引生效了反而查询变慢了呢？

因为优化器误判，导致在不合适的情况下依然使用索引，造成大量回表操作，产生大量随机 IO，效率更低

在 MySQL 中建索引时需要注意哪些事项？

首先决定要不要建立索引，索引被建立之后，每次数据被修改都要进行维护，索引越多，维护开销越大，插多读少的表尽量不要建立索引

然后再说索引的建立技巧：

• 索引要建立在区分度高的字段上面，低的字段建立了也没什么意义
• 频繁查询的字段建立索引
• 大字段不要建立索引，维护 B+ 开销太大，还容易侵占缓存，导致热点数据遗失
• 排序，分组字段建立索引，有效防止建立临时表

针对联合索引，字段的顺序也很重要，区分度高的放在前面，可以及时过滤掉大部分数据，范围查询放到最后，避免索引失效，然后要根据平时查询的高频字段建立覆盖索引，防止频繁回表带来的性能开销

MySQL 中的索引数量是否越多越好？为什么？

不是，索引太多反而会消耗更大

首先从空间上来说，每一个索引都是一个 B+ 树，在 InnoDB 下，每个数据页都是 16 KB，如果一个 1000 万行数据的表，就要占据差不多一个G的空间，磁盘和内存占用都会飙升，热点数据也会从缓存里面被挤走

然后是时间上来说，每一个 B+ 树都需要在数据更新的时候进行维护，索引更多就会触发更多的页分裂，页合并，CPU 压力大，其次是 MYSQL 自带的优化器会对每一条 SQL 进行评判，选择走什么索引，优化器压力增大，可能还选错，导致性能降低

如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

可以在 select 前面加上 EXPLAIN 可以看到这条 SQL 的查询计划，也就是 MYSQL 打算怎么执行这个语句，主要关注下面几个字段：

列名	含义	状态优先级（从好到差）
type	访问类型（最重要的指标）	system > const > eq_ref > ref > range > index > ALL
possible_keys	可能用到的索引	供参考
key	实际用到的索引	如果为 NULL，说明没用到索引
rows	预计扫描的行数	越小越好
Extra	额外信息	关注 Using filesort 或 Using temporary（性能杀手）

type 解释：

• system：表示这个表中只有一行数据，直接定位
• const：通过索引定位到一行数据
• eq_ref：针对多表关联数据定位到一行数据
• ref：使用普通索引
• range：使用索引进行范围扫描
• index：扫描整颗索引树
• ALL：全表扫描，直接起飞

MySQL 中如何进行 SQL 调优？

SQL 调优的根本目的是减少磁盘 IO，优化慢 SQL 是调优的重要部分，可以通过慢查询日志查询 MYSQL 工作的时候运行慢的指令，然后通过 EXPLAIN 查看执行计划，然后根据执行计划进行针对性优化

• 根据平时的查询使用覆盖索引，尽量覆盖查询字段，避免回表查询
• 多个条件查询也要注意配合联合索引，遵循最左匹配原则
• 不使用前模糊查询，索引无法精确定位
• 尽量只查询需要的字段，防止回表
• 防止在条件处使用函数，因为索引无法定位函数结果
• 热点数据放置到 Redis，减少数据库压力
• 大表考虑分库分表
• 使用主从分离，从库负责读，主库负责写
• 减少业务展示字段，非必要不查询

请详细描述 MySQL 的 B+ 树中查询数据的全过程 ？

主要分为两个过程：定位叶子节点，页内定位数据：

• 首先通过非叶子节点内部存储的索引值进行二分搜索，找到下一层的非叶子节点，然后再去找下一个，一直重复，直到寻找到叶子节点
• InnoDB 使用页目录来加速查找过程，叶子节点数据被页目录分割成组，查找具体数据的时候，先通过二分页目录定位组，然后再通过单项链表遍历组找到数据

MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？

区别就是统计时对于 NULL 值的处理不同，Count(*) 和 count(1) 会统计所有行，包括值为 NULL 的行，Count(字段名) 会统计设定字段的非空行

性能上来说，count(*) 和 count(1) 完全一样，都会统计所有行，count(字段名) 会根据该字段有无索引来决定搜索方式，如果没索引的话，就要进行全表扫描，还要判断当前值是否为空，稍微慢一点，如果当前字段有索引存在，那么就差不多了

MySQL 中 varchar 和 char 有什么区别？

区别是是否定长，char 定长，varchar 不定长，花费 1-2 个字段来进行记录长度

存储固定的数据场景下，使用 char 更好，比如存储编号等场景，因为使用 vachar 还需要计算长度来定位，如果字符串长度不相等，使用 vachar 效果更好，空间利用率高

MySQL 是如何实现事务的？

事务主要是保证一致性，让数据从一个正确的状态完整的调整到另一个正确的状态，中间不发生错误，之后不遗留问题，这些主要靠 Redo Log，Undo log，锁，MVCC 共同实现的，通过保证持久性，原子性，隔离性，然后提升并发效率共同作用实现的

• Redo Log：负责持久化，数据在写入磁盘的数据页之前先写入 Redo Log，这样即使宕机也可以通过 Redo Log 恢复数据
• Undo Log：负责原子性：数据写入前，他会保存之前的数据，如果事务发生回滚，就通过 Undo Log 反向操作回滚数据，防止改了一半的情况出现
• 锁：实现隔离性，两个事务同时更改一行，通过行级锁保证不会出现安全问题，通过间隙锁防止出现幻读
• MVCC 保证隔离状态下的并发效率，通过 Undo Log 的版本链找到自己的数据版本，读写分离，效率更高

MySQL 中的 MVCC 是什么？

MVCC（Multi-Version Concurrency Control）多版本并发控制，目的是增加高并发下读写的效率，让读写不阻塞

简单来说，写数据的时候，不会直接覆盖数据，而是将老数据存放到 Undo Log 中，然后新数据引用老数据成为链式结构，读操作直接读取自己对应的数据，和写操作不干扰，以此增加性能

具体来说，每张表都维护了两个额外的字段：

• trx_id：最后修改这条数据的事务 ID
• roll_pointer：指向 Undo Log 的指针

修改时：会对 ReadView 做一次快照，通过这个快照，结合 UndoLog 的内容来判断哪一条数据是自己的。

ReadView 中有如下字段用于判断：

• m_ids：当前活跃的事务 ID 集合
• min_trx_id：活跃 ID 中的最小值
• max_trx_id：下一个将要被分配的 ID
• creator_trx_id：创建这个视图的事务 ID

首先要明确的是，事务 ID 生成的规则是递增的

然后开始判断：

• 如果 trx_id = creator_trx_id，说明这是自己修改的数据，可以读取
• 如果 trx_id < min_trx_id，说明这条数据所在的事务已经被提交了，可以读取
• 如果 trx_id >= max_trx_id，说明这条记录要比这个快照创建的时候要晚，不能读取
• 最后就是 min_trx_id <= trx_id < max_trx_id，这个时候要查询 m_ids，如果这个事务还在活跃，说明没有被提交，不能读，反之可以读取

本质上来说就是在这个视图创建之前已经提交的事务就可以读取，反之不能读取

MySQL 中的日志类型有哪些？binlog、redo log 和 undo log 的作用和区别是什么？

核心日志；binlog，redoLog，undolog。

• binlog：Service 日志，记录逻辑操作，主要用于主从同步，完整拉取一个 binlog 就能复现数据库
• redolog：InnoDB 日志，记录了要更改还没更改的值，如果 MYSQL 挂了，就可以通过这个日志将没来得及刷盘的数据恢复出来，算是持久层的实现，值得注意的是，redolog 是固定大小的，需要等待 checkpoint 周期性同步
• undolog：InnoDB 日志，记录修改的旧值，用于事务回滚操作数据复原，还用于 MVCC 的快照读