您可以捐助,支持我们的公益事业。

1元 10元 50元





认证码:  验证码,看不清楚?请点击刷新验证码 必填



  求知 文章 文库 Lib 视频 iPerson 课程 认证 咨询 工具 讲座 Model Center   Code  
会员   
   
 
     
   
 
 订阅
深入探索MySQL索引策略
 
作者:木佳
  389  次浏览      20 次
2024-7-29
 
编辑推荐:
本文主要介绍了MySQL数据库中索引的设计与优化方法相关内容,希望对你的学习有帮助。
本文来自于微信公众号阿里云开发者,由火龙果软件Linda编辑、推荐。

阿里妹导读

本文旨在深入探讨MySQL数据库中索引的设计与优化方法。

引言

有一张表user(无索引):

假如要执行的sql语句为:select * from user where age = 45;

需要从第一行开始,一直扫描到最后一行,称为全表扫描,性能很低;有没有提升性能,减少搜索时间的方法呢?

索引介绍

1. B+tree 结构介绍

在Mysql中,索引就是帮助搜索数据的一种有序的数据结构,它以某种方式引用(指向)数据。

Mysql中的索引是在存储引擎层实现的,因此不同的存储引擎又有着不同的索引结构,主要包含以下几种:

简单介绍下经典的B+tree 的结构:

可以看出:

所有的数据都会出现在叶子节点,叶子节点形成一个单向链表;

非叶子节点仅仅索引数据,具体的数据都是在叶子节点存放的;

Mysql索引数据结构对经典的B+tree进行了优化,在原有B+tree的基础上,增加了一个只想相邻叶子节点的链表指针,形成了带有顺序指针的B+tree,提高区间访问的性能,利于排序;

2. 索引分类

在mysql数据库,将索引的具体类型主要分为以下几类:

在Innodb 存储引擎中,根据索引的存储形式,分为两种:

执行一条查询语句,我们分析一下具体的查找过程:

具体过程如下:

1. 因为是根据name字段查询,所以先根据 name='Arm' 到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10;

2. 由于查询返回的数据是*,所以此时,还需要根据主键值10,到聚集索引中查找10对应的记录,最终找到10对应的行row;

3. 最终拿到这一行的数据,直接返回即可。

3. 索引语法

创建索引:

create [unique|fulltext] index 索引名 on 表名 (字段名1,字段名2,……);

查看索引:

show index from 表名;

删除索引:

drop index 索引名 on 表名;

SQL性能分析

1. sql执行频率

Mysql客户端链接成功后,通过以下命令可以查看当前数据库的 insert/update/delete/select 的访问频次:

show [session|global] status like ‘com_____’;

session: 查看当前会话;

global: 查看全局数据;

com insert: 插入次数;

com select: 查询次数;

com delete: 删除次数;

com updat: 更新次数;

通过查看当前数据库是以查询为主,还是以增删改为主,从而为数据库优化提供参考依据,如果以增删改为主,可以考虑不对其进行索引的优化;如果以查询为主,就要考虑对数据库的索引进行优化。

2. 慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位秒,默认10秒)的所有sql日志:

开启慢查询日志前,需要在mysql的配置文件中(/etc/my.cnf)配置如下信息:

# 1. 开启mysql慢日志查询开关:

slow_query_log = 1

#2. 设置慢日志的时间,假设为2秒,超过2秒就会被视为慢查询,记录慢查询日志:

long_query_time=2

#3. 配置完毕后,重新启动mysql服务器进行测试:

systemctl restarmysqld

#4. 查看慢查询日志的系统变量,是否打开:

show variables like “slow_query_log”;

#5. 查看慢日志文件中(/var/lib/mysql/localhost-slow.log)记录的信息:

Tail -f localhost-slow.log

最终发现,在慢查询日志中,只会记录执行时间超过我们预设时间(2秒)的sql,执行较快的sql不会被记录。

3. Profile 详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

#1. 通过 have_profiling 参数,可以看到mysql是否支持profile 操作:

select @@have_profiling;

#2. 通过set 语句在session/global 级别开启profiling:

set profiling =1;

开关打开后,后续执行的sql语句都会被mysql记录,并记录执行时间消耗到哪儿去了。比如执行以下几条sql语句:

select * from tb_user;

select * from tb_user where id = 1;

select * from tb_user where name = '白起';

select count(*) from tb_sku;

#3. 查看每一条sql的耗时基本情况:

show profiles;

#4. 查看指定的字段的sql 语句各个阶段的耗时情况:

show profile for query Query_ID;

#5. 查看指定字段的sql语句cpu 的使用情况:

show profile cpu for query Query_ID;

4. explain 详情

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中,表如何连接和连接的顺序。

语法 :直接在 select 语句之前加上关键字 explain/desc;

# explain select 字段 from 表名 where 条件;

索引使用

1. 索引失效的情况

1.1 最左前缀法则

如果存在联合索引,要遵守最左前缀法则。即查询从索引的最左列开始,并且不跳过索引中的列,如果跳跃其中某一列,索引将会部分失效(后面的字段索引失效)。

假设在 tb_user 表中

联合索引,涉及三个字段,顺序为:profession(索引长度为47), age(索引长度为2), status(索引长度为5)。

a)explain select * from tb_user where profession = '软件工程 ' and age = 31 and status='0';

b)explain select * from tb_user where profession = '软件工程 ' and age = 31 ;

c)explain select * from tb_user where profession = '软件工程‘;

以上的这三组测试中,我们发现只要联合索引最左边的字段profession存在,索引就会生效;

a)explain select * from tb_user where age = 31 and status='0';

b)explain select * from tb_user where status='0';

以上的这两组测试中,我们发现只要联合索引最左边的字段 profession不存在,索引并未生效;

explain select * from tb_user where profession = '软件工程 ' and status='0';

上述的一条SQL查询时,联合索引最左边的列profession字段是存在的,索引满足最左前缀法则的基本条件。但是查询时,跳过了age这个列,所以后面的列索引是不会使用的,也就是索引部分生效,所以索引的长度为47;

explain select * from tb_user where age = 31 and status='0' and profession = '软件工程’;

可以看到,索引长度54,完全满足最左前缀法则,联合索引生效。

⚠️ 最左前缀法则中是指查询时,联合索引的最左边的字段(即第一个字段)必须存在,与编写sql时,条件编写的先后顺序无关。

1.2 范围查询

联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程 ' and age >31 and status='0';

上述可以看到,当范围查询使用>或<时,查询走联合索引了,但是索引长度为49,说明范围查询右边的status字段没有走索引;

explain select * from tb_user where profession = '软件工程 ' and age >= 31 and status='0';

上述可以看到,当范围查询使用>=或<=时,查询走联合索引了,但是索引长度为54,说明所有字段都是走索引的;

⚠️ 所以,在业务允许的情况下,尽可能使用类似于>=或<=这类的范围查询,避免使用>或<。

1.3 索引列运算

在索引列上进行运算操作,索引将失效。

假设在 tb_user 表中,存在单列索引:phone

explain select * from tb_user where phone = '17799990015';

上述看到,当根据phone字段进行等值匹配查询时,索引生效。

explain select * from tb_user where substring(phone,10,2)=’15’;

上述看到,当根据phone字段进行函数运算操作之后,索引失效。

1.4 字符串不加引号

字符串类型字段使用时,不加引号,索引将失效。

explain select * from tb_user where profession = '软件工程 ' and age = 31 and status = '0 ';

explain select * from tb_user where profession = '软件工程 ' and age = 31 and status = 0;

explain select * from tb_user where phone = ‘1779990015’;

explain select * from tb_user where phone = 1779990015;

上述看到,字符串不加单引号时,对于查询结果没有影响,但是由于数据库存在隐式类型转换,索引将失效。

1.5 模糊查询

尾部模糊匹配,索引不会失效;头部模糊匹配,索引失效。

explain select * from tb_user where profession like ‘软件%’;

explain select * from tb_user where profession like ‘%工程’;

explain select * from tb_user where profession like ‘%工%’;

上述看到,在like模糊查询中,在关键字后面加%,索引可以生效;在关键字前面加%,索引将会失效。

1.6 or 连接条件

or 前的条件中的列有索引,后面的列中没有索引,则涉及的所有索引都不会被用到。

步骤一:存在单列索引:phone

explain select * from tb_user where id=10 or age=23;

explain select * from tb_user where phone=’1779990017’ or age=23;

上述看到,由于age 没有索引,所以即使id, phone有索引,索引也会失效。

步骤二:对age字段 建立索引

create index idx_user on tb_user(age);

步骤三:再次执行上述的sql语句

可以看到,当or 连接的条件,左右两侧字段都有索引时,索引才会生效。

1.7 数据分布影响

Mysql评估使用索引会比全表更慢,则不会使用索引。

explain * from tb_user where phone >= ‘1779999005’;

explain * from tb_user where phone >= ‘1779999015’;

上述看到,相同的sql语句,传入字段值不同时,所执行的计划也不同,这是因为:

mysql在查询时,会评估使用索引的效率与走全表扫描的效率,哪种效率高使用哪种。因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则不如走全表扫描来的快,此时索引就会失效。

再来看下,is null & is not null 的操作是否会走索引:

步骤一:

explain select * from tb_user where profession is null;

explain select * from tb_user where profession is not null;

步骤二:把profession 字段全部更新为null:

update tb_user set profession = null;

步骤三:再次执行上述语句

最终看到,相同的sql语句,先后执行两次,查询的计划不同。

这是因为和数据库的数据分布有关系,查询是mysql会评估,走索引还是全表扫描,如果全表扫描块,则放弃索引走全表扫描,因此,is null & is not null 是否走索引,得具体情况具体分析,不是固定的。

2. SQL提示

字段profession 存在联合索引(idx_user_pro_sta) & 单列索引(idx_user_pro)。

explain select * from tb_user where profession=’软件工程’;

可以看出,mysql 最终选择了idx_user_pro_age_sta 联合索引,这是mysql自动选择的结果。

那么,我们在查询时,可以使用mysql的sql提示,加入一些人为的提示来达到优化操作的目的:

user index:建议mysql使用哪一个索引完成此次查询(仅仅是建议,mysql内部还会再次进行评估);

ignore index:忽略指定的索引;

force index:强制使用索引。

演示:

1)explain select * from tb_user use index(idx_user_pro) where profession=’软件工程’;

2)explain select * from tb_user ignore index(idx_user_pro) where profession=’软件工程’;

3)explain select * from tb_user force index(idx_user_pro) where profession=’软件工程’;

3. 覆盖索引

覆盖索引是指查询使用了索引且返回需要的列,在该索引列中已经全部能够找到。

在查询时,尽量使用覆盖索引,减少select *。

表中存在联合索引 idx_user_pro_age_sta (关联了三个字段profession, age, status),该索引也是一个二级索引,该叶子节点下面的是这一行的主键id。当查询返回的数据在id, profession, age, status 中,则直接走二级索引返回数据,如果查询字段超出这个范围,就需要拿到主键id, 再去扫描聚集索引获取额外的数据,这个过程就是回表。

当我们一直使用 select * 查询返回所有字段值,很容易造成回表查询(除非根据主键查询,此时只会扫描聚集索引)。

演示如下:

explain select id, profession, from tb_user where profession=’软件工程’ and age=31 and status=’0’;

explain select id, profession, age, status from tb_user where profession=’软件工程’ and age=31 and status=’0’;

explain select id, profession, age, status, name from tb_user where profession=’软件工程’ and age=31 and status=’0’;

explain select * from tb_user where profession=’软件工程’ and age=31 and status=’0’;

为了让大家更清楚地理解,什么是覆盖索引和回表查询,我们一起来看下一组sql的执行过程:

A. 表结构及索引示意图:

id是主键,是一个聚集索引。name字段建立了普通索引,是一个二级索引(辅助索引)。

B. 执行SQL: select * from tb_user where id = 2;

根据id查询,直接走聚集索引查询,一次索引扫描,直接返回数据,性能高。

C. 执行SQL: selet id,name from tb_user where name = 'Arm';

虽然是根据name字段查询,查询二级索引,但是由于查询返回在字段为 id,name,在name的二级索 引中,这两个值都是可以直接获取到的,因为覆盖索引,所以不需要回表查询,性能高。

D. 执行SQL: selet id,name,gender from tb_user where name = 'Arm';

由于在name的二级索引中,不包含gender,所以,需要两次索引扫描,也就是需要回表查询,性能相对较差一点。

思考一下:一张表四个字段(id, username, password, status),如何对sql优化最优?

select id, name, password from tb_user where username=’itcast’;

答案:针对username, password 建立联合索引,避免回表查询。

4. 前缀索引

当字段类型为字符串(varchar, text, longtext等)时,有时候需要索引很长的字符串,导致索引较大,查询是浪费大量的磁盘IO,影响查询效率。此时可以只对字符串的一部分前缀建立索引,节约索引空间,提高索引效率。

1)语法:create index idx_xxx on 表名(column(n));

create index idx_email on tb_user(email(5)); # 为表tb_user的 email 字段建立长度为5的前缀索引。

2)前缀长度

可以根据索引的选择性来决定,选择性=不重复的索引值(基数)/数据表的记录总数。

索引选择性越高则查询效率最高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

select count(distinct email)/count(*) from tb_user;

select count(distinct substring(email, 1, 5)/count(*) from tb_user;

3)前缀索引的查询流程

5. 单列索引&联合索引

单列索引:一个索引只包含单个列

联合索引:一个索引包含了多个列

当and 连接的两个字段 phone、name上都有单列索引,mysql最终只会选择一个索引,也就是说只能走一个字段的索引,此时会进行回表查询的。

此时,我们创建一个phone和name字段的联合索引。

create unique index_user_phone_name on tb_user(phone, name);

查询以下执行计划:

此时查询时,走了联合索引,在联合索引中包含phone, name的信息,在叶子节点下挂的是对应的主键id,所以查询不需要回表查询。

⚠️ 所以在业务场景中,若存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。

查询使用联合索引时,具体的结构示意图如下:

6. 索引设计原则

1)针对数据量较大,且查询比较繁琐的表建立索引;

2)针对于常作为查询条件(where),排序(order by),分组(group by)操作的字段,建立索引;

3)尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高;

4)如果是字符串类型的字段,字段的长度过长,可以针对字段的特点,建立前缀索引;

5)尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率;

6)要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率;

7)如果索引列不能存储null值,在创建表时使用not null约束它。当优化器知道每列是否包含null值时,它可以更好地确定哪个索引最有效地用于查询。

SQL优化

1. 主键优化

1.1 主键顺序插入

1. 从磁盘中申请页, 主键顺序插入;

2. 第一个页没有满,继续往第一页插入;

3. 当第一个也写满之后,再写入第二个页,页与页之间会通过指针连接;

4. 当第二页写满了,再往第三页写入;

1.2 主键乱序插入

1.2.1 页分裂

1.假如1#,2#页都已经写满了,存放了如图所示的数据;

2.此时再插入id为50的记录,我们来看看会发生什么现象 会再次开启一个页,写入新的页中吗?

不会。因为,索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后。

3.但是47所在的1#页,已经写满了,存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。

4.但是并不会直接将50存入3#页,而是会将1#页后一半的数据,移动到3#页,然后在3#页,插入50。

5.移动数据,并插入id为50的数据之后,那么此时,这三个页之间的数据顺序是有问题的。 1#的下一个页,应该是3#, 3#的下一个页是2#。 所以,此时,需要重新设置链表指针。 这种现象,称之为 "页分裂",是比较耗费性能的操作。

1.2.2 页合并

1.目前表中已有数据的索引结构(叶子节点)如下:

2.当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。

3.继续删除2#数据,当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

4.删除数据,并将页合并之后,再次插入新的数据21,则直接插入3#页。

这个里面所发生的合并页的这个现象,就称之为 "页合并"。

1.2.3 索引设计原则

满足业务需求的情况下,尽量降低主键的长度;

插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键;

尽量不要使用UUID做主键或者是其他自然主键,如身份证号;

业务操作时,避免对主键的修改。

2. order by优化

MySQL的排序,有两种方式:

对于以上的两种排序方式,Using index的性能高,而Using filesort的性能低,我们在优化排序操作时,尽量要优化为 Using index。

接下来,我们来做一个测试:

A. tb_user表中所建立的部分索引删除掉

B. 执行排序SQL

explain select id,age,phone from tb_user order by age ;

explain select id,age,phone from tb_user order by age, phone ;

由于 age, phone 都没有索引,所以此时再排序时,出现Using filesort,排序性能较低。

C. 创建索引

create index idx_user_age_phone_aa on tb_user(age, phone);

D. 创建索引后,根据age, phone进行升序排序

explain select id,age,phone from tb_user order by age;

explain select id,age,phone from tb_user order by age , phone;

建立索引之后,再次进行排序查询,就由原来的Using filesort, 变为了 Using index,性能 就是比较高的了。

E. 创建索引后,根据age, phone进行降序排序

explain select id,age,phone from tb_user order by age desc , phone desc ;

也出现 Using index, 但是此时Extra中出现了 Backward index scan,这个代表反向扫描索引。因为在Mysql中我们创建的索引,默认索引的叶子节点是从小到大排序的,而此时我们查询排序时,是从大到小,所以,在扫描时,就是反向扫描,就会出现 Backward index scan。在 MySQL8版本中,支持降序索引,我们也可以创建降序索引。

F. 根据phone,age进行升序排序,phone在前,age在后

explain select id,age,phone from tb_user order by phone , age;

排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候, age是第一个字段,phone是第二个字段,所以排序时,也就该按照这个顺序来,否则就会出现 Using filesort。

G. 根据age, phone进行降序一个升序,一个降序

explain select id,age,phone from tb_user order by age asc, phone desc ;

因为创建索引时,未指定顺序,所以默认都是按照升序排序的,

而查询时,一个升序,一个降序,此时就会出现Using filesort。为了解决上述的问题,我们可以创建一个索引,这个联合索引中 age 升序排序,phone 倒序排序。

H. 创建联合索引(age 升序排序,phone 倒序排序)

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

I. 然后再次执行如下SQL

explain select id,age,phone from tb_user order by age asc , phone desc ;

升序/降序联合索引结构图示:

由上述的测试,我们得出order by优化原则:

1)根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则;

2)尽量使用覆盖索引;

3)多字段排序, 一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC);

4)如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

3. group by优化

分组操作,我们主要来看看索引对于分组操作的影响。

步骤一:在没有索引的情况下,执行如下SQL,查询执行计划:

explain select profession , count(*) from tb_user group by profession ;

步骤二:针对 profession ,age,status 创建一个联合索引。

create index idx_user_pro_age_sta on tb_user(profession , age , status);

步骤三:再次执行前面相同的SQL查看执行计划。

explain select profession , count(*) from tb_user group by profession ;

步骤四:执行如下的分组查询SQL,查看执行计划:

explain select profession , count(*) from tb_user group by profession, age ;

explain select profession , count(*) from tb_user group by age ;

我们发现,如果仅仅根据age分组,就会出现 Using temporary ;而如果是根据 profession,age两个字段同时分组,则不会出现Using temporary。原因是对于分组操作, 在联合索引中,也是符合最左前缀法则的。

⚠️ 所以,在分组操作中,我们需要通过以下两点进行优化,以提升性能:

1)在分组操作时,可以通过索引来提高效率;

2)分组操作时,索引的使用也是满足最左前缀法则的。

4. limit优化

在数据量比较大时,如果进行limit分页查询,在查询时,越往后,分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比:

通过测试我们会看到,越往后,分页查询效率越低,这就是分页查询的问题所在:

因为,当在进行分页查询时,如果执行 limit 2000000,10 ,此时需要MySQL排序前2000010 记录,仅仅返回 2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大 。

优化思路: 一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

5. count优化

5.1 概述

在之前的测试中,我们发现,如果数据量很大,在执行count操作时,是非常耗时的。InnoDB 引擎中,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。

如果说要大幅度提升InnoDB表的count效率,主要的优化思路: 自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

5.2 count用法

count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加,最后返回累计值。

用法:count(*)、count(主键)、count(字段)、count(数字)

按照效率排序的话,count(字段) < count(主键 id) < count(1) ≈ count(*),所以尽量使用count(*)。

6. update优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时,会锁定id为1这一行的数据,然后事务提交之后,行锁释放。

当我们开启多个事务,再执行如下SQL时:

update course set name = 'SpringBoot' where name = 'PHP' ;

我们发现行锁升级为了表锁。导致该update语句的性能大大降低。

Innodb的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级成表锁。

   
389 次浏览       20
相关文章

基于EA的数据库建模
数据流建模(EA指南)
“数据湖”:概念、特征、架构与案例
在线商城数据库系统设计 思路+效果
 
相关文档

Greenplum数据库基础培训
MySQL5.1性能优化方案
某电商数据中台架构实践
MySQL高扩展架构设计
相关课程

数据治理、数据架构及数据标准
MongoDB实战课程
并发、大容量、高性能数据库设计与优化
PostgreSQL数据库实战培训

最新活动计划
面向对象业务分析与系统设计 10-16[线上]
嵌入式软件架构设计-高级实践 10-17[线上]
Qlik Sense数据分析技术 10-17线上]
基于 UML 和EA进行分析设计 10-22[北京]
用户研究与用户建模 10-24[北京]
QT应用开发 10-24[北京]
 
 
最新文章
InfluxDB概念和基本操作
InfluxDB TSM存储引擎之数据写入
深度漫谈数据系统架构——Lambda architecture
Lambda架构实践
InfluxDB TSM存储引擎之数据读取
最新课程
Oracle数据库性能优化、架构设计和运行维护
并发、大容量、高性能数据库设计与优化
NoSQL数据库(原理、应用、最佳实践)
企业级Hadoop大数据处理最佳实践
Oracle数据库性能优化最佳实践
成功案例
某金融公司 Mysql集群与性能优化
北京 并发、大容量、高性能数据库设计与优化
知名某信息通信公司 NoSQL缓存数据库技术
北京 oracle数据库SQL优化
中国移动 IaaS云平台-主流数据库及存储技术