慢查询
什么是慢查询
慢查询，就是耗时较长、速度较慢的查询。
mysql提供了API来支持用户查看哪些查询比较慢。
mysql慢查询相关参数说明
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录
查看慢查询相关参数
通过mysql自带的客户端设置：cmd进入bin目录下，运行 mysql -uroot -p命令登入后：
mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/localhost-slow.log |
+---------------------------+----------------------------------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
设置相关参数、开启慢查询日志记录
方法一：全局变量设置
通过mysql自带的客户端设置：cmd进入bin目录下，运行 mysql -uroot -p命令登入后：
--将 slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON';
--设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';
--查询超过1秒就记录
mysql> set global long_query_time=1;
方法二：配置文件设置
Linux下编辑etc/文件夹下的my.cnf，Windows下编辑my.ini
5.5以上版本，在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
5.0、5.1等版本配置如下选项
log-slow-queries="mysql_slow_query.log"
重启MySQL服务
service mysqld restart
查看设置后的参数
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
测试
1.执行一条慢查询SQL语句
mysql> select sleep(2);
2.查看是否生成慢查询日志
ls /usr/local/mysql/data/slow.log
如果日志存在，MySQL开启慢查询设置成功！
查看慢查询语句
可以使用MySQL自带的mysqldumpslow命令，其用法如下
排序参数-s，取值有 c(按照记录次数排序)、t(按照查询的发生时间排序)、l(按照耗时排序)、r(按照结果集数目排序)，ac、at、al、ar表示相应的倒序。
取前多少个-t，即 top n
正则匹配-g
分析慢查询原因：
Explain或者DESC命令
DESC命令的使用很简单，例：sql>desc select * from easy_sys_user;
Explain 待续……
优化慢查询：
1、新建索引
为了减少磁盘IO消耗，MySQL的索引存储采用B+树结构，在B+树中，如果数据项是复合结构，则最左边的是第一比较因子，如果没有给出第一比较因子的值，则会返回所有数据，即索引没有起到作用。另如果遇到的比较因子是一个范围时，它后面的所有比较因子将都用不上。
根据B+树的这种特性，要求优化慢查询建立联合索引的时候坚持最左匹配原则，即查询条件是等值查询的字段作为联合索引的第一个字段。
Explain 待续……
Explain
什么是explain
explain是mysql提供给开发人员的工具，它向开发人员解释了mysql如何执行的SQL语句，通过这些解释，开发人员或许可以明白查询的效率慢的原因，可以对 SQL 语句做相应的优化，提高执行效率。
如何使用explain
调用 Explain，只需要在 SQL 语句前添加 explain 关键字即可。
一般情况下，添加 explain 关键字后，认为 MySQL 不会执行查询，但是如果在 from 子句中包含子查询，那么 MySQL 实际上会执行子查询，将其子查询的结果放在一个临时表中，然后完成外层查询优化。
Explain提供了哪些信息
调用 Explain 后，MySQL 会返回一行或者多行记录，通过这些记录就可以知道 SQL 语句的执行情况了。
每行记录都包含了以下几个字段
- id：执行编号，标识 select 所属的行。一个sql语句在数据库上可能需要执行多个查询，比如in。explain给每个查询都编号，编号越大，查询越先执行
- select_type：select 查询的类型
- table：查询的是哪个表
- partitions：匹配的分区
- type：关联类型，或者访问类型
- possible_keys：该查询可以选用的索引
- key：该查询选用的索引
- key_len：索引中使用的字节数
- ref：显示上述表的连接匹配条件，即哪些列或常量被用于查询索引列上的值
- rows：估计为了找到所需行而要读取的行数
- filtered：按表条件过滤的行的百分比
- Extra：额外的信息
Explain字段详解
- id
该列总是包含一个编号，标识 select 所属的行。如果语句当中没有子查询或联合查询，那么只会有唯一的 select，于是每一行在这个列中都将显示一个 1。否则，内层的 select 语句一般会顺序编号，对应于原始语句中的位置。
id 值越大，越先执行。
- select_type
该列指明了查询的类型，以下为常见的取值
SIMPLLE：简单查询，该查询不包含 UNION 或子查询
PRIMARY：如果查询包含 UNION 或子查询，则最外层的查询被标识为 PRIMARY
UNION：表示此查询是 UNION 中的第二个或者随后的查询
DEPENDENT：UNION 满足 UNION 中的第二个或者随后的查询，其次取决于外面的查询
UNION RESULT：UNION 的结果
SUBQUERY：子查询中的第一个 select 语句
DEPENDENT SUBQUERY：子查询中的 第一个 select，同时取决于外面的查询
DERIVED：派生表 select，包含在 from 字句的子查询中的查询
UNCACHEABLE SUBQUERY：满足是子查询中的第一个 select 语句，同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中
UNCACHEABLE UNION：满足此查询是 UNION 中的第二个或者随后的查询，同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中
-
table
该列显示了对应行正在访问哪个表，或者该表的别名。 -
partitions
记录将与查询匹配的分区，非分区表的值为NULL。 -
type
该列称为关联类型或者访问类型，它指明了 MySQL 决定如何查找表中符合条件的行，同时为判断查询是否高效提供了重要的依据。
以下为常见的取值
ALL：全表扫描，这个类型是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型，因为这样的查询，在数据量最大的情况下，对数据库的性能是巨大的灾难。
index：全索引扫描，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型是扫描全部的索引，主要优点是避免了排序，但是开销仍然非常大。如果在 Extra 列看到 Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要少很多。
range：范围扫描，就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。这个类型通常出现在 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() 的操作中，key 列显示使用了哪个索引，当 type 为该值时，则输出的 ref 列为 NULL，并且 key_len 列是此次查询中使用到的索引最长的那个。
ref：一种索引访问，也称索引查找，它返回所有匹配某个单个值的行。此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询。
eq_ref：使用这种索引查找，最多只返回一条符合条件的记录。在使用唯一性索引或主键查找时会出现该值，非常高效。
const、system：该表至多有一个匹配行，在查询开始时读取，或者该表是系统表，只有一行匹配。其中 const 用于在和 primary key 或 unique 索引中有固定值比较的情形。
NULL：在执行阶段不需要访问表。
该列取不同值的执行效率依次是
ALL < index < range < ref < eq_ref < const < system < NULL
一般来说，至少要保证查询达到 range 级别，最好能达到 ref 级别。
-
possible_keys
该列显示了查询可以选用哪些索引，但是列出来的索引，可能对于后续优化过程是没有用的。 -
key
该列显示了查询选用了哪个索引，如果该索引没有出现在 possible_keys 列中，那么选用它可能出于另外的原因，比如，它可能选择了一个覆盖索引。 -
key_len
该列显示了在索引里使用的字节数，当 key 列的值为 NULL 时，则该列也是 NULL。如果正在使用的只是索引里的某些列，那么可以通过用该列的值算出来具体是哪些列。 -
ref
该列显示了哪些字段或者常量被用来和 key 配合从表中查询记录出来。 -
rows
该列显示了估计要找到所需的行而要读取的行数，这个值是个估计值，原则上值越小越好。
filtered
该列表示根据条件过滤的表行的估计百分比，和 rows 相乘，表示和查询计划里前一个表关联的行数。
Extra
该列显示了有关 MySQL 如何解析查询的其它信息。
以下为常见的取值
Using index：使用覆盖索引，表示查询索引就可查到所需数据，不用扫描表数据文件，往往说明性能不错。
Using Where：在存储引擎检索行后再进行过滤，使用了 where 从句来限制哪些行将与下一张表匹配或者是返回给用户。
Using temporary：在查询结果排序时会使用一个临时表，一般出现于排序、分组和多表 join 的情况，查询效率不高，建议优化。
Using filesort：对结果使用一个外部索引排序，而不是按索引次序从表里读取行，一般有出现该值，都建议优化去掉，因为这样的查询 CPU 资源消耗大。
扫一扫
5326
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
