昨天看到慢查询里面
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)
优化器可以自动识别,看来新版本的优化器的算法是经过改良的
阅读(2517) | 评论(0) | 转发(1) |