CREATE TABLE `t_member` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`sex` int DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`token` varchar(255) DEFAULT NULL,
`del_flag` int DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_name` (`name`) USING BTREE COMMENT '普通索引'
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--- 定义存储过程
DELIMITER $
DROP PROCEDURE IF EXISTS `autoinsert`$
CREATE PROCEDURE `autoinsert`(IN insertCount INT)
BEGIN
DECLARE count INT DEFAULT 0;
WHILE(count < insertCount)
DO
INSERT INTO `t_member`( `name`, `sex`, `password`, `create_time`, `token`)
select substring(MD5(RAND()),1,8),0,substring(MD5(RAND()),1,10),SYSDATE(),substring(MD5(RAND()),1,20) from dual;
SET count = count+1;
END WHILE;
END$
DELIMITER;
-- 调用存储过程
call autoinsert(100000);
存储过程有点慢(可以试试java代码批量插入):
call autoinsert(1000000)
> OK
> 时间: 701.366s
使用代码插入也是要差不多,mybatisplus的批量插入(包含了构造数据)
select * from t_member limit 10,10;
200w的数据,都是0.00几秒
select * from t_member limit 999990,10; -- 100w数据
select * from t_member limit 1999990,10; -- 200w数据
select a.*
from t_member a
join(
select id
from t_member order by id
limit 1999990,10
) b ON a.id = b.id;
200w的结果下,快了0.3s左右
记录上次查询得到的最大id
select a.*
from t_member a
join(
select id
from t_member where id > 1999990 order by id
limit 10
) b ON a.id = b.id;
强制指定查询条件,比如京东 web 端为例,根据关键词搜索历史订单,时间维度默认为近三个月,以年为单位允许用户手动切换,但不允许查询全量数据。
页面更新:2024-02-01
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号