MySql下大数据量级别（1000万+）优化查询和操作方法

ゝ一纸荒年。 2022-02-27 13:14 1993阅读 0赞

> ### MySql下大数据量级别（1000万+）优化查询和操作方法 ###

**一、【原则一】： **insert into tb (...) values(...),(...)...; 要比 insert into tb (...) values (...);insert into tb (...) values (...);...方式批量插入效率高 **【原因】：**

**这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志） 减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。**

**【例子】：  效率较高**

public static void insert() {
    	// 开时时间
    	Long begin = new Date().getTime();
    	// sql前缀
    	String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES ";
    	try {
    		// 保存sql后缀
    		StringBuffer suffix = new StringBuffer();
    		// 设置事务为非自动提交
    		conn.setAutoCommit(false);
    		// Statement st = conn.createStatement();
    		// 比起st，pst会更好些
    		PreparedStatement pst = conn.prepareStatement("");
    		// 外层循环，总提交事务次数
    		for (int i = 1; i <= 100; i++) {
    			// 第次提交步长
    			for (int j = 1; j <= 10000; j++) {
    				// 构建sql后缀
    				suffix.append("(" + j * i + ", SYSDATE(), " + i * j
    						* Math.random() + "),");
    			}
    			// 构建完整sql
    			String sql = prefix + suffix.substring(0, suffix.length() - 1);
    			// 添加执行sql
    			pst.addBatch(sql);
    			// 执行操作
    			pst.executeBatch();
    			// 提交事务
    			conn.commit();
    			// 清空上一次添加的数据
    			suffix = new StringBuffer();
    		}
    		// 头等连接
    		pst.close();
    		conn.close();
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	// 结束时间
    	Long end = new Date().getTime();
    	// 耗时
    	System.out.println("cast : " + (end - begin) / 1000 + " ms");
    }

**二、【使用事务提交方式】**

**【例子】：**START TRANSACTION;  （INSERT INTO TABLE (....) VALUES(...)）；...COMMIT;

**【原因】**：

**使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。**

**三、****【数据有序的插入】**

**【例子】： **

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1); 
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); 
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2);
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)  VALUES ('1', 'userid_1', 'content_1', 1);
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)  VALUES ('0', 'userid_0', 'content_0', 0);
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)  VALUES ('2', 'userid_2', 'content_2',2);
    
    修改成：
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); 
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1); 
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2);
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)  VALUES ('0', 'userid_0', 'content_0', 0);
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)  VALUES ('1', 'userid_1', 'content_1', 1);
    INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)  VALUES ('2', 'userid_2', 'content_2',2);

**【原因】： **

**由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最 后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的 索引定位效率会下降，数据量较大时会有频繁的磁盘操作。、**

** 【注意事项】：**  
1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max\_allowed\_packet配置可以修改，默认是1M，测试时修改为8M。  
2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb\_log\_buffer\_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。

### **【千万级+以上数据量的查询优化方案】：** ###

参考文献：[mysql 如何提高批量导入的速度][mysql]

**一、【临时表的使用】：**

**建立两张测试表：**

CREATE TABLE `department` (
      `dep_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `dep_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `out_warehouse` (
      `dep_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `out_remark` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `out_total_amount` decimal(18,2) DEFAULT NULL,
      `out_date` date DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

**这里只是模拟使用mysql中建立临时表的方法： 临时表data1    注：mysql（版本5.7）中不支持  WITH TABLE AS  语法建立临时表**

DROP TEMPORARY TABLE IF EXISTS data1;
    CREATE TEMPORARY TABLE data1 (dep_id BIGINT(20), dep_name VARCHAR(200));
    
    INSERT INTO data1 SELECT t.dep_id,t.dep_name FROM department t;
    
    SELECT * FROM out_warehouse t1 where t1.dep_id IN (SELECT dep_id FROM data1 );
    DROP TEMPORARY TABLE IF EXISTS data1;

**【使用临时表的前提条件】： **

**【优化前】：****table1 表数据100W+  table3表数据200W+**

SELECT
      b.*,
      A.value1,
      A.value2,
      A.value3,
      A.value4,
      A.value5,
      A.value6,
    FROM table1 b
      LEFT JOIN table2A
        ON b.order_no = A.order_no AND b.channel_no = A.channel_no
    WHERE 1 = 1 AND EXISTS (SELECT
        1
      FROM table3 t
      WHERE b.order_no = t.order_no AND t.ticket_no LIKE '%1792903240%')
    ORDER BY CREATE_TIME LIMIT 0, 20

**【优化后】： **

CREATE TEMPORARY TABLE tmp_order_no (ticket_order_no varchar(100));
    
    INSERT INTO tmp_order_no SELECT tp.order_no FROM t_passenger tp WHERE tp.ticket_no LIKE CONCAT('%',2903240,'%');
    
    SELECT
      b.*,
      A.value1,
      A.value2,
      A.value3,
      A.value4,
      A.value5,
      A.value6,
    FROM table1 b
      LEFT JOIN table2A
        ON b.order_no = A.order_no AND b.channel_no = A.channel_no
    WHERE 1 = 1 AND b.order_no IN (SELECT ticket_order_no FROM tmp_order_no)
    ORDER BY CREATE_TIME LIMIT 0, 20 
    
    DROP TEMPORARY TABLE IF EXISTS tmp_order_no;

**主要原因在于 exists这个部分，因为 table3有200W的数据，并且循环式和外表扫描查询，并且这里的like是不会走索引的，只能全扫描，所以慢就很明显了，由于是动态语句，并在存储过程中，所以优化就是拆解EXISTS这部分**

**主要思路就是 先从200W+ 的table3中查出来order\_no 然后把order\_no插入临时表，然后再使用in 临时表查询，减少关联扫描次数就能极大的优化查询时间**

**前提： table3中的ticket\_no 重复率非常低，200W+的数据 有200W的非重复，为什么强调这个，临时表在处理少量数据时性能很优异，（一般只在确定不能用索引的时候才使用临时表，或者在存储过程中某些固定数据使用次数非常多的时候使用临时表，其他时候我一般不建议使用）**

**【嵌套子句】：** **有博主指出多union all不花费查询时间，重点是通过优化 子句 ，通过索引或者优化子句其他方式等**

【**WITH TABLE AS  语法例子**】：

with data1 as (
    select *,
    case when 来源 like '%Touareg%' then 'Touareg'   
         when 来源 like '%X5%' then 'X5'
         when 来源 like '%Q7%' then 'Q7'
         when 来源 like '%GLE%' then 'GLE'       
    end as 车型1
        from autohome),
     
    data2
    as
    (
      
    /*下面是想定义的临时表data2,且下面的临时表是引用了上面的data1的*/
    select * from (
    select *,'国产途观'as 车型2 
    from data1
    where (来源 like '%Tiguan%' or 来源 like '%途观%') and 用户id not in (
    select 用户ID from koubeicontent
    where 发布类型 like '%口碑%' and 来源 like '%Tiguan%')
    )
    )
     
    select * from data2

**【union all 语句优化意见】： **

(方式一 - 原来的方式：) ：EXPLAIN SELECT t.dep_id, t.dep_name FROM `department` t 
    INNER JOIN
    (
    SELECT dep_id FROM out_warehouse1 
    UNION ALL
    SELECT dep_id FROM out_warehouse2
    ) a
    ON t.dep_id = a.dep_id LIMIT 0,30
    
    (方式二 - 优化方式一 )：EXPLAIN (SELECT t1.dep_id,t1.dep_name FROM department t1 INNER JOIN out_warehouse1 t2 
    ON t1.dep_id = t2.dep_id LIMIT 0,30)
    union all
    (SELECT t1.dep_id,t1.dep_name FROM department t1 INNER JOIN out_warehouse2 t3  
    ON t1.dep_id = t3.dep_id  LIMIT 0,30)
    
    
    (方式三 - 优化方式二)：EXPLAIN (SELECT t1.dep_id,t1.dep_name FROM department t1 WHERE EXISTS (SELECT dep_id FROM out_warehouse1 t2 WHERE t2.dep_id = t1.dep_id )
    LIMIT 0,30)
    UNION ALL
    (SELECT t1.dep_id,t1.dep_name FROM department t1 WHERE EXISTS (SELECT dep_id FROM out_warehouse1 t3 WHERE t3.dep_id = t1.dep_id )
    LIMIT 0,30)

**【mySql优化】：参考文献：**[MySQL的查询优化（一）][MySQL]

**（1）在MySQL中如果or语句两边的字段不是都增加了索引，那么即使一方有索引，查询仍会成为全表扫描 ；如果是不同的两个字段，我们给他们都加上索引，这里type变成了index\_merge，也就是索引合并（联合索引**）

**（2）如果是相同的字段，使用union all。union all 就是执行两条SQL语句，然后组合在一起，它的执行计划实际上是两条SQL。但是总共扫描行数是少于全表的行数的。**

**（3）谈到union all 我们也要说一下 union 。Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All。**

（4）有时候采用分组操作代替排序去重

例子：

原SQL:
    select distinct id_card 
     from (select t.id_card 
     from clspuser.crf_p2p_account_info t 
     where t.loan_amount <= 200000 and t.pay_date <= '2016-11-14' 
     union 
     select t.id_card 
     from clspuser.zh_crf_p2p_account_info t 
     where t.loan_amount <= 200000 and t.pay_date <= '2016-11-14');
    
    
    
    改造后：
     select  id_card 
      from (select t.id_card 
      from clspuser.crf_p2p_account_info t 
      where t.loan_amount <= 200000 
      union all
      select t.id_card 
      from clspuser.zh_crf_p2p_account_info t 
      where t.loan_amount <= 200000 ) a group by a.id_card

[mysql]: https://www.cnblogs.com/littlehb/p/6877148.html
[MySQL]: https://cloud.tencent.com/developer/news/319454