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

ImportNew

（给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技能

好文章，我在看❤️