1. 提问

问: MySQL中如何对SQL性能调优？

2. 当我们在谈到SQL性能优化的时候,我们究竟在讨论什么?

2.1 优化的对象——DQL语句

在绝大多的业务场景中，我们对数据的操作是查询。从数据库查找到我们想要的数据，经过服务端程序的二次加工，通过HTTP协议，使用JSON作为格式传递给前端，前端获得数据渲染页面，最终呈现给用户。这一系列的动作都存在可以优化的点，那么在数据库这一端，需要优化的就是select语句了。

2.2 优化的结果——“快”

查询的速度越快越好，这是我们的最终期望，只有更快了，用户体验才会更好，没有人会愿意面对一片空白的屏幕等上个数秒甚至数十秒，尽管在其他情况下我们对时间并没有这么敏感。

2.3 优化的基本思想——找到问题的根源，解决问题

提出问题，找到根源，解决问题。当你的想法越简洁的时候，你才能更好的命中目标。对于复杂的系统，只有一步步简化成一个个具体的问题时，你才能知道你到底需要做什么。
OK，闲话扯远了。按照目前的情况来看，问题还不够细致。当我们在进一步细化的这个大问题的时候，我们首先会想到：在调优之前，我得知道2件事：
　　1. “快”，“慢”的标准是什么？
　　2. 在知道标准的情况下，我要如何知道哪些SQL是不满足这个标准的即所谓的“慢查询”。

3.解决问题的第一步——实现一个小目标：找到需要处理的SQL

从此，我们开始踏上了我们解决“SQL性能调优”这个终极问题的第一步：找出我们需要调优的select语句。那么从哪找呢？回到MySQL中来，这时，我们需要用的就是MySQL日志系统中的慢查询日志（slow query log）。
MySQL的慢查询日志默认是关闭的，需要将配置文件中的slow_query_log设置为ON开启。慢查询日志还有一个参数long_query_time，单位是秒。设置了这个参数以后，所有超过该时长的SQL都会被记录，注意是所有SQL语句，而不仅仅是select语句。这样，我们就解决了2.3中的两个小问题：1.快慢的标准由执行时间决定，超过我们设置的执行时间的为慢；2.在慢查询日志中我们能够获取执行时间超过阈值的SQL。关于慢查询日志更为细致的内容，可以参考 MySQL慢查询日志总结

4.解决问题的第二步——SQL性能分析

现在，我们面对着一堆耗时“过长”的SQL（主要是select语句），我们不得不问自己一句：这些select语句是哪里出了问题呢？此时，我们需要借助的第二个工具就出现了：EXPLAIN命令。MySQL提供的这个命令可以对select语句的执行计划进行分析，并输出select执行的详细信息，通过在select语句前加上explain就可以使用该命令了。那么，让我们来看看explian命令都输出了些什么信息。

图4.1 执行explain命令
关于MySQL执行计划的每一个字段的含义，可以参考mysql 执行计划explain详解，如果我们想真正看懂执行计划，必须了解MySQL索引。

5. 一切问题的源头——索引

官方介绍索引时帮助MySQL高效获取数据的数据结构。索引可以提高数据检索的效率，降低数据库的IO成本，通过索引对数据进行排序，降低数据排序的成本，降低了CPU消耗。但是索引也有自己的问题，首先索引也是会占据磁盘空间的，索引虽然会提高数据的查询效率，但是会降低更新表的效率。在每次对表进行增删改操作的时候，MySQL不仅要保存数据，还要保存和更新索引文件。
怎么理解索引的工作原理呢？可以想象成查字典，要查的字就是我们select语句需要回去的数据，而拼音目录就是索引，我们可以通过查询拼音目快速定位到我们想要的字，这个过程就是使用索引进行查询，如果不用目录，一页页查找当然也是可以找到目标的，这种情况就是全表扫描了。
关于索引，这里对其分类和使用就不再赘述，请自行搜索。我们关注的重点在于，索引的原理以及索引的使用场景。

5.1 索引的原理性分析

MySQL的索引时由存储引擎实现的，不同的存储引擎会使用不同的数据结构来实现索引。MyISAM和InnoDB只支持B-tree，Memory引擎支持Hash和B-tree，由此产生了一个新的问题：为什么常用InnoDB引擎使用的是B-tree而不是Hash呢？

为什么不是Hash索引？

我们知道Hash索引由于其结构的特殊性，可以通过HashCode一次定位到索引，而B-tree每次都需要从根节点开始遍历到叶子节点，这其中磁盘的IO操作是比Hash要多的，所以Hash索引的查询效率确实要比B-tree要快。
但是，话又说回来。数据库是一个复杂的系统，要处理的查询远不止等值查询一种情况。而Hash索引的劣势正是由于它是使用计算后的Hash值作为过滤条件，导致了他仅仅在处理“=”和in条件下的查询才是准确的，对于范围查询它只能通过比较Hash值的大小，而这种大小关系并不能保证与运算前的大小关系相同。所以它对范围查询无能为力，也因为此，Hash索引无法处理排序以及模糊查询（like）。再有，Hash算法避免不了的问题——Hash冲突。一旦发生大量的Hash冲突以后，大量索引使用同一个Hash值，使得如果要进一步定位数据需要进行遍历，同时也无法从Hash索引中直接完成查询，依然要通过表扫描来定位数据，由此带来的性能损耗使得Hash索引在实际使用的过程中不一定比B-tree要快。

图5.1 Hash索引

为什么是B-tree？

Hash索引的方案已经被我们抛弃了，但是二叉搜索树这么多，为什么选择多路查找树B-tree呢？我们以红黑树来举例。
红黑树作为一个自平衡二叉树，它的当然是高效的，但是为什么不用呢？因为磁盘IO。一般来说索引本身也是很大的，不可能全部存储在内存里。而当索引作为文件存储在磁盘中后，索引查找过程中就要产生磁盘I/O消耗，索引的组织结构需要尽可能少的IO操作，而磁盘的IO操作又涉及到一个关键词——局部性原理。

磁盘本身为了提高效率减少IO操作，并不是严格的按需读取，而是每次都会预读，即使只需要一个字节，磁盘从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：
1.当一个数据被用到时，其附近的数据也通常会马上被使用。
2.程序运行期间所需要的数据通常比较集中。

由于磁盘预读机制的存在，一次预读的长度一般为页（page）的整倍数（linux中一页大小为4K），数据库将B-tree一个节点的大小设置为一页，这样一次IO就可以将一个节点完全加载，而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性。

一般来说B-tree的高度在2-4这个高度，如果是一个三层结构的B-tree，支撑的数据可以达到20G，如果是四层机构，则可以达到几十T

B-tree和B+tree的区别

B-tree和B+tree最大的区别在于非叶子节点是否存储了数据的问题。
B-tree在叶子节点和飞叶子节点都存储数据，而B+树只在非叶子节点存储数据，在B+tree的非叶子节点上只存储了子节点的指针。这个有什么好处呢？首先，上面我们提到过B-tree一个节点的大小是一页（page），节点内的数据是一项一项存放的，如果每一项的数据量减少了（不存数据只存指针），那么每一个节点所能存放的数据项就增多了，从而整个树的高度h就降低了，进一步减少了IO的消耗。然后，由于所有的数据都存储在叶子节点上，在B+tree树中任何数据的查找都必须走过从根节点到叶子节点的完整路径，每个查询的路径长度相同，导致每一个数据的查询效率相当。
而且，B+tree为所有叶子节点增加一个链指针，是的我们只需要遍历链表即可遍历所有的数据，而无需从根节点开始遍历每一个叶子节点。并且这个链表还是有序的，十分利于排序和范围查询。

图5.2 B+tree

聚集索引和非聚集索引

上面说过，索引是由存储引擎实现的，在MySQL中MyISAM和InnoDB两种存储引擎对于B+tree索引的实现也有所区别，分别称为非聚集索引（MyISAM）和聚集索引（InnoDB）

• 非聚集索引：B+tree的叶子节点并不存储具体数据，而是具体数据行的指针，即数据和索引不在一起。MyISAM中.myb文件存放表数据信息，.myi存放索引。
  

  图5.3 MyISAM主键索引
  MyISAM引擎中的主键索引和辅助索引都是非聚集索引，主键索引的叶子节点上存放的是行数据的地址，辅助索引的叶子节点上存放的是主键索引的地址。区别在于主键索引的Key具有唯一性，辅助索引的Key可以重复。
  图5.4 MyISAM辅助索引

• 聚集索引：B+tree的叶子节点直接存放具体数据，即数据和索引存放在一起。InnoDB中.ibd文件使用独享表空间存储表数据和索引信息，ibdata文件使用共享表空间存储表数据和索引信息。
  

  图5.5 InnoDB主键索引
  InnoDB中的主键索引的叶子节点会存放数据行，也就是数据和索引存放在一起的聚集索引，而辅助索引的叶子节点存放的是主键值（注意：不是地址）
  图5.6 InnoDB辅助索引