查看原文
其他

一次线上Mysql 索引优化的思考,奇怪的知识又增加了~

不才陈某 码猿技术专栏 2021-11-29


点击上方☝码猿技术专栏 轻松关注!

及时获取有趣有料的技术文章

转自:简书   作者:Fooisart

www.jianshu.com/p/efb7881f18b2

查看系统性能监控,发现有十多条慢查询,决定将其优化。挑选其中一条典型Sql记录其优化历程。


1.概述


在下文的查询优化中,主要围绕的问题:Mysql为何会选错索引?

2.优化历程


2.1,定位问题


为了便于描述,贴出建表语句(表中数据约有400万行):
MySQL > show create table demo_table\G
*************************** 1\. row ***************************
Table: demo_table
Create Table: CREATE TABLE `demo_table` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` varchar(100) NOT NULL DEFAULT '' COMMENT '用户ID',
`source_channel` int(11) NOT NULL DEFAULT '0' COMMENT '来源',
`source_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '来源ID',
`category_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '分类ID',
`classify_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '归类时间',
`content_id` bigint(20) DEFAULT NULL COMMENT '语义模板ID',
PRIMARY KEY (`id`),
KEY `idx_category_id` (`category_id`),
KEY `idx_classify_time` (`classify_time`),
KEY `idx_channel_source_id` (`source_channel`,`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3081571 DEFAULT CHARSET=utf8mb4


业务查询SQL-1(统计在近七天内每条content_id在表中出现的次数):
select content_id, count(1) as c from demo_table where source_channel = 2
and classify_time between 1556019882000 and 1556624682000
and content_id is not null group by content_id;


执行SQL-1,显示耗时 9.35sec。显然是不乐观的一个值,查看其执行计划(explain): 

id: 1
select_type: SIMPLE
table: demo_table
partitions: NULL
type: ref
possible_keys: idx_classify_time,idx_channel_source_id
key: idx_channel_source_id
key_len: 4
ref: const
rows: 1487434
filtered: 42.67
Extra: Using where; Using temporary; Using filesort


从执行计划中,可以看出,用到了索引idx_channel_source_id。而source_channel是个区分度很低的字段,当得知Mysql优化器选择了idx_channel_source_id,个人感觉是比较吃惊的。idx_classify_time是区分度更好的索引却没有被选中?

强制使用idx_classify_time,验证是否会执行效率更高,SQL-2:
select content_id, count(1) as c from demo_table force index(idx_classify_time)
where source_channel = 2 and classify_time between 1556019882000
and 1556624682000 and content_id is not null group by content_id;


果不其然,执行时间为:2.19 sec。那么这里开始疑惑了,Mysql优化器为何会选错索引?继续探究。

Mysql优化器会根据 ①扫描行数、②是否使用临时表、③是否排序等因素进行综合判断。

贴出SQL-1、SQL-2的执行计划:
SQL-1
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: demo_table
partitions: NULL
type: ref
possible_keys: idx_classify_time,idx_channel_source_id
key: idx_channel_source_id
key_len: 4
ref: const
rows: 1487434
filtered: 42.67
Extra: Using where; Using temporary; Using filesort

SQL-2
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: demo_table
partitions: NULL
type: range
possible_keys: idx_classify_time
key: idx_classify_time
key_len: 8
ref: NULL
rows: 1410438
filtered: 45.00
Extra: Using where; Using temporary; Using filesort


从执行计划的Extra参数中,可以看出 ②是否使用临时表、③是否排序 这两个因素完全一样,再看扫描行数,idx_classify_time是141万行,idx_channel_source_id是148万行。明显是idx_classify_time更少,为何没有选它呢?

其实这里,优化器认为他们俩的行数是差不多的,没有本质的差别。而在执行计划中,有个参数确差别很大:type。

type最重要且经常遇见的六种类型:all, index, range, ref, eq_ref,const。从左到右,它们的效率依次是增强的,所以优化器根据 type类型更倾向于idx_channel_source_id。而且idx_channel_source_id的 key_len更小,这样的话,一页中可以扫描更多行数。

2.2,解决方案


既然Mysql优化器选错了索引,我们如何去纠正它呢?

第一种方式:使用SQL-2中的方式,在应用程序中显示选择索引。由于索引可能会变更,名称更改或者索引删除等,这样明显会影响应用程序的可用性。

第二种方式:创建更合适的索引。

2.2.1 回表


在介绍如何创建更合适索引之前,先简单介绍Mysql中的两个基础概念:聚簇索引,普通索引。


image-1为聚簇索引(clustered index),B+树的节点存放的是每一行记录;image-2为普通索引(secondary index),B+树的节点存放的是其对应的主键ID。

使用索引查询具体执行流程:

聚簇索引:如果我是根据主键id查询某个值,只需要查询主键索引树即可获取内容行R;

普通索引:第一次查询普通索引树,拿到字段ID,然后拿着ID值去主键索引树再次查找内容行R。

从查询流程可以看出,使用普通索引需要多扫描一次索引树。而这个过程,称为 回表。

2.2.2 覆盖索引


那么如果能够减少回表的次数,会很大程度地提升性能,这里就用到了联合索引。将需要用到的字段,建立成一个联合索引,那么这样就无需再次回表。这样也就用到了覆盖索引,效率更高。

为了优化SQL-1,创建的索引语句如下:
alter table `demo_table`add index
idx_content_id_calssify_time_source_channel(content_id,classify_time,source_channel);

再次执行SQL-1,显示耗时 0.02sec,性能成飞跃式提升。查看SQL-1执行计划:
id: 1
select_type: SIMPLE
table: demo_table
partitions: NULL
type: range
possible_keys:idx_classify_time,idx_channel_source_id,idx_channel_classify_time_content_id
key: idx_channel_classify_time_content_id
key_len: 21
ref: NULL
rows: 1788506
filtered: 50.00
Extra: Using where; Using index; Using temporary; Using filesort


从执行计划中,看Extra比之前多了个Using index,这就表示本次查询用到了覆盖索引,一般效率较高(基本达成三星索引的标准)。


至此,优化就结束了。


另外,作者已经完成了两个专栏的文章Mybatis进阶Spring Boot 进阶 ,已经将专栏文章整理成书,有需要的公众号回复关键词Mybatis 进阶Spring Boot 进阶免费获取。



往期推荐



SQL优化极简法则,还有谁不会?

SQL:事务你都阳性了,赶紧隔离吧~

面试官:MyBatis的SQL执行流程说这么详细,网上抄的吧!

SQL优化最干货总结-MySQL(2020最新版)

这篇 MySQL 索引和 B+Tree 讲的太通俗易懂!

太厉害了!推荐几款 Redis 可视化工具



点个赞+在看,少个 bug 👇
: . Video Mini Program Like ,轻点两下取消赞 Wow ,轻点两下取消在看

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存