MySQL5.6：一个神奇的执行计划

最新推荐文章于 2024-07-19 00:12:38 发布
最新推荐文章于 2024-07-19 00:12:38 发布
阅读量114 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/71651
版权

今天遇到一个非常神奇的sql执行计划时好时坏，我们一起来领略一下吧

废话不多说，直接进入实战

环境

* version：MySQL5.6.27 社区版

* 表结构

CREATE TABLE `xx` (
  `TagId` int(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `TagType` int(11) DEFAULT NULL COMMENT '',
  `SubType` int(11) DEFAULT NULL COMMENT '',
  `CommId` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `TagFlag` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `TagName` varchar(255) DEFAULT NULL COMMENT '',
  `OrderId` int(11) DEFAULT '0' COMMENT '',
  `Unum` int(10) NOT NULL DEFAULT '0' COMMENT '',
  `IsBest` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `BrokerId` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `AddDate` int(11) DEFAULT NULL COMMENT '',
  `UpdateDate` int(11) DEFAULT NULL COMMENT '',
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tmpnum` int(10) DEFAULT '0' COMMENT '',
  `cityid` int(11) DEFAULT '0' COMMENT '',
  PRIMARY KEY (`TagId`),
  KEY `idx_4` (`IsBest`,`TagFlag`,`CommId`),
  KEY `idxnew` (`UpdateDate`),
  KEY `idx_lc_1` (`TagName`,`TagType`,`TagId`),
  KEY `idx_lc_2` (`CommId`,`TagName`,`TagType`),
  KEY `idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`),
  KEY `idx_lc_3` (`SubType`,`TagType`,`cityid`),
) ENGINE=InnoDB AUTO_INCREMENT=20628140 DEFAULT CHARSET=utf8

DB症状

1. slow query 非常多
2. thread_running 非常多
3. cpu 90%
4. too many connection

多症齐发

定位问题

很明显就是去寻找slow query，毕竟slow是我衡量DB性能重要标准。

然后发现99%都是类似这样的语句：

# Time: 170304 10:32:07
# User@Host[] @  []  Id: 26019853
# Query_time: 0.251174  Lock_time: 0.000078 Rows_sent: 1  Rows_examined: 470135
SET timestamp=1488594727;
select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = '1'  and `TagName` = '**高'  order by `TagId` ASC limit 1 ;

分析问题

  • step1：查看执行计划
explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = '1'  and `TagName` ='**高'  order by `TagId`  limit 1;
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys                        | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | xx | index | idx_lc_1,idx_tagName_brokerId_cityId | PRIMARY | 4       | NULL |  175 | Using where |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


这条语句执行时间是： 0.99s


奇怪，从表结构上看，应该会使用idx_lc_1才对，为什么执行计划是错的呢？
  • step2：第二反应
会不会是TagType是int类型，但是sql语句确实字符串呢？隐士类型转换的导致的执行计划出错之前也是碰到过的。
试试吧，

explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='**高'  order by `TagId`  limit 1;
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys                        | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | xx | index | idx_lc_1,idx_tagName_brokerId_cityId | PRIMARY | 4       | NULL |  175 | Using where |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


这条语句执行时间是： 0.89s

还是非常缓慢，看来不是这个原因。
  • step3：会不会是数据的问题呢？
因为从slow的分布看，基本上都是`TagName` ='**高' 的slow，其他的值也没发现，所以开始怀疑value，调整下看看呢

explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='%%高'  order by `TagId`  limit 1;
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys                        | key      | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_lc_1 | 773     | const,const |    3 | Using index condition; Using where |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+

这条语句执行时间：0.00s

哇塞，0s就解决战斗，但是这又是为什么呢？


再试一下：将‘**’高，换成‘*高’

explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='*高'  order by `TagId`  limit 1;
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys                        | key      | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_lc_1 | 773     | const,const |    3 | Using index condition; Using where |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+

执行计划也正确，执行时间也非常快。

然后笃定的认为问题找到了，竟然是 ‘**’导致的。

当我自己给自己sleep 10s 之后，开始思考，这是为什么呢？ 等值匹配跟*有关系吗？
  • step4: 再次调整语句
* 去掉limit呢？ 因为limit是执行计划的杀手，这个我想大部分DBA知道的吧。。。

explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='*高'  order by `TagId`  ;
+----+-------------+-----------------+------+--------------------------------------+-----------------------------+---------+-------+-------+----------------------------------
------------------+
| id | select_type | table           | type | possible_keys                        | key                         | key_len | ref   | rows  | Extra
                  |
+----+-------------+-----------------+------+--------------------------------------+-----------------------------+---------+-------+-------+----------------------------------
------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_tagName_brokerId_cityId | 768     | const | 13854 | Using index condition; Using wher
e; Using filesort |
+----+-------------+-----------------+------+--------------------------------------+-----------------------------+---------+-------+-------+----------------------------------
------------------+


惊奇的发现，执行计划再次发生了改变。。。。

idx_tagName_brokerId_cityId 为什么又冒出来了呢？



那我们再回头看看表结构：

PRIMARY KEY (`TagId`),
  KEY `idx_4` (`IsBest`,`TagFlag`,`CommId`),
  KEY `idxnew` (`UpdateDate`),
  KEY `idx_lc_1` (`TagName`,`TagType`,`TagId`),
  KEY `idx_lc_2` (`CommId`,`TagName`,`TagType`),
  KEY `idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`),
  KEY `idx_lc_3` (`SubType`,`TagType`,`cityid`)

去掉干扰项后：

PRIMARY KEY (`TagId`),
`idx_lc_1` (`TagName`,`TagType`,`TagId`),
`idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`),

执行计划竟然没有选择idx_lc_1，而是idx_tagName_brokerId_cityId，那么这个肯定是干扰索引。

所以，就更加清晰的定位到idx_tagName_brokerId_cityId索引的问题，然后开始调整这个索引，主要是第一个字段TagName的干扰，选择性的问题。

将：  KEY `idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`) =>  KEY `idx_tagName_brokerId_cityId` (`BrokerId`,`TagName`,`cityid`)
  • step 5: 再次观察执行计划

explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='**高'  order by `TagId`  limit 1;
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys                        | key      | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_lc_1 | 773     | const,const |    3 | Using index condition; Using where |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+


sql执行时间：0.00s

总结

  • 至此，问题已经解决，第一个前缀索引是如此的重要。
  • 索引调优是门艺术

展望

  • 以后如何调整和优化类似的索引执行计划呢？
原则： 高索引基数的filed，必须放前面。
  • 希望MySQL的优化器以后越来越强大
weixin_34419326
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql索引
weixin_42906466的博客
09-07 1172
为什么要使用索引 1.可以加快数据的检索速度（大大减少检索的数据量，这也是使用索引的主要原因）。 2.帮助服务器避免排序和临时表 3.将随机IO变为顺序IO 使用索引要注意的事项 1.在经常需要搜索的列上创建，可以加快搜索速度。 2.在经常使用where子句中的列上面创建，加快条件的判断速度。 3.在经常需要排序的列上面创建，因为索引已经排序，这样可以直接使用索引的排序，加快排序查询时间。 4.对于中到大型表创建索引都是非常有效的，但对于超大型表创建索引成本较大，不建议使用。 5.在经常用在连接的列上创建索
初步理解MySQL（5.6）的执行计划
weixin_34219944的博客
09-24 88
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql5.6 执行计划相关文章
qyvlik的专栏
04-26 161
查看 mysql 5.6 的执行计划 优先看 type 字段，type 的效率从上到下性能越好，如下 ALL: 全表扫描 index: 索引全扫描 range: 索引范围扫描 ref: 非唯一索引扫描 eq_ref: 唯一索引扫描 const,system: 单表最多有一个匹配行 NULL: 不用扫描表或索引 接着再看 rows，预计扫描的行数，越少越好。 最后再看看 Extra，有出现 Us...
mysql 执行计划 order_MySQL · 捉虫动态 · 5.6中ORDER BY + LIMIT 错选执行计划
weixin_30418751的博客
01-31 178
问题描述create table t1(id int auto_increment primary key, a int, b int, c int, v varchar(1000), key iabc(a,b,c), key ic(c)) engine = innodb;insert into t1 select null,null,null,null,null;insert into t1 s...
MySQL 5.6 Reference Manual MySQL 5.6 Reference Manual
04-09
MySQL 5.6 Reference Manual MySQL 5.6 Reference Manual MySQL 5.6 Reference Manual MySQL 5.6 Reference Manual MySQL 5.6 Reference Manual MySQL 5.6 Reference Manual MySQL 5.6 Reference Manual ...
mysql5.6版本.zip
03-19
本压缩包文件"mysql5.6版本.zip"包含的是MySQL 5.6的安装程序"mysql-installer-community-5.6.40.1.msi"，这是一个社区版的安装程序，适用于个人学习和非商业用途。 MySQL 5.6版本的主要特性包括： 1. **性能提升*...
简化版MySQL5.6.51解压即用
08-02
2021年1月20日，MySQL5.6发布了最后一个维护版本——5.6.51，从此，MySQL正式告别了5.6时代。 MySQL5.6于2013年正式发布（GA），它是MySQL一个非常重要的里程碑。 它在InnoDB，分区，Performance Schema，优化器和...
MySQL5.6安装及卸载
06-17
MySQL 5.6是MySQL数据库管理系统的一个重要版本，它提供了许多功能增强和性能优化，适合于各种规模的应用场景。在本篇文章中，我们将详细探讨如何安装和卸载MySQL 5.6。 首先，我们来讨论MySQL 5.6的安装过程： 1....
mysql5.6安装包
02-05
MySQL 5.6 是 MySQL 数据库的一个重要版本，它提供了许多增强的功能和性能优化，适合于各种规模的企业级应用。本教程将详细讲解如何在Linux系统上安装MySQL 5.6，以`MySQL-server-5.6.47-1.el7.x86_64.rpm`和`MySQL-...
解决DBeaver无法查看MySQL执行计划问题
choucangcheng2245的博客
09-06 7362
解决DBeaver无法查看MySQL执行计划问题 DBeaver这个数据库连接工具的确很好用，但是最近在使用时发现它不能查看MySQL的执行计划 explain execute select * from table; explain select * from table; /**点 ...
初步了解mysql_初步理解MySQL（5.6）的执行计划
weixin_36323390的博客
01-30 83
声明：以下均来自于MySQL英文手册5.6，本文主要针对执行计划，由于本人才疏学浅，其他部分可能会一句话带过。引言.什么是执行计划：The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan”, also known ...
MySQL 5.6 执行计划错误案例分析
weixin_34116110的博客
08-19 352
前提 Mysql 优化器本就是为了优化SQL语句的查找路径而存在，当优化器足够智能的时候，这是一件美事。但是，如果优化器犯二的时候呢？有的时候执行计划看上去非常好，但是慢的无可救药。有的时候执行计划看上去很差，却跑的很欢。 接下来我们一起来看一下下面的例子： 表结构 CREATE TABLE `test_table` ( `id` bigint(...
mysql 之 执行计划
qq_29257691的博客
12-27 480
EXPLAIN SELECT * FROM `sjt_doctor` sd LEFT JOIN `sjt_doctor_bank_card` sb ON sb.`doctor_id`=sd.`id` WHERE sd.id=250 1.添加PRIMARY KEY（主键索引） mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `c...
MySQL（事务、索引）&&MyBatis
最新发布
hycccccch的博客
07-19 466
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
【星海随笔】vCenter Server 和主机管理。
weixin_41997073的博客
07-16 206
1.方法：删除页面信息，然后断连这个受管主机，断开受管主机的连接不会将其从 vCenter Server 中移除，而只是临时挂起 vCenter Server 执行的所有监控活动。2.方法：在分布式存储中找到该虚拟设备的存储盘，文件里找到相关的vmdk磁盘文件，点击注册虚拟机。当资料不匹配时候，可以删除展示页面，孤立的设备的 匹配位置的信息。断开后，然后重新连接，既可以重新识别受监管的主机。数据库在Vserver中展示的是一个数据库的资料，应该是client的资料。1.确定为资料不匹配导致的展示问题。
Spring框架之DI依赖注入
G81948577的博客
07-18 581
我们在下面构建spring的过程中体会依赖注入；从上面的图中我们知道，在ssm框架中服务层（server）无法直接操作数据库，持久层（dao）是直接操作数据库进行数据处理的，但是我们如果在服务层有一个持久层的对象，然后我们在服务层通过对象去调用持久层的方法就可以操作数据库了。@OverrideSystem.out.println("持久层，你好");UserServiceImpl类中的代码如下@Override。
深入 Dify 源码，洞察 Dify RAG 核心机制
易迟的专栏
07-17 1121
之前深入源码对 Dify 的完整流程进行了解读，基本上梳理了 Dify 的实现流程与主要组件。但是在实际部署之后，发现 Dify 现有的 RAG 检索效果没有那么理想。因此个人结合前端页面，配置信息与实现流程，深入查看了私有化部署的 Dify 的技术细节。将核心内容整理在这边，方便大家根据实际的业务场景调整 Dify 知识库的配置，或者根据需要进行二次开发调优。
day04：Redis和店铺营业状态设置
m0_69266818的博客
07-15 865
介绍了Redis命令和用java操作redis还有营业额状态接口的书写
理解MySQL执行计划：从语法到执行
1. 了解MySQL查询的四个阶段：语法分析、生成逻辑查询执行计划、生成物理查询执行计划和执行查询计划。 2. 学会如何使用`EXPLAIN`命令显示执行计划。 3. 掌握如何解析和理解执行计划的结果。 ### MYSQL查询过程 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值