Mysql数据库索引创建、索引删除、索引失效场景详解

电玩女神 2024-03-25 16:43 139阅读 0赞

#### 文章目录 ####

*  前言
 *  一、索引分类及创建
 *   *  1.普通索引
     *  2.主键索引
     *  3.唯一索引
     *  4.复合索引
     *  5.全文索引
 *  二、索引删除
 *  三、索引失效场景
 *   *  1.查询中带有or关键字导致索引失效
     *  2.模糊查询中like以%开头导致索引失效
     *  3.字符类型查询时不带引号导致索引失效
     *  4.索引字段参与计算或使用函数导致索引失效
     *  5.违背最左前缀原则导致索引失效
     *  6.不同字段值对比导致索引失效
     *  7.反向范围操作导致索引失效
     *  8.优化器评估使用全表扫描要比使用索引快,则索引失效
     *  9.字符集不统一导致索引失效
 *  总结

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

## 前言 ##

在关系数据库中，索引是一种单独对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。  
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。在MySQL数据库一共支持5种类型的索引和9种索引失效的场景，下面，我来进行一一介绍。

## 一、索引分类及创建 ##

### 1.普通索引 ###

普通索引是MySQL数据库中的一种普通的索引，添加普通索引的列对数据没有特殊要求，普通索引能起到的作用就是加快差查询速度。  
**常用示例：**  
示例中的t\_user（表名称）, index\_name（索引名）id、name、address（均为字段名称）。  
**使用create语句创建普通索引**

create index index_name on t_user (name);

**使用alter语句创建普通索引**

alter table t_user add index index_name(name);

**建表时创建普通索引**

create table t_user(id int,name varchar(64),address text,index index_name (name));

### 2.主键索引 ###

主键索引是数据库的所有索引中查询速度最快的，并且每个数据表只能有一个主键索引，但可以有多个主键列（即复合主键）。主键索引的列不允许出现重复的数据，也不允许为空值。  
**常用示例：**  
示例中的t\_user（表名称）, index\_name（索引名）id、name、address（均为字段名称）。  
**创建表示设置主键。（主键会自动创建主键索引）**

create table t_user(id int,name varchar(64),address text,primary key(id));

**使用alter语句添加主键**

alter table t_user add primary key(id);

### 3.唯一索引 ###

唯一性索引要求添加该索引的列所有的值不能重复。唯一索引允许有空值（注意和主键索引不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。  
**常用示例：**  
示例中的t\_user（表名称）, index\_name（索引名）id、name、address（均为字段名称）。  
**使用create语句创建唯一索引**

create unique index index_name on t_user(name);

**使用alter语句创建唯一索引**

alter table t_user add unique index_name(name);

**创建表示创建唯一索引**

create table t_user(id int,name varchar(64),address text,unique index_name(name));

### 4.复合索引 ###

复合索引也叫组合索引，指的是我们在建立索引的时候使用多个字段。复合索引在配合select语句使用时需要满足最左原则，否则索引会失效。  
**常用示例：**  
示例中的t\_user（表名称）, index\_name（索引名）id、name、address（均为字段名称）。  
**使用create语句创建复合索引**

create index index_name on t_user(name, address);

**使用alter语句创建复合索引**

alter table t_user add index index_name(name, address);

**创建表是创建复合索引**

create table t_user(id int,name varchar(64), address text,index index_name(name, address));

### 5.全文索引 ###

全文索引主要是用于解决大数据量的情况下模糊匹配的问题。如果数据库中某个字段的数据量较大，那么如果我们想要使用like+通配符的方式进行查找，速度就会变得非常慢。针对这种情况，我们就可以使用全文索引的方式，来加快模糊查询的速度。当然，如果你项目数据规模足够大，建议引入ElasticSearch、Solr、MeiliSearch等搜索引擎来实现全文检索。全文索引的原理是通过分词技术，分析处文本中关键字及其出现的频率，并依次建立索引。全文索引的使用，与mysql数据库版本、数据表引擎乃至字段类型息息相关，**主要限制如下：**  
（1）MySQL3.2版本以后才支持全文索引。  
（2）MySQL5.7版本以后MySQL才内置ngram插件，全文索引才开始支持中文。  
（3）MySQL5.6之前的版本，只有MyISAM引擎才支持全文索引。  
（4）MySQL5.6以后的版本，MyISAM引擎和InnoDB引擎都支持全文索引。  
（5）只有字段数据类型为char、varchar、以及text的字段才支持添加全文索引。  
**常用示例：**  
示例中的t\_user（表名称）, index\_name（索引名）id、name、address（均为字段名称）。  
**使用create语句创建全文索引**

create fulltext index index_name on t_user(address);

**使用alter语句创建全文索引**

alter table t_user add fulltext index_name(address);

**创建表时创建全文索引**

create table t_user(id int,name varchar(64), address text,fulltext index_name(address));

## 二、索引删除 ##

mysql数据库删除索引分为删除主键索引、删除其他索引两种不同的方式。  
**常用删除索引示例：**  
示例中的t\_user（表名称）, index\_name（索引名）id、name、address（均为字段名称）。  
**删除主键索引**

alter table t_user drop primary key;

**使用drop语句删除其他索引**

drop index index_name on t_user;

**使用alter语句删除其他索引**

alter table t_user drop index index_name;

## 三、索引失效场景 ##

仅仅知道如何创建和删除索引还不够，要想利用好索引，那一定要先搞清楚那些情况会导致索引失效，弄明白这些事儿之后，在写SQL的时候刻意避开这些场景才能让你的SQL使用索引。下面介绍一下索引失效的一些场景。  
**`在介绍索引失效场景之前，大家还需要了解explain工具，以下所有的示例都是使用explain命令来查看是否使用索引，当在一条SQL前加上explain命令，执行这条SQL后会列出相应的执行计划。如下图。`**  
![在这里插入图片描述][b0fb832336c5469aaeea15cb04d9f4b5.png]  
**红框里的各个字段含义如下：**  
**id：**  
这是执行计划的ID值，这个值越大，表示执行的优先级越高。  
select\_type：当前查询语句的类型，有如下几个值： simple：简单查询。primary：复杂查询的外层查询。 subquery：包含在查询语句中的子查询。 derived：包含在FROM中的子查询。  
**table：**  
表示当前这个执行计划是基于那张表执行的。  
**type：**  
当前执行计划查询的类型，有几种情况： all：表示走了全表查询，未命中索引或索引失效。 system：表示要查询的表中仅有一条数据。 const：表示当前SQL语句的查询条件中，可以命中索引查询。 range：表示当前查询操作是查某个区间。 eq\_ref：表示目前在做多表关联查询。 ref：表示目前使用了普通索引查询。 index：表示目前SQL使用了辅助索引查询。  
**possible\_keys：**  
执行SQL时，优化器可能会选择的索引（最后执行不一定用）。  
**key：**  
查询语句执行时，用到的索引名字。  
**key\_len：**  
这里表示索引字段使用的字节数。  
**ref：**  
这里显示使用了那种查询的类型。  
**rows：**  
当前查询语句可能会扫描多少行数据才能检索出结果。  
**Extra：**  
这里是记录着额外的一些索引使用信息，有几种状态： using index：表示目前使用了覆盖索引查询（稍后讲）。 using where：表示使用了where子句查询，通常表示没使用索引。 using index condition：表示查询条件使用到了联合索引的前面几个字段。 using temporary：表示使用了临时表处理查询结果。 using filesort：表示以索引字段之外的方式进行排序，效率较低。 select tables optimized away：表示在索引字段上使用了聚合函数。  
**`对于上述这么多的字段，其实目前不需要完全弄懂，本文只需要记住里面的type字段即可，all表示走全表扫描，const、ref、range等表示通过索引查询。`**

### 1.查询中带有or关键字导致索引失效 ###

**示例：**

explain select * from t_user where id='1' and name='lilei';

**如下图，当使用and连接两个查询条件时type=const，表示使用了索引。**  
![在这里插入图片描述][89eb75f19f334f29a7ca27412390ac7c.png]

**如下图，当使用or连接两个查询条件时，尽管id和name都创建了索引，但type=all，表示进行了全表扫描，索引失效。**  
![在这里插入图片描述][f6111a6fe071493c8f8a423187083199.png]

### 2.模糊查询中like以%开头导致索引失效 ###

在进行模糊查询使用like '%XX’或者like '%XX%'方式进行查询会导致索引失效。  
**示例：**

explain select * from t_user where name like '%lilei';

**如下图，以%开头，尽管name字段创建了索引，但type=all，表示进行了全表扫描，索引失效。**  
![在这里插入图片描述][a41c96278bc04a54a3c0965cdd67dd64.png]

**如下图，如果以%结尾type=range，表示进行范围扫描，并未导致索引失效。**  
![在这里插入图片描述][f267ca091d8246c0ad9cc6532ff375df.png]

### 3.字符类型查询时不带引号导致索引失效 ###

**示例：**

explain select * from t_user where name = 111;

**如下图，name字段为字符类型，但查询时，值未加引号type=all，导致索引失效。**  
![在这里插入图片描述][c738adbdd8a2488ea30b74e02570d662.png]

**如下图，当给name值加引号后，type=ref，索引生效。**  
![在这里插入图片描述][d6d0599074d84f569e4fa5033a1b248b.png]

### 4.索引字段参与计算或使用函数导致索引失效 ###

**示例：**

--索引字段参与计算
    explain select * from t_user where name + 1 = 'lilei';
    --索引字段使用函数
    explain select * from t_user where substring(name,0,2) = 'li';

**如下图，name字段进行+1操作，type=all进行全表扫描，索引失效。**  
![在这里插入图片描述][cceaaacec2d949acbdd4183a592b6f35.png]

**如下图，name字段使用了substring函数，type=all进行全表扫描，索引失效。**

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

### 5.违背最左前缀原则导致索引失效 ###

当使用复合索引时必须满足最左前缀原则，否则索引失效。  
**示例：（email、is\_active两个字段创建了复合索引）**

explain select * from t_user where email = 'li' and is_active='1';

**如下图，当查询条件不包含email字段时，违背最左前缀原则，type=all进行全表扫描，索引失效。**  
![在这里插入图片描述][b346388ccf504578a6eb140052ca4081.png]

**如下图，当查询条件包含email字段时，type=ref，索引生效。**  
![在这里插入图片描述][6a113712bfe94523923f31e594e72d17.png]

### 6.不同字段值对比导致索引失效 ###

从一张表中查询出一些值，然后根据这些值去其他表中进行比对，这个业务场景也较为常见，下面为了简单实现，就用name和address字段来模拟一下字段对比的场景  
**示例：**

explain select * from t_user where name = address;

**如下图，由于name和address两个字段进行了比对，尽管这2个字段都创建了索引，但执行计划中type=all，进行全表扫描，索引失效。**  
![在这里插入图片描述][30465489e2554d6b8759326adef96230.png]

### 7.反向范围操作导致索引失效 ###

一般来说，如果SQL属于正向范围查询，例如>、<、between、like、in…等操作时，索引是可以正常生效的，但如果SQL执行的是反向范围操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>…等操作时，就会导致索引失效。  
**`但你一定要记住这并非绝对了，随着数据库优化器的逐步完善，这种失效的情况也在逐步改善，所以本条只做参考，一切以执行计划结果为准。`**  
**示例：**

explain select * from t_user where name <> 'lilei';

**如下图，使用<>号操作后，执行计划的type=all，进行全表扫描，索引失效。**  
![在这里插入图片描述][da99d02799ce46d4a98731e0c3ff27d4.png]

**如下图，当使用=号操作后，执行计划的type=rel，索引生效。**  
![在这里插入图片描述][e97fad86880540c1bcd4eb5b4f3eca31.png]

### 8.优化器评估使用全表扫描要比使用索引快,则索引失效 ###

在MySQL中还有一种特殊情况会导致索引失效，也就是当走索引扫描的行数超过表行数的30%时，MySQL优化器会认为全表扫描更快，就会默认放弃索引查询，转而使用全表扫描的方式检索数据，因此这种情况下走索引的顺序磁盘IO，反而不一定有全表的随机磁盘IO快。

### 9.字符集不统一导致索引失效 ###

Mysql数据库在使用的时候会比对字符集，如果数据表的字符集不一致可能会导致索引失效。mysql数据库建议使用utf8mb4字符集。

## 总结 ##

word文档下载地址：[Mysql数据库索引创建、索引删除、索引失效场景详解][Mysql]

[b0fb832336c5469aaeea15cb04d9f4b5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ab59312e9f0f414e86707a6ef79e939e.png
[89eb75f19f334f29a7ca27412390ac7c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4b4077547cfb40d6a590a7ed3dc52020.png
[f6111a6fe071493c8f8a423187083199.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2ee3be420057414d84c8c6aeb31351b4.png
[a41c96278bc04a54a3c0965cdd67dd64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/329d230f209244c3b1a9a42770325c4d.png
[f267ca091d8246c0ad9cc6532ff375df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/eb455d27737e49fbb2a8f9980eb43bd8.png
[c738adbdd8a2488ea30b74e02570d662.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4bc58c399ec94d1581b5b9a999082865.png
[d6d0599074d84f569e4fa5033a1b248b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1183e7a1747b42afb77a51d0cbfc9adc.png
[cceaaacec2d949acbdd4183a592b6f35.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/38ffed6bd46c4e738dcbfb6c84240952.png
[d1000b48fae84d91b91b7de5d90bbf6c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/d3c91712041c49229f85aac68f4940f7.png
[b346388ccf504578a6eb140052ca4081.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/42a32ab2334447df83802116c433aca0.png
[6a113712bfe94523923f31e594e72d17.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/663e2566db5b44bf9c540bbf17ee562c.png
[30465489e2554d6b8759326adef96230.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f33e800d48c8454799de316f708c7880.png
[da99d02799ce46d4a98731e0c3ff27d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/b47bc82369d74e0b94f95d2e0d20f904.png
[e97fad86880540c1bcd4eb5b4f3eca31.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/7df07cbdd2484858b90c7bd5aab33819.png
[Mysql]: https://download.csdn.net/download/ma286388309/87580073