chenjintao_ii 发表于 2016-09-06 12:27

mysql 百万级更新性能

本帖最后由 chenjintao_ii 于 2016-09-06 12:30 编辑

ne_elementparam 表有400多万条记录，我们的业务场景是这400万条数据每2小时更新一遍，我们有两种方法更新记录。

第一种方法是每次更新一条记录，重复400万次。更新一条的 explain 解释：
mysql> explain UPDATE ne_elementparamSET epm_curvalue='', epm_updatetime = sysdate() WHERE epm_neid = 1 AND epm_objid = 2;         
+----+-------------+-----------------+-------+----------------------------+---------+---------+-------+------+-------------+
| id | select_type | table         | type| possible_keys            | key   | key_len | ref   | rows | Extra       |
+----+-------------+-----------------+-------+----------------------------+---------+---------+-------+------+-------------+
|1 | SIMPLE      | ne_elementparam | range | PRIMARY,PK_NE_ELEMENTPARAM | PRIMARY | 5       | const |    1 | Using where |
+----+-------------+-----------------+-------+----------------------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

第二种方法是先把记录插到临时表 tt_elementparam 中，插完400万条后，再一次性更新到 ne_elementparam 表中。下面是更新数据的 explain 解释：

mysql> explain UPDATE ne_elementparam ne, tt_elementparam tt SET ne.epm_curvalue=tt.epm_curvalue, ne.epm_updatetime = sysdate()WHERE ne.epm_neid = tt.epm_neid AND ne.epm_objid = tt.epm_objid AND ne.epm_neid=1;
+----+-------------+-------+--------+----------------------------+---------+---------+---------------------------+------+-------------+
| id | select_type | table | type   | possible_keys            | key   | key_len | ref                     | rows | Extra       |
+----+-------------+-------+--------+----------------------------+---------+---------+---------------------------+------+-------------+
|1 | SIMPLE      | ne    | ref    | PRIMARY,PK_NE_ELEMENTPARAM | PRIMARY | 5       | const                     |    1 | NULL      |
|1 | SIMPLE      | tt    | eq_ref | PRIMARY                  | PRIMARY | 23      | const,das_uq.ne.EPM_OBJID |    1 | Using where |
+----+-------------+-------+--------+----------------------------+---------+---------+---------------------------+------+-------------+
2 rows in set (0.00 sec)

第一种方法中，type 字段为 range，而第二种方法中，type 字段为 ref 和 eq_ref，说明第二种方法效率更高。
我想问有没有人是用第二种方法来更新数据的？我实在想不通第二种方法怎么能提高效率啊~~



王楠w_n 发表于 2016-09-06 15:50

谢谢分享

seesea2517 发表于 2016-09-06 17:14

谢谢分享，比较少想到第二种方式，不知道是不是和表的索引结构有关系。

action08 发表于 2016-09-06 17:33

临时表跑在内存，性能刚刚的

chenjintao_ii 发表于 2016-09-09 07:52

回复 2# 王楠w_n

第二种方法中，不知道是不是 MySQL 在做批量更新的时候对性能进行优化了。因为按我的理解，临时表有100百万数据需要更新到 elementparam 表时，过程应该是这样的：临时表拿到一条数据 -> 从 elementparam 表的100百万数据中查到对应的数据 -> 更新数据。此流程走100百万遍。

而第一种方法，是“从 elementparam 表的100百万数据中查到对应的数据 -> 更新数据。此流程走100百万遍”，少一步去临时表拿数据的过程（也少了插入临时表的过程）。

理论上来说第一种方法比第二种更高效，但真实环境效果相反了。

chenjintao_ii 发表于 2016-09-09 07:55

回复 4# action08

多谢指点，刚看了下临时表的属性，我现在的临时表只是业务上的临时，数据还是会写磁盘的。替换成 MySQL 的临时表，估计效率会有一定提升。
页: [1]
查看完整版本: mysql 百万级更新性能