当前位置: > 数据库 > MySQL >

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

时间:2019-04-04 14:47来源: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)
------分隔线----------------------------
栏目列表
推荐内容