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

时间:2019-05-14 17:13 来源:linux.it.net.cn 作者:IT

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

(责任编辑：IT)