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

原文链接：

MySQL高级篇——性能分析工具

【MySQL调优】如何进行MySQL调优？一篇文章就够了！

数据库服务器的优化步骤

在数据库调优中，目标是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。

调优流程：

  1. SHOW STATUS观察服务器状态，是否存在周期性波动；如果存在的话就缓存优化；
  2. 如果还存在不规则延迟或卡顿的话，就开启慢查询、explain分析查询语句；
  3. 如果发现sql等待时间长，就调优服务器参数；如果发现sql执行时间长，就索引优化、表优化；
  4. 如果还存在不规则延迟或卡顿的话，就观察sql查询是否到瓶颈了；是的话就读写分离、分库分表。

三种分析工具（SQL调优三步骤）：慢查询、EXPLAN、SHOW PROFLING

image-20250726121610266

整个流程划分成了观察（Show status） 和行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

优化方式硬件，系统配置，数据库表结构，SQL及索引，从左至右效果渐好，从右至左，成本渐高。

查看系统性能参数

SHOW STATUS LIKE ‘参数’

在MySQL中，可以使用SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下：

1
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

中括号代表可省略。

一些常用的性能参数如下：

  • Connections：连接MySQL服务器的次数。

  • Uptime：MySQL服务器的上线时间。重启服务器后会重置。

  • Slow_queries：慢查询的次数。查询时长超过指定时间，次数越少越好。

  • Innodb_rows_read：Select查询返回的行数

  • Innodb_rows_inserted：执行INSERT操作插入的行数

  • Innodb_rows_updated：执行UPDATE操作更新的行数

  • Innodb_rows_deleted：执行DELETE操作删除的行数

  • Com_select：查询操作的次数。

  • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。

  • Com_update：更新操作的次数。

  • Com_delete：删除操作的次数。

  • last_query_cost：查询优化器上一个查询的成本，最近一次删除用到数据页数量。

查看SQL的查询成本

1
SHOW STATUS LIKE 'last_query_cost';

SQL查询是一个动态的过程，从页加载的角度来看:

  1. 缓冲池查询效率优于从磁盘查

    如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。

    MySQL的缓冲池被分为多个不同的缓存池，其中包括：

    • 查询缓存：用来缓存查询结果。
    • InnoDB缓存池：用来缓存热点表和索引数据页。
    • MyISAM缓存池：用来缓存表数据块。

    当缓冲池中已经存储了较多的数据时，MySQL会使用一种叫做缓冲池替换算法的方法，将部分缓存数据替换出去，以腾出空间为新的数据做缓存。

    MySQL的缓冲池使用的是LRU（最近最少使用）算法，它会优先缓存最近使用的数据。当缓冲池的空间不足时，MySQL会将最不常用的数据从缓冲池中替换出去，以腾出空间缓存新的数据。

  2. 批量顺序查询平均下来每页查询更高

    如果从磁盘中对单一页进行随机读，那么效率是很低的(差不多10ms)，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

    首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

    测试缓冲池缓存已使用的表和索引到内存中，效率高：查询900001和 900001~9000100查询成本差很多，查询速度差不多

    查询 id=900001 的记录，然后看下查询成本，可以直接在聚簇索引上进行查找：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # 查询 id=900001 的记录
    SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001; 
    # 运行结果（1 条记录，运行时间为 0.042s ）
    # 查询优化器的成本，实际上 只需要检索一个页
    mysql> SHOW STATUS LIKE 'last_query_cost';
    +-----------------+----------+
    | Variable_name   | Value    |
    +-----------------+----------+
    | Last_query_cost | 1.000000 |
    +-----------------+----------+

    查询 id 在 900001 到 9000100 之间的记录：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # 查询 id 在 900001 到 9000100 之间的记录
    SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;
    # 运行结果（100 条记录，运行时间为 0.046s ）：
    # 查询优化器的成本，这时我们大概需要进行 20 个页的查询。
    mysql> SHOW STATUS LIKE 'last_query_cost';
    +-----------------+-----------+
    | Variable_name   | Value     |
    +-----------------+-----------+
    | Last_query_cost | 21.134453 |
    +-----------------+-----------+

    虽然页的数量是刚才的 20 倍，但查询的效率没有明显变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

    为什么第二次是直接从缓冲池查

    因为 MySQL 缓存淘汰策略 LRU 最近最少使用，会优先缓存最近查询数据，优先淘汰最近最少使用数据。

    使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

定位执行慢的 SQL：慢查询日志

介绍

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long-query_time 值的SQL，则会被记录到慢查询日志中。 long_query_time的默认值为 10，意思是运行10秒以上(不含10秒)的语句，认为是超出了最大忍耐时间值。

主要作用是发现执行时间特别长的 SOL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，结合explain进行全面分析，以解决问题。

默认情况下，MySQL数据库 没有开启慢查询日志 ，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件 。

监控报警

利用监控工具（例如Prometheus+Grafana）监控MySQL，发现查询性能变慢，报警提醒运维人员

常见监控工具：

  1. MySQL Enterprise Monitor：由Oracle提供，它提供实时和历史的MySQL性能监控（查询性能、服务器状态、数据库复制等）。
  2. Percona Monitoring and Management：由Percona提供的开源工具，提供性能监控、查询分析、数据库配置等功能。
  3. MyTOP：一个基于命令行的工具，用于实时监控MySQL数据库的性能。
  4. MySQL Performance Schema：MySQL自带的性能监控工具，可以通过查询Performance Schema表来获取有关数据库性能和资源利用情况的详细信息。
  5. Nagios：一个通用的网络监控工具，可以使用插件来监控MySQL数据库的各种指标。
  6. Zabbix：一个通用的网络监控工具，可以使用插件来监控MySQL数据库的各种指标。
  7. Datadog：一个云端监控服务，提供对MySQL数据库的性能和状态的实时监控。
  8. Prometheus + Grafana：一组流行的开源工具，通过Prometheus监控MySQL数据库，使用Grafana创建仪表板进行可视化。

开启慢查询日志参数

  1. 慢查询是否开启 

    1
    2
    3
    4
    5
    6
    7
    # 慢查询是否开启
    mysql> show variables like '%slow_query_log';
    +-----------------+-----------+
    | Variable_name   | Value     |
    +-----------------+-----------+
    | low_query_log   | OFF       |
    +-----------------+-----------+

  2. 开启慢查询日志slow_query_log

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # 开启慢查询日志
    set global slow_query_log='ON';
    # 查看下慢查询日志是否开启，以及慢查询日志文件的位置（默认为MySQL的数据目录）
    mysql> show variables like '%slow_query_log';
    +----------------------+----------------------------------------+
    | Variable_name          | Value     								|
    +----------------------+----------------------------------------+
    | low_query_log          | ON       								|
    | low_query_log_file   | /var/lib/mysql/atguigu01-slow.log      |
    +----------------------+----------------------------------------+

  3. 修改慢查询阈值long_query_time

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 查看慢查询的时间阈值
    show variables like '%long_query_time%';
    # 查看全局慢查询的时间阈值：
    show global variables like '%long_query_time%';
    +-------------------+-----------+
    | Variable_name     | Value     |
    +-------------------+-----------+
    | long_query_time   | 10.000000 |
    +-------------------+-----------+

    临时修改慢查询的时间阈值

    1
    2
    3
    4
    # 当前回话
    set long_query_time = 1; 
    # 全局
    set global long_query_time = 1;

    对于 “global” 选项，是全局级别的配置参数。它可以在 MySQL 服务器启动时或 MySQL 安装时在 MySQL 配置文件中设置，或者通过 SET GLOBAL 命令在运行时更改。全局级别的配置参数对所有的 MySQL 连接都有效。

    永久修改（重启数据库后依然有效，不建议永久修改，仅在优化时候打开，慢查询拖性能） ：修改my.cnf

    1
    2
    3
    4
    5
    [mysqld]
    slow_query_log=ON # 开启慢查询日志的开关
    slow_query_log_file=/var/lib/mysql/atguigu-slow.log #慢查询日志的目录和文件名信息
    long_query_time=3 #设置慢查询的闽值为3秒，超出此设定值的SQL即被记录到慢查询日志
    log_output=FILE

    注意：

    1
    2
    3
    4
    5
    # 测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
    set global long_query_time=1;                        # 设置全局慢查询阈值1s
    show global variables like '%long_query_time%';        # 全局1s
    set long_query_time=1;
    show variables like '%long_query_time%';            # 当前会话10s

查看慢查询次数

查询当前系统中有多少条慢查询记录

1
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

慢查询日志分析工具：mysqldumpslow

mysqldumpslow是用于分析MySQL慢查询日志的命令行工具。通过解析慢查询日志，可以了解到数据库的性能问题，从而进行优化。

查看mysqldumpslow的帮助信息

1
mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

  • -a: 不将数字抽象成N，字符串抽象成S
  • -s: 是表示按照何种方式排序：
    • c: 访问次数
    • l: 锁定时间
    • r: 返回记录
    • t: 查询时间
    • al:平均锁定时间
    • ar:平均返回记录数
    • at:平均查询时间 （默认方式）
    • ac:平均查询次数
  • -t: 即为返回前面多少条的数据；
  • -g: 后边搭配一个正则匹配模式，大小写不敏感的；

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 按照查询时间排序，查看最慢的10个查询，可以使用如下命令
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# 如果需要筛选特定的查询，可以使用 `-g` 参数
mysqldumpslow -s t -t 10 -g "SELECT * FROM user" /var/log/mysql/mysql-slow.log
# 解析：该命令表示按照时间排序，显示最慢的10个查询，其中关键字为 "SELECT * FROM user"。

# 重新返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log
# 查询访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
# 查询按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/mysql-slow.log
# 建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log | more

关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法：

方式1：永久性方式

1
2
[mysqld]
#slow_query_log=OFF

mysql默认关闭慢查询日志，或者，把slow_query_log一项注释掉 或 删除

重启MySQL服务，执行如下语句查询慢日志功能。

1
2
SHOW VARIABLES LIKE '%slow%';                 # 查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%';     # 查询超时时长

方式2：临时性方式

使用SET语句来设置。停止MySQL慢查询日志功能，具体SQL语句如下：

1
2
3
4
5
SET GLOBAL slow_query_log=off;                 # golbal全局有效。
# 重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，发现慢查询日志已经关闭成功。
SHOW VARIABLES LIKE '%slow%';                # 发现关闭成功
# 慢查询阈值
SHOW VARIABLES LIKE '%long_query_time%';       # 10s。前面改的时候没有加global，所以重启服务器后阈值恢复10s。

删除慢查询日志

手动删除 

1
2
# 使用SHOW语句显示慢查询日志信息，会得到慢查询日志的目录，在该目录下 手动删除慢查询日志文件 即可。
SHOW VARIABLES LIKE `slow_query_log%`;

自动删除

使用命令 mysqladmin flush-logs 来重新生成查询日志文件，执行完毕会在数据目录下重新生成慢查询日志文件。

重新生成慢查询日志文件（直接删除旧的）

1
mysqladmin -uroot -p flush-logs slow

提示

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

定位慢查询语句、查看 SQL 执行成本：show profile

show profile 是 MySQL 提供的可以用来分析当前会话中 SQL 都做了什么、执行的资源消耗工具的情况，可用于 sql 调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

SHOW PROFILE 是一个用于查看会话执行的查询的性能分析信息的 MySQL 命令。它可以帮助开发人员和 DBA 分析查询语句执行时的瓶颈，并找出哪些部分需要优化。

SHOW VARIABLES 显示了 MySQL 服务器的当前配置变量，包括全局配置变量和会话配置变量，以及它们的值。SHOW VARIABLES 用于查看 MySQL 配置系统参数的详细信息并进行系统参数的修改。
SHOW STATUS 显示服务器的性能参数，包括连接、线程、查询等方面的状态信息，以及它们的值。

查看配置是否开启profile： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql > show variables like 'profiling';
# 开启 show profile
mysql > set profiling = 'ON';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| profiling         | ON        |
+-----------------+-----------+
# 执行查询语句
select * from employees;
# show profiles; 查询当前会话所有查询语句持续时间
mysql > show profiles;
# show profile;查询当前会话最近sql语句的执行成本
mysql > show profile;
# 查询指定QueryID的cpu信息
mysql > show profile cpu for 2;
# 在SHOW PROFILE 中可以查看不同部分的开销，比如 cpu、block.io 等
mysql> show profile cpu,block io for query 2

show profile的常用查询参数：

  1. ALL：显示所有的开销信息。

  2. BLOCK IO：显示块IO开销。

  3. CONTEXT SWITCHES：上下文切换开销。

  4. CPU：显示CPU开销信息。

  5. IPC：显示发送和接收开销信息。

  6. MEMORY：显示内存开销信 息。

  7. PAGE FAULTS：显示页面错误开销信息。

  8. SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。

  9. SWAPS：显示交换次数开销信息。

日常开发需注意：

  1. converting HEAP to MyISAM: 查询结果太大，内存不够，数据往磁盘上搬了。

  2. Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。

  3. Copying to tmp table on disk：把内存中临时表复制到磁盘上，警惕！

  4. locked。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

注意：不过SHOW PROFILE命令将被弃用，但可以从 information_schema 中的 profiling 数据表进行查看。

执行计划表：EXPLAIN

简介

MySQL的EXPLAIN是一种分析SQL语句查询性能的工具。当我们在MySQL中执行SELECT语句时，EXPLAIN可以帮助我们查看MySQL如何执行这个查询，即执行计划，包括使用哪些索引、选择哪些表、以及如何读取数据等信息。

EXPLAIN的使用方式：

1
EXPLAIN SELECT * FROM my_table WHERE my_column = 'my_value';

执行以上命令后，MySQL会返回一张查询执行计划表，其中包含了MySQL执行这个查询的详细信息。通过分析查询执行计划表了解查询的性能瓶颈，以及如何优化查询语句，从而提高查询性能。

注意：

  • EXPLAIN不考虑各种Cache
  • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
  • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
  • 部分统计信息是估算的，并非精确值

MySQL中有专门负责优化SELECT语句的优化器模块

主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的 执行计划 (他认为最优的数据检索方式，但不见得是DBA（数据库管理员）认为是最优的，这部分最耗费时间)。

能做什么？

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

官网介绍

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

版本情况

  • MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE， DELETE
  • 在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示 filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和 filtered中的信息。

基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

1
2
3
4
5
EXPLAIN SELECT select_options
# 一般指定在查询时不使用缓存
EXPLAIN SELECT SQL_NO_CACHE select_options
# 或者
DESCRIBE SELECT select_options

除了以 SELECT 开头的查询语句，其余的 DELETE、INSERT、REPLACE 以及UPDATE 语句等都可以加上EXPLAIN

执行计划表介绍

执行计划各个列的作用（概述）

列 说明
id 每个SELECT子句或者join操作都会被分配一个唯一的编号，编号越小优先级越高，id相同的语句可以被认为是一组。id为NULL表示独立的子查询，子查询优先级都比主查询高。
select_type 查询的类型。主查询(primary)、普通查询(simple)、联合查询、子查询(subquery)、derived(from表临时子查询)、union(union后查询)、union result()
table 表名。显示当前这行的数据是哪个表的。
partitions 匹配的分区信息。如果表未分区则为NULL。
type 访问类型，根据索引、全表扫描等方法来执行查询的优化策略。all（全表扫描），ref（命中非唯一索引），index(没命中索引，扫描索引树再回表)、const（命中主键/唯一索引）、range(范围索引查询)、index_merge(使用多个索引)、 system(一行记录时,快速查询)。
possible_keys 可能用到的索引。列出MySQL能够使用哪些索引来查询。如果该列只有一个possible_keys，通常意味着这个查询是高效的。如果这个列有多个possible_keys，并且MySQL只使用了其中一个，则需要考虑是否需要在该列上增加一个联合索引。
key 实际上使用的索引。如果没有明确的指定KEY，MySQL会根据查询条件自动选择最优的索引。
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息。常量等值查询const, 表达式/函数使用到时func,关联查询显示关联字段名
rows 预估的需要读取的记录条数。数值越小越好，表示结果集越小，查询越高效。
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比。这个值越小越好，说明可通过索引直接返回数据。
Extra 额外信息。看有没有走索引，还是全表扫描了。一般搭配type字段看。Using index(使用到覆盖索引)、Using where(未完全命中索引)、Using temporary(临时表存储结果集.排序/分组会使用)、Using filesort(排序操作未用索引)、Using join buffer(连接条件未用索引)、Impossible where(where约束语句可能有问题导致没有结果集)

详细介绍

select_type

select_type：查询的类型，有以下几种取值：

  • SIMPLE：不使用子查询或UNION，不包含UNION ALL的简单SELECT查询。
  • PRIMARY：最外层的SELECT查询。
  • DERIVED：以FROM子句中的子查询方式出现的SELECT语句。
  • UNION：UNION中的第二个或之后的SELECT查询。
  • UNION RESULT：从UNION的结果集中获取数据的SELECT查询。
  • SUBQUERY：不在FROM子句中出现的子查询，通常在SELECT语句中使用。
  • DEPENDENT SUBQUERY：子查询依赖外层查询的结果集。

key

key：实际上使用的索引。在MySQL中创建索引时使用的是INDEX关键字，但在EXPLAIN执行计划表中，显示的是KEY，这是因为MySQL允许在创建索引时指定统计信息，例如最小值、最大值等，这些统计信息在索引中被视为索引键（Index key），所以在执行计划表中，显示为KEY。

type

type：访问类型，根据索引、全表扫描等方法来执行查询的优化策略。当 type 列的取值不是 Const 时，我们需要重点关注有关索引、缓存的性能调优，对 SQL 语句进行优化，适当修复可能的数据设计问题。

  • system：一行记录时,快速查询。只有一行数据即将被查询。这是最快的查询类型，通常出现在系统表的查询中。

  • const：命中主键或唯一索引。使用主键或唯一索引查找单个行时使用，此时查询只能返回一行数据。这是一种非常快的查询类型。

  • eq_ref：连接使用唯一索引查找符合查询条件的数据时使用，每个连接类型都需要使用唯一索引进行访问，比ref执行速度更快。

  • ref：命中非唯一索引。使用非唯一索引查找数据时使用，查询结果比eq_ref大，但仍很快。

  • range：范围索引查询。使用索引范围查找数据时，可能会查找一定范围内的数据，如使用 BETWEEN 或 > 或 > < 等操作时的查询。

  • index：没命中索引，扫描非聚簇索引树再回表。

    • 直接在某个索引树上做条件判断，并且不需要回表。全表扫描没有好的索引适用时使用，相比于全表扫描速度更快。

    • index是另外一种形式的全表扫描，扫描已有索引树然后回表取数据。和all相比，他要回表随机取数据，因此index不可能会比all快（取同一个表数据），官方手册说它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。

    • 比如：select t3.key1 from t3 where t3.key2 =6 ;当我们创建了联合索引idx_key1_key2(key1,key2)时，判断条件key2=6时，其虽然不满足索引的最左前缀原则，但是我们可以遍历idx_key1_key2这颗索引树，找到key2=6的记录即可。由于查询结果需要的key1在这个联合索引上，也不需要回表，此时就可以使用index。

  • all：全表扫描。扫描整个表以获得需要的数据，速度最慢，必须尽量避免使用。

  • unique_subquery：在对查询结果进行过滤或使用 IN 操作时，优化器会选择使用此类型的查询，使用了 In 操作符的子查询依赖于外层查询的唯一索引。

  • index_subquery：使用了 In 操作符但子查询使用的普通索引，而不是唯一索引。

  • range_check：在使用索引来检查外键参照时使用。

  • index_merge：使用多个索引。

Extra

  • using index：使用了覆盖索引，即不需要回表。查询的几个列正好都在这个聚簇索引树上。
  • Using where：通过where过滤。没完全命中索引，需要回表。如index(a)，查的是where a=2 and b=3，查b=3时就要回表过滤。
  • Using index condition：使用了索引下推。
  • Using temporary：临时表存储结果集.排序/分组会使用
  • Using filesort：排序操作未用索引
  • Using join buffer：连接条件未用索引
  • Impossible where：where约束语句可能有问题导致没有结果集

EXPLAIN四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式： 传统格式 ，JSON格式 ， TREE格式 以及 可视化输出 。用户可以根据需要选择适用于自己的格式。

  1. 传统格式：传统格式简单明了，输出是一个表格形式，概要说明查询计划。输出中缺少衡量执行好坏的重要属性 —— 成本。

  2. JSON格式：四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。

    1
    EXPLAIN FORMAT=JSON SELECT ....

  3. TREE格式：TREE格式是8.0.16版本之后引入的新格式，主要根据查询的 各个部分之间的关系 和 各部分的执行顺序 来描述如何查询。

  4. 可视化输出：通过MySQL Workbench可视化查看执行计划。点击Workbench的放大镜图标，即可生成可视化的查询计划。

    从左到右的连接顺序显示表。红色框表示 全表扫描 ，而绿色框表示使用 索引查找 。对于每个表， 显示使用的索引。还要注意的是，每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本。

SHOW WARNINGS的使用

在MySQL中，SHOW WARNINGS是一个可以查看最近一次执行的语句中产生的警告信息的命令。当MySQL执行语句时，如果发现一些不符合预期的情况，会产生一些警告信息，包括非致命性错误，例如某些类型的数据不能隐式转换或某些数据截断等。

执行SHOW WARNINGS命令时，MySQL会返回警告信息的详细列表，包括：

  • Warning：该警告的类型
  • Level：该警告的级别，通常为Note、Warning或Error
  • Code：警告的返回代码
  • Message：警告信息的内容
1
2
3
4
5
# 可以使用SELECT的方式来查看最近一次操作的警告信息：
SHOW WARNINGS;
# 也可以配合使用INSERT、UPDATE、DELETE、ALTER TABLE等命令，检查某个具体操作产生的警告信息：
INSERT INTO my_table (name, age) VALUES ('John Doe', 150);
SHOW WARNINGS;

EXPLAIN ANALYZE命令

MySQL 8.0引入了explain analyze命令，相比explain，它提供的是实际的查询计划，而explain提供的是预估查询计划。

explain和explain analyze的区别：

  • explain：只生成执行计划，不实际执行
  • explain analyze：生成执行计划，并实际执行sql

示例：

1
2
3
4
5
6
7
8
# 分析人员表联查部门表
EXPLAIN ANALYZE
SELECT * FROM personnel p LEFT JOIN department d on p.department=d.id
# 查询计划结果
-> Nested loop left join  (cost=915.25 rows=1980) (actual time=0.333..14.500 rows=2453 loops=1)
-> Table scan on p  (cost=222.25 rows=1980) (actual time=0.283..8.625 rows=2453 loops=1)
-> Filter: (p.DEPARTMENT = d.ID)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=2453)
-> Single-row index lookup on d using PRIMARY (ID=p.DEPARTMENT)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=2453)

结果分析：

第一行：Nested loop left join (cost=915.25 rows=1980) (actual time=0.333..14.500 rows=2453 loops=1)

  • Nested loop left join: 执行的最外层操作，表示使用嵌套循环的左连接。
  • 成本估计: (cost=915.25 rows=1980)：预计消耗915.25ms并返回1980行。
  • 实际时间: (actual time=0.333..14.500 rows=2453 loops=1)：实际读取第一行平均花费0.333ms，返回所有行平均花费14.500ms，共循环调用该迭代器1次，返回2453行。

第二行：Table scan on p (cost=222.25 rows=1980) (actual time=0.283..8.625 rows=2453 loops=1)

  • Table scan on p: 对人员表的全表扫描。
  • 成本估计: (cost=222.25 rows=1980)：预计消耗222.25ms并返回1980行。
  • 实际时间: (actual time=0.283..8.625 rows=2453 loops=1)：实际读取第一行平均花费0.283ms，返回所有行平均花费8.625ms，共循环调用该迭代器1次，返回2453行。

第三行：Filter: (p.DEPARTMENT = d.ID) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=2453)

  • Filter: (p.DEPARTMENT = d.ID): 执行对 md_gams_jc_department 表中 p.DEPARTMENT = d.ID 条件的过滤操作。

  • 成本估计: (cost=0.25 rows=1)：预计消耗0.25ms并返回1行。

  • 实际时间: (actual time=0.002..0.002 rows=1 loops=2453)：过滤操作平均花费0.002ms，共循环调用该迭代器2453次，返回1行。

第四行：Single-row index lookup on d using PRIMARY …… (actual time=0.002..0.002 rows=1 loops=2453)

  • Single-row index lookup on d using PRIMARY (ID=p.DEPARTMENT): 对部门表使用主键索引进行单行查找，其中 ID=p.DEPARTMENT。
  • 成本估计: (cost=0.25 rows=1)：预计消耗0.25ms并返回1行。
  • 实际时间: (actual time=0.002..0.002 rows=1 loops=2453)：查找操作平均花费0.002ms，共循环调用该迭代器2453次，返回1行。

分析优化器执行计划：trace

在MySQL中，可以使用trace命令来进行优化器执行计划的跟踪和分析。trace命令可以显示MySQL优化器在生成执行计划时所采取的决策，包括哪些表被处理，以及使用哪些索引、算法等。

使用trace命令需要先启用general_log和performance_schema两个系统变量，其次需要使用SET语句来设置一些参数，例如trace-unique-check、trace-max-protocol、trace-protocol、trace-feature、trace-feature-check等。设置完成后，可以通过SET global trace_format='json'语句来选择输出结果的格式。

下面是对使用trace命令的一个简单示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 1.设置参数：
SET @trace_feature = 'qa';
SET @max_execution_time=50000;
SET @trace_level = '+ddl,+engine';
SET @trace_feature_check = 1;
SET @trace_unique_check = 1;
SET @trace_protocol = 1;
SET @trace_max_protocol = 6;
# 2.启用general_log和performance_schema：
SET global general_log = on;
SET global performance_schema = on;
# 3.执行查询并查看结果：
SELECT *
FROM my_table
WHERE my_column = 'some_value';
SHOW SESSION STATUS LIKE 'Last_Query_Plan';
# 4.关闭general_log和performance_schema：
SET global general_log = off;
SET global performance_schema = off;

在trace输出中，可以看到优化器在执行计划中使用的索引、执行算法、行数估计等细节信息。通过分析trace结果，可以找到一些性能问题的根源，并进行相应的调整和优化。但要注意，trace命令可能会带来额外的性能消耗和IO开销，不应该在生产环境中长期启用。

MySQL监控分析视图-sys schema

简介

MySQL在8.0版本引入了sys schema，该模式包含用于监视和分析MySQL服务器性能的视图和函数。sys schema提供了一组易于使用的视图和函数，可以帮助我们更好地理解和分析MySQL数据库的行为和性能。

以下是sys schema中一些常用的监控分析视图：

  • sys.statements_with_sorting: 显示哪些语句使用了排序操作，包括使用哪些排序操作、每个语句排序次数及排序操作的资源消耗。

  • sys.statements_with_runtimes_in_95th_percentile`: 显示执行时间最长的语句。

  • sys.io_global_by_file_by_bytes: 显示每个文件的磁盘IO字节数，可以用来检测IO瓶颈。

  • sys.memory_by_host_by_current_bytes: 显示每个客户端的当前内存使用情况，可以用于检测内存泄漏或内存占用高的情况。

  • sys.waits_global_by_latency: 显示哪些等待操作最耗费时间，可以帮助我们找到性能问题的瓶颈所在。

  • sys.processlist: 显示当前正在运行的线程和进程的信息，包括执行的语句、查询ID、用户、主机、线程ID和状态等信息。

sys schema中的视图和函数提供了更深入的MySQL性能分析和监控功能，可以帮助我们更好地理解MySQL数据库的行为和性能瓶颈。

  1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
  2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
  3. I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。
  4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
  5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
  6. 表相关：以schema_table开头的视图，展示了表的统计信息。
  7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
  8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
  9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
  10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

使用场景

  1. 索引情况

    1
    2
    3
    4
    5
    6
    7
    # 1. 查询冗余索引
    select * from sys.schema_redundant_indexes;
    # 2. 查询未使用过的索引
    select * from sys.schema_unused_indexes;
    # 3. 查询索引的使用情况
    select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
    from sys.schema_index_statistics where table_schema='dbname';

  2. 表相关

    1
    2
    3
    4
    5
    6
    7
    8
    # 1. 查询表的访问量
    select table_schema,table_name,sum(io_read_requests+io_write_requests) as io 
    from sys.schema_table_statistics group by table_schema,table_name order by io desc;
    # 2. 查询占用bufferpool较多的表
    select object_schema,object_name,allocated,data
    from sys.innodb_buffer_stats_by_table order by allocated limit 10;
    # 3. 查看表的全表扫描情况
    select * from sys.statements_with_full_table_scans where db='dbname';

  3. 语句相关

    1
    2
    3
    4
    5
    6
    7
    8
    # 1. 监控SQL执行的频率
    select db,exec_count,query from sys.statement_analysis order by exec_count desc;
    # 2. 监控使用了排序的SQL
    select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1;
    # 3. 监控使用了临时表或者磁盘临时表的SQL
    select db,exec_count,tmp_tables,tmp_disk_tables,query
    from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
    order by (tmp_tables+tmp_disk_tables) desc;

  4. I/O相关

    1
    2
    3
    # 1. 查看消耗磁盘IO的文件
    select file,avg_read,avg_write,avg_read+avg_write as avg_io
    from sys.io_global_by_file_by_bytes order by avg_read limit 10;

  5. Innodb 相关

    1
    2
    # 1. 行锁阻塞情况
    select * from sys.innodb_lock_waits;

MySQL调优

基础优化

缓存优化

  • 缓冲池优化：调整缓冲池大小innodb_buffer_pool_size。
  • 引入内存结构数据库：例如Redis。

提示：InnoDB使用缓冲池缓存记录和索引

缓冲池优化

缓冲池：MySQL的缓冲池被分为多个不同的缓存池，其中包括：

  • 查询缓存：用来缓存查询结果。
  • InnoDB缓存池：用来缓存热点表和索引数据页。
  • MyISAM缓存池：用来缓存表数据块。

缓冲池是主内存中的一部分空间，用来缓存已使用的表和索引数据。缓冲池使得经常被使用的数据能够直接在内存中获得，从而提高速度。

缓冲池的淘汰策略：

LRU算法。MySQL的缓冲池默认使用的是LRU（最近最少使用）淘汰策略，它会优先缓存最近使用的数据。当缓冲池的空间不足时，MySQL会将最不常用的数据从缓冲池中替换出去，以腾出空间缓存新的数据。

lru算法底层原理：

底层是双向链表（因为经常要移动元素），链表首部是最常使用元素，尾部是最少使用元素。

每次刚访问的数据会移动到链表首部，刚添加的数据也会添加到链表首部。超出maxmemory会淘汰链表尾部元素，它也最长时间没有被使用的数据。

image-20250726175640829

缓冲池相关参数：

  • MyISAM缓冲池大小：key_buffer_size：该参数用来设置MyISAM索引的缓存大小。如果应用程序中涉及到大量的索引查询，可以适当提高该值。一般来说，key_buffer_size占用总内存的1/4到1/3比较合适。

  • InnoDB缓冲池大小：innodb_buffer_pool_size：该参数用来设置InnoDB缓冲池的大小。InnoDB存储引擎使用缓冲池来缓存数据和索引文件。如果InnoDB表的读写频次较高，建议将该值设置为物理内存的70%到80%。

  • 排序缓冲区大小：sort_buffer_size：该参数用来设置排序缓冲区大小。如果查询中涉及到ORDER BY或GROUP BY操作，可以适当提高该值。一般来说，sort_buffer_size占用总内存的1/4到1/3比较合适。

  • 读取缓冲区大小：read_buffer_size和read_rnd_buffer_size：这两个参数是用来设置读取缓冲区大小的，默认值为128 KB。如果应用程序中经常进行大文件的读取操作，可以适当提高这两个参数。

  • binlog大小：binlog_cache_size：该参数是用来设置二进制日志的缓存大小。如果应用程序中需要持久化一些数据，可以开启二进制日志，并适当调整该参数。

参数配置方法：

1
2
3
4
5
6
7
8
# 1.查看当前缓冲池参数：
show VARIABLES  like 'key_buffer_size';
# 2.修改缓冲池参数：
# 2.1 在运行中的MySQL实例中临时设置这个值（这不会持久保存，重启后会失效）：
SET GLOBAL key_buffer_size = 67108864;  -- 64MB
# 在MySQL配置文件（通常是 my.cnf 或 my.ini）中进行更改，然后重启MySQL服务使更改生效：
[mysqld]
key_buffer_size = 64M

Redis优化

Redis是一个基于内存的NoSQL数据库，MySQL是一个基于磁盘的关系型数据库。内存的读写速度是远高于磁盘的，所以对于一些多读少写的热点数据，搭配Redis存储数据，可以极大地提高数据的访问速度。

参考文章：Redis

硬件优化

服务器加内存条、升级SSD固态硬盘、把磁盘I/O分散在多个设备、配置多处理器。

参数优化

  1. 关闭不必要的服务和日志：调优结束关闭慢查询日志；

    1
    2
    # 临时关闭慢查询日志，如果想永久关闭，需要修改my.ini或my.cnf配置文件
    SET GLOBAL slow_query_log = 'OFF';

  2. 调整最大连接数：max_connections 。MySQL5.5及之后版本默认最大连接数是151，可根据实际场景压测得出合适的最大连接数。

    • MySQL5.5 ～ 5.7：默认的最大连接数都是 151，上限为：100000

    • MySQL5.0 版本：默认的最大连接数为 100，上限为 16384

    • MySQL8.0 版本: 默认的最大连接数是 151

    • 修改最大链接数

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      # 1.查看当前最大连接数：
      SHOW VARIABLES LIKE 'max_connections';
      # 2.临时修改最大连接数（重启后失效）：
      SET GLOBAL max_connections =1000
      # 3.永久修改连接数（重启后有效）：
      # 在Linux系统中，配置文件通常是/etc/my.cnf或/etc/mysql/my.cnf。
      # 在Windows系统中，通常是 [安装目录下]\MySQL Server 5.7\my.ini。
      [mysqld]
      max_connections = 1000
      # Linux，重启
      sudo systemctl restart mysql
      # windows，服务重启：服务管理器-搜索MySQL-重启服务

  3. 线程池缓存线程数：thread_cache_size，缓存空闲线程，有连接时直接分配该线程处理连接；

  4. 缓冲池大小：innodb_buffer_pool_size 。

定期清理垃圾

对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的MySQL资源，从而提高MySQL的性能。

清理不再使用的表

1
2
3
4
# 删除这些表：
DROP TABLE table_name;
# 保留表结构但删除所有数据
delete from table_name;

清理过期数据

1
2
3
4
5
6
7
# 一些场景下，某个时期之前的数据都不再需要，可以清理这些数据
DELETE FROM table_name WHERE createTime < NOW() - INTERVAL 30 DAY;
# 创建一个MySQL事件来定期清理过期数据
CREATE EVENT clean_up_event
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM table_name WHERE created_at < NOW() - INTERVAL 30 DAY;

清理日志

1
2
# 清理2023年之前的日志
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

清理缓存池

1
2
3
4
5
RESET QUERY CACHE;
# 或者修改配置文件，禁用查询缓存以避免潜在的性能问题：
[mysqld]
query_cache_type = 0
query_cache_size = 0

优化表：OPTIMIZE TABLE

在 MySQL 数据库中，OPTIMIZE TABLE 是一个重要的命令，用于优化表的性能和空间利用。通过重新组织表的存储结构，去除碎片、重建索引，OPTIMIZE TABLE 可以帮助提高查询性能、减少存储空间占用以及减少数据碎片。

1
OPTIMIZE TABLE table_name;

优化原理：

删除delete语句留下来的垃圾碎片。使用delete语句删除数据时，delete语句只会将记录的位置或者数据页标记为”可复用”，但是数据库磁盘文件的大小不会改变，即表空间不会被回收，此时使用该命令可以释放空间，压缩数据文件。

底层原理：执行OPTIMIZE TABLE命令后，MySQL会进行以下几个步骤

  1. 创建临时表：MySQL 首先会创建一个与原表结构相同的临时表。
  2. 原表数据复制到临时表：将原表中的数据复制到临时表中。
  3. 临时表去碎片：在数据复制的过程中，MySQL 会对数据进行整理和重组，去除碎片，提高数据的连续性。
  4. 删旧表留新表：当数据复制完成并且表被优化后，MySQL 会删除原表，然后将临时表重命名为原表的名称。

分析表：ANALYZE TABLE

MySQL 的Optimizer（优化元件）在优化SQL语句时，首先需要收集一些相关信息，其中就包括表的cardinality（散列程度），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。

1
ANALYZE TABLE table_name;

对不同存储引擎的效果：

  • InnoDB：对 InnoDB 表执行 ANALYZE TABLE 会重新计算表和索引的统计信息，并更新优化器统计信息。
  • MyISAM：对 MyISAM 表执行 ANALYZE TABLE 会分析表的关键字分布，并更新索引统计信息。
  • 其他存储引擎：对其他存储引擎（如 MEMORY 或 ARCHIVE），效果类似，即更新表和索引的统计信息。

计划任务清理数据、日志、优化表

对于以上的清理垃圾方案，可以写一个定时任务，定期统一清理垃圾数据、优化表的存储空间和索引。

方案一：创建cron作业
1
2
3
4
5
6
7
8
# 1.编辑crontab
# 使用crontab命令编辑当前用户的cron作业列表。对于系统级别的作业，可以使用sudo运行crontab。
crontab -e
# 或者，为特定用户编辑cron作业：
sudo crontab -u username -e
# 2. 编写cron作业
# 在打开的编辑器中，添加新的cron作业，每行代表一个作业，执行指定路径下的脚本：
0 2 * * * /path/to/cleanup_script.sh

cron表达式格式如下:

1
2
3
4
5
6
7
8
* * * * * command-to-be-executed
- - - - -
| | | | |
| | | | +----- Day of the week (0 - 7) (Sunday=0 or 7)
| | | +------- Month (1 - 12)
| | +--------- Day of the month (1 - 31)
| +----------- Hour (0 - 23)
+------------- Minute (0 - 59)

示例清理脚本：/path/to/cleanup_script.sh

1
2
3
4
5
6
7
8
9
10
11
#!/bin/bash
# MySQL credentials
USER="your_username"
PASSWORD="your_password"
DATABASE="your_database"
# 清理过期数据
mysql -u $USER -p$PASSWORD -e "DELETE FROM table_name WHERE created_at < NOW() - INTERVAL 30 DAY;" $DATABASE
# 清理二进制日志
mysql -u $USER -p$PASSWORD -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;"
# 优化表
mysql -u $USER -p$PASSWORD -e "OPTIMIZE TABLE table_name;" $DATABASE
方案二：使用Spring定时任务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// 步骤①：开启定时任务功能，在引导类上开启定时任务功能的开关，使用注解@EnableScheduling
@SpringBootApplication
@EnableScheduling // 开启定时任务功能
public class SpringbootTaskApplication {
    public static void main(String[] args) {
        SpringApplication.run(Springboot22TaskApplication.class, args);
    }
}
// 步骤②：在task包下定义Bean，在对应要定时执行的操作上方，使用注解@Scheduled定义执行的时间，执行时间的描述方式还是cron表达式
@Component
public class MyBean {
    @Scheduled(cron = "0/1 * * * * ?")
    public void print(){
        // 具体清理垃圾的逻辑
    }
}

如何想对定时任务进行详细配置，可以通过配置文件进行

1
2
3
4
5
6
7
8
9
spring:
  task:
       scheduling:
      pool:
           size: 1							 #任务调度线程池大小 默认 1
      thread-name-prefix: ssm_           #调度线程名称前缀 默认 scheduling-      
        shutdown:
          await-termination: false         #线程池关闭时等待所有任务完成
          await-termination-period: 10s     #调度线程关闭前最大等待时间，确保最后一定关闭

总结

  1. spring task需要使用注解@EnableScheduling开启定时任务功能
  2. 为定时执行的的任务设置执行周期，描述方式cron表达式

使用合适的存储引擎

MyISAM：适合读取频繁，写入较少的场景（因为表级锁、B+树叶存地址）

InnoDB：适合并发写入的场景（因为行级锁、B+树叶存记录）。

读写分离与分库分表

参考文章：MySQL数据库-表设计优化、读写分离与分库分表

读写分离：读写分离能有效提高查询性能。主从同步用到bin log和relay log。

分库分表：数据量级到达千万级以上后，垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）。

表设计优化

参考文章：MySQL数据库-表设计优化、读写分离与分库分表

  1. 混合业务分表、冷热数据分表
  2. 联合查询改为中间关系表
  3. 遵循三个范式
  4. 字段建议非空约束
  5. 反范式：使用冗余字段
  6. 数据类型优化

索引优化

索引优化思路

都有哪些维度可以进行数据库调优？简言之：

  • 索引失效、没有充分利用到索引——建立索引

  • 关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化

  • 服务器调优及各个参数设置（关闭慢查询日志、缓冲、线程数等）——调整my.cnf

  • 数据过多——分库分表

  • 定期清理垃圾：对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的MySQL资源，从而提高MySQL的性能。

  • 使用合适的存储引擎：MyISAM比较适合读取频繁，写入较少的场景（因为表级锁、B+树叶存地址），而InnoDB比较适合并发写入的场景（因为行级锁、B+树叶存记录）。

关于数据库调优的知识非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。

虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

  • 物理查询优化：索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
  • 逻辑查询优化：通过SQL等价变换提升查询效率，即换一种查询写法效率可能更高。

索引失效的11种情况

用不用索引，最终都是优化器说了算：

优化器是基于什么的优化器? 基于 **cost开销(CostBaseOptimizer)**，它不是基于规则(Rule-Basedoptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

查询条件相关

  • 隐式类型转换：当查询条件中的数据类型与索引列的数据类型不匹配时，会触发隐式转换，导致索引失效。例如，索引列是varchar类型，查询时使用了数字类型。

  • 左模糊匹配：使用like语句进行模糊查询时，如果通配符%在最左边，无法利用索引的有序性，索引失效。如select * from users where name like '%张%'。

  • 索引列参与运算：对索引列进行运算，如age + 1 > 30，数据库需对每行数据计算后才能比较，无法直接利用索引，索引失效。

  • 函数操作索引字段：在查询条件中对索引列使用函数，如date_format(created_at, '%y-%m')，函数转换会破坏索引的有序性，导致索引失效。

  • 最左前缀原则违反：对于联合索引，必须从左到右连续使用，即最左优先，否则索引失效。

    结论：MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

    例如：联合索引(country, city, age)，查询条件为city='北京' and age>25，跳过了第一个字段country字段，索引失效。

  • 范围条件右边的列索引失效：（a,b,c）联合索引，查询条件a,b,c，如果b使用了范围查询，那么b右边的c索引失效。这里右边看的联合索引的键右边。

    解决办法：新建联合索引（a,c,b）或（c,a,b），把需要范围查询的字段放在最后

    范围包括：(<) (<=) (>) (>=) 和 between。

  • or连接非索引字段：or条件要求所有涉及的列都有索引，否则索引失效。如select * from users where age=25 or address='北京'，address无索引。

  • 使用不等号（!= / <>）：因为“不等于”不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。使用不等号进行查询时，如果需要扫描大部分数据，优化器可能会放弃索引。如select * from orders where status != 'completed'。

  • 索引选择性过低：当索引列的值过于重复，区分度低于30%时，优化器可能认为全表扫描更高效，从而放弃使用索引。

查询操作相关

  • 使用not in / not exists：not in和not exists操作符在某些情况下会导致索引失效，尤其是当查询结果集较大时。如select * from user where id not in (1,2,3)。
  • 使用is null / is not null：虽然is null或is not null条件可以使用索引，但如果索引列中的数据分布不均匀，查询可能仍然会导致索引失效，特别是在大数据量的情况下。
  • 全表扫描更快的情况：当查询结果超过表总行数的30%时，MySQL可能会认为全表扫描比索引查询更快，从而选择全表扫描。
  • 使用distinct：在某些情况下，distinct查询可能会导致MySQL不使用索引，尤其是在查询中涉及多个字段时。

其他情况

  • 排序顺序不同：order by中的字段与where条件中的字段不一致时，可能会导致索引失效。如select id_no,name,email from db_staff where id_no > '110112202409881120' order by create_time。

  • 查询不在索引范围内：如果查询条件不在索引覆盖范围内，索引失效。如select * from test_001 where user_age = 2500。

  • 不同字符集导致索引失败，建议utf8mb4：不同的字符集进行比较前需要进行转换会造成索引失效。

    数据库和表的字符集统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，可以避免由于字符集转换产生的乱码。

  • 主键插入顺序尽量自增

    对于使用 InnoDB 存储引擎的表来说，在没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序。所以如果 插入 的记录的 主键值是依次增大 的话，那每插满一个数据页就换到下一个数据页继续插，而插入的主键值忽大忽小的话，假设某个数据页存储的记录已经满了，再插入数据就需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着 性能损耗 ！

    建议：让主键 AUTO_INCREMENT ，插入数据时存储引擎会自动填入自增主键值。这样的主键占用空间小，顺序写入，减少页分裂。

遵循索引设计原则

  1. 命名：索引的字段个数尽量别超过5个，命名格式“idx_col1_col2”

  2. 在频繁查询（特别是分组、范围、排序查询）的列建立索引；

  3. 频繁更新的表，不要创建过多索引

  4. 唯一特性的字段，适合创建索引；

  5. 很长的varchar字段，适合根据区分度和长度创建前缀索引；

  6. 多个字段都要创建索引时，联合索引优于单值索引；

  7. 避免创建过多索引，避免索引失效；

  8. 尽量用有序的字段作为主键索引：防止乱序时新主键前移到已满的数据页，导致插入后分裂数据页，造成性能损耗；

查询优化

参考文章：MySQL数据库-查询优化

  1. 连接查询优化
    1. 优化方案
    2. 左外连接：优先右表创建索引，连接字段类型要一致
    3. 内连接：驱动表由数据量和索引决定
    4. join语句原理
  2. 子查询优化
    1. 子查询优化成关联查询
    2. 多次查询代替子查询
    3. 临时表代替子查询
  3. 排序优化
    1. 排序优化建议
    2. 范围查询时索引字段选择
    3. filesort 算法
  4. 分组优化
  5. 分页查询优化
    1. 深分页查询优化
    2. 带排序的深分页优化

尽量覆盖索引

详细请参考：MySQL数据库-索引

字符串前缀索引

详细请参考：MySQL数据库-索引

尽量使用索引下推

详细请参考：MySQL数据库-索引

MySQL5.6支持。一个索引包含了满足查询结果的数据。因为不需要回表，故查询效率高。覆盖索引时左模糊和不等于不能让索引失效。

写多读少时尽量用普通索引

查询时普通索引和唯一索引效率差不多；更新时普通索引效率更高，因为有change buffer（写缓存）将更新后的数据页缓存到内存，下次访问时或后台定期会执行merge操作，将该数据页写入磁盘。change buffer在事务提交时会写入redo log，保证数据持久化。

普通索引：不加任何限制条件，如create index idx_name on student(name)。

唯一索引：UNIQUE参数限制索引唯一，如create UNIQUE index idx_name on student(name)。

详细请参考：MySQL数据库-索引

SQL优化

EXISTS 和 IN 的区分

问题：不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗？

回答：索引是个前提，其实选择与否还是要看表的大小。可将选择标准理解为 小表驱动大表。在这种方式下效率是最高的。

1
2
3
4
5
6
7
8
9
10
11
# 比如下面这样:
SELECT * FROM A WHERE CC IN (SELECT CC FROM B);                     # 当B小于A
SELECT * FROM A WHERE EXISTS (SELECT CC FROM B WHERE B.CC=A.CC);     # 当A小于B时
# 当A小于B时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于
for i in A
    for j in B
        if j.cc == i.cc then
# 当B小于A时，用 IN。因为实现的逻辑类似于
for i in B
    for j in A
        if j.cc == i.cc then

遵循小表驱动大表原则，左边表小就是EXISTS，左边表大就用IN。

小表驱动大表是为了减少连接次数：连接查询的原理是先查左表，再根据连接字段查右表，然后过滤右表的条件。因为相比普通的查询，连接查询要左表右表都查一次，肯定没有只查一次快，所以连接次数越少越好。

建议COUNT(*)或COUNT(1)

**统计行数尽量用COUNT(1),COUNT(*)**：这时查询优化器会优先选用有索引的、占用空间最小的二级索引树进行统计，只有找不到非聚簇索引树时采用使用聚簇索引树统计，空间占用大。当然也能COUNT(最小空间二级索引字段)，但麻烦不如交给优化器自动选择。

问：在 MySQL 中统计数据表的行数，可以使用三种方式： SELECT COUNT(*) 、 SELECT COUNT(1) 和 SELECT COUNT(具体字段) ，使用这三者之间的查询效率是怎样的？

答：如果要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。

  • count（1）：统计整个表的记录行数。括号里表示一个固定值，可以是任何固定的数字字符，是个常量。在InnoDB存储引擎中，查询优化器会优先选择占用空间最小的二级索引树进行统计。COUNT(*) 和COUNT(1)都是对所有结果进行COUNT(*)，两者本质上没有区别，性能上也没有显著差别，因为优化器会处理为相同的查询计划。。有 WHERE 子句是对所有符合筛选条件的数据行进行统计，没有 WHERE子句，则是对数据表的数据行数进行统计。

  • count（*）：统计整个表的记录行数，与count（1）执行结果相同，但是执行会根据目标表的不同进行优化。

  • count（列名）：统计某一列的非空记录数。它会统计指定列中不为NULL的行数，忽略NULL值。

  • count(distinct(列名)) ：统计某一列的非空去重记录数。其实是 count(列名) + distinct 的结果集，指定列不为NULL，并且在字段值重复的情况下只统计一次

MylSAM 统计只需O(1)：如果是 MylSAM 存储引擎，统计数据表的行数只需要 O(1)的复杂度，因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值，而一致性则由表级锁来保证。 如果是InnoDB 存储引擎，因为innoDB 支持事务，采用行级锁和 MVCC机制，无法像 MyISAM 维护1个row_count变量，因此需要采用扫描全表，是O(n)的复杂度，进行循环+计数的方式来完成统计。

选择建议：在ImnoDB中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键是聚簇索引，聚簇索引叶节点包含整个记录，统计时要加载到内存的数据量更大，性能就差一点。对于COUNT(*)和 COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。 如果有多个二级索引，会使用 key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

建议SELECT(字段)而不是SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

  1. MySQL 在解析的过程中，会通过查询数据字典将*按序转换成所有列名，会大大耗费资源和时间。
  2. 无法使用覆盖索引

数据库引擎的查询流程：参考文章MySQL数据库-查询优化

LIMIT 1 对优化的影响

针对的是会扫描全表的 SQL 语句，如果可以确定结果集只有一条，加上 LIMIT 1 时，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1 了。

全表扫描时尽量用“LIMIT”

当进行全表扫描并且明确时，使用LIMIT可以在达到指定数量后停止扫描，减少不必要的开销。

例如根据学号查询学生，根据身份证号查询人，根据订单号查询订单，当我们明确知道需要精准查询时，用Limit 1 总错不了。

当然如果走了唯一索引，就无需用limit了，查到对应记录会直接返回；如果走了普通索引，并且对应记录重复数据很多的话，用limit也会提高一些性能。

1
2
# 根据学号（假设学号是按班级隔离的）和班级号精准查询学生
SELECT * FROM student where stuno =23 and classid=1 LIMIT 1;

使用limit N，少用limit M，N

避免大偏移量的LIMIT：在大表或M值较大时，LIMIT M, N的性能较差，因为需要扫描并丢弃前M条记录。可以通过记录上次查询的最大ID来优化分页。

1
SELECT * FROM large_table WHERE id > last_id ORDER BY id ASC LIMIT 10;

将长事务拆为多个小事务

  • 提交事务可以释放的资源：回滚段上用于恢复数据的信息、锁、redo / undo log buffer 中的空间。

  • 多使用COMMIT：长事务会持有锁和占用资源较长时间，拆分为小事务并频繁COMMIT可以释放锁、减少资源占用。

尽量多使用 COMMIT，用编程式事务而不是声明式事务，降低事务粒度。示例： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@Transactional
public void fun(){
    // 1.查询a
    // 2.查询b
    // 3.数据处理
    // 4.保存c表
    // 5.保存b表
}
// 优化
@Transactional
public void fun(){
    // 1.查询a
    // 2.查询b
    // 3.数据处理
    // 4.落库
    savaFun();
}
public void savaFun(){
    // 1.保存c表
    // 2.保存d表
}

先查再删改

UPDATE、DELETE语句一定要有明确的WHERE条件。

确保WHERE条件明确：在执行UPDATE或DELETE操作前，先SELECT一下并不会让性能变差，它可以确保有明确的WHERE条件，避免误操作和全表扫描。

尽量UNION ALL而不是UNION

UNION ALL：UNION ALL 和 UNION 都用于组合两个或多个查询结果集。UNION ALL在组合时，不进行去重操作，比UNION更快，适用于不需要去重的场景。

多使用COMMIT

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。

COMMIT 所释放的资源：

  • 回滚段上用于恢复数据的信息
  • 被程序语句获得的锁
  • redo / undo log buffer 中的空间
  • 管理上述 3 种资源中的内部花费