MySQL——SQL优化

前言

SQL优化是提升数据库性能、减少系统资源消耗的关键。本文将从多个方面介绍SQL优化的策略、最佳实践和实际案例，帮助您编写更高效的SQL语句。

一、SQL语句优化技巧

1.避免使用SELECT *

问题：SELECT * 会查询出表中的所有数据，即使有些列在业务中不需要，这样会导致数据库资源浪费（CPU、IO、内存、网络带宽），并可能导致无法使用覆盖索引，增加回表操作。

SELECT * 会消耗更多的 CPU。

SELECT * ⽆⽤字段增加⽹络带宽资源消耗，增加数据传输时间，尤其是

SELECT * ⼤字段（如 varchar、blob、text）。

SELECT * SELECT * ⽆法使⽤ MySQL 优化器覆盖索引的优化（基于 MySQL 优化 器的“覆盖索引”策略⼜是速度极快，效率极⾼，业界极为推荐的查询优化⽅ 式）

SELECT * <字段列表> 可减少表结构变更带来的影响。

回表是什么：MySQL回表是什么？哪些情况下会回表-CSDN博客

优化：明确指定需要查询的列，例如：

SELECT name,age FROM user WHERE id=1;

这样有助与减少数据传输量，并提高使用覆盖索引的可能性。

2.使用UNION ALL 代替 NUION

问题：NUINO 操作会进行去重，这涉及到遍历、排序和比较，回消耗更多的CPU资源和时间。

UNION ALL 不会对结果集进行去重操作，获取到的数据包含重复项。

优化：如果明确知道两个结果集没有重复数据，或者业务允许重复数据，应使用UNION ALL。

例如：

(SELECT * FROM user WHERE id=1)UNION ALL ((SELECT * FROM user WHERE id=2); 

3.小表驱动大表

问题：在多表关联查询时，如果驱动选择不当，可能导致性能下降。

优化：

IN适用于左边大表，右边小表：

SELECT * FROM order WHERE user_id IN (SELECT id FROM user WHERE status=1);

EXISTS适用于左边小表，右边大表：

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

4.批量操作

问题：循环条件插入、更新或删除数据会导致多次数据请求，增加网络IO和数据负载。

优化： 采用批量操作，将多条SQL语句合并为一条。

例如批量插入：

INSERT INTO order(id,code,user_id) VALUES(123,'001',100),(124,'002',100),(125,'003',101);

建议每次批量操作的数据量控制在500以内

循环插入数据：

批量插入数据：

5.善用LIMIT

问题：查询只需要少量数据时，不加LIMIT会返回所有匹配数据，浪费资源。

优化： 当只需要查询部分数据时，使用LIMIT限制返回的记录数。

例如：

SELECT id, create_date FROM order WHERE user_id=123 ORDER BY create_date ASC LIMIT 1;

6.避免IN中值过多

问题：IN子句中包含大量值时，可能会导致查询效率低下甚至超时。

优化：

•在SQL中对数据使用LIMIT限制。

•在业务代码中限制IN子句中的值数量，例如限制在500条以内。

•如果数据量过大，可以考虑分批次、多线程查询。

7.，增量查询

问题：频繁全量同步数据会导致大量不必要的IO和网络传输

优化：针对需要同步的数据，使用增量查询，只同步发生变化的数据。例如，通过时间戳或自增ID来标识新增或修改的数据。

8.避免使用子查询（针对MySQL 5.5及更早版本）

问题：在MySQL 5.5及更早版本中，子查询的执行计划可能效率低下，先查外表再匹配内表。

优化：尽量将子查询改写为JOIN关联查询。

例如：

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id

虽然MySQL 5.6及更高版本对SELECT子查询进行了优化，但对UPDATE/DELETE子查询仍然无效，因此建议避免使用。

9.用IN代替OR

问题：在WHERE子句中使用多个OR连接条件，可能导致索引失效。

优化L：对于等值判断，使用IN代替OR，

例如：

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id

于连续的数值，优先使用BETWEEN。

10.禁止不必要的ORDER BY排序

问题：不必要的排序会消耗额外的CPU资源。

优化如果对结果没有排序要求，尽量避免使用ORDER BY ，在ORDER BY 查询中，如果不需要排序，可以添加ORDER BY NULL 来禁止默认排序：

SELECT goods_id,COUNT(*) FROM t GROUP BY goods_id ORDER BY NULL;

11.避免随机取记录

问题： 使用ORDER BY RAND()会导致全表扫描，无法利用索引，性能极差。

优化：避免使用ORDER BY RAND()。如果需要随机记录，可以考虑其他方法，例如先获取总数，然后生成随机数，再结合LIMIT和OFFSET查询。

12.优化GROUP BY语句

问题：GROUP BY操作可能导致使用临时表和文件排序，影响性能。

优化：

•如果对GROUP BY结果没有排序要求，添加ORDER BY NULL。

•尽量让GROUP BY过程利用表的索引，通过EXPLAIN查看结果中是否包含Using temporary和Using filesort。

•适当调大tmp_table_size参数，尽量使用内存临时表。

•如果数据量实在太大，可以使用SQL_BIG_RESULT提示，让优化器直接使用排序算法（磁盘临时表）。

13.区分IN 和 EXISTS

问题：IN和EXISTS在某些场景下性能差异较大，选择不当可能影响效率。

优化：

•IN适合于外表大而内表小的情况。

•EXISTS适合于外表小而内表大的情况。

14.减少使用通配符字符

问题：在WHERE子句中使用%开头的模糊查询（例如LIKE '%keyword%'）会导致索引失效，进行全表扫描。

优化： 尽量避免使用%开头的模糊查询。如果必须使用，可以考虑使用全文索引或搜索引擎。

15.避免在WHERE子句中使用！=或<>操作符

问题：：!=或<>操作符会导致引擎放弃使用索引而进行全表扫描。

优化：尽量使用=、>、<、>=、<=、BETWEEN等操作符

16.避免在WHWERE子句中对列进行函数操作或者表达式计算

问题：对列进行函数操作或表达式计算会导致索引失效。

优化：将函数操作或表达式计算放到=的右侧。

例如：

//将
WHERE DATE(create_time) = '2023-01-01'
//改为
WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00'

17.使用JOIN代替子查询（通用原则）

问题：子查询在某些数据库中可能效率不高，尤其是在处理大量数据时。

优化：尽可能使用JOIN来连接表，因为JOIN通常会比子查询更高效，数据库优化器能更好地处理JOIN操作。

注：尽量避免多表做JOIN，阿⾥巴巴《Java 开发⼿册》中说到：超过三个表禁⽌ join。需要 join 的字段，数据类型保持绝对⼀致;多表 关联查询时，保证被关联 的字段需要有索引。

join 的效率⽐较低，主要原因是因为其使⽤嵌套循环（Nested Loop）来实现关

联查询，三种不同的实现效率都不是很⾼：

Simple Nested-Loop Join ：没有进过优化，直接使⽤笛卡尔积实现 join， 逐⾏遍历/全表扫描，效率最低。

Block Nested-Loop Join ：利⽤ JOIN BUFFER 进⾏优化，性能受到 JOIN BUFFER ⼤⼩的影响，相⽐于 Simple Nested-Loop Join 性能有所提 升。不过，如果两个表的数据过⼤的话，⽆论如何优化，Block NestedLoop Join 对性能的提升都⾮常有限。

Index Nested-Loop Join ：在必要的字段上增加索引，使 join 的过程中可以使⽤到这个索引，这样可以让 Block Nested-Loop Join 转换为 Index Nested-Loop Join，性能得到进⼀步提升。

实际业务场景避免多表 join 常⻅的做法有两种：

1. 单表查询后在内存中⾃⼰做关联 ：对数据库做单表查询，再根据查询结果进 ⾏⼆次查询，以此类推，最后再进⾏关联。

2. 数据冗余，把⼀些重要的数据在表中做冗余，尽可能地避免关联查询。很笨 的⼀张做法，表结构⽐较稳定的情况下才会考虑这种做法。进⾏冗余设计之 前，思考⼀下⾃⼰的表结构设计的是否有问题。更加推荐第⼀种，这种在实际项⽬中的使⽤率⽐较⾼，除了性能不错之外，还有

如下优势：

1. 拆分后的单表查询代码可复⽤性更⾼ ：join 联表 SQL 基本不太可能被复 ⽤。

2. 单表查询更利于后续的维护 ：不论是后续修改表结构还是进⾏分库分表，单 表查询维护起来都更容易。 不过，如果系统要求的并发量不⼤的话，我觉得多表 join 也是没问题的。很多公 司内部复杂的系统，要求的并发量不⾼，很多数据必须 join 5 张以上的表才能查出来。

可以去知乎上面找找相关话题。

二、索引优化

1.合理创建索引

重要性：索引时提高查询性能最有效的方法之一。

原则：

•在WHERE子句、JOIN条件和ORDER BY子句中经常使用的列上创建索引。

•避免在选择性低的列（如性别）上创建索引。

•考虑联合索引，覆盖查询的多个列。

•索引不是越多越好，过多的索引会增加写操作的开销。

2.避免索引失效

常见情况：

•在索引列上使用函数或表达式。

•使用OR连接条件（除非所有条件都使用索引）。

•LIKE '%keyword%'。

•数据类型不匹配。

•使用!=或<>。

3.删除长期未使用的索引

删除⻓期未使⽤的索引，不⽤的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使⽤

三、数据库结构优化

1.数据类型选择

原则：选择合适的数据类型，尽量使用占用空间小的数据类型。例如，能用TINYINT就不用INT，能用VARCHAR就不用TEXT

2.范式和反范式

原则：

•范式： 减少数据冗余，保持数据一致性，但可能导致多表联查，增加查询复杂度。

•反范式： 适当增加数据冗余，减少JOIN操作，提高查询性能，但可能导致数据一致性问题。 根据业务需求权衡范式和反范式。

3.表分区

作用：将大表分割成更小、更可管理的部分，提高查询效率，尤其是在处理历史数据时。

四、查询执行计划分析

工具： 使用EXPLAIN（MySQL）、EXPLAIN PLAN（Oracle）等工具分析SQL语句的执行计划。

关注点：

•type： 访问类型，从好到坏依次是system > const > eq_ref > ref > range > index > ALL。

•rows： 扫描的行数，越少越好。

•Extra： 额外信息，关注Using filesort（文件排序）和Using temporary（使用临时表），这些都表示需要优化。

五、数据库配置优化

1. 调整缓冲区大小

作用： 增加数据库缓冲区（如MySQL的innodb_buffer_pool_size）可以减少磁盘IO，提高性能。

2. 优化连接池

作用： 合理配置数据库连接池大小，避免频繁创建和销毁连接，减少资源消耗。

六.分页优化

普通的分⻚在数据量⼩的时候耗费时间还是⽐较短的

SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC LIMIT

如果数据量变⼤，达到百万甚⾄是千万级别，普通的分⻚耗费的时间就⾮常⻓了。

SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC LIMIT
SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC LIMIT

优化：

SELECT `score`,`name` FROM `cus_order` WHERE id >= (SELECT id

我们先查询出 limit 第⼀个参数对应的主键值，再根据这个主键值再去过滤并

limit，这样效率会更快。

阿⾥巴巴《Java 开发⼿册》中也有对应的描述：

七、其他优化建议

1. 避免大事务

问题： 大事务会长时间锁定资源，影响并发性能。

优化： 将大事务拆分为小事务，减少事务的持续时间。

2. 使用存储过程和函数

作用： 存储过程和函数可以减少网络传输，提高执行效率，并增强代码的复用性。

3. 读写分离与分库分表

作用：

•读写分离： 将读操作和写操作分发到不同的数据库服务器，提高并发能力。

•分库分表： 当单表数据量过大时，将数据分散到多个数据库或表中，提高查询和写入性能。

4.Show Profile 分析 SQL 执⾏性能

为了更精准定位⼀条 SQL 语句的性能问题，需要清楚地知道这条 SQL 语句运⾏

时消耗了多少系统资源。 SHOW PROFILE 和 SHOW PROFILES 展示 SQL 语

句的资源使⽤情况，展示的消息包括 CPU 的使⽤，CPU 上下⽂切换，IO 等待，

内存使⽤等。

MySQL 在 5.0.37 版本之后才⽀持 Profiling， select @@have_profiling 命

令返回 YES 表示该功能可以使⽤。

注意 ： SHOW PROFILE 和 SHOW PROFILES 已经被弃⽤，未来的 MySQL 版本中可能会被删除，取⽽代之的是使⽤ Performance Schema

想要使⽤ Profiling，请确保你的 profiling 是开启（on）的状态。 你可以通过 SHOW VARIABLES 命令查看其状态：

也可以通过 SELECT @@profiling 命令进⾏查看：

默认情况下， Profiling 是关闭（off）的状态，你直接通过 SET @@profil

ing=1 命令即可开启。

开启成功之后，我们执⾏⼏条 SQL 语句。执⾏完成之后，使⽤ SHOW PROFILE

S 可以展示当前 Session 下所有 SQL 语句的简要的信息包括 Query_ID（SQL

语句的 ID 编号） 和 Duration（耗时）。

具体能收集多少个 SQL，由参数 profiling_history_size 决定，默认值

为 15，最⼤值为 100。如果设置为 0，等同于关闭 Profiling。

总结

SQL优化是一个持续的过程，需要结合具体的业务场景和数据库类型进行。通过遵循上述优化策略和最佳实践，并结合执行计划分析，可以显著提升SQL查询性能和数据库整体效率。

参考资料：

MySQL 8.2 Optimizing SQL Statements：

https链接://dev.mysql.com/doc/refman/8.0/en/statementoptimization.html

为什么阿⾥巴巴禁⽌数据库中做多表 join - Hollis：

https://mp.weixin.qq.com/s/GSGVFkDLz1hZ1OjGndUjZg

MySQL 的 COUNT 语句，竟然都能被⾯试官虐的这么惨 - Hollis：

https://mp.weixin.qq.com/s/IOHvtel2KLNi-Ol4UBivbQ

MySQL 性能优化神器 Explain 使⽤分析：

https://segmentfault.com/a/1190000008131735

如何使⽤ MySQL 慢查询⽇志进⾏性能优化 ：

https://kalacloud.com/blog/how-to-use-mysql-slow-query-logprofiling-mysqldumpslow/