第06章 MySQL索引

SQL中的查询操作非常重要，它也是修改和删除操作的前提，只有找到记录数据，才能修改它。所以，如何提升查询速度就是我们需要关注的问题。这个本质上取决于数据的物理存储，也就是数据结构的范畴。关系型数据的逻辑存储（类似二维表格）只是方便我们对数据进行操作（SQL语句就是在二维表格的逻辑结构上进行操作）。但是，数据的真正查询，修改，删除操作还是要到物理存储上进行（数据还是以文件的形式存储在硬盘上）。那么，数据库以何种物理方式来存储数据，来保证我们不管何种方式的查询都能提供高效的性能呢？

举个实际的例子，我们将一些学生按照身高（从低到高）排列在一起。那么，当我们寻找某一个身高的学生时候，这种排列方式对我们的寻找就非常的方便。但是，如果我们想要寻找某个学习成绩的学生的时候，我们不得不查询所有学生的成绩。在数据库查询中，最糟糕的情况就是需要查询表中所有的记录数据。显然，为了加快按照成绩寻找学生的方式，我们需要将学生按照学习成绩进行重新排列。当然，我们不可能将学生数据在重新复制一份，然后按照成绩进行重新排列存储一遍，这样数据冗余太多了。为了减少数据冗余，我们可以仅仅将学生的位置信息（主键ID）和学习成绩复制一份，并按照学习成绩进行排列存储。这样，当我们想要按学习成绩查询的时候，就可以从这份数据中寻找，找到后在根据位置信息（主键ID）在去主存储数据中提取学生的完整信息。虽然，这样看似比较麻烦，但却是快一些。那么，这个按照学生成绩进行排列存储的方式，我们就称之为“索引”。更加简单的理解，就是我们给学生的“成绩”字段创建索引，然后再根据成绩查询的时候，速度就会快一些。但是，这样做有一个缺点，就是我们加入新的学生成绩的时候，需要同步更新这个索引数据。

那么，MySQL的索引是一种什么样的数据结构呢？这个跟我们使用哪种存储引擎有很大关系。在我们使用“CREATE TABLE”语句创建表结构的时候，会使用“ENGINE=InnoDB”来指定使用哪种存储引擎。MySQL数据库常用的存储引擎为InnoDB和MyISAM，当然还有其他存储引擎。MySQL中常用的索引结构为B+树。在我们之前的章节中“第二十章 数据结构”介绍过数据结构。

https://blog.csdn.net/richieandndsc/article/details/125289725

在这个章节中，我们介绍了“树”的存储结构，并且简单介绍了“平衡二叉树”。 平衡二叉树就是左右两个子树的高度差的绝对值不超过1。平衡二叉树支持快速插入、删除、查找操作，各个操作的时间复杂度跟树的高度成正比，理想情况下，时间复杂度是 O(logn)。

平衡二叉树在插入删除的时候平衡二叉树的平衡可能被改变。这个时候我们就需要通过“旋转”的方式将其重新变成一个平衡二叉树。这样验证了我们上面说的，索引的缺点就是新增或删除记录数据的时候，需要同步更新索引，这也是空间换时间（查询快）的概念。

我们可以将学生的“身高”或“成绩”作为“树节点”进行存储，同时还可以附带存储学生的全部信息（姓名，年龄，班级等等）。这样，当我们需要按照“身高”或“成绩”进行学生信息检索的时候，就可以使用平衡二叉树的查询优势加快数据的检索。MySQL的数据是存储在磁盘文件中的，在查询数据的时候，需要先把磁盘中的数据加载到内存中。众所周知，磁盘的读写（IO）操作非常耗时，所以我们应该尽量减少磁盘的读写（IO）操作。访问二叉树的每个节点都会发生磁盘读写（IO），如果想要减少磁盘读写（IO）的话，我们就需要尽量降低树的高度。只有这样，我们从树的根节点开始向下查找的时候，就会更快的找到目标数据。一个解决方法就是在每个树节点处存储多个记录数据，也就是通过增加树的分叉，将树的体型从“高瘦”变成了“矮胖”。这种数据结构我们称之为B树，B树是一种多叉平衡查找树。当然，这些树节点存储的多个记录数据，也是按照“索引顺序”进行存储的。相比较二叉平衡查找树，B树在整个查找过程中，虽然数据的比较次数并没有减少（我们不仅要对树节点进行比较查找，还需要在树节点存储的多个记录数据之间也要进行比较查找），但是对于磁盘读写（IO）的次数会大大减少。同时，由于我们是在内存中进行的数据比较操作，所以比较数据所消耗的时间是非常短暂的。 B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。MySQL在B树的基础上继续进行改造B+树。B+树和B树最主要的区别在于叶子节点（树末端节点）是否存储记录数据的问题。B树的所有树节点都会存储数据，而B+树只有叶子节点（树末端节点）才会存储记录数据（学生的全部信息，姓名，年龄，班级等等），非叶子节点只存储索引数据（“身高”或“成绩”）。为什么要这么做呢？因为树节点的存储空间是有限的，要想存储更多的索引数据（“身高”或“成绩”），就不能存储太多的记录数据（学生的全部信息，姓名，年龄，班级等等）。否则，我们就需要增加树的高度来存储更多的索引数据，这明显违背了我们优化平衡二叉树的原则。

接下来，我们就来介绍一下MySQL索引有哪些分类。

如果从物理结构来讲，分为聚簇索引和非聚簇索引两种。

聚簇索引就是将记录数据存储与索引数据放到了一起，找到索引也就找到了数据，这种存储方式是依靠B+树来实现的。非聚簇索引则相反，它的叶子节点不存储记录数据。因此，非聚簇索引查询数据需要先查到聚簇索引的主键值，然后用这个主键值去查询真正的数据 (这个过程称为回表)。也就是说非聚簇索引是需要查询两次。在MySQL中，InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键（primary key）索引才是聚簇索引（每张表最多只能拥有一个聚簇索引），InnoDB中的其他索引以及MyISAM使用的都是非聚簇索引。聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。聚簇索引对于主键的排序查找和范围查找速度非常快。对于非聚簇索引，在数据量比较大的时候，回表必然会消耗很多的时间影响性能，所以我们要尽量避免回表的发生。覆盖索引就可以解决这个问题。覆盖索引并不是一种索引结构，它只是一种优化手段，就是将查询的字段做成索引。例如，我们查询某表的a、b、c这三个字段，那我们创建包含a，b，c三字段的组合索引。那么，我们查询到叶子节点（里面存储的就是a，b，c三字段）时就可以直接返回了，而不需要再次回表查询，这种情况就是覆盖索引。

MySQL 的索引按照逻辑划分的话，可以分为以下几类。
第一种分类：普通索引和唯一索引，区别在于索引字段值是否唯一（不能重复）。
第二种分类：单列索引和多列索引（也叫组合索引）。

在创建表时，除了定义列的数据类型以外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，都相当于在指定列上创建了一个索引。

创建索引语法：create index 索引名 on 表名(列名1, 列名2, ……);

例如：create index `exam_score` on `exam_info`(`exam_score`);

上面创建了一个单列普通索引，因为考试成绩可能会重复。

接下来，我们在使用`exam_score`进行查询的时候，就会使用这个索引。

例如：select `stu_id`,`exam_score` from `exam_info` where `exam_score` > 90;

备注：创建索引的一个最简单原则就是，在Where条件中使用的字段创建索引。

删除索引语法：drop index 索引名 on 表名;

例如：drop index `exam_score` on `exam_info`;

接下来是其他索引创建语法：

创建唯一索引语法：create unique index索引名 on 表名(字段1, 字段2, ……);
创建组合索引：create index 索引名 ON 表名(字段1, 字段2, ……);
创建唯一组合索引：create unique index索引名ON表名(字段1, 字段2, ……);

以上语法就非常简单了，我们不详细介绍了。索引一旦建立，就由数据库系统维护它，不需要用户干预。建立索引是为了减少查询操作的事件，但如果数据增加或删除频繁，系统会花费大量来更新索引数据。索引能够提高查询速度的原因就是使用空间换时间的概念。

虽然使用索引技术可以提高数据查询的速度。但另一个方面，增加了数据插入，删除和修改的复杂性，以及维护索引的时间开销。因此，是否使用索引，对那些表字段建立索引，数据库设计人员必须全面考虑。以下是一些建议：

1. 对于小表（十几万条记录以内）来说，不需要使用索引。

2. 索引列中有较多的不同的数据时，索引是的查询性能有极大的提高。相反，对于性别字段建立索引，效率就非常的差。

3. 当查询要返回的数据很少时候，索引可以优化查询，如果要返回的数据很多，索引会加大系统的开销。建议需要什么字段数据，就查询什么字段数据，减少使用*通配符。

4. 索引可以提高数据的查询速度，但是它使得数据的更新变慢。如果需要进行大量数据的更新操作时候，可以删除索引，更新完毕后，在恢复索引。

5. 不要对经常需要更新或修改的字段建立索引，因为他们会频繁更新索引。对于海量数据的索引更新时间，是非常长的（可能1-2小时，甚至3-4小时，甚至更长……）。

6. 索引主要建立在数值类型的字段上面，字符串类型次之，而Text或Blob不应该建立索引。

如何验证我们的SQL语句真正的使用到了索引呢？

SQL预计分析 explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。例如，我们之前的查询语句：

explain select `stu_id`,`exam_score` from `exam_info` where `exam_score` > 90;

注意上面的“type”一列显示为“ALL”，表示全表扫描，也就是检索表中所有记录数据，然后对比发现分数大于90的记录。接下来，我们增加索引：

create index `exam_score` on `exam_info`(`exam_score`);

我们注意到“type”一列变成了“range”范围检索，并且“possible_keys”和“key”两列都显示了我们的索引名称“exam_info”。这说明，我们创建的索引起作用了。

接下来，我们介绍上面expain出来的信息有12列，分别是id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

1. id选择标识符，代表sql语句执行的顺序。如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。因为要先执行子查询，在执行父查询。

2. select_type表示查询的类型：普通查询(simple), 联合查询(union), 子查询等等。

3. table表示查询的表。

4. partitions表示用到的表分区，一般情况下，只有设置了分区后，该列才会使用。

5. type表示MySQL在表中找到所需行的方式，通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为：

null > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all

null：表示 MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如，EXPLAIN SELECT 300; 语句就是这种情况，基本很少使用的。

system：表只有一行记录，出现的情况较少。

const：表示该表最多有一个匹配记录。通常情况下是主键索引或唯一索引情况下的查询。例如，EXPLAIN SELECT * FROM class_info WHERE class_id = 1; 就是此类型的查询。

eq_ref：表示主键索引或唯一索引的所有部分被连接使用，最多只会返回一条符合条件的记录。一般eq_ef 通常出现在连表的情况下，而 const 通常出现在单表情况下。

ref：表示使用了非唯一索引扫描。与 const 非常类似，只不过 ref 会匹配到多个记录，而 const 则只会匹配到单个记录。

ref_or_null：类似ref，但是可以检索字段为NULL的记录，也就是增加了is null的条件判断。

index_merge：表示使用了索引合并的优化方法。索引合并指的是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。

range：表示检索给定范围的记录，通常条件判断出现 between、<>、in 等操作符。

index：表示只遍历索引树，且可以从索引树中获取数据。相当于覆盖索引优化的情况。

all：表示该查询将遍历全表以找到匹配行，这是最糟糕的一种查询方式。

6. possible_keys表示可能使用的索引

7. key表示实际使用的索引

8. key_len表示索引字段的长度，通过这个值可以算出具体使用了索引中的哪些字段。

9. ref显示使用哪个列与key一起从表中筛选数据

10.rows估算扫描记录的行数，当然是越少越好

11. filtered它指返回结果的行占需要读到的行(rows列的值)的百分比。

12. Extra执行情况的描述和说明，这里需要注意Using filesort和Using temporary。

Using filesort：MySQL 中无法利用索引完成的排序操作称为文件排序。一般情况下，是因为没有给排序字段建立索引，所以在根据该字段排序的时候，他就使用了外部排序。

Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般还是需要使用索引来进行优化。这样的查询效率是比文件排序更低的，常见于order by和group by操作。

接下来，我们分析一个连表查询语句：

explain select e.stu_id, s.stu_name, e.exam_score from `exam_info` as e join `student_info` as s on e.stu_id = s.stu_id where e.sub_id = 1;

由于是两个表的查询，因此explain给出了两条解析记录。由上图可知，学生成绩表的查询类型为“index”，使用到了“exam_score”索引。我们查询学生表的时候，是根据学生主键进行关联的，因此学生表的查询类型是“eq_ref”，使用到了主键索引。

Explain中优化索引心得总结

1. 左前缀保留原则。如果索引了多个字段，查询从索引的最左端字段开始，并且不会跳过索引中的字段；

explain select * from `exam_info` where `sub_id` = 1;						// 全表扫描

explain select * from `exam_info` where `stu_id` > 0 and `sub_id` = 1;		// 范围扫描

我们的`exam_info`表中的索引是`stu_id`,`sub_id`,`type_id`三个，并且顺序不能改变。如果我们只是单纯使用第二项`sub_id`进行查询的话，是不会使用索引的。此时，我们在原来条件前面增加一个`stu_id` > 0的话，既不会改变原SQL查询含义，还能使用上索引查询，避免全表扫描。这就是左前缀保留原则，是按照索引定义的顺序而定，一般用于组合索引情况。

2. 不在索引列上做任何操作比如计算、函数等等

explain select * from `exam_info` where `stu_id` = 1;			// type = ref

explain select * from `exam_info` where (`stu_id` + 1) = 1;		// type = all

3. 不能在索引列上进行范围操作

explain select * from `exam_info` where `exam_score` = 90;	// type = ref

explain select * from `exam_info` where `exam_score` > 90;	// type = all

我们之前给`exam_score`增加了一个索引。

4. 使用like的时候，不能将通配符%开头。

create index `stu_name` on `student_info`(`stu_name`);		// 创建索引

explain select `stu_id`, `stu_name` from `student_info` where `stu_name`='小王';		// ref

explain select `stu_id`, `stu_name` from `student_info` where `stu_name` like '小%';		// index

explain select `stu_id`, `stu_name` from `student_info` where `stu_name` like '%小%';	// index

一般情况下，我们给字符串类型创建索引的时候，使用like模糊匹配查询的时候，不能将通配符%开头。虽然显示为“index”方式，但实际不会用到`stu_name`索引。

5. 减少使用select *，提高索引效率；

explain select `stu_id`,`exam_score` from `exam_info` where `exam_score` > 90;	// range

explain select * from `exam_info` where `exam_score` > 90;	// all

我们发现换成通配符*号之后，就可能用不到索引了。因此，通配符*号尽量少使用。

6. 使用不等于判断时候，有时让索引会失效

explain select * from `exam_info` where `exam_score` != 90;			// all

7. 有时候or也会让索引会失效

explain select * from `exam_info` where `stu_id` > 0 and `sub_id` = 1;		// range

explain select * from `exam_info` where `stu_id` > 0 or `sub_id` = 1; 		// all

8. order by 对主键索引排序会用到索引，其他的索引失效

explain select stu_id, stu_name from student_info where stu_id > 4 order by stu_age desc;				// range

explain select stu_id, stu_name from student_info where stu_name like '小%' order by stu_age desc;		// all