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

我会带着你远行 2023-01-12 04:28 172阅读 0赞

1、查询优化神器 —— explain

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

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

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

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

(2)、使用方式：

EXPLAIN + SQL语句

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

\+----+-------------+-------------+------+---------------+------+---------+------+------+-------+

\+----+-------------+-------------+------+---------------+------+---------+------+------+-------+

(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内容：