MySQL篇—执行计划之覆盖索引Using index和条件过滤Using where介绍（第三篇，总共三篇）

奈斯DBA

已于 2024-05-26 22:49:51 修改

阅读量1.6k

点赞数 23

分类专栏： MySQL专栏文章标签： mysql 数据库运维

于 2024-03-04 18:07:20 首次发布

本文链接：https://blog.csdn.net/naisiing/article/details/136456859

版权

MySQL专栏专栏收录该内容

17 篇文章 7 订阅

订阅专栏

💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨

💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

SQL语句的执行计划复杂多变，每一次的查询都可能带来截然不同的执行策略。为了真正掌握其内在的工作机制，我们必须深入生产环境，通过反复的实践与观察来积累经验。今天将聚焦于explain Extra输出列中的两个关键要素：“Using index”和“Using where”，带大家一起看一下Using index和Using where对查询计划的影响，当然今天的介绍只是众多Extra输出列中其中的两个。希望通过这篇文章，能够帮助大家更好地理解SQL查询的执行过程，以及如何更有效地优化查询性能。

今天作为统计信息和执行计划的最后一篇，让我们回顾一下前两篇的介绍内容：

第一篇：持久化和非持久化统计信息介绍

第二篇：执行计划介绍

第三篇：执行计划之覆盖索引Using index和条件过滤Using where详细介绍（当前篇）

一、Using index：使用覆盖索引。属于积极现象，一般不需要过多再去干预。

案例：不使用覆盖索引的情况和使用覆盖索引的情况

不使用覆盖索引的情况：

使用覆盖索引的情况：

二、Using where：条件中的字段没有使用索引，或者部分字段没有使用索引。属于消极现象，进行分析干预。

案例：使用到Using where的情况和不使用到Using where的情况

使用到Using where的情况：

不使用到Using where的情况：

一、Using index：使用覆盖索引。属于积极现象，一般不需要过多再去干预。

注意：是使用了覆盖索引，和使用索引不是一个概念，有没有使用索引通过key输出列确定。

官方文档介绍：MySQL :: MySQL 8.0 Reference Manual :: 10.8.2 EXPLAIN Output Format

使用索引（JSON属性：Using_index）

只使用索引树中的信息从表中检索列信息，而不必进行额外的查找来读取实际行。当查询仅使用作为单个索引一部分的列时，可以使用此策略。

对于具有用户定义的聚集索引的InnoDB表，即使Extra列中没有Using索引，也可以使用该索引。如果类型为索引，键为PRIMARY，则会出现这种情况。

显示了EXPLAIN FORMAT=TRADINAL和EXPLAIN ORMAT=JSON所使用的任何覆盖索引的信息。从MySQL 8.0.27开始，它也显示为EXPLAIN FORMAT=TREE。

覆盖索引（Covering Index）：

如果一个索引包含了查询所需的所有字段，则称该索引为覆盖索引。当MySQL查询使用这个索引时，它可以直接从索引中获取所有需要的数据，而无需再回表（即返回原表）查找。

在查询执行计划中，当出现“Using index”时，意味着MySQL已经使用了覆盖索引（Covering Index）来检索数据。这是一个优化提示，表明查询可以仅通过索引来满足，而无需访问实际的数据表。

出现覆盖索引的好处：1、减少I/O操作：因为MySQL可以仅通过索引获取数据，所以它不需要读取整个表的数据，这通常意味着更少的磁盘I/O操作。

2、提高查询速度：由于减少了I/O操作和可能的表查找，查询通常会更快。

3、减少内存使用：由于不需要从表中读取额外的数据，所以内存使用可能会更低。

需要注意的是，虽然“Using index”是一个优化提示，但在某些情况下，它可能不是最优的。例如，如果查询中的某些条件不能有效地使用索引，或者索引本身非常大，那么全表扫描可能更快。

回表（Back to Table）：

"回表"是指当查询语句需要获取的数据不仅仅在索引中，还需要回到主表中进行二次查询获取的过程。

1、回表的影响：回表操作会增加额外的I/O操作和访问时间，影响查询的性能。因为回表需要额外的查找步骤，所以在高并发的情况下，回表操作会导致数据库的负载增加，可能会成为性能瓶颈。

2、如何避免回表：为了避免回表操作，可以使用覆盖索引（Covering Index）的方式。覆盖索引是指创建一个包含了查询所需的所有列的索引，这样就可以直接从索引中获取所需的数据，而无需回到表中查找。使用覆盖索引可以减少I/O操作和提高查询性能。

3、如何优化回表：如果无法避免回表操作，可以通过以下方法进行优化：

一、优化查询语句：尽量减少回表操作的次数，可以通过合理的查询条件、索引设计和查询优化等方式来减少回表操作。

二、调整表结构：如果回表操作非常频繁，可以考虑调整表结构，将需要查询的列放在索引中，或者使用聚簇索引来减少回表操作。

三、使用缓存：如果查询的数据具有一定的重复性，可以考虑使用缓存来减少回表操作。

覆盖索引触发的条件：

1、没有where条件，select查询的返回列包含在索引列中：这意味着查询的结果可以直接从索引中获取，而无需访问实际的数据表。

2、有where条件，where和select都要包含索引列或复合索引：这有助于数据库系统更有效地利用索引进行查找和过滤。

3、查询结果的总字段长度可以接受：如果查询结果的总字段长度过大，可能不适合使用覆盖索引，因为这可能会增加系统的开销。

当满足上述条件时，数据库系统可能会选择使用覆盖索引来执行查询，从而提高查询的效率。不过，请注意，索引的使用和优化是一个复杂的过程，具体是否使用覆盖索引还取决于数据库管理系统的具体实现和查询的具体情况。

案例：不使用覆盖索引的情况和使用覆盖索引的情况

mysql> show index from tb_200w;   ---表现在无索引

mysql> create index idx_tb_200w_ina on tb_200w(id,name,age);    ---创建索引

不使用覆盖索引的情况：

1）没有where条件，select查询的返回列没有在索引列中：

mysql> explain select * from tb_200w;
mysql> explain select sex from tb_200w;
mysql> explain select age,sex from tb_200w;    ---select中包含了索引列和没有索引的列，这种情况肯定是不会使用到覆盖索引的，因为查询语句需要获取的数据不仅仅在索引中，还需要回到主表中进行二次查询获取的过程。

2）有where条件，where或select没有包含索引列或复合索引：

mysql> explain select * from tb_200w where id=1;
mysql> explain select sex from tb_200w where id=1;
mysql> explain select age,sex from tb_200w where id=1;    ---where包含了索引列，但是select中包含了索引列和没有索引的列，这种情况肯定是不会使用到覆盖索引的，因为查询语句需要获取的数据不仅仅在索引中，还需要回到主表中进行二次查询获取的过程。

使用覆盖索引的情况：

1）没有where条件，select查询的返回列包含在索引列中：

mysql> explain select id from tb_200w;
mysql> explain select name from tb_200w;
mysql> explain select age from tb_200w;
mysql> explain select name,age from tb_200w;  
mysql> explain select age,id from tb_200w;                 ---都会使用到覆盖索引，因为select查询的返回列包含在索引列中了

2）有where条件，where和select都要包含索引列或复合索引：

mysql> explain select id from tb_200w where id=1;
mysql> explain select name from tb_200w where id=1;
mysql> explain select age from tb_200w where id=1;
mysql> explain select name,age from tb_200w where id=1;
mysql> explain select age,id from tb_200w where id=1;      ---都会使用到覆盖索引，因为where和select都包含索引列或复合索引了

二、Using where：条件中的字段没有使用索引，或者部分字段没有使用索引。属于消极现象，进行分析干预。

官方文档介绍：MySQL :: MySQL 8.0 Reference Manual :: 10.8.2 EXPLAIN Output Format

使用where（JSON属性：attached_condition）

WHERE子句用于限制哪些行与下一个表匹配或发送到客户端。除非您特别打算从表中提取或检查所有行，否则如果Extra值不是Using where，并且表联接类型是all或index，则查询中可能会出现错误。

Using where介绍：

Using where表示查询在检索到行之后，需要使用WHERE子句中的条件对这些行进行过滤。这并不一定意味着查询没有使用索引，而是意味着即使有索引被使用，索引本身并不能完全满足WHERE子句中的所有条件，因此MySQL需要读取实际的行数据，并在服务器层面对这些行进行额外的过滤。

Using where触发的条件：

1、复合索引与部分条件匹配：如果你有一个复合索引（例如 (a, b, c)），但你的WHERE子句只涉及索引的前两个字段（例如 WHERE a = 1 AND b = 2），那么MySQL可以使用这个复合索引来快速找到匹配的行。但是，如果WHERE子句还包含第三个字段的条件（例如 c > 3），则即使索引被使用，Using where 也会出现在执行计划中，因为需要对找到的行进行额外的过滤。

2、没有合适的索引：如果查询的WHERE子句条件没有对应的索引，或者索引的选择性不高（即索引中的不同值不多），MySQL可能会选择全表扫描而不是使用索引。在这种情况下，Using where 会出现在执行计划中，因为所有检索到的行都需要进行过滤。

避免Using where的触发：

Using where 的出现并不一定意味着查询性能不佳。实际上，在很多情况下，即使需要额外的过滤，使用索引仍然比全表扫描更快。然而，如果发现Using where经常出现并且查询性能不佳，需要考虑：

1、添加或优化索引：确保你的查询条件有合适的索引支持。

2、调整查询：尝试重写查询或分解复杂查询为多个简单查询，以便更好地利用索引。

3、分析数据和索引统计信息：使用ANALYZE TABLE命令更新表的统计信息，以便MySQL优化器能够做出更好的决策。

案例：使用到Using where的情况和不使用到Using where的情况

mysql> show index from tb_200w;   ---表现在无索引

mysql> create index idx_tb_200w_id on tb_200w(id);                 ---创建单列索引
mysql> create index idx_tb_200w_ina on tb_200w(name,age,cardid);   ---创建复合索引

使用到Using where的情况：

mysql> explain select * from tb_200w where tel=13604981449;      ---直接查询普通列，而不引用索引列
mysql> explain select * from tb_200w where age=38;               ---查询的列在复合索引中，但是并没有引用前置列(name列)，所以不会使用到索引
mysql> explain select * from tb_200w where cardid=1000110123;     ---查询的列在复合索引中，但是并没有引用前置列(name列)，所以不会使用到索引

不使用到Using where的情况：

mysql> explain select * from tb_200w where id=1000;       ---使用单列索引
mysql> explain select * from tb_200w where name='n1000';     ---使用复合索引，引用了前置列(name列)
mysql> explain select * from tb_200w where name='n1000' and age=38 and cardid='1000110123';
---使用复合索引，引用了前置列(name列)