MySQL online ddl擴(kuò)展字段長(zhǎng)度
點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了?。。?/strong>
從MySQL5.5開(kāi)始支持online ddl，5.6版本更加完善，一直到MySQL8.0版本，支持了更多alter table語(yǔ)句通過(guò)online ddl快速的執(zhí)行，在執(zhí)行效率、降低鎖表時(shí)間以及IO消耗上有了很大的提升。在日常運(yùn)維MySQL時(shí)，ddl執(zhí)行的最多的大概就是擴(kuò)展varchar字段長(zhǎng)度的情況，但是面對(duì)一定數(shù)據(jù)量的表，在執(zhí)行時(shí)間上有時(shí)很快，有時(shí)執(zhí)行起來(lái)很慢，這是什么原因？
首先，name字段長(zhǎng)度由30擴(kuò)展至50。再把name字段長(zhǎng)度由50擴(kuò)展至90。可以看到兩次ddl的執(zhí)行時(shí)間差異很大， 造成差異的原因是什么呢？通過(guò)MySQL官方文檔得知，這跟varchar的字段所占用的字節(jié)數(shù)有關(guān)。
- 當(dāng)占用字節(jié)數(shù)為0-255字節(jié)時(shí)（注意是字節(jié)，不是字符），需要用一個(gè)字節(jié)記錄字段的長(zhǎng)度；
- 當(dāng)占用256及以上的字節(jié)時(shí)，需要用兩個(gè)字節(jié)記錄字段的長(zhǎng)度；
- 添加字段長(zhǎng)度時(shí)，如果添加前后長(zhǎng)度都在0-255范圍內(nèi)，或者大于等于256范圍內(nèi)，可以通過(guò)inplace的算法進(jìn)行online ddl，如果是從0-255范圍跨到大于等于256 ，就不支持online ddl，只能通過(guò)copy的算法進(jìn)行ddl，所以執(zhí)行較慢。MySQL會(huì)自動(dòng)根據(jù)是否支持online ddl自動(dòng)處理，所以才會(huì)出現(xiàn)擴(kuò)展varchar字段長(zhǎng)度執(zhí)行有時(shí)很快，有時(shí)很慢的情況。
通過(guò)上面的例子分析，varchar(30) 占用了30個(gè)字符，字符集為utf8mb4每個(gè)字符4個(gè)字節(jié)，共占用120個(gè)字節(jié)，varchar(50) 占用了200個(gè)字節(jié)，而varchar(90)占用了360個(gè)字節(jié)，varchar(30)到varchar(50)不涉及跨字節(jié)范圍，而從varchar(50)到varchar(90)則涉及到跨字節(jié)范圍，所以后者不能通過(guò)online ddl執(zhí)行語(yǔ)句，只能通過(guò)原始的方式去copy表執(zhí)行，速度較慢。通過(guò)顯示指定inplace的方式，更加明顯的看出，當(dāng)涉及到跨字節(jié)范圍時(shí)，MySQL會(huì)報(bào)錯(cuò)提示使用copy的方式執(zhí)行alter table語(yǔ)句。通過(guò)使用copy的方式跨話字節(jié)范圍后，再次指定inplace 的方式，又可以進(jìn)行online ddl了。- 在創(chuàng)建表時(shí)，如果字段的字節(jié)長(zhǎng)度不會(huì)超過(guò)256字節(jié)，那么建議varchar的字節(jié)長(zhǎng)度創(chuàng)建時(shí)小于256字節(jié)；
- 如果字符串的長(zhǎng)度超過(guò)200字節(jié)，那么varchar的字節(jié)長(zhǎng)度建議超過(guò)256字節(jié)。
依據(jù)本例，varchar(30)為占用120字節(jié)，如果確定不會(huì)超過(guò)256字節(jié)，可以根據(jù)實(shí)際需求創(chuàng)建小于等于varchar(63);如果預(yù)估數(shù)據(jù)可能會(huì)超過(guò)256字節(jié)，就不要?jiǎng)?chuàng)建低于varchar(64)的，這樣方便以后字段擴(kuò)展進(jìn)行使用online ddl。當(dāng)然，不建議為了方便online ddl操作，將所有的字段長(zhǎng)度都超過(guò)256字節(jié)，因?yàn)镸ySQL在加載內(nèi)存臨時(shí)表的時(shí)候，會(huì)將字段定義的所有的長(zhǎng)度加載到內(nèi)存中，而不是實(shí)際的長(zhǎng)度，同樣又會(huì)造成資源消耗。
本文作者：吳 昊(上海新炬中北團(tuán)隊(duì)）
本文來(lái)源：“IT那活兒”公眾號(hào)
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129105.html
