mysql中索引的使用

大家好，我们知道创建索引是要付出时间和空间的代价的，只有合理的创建索引才能提高查询效率。今天我们来聊一聊如何优雅的，合理的使用索引。

上篇文章我们讲到了在索引在where查询过程中是如何发挥作用的，今天我们再谈谈索引在排序和分组的时候是如何使用的。

为了方便讲解，我们依然建立一个test_table表并创建了一个聚簇索引（id）、三个二级索引（c1、c2、（c4、c5）），其中c4和c5为联合索引。注意：c3列没有创建索引

CREATE TABLE `javaTuT`.`test_table`  (
  `id` int NOT NULL,
  `c1` varchar(100) NULL,
  `c2` int NULL,
  `c3` varchar(100) NULL,
  `c4` varchar(100) NULL,
  `c5` varchar(100) NULL,
  PRIMARY KEY (`id`),
  INDEX `idx1`(`c1`),
  INDEX `idx2`(`c2`),
  INDEX `idx3`(`c4`, `c5`)
);

索引用于排序

平时我们写列表查询接口的时候会经常要使用order by语句来对查询出来的记录进行排序，一般情况下，我们只能是把记录加载到内存，然后再通过排序算法来对内存中的记录进行排序。当查询的结果很多时，可能内存无法进行排序，只能借助磁盘的空间来存放中间结果，最终排序操作结束后将结果集传给客户端。

mysql将上述这种在内存和磁盘中进行排序的方式称之为文件排序（filesort）。我们知道当我们创建索引列时会生成一个B+树，树的叶子节点中索引列是按照从小到大的顺序进行排列的。所以，当通过索引列进行排序时，直接从扫描区间的第一条索引记录往后取即可，无需再进行排序，例如：

select * from test_table order by c4, c5 limit 10

这条sql执行时是先按照c4列进行排序，当c4列相同时再根据c5列进行排序，然后取结果的前十条。但是我们创建了c4列和c5列的联合索引，正好直接取索引的前十条数据，然后再进行回表操作即可。

在使用索引进行排序时要注意以下几点：

1. order by子句后面的列顺序必须要和索引列的顺序一致。

select * from test_table order by c5, c4 limit 10

这条sql就无法使用索引。原因很简单，我们创建的索引是（c4，c5），索引中记录是先按照c4列进行排序，当c4列相同时再根据c5列进行排序。而sql中是先按照c5列进行排序，当c5列相同时再根据c4列进行排序。

2. 当仅对联合索引的左边的列进行排序，或者索引左边的列为常量时也可以对右边的列进行排序。

select * from test_table order by c4 limit 10
select * from test_table where c4 = 'a' order by c5 limit 10

索引是先按照c4列进行排序，当c4列相同时再根据c5列进行排序。第一条sql只对c4列进行排序，索引也是先对c4列进行排序，第二条sql虽然只对c5列进行排序，但是c4列是常量，在索引中 c4 = ‘a’ 的记录c5一定是排序过的，所以上边这两条sql是可以使用索引的。

3. sql中ASC（升序），DESC（降序）不能混用

select * from test_table order by c4，c5 desc limit 10

如果这条sql要使用索引进行排序，过程如下所示：
先找到c4列的最小值（min_value），然后再从c4列等于最小值（min_value）的最后一条记录开始向左找10条记录。那么如果c4列等于最小值（min_value）的记录不够10条，我们假设有4条时呢，我们就要找c4列等于最小值（min_value）的最后一条记录的下一条记录，假设下一条记录值为min_value2，那么要再从c4列等于min_value2的最后一条记录往左找6条记录，那如果c4列等于min_value2的记录也不够6条呢？周而复始的往下找，这种情况是不能高效的使用索引的。（mysql 8.0 版本引入了Descending Index 的特性，可以支持asc和desc混用，具体大家可以自己搜索一下）

4. sql排序中不能包含非同一个索引的列

select * from test_table order by c1，c2 desc limit 10

我们在创建表的时候c1列和c2列为两个不同的二级索引，它们互不影响。当使用c1列的索引进行排序时，无法对c2列进行排序，当使用c2列的索引进行排序时，无法对c1列进行排序。所以这条sql无法使用索引进行排序。

5. 用来形成扫描区间的索引列与排序列不同时，无法使用索引进行排序

select * from test_table where c1 = 'a' order by c2 limit 10

这条sql会根据c1列索引值生成扫描区间[‘a’，‘a’]，因此无法再去使用c2列的索引进行排序。

7. 排序列必须要以单独的列名的形式出现在order by 子句中

select * from test_table order by upper(c1) limit 10

这条sql中c1列是以upper(c1)的形式出现在order by 子句中的，所以无法使用索引进行排序。

索引用于分组

我们在开发的过程中也会经常用到group by语句对数据进行分组，例如：

select c4, c5 from test_table group by c4, c5

这条sql在执行时，会先将c4列进行分组，然后再在c4列分好的每一组中再对c5列进行分组，我们会发现，这刚好又是和我们创建的联合索引中的记录顺序是一样的，所以分组时也可以使用创建的索引，索引用于分组的注意事项和上述用于排序的注意事项相同，在这里就不一一赘述了。

我们之前在讲索引的时候提到过InnoDB索引中，除了聚簇索引外，其他类型的索引中的记录都是只存储索列和主键，在查到索引记录后还要进行回表操作，那么我们要考虑一下下面这个问题：

select * from test_table where c1 > 'a' and c1 < 'c'

当我们以全表扫描的方式执行这条sql时，会直接扫描聚簇索引记录，然后针对每一条聚簇索引记录都判断搜索条件是否成立，成立就发送到客户端，不成立就跳过。

当我们用c1列的索引来执行查询时，会先确定c1列扫描区间为（‘a’,‘c’）,然后扫描该扫描区间中的二级索引，当条件满足时，再取索引记录中的主键去通过聚簇索引查询出完整的数据，也就是回表操作。

一般情况下肯定是通过c1列索引去查询会快，但是我们思考一下极端的情况，表中所有数据的c1列都在（‘a’,‘c’）范围内，这种情况下，如果通过c1列索引去查，需要每条索引记录都进行一次回表操作，注意：索引记录是按照索引列进行排序的，主键大概率情况下是不连续的。这肯定是没有全表扫描查的快。

那么在执行查询的时候，什么时候使用二级索引+回表操作，什么时候使用全表扫描呢？这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高。

为了减少回表的次数，我们可以通过limit子句来限制查询返回的记录数，这也是为什么我们在开发过程中经常进行分页查询的原因。

最后，我们来说一下如何更好的创建和使用索引：

1. 只为用于搜索、排序或分组的列创建索引

我们只为出现在WHERE 子句中的列、连接子句中的连接列，或者出现在ORDER BY 或 GROUP BY 子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了。

select c2， c3 from test_table where c1 > 'a' and c1 < 'c'

拿这条sql来说，我们只对c1列创建索引即可，无需再对c2和c3列创建索引。

2. 考虑列中不重复数据的个数

上边我们已经讨论过，如果列中重复的数据很多，在通过二级索引+回表方式查询时，可能会进行大量的回表操作，这个时候不如通过全表扫描的方式进行查询，这个列就不适合创建索引。

3. 索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT 、INT 、BIGINT这么几种，它们占用的存储空间依次递增，能表示的整数范围也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT 就不要使用 INT。

这是因为：数据类型越小，在查询时进行的比较操作越快，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带 来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

4. 为列前缀创建索引

在创建索引时，我们也可以只对列的前缀创建索引，这和上述索引列的类型尽可能小原理一致。 例如我们可以只对c1列的前5位创建索引:

select * from test_table where c1 = 'abcdefghijklmn'

在查询时，会先定位到前缀为’abcde’的二级索引记录，然后再取这些记录中满足查询条件的记录进行回表操作。

注意，根据前缀创建的索引不适用于根据索引列进行排序的查询

5. 覆盖索引

在查询过程中，我们可以通过只查询索引列数据的方式来减少回表次数。例如下面这条sql，

select c1 from test_table where c1 > 'a' and c1 < 'c'

因为c1列的索引记录中是存在c1列的真实记录的，所以就无需再进行回表操作。这种索引中已经包含所有需要读取到的列的查询方式称之为覆盖索引，排序操作也优先使用覆盖索引。

6. 让索引列在比较表达式中单独出现

select * from test_table where c2 > 10
select * from test_table where c2 * 2 > 10

上述两条sql中，第二条sql中的c2列并不是以单独列的形式出现的，而是以c2 * 2这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是大于10，这种情况下是使用不到为c2列建立的B+树索引的。所以如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

7. 主键插入顺序

我们知道，对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，当一个数据页已经满了时，再插入主键就需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的页中。我们要尽量避免因为页面分裂和记录移位而产生的性能损耗。所以最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。

8. 冗余和重复索引

拿test_table表来说，我们已经为c4列和c5列创建了联合索引，这个时候如果再单独为c4列创建一个索引是没有必要的，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。所以我们在创建索引时要避免创建重复的索引。

好了，到此索引的使用就介绍完毕了，有什么问题欢迎大家在评论区进行讨论。请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！