Tips-Mysql Explain && Index Merge

OverView

最近工作场景下经常遇见sql执行慢查询的状况,但是却没有系统地针对sql语句进行分析。这篇文章主要介绍一下 Explain sql语句的output format，以及index merge 的概念以及执行场景。

Content

首先附上链接 官方文档

首先需要理解的是Query Execution Plan,文档里是这么说的，"The set of operations that the optimizer chooses to perform the most efficient query is called 'query execution plan', also know the explain plan." 简单说就是 优化器会基于你的sql语句，选择有效的操作，从而能够最有效率地执行这条query语句，这里的explain也可以理解为mysql server是如何理解这条sql，并决定采用怎样的策略去执行。

explain 的结果反过来可以验证我们的查询是否是合理的，表的索引设计是否合理。

首先创建一张表，表结构是

CREATE TABLE `cc_test_userinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户姓名',
  `cardid` int(11) NOT NULL COMMENT '证件识别码',
  `address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '家庭地址',
  `gender` int(2) NOT NULL COMMENT '性别',
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UIX_cardid` (`cardid`),
  KEY `IX_name` (`name`),
  KEY `IDX_Update_Time` (`UpdateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

explain select * from cc_test_userinfo
下面是 explain 语句 output 列的说明，第一列为column name, 第二列为format=json时的等价的property name，关于 format=json 可以参考 这里

column name	json name	meaning
id	select_id	表明select的标识序号，因为对一条sql语句可能explain出来多条结果，对于有subquery的场景尤其是
select_type	None	select 的类型，包括 SIMPLE, PRIMARY,SUBQUERY...
table	table_name	output table_name
partitions	partitions	the match partitions
type	access type	the join type 包含system,const,full_text,index_merge,range,index ...
possible_keys	possible_keys	the possible indexes to choose
key	key	the index actually choose
key_len	key_length	the length of the chosen key
ref	ref	the columns compared to the index
rows	rows	extimate of rows to be examind
filtered	filtered	percentage of rows filtered by table condition
extra	none	additional information

这个时候我们执行一条explain语句分别查看对应column的值是什么

image.png

explain select * from cc_test_userinfo

其中 select_type 为 SIMPLE 即是没有 union等操作，简单的SIMPLE 语句

type 为 ALL 表明是一个full table scan

filtered The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from 100 indicate increasing amounts of filtering. 这里没有过滤条件，所以是100%

image.png

explain select * from cc_test_userinfo where id = 5 and name = "kaka" and cardid=99

possible_keys 为 primary, UIX_cardid,IX_name

但是实际使用的key是primary

还有很多explain的细节，但是我发现我创建的表，对各种select覆盖不全

Index Merge

下面介绍index merge 的概念

index merge 的思路实际上就是 multiple range scans and merge result into one, 主要支持三种对结果集聚合的方式，intersections交集,unions并集,sort-union

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

** Index Merge Intersection **

• An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

• Any range condition over the primary key of an InnoDB table.

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

** Index Merge Union **

• An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

• Any range condition over a primary key of an InnoDB table.
• A condition for which the Index Merge intersection algorithm is applicable.

Conclusion

实际上 mysql optimizer在执行 查询优化时，有很多策略，路漫漫
本篇更多是概念上的介绍，可以看下源码是如何采取执行计划的