MySQL——优化（六）：其他分析诊断
一、慢日志查询
1、参数
- log_output：
- 日志输出到哪儿，默认FILE，表示文件；设置成TABLE，则将日志记录到mysql.slow_log中。也可设置多种格式，比如 FILE,TABLE
- long_query_time：
- 执行时间超过这么久才记录到慢查询日志，单位秒，可使用小数表示小于秒的时间
- log_queries_not_using_indexes：
- 是否要将未使用索引的SQL记录到慢查询日志中，此配置会无视long_query_time的的配置。生产环境建议关闭；开发环境建议开启。
- log_throttle_queries_not_using_indexes：
- 和log_queries_not_using_indexes配合使用，如果log_queries_not_using_indexes打开，则该参数将限制每分钟写入的、未使用索引的SQL数量。
- min_examined_row_limit：
- 扫描行数至少达到这么多才记录到慢查询日志
- log_slow_admin_statements：
- 是否要记录管理语句，默认关闭。管理语句包括ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE。
- slow_query_log_file：
- 指定慢查询日志文件路径
- log_slow_extra：
- 当log_output=FILE时，是否要记录额外信息（MySQL 8.0.14开始提供），对log_output=TABLE的结果无影响。
2、使用方式
方式一、修改配置文件my.cnf，在[mysqld]段落中加入如上参数即可
需要重启
[mysqld] # ... log_output = 'FILE,TABLE'; slow_query_log = ON long_query_time = 0.001
方式二、通过全局变量设置
这种方式无需重启即可生效，但一旦重启，配置又会丢失。
set global log_output = 'FILE,TABLE'; set global slow_query_log = 'ON'; set global long_query_time =0.001;
3、分析
当log_output = TABLE时，可直接用如下语句分析：
select * from `mysql`.slow_log
当log_output = FILE时，可使用mysqldumpslow分析
➜ mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v 展示更详细的信息 -d debug -s ORDER 以哪种方式排序，默认at al: 平均锁定时间 ar: 平均返回记录数 at: 平均查询时间 c: 访问计数 l: 锁定时间 r: 返回记录 t: 查询时间 -r 将-s的排序倒序 -t NUM top n的意思，展示最前面的几条 -a 不去将数字展示成N，将字符串展示成'S' -n NUM abstract numbers with at least n digits within names -g PATTERN 后边可以写一个正则，只有符合正则的行会展示 -h HOSTNAME 慢查询日志以 主机名-slow.log的格式命名，-h可指定读取指定主机名的慢查询日志，默认情况下是*，读取所有的慢查询日志 -i NAME MySQL Server的实例名称（如果使用了mysql.server startup脚本的话） -l 不将锁定时间从总时间中减去
二、SQL性能分析
1、SHOW PROFILE
- 格式
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列出相应操作对应的函数名及其在源码中的位置(行) | SWAPS显示swap交换次数 }
- 开关
select @@have_profiling; 0表示未启用，1表示已启用，
开启：set profiling = 1;
-- 默认展示15条 show profiles -- 使用profiling_history_size调整展示的条目数 set profiling_history_size = 100;
- 分析查询
mysql> SHOW PROFILES; +----------+----------+--------------------------+ | Query_ID | Duration | Query | +----------+----------+--------------------------+ | 0 | 0.000088 | SET PROFILING = 1 | | 1 | 0.000136 | DROP TABLE IF EXISTS t1 | | 2 | 0.011947 | CREATE TABLE t1 (id INT) | +----------+----------+--------------------------+ 3 rows in set (0.00 sec) mysql> SHOW PROFILE; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | checking permissions | 0.000040 | | creating table | 0.000056 | | After create | 0.011363 | | query end | 0.000375 | | freeing items | 0.000089 | | logging slow query | 0.000019 | | cleaning up | 0.000005 | +----------------------+----------+ 7 rows in set (0.00 sec) -- 默认情况下，只展示Status和Duration两列，如果想展示更多信息，可指定type。 mysql> SHOW PROFILE FOR QUERY 1; +--------------------+----------+ | Status | Duration | +--------------------+----------+ | query end | 0.000107 | | freeing items | 0.000008 | | logging slow query | 0.000015 | | cleaning up | 0.000006 | +--------------------+----------+ 4 rows in set (0.00 sec) -- 展示CPU相关的开销 mysql> SHOW PROFILE CPU FOR QUERY 2; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | checking permissions | 0.000040 | 0.000038 | 0.000002 | | creating table | 0.000056 | 0.000028 | 0.000028 | | After create | 0.011363 | 0.000217 | 0.001571 | | query end | 0.000375 | 0.000013 | 0.000028 | | freeing items | 0.000089 | 0.000010 | 0.000014 | | logging slow query | 0.000019 | 0.000009 | 0.000010 | | cleaning up | 0.000005 | 0.000003 | 0.000002 | +----------------------+----------+----------+------------+ 7 rows in set (0.00 sec)
2、INFORMATION_SCHEMA.PROFILING
INFORMATION_SCHEMA.PROFILING用来做性能分析。它的内容对应SHOW PROFILE和SHOW PROFILES 语句产生的信息
3、PERFORMANCE_SCHEMA
PERFORMANCE_SCHEMA是MySQL建议的性能分析方式，未来SHOW PROFILE、INFORMATION_SCHEMA.PROFILING都会废弃。据笔者研究，PERFORMANCE_SCHEMA在MySQL 5.6引入，因此，在MySQL 5.6及更高版本才能使用
三、数据库诊断命令
1、SHOW PROCESSLIST
SHOW FULL PROCESSLIST 等价于 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
SHOW [FULL] PROCESSLIST用于查看当前正在运行的线程。如果执行此命令的用户拥有 PROCESS 权限，则可看到所有线程；否则只能看到自己的线程（即与当前登录用户关联的线程）。如果不使用FULL关键字，只在Info字段中展示前100个字符。
使用场景：
当遇到“too many connections”错误信息时，想要了解发生了什么，SHOW PROCESSLIST就非常有用。可使用kill 语句杀死异常线程。
字段说明：
- Id：连接的唯一标识，是CONNECTION_ID()函数的返回
- User：发出该语句的MySQL用户
- system_user表示服务器产生的非客户端线程，用于处理内部任务。这可能是用来在从库复制或延迟行处理器的IO/SQL线程。对于system_user，Host字段将会为空
- unauthenticated user是指与客户端连接，但尚未完成客户端用户身份认证的线程
- event_scheduler是指事件调度器的监控线程
- Host：发出该语句的客户端的主机名（当User是system_user时，Host为空）。TCP/IP链接的主机名以 host_name:client_port 格式上报，以便更轻松地了解哪个客户端在干什么。
- db：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则值为NULL
- Command：当前线程正在执行的命令
- Binlog Dump：主库上的线程，用于将binlog内容发送到从库
- Change user：线程正在执行更改用户操作
- Close stmt：线程正在关闭一个prepared statement
- Connect：一个复制从库已连接到其主库
- Connect Out：一个复制从库正在连接到其主库
- Create DB：线程正在执行create-database操作
- Daemon：服务器内部线程，而非为客户端连接提供服务的线程
- Debug：该线程正在生成调试信息
- Delayed insert：该线程是延迟插入处理程序
- Drop DB：线程正在执行drop-database操作。
- Error
- Execute：线程正在执行一个prepared statement
- Fetch：正在从Prepared Statement 中获取执行结果
- Field List：该线程正在获取表的字段信息
- Init DB：线程正在选择默认数据库
- Kill：该线程正在杀死另一个线程
- Long Data：正在从prepared statement中检索long data
- Ping：线程正在处理server-ping请求
- Prepare：该线程正在准备一个prepared statement
- Processlist：该线程正在生成服务器线程相关信息
- Query：线程正在执行一条语句
- Quit：线程正在终止
- Refresh：该线程是刷新表，日志或缓存；或者正在重置状态变量或在复制服务器信息
- Register Slave：该线程正在注册一个从库
- Reset stmt：线程正在重置prepared statement
- Set option：线程正在设置或重置client statement-execution选项
- Shutdown：线程正在关闭服务器
- Sleep：线程正在等待客户端向其发送statement
- Statistics：该线程正在生成服务器状态信息
- Table Dump：线程正在将表内容发送到从属服务器
- Time：线程处于当前状态的时间（单位秒）。对于从库的SQL线程，该字段的值表示上次复制事件的时间和从库机器的实际时间之间经过了多少秒
- State：指示线程正在执行的操作、事件或状态。大多数State对应于非常快速的操作。如果线程在给定状态下很久，则需要排查下。
- Info：当前线程正在执行的语句，如果未执行任何语句则值为NULL。该语句可能是发送到服务器的那条语句，也可能是内部的语句（如果某个语句执行了其他语句）。例如一条CALL语句执行了一条正在执行SELECT语句的存储过程，则Info字段会展示SELECT语句。
INFORMATION_SCHEMA.PROCESSLIST 表的应用SQL
-- 按照客户端IP分组，看哪个客户端的连接数最多 select client_ip, count(client_ip) as client_num from (select substring_index(host, ':', 1) as client_ip from `information_schema`.processlist) as connect_info group by client_ip order by client_num desc; -- 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程 select * from `information_schema`.processlist where Command != 'Sleep' order by Time desc; -- 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 select concat('kill ', id, ';') from `information_schema`.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
参考：
2、其他命令
- SHOW STATUS
- SHOW VARIABLES
- SHOW TABLE STATUS
- SHOW INDEX
SHOW INDEX FROM mydb.mytable;
- SHOW ENGINE
浙公网安备 33010602011771号