一次关于 Mysql 索引优化的思考

ImportNew 2021-12-02

（给ImportNew加星标，提高Java技能）

转自：简书作者：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，这就表示本次查询用到了覆盖索引，一般效率较高(基本达成三星索引的标准)。

至此，优化就结束了。

推荐阅读点击标题可跳转

为什么数据库会丢失数据？

Mysql百万量级数据高效导入Redis

如果是 MySQL 引起的 CPU 消耗过大，你会如何优化？

看完本文有收获？请转发分享给更多人

关注「ImportNew」，提升Java技能

好文章，我在看❤️

: ， . Video Mini Program Like ，轻点两下取消赞 Wow ，轻点两下取消在看

李尚福、魏凤和双双被拿下，与美国一份报告是否有关？

圈内疯传某谣言

不要放过这些人渣

“辣条二哥”直追卫龙，麻辣王子工厂遇洪受关注

中金跳楼女生诡异之处。

一次关于 Mysql 索引优化的思考

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

1.概述

2.优化历程

2.1，定位问题

2.2，解决方案

2.2.1 回表

2.2.2 覆盖索引

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

李尚福、魏凤和双双被拿下，与美国一份报告是否有关？

圈内疯传某谣言

不要放过这些人渣

“辣条二哥”直追卫龙，麻辣王子工厂遇洪受关注

中金跳楼女生诡异之处。

生成图片，分享到微信朋友圈

一次关于 Mysql 索引优化的思考

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

1.概述

2.优化历程

2.1，定位问题

2.2，解决方案

2.2.1 回表

2.2.2 覆盖索引

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