关于MYSQL的索引知识

阳光穿透心脏的1/2处 2023-07-18 09:29 9阅读 0赞

**目录**

一、什么是索引

二、索引为什么快

三、数据结构比较

四、聚簇索引/非聚簇索引

五、覆盖索引

六、唯一索引/普通索引

七、Full-index全文索引

八、单列索引/联合索引

九、下推索引

十、其他知识

--------------------

### 一、什么是索引 ###

索引在MYSQL中也叫做 “键（key）”，是存储引擎用于快速找到记录的一个数据结构。要理解MYSQL中索引是如何工作的，最简单的方法就是去看一本书的“索引”部分，如果想在一本书中找到某个特定的主题，一般会看书的“索引”，找到对应的页码。把表当作一本书的话，索引就相当于这本书的目录，建立索引就是创建目录，如果这本书的内容不是很多，就完全没有必要去建立目录--索引，因为索引它也是需要空间去存放的。

## ##

### 二、索引为什么快 ###

索引的本质就是以空间price换时间times

\+times 加快检索的数据，加快多表连接

\-price 额外空间开销，维护索引的额外时间开销

所以我们通过索引这个**缓存**来提高数据查询的效率，假如我们自己设计数据库索引的话，我们会选取什么样的数据结构呢？我们来做以下的分析，再来选择。

### 三、数据结构比较 ###

有序数组：等值查询和范围查询场景中的性能就都很优秀。**特定值查询**就用**二分法**就可以快速得到结果，这个的时间复杂度为 O(log(N))。类似between\[x,y\]的**范围查询**也比较快，先用值查询二分法找到x，然后向后遍历，直到找到y。但是他最大的问题就是插入或者删除一个新数据，这个新数据后面的整个数组都需要挪动，时间复杂度为 O(N)。

HashMap：虽然可以快速定位，**特定值查询**查询的时间复杂度为O(1)，但是HashMap是没有顺序的，进行**范围查询**的话复杂度O(N)。

二叉查找树（BST）：二叉查找树的高度不均衡，不能自平衡，查找效率跟数据量有关（树的高度），在极端情况下（插入数据本身就是有序的），这棵树就退化成了链表结构，查询时间复杂度为O(N)。

红黑树：是平衡的BST，性能稳定在O(log(N))，但因为其还是二叉树，树的高度随着数据量的增加而增加，并且需要再平衡，适合数据都在内存的情况下，比如java的HashMap，但是在硬盘寻址的场景下，IO成本会比较高。

B-Tree：相比二叉树来说，是一种多路平衡查询树，但是B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量的数据，只能增加树的高度，导致IO操作变多，查询性能变低。

B+Tree：从屋里存储结构上说是N叉树，B-Tree和B+Tree都以页（4K）来划分节点的大小，但是由于B+Tree的中间节点（非叶子节点）不存储数据，存的是索引信息，所以包含Key和Point指针，因此B+Tree能够在同样大小的节点中，存储更多的key，提高查找效率。

每一个索引在 InnoDB 里面对应一颗B+Tree。以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存![1200^\{4-1\}][1200_4-1]的数据。这已经是17亿了，，考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。

### 四、聚簇索引/非聚簇索引 ###

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。这两者的区别主要是看叶子节点存了什么数据，在InnoDB中，索引B+Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，因为它存储了这一行的所有数据（包括主键字段的值和其他字段的值），所以就叫**聚簇**。而索引B+Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

聚簇索引查询相对会快一些，因为主键索引树的叶子节点直接就是我们要查询的整行数据了，而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询（这个过程就叫做 **回表** ，也就是查了2个索引树）。

### 五、覆盖索引 ###

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。覆盖索引不是索引树，是一个结果，当一条查询语句符合覆盖索引条件时，MYSQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O，提高效率。

例如表student中有一个普通索引 idx\_key（student\_code：学号字段），那么：

\--索引覆盖了,为什么会覆盖？即意思是说这个普通索引覆盖了非聚簇索引，非聚簇索引的叶子节点存的是id。

select id from student where student_code = '00001';

\--索引没有覆盖，需要回表。第一次用索引查询出id，再用id进行了再一次查询。

select * from student where student_code = '00001';

### 六、唯一索引/普通索引 ###

唯一索引和普通索引在查询和更新时候的区别：

1、唯一索引找到满足的第一条记录会立马返回，通知检索（因为唯一性的保证）。但是这个区别并没有很大的性能区别，因为InnoDB是按照页（默认16kb）读写的。读数据的时候是从B+树的根节点开始搜索，搜索的时候将整个页从硬盘加载到内存。

2、唯一索引在插入的时候回多做些判断，想要做这个判断就必须先把数据页读入内存，但是普通索引不需要做这个判断操作，就可以把需要更新的数据做判断。如果数据在内存，则直接更新；如果不在也不加载内存，而是先写入change buffer（[change buffer的含义][change buffer]）。等下次查询的时候再执行change buffer，这样普通索引会相对性能好一些，但是注意，如果业务场景是写入后立马又查询，其实还是会立马需要把数据页加载到内存，这样的情况下其实并不能带来优化IO的操作。

### 七、Full-index全文索引 ###

mysql5.6中引入了全文索引 Full text index 。但是只能适用于分词的情况，如果是匹配字符串的一部分就不适用了。

MYSQL支持三种模式的全文检索模式，

1、自然语言模式（IN NATURAL LANGUAGE MODE）：通过MATCH AGAINST（[MATCH AGAINST的含义][MATCH AGAINST]）传递某个特定的字符串来进行检索；

2、布尔模式（IN BOOLEAN MODE）：可以为检索的字符串增加操作符，例如 “+” 表示必须包含，“-” 表示不包含，“\*”表示通配符（此类情况，即时传递的字符串较小或出现在停词中，也不会被过滤掉），其他还有很多特殊的布尔操作符，可以通过参数控制。

3、查询拓展模式（WITH QUERY EXPANSION）：这种模式是自然语言模式的一个变种，会执行两次检索，第一次使用给定的短语进行检索，第二次是结合第一次相关性比较高的行进行检索。

### 八、单列索引/联合索引 ###

对于一个表里的多个列，特别是有些列要高频查询，有些列只是低频查询。如果为每一个低频的列单独建立索引就会造成资源浪费，如果不建立的话，又只能走全表扫描。所以我们经常用**联合索引**来解决这个问题。

联合索引，如：idx\_key1\_key2\_key3(key1,key2,key3) 相当于创建了 （key1）、（key2,key3）和（key1,key2,key3）三个索引，那么在建立联合索引的时候，可以这个来安排其字段顺序：

1、如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用按照字段在查询条件中出现的频率来建立索引。

2、由于考虑到key1是最常用的列是放最前面，key2和key3不常用，上面这种建立一个联合索引就实际上包含了3个索引的特性就是**最左匹配原则**，这个最左匹配可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。总结起来就是：

1）索引的匹配规则是**左匹配**的；

2）只有符合索引的第一个字段出现在查询条件中，该索引才可能被使用；

3）有了（A,B,C），就等于同时拥有了（A），（A，B）和（A，B，C）三个索引；

4）只要在索引内，看是用范围查询，后面的索引就失效了。（注意：如果 in 在 where 中，也属于准确查询，不会使后面的索引失效）

### 九、下推索引 ###

在Mysql5.6中，引入了Index condition Pushdown Optimization（[下推索引的含义][Link 1]）优化，本质是针对那些需要回表查找的部分，如果索引里已经包含了该列，那么先在索引里做过滤判断，以 user 表的联合索引(name，age)为例，如果现在有一个需求，检索出表中 名字第一个字是张，而且 年龄是10岁的所有男孩 。那么，sql语句是这么写的：

select * from user where name like '张%' and age = 10 and sex='man';

我们已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用“张”，找到第一个满足条件的记录（id=3），然后，这总比全表扫描要好。然后接着判断其他条件是否满足。在MYSQL5.6之前，只能从id=3开始一个个回表。到主键索引上找出数据行，再对比字段值。而从5.6引入的索引下推优化（Index condition Pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

### 十、其他知识 ###

> 1.哪些字段适合建立索引？

*  出现在 select、update、delete 语句中的 where 从句中的列
 *  包含在 order by 、group by 、distinct 中的字段
 *  并不要将符合 1 和 2 中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好
 *  多表join的关联连

> 2.为什么有些SQL不走索引

*  使用了通配符开头，NOT IN 语句
 *  联合索引的第一个字段查询条件中
 *  数据引擎的优化器选错了索引（可以适当使用 force index 语句来[优化][Link 2]）

> 3.为什么一般主键索引最好是自增的，尽量短的数值类型

*  自增长

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。由于InnoDB的索引的特性，因此如果主索引不是自增的（id是主键），那么每次插入新的数据，都很可能对B+Tree的主索引进行重整，影响性能。因此，尽量以自增id作为InnoDB的主索引。

*  短数

每个非主键索引的叶子节点上都是主键的值。如果用UUID，比如 ed3c76e6-9370-40b9-941e-e68ffd85dab5 做主键，那么每个二级索引的叶子节点占用约36字节，而如果用整型做主键，则只要4字节，如果是长整型（bigint）则是8字节，所以，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。利用雪花算法来尽量做到生成 **短数字** 且 **趋势自增** 的ID。

> 4.索引的最佳实践

*  定义 主键的数据列 或 有外键的数据列 一定要建立索引
 *  对于 经常查询的数据列 最好建立索引
 *  对于需要在指定范围内的快速或频繁查询的数据列
 *  经常用在 where 字句中的数据列
 *  经常出现在关键字 order by ,group by,distinct 后面的字段，建立索引，比如一个表中的 create\_time 字段。

如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

> 5.不要建索引

*  对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
 *  对于定义为text，image 和 bit 的数据类型的列不要建立索引。因为这些数据类型本来就大，放在叶子节点的话，占用空间太大。
 *  对于经常存取的列避免建立索引

> 6.索引的坑

*  限制表上的索引数目，对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多的索引会影响数据的更新操作。就例如一本书的目录太多了，影响整个书的修订。
 *  对复合索引，按照字段在查询条件中出现的频率建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此 只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用，因此将应用频率高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

> 7.索引不会包含有NULL值的列

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，要建立索引的字段，就不要让字段的默认值为NULL。

> 8.使用短索引（列内容越短越好）

对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度，而且可以节省磁盘空间和I/O操作。

> 9.索引列排序

MySQL查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序。如果需要，则最好给这些列创建符合索引。

> 10.LIKE 语句操作

一般情况下，不鼓励使用 like 操作，如果非使用不可，如果使用也是一个问题。 想 like '%aaa%'  不会使用索引，而 like 'aaa%' 可以使用索引。即为左匹配规则。可以使用reverse函数来支持逆序匹配，从而增强 like 走索引的可能。

ALTER TABLE 'user' ADD 'reverse_identifier' VARCHAR(255) CHARCTER SET utf8 COLLATE utf8_general_ci;
    
    select * from user where reverse_identifiter like reverse('%SDTE');

> 11.不要在列上进行运算

select * from users where YEAR(add_date) < 2020;

这个sql语句将在每个行上进行运算，这将导致索引失效而进行全表扫描，因为我们可以改成

select * from users where add_date < '2020-01-01';

> 12.不使用 NOT IN 和 < > 操作

因为MySQL只对<, <= , = , > , >= , BETWEEN , IN 以及某些时候的LIKE 才会使用索引。因为在以通配符 % 和 \_ 开头做查询时，MySQL才不会使用索引。

[1200_4-1]: https://private.codecogs.com/gif.latex?1200%5E%7B4-1%7D
[change buffer]: https://www.jianshu.com/p/09da43bda609
[MATCH AGAINST]: https://blog.csdn.net/xueliangEmail/article/details/83880913?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
[Link 1]: https://www.cnblogs.com/zhoujinyi/archive/2013/04/16/3016223.html
[Link 2]: https://www.jianshu.com/p/df2bb6ca178e