生成数据表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`uuid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `permission`;
CREATE TABLE `role` (
`uuid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for role_permission
-- ----------------------------
DROP TABLE IF EXISTS `role_permission`;
CREATE TABLE `role_permission` (
`role_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`permission_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`role_id`, `permission_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
建立函数循环向实体表插入1万条数据
函数定义
CREATE DEFINER=`root`@`%` PROCEDURE `stressTest`()
BEGIN
DECLARE num INT;
SET num = 1;
WHILE num < 1000000 DO
INSERT INTO permission(uuid) select uuid();
INSERT INTO role(uuid) select uuid();
SET num = num + 1;
END WHILE;
END
建立关系 百万级笛卡尔积
函数定义
CREATE DEFINER=`root`@`%` PROCEDURE `stressTest1`()
BEGIN
DECLARE num INT;
SET num = 1;
WHILE num < 10000 DO
insert into role_permission(permission_id,role_id)
select a.uuid,b.uuid from permission a,role b where a.uuid =(
select uuid from permission a order by a.uuid limit 1 OFFSET num);
SET num = num + 1;
END WHILE;
END
实验需求
获取某一主键permission_id的1万条role_id
并使用offset参数进行分页,
分页条件为 limit 10 offset 9990
且需要对role_id进行排序。
实验中
由于实验时忘记给num加1,循环函数在强制关闭服务时才结束。
实验时产生的数据为固定的11552条。
实验结果
在计算行数时, 查询时间约为120秒.
select count(0) from role_permission where permission_id = ?
11552
116.262s
在使用正排排序时,计算 limit 10 offset 9990,查询语句约为90秒.
select * from role_permission a where a.permission_id = ? limit 10 offset 9990
90.935s
在使用倒排排序时,计算 limit 10 offset (10000-9990-10) 查询语句约为7秒
select * from(select * from role_permission by role_id desc limit 10 offset 1552) b order by role_id;
7.465s
结论
由于MySQL使用B树查询结构,使用二分查找。
所以逆向查询可以缓解查询缓慢。
但此法并不能用于线上。因为线上的数据是实时变动的。查询最新的百条数据会很快。
但是如果要将旧的数据找出来。
建议分表。