本帖最后由 chenjintao_ii 于 2016-09-06 12:30 编辑
ne_elementparam 表有400多万条记录,我们的业务场景是这400万条数据每2小时更新一遍,我们有两种方法更新记录。
第一种方法是每次更新一条记录,重复400万次。更新一条的 explain 解释:
mysql> explain UPDATE ne_elementparam SET 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,说明第二种方法效率更高。
我想问有没有人是用第二种方法来更新数据的?我实在想不通第二种方法怎么能提高效率啊~~
|