MySQL慢查询

01.MySQL慢查询

1、MySQL慢查询

  • 什么是 MySQL 慢查询

    • MySQL 的慢查询日志,用来记录在 MySQL 中响应时间超过阀值的语句

    • 具体指运行时间超过 long_query_time 值的SQL,则会被记录到慢查询日志中

    • long_query_time 的默认值为10,意思是运行10秒以上(不含10秒)的语句

  • 慢查询日志作用

    • 作用是,帮助我们发现那些执行时间特别长的SQL查询,并且有针对性地进行优化

    • 当我们运行变慢的时候,检查一下慢查询日志,找到那些慢查询,对解决问题很有帮助

2、开启慢查询

-- 查看慢查询配置
show variables like '%slow_query_log%';
-- 开启慢查询日志
set global slow_query_log='ON';
-- 查看long_query_time阈值
show variables like '%long_query_time%';
-- 修改long_query_time阈值
set global long_query_time = 1;
-- 查看long_query_time阈值
show global variables like '%long_query_time%';

  • 查询sql慢查询
-- 查看已经被记录的慢查询数量
MySQL> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
-- 模拟慢查询
MySQL> select sleep(4);
MySQL> select sleep(6);
-- 查看慢查询日志位置
MySQL> show variables like '%slow_query_log%';

1
2
3
4
5
6
7

  • 查看慢查询日志
    • User@Host:表示用户和慢查询的连接地址(root 用户,localhost地址,Id为 9)
    • Query_time:表示 SQL 查询的耗时,单位为秒
      • Lock_time:表示获取锁的时间,单位为秒
      • Rows_sent:表示发送给客户端的行数
      • Rows_examined:表示服务器层检查的行数
    • SET timestamp:表示慢 SQL 记录时的时间戳
    • select sleep(4);:最后一行表示慢查询 SQL 语句
tom@tomdeMBP ~ % sudo more /usr/local/MySQL-8.0.29-macos12-arm64/data/tomdeMBP-slow.log
Time                 Id Command    Argument
# Time: 2024-03-07T09:12:15.910446Z
# User@Host: root[root]@localhost []  Id:     8
# Query_time: 4.005642  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1709802731;
select sleep(4);

1
2
3
4
5
6
7

3、查找那些语句慢

-- 比如,得到返回记录集最多的10个SQL
MySQLdumpslow -s r -t 10 /database/MySQL/MySQL06_slow.log

-- 得到访问次数最多的10个SQL
MySQLdumpslow -s c -t 10 /database/MySQL/MySQL06_slow.log

-- 得到按照时间排序的前10条里面含有左连接的查询语句
MySQLdumpslow -s t -t 10 -g “left join” /database/MySQL/MySQL06_slow.log

-- 另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况
MySQLdumpslow -s r -t 20 /MySQLdata/MySQL/MySQL06-slow.log | more

02.explain

原文链接(opens new window)

0、explain基础

  • 在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记

  • 执行查询时,会返回执行计划的信息,而不是执行这条SQL

  • 如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中

1、创建表
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 插入测试数据
INSERT INTO film (name) VALUES ('MovieName');
INSERT INTO actor (id, name, update_time) VALUES (1, 'ActorName', '2024-03-08 12:00:00');
INSERT INTO film_actor (id, film_id, actor_id, remark) VALUES (1, 1, 1, 'SomeRemark');

2、explain字段注解
MySQL> explain select * from film where id = 'abc' \G;
*************************** 1. row ***************************
           id: 1    -- SELECT语句的查询序列号,每个SELECT语句都有一个唯一的序列号,从1开始递增
  select_type: SIMPLE  -- 显示了查询的类型
                    -- SIMPLE: 简单查询,不包含子查询或UNION操作
                    -- PRIMARY: 包含子查询或UNION操作的查询的最外层查询
                    -- SUBQUERY: 子查询中的第一个查询
                    -- DERIVED: 子查询中的派生表
                    -- UNION: UNION操作的第二个或后面的查询
                    -- UNION RESULT: 从union临时表检索结果的select
        table: NULL -- 显示了查询的表名
   partitions: NULL -- 显示了查询的分区情况,如果表没有被分区,则为NULL
   
         type: NULL -- 显示了查询使用的访问方式
                    -- NULL: 
                    	-- MySQL无法确定使用哪种访问方式,通常因为没有使用索引或者使用了不适合的索引
                    	-- 或者MySQL能够在优化阶段分解查询语句,在执行阶段`用不着再访问表或索引`
                    -- const: 索引匹配唯一数据,system可以匹配多条数据
                    -- eq_ref: 多表联合查询时,使用了唯一索引或主键索引
                    -- ref: 多表联合查询时,使用非唯一索引
                    -- range: 使用索引范围查询的访问方式
                    -- index: 查询操作只需要访问索引,而不需要访问数据行
                    -- all: 使用聚集索引,所以`type: ALL`实际上是通过扫描主键索引来完成的
                    
possible_keys: NULL -- 显示了MySQL可以使用哪些索引来执行查询,多个索引之间用逗号分隔

          key: NULL -- 显示了MySQL实际使用的索引,如果为NULL则表示没有使用索引
          
      key_len: NULL -- 显示了MySQL使用的索引长度,通常是指索引字段的字节数
          ref: NULL -- 显示了MySQL使用的索引列与查询条件之间的关系(例:film.id)
         rows: NULL -- MySQL估计要读取并检测的行数,注意这个不是结果集里的行数
     filtered: NULL -- 显示了MySQL估算出的结果集的行数占总表行数的比例
     
        Extra: no matching row in const table -- 显示了MySQL的一些额外信息
                    -- Using index: 查询的列被索引覆盖,是性能高的表现
                    -- Using index condition: where 条件中使用到了部分索引
                    
                    -- Using where: 查询的列未被索引覆盖,where筛选条件非索引的前导列
                    -- Using where; Using index: 查询被索引覆盖,where不是索引前导列,无法使用索引
                    -- Using temporary: MySQL需要使用临时表来完成查询,通常是排序或分组操作(可以索引优化)
                    -- Using filesort: 一般指order by 操作字段没有索引导致的(可以索引优化)
                    -- Using join buffer: MySQL使用了连接缓存来优化连接操作
                    -- Impossible where: 查询条件不可能存在,导致MySQL没有扫描任何行
                    -- Select tables optimized away: 查询优化器优化了查询,表没有被访问
                    -- No matching min/max row: MySQL无法通过索引优化MIN/MAX操作
                    -- NULL: 味着查询中没有特殊的情况需要显示
                        -- 查询的列未被索引覆盖,并且where筛选条件是索引的前导列
                        -- 意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表”来实现

#3、show warnings
MySQL> show warnings \G;
-- 1.row 截断错误的DOUBLE值:'abc'
*************************** 1.row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'abc'

-- 2.row 这个告警可以忽略,可能是MySQL不同版本导致的
*************************** 2.row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select NULL AS `id`,NULL AS `name` from `mall`.`film` where (NULL = 0)
2 rows in set (0.01 sec)

1、id 列

  • id列是MySQL EXPLAIN结果中的一列,用于标识查询计划中各个操作的唯一编号,从1开始递增
  • id列本身并不会对查询的执行产生影响,它仅仅是EXPLAIN结果中的一个信息列
  • id列还可以帮助我们了解查询计划中各个操作的执行顺序和关系,更好地理解查询的执行过程和性能问题
  • 注意:总的来说,id列在查询优化和性能分析方面具有重要的作用

2、select_type列

  • select_type 表示对应行是简单还是复杂的查询
select_type: SIMPLE  -- 显示了查询的类型
-- SIMPLE: 简单查询,不包含子查询或UNION操作
-- PRIMARY: 包含子查询或UNION操作的查询的最外层查询(主查询)
-- SUBQUERY: SUBQUERY表示子查询,即嵌套在主查询中的一个子查询语句
-- DERIVED: 子查询中的派生表
-- UNION: UNION操作的第二个或后面的查询
-- UNION RESULT: 从union临时表检索结果的select

1、SIMPLE
MySQL> explain select * from film where id = 2 \G;
           id: 1
  select_type: SIMPLE -- 简单查询,不包含子查询或UNION操作
        table: film
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

2、PRIMARY 和SUBQUERY
MySQL> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY  -- 包含子查询或UNION操作的查询的最外层查询
        table: film
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY -- SUBQUERY表示子查询,即嵌套在主查询中的一个子查询语句
        table: actor
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

#3、PRIMARY和UNION
  • 这个查询语句是一个UNION ALL操作,用于将两个SELECT语句的结果合并在一起
MySQL> explain select 1 union all select 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY  -- 包含子查询或UNION操作的查询的最外层查询(主查询)
*************************** 2. row ***************************
           id: 2
  select_type: UNION    -- UNION操作的第二个或后面的查询

3、type列

  • 这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围
  • 依次从最优到最差分别为:
    • system > const > eq_ref > ref > range > index > ALL 一般来说,得保证查询达到range级别,最好达到ref
type: NULL -- 显示了查询使用的访问方式
-- NULL: MySQL无法确定使用哪种访问方式,通常因为没有使用索引或者使用了不适合的索引
-- const: 当MySQL能够通过索引或常量关系只匹配一行数据时使用的访问方式
-- eq_ref: 使用了唯一索引或主键索引,只匹配一行数据的访问方式
-- ref: 使用非唯一索引,匹配多行数据的访问方式
-- range: 使用索引范围查询的访问方式
-- index: 使用索引扫描的访问方式
-- all: 全表扫描的访问方式,通常是最慢的一种访问方式

1、NULL
  • MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引

  • 例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表

MySQL>  explain select min(id) from film \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
         type: NULL
         -- MySQL的查询优化器会寻找到主键索引的第一个条目,这个条目的id是所有条目中最小的
         -- 因此,MySQL并不需要扫描整个表或者整个索引,而是直接找到了结果

SELECT id FROM table1 WHERE status = 1;
-- 如果table1表中有一个名为status的索引,并且该索引包含了id列
-- MySQL就可以使用该索引来优化查询,不需要再回表获取数据

2、const, system

const类型通常用于对primary key或者unique key的所有列与常数比较的情况下

  • system:表只有一行,这是最好的可能情况,查询性能最高
  • const:表中有一个或多个具有相同值的索引
MySQL> explain select * from film where id = 2 \G;
           id: 1
  select_type: SIMPLE -- 简单查询,不包含子查询或UNION操作
        table: film
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

3、eq_ref

primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录

  • 在连接操作中,MySQL在查询时对于每个连接的行组合,都可以从表中使用唯一索引或主键查找到一行

  • 这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种 type

MySQL> explain select * from film_actor left join film on film_actor.film_id = film.id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: eq_ref  -- primary key 或 unique key 索引的所有部分被连接使用
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: mall.film_actor.film_id
         rows: 1
     filtered: 100.0


 

4、ref
  • 这种查询类型可以返回多行,它使用普通索引或者唯一索引的一部分来查找行
MySQL> explain select * from film where name = "film1" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ref  -- 使用普通索引或者唯一性索引的部分前缀
possible_keys: idx_name
          key: idx_name
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

  • 关联表查询,idx_film_actor_id是film_id和actor_id的联合索引

  • KEY `idx_film_actor_id` (`film_id`,`actor_id`)
    -- 这里使用到了film_actor的左边前缀film_id部分
    

MySQL> explain select film_id from film left join film_actor on film.id = film_actor.film_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name
      key_len: 33
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref  -- 这里使用到了film_actor的左边前缀film_id部分
possible_keys: idx_film_actor_id
          key: idx_film_actor_id
      key_len: 4
          ref: mall.film.id
         rows: 1
     filtered: 100.00
        Extra: Using index


 

5、range
  • 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中(使用一个索引来检索给定范围的行)
MySQL> explain select * from actor where id > 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

6、index
  • 查询操作只需要访问索引,而不需要访问数据行
  • 这里的film表中只有两个字段 id:主键索引 name:普通索引
  • 所以这里直接从索引中就可以获取,无需访问主键中的数据行
MySQL> explain select * from film \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index  -- index是从索引中读取的,而不需要访问数据行
possible_keys: NULL
          key: idx_name
      key_len: 33
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index

7、ALL
  • MySQL需要获取表中的所有列,因此无法只查询索引就能获取,还需要访问主键索引叶子节点的行数据

  • ALL确实表示MySQL需要读取表中的所有行,但这并不一定意味着MySQL需要进行全表扫描

  • 对于MyISAM存储引擎,由于它使用的是非聚集索引,所以type: ALL确实意味着全表扫描

  • InnoDB存储引擎,使用聚集索引,所以type: ALL实际上是通过扫描主键索引来完成的

MySQL> explain select * from actor \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL  -- 实际上是通过扫描主键索引来完成的
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL

4、possible_keys列

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

  • explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况

  • 这种情况是因为表中数据不多,MySQL认为索引对此查询帮助不大,选择了全表查询

  • 如果该列是NULL,可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能

5、key列

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

  • 如果没有使用索引,则该列是 NULL

  • 如果想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index

6、Extra列

1、Using index
  • 查询的列被索引覆盖,并且where筛选条件是索引的前导列,是性能高的表现

  • 一般是使用了覆盖索引(索引包含了所有查询的字段)

  • 对于innodb来说,如果是辅助索引性能会有不少提高

MySQL> explain select film_id from film_actor where film_id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_film_actor_id
          key: idx_film_actor_id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index  -- 索引包含了所有查询的字段

2、Using where
  • 查询的列未被索引覆盖,where筛选条件非索引的前导列
MySQL> explain select * from actor where name = 'a' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where  -- name列没有创建索引,无法匹配索引查找

3、Using where Using index
  • 查询被索引覆盖,where不是索引前导列,无法使用索引
MySQL> explain select film_id from film_actor where actor_id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: index
possible_keys: idx_film_actor_id
          key: idx_film_actor_id
      key_len: 8
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index  -- where不是索引前导列,`无法使用索引`

4、NULL
  • 查询的列未被索引覆盖,并且where筛选条件是索引的前导列
  • 意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表”来实现
MySQL> explain select * from film_actor where film_id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_film_actor_id
          key: idx_film_actor_id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL  -- 用到了最左前缀

5、Using index condition
  • 与Using where类似,查询的列不完全被索引覆盖,where条件中是一个前导列的范围
MySQL> explain select * from film_actor where film_id > 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: range
possible_keys: idx_film_actor_id
          key: idx_film_actor_id
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition  -- 查询的列不完全被索引覆盖,where条件中是一个前导列的范围

6、Using temporary
  • MySQL需要创建一张临时表来处理查询
  • 出现这种情况一般是要进行优化的,首先是想到用索引来优化
  • 1)actor.name没有索引,此时创建了张临时表来distinct
MySQL> explain select distinct name from actor \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using temporary  -- actor.name没有索引,此时创建了张临时表来distinct

  • film.name建立了idx_name索引,此时查询时extra是using index,没有用临时表
MySQL> explain select distinct name from film \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: idx_name
          key: idx_name
      key_len: 33
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index -- film.name建立了idx_name索引,查询时extra是using index,没有用临时表

7、Using filesort
  • MySQL 会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。

  • 此时MySQL会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息。

  • 这种情况下一般也是要考虑使用索引来优化的。

  • 1)actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录。
MySQL>  explain select * from actor order by name \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00  -- actor.name未创建索引,会浏览actor整个表

  • 2)film.name建立了idx_name索引,此时查询时extra是using index
MySQL> explain select * from film order by name \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name
      key_len: 33
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index -- film.name建立了idx_name索引,此时查询时extra是using index
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

不做大哥好多年xw

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值