简介 ↓↓↓处理千万级数据的Mysql数据库,可以采取以下优化措施↓↓↓
↓↓↓处理千万级数据的Mysql数据库,可以采取以下优化措施↓↓↓
- 使用索引:确保对经常用于查询和排序的字段添加索引。不要在查询中使用SELECT *,而是明确指定需要的字段。
- 分区表:如果表中的数据按照时间或其他维度进行划分,可以考虑使用分区表。这有助于加快查询速度,因为mysql可以只扫描一部分数据。
- 缓存:考虑使用缓存,如Redis,来存储经常查询的数据。这可以减轻数据库的负担,提高查询速度。
- 水平扩展:增加Mysql服务器的数量来提高处理能力。可以使用负载均衡技术将请求分配到不同的服务器上。
- 优化查询语句:确保查询语句简单、高效。避免使用子查询和复杂的JOIN语句。对查询结果进行分页,以减少返回的数据量。
- 数据库监控:定期监控数据库的性能指标,如慢查询日志、锁等待等。根据监控结果对数据库进行调优,如调整缓存大小、优化索引等。
- 使用索引优化器:使用MySQL自带的索引优化器来分析查询性能,并找出可以优化的字段和索引。
- 数据库分区:根据业务逻辑对数据库进行分区,将相关数据存储在同一个分区中。这有助于加快查询速度,减少锁等待等问题。
- 优化MySQL配置:根据硬件和业务需求,对MySQL的配置进行优化,如调整缓冲区大小、连接数等。
DROP TABLE IF EXISTS `user_data`;CREATE TABLE `user_data` ( `id` bigint(50) NOT NULL AUTO_INCREMENT, `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
存储过程实现效率低(不推荐 仅供参考)
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_xiao_jian`()BEGINDECLARE i INT DEFAULT 1;#Routine body Goes here...WHILE i<=10000000 DO INSERT INTO user_data(id,attr1) VALUES(i,'CSDN臭弟弟测试数据'); SET i = i+1; END WHILE;END
可以看到效率很慢,执行老好长时间才14万条数据
代码实现更高效(大约30多秒,推荐)
import com.baomidou.mybatisplus.core.toolkit.IdWorker;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;public class TestDataController { public static void main(String[] args){ String sql = "INSERT INTO user_data(id,attr1) VALUES(%s,'CSDN臭弟弟测试数据');"; System.out.println(String.format(sql, IdWorker.getId())); String path="J:\\testData.sql"; File file=new File(path); if(!file.exists()){ try { file.createNewFile(); } catch (IOException e) { e.printStackTrace(); } } try { //BufferedOutputStream是Java中一个用于输出字节流的缓冲区类。 BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(path)) ; long startTime = System.currentTimeMillis(); for (int i = 0; i < 10000000; i++) { //写数据 IdWorker是一个Java类,该方法返回一个long类型的ID。 bos.write(String.format(sql, IdWorker.getId()).getBytes()); if(i<10000000-1){ bos.write("\n".getBytes()); } } long endTime = System.currentTimeMillis(); System.out.println("一千万条测试数据耗时:" + (endTime - startTime)); //释放资源 bos.close(); } catch (IOException e) { e.printStackTrace(); } }}
查看testData.sql 文件
导入testData.sql 文件(注:导入之前如果testData.sql文件生成随机id ,导入前关闭主键自增),当然也可以命令行导入。
导入完成
查询一千万条测试数据 耗时8秒
注意: MySQL 是通过 LIMIT 语句来选取指定的条数, oracle 使用 ROWNUM 来选取指定的条数。
MySQL:
SELECT attr1, attr2, ... FROM table1LIMIT offset, count;
说明:
offset是起始行数(也称之为偏移量),count是要返回的行数。
SELECT * FROM table1 LIMIT 0, 5;
SELECT * FROM table1 LIMIT 3, 10;
Oracle:
SELECT * FROM ( SELECT rownum rn, attr1, attr2, ... FROM table ) WHERE rn BETWEEN 10 AND 20;
说明:
选取10到20行数据。查询结果将包括10行数据,从第11行到第20行。注意,必须先选取ROWNUM列,然后才能使用WHERE子句来限制结果集。
注意: 最近看到平台很多这样的帖子复现给大家,都在说这个偏移量 和 数据量 ,数据越来越大肯定是影响查询效率啊,查一条数据 和查100万条数据 能一样吗? 以此叠加数据效率肯定是越来越慢。
SELECT * FROM user_data LIMIT 10000, 10;
语句:
SELECT * FROM user_data LIMIT 10000, 10;SELECT * FROM user_data LIMIT 10000, 100;SELECT * FROM user_data LIMIT 10000, 1000;SELECT * FROM user_data LIMIT 10000, 10000;SELECT * FROM user_data LIMIT 10000, 100000;SELECT * FROM user_data LIMIT 10000, 1000000;
执行结果:
数据量越大执行时间越长,往下继续↓↓↓(为什么不在加大数据测试,我不敢,电脑会卡)
语句:
SELECT * FROM user_data LIMIT 10, 10000;SELECT * FROM user_data LIMIT 100, 10000;SELECT * FROM user_data LIMIT 1000, 10000;SELECT * FROM user_data LIMIT 10000, 10000;SELECT * FROM user_data LIMIT 100000, 10000;SELECT * FROM user_data LIMIT 1000000, 10000;
执行结果:
偏移量越大执行时间越长,往下继续↓↓↓
语句:
SELECT * FROM user_data LIMIT 1, 1000000;SELECT id FROM user_data LIMIT 1, 1000000;SELECT id, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM user_data LIMIT 1, 1000000;
执行结果:
说明: 我相信没有人会这么干的吧! 查几十万的数据,当然这种情况也不能排查,就算有也会使用 redis数据库做缓存处理 ,redis是一个高速缓存服务器,可以快速地存储和检索数据。redis读取速度达到10万/s ,写的速度为8万/秒。
语句:
SELECT id FROM user_data LIMIT 1000000, 1;SELECT * FROM user_data WHERE id >= (SELECT id FROM user_data LIMIT 1000000, 1) LIMIT 10;
说明: 定位偏移位置的 id,再查询数据↓↓↓
执行结果:
说明: 接下来分析查看EXPLAIN执行计划↓↓↓
语句:
EXPLAIN SELECT id FROM user_data LIMIT 1000000, 1;EXPLAIN SELECT * FROM user_data WHERE id >= (SELECT id FROM user_data LIMIT 1000000, 1) LIMIT 10;
执行结果:
上面执行计划走索引了啊??? (注意: 创建表时,如果没有指定索引,则MySQL会自动创建一个名为PRIMARY
的索引。)继续↓↓↓
没有在加一层解决不了的 ,在加索引。(UNIQUE 唯一索引)
Navicat视图工具加索引,也可以通过命令。
执行语句:
EXPLAIN SELECT id FROM user_data LIMIT 1000000, 1;EXPLAIN SELECT * FROM user_data WHERE id >= (SELECT id FROM user_data LIMIT 1000000, 1) LIMIT 10;
执行结果:
在和之前对比有比较显著的提高
再次分析执行计划:
总结: 命中的索引不一同,命中唯一索引的查询效率更高。
WHERE
和ORDER BY
命令上涉及的列建立索引,可根据EXPLAIN
来查看是否用了索引还是全表扫描
子查询优化带来的问题,加点条件 你猜性能还会好吗? 继续↓↓↓
SELECT * FROM user_data WHERE id >= ( SELECT id FROM user_data ORDER BY id desc LIMIT 1000000, 1 ) ORDER BY id LIMIT 10;
在以上sql基础上随便加点条件执行结果如下:
来查看执行计划:
理论上说上面这子查询是错误的,虽然走索引了但是都彪到1.6秒,在加点复杂查询会更高。
子查询会带来以下问题:
可以尝试以下方法来避免这些问题:
总结: 如果设计初期能够预料到数据库表的数据会倍增长,请合理的构建优化方案,比如: 索引、分区表、缓存、水平扩展、数据库分区、优化MySQL配置 等等....
来源地址:https://blog.csdn.net/weixin_50002038/article/details/130805409
--结束END--
本文标题: MySQL千万级数据优化方案
本文链接: https://www.lsjlt.com/news/441264.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0