MySQL调优思路(回表、LRU算法、索引下推、预读取失效、缓冲区污染、刷脏、sql执行流程、redo、undo、bin log)

回表、避免回表、索引下推、刷脏、LRU算法、索引覆盖、预读取失效、缓冲区污染、rodo Log、undo Log、bin Log、sql执行流程

前言

闲来无事，就准备写一篇MySQL的文章来帮助一些萌新童鞋更好的了解MySQL数据库。MySQL文档官方网址
当然，本人水平有限，如有误导，欢迎斧正，一起学习，共同进步！

一、一条Sql在MySQL的执行流程？

正常的一条sql写完是怎么在数据库中执行的呢，它的具体执行流程是什么呢？

1、总体流程：

1.1、查询操作流程(query)：

查询缓存(query cache)–>查询解析器(parser)–>预处理器(pre processor)–>查询优化器(query optimizer)–>执行计划(explain plan)–>执行器(Executor)–>存储引擎(Storage Engine)

1.1.1、具体介绍每个节点

查询缓存：是说第一次查询以后，第二次如果是同样的sql，就不查数据了，直接从缓存中拿结果。但是这个MySQL官网已经不推荐使用了，一来是不好用(命中效率低)，二来是有替代它(Buffer Pool)的了。

不好用的原因是因为：但凡sql有一丁点不一样，就不会走缓存。如下示例
select name from user where subject = ‘语文’
select name from user as u where subject = ‘语文’ ##加了别名，不会走缓存
select name from user where subject = ‘语文’
select name from user as u where subject = ‘语 文’ ##加了空格，不会走缓存

查询解析器：查询解释器主要作用是看有没有语法错误的，有些类似于Java的编译，编译通过了才能进行下一步的操作
查询解析器：查询解释器主要作用是看有没有语法错误的，有些类似于Java的编译，编译通过了才能进行下一步的操作。内部有词法解析、语法解析，生成解析树，通过判断关键词来判断sql的语法有没有问题。
预处理器：预处理器是进一步校验上面的解析树是否合法，校验语法是不是通的。可以简单的理解为，查询解释器是用来看这句话有没有语病，预处理器是看这句话能不能读的通。
查询优化器：这步主要是对你的sql进行优化的。比如说，你写的sql语句不符合最左匹配原则，如果按你的sql查的话，是不会走对应的索引的，但是结果却是走索引了，这就是查询优化器给你做的优化。又或者是多表查询时，你把数据量小的表(筛选力度大的表)写在后面了，优化器也会帮你优化的。
执行计划：Explain是我们Sql优化时最常用的关键字。在下面会详细介绍。可以先简单介绍一下，explain的参数中，key有的比没有的效率高；rows小的比大的效率高，type类型中前面分类的比后面分类的效率高。
执行器：执行器就是去存储引擎中查数据的。

1.2、更新操作流程(update)：

比如说， name=“天明” 改成(update) name=“明天”

1. 开启事务
2. client >
3. server > …(还是查询那一套) 查询到name=“天明”
4. engine 引擎中
5. buffer pool & log buffer
6. 修改name=“明天” (此时修改的是内存，不是db file磁盘)
7. 记录name=”明天“到redo log便于重做(刷盘，刷脏)
8. 记录bin log日志以便于备份恢复
9. 记录undo log name=“天明” 以便于回滚(注意，这里是写反的)
   10.提交事务，修改db file
10. doubleWrite双写机制确保刷脏成功

1.3、相关概念

1.3.1、redo Log & undo Log & bin Log

• redo Log:数据恢复。比如你写数据写到一半，数据库宕机了，出问题了，那么怎么还原数据呢，就是通过这个redo 日志去实现的(redo log是用来恢复数据的 用于保障，已提交事务的持久化特性)。mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。那么问题来了，如果还没来的同步的时候宕机或断电了怎么办？还没来得及执行上面图中红色的操作。这样会导致丢部分已提交事务的修改信息！所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。
• undoLog:事务回滚时用的。(提交事务之前，先把undo log写好，然后再去修改db file 文件。在你修改db file文件的过程中，如果事务有问题了，那么就通过这个undo log去回滚)而且这个undo log日志，存的是相反的。就是比如你的是insert操作，那么undolog中存的就是delete操作。如果你update的是天明，那么undo log存的就是明天
• binlog:做备份和恢复的。将全部的 DDL、DML 操作记录下来，一旦数据库宕机或异常，就把全部的bin Log日志执行一遍，来达到恢复数据的效果。

1.3.2、刷脏(刷盘)

刷脏刷脏，什么是脏，比如说 update name = “天” 。update之前 name是"明"。那首先查出来的，肯定是明。那么buffer pool里面的，就是叫明。然后你update之后，你的库里面的name现在是天了，但是你的buffer pool里面还是明，是对不上的。如果不一样的时候，我们就称之为脏数据。为了保证数据一致，就有一个双写机制，也就是刷脏。

二、MySQL的存储引擎相关

以下是从官网截取的截图，感兴趣的童鞋可以去官网中自行查看(前言部分有官网地址)，在次我作着重介绍InnoDB和MyISAM。

1.innodb

1.1、innodb三大特性：

1、Buffer Pool：意思是，第一次查库，然后就放到buffer pool中了，然后下次就从buffer pool中查。
2、Adaptive Hash Index：发现使用二级索引，频繁查询(大于等于3次的时候，)就会把数据当做是热数据放到这个自适应hash索引中去。
3、DoubleWrite：(引入的目的是)刷脏失败时，通过redo Log 确保重做能正常。日志的预写机制(涉及到刷脏)

2.LRU算法

LRU(least Recently Used)：最近最少使用原则。大概意思是，谁用的频率高，我就把谁放在前面，让你更快的被查到。下次直接从内存(Buffer Pool)里面去拿，就不用从数据库中去查了。因为查内存比直接查磁盘速度更快，性能更好。

2.1、传统的LRU算法

就是说，缓存有这么大，你让哪些数据往前，哪些数据往后呢。这个是buffer pool嘛，是内存中加载的。
假设我的内存现在就只能放10个，然后每个里是16kb的数据，然后假设我这次是查的编号是4的内容，那么我就会把4放在最前面。那假设我现在查的数据，这个buffer Pool里面没有，我怎么办呢，那么查出来以后，就会加载到最前面，然后把最后面的(7)给挤出去，就不在内存中了，下次想查，就得从磁盘中查了。

2.2、传统LRU算法的问题

但是传统的 LRU 会有俩问题：预读取失效、缓冲区污染。
预读取失效：你提前把数据写到缓存中去了，但是mysql最终也没有从缓存里面读取数据，都是从库查的，没用到你。
缓冲区污染：比如我一个很大数据的表，然后我还写了个select * from，那么这个select * 查到的数据就会把我缓存区给全部替换了。因为你查的多嘛，然后就算你16k能塞100条数据，你内存(Buffer Pool)一共能放100个16k，那你数据量一大的话，内存(Buffer Pool)中的数据还是会全部的替换成你刚查的这个数据，其他的数据都会被这个数据给挤出去。

2.3、MySQL的LRU算法

其实有点类似于jvm，jvm的堆内存空间嘛，新生代比老年代默认是1比2(其实早已不是1:2了。).mysql的lru是把内存(Buffer Pool)分为两部分，一部分New、一部分Old。默认是New占63%，Old占37%。这是默认。new区和old区是首尾相连的，new的最后一个连着old的开头。大概是，查到数据以后，优先塞到 old区。然后确定这个数据是热点数据了，好多人都查这个数据话，就把这个数据移动到new区(默认是，过了1秒钟，热度仍在，就会移动到new区)。这样的话，即使是污染数据，也只是污染了old区，而对真正的热点数据存的new区影响不大。

三.索引

1、索引分类(主键索引&普通索引)

其实对于Mysql来说，只有两类索引：Secondary Index(二级/辅助索引)、Clustered Index(聚集/主键索引)。他们最主要的区别就是，是否存了行数据row date。
常见的数据结构有：数组、链表、哈希、树、网、图。其中适合做索引的有树、哈希。

Clustered Index(聚集/主键索引)：如果有主键id，则id自然而然是聚集索引；如果没有主键，那么就会找唯一且非空的索引做聚集索引；如果既没有主键id，又没有唯一且非空的索引，就会创建一个rowId来当聚集索引。
Secondary Index(二级/辅助索引)：其实也是b树，只不过最下面的叶子节点，只存了id，没有存完整的数据；而主键索引是存的完整的数据(行数据)的。
一般一个表中，只有主键是Clustered Index，其他索引是普通索引。对于索引来说，只要有一个索引(比如说索引1)存全部的数据就行，其他索引(比如是索引2)存一个前面索引(索引1)的id就行。就比如说，Java中的一个方法写好一个逻辑，其他方法直接调用前面的方法就行，而不是把逻辑在写一遍。所以，主键索引的叶子节点存行的全部数据，普通索引的叶子节点存主键索引的id。因为如果你的普通索引也像主键索引一样，叶子节点存全部的行数据的话，不仅会造成冗余，重复造轮子，而且这样的话索引的数据量太大，光索引就这么大，那数据量呢，索引也失去了它原有的意义。

2、B+Tree

2.1、B-Tree和B+Tree区别？

先说一下原理，二叉树可以是那种左右对称的平衡二叉树，但是也可以是那种一条腿的二叉树，而如果是一条腿的，效率就会很差。b+tree就是为了避免这个情况出现的。
区别：
B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：
①、B+Tree中的非叶子结点不存储数据，只存储键值；B 树存储数据。
②、B+Tree的查询更稳定，因为不会出现那种一条腿的情况。
③、B+Tree的范围查询效率更高。因为B+Tree的数据都存在叶子节点，并且有相邻节点的指针的指向，而B树的数据是存在每个节点上的，更加分散。
④、B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
⑤、B+Tree的每个非叶子节点由n个键值key和n个指针point组成；
好处：
①、磁盘读写代价更低
那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key
②、查询速度更稳定
由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。
很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

2.2、B+Tree特点

1、非叶子节点没有date数据，存键值(索引的值，指针的键)信息
2、所有的叶子节点都有一个双向的指针
3、data全放在叶子节点上

2.3、B+Tree缺点

既然b+树这么好(叶子节点是双向链表，范围、等值查都快；非叶子节点不存数据只存索引，让树更矮，进行io操作更少)，那能不能全部的索引都用b+树呢，这样的话，就相当于你重复造轮子了，比如说你有一个逻辑，然后用a方法(主键索引)封装好了，你b方法(普通索引)又把一模一样的逻辑封装了一遍，还不如直接b方法里面调用a方法。MySQL就是这么做的，主键索引存全部的行数据，普通索引存主键索引的主键id。b+树也有缺点，就是占的空间太大了，因为是全部的数据都存起来，而不是只存索引。

3、索引结构

如图所示，如果是主键索引的话，那么叶子节点中，就会把这行的数据都存下来。如果是普通索引的话，最下面的叶子节点只会存一个主键索引的id。这就涉及到了一个回表的操作。

4、回表&怎么避免回表

回表：第一次走索引树查数据，没有把完整的数据获取到，还需要根据主键id再查一次，把其他的数据也查出来。(select name,type from user) 但是你只给了name加索引了，那么就需要回表。先根据name找到索引的叶子节点，因为name是普通索引，那么叶子节点的数据只会存主键索引的索引id，主键索引的叶子节点，才存的是完整的数据行。即：先根据name索引，去找到主键索引的索引id，再根据主键索引的索引id，去查到完整的数据行数据(即拿到type的值)，这就是回表，很明显的发现，进行了两次io操作。

那怎么避免回表呢？
①、就是覆盖索引。什么是覆盖索引呢？就是比如说你查 name、type。需要回表，那你直接建立一个name、type的联合索引，不就不需要回表了，因为全部的数据都被你查出来了啊
②、索引下推(索引下推的目的就是为了减少回表的次数)。

5、索引下推

5.1、索引下推解释

索引下推 icp(Index Condition Pushdown)。要想有索引下推，前提条件就是走的二级索引，
目的：为了减少查询的次数，提高查询的效率
设计思路：将部分上层(服务层)负责的事，交给下层(引擎层)去处理
标识：在explain的 Extra 字段中 Using index condition 表示出现了索引下推。
正常的查询流程是： 客户端访问数据库服务端，数据库服务端去调搜索引擎(innoDB，MyISAM)，搜索引擎返回数据给服务端，数据库服务端返回给客户端。

比如说，
你有一个 name + phone 的联合索引。如果你的数据中，有 天明、天赐、天宇等三条数据，且天明的phone是15631698081。

explain select * from group_user where name like '天%' and phone = 15631698081;

如果没有索引下推(icp) 的话，是客户端查数据库服务端，数据库服务端查数据引擎，数据引擎根据“天”去联合索引中查，查到这些数据(此时是天明、天赐、天宇这三数据)的索引id，然后回表查到完整数据行(回表3次，因为给server端肯定是要给全部的数据行的，不能说给部分数据的。)。然后返回给数据库服务端，数据库服务端在根据phone去进行过滤(引擎是查出了全部的“天“开头的数据，然后回表查全部的“天”数据，然后都推给了服务端，数据库服务端自己去过滤)。
如果有索引下推 的话，则同样是客户端调用数据库服务端，服务端调用引擎端，然后数据引擎根据“天”去查出全部的数据以后，在根据phone的条件去过滤。然后再根据筛选后的数据(此时只有天明一条数据)的索引id去进行回表操作(此时只回表1次，)，然后将数据推送给数据库服务端。服务端已经不用再过滤了。这样一来，回表的次数就减少了。

其实这又引出了最左匹配原则。因为你可能觉得，为啥查出来的是“天明”、“天赐”、“天宇”这三数据，而不是同时根据name和phone来联合查出来的呢。就是因为，最左匹配原则，因为InnoDB的索引的默认结构是B+树，而构建b+树只能是一个值来构建节点，但你又是联合索引，那怎么存呢，就是最左边的字段。

5.2、索引下推的条件：

1、二级索引
2、InnoDB引擎和MyISAM引擎
3、只能用于type是 range、ref、eq_ref、ref_or_null 访问方法

四、explain的参数

对于mysql来说，直接explain关键字就行。其中值得在意的有三个字段，type、key、rows、extra。

4.1、type字段(性能从好到差)

system：该表只有一行(相当于系统表)，system是const类型的特例
const：针对主键或者唯一索引的等值查询结果，最多只返回一行。查询速度很快，因为只读取一次即可。
eq_ref：使用了索引的全部组成部分，并且索引是primary key或者unique not null 才会使用该类型
ref：满足了索引的最左前缀原则，或者索引不是主键也不是唯一索引的时候会走这个。如果使用的索引只会匹配一小部分，性能也是不错的。
…
index：全索引扫描，和all类似，只不过index是全盘扫描了索引的数据。(两种场景会触发：a、只查索引就会满足查询中所需的数据；b、按索引的顺序去查找数据行，执行了全表扫描，比如说分页查 10000到10010的数据)
all：全表扫描，性能最差。

4.2、key

其实就是单纯的索引的名称，如果有就是你定义的索引名；没走索引就是null

4.3、rows

此次查询所涉及的行，行数越小越好

4.4、extra

主要是一些标识。比如说 extra的值为Useing index condition 是索引下推。

五、调优方向

其实调优嘛。也就那几个方向。单表数据量大、没索引、有索引查的慢。

5.1、单表大(分区、分表)

单表数据量大的话，可以分区，可以分表。

• 分区是说，比如你的订单数据，一年的量太大了，你按季度来分，第一季度是一个表，第二季度是一个表，如果单个季度表的数据还大的话，还可以继续分，第一个月是一个表，第二个月是一个表等等。
• 分表是说，其实也是一个大表拆分为小表。不管是MyCat还是Sharding-JDBC,原理都是一样的，都是根据索引规则来分片(比如说对id求hash，hash值是1的存这个表，hash值是2的存那个表)，然后存到不同的地方。只不过一个是客户端代理，一个是服务器端代理而已。

5.2、没索引查不动

经过上面的操作，大表已经变成了小表，小表还查不动的话，就加索引呗，加了索引一般都会变快。当然，也有情况是，不加索引(全表扫描)更快。加了索引反而更慢。不过这种情况比较少见，属于具体情况具体分析了。

5.3、有索引查不动

有索引查不动，就explain看原因，就是explain的几个参数来看原因，找问题。

六、总结

其实只要合力的创建表结构，合力的构建表索引，即使你的单表是千万级别的数据，依然能查的很快。因为通常情况下，索引的B+Tree是3、4层，而第1、2层节点几乎都能加载到内存中，第3、4层需要进行io操作，也就是2次io就能查到数据，所以效率还是很可观的。