2-6 通过explain查询和分析SQL的执行计划

2018/9/26 comments

https://www.imooc.com/video/3710

explain 分析执行计划

说明

关于 type 为 index_merge

之前项目一个sql示例

mysql> mysql> explain SELECT COUNT(id) FROM `orders`  WHERE `orders`.`types` = 2 AND `orders`.`status` = 2 AND  `orders`.`pay_type` IN (1, 2);
+----+-------------+--------+------------+-------------+-----------------------------------------------------------------------+----------------------------------------------+---------+------+-------+----------+----------------------------------------------------------------------------+
| id | select_type | table  | partitions | type        | possible_keys                                                         | key                                          | key_len | ref  | rows  | filtered | Extra                                                                      |
+----+-------------+--------+------------+-------------+-----------------------------------------------------------------------+----------------------------------------------+---------+------+-------+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | index_merge | index_orders_on_types,index_orders_on_status,index_orders_on_pay_type | index_orders_on_types,index_orders_on_status | 5,5     | NULL | 97112 |    14.71 | Using intersect(index_orders_on_types,index_orders_on_status); Using where |
+----+-------------+--------+------------+-------------+-----------------------------------------------------------------------+----------------------------------------------+---------+------+-------+----------+----------------------------------------------------------------------------+

type 为 index_merge

对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。

links