性能测试--数据库慢 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] 部分添加或修改以下行：

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

复制代码

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

2 重启数据库服务

​ 生存并关闭配置文件，然后重启 MySQL 或 MariaDB 服务。
3 检查配置是否见效

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

复制代码

4 检察日记

​ 慢查询日记会记录查询实行时间凌驾设定阈值的查询语句，你可以通过检察慢查询日记文件来分析慢查询的缘故原由，并优化相应的查询。
三 分析 慢SQL语句的步调

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

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

复制代码

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

上述通过 EXPLAIN 分析实行计划，仅仅是停顿在分析 SQL 的外部的实行环境，如果我们想要深入到 MySQL 内核中，从实行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。
Profile 除了可以分析实行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。以下是相干命令的解释：

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

复制代码

1. type：指定要显示的性能分析类型，可以是下列之一或其组合：
2.     ALL：显示所有类型的性能分析信息。
3.     BLOCK IO：显示块输入输出的性能分析信息。
4.     CONTEXT SWITCHES：显示上下文切换的性能分析信息。
5.     CPU：显示 CPU 使用情况的性能分析信息。
6.     IPC：显示进程间通信的性能分析信息。
7.     MEMORY：显示内存使用情况的性能分析信息。
8.     PAGE FAULTS：显示页面错误的性能分析信息。
9.     SOURCE：显示查询的源代码和栈跟踪的性能分析信息。
10.    
11. FOR QUERY n：可选项，指定要显示性能分析信息的查询编号 n。如果省略此选项，则显示最后一次查询的性能分析信息。
13. LIMIT row_count：可选项，指定要显示的行数限制。
15. OFFSET offset：可选项，指定结果集的偏移量。

复制代码

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

1. -- 显示最后一次查询的所有性能分析信息
2. SHOW PROFILE;
4. -- 显示最后一次查询的 CPU 和 MEMORY 性能分析信息
5. SHOW PROFILE CPU, MEMORY;
7. -- 显示第 5 条查询的所有性能分析信息
8. SHOW PROFILE FOR QUERY 5;
10. -- 显示第 5 条查询的 CPU 和 MEMORY 性能分析信息，并限制结果集的行数为 10
11. 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

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

复制代码

优化查询：
Sql

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

复制代码

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

• 避免在索引列上使用计算、函数或表达式，这可能导致无法有效使用索引。
  未优化查询：
  
  1. 1SELECT * FROM products WHERE UPPER(title) = 'APPLE IPHONE';

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

  复制代码
  分析：原查询中对索引列title使用了UPPER()函数，导致无法直接使用已有的索引。优化方案是创建一个基于UPPER(title)的函数索引，大概直接在查询中使用未颠末函数处置惩罚的原始值，以便使用索引加快查询。
  
• 对于范围查询，思量使用BETWEEN替换IN列表，尤其当IN列表中的值不连续时。
  未优化查询：
  
  1. 1SELECT * FROM sales WHERE order_date IN ('2024-0¼-01', '2024-04-02', '2024-04-03');

  复制代码
  优化查询：
  
  1. 1SELECT * FROM sales WHERE order_date BETWEEN '2024-04-01' AND '2024-04-03';

  复制代码
  分析：将不连续的IN列表替换为连续的BETWEEN范围查询，若order_date列已有索引，BETWEEN查询能更有效地使用索引来检索指定日期范围内的贩卖记录。
  
• 对于团结索引，遵循最左前缀原则，并注意查询条件的顺序。
  未优化查询：
  
  1. 1CREATE INDEX idx_users_name_email ON users(name, email);
  2. 2SELECT * FROM users WHERE email = 'john.doe@example.com';

  复制代码
  优化查询：
  
  1. 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等逻辑等价但可能更高效的查询方式。
​ 未优化查询：

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

复制代码

优化查询：

1. 1SELECT o.* FROM orders o
2. 2LEFT JOIN customers c ON o.customer_id = c.id AND c.country = 'USA'
3. 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值判定和复杂的表达式运算

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。