mysql 满查询管理平台_MySQL 之 慢查询优化及慢日志管理

1、查询优化神器 —— explain

​使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析查询语句或是表结构的性能瓶颈。

(1)、通过EXPLAIN，可以分析出以下结果：

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

(2)、使用方式：

EXPLAIN + SQL语句

(3)、执行计划包含的信息

+----+-------------+-------------+------+---------------+------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------------+------+---------------+------+---------+------+------+-------+

(4)、执行计划各字段的含义

<1>、id： select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在。id如果相同，可以认为是一组，从上往下执行。

<2>、select_type：分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

SIMPLE ：简单的select查询，查询中不包含子查询或者UNION

PRIMARY ：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY ：在SELECT或WHERE列表中包含了子查询

DERIVED ：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中

UNION ：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT ：从UNION表获取结果的SELECT

<3>、table：指的就是当前执行的表

<4>、type：显示的是查询使用了哪种类型

system：表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计

const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

index：Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的)

id是主键，所以存在主键索引

all：Full Table Scan 将遍历全表以找到匹配的行

从最好到最差依次是：system > const > eq_ref > ref > range > index > all。一般来说，得保证查询至少达到range级别，最好能达到ref。

<5>、possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

<6>、key：实际使用的索引，如果为NULL，则没有使用索引。(可能原因包括没有建立索引或索引失效)。查询中若使用了覆盖索引(select 后要查询的字段刚好和创建的索引字段完全相同)，则该索引仅出现在key列表中。

<7>、key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

<8>、ref：显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

<9>、rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好。

<10>、包含不适合在其他列中显式但十分重要的额外信息。

​关于 explain 命令的具体用法和字段含义可以参考官网explain-output。

需要强调 rows 是核心指标，绝大部分 rows 小的语句执行一定很快(也有例外)。所以优化语句基本上都是在优化rows。

执行计划：让mysql预估执行操作(一般正确)

all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

id,email

慢：

select * from test where name='cai'

explain select * from test where name='cai'

type: ALL(全表扫描)

select * from test limit 1;

快：

select * from test where email='cai@'

type: const(走索引)

2、慢查询优化的基本步骤

0.先运行看看是否真的很慢，注意设置 SQL_NO_CACHE

1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2.explain查看执行计划，是否与1预期一致(从锁定记录较少的表开始查询)

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果，不符合预期继续从0分析

3、慢日志管理

​MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

# 慢日志

- 执行时间 > 10

- 未命中索引

- 日志文件路径

# 配置：

- 内存

show variables like '%query%';

show variables like '%queries%';

set global 变量名 = 值

- 配置文件

mysqld --defaults-file='E:\cai\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'

# my.conf内容：

slow_query_log = ON

slow_query_log_file = D:/....

# 注意：修改配置文件之后，需要重启服务

4、MySQL日志管理

(1)、MySQL日志

#========================================================

错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息

二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作

查询日志: 记录查询的信息

慢查询日志: 记录执行时间超过指定时间的操作

中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放

通用日志： 审计哪个账号、在哪个时段、做了哪些事件

事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等

#========================================================

(2)、MySQL日志配置

## 一、bin-log

1. 启用

# vim /etc/my.cnf

[mysqld]

log-bin[=dir\[filename]]

# service mysqld restart

2. 暂停

//仅当前会话

SET SQL_LOG_BIN=0;

SET SQL_LOG_BIN=1;

3. 查看

查看全部：

# mysqlbinlog mysql.000002

按时间：

# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"

# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"

# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54"

按字节数：

# mysqlbinlog mysql.000002 --start-position=260

# mysqlbinlog mysql.000002 --stop-position=260

# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

4. 截断bin-log(产生新的bin-log文件)

a. 重启mysql服务器

b. # mysql -uroot -p123 -e 'flush logs'

5. 删除bin-log文件

# mysql -uroot -p123 -e 'reset master'

## 二、查询日志

启用通用查询日志

# vim /etc/my.cnf

[mysqld]

log[=dir\[filename]]

# service mysqld restart

## 三、慢查询日志

启用慢查询日志

# vim /etc/my.cnf

[mysqld]

log-slow-queries[=dir\[filename]]

long_query_time=n

# service mysqld restart

MySQL 5.6:

slow-query-log=1

slow-query-log-file=slow.log

long_query_time=3 单位为秒

查看慢查询日志

测试:BENCHMARK(count,expr)

SELECT BENCHMARK(50000000,2*3);