MySQL索引

atwdy

已于 2024-03-08 01:30:58 修改

阅读量984

点赞数 25

分类专栏： SQL 文章标签： mysql 数据库索引

于 2024-03-04 02:58:29 首次发布

本文链接：https://blog.csdn.net/atwdy/article/details/136408705

版权

SQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

一、索引分类
二、语句结构
三、索引失效

测试环境：mysql8.0.18(InnoDB)

一、索引分类

mysql中常用的索引类型包括主键索引、普通索引、唯一索引、联合索引，至于文档中提到的全文索引和空间索引业务中涉及的较少，暂不深入。下面分别介绍下上面四种索引之间的区别，并结合实际数据验证索引失效的场景，为sql优化提供一些实践思路。

主键索引(primary key)：要求索引字段唯一且不为null。
普通索引(key)：索引字段可以为null，也可以重复。
唯一索引(unique key)：索引字段可以为null，但不可以重复(需要注意可以有多个null值，因为null与null并不相等)。
联合索引：可以理解为多个字段的普通索引，每个字段可以重复，也可以为null。

InnoDB引擎中主键索引又叫一级索引，同时也默认为聚簇索引。其它非主键索引都属于二级索引，也叫非聚簇索引。

简单的理解聚簇索引与非聚簇索引：
聚簇索引：索引的叶子节点保存了完整的数据行信息。聚簇索引的结构决定了表中数据实际的物理存储顺序。
非聚簇索引：索引的叶子节点只保存索引字段的值和该行记录的主键，可以理解为，非聚簇索引其实是维护了自定义索引和聚簇索引之间的关系。在基于非聚簇索引查询时，先根据字段值拿到该行记录的主键，如果select的字段在聚簇索引中未被包含时，还要再从聚簇索引中根据主键值取出该行数据，也就是常说的回表操作。
因为聚簇索引保存了全部数据，决定了数据的实际物理存储顺序，所以一个表只能有一个聚簇索引。而非聚簇索引只是保存了记录的主键，不影响数据的物理存储顺序，所以一个表中可以有多个非聚簇索引。

上面4种索引底层数据结构均为B+树，后续会针对B+树单独的介绍一下：B+树

下图截自官方文档，指出了InnoDB引擎中几种索引对于重复值和null值的限制，其中后面IS NULL Scan Type和IS NOT NULL Scan Type表示当查询中使用is null或is not null比较时是走索引还是全表扫描。
在这里插入图片描述

二、语句结构

1.创建索引

https://dev.mysql.com/doc/refman/8.0/en/create-index.html：
在这里插入图片描述
create index 索引名 on 表名(字段1, ...)，还有种创建方式：alter table 表名 add index 索引名(字段名1, ...)，文档上没有看到create语句创建主键索引的方式，可能是不支持，因此创建主键索引时可以通过后一种方式实现：alter table 表名 add primary key(字段名)。

2.查看索引

https://dev.mysql.com/doc/refman/8.0/en/show-index.html：
在这里插入图片描述

show index from 表名 from 库名或者show index from 库名.表名，当前操作库为指定库名的库时库名可省略。语句里的from可以用in替换，同时index也可以换成indexes或者keys，效果都是一样的。至于extended和where关键字，分别用来显示mysql内部创建使用但对用户不可见的索引和指定索引的限制查询条件，如只返回索引名包含ID的索引列表：show indexes from test_index where `Key_name` like '%ID%';
在这里插入图片描述

下面是返回值中几个主要字段代表的含义：

Table：查询的表名
Non_unique：该索引是否允许包含重复值，0表示否，1表示是。
Key_name：索引的名称。如果索引是主键，则名称始终为PRIMARY。
Seq_in_index：该列字段在索引中的序号，从1开始。这个字段可以看出联合索引中各个字段的先后顺序。
Column_name：属于该索引的列名。
Collation：列在索引中的排序方式。它的值可以是A(升序)、D(降序)或NULL(未排序)。
Cardinality：对索引中唯一值的数量的估计。基数是根据存储为整数的统计信息计算的，因此即使对于小表，该值也不一定是精确的。基数越高，MySQL在执行连接时使用索引的可能性就越大。
Sub_part：索引前缀。也就是说，如果列只被部分索引，则为索引字符数，如果整个列被索引，则为NULL。

3.删除索引

https://dev.mysql.com/doc/refman/8.0/en/drop-index.html：
在这里插入图片描述
drop index 索引名 on 库名.表名，库名可省略。当删除主键索引时，索引名固定为PRIMARY，大小写随意，但必须反引号包裹，否则会报语法错误，因为该关键字为mysql的保留字段。

三、索引失效

1.数据准备

测试数据来源于https://www.kaggle.com/datasets/tanishqdublish/urban-traffic-density-in-cities?select=futuristic_city_traffic.csv，共有1219567条记录，每条记录包含11个字段。

首先通过pandas将csv文件数据导入到mysql中：

import time
import pandas as pd
from sqlalchemy import create_engine

try:
    df = pd.read_csv(r"D:\Download\Chrome\archive\futuristic_city_traffic.csv")
    engine = create_engine('mysql+pymysql://root:123456@localhost/demo')
    t1 = time.time()
    df.to_sql('test_index', engine, index_label='ID', chunksize=10000)
    t2 = time.time()
    print(f'导入完成，用时{t2 - t1:.2f}S')
except Exception as e:
    print(e)

为了方便后续测试修改下几个字段的数据类型：

alter table test_index modify column City varchar(15);
alter table test_index modify column Weather varchar(25);
alter table test_index modify column `Traffic Density` varchar(20);

表结构如下：
在这里插入图片描述

表数据：
在这里插入图片描述

测试之前，首先设置主键索引(ID)，普通索引(City)，普通索引(Traffic Density)，普通索引(Speed)，联合索引(City、Weather、Speed)：

-- 添加主键索引
alter table test_index add primary key(ID);

-- 字符型字段添加普通索引
create index idx_city on test_index(City);
create index idx_trafficdensity on test_index( `Traffic Density`);

-- 数值型字段添加普通索引
create index idx_speed on test_index(Speed);

-- 添加联合索引，字段类型分别为varchar、varchar、double
create index idx_city_weather_speed on test_index(City, Weather, Speed);

在这里插入图片描述

2.失效场景

1）字段类型不匹配

当索引字段类型和比较值字段类型不匹配时，mysql会进行隐式转换，但可能会导致不走索引。例如：

explain
select * from test_index where `Traffic Density` = 0.0894;

在这里插入图片描述
执行计划显示可选择的索引possible_keys为idx_trafficdensity，但实际选择的索引key为null，说明当索引字段类型为字符型而比较值为数值型时会导致索引失效。在join关联查询和where条件过滤时都要注意这种情况。

但是当索引字段类型为数值型，比较值为字符型时就不会导致索引失效：

explain
select * from test_index where Speed='100000';

在这里插入图片描述
所以最终是否会导致索引失效，还得结合具体比较的字段类型来说。

2）索引字段使用函数或表达式计算

当对索引字段使用函数或表达式计算时会导致索引失效：

explain
select * from test_index where ID+1 = 10000;

在这里插入图片描述
可以通过把函数计算调整到等号右边，也就是不直接在索引字段上计算来避免这种情况下的索引失效：

explain
select * from test_index where ID = 10000-1;

在这里插入图片描述

3）or 连接符两边包含非索引字段

当where查询条件中多个字段被or操作符连接，但是其中存在没有索引的字段时，会导致索引失效：

explain
select * from test_index where ID = 10000 or `Energy Consumption` = 25.8028;

在这里插入图片描述

解决方法可以是分开查询然后做union all操作，但是union all可能会导致数据重复，如果需要去重也可以采用union：

explain
select * from test_index where ID = 10000 
union all 
select * from test_index where `Energy Consumption` = 25.8028;

在这里插入图片描述

4）范围查询可能导致索引失效

范围查询指<、<=、>、>=、between and、和like xxx%等这样的运算，这种情况逻辑上来说应该会走索引，因为根据B+树叶子节点之间相互连接的特点，只需要定位到比较值的节点，然后往前或者往后一直遍历就行了。但实际中，当符合范围的数据相对过多时，mysql内部会对此优化，直接全表扫描不走索引：

explain
select * from test_index where City < 'Z';

在这里插入图片描述
可以这么理解，因为City上的索引为二级索引，所以在查询到符合条件记录的key后还要进行一次回表操作，从主键索引中根据key获取行的其它字段信息。如果符合条件的数据量比较大，那么从二级索引上省下来的开销还不够回表操作浪费。按照这种思路可以猜想，如果对于主键上的范围查询，无论符合条件的数据量有多大都一定会走索引，因为主键索引保存了完整的行信息，不需要再做一次回表操作。

explain
select * from test_index where ID < 1000000;

在这里插入图片描述
结果符合预期。

5）对索引字段进行非等值运算

非等值运算包含!=、not in、not between、not like等，下面以!=进行测试。

当对索引字段使用 != 比较运算时，可能会导致索引失效：

explain
select * from test_index where Speed != 100;

在这里插入图片描述
对于这种情况下的索引失效，可以通过between and+union all的方式避免。

但是这种场景下需要注意两点：
1.between and并不是说一定会走索引；
2.对于主键索引，!= 运算符不会导致索引失效；
这两点的原因都同4）中的范围查询。

6）like 模糊查询

网上很多博客和资料说的是，对于like模糊查询，如果匹配值左边有%，那么就会导致索引失效，如果匹配值右边有%，那么一般不会影响索引的选择（因为此时相当于是范围查询）。整体的思路是对的，但是在实践中优化器会有一个优化选择的过程。

测试之前先需要了解下什么是覆盖索引，什么是非覆盖索引：

覆盖索引是一个相对的概念。覆盖索引是指一个查询可以完全通过索引来满足，而不需要去查找实际的数据行。当一个查询涉及到的列都包含在同一个索引中，并且查询的条件、排序、分组等操作都可以通过索引完成时，就可以说这个索引覆盖了该查询。
同理，当一个查询涉及到的列并不全包含在同一个索引中，那么这个索引就算是非覆盖索引。

1.对于需要回表操作的查询，也就是非覆盖索引查询，当匹配串的%在右边时，是否会走索引跟表中和匹配串能匹配上的数据量有关，当能匹配的数据较多时就会走全表扫描，较少时会走索引。这里的原因和4）范围查询中的原因相同。

-- `Is Peak Hour`字段为非索引字段，City仅包含6个唯一值，其中包含Neuroburg
-- City为Neuroburg的数据大概有20W条
explain
select City, Weather, `Is Peak Hour` from test_index where City like 'Neuroburg%';

在这里插入图片描述

-- 将Neuroburg用'Neurobura%'匹配，匹配到的数据量为0但是会走索引
explain
select City, Weather, `Is Peak Hour` from test_index where City like 'Neurobura%';

在这里插入图片描述

当匹配串的左边包含%，那么不管能匹配上的数据量有多少，都不会走索引：

explain
select City, Weather, `Is Peak Hour` from test_index where City like '%Neuroburg';

在这里插入图片描述

2.对于覆盖索引查询，无论左右两边是否包含%，以及表中和匹配串能匹配上的数据量有多少，都会走索引：

-- 将Neuroburg用'Neurobura%'匹配，匹配到的数据量为0但是会走索引
explain
select City, Weather from test_index where City like '%Neuroburg%';

在这里插入图片描述

7）不符合最左前缀匹配原则

要说最左前缀匹配，其实还是得回到底层的数据结构B+树，对与联合索引(A, B, C)来说，它在B+树中的存储特点为：
（1）A整体有序。
（2）在A整体有序的基础上B局部有序。
（3）在B局部有序的基础上C局部有序。
找了下面一张图片，图片中的联合索引为(姓名，出生日期，手机号)，描绘的很形象：
在这里插入图片描述
从图中可以直观看到，如果查询字段仅有出生日期或是手机号，那么就不能利用这个索引，因为这二者的顺序都是在姓名确定了的前提下的某个局部区间内有序，而对于全局来说，这两个字段都是无序的。这就可以理解为最左前缀匹配原则。

为了方便测试，先删除字段City和Speed的普通索引，只保留关于City、Weather和Speed三个字段的联合索引，此时表中剩余的索引如下：
在这里插入图片描述

ABC型查询索引生效：

explain
select * from test_index where City = 'Neuroburg' and Weather = 'Clear' and Speed = 1;

在这里插入图片描述

CBA型查询索引生效：

explain
select * from test_index where Speed = 1 and Weather = 'Clear' and City = 'Neuroburg';

在这里插入图片描述

BC型查询索引不生效：

explain
select * from test_index where Weather = 'Clear' and Speed = 1;

在这里插入图片描述

AC型查询索引生效：

explain
select * from test_index where City = 'Neuroburg' and Speed = 1;

在这里插入图片描述

通过ABC型和CBA型执行计划中的ref和rows可以看出，两种查询方式都是三个字段的索引全部利用上了，直接定位到该行数据，所以可以说明的是，在sql的书写中对索引字段的顺序并没有强制要求，只要保证筛选字段全部属于联合索引字段且包含了最左字段，就能保证会走联合索引。

但即使走联合索引，这种场景下又可以分为全部索引利用和部分索引利用。还是拿联合索引(A, B, C)来说，如果C所依赖的字段B使用的是范围查找或是模糊查询，都会导C的索引失效，从而可能只利用到A的索引或AB的索引。

范围查询导致后面字段索引失效：

explain
select * from test_index where City = 'Neuroburg' and Weather < 'D' and Speed = 1;

在这里插入图片描述

模糊查询导致后面字段索引失效：

explain
select * from test_index where City = 'Neuroburg' and Weather like 'Clear%' and Speed = 1;

在这里插入图片描述

对于 != 运算比较特殊，无论联合索引的哪个字段使用了 != 运算，都会导致整个联合索引失效。也很好理解，因为无论哪个字段使用 != 运算，最终的结果一定是每条数据都要经过比较才能确定最后的结果，因此不如直接全表扫描来的直接：

explain
select * from test_index where City = 'Neuroburg' or Weather != 'Clear' and Speed = 1;

在这里插入图片描述

PS

目前了解到的语句层面导致索引失效的场景就这么多，这些只是一个示例参考并不全面，可以为索引失效的优化提供一些思路。mysql内部优化器对索引的选择很灵活，并非是满足某种条件就一定会选择走索引，走哪个索引。但是原理是不变的，理解好索引的底层结构，什么时候需要回表，什么时候不需要回表，结合具体的执行计划，针对性的优化。