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

 

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配置  等等....