最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

实战SQL优化（以MySQL深分页为例）

来源：博客园

1 准备表结构

CREATE TABLE `student`  (  `id` int NOT NULL AUTO_INCREMENT,  `user_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,  `score` decimal(10, 2) NULL DEFAULT NULL,  `create_time` datetime NULL DEFAULT NULL,  `update_time` datetime NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

2 需求

按照成绩降序排列，并查询字段 user_no,user_name,score，做一个带排序的分页查询

3 自动执行数据

delimiter $$CREATE PROCEDURE BatchInsert ( IN initId INT, IN loop_counts INT)BEGINDECLARE Var INT;DECLARE ID INT;SET Var = 0;SET ID = initId;SET autocommit = 0;WHILE Var < loop_counts DOINSERT INTO `test`.`student` ( `user_no`, `user_name`, `score`, `create_time`, `update_time` )VALUES(CONCAT( "学号", ID ),CONCAT( "姓名", ID ),FLOOR( 1 + RAND()* 100 ),DATE_ADD( "2023-3-30 16:08:00", INTERVAL ROUND( RAND()* 1000+1 ) DAY ),DATE_ADD( "2023-3-30 16:08:00", INTERVAL ROUND( RAND()* 1000+1 ) DAY ) );SET ID = ID + 1;SET Var = Var + 1;END WHILE;COMMIT;END $$;delimiter;CALL BatchInsert(1,2000000)

4 需要分页的sql

SELECT user_no,user_name,score FROM student ORDER BY score DESC LIMIT 5,20 #浅分页SELECT user_no,user_name,score FROM student ORDER BY score DESC LIMIT 80000,20 #深分页

5 分页执行计划

通过执行计划 expladin 看下执行效率：

  • 浅分页：


    (资料图片仅供参考)

  • 深分页：

可以看出 type=all都是走的全表扫描，并且都使用了额外的文件排序，现在记录一下执行时间：

浅分页：0.887s，深分页：1.427s

5.1 对排序字段添加索引

对 score 添加索引：alter table student add index idx_score(score)

浅分页：

耗时：0.021s

深分页：

耗时：1.475s

可以看出，虽然对排序字段加了索引，但是由于深分页偏移量太大，还是选择了走全表扫描 type=all。并额外使用了文件排序。

可以分析出，排序需要成本，回表也需要成本，浅分页由于偏移量小，回表成本低，所以执行效率有很大的提升，深分页偏移量大，回表成本太高了，所以需要降低深分页回表的成本。

5.2 建立联合索引

建立联合索引，就是为了消除回表带来的效率损耗。

alter table student add index idx_no_name_score(score,user_no,user_name)

浅分页：

耗时：0.024s

深分页：

耗时：0.047s

可以看到，使用联合索引已经可以解决了回表的问题，两者的执行效率也高了很多，但是这种做法有一个缺点，如果我们要查询出来的数据多了一个字段，就得重建联合索引，这样扩展性太差肯定不能接受的。所以还有一种办法，手动回表。

5.3 手动回表

手动回表的前提是对order by 字段添加了索引

浅分页：

SELECTuser_no,user_name,score FROMstudent s1JOIN ( SELECT id FROM student ORDER BY score DESC LIMIT 5, 20 ) s2 ON s1.id = s2.id

执行计划：

id 大的先执行

耗时：0.021s

深分页：

SELECTuser_no,user_name,score FROMstudent s1JOIN ( SELECT id FROM student ORDER BY score DESC LIMIT 80000, 20 ) s2 ON s1.id = s2.id

执行计划：

耗时：0.042s

6 总结

优化方式浅分页索引Type深分页索引Type浅分页耗时深分页耗时
无AllAll0.887s1.427s
order by 字段加索引indexAll0.021s1.475s
联合索引indexindex0.024s0.047s
手动回表(order by字段加索引)indexindex0.021s0.042s

关键词：