MySQL数据库索引的类型、命名规范、建立原则以及索引失效的情况

小鱼儿 2023-02-25 10:18 69阅读 0赞

本篇中记录下数据库索引相关的知识点！

**索引是什么？**  
举个例子；大家去图书馆借书时，会先在电脑检索书名或作者等关键字信息，查询出该本书对应的一个图书索引后，紧接着就可以拿着这个图书索引去精确定位存放该索引范围内的书架并找到这本书！  
所以数据库索引也是这个作用，通过在表的一列或多个列上建立索引对象，用于加快数据的检索。但索引也有缺点，过多的索引会占用硬盘空间以及导致insert、update、delete语句的执行效率降低！

### （一）索引的类型 ###

1.  **普通索引和唯一索引：**  
    普通索引： MySQL中最基础的索引类型，允许在定义索引的列中插入重复值和空值。  
    唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值和组合必须唯一。  
    主键索引是一种特殊唯一索引，不行允许有空值。
2.  **单列索引和组合索引：**  
    单列索引：一个索引只包含一个列，一个表也可以有多个单列索引。  
    组合索引：在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
3.  **全文索引：**  
    全文索引类型为FULLTEXT，在定义索引的列上支持全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR,VARCHAR或者TEXT类型上创建。只有MyISAM存储引擎支持全文索引。
4.  **空间索引：**  
    空间索引是对空间数据类型的字段建立索引，Mysql中的空间数据有4中：GEOMETRY、POINT、LINESTRING和POLYGON。Mysl使用SPATIAL关键字进行扩展，使得能够创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT  
    NULL，空间索引只能在存储引擎为MyISAM中创建。

### （二）索引的命名规范 ###

一般来说，每家公司可能存在自己的一套规范；也可参照以下的比较通用的索引命名规则（**通过索引类型简写前缀 + 下划线\_ + 字段名（组合索引多个字段则继续加\_）拼接而成，名称需使用小写字母，同时避免用到MySQL保留关键字**）：

*  唯一索引：使用`uni_[字段名]`来命名，比如用户名唯一索引uni\_username
 *  非唯一索引：使用`idx_[字段名]`来命名，比如用户名普通索引idx\_username，用户名手机号组合索引idx\_username\_phone

索引的最佳规则：Primary key > Unique key > 一般索引

### （三）索引的建立原则 ###

*  定义有主键和外键的字段列一定要建立索引（数据库默认建立主键索引）；
 *  对于查询频率高，频繁作为 WHERE 条件且不同值较多的列，以及频繁出现在关键字ORDER BY、GROUP BY、DISTINCT后面的列，需要建立索引；
 *  经常与其他表进行JOIN连接的表，在连接字段上应该建立索引；
 *  对于那些查询中很少涉及的列，重复值比较多的列（一般为逻辑型字段，比如性别，只有男、女、其他三种值，这种字段区分度不高）不要建立索引；
 *  频繁更新的字段不适合建立索引；
 *  索引应该建在小字段上，对于定义为text等文本数据类型的列不要建立索引；
 *  表的数据量很少的情况下不适合建立索引；
 *  限制每张表上的索引数量，不超过5个；

### （四）索引失效的情况 ###

下面列举一些索引失效的情况（MySQL8.0版本，根据explain分析执行计划中的key信息是否用到索引）：

##### 1. where条件中使用OR #####

分以下三种情况（user表的索引有主键索引和唯一索引uni\_phone）：

*  OR连接的字段若有非索引字段，则索引失效：  
    ![在这里插入图片描述][20200712154234680.png]
 *  OR连接的字段若是两个不同的索引字段或者同一个组合索引的不同字段，则索引失效（可以用UNION代替OR）：![在这里插入图片描述][20200712153336394.png]
 *  OR连接的字段若是两个相同的索引字段，则索引生效（可以用IN代替OR）：![在这里插入图片描述][20200712154115786.png]

##### 2. where条件中使用LIKE #####

分以下两种情况（user表的索引有主键索引和唯一索引uni\_phone）：

*  LIKE的值中带%或者\_的前缀匹配符，则索引失效：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70]
 *  LIKE的值中带%或者\_的通配符，但不置于其前缀，则索引生效：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 1]

##### 3. where条件中使用了 组合索引，但没有遵循最左前缀原则 #####

组合索引的最左前缀原则：假如在只建立了一个组合索引(a,b,c)的情况下，那么实际上相当于建立了(a), (a,b), (a,b,c)共三个索引。  
但根据最左前缀原则，在使用索引时，只有`where a = 'test1'` 和 `where a = 'test1' AND b = 'test2` 和 `where a = 'test1' AND c = 'test3`以及`where a = 'test1' AND b = 'test2 AND c = 'test3'`会索引生效（条件中的字段顺序可以打乱，但字段必须得满足）；  
而类似于`where b = 'test2'` 和 `where c = 'test3'`以及`where b = 'test2 AND c = 'test3'`则索引失效；

例子（user表的索引有主键索引和组合索引idx\_username\_email\_phone）：  
索引失效的情况：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 2]  
索引生效的情况：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 3]

##### 4. where条件中对索引列使用函数 #####

user表的索引有主键索引和唯一索引uni\_username，在查询中对索引字段username使用了函数，因此这种情况下索引失效：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 4]

##### 5. where条件中对索引列使用数学运算 #####

比如对主键索引执id执行`id/2`，则会出现索引失效：![在这里插入图片描述][20200712175217415.png]  
所以在实际中我们应该改成`id = a/2`，这时就会索引生效：  
![在这里插入图片描述][20200712175451837.png]

##### 6. where条件中存在对索引列的数据类型隐式转换 #####

user表的索引有主键索引和唯一索引uni\_phone，且phone字段类型为varchar类，但在查询条件中没有加引号，所以存在隐式转换，因此这种情况下索引失效：![在这里插入图片描述][20200712173949781.png]

##### 7. where条件中存在NOT EXISTS #####

使用到NOT EXISTS语法会索引失效（user表的索引有主键索引）：  
![在这里插入图片描述][20200712180538489.png]

##### 8.where条件中存在NULL #####

存在IS NULL或者IS NOT NULL的null值判断，分两种情况（user表的索引有主键索引和唯一索引uni\_username）：

*  当查询的数据不仅只有索引字段列时，则索引失效：

![在这里插入图片描述][2020071217581739.png]

*  当查询的数据只有索引字段列时，则索引生效（实际中基本不会这么用）：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 5]

**注意：以上是MySQL8.0版本的索引失效情况，但在MySQL5.7版本中，除了以上这些情况外，还包括下面的情况会索引失效：**

1.  MySQL5.7，表关联时，关联字段字符集不一致会导致索引失效
2.  MySQL5.7，使用 != 或 IN 或 NOT IN 且返回值不只有索引列

[20200712154234680.png]: /images/20230209/b1474015d18b4c84a159e81785cafaa5.png
[20200712153336394.png]: /images/20230209/b7b8e29131e34015b6e88418907d6337.png
[20200712154115786.png]: /images/20230209/dba8a6e6255b499184cfd7b7f012489b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70]: /images/20230209/372e3d9f8d5949b3a0f6a18d038285ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 1]: /images/20230209/8abfe87c388e494fb20b7014bd87ff67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 2]: /images/20230209/9e992eaede7d43f8845ad4527b7241de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 3]: /images/20230209/e5859ba812f442d59d233ccd81a03a66.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 4]: /images/20230209/508cefe88aae4caf9c20079e1ae77bbf.png
[20200712175217415.png]: /images/20230209/b9416446a3f6427b9c52e3d2dfb26f3b.png
[20200712175451837.png]: /images/20230209/513b6c8647fb40d9be7323f606647636.png
[20200712173949781.png]: /images/20230209/1467ecbb1a994fd498abe3f1c5468549.png
[20200712180538489.png]: /images/20230209/23d806aae2544b34bccc0c0bbd6a33e8.png
[2020071217581739.png]: /images/20230209/8ad2e6c8d5e2485c9f49f485bc77a3fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 5]: /images/20230209/416bcf2772ee4d7a8a286b3a38c73784.png