目录

一、业务场景

二、影响范围

三、优化方案

3.1 程序优化

3.1.1.合并for循环

3.1.2.减少数据库查询次数

3.1.3.按需查询

3.1.4.查询条件字段索引命中与添加

3.1.5.缓存数据

3.2 数据库优化

3.2.1.数据库连接分析

3.2.2.数据库锁分析

3.2.3.数据库事务分析

3.2.4.当前运行SQL分析

3.2.5.数据库线程分析

3.2.6.性能摘要分析

3.2.8.Explain分析查询

3.2.9.数据分区

---

一、业务场景

随着系统数据和用户使用数量的增加，接口查询速率提升工作也将同步进行，以解决接口慢查询的问题。

二、影响范围

接口查询慢原因之一是数据库存在着慢SQL，SQL查询过慢会给数据库造成性能压力，比如数据库连接数增加，数据库事务或锁堆积，服务器CPU或内存超负载等，同时系统响应时间增加，用户体验下降。

1.系统接口响应超时

 2.当前运行的sql查询

3.数据库连接状态

4.服务器负载情况

三、优化方案

以订单查询接口为例，从程序和数据库两方面进行优化，订单表存在上百万条数据，随着数据量的增加，接口查询速率也随之增加。

系统中，待发货订单、已发货订单、已完成订单、已取消订单、全部订单等订单管理菜单都会用到订单列表查询接口，订单列表查询接口中，需要附带很多信息，比如订单所属机构数据字典转码、订单物流信息、商品信息、渠道信息、买家信息等信息查询都会影响接口的查询速度。

3.1 程序优化

3.1.1.合并for循环

减少或禁止在公共查询方法中使用for循环，订单表是大数据表，把for循环改到具体业务调用的controller层

3.1.2.减少数据库查询次数

把循环查询数据的方法改为一次性查出，从 O(n) 降到 O(1)，存入map集合，通过map集合进行匹配

3.1.3.按需查询

前端传送是否查询的标识来决定是否查询机构信息。

3.1.4.查询条件字段索引命中与添加

1.索引命中

前缀匹配（LIKE 'xxx%'）可以利用索引，但后缀匹配（LIKE '%xxx'）或全模糊（LIKE '%xxx%'）不行。

 

2.索引添加

由于 create_time 上的范围查询（>= 和 <=）通常能显著减少扫描行数，在 create_time 上创建单列索引。

范围查询通常会过滤掉大量数据，create_time 索引可以让 MySQL 快速定位符合时间范围的行，减少扫描行数，尤其是orders 表数据量大且时间跨度广。

 

 然后前端查询条件默认固定为当前年的数据。

 

3.1.5.缓存数据

查询耗时且更新频率较低的数据存入缓存，定时更新缓存。

 

 

3.2 数据库优化

借助Mysql系统数据库performance_schema 和 information_schema两个系统库中的性能分析表或元数据表对数据库性能进行分析，或使用EXPLAIN关键字对索引进行分析，添加相应的字段索引。

3.2.1.数据库连接分析

1.查看最大连接数：

SHOW VARIABLES LIKE 'max_connections';

这将返回一个结果集，其中包含了 MySQL 当前的最大连接数设置。默认情况下，MySQL 的最大连接数为 151。

2.查看正在使用的连接数：

SHOW STATUS LIKE 'Threads_connected';

这将返回一个结果集，其中包含了当前正在使用的连接数。要注意的是，并不是所有的连接都在活动状态，有些连接可能处于闲置状态。druid连接池中的initialSize初始化大小,影响该值

3.查询总连接数：

SHOW GLOBAL STATUS LIKE 'Connections';

4.查看连接超时：

wait_timeout： 这个参数指定了一个连接在服务器端闲置多长时间后会被自动关闭。默认情况下，wait_timeout 的值为 28800 秒（8 小时）。如果一个连接在这段时间内没有执行任何查询或操作，服务器会将其关闭。你可以使用以下 SQL 查询来查看当前的 wait_timeout 设置：SHOW VARIABLES LIKE 'wait_timeout';

主要用于处理通过程序或脚本（如 PHP、Python 应用）建立的连接，这些连接通常是持续时间较短、但频繁的数据库访问。interactive_timeout主要用于命令行客户端（如 mysql 命令行工具）建立的连接，这些连接通常需要用户手动输入命令并且可能存在较长时间的空闲。

5.查询重置：

SHOW VARIABLES LIKE 'mysql_reset_connection';

mysql_reset_connection 是 MySQL 中的一个系统变量，用于控制客户端连接是否在每次执行查询后重置。

当 mysql_reset_connection 设置为 ON 时，每次执行查询后，MySQL 将重置连接状态，这意味着会话变量、用户变量、临时表等都会被重置。这可以确保每次查询的环境都是一致的，不受之前查询的影响。

当 mysql_reset_connection 设置为 OFF 时，连接状态不会被重置。这意味着之前设置的会话变量、用户变量等在后续查询中仍然保持有效。

默认情况下，mysql_reset_connection 的值为 OFF，这意味着连接状态不会在每次查询后被重置。可以使用以下 SQL 查询来查看当前 mysql_reset_connection 的设置：

SHOW VARIABLES LIKE 'mysql_reset_connection';

3.2.2.数据库锁分析

1.查看死锁日志设置:

show variables like 'innodb_print_all_deadlocks';

设置通过SET GLOBAL innodb_print_all_deadlocks = 1;

2.查看死锁日志位置:

SHOW VARIABLES LIKE '%log_error%';

SHOW VARIABLES LIKE '%log%';

3.查看当前所有的锁：

SELECT * FROM  information_schema.INNODB_LOCKS;

mysql8及以上：SELECT * FROM performance_schema.data_locks;

4.查看锁等待的对应的关系:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

3.2.3.数据库事务分析

1.当前所运行的所有事务：

SELECT * FROM information_schema.INNODB_TRX;

SELECT * FROM performance_schema.events_transactions_current;

2.查看事务等待锁的最大时间，以秒为单位，超时自动关闭未完成的事务：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

3.查询当前正在等待的事件

SELECT * FROM performance_schema.events_waits_current;

4.查看事务隔离级别：

Mysql8以下：SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

Mysql8以上：SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';

5.查看 InnoDB 引擎的详细状态，包括锁和事务的信息:

SHOW ENGINE INNODB STATUS

3.2.4.当前运行SQL分析

1.前运行的sql查询和连接的信息：

SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;查看完整的查询语句

2.使用events_statements_current表：

SELECT * FROM performance_schema.events_statements_current;

3.查询当前所有连接数：

SELECT COUNT(*) AS total_connections FROM INFORMATION_SCHEMA.PROCESSLIST;

4.查询当前正在执行查询的连接数：

SELECT COUNT(*) AS running_queries FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'Query';

3.2.5.数据库线程分析

1.查看线程信息：

SHOW GLOBAL STATUS LIKE 'Threads_%';

3.2.6.性能摘要分析

events_statements_summary_by_digest 表是  PERFORMANCE_SCHEMA库 的一部分，用于存储按查询摘要（digest）聚合的 SQL 语句性能统计信息。

例如：查询 2025 年 7 月 15 日执行的、未使用索引或未使用高效索引的 SQL 语句，并按总耗时从高到低排序。找出可能导致性能瓶颈的查询，优化索引或查询逻辑。

SELECT *
FROM PERFORMANCE_SCHEMA.events_statements_summary_by_digest
WHERE LAST_SEEN LIKE '2025-07-15%'
AND (SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0)
ORDER BY SUM_TIMER_WAIT DESC;

 3.2.7.事务、锁与线程综合查询SQL

SELECT
  trx.trx_id  事务的唯一标识符,
  trx.trx_state   事务的状态,
  trx.trx_started 事务开始的时间,
  trx.trx_mysql_thread_id 事务关联的MySQL线程ID,
	trx.trx_isolation_level 事物隔离级别,
	t.name 线程名称,
  pl.Id 线程ID,
  pl.USER 线程所属的用户,
  pl.HOST 线程连接的主机,
  pl.Command  线程的当前命令,
  pl.TIME 线程的运行时间,
  pl.State 线程的状态,
  pl.Info 正在执行的SQL语句,
	es.THREAD_ID ,
  es.SQL_TEXT 当前执行的SQL语句,
  es.CURRENT_SCHEMA 当前使用的数据库,
  es.TIMER_WAIT/1000000000 语句的执行时间,
  il.lock_id 锁的唯一标识符,
  il.lock_mode 锁的模式,
  il.lock_type 锁的类型,
  il.lock_table 被锁定的表,
  il.lock_index 被锁定的索引,
  il.lock_space 被锁定的表空间,
  il.lock_page 被锁定的页,
  il.lock_rec 被锁定的记录
FROM
  information_schema.INNODB_TRX trx
  LEFT JOIN information_schema.PROCESSLIST pl ON trx.trx_mysql_thread_id = pl.Id
  LEFT JOIN information_schema.INNODB_LOCKS il ON trx.trx_id = il.lock_trx_id
	LEFT JOIN PERFORMANCE_SCHEMA.threads t ON t.processlist_id =  pl.id
	LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current es ON es.THREAD_ID  = t.thread_id

3.2.8.Explain分析查询

EXPLAIN 是 MySQL 中用于分析 SQL 查询的一个工具，通过它可以查看查询的执行计划，帮助开发者了解 MySQL 是如何优化和执行，例如EXPLAIN SELECT * FROM your_table WHERE condition;

输出字段解释

字段	解释
id	查询中每个选择操作的标识符。复杂查询中，id 值越大优先级越高。
select_type	表示查询的类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、DERIVED（派生表）等。
table	查询涉及的表名或别名。
partitions	显示使用的分区信息（如果有）。
type	连接类型。性能从好到差的顺序为：system > const > eq_ref > ref > range > index > ALL。 ALL 表示全表扫描，通常性能最差。
possible_keys	查询可能使用的索引列表。
key:	实际使用的索引。如果没有使用索引，显示为 NULL。
key_len	使用的索引的长度。
ref	显示索引的哪一列被使用以及与之比较的常量（如果有）。
rows	估计的扫描行数。这个值是基于统计信息的估算，不是精确值。
filtered	表示经过查询条件过滤后返回的行数百分比。
Extra	提供额外信息。例如： Using index：只使用了索引中的信息，没有访问表的数据行。 Using where：使用了 WHERE 子句进行过滤。 Using temporary：使用了临时表存储中间结果。 Using filesort：使用了文件排序（非内存排序），通常表示需要优化的查询。

3.2.9.数据分区

与Hive相同，在 MySQL 中，也支持分区，分区列是指在创建表时通过 PARTITION BY 子句指定的用于分区的列。分区列决定数据如何被分割到不同的分区中。MySQL 支持分区是为了提高查询性能、管理大规模数据以及便于数据维护。

1.支持的分区类型

MySQL 支持以下几种分区方式：

RANGE 分区：基于分区列值的范围。例如，按年份范围分区。

LIST 分区：基于分区列的离散值列表。例如，按地区值分区。

HASH 分区：基于分区列值的哈希值分配到固定数量的分区。

KEY 分区：类似 HASH，但使用 MySQL 内部的哈希函数。

COLUMNS 分区（MySQL 5.5+）：支持非整数列（如字符串、日期）进行 RANGE 或 LIST 分区。

2.分区列的要求

分区列必须是表的主键或唯一键的一部分（对于 KEY 和 HASH 分区）。

分区列可以是整数、字符串、日期等类型（COLUMNS 分区支持非整数）。

分区列的值不能是表达式或函数的结果（除非使用 MySQL 8.0+ 的表达式分区）。