性能测试--数据库慢 SQL 语句分析

一 慢 SQL 语句的几种常见诱因

1. 无索引或索引失效

​ 当查询基于一个没有索引的列进行过滤、排序或连接时，数据库可能被迫进行全表扫描，即逐行检查所有数据，导致性能显著下降。

​ 虽然我们很多时候建立了索引，但在一些特定的场景下，索引还有可能会失效，所以索引失效也是导致慢查询的主要原因之一。索引失效可能由于以下原因：

• 使用了不等于（!=）或 NOT IN 这类无法有效利用索引的比较运算符。
• 对索引列进行了复杂的函数计算或表达式操作，导致索引无法被直接用于查询优化。
• 索引选择性不高，即索引列值分布过于均匀或重复率过高，使得使用索引的效益降低。

2.锁等待

​ 我们常用的存储引擎有 InnoDB 和 MyISAM，前者支持行锁和表锁，后者只支持表锁。

​ 如果数据库操作是基于表锁实现的，如果一张订单表在更新时，需要锁住整张表，那么其它大量数据库操作（包括查询）都将处于等待状态，这将严重影响到系统的并发性能。这时，InnoDB 存储引擎支持的行锁更适合高并发场景。但在使用 InnoDB 存储引擎时，要特别注意行锁升级为表锁的可能。在批量更新操作时，行锁就很可能会升级为表锁。

​ MySQL 认为如果对一张表使用大量行锁，会导致事务执行效率下降，从而可能造成其它事务长时间锁等待和更多的锁冲突问题发生，致使性能严重下降，所以 MySQL 会将行锁升级为表锁。还有，行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行锁也会升级为表锁。

​ 因此，基于表锁的数据库操作，会导致 SQL 阻塞等待，从而影响执行速度。在一些更新操作（insert\update\delete）大于或等于读操作的情况下，MySQL 不建议使用 MyISAM存储引擎。除了锁升级之外，行锁相对表锁来说，虽然粒度更细，并发能力提升了，但也带来了新的问题，那就是死锁。因此，在使用行锁时，我们要注意避免死锁。

3. 不恰当的 SQL 语句

3.1分页查询

​ 在大数据量的表中，使用 LIMIT 子句配合 OFFSET 实现分页时，OFFSET 值越大，查询效率越低，因为数据库需要先跳过大量不需要的行。可以考虑使用“跳跃查询”（如 MySQL 中的 LIMIT ... OFFSET 与 WHERE ... > 结合）或基于索引的分页技术来改善。

3.2对非索引字段进行排序：

​ 对没有索引的字段进行 ORDER BY 或 GROUP BY 操作，数据库可能需要进行临时表排序，消耗大量内存和 CPU 资源，尤其是在数据量大时。

3.3全表 JOIN

未指定有效连接条件或连接条件未使用索引，导致数据库进行笛卡尔积运算，产生庞大的中间结果集。

3.4子查询效率低下

​ 某些复杂的子查询可能无法被优化器高效处理，特别是嵌套多层或关联子查询。有时可将其改写为连接查询或使用临时表、物化视图等技术提高效率。

3.5过度使用 DISTINCT、GROUP BY 或 UNION

​ 这些操作可能导致大量的数据排序与去重工作，特别是在未伴随适当索引的情况下

二 开启数据库的慢查询日志

​ 开启数据库的慢查询日志可以帮助你识别和优化数据库中的查询性能问题,下面是mysql慢查询日志的启用方法

1 编辑配置文件

​ 打开 MySQL 的配置文件（通常是 my.cnf 或 my.ini），在 [mysqld] 部分添加或修改以下行：

Copy Codeslow_query_log = 1
slow_query_log_file = /path/to/slow_query.log
long_query_time = 1

• slow_query_log：启用慢查询日志，设置为 1 表示启用，0 表示禁用。
• slow_query_log_file：指定慢查询日志文件的路径。
• long_query_time：指定查询执行时间的阈值，单位为秒。超过此阈值的查询会被记录在慢查询日志中。

2 重启数据库服务

​ 保存并关闭配置文件，然后重启 MySQL 或 MariaDB 服务。

3 检查配置是否生效

show  VARIABLES LIKE "slow_query_log";   ----查询慢sql日志是否开启
show  VARIABLES LIKE "long_query_time";  ----查询多长时间为慢查询

4 查看日志

​ 慢查询日志会记录查询执行时间超过设定阈值的查询语句，你可以通过查看慢查询日志文件来分析慢查询的原因，并优化相应的查询。

三 分析 慢SQL语句的步骤

​ 通过 EXPLAIN命令来查看些执行信息,通过执行信息可以获取,个 SQL 先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等。

1 通过 EXPLAIN 分析 SQL 执行计划

id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查
询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、
SUBQUERY（子查询）等。
table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
partitions：访问的分区表信息。
type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，
结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
	system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。如果	是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。
	eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
	ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。
	range：索引范围扫描，比如，<，>，between 等操作。
	index：索引全表扫描，此时遍历整个索引树。
	ALL：表示全表扫描，需要遍历全表来找到对应的行
possible_keys：可能使用到的索引。
key：实际使用到的索引。
key_len：当前使用的索引的长度。
ref：关联 id 等信息。
rows：查找到记录所扫描的行数。
filtered：查找到所需记录占总扫描记录数的比例。
Extra：额外的信息

2. 通过 Show Profile 分析 SQL 执行性能

上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。

Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。以下是相关命令的注释：

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n [LIMIT row_count [OFFSET offset]]]

type：指定要显示的性能分析类型，可以是下列之一或其组合：
    ALL：显示所有类型的性能分析信息。
    BLOCK IO：显示块输入输出的性能分析信息。
    CONTEXT SWITCHES：显示上下文切换的性能分析信息。
    CPU：显示 CPU 使用情况的性能分析信息。
    IPC：显示进程间通信的性能分析信息。
    MEMORY：显示内存使用情况的性能分析信息。
    PAGE FAULTS：显示页面错误的性能分析信息。
    SOURCE：显示查询的源代码和栈跟踪的性能分析信息。
    
FOR QUERY n：可选项，指定要显示性能分析信息的查询编号 n。如果省略此选项，则显示最后一次查询的性能分析信息。

LIMIT row_count：可选项，指定要显示的行数限制。

OFFSET offset：可选项，指定结果集的偏移量。

以下是一些 SHOW PROFILE 的示例用法

-- 显示最后一次查询的所有性能分析信息
SHOW PROFILE;

-- 显示最后一次查询的 CPU 和 MEMORY 性能分析信息
SHOW PROFILE CPU, MEMORY;

-- 显示第 5 条查询的所有性能分析信息
SHOW PROFILE FOR QUERY 5;

-- 显示第 5 条查询的 CPU 和 MEMORY 性能分析信息，并限制结果集的行数为 10
SHOW PROFILE CPU, MEMORY FOR QUERY 5 LIMIT 10;

注意，MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，如果你不太确定的话，可以通过 select @@have_profiling 查询是否支持该功能

Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100

3通过 Show Profile for Query ID查看线程消耗时间

获取到 Query_ID 之后，我们再通过 Show Profile for Query ID 语句，就能够查看到对应Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了：

四 sql常见查询语句优化

​

1 避免全表扫描

未优化查询：

Sql

1SELECT * FROM employees WHERE name LIKE '%John%';

优化查询：

Sql

1CREATE INDEX idx_employees_name ON employees(name);
2SELECT * FROM employees WHERE name LIKE 'John%'; -- 或使用全文索引进行模糊匹配

分析：在经常用于查询条件的name列上创建普通索引（或针对模糊搜索的全文索引），使得查询可以利用索引来快速定位含有“John”起始的员工记录，避免了全表扫描。

2合理使用索引

• 避免在索引列上使用计算、函数或表达式，这可能导致无法有效利用索引。

  未优化查询：

  1SELECT * FROM products WHERE UPPER(title) = 'APPLE IPHONE';
  

  优化查询：

  1CREATE INDEX idx_products_title_upper ON products(UPPER(title));
  2SELECT * FROM products WHERE title = 'APPLE IPHONE'; -- 或使用新建的函数索引来支持原查询
  

  分析：原查询中对索引列title使用了UPPER()函数，导致无法直接使用已有的索引。优化方案是创建一个基于UPPER(title)的函数索引，或者直接在查询中使用未经过函数处理的原始值，以便利用索引加速查询。

• 对于范围查询，考虑使用BETWEEN替换IN列表，尤其当IN列表中的值不连续时。

  未优化查询：

  1SELECT * FROM sales WHERE order_date IN ('2024-0¼-01', '2024-04-02', '2024-04-03');
  

  优化查询：

  1SELECT * FROM sales WHERE order_date BETWEEN '2024-04-01' AND '2024-04-03';
  

  分析：将不连续的IN列表替换为连续的BETWEEN范围查询，若order_date列已有索引，BETWEEN查询能更有效地利用索引来检索指定日期范围内的销售记录。

• 对于联合索引，遵循最左前缀原则，并注意查询条件的顺序。

  未优化查询：

  1CREATE INDEX idx_users_name_email ON users(name, email);
  2SELECT * FROM users WHERE email = 'john.doe@example.com';
  

  优化查询：

  1SELECT * FROM users WHERE name = 'John Doe' AND email = 'john.doe@example.com';
  

  分析：联合索引idx_users_name_email遵循最左前缀原则，即查询必须从索引的第一列开始。优化后的查询同时使用了name和email作为条件，符合最左前缀原则，可以利用联合索引来提升查询效率。

3 慎用NOT IN和!=**

​ 这些操作可能导致索引失效，改用LEFT JOIN ... IS NULL或EXISTS等逻辑等价但可能更高效的查询方式。

​ 未优化查询：

SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE country = 'USA');

优化查询：

1SELECT o.* FROM orders o
2LEFT JOIN customers c ON o.customer_id = c.id AND c.country = 'USA'
3WHERE c.id IS NULL;

分析：将NOT IN子查询改写为LEFT JOIN ... IS NULL形式，逻辑等价但可能更高效，因为某些数据库系统在处理NOT IN和!=时可能无法充分利用索引。

4 减少SELECT *

​ 仅选择需要的列，避免无谓的数据传输和处理开销。

5 使用LIMIT

当只需要返回少量结果时，加上LIMIT限制返回记录数，提高查询效率。

6 优化JOIN操作

• 确保连接条件上有合适的索引。
• 尽量减少嵌套循环连接的使用，特别是当其中一个表很大时。
• 使用INNER JOIN替代子查询，或者将子查询改写为关联查询。

7避免在WHERE子句中对字段进行NULL值判断和复杂的表达式运算

​ 这些可能导致索引无法使用。