您现在的位置是：网站首页> 编程资料编程资料

将MySQL去重操作优化到极致的操作方法_Mysql_

2023-05-27 414人已围观

简介 将MySQL去重操作优化到极致的操作方法_Mysql_

•问题提出

源表t_source结构如下：

 item_id int, created_time datetime, modified_time datetime, item_name varchar(20), other varchar(20)

要求：

1.源表中有100万条数据，其中有50万created_time和item_name重复。
2.要把去重后的50万数据写入到目标表。
3.重复created_time和item_name的多条数据，可以保留任意一条，不做规则限制。

•实验环境

Linux虚机：CentOS release 6.4；8G物理内存（MySQL配置4G）；100G机械硬盘；双物理CPU双核，共四个处理器；MySQL 8.0.16。

•建立测试表和数据

 -- 建立源表 create table t_source ( item_id int, created_time datetime, modified_time datetime, item_name varchar(20), other varchar(20) ); -- 建立目标表 create table t_target like t_source; -- 生成100万测试数据，其中有50万created_time和item_name重复 delimiter // create procedure sp_generate_data() begin set @i := 1; while @i<=500000 do set @created_time := date_add('2017-01-01',interval @i second); set @modified_time := @created_time; set @item_name := concat('a',@i); insert into t_source values (@i,@created_time,@modified_time,@item_name,'other'); set @i:=@i+1; end while; commit; set @last_insert_id := 500000; insert into t_source select item_id + @last_insert_id, created_time, date_add(modified_time,interval @last_insert_id second), item_name, 'other' from t_source; commit; end // delimiter ; call sp_generate_data(); -- 源表没有主键或唯一性约束，有可能存在两条完全一样的数据，所以再插入一条记录模拟这种情况。 insert into t_source select * from t_source where item_id=1; 源表中有1000001条记录，去重后的目标表应该有500000条记录。 mysql> select count(*),count(distinct created_time,item_name) from t_source; +----------+----------------------------------------+ | count(*) | count(distinct created_time,item_name) | +----------+----------------------------------------+ | 1000001 | 500000 | +----------+----------------------------------------+ 1 row in set (1.92 sec)

一、巧用索引与变量

1. 无索引对比测试

（1）使用相关子查询

 truncate t_target; insert into t_target select distinct t1.* from t_source t1 where item_id in (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);

这个语句很长时间都出不来结果，只看一下执行计划吧。

 mysql> explain select distinct t1.* from t_source t1 where item_id in -> (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name); +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 1.00 | Using where | +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ 2 rows in set, 3 warnings (0.00 sec)

主查询和相关子查询都是全表扫描，一共要扫描100万*100万数据行，难怪出不来结果。

（2）使用表连接

 truncate t_target; insert into t_target select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;

这种方法用时14秒，查询计划如下：

 mysql> explain select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id; +----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using where; Using temporary | | 1 | PRIMARY |  | NULL | ref |  |  | 5 | test.t1.item_id | 10 | 100.00 | Distinct | | 2 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ 3 rows in set, 1 warning (0.00 sec)

•内层查询扫描t_source表的100万行，建立临时表，找出去重后的最小item_id，生成导出表derived2，此导出表有50万行。
•MySQL会在导出表derived2上自动创建一个item_id字段的索引auto_key0。
•外层查询也要扫描t_source表的100万行数据，在与导出表做链接时，对t_source表每行的item_id，使用auto_key0索引查找导出表中匹配的行，并在此时优化distinct操作，在找到第一个匹配的行后即停止查找同样值的动作。

（3）使用变量

 set @a:='1000-01-01 00:00:00'; set @b:=' '; set @f:=0; truncate t_target; insert into t_target select item_id,created_time,modified_time,item_name,other from (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name from (select * from t_source order by created_time,item_name) t0) t1 where f=1;

这种方法用时13秒，查询计划如下：

 mysql> explain select item_id,created_time,modified_time,item_name,other -> from -> (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name -> from -> (select * from t_source order by created_time,item_name) t0) t1 where f=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ | 1 | PRIMARY |  | NULL | ref |  |  | 4 | const | 10 | 100.00 | NULL | | 2 | DERIVED |  | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | NULL | | 3 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using filesort | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ 3 rows in set, 5 warnings (0.00 sec)

•最内层的查询扫描t_source表的100万行，并使用文件排序，生成导出表derived3。
•第二层查询要扫描derived3的100万行，生成导出表derived2，完成变量的比较和赋值，并自动创建一个导出列f上的索引auto_key0。
•最外层使用auto_key0索引扫描derived2得到去重的结果行。

与上面方法2比较，总的扫描行数不变，都是200万行。只存在一点微小的差别，这次自动生成的索引是在常量列 f 上，而表关联自动生成的索引是在item_id列上，所以查询时间几乎相同。

至此，我们还没有在源表上创建任何索引。无论使用哪种写法，要查重都需要对created_time和item_name字段进行排序，因此很自然地想到，如果在这两个字段上建立联合索引，利用索引本身有序的特性消除额外排序，从而提高查询性能。

2. 建立created_time和item_name上的联合索引对比测试

 -- 建立created_time和item_name字段的联合索引 create index idx_sort on t_source(created_time,item_name,item_id); analyze table t_source;

（1）使用相关子查询

 truncate t_target; insert into t_target select distinct t1.* from t_source t1 where item_id in (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);

本次用时19秒，查询计划如下：

 mysql> explain select distinct t1.* from t_source t1 where item_id in -> (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name); +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997281 | 100.00 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | t2 | NULL | ref | idx_sort | idx_sort | 89 | test.t1.created_time,test.t1.item_name | 2 | 100.00 | Using index | +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+ 2 rows in set, 3 warnings (0.00 sec)

•外层查询的t_source表是驱动表，需要扫描100万行。

•对于驱动表每行的item_id，通过idx_sort索引查询出两行数据。

（2）使用表连接

 truncate t_target; insert into t_target select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;

本次用时13秒，查询计划如下：

 mysql> explai
                
                

                

                                            
上一篇：监听mysql表内容变化 mysql开启binlog_Mysql_

                                                                
下一篇：navicat 8 for mysql建库的方法_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网