MySQL训练营-如何判断SQL的主要消耗阶段

慢查询分析思路

  1. 确认是锁还是执行消耗

  2. 对于执行消耗,分析执行过程

索引基础知识回顾

InnoDB 聚簇索引结构

CREATE TABLE `t1` (
    `id` int NOT NULL AUTO_INCREMENT,
    `a` int DEFAULT NULL,
    `b` int DEFAULT NULL,
    `c` int DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `a` (`a`),
    KEY `bc` (`b`,'c')
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

索引结构为B+树,只有主键索引的叶子结点带有数据,通过索引a获取数据时,只能先获取到id,回表在主键索引上回表进行查找数据行。索引bc为联合索引,非叶子结点上有同时保存了两个字段。

在索引树上能进行的操作:

  • index lookup:点查

  • index range scan:范围扫描(B+树特性)

  • table scan:全表扫描,扫描主键的叶子结点

  • index skip scan:索引跳跃扫描

索引跳跃扫描

Index Skip Scan(索引跳跃扫描)是数据库系统中一种特殊的索引扫描方式,主要用于优化查询性能,下面从适用场景、工作原理、示例、优缺点几个方面详细介绍:

适用场景

  • 复合索引:当表上存在复合索引(由多个列组成的索引),且查询语句仅引用了复合索引中的部分列,尤其是没有引用复合索引的前导列时,数据库可能会考虑使用 Index Skip Scan。
  • 前导列值唯一值较少:如果复合索引的前导列的唯一值数量相对较少,使用 Index Skip Scan 可以避免全表扫描,提高查询效率。

工作原理

  1. 确定复合索引:首先,数据库需要识别出满足条件的复合索引。例如,对于一个复合索引 (col1, col2),查询语句中可能只涉及 col2 列。
  2. 划分前导列值:数据库会对复合索引的前导列(如 col1)的所有唯一值进行枚举。
  3. 逐值扫描:针对前导列的每个唯一值,数据库会在索引中查找满足查询条件的 col2 列的值,就好像跳过了前导列的筛选过程,直接对后续列进行扫描。
  4. 合并结果:最后,将每次扫描得到的结果合并起来,作为最终的查询结果。

示例

假设有一个表 sales,其结构如下:

CREATE TABLE sales (
    region VARCHAR(50),
    product VARCHAR(50),
    amount DECIMAL(10, 2),
    INDEX idx_region_product (region, product)
);

如果执行以下查询:

SELECT product, SUM(amount)
FROM sales
GROUP BY product;

在这个查询中,没有使用复合索引 idx_region_product 的前导列 region。如果 region 列的唯一值数量较少,数据库可能会使用 Index Skip Scan 来优化查询。具体过程如下:

  • 数据库会枚举 region 列的所有唯一值,例如 'North''South''East''West'
  • 对于每个 region 值,在索引中查找满足条件的 product 列的值,并计算 amount 的总和。
  • 最后将所有 region 值对应的结果合并起来,得到最终的查询结果。

优点

  • 提高查询性能:在某些情况下,Index Skip Scan 可以避免全表扫描,减少磁盘 I/O 操作,从而提高查询效率。
  • 充分利用索引:即使查询没有使用复合索引的前导列,也可以利用复合索引进行查询,提高索引的利用率。

缺点

  • 开销较大:由于需要枚举前导列的所有唯一值,并进行多次扫描,Index Skip Scan 的开销相对较大。如果前导列的唯一值数量较多,性能可能会受到影响。
  • 优化器选择:数据库优化器需要根据具体情况判断是否使用 Index Skip Scan。如果优化器判断失误,可能会导致性能下降。

执行器单元操作演示及相应的数据结构

  1. filter:where条件过滤
  2. temporary/materialize:中间会创建临时表/物化
    • group by
    • distinct
    • sum/avg

group by存在不需要创建临时表情况:先order by在group by,即输入是有序的时候。

  1. 排序
    • qsort:快排时间复杂度Mlog(M)
    • prioriy queue:堆排序,当语句中出现where ... limit N,时间复杂度Mlog(N),当N远远小于M时,效率比快排高。当 1.没有limit语句 2. 所需内存大于sort_buffer_size 不适用该排序算法。
    • merge sort:所需内存大于sort_buffer_size时,会讲计算结果临时存放至磁盘。

索引案例

数据准备:

create table t1(id int primary key auto_increment, a int , b int, c int , index (a), index bc(b,c))engine=innodb;
insert into t1(a,b,c) values(1,1,1),(2,2,2),(3,3,3),(4,4,4); 
DROP PROCEDURE IF EXISTS insert_data;

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 12 DO
        insert into t1(a,b,c) select a,b,c from t1;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程来执行插入操作,重复插入到16384行 
CALL insert_data(); 
insert into t1(a,b,c) values(5,5,5);
insert into t1(a,b,c) select a,b,c from t1;
insert into t1(a,b,c) select a,b,c from t1; //总共65540+------+----------+
| a    | count(1) |
+------+----------+
|    1 |    16384 |
|    2 |    16384 |
|    3 |    16384 |
|    4 |    16384 |
|    5 |        4 |
+------+----------+

分析以下执行

select c from t1 where a=3 and b>1 order by b limit 300,10\G

首先在索引的选择上,选择a的原因是a = 3的条数小于b > 1。

其次会使用堆排序,因为存在limit语句。

验证:

explain analyze select c from t1 where a=3 and b>1 order by b limit 300,10\G

执行结果:

mysql> explain analyze select c from t1 where a=3 and b>1 order by b limit 300,10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit/Offset: 10/300 row(s)  (cost=1631 rows=10) (actual time=21..21 rows=10 loops=1)
    -> Sort: t1.b, limit input to 310 row(s) per chunk  (cost=1631 rows=30206) (actual time=21..21 rows=310 loops=1)
        -> Filter: (t1.b > 1)  (cost=1631 rows=30206) (actual time=4.66..19.7 rows=16384 loops=1)
            -> Index lookup on t1 using a (a=3)  (cost=1631 rows=30206) (actual time=4.66..18.9 rows=16384 loops=1)

1 row in set (0.02 sec)
  • 调大sort_buffer_size是否能提高性能?

不能,因为排序都是在内存中进行的堆排序。

  • 堆排序是否导致多次查询结果不一致

是的,堆排序是不稳定排序。示例比较简单,若b,c没有关系,是可能出现前后两次执行结果不一样情况。

join基础知识回顾

数据准备:

CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;

create table t2 like t1;
insert into t1 values(2,2,2),(4,4,4),(6,6,6),(8,8,8);
insert into t2 values(3,3,3),(6,6,6),(9,9,9);

Inner/left/right/outter join 语义

  1. Inner join
mysql> select * from t1 join t2 on t1.id = t2.id;
+----+------+------+----+------+------+
| id | a    | b    | id | a    | b    |
+----+------+------+----+------+------+
|  6 |    6 |    6 |  6 |    6 |    6 |
+----+------+------+----+------+------+
1 row in set (0.00 sec)
  1. Left join
mysql> select * from t1 left join t2 on t1.id = t2.id;
+----+------+------+------+------+------+
| id | a    | b    | id   | a    | b    |
+----+------+------+------+------+------+
|  2 |    2 |    2 | NULL | NULL | NULL |
|  4 |    4 |    4 | NULL | NULL | NULL |
|  6 |    6 |    6 |    6 |    6 |    6 |
|  8 |    8 |    8 | NULL | NULL | NULL |
+----+------+------+------+------+------+
4 rows in set (0.01 sec)
  1. Right join
mysql> select * from t1 right join t2 on t1.id = t2.id;
+------+------+------+----+------+------+
| id   | a    | b    | id | a    | b    |
+------+------+------+----+------+------+
| NULL | NULL | NULL |  3 |    3 |    3 |
|    6 |    6 |    6 |  6 |    6 |    6 |
| NULL | NULL | NULL |  9 |    9 |    9 |
+------+------+------+----+------+------+
3 rows in set (0.01 sec)

尽量少用,t1 right join t2等于t2 left jion t1

Outter Join

Mysql不支持,等同于以下写法

mysql> select * from t1 left join t2 on t1.id = t2.id union select * from t1 right join t2 on t1.id = t2.id;
+------+------+------+------+------+------+
| id   | a    | b    | id   | a    | b    |
+------+------+------+------+------+------+
|    2 |    2 |    2 | NULL | NULL | NULL |
|    4 |    4 |    4 | NULL | NULL | NULL |
|    6 |    6 |    6 |    6 |    6 |    6 |
|    8 |    8 |    8 | NULL | NULL | NULL |
| NULL | NULL | NULL |    3 |    3 |    3 |
| NULL | NULL | NULL |    9 |    9 |    9 |
+------+------+------+------+------+------+
6 rows in set (0.01 sec)

using

USING子句通常用于INNER JOINLEFT JOINRIGHT JOIN等连接操作中,其基本语法如下:

SELECT column_list
FROM table1
JOIN table2
USING (column_name);

其中,column_name是两个表中具有相同名称和数据类型的列,JOIN可以替换为其他连接类型(如LEFT JOINRIGHT JOIN等)。

mysql> select * from t1 left join t2 using(id);
+----+------+------+------+------+
| id | a    | b    | a    | b    |
+----+------+------+------+------+
|  2 |    2 |    2 | NULL | NULL |
|  4 |    4 |    4 | NULL | NULL |
|  6 |    6 |    6 |    6 |    6 |
|  8 |    8 |    8 | NULL | NULL |
+----+------+------+------+------+
4 rows in set (0.00 sec)

没有使用场景,通常业务不会写select *语句

Nested Loop join 算法流程和代价分析

t1 插入 100 行

t2 插入 1000 行

truncate table t1;
truncate table t2;
DROP PROCEDURE IF EXISTS insert_data;

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    WHILE i <= 100 DO
        insert into t1(id,a,b) values(i,i,i);
        SET i = i + 1;
    END WHILE;
    WHILE j <= 1000 DO
        insert into t2(id,a,b) values(j,j,j);
        SET j = j + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_data(); 

select * from t1 join t2 using (a);的执行计划:

数据库总是使用小表做驱动表,这样能使执行效率最高。

这里是通过t1查到所需的索引,再在t2表中进行索引查找。

结果是否需要占用join_buffer_size?不需要,因为查询的结果可以直接发送给客户端。

straight_join

即使使用select * from t2 join t1 using (a);也会使用t1小表作为驱动表

要想使用t2作为驱动表,可以select * from t2 straight_join t1 using (a);

只在优化器计算不准时,调优使用

semijoin

explain select * from t1 where a in (select a from t2);:

in的语意就是存在一个就行,只会返回一条数据。mysql会改写成join语句。

anti join

explain select * from t1 where id not in (select id from t2);

同上,mysql会改写成join语句。

join_buffer_size使用场景

刚刚都是基于索引进行join操作,如果用于过滤的字字段没有索引如:select * from t1 join t2 using (b);

在mysql8.0之前,没有hash join,流程:

会先将t1表中内容加入join_buffer,在用t2的每一行遍历匹配join_buffer中的数据。

mysql8.0及以后对其进行优化,join_buffer中存储的不再是原始数据,改为hash表,这样就不需要遍历匹配改为hash查找。

验证:

当出现这种情况时,就需要考虑join_buffer中能否将数据都放下,避免出现io影响执行效率。

join案例分析

create table users(id int primary key auto_increment, nickname varchar(255));
create table orders(id int primary key auto_increment, order_id int, customerid int, sellerid int, lastmodified datetime,info text, index(sellerid));

假设 orders 有100w行, 其中sellerid=1的有10w行,现在要查出sellerid=1的所有订单 和买家信息

select * from orders o join users u on o.customerid=u.id where o.sellerid=1;

执行分析:因为存在主键,所以会走Nested Loop join,就是扫描orders表,找到满足where条件的语句,在通过o.customerid=u.idusers表中做B+树查找。

优化思路1:B+树的查找因为是大量点差,所以能改成hash join会更好:

select * from orders o join users u ignore index (PRIMARY) on o.customerid=u.id where o.sellerid=1;

沿着上面的思路,也可以将hash操作放在应用层做,避免数据库出现性能瓶颈。在mysql8.0之前没有hash join该操作只能放在应用层。

posted @   余为民同志  阅读(17)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示