MySQL慢查询日志和explain执行计划

慢查询日志相关参数:

• slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。
• log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
• slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
• long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
• log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
• log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需<br>要能够获得更高的系统性能，那么建议优先记录到文件。

#是否开启慢查询日志
show variables like '%slow_query_log%';
set global slow_query_log=1;
#记录到慢查询日志的时间阀
show variables like 'long_query_time';
set global long_query_time=1;
#指定日志的存储方式
show variables like '%log_output%';
set global log_output='FILE,TABLE';
#未使用索引的查询也被记录到慢查询日志中
show variables like 'log_queries_not_using_indexes';
set global log_queries_not_using_indexes=1;
#查询有多少条慢查询记录
show global status like '%slow_queries%';
#是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
show variables like '%log_slow_admin_statements%';

 

 

explain

用法：explain +查询语句。

id：查询语句的序列号，上面图片中只有一个select 语句，所以只会显示一个序列号。如果有嵌套查询，如下

select_type：表示查询类型，有以下几种

• simple:简单的 select （没有使用 union或子查询）
• primary:最外层的 select。
• union:第二层，在select 之后使用了 union。
• dependent union:union 语句中的第二个select，依赖于外部子查询
• subquery:子查询中的第一个 select
• dependent subquery:子查询中的第一个 subquery依赖于外部的子查询
• derived:派生表 select（from子句中的子查询）

table：查询的表、结果集

type：全称为"join type"，意为连接类型。通俗的讲就是mysql查找引擎找到满足SQL条件的数据的方式。其值为：

• system：系统表，表中只有一行数据
• const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
• eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
• ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取
• fulltext：进行全文索引检索。
• ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
• index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并，再读取表数据。
• unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束。
• index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
• rang：索引范围扫描。
• index：全索引扫描。
• all：全表扫描。

　　(性能从上到下依次降低。)

possible_keys：可能用到的索引

key：使用的索引

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

rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。

Extra：额外的信息

• Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
• Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
• range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
• Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
• Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
• Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
• Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
• Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
• Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

 

 

// 查看进程

SHOW PROCESSLIST;

// 查看是否锁表

SHOW OPEN TABLES WHERE In_use > 0;

// 查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

// 查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 

 

 

查询数据库中的存储过程和函数

select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE'   //存储过程

select * from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' and name='xx'

select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION'   //函数

show procedure status; //存储过程
show function status;     //函数

查看存储过程或函数的创建代码

show create procedure proc_name;
show create function func_name;

查看视图
SELECT * from information_schema.VIEWS   //视图
SELECT * from information_schema.TABLES   //表

查看触发器

SHOW TRIGGERS [FROM db_name] [LIKE expr]
SELECT * FROM triggers T WHERE trigger_name=”mytrigger”