数据库系统优化是通过调整数据库结构、参数及应用程序以提升系统性能的技术，核心目标是将低效SQL语句转换为高效等效语句。人工智能自动优化技术通过重写SQL语句实现性能提升。优化技术演变历经三代工具：第一代解析执行计划，第二代推荐索引优化，第三代提出语法改进建议。优化策略贯穿数据库生命周期，其中设计阶段优化成本最低且收益最大。

参考文章：

【MySQL调优】如何进行MySQL调优？从参数,数据,建模,索引,SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）

MySQL高级篇——关联查询和子查询优化

MySQL高级篇——排序、分组、分页优化

数据库引擎查询流程

通用查询流程

  1. 解析 SQL 语句：数据库引擎先将 SQL 语句解析成内部的执行计划，包括了查询哪些数据表、使用哪些索引、如何连接多个数据表等信息。
  2. 优化查询计划：数据库引擎对内部的执行计划进行优化，根据查询的复杂度、数据量和系统资源等因素，选择最优的执行计划。
  3. 执行查询计划：数据库引擎根据执行计划，通过 I/O 操作读取数据表的数据，进行数据过滤、排序、分组等操作，最终返回结果集。
  4. 缓存查询结果：如果查询结果集比较大或者查询频率较高，数据库引擎会将查询结果缓存在内存中，以加速后续的查询操作。

MySQL 中 select 的执行流程

  1. 连接器：主要作用是建立连接、管理连接及校验用户信息。
  2. 查询缓冲：查询缓冲是以key-value的方式存储，key就是查询语句，value就是查询语句的查询结果集；如果命中直接返回。
    • 8.0版本废弃：注意，MySQL 8.0已经删除了查询缓冲。从MySQL 5.6版本开始，官方将Query Cache设置为了默认关闭。
    • 原因：官方给出的原因是此功能比较鸡肋，而且减少性能的可变性确实通常比提高峰值吞吐量更重要，尤其是在生产环境中。稳定的性能可以确保用户体验的一致性，并减少系统出现瓶颈或宕机的风险。
    • 方案：官方给出了所替代的解决方案建议——使用第三方工具客户端缓存ProxySQL 来代替Query Cache。
  3. 分析器：词法句法分析生成语法树。
  4. 优化器：指定执行计划，选择查询成本最小的计划。
  5. 执行器：根据执行计划，从存储引擎获取数据，并返回客户端

image-20250727122422644

ProxySQL

基本介绍

一个MySQL中间件，一个高性能的 MySQL 代理，一个用 C++ 开发的轻量级产品。旨在提高 MySQL 服务器的性能、可伸缩性和可用性。MySQL官方推荐的Query Cache替换方案。

同类产品：DBproxy、MyCAT、OneProxy

安装配置

1
2
3
4
5
6
7
8
9
10
11
# 安装
sudo yum install proxysql
# 配置： 修改/etc/proxysql.cnf 直接配置，或者访问管理接口配置
-- 连接到管理接口
mysql -u admin -padmin -h 127.0.0.1 -P 6032
-- 添加MySQL服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '192.168.1.100', 3306);
-- 加载配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;
-- 保存配置到磁盘
SAVE MYSQL SERVERS TO DISK;

功能说明

  • 查询缓存

  • 负载均衡：支持自动摘除宕机的DB

  • 读写分离

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    -- 主库
    INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, '192.168.1.101', 3306);

    -- 从库
    INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '192.168.1.102', 3306);
    INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '192.168.1.103', 3306);
    -- 将所有写操作（INSERT、UPDATE、DELETE）定向到主库
    INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) 
    VALUES (1, 1, '^INSERT', 10);
    INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) 
    VALUES (2, 1, '^UPDATE', 10);
    INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) 
    VALUES (3, 1, '^DELETE', 10);
     
    -- 将所有读操作（SELECT）定向到从库
    INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) 
    VALUES (4, 1, '^SELECT', 20);
    -- 加载并保存配置
    LOAD MYSQL SERVERS TO RUNTIME;
    SAVE MYSQL SERVERS TO DISK;

  • 实时监控

  • 连接池

  • 动态加载配置

  • 访问控制

  • ProxySQL集群

连接查询优化

优化方案

  • 外连接小表驱动大表：LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
  • 内连接驱动表由优化器决定：INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
  • 被驱动表优先创建索引：被驱动表的JOIN字段要创建索引；
  • 两表连接字段类型必须一致：两个表JOIN字段数据类型保持绝对一致。防止自动类型转换导致索引失效。
  • 关联替代子查询：能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)。子查询是一个SELECT查询的结果作为另一个SELECT语句的条件。
  • 多次查询代替子查询：不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
  • 衍生表建不了索引

被驱动表优先创建索引

外连接查询时，右表就是被驱动表，建议加索引。

原因：因为MySQL连接查询底层是先查过滤条件下的左表，按左表查询的结果，再以连接字段作为条件查询结果，所以右表的连接字段加索引，将极大地提高查询性能。

驱动表与被驱动表：

  • 驱动表：在连接操作中，驱动表是首先被读取的表。MySQL会从驱动表中读取数据行，然后在被驱动表中寻找匹配的行。
  • 被驱动表：被驱动表是连接操作中第二个被读取的表。对于驱动表中的每一行，MySQL会在被驱动表中寻找匹配的行。

左外连接：优先右表创建索引，连接字段类型要一致

优先右表创建索引：因为左表是查所有数据，右表是按条件查询，所以右表的条件字段创建索引价值更高一点。

连接字段类型要一致：两个表的card字段一定要是同一类型，如果类型不同会导致隐式类型转换从而索引失效。

内连接：驱动表由数据量和索引决定

内连接查到的是交集，两个表谁做驱动表查的结果是一样的。所以查询优化器会根据查询成本选择驱动表。驱动表就是主表，被驱动表就是从表。驱动表的选择依据：

  • 没索引的表：当只有一个表有索引时，查询优化器会选择没索引的表作为驱动表。
  • 小表：当两个表都有或都没有索引时，数据量小的表为驱动表。

join语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会很长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

  1. 驱动表和被驱动表：驱动表就是主表，被驱动表就是从表、非驱动表。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 对于内连接来说：
    SELECT * FROM A JOIN B ON ... 
    # 优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。
    # 对于外连接来说：
    CREATE TABLE a(f1 INT, f2 INT, INDEX(f1)) ENGINE=INNODB;
    CREATE TABLE b(f1 INT, f2 INT) ENGINE=INNODB;
    INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
    INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
    # 测试
    EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);
    EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);
    # 通常，大家会认为A就是驱动表，B就是被驱动表。但也未必。

  2. Simple Nested-Loop Join (简单嵌套循环连接)

    算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result.. 以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断：

    image-20250726165251064

    这种方式效率是非常低的，以上述表A数据100条，表B数据1000条计算，则A*B=10万次。开销统计如下:

    开销统计 SNLJ
    外表扫描次数 1
    内表扫描次数 A
    读取记录数 A+B * A
    JOIN比较次数 B * A
    回表读取记录次数 0

​ 当然mysql肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nested-Loop Join优化算法。

  1. Index Nested-Loop Join （索引嵌套循环连接）

    Index Nested-Loop Join其优化的思路主要是为了减少内存表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内存表的每条记录去进行比较，这样极大的减少了对内存表的匹配次数。

    image-20250726165315601

    驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表（外表）。

    开销统计 SNLJ INLJ
    外表扫描次数 1 1
    内表扫描次数 A 0
    读取记录数 A+B * A A+B(match)
    JOIN比较次数 B * A A*Index(Height)
    回表读取记录次数 0 B(match) (if possible)

    如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高。

  2. Block Nested-Loop Join（块嵌套循环连接）

    如果存在索引，那么会使用index的方式进行ioin，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了!0的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。
    不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 **join buffer缓冲区**，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join bufer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和ioinbuffer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

    注意：

    这里缓存的不只是关联表的列，select后面的列也会缓存起来。

    在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

    image-20250726165457696

    开销统计 SNLJ INLJ BNIJ
    外表扫描次数 1 1 1
    内表扫描次数 A 0 A*used_column_size/join_buffer_size+1
    读取记录数 A+B * A A+B(match) A+B*(A*used_column_size/join_buffer_size)
    JOIN比较次数 B * A A*Index(Height) B*A
    回表读取记录次数 0 B(match) (if possible) 0

    参数设置：

    • block_nested_loop：通过show variables like ‘%optimizer_switch% 查看 block_nested_loop状态。默认是开启的。

    • join_buffer_size：驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认join_buffer_size=256k。

    1
    mysql> show variables like '%join_buffer%';

    join_buffer_size的最大值在32位操作系统可以申请4G，而在64位操作系统下可以申请大于4G的Join Buffer空间（64位Windows除外，其大值会被截断为4GB并发出警告）。

  3. Join小结

    1. 整体效率比较：INLJ > BNLJ > SNLJ

    2. 永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是表行数 * 每行大小）

    3. 为被驱动表匹配的条件增加索引(减少内存表的循环匹配次数)

    4. 增大join buffer size的大小（一次索引的数据越多，那么内层包的扫描次数就越少）

    5. 减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）

    6. Hash Join

      从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

      • Nested Loop：对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。

      • Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。

        • 这种方式适合于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。

        • 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。

        • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接（如WHERE A.COL1 = B.COL2），这是由Hash的特点决定的。

      类别 Nested Loop Hash Join
      使用条件 任何条件 等值连接(=)
      相关资源 CPU、磁盘I/0 内存、临时空间
      特点 当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。 当缺乏索引或者索引条件模糊时，Hash Join比NestedLoop有效。在数据仓库环境下，如果表的纪录数多，效率高。
      缺点 当索引丢失或者查询条件限制不够时，效率很低;当表的纪录数多时，效率低。 为建立哈希表，需要大量内存。第一次的结果返回较慢。

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：

  1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
  2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会 受到一定的影响。
  3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

子查询原理：

子查询是指在一个SQL语句中嵌套另一个完整的SQL查询。它可以作为主查询的一部分，也可以作为WHERE、FROM或HAVING子句的一部分。子查询的执行顺序是先执行子查询，然后将其结果作为外部查询的条件或数据源。

子查询优化成关联查询

性能：

  • 数据量小、过滤条件简单时，子查询效率高一点；数据量大、过滤条件复杂时，关联查询效率高很多。综合考虑，建议关联查询。
  • 子查询的缺点：
    • 嵌套查询：子查询可能导致嵌套查询，一个子查询套另一个子查询，又套另一个子查询，这会增加查询的复杂性并降低性能。
    • 数据重复检索：子查询可能需要对数据进行多次检索，尤其是在相关子查询中。而连接则允许数据库一次性检索所有需要的数据，从而减少I/O操作和计算开销。
  • 语义清晰度：还是建议使用关联查询，阅读起来更直白、明确，层次结构清晰，后期维护成本也越低，查询次数也一般更低一些。

注意：如果见到子查询+范围查询的SQL，要直接进行优化，例如：

1
2
3
4
# 查询每个学生的出生地
SELECT id, fullname, birth_city FROM person WHERE id IN ( SELECT person_id FROM student);
# 优化后
SELECT id, fullname, birth_city FROM person p LEFT JOIN student s ON p.id = s.person_id;

因为范围查询会使索引失效，再加上使用子查询，优化前的SQL性能是很慢的。

结论：能够直接多表关联的尽量使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。子查询是一个SELECT查询的结果作为另一个SELECT语句的条件。

多次查询代替子查询

在复杂SQL中，同一个子查询语句可能在整个SQL中多次出现，这就导致了性能浪费。这种情况下，结合Java代码多次查询而不用子查询，可以使这个重复子查询语句只查一次，从而提高性能。

误区：通常可能认为多次查询肯定没一次查询快，因为MySQL查询两次肯定就有两次I/O调用过程，而查询一次只有一次调用过程。

如果是简单SQL，只调用了一次子查询，那肯定是子查询快，当同一个子查询语句可能在整个SQL中多次出现时，用Java代码，肯定是更快的，虽然IO次数多，但这么多次子查询缩减成了一次，性能是快了的。

结论：不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

临时表代替子查询

如果一个复杂SQL中，多次用到了同一个子查询，可以尝试将其抽离出来，优化成临时表。这样可以避免重复计算、减少查询次数，从而提高查询性能。

并且可维护性也有效提高，每次修改时只需要修改这一个临时表，而不需要手动一个个修改子查询语句。

​

最终结论：尽量不要使用NOT IN或者NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化

排序优化建议

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

MySQL支持两种排序方式：

  • Index排序：索引排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高，推荐使用。
  • FileSort排序：FileSort 排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

优化建议：

  • 优化器自动选择排序方式：MySQL支持索引排序和FileSort排序，索引保证记录有序性，性能高，推荐使用。FileSort排序是内存中排序，数据量大时产生临时文件在磁盘里排序，效率低还占用大量CPU。并不是说FileSort一定效率低，一些情况它可能效率高。例如没覆盖索引的左模糊、“不等于”查询，全表扫描效率比索引遍历再回表更高。
  • 要符合最左前缀：where后条件和order by字段创建联合索引，顺序要需要符合最左前缀。例如索引(a,b,c)，查询where a=1 order by b,c。
  • 范围查询右边排序索引失效：例如索引(a,b,c)，查询where a>1 order by b,c，导致b,c排序不能走索引，需要filesort。
  • 要么全升序要么全降序：排序顺序必须要么全部DESC，要么全部ASC。乱序会导致索引失效。
  • 待排序数量大时会导致索引失效：待排序数据量大约超过一万个，就不走索引走filesort了。建议用limit和where过滤，减少数据量。数据量很大时，索引排序完需要回表查所有数据，性能很差，还不如FileSort在内存中排序效率高。并不是说使用limit一定会走索引排序，关键看的是数据量，数据量过大时优化器会使用FileSort排序。
  • 优先范围字段加索引：当【范围条件】和【group by 或者 order by】的字段出现二选一时，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。这样即使范围查询导致排序索引失效，效率依然比只索引排序字段时候高。如果只能过滤一点点，那就优先索引放到排序字段上。
  • 调优FileSort ：无法使用 Index 排序时，需要对 FileSort 方式进行调优。例如增大sort_buffer_size（排序缓冲区大小）和 max_length_for_sort_data（排序数据最大长度）
1
2
3
4
5
6
7
8
# 不加索引直接走filesort排序
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;
# 加索引后，order by 时不limit导致数据量过大,从而索引失效：
CREATE INDEX idx_age_classid_name ON student(age,classId,name);
# 索引失败。没有limit
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;
# 索引成功，key_len为73
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10000;

order by 时顺序错误，索引失效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 创建索引age,classid,stuno
# call proc_drop_index('atguigudb2','student');
CREATE INDEX idx_age_classid_stuno ON student (age,classid,stuno);
# 索引失效，不符合最左前缀
EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10;
# 索引失效，不符合最左前缀
EXPLAIN SELECT * FROM student ORDER BY classid,name LIMIT 10;
# 索引失效，不符合最左前缀
EXPLAIN SELECT * FROM student WHERE classid=1 ORDER BY age,stuno;
# 全走索引，虽然不符合最左前缀，但因为查询量小，优化器先排序三个字段，再where找10个返回。
# 优化器认为索引比filesort效率高，就用了索引
EXPLAIN SELECT * FROM student WHERE classid=1 ORDER BY age,stuno LIMIT 10;
# 索引成功，符合最左前缀
EXPLAIN SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10;
# 索引成功，符合最左前缀
EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10;

order by 时规则不一致，索引失效（顺序错，不索引；方向反，不索引）

必须符合最左前缀和“全升序或全降序”

1
2
3
4
5
6
7
8
9
10
# 创建索引age,classid,stuno
CREATE INDEX idx_age_classid_stuno ON student (age,classid,stuno);
# 没符合“全升序或全降序”，索引失效
EXPLAIN SELECT * FROM student ORDER BY age DESC,classid ASC LIMIT 10;
# 没符合最左前缀，索引失效
EXPLAIN SELECT * FROM student ORDER BY classid DESC,name DESC LIMIT 10;
# 没符合“全升序或全降序”，索引失效
EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;
# 符合最左前缀，符合“全升序或全降序”，索引成功
EXPLAIN SELECT * FROM student ORDER BY age DESC,classid DESC LIMIT 10;

limit数据量小时，不满足最左前缀也可能走索引，先排序再where筛选。

1
2
3
4
5
6
7
8
9
10
CREATE INDEX idx_age_classid_stuno ON student (age,classid,stuno);
CREATE INDEX idx_age_classid_name ON student(age,classId,name);
# 都走了索引。
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid LIMIT 10;
# 都走了索引。
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,name;
# 都没用索引，不符合最左前缀
EXPLAIN SELECT * FROM student WHERE classid=45 order by age;
# 全走了索引。因为limit数据量小，优化器直接先用排序字段索引排序，然后再where筛选10个
EXPLAIN SELECT * FROM student WHERE classid=45 order by age limit 10;

范围查找导致索引失效：下面有索引(userDbid,addressDbid,createTime)，userDbid,addressDbid走了索引，因为addressDbid是范围查找，导致createTime索引失败。

范围查询时索引字段选择

  1. mysql自动选择最优的方案：两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择 idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的。
  2. 过滤比例高时优先过滤字段加索引：当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。

案例分析：查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

1
2
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
# 结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

为了去掉 filesort 创建索引，查询效率高了一点

1
2
3
4
# 创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
只有age走了索引： 优化掉了 using filesort

尽量让where的过滤条件和排序使用上索引，发现查询效率更高

1
2
3
# 建一个三个字段的组合索引，发现using filesort依然存在：
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

发现using filesort依然存在，所以name并没有用到索引，而且type还是range光看名字其实并不美好。因为stuno是一个范围过滤，所以索引后面的字段不会在使用索引了 。但分析发现filesort运行速度比索引还快，而且快了很多，几乎一瞬间就出现了结果。

原因：所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的 stuno<101000 这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

filesort 算法

双路排序和单路排序

双路排序 （慢）

  • MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段 。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种 改进的算法，就是单路排序。

单路排序 （快）

从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论及引申出的问题

  • 由于单路是后出的，总体而言好过双路
  • 但是用单路有问题
    • 在sort_buffer中，单路要比多路多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排…从而多次I/O。
    • 单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

调优filesort

  1. 尝试提高 sort_buffer_size

    不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程
    (connection)的1M-8M之间调整。MySQL5.7，InnoDB存储引擎默认值是1048576字节，1MB。

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SHOW VARIABLES LIKE %sort_buffer_size%';
    +---------------------------+-----------+
    | Variable_name             | Value     |
    +---------------------------+-----------+
    | innodb_sort_buffer_size   | 1048576   |
    | myisam_sort_buffer_size   | 8388608   |
    | sort_buffer_size          | 262144    |
    +---------------------------+-----------+

  2. 尝试提高 max_length_for_sort_data

    1
    SHOW VARIABLES LIKE '%max_length_for_sort_data%'; # 默认1924字节

    提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort buffer size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort data，使用双路算法，否则使用单路算法.1024-8192字节之间调整。

  3. Order by 时select * 是一个大忌。最好只Query需要的字段。

    • 当Query的字段大小总和小于max_ength_for_sort_data，而且排序字段不是TEXTBLOB 类型时，会用改进后的算法–单路排序，否则用老算法——多路排序。
    • 两种算法的数据都有可能超出sort_bufer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/0，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

分组优化

  • 跟排序优化近似：group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。

  • 最左前缀：group by 先排序再分组，遵照索引建的最佳左前缀法则

  • 调优FileSort：当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置

  • where效率高于having，能写在where限定的条件就不要写在having中了。where是分组前过滤，having是分组后过滤。

  • 尽量不排序分组、节省cpu：减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

  • 使用limit：包含order by、group by、distinct的查询语句，where条件过滤出的结果集请保持1000行以内，否则SQL会很慢。

分页查询优化

深分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。

目前问题： offset非常大时，需要查询大量无用的数据量再分页，性能差。

一个常见问题：limit 2000000,10。此时需要MySQL排序前200000010 记录，仅仅返回2000000~2000010 的记录，其他记录丢弃，查询排序的代价常大。并且select * 需要回表，更耗费时间。

1
2
3
4
5
6
# 直接查询 limit 2000000,10
EXPLAIN SELECT * FROM student LIMIT 2000000,10; 
# 优化方案一：主键自增的表：直接查范围之后的10个数据。可以把Limit 查询转换成某个位置的查询 。
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;
# 优化方案二：主键不自增的表：当前表内连接排序截取后的主键表，连接字段是主键。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

也可以用子查询，子查询优化成关联查询。

带排序的深分页优化

1
2
3
4
5
6
7
# 优化前：查询根据age逆序排列的深分页
EXPLAIN SELECT * FROM student order by age desc LIMIT 2000000,10; 
# 优化方案一：主键不有序的表根据主键排序，先给主键分页，然后内连接原表：当前表内连接排序截取后的主键表，连接字段是主键。因为查主键是在聚簇索引树查，不用回表，排序和分页很快
EXPLAIN SELECT * FROM student t1,(SELECT id FROM student ORDER BY age desc LIMIT 2000000,10) t2 
WHERE t1.id=t2.id
# 优化方案二：主键有序的表根据非主键排序，可以得到上一页最后一条记录x，那么目标页码的所有记录id都比x.id小（因为逆序，且排序依据其实是age,id），目标页码的所有记录age都比x.age小或等于。需要传参数。
EXPLAIN SELECT * FROM student WHERE id < \#{x.id} AND age <= \#{x.age} ORDER BY age DESC LIMIT 10;