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视图来查询哪些索引从未被使用。