mysql 数据库性能分析工具简介

分析 sql 性能整体步骤

  1. 观察服务器状态(系统性能)，看是否存在周期性波动。
  2. 如果存在波动，则可以增加缓存或修改缓存失效策略。
  3. 如果没有周期性波动，而是不规则的延迟或卡顿，又或者是添加了缓存或修改了缓存失效策略之后问题仍然未解决，我们就可以开启慢查询功能，通过慢查询日志找到执行较慢的 sql 语句。
  4. 通过 explain 查看对于 sql 的执行计划，或通过 show profiling 查看 sql 语句执行过程中每一步的时间成本。
  5. 如果是 sql 等待的时间过长，则可以调整服务器参数。
  6. 如果是执行时间较久，则可以观察索引是否需要优化、sql 语句是否需要优化、数据表设计是否需要优化。
  7. 如果通过优化后问题仍然没有得到解决，则需要增加服务器数量进行读写分离或分库分表，来分散服务器的压力。

查看系统性能参数

在 mysql 中，可以使用以下语句查看数据库服务器的性能参数、执行频率:

-- 如果不明确指定 global 或 session 则会先在 session 中查询，没查询到才会在 gobal 中查询
show [global | session] status like '参数'

一些常用的参数如下：

  • Connection：连接 mysql 服务器的次数
  • Uptime：mysql 服务器的上线时间
  • Slow_queries：慢查询的次数
  • Last_query_cost：查询最后一次执行的 sql 语句的所花费的数据页成本

sql 查询时一个动态的过程，从页加载的角度来看，我们可以得到两点结论：
1.位置决定效率(如果页就在数据库缓冲池中，那么效率时最高的，否则还需要从内存或磁盘中进行读取，当然针对单个页的读取来说，如果页存在内存中会比在磁盘中读取效率高很多)。
2.批量决定效率(如果我们从磁盘中对单一页进行随机读取，那么效率很低，而采用顺序读取的方式批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取)。
所以说，遇到 I/O 并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

  • Innodb_rows_read：Select 查询返回的行数
  • Innodb_rows_inserted：执行 Insert 操作插入的行数
  • Innodb_rows_updated：执行 update 操作更新的行数
  • Innodb_rows_deleted：执行 delete 操作删除的行数
  • Com_select：查询操作的次数
  • Com_insert：插入操作的次数(对于批量插入的 insert 操作，只累加一次)
  • Com_update：更新操作的次数
  • Com_delete：删除操作的次数

定位执行慢的 sql 语句：慢查询日志

开启慢查询日志功能

默认情况下，mysql 数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优的话，一般不建议启动该参数，因为开启慢查询日志会对性能有所影响。慢查询日志支持将日志写入文件中。
可以通过以下命令来查询/开启慢查询日志相关功能

-- 查询慢查询日志是否开启
show variables like 'slow_query_log';
-- 开启慢查询功能
set global slow_query_log = on;
-- 查询慢查询时间阈值(单位：秒)
show variables like 'long_query_time';
-- 设置慢查询时间阈值
set [global | session] long_query_time = 8;
-- 查询慢查询日志文件所在目录
show variables like 'slow_query_log_file';
-- 关闭慢查询功能
set global slow_query_log = off;

除了上述变量，控制慢查询日志的还有一个系统变量:min_examined_row_limit。这个变量的意思是查询扫描过的最少记录数。这个变量和查询实现时间共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且执行时间超过 long_query_time 的值，那么这个查询就会被记录到慢查询日志中，反之咋不被记录到慢查询日志中。
这个默认值是 0，与 long_query_time=8 结合在一起，表示只要查询的执行时间超过 8 秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。可以根据需要用 set 语句修改 min_examined_row_limit 的值。

使用慢查询日志分析工具：mysqldumpslow

mysqldumpslow 工具在 mysql 安装目录的 bin 目录下，可以使用命令mysqldumpslow -help查看该工具的使用方法。
比如我们用mysqldumpslow -s t -t 5 /var/lib/mysql/liejizhuanyuan-slow.log来查询最近5条触发慢查询的 sql 语句。
image

查看 sql 执行成本：show profile

show profile 是 mysql 提供的可以用来分析当前会话中 sql 都做了什么、执行的资源消耗情况的工具，可用于 sql 调优的测量。默认情况下处于关闭状态。相关命令如下

-- 查看 show profile 功能是否开启
show variables like 'profiling';
-- 开启/关闭 show profile
set profiling = 'on | off';
-- 查看最近执行的查询 sql 信息
show profiles;
-- 查看最后一次查询 sql 的执行成本
show profile [查询参数];
-- 查看某一次的查询 sql 的执行成本
show profile [查询参数] for query 查询id;

show profile常用的查询参数:

  • all:显示所有开销信息
  • block io:显示块 IO 开销
  • context switches：上下文切换开销
  • cpu：显示 CPU 开销信息
  • memory：显示内存开销信息
  • page faults：显示也没错误开销信息
  • source：显示 source_function、source_file、source_line相关的开销信息
  • swaps：显示交换册数开销信息

分析语句执行计划：explain

定位了查询慢的 sql 之后，我们就可以使用 explain 或 describe 工具查看语句的执行计划(查看标的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化查询)，从而做针对性的分析。describe 语句的使用方法与 explain 语句是一样的。
使用方法就是在需要分析的 sql 语句前加上 explain 或 describe 关键字即可。

-- explain 语法示例
explain [format=JSON | TREE] select * from demo_table where id=1;

mysql 中有专门负责优化 select 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供它认为最优的执行计划(它认为最优的数据检索方式不一定是 DBA 认为最优的，这部分最耗费时间)。

mysql 5.6.3 以前 explain 只能分析 select 语句，5.6.3 之后就可以分析 select、update、delete
explain 语句输出的各个列的含义如下：

  • id：代表查询执行中的执行顺序标识号和执行优先级
  • select_type：select 关键字对于的查询类型
  • table：表名
  • partitions：匹配的分区信息，代表分区表的命中情况，非分区表该项为 NULL。
  • type：针对单表的访问方法，常见值如下(从好到坏)：
    • system：当表中只有一条记录并且该表使用的存储引擎的统计数据时精确的(如：MyISAM、Memory)，那么对该表的访问方法就是 system
    • const：当我们根据主键或唯一索引列与常数进行等值匹配时，对单表的访问方法就是 const
    • eq_ref：在连接查询时，如果被驱动表是通过主键或唯一二级索引等值匹配的方式进行访问的(如果该主键或唯一二级索引时联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是 eq_ref。
    • ref：当通过普通二级索引与常量进行等值匹配时来查询某个表，那么该表的访问方法就是 ref。
    • ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以时 null 值时，那么对该表的访问方法就可能是 ref_or_null。
    • index_merge：单表访问方法时在某些场景下可以使用 Intersection、Union、Sort-Union 这三种索引合并的方式来执行，那么对该表的访问方法就是 index_merge。
    • unique_subquery：针对在一些包含 in 子查询的查询语句中，如果查询优化器决定将 in 子查询转换为 exists 子查询，且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的类型就是 unique_subquery。
    • range：如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法。
    • index：当我们可以使用索引覆盖，但需要扫描圈闭的索引记录时，该表的访问方法就是 index。
    • ALL：全表扫描

sql 性能优化的目标至少要达到 range 级别

  • possible_keys：可能用到的索引
  • key：实际用到的索引
  • key_len：实际使用到的索引长度(即字节数)，主要针对于联合索引有一定参考意义。
  • ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
  • rows：预估的需要读取的记录条数，该数值越小越好
  • filtered：某个经过搜索条件过滤后剩余记录数的百分比，该数值越大越好
  • extra：额外信息，包含不适合在其他列中显示但十分重要的信息。我们可以通过这些信息来更准确的理解 mysql 到底将如何执行给定的查询语句。

mysql 5.7 以前的版本总，要想显示 partitions 需要使用 explain partitions 命令，想要显示 filtered 需要使用 explain extended 命令。在 5.7 版本后，默认显示中就包含了 partitions 和 filtered 信息。

注意：

  • explain 不考虑各种 Cache
  • explain 不能显示 mysql 在执行查询时优化器所做的工作
  • explain 不会告诉你关于触发器、存储过程的信息或用户自定义函数时对查询的影响情况
  • 部分统计信息时估算的，并非精确值

分析优化器执行计划：trace

OPTIMIZER_TRACE是 mysql 5.6 引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
此功能默认关闭，可以通过以下语句开启/关闭：

-- 开启 OPTIMIZER_TRACE 并设置格式为 json
SET [SESSION | GLOBAL] optimizer_trace="enabled=on,end_markers_in_json=on";
-- 设置 trace 最大能够使用的内存大小，避免解析过程因默认内存太小而不能完整展示
set optimizer_trace_max_mem_size=1000000;
-- 开启 OPTIMIZER_TRACE
SET [SESSION | GLOBAL] optimizer_trace="enabled=off";

开启后，我们只需要查询 INFORMATION_SCHEMA.OPTIMIZER_TRACE 就可以知道 mysql 时如何执行 sql 的。可分析的语句包括：select、insert、replace、update、delete、explain、set、declare、case、if、return、call

select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

记录的信息分为以下四部分：

  1. QUERY：查询语句
  2. TRACE：QUERY 字段对于语句的跟踪信息
  3. MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时被截断的跟踪信息的字节数
  4. INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且 TRACE 字段为空，一般在带有 SQL SECURITY DEFINER 的仕途或者是存储过程的情况下会出现此问题。
    通常情况下，MySQL 默认设置下只会记录最后一个被执行的 SQL 查询的优化跟踪信息，这是因为 optimizer_trace_limit 参数默认设置为 1，表示仅存储最近一次查询的跟踪数据。如果你想跟踪并保存更多查询的优化信息，需要修改此参数以及其他相关参数的设置。

mysql 高级性能监控和诊断工具：sys

sys 库是在 MySQL 5.6 版本以后引入的，专门为 MySQL 数据库管理员（DBA）和开发人员提供了一套高级性能监控和诊断工具。它通过组合和扩展 performance_schema 数据库中的信息，以更加友好的视图形式呈现出来，帮助用户更方便地了解和分析数据库性能、资源使用情况以及查询执行计划等关键指标。sys 库包含了一系列预定义的视图，这些视图简化了对性能数据的访问，使得用户无需直接查询复杂的 performance_schema 表就能获取到有用的性能监控信息。例如：

-- 查询冗余索引
select * from sys.schema_redundant_indexes;
-- 查询未使用过的索引
select * from sys.schema_unused_indexes;
-- 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='数据库名';
-- 查询表的访问量
select table_schema,table_name,SUN(io_read_requests+io_write_requests) as io from sys.schma_table_statistics group by table_schema,table_name order by io desc;
-- 查询占用 bufferpool 较多的表
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;
-- 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans shere db='数据库名';
-- 监控 sql 执行的频率
select db,exec_count,query from sys.statement_analysis order by exec_count desc;
-- 监控使用了排序的 sql
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1;
-- 监控使用了临时表或者磁盘临时表的 sql
select db,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_table>0 order by (tmp_tables+tmp_disk_tables) desc;
-- 查看消耗磁盘io的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10;
-- 行锁阻塞情况
select * from sys.innodb_lock_waits;

风险提示：通过 sys 库查询时，MySQL 会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁去查询 sys 或者 performance_schema、information_schema 来完成监控、巡检等工作。

补充说明

当我们调优结束后，我们可以删除相关的慢查询日志文件来节约磁盘空间。
删除慢查询日志后，需要使用命令 mysqladmin -u root -p flush-logs slow 来重置我们的慢查询日志文件。

posted @ 2024-04-17 17:17  劣技砖猿  阅读(62)  评论(0)  编辑  收藏  举报