MySQL之索引优化（一）

1. 联合索引和模糊查询的常见问题

1.1 联合索引第一个字段用范围导致不会走索引的情况

联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

注意：employees数据偏少时会走索引（测试employees表一万多条测试数据）

在mysql内部没有走索引的情况下，强制该范围查询走索引的话，效率提升并不大，因为回表效率不高。

优化方案：
覆盖索引优化。尽量让索引覆盖查询的字段

1.2 in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

1.3 like KK% 一般情况都会走索引

注意： like KK%一般是用到了索引下推优化

索引下推：
对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则

SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'

这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和 position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

简单来说，即是索引下推之前是：name字段走索引查询符合的值的索引 —> 用这些索引去聚簇索引去查询相应的值 —> 再从这些值里用后续的age和position这两个字段去过滤符合条件的值

索引下推是：name字段走索引查询符合的值的索引 —> 再在这些索引判断符合age和position这两个字段的值的索引 —> 再把这些符合条件的值的索引去聚簇索引去查询相应的值（回表）

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

为什么范围查找Mysql没有用索引下推优化？
估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

2. Mysql的索引分析工具—trace

在判断mysql最终是否选择走索引或者一张表涉及多个索引，mysql最 终如何选择索引，可以用trace工具来分析。

注意：开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭

# 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;

# 示例sql语句
select * from employees where name > 'a' order by position;

# 查看trace分析日志
SELECT * FROM information_schema.OPTIMIZER_TRACE;

trace分析日志：

{ 
	"steps": [
	 { 
	   "join_preparation": { ‐‐ 第一阶段：SQL准备阶段，格式化sql 
	   "select#": 1, 
	    "steps": [
	    ...
	    },
	    {
	    join_optimization": { ‐‐第二阶段：SQL优化阶段 
	     "select#": 1, 
	      "steps": [ 
	       { 
	        "condition_processing": { ‐‐条件处理 
	         "condition": "WHERE", 
	         "original_condition": "(`employees`.`name` > 'a')", 
	         "steps": [
	          ...
	    },
	    { 
	     "table_dependencies": [ ‐‐表依赖详情 
	    {
		 ...
		{ 
		 "rows_estimation": [ ‐‐预估表的访问成本 
		{ 
		"table": "`employees`", 
		"range_analysis": { 
		"table_scan": { ‐‐全表扫描情况 
		"rows": 10123, ‐‐扫描行数
		"cost": 2054.7 ‐‐查询成本 
		 } /* table_scan */,
		"potential_range_indexes": [ ‐‐查询可能使用的索引
		{
		"index": "PRIMARY", ‐‐主键索引 
		"usable": false, 
		"cause": "not_applicable" 
		}, 
		{ 
		 "index": "idx_name_age_position", ‐‐辅助索引
		  ...
		}, 
		"analyzing_range_alternatives": { ‐‐分析各个索引使用成本
		 ...
		}, 
		...
		"rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序 
		"using_mrr": false, 
		"index_only": false, ‐‐是否使用覆盖索
	    "rows": 5061, ‐‐索引扫描行数 
	    "cost": 6074.2, ‐‐索引使用成本 
	    "chosen": false, ‐‐是否选择该索引 
	    "cause": "cost"
	    ...
	   "best_access_path": { ‐‐最优访问路径
	   "considered_access_paths": [ ‐‐最终选择的访问路径
	   { 
	   "rows_to_scan": 10123,
	   "access_type": "scan", ‐‐访问类型：为scan，全表扫描 
	   "resulting_rows": 10123,
	   "cost": 2052.6, 
	   "chosen": true, ‐‐确定选择 
	   "use_tmp_table": true 
	    }
	   ...
	   "join_execution": { ‐‐第三阶段：SQL执行阶段
	   ...
	    
	结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描

3. 常见优化场景

3.1 Order by与Group by优化

联合索引（name，age，position）

①：age索引列用在排序过程中，因为Extra字段里没有using filesort

②：用了position进行排序，跳过了 age，出现了Using filesort。
③：查找只用到索引name，age和position用于排序，无Using filesort

④：查找只用到索引name，出现了Using filesort，因为排序的时候age和position颠倒位置了

⑤：与 ④ 相比，并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort

⑥：虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式

⑦：对于排序来说，多个相等条件也是范围查询

总结：

• MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低

• order by满足两种情况会使用Using index。
  1) order by语句使用索引最左前列
  2) 使用where子句与order by子句条件列组合满足索引最左前列

• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则

• 如果order by的条件不在索引列上，就会产生Using filesort

• 能用覆盖索引尽量用覆盖索引

• group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了

Using filesort文件排序原理：

• 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer（内存开辟一块空间）中进行排序；用trace工具可 以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key
• 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer （内存开辟一块空间）中进行排序，排序完后需要再次取回其它需要的字段；用trace工具 可以看到sort_mode信息里显示< sort_key

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式

• 如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式
• 如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式

例：

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY position;

单路排序的过程：

1. 从索引name找到第一个满足 name = ‘LiLei’ 条件的主键 id
2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
3. 从索引name找到下一个满足 name = ‘LiLei’ 条件的主键 id
4. 重复步骤 2、3 直到不满足 name = ‘LiLei’
5. 对 sort_buffer 中的数据按照字段 position 进行排序
6. 返回结果给客户端

双路排序的过程：

1. 从索引name找到第一个满足 name = ‘LiLei’ 条件的主键 id
2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
3. 从索引name找到下一个满足 name = ‘LiLei’ 条件的主键 id
4. 重复步骤 3、4直到不满足 name = ‘LiLei’
5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

区别：
单路排序会把所有需要查询的字段都放到 sort buffer 中

双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段

注意
如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

4. 索引设计原则

4.1 应该等到主体业务功能开发完毕，再实际分析怎么建立索引

4.2 联合索引尽量覆盖条件

尽量让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则

4.3 不要在小基数字段上建立索引

4.4 长字符串可以采用前缀索引

例：
对于varchar(255)的类型大字段可以考虑针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)

注意：
order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理

4.5 where与order by冲突时优先where

因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多

4.6 基于慢sql查询做优化

参考https://blog.csdn.net/qq_40884473/article/details/89455740