MySQL千万级数据优化方案

简介

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

                                                     
使用索引：确保对经常用于查询和排序的字段添加索引。不要在查询中使用SELECT *，而是明确指定需要的字段。

分区表：如果表中的数据按照时间或其他维度进行划分，可以考虑使用分区表。这有助于加快查询速度，因为MySQL可以只扫描一部分数据。

缓存：考虑使用缓存，如Redis，来存储经常查询的数据。这可以减轻数据库的负担，提高查询速度。

水平扩展：增加MySQL服务器的数量来提高处理能力。可以使用负载均衡技术将请求分配到不同的服务器上。

优化查询语句：确保查询语句简单、高效。避免使用子查询和复杂的JOIN语句。对查询结果进行分页，以减少返回的数据量。

数据库监控：定期监控数据库的性能指标，如慢查询日志、锁等待等。根据监控结果对数据库进行调优，如调整缓存大小、优化索引等。

使用索引优化器：使用MySQL自带的索引优化器来分析查询性能，并找出可以优化的字段和索引。

数据库分区：根据业务逻辑对数据库进行分区，将相关数据存储在同一个分区中。这有助于加快查询速度，减少锁等待等问题。

优化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万条数据

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 文件

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

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

导入完成

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

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)秒级的够可以吧! 毕竟是本地也正常。继续↓↓↓

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;

执行结果:

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

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;

执行结果:

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

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;

执行结果:

说明: 我相信没有人会这么干的吧! 查几十万的数据，当然这种情况也不能排查，就算有也会使用 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，再查询数据↓↓↓

执行结果:

说明: 接下来分析查看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;

执行结果:

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

PRIMARY

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

3.2.3 加索引

没有在加一层解决不了的，在加索引。(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来查看是否用了索引还是全表扫描

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基础上随便加点条件执行结果如下:

来查看执行计划:

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

子查询会带来以下问题：

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

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

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

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

标签： mysql 数据库

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

MySQL千万级数据优化方案

简介

1、创建数据

1.1、建表语句

1.2、存储过程(反例)

1.3、高效执行(正例)

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

1.5、普通分页查询

2、开始测试查询

2.1、测试语句

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

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

3、优化查询

3.1 数据量过大问题

3.2 偏移量过大问题

3.2.1 采用子查询方式

3.2.2 EXPLAIN分析sql 执行计划

3.2.3 加索引

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

发表评论

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

关于作者

overfit同步小助手

相关阅读

文章导航