【MySQL必知必会 SQL优化之索引优化】

MySQL优化常用手段

• 优化SQL查询所涉及到的表中的索引
• 改写SQL以更好的利用索引

索引优化

B树与B+树

B树

何为B树，B树就是一颗多路平衡查找树，也是由简单的二叉树变来的，我们可以先看一个4阶的B树：

一颗m阶的B树定义如下：

• 每一个节点最多有m-1个关键字
• 根节点最少可以只有一个关键字
• 非根节点至少有m/2(向上取整)-1个关键字
• 每一个节点中的关键字都按照从小到大排列，每一个关键字的左子树的所有关键字小于它，右子树的所有关键字大于它
• 所有的叶子节点位于同一层，或者说根节点到每一个叶子节点的长度都相等

B+树

我们再来看看B+树，它算是B树的变形，对于B+树的定义有多种，一种定义方式是关键字个数与孩子节点个数相同，我们这里采用维基百科的定义，即关键字个数比孩子节点小1，我们先来看一张图，这是一颗4阶B+树：

一颗m阶B+树的定义如下：

• B+树有两种类型的节点：内部节点（也称索引结点）和叶子节点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点
• 内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
• 每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
• m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。
• 父节点存有右孩子的第一个元素的索引

比较

B树的优点：B树的每一个节点都包含Key和Value，因此，把经常访问的元素放在靠近根节点的地方将会大大提高元素的查询效率。这种特性使得B树在特定元素重复多次查询的场景中更加高效。

B+树的特点：

• 由于B+树的内部节点不存储数据，所以在内存页中可以存放更多的Key，有利于更快的缩小查找范围
• B+树的叶子节点都是相链的，所以对整棵树的遍历只需要遍历一次叶子节点，而且数据顺序排列且相连，所以有利于区间查询，而B树中，遍历一棵树需要递归遍历每一层，且相邻的元素在内存页中不相邻，缓存命中性没有B+树好

数据库为什么使用B+树

我们知道为了将大型数据库文件存储在硬盘上，以减少访问硬盘次数为目的，提出了一种多路平衡查找树–B树，但B树存在以下问题：

• 每个节点中既要存索引信息，又要存其对应的数据，如果数据量很大，那么当树的体量很大时，每次读到内存中的树的信息就会不太够。
• B树遍历整个树的过程和二叉树本质上是一样的，B树相对二叉树虽然提高了磁盘IO性能，但并没有解决遍历元素效率低下的问题

所以为了提高B树的性能，对其进行了变形，B+树诞生了，区别在于，B+树的所有根节点都不带有任何的数据信息，只存储了索引信息，所有的数据信息存储在叶子节点里，这每一个节点所占的内存空间变小了，读到内存中的索引信息就会增加，这相当于磁盘单次读取的信息量相对于B树增多了，磁盘IO次数减少了，除此之外，B+树的叶子节点还通过链表将所有数据信息串联起来，想遍历一颗树只需要遍历叶子节点就可以了，同时顺序排列且相连的特性，便于区间查询，而数据库中区间查询恰恰是常见的情况，最后，B+树的查询性能还很稳定，也就是从根节点到每一个叶子节点的路径长度是相同的，总结一下：

• B+树的磁盘读写代价更低
  B+树中内部节点没有存储数据信息，所以内部节点相对于B树更小，若把所有同一内部节点的关键字存储到同一磁盘块中，B+树所能存储的关键字更多，这就相当于磁盘IO读取次数减少了
• B+树的数据信息遍历更加方便
  B+树遍历叶子节点就可以遍历一棵树，同时还便于区间查询，这满足了数据库经常区间查询的需要，这些是B树没有的
• B+树的查询效率更加稳定
  B+树所有信息都存储在叶子节点里，从根节点到任意叶子节点的路径长度相同，所有查询效率稳定，而B树不能做到这一点
  有兴趣了解B树与B+树的插入删除操作推荐一下一篇文章
  B树与B+树
  B树与B+树的插入删除操作

Btree索引的特点

• 适用于全值匹配的查询

class_name='mysql'
class_name in ('mysql','oracle')

• 适合处理范围查找

study_cnt between 1000 and 3000
study_cnt >3000

• 从索引的最左侧列开始匹配查找
  

如何选择在什么列上建立索引

WHERE子句中的列（在筛选性好的列建立索引）

如若要在user表中建立索引，应该在那一列上建立呢，是sex列还是reg_time列,我们可以看这两列的筛选性各自为多少，筛选性好的作为索引列

第一行计算sex列的不重复值，第二行是reg_time列的不重复值，第三行是总行数，第四行计算sex列的筛选率，第五行是计算reg_time列的筛选率；

可以看出reg_time列筛选率高，所有选择在该列建立索引

CREATE INDEX idx_regtime ON imc_user(reg_time);

建立索引后查询列数变少了，性能提高了

多表JOIN的关联列

EXPLAIN
SELECT course_id,b.class_name,c.level_name,d.type_name,title,score
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id
JOIN imc_level c ON c.level_id=a.level_id
JOIN imc_type d ON d.type_id=a.type_id
WHERE c.level_name='高级' AND b.class_name='MySQL';

查看他们的执行计划，可以看到

只有d表有主键索引，其他列上都没有，而且a表的查询行数为100，是最多的，所以我们应该在a表与其他表的关联列上建立索引，

CREATE INDEX idx_classid_typeid_levelid ON imc_course(class_id,type_id,level_id);

给a表建立索引后，可以看到查询行数变少了
然后我们还可以给class表的class_name列建立索引，因为这一列的值都不重复

CREATE UNIQUE INDEX uqx_classname ON imc_class(class_name);

我们再查看level表的ddl发现，还可以给level_name列建立索引，

CREATE UNIQUE INDEX uqx_levelname ON imc_level(level_name);

通过建立三个索引，使得查询行数变为7，极大的提高了查询性能

如何选择复合索引键的顺序

• 区分度最高的列要放在联合索引的最左侧
• 使用最频繁的列要放在联合索引的最左侧
• 索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面
• 尽量把字段长度小的列放在最左侧
• 只能从最左侧开始按索引键的顺序使用索引，不能跳过索引键
• NOT IN 和<>无法使用索引
• 索引列上不能是表达式或函数

索引使用的事项

• 查询过滤顺序与索引键顺序不同，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的
• 使用IN列表也能用到索引
• 索引一般不超过6个，需要视业务情况而定，不是越多越好

SQL改写优化

1.使用OUTER JOIN 替代 NOT IN

上面查询语句可以改写成下面的，在MySQL8.0版本中会自动优化成下面的语句

2.使用CTE代替子查询
3.拆分大的SQL成多个小的SQL，再进行合并
4.对于计算值的情况，巧妙运用计算列

如我们可以新建一个计算列

然后再在这个计算列上建立索引

最后查询可以发现利用了这个索引，满足业务需求