Mysql 子查询提高Limit查询效率

原创已于 2024-08-22 15:38:55 修改 · 703 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #性能优化 #sql

于 2022-07-24 22:01:15 首次发布

Mysql高级同时被 2 个专栏收录

2 篇文章

订阅专栏

Mysql性能优化

1 篇文章

订阅专栏

本文探讨了Mysql中使用Limit进行查询时的性能问题，特别是当offset值较大时，导致效率显著下降。文章通过实例展示了如何利用子查询优化这一过程，减少了不必要的数据读取和回表操作，从而提高了查询效率。通过对原SQL和优化后SQL的执行结果对比，证明了优化的有效性。总结中强调了理解Mysql Limit的工作原理以及应用子查询和覆盖索引的重要性。

一. 概述

围绕Sql展开分析

SELECT * FROM t_test WHERE age = 100 LIMIT 1000000, 10

SELECT * FROM `t_test` a INNER JOIN (SELECT id FROM t_test WHERE age = 100 LIMIT 1000000, 10) b on a.id = b.id;

环境Docker
底层分析原因
通过实例验证分析的结果
Mysql存储过程

二. 准备工作

表结构

CREATE TABLE `t_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `age` int NOT NULL,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `gender` tinyint NOT NULL,
  `address` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

编写存储过程插入测试数据

DELIMITER $$
CREATE PROCEDURE t_test()
BEGIN
DECLARE i INT;
SET i = 1;
WHILE i<1000000 DO
		INSERT INTO t_test (age,`name`,gender,address) VALUES(100,'瞎玩儿',1,'花果山水帘洞美猴王齐天大圣孙悟空');
		SET i = i+1;
	END WHILE;
END $$
DELIMITER ;

执行存储过程
```
CALL t_test();
```

三. 测试SQL

原Sql

SELECT * FROM t_test WHERE age = 100 LIMIT 257583, 10

优化后的Sql

SELECT * FROM `t_test` a INNER JOIN (SELECT id FROM t_test WHERE age = 100 LIMIT 257583, 10) b on a.id = b.id;

分析原因
原sql查询过程：首先在age索引树中查询257583+10次来获取257583+10个主键索引值，然后从主键索引树（聚集索引）中查询257583+10次获取所有数据，最后过滤出10条数据。Mysql浪费大量时间查询无用的数据。导致Sql效率下降。
优化后：首先在age索引树中查询257583+10次来获取10主键索引值，然后从主键索引树（聚集索引）中查询10次获取所有数据，避免查询过滤大量无用数据。

四. 验证分析原因

sql执行结果对比，数据量越大效果越明显（本地插入1000000数据太慢, 等不了了~）

通过buffer pool进行验证原Sql

docker restart mysql 重启mysql清空buffer pool
SELECT * FROM t_test WHERE age = 100 LIMIT 257583, 10 执行Sql
SELECT index_name, count(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME LIKE '%test%' GROUP BY index_name; 查询Buffer Pool读取数据页情况

在这里插入图片描述

可以看出原SQL 读取了1507个数据页，226个索引页

测试优化后的Sql

docker restart mysql 重启mysql清空buffer pool
SELECT * FROM `t_test` a INNER JOIN (SELECT id FROM t_test WHERE age = 100 LIMIT 257583, 10) b on a.id = b.id; 执行SQL
SELECT index_name, count(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME LIKE '%test%' GROUP BY index_name;查询buffer pool读取数据情况

在这里插入图片描述

可以看出优化后SQL 读取226索引页，而数据页只有3页。

五. 总结

Msyql中的Limit （offset,count）原理：先取出offset+count条记录，然后抛弃前面offset条，然后读后面的count条。如果offset偏移量越大，性能越差。Mysql会浪费大量随机IO查询数据页进行回表操作，占用buffer pool的空间。
可以使用覆盖索引和子查询的方式进行优化（如上优化后的Sql）
Mysql innodb 每页的大小默认是16kb
Msyql中还有很多盲点，如果有问题欢迎一起讨论。