mysql 百萬(wàn)級(jí)更新性能

chenjintao_ii

ne_elementparam 表有400多萬(wàn)條記錄，我們的業(yè)務(wù)場(chǎng)景是這400萬(wàn)條數(shù)據(jù)每2小時(shí)更新一遍，我們有兩種方法更新記錄。

第一種方法是每次更新一條記錄，重復(fù)400萬(wàn)次。更新一條的 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)

第二種方法是先把記錄插到臨時(shí)表 tt_elementparam 中，插完400萬(wàn)條后，再一次性更新到 ne_elementparam 表中。下面是更新數(shù)據(jù)的 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，說(shuō)明第二種方法效率更高。
我想問(wèn)有沒(méi)有人是用第二種方法來(lái)更新數(shù)據(jù)的？我實(shí)在想不通第二種方法怎么能提高效率啊~~

王楠w_n

謝謝分享

seesea2517

謝謝分享，比較少想到第二種方式，不知道是不是和表的索引結(jié)構(gòu)有關(guān)系。

action08

臨時(shí)表跑在內(nèi)存，性能剛剛的

chenjintao_ii

回復(fù) 2# 王楠w_n

第二種方法中，不知道是不是 MySQL 在做批量更新的時(shí)候?qū)π阅苓M(jìn)行優(yōu)化了。因?yàn)榘次业睦斫�，臨時(shí)表有100百萬(wàn)數(shù)據(jù)需要更新到 elementparam 表時(shí)，過(guò)程應(yīng)該是這樣的：臨時(shí)表拿到一條數(shù)據(jù) -> 從 elementparam 表的100百萬(wàn)數(shù)據(jù)中查到對(duì)應(yīng)的數(shù)據(jù) -> 更新數(shù)據(jù)。此流程走100百萬(wàn)遍。

而第一種方法，是“從 elementparam 表的100百萬(wàn)數(shù)據(jù)中查到對(duì)應(yīng)的數(shù)據(jù) -> 更新數(shù)據(jù)。此流程走100百萬(wàn)遍”，少一步去臨時(shí)表拿數(shù)據(jù)的過(guò)程（也少了插入臨時(shí)表的過(guò)程）。

理論上來(lái)說(shuō)第一種方法比第二種更高效，但真實(shí)環(huán)境效果相反了。

chenjintao_ii

回復(fù) 4# action08

多謝指點(diǎn)，剛看了下臨時(shí)表的屬性，我現(xiàn)在的臨時(shí)表只是業(yè)務(wù)上的臨時(shí)，數(shù)據(jù)還是會(huì)寫磁盤的。替換成 MySQL 的臨時(shí)表，估計(jì)效率會(huì)有一定提升。