MySQL索引分析

索引的出现解决数据量上升导致查询越来越慢的问题，优化数据的查询，提高查询的速度。

索引

定义：
通过各种数据结构实现的值到行位置的映射。快速定位与访问特定的数据。

作用：

• 提高访问速度
• 实现主键、唯一键逻辑

数据结构：

• Btree索引：实际上是B+ tree，绝大部分RDBMS最主要的索引
• Hash索引：主要用于InnoDB一些内存索引结构，以及Memory存储引擎
• Rtree索引：地理位置检索，MyISAM引擎
• Fulltext索引：MyISAM引擎
• Bitmap索引：原生MySQL目前不支持

B+/-Tree原理及mysql的索引分析

B树

即二叉搜索树：

   1.所有非叶子结点至多拥有两个儿子（Left和Right）；

   2.所有结点存储一个关键字；

   3.非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

实际使用的B树都是在原B树的基础上加上平衡算法，即“平衡二叉树”；如何保持B树结点分布均匀的平衡算法是平衡二叉树的关键；平衡算法是一种在B树中插入和删除结点的策略；

B-树

B-Tree是一种多路搜索树（并不是二叉的）：
1.定义任意非叶子结点最多只有M个儿子；且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]；
4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8.所有叶子结点位于同一层；

如：（M=3）

B-树的特性：
1.关键字集合分布在整颗树中；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束；
4.其搜索性能等价于在关键字全集内做一次二分查找；
5.自动层次控制；

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+Tree

B+树是B-树的变体，也是一种多路搜索树：
1.其定义基本与B-树同，除了：
2.非叶子结点的子树指针与关键字个数相同；
3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
5.为所有叶子结点增加一个链指针；
6.所有关键字都在叶子结点出现；
如：（M=3）

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
B+的特性：
1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2.不可能在非叶子结点命中；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4.更适合文件索引系统；

聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)

聚簇索引的顺序就是数据的物理存储顺序
所谓聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引。

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
非聚簇索引的索引顺序与数据物理排列顺序无关。

动作	使用聚簇索引	使用非聚簇索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

explain命令

在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN去完成。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。需要注意的是，生成的QEP并不确定，它可能会根据很多因素发生改变。MySQL不会将一个QEP和某个给定查询绑定，QEP将由SQL语句每次执行时的实际情况确定，即便使用存储过程也是如此。尽管在存储过程中SQL语句都是预先解析过的，但QEP仍然会在每次调用存储过程的时候才被确定。

1. id
   包含一组数字，表示查询中执行select子句或操作表的顺序。id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2. select_type
   示查询中每个select子句的类型（简单OR复杂） a. SIMPLE：查询中不包含子查询或者UNION b. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY c. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY d. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的 e. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED f. 从UNION表获取结果的SELECT被标记为：UNION RESULT SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。 DEPENDENT意味着select依赖于外层查询中发现的数据。 UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

3. type
   表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下: ALL, index, range, ref, eq_ref, const, system, NULL 从左到右，性能从最差到最好

4. possible_keys
   指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

5. key
   显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

6. key_len
   表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

7. ref
   表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

8. rows
   表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

9. Extra 包含不适合在其他列中显示但十分重要的额外信息 a. Using index 该值表示相应的select操作中使用了覆盖索引（Covering Index）

其他

分库分表

联合索引

where函数优化

区分度过低

不建议建索引

外键建索引

不建索引，性能急剧下降

MySQL优化器

SSD索引性能优化

取决于并发量和访问量，在软件上优化效果比在硬件上优化效果更好

分区

查询最频繁的字段进行拆分

纵向分表和横向分表

连表查询

业务量大，尽量避免

参考资料

EXPLAIN 命令详解