SQL优化手段

发表于 更新于 分类于 ， 阅读次数：

优化慢 SQL

慢查询日志记录了执行时间超过 long_query_time(默认 10s，通常设置为 1s)的所有查询语句，在解决 SQL 慢查询问题时经常会用到。

  • 查询慢查询日志是否开启 (默认关闭)

    1
    show variables like "slow_query_log;

    开启慢查询日志

    1
    SET GLOBAL slow_query_log=ON;

  • 查看慢查询的 超时时间

    1
    show variables like "%long_query_time%";

    修改 long_query_time 参数：

    1
    SET GLOBAL long_query_time=1;

  • 查询当前 慢查询语句的个数

    1
    show global status like "%Slow_queries%";

  • 查询慢查询日志存放位置

    1
    SHOW VARIABLES LIKE "slow_query_log_file";

  • 无论是否超时，未被索引的记录也被记录

    1
    SET GLOBAL log_queries_not_using_indexes = "ON";

  • 慢查询仅记录扫描 行数 > 此参数 的 SQL

    1
    SET SESSION min_examined_row_limit = 100;

设置完成后，可以用 SHOW VARIABLES LIKE "slow%"; 命令查看。

故意执行一条慢查询并查看日志

1
2
3
4
# 故意在百万数据量的表中执行一条 未使用索引的排序语句
SELECT score, name
FROM cus_order
ORDER BY score DESC;

查看日志路径，并去实际查看日志记录

1
SHOW VARIABLES LIKE "slow_query_log_file";
1
2
3
4
5
6
7
8
# Time: 2023-09-25T15:30:40.620921Z
# User@Host: root[root] @  [172.17.0.1]  Id:    18
# Query_time: 0.637583  Lock_time: 0.000002 Rows_sent: 1002000  Rows_examined: 2004000
SET timestamp=1695655839;
# 故意在百万数据量的表中执行一条 未使用索引的排序语句
SELECT score, name
FROM cus_order
ORDER BY score DESC;
  • Query_time 这段代码的运行时长。
  • Lock_time 执行这段代码时，锁定了多久。
  • Rows_sent 慢查询返回的记录数量。
  • Rows_examined 慢查询扫描过的行数。

实际项目中，慢查询日志通常会比较复杂，需要借助一些工具对其进行分析。比如 MySQL 内置的 mysqldumpslow 工具，可以将相同的 SQL 归为一类，并统计出归类项的执行次数和每次执行的耗时等一系列对应的情况。

执行计划：一条语句在经过 MySQL 查询优化器 的优化后，具体的执行方式。执行计划通常用于 SQL 性能分析、优化 等场景。

通过 EXPAIN 命令，可以获取执行计划的相关信息。可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

找到了慢 SQL 后，可以通过 EXPLAIN 命令分析对应的语句。

1
2
3
EXPLAIN SELECT score, name
FROM cus_order
ORDER BY score DESC;

EXPLAIN 命令分析对应的语句的结果

  • select_type 查询的类型
    • SIMPLE: 普通查询（即没有联合查询、子查询）
    • PRIMARY：主查询
    • UNION （UNION 中后面的查询）
    • SUBQUERY
  • table 查询涉及的表或衍生表
  • type 执行方式，判断查询是否高效的重要参考指标，结果值从差到好：
    1. ALL
    2. index
    3. range~index_range
    4. ref
    5. eq_ref
    6. const
    7. system
  • rows SQL 要查找到结果集需要扫描读取的数据行数，原则上 rows 越少越好。

避免使用 SELECT *

  • 需要解析更多的对象、字段、权限、属性等相关内容，会消耗更多的 CPU
  • 无用字段增加网络带宽资源消耗、增加数据传输时间，尤其是大字段（varchar、blob、text）
  • 无法使用 MySQL 优化器 覆盖索引的优化

分页优化

1
2
3
4
SELECT score, `name`
FROM cus_order
ORDER BY score DESC
LIMIT 1000000, 10;

使用 延迟关联 来优化这个分页查询语句：

1
2
3
4
5
6
SELECT score, name
FROM cus_order AS a, (SELECT id 
                      FROM cus_order
                      ORDER BY score DESC
                      LIMIT 1000000, 10) AS b
WHERE a.id = b.id;
  1. 先提取主键
  2. 再将这个主键表与原数据表关联

尽量避免多表做 join

join 的效率比较低，主要是因为使用 嵌套循环（Nested Loop） 来实现关联查询，效率都不是很高。实际业务场景避免多表 join 常见的作法有 2 种：

  1. 单表查询后，在内存中自己做关联。

    对数据库做单表查询，再根据查询结进行二次查询。以此类推，最后再进行关联。

    • 拆分后的单表查询代码可复用性更高。
    • 单表查询更利于后续的维护。

  2. 数据冗余

    把一些重要的数据在表中做冗余，尽可能避免关联查询。很笨的一种做法，表结构比较稳定的情况下才会考虑。

建议不要使用外键与级联

不建议使用外键的主要原因是 对分库分表不友好，性能方面影响较小。

选择合适的字段类型

  1. 某些 字符串 可以 转换成数字类型存储，比如 IP 地址。

    数字是连续的，性能更好，占用空间叶更小。

    MySQL 提供了两个方法来处理 IP 地址：

    1. INET_ATON()：把 ip 转为无符号整型（4-8 位）
    2. INET_NTOA()：把整型的 ip 转为地址

  2. 对于非负的数据（自增 ID、整型 IP、年龄）来说，要 优先使用无符号整型 来存储

    无符号（SIGNED INT） 相对于 有符号（UNSIGNED INT） 多出了一倍的存储空间

  3. 小数值 类型（年龄、状态表示如 0/1）优先使用 TINYINT 类型

  4. 日期类型 一定不要用字符串存储，可以考虑 DATETIME、TIMESTAMP 和数值型 时间戳

    类型 存储空间 日期格式 日期范围 是否携带时区
    DATETIME 5-8 字节 YYYY-MM-DD hh: mm: ss [.fraction] 1000-01-01 00:00:00 [.000000] ~ 9999-12-31 23:59:59 [.999999] 否
    TIMESTAMP 4-7 字节 YYYY-MM-DD hh: mm: ss [.fraction] 1970-01-01 00:00:01 [.000000] ~2038-01-19 03:14:07 [.999999] 是
    数值型时间戳 4 字节 全数字 1970-01-01 00:00:01 之后的时间 否

  5. 金额字段用 decimal，避免精度丢失

    Java 中，对应 BigDecimal

  6. 尽量使用自增 id 作为主键（分布式场景不建议）

    主键如果是 自增 id，每次都会将数据加在 B+树尾部（本质是双向链表），时间复杂度为 O(1)。在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。

    如果主键是 非自增 id，为了让新加入数据后，B+树的叶子节点还能保持有序，需要往叶子节点的中间找，查找过程的时间复杂度是 O($ \log n$)。如果数据页被写满，需要进行页分裂。页分裂操作需要加悲观锁，性能非常低。

    但是 像分库分表这类场景就不建议使用自增 id 作为主键，应该使用分布式 id，比如 uuid。

  7. 不建议使用 NULL 作为列默认值

    • NULL 需要占用空间，空字符串 '' 反而不用占用空间。

    • NULL 会影响聚合函数的结果。

      SUM、AVG 等函数会忽略 NULL 值，COUNT(列名) 也会忽略 NULL，但 COUNT(*) 会统计所有的记录数。

尽量用 UNION ALL 代替 UNION

UNION 会把两个结果集放在一个临时表中再进行去重操作，更耗时、消耗 CPU 资源。

批量操作

批量操作能减少请求数据库的次数，提高性能。

1
2
INSERT INTO cus_order(id, score, name)
VALUES (1, 3512, "user1"), (2, 34335, "user2"), (3, 320985, "user3");

Show PROFILE 分析 SQL 执行性能

SHOW PROFILE 和 SHOW PROFILES 已经被弃用，未来的 MySQL 版本中可能会删除，取代它的是 Performance Schema。

SHOW PROFILE 和 SHOW PROFILES 展示 SQL 语句的资源使用情况，展示的消息包括 CPU 的使用、CPU 上下文切换、IO 等待、内存使用等。

  • 查看是否支持 profiling

    1
    SELECT @@have_profiling;

  • 查看 profiling 是否开启

    1
    2
    3
    4
    # 0是关闭，1是开启
    SELECT @@profiling;

    SET @@profiling = 1;

  • SHOW PROFILES 展示 当前 Session 下所有 SQL 语句的简要信息（Query_ID、Duration）

  • SHOW PROFILE 展示一个 SQL 语句的执行耗时细节

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SHOW PROFILE [type [, type] ...]
    	[FOR QUERY n] # 展示Query_ID为n的执行情况，默认展示最新的一次SQL执行情况
    	[LIMIT row_count [OFFSET offset]]

    type: { # 具体某类资源的消耗情况
    	ALL
    	BLOCK IO
    	CONTEXT SWITCHES
    	CPU
    	IPC
    	MEMORY
    	PAGE FAULTS
    	SOURCE
    	SWAPS
    }

正确使用索引

正确的索引可以大大加快数据的检索速度。

选择合适的字段创建索引

  1. 不为 NULL 的字段

  2. 被频繁查询 的字段

  3. 被作为条件 查询的字段

  4. 频繁需要排序 的字段（索引已经排序）

  5. 被频繁用于连接 的字段

    提高多表连接查询的效率。

被频繁更新的字段应该慎重建立索引

维护索引的成本不小。

尽可能考虑建立 联合索引 而不是单列索引

可以简单理解为 每个索引都对着一棵 B+树。如果一个表的字段过多，索引过多，当表数据达到一个体量之后，索引占用的空间也会很多，且修改索引的耗时也会很多。如果是联合索引，会节约很大的磁盘空间，且修改数据的效率也会提升。

避免冗余索引

冗余索引指的是索引功能相同，**能够命中索引(a, b)就肯定能命中索引(a)**。在大多数情况下，都应该尽量扩展已有的索引，而不是创建新索引。

考虑在 字符串类型 的字段上使用 前缀索引 代替普通索引

避免索引失效

索引失效也是慢查询的主要原因之一。常见的导致索引失效的情况：

  1. SELECT *
  2. 创建了组合索引，但 查询条件未遵守最左匹配原则 （查询中 未使用了该索引的第一列）
  3. 在索引列上进行计算、函数、类型转换等操作
  4. 以 % 开头的 LIKE 查询
  5. 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到
  6. 发生隐式转换 （操作符左右两边的数据类型不一致时，左边为字符类型时发生转换，会导致索引失效，效率极低）

删除长期未使用的索引

可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。