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

MySQL架构
MySQL-1.svg
图1. MySQL的逻辑架构图

大体来说，MySQL可以分为Server层和存储引擎层两部分：

  • Server层包括连接器、查询缓存、分析器、优化器和执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
  • 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM和Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL5.5.5版本开始成为了默认存储引擎。

连接器
基础：

  1. 连接器负责跟客户端建立连接、获取权限和管理连接。
  2. 连接完成后，如果没有后续动作，这个连接就处于空闲状态，可以在show processlist命令中看到它。
  3. 客户端如果太长时间没动静，连接器会自动将它断开，这个时间由参数wait_timeout控制，默认为8小时。
  4. 如果在连接被断开之后，客户端再次发送请求，会受到错误提醒：Lost connection to MySQL server during query。

扩展：

  • 长连接：如果客户端持续由请求，则一致使用同一个连接。
  • 短链接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建议：

  • 建立连接的过程通常是比较复杂的，尽量减少连接的动作，也就是尽量使用长连接。

问题：

  • 全部使用长连接后，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启。

解决：

  1. 定期断开长连接。
  2. MySQL5.7及以上版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存
MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前是不是执行过这条语句。
之前执行过的语句及其结果可能会以Key-Value键值对的形式，被直接缓存在内存中。

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

MySQL也提供了按需使用的方式，可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以使用SQL_CACHE显示指定，比如：

  1. select SQL_CACHE * from T where ID = 10;

注意：MySQL8.0版本直接将查询缓存的整块功能删除了。

分析器

  1. 对SQL语句进行解析，分析器先会做“词法分析”。根据输入的由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。从输入的关键字识别出SQL类型，然后识别表名和列名。
  2. 接着做“语法分析”，根据词法分析的结果，语法分析器会根据语法规则，判断输入的SQL是否满足语法。如果语法不对，就会收到“You have an error in your SQL syntax”的错误提醒。

优化器
在开始执行之前，还要经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器
开始执行的时候，要先判断用户是否有查询权限，如果没有，就会返回没有权限的错误。
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

对于没有索引的表，会调用接口引擎从表的第一行开始取，一直取到最后一行，遍历每一行，将所有满足条件的行组成的记录集作为结果集返回给客户端。
对于有索引的表，执行的逻辑也差不多。但是，第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

小结
简要学习了MySQL的逻辑架构，对SQL语句完整执行流程的各个阶段有了一个初步印象。

小问题
如果表T中没有字段k，那么在执行select * from T where k = 1时，报错”Unknown column ‘k’ in ‘where clause’”，这个错误时在哪个阶段报出来的？
解析：分析器处理语法和解析查询，生成对应的解析树。
预处理器进一步检查解析树的合法性，比如数据表和数据列是否存在和别名是否有歧义等。
在分析阶段判断语句的合法性，这一点MySQL的设计受到Oracle的影响颇深。

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

  1. update T set k = k + 1 where ID = 2;

查询语句的那一套流程，更新语句也会同样走一遍。
分析器会通过此法和语法解析直到这是一条更新语句，优化器决定要使用ID这个索引。然后，执行器负责具体执行，找到这一行，然后更新。
与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和binlog（归档日志）。

redo log
redo log是InnoDB的特有日志。
WAL技术，全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。
具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB的redo log是固定大小，比如可以配置为一组4个文件，每个文件的大小是1GB，那么就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。
MySQL(01-05) - 图2
图2. redo log日志写入

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头；checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
writepos和checkpoint之间的是空余部分，可以用来记录新的操作。如果writepos追上checkpoint，表示已满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

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

binlog
binlog是Server层自己的日志。
最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件的形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所有InnoDB使用另外一套日志系统，也就是redolog来实现crash-safe能力。

不同

  1. redol og是InnoDB引擎特有的，binlog是MySQL的Server层实现的，所有引擎都可以使用。
  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的K字段加1”。
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。追加写是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

流程
执行器和InnoDB引擎在执行，update语句时的内部流程：

  1. 执行器先找引擎ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

简记：

  1. 取改行数据
  2. 更新并刷回内存
  3. 写redo log，prepare状态
  4. 写binlog
  5. 提交事务，redo log改为commit状态

流程图如下：
mysql-2.svg
图3. update语句执行流程

最后三步，将redo log的写入拆成了两个步骤：prepare和commit，这就是“两阶段提交”。

两阶段提交
第一阶段：事务处于prepare，持有prepare_commit_mutex，写入redo log；
第二阶段：事务写入binlog，将redo log上的事务打上commit，释放prepare_commit_mutex。

如果不使用两阶段提交会出现什么情况？

  1. 先写redo log后写binlog。假设在redo log写完，binlog还没有写完的时候，MySQL进行异常重启。redo log写完之后，系统即使崩溃，仍然能够把数据恢复过来，所以恢复后这一行c的值是1。但是由于binlog没写完就崩溃了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。
  2. 先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃修复以后这个事务无效，所以这一行c的值是0。但是binlog已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

崩溃修复
redo log和binlog有一个共同的数据字段，叫做XID，崩溃修复的时候，会顺序扫描redo log：

  • 如果redo log里的事务是完整的prepare + commit，则进行提交。
  • 如果redo log里的事务是不完整的，获取redo log的prepare事务列表和最后的一个binlog的Xid列表进行结合：
    • 如果binlog事务完整，则进行提交。
    • 如果binlog事务不完整，则进行回滚。

小结
redo log和binlog都可以标识事务的提交状态，两阶段提交可以保证两个日志在逻辑上的一致性。

小问题
定期全量备份的周期取决于系统的重要性，有的是一天一备，有的是一周一备。
在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？
解析：一天一备的模式最长恢复时间更短。
一天一备：最坏情况下需要应用一天的binlog。
一周一备：最坏情况下就要应用一周的binlog。
系统对应的指标就是恢复目标时间（RTO），这个是有时间成本的，因为更频繁全量备份需要消耗更多的存储空间，所以这个RTO是成本换来的，需要根据业务重要性来评估。

03. 事务隔离：为什么你改了我还看不见

隔离性与隔离级别
原子性atomicity、一致性consistency、隔离性isolation、持久性durability。

  • 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读已提交：一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
  • 可串行化：对于同一行记录，写会加写锁，读会加读锁。当读写锁冲突的时候，后访问的事务必须等待前一个事务执行完成，才能继续执行。

事务隔离的实现
（1）保证原子性
利用InnoDB的undo log。
undo log：回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的SQL语句，它需要记录要回滚的相应日志信息。

  • 插入：记录数据主键，回滚时根据主键执行删除操作
  • 更新：记录之前旧值，回滚时根据旧值执行更新操作
  • 删除：记录这个数据，回滚时根据数据执行插入操作

（2）保证持久性
利用InnoDB的redo log，分为两部分：redo log buffer和redo log file。
MySQL使用WAL技术（Write-Ahead Logging，预写式日志）。当数据更新的时候，先更新数据到内存，并在redo log buffer中记录，并且写入binlog。事务提交的时候，将事务日志从redo log buffer写入到磁盘的redo log file中进行持久化。
redo log的写入使用两阶段提交：prepare和commit，保证和binlog的逻辑一致。
（3）保证隔离性
MVCC（Multiversion concurrency control ）是一种多版本并发控制机制。

  • 插入：添加隐藏两列，创建版本号(当前事务id)和删除版本号(null)
  • 查询：需要满足条件，创建版本号 < 当前事务id < 删除版本号
  • 删除：更新数据的删除版本号为当前事务id
  • 更新：复制一份数据，先执行删除，再执行插入，旧数据的删除版本号和新数据的创建版本号都设置为当前事务id

事务的启动方式

  1. 显式启动事务语句，begin或者start transaction。配套的提交语句是commit，回滚语句是rollback。
  2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这恶鬼事务持续存在直到你主动执行commit或者rollback语句，或者断开连接。有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

建议：使用set autocommit = 1，通过显式语句的方式来启动事务。

在autocommit为1的情况下，用begin显示启动的事务，如果执行commit则提交事务。如果执行commit work and chain，则是提交事务并启动启动下一个事务，这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地直到每个语句是否处于事务中。

可以在information_schema库的innodb_trx这个表中查询长事务，比如下面这个语句，用于查找持续时间超过60s的事务：

  1. SELECT 
  2.     *
  3. FROM
  4.     information_schema.innodb_trx
  5. WHERE
  6.     TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

小结
简单学习了事务隔离级别的现象和实现，以及从原理上了解长事务存在的风险和如何正确避免长事务。

小问题
如何避免长事务对业务的影响？
解析：
从应用开发端来看

  1. 确认是否使用了set autocommit = 0；
  2. 确认是否有不必要的只读事务。将好几个select语句放在事务中，这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令。来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

从数据库端来看

  1. 监控information_schema.Innodb_trx表，设置长事务阈值，超过就报警，或者kill；
  2. Percona的pt-kill这个工具不错，推荐使用；
  3. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
  4. 如果使用使用的是MySQL5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚过大，这样设置后清理起来更方便。

04. 深入浅出索引（上）

索引的常见模型
索引的出现是为了提高查询效率，但是实现索引的方式却有很多种。
三种常见的索引模型：哈希表、有序数组和搜索树。

  1. 哈希表：K-V存储数据的结构，适用于等值查询的场景，比如Memcached及其他一些NoSQL引擎。在MySQL中，使用链表法解决哈希冲突。
  2. 有序数组：等职查询和范围查询的性能都很优秀，但是更新索引的成本太高，只适用于静态存储引擎。
  3. 搜索树：查询的时间复杂度和更新的时间复杂度都是O(logN)，读写性能较好，适配磁盘访问，广泛应用。

B树和B+树
M阶B树的特征：

  1. 根节点的子节点数量为[2, M]；
  2. 每个中间节点都包含K-1个元素和K个子节点，其中M/2 <= K <= M；
  3. 每一个叶子节点都包含K-1个元素，其中M/2 <= K <= M；
  4. 所有的叶子节点都位于同一层；
  5. 每个节点中的元素从小到大排列，节点当中K-1个元素正好是K个子节点包含的元素的值域分划。

M阶B+树的特征：

  1. 有K个子树的中间节点包含有K个元素（B树中是K-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点；
  2. 所有的叶子节点包含了全部元素的信息，及指向含这些元素记录的指针，且叶子节点本身依关键字的大小自小而大顺序连接；
  3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大元素。

InnoDB的索引模型
InnoDB引擎使用了B+树的索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB中对应一颗B+树。
在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

假设有一个主键列为ID的表，表中有字段k，并且在k上有索引，即ID为主键索引，k为普通索引。
这个表的建表语句是：

  1. create table T (
  2.     id int primary key,
  3.   k int not null,
  4.   name varchar(16),
  5.   index (k) 
  6. ) engine = InnoDB;

表中R1~R5的(ID, k)值分别为(100, 1)、(200, 2)、(300, 3)、(500, 5)和(600, 6)。
ID和k索引对应的树如下：
MySQL(01-05) - 图4
图4. InnoDB的索引组织结构

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

那么问题来了，基于主键索引和普通索引的查询有什么区别？

  • 如果语句是select * from T where ID = 500，即主键查询方式，则只需要搜索ID这棵树B+树；
  • 如果语句是select * from T where k = 5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次，这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护
B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。
以上图为例，如果插入新的行ID=700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID=400，就相对比较麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

基于以上的索引维护过程，讨论一个案例：哪些场景下应该使用自增主键，而哪些场景下不应该。

自增主键是指自增列上定义的主键，建表语句定义：NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新纪录的时候可以不指定ID的值，系统会自动获取当前ID最大值加1作为下一条记录的ID值。
也就是说，自增主键的插入数据模式，正符合了我么前面提到的递增插入的场景。每次插入一条新纪录，都是追加操作，都不涉及挪动其他纪录，也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键。
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（binlog）则是8个字节。
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

那么，有什么业务场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务需求时这样的：

  1. 只有一个索引；
  2. 该索引必须是唯一索引。

这就是典型的KV场景。
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
这时候我么就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

小结
学习了常见的索引模型和索引维护。
InnoDB选择的B+树结构，能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

小问题
对于上述例子中的InnoDB表T，如果你要重建索引k，可以这么写：

  1. alter table T drop index k;
  2. alter table T add index(k);

如果你要重建主键索引，也可以这么写：

  1. alter table T drop primary key;
  2. alter table T add primary key(id);

对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什 么，更好的方法是什么？
解析：
先解释为什么要重建索引。索引可能因为删除或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
重建索引k的做法是合理的，可以达到省空间的目的。
但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。

05. 深入浅出索引（下）

问题引入
在下面这个表T中，如果执行select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
初始化语句如下：

  1. create table T (
  2.     ID int primary key,
  3.   k int not null default 0,
  4.   s varchar(16) not null default '',
  5.   index k(k)
  6. ) engine = InnoDB;
  8. insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

MySQL(01-05) - 图5
图5. InnoDB的索引组织结构

看一看这条SQL查询语句的执行流程：

  1. 在k索引树上找到k=3的记录，取得ID=300；
  2. 再到ID索引树查到ID=300对应的R3；
  3. 在k索引树取下一个值k=5的记录，取得ID=500；
  4. 再回到ID索引树查到ID=500对应的R4；
  5. 在k索引树取下一个值k=6，不满足条件，循环结束。

在这个过程中，回到主键索引搜索的过程，我们称为回表。可以看到，这个查询过程读了k索引树的3条记录，回表了两次。
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

覆盖索引
如果执行的语句是select ID from where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经覆盖了我们的查询需求，我们成为覆盖索引。
由于覆盖索引可减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
需要注意的是，在引擎内部使用覆盖索引在索引k上其实读了三个记录，R3~R5（对应的索引k上的记录项），但是对于MySQL的Server层来说，它就是找引擎拿到了两条记录，因此MySQL认为扫描行数是2。

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
假设这个市民表的定义是这样的：

  1. CREATE TABLE `tuser` (
  2.  `id` int(11) NOT NULL,
  3.  `id_card` varchar(32) DEFAULT NULL,
  4.  `name` varchar(32) DEFAULT NULL,
  5.  `age` int(11) DEFAULT NULL,
  6.  `ismale` tinyint(1) DEFAULT NULL,
  7.  PRIMARY KEY (`id`),
  8.  KEY `id_card` (`id_card`),
  9.  KEY `name_age` (`name`,`age`)
  10. ) ENGINE=InnoDB

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号，姓名） 的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务DBA，或者称为业务数据架构师的工作。

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

为了直观地说明这个概念，我们用 (name, age) 这个联合索引来分析。
MySQL(01-05) - 图6
图6. (name, age) 索引示意图

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。
当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。
如果你要查的是所有名字第一个字是“张”的人，你的SQL语句条件是where name like '张%'。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

基于上面对最左前缀索引的说明，我们来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a, b) 这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
那么，如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a, b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a, b)、(b)这两个索引。
这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name字段是比age字段大的，那我就减一你创建一个(name, age)的联合索引和一个(age)的单字段索引。

索引下推
最左前缀可用于在索引中定位记录，那么，那些不符合最左前缀的部分，会怎么样呢？

依然以市民表的联合索引(name, age)为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么SQL语句是这么写的：

  1. select * from tuser where name like '张%' and age = 10 and ismale = 1;

这个语句在搜索索引树的时候，只能用“张”，找到第一个满足条件的记录ID3。当然，这还不错，总比全表扫描要好。
然后判断其他条件是否满足。
在MySQL5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。
而MySQL5.6引入的索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤不满足条件的记录，减少回表次数。

无索引下推执行流程：
MySQL(01-05) - 图7
图7. 无索引下推执行流程
索引下推执行流程：
MySQL(01-05) - 图8
图8. 索引下推执行流程

无索引下推时，这个过程InnoDB 并不会去看age的值，只是按顺序把“name第一个字是‘张’”的记录一条条取出来回表。因此，需要回表4次。
有索引下推时，InnoDB在(name, age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。索引，跳过了ID3和ID6的回表，就只需要回表2次。

小结
进一步学习了覆盖索引、最左前缀原则和索引下推优化。在满足需求的情况下，尽量少地访问资源是数据库设计的重要原则之一。

小问题
表结构定义如下：

  1. create table `geek` (
  2.   `a` int(11) NOT NULL,
  3.   `b` int(11) NOT NULL,
  4.   `c` int(11) NOT NULL,
  5.   `d` int(11) NOT NULL,
  6.   PRIMARY KEY (`a`, `b`),
  7.   KEY `c` (`c`),
  8.   KEY `ca` (`c`, `a`),
  9.   KEY `cb` (`c`, `b`),
  10. ) engine = InnoDB;

现在这个表需要a、b做联合主键，但是业务里有两种语句：

  1. select * from geek where c=N order by a limit 1;
  2. select * from geek where c=N order by b limit 1;

那么为了这两个查询模式，ca和cb这两个索引是否都是必须的？为什么呢？
解析：
ca索引可以去掉，cb索引需要保留。
ca索引，通过索引对数据进行筛选，回表的时候，a本身就是主键索引，可以保证有序。
cb索引，b上没有索引，主键索引(a, b)无法满足最左匹配原则，需要保留加快排序速度。