mysql海量数据优化
2024-09-02 01:51:17
一般我们数据量大的时候,然后就需要进行分页,一般分页语句就是limit offset,rows。这种分页数据量小的时候是没啥影响的,一旦数据量越来越大随着offset的变大,性能就会越来越差。下面我们就来实验下:
- 准备数据
- 建一个测试表引擎为MyISAM(插入数据没有事务提交,插入速度快)的表。
CREATE TABLE USER (
id INT ( 20 ) NOT NULL auto_increment,
NAME VARCHAR ( 20 ) NOT NULL,
address VARCHAR ( 20 ) NOT NULL,
PRIMARY KEY ( id )
) ENGINE = MyISAM;
- 写一个批量插入的存储过程
delimiter //
# 删除表数据
TRUNCATE TABLE t;
# 如果已经有sp_test_batch存储过程,将其删除,后面重新创建
DROP PROCEDURE IF EXISTS sp_test_batch;
# 创建存储过程,包含num和batch输入,num表示插入的总行数,batch表示每次插入的行数
CREATE PROCEDURE sp_test_batch(IN num INT,IN batch INT)
BEGIN
SET @insert_value = '';
# 已经插入的记录总行数
SET @count = 0;
#
SET @batch_count = 0;
WHILE @count < num DO
# 内while循环用于拼接INSERT INTO t VALUES (),(),(),...语句中VALUES后面部分
WHILE (@batch_count < batch AND @count < num) DO
IF @batch_count>0
THEN
SET @insert_value = concat(@insert_value,',');
END IF;
SET @insert_value = concat(@insert_value,"('name", @count, "','address", @count, "')");
SET @batch_count = @batch_count+1;
END WHILE;
SET @count = @count + @batch_count;
# 拼接SQL语句并执行
SET @exesql = concat("insert into user(name,address) values ", @insert_value);
PREPARE stmt FROM @exesql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# 重置变量值
SET @insert_value = '';
SET @batch_count=0;
END WHILE;
# 数据插入完成后,查看表中总记录数
SELECT COUNT(id) FROM user;
END
CALL sp_test_batch(10000000,10000);
插入100w数据
- 测试性能 下 下面我们分别针对于offset等于不同的值来进行实:offset等于10000时耗时
- offset等于100000时耗时
- offset等于1000000时耗时
- offset等于5000000时耗时
- offset等于10000000时耗时
从上图可以得出随着offset的值越大耗时就越来越多。这还只是1000w数据,如果我们上亿数据呢,可想而知这时候查询的效率有多差。下面我们来进行优化。
4 .进行优化
子查询的分页方式:
SELECT * FROM user WHERE id >=
(SELECT id FROM user ORDER BY id LIMIT 9000000, 1) LIMIT 10
从图可以得出子查询确实速度快了一倍。
JOIN分页方式:
SELECT * FROM user t1 INNER join
(SELECT id FROM user ORDER BY id LIMIT 9000000, 10) t2 on t2.id =t1.id
join的方式比子查询性能在稍微好点。
终极优化:
这个时间性能是最好的。这种优化必须要依赖前一次的查询的最大ID,如果是那种分页直接可以指定多少页的是不行的,必须是只能后一页,后一页这么点击。
SELECT id FROM user where id > 9000000 ORDER BY id LIMIT 10;
通过伪列对ID进行分页,可以多线程同时查询,这个适合分页把全量数据加载到缓存。
得到ID的范围
select id from(
SELECT @rownum:=@rownum+1 AS rownum, id FROM user as t1 ,(SELECT @rownum:=0) t2 order
by t1.id asc
) t3 where t3.rownum%5000=0
select * from user where id >0 and id<=5000 一直到最大的id
最新文章
- js 判断 是否位数字
- JS之对象
- ctrl + d 在phpstorm 和 eclipse 中的不同含义
- Adroid_Spinner_ArrayAdapter
- mysql命令使用
- User、Role、Permission数据库设计ABP
- WPF之TreeList的实现方法1
- ALAssetsLibrary 照片相关 浅析
- 图解SQL的各种连接join
- (原创)我对未来的人类的发展,以及AI技术发展的一些思考。
- cocos2dx 魔塔项目总结(一)
- 集成direnv 与docker-compose 进行环境变量管理
- Django标签&;迭代&;循环&;过滤
- Redhat配置yum源(使用阿里云yum Repo)
- java比较两个对象是否相等?
- 解剖 CPU
- EJB3.0 EJB开发消息驱动bean
- Cognos TM1_10.1.1服务端配置
- 征战jQuery
- “ResGen.exe”已退出,代码为2 问题处理
热门文章
- CentOS下搭建VNC/TEAMVIEW/SSH无密码登录
- VS2019配置C+++mingW32配置
- centOs7.5.64之前的操作系统搭建GitLab记录
- 第一次UML编程作业
- WEB组态可视化软件(B/S)
- 【2014广州市选day1】JZOJ2020年9月12日提高B组T3 消除游戏
- Spring Boot 实现看门狗功能 (调用 Shell 脚本)
- RestTemplate 统一添加 Header
- 第15.40节、PyQt(Python+Qt)实战:moviepy实现MP4视频转gif动图的工具
- Flutter开发实战笔记