1. SQL的执行过程

1.1 流程

首先客户端发送请求到服务端，建立连接。
服务端先看下查询缓存是否命中，命中就直接返回，否则继续往下执行。
接着来到解析器，进行语法分析，一些系统关键字校验，校验语法是否合规。
然后优化器进行SQL优化，比如怎么选择索引之类，然后生成执行计划。
最后执行引擎调用存储引擎API查询数据，返回结果。

1.2 分析

Server 层负责建立连接、分析和执行 SQL。
MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
存储引擎层负责数据的存储和提取。

1.3 索引下推

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

1
2
3

-- 联合索引 index(age, reward)

select * from t_user  where age > 20 and reward = 100000;

不使用

Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；
如此往复，直到存储引擎把表中的所有记录读完。

使用索引下推

Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
如此往复，直到存储引擎把表中的所有记录读完。

可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

2. 技术优化

Index Condition Pushdown 优化可以将查询效率在原有 MySQL 5.5 版本的技术上提高 23%，而再同时启用 Mulit-Range Read 优化后，性能还能有 400% 的提升！

2.1 Index Condition Pushdown 优化（索引下推）

复合索引直接判断，减少回表。

想象一下你去图书馆找书的场景：

没有索引下推（旧模式）：

你告诉图书管理员（MySQL Server）：“我要找所有姓‘张’的作者，并且出版年份在2010年之后的书。”
图书管理员助手（Storage Engine，如 InnoDB）很“笨”，他只懂得按作者姓氏索引（比如按姓氏拼音排序的书架）。
他跑到“张”姓作者的书架，把所有姓张的书（不管哪年出版的）全部抱出来，堆在一张大桌子上。
然后，图书管理员（MySQL Server）再亲自一本一本地翻看这些书的出版年份，把不符合“2010年之后”这个条件的书挑出来扔掉。
问题：助手（Storage Engine）做了很多无用功，搬了很多根本不需要的书，导致管理员（Server）的工作量也很大。

有了索引下推（新模式，ICP）：

你还是告诉图书管理员（MySQL Server）相同的需求。
现在，图书管理员助手（Storage Engine）变“聪明”了。管理员把两个条件都告诉了他：“去‘张’姓作者的书架，并且在书架上就顺便检查一下出版年份是不是在2010年之后。”
助手（Storage Engine）跑到“张”姓作者的书架。他找到一本姓张的书，不立即抱走，而是先翻开版权页看一下出版年份。
如果年份符合（>2010年），他才把这本书抱出来，交给管理员。如果不符合，他直接就地放下，看下一本。
最后，助手只把那些真正符合两个条件的书抱给了管理员。
优势：助手在索引层面（书架上）就过滤掉了很多数据，大大减少了需要搬运的书籍数量。这不仅减轻了助手的工作量（减少了I/O），也让管理员最终需要检查的书变得更少。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

当优化器选择 Index Condition Pushdown 优化时，可在执行计划的列 Extra 看到 Using index condition 提示。

假设某张表有联合索引(zip_code，last_name，first_name)，并且查询语句如下：

1	SELECT*FROM people WHERE zipcode='95054' AND lastname LIKE'%etrunia%' AND address LIKE'%Main Street%';

若不支持 Index Condition Pushdown 优化，则数据库需要先通过索引取出所有 zipcode 等于 95054 的记录，然后再过滤 WHERE 之后的两个条件。

若支持 Index Condition Pushdown 优化，则在索引取出时，就会进行 WHERE 条件的过滤，然后再去获取记录。这将极大地提高查询的效率。当然，WHERE 可以过滤的条件是要该索引可以覆盖到的范围。

2.2 Multi-Range Read 优化（多范围读）

收集主键，排序后批量回表，减少磁盘的随机访问。

想象一下，你手上有一份书单，上面列了 10 本你要借的书，这些书都属于“计算机科学”这个类别。

没有 MRR 的常规做法（随机查找）：

你在图书馆的索引系统（相当于 二级索引）里，按书名查到第一本书在 A区-3号架。
你跑到 A区-3号架，拿到书。
你回到索引系统，查到第二本书在 F区-8号架。
你又跑到 F区-8号架，拿到书。
你再回到索引系统，查到第三本书在 B区-1号架...
这个过程你需要在索引系统和各个书架之间来回奔波，路线毫无规律，非常低效。这就像 随机 I/O，性能很差。

使用 MRR 的做法（批量、顺序查找）：

你先不去书架，而是在索引系统里，把你书单上 所有 10 本书 的位置（A-3, F-8, B-1 ...）一次性全部查出来，记在一张纸上（这张纸就是 MRR 的缓冲区 Buffer）。
你看着这张纸，把这些位置 按书架号重新排序，比如：A-3, B-1, C-5...F-8。
现在，你拿着这张排好序的清单，按照 A区 -> B区 -> C区 ... 的顺序，一次性、有条不紊地 走遍图书馆，把所有书都拿到手。
这个过程，你只需要完整地逛一遍图书馆，路线是规划好的，效率极高。这就类似于 顺序 I/O，性能非常好。

MRR 的核心思想就是：把“随机”的访问，通过排序，变成“顺序”的访问。

MySQL5.6 版本开始支持 Multi-Range Read（MRR）优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于 IO-bound 类型的 SQL 查询语句可带来性能极大的提升。

通常情况下，在执行查询时，MySQL会遍历整个索引树，以找到所有匹配的行。但是，对于大型数据集，这种方式可能会导致性能下降，因为它需要大量的磁盘I/O和CPU资源。

MRR通过将索引分成多个范围并在内存中缓存结果来避免这种情况。在使用MRR时，MySQL会尝试将查询范围分成多个不重叠的部分，并使用范围扫描技术来查找每个部分中的匹配行。这种方式可以有效地减少磁盘I/O和CPU消耗，从而提高查询性能。

MRR能够提升性能的核心在于，一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

3. 头脑风暴

执行过程：建立连接，查询缓存，语法分析，sql优化，到存储引擎层获取数据。
索引下推，一般是一个复合索引，查询的时候，直接在存储器过滤。
多范围读MRR。理解为查询条件拆分，然后再进行数据查询，减少磁盘的随机访问。

4. 参考资料

https://www.xiaolincoding.com/mysql/base/how_select.html

Levon's Blog

mysql的执行过程和优化技术