Jquery中文網 www.afdoqc.live
Jquery中文網 >  數據庫  >  mysql  >  正文 mysql 不同版本的優化器差異

mysql 不同版本的優化器差異

發布時間:2015-09-23   編輯:www.afdoqc.live
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)

總結:優化器可以自動識別,看來新版本的優化器的算法是經過改良的

您可能感興趣的文章:
mysql 不同版本的優化器差異
Amoeba:開源的分布式數據庫Porxy解決方案
六款幫助你實現驚艷視差滾動效果的jQuery插件
mysql開啟慢查詢以檢查查詢慢的語句
探討js字符串數組拼接的性能問題
PHP程序員最容易犯的11個MySQL錯誤
windows下mysql主從復制配置
MySQL集群億級數據量并發訪問解決方案測試
asp.net性能優化方法-其它
javascript 鼠標按鈕button屬性

[關閉]
英格兰足球队