正文

关于MySQL查询语句的优化详解（mysql查询语句）快来看

11 分钟阅读

文章摘要

这篇文章介绍了MySQL查询优化的多种方法，重点在于通过索引、子查询优化以及排序策略来提升性能。文章指出，在处理子查询时，改用表连接而非子查询可显著提升性能；同时，添加复合索引以包含排序字段和关联字段，可优化排序操作。然而，复合索引的性能依赖于字段的有序性，若字段无序则需使用全表扫描或范围查询。此外，文章提到MySQL 8支持自动优化子查询，进一步简化了优化流程。在实际应用中，需确保索引按照最左匹配原则排列，避免使用`using filesort`，否则可能导致性能下降。文章还强调了手动回表等技术在处理深分页查询时的有效性，以及通过业务方法（如限制查询数量）来避免性能瓶颈。整体而言，文章通过实例分析，展示了MySQL查询优化的关键技巧及其实际应用。


目录MySQL 优化子查询优化待排序的分页查询的优化给排序字段添加索引给排序字段跟 select 字段添加复合索引给排序字段加索引 + 手动回表解决办法排序优化
将子查询改变为表连接，尤其是在子查询的结果集较大的情况下；添加复合索引，其中复合索引的包含的字段应该包括 where 字段与关联字段；复合索引中的字段顺序要遵守最左匹配原则；MySQL 8 中自动对子查询进行优化；

现有两个表

create table Orders ( id integer AUTO_INCREMENT PRIMARY KEY, name varchar(255) not null, order_date datetime NOT NULL ) comment ‘订单表’; create table OrderDetails ( id integer AUTO_INCREMENT PRIMARY KEY, order_id integer not null, product_code varchar(20) not null, quantity integer not null ) comment ‘订单详情表’;

子查询

;

优化1：改为表连接

;

优化2：给 字段添加索引

优化3：给 字段添加索引

结果证明：给 字段添加索引 的效果优于给 字段添加索引，因为不用对索引列进行全表扫描

优化4：给  添加复合索引

优化5：给  添加复合索引

对于复合索引 ，因为查询中需要判断 的值是否为 PC51，所以要对 该列进行全索引扫描，性能较低 [ 因为 product_code 不是有序的，先根据 order_id 进行排序，再根据 product_code 进行排序 ]；

对于复合索引 ，因为 本身是有序的，所以可以快速定位到该 然后快速获取该 ，性能较高；

现有一个电影表

create table film ( id integer auto_increment primary key, score decimal(2, 1) not null, release_date date not null, film_name varchar(255) not null, introduction varchar(255) not null ) comment ‘电影表’;

对于浅分页

耗时 825ms

对于深分页

耗时 1s 247ms

若不加处理，浅分页的速度快，limit 的深度越深，查询的效率越慢

给排序字段添加索引

给 score 字段添加索引

结果

浅分页的速度为 60 ms，深分页的速度为 1s 134ms

浅分页的情况得到了优化，而深分页依然很慢

查看深分页的执行情况

其并没有走 score 索引，走的是全表的扫描，所以给排序字段添加索引只能优化浅分页的情况

解释

只给 score 添加索引，会造成回表的情况

对于浅分页，回表的性能消耗小于全表扫描，故走 score 索引；

对于深分页，回表的性能消耗大于全表扫描，故走 全表扫描；

给 score, release_date, film_name 添加复合索引

create index idx_score_date_name on film(score, release_date, film_name);

浅分页的速度为 58 ms，深分页的速度为 357 ms，两者的速度都得到了提升

查看深分页的执行情况

可见其走了复合索引

解释

对于该复合索引，排序的值和查询的值都在索引上，没有进行回表的操作，效率很高。唯一的不足是：若要添加新的查询列，就要更改该索引的列，不够灵活。

改进SQL语句，给 score 字段添加索引

# 给排序字段添加索引 + 手动回表 select score, release_date, film_name,introduction from film a join (select id from film order by score limit 1500000, 20) b on a.id=b.id;

思路：先把 limit 字段的 id 找出来，这里走了 score 索引，效率高。然后再走主键索引根据 id 去寻找；

该语句的执行情况

可见子查询中走了 score 索引，而外查询走了主键索引，效率非常高，执行速度为 297 ms

缺点

由上面的执行计划可见，它创建了一张中间表 ，走的是全表扫描，也就是说，中间表中的记录越多，该执行效率就越慢，观察以下语句，从500000开始查，查找 1500000 条数据；

select score, release_date, film_name,introduction from film a join (select id from film order by score limit 500000, 1500000) b on a.id=b.id;

消耗的时间为：911ms，接近一秒

所以我们可以通过业务的方法，限制每次查询的条数即可

解决办法 给排序的字段 + select 的字段添加复合索引给排序的字段加索引 + 手动回表深分页的性能问题可以通过业务方法解决：限制每次查询的数量等

索引的字段要根据排序的字段走，且要满足最左匹配原则

create table t_order ( id integer primary key auto_increment, col_1 int not null , col_2 int not null , col_3 int not null )

, 需要创建联合索引

，需要创建联合索引

，需要创建联合索引 ，指定索引的排序规则，只有在 MySQL 8 中才支持

索引失效的情况（避免出现 using filesort）

没有遵守最左匹配原则

可见都使用到了 ****

以第一条为例

最左匹配原则的实质是：先根据第一列排序，若第一列的值相同就根据第二列来排序，若第二列的值相同就根据第三列来排序，以此类推；

第一条 SQL 中，排序的字段为 col_2 和 col_3 明显 在抛开 col_1 的情况下，col_2 和 col_3 的顺序是无序的，故要使用 using filesort，不能依靠索引来进行排序；

使用了范围查询

若走该复合索引，可以发现查询计划中使用到了 

解释

经过 col_1 的筛选后，col_2 的数据都是无序的

所以要使用 using filesort 再次根据 col_2 排序

若使用等值查询，则不会出现 ，前提是要满足最左匹配原则

若不满足 最左匹配原则

则使用到了

以上就是关于MySQL查询语句的优化详解的详细内容，更多关于MySQL查询语句优化的资料请关注脚本之家其它相关文章！

您可能感兴趣的文章:MySQL查询语句过程和EXPLAIN语句基本概念及其优化mysql explain的用法(使用explain优化查询语句)Mysql查询语句优化技巧优化MySQL数据库中的查询语句详解mysql优化limit查询语句的5个方法MySQL对limit查询语句的优化方法file.mkdir()、file.mkdirs()和file.createNewFile()的区别