mysql5.7在多列索引 in条件查询的优化
来源:互联网 发布:淘宝睡衣女装 编辑:程序博客网 时间:2024/06/05 00:30
山谷中偶遇,分享出来。
某日(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: 1739Message: 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: 1739Message: 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: 1003Message: /* 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 (<cache>(('464580908742086656','ffffffffdeaea5fcffffffff96fddfcf')),<cache>(('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多个条件时,可以使用索引。
未完,希望明白的人在下方留言,谢谢。
- mysql5.7在多列索引 in条件查询的优化
- mysql5.7官网直译优化和索引--使用生成列的索引
- mysql5.7官网直译优化和索引--多列索引
- 联合索引优化多条件查询
- 联合索引优化多条件查询
- Mysql多列索引,查询优化
- Atitit. 单列索引与多列索引 多个条件的查询原理与设计实现
- mysql5.7官网直译SQL语句优化--索引条件压入优化
- sql大数据多条件查询索引优化
- 多个条件的查询建立索引
- mysql5.7官网直译SQL语句优化--where条件查询优化
- MySQL5.6 Internals-隐藏的索引列
- MySQL5.7 虚拟列实现表达式索引
- MySQL5.7 虚拟列实现表达式索引
- MySQL5.7 虚拟列实现表达式索引
- where条件对mysql多列索引的使用
- 多条件数据库查询的优化方法
- 多条件数据库查询的优化方法
- 遍历Windows桌面上所有窗口
- poj1611(并查集)
- iOS CGAffineTransform简单应用
- Android必知必会-Fragment监听返回键事件
- 【概念混淆】取余运算和取模运算
- mysql5.7在多列索引 in条件查询的优化
- 使用AndroidEventBus
- [zz]css绝对定位、相对定位和文档流的那些事
- Eclipse JEE中Server Options作用
- [Platform]Phantom -- Programmable Security Controller
- maven pom.xml详解
- 四月排名赛
- tungsten-replicator
- 使Datawindow每页打印固定行