1.explain
有时在使用explain时，感觉有些条件一定能使用到索引，但是并没有使用到，可能是数据少，mysql优化器做了查询优化，全表扫描速度大于索引速度。explain不同mysql版本会有不同的结果，不要完全死记。
表结构
CREATE TABLE
test(
idint(11) NOT NULL,
aint(11) DEFAULT NULL,
bint(11) DEFAULT NULL,
cvarchar(45) DEFAULT NULL,
dvarchar(45) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEYindex_d(d),
KEYabc(a,b,c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
在mysql命令行中，执行explain命令可以看到sql执行计划，如下图所示：
id：select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
select_type：表示查询中每个select子句的类型，简单 、复杂，复杂包括简单子查询、派生表（from语句中的子查询）、union 查询
simple：查询中不包含子查询或者union
primary：复杂查询中最外层的 select
subquery：在select或where列表中包含的子查询
derived：在from列表中包含的子查询被标记为derived，mysql会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
union：在 union 中的第二个和随后的 select
union result：从union表获取结果的select被标记为union result
table： explain 的一行所引用的表。
1.当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
2.当有 union 时，union result 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
type*：表示mysql在表中找到所需行的方式，即mysql决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引
system：system是const类型的特例，当查询的表只有一行的情况下为system
const：当mysql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如主键或唯一索引表最多只有一行匹配，只需读取一次，所以很快。
eq_ref：主键或唯一索引被连接使用时，最多返回一条符合条件的记录。简单的select查询不会出现这种type
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>、in()等的查询
index：扫描全表索引，index与ALL区别为index类型只遍历索引树，从索引读取
ALL：全表扫描，mysql将遍历全表以找到匹配的行，从硬盘读取
possible_keys
指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
key*
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
【MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件（即不用遍历两棵索引树），包含所有满足查询需要的数据的索引称为“覆盖索引”】
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
key_len*
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
key_len计算规则如下：
- 字符串
n char(n)：n字节长度
n varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2- 数值类型
n tinyint：1字节
n smallint：2字节
n int：4字节
n bigint：8字节- 时间类型
n date：3字节
n timestamp：4字节
n datetime：8字节- 如果字段允许为 NULL，需要1字节记录是否为 NULL
row：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
Extra*
十分重要的额外信息
Using index：查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高
Using where：表查询的列未被索引覆盖，where筛选条件非索引的前导列
Using where Using index：查询的列被索引覆盖，并且where筛选条件是索引列之一但不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据，不是索引的最佳使用姿势
NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
Using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Using temporary：表示mysql需要使用临时表来存储结果集，常见于group by
Using filesort： MySQL中无法利用索引完成的排序操作称为“文件排序”，常见于order by
2.查询索引失效
1.索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效
2.索引中范围条件右边的索引列会失效
3.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
4.is null,is not null 也无法使用索引
5.字符串不加单引号索引失效
6.like以通配符开头（'%abc...'）mysql索引会失效
关于部分索引命名符合使用条件，有时却会导致索引失效
如：or或in，或范围查询
mysql内部优化器会根据检索比例、表大小等都会影响整体评估是否使用索引（如何分析见第4节）
mysql特殊处理：like KK%相当于=常量，%KK和%KK% 相当于范围
3.排序索引失效
explain-Using filesort
order by会导致Using filesort，我们需要做的是使用索引来避免文件排序，因为索引是有序的
Using filesort底层原理见第5节
explain-Using temporary
group by会导致Using temporary，group by实质是先排序后分组，所以实际上避免Using filesort则会避免Using temporary
index(a,b,c)
case:1查询使用索引(a,b) 排序c使用索引(c)
case:2查询使用索引(a) 排序b使用索引(b)
case:3查询使用索引(a,b) 排序d由于d没有索引，产生Using filesort
case:4查询使用索引(a) 排序c没有使用索引产生Using filesort，与case1,case2对比可知查询与order by索引必须连续才可在排序也使用到索引
case:5 同case4
联合索引是将各个索引字段做字符串连接后作为key，使用时将整体做前缀匹配。
结论：如果where字段与order by字段是连续的组合索引，由于b+树底层是有序的，所以不会出现Using filesort。where条件后通过索引(a,b)前缀匹配到符合条件的数据，由于索引是有序的，所以对这些数据进行order by排序处理时只要order by的字段符合索引的顺序则没有问题，如果order by排序的字段与前边的条件(a,b)不连续则不会使用索引本身的顺序。
case:1 (where a order by b,a)会优化为(order by b)，(a,b)符合索引正常顺序，使用索引顺序
case:2 (where a order by b,a)会优化为(order by b)，(a,b)符合索引正常顺序，使用索引顺序
case:3 (where a order by c,a)会优化为(order by c)，(a,c)不符合索引正常顺序，产生Using filesort
case:4 (where a order by b,c) (a,b,c)符合索引正常顺序，使用索引顺序
case:5 (where a order by a,c)会优化为(order by c)，(a,c)不符合索引正常顺序，产生Using filesort
case:6 (where a order by c,b) (a,c,b)不符合索引正常顺序，产生Using filesort
case:7 (where a order by b,c) (a,c,b)符合索引顺序，但是b正序，c逆序，与联合索引底层数据结构顺序不匹配，所以需要重新排序，产生Using filesort
case:8 (where a= and c> order by b) ac查询索引断掉，所以查询(a)，排序(b) (a,b)符合索引顺序
case:9 (where a= and b> order by c) (a,b)查询符合索引顺序，(b)范围查询导致后续索引断掉，即order by c不会使用到索引，产生Using filesort
重点：索引是排好序的数据结构，联合索引是将各个索引字段做字符串连接后作为key，，使用时将整体做前缀匹配。所以一定要顺序匹配索引才会使用索引。
4.trace工具
-- 开启trace工具
set session optimizer_trace="enabled=on",end_markers_in_json=on;
-- 鉴定sql运行
select * from sy_demo.user where name > 'hh';
-- 查看trace工具sql结果
SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- 关闭trace
set session optimizer_trace="enabled=off";
{
"steps": [{
"join_preparation": { ‐‐第一阶段:SQL准备阶段
"select#": 1,
"steps": [{
"expanded_query": "/* select#1 */ select `sy_demo`.`user`.`id` AS `id`,`sy_demo`.`user`.`name` AS `name`,`sy_demo`.`user`.`id_card` AS `id_card`,`sy_demo`.`user`.`status` AS `status`,`sy_demo`.`user`.`phone` AS `phone`,`sy_demo`.`user`.`modify_time` AS `modify_time`,`sy_demo`.`user`.`create_time` AS `create_time`,`sy_demo`.`user`.`version` AS `version` from `sy_demo`.`user` where (`sy_demo`.`user`.`name` > 'hh') limit 0,500"
}] /* steps */
} /* join_preparation */
},
{
"join_optimization": { ‐‐第二阶段:SQL优化阶段（如联合索引字段顺序调整）
"select#": 1,
"steps": [{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`sy_demo`.`user`.`name` > 'hh')",
"steps": [{
"transformation": "equality_propagation",
"resulting_condition": "(`sy_demo`.`user`.`name` > 'hh')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`sy_demo`.`user`.`name` > 'hh')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`sy_demo`.`user`.`name` > 'hh')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {} /* substitute_generated_columns */
},
{
"table_dependencies": [{ ‐‐表依赖详情
"table": "`sy_demo`.`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [] /* depends_on_map_bits */
}] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [{ ‐‐预估表的访问成本（重点）
"table": "`sy_demo`.`user`",
"range_analysis": {
"table_scan": { ‐全表扫描情况 scan
"rows": 17, ‐‐扫描行数
"cost": 6.5 ‐‐查询成本 相对值
} /* table_scan */ ,
"potential_range_indexes": [{ ‐‐查询可能使用的索引
"index": "PRIMARY", ‐‐主键索引
"usable": false, --是否使用该索引
"cause": "not_applicable"
},
{
"index": "idx_user", -‐辅助索引
"usable": true, --是否使用该索引
"key_parts": [ --索引构成
"name",
"id_card",
"phone",
"id"
] /* key_parts */
}
] /* potential_range_indexes */ ,
"setup_range_conditions": [] /* setup_range_conditions */ ,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */ ,
"analyzing_range_alternatives": { ‐‐分析各个索引使用成本（重点）
"range_scan_alternatives": [{
"index": "idx_user",
"ranges": [
"hh < name" ‐‐索引使用范围
] /* ranges */ ,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, ‐‐是否使用覆盖索引
"rows": 10, ‐‐索引扫描行数
"cost": 13.01, ‐‐索引使用成本
"chosen": false, ‐‐是否选择该索引
"cause": "cost"
}] /* range_scan_alternatives */ ,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}] /* rows_estimation */
},
{
"considered_execution_plans": [{
"plan_prefix": [] /* plan_prefix */ ,
"table": "`sy_demo`.`user`",
"best_access_path": { ‐‐最优访问路径
"considered_access_paths": [{ ‐‐最终选择的访问路径
"rows_to_scan": 17,
"access_type": "scan", ‐‐访问类型 "access_type": "scan" 全表扫描 "used_index": "PRIMARY" 主键索引
"resulting_rows": 17,
"cost": 4.4,
"chosen": true ‐‐确定选择
}] /* considered_access_paths */
} /* best_access_path */ ,
"condition_filtering_pct": 100,
"rows_for_plan": 17,
"cost_for_plan": 4.4,
"chosen": true
}] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`sy_demo`.`user`.`name` > 'hh')",
"attached_conditions_computation": [] /* attached_conditions_computation */ ,
"attached_conditions_summary": [{
"table": "`sy_demo`.`user`",
"attached": "(`sy_demo`.`user`.`name` > 'hh')"
}] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [{
"table": "`sy_demo`.`user`"
}] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { ‐‐Sql执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`sy_demo`.`user`",
"field": "phone"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"limit": 500,
"rows_estimate": 642,
"row_size": 581,
"memory_available": 262144,
"strip_additional_fields": {
"row_size": 117,
"sort_merge_cost": 451.8,
"priority_queue_cost": 1758.8,
"chosen": false
} /* strip_additional_fields */
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { ‐‐文件排序信息
"rows": 16, ‐‐预计扫描行数
"examined_rows": 16, ‐‐参数排序的行
"number_of_tmp_files": 0, ‐‐使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用磁盘文件排序
"sort_buffer_size": 261816, ‐排序缓存的大小
"sort_mode": "<sort_key, packed_additional_fields>" ‐排序方式，这里用的单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
5.Using filesort原理
单路排序:是一次性取出满足条件行的所有字段存入sort buffer，然后在sort buffer中进行排序;用trace工具可以看到sort_mode信息里显示<sort_key, additional_fields>或者< sort_key, packed_additional_fields>
双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和主键id存入sort buffer，然后在 sort buffer 中进行排序，排序完后需要再次根据id回到原表取回其它需要的字段;用trace工具 可以看到sort_mode信息里显示<sort_key, rowid>
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来 判断使用哪种排序模式。
如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 单路排序模式;
如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 双路排序模式。
6.表关联算法（了解）
mysql的表关联常见有两种算法
Nested-Loop Join 算法
Block Nested-Loop Join 算法
1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表(称为驱动表)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(被驱动 表)里取出满足条件的行，然后取出两张表的结果合集。
2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有 索引的情况下 NLJ 算法比 BNL算法性能更高
关联字段加索引，让mysql做join操作时尽量选择NLJ算法 小表驱动大表
7.count(*)查询优化
explain select count(1) from sy_demo.user;
explain select count(id) from sy_demo.user;
explain select count(*) from sy_demo.user;
explain select count(name) from sy_demo.user;
以上四个sql的执行计划都一样的，都是使用的辅助索引，其执行效率也差不多。区别在于根据某个字段count不会统计字段为null值的数据行
为什么mysql最终选择辅助索引而不是主键聚集索引?因为二级索引相对主键索引存储数据更少，检索性能应该更高，所以即使我们使用count(id)、count(*)、count(1)都是走的辅助索引。而为了防止null影响，参考阿里巴巴编程规范，建议使用count(*)
8.索引口诀
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE符号写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不能丢，SQL高级也不难
分组之前必排序，一定要上索引啊
参考：
https://www.iteye.com/blog/hudeyong926-785181
https://blog.csdn.net/weiwangchao_/article/details/50256673
https://blog.csdn.net/lbr2008/article/details/79245289#%E5%8F%A3%E8%AF%80
