当前位置:   article > 正文

MySQL千万级数据优化方案_mysql千万级别数据优化方案

mysql千万级别数据优化方案

简介

                          ↓↓↓处理千万级数据的MySQL数据库,可以采取以下优化措施↓↓↓

                                                         

  1. 使用索引:确保对经常用于查询和排序的字段添加索引。不要在查询中使用SELECT *,而是明确指定需要的字段。
  2. 分区表:如果表中的数据按照时间或其他维度进行划分,可以考虑使用分区表。这有助于加快查询速度,因为MySQL可以只扫描一部分数据。
  3. 缓存:考虑使用缓存,如Redis,来存储经常查询的数据。这可以减轻数据库的负担,提高查询速度。
  4. 水平扩展:增加MySQL服务器的数量来提高处理能力。可以使用负载均衡技术将请求分配到不同的服务器上。
  5. 优化查询语句:确保查询语句简单、高效。避免使用子查询和复杂的JOIN语句。对查询结果进行分页,以减少返回的数据量。
  6. 数据库监控:定期监控数据库的性能指标,如慢查询日志、锁等待等。根据监控结果对数据库进行调优,如调整缓存大小、优化索引等。
  7. 使用索引优化器:使用MySQL自带的索引优化器来分析查询性能,并找出可以优化的字段和索引。
  8. 数据库分区:根据业务逻辑对数据库进行分区,将相关数据存储在同一个分区中。这有助于加快查询速度,减少锁等待等问题。
  9. 优化MySQL配置:根据硬件和业务需求,对MySQL的配置进行优化,如调整缓冲区大小、连接数等。

1、创建数据 

1.1、建表语句

  1. DROP TABLE IF EXISTS `user_data`;
  2. CREATE TABLE `user_data` (
  3. `id` bigint(50) NOT NULL AUTO_INCREMENT,
  4. `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  5. `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  6. `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  7. `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  8. `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  9. `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  10. `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  11. `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  12. `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  13. `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  14. `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  15. `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  16. PRIMARY KEY (`id`) USING BTREE
  17. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

1.2、存储过程(反例)

存储过程实现效率低(不推荐 仅供参考)

  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `P_xiao_jian`()
  2. BEGIN
  3. DECLARE i INT DEFAULT 1;
  4. #Routine body goes here...
  5. WHILE i<=10000000 DO
  6. INSERT INTO user_data(id,attr1) VALUES(i,'CSDN臭弟弟测试数据');
  7. SET i = i+1;
  8. END WHILE;
  9. END

 可以看到效率很慢,执行老好长时间才14万条数据

 df9d7244fc9d40bcab9e203a3901e230.png

1.3、高效执行(正例)

代码实现更高效(大约30多秒,推荐)

  1. import com.baomidou.mybatisplus.core.toolkit.IdWorker;
  2. import java.io.BufferedOutputStream;
  3. import java.io.File;
  4. import java.io.FileOutputStream;
  5. import java.io.IOException;
  6. public class TestDataController {
  7. /**
  8. * 快速添加一千万条测试数据
  9. * @param args
  10. */
  11. public static void main(String[] args){
  12. String sql = "INSERT INTO user_data(id,attr1) VALUES(%s,'CSDN臭弟弟测试数据');";
  13. System.out.println(String.format(sql, IdWorker.getId()));
  14. String path="J:\\testData.sql";
  15. File file=new File(path);
  16. if(!file.exists()){
  17. try {
  18. file.createNewFile();
  19. } catch (IOException e) {
  20. e.printStackTrace();
  21. }
  22. }
  23. try {
  24. //BufferedOutputStream是Java中一个用于输出字节流的缓冲区类。
  25. BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(path)) ;
  26. long startTime = System.currentTimeMillis();
  27. for (int i = 0; i < 10000000; i++) {
  28. //写数据 IdWorker是一个Java类,该方法返回一个long类型的ID。
  29. bos.write(String.format(sql, IdWorker.getId()).getBytes());
  30. if(i<10000000-1){
  31. bos.write("\n".getBytes());
  32. }
  33. }
  34. long endTime = System.currentTimeMillis();
  35. System.out.println("一千万条测试数据耗时:" + (endTime - startTime));
  36. //释放资源
  37. bos.close();
  38. } catch (IOException e) {
  39. e.printStackTrace();
  40. }
  41. }
  42. }

查看testData.sql 文件

aa3c160eeeb6440cad23b6bafb8d5791.png

1.4、使用Navicat将sql文件导入数据库

导入testData.sql 文件(注:导入之前如果testData.sql文件生成随机id ,导入前关闭主键自增),当然也可以命令行导入。

e6cd45318b2e41aca2a8729c20a4e116.png

导入完成 

 c79a6fe3e46a4cea88e77d2f6c28ae2e.png

查询一千万条测试数据 耗时8秒

beb2ace9c09e4d7cbc1533716bb305a2.png

 1.5、普通分页查询

注意: MySQL 是通过 LIMIT 语句来选取指定的条数, Oracle 使用 ROWNUM 来选取指定的条数。

MySQL:

  • LIMIT子句用于限制结果集中返回的行数,语法如下↓↓↓
  1. SELECT attr1, attr2, ...
  2. FROM table1
  3. LIMIT offset, count;

    说明: 

    offset是起始行数(也称之为偏移量),count是要返回的行数。

  • 列如,选取表table1的前5条记录,可以使用以下语句↓↓↓
SELECT * FROM table1 LIMIT 0, 5;
  • 取从第3条记录开始的10条记录,可以使用以下语句↓↓↓
SELECT * FROM table1 LIMIT 3, 10;

Oracle:

  • ROWNUM是一个伪列,用于标识查询结果集中的每一行,从1开始,并在每一行中递增。语法如下↓↓↓
  1. SELECT *
  2. FROM (
  3. SELECT rownum rn, attr1, attr2, ...
  4. FROM table
  5. )
  6. WHERE rn BETWEEN 10 AND 20;

说明: 

选取10到20行数据。查询结果将包括10行数据,从第11行到第20行。注意,必须先选取ROWNUM列,然后才能使用WHERE子句来限制结果集。

2、开始测试查询

注意: 最近看到平台很多这样的帖子复现给大家,都在说这个偏移量 和 数据量 ,数据越来越大肯定是影响查询效率啊,查一条数据 和查100万条数据 能一样吗? 以此叠加数据效率肯定是越来越慢。

2.1、测试语句

SELECT * FROM user_data LIMIT 10000, 10;
  •  查询结果(两次结果 分别是: 0.039s /0.033s)秒级的够可以吧! 毕竟是本地也正常 。 继续↓↓↓

53ac4f695c2f42cd82a1b937528543a6.png

f102ce7c64994fa9887598a0c087c0c3.png

2.2、偏移量相同,数据量不同

语句:

  1. SELECT * FROM user_data LIMIT 10000, 10;
  2. SELECT * FROM user_data LIMIT 10000, 100;
  3. SELECT * FROM user_data LIMIT 10000, 1000;
  4. SELECT * FROM user_data LIMIT 10000, 10000;
  5. SELECT * FROM user_data LIMIT 10000, 100000;
  6. SELECT * FROM user_data LIMIT 10000, 1000000;

 执行结果:

bfdc74f694914667818eb0edffead2d1.png

 数据量越大执行时间越长,往下继续↓↓↓(为什么不在加大数据测试,我不敢,电脑会卡)

2.3、偏移量不同,数据量相同

语句:

  1. SELECT * FROM user_data LIMIT 10, 10000;
  2. SELECT * FROM user_data LIMIT 100, 10000;
  3. SELECT * FROM user_data LIMIT 1000, 10000;
  4. SELECT * FROM user_data LIMIT 10000, 10000;
  5. SELECT * FROM user_data LIMIT 100000, 10000;
  6. SELECT * FROM user_data LIMIT 1000000, 10000;

 执行结果:

a4ee7d6a2e304d46a160832146f84ba6.png

 偏移量越大执行时间越长,往下继续↓↓↓

3、优化查询

3.1 数据量过大问题

语句:

  1. SELECT * FROM user_data LIMIT 1, 1000000;
  2. SELECT id FROM user_data LIMIT 1, 1000000;
  3. SELECT id, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM user_data LIMIT 1, 1000000;

执行结果:

022f58b864034ab19292c976a23b5735.png

 说明: 我相信没有人会这么干的吧!  查几十万的数据,当然这种情况也不能排查,就算有也会使用 redis数据库做缓存处理 ,redis是一个高速缓存服务器,可以快速地存储和检索数据。redis读取速度达到10万/s ,写的速度为8万/秒。

  • 注意代码中涉及查询的sql禁止select *
  • 严谨使用 SELECT * 会出现性能问题,使用星号会读取所有字段,增加开销。
  • 建议在使用SELECT语句时,不要使用星号,而是明确指定需要查询的字段。
  • 字段数不同问题:如果你在使用"insert into table1 select * from table2"这样的语句时,若table1和table2的字段数不同,会导致任务运行失败出现错误。

3.2 偏移量过大问题

3.2.1 采用子查询方式

语句:

  1. SELECT id FROM user_data LIMIT 1000000, 1;
  2. SELECT * FROM user_data WHERE id >= (SELECT id FROM user_data LIMIT 1000000, 1) LIMIT 10;

 说明: 定位偏移位置的 id,再查询数据↓↓↓

执行结果:

f7a37f80842941aa8fa304da99af9c7d.png

 说明: 接下来分析查看EXPLAIN执行计划↓↓↓

3.2.2 EXPLAIN分析sql 执行计划

语句:

  1. EXPLAIN SELECT id FROM user_data LIMIT 1000000, 1;
  2. EXPLAIN SELECT * FROM user_data WHERE id >= (SELECT id FROM user_data LIMIT 1000000, 1) LIMIT 10;

执行结果:

13d798f1ea6c45da93974fdd8c01065f.png

 ab58b7435ce54887a97540c77c23244e.png

 上面执行计划走索引了啊??? (注意: 创建表时,如果没有指定索引,则MySQL会自动创建一个名为PRIMARY的索引。)继续↓↓↓

                                           

3.2.3 加索引

没有在加一层解决不了的 ,在加索引。(UNIQUE  唯一索引)

Navicat视图工具加索引,也可以通过命令。

1aef2b7a9dc243abbc928d6244d05d6e.png

执行语句:

  1. EXPLAIN SELECT id FROM user_data LIMIT 1000000, 1;
  2. EXPLAIN SELECT * FROM user_data WHERE id >= (SELECT id FROM user_data LIMIT 1000000, 1) LIMIT 10;

执行结果: 

af59f33a5b434ec08a6cfe9b2462b72c.png

 在和之前对比有比较显著的提高

再次分析执行计划:

d9948ae38c97494db2989f21b4698015.png

 13623cbf7376423d990791cea2205949.png

总结: 命中的索引不一同,命中唯一索引的查询效率更高。

  • 索引优化:在表中添加适当的索引可以提高查询性能,尤其是对于关联查询。确保在建立索引时考虑到查询条件,并避免重复索引
  • 索引并不是越多越好,要根据查询有针对性的创建,考虑在WHEREORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描

 

3.2.4、重点头戏(子查询优化带来的问题)

子查询优化带来的问题,加点条件 你猜性能还会好吗? 继续↓↓↓

  1. SELECT * FROM user_data
  2. WHERE id >= ( SELECT id FROM user_data ORDER BY id desc LIMIT 1000000, 1 )
  3. ORDER BY id LIMIT 10;

 在以上sql基础上随便加点条件执行结果如下:

8fea40468a3b41d69d678f38555f10b7.png

来查看执行计划:

1090d252c38f4d5a883ba4c0e4f2603c.png

理论上说上面这子查询是错误的,虽然走索引了但是都彪到1.6秒,在加点复杂查询会更高。

子查询会带来以下问题:

  • 性能问题:子查询需要额外的计算,这可能会导致性能变差。
  • 可读性问题:大量的子查询代码可能难以阅读和维护。
  • 错误率问题:子查询的sql代码可能容易出现错误,因为它们的逻辑可能很复杂。
  • 可维护性问题:大量的子查询代码可能难以维护,因为它们可能很长并且难以理解。

可以尝试以下方法来避免这些问题:

  • 进行优化:对mysql进行优化,例如调整缓冲区、增加索引等。
  • 分页查询:将结果分页,减少一次性返回的数据量,从而减少子查询的计算量。
  • 使用连接:使用连接(JOIN)代替子查询(注意JOIN也不易过多),这(可能)会更高效,并且更容易阅读和维护。
  • 使用视图:使用视图(View)来封装复杂的查询,从而使其更易于理解和维护。
  • 避免复杂逻辑:尽可能避免使用复杂的逻辑,例如嵌套的子查询,这可能会导致性能下降和错误率增加。

总结: 如果设计初期能够预料到数据库表的数据会倍增长,请合理的构建优化方案,比如: 索引、分区表、缓存、水平扩展、数据库分区、优化MySQL配置  等等....

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/人工智能uu/article/detail/743888
推荐阅读
相关标签
  

闽ICP备14008679号