MySQL 百万级 查询性能优化

生成数据表

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树查询结构,使用二分查找。
所以逆向查询可以缓解查询缓慢。
但此法并不能用于线上。因为线上的数据是实时变动的。查询最新的百条数据会很快。
但是如果要将旧的数据找出来。
建议分表。