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

最新推荐文章于 2023-11-15 14:10:28 发布

weixin_39634900

最新推荐文章于 2023-11-15 14:10:28 发布

阅读量36

点赞数

文章标签： mysql 索引 importnew

本文链接：https://blog.csdn.net/weixin_39634900/article/details/113908165

版权

(给ImportNew加星标，提高Java技能)

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

至此，优化就结束了。