MySQL之索引优化（一）

电玩女神 2022-12-25 00:54 131阅读 0赞

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

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

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70]

在mysql内部没有走索引的情况下，强制该范围查询走索引的话，效率提升并不大，因为回表效率不高。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 1]

**优化方案：**  
覆盖索引优化。尽量让索引覆盖查询的字段  
![在这里插入图片描述][20201129204450242.png]

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

![在这里插入图片描述][20201129204644877.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 2]

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

![在这里插入图片描述][20201129205034374.png]  
注意： 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  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 3]

②：用了position进行排序，跳过了 age，出现了Using filesort。![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 4]  
③：查找只用到索引name，age和position用于排序，无Using filesort  
![在这里插入图片描述][20201129213652382.png]  
④：查找只用到索引name，出现了Using filesort，因为排序的时候age和position颠倒位置了  
![在这里插入图片描述][20201129213825760.png]

⑤：与 ④ 相比，并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort  
![在这里插入图片描述][20201129214135511.png]  
⑥：虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 5]  
⑦：对于排序来说，多个相等条件也是范围查询  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 6]

**总结：**

*  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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70]: /images/20221120/c452494edc5a4c21b0e15f18f5368c9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201129204154835.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg==,size_16,color_FFFFFF,t_70
[20201129204450242.png]: https://img-blog.csdnimg.cn/20201129204450242.png
[20201129204644877.png]: https://img-blog.csdnimg.cn/20201129204644877.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201129204906274.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg==,size_16,color_FFFFFF,t_70
[20201129205034374.png]: https://img-blog.csdnimg.cn/20201129205034374.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201129213509264.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201129213246183.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg==,size_16,color_FFFFFF,t_70
[20201129213652382.png]: https://img-blog.csdnimg.cn/20201129213652382.png
[20201129213825760.png]: https://img-blog.csdnimg.cn/20201129213825760.png
[20201129214135511.png]: https://img-blog.csdnimg.cn/20201129214135511.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20201129214456876.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20201129214719715.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg==,size_16,color_FFFFFF,t_70