mysql 不同版本的优化器差异
昨天在日志中发现一个慢查询的语句：
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
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+
| 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 可以看出来，
把语句改成：
+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+
| 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)
总结：优化器可以自动识别，看来新版本的优化器的算法是经过改良的
您可能感兴趣的文章：
mysql 不同版本的优化器差异
mysql数据库sql优化原则
mysql性能优化之sql优化
影响MySQL性能的查询类型有哪些
高性能mysql(第二版)之查询性能优化
mysql 索引优化之btree、hash与rtree
mysql数据库性能优化技巧
php的集成开发环境哪个好
mysql索引的建立原则
mysql优化之怎么使用sql profiler 性能分析器