【MySQL 原理分析】之 Explain & Trace 深入分析全模糊查询（%%）走索引的原理

古城微笑少年丶 2023-07-13 03:02 41阅读 0赞

## 一、背景 ##

今天，交流群有一位同学提出了一个问题。看下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu_size_16_color_FFFFFF_t_70]  
之后，这位同学确实也发了一个全模糊查询走索引的例子：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu_size_16_color_FFFFFF_t_70 1]  
到这我们可以发现，这两个sql最大的区别是：一个是查询全字段（select \*），而一个只查询主键（select id）。

此时，又有其他同学讲了其他方案：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu_size_16_color_FFFFFF_t_70 2]  
全文索引这个不用说，那是能让全模糊查询走索引的。但是索引覆盖这个方案，我觉得才是符合背景的：

1、因为提问的背景就是模糊查询字段是普通索引，而普通索引只查询主键就能用上覆盖索引。

2、并且背景中，就是只查询主键（ID）就显示用上索引了。

## 二、数据准备和场景重现 ##

#### 1、准备表和数据： ####

创建 user 表，给 phone 字段加了个普通索引：

CREATE TABLE `user` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `phone` varchar(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_phone` (`phone`) USING BTREE COMMENT 'phone索引'
    ) ENGINE=InnoDB AUTO_INCREMENT=200007 DEFAULT CHARSET=utf8;

准备10万条数据意思意思：

delimiter ;
    CREATE DEFINER=`root`@`localhost` PROCEDURE `iniData`()
    begin
      declare i int;
      set i=1;
      while(i<=100000)do
        insert into user(name,age,phone) values('测试', i, 15627230000+i);
        set i=i+1;
      end while;
    end;;
    delimiter ;
    
    call iniData();

#### 2、执行 SQL ，查看执行计划： ####

explain select * from user where phone like '%156%';
    explain select id from user where phone like '%156%';

#### 3、执行结果： ####

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>user</td> 
   <td></td> 
   <td>ALL</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>99927</td> 
   <td>11.11</td> 
   <td>Using where</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>user</td> 
   <td></td> 
   <td>index</td> 
   <td></td> 
   <td>index_phone</td> 
   <td>36</td> 
   <td></td> 
   <td>99927</td> 
   <td>11.11</td> 
   <td>Using where; Using index</td> 
  </tr> 
 </tbody> 
</table>

我们可以发现，第二条 SQL 确实是显示用上了 `index_phone` 索引。

但是细心的同学可能会发现：`possible_keys` 竟然为空！有猫腻。。。

> **我这里先说一下 prossible\_keys 和 key 的关系：**
> 
> 1、`possible_keys` 为可能使用的索引，而 `key` 是实际使用的索引；
> 
> 2、正常是： `key` 的索引，必然会包含在 `possible_keys` 中。

还有猫腻一点就是：使用索引和不使用索引读取的行数（rows）竟然是一样的！

## 三、验证和阶段性猜想 ##

上面讲到，`possible_keys` 和 `key` 的关系，那么我们利用正常的走索引来验证一下。

下面的 SQL， 不是全模糊查询，而是右模糊查询，保证是一定走索引的，我们分别看看此时 `possible_keys` 和 `key` 的值：

explain select id from user where phone like '156%';

执行结果：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>partitions</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>user</td> 
   <td></td> 
   <td>range</td> 
   <td>index_phone</td> 
   <td>index_phone</td> 
   <td>36</td> 
   <td></td> 
   <td>49963</td> 
   <td>100</td> 
   <td>Using where; Using index</td> 
  </tr> 
 </tbody> 
</table>

这里太明显了：

1、`possible_keys` 里确实包含了 `key` 里的索引。

2、 并且`rows` 瞬间降到 49963，整整降了一倍，并且 `filtered` 也达到了 100。

#### 阶段猜想： ####

1、首先，`select id from user where phone like '%156%';` 因为**覆盖索引**而用上了索引 `index_phone`。

2、possible\_keys 为 null，证明用不上索引的树形查找。很明显，`select id from user where phone like '%156%';` 即使显示走了索引，但是读取行数 **rows** 和 `select * from user where phone like '%156%';` 没有走索引的 **rows** 是一样的。

3、那么，我们可以猜测到，`select id from user where phone like '%156%';` 即使因为覆盖索引而用上了 `index_phone` 索引，但是却没用上树形查找，只是正常顺序遍历了索引树。所以说，其实这两条 SQL 在表字段不多的情况下，查询性能应该差不了多少。

## 四、通过 Trace 分析来验证 ##

#### 我们分别利用 Trace 分析对于这两个 SQL 优化器是如何选择的。 ####

##### 1、查询全字段： #####

-- 开启优化器跟踪
    set session optimizer_trace='enabled=on';
    select * from user where phone like '%156%';
    -- 查看优化器追踪
    select * from information_schema.optimizer_trace;

下面我们只看 TRACE 就行了：

{ 
      "steps": [
        { 
          "join_preparation": { 
            "select#": 1,
            "steps": [
              { 
                "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`phone` AS `phone` from `user` where (`user`.`phone` like '%156%')"
              }
            ]
          }
        },
        { 
          "join_optimization": { 
            "select#": 1,
            "steps": [
              { 
                "condition_processing": { 
                  "condition": "WHERE",
                  "original_condition": "(`user`.`phone` like '%156%')",
                  "steps": [
                    { 
                      "transformation": "equality_propagation",
                      "resulting_condition": "(`user`.`phone` like '%156%')"
                    },
                    { 
                      "transformation": "constant_propagation",
                      "resulting_condition": "(`user`.`phone` like '%156%')"
                    },
                    { 
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "(`user`.`phone` like '%156%')"
                    }
                  ]
                }
              },
              { 
                "substitute_generated_columns": { 
                }
              },
              { 
                "table_dependencies": [
                  { 
                    "table": "`user`",
                    "row_may_be_null": false,
                    "map_bit": 0,
                    "depends_on_map_bits": [
                    ]
                  }
                ]
              },
              { 
                "ref_optimizer_key_uses": [
                ]
              },
              { 
                "rows_estimation": [
                  { 
                    "table": "`user`",
                    "table_scan": { 
                      "rows": 99927,
                      "cost": 289
                    }
                  }
                ]
              },
              { 
                "considered_execution_plans": [
                  { 
                    "plan_prefix": [
                    ],
                    "table": "`user`",
                    "best_access_path": { 
                      "considered_access_paths": [
                        { 
                          "rows_to_scan": 99927,
                          "access_type": "scan", // 顺序扫描
                          "resulting_rows": 99927,
                          "cost": 20274,
                          "chosen": true
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 99927,
                    "cost_for_plan": 20274,
                    "chosen": true
                  }
                ]
              },
              { 
                "attaching_conditions_to_tables": { 
                  "original_condition": "(`user`.`phone` like '%156%')",
                  "attached_conditions_computation": [
                  ],
                  "attached_conditions_summary": [
                    { 
                      "table": "`user`",
                      "attached": "(`user`.`phone` like '%156%')"
                    }
                  ]
                }
              },
              { 
                "refine_plan": [
                  { 
                    "table": "`user`"
                  }
                ]
              }
            ]
          }
        },
        { 
          "join_execution": { 
            "select#": 1,
            "steps": [
            ]
          }
        }
      ]
    }

##### 2、只查询主键 #####

set session optimizer_trace='enabled=on';
    select id from user where phone like '%156%';
    -- 查看优化器追踪
    select * from information_schema.optimizer_trace;

下面我们继续只看 TRACE 就行了：

{ 
      "steps": [
        { 
          "join_preparation": { 
            "select#": 1,
            "steps": [
              { 
                "expanded_query": "/* select#1 */ select `user`.`id` AS `id` from `user` where (`user`.`phone` like '%156%')"
              }
            ]
          }
        },
        { 
          "join_optimization": { 
            "select#": 1,
            "steps": [
              { 
                "condition_processing": { 
                  "condition": "WHERE",
                  "original_condition": "(`user`.`phone` like '%156%')",
                  "steps": [
                    { 
                      "transformation": "equality_propagation",
                      "resulting_condition": "(`user`.`phone` like '%156%')"
                    },
                    { 
                      "transformation": "constant_propagation",
                      "resulting_condition": "(`user`.`phone` like '%156%')"
                    },
                    { 
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "(`user`.`phone` like '%156%')"
                    }
                  ]
                }
              },
              { 
                "substitute_generated_columns": { 
                }
              },
              { 
                "table_dependencies": [
                  { 
                    "table": "`user`",
                    "row_may_be_null": false,
                    "map_bit": 0,
                    "depends_on_map_bits": [
                    ]
                  }
                ]
              },
              { 
                "ref_optimizer_key_uses": [
                ]
              },
              { 
                "rows_estimation": [
                  { 
                    "table": "`user`",
                    "table_scan": { 
                      "rows": 99927,
                      "cost": 289
                    }
                  }
                ]
              },
              { 
                "considered_execution_plans": [
                  { 
                    "plan_prefix": [
                    ],
                    "table": "`user`",
                    "best_access_path": { 
                      "considered_access_paths": [
                        { 
                          "rows_to_scan": 99927,
                          "access_type": "scan", // 顺序扫描
                          "resulting_rows": 99927,
                          "cost": 20274,
                          "chosen": true
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 99927,
                    "cost_for_plan": 20274,
                    "chosen": true
                  }
                ]
              },
              { 
                "attaching_conditions_to_tables": { 
                  "original_condition": "(`user`.`phone` like '%156%')",
                  "attached_conditions_computation": [
                  ],
                  "attached_conditions_summary": [
                    { 
                      "table": "`user`",
                      "attached": "(`user`.`phone` like '%156%')"
                    }
                  ]
                }
              },
              { 
                "refine_plan": [
                  { 
                    "table": "`user`"
                  }
                ]
              }
            ]
          }
        },
        { 
          "join_execution": { 
            "select#": 1,
            "steps": [
            ]
          }
        }
      ]
    }

好了，到这里我们可以发现，在 Trace 分析里面，都没显示优化器为这两个 SQL 实际选择了什么索引，而只是显示了都是用了 **顺序扫描** 的方式去查找数据。

可能唯一不同点就是：一个使用了主键索引的全表扫描，而另外一个是使用了普通索引的全表扫描；**但是两个都没用上树形查找，也就是没用上 B+Tree 的特性来提升查询性能。**

## 六、最后总结 ##

1、当全模糊查询的 SQL 只查询主键作为结果集时，因为覆盖索引，会用上查询字段对应的索引。

2、即使用上了索引，但是却没用上树形查找的特性，只是正常的顺序遍历。

3、而正常的全表扫描也是主键索引的顺序遍历，所以说，其实这两者的性能其实是差不多的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200309172555930.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200309172606317.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200309172629690.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hvd2luZnVu,size_16,color_FFFFFF,t_70