本文介绍MySQL的逻辑架构，引出一条select语句的执行流程
MySQL逻辑架构
MySQL的逻辑架构可以分为Server层和存储引擎层两部分
Server层包括连接器、查询缓存、分析器、优化器、执行器等；存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、 Memory等多个存储引擎
注：MySQL8.0将查询缓存删除
Server 层
连接器
负责建立、维持、管理客户端的连接，权限认证
查询缓存
将之前执行过的查询语句及其结果以key-value对的形式缓存到内存中，如果能够直接在这个缓存中找到key，则直接返回查询结果
只要有一个表的更新，这个表的查询缓存就会被清空，导致查询缓存的失效非常频繁，如果查询缓存的命中率非常低，则反而影响了效率
MySQL8.0将查询缓存删除
分析器
对查询语句做语法分析
优化器
选择索引，join多表关联时，决定各表的连接顺序
执行器
- 判断用户对表是否有执行查询的权限
- 调用搜索引擎的接口取值
存储引擎层
只讨论InnoDB
Server层的执行器调用存储引擎的接口，如使用索引name查询name=Tom 的记录，这个流程是什么样的呢
首先介绍索引的类型，以及几种索引机制
索引类型
索引类型分为主键索引（聚簇索引）和非主键索引（二级索引）
如果语句是select * fromTwhere ID=500，主键查询方式，只需要搜索ID这棵B+树；
如果语句是select * fromTwhere k=5，普通索引查询方式，需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表
可以看到回表会增加一次索引查询，在范围查询中，如select id, name from user where age > 10，则需要回表很多次，如何避免回表，提高效率呢
覆盖索引
如果select的字段在一个二级索引中已包含，则不需要回表，这个二级索引对于覆盖了查询需求的语句是一个覆盖索引
如 select id, name, age from user where name like 'Jane%' and age < 18; 建立一个联合索引A (name, age)，在A中，已经包含了要查询的id, name, age信息，无需回表
最左前缀匹配
最左前缀匹配是针对联合索引而言
如下，为(name, age)的联合索引
索引项是按照索引定义里面出现的字段顺序排序的，匹配时按照从左至於的顺序匹配
- 只要是联合索引的那几个条件与where子句中的条件匹配，都能应用对应的联合索引
- 在有联合索引（a+b）和单列索引a同时存在时，通过a条件的查询会优先使用联合索引
- 在联合索引（a+b）中无法只通过条件b查询，因为b无法匹配最左前缀，这时只能再建一个(b)索引
索引下推
对于mysql> select * from user where name like '张%' and age=10 and ismale=1;
在MySQL 5.6之前，只能从第一个满足【张*】的记录开始一个个回表。到主键索引上找出数据行，再对比字段值
而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
如联合索引 (name, age)，会找到 name 和 age 符合条件的 id，再回表
根据上述的索引查找机制，对于一个查询，走哪个索引，是否要回表，就有了一定的认知，进而可以对一些场景进行优化
索引的优化
- 覆盖索引：对于频繁查询 select name, age from user where age between (10, 20)，建立联合索引（age, name）避免回表
- 索引下推：对于查询 select * from user where name like '张%' and age > 10; 建立联合索引（name, age），相对于单个name索引，可以减少回表次数
题外话：在用户表中，有身份证号的字段，要不要用身份证号做主键
分析：
- 身份证是无序的，在维护主键索引树的时候，需要挪动其他记录，引发叶分裂
- 身份证号长度较大，主键长度越大，二级索引的叶子节点长度就越大，二级索引占用的空间就越大
答案：不要
用自增id做索引，在主键索引中，每次插入一条新记录，都是追加操作，不涉及到挪动其他记录，也不会触发叶子节点的分裂，相比于身份证长度要短:+1:
总结
本文介绍了MySQL的逻辑架构，包括Server层和存储引擎层，Server层包括连接器、查询缓存、分析器、优化器、执行器；存储引擎层介绍了索引类型，如何通过索引找到记录，索引的一些机制，如覆盖索引、最左前缀匹配、索引下推，最后列举了两个索引的优化策略。一条select语句，就是通过Server层和索引层，最终找到对应的记录
参考文献
mysql 45 讲 第1,4,5讲