mysql诊断步骤，日常问题查询步骤

【查看当前mysql链接状态】

 

 1show processlist;

SHOW PROCESSLIST显示哪些线程正在运行。
    如果您有SUPER权限，您可以看到所有线程。
    否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。
    如果有线程在update或者insert 某个表，此时进程的status为updating 或者 sending data。
    如果您不使用FULL关键词，则只显示每个查询的前100个字符(show full processlist)。

id列:一个标识，你要kill 一个语句的时候很有用。

user列: 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。

db列:显示这个进程目前连接的是哪个数据库。

command列:显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）

time列:此这个状态持续的时间，单位是秒。

state列:显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。

info列:显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

 

2 KILL [CONNECTION | QUERY] processlist_id 

它有 CONNECTION 和 QUERY 两种模式，默认为CONNECTION，会将整个应用杀掉。而 QUERY 模式会将当前应用正在执行的语句杀掉，应用本身还是保持着连接。查看所有的应用需要 PROCESS 权限，杀掉应用需要 SUPER 权限，否则只能看到和杀掉自己。 

KILL QUERY 87;//杀掉87查询

KILL CONNECTION 87;//杀掉87应用

 

 一 通过慢查日志等定位那些执行效率较低的SQL语句

//查看是否开启
show variables like 'log_slow_queries'; 
//设置开启
set global log_slow_queries = on;    
//1s以上的识别为慢sql
set global long_query_time =1;
//查看慢查询存放日志
show variables like 'slow_query_log_file';

二 explain 分析SQL的执行计划   

 explain select * from adsource_user limit 1;

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional informatio

1. id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
2. select_type //查询类型(SIMPLE PRIMARY SUBQUERY DERIVED UNION UNION RESULT)
3. table //正在访问哪个表
4. partitions //匹配的分区
5. type //访问的类型(NULL system const eq_ref ref ref_or_null index_merge  range index ALL)
6. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
7. key //实际使用到的索引，如果为NULL，则没有使用索引
8. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
9. ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
10. rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
11. filtered //查询的表行占表的百分比
12. Extra //包含不适合在其它列中显示但十分重要的额外信息

具体意思移步：https://blog.csdn.net/jiadajing267/article/details/81269067

三 show profile 分析

Show  variables like 'profiling'; #默认是关闭的

set profiling = on;#开启( set profiling = 1; 由参数 profiling_history_size决定，默认值为15，最大值为100。如果设置为0，等同于关闭profiling)

show profiles; #会记录profile开启之后的每条sql语句的执行持续时间，可据此诊断有问题的sql语句

show profile cpu,block io for query 2 ;上一步前面的问题SQL数字号码;

 

show profile的常用查询参数。

ALL：显示所有的开销信息。

BLOCK IO：显示块IO开销。

CONTEXT SWITCHES：上下文切换开销。

CPU：显示CPU开销信息。

IPC：显示发送和接收开销信息。

MEMORY：显示内存开销信息。

PAGE FAULTS：显示页面错误开销信息。

SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

SWAPS：显示交换次数开销信息。

常见影响sql速度的status
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了
create tmp table 创建临时表，这个要注意
Copying to tmp table on disk 把内存临时表复制到磁盘
locked

四 trace

使用方式：首先打开trace，设置格式为JSON，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。

 

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

select * from information_schema.optimizer_trace

步骤截图：