【前言】

经常有一些朋友向我咨询，如何写出高效的SQL，这不是三言两语能说得清的，索性认真来写一下，增删查改方面的知识我不再赘述，如果有基础薄弱的同学，可以好好的补一补再来看。

以MySQL为基础，MySQL调优篇内容主要包含MySQL逻辑架构、索引知识、表关联算法、explain执行计划解读及SQL调优实战等。

文章受众主要为两类人：

第一类人是工作中不可避免的会接触到MySQL的人，比如说一些项目人员、开发人员、测试人员等。

第二类人是专职DBA。

其实不管是专职的还是非专职的，就我接触到的情况而言，很多DBA平时维护MySQL看起来没什么问题，但其实没有很好的理论支撑，知其然而不知其所以然，解释一个简单的问题就能问倒一大部分的人。

比如说：MySQL的逻辑架构，分析当前业务架构优缺点？SQL工作原理是什么样的？

而且很多公司招聘面试的时候，考验的也是背后的原理居多，基本上没有机试。面试官问一个问题，即便你会解决但就是说不出原理，那么你肯定要不了高薪。

理论+实战=高薪

文章能够让大家有所收获、有所借鉴那是最好的。

【索引知识】

面试的时候，提起索引不要再拿目录类比索引的优点了，这样显的很菜。一句话：索引是数据结构，是一个排好序且快速查找的数据结构。

1、索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

查询是数据库的最主要功能之一，速度当然是越快越好，因此数据库系统的设计者会从查询算法的角度进行优化。常见的查询算法有顺序查找（linear search）、二分查找（binary search）、二叉树查找（binary tree search）等。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

这种数据结构，就是索引。

看一个例子：

图1展示了一种可能的索引方式。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

2、索引结构

MySQL 索引一般是哈希表或 B+ 树，常用的 InnoDB 引擎默认使用的是 B+ 树来作为索引的数据结构。

2.1为什么是B+树

B+树是B树的变种，是基于B树来改进的。为什么B+树会比B树更加优秀呢？

B树：有序数组+平衡多叉树； 

B+树：有序数组链表+平衡多叉树；

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。

B+ 树查找过程

磁盘块 1 中存储 17 和 35 数据项，还有 P1、P2、P3 指针，P1 表示数据项小于 17 的磁盘块，P2 表示数据项在 17 和 35 之间的数据项，P3 表示数据项大于 35 的数据项。非叶子节点不储存数据，只储存指引搜索方向的数据项。

我们知道每次 IO 读取一个数据页的大小，也就是一个磁盘块。

假设我们要查找 29 这个数据项，首先进行第一次 IO 将磁盘块 1 读进内存，发现17 < 29 < 35，然后选用 P2 指针进行第二次 IO 将磁盘块 3 读进内存，发现26 < 29 < 30，然后选用 P2 指针将磁盘块 8 读进内存，在内存中做二分查找，找到 29，结束查询。

通过分析查询过程，我们可以知道 IO 次数和 B+ 树的高度成正比。H 为树的高度，M 为每个磁盘块的数据项个数，N 为数据项总数。

从下面的公式可以看出如果数据量N一定，M越大相应的H越小。

M 等于磁盘块的大小除以数据项大小，由于磁盘块大小一般是固定的，所以减小数据项大小才能使得 M 更大从而让树更矮胖。这也是为什么 B+ 树把真实数据放在叶子节点而不是非叶子节点的原因。

如果真实数据放在非叶子结点，磁盘块存储的数据项会大幅度减少，树就会增高相应查询数据时的 IO 次数就会变多。

3、索引实现

在MySQL中，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

3.1、MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

下图是MyISAM索引的原理图：

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

3.2、InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

1、InnoDB的数据文件本身就是索引文件。2、InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

4、如何使用索引

说了那么多原理，总结一下索引的优缺点和使用时机。

4.1、优点

• 提高数据检索的效率，降低数据库的IO成本；
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

所以记住，索引功能是：搜索+排序

4.2、缺点

• 索引提升查询效率的同时也会降低更新的效率，更新表时，MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

4.3、索引使用时机

• 主键自动建立唯一索引；
• 频繁作为查询条件的字段应该创建索引；
• 查询中与其它表关联的字段，外键关系建立索引；
• 单键/组合索引的选择问题， 组合索引性价比更高；
• 查询中排序的字段，如order by create_time，排序字段若通过索引去访问将大大提高排序速度；
• 查询中统计或者分组字段；

【总结】

索引调优是一项技术活，没有理论不行，仅靠理论也不行，实际情况千变万化，结合实践中不断的实验和摸索，从而真正达到高效使用MySQL索引的目的。

以上就是全部内容了，下一篇讲表关联算法相关的知识，希望对大家的学习或者工作具有一定的参考价值。

【往期回顾】

MySQL调优篇 | 逻辑架构解读（1）


更多精彩内容，关注我们▼▼