mysql 不同版本的优化器差异_mysql性能 - 脚本学堂

php实例 shell脚本 jquery选择器 python模拟登录 linux命令 nginx apache

当前位置：首页 > 数据库教程 > mysql > mysql性能 > 正文

mysql 不同版本的优化器差异: 发布时间：2020-07-07编辑：脚本学堂

mysql 版本带来的优化器差异

昨天在日志中发现一个慢查询的语句：

SELECT sql_no_cache * FROM `news`.`v9_news` WHERE `catid` = '15' AND `status`=99 AND `id`<'201846' ORDER BY id DEsc ；

explain 后

+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+
| 1 | SIMPLE | v9_news | ref | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3 | const,const | 56502 | Using where |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+

把catid和status加到order by

mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15' AND `status`=99 AND `id`<'201846' ORDER BY catid,status,id DEsc limit 1;
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+
| id | select_type | table   | type | possible_keys                               | key                 | key_len | ref         | rows | Extra                       |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+
| 1 | SIMPLE      | v9_news | ref | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 56502 | Using where; Using filesort |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+

虽然有filesort但是这样执行的速度快很多，
###
原来是catid 和id 都是smallint 和int，是以数值定义的，但是''包含的是属于字符串类型，所以会有filesort的出现：

mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news` WHERE `catid` = 15 AND `status`=99   ORDER BY catid,status,id limit 1;
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
| id | select_type | table   | type | possible_keys                       | key                 | key_len | ref         | rows   | Extra       |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
| 1 | SIMPLE      | v9_news | ref | catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 254418 | Using where |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news` WHERE `catid` = '15' AND `status`=99   ORDER BY catid,status,id limit 1;
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-----------------------------+
| id | select_type | table   | type | possible_keys                       | key                 | key_len | ref         | rows   | Extra                       |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-----------------------------+
| 1 | SIMPLE      | v9_news | ref | catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 254418 | Using where; Using filesort |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-----------------------------+

####
和其他人讨论了下，是因为虽然选择idx_catid_status_id 这个索引，但是根本没用这个索引中的id这个键，这点从key_len 为3 可以看出来，

把语句改成：

mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news` force index (idx_catid_status_id) WHERE `catid` = '15' AND `status`=99 AND `id`<'201846' ORDER BY id DEsc limit 1;
+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | v9_news | range | idx_catid_status_id | idx_catid_status_id | 6 | NULL | 56502 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+

会发现强制带上id这个键，
原来优化器算法错了，这个是在5.5.9上去得到的结果

又安装新版本的5.5.28后发现

mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15' AND `status`=99 AND `id`<'201846' ORDER BY id DEsc limit 1;
+----+-------------+---------+-------+---------------------------------------------+---------------------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys                               | key                 | key_len | ref | rows | Extra       |
+----+-------------+---------+-------+---------------------------------------------+---------------------+---------+------+-------+-------------+
| 1 | SIMPLE      | v9_news | range | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 6       | NULL | 56548 | Using where |
+----+-------------+---------+-------+---------------------------------------------+---------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version() |
+------------+
| 5.5.28-log |
+------------+
1 row in set (0.00 sec)

总结：优化器可以自动识别，看来新版本的优化器的算法是经过改良的

上一篇：返回列表
下一篇：使用super-smack测试mysql数据库性能

与 mysql 不同版本的优化器差异有关的文章

本文标题：mysql 不同版本的优化器差异
本页链接：http://www.jb200.com/db/2578.html

浏览排行

栏目分类

热点文章