mysql 优化器不准_mysql 版本带来的优化器差异

昨天看到慢查询里面

SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15'  AND `status`=99 AND `id`

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`+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+

| 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`+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+

| 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`+----+-------------+---------+-------+---------------------------------------------+---------------------+---------+------+-------+-------------+

| 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)

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

阅读(1473) | 评论(0) | 转发(1) |