深入浅出Mysql索引

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 深入浅出Mysql索引

1 引言


 在下面这个表T中,如果执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?


create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');


21.png

                                            图1 InnoDB的索引组织结构


这条SQL查询语句的执行流程:


1.在k索引树上找到k=3的记录,取得 ID = 300;

2.再到ID索引树查到ID=300对应的R3;

3.在k索引树取下一个值k=5,取得ID=500;

4.再回到ID索引树查到ID=500对应的R4;

5.在k索引树取下一个值k=6,不满足条件,循环结束。

在这个过程中,回到主键索引树搜索的过程,称为回表。可以看到,这个查询过程读了k索引树的3条记录(步骤1、3、5),回表了两次(步骤2、4)。


 在这个例子中,由于查询结果所需要的数据只在主键索引上有,所以不得不回表。有没有可能经过索引优化,避免回表过程呢?


2 覆盖索引


 如果执行的语句是select ID from T where k between 3 and 5,这时只需要查ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引k已经“覆盖了”查询需求,称为覆盖索引。


 覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。


 注意:在引擎内部使用覆盖索引在索引k上其实读了三个记录,k上的3~5,ID上的R3~R5,但是对于MySQL的Server层来说,它就是找引擎拿到了两条记录,因此MySQL认为扫描行数是2。


基于上面覆盖索引的说明,来讨论一个问题:在一个市民信息表上,是否有必要将身份证号和名字建立联合索引?

市民表的定义:


CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB


 身份证号是市民的唯一标识。也就是说,如果有根据身份证号查询市民信息的需求,我们只要在身份证号字段上建立索引就够了。而再建立一个(身份证号、姓名)的联合索引,是不是浪费空间?


 如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。


 索引字段的维护总是有代价的。因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。


3 最左前缀原则


 如果为每一种查询都设计一个索引,索引是不是太多了。如果现在要按照市民的身份证号去查他的家庭地址,虽然这个查询需求在业务中出现的概率不高,但也不能让它走全表扫描。

 反过来说,单独为一个不频繁的请求创建一个(身份证号,地址)的索引又感觉有点浪费。应该怎么做呢?

 这里,B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录。

 为了直观地说明这个概念,用(name,age)这个联合索引来分析。

22.png


 索引项是按照索引定义里面出现的字段顺序排序的。

 当逻辑需求是查到所有名字是“张三”的人时,可以快速定位到ID4,然后向后遍历得到所有需要的结果。

 如果要查的是所有名字第一个字是“张”的人,SQL语句的条件是where name like '张%'。这时,也能够用上这个索引,查找到第一个符合条件的记录是ID3,然后向后遍历,直到不满足条件为止。

 因此,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。


 基于上面对最左前缀索引的说明,讨论一个问题:在建立联合索引的时候,如何安排索引内的字段顺序。

 这里的评估标准是,索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

 所以要为高频请求创建(身份证号,姓名)这个联合索引,并用这个索引支持“根据身份证号查询地址”的需求。


 如果既有联合查询,又有基于a、b各自的查询呢?查询条件里面只有b的语句,是无法使用(a,b)这个联合索引的,这时就不得不维护另外一个索引,也就是说需要同时维护(a,b)、(b) 这两个索引。

 这时候要考虑的原则就是空间。比如上面这个市民表的情况,name字段是比age字段大的 ,这里就建议创建一个(name,age)的联合索引和一个(age)的单字段索引。


4 索引下推


 最左前缀可以用于在索引中定位记录。但那些不符合最左前缀的部分,会怎么样呢?

 还是以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是10岁的所有男孩”。SQL语句如下:


select * from tuser where name like '张%' and age=10 and ismale=1;


 根据前缀索引规则,这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录ID3。然后是判断其他条件是否满足。


 在MySQL 5.6之前,只能从ID3开始一个个回表。到主键索引上找出数据行,再对比字段值。

 MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

这两个过程的执行流程图如下。

23.png

                                             无索引下推执行流程

24.png

                                                索引下推执行流程

 

可以看出,有索引下推只用回表2次,没有索引下推需要回表4次。


5 实际设计


CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;


业务里面有这样的两种语句:


select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;


ca和cb索引是否全都需要?


分析:


表记录:

a

b

c

d

1

2

3

d

1

3

2

d

1

4

3

d

2

1

3

d

2

2

2

d

2

3

4

d

 

主键 a,b的聚簇索引组织顺序相当于 order by a,b ,也就是先按a排序,再按b排序,c无序。

 索引 ca 的组织是先按c排序,再按a排序,同时记录主键


c

a

主键部分b(注意只有b)

2

1

3

2

2

2

3

1

2

3

1

4

3

2

1

4

2

3

 

这个跟索引c的数据是一模一样的。

 索引 cb 的组织是先按c排序,在按b排序,同时记录主键。


c

b

主键部分a

2

2

2

2

3

1

3

1

2

3

2

1

3

4

1

4

3

2


对于

select * from geek where c=N order by a limit 1;


 走ca,cb索引都能定位到满足c=N主键

 而且主键的聚簇索引本身就是按order by a,b排序,无序重新排序。所以ca可以去掉。


对于

select * from geek where c=N order by b limit 1;


order by b的顺序与主键顺序不一致,需要额外排序


 所以,结论是ca可以去掉,cb需要保留。


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
98 4
|
4月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
6月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
2月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
3月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
122 9
|
4月前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
108 12
|
9月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
1870 10
|
8月前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
608 81
|
5月前
|
SQL 存储 关系型数据库
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
138 3

推荐镜像

更多