【面试突击】数据库面试实战-SQL 优化（加更）

精选原创

11来了 2024-02-05 17:18:15 博主文章分类：面试突击 ©著作权

©著作权归作者所有：来自51CTO博客作者11来了的原创作品，请联系作者获取转载授权，否则将追究法律责任

欢迎关注公众号【11来了】，及时收到 AI 前沿项目工具及新技术的推送！
在我后台回复「资料」可领取编程高频电子书！
在我后台回复「面试」可领取硬核面试笔记！

MySQL 中的 SQL 优化

这里主要说一下 MySQL 中如何对 SQL 进行优化，其实主要还是根据索引来进行优化的，如果好好了解下边的 SQL 优化，可以对 MySQL 的理解更加深入接下来的 SQL 优化，以下边这个 employees 表为例进行优化：

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

‐‐ 插入一些示例数据
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zqy',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

order by、group by 优化

下边是 8 种使用 order by 的情况，我们通过分析以下案例，可以判断出如何使用 order by 和 where 进行配合可以走using index condition（索引排序）而不是 using filesort（文件排序）

case1

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and position = 'dev' order by age;

【面试突击】数据库面试实战-SQL 优化（加更）_主键

分析：查询用到了 name 索引，从 key_len=74 也能看出，age 索引列用在排序过程中，因此 Extra 字段里没有 using filesort

case2

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_02

分析：从 explain 执行结果来看，key_len = 74，查询使用了 name 索引，由于用了 position 进行排序，跳过了 age，出现了 Using filesort

case3

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by age, position;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_03

分析：查找只用到索引name，age和position用于排序，与联合索引顺序一致，因此无 using filesort。

case4

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position, age;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_04

分析：因为索引的创建顺序为 name,age,position，但是排序的时候 age 和 position 颠倒位置了，和索引创建顺序不一致，因此出现了 using filesort

case5

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 18 order by position, age;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_05

分析：与 case 4 相比，Extra 中并未出现 using filesort，并且查询使用索引 name，age，排序先根据 position 索引排序，索引使用顺序与联合索引顺序一致，因此使用了索引排序

case6

EXPLAIN SELECT * FROM employees WHERE name = 'zqy' order by age asc, position desc;

分析：虽然排序字段列与联合索引顺序一样，但是这里的 position desc 变成了降序排序，导致与联合索引的排序方式不同，因此产生了 using filesort

case7

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei', 'zqy') order by age, position;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_06

分析：先使用索引 name 拿到 LiLei，zqy 的数据，之后需要根据 age、position 排序，但是根据 name 所拿到的数据对于 age、position 两个字段来说是无序的，所以需要使用到 filesort。

为什么根据 name in 拿到的数据对于 age、position 来说是无序的：
对于下图来说，如果取出 name in (Bill, LiLei) 的数据，那么对于 age、position 字段显然不是有序的，因此肯定无法使用索引扫描排序

【面试突击】数据库面试实战-SQL 优化（加更）_sql_07

case8

EXPLAIN SELECT * FROM employees WHERE name > 'a' order by name;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_08

分析：对于上边这条 sql 来说，是 select * 因此 mysql 判断不走索引，直接全表扫描更快，因此出现了 using filesort

EXPLAIN SELECT name FROM employees WHERE name > 'a' order by name;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_09

分析：因此可以使用覆盖索引来优化，只通过索引查询就可以查出我们需要的数据，不需要回表，通过覆盖索引优化，因此没有出现 using filesort

优化总结

MySQL支持两种方式的排序 filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。
order by满足两种情况会使用Using index。

order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。

尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果order by的条件不在索引列上，就会产生Using filesort。
能用覆盖索引尽量用覆盖索引
group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 order by null 禁止排序。注意，where 高于 having，能写在 where 中的限定条件就不要去 having 限定了。

分页查询优化

我们实现分页功能可能会用以下 sql：

select * from employees limit 10000, 10;

该 sql 表示从 employees 表的第 10001 行开始的 10 行数据，虽然只查询了 10 条数据，但是会先去读取 10010 条记录，再抛弃前 10000 条数据，因此如果查询的数据比较靠后，效率非常低

1、根据自增且连续的主键排序的分页查询

该优化必须保证主键是自增的，并且主键连续，中间没有断层。

未优化 sql

select * from employees limit 9000, 5;

结果：

【面试突击】数据库面试实战-SQL 优化（加更）_sql_10

执行计划：

【面试突击】数据库面试实战-SQL 优化（加更）_主键_11

因为 id 是连续且自增的，所以可以直接通过 id 判断拿到 id 比 9000 大的 5 条数据，效率更高：

优化后 sql

select * from employees where id > 9000 limit 5;

结果

【面试突击】数据库面试实战-SQL 优化（加更）_sql_12

执行计划：

【面试突击】数据库面试实战-SQL 优化（加更）_主键_13

总结

如果主键空缺，则不能使用该优化方法

2、根据非主键字段排序的分页查询

未优化 sql

select * from employees order by name limit 9000, 5;
> OK
> 时间: 0.066s

【面试突击】数据库面试实战-SQL 优化（加更）_sql_14

explain select * from employees order by name limit 9000, 5;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_15

根据执行计划得，使用了全表扫描（type=ALL），并且 Extra 列为 using filesort，原因是联合索引为（name，age，position），但是使用了 select * 中有的列并不在联合索引中，如果使用索引还需要回表，因此 mysql 直接进行全表扫描

优化 sql

优化的点在于：让在排序时返回的字段尽量为覆盖索引，这样就会走索引并且还会使用索引排序

先让排序和分页操作查出主键，再根据主键查到对应记录

select * from employees e inner join (select id from employees order by name limit 9000, 5) ed on e.id = ed.id;
> OK
> 时间: 0.032s

【面试突击】数据库面试实战-SQL 优化（加更）_sql_16

explain select * from employees e inner join (select id from employees order by name limit 9000, 5) ed on e.id = ed.id;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_17

根据执行计划得，优化后查询走了索引，并且排序使用了索引排序

总结

优化后，sql 语句的执行时间时原 sql 的一半

CREATE TABLE `t1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;  
‐‐ 插入一些示例数据
‐‐ 往t1表插入1万行记录
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
    declare i int;
    set i=1;
    while(i<=10000)do
        insert into t1(a,b) values(i,i);
        set i=i+1;
    end while;
end;;
delimiter ;
call insert_t1();

‐‐ 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
    declare i int;
    set i=1;
    while(i<=100)do
        insert into t2(a,b) values(i,i);
        set i=i+1;
    end while;
end;;
delimiter ;
call insert_t2();

in 和 exists 优化

原则：小表驱动大表

in：当 B 表的数据集小于 A 表的数据集时，使用 in

select * from A where id in (select id from B)

exists：当 A 表的数据集小于 B 表的数据集时，使用 exists

将主查询 A 的数据放到子查询 B 中做条件验证，根据验证结果（true 或 false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id)

总结

exists 只返回 true 或 false，因此子查询中的 select * 也可以用 select 1 替换

count(*)查询优化

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size=0;
set global query_cache_type=0;
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_18

分析：4 条 sql 语句的执行计划一样，说明这 4 个 sql 的执行效率差不多

总结

当字段有索引，执行效率：count(*) ≈ count(1) > count(字段) > count(主键id)
如果字段有索引，走二级索引，二级索引存储的数据比主键索引少，所以 count(字段) 比 count(主键id) 效率更高
当字段无索引，执行效率：count(*) ≈ count(1) > count(主键id) > count(字段)
count(1) 和 count(*) 比较

count(1) 不需要取出字段统计，使用常量 1 做统计，count(字段) 还需要取出字段，所以理论上 count(1) 比 count(字段) 快
count(*) 是例外，mysql 并不会把全部字段取出来，会忽略所有的列直接，效率很高，所以不需要用
count(字段) 或 count(常量) 来替代 count(*)

为什么对于 count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索
性能应该更高，mysql内部做了点优化（在5.7版本才优化）。

上一篇：【面试突击】数据库面试实战-MySQL锁（加更）

下一篇：【面试突击】网络通信面试实战

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯