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

目录 MySQL 优化 子查询优化 待排序的分页查询的优化 给排序字段添加索引 给排序字段跟 select 字段添加复合索引 给排序字段加索引 + 手动回表 解决办法 排序优化 MySQL 优化 子查询优化

                        目录MySQL 优化子查询优化待排序的分页查询的优化给排序字段添加索引给排序字段跟 select 字段添加复合索引给排序字段加索引 + 手动回表解决办法排序优化

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 的深度越深，查询的效率越慢