mysql常见慢sql,MySQL中慢SQL的查询及原因分析

准备数据

查看系统变量

SHOW VARIABLES LIKE 'slow_query%';

slow_query_log：慢sql日志开启状态

slow_query_log_file：慢sql日志存放位置

SHOW VARIABLES LIKE 'long_query_time';

long_query_time：执行超过多少秒才记录日志

修改系统变量

SET GLOBAL slow_query_log = ON;

SET GLOBAL long_query_time=0.001;

执行修改语句后如果再次查询没有生效，重新打开个窗口再次查询就可以了。

测试结果

执行查询语句

select * from student;

slow.log中的记录

select * from student;

# Time: 2020-08-04T06:09:07.716447Z

# User@Host: root[root] @ localhost [::1] Id: 13

# Query_time: 0.001787 Lock_time: 0.000417 Rows_sent: 2 Rows_examined: 1024

SET timestamp=1596521347;

再次测试更新语句

UPDATE student SET `name` = '李四' WHERE id = 999;

> Affected rows: 1

> 时间: 0.004s

发现也被记录了

UPDATE student SET `name` = '李四' WHERE id = 999;

# Time: 2020-08-04T06:25:11.808408Z

# User@Host: root[root] @ localhost [::1] Id: 13

# Query_time: 0.001236 Lock_time: 0.000092 Rows_sent: 356 Rows_examined: 712

SET timestamp=1596522311;

Time：慢sql发生时间

User@Host：客户端用户和IP

Query_time：查询时间

Lock_time：等待表锁的时间

Rows_sent：语句返回的行数

Rows_examined：语句执行期间从存储引擎扫描的行数

sql执行分析

EXPLAIN

Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。

使用方法：在查询语句前面加上 explain 运行就可以了。

EXPLAIN UPDATE student SET `name` = '李' WHERE id = 999;

EXPLAIN字段详解

列名

描述

id

在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

select_type

SELECT关键字对应的哪个查询的类型

table

表名

partitions

匹配的分区信息

type

针对单表的访问方法

possible_keys

可能用到的索引

key

实际上使用的索引

key_len

实际使用到的索引长度

ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows

预估的需要读取的记录条数

filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

Extra

一些额外的信息

select_type详解

select_type的值

解释

SIMPLE

简单查询(不使用关联查询或子查询)

PRIMARY

如果包含关联查询或者子查询

UNION

联合查询中第二个及后面的查询

DEPENDENT UNION

满足依赖外部的关联查询中第二个及以后的查询

UNION RESULT

联合查询的结果

SUBQUERY

子查询中的第一个查询

DEPENDENT SUBQUERY

子查询中的第一个查询，并且依赖外部查询

DERIVED

用到派生表的查询

MATERIALIZED

被物化的子查询

UNCACHEABLE SUBQUERY

一个子查询的结果不能被缓存，必须重新评估外层查询的每一行

type详解

查询性能从上到下依次是最好到最差

type的值

解释

system

查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表，这是最好的情况

const

基于主键或唯一索引查询，最多返回一条结果

eq_ref

表连接事基于主键或非NULL的唯一索引完成扫描

ref

基于普通索引的等值查询，或者表间等值连接

fulltext

全文检索

ref_or_null

表连接类型是ref，但进行扫描的索引列中可能包含NULL值

index_merge

利用多个索引

unique_subquery

子查询中使用唯一索引

index_subquery

子查询中使用普通索引

range

利用索引进行范围查询

index

全索引扫描

extra详解

Extra常见的值

解释

Using filesort

将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序

Using temporary

需要创建一个临时表来存储结构，通常发生对没有索引的列进行GROUP BY时

Using index

使用覆盖索引

Using where

使用where语句来处理结果

Impossible WHERE

对where子句判断的结果总是false而不能选择任何数据

Using join buffer

改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Using index condition

先条件过滤索引，再查数据

Select tables optimized away

使用某些聚合函数(比如max、min)来访问存在索引的某个字段