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 如何提高批量导入的速度

一、【临时表的使用】：

建立两张测试表：

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的查询优化（一）

（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