MySQL 利用 Performance Schema 定位息争决慢 SQL 题目

在数据库性能调优的过程中，慢 SQL 查询每每是导致应用步调相应迟和缓体系性能降落的紧张缘故原由。MySQL 提供了 Performance Schema 作为强盛的工具，资助开辟者和数据库管理员定位并办理慢 SQL 查扣题目。Performance Schema 是 MySQL 内置的一套性能监控体系，可以深入发掘数据库的实验情况，并为优化提供具体的指标。本文将先容怎样利用 MySQL 的 Performance Schema 定位息争决慢 SQL 查扣题目。
1. 什么是 Performance Schema？

Performance Schema 是 MySQL 5.5 版本引入的一个性能监控框架，它为开辟者提供了丰富的性能数据，包罗查询实验时间、资源斲丧、锁争用、索引利用情况等。通过 Performance Schema，用户可以监控到 SQL 查询的实验过程、体系资源的利用率以及其他与性夺目系的具体信息。
Performance Schema 在数据库的内部运行并记载实验情况，数据可以通过查询相应的体系表来访问。因此，它不像传统的日志记载工具那样影响性能，可以大概实时提供数据库运行状态的全面视图。
2. 启用 Performance Schema

在默认情况下，Performance Schema 在 MySQL 中是禁用的，尤其是在某些轻量级设置中。为了启用 Performance Schema，必要编辑 MySQL 的设置文件并确保 Performance Schema 被启用。
步调：

• 打开 MySQL 设置文件（my.cnf 或 my.ini），添加以下设置：
  2.  performance_schema = ON # 启用 Performance Schema

  复制代码
• 生存并重启 MySQL 服务。
  

启用 Performance Schema 后，你可以利用它来分析数据库的性能，特殊是定位慢 SQL 查询。
3. 利用 Performance Schema 定位慢 SQL 查询

Performance Schema 提供了多个表来网络和存储数据库性能数据。要定位慢 SQL 查询，紧张依赖以下几个表：
3.1 创建large_table 表

2. CREATE TABLE large_table (
3.     id INT AUTO_INCREMENT PRIMARY KEY,
4.     name VARCHAR(255) NOT NULL,
5.     email VARCHAR(255) NOT NULL,
6.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
7.     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
8. );

复制代码

3.2利用navicat自带工具天生500w模仿数据

3.2 创建查询sql语句

1.利用 LIKE '%value%' 会导致全表扫描，由于数据库无法利用索引（如果没有特定的前缀索引）。此类查询在数据量较大时通常会非常慢。
大概导致慢查询的 SQL 示例：

2. SELECT *
3. FROM large_table
4. WHERE email LIKE '%example.com';

复制代码

2.查询缺少索引的列举行排序：ORDER BY

2. 如果 ORDER BY操作没有适当的索引，MySQL 需要扫描整个表并在内存中进行排序，这可能会导致性能下降，尤其是数据量较大时

复制代码

大概导致慢查询的 SQL 示例

2. SELECT *
3. FROM large_table
4. ORDER BY created_at DESC
5. LIMIT 10;

复制代码

3.3利用 Performance Schema 定位慢查询

2. SELECT
3.     DIGEST_TEXT,
4.     COUNT_STAR,
5.     SUM_TIMER_WAIT,
6.     AVG_TIMER_WAIT
7. FROM
8.     performance_schema.events_statements_summary_by_digest
9. ORDER BY
10.     SUM_TIMER_WAIT DESC
11. LIMIT 10;

复制代码

这段SQL语句的作用是：从performance_schema中查询出总实验时间最长的前10条SQL语句，并表现它们的择要文本、实验次数、总实验时间平静均实验时间。这通常用于性能优化场景，资助数据库管理员快速定位那些对数据库性能影响最大的SQL语句。

检察具体的慢查询汗青

2. SELECT
3.     EVENT_ID,
4.     SQL_TEXT,
5.     TIMER_WAIT
6. FROM
7.     performance_schema.events_statements_history
8. WHERE
9.     TIMER_WAIT > 1000000  -- 查找执行时间大于 1 毫秒的查询
10. ORDER BY
11.     TIMER_WAIT DESC;

复制代码

4. 性能指标的解读与优化

Performance Schema 提供的各种指标可以资助你深入相识慢查询背后的缘故原由。常见的性能瓶颈包罗：

• 长时间的锁期待：如果某个查询实验时间长，大概是由于它在期待锁。你可以通过查询 performance_schema.data_locks 表来检察是否有锁期待。
  
• 高 I/O 利用：如果查询导致了大量的磁盘读写，大概是由于缺乏符合的索引。通过 performance_schema.file_summary_by_instance 和 performance_schema.table_io_waits_summary_by_table 等表，你可以分析 SQL 查询的 I/O 性能。
  
• 缺乏索引：某些查询大概会导致全表扫描，进而斲丧大量的 CPU 和内存资源。通过 EXPLAIN 语句分析查询实验筹划，查抄是否利用了索引。
  

5. 性能优化战略

在通过 Performance Schema 定位到慢查询之后，你可以采取以下优化战略：
5.1 优化查询语句

• 利用索引：确保查询中涉及的列（尤其是 WHERE、JOIN 和 ORDER BY 中的列）有符合的索引。
  
• 克制全表扫描：只管克制在 WHERE 子句中利用导致全表扫描的条件。
  
• 简化查询：克制复杂的查询，尤其是多层嵌套的子查询，可以通过优化查询逻辑或利用临时表来简化查询。
  

5.2 调解数据库设置

• 增长缓冲池巨细：增大 MySQL 的 innodb_buffer_pool_size，进步数据缓存的掷中率，淘汰磁盘 I/O。
  
• 调解查询缓存：固然查询缓存功能在 MySQL 5.7 后已被弃用，但在老版本中，开启查询缓存可以进步查询性能。
  

6. 总结

慢 SQL 查询的优化并不但仅是通过修改单一查询语句实现的，更多的是必要综合思量索引优化、查询逻辑、数据库设置以及硬件资源等多个因素。借助 Performance Schema，可以在 MySQL 数据库中实现高效的性能调优，从而提拔团体的数据库相应速率和体系性能。

---

盼望你喜好这篇文章！请点关注和收藏吧。你的关注和收藏会是我积极更新的动力，祝关注和收藏的帅哥玉人们本年都能暴富。如果有更多题目，接待随时提问