MySQL day6(慢查询日志， explain)

开启慢日志查询

查询是否已开启慢查询日志

mysql> show variables like 'slow%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/091b27e91d40-slow.log | +---------------------+--------------------------------------+ 3 rows in set (0.01 sec) slow_query_log: 是否开启慢查询日志 slow_query_log_file: 日志路径

开启慢查询日志

-- 方法一(临时): set global slow_query_log = on; -- 开启慢查询日志 -- 设置日志路径 set global slow_query_log_file = "/var/lib/mysql/slowsql.log"; -- 查看慢日志时间 show variables like '%long%'; +----------------------------------------------------------+-----------+ | Variable_name | Value | +----------------------------------------------------------+-----------+ | long_query_time | 10.000000 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_transactions_history_long_size | 10000 | | performance_schema_events_waits_history_long_size | 10000 | +----------------------------------------------------------+-----------+ 5 rows in set (0.00 sec) -- 设置时间 mysql> set long_query_time = 2; Query OK, 0 rows affected (0.00 sec) -- 方法二(永久): 1, 打开配置文件 2, 添加如下配置 [mysqld] slow_query_log = 1 long_query_time = 0.1 slow_query_log_file =/var/lib/mysql/slowsql.log 3, 重启服务

查看性能详情是否开启

-- 查看是否开启 mysql> show variables like '%profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | OFF | | profiling_history_size | 15 | +------------------------+-------+ 3 rows in set (0.00 sec) # 开启 mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like '%profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | ON | | profiling_history_size | 15 | +------------------------+-------+ 3 rows in set (0.01 sec) # 查看性能记录 mysql> show profiles; +----------+------------+-----------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------+ | 1 | 0.00194350 | show variables like '%profiling%' | +----------+------------+-----------------------------------+ 1 row in set, 1 warning (0.00 sec) # 查看详情 mysql> show profile for query 1; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000081 | | checking permissions | 0.000013 | | Opening tables | 0.000017 | | init | 0.000056 | | System lock | 0.000007 | | optimizing | 0.000004 | | optimizing | 0.000003 | | statistics | 0.000015 | | preparing | 0.000016 | | statistics | 0.000007 | | preparing | 0.000005 | | executing | 0.000021 | | Sending data | 0.000007 | | executing | 0.000003 | | Sending data | 0.001501 | | end | 0.000018 | | query end | 0.000009 | | closing tables | 0.000004 | | removing tmp table | 0.000009 | | closing tables | 0.000008 | | freeing items | 0.000119 | | cleaning up | 0.000023 | +----------------------+----------+ 22 rows in set, 1 warning (0.00 sec)

性能分析 explain

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）

-- 语法 explain SQL语句 eg. mysql> explain select count(0)from user where name ='任利'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref possible_keys: ix_name key: ix_name key_len: 152 ref: const rows: 514 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)

字段描述

字段名描述补充id选择标识符select_type查询的类型table输出结果集的表partitions匹配的分区type表示表的连接类型possible_keys表示查询时，可能使用的索引key表示实际使用的索引key_len索引字段的长度ref列与索引的比较rows扫描出的行数(估算的行数)

| filtered | 按表条件过滤的行百分比 | | | Extra | 执行情况的描述和说明 | |

id:

​ id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

# 简单子查询 mysql> explain select (select 1 from user limit 1) from user; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ | 1 | PRIMARY | user | NULL | index | NULL | ix_gender | 1 | NULL | 16326775 | 100.00 | Using index | | 2 | SUBQUERY | user | NULL | index | NULL | ix_gender | 1 | NULL | 16326775 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ # from 子查询 mysql> explain select id from (select id from user) as der; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | user | NULL | index | NULL | ix_gender | 1 | NULL | 16326775 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) # union 查询 mysql> explain select 1 union all select 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) #union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL。

select_type:

select_type 表示对应行是是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种。

simple: 简单查询 primary: 复杂查询中最外层的 select subquery: 包含在select中的子查询(不在from子句中) derived: 包含在from子句中的子查询, 虚拟表 union: union result:

table 列:

表示正在访问哪一个表

type:

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

NULL: mysql能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表 const, system: mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。 eq_ref： primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。 ref： 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。 ref_or_null： 类似ref，但是可以搜索值为NULL的行 index_merge： 表示使用了索引合并的优化方法， 即两个索引都使用到了。 range： 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。 index： 和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些 ALL： 即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

possible_keys 列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

row 列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

extra

这一列展示的是额外信息。常见的重要值如下：

distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了 Using index: 这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。 Using where： mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃 Using temporary： mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。 Using filesort： mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。