最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

mySql索引优化实战操作之记一次Sql记录其优化历程

XAMPP相关 admin 447浏览 0评论

zzzzzt43

查看系统性能监控,发现有十多条慢查询,决定将其优化。挑选其中一条典型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中的两个基础概念:聚簇索引,普通索引。
ex000081
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,这就表示本次查询用到了覆盖索引,一般效率较高(基本达成三星索引的标准)。

至此,优化就结束了。

转载请注明:XAMPP中文组官网 » mySql索引优化实战操作之记一次Sql记录其优化历程

您必须 登录 才能发表评论!