MySQL索引原理及性能优化

一、MySQL底层数据结构及算法

索引的本质是为了帮助mysql高效获取数据的排好序的数据结构。

1 索引的数据机构分类

数据结构模拟地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树

二叉树 (Binary Tree) 是 n(n ≥ 0) 个结点的有限集合，该集合为空集时称为空二叉树，由一个根结点和两棵互不相交的、分别称为根结点的左子树和右子树的二叉树组成。例如上文作为例子的树结构，由于出现了一个结点有 3 个子树的情况，所以不属于二叉树，而如图所示结构就是二叉树。

Key-value结构，key就是索引，value就是磁盘文件地址。

对于二叉树来说有以下特点：

• 二叉树的每个结点至多有两个子树，也就是说二叉树不允许存在度大于 2 的结点；
• 二叉树有左右子树之分，次序不允许颠倒，即使是只有一棵子树也要有左右之分。

红黑树

R-B Tree，全称是Red-Black Tree，又称为“红黑树”，它一种特殊的二叉查找树。红黑树的每个节点上都有存储位表示节点的颜色，可以是红(Red)或黑(Black)。

红黑树的特性：
（1）每个节点或者是黑色，或者是红色。
（2）根节点是黑色。
（3）每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]
（4）如果一个节点是红色的，则它的子节点必须是黑色的。
（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

红黑树的基本操作是添加、删除。在对红黑树进行添加或删除之后，都会用到旋转方法。添加或删除红黑树中的节点之后，红黑树就发生了变化，可能不满足红黑树的5条性质，也就不再是一颗红黑树了，而是一颗普通的树。而通过旋转，可以使这颗树重新成为红黑树。简单点说，旋转的目的是让树保持红黑树的特性。
旋转包括两种：左旋 和 右旋。

Hash表

MySQL 的默认索引结构是 B+ 树，也可以指定索引结构为 HASH 或者 R 树等其他结构来适应不同的检索需求。
哈希表（散列表），表现为根据 {key，value}（类似字典）直接访问的一种数据结构。哈希表一般用数组来保存，其中下标是根据一个固定的函数 func1（散列函数）带入参数 key 计算的结果，value 为对应的数据。

优点：
哈希表的目的是让写入和查找时间复杂度都接近常量 O（1），所以小表做哈希性能非常好。

缺点：
要提前预判用来生成哈希表的基础表数据量，防止数据量过大，哈希表被撑大。要找到合适的哈希函数，以防哈希表碰撞太频繁。

哈希索引的实现就是建立在散列表的基础上，把索引字段当成 key，通过散列函数计算结果后，指向对应的行记录。很多时候 hash 索引要比 B+ 树索引更高效。只能支持 “=”，“in”查询，不支持范围查询。

B-Tree

B 树不要和二叉树混淆，B 树不是二叉树，而是一种自平衡树数据结构。

它是一种平衡的多分树，通常我们说 m 阶的 B 树，需满足以下条件：

• 每个节点最多只有 m 个子节点。
• 每个非叶子节点（除了根）具有至少 ⌈m/2⌉ 子节点。
• 如果根不是叶节点，则根至少有两个子节点。
• 具有 k 个子节点的非叶节点包含 k -1 个键。
• 所有叶子都出现在同一水平，没有任何信息（高度一致）。

B+树

B+树是应文件系统所需而产生的B树的变形树。
特征：

• 有m个子树的中间节点包含有m个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引；
• 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B 树的叶子节点并没有包括全部需要查找的信息)；
• 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)。

为什么说B+树比B树更适合数据库索引？

1）B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；

2）B+树查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）

B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

MySQL索引优化

1 分页查询优化

表结构：

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100004 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

select * from employees ORDER BY name limit 90000,5;

此SQL并没有使用 name 字段的索引（key 字段对应的值为 null），具体原因是扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

此SQL是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

2 Join关联查询优化

select * from t1 inner join t2 on t1.a= t2.a;

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动 表）里取出满足条件的行，然后取出两张表的结果合集。

select * from t1 inner join t2 on t1.b= t2.b;

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

优化建议：

• join表的中的字段保留需要的字段，例只查询t2表中id和name字段，其他字段不查询。
• 关联字段加索引，让mysql做join操作时尽量选择NLJ算法。
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间。

3 in和exsits优化

select * from A where id in (select id from B)

in：当B表的数据集小于A表的数据集时，in优于exists。

select * from A where exists (select 1 from B where B.id = A.id)

exists：当A表的数据集小于B表的数据集时，exists优于in。

优化总结：

1. MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
2. order by满足两种情况会使用Using index。
   1. order by语句使用索引最左前列。
   2. 使用where子句与order by子句条件列组合满足索引最左前列。
3. 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4. 如果order by的条件不在索引列上，就会产生Using filesort。
5. 能用覆盖索引尽量用覆盖索引。
6. group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。