MySQL分析常用语句

MySQL分析常用语句

1.show processlist 命令

用于数据库整体情况的查看，如果有spuer权限，则可以看到所有线程状态
例：
mysql> show processlist;

Id	User	Host	db	Command	Time	State	Info
61	root	localhost:54434		Sleep	14
62	root	localhost:54436		Sleep	11
63	root	localhost:54441		Query	0		show 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语句就显示不全，但是一个判断问题语句的重要依据。 

重点字段 state 解析：
通常state状态变化较快，如果长时间处于某一状态，请检查相关SQL运行是否正常。
由于state字段状态比较多，挑几个比较重要的记录一下

 Sending data ：并不是单纯的发送数据，而是包括“收集 + 发送数据”， 如果出现大量相似的SQL语句出现在show proesslist列表中，并且都处于sending data状态，优化查询索引

 Sorting for order : 与SendingData 类似，考虑查询相关优化或索引优化

 Locked  : 该查询被其它查询锁定。如果长时间锁住，注意是否发生死锁 (ps：Innodb 为行级锁，极大的减少了Locked状态的出现)

 Copying to tmp table on disk :拷贝临时表至硬盘中，临时表数据量过大，内存中放不下，拷贝过程中会产生大量I/O 影响服务器性能，检查Sql是否合理，检查业务逻辑是否合理

2.slow_query_log 慢日志查询

MySQL 的慢查询日志，把执行时间超过设定值（默认为10s）的 SQL 记录到日志中。这项功能需要手动开启，但是开启后会造成一定的性能损耗。
例：
mysql > show VARIABLES like ‘%slow_query_log%’;

Variable_name	Value
slow_query_log	ON
slow_query_log_file	db-server-3-slow.log

字段解释：

slow_query_log  开启状态
slow_query_log_file  慢日志保存位置

相关操作：
开启慢日志语句:
mysql> set global slow_query_log=1;
设置时间语句：
mysql> set global long_query_time=3;
查看时间语句：
mysql>show variables like ‘long_query_time’;

其他参数:

log_output

参数是指定日志的存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

log-queries-not-using-indexes

未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

log_slow_admin_statements

表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

慢查询的分析工具：mysqldumpslow
注：windows 默认没有安装此工具

MySQL 提供了慢日志分析工具 mysqldumpslow。

-s 表示按照何种方式排序；
    c: 访问计数
    l: 锁定时间
    r: 返回记录
    t: 查询时间
    al:平均锁定时间
    ar:平均返回记录数
    at:平均查询时间
-t 是top n的意思，即为返回前面多少条的数据；
-g 后边可以写一个正则匹配模式，大小写不敏感的；

命令示例

得到返回记录集最多的 10 个 SQL：
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的 10 个 SQL：
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现刷屏的情况：
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

3.explain 命令

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。
例：
mysql>explain select * from user

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	ALL					10

字段解释：
id：这是SELECT的查询序列号

select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二个或后面的SELECT语句

DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT：UNION的结果。

SUBQUERY：子查询中的第一个SELECT

DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询

DERIVED：导出表的SELECT(FROM子句的子查询)

table：显示这一行的数据是关于哪张表的

type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

possible_keys：列指出MySQL能使用哪个索引在该表中找到行

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示使用哪个列或常数与key一起从表中选择行。

rows：显示MySQL认为它执行查询时必须检查的行数。

Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行

Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候

Using temporary
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题

参考链接：
show processlist 命令详解
MySQL 的 SQL 执行分析
mysql explain用法和结果的含义