一、准备表数据
咱们建一张用户表,表中的字段有用户ID、用户名、地址、记录创建时间,如图所示
OK,接下来准备写一个存储过程插入一百万条数据
CREATE TABLE `t_user` (
`id` int NOT NULL,
`user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE PROCEDURE user_insert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<1000000
DO
INSERT INTO t_user(id, user_name, address, create_time) VALUES (i, CONCAT('mayun',i), '浙江杭州', now());
SET i=i+1;
END WHILE ;
commit;
END;;
CALL user_insert();
插入完后咱们看看数据条数
二、优化方式
1.分页查询优化
OK,咱们看下分页limit到一定值时的耗时是多少
-
limit 1000时
-
limit 10000时
-
limit 100000时
-
limit 1000000时
可以看到limit值越大,耗时越长,这还只是一百万数据,要是千万级、亿级呢?
OK不废话,咱们马上进行分页优化
-
的查询优化
可以看到比起之前 limit 1000000时的0.218s 效率提高了很多
-
使用JOIN分页
可以看到比起之前 limit 1000000时的0.218s 效率也同样提高了很多
-
使用前一次查询的最大ID
可以看到这种方法效率最高,但依赖于需要知道最大ID,这种适合点击下一页查询(类似于滚动加载数据)的场景
-
通过伪列对ID进行分页
然后可以开启多个线程去进行最高效率查询语句的批量查询操作 0~10000,10001-20000.... 这样子的话可以快速把全量数据查询出来同步至缓存中。
分页优化总结:使用前一次查询的最大ID进行查询优化是效率最高的方法,但这种方法只适用于下一页点击的这种操作,对于同步全量数据来说建议的方式使用伪列对ID进行分页,然后开启多个线程同时查询,把全量数据加载到缓存,以后面试官问你如何 快速获取海量数据并加载到缓存 你该知道怎么回答了吧。
2.普通索引优化
先来看没索引优