山谷中偶遇,分享出來。
某日(ri),zabbix mysql慢日志監控發現很多陌生的慢查詢。
大概如下:SELECT * FROM `entry_device` WHERE (`entry_no`, `device_udid`) IN (('464580908742086656', 'ffffffffdeaea5fcffffffff96fddfcf')), ('464109860674342912', 'acbf32266351de103bd80bc230882b4c783cb79d'));
線上版本為5.6.2X
查詢計划:mysql> explain extended SELECT * FROM `entry_device` WHERE (`entry_no`, `device_udid`) IN (('464580908742086656', 'ffffffffdeaea5fcffffffff96fddfcf')), ('464109860674342912', 'acbf32266351de103bd80bc230882b4c783cb79d'));
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | entry_device | ALL | NULL | NULL | NULL | NULL | 9763 | 100.00 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
瞬間石化,全表掃,當然上面看到的是掃描9763行數據,然而當時線上數據為1億多行,內存讀取全表掃,差不多60秒。
然而5.7給了不一樣的查詢計划,並且結果秒出,這本不是個復雜的查詢,簡單的不能再簡單,基於聯合索引的in條件查詢。
5.7的查詢計划如下:mysql> explain SELECT * FROM `entry_device`WHERE (`entry_no`, `device_udid`) IN (('464580908742086656', 'ffffffffdeaea5fcffffffff96fddfcf'), ('464109860674342912', 'acbf32266351de103b0882b4c783cb79d'));
+----+-------------+--------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | entry_device | NULL | range | index_entry_no_device_udid | index_entry_no_device_udid | 262 | NULL | 2 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1739
Message: Cannot use ref access on index 'index_entry_no_device_udid' due to type or collation conversion on field 'entry_no'
*************************** 2. row ***************************
Level: Warning
Code: 1739
Message: Cannot use ref access on index 'index_entry_no_device_udid' due to type or collation conversion on field 'entry_no'
*************************** 3. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `JDB`.`entry_device`.`id`AS `id`,`JDB`.`entry_device`.`entry_device_no`AS `entry_device_no`,`JDB`.`entry_device`.`entry_no`AS `entry_no`,`JDB`.`entry_device`.`status`AS `status`,`JDB`.`entry_device`.`device_udid`AS `device_udid`,`JDB`.`entry_device`.`device_type`AS `device_type`,`JDB`.`entry_device`.`platform`AS `platform`,`JDB`.`entry_device`.`sys_version`AS `sys_version`,`JDB`.`entry_device`.`app_channel`AS `app_channel`,`JDB`.`entry_device`.`client_version`AS `client_version`,`JDB`.`entry_device`.`create_time`AS `create_time`,`JDB`.`entry_device`.`last_time`AS `last_time`from `JDB`.`entry_device`where ((`JDB`.`entry_device`.`entry_no`,`JDB`.`entry_device`.`device_udid`) in ((('464580908742086656','ffffffffdeaea5fcffffffff96fddfcf')),(('464109860674342912','acbf32266351de103bd80bc230882b4c783cb79d'))))
分析第一階段:
5.7采取了更好的優化器選擇,於是關閉了5.7所有與5.6不同的optimizer_switch,如下:
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=off,firstmatch=on,duplicateweedout=off,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=off,derived_merge=off
1 row in set (0.00 sec)
然,再次查詢執行計划時,依然可以用到聯合索引。
第二階段,從5.7的warning信息入手,翻閱文檔
第三階段,5.6加hint,強制索引
結果證明是無效的,5.6依然不會使用這個索引。
第四階段
trace,這是除了去翻閱代碼最后我能做的事情了,5.6的trace結果顯示,在聯合索引in多個條件時,根本就沒有考慮任何索引的cost值,直接選擇全表掃描。5.7在做了分析判斷之后,選擇了代價較小的聯合索引。
注釋:trace可以告訴我們mysql為什么會選擇這樣或者那樣的執行計划。
結論:
我只是知道5.7在這個場景下比5.6強大很多。
於是乎,其實我並沒有明白5.7是哪里做了改變,使得這種聯合索引in多個條件時,可以使用索引。
未完,希望明白的人在下方留言,謝謝。