mysql数据库百万级以上数据，如何对其进行边查询变更新
有时候我们会不小心对一个大表进行了 update，比如说写错了 where 条件......
此时，如果 kill 掉 update 线程，那回滚 undo log 需要不少时间。如果放置不管，也不知道 update 会持续多久。
那我们能知道 update 的进度么？
实验
我们先创建一个测试数据库：
快速创建一些数据：
连续执行同样的 SQL 数次，就可以快速构造千万级别的数据：
查看一下总的行数：
我们来释放一个大的 update：
然后另起一个 session，观察 performance_schema 中的信息：
可以看到，performance_schema 会列出当前 SQL 从引擎获取的行数。
等 SQL 结束后，我们看一下 update 从引擎总共获取了多少行：
可以看到该 update 从引擎总共获取的行数是表大小的两倍，那我们可以估算：update 的进度 = (rows_examined) / (2 * 表行数)
💡小贴士
information_schema.tables 中，提供了对表行数的估算，比起使用 select count(1) 的成本低很多，几乎可以忽略不计。
那么是不是所有的 update，从引擎中获取的行数都会是表大小的两倍呢？这个还是要分情况讨论的，上面的 SQL 更新了主键，如果只更新内容而不更新主键呢？我们来试验一下：
等待 update 结束，查看 row_examined，发现其刚好是表大小：
那我们怎么准确的这个倍数呢？
一种方法是靠经验：update 语句的 where 中会扫描多少行，是否修改主键，是否修改唯一键，以这些条件来估算系数。
另一种方法就是在同样结构的较小的表上试验一下，获取倍数。
这样，我们就能准确估算一个“不小心”执行的大型 update 的进度了。
绛旓細1銆佸簲灏介噺閬垮厤鍦 where 瀛愬彞涓娇鐢!=鎴栨搷浣滅锛屽惁鍒欏皢寮曟搸鏀惧純浣跨敤绱㈠紩鑰岃繘琛屽叏琛ㄦ壂鎻忋 2銆佸鏌ヨ杩涜浼樺寲锛屽簲灏介噺閬垮厤鍏ㄨ〃鎵弿锛岄鍏堝簲鑰冭檻鍦 where 鍙 order by 娑夊強鐨勫垪涓婂缓绔嬬储寮曘 3銆佸簲灏介噺閬垮厤鍦 where 瀛愬彞涓瀛楁杩涜 null 鍒ゆ柇锛屽惁鍒欏皢瀵艰嚧寮曟搸 1銆佸簲灏介噺閬垮厤鍦 where 瀛愬彞涓娇鐢!=...
绛旓細1銆佸苟涓嶆槸鎵鏈夌储寮曞鏌ヨ閮芥湁鏁,SQL鏄牴鎹〃涓鏁版嵁鏉ヨ繘琛屾煡璇紭鍖栫殑,褰撶储寮曞垪鏈夊ぇ閲忔暟鎹噸澶嶆椂,SQL鏌ヨ鍙兘涓嶄細鍘诲埄鐢ㄧ储寮,濡備竴琛ㄤ腑鏈夊瓧娈 sex,male銆乫emale鍑犱箮鍚勪竴鍗,閭ｄ箞鍗充娇鍦╯ex涓婂缓浜嗙储寮曚篃瀵规煡璇㈡晥鐜囪捣涓嶄簡浣滅敤銆 2銆佺储寮曞苟涓嶆槸瓒婂瓒婂ソ,绱㈠紩鍥虹劧鍙互鎻愰珮鐩稿簲鐨 select 鐨勬晥鐜,浣嗗悓鏃朵篃闄嶄綆浜 insert 鍙...
绛旓細鎴戜滑鍏堝垱寤轰竴涓祴璇鏁版嵁搴锛氬揩閫熷垱寤轰竴浜涙暟鎹細杩炵画鎵ц鍚屾牱鐨 SQL 鏁版锛屽氨鍙互蹇熸瀯閫犲崈涓绾у埆鐨勬暟鎹锛氭煡鐪嬩竴涓嬫荤殑琛屾暟锛氭垜浠潵閲婃斁涓涓ぇ鐨 update锛氱劧鍚庡彟璧蜂竴涓 session锛岃瀵 performance_schema 涓殑淇℃伅锛氬彲浠ョ湅鍒帮紝performance_schema 浼氬垪鍑哄綋鍓 SQL 浠庡紩鎿庤幏鍙栫殑琛屾暟銆傜瓑 SQL 缁撴潫鍚庯紝鎴戜滑鐪...
绛旓細27銆佷笌涓存椂琛ㄤ竴鏍凤紝娓告爣骞朵笉鏄笉鍙娇鐢ㄣ傚灏忓瀷鏁版嵁闆嗕娇鐢 FAST_FORWARD 娓告爣閫氬父瑕佷紭浜庡叾浠栭愯澶勭悊鏂规硶锛屽挨鍏舵槸鍦ㄥ繀椤诲紩鐢ㄥ嚑涓〃鎵嶈兘鑾峰緱鎵闇鐨勬暟鎹椂銆傚湪缁撴灉闆嗕腑鍖呮嫭鈥滃悎璁♀濈殑渚嬬▼閫氬父瑕佹瘮浣跨敤娓告爣鎵ц鐨勯熷害蹇傚鏋滃紑鍙戞椂 闂村厑璁革紝鍩轰簬娓告爣鐨勬柟娉曞拰鍩轰簬闆嗙殑鏂规硶閮藉彲浠ュ皾璇曚竴涓嬶紝鐪嬪摢涓绉嶆柟娉曠殑鏁...
绛旓細offset+limit鏂瑰紡鐨勫垎椤垫煡璇紝褰鏁版嵁琛ㄨ秴杩100w鏉¤褰曪紝鎬ц兘浼氬緢宸備富瑕佸師鍥犳槸offset limit鐨勫垎椤垫柟寮忔槸浠庡ご寮濮嬫煡璇紝鐒跺悗鑸嶅純鍓峯ffset涓褰曪紝鎵浠ffset鍋忕Щ閲忚秺澶э紝鏌ヨ閫熷害瓒婃參銆傛瘮濡: 璇荤10000鍒10019琛屽厓绱(pk鏄富閿/鍞竴閿).浣跨敤order by id鍙互鍦ㄦ煡璇㈡椂浣跨敤涓婚敭绱㈠紩銆備絾鏄繖绉嶆柟寮忓湪id涓簎uid鐨...
绛旓細浣犲ソ锛屼綘鍙互鏍规嵁鏉′欢鍘绘坊鍔犵储寮曪紝渚嬪锛氫竴銆佹墍鏈mysql绱㈠紩鍒楃被鍨嬮兘鍙互琚储寮曪紝瀵规潵鐩稿叧绫讳娇鐢ㄧ储寮曞彲浠ユ彁楂榮elect鏌ヨ鎬ц兘锛屾牴鎹甿ysql绱㈠紩鏁帮紝鍙互鏄渶澶х储寮曚笌鏈灏忕储寮曪紝姣忕瀛樺偍寮曟搸瀵规瘡涓〃鐨勮嚦灏戞敮鎸16鐨勭储寮曘傛荤储寮曢暱搴︿负256瀛楄妭銆俶ysim鍜宨nnodb瀛樺偍寮曟搸鐨勮〃榛樿鍒涘缓绱㈠紩閮芥槸btree绱㈠紩锛岀洰鍓峬ysql杩樹笉...
绛旓細绯荤粺鍐呮湁涓鍙父鎴忔棩蹇楄〃锛屾瘡鏃ヤ互鐧句竾鏉鏁版嵁澧為暱锛岃繃娈垫椂闂撮渶瑕佹寜鐓ф棩鏈熸竻鐞嗘暟鎹傚悓浜嬩娇鐢╠elete寰幆鍒犻櫎杩囦竴娆★紝鏃堕棿涔呬笉璇达紝琛ㄤ腑鐨勬暟鎹槸鍒犻櫎浜嗭紝浣嗘槸鏌ョ湅鏈嶅姟鍣ㄥ彂鐜帮紝*.idb鏂囦欢澶у皬灞呴珮涓嶄笅锛屼娇鐢╫ptimize table 琛ㄥ悕 , 浼樺寲琛ㄤ互鍚庯紝鍐呭瓨澶у皬鎭㈠姝ｅ父銆傚墠鍓嶅悗鍚庤姳璐瑰皢杩4涓皬鏃剁殑鏃堕棿銆傛晥鐜囨瘮杈冧綆锛屽伓鐒...
绛旓細MySql 杩欎釜鏁版嵁搴缁濆鏄傚悎dba绾х殑楂樻墜鍘荤帺鐨,涓鑸仛涓鐐1涓囩瘒鏂伴椈鐨勫皬鍨嬬郴缁熸庝箞鍐欓兘鍙互,鐢▁x妗嗘灦鍙互瀹炵幇蹇熷紑鍙戙傚彲鏄暟鎹噺鍒颁簡10涓,鐧句竾鑷冲崈涓,浠栫殑鎬ц兘杩樿兘閭ｄ箞楂樺悧?涓鐐瑰皬灏忕殑澶辫,鍙兘閫犳垚鏁翠釜绯荤粺鐨勬敼鍐,鐢氳嚦鏇存湰绯荤粺鏃犳硶姝ｅ父杩愯!濂戒簡,涓嶉偅涔堝搴熻瘽浜嗐傜敤浜嬪疄璇磋瘽,鐪嬩緥瀛: 鏁版嵁琛 collect ( id, ...
绛旓細鏄渶瀹夊叏蹇嵎鐨勫浠芥柟娉曘傚懡浠ょ殑浣跨敤鏂规硶鏄細mysqlhotcopy -u root -p<rootpass> db1 db2 鈥 dbn 濡傛灉闇瑕佸浠藉叏閮鏁版嵁搴锛屽彲浠ュ姞涓娾搑egexp=鈥.*鈥濆弬鏁般侻ysqlhotcopy鍛戒护鍙嚜鍔ㄥ畬鎴愭暟鎹攣瀹氬伐浣滐紝澶囦唤鏃朵笉鐢ㄥ叧闂湇鍔″櫒銆傚畠杩樺彲浠ュ埛鏂版棩蹇楋紝浣垮浠芥枃浠跺拰鏃ュ織鏂囦欢鐨勬鏌ョ偣鑳戒繚鎸佸悓姝ャ
绛旓細鐧句竾绾鏄甯歌寖鍥淬mysql鎬ц兘涓昏鑰楀湪琛ㄩ棿鏌ヨ锛屽鏋滄病鏈夋秹鍙婂涓〃鐨勬搷浣滐紝鎬ц兘涓嶄細涓嬮檷澶銆傚湪鍚屼竴寮犺〃鍐呯殑璇濓紝鍐嶅涔熸槸娌￠棶棰樼殑銆傚彟澶栵紝琛ㄥ唴涔熷彲浠ユ寚瀹氭煇瀛楁涓虹储寮曪紙鍒涘缓鏃舵寚瀹氫富閿殑璇濅細鑷姩鍒涘缓绱㈠紩锛夈傝繖鏍峰氨鍙互鍔犻熶簡銆傛彁楂樻ц兘鏈夊緢澶氭柟闈紝浣嗘渶鍩烘湰鐨勫氨鏄紝1.閫夊ソ瀛楁鐨鏁版嵁绫诲瀷锛屾瘮濡俰p鍦板潃...
