MySQL8️⃣SQL 性能分析

1、分析工具

1.1、运行状态

可以查看 SQL 语句的执行频率

• 作用：

  1. 得知当前数据库的 SQL 整体执行情况。
  2. 得知数据库主要的 SQL 类型（查/增删改），以查询为主则可考虑设计索引。

• 语法：查询服务器状态信息，包含执行频率。

  • SESSION：当前会话

  • GLOBAL：全局

    SHOW [SESSION|GLOBAL] STATUS;
    

示例

1.2、慢查询日志（❗）

慢 SQL：执行时间超过指定参数的 SQL。

• 作用：记录慢查询 SQL，方便定位执行效率低的 SQL。

• 开启步骤：

  1. 配置文件：

     # 支持慢查询日志
     slow_query_log=1
     
     # 配置慢日志时间（秒）
     long_query_time=2
     

  2. 开启慢查询日志：

     # 查看状态
     SHOW VARIABLES LIKE 'slow_query_log';
     

  3. 重启 MySQL 服务。

1.3、PROFILES

资料收集：可查看 SQL 语句的执行耗时。

1. 查看状态：

   # 是否支持：YES/NO
   SELECT @@have_profiling;
   
   # 开启状态：0/1
   SELECT @@profiling;
   
   # 开启/关闭
   SET profiling = 1;
   

2. 使用：

   # 所有 DQL 的耗时
   SHOW PROFILES;
   
   # 指定 DQL 的各阶段耗时
   SHOW PROFILE FOR QUERY <query_id>;
   
   # 指定 DQL 的 CPU 使用情况
   SHOW PROFILE CPU FOR QUERY <query_id>;
   

1.4、EXPLAIN（❗）

EXPLAIN 或 DESC：解释 DQL 的执行信息。

字段含义：

1. id：序列号，涉及多表查询时
   • 优先执行 id 值大的 DQL。
   • 相同 id 从上到下执行。
2. select_type：查询类型。
   • SIMPLE：简单表，无需连接或者子查询
   • UNION：连接查询
   • PRIMARY：主查询
   • SUBQUERY：子查询
3. type：连接类型。
   • NULL，system，const
   • eq_ref，ref，range， index，all
4. possible_key：可用的索引。
5. key：实际使用的索引。
6. key_len：索引使用的字节数。
   • 值为索引字段最大可能长度，并非实际使用长度。
   • 在不损失精确性的前提下，值越小越好。
7. rows：需执行查询的行数
   • 在 Innodb 引擎的表中是一个估计值，可能并不准确。
8. filtered：结果行数占 rows 的百分比，值越大越好。
9. Extra：其它信息，如使用的条件。

2、分析思路

实际开发中，可从以下角度分析慢查询 SQL。

1. 没有索引/索引失效：
   1. 使用慢查询日志，找到耗时长的 SQL。
   2. 使用 EXPLAIN 执行计划，判断 SQL 是否使用索引。
      • 有索引，没命中：优化 SQL 结构，避免索引失效。
      • 无索引：新增索引。
2. 单表数据量过多：考虑分库分表
   • 分表：
     1. 水平：根据业务主键，分为多张表（表头不变）。
     2. 垂直：根据业务逻辑，将关联性大的列放在同一张表。
   • 分库：将表放到不同的数据库实例中，将请求路由到不同数据库实例。
3. 网络原因/机器负载过高：
   • 读写分离：主写从读，提高从库数量。
   • 搭建集群
4. 热点数据导致单点负载不均衡：引入缓存以缓解数据库压力，提高数据库响应速度。