关于MySQL查询语句的优化详解

MySQL 优化

子查询优化

• 将子查询改变为表连接，尤其是在子查询的结果集较大的情况下；
• 添加复合索引，其中复合索引的包含的字段应该包括 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 '订单详情表';

子查询

select * from orders where id in (select order_id from OrderDetails where product_code = 'PC50');

优化1：改为表连接

select * from orders as t1 inner join orderdetails o on t1.id = o.order_id where product_code='PC50';

优化2：给 order_id 字段添加索引

优化3：给 product_code 字段添加索引

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

优化4：给 order_id 和 product_code 添加复合索引

优化5：给 product_code 和 order_id 添加复合索引

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

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

待排序的分页查询的优化

现有一个电影表

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 '电影表';

对于浅分页

select score, release_date, film_name from film order by score limit 0, 20;

耗时 825ms

对于深分页

select score, release_date, film_name, introduction from film order by score limit 1500000, 20;

耗时 1s 247ms

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

给排序字段添加索引

给 score 字段添加索引

create index idx_score on film(score);

结果

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

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

查看深分页的执行情况

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

解释

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

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

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

给排序字段跟 select 字段添加复合索引

给 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 
)

select * from t_order order by col_1, col_2, col_3, 需要创建联合索引 col_1,col_2,col_3

select * from t_order order by col_1, col_2，需要创建联合索引 col_1, col_2, col_3

select * from t_order order by col_1 asc, col_2 desc ，需要创建联合索引 col_1 asc, col_2 desc ，指定索引的排序规则，只有在 MySQL 8 中才支持

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

• 没有遵守最左匹配原则

select * from t_order order by col_1, col_3

select * from t_order order by col_2, col_3

可见都使用到了 ****using filesort

以第一条为例

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

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

• 使用了范围查询

select * from t_order where col1 >= 1 and col1 <= 4 order by col_2

select * from t_order where col1 in(1,2,4) order by col_2

若走该复合索引 (col_1, col_2, col_3) ，可以发现查询计划中使用到了 using filesort

解释

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

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

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

select col_1, col_2 from t_order where col_1 = 2 order by col_2;

若不满足 最左匹配原则

select col_1, col_3 from t_order where col_1 = 2 order by col_3;

则使用到了 using filesort

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