一、基础部分

1. 基础架构：一条SQL查询语句是如何执行的？

• （1）下面是 MySQL 的基本架构示意图：

从上图中可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程，同时可以看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。MySQL 可以分为 Server 层和存储引擎层两部分。

• （2）Server 层：

• 包括：连接器、查询缓存、分析器、优化器、执行器

• 涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

• （3）存储引擎层：

• 负责数据的存储和提取。

• 其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

• （4）根据上面说到的 SQL 语句执行流程，对各组件的作用依次作下说明：

• A. 连接器：

• 顾名思义，就是连接负责跟客户端建立连接、获取权限、维持和管理连接。

• 比如：mysql -h$ip -P$port -u$user -p3306 通过该命令就可以连接上数据库。

• B. 查询缓存：

• 连接上数据库之后，接下来就可以执行需要的 SQL 语句了，执行逻辑就会来到：查询缓存。

• MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

• 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

• 【注意】但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

• 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

• 需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

• C. 分析器：

• 如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

• 分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

• “词法分析”通过之后就来到“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

• D. 优化器：

• “分析器”过后，在正式执行语句之前，还需要经过“优化器”处理。

• 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。最终的目的是，给出最优的执行方案。

• 优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

• E. 执行器：

• 开始执行的时候，要先判断一下你对需要操作的表有没有执行查询的权限，如果没有，就会返回没有权限的错误。

• 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

• 经过以上步骤流程，一条 SQL 语句才算完整地执行完成。

2. 日志系统：一条SQL更新语句是如何执行的？

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。

（1）一个小故事：

• 为了方便我们理解这两个重要日志的原理，作者“丁奇”大神举了一个易理解，巧妙的例子：

不知道你还记不记得《孔乙己》这篇文章，酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还账的话，掌柜一般有两种做法：

一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；

另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先，你得找到这个人的赊账总额那条记录。你想想，密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写回到账本上。

这整个过程想想都麻烦。相比之下，还是先在粉板上记一下方便。你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？

同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

 (2) redo log（重做日志，是物理日志）

• InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示:

• write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

• write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

• 有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

 (3) binlog（归档日志，是逻辑日志）

• binlog（归档日志）是 Server 层的日志。

• binlog 和 redo log 主要有下面 3 点不同：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

（4）update 内部流程

• 执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程如下：

• 示例语法：update T set c=c+1 where ID=2;

• 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

• 以上 update 语句的执行流程图如下，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的：

（5）小结：

• redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

• sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

3. 索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

（1）索引的常见类型

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

假设，现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下所示：

图中，User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链表。假设，这时候要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

需要注意的是，图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

可以设想下，如果现在要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户，就必须全部扫描一遍了。

所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

而有序数组在等值查询和范围查询场景中的性能就都非常优秀。还是上面这个根据身份证号查名字的例子，如果使用有序数组来实现的话，示意图如下所示：

这里假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。

同时很显然，这个索引结构支持范围查询。要查身份证号在[ID_card_X, ID_card_Y]区间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环。

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

二叉搜索树也是课本里的经典数据结构了。还是上面根据身份证号查名字的例子，如果我们用二叉搜索树来实现的话，示意图如下所示：

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。

当然为了维持 O(log(N)) 的查询复杂度，就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

（2）索引优化&原则

• 覆盖索引：

• 如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表（回到主键索引树搜索的过程，称为回表）。也就是说，在这个查询里面，索引 k 已经“覆盖了”查询需求，称为覆盖索引。

• 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

• 最左前缀原则：

• 结论：B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

• 为了直观地说明这个概念，这里用（name，age）这个联合索引来分析：

• 可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

• 当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

• 如果要查的是所有名字第一个字是“张”的人，SQL 语句的条件是"where name like ‘张 %’"。这时，也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

• 可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

• 由于支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

4. 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

（1）全局锁

• 顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

• 全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

• 但是让整库都只读，听上去就很危险：

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

（2）表级锁

• MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

• 表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

• 举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

• 另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

5. 普通索引和唯一索引，应该怎么选择？

（1）查询过程

• 假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

• InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

• 因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

• 当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

• 但是，我们之前计算过，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

（2）更新过程

• 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

• 需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

• 将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

• 显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

（3）索引选择和实践

• 普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，“丁奇”大神建议你尽量选择普通索引。

• 如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

• 在实际使用中，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

• 特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

6. “order by”是怎么工作的？

假设部分表定义：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;
复制

复制

假设按照下面的sql查询并排序。

select city,name,age from t where city='杭州' order by name limit 1000  ;
复制

全字段排序
复制

explain

Extra中"Using filesort"表示排序，mysql会给每个线程分配一个块内存（sort_buffer）用来排序。

city索引示意图：

sql执行过程：

1. 初始化sort_buffer，确定放入name、city、age 这三个字段；

2. 从city索引找到第一个city='杭州’的主键id，图中的ID_X；

3. 根据id去聚集索引取这三个字段，放到sort_buffer；

4. 在从city索引取下一个；

5. 重复3、4查询所有的值；

6. 在sort_buffer按name快速排序；

7. 按照排序结果取前1000行返回给客户端。

如果sort_buffer太小，内存放不下排序的数据，则需要使用外部排序，利用磁盘临时文件辅助排序。这取决于排序所需内存和参数 sort_buffer_size。

下面方法可以确定排序是否使用临时文件：

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算 Innodb_rows_read 差值 */
select @b-@a;
复制

复制

通过查看 OPTIMIZER_TRACE，number_of_tmp_files表示排序使用的临时文件数，外部排序一般使用归并排序算法。

rows表示满足city='杭州’有4000条，examined_rows=4000表示4000行参与排序。

sort_mode packed_additional_fields表示排序过程字符串做了“紧凑”处理。name字段定义varchar(16)，排序过程中按照实际长度分配空间。

最后一个查询语句 select @b-@a返回结果是 4000，表示只扫描了4000行。

这边老师把internal_tmp_disk_storage_engine 设置成MyISAM，否则，select @b-@a结果为 4001。因为innodb把数据从临时表取出来时，会让Innodb_rows_read 的值加 1。

rowid 排序

如果排序的单行长度太大mysql会使用另一种算法。

SET max_length_for_sort_data = 16;
复制

city、name、age 这三个字段的定义总长度是 36 > max_length_for_sort_data，所以会使用别的算法。

该算法和全字段排序的差别：

1. sort_buffer只会确定放入name 和 id字段，所以只会取这两个字段。

2. 最后根据name排完序，会根据id字段去原表取city、name 和 age 三个字段返回给客户端。

全字段排序 VS rowid 排序

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

假设从city索引上取出来的行天然按照name递增排序，就不需要再进行排序了。

所以可以建一个city和name的联合索引：

alter table t add index city_user(city, name);
复制

整个查询流程就变成了：

1. 从索引(city, name)找到第一个city='杭州’的主键id；

2. 到聚集索引取name、city、age三个字段，作为结果集一部分直接返回；

3. 从索引(city, name)取下一个。

4. 重复2、3，直到查到1000条记录，或不满足city='杭州’时结束。

没有"Using filesort"。

使用“覆盖索引”：

alter table t add index city_user_age(city, name, age);
复制

但维护索引是有代价的，所以需要权衡。

二、实际开发中关注项总结

1. 如何安全地给小表加字段？

查看information_schema 库的 innodb_trx 表中的当前事务，等待事务结束或者 kill 该事务。（另外MariaDB支持DDL NOWAIT/WAIT n 语法避免长时间等待导致业务不可用）

2. 从性能和存储空间方面考量，推荐使用自增主键

自增主键的插入数据模式，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。并且自增主键在非主键索引占用的空间最小。

3. 如何安排联合索引顺序？

假设a、b两个字段都需要索引，a字段存储空间比b字段大，则建议建（a，b）和 b 两个索引。

假设有PRIMARY KEY（a，b）和KEY c，则不需要建（c，a）索引，可以建（c，b）索引。

4. 避免长事务

5. 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

6. 怎么解决由热点行更新导致的性能问题？

• 如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉

• 控制并发度，降低死锁检测

• 将一行改为多行，比如把余额分成10个子余额，但这样需要考虑扣钱问题

7. 怎么删除表的前 10000 行？

在一个连接中循环执行 20 次 delete from T limit 500，避免长事务（delete from T limit 10000），避免多线程（20 个连接中同时执行 delete from T limit 500）

8. 从性能的角度考虑，选择唯一索引还是普通索引呢？

尽量选择普通索引，因为当更新记录的目标页不在内存中时，唯一索引需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；而普通索引来说，则是将更新记录在change buffer，语句执行就结束了。但是如果业务不能保证重复，就需要唯一索引保证。

9. MySQL有时候会选错索引？

平常不断地删除历史数据和新增数据的场景，mysql有可能会选错索引。sql太慢就用explain看看，有可能就是索引选错了。

10. 怎么给字符串字段加索引？

直接使用字符串建索引有时候可能效率较低，存储空间较大

• 使用前缀索引

• 例如身份证等前面相似度较大的字符串，可以采用倒序存储

• 建另外的字段（如hash字段）建索引

11. MySQL偶尔执行很慢

偶尔慢一下的那个瞬间，可能在刷脏页（flush）。innodb的redo log写满、buffer pool内存不足等情况。

合理地设置 innodb_io_capacity 的值，平时要多关注脏页比例，不要让它经常接近 75%。

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
复制

12. 为什么表数据删掉一半，表文件大小不变？

删除某行，innodb只会标记删除。如果之后在该行范围内插入新数据就会复用。假如表本身没有多少空洞，重建索引可能会使表文件变大。

重建主键索引：

alter table T engine=InnoDB;
复制

不推荐drop，再add。并且不论是删除主键还是创建主键，都会将整个表重建。

13. count(*)慢怎么办？

Innodb需要一行一行读出来累积计数，MyISAM 引擎保存总行数，所以count很快。

• 用缓存系统保存计数

• 在数据库保存计数

不同的 count 用法效率

count(字段)<count(主键 id)<count(1)≈count()，所以建议尽量使用 count()

14. order by

Extra中"Using filesort"表示排序，mysql会给每个线程分配一个块内存（sort_buffer）用来排序。

假设从某个索引上取出来的行天然按照递增排序，就不需要再进行排序了。但维护索引是有代价的，所以需要权衡。

15. 避免条件字段函数操作、隐式类型转换、隐式字符编码转换

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

16. 为什么只查一行的SQL也执行这么慢

查询长时间不返回：等MDL锁；等flush；等行锁

查询慢：扫描行数多；其他长事务导致undo log快照过多

17. 覆盖索引不给主键索引加锁，所以更新主键索引（没有建索引的列）不更新覆盖索引的情况不会等待。也就是只锁被访问到的对象

18. 在删除数据时，尽量加limit

limit删除数据时，只会扫描limit行数，不会继续扫描，所以加锁粒度更小。

19. 避免字段函数操作、避免隐式转换、隐式字符编码转换

20. 查询一行很慢

• 查询长时间不返回：等MDL锁、等磁盘flush、等行锁

• 查询慢：没走索引、长事务导致快照过多

  
  

21. 主备

• binlog 的三种格式

• statement 格式：由于没有记录行信息，删除时如果主备走的索引不一致会删除不同的行

• row 格式：记录的行信息

mixed 格式：由于可能以statement格式记录，所以也会主备不一致

• 实际生产上使用比较多的是双 M 结构

循环复制：规定两个库的 server id 必须不同，每个库收到主库发来的日志，判断server id是否和自己相同，相同直接丢弃日志。

• 主备延迟

在备库执行show slave status 命令，seconds_behind_master显示了当前备库延迟，精度秒。

• 双主切换

• 判断备库B现在的seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；

• 把主库A改成只读状态，即把readonly 设置成true；

• 判断备库 B 的 seconds_behind_master的值，直到这个值变成 0 为止；

• 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；

• 把业务请求切到备库 B。

上述过程会有一段时间不可用，假设4、5步互换会导致不一致。

binlog_format=mixed时，可能有两行不一致，binlog_format=row时会有一行不一致。

• 可靠性异常切换

假设主库掉电，必须等到备库B seconds_behind_master=0 之后，才能切换。

22. 读写分离

• 实时请求强制走主库方案

• sleep几秒（不推荐）

• 判断主备无延迟方案，对于一些从库还没有收到的请求还是会有延迟

• 配合 semi-sync，半同步只要一个从库返回ack就返回给客户端成功，但不能确保所有从库都同步完成

• 等主库位点方案，主库更新完后执行show master status 得到当前主库执行到的File 和 Position，拿到信息后查询前去从库select master_pos_wait(File, Position, 1)；判断是否同步完成

• GTID 方案，事务完成直接返回事务的 GTID，根据这个id去从库查询select wait_for_executed_gtid_set(gtid1, 1)；判断是否同步完成

  
  

23. Join

让小表做驱动表、被驱动表有索引。

如果被驱动表没有索引会走BNL算法，将驱动表加载到 join_buffer 中，将被驱动表中的数据一行行读出来与内存中的驱动表数据对比。

如果被驱动表是个大表，会把冷数据的page加入到buffer pool（join_buffer 用了其中的内存），并且BNL要扫描多次，两次扫描的时间可能会超过1秒，使上节提到的分代LRU优化失效，把热点数据从buffer pool中淘汰掉，影响正常业务的查询效率。

• Join优化

Multi-Range Read 优化

Batched Key Access：缓存读取多行传给被驱动表

• BNL 算法的性能

除了给被驱动表加索引之外，还可以使用临时表，创建临时表然后加索引

24. 临时表的应用

1. 临时表只能被创建它的 session 访问，对其他线程不可见。所以在这个 session 结束的时候，会自动删除临时表。

2. 临时表可以与普通表同名（还是不要这么做）。

3. session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。

4. show tables 命令不显示临时表。

• 分表分库跨库查询

分库分表系统都有一个中间层 proxy，如果 sql 能够直接确定某个分表，这种情况是最理想的。

但如果涉及到跨库，一般有两种方式：

• 在 proxy 层的进程代码中实现排序，但对 proxy 的功能和性能要求较高。

• 把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。如果每个分库的计算量都不饱和，那么直接可以在把临时表放到某个分库上。

  
  

25. MySQL 什么时候会使用内部临时表？

1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；

2. join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；

3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

   
   

26. group by使用的指导原则：

1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

2. 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；

3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；

4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

   
   

27. insert 唯一键冲突

执行相同的 insert 语句，发现了唯一键冲突，加上读锁（Next-key lock）。session A 回滚，session B 和 session C 都试图继续执行插入操作，都要加上插入意向锁（LOCK_INSERT_INTENTION）。

28. 怎么最快地复制一张表？

• mysqldump 方法

• 导出 CSV 文件

• mysql5.6 物理拷贝

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r：

• 执行 create table r like t，创建一个相同表结构的空表；

• 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

• 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；

• 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令；

• 执行 unlock tables，这时候 t.cfg 文件会被删除；

• 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

  
  

29. 分区表

innodb 只会锁一个分区，而 MyISAM 会锁所有的。

• 应用

• 分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。

• 按照时间分区的分区表，就可以直接通过 alter tablet drop partition …这个语法删掉分区，从而删掉过期的历史数据。

  
  

30. explain

• type

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

• system:表仅有一行(=系统表)。这是const联接类型的一个特例。

• const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

• eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。

• ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。

• ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。

• index_merge:该联接类型表示使用了索引合并优化方法。

• unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

• index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

• range:只检索给定范围的行,使用一个索引来选择行。

• index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

• ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

• Extra

• Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。

• Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。

• range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

• Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。

• Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

• Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。

• Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。

• Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。

• Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。