mysql作为开源数据库中的榜一大哥，一直是面试官们考察的重中之重。今天，我们来总结一下mysql的知识点，供大家复习参照，看完这些知识点，再加上一些边角细节，基本上能够应付大多mysql相关面试了（如果有遗漏，请各位评论区留言补充）。

mysql知识点大纲

索引

在数据库优化等操作中，索引是一个非常重要的角色。要想提高查询效率，离不开索引。索引（Index）是帮助 MySQL 高效获取数据的数据结构，其目的在于提高查询效率。也就是我们常说的空间换时间。

索引的类型是由存储引擎决定的。mysql默认的存储引擎是InnoDB，InnoDB的主键索引是一个聚簇索引（索引和数据存储在一起），普通索引是非聚簇索引，只记录主键。

InnoDB的索引结构是B+Tree，要了解B+tree，首先，我们讲一下B-Tree。

B-Tree，是一个为外部磁盘存储而设计的一种多路平衡查找树。我们知道数据从磁盘读取到内存是以块为单位。而B-Tree是以页为单位存取，每次存取一次性把页中所有数据取出到内存。而页远远比块大，所以页是一批连续的数据块组成。由于是连续的，所以减少了磁盘指针的移动次数。查找的时候，是从根节点开始，一层一层查找，最终查找到所需要的数据。这里磁盘IO是瓶颈，所以B-Tree的树高越高，层级越深，磁盘IO次数越多，性能越差。所以，我们要减少树高。

B+Tree是B-Tree的优化，其优势主要体现在：

• B+Tree的数据存储在叶子节点上，非叶子节点不存储数据，只存储key。这样，索引结构中的页会存储更多的key，减少节点的向下分裂，从而减少树高，提高查询效率。
• B+Tree会通过算法，将树高保持在3到4层，使查询性能更稳定。
• B+Tree通过双向指针将叶子节点的数据连接成一个有序链表。有利于进行范围查询以及索引排序。

这里有个知识点，为什么推荐使用整型自增主键而不是选择UUID？

• UUID是字符串，比整型消耗更多的存储空间；
• 在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；
• 自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 1 && id < 5的条件查询语句。
• 在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

索引的使用场景

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题，高并发下倾向创建组合索引
5. 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
6. 查询中统计或分组字段

哪些情况不要创建索引

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
4. 频繁更新的字段不适合创建索引（会加重IO负担）
5. where条件里用不到的字段不创建索引

事务

事务的特性：ACID，原子性，一致性，隔离性，持久性。

事务的隔离级别：读未提交，读已提交，可重复读，串行化。InnoDB的默认隔离级别是可重复读。

事务的实现：事务是通过事务日志来实现的，事务日志分为：redo log和undo log。

redo log：事务开启后，当我们更新数据时，我们在向buffer中更改数据的同时，会向redo log里追加一条事件记录，而不是直接修改磁盘数据。这样，我们就将磁盘的随机IO转变成了顺序IO，提高了修改效率。事务提交以后，系统在慢慢将内存的数据写入磁盘。当我们系统发生崩溃后，buffer中的内存数据丢失了，我们仍可以通过重放redo log，来找回丢失的数据，从而保证了事务的持久性。

undo log：事务开启后，当我们更新数据时，我们会在undo log中记录数据更改之前的状态。当事务发生异常导致回滚时，就可以通过undo log倒序执行，回滚到更新前的数据，从而保证了数据的原子性和一致性。

MVCC：事务开启后，系统会分配一个新的版本号。当我们更新数据时，我们会在记录中记录当前版本号。在事务未提交之前，其他事务只能看到老的版本号。事务提交后，其他事务才能看到新版本号的状态。这就是我们常说的MVCC（多版本并发控制），他保证了事务的隔离性。同时，由于有版本号的存在，同一事务重新读取同一条数据时，所获得状态是相同的。体现了事务可重复读的隔离级别。

分布式事务：mysql中的分布式事务，是基于XA协议。其事务模型包括：

• 应用程序（AP）：定义了事务的边界，指定需要做哪些事务；
• 资源管理器（RM）：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
• 事务管理器（TM）：协调参与了全局事务中的各个事务。

mysql的分布式事务，采用两阶段提交(2PC)的方式：

• 第一阶段所有的事务节点开始准备，告诉事务管理器ready。
• 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

锁

锁的粒度：行锁，表锁，页锁

锁的模式（三种行锁）：记录锁（Record Locks），间隙锁（Gap Locks），临键锁（Next-key Locks）

死锁如何避免：

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别

SQL优化

1. SQL查询的执行顺序：

2. Explain：使用 Explain 关键字可以模拟优化器执行 SQL 查询语句，分析你的查询语句或是表结构的性能瓶颈。

Explain + SQL语句

各字段解释：

id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）

1. id 相同，执行顺序从上往下
2. id 全不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
3. id 部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）

1. SIMPLE ：简单的 select 查询，查询中不包含子查询或 UNION
2. PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为 PRIMARY
3. SUBQUERY：在select或where列表中包含了子查询
4. DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
5. UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
6. UNION RESULT：从UNION表获取结果的select

table（显示这一行的数据是关于哪张表的）

type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

1. system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
2. const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
4. ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
6. index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
7. ALL：Full Table Scan，将遍历全表找到匹配的行

tip: 一般来说，得保证查询至少达到range级别，最好到达ref

possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

key（实际使用的索引，如果为 NULL，则没有使用索引）

查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

key_len（表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好）key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref （显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

rows （根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）

Extra（包含不适合在其他列中显示但十分重要的额外信息）

1. using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
2. Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3. using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
4. using where：使用了where过滤
5. using join buffer：使用了连接缓存
6. impossible where：where子句的值总是false，不能用来获取任何元祖
7. select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
8. distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作。

3. 慢查询日志：顾名思义，记录了一些慢查询。默认是不开启的，我们需要设置

slow_query_log = ON

long_query_time = 1

有了慢查询日志，我们可以通过MySQL自带工具（mysqldumpslow）来分析。

这篇写的太多了，休息一下。下一篇我们继续来写。同学们点点关注哈。