MySQL5.6執行計劃錯誤案例分析

蘭春發表於2016-08-19

前提

Mysql 優化器本就是為了優化SQL語句的查詢路徑而存在，當優化器足夠智慧的時候，這是一件美事。但是，如果優化器犯二的時候呢？有的時候執行計劃看上去非常好，但是慢的無可救藥。有的時候執行計劃看上去很差，卻跑的很歡。接下來我們一起來看一下下面的例子：

表結構

CREATE TABLE `test_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `brokerid` int(10) NOT NULL COMMENT ``,
  `groupid` int(10) NOT NULL COMMENT ``,
  `cid` int(10) NOT NULL COMMENT ``,
  `gid` int(10) NOT NULL COMMENT ``,
  `fix_prop_num` mediumint(6) NOT NULL COMMENT ``,
  `more_10hours_num` mediumint(6) NOT NULL COMMENT ``,
  `new_add_num` mediumint(6) NOT NULL COMMENT ``,
  `multi_map_num` mediumint(6) NOT NULL COMMENT ``,
  `fix_clicks` mediumint(6) NOT NULL COMMENT ``,
  `fix_consume` float(8,2) NOT NULL COMMENT ``,
  `bid_prop_num` mediumint(6) NOT NULL COMMENT ``,
  `bid_clicks` mediumint(6) NOT NULL COMMENT ``,
  `bid_consume` float(8,2) NOT NULL COMMENT ``,
  `report_date` int(6) NOT NULL COMMENT ``,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ``,
  `cst_broker_company_ids` int(10) NOT NULL DEFAULT `0` COMMENT ``,
  `new_fix_multi_num` mediumint(6) NOT NULL DEFAULT `0` COMMENT ``,
  `new_bid_num` mediumint(6) NOT NULL DEFAULT `0` COMMENT ``,
  `bid_multi_num` mediumint(6) NOT NULL DEFAULT `0` COMMENT ``,
  `new_bid_multi_num` mediumint(6) NOT NULL DEFAULT `0` COMMENT ``,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`,`report_date`),
  KEY `gid` (`gid`,`report_date`),
  KEY `report_date` (`report_date`),
  KEY `brokerid` (`brokerid`,`report_date`),
  KEY `cst_date` (`cst_broker_company_ids`,`report_date`)
) ENGINE=InnoDB AUTO_INCREMENT=57230309 DEFAULT CHARSET=utf8 COMMENT=`test_table`

total rows

dbadmin:abc> select count(*) from test_table;
+----------+
| count(*) |
+----------+
| 52023757 |
+----------+
1 row in set (14.04 sec)

index

dbadmin:abc> show index from test_table;
+---------------------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+--------------
-+
| Table               | Non_unique | Key_name     | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
 |
+---------------------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+--------------
-+
| test_table |          0 | PRIMARY      |            1 | id                     | A         |    51696652 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cid          |            1 | cid                    | A         |       47341 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cid          |            2 | report_date            | A         |     4308054 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | gid          |            1 | gid                    | A         |       39016 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | gid          |            2 | report_date            | A         |     6462081 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | report_date  |            1 | report_date            | A         |      106591 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cst_date     |            1 | cst_broker_company_ids | A         |      181391 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cst_date     |            2 | report_date            | A         |    25848326 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | idx_brokerid |            1 | brokerid               | A         |      555877 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | idx_brokerid |            2 | report_date            | A         |    51696652 |     NULL | NULL   |      | BTREE      |         |
 |
+---------------------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+--------------
-+
10 rows in set (0.00 sec)

問題1

SQL 1

dbadmin:abc> explain select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+-------------------------------+----------+---------+------+----------+-------------+
| id | select_type | table               | type  | possible_keys                 | key      | key_len | ref  | rows     | Extra       |
+----+-------------+---------------------+-------+-------------------------------+----------+---------+------+----------+-------------+
|  1 | SIMPLE      | test_table | index | report_date,brokerid,cst_date | brokerid | 8       | NULL | 51696652 | Using where |
+----+-------------+---------------------+-------+-------------------------------+----------+---------+------+----------+-------------+
1 row in set (0.00 sec)

dbadmin:abc> show status like `Han%`;
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Handler_commit             | 1        |
| Handler_delete             | 0        |
| Handler_discover           | 0        |
| Handler_external_lock      | 2        |
| Handler_mrr_init           | 0        |
| Handler_prepare            | 0        |
| Handler_read_first         | 0        |
| Handler_read_key           | 1        |
| Handler_read_last          | 1        |
| Handler_read_next          | 0        |
| Handler_read_prev          | 45189200 |  --all index scan
| Handler_read_rnd           | 0        |
| Handler_read_rnd_next      | 1        |
| Handler_rollback           | 0        |
| Handler_savepoint          | 0        |
| Handler_savepoint_rollback | 0        |
| Handler_update             | 0        |
| Handler_write              | 0        |
+----------------------------+----------+
18 rows in set (0.00 sec)

執行時間：15 rows in set (5 min 36.12 sec)

SQL 2

dbadmin:abc> explain select distinct  `brokerid`  from `test_table` force index(brokerid) where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15
    -> ;
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | test_table | index | brokerid      | brokerid | 8       | NULL | 3300 | Using where |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)


dbadmin:abc> show status like `Han%`;
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Handler_commit             | 1        |
| Handler_delete             | 0        |
| Handler_discover           | 0        |
| Handler_external_lock      | 2        |
| Handler_mrr_init           | 0        |
| Handler_prepare            | 0        |
| Handler_read_first         | 0        |
| Handler_read_key           | 1        |
| Handler_read_last          | 1        |
| Handler_read_next          | 0        |
| Handler_read_prev          | 45189200 |
| Handler_read_rnd           | 0        |
| Handler_read_rnd_next      | 0        |
| Handler_rollback           | 0        |
| Handler_savepoint          | 0        |
| Handler_savepoint_rollback | 0        |
| Handler_update             | 0        |
| Handler_write              | 0        |
+----------------------------+----------+
18 rows in set (0.00 sec)

執行時間：15 rows in set (5 min 38.85 sec)

總結

為什麼explain中的rows不一樣，最終的掃描的Handler_read_prev一樣呢？

哈哈，只能說explain 中的limit 欺騙了你。。。 limit optimization

問題二

針對以上案例，為什麼Mysql 會選擇brokerid 作為索引呢？為什麼不用其他的索引呢？我們來強制指定看看

dbadmin:abc> explain select distinct  `brokerid`  from `test_table` force index(cst_date) where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15
    -> ;
+----+-------------+---------------------+------+-----------------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table               | type | possible_keys         | key  | key_len | ref  | rows     | Extra                                        |
+----+-------------+---------------------+------+-----------------------+------+---------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | test_table | ALL  | cst_date,idx_brokerid | NULL | NULL    | NULL | 51696652 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+------+-----------------------+------+---------+------+----------+----------------------------------------------+
1 row in set (0.00 sec)

看樣子，還是不行？強制索引無效。。。怎麼辦？那我們就應該去看看Mysql到底是如何一步一步選擇執行計劃的，還好Mysql 5.6 提供了另外一種追蹤途徑 optimizer_trace

mysql> SET optimizer_trace="enabled=on";

SQL1:
select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15;

mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE INTO outfile `trace.json`;

最終看到的jason時這樣的(擷取部分)：
            "clause_processing": {
              "clause": "GROUP BY",
              "original_clause": "`test_table`.`brokerid` desc",
              "items": [
                {
                  "item": "`test_table`.`brokerid`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`test_table`.`brokerid` desc"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`test_table`",
                "access_type": "table_scan"
              }
            ]
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "GROUP BY",
              "index_order_summary": {
                "table": "`test_table`",
                "index_provides_order": true,
                "order_direction": "desc",
                "index": "brokerid",
                "plan_changed": true,
                "access_type": "index_scan"

大家可以很清晰的看到，Mysql在之前還是有很多可以選擇的索引，但是最後
reconsidering_access_paths_for_index_ordering 中卻選擇了brokerid，訪問路徑為index_scan.
奇了個怪了，為啥？google了一把後，發現之前有類似的bug [Bug #70245](http://bugs.mysql.com/
bug.php?id=70245),裡面說eq_range_index_dive_limit 會影響range查詢計劃，官方文件確實也是這
麼說的。But，無論我怎麼設定eq_range_index_dive_limit的值，絲毫不會影響執行計劃

dbadmin:abc> select @@session.eq_range_index_dive_limit;
+-------------------------------------+
| @@session.eq_range_index_dive_limit |
+-------------------------------------+
|                                  10 |
+-------------------------------------+
1 row in set (0.00 sec)
以上SQL測試均來自 @@session.eq_range_index_dive_limit。

設定成200（>in(N)）：set @@session.eq_range_index_dive_limit=200;

設定成0(<in(N))，set @@session.eq_range_index_dive_limit=0；

設定成與IN列表中的個數(=in(N))： set @@session.eq_range_index_dive_limit=6；

以上執行計劃沒有任何變化，跑出來的時間，和上面一樣。

那怎麼辦呢？

首先

既然brokerid干擾其優化器的選擇，如果我將其drop掉,優化器是否能夠選擇正確的索引呢？

dbadmin:abc> alter table test_table drop index brokerid;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

dbadmin:abc> explain select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+----------------------+----------+---------+------+------+--------------------------------------------------------+
| id | select_type | table               | type  | possible_keys        | key      | key_len | ref  | rows | Extra                                                  |
+----+-------------+---------------------+-------+----------------------+----------+---------+------+------+--------------------------------------------------------+
|  1 | SIMPLE      | test_table | range | report_date,cst_date | cst_date | 8       | NULL |  780 | Using index condition; Using temporary; Using filesort |
+----+-------------+---------------------+-------+----------------------+----------+---------+------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

dbadmin:abc> flush status;
Query OK, 0 rows affected (0.00 sec)

dbadmin:abc> select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15;
+----------+
| brokerid |
+----------+
|  2112641 |
|  2111870 |
|  2076429 |
|  2072897 |
|  1988209 |
|  1897956 |
|  1816767 |
|  1767494 |
|  1754405 |
|  1709879 |
|  1628017 |
|  1587473 |
|  1582185 |
|  1574712 |
|  1562055 |
+----------+
15 rows in set (0.11 sec)

dbadmin:abc> show status like `Hand%`;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 6     |
| Handler_read_last          | 0     |
| Handler_read_next          | 781   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 30    |
| Handler_read_rnd_next      | 36    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 781   |
+----------------------------+-------+
18 rows in set (0.00 sec)

果然，Mysql選擇了正確的索引，跑起來還不錯。但是那個索引要經常被用到，不能被刪除，結果這條道路是走不通的。

其次

再回頭看看trace的選擇，裡面有關於”clause”: “GROUP BY”？我就再想，是不是由於Group by的原因呢？不清楚，那就試試唄，於是將distinct去掉，試試看

dbadmin:abc> explain select   `brokerid`  from `test_table` force index(cst_date) where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+---------------------------------------+
| id | select_type | table               | type  | possible_keys | key      | key_len | ref  | rows | Extra                                 |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | test_table | range | cst_date      | cst_date | 8       | NULL |  780 | Using index condition; Using filesort |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)


dbadmin:abc> show status like `Hand%`;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 6     |
| Handler_read_last          | 0     |
| Handler_read_next          | 781   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

情況貌似好轉了，但是這樣子是不滿足業務邏輯的呀。。。。
於是，再仔細看看SQL語句的，發現order by 和 group by 重合了，，，為啥不利用group by來排序呢？
so，SQL語句這樣修改一下

dbadmin:abc> explain select   `brokerid`    from     `test_table`  where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`   group  by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+-----------------------------------+----------+---------+------+------+--------------------------------------------------------+
| id | select_type | table               | type  | possible_keys                     | key      | key_len | ref  | rows | Extra                                                  |
+----+-------------+---------------------+-------+-----------------------------------+----------+---------+------+------+--------------------------------------------------------+
|  1 | SIMPLE      | test_table | range | report_date,cst_date,idx_brokerid | cst_date | 8       | NULL |  780 | Using index condition; Using temporary; Using filesort |
+----+-------------+---------------------+-------+-----------------------------------+----------+---------+------+------+--------------------------------------------------------+
1 row in set (0.01 sec)

dbadmin:abc> show status like `Hand%`;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 6     |
| Handler_read_last          | 0     |
| Handler_read_next          | 781   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 15    |
| Handler_read_rnd_next      | 36    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 781   |
+----------------------------+-------+
18 rows in set (0.00 sec)

從效能上看

優化前的SQL：
dbadmin:abc> select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126`  order by `brokerid` desc limit 15,15;

+----------+
| brokerid |
+----------+
|  2112641 |
|  2111870 |
|  2076429 |
|  2072897 |
|  1988209 |
|  1897956 |
|  1816767 |
|  1767494 |
|  1754405 |
|  1709879 |
|  1628017 |
|  1587473 |
|  1582185 |
|  1574712 |
|  1562055 |
+----------+
15 rows in set (5 min 42.10 sec)

優化後的SQL：
dbadmin:abc> select  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( `59494` , `59499` , `59502` , `59727` , `60119` , `93204` )  and `report_date` >= `20141101`  and `report_date` <= `20141126` group by `brokerid` desc limit 15,15;
+----------+
| brokerid |
+----------+
|  2112641 |
|  2111870 |
|  2076429 |
|  2072897 |
|  1988209 |
|  1897956 |
|  1816767 |
|  1767494 |
|  1754405 |
|  1709879 |
|  1628017 |
|  1587473 |
|  1582185 |
|  1574712 |
|  1562055 |
+----------+
15 rows in set (0.01 sec)


PS：為了保證SQL的效率的準確性，以上SQL均重啟後第一次跑的時間為準。

總結
1. distinct，orderby ，group by，limit 這幾個條件放在一起，會給Mysql 優化器帶來很大的負擔，建議儘量不要這樣使用。

交流（1）-- 執行計劃錯誤問題
2011-01-07
SQL 執行計劃案例1
2010-10-16
SQL
執行計劃中Cardinality (Estimated Rows， E-Rows) 估算錯誤原因分析
2014-12-29
explain執行計劃分析
2020-11-14
AI
SQL執行計劃分析
2009-04-27
SQL
分析執行計劃優化SQLORACLE的執行計劃(轉)
2019-04-04
優化SQLOracle
autotrace 和explain plan for可能導致執行計劃錯誤
2013-09-27
AI
執行計劃錯誤導致系統負載高
2011-11-28
負載
索引失效系列——統計量過期引起執行計劃錯誤
2011-04-27
索引
分析執行計劃最佳化SQLORACLE的執行計劃(轉)
2009-03-04
SQLOracle
統計資訊不正確導致執行計劃的錯誤選擇
2016-06-04
檢視執行計劃出現ORA-22992錯誤
2019-07-05
11G的SORT GROUP BY NOSORT導致錯誤執行計劃
2008-09-10
DB2執行計劃分析
2020-04-05
DB2
SqlServer的執行計劃如何分析？
2023-10-18
SQLServer
【優化】ORACLE執行計劃分析
2012-04-18
優化Oracle
由於統計量失真造成SQL執行計劃錯誤一例
2012-11-18
SQL
執行計劃-1：獲取執行計劃
2019-10-07
mysql 執行計劃索引分析筆記
2019-05-06
MySql索引筆記
(4) MySQL中EXPLAIN執行計劃分析
2019-01-08
MySqlAI
執行錯誤集
2018-08-15
執行計劃
2014-04-03
Oracle檢視正在執行的SQL以及執行計劃分析
2014-05-04
OracleSQL
執行計劃不穩定的原因分析
2012-02-14
oracle的執行計劃居然出錯[轉帖]
2010-11-09
Oracle
oracle sqlprofile 固定執行計劃，並遷移執行計劃
2017-08-09
OracleSQL
【sql調優之執行計劃】獲取執行計劃
2010-08-16
SQL
SYBASE執行計劃
2019-06-28
MySQL 執行計劃
2020-12-24
MySql
MySQL執行計劃
2018-05-29
MySql
sql 執行計劃
2008-08-20
SQL
ORACLE執行計劃
2011-05-02
Oracle
MySQL——通過EXPLAIN分析SQL的執行計劃
2018-08-03
MySqlAI
Oracle 執行計劃分析和動態取樣
2014-02-13
Oracle
ORACLE關於執行計劃的簡要分析
2014-11-27
Oracle
pl/sql developer 分析的執行計劃不可信
2009-04-10
SQLDeveloper
【執行計劃】Oracle獲取執行計劃的幾種方法
2021-08-09
Oracle
【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃
2013-07-26
Oracle索引

MySQL5.6執行計劃錯誤案例分析

前提

問題1

問題二

相關文章