0


MySQL千万级数据优化方案

简介

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

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

1、创建数据

1.1、建表语句

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;

1.2、存储过程(反例)

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

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_xiao_jian`()
BEGIN
DECLARE 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万条数据

df9d7244fc9d40bcab9e203a3901e230.png

1.3、高效执行(正例)

代码实现更高效(大约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 {

    /**
     *  快速添加一千万条测试数据
     * @param args
     */
    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 文件

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子句用于限制结果集中返回的行数,语法如下↓↓↓
SELECT attr1, attr2, ...  
FROM table1
LIMIT offset, count;

** 说明: **

offset是起始行数(也称之为偏移量),count是要返回的行数。
  • 列如,选取表table1的前5条记录,可以使用以下语句↓↓↓
SELECT * FROM table1 LIMIT 0, 5;
  • 取从第3条记录开始的10条记录,可以使用以下语句↓↓↓
SELECT * FROM table1 LIMIT 3, 10;

Oracle:

  • ROWNUM是一个伪列,用于标识查询结果集中的每一行,从1开始,并在每一行中递增。语法如下↓↓↓
SELECT *  
FROM (  
  SELECT rownum rn, attr1, attr2, ...  
  FROM table  
)  
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、偏移量相同,数据量不同

语句:

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;

执行结果:

bfdc74f694914667818eb0edffead2d1.png

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

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

语句:

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;

执行结果:

a4ee7d6a2e304d46a160832146f84ba6.png

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

3、优化查询

3.1 数据量过大问题

语句:

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;

执行结果:

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 采用子查询方式

语句:

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,再查询数据↓↓↓

执行结果:

f7a37f80842941aa8fa304da99af9c7d.png

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

3.2.2 EXPLAIN分析sql 执行计划

语句:

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;

执行结果:

13d798f1ea6c45da93974fdd8c01065f.png

ab58b7435ce54887a97540c77c23244e.png

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

PRIMARY

的索引。**)继续↓↓↓


3.2.3 加索引

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

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

1aef2b7a9dc243abbc928d6244d05d6e.png

执行语句:

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;

执行结果:

af59f33a5b434ec08a6cfe9b2462b72c.png

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

再次分析执行计划:

d9948ae38c97494db2989f21b4698015.png

13623cbf7376423d990791cea2205949.png

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

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

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

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

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

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

8fea40468a3b41d69d678f38555f10b7.png

来查看执行计划:

1090d252c38f4d5a883ba4c0e4f2603c.png

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

子查询会带来以下问题:

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

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

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

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

标签: mysql 数据库

本文转载自: https://blog.csdn.net/weixin_50002038/article/details/130805409
版权归原作者 你才是臭弟弟 所有, 如有侵权,请联系我们删除。

“MySQL千万级数据优化方案”的评论:

还没有评论