MySQL优化-Explain详解
EXPLAIN 为每个执行 SELECT 指令的表返回一条记录。在连接多个表读取数据时，按照读取顺序，通过遍历的方式处理，最终展示结果也是按照读取顺序展示。
# EXPLAIN 中的列
# id
id 列的编号是 select 的序列号，有几个 select 就有几个 id，它的大小和位置反应了每个操作在执行计划中的位置。id 值小的操作通常在执行计划中优先执行。
相同
id代表是一组操作，执行顺序是从上到下不同的
id值越大，优先级越高，越先执行null代表一组联合查询的聚合结果，在table列会显示<union M, N>
# select_type
SIMPLE 简单查询，不包含子查询和联合查询。
例如：
explain select * from salaries s WHERE emp_no = 10030
PRIMIARY 子查询中的最外层查询，通常是整个查询的第一个查询。
SUBQUERY 包含在 select 中的子查询（不在 from 子句中）
UNION 使用UNION操作进行两个或多个SELECT查询的联合，每个UNION查询都会有一个UNION结果列，查询需要合并这些结果。
explain select * from titles t WHERE t.emp_no = 10030 UNION SELECT * from titles t2 WHERE t2.emp_no = 10040 UNION SELECT * from titles t3 WHERE t3.emp_no = 10050
DEPENDENT UNION
UNION RESULT UNION查询的结果集，当使用两个或多个UNION时，每个SELECT语句都会有一个对应的UNION RESULT行。
DERIVED 包含在 from 子句中的子查询，MySQL必须先执行内部SELECT语句，然后将结果传递给外部查询。这个子查询的结果会存放在一个临时表中，也称为派生表
set SESSION optimizer_switch='derived_merge=off'; -- 关闭 MySQL 5.7 新特性对于衍生表的合并优化 explain SELECT (select 1 from employees e1 WHERE e1.emp_no = 10030) as tmp2 from (select * from employees e2 WHERE e2.emp_no = 10030) as tmp1 set SESSION optimizer_switch='derived_merge=on'; -- 还原配置
MATERIALIZED
UNCACHEABLE SUBQUERY
UNCACHEABLE UNION
# table
表示 explain 的一行正在访问哪个表
table name表示原始表名NULL该查询不涉及任何表，比如一些常量表达式或者没有from子句的select查询derivedN表示查找结构的派生表（Derived Table），N 表示当前查询依赖id = N的查询unionNUNION RESULT 的table列的值为<unionM,n>，m 和 n 表示参与union的select行的id
# type
system
表中只有一行 （=系统表）？？？？，
const连接类型的一种特殊情况。const
查询条件是一个常量，并且是通过主键或者唯一索引进行等值查询，查询优化器可以直接定位到匹配的唯一行，从而高效的执行查询。
explain select * from conversation_v2_122 cv WHERE cv.user_id = 48356986 AND cv.conv_id = '00008123d345a66eee0de73d778d1f47'
eq_ref
有点类似
const，也是在主键或者唯一索引上进行等值查询，区别是查询条件并不是直接给了一个常量，而是涉及到了连接操作，对于前一个表的每个 “数据组合” 都有一行与之对应。explain select * from salaries s left join employees e on s.emp_no = e.emp_no -- 在 employees 表中 emp_no 是主键，连接条件使用了主键，所以当查到 employees 表的数据时是命中主键的，跟 const 的区别就是条件不是一个确定的常量，但是效率也是很高的
ref
未使用主键或者唯一索引，只用了普通索引进行等值查询，或者使用了唯一索引的前缀来检索。
--- mysql 官方示例 SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
使用索引在给定的范围内查询，对
=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()这些操作符都有可能触发。index
跟
ALL类似，不同的是index只遍历索引树，不需要全表扫描。ALL
# possible_keys
在查询的过程中可能会用到的索引，实际有可能用不到，具体情况还是得根据 key 列来判断
# key
在查询的过程中实际使用了哪个索引
# key_len
实际用到的索引的列的总长度，计算规则如下：
char(n)如果使用的是 utf-8，则长度为 3n，如果是 utf8mb4，则长度为 4nvarchar(n)如果使用的是 utf-8，则长度为 3n+2，如果允许 null 值，则长度为 3n+2+1int4 个字节date3 个字节
# ref
展示的是与 key 这一列比较时用到的 "列" 或者 "常量值"，例如 key 这列显示的是用到了索引 idx_emp_id ，当基于这个索引做等值查询时，我们可以理解为是用常数进行连接，此时 ref 的值是 const，表示用常数连接，只有一行数据符合条件。
也就是说它体现了使用什么方式去查询的 key 列
# row
估计需要扫描的行数
# extra
Using index 表示查询中使用了覆盖索引，只需要读取索引数据不需要回表。
Using where 待确认…… (表示查询使用了 WHERE 条件进行筛选，但是无法使用索引来优化查询。因此，MySQL 需要扫描所有记录来进行筛选)
Using index condition 表示查询条件都包含在索引中，直接通过索引就可以判断出需要哪些数据，不需要再通过回表筛选。例如有
a,b,c,d4 列数据，索引是index(a,b)，查询条件是where a='abc' and b > 1这样查询条件都包含在索引中，筛选时就不需要再回表比较，根据索引取出主键，再根据主键拿数据就行。-- 索引是 UNIQUE KEY `user_conv_id_unique` (`user_id`,`conv_id`) explain select * from conversation_v2_100 WHERE user_id = 109240932 and conv_id > '7aaac4a9081c3a424cc9a2292eebfd49'
Using temporary 表示需要创建临时表来处理中间结果，通常出现在排序、分组或者连接操作中，这种情况一般是需要优化的。
Using filesort 需要排序的结果集比较大，无法在内存中完成，需要依赖外部磁盘进行数据暂存排序，这种情况增加了跟磁盘的 IO 操作，性能较差。
Using join buffer：表示查询需要使用连接缓冲区来处理连接操作。如果连接表的数量较大，或者连接字段没有索引，就可能出现这种情况。
# Profiling 工具
-- 查询开启状态
select @@profiling
-- 开启，最对当前 session 生效
set profiling = 1;
-- 执行 A、B 两条 SQL 之后对比查询效率
select * from t1 where id = 1 or id = 2 or id = 3 or id = 4;
select * from t1 where id in (1,2,3,4)
-- 查询执行效率
show profiles;
# 问题
# 索引的最大长度是多少？超过长度会怎么样？
innodb_large_prefix (opens new window) 默认是开启的，开启时的长度限制是 3072 bytes，如果 innodb_large_prefix 关闭，则长度限制是 767 bytes。超过限制的长度将会报错。
如果修改了 InnoDB 的 page_size （默认 16 kb），索引的最大长度也将会按比例减少，例如 page_size 修改为 8kb ，索引最大长度将变为 1536 bytes