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

柔情只为你懂 2023-01-02 01:21 161阅读 0赞

点击上方蓝色“java大数据修炼之道”, 选择“设为星标”

每晚九点: 技术干货 ???? 必定奉上![format_png][]![format_png 1][]

> 转自：简书   作者：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更小，这样的话，一页中可以扫描更多行数。

搜索Java大数据修炼之道公众号，回复“PDF”，送你一份Java面试题宝典.pdf

### **2.2，解决方案** ###

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

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

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

#### **2.2.1 回表** ####

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

![format_png 2][]

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、Oracle数据库系列全套 完整版视频讲解资源 , 感谢支持 !)

#### ![format_png 3][]  推荐阅读： ####

1、[最新SQL数据库面试题以及答案！][SQL]

[2、『精华版』][2][程序员需知的 58 个网站][58]

3、[SpringCloud网上商城系统（附源码及教程）][SpringCloud]

4、[面试官：来说说单点登录的三种实现方式][Link 1]

5[、][Link 2][Spring事务的那些坑，这里都给你总结好了！][Spring]

6、[面试官：Mybatis里的设计模式有哪些？脱口而出8种][Mybatis_8]

#### **- END -**     最近整理一份资料**《程序员学习手册》**，覆盖了 Java技术、面试题精选、操作系统基础知识、计算机基础知识、Linux教程、计算机网络等等。  获取方式：点“ **在看**，关注公众号 **Java大数据修炼之道** 并回复 **PDF** 领取，更多内容陆续奉上。 ####

长按识别下方二维码关注后回复关键字: **PDF** 领取

####  ####

关注 java大数据修炼之道

每天学习java技术，你想学的Java知识这里都有!**![format_png 4][]**

![format_png 5][]微信扫描二维码，关注我的公众号

写留言

好文章、留言、点赞、 在看 ![format_png 6][]和分享一条龙吧 ![format_png 7][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy80dlpIaWI2a0RxaGV5dHNqMkFmMnhXWVZpYnlYRE1rbW5aZkNYb2liM2lhUWtTcGxmUGJRdWRXdGFONWwxTzUyc3JWYlZaV2pRWWN0UXl1NXNMSkpmZHBPaWJnLzY0MA?x-oss-process=image/format,png
[format_png 1]: /images/20221120/ca7a55c5d34548ebb44f8497822a72b4.png
[format_png 2]: /images/20221120/23e64255b2fc464598b32300e20c4047.png
[format_png 3]: /images/20221120/19be679b465a4d5fa3db302effa53ce0.png
[SQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc229c6ccb5a0d068fdce76d8e457510c9c43e866952a917ae63ee0f1da8c5c25bd5d3879a2&idx=1&mid=2247493386&scene=21&sn=6014e7fb6c2a43ca8df3b011754fa4e1#wechat_redirect
[2]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22dc7ccb5a4d1b94f2a9bbce26082c9f845fbbf7d588a5540d072bcef39b2281d0b705f91&idx=1&mid=2247492363&scene=21&sn=6975a1a5de92dc49d04654f21ffa7c21#wechat_redirect
[58]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc23f8bccb5b69d3606c7c10b2468e6e3ff494cead2301993b3cc524978bba1adbaa4d0fab8&idx=1&mid=2247496007&scene=21&sn=b77e331b8f58a6a45a66789da9bf686c#wechat_redirect
[SpringCloud]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc2236eccb5aa78ff94c055aceefae33094642c060e4b152f38aa4ac79f85fe1d27f0d3f227&idx=1&mid=2247495074&scene=21&sn=a3c0acade7e4cc8efb55e01988896cbc#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc229b8ccb5a0ae5f433e40aeb031d462bc825b837ca9e09720b6b58439dada694eeb54bbaf&idx=1&mid=2247493492&scene=21&sn=cd0a9b65387d5107fe9d0e1ded268d22#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22c17ccb5a501df41ddeaecf0f7e85e292b45d6c7522e4d6a4d9c4a93a389338ee175eae8&idx=1&mid=2247492315&scene=21&sn=37c6888e30892c7fae56d0769c35a571#wechat_redirect
[Spring]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc2220bccb5ab1d8756b893720a2abd4b4db36489bd729309859f3ec9eba3cafd99e7ed689d&idx=1&mid=2247494855&scene=21&sn=222b32da80b9852d826bd3fb9aa9cf8c#wechat_redirect
[Mybatis_8]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22080ccb5a9960520c1a593c2dced582b39197e65beb7070903dc47704d45fd6f024ba417&idx=1&mid=2247495244&scene=21&sn=692508a045cfb8cadea3bf809c2892c8#wechat_redirect
[format_png 4]: /images/20221120/bf62a3f810e04050ae2f78a2dd8a8689.png
[format_png 5]: /images/20221120/e18d430c7c714d2691d4ca6ae5c5c155.png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9DMXVETURxam4xOXd0dVFwSzNqbUpXM2JGR1dJOFl6NkZSMTd0bDFNRjhWZnFZeFB4OTkwa3YySjc0THZxd2liMjZLYXlIZE9YZDZlYnpycVlpYmJUand3LzY0MA?x-oss-process=image/format,png
[format_png 7]: /images/20221120/b86bb04c27114aa9822e42ba2d211d04.png