网站开发 密码,wordpress左侧插件,学校网站建设与维护方案,微网站 服务器☘️博主介绍☘️#xff1a; ✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux#xff0c;也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章#xff0c;并且也会默默的点赞收藏加关注❣… ☘️博主介绍☘️ ✨又是一天没白过我是奈斯DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注❣️❣️❣️ 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 FORMATTRADINAL和EXPLAIN ORMATJSON所使用的任何覆盖索引的信息。从MySQL 8.0.27开始它也显示为EXPLAIN FORMATTREE。 覆盖索引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 id1;
mysql explain select sex from tb_200w where id1;
mysql explain select age,sex from tb_200w where id1; ---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 id1;
mysql explain select name from tb_200w where id1;
mysql explain select age from tb_200w where id1;
mysql explain select name,age from tb_200w where id1;
mysql explain select age,id from tb_200w where id1; ---都会使用到覆盖索引因为where和select都包含索引列或复合索引了 二、Using where条件中的字段没有使用索引或者部分字段没有使用索引。属于消极现象进行分析干预。 官方文档介绍MySQL :: MySQL 8.0 Reference Manual :: 10.8.2 EXPLAIN Output Format 使用whereJSON属性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 tel13604981449; ---直接查询普通列而不引用索引列
mysql explain select * from tb_200w where age38; ---查询的列在复合索引中但是并没有引用前置列(name列)所以不会使用到索引
mysql explain select * from tb_200w where cardid1000110123; ---查询的列在复合索引中但是并没有引用前置列(name列)所以不会使用到索引 不使用到Using where的情况 mysql explain select * from tb_200w where id1000; ---使用单列索引
mysql explain select * from tb_200w where namen1000; ---使用复合索引引用了前置列(name列)
mysql explain select * from tb_200w where namen1000 and age38 and cardid1000110123;
---使用复合索引引用了前置列(name列) 关于统计信息和执行计划的内容全部就到这里执行计划所涉及的内容太过广泛我所介绍的内容也只是冰山一角以后就靠大家自己去学习和实践。当然我也会继续在CSDN上分享关于执行计划的内容。
