MySQL 索引基础

这是我参与更文挑战的第4天，活动详情查看： 更文挑战

前言

MySQL 数据库中如果想要高效快速的查询数据，必不可少的一项就是创建索引，本文会介绍MySQL索引的基础知识。

索引的类型

一般只要掌握B-Tree索引和哈希索引就可以了。

B-Tree索引

InnoDB使用的是B+Tree,每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子结点的范围遍历。通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。图5-1中仅绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。数据不是按照索引值顺序存储的，无法用于排序。只包含哈希值和行指针，并不存储字段值，因此不能用索引中的值来避免读取行。哈希冲突很多时会导致索引维护操作的代价会很高。
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引 (adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要,完全可以关闭该功能。

准备工作

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
首先，我们先建一张表，加上索引

CREATE TABLE `chinese_score` (
  `exam_code` int(11) NOT NULL,
  `user_name` char(5) DEFAULT NULL,
  `class_name` int(11) DEFAULT NULL,
  `total_score` int(11) DEFAULT NULL,
  `area_ranking` int(11) DEFAULT NULL,
  `school_ranking` int(11) DEFAULT NULL,
  `class_ranking` int(11) DEFAULT NULL,
  PRIMARY KEY (`exam_code`),
  UNIQUE KEY `idx_exam_code` (`exam_code`,`class_name`,`user_name`) USING BTREE,
  KEY `idx_total_score` (`total_score`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

注意到我们的索引是加到了三行上的一个联合唯一索引和一个分数的索引

会使用索引的情况

全值匹配，匹配最左前缀，匹配列前缀，匹配范围值，精确匹配某一列并范围匹配另一列，只访问索引的查询正常情况下会使用索引来进行数据的查询。

IS NULL会使用索引

如果* col_name * 被索引，则使用col_name IS NULL的搜索将使用索引。

SELECT	* FROM	`chinese_score` WHERE	total_score IS NULL
复制代码

前提是这列里面NULL值很少！！！多的话会全表扫描的。

索引也可以用于查询中的order by 操作，如果order by 子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求，如果order by的列上没有索引，那么在拿到数据之后还要在内存里面排次序。

索引的最左原则

如果不是按照索引的最左列开始查找，则无法使用索引。
idx_exam_code这个索引是由exam_code,class_name,user_name这三列组成的
下面的这个sql

SELECT	* FROM	`chinese_score` WHERE class_name =15
复制代码

就因为它跳过exam_code这一列，所以索引没有起效果。

所以不能跳过索引中的列

范围查询右边所有列无法利用索引

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找，这时候我们应该考虑是不是要调整索引列的顺序，或者新加一个相同列但顺序不同的索引来满足不同类型的查询需求。

访问大比例行时有可能不用索引

有时，即使索引可用，MySQL 也不使用索引。发生这种情况的一种情况是，优化器估计使用索引将需要 MySQL 访问 table 中很大比例的行。
比如sys_oper_log这个表中有157条数据，其中菜单管理对应的数据量较大，有99行
那么我们执行

SELECT * FROM `sys_oper_log` where method like'com.ruoyi.project.system.controller.SysMenuController%'
复制代码

可以看到我们的idx_log索引并没有被使用，直接进行了一个全表查询，在这种情况下，table 扫描可能会更快，因为它需要更少的查找。
而当我们查询角色管理时，因为数据量小，所以就走了索引

SELECT * FROM `sys_oper_log` where method like'com.ruoyi.project.system.controller.SysRoleController%'
复制代码

但是，如果这样的查询使用LIMIT来仅检索某些行，则 MySQL 仍会使用索引，因为它可以更快地找到返回结果的几行。
这也就是为什么区分度非常小的列不建议加索引，往往会全表查询的。

总结

本文讲解了数据库索引的一些基础知识，相信帮助大家增加了些对于索引的粗浅认识。下次还将带来MySQL的其他内容。敬请期待，下篇再见！