MySQL训练营-如何判断SQL的主要消耗阶段
慢查询分析思路
-
确认是锁还是执行消耗
-
对于执行消耗,分析执行过程
索引基础知识回顾
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 可以避免全表扫描,提高查询效率。
工作原理
- 确定复合索引:首先,数据库需要识别出满足条件的复合索引。例如,对于一个复合索引
(col1, col2)
,查询语句中可能只涉及col2
列。 - 划分前导列值:数据库会对复合索引的前导列(如
col1
)的所有唯一值进行枚举。 - 逐值扫描:针对前导列的每个唯一值,数据库会在索引中查找满足查询条件的
col2
列的值,就好像跳过了前导列的筛选过程,直接对后续列进行扫描。 - 合并结果:最后,将每次扫描得到的结果合并起来,作为最终的查询结果。
示例
假设有一个表 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。如果优化器判断失误,可能会导致性能下降。
执行器单元操作演示及相应的数据结构
- filter:where条件过滤
- temporary/materialize:中间会创建临时表/物化
- group by
- distinct
- sum/avg
group by存在不需要创建临时表情况:先order by在group by,即输入是有序的时候。
- 排序
- 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 语义
- 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)
- 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)
- 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 JOIN
、LEFT JOIN
、RIGHT JOIN
等连接操作中,其基本语法如下:
SELECT column_list
FROM table1
JOIN table2
USING (column_name);
其中,column_name
是两个表中具有相同名称和数据类型的列,JOIN
可以替换为其他连接类型(如LEFT JOIN
、RIGHT 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.id
在users
表中做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
该操作只能放在应用层。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战