聊聊如何实现sql优化

木子江Y
346 阅读7分钟

一、SQL语句逻辑执行顺序

在聊sql优化之前，先了解一下sql语句逻辑执行顺序。

序号执行顺序核心操作关键说明
1FROM & JOIN确定数据源表和连接方式（INNER/LEFT JOIN等）计算笛卡尔积后应用ON条件筛选连接结果，生成中间虚拟表vt1。外连接会在此阶段补全未匹配数据。
2WHERE对虚拟表vt1进行行级过滤仅能使用普通列条件（如id>10），过滤后生成vt2。
3GROUP BY按指定列分组生成分组虚拟表vt3。后续步骤仅能操作分组列或聚合函数结果。
4聚合函数执行COUNT/SUM/AVG等聚合运算在分组后计算（如HAVING COUNT(*)>5），生成vt4。
5HAVING过滤分组后的数据作用于vt4，可使用聚合函数（如AVG(salary)>5000），生成vt5。与WHERE不同，处理的是分组后结果。
6SELECT选择输出列并处理表达式生成vt6。此时可定义列别名（如total AS t），但不可在WHERE中使用别名。
7DISTINCT去重操作对vt6去重生成vt7。若存在GROUP BY，此步骤可能被优化。
8ORDER BY按指定列排序生成有序游标vt8（非标准表结构）。
9LIMIT/OFFSET限制返回行数最终截取结果（如分页查询）。深分页（LIMIT 100000,10）需结合ID游标优化。

 二、SQL语句优化

1、避免使用select *，仅选择需要的字段，减少数据传输，同时避免回表查询，提高查询效率。

select id, order_num, user_name from `order`;

2、用连接查询代替子查询

众所周知，子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，生成一个临时表，外层语句在临时表获取所需数据。执行完毕，需要删除临时表，造成额外的消耗。连接查询，可以避免临时表的生成，同时注意合理使用join，避免连接表过多。

#通过子查询找到用户对应订单号
select order_num 
from order 
where user_id in (select id from user where name = '李华' and status = 1);

#连表查询实现方式
select order_num 
from order
left join user on order.user_id = user.id and user.status = 1

 3、 避免使用or条件
OR条件可能导致索引失效，尽量使用UNION ALL替代

SELECT * FROM user 
WHERE role = 'admin'
UNION ALL
SELECT * FROM user 
WHERE role = 'client';

4、union all代替union

union会自动进行去重操作，比较耗时。

SELECT * FROM user WHERE role = 'admin'
UNION ALL
SELECT * FROM user WHERE role = 'client';

5、合理使用in和exists

在子查询返回大量数据时，exists比in更高效

SELECT * FROM user
WHERE EXISTS (SELECT 1 FROM order WHERE order.user_id = user.id);

在子查询返回数据少时，使用in可能更高效（小表驱动大表）

select order_num 
from order 
where user_id in (select id from user where status = 1);

6、多用limit

如果我们想获取某个用户首次下单的时间，在不使用limit情况下，可以对这个用户的订单数据按时间进行升序排序，在业务代码层面取出第一条数据即可，但是这样需要先查询出所有的数据，浪费时间。相反，加上limit 1限制条件，取出排序后的第一条，效率会更高一些。

select id, create_time 
from `order`
where user_id = 4343242
order by create_time asc
limit 1;

7、分页查询

有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。在mysql中分页一般用的limit关键字：

select *
from user
limit 1000 20;

当表的数据量变大时，使用limit关键字进行分页的话效率就会降低很多。例如 limit 1000000, 20 时，他会查出1000020条数据，然后丢弃前面1000000条，只保留后面20条，可想而知这又多浪费时间。优化方式如下：每次分页之前，获取上次分页最大主键id(id需要连续)

select *
from user
where id > 1000000
limit 20;

三、索引优化

1、为经常用于查询条件的列创建索引

在大数据量的情况下，为经常用于查询条件的列创建索引可以加快查询速度。

create index idx_name on user(name);

2、避免过度索引

索引过多会增大增删改操作的成本，定期审查索引使用情况，删除不必要索引。

drop index idx_name on user;

3、使用复合索引

当查询条件涉及多列时，创建复合素引可以提高查询效率。注意列的顺序应根据查询频率和选择性来确定。

create index idx_order_id_no_status on order(id, no, status);

最左匹配原则:

使用复合索引时，当某个索引列为范围查询，可能导致后面索引列失效。

（1）场景一：等值查询+范围查询

explain select  * from order where id = 1234 and no > 43535 and status = 1;

分析：idx_order_id_no_status索引生效，但是索引列id和no生效，status失效

（2）全范围查询

explain select  * from order where id > 234 and no = 43535 and status = 1;

分析：idx_order_id_no_status索引生效，但是只有索引列id生效

4、使用覆盖索引
确保索引包含查询所需的所有列，减少回表查询。

create index idx_order_id_no_status on order(id, no, status);
select id, no, status from `order` where no = id = 43244;

四、数据库设计优化

1、合理设置字段类型，避免使用text类型。select查询中出现text类型字段，会消耗大量的网络和IO带宽，成本高。

2、在设计表结构时，根据具体情况可以冗余一些字段，提高查询效率。

3、针对数据量大情况，还可以进行分库分表，根据具体情况选择合适的方式，可以提高查询效率。例如，关于订单数据表我们可以根据下单时间进行水平分表，这样就可以根据规定时间点，去查询对应的表，提高查询效率。

五、数据库配置优化

1、根据情况选择合适的存储引擎，如InnoDB、MyISAM等。

2、增加数据库缓冲区，减少磁盘I/O操作。

3、根据实际运行情况调整数据库的各项参数，如缓冲池大小、日志文件大小等。

SET GLOBAL innodb_buffer_pool_size = 2G;
SET GLOBAL max_connections = 500;

4、使用主从复制+读写分离 ，减轻主数据库压力，主库负责写操作、从库负责读操作。

六、缓存的使用

1、开启数据库的查询缓存机制，合理配置缓存大小

-- 启用查询缓存
SET GLOBAL query_cache_type = 1;
-- 设置查询缓存大小
SET GLOBAL query_cache_size = 64 * 1024 * 1024;

2、对于不经常访问和不经常变化的数据，在业务层代码中可以采用redis进行存储，减少磁盘I/O次数，提高查询效率。

七、监控与调优验证工具

1、监控数据库性能指标

-- 查看当前连接数与线程状态
SHOW STATUS LIKE 'Threads_%';  
-- 查询InnoDB缓冲池命中率（需定期采样计算）
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 ≈ (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

2、慢日志开启

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 定义慢查询阈值（秒）

3、使用EXPLAIN关键字分析查询计划，找出性能瓶颈

explain select * from `order` where staus = 2;

八、总结

本章从多方面介绍了SQL优化内容，希望可以给大家带来帮助。如果您觉得有帮助的话，挥挥您发财的小手点个小赞。

​

avatar
文章
阅读
粉丝