作者：胡存存

愛(ài)可生 DBA 團(tuán)隊(duì)成員，主要負(fù)責(zé) MySQL 故障處理和 SQL 審核優(yōu)化。對(duì)技術(shù)執(zhí)著，為客戶(hù)負(fù)責(zé)。

本文來(lái)源：原創(chuàng)投稿

*愛(ài)可生開(kāi)源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來(lái)源。

---

MySQL 在 5.6 引入 Online DDL 之后，在 5.7 和 8.0 版本又對(duì)這一功能進(jìn)行了大幅的優(yōu)化。尤其是在 8.0 之后，已經(jīng)實(shí)現(xiàn)了列的秒加。在 5.7 中有些 DDL 操作也實(shí)現(xiàn)了秒修改，比如修改字段的默認(rèn)值，修改列名，但是這些在工作中不是很常見(jiàn)，今天我們討論下能夠?qū)崿F(xiàn)秒修改的一種特殊情況，稍不注意可能就掉進(jìn)坑中。

在日常工作中，我們用的最多的數(shù)據(jù)類(lèi)型就是 int(bigint) 類(lèi)型和 varchar 類(lèi)型。在這兩個(gè)數(shù)據(jù)類(lèi)型中，其中 int 類(lèi)型只要不超過(guò)數(shù)據(jù)類(lèi)型允許的最大值，int(N) 類(lèi)型的中的 N 不會(huì)影響輸入的字節(jié)數(shù)，但是 varchar(N) 數(shù)據(jù)類(lèi)型中的 N，當(dāng)輸入的字符數(shù)超過(guò) N 時(shí)，就不允許輸入，這時(shí)就需要擴(kuò)容，這也是我們經(jīng)常會(huì)遇到的一種數(shù)據(jù)變更。當(dāng)出現(xiàn)這種需求，我們是采用 MySQL 的 Online DDL 直接修改還是用第三方的工具呢。我們先看下官網(wǎng)關(guān)于擴(kuò) varchar 類(lèi)型的描述：

參考文章：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html通過(guò)上圖可以發(fā)現(xiàn)，Extending VARCHAR column size 在 In Place 是 yes，Rebuilds Table 是 no，也就是在修改是在修改時(shí)不用 copy 表，那是不是這樣，我們驗(yàn)證下。我們用 sysbench 準(zhǔn)備 4 張表，單表 500w，用 4 線(xiàn)程壓數(shù)據(jù)庫(kù)，模擬業(yè)務(wù)量(數(shù)據(jù)庫(kù)版本：5.7.29)。表結(jié)構(gòu)為：root@localhost[sbtest]> show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `c1` varchar(10) NOT NULL DEFAULT 'cc', PRIMARY KEY (`id`), KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)此時(shí)啟動(dòng) sysbench，看到此時(shí)的 qps 在 5000 左右，

此時(shí)將 c1 列從 varchar(10) 擴(kuò)到 varchar(50)，我們發(fā)現(xiàn)秒修改，直接毫無(wú)感知，與官網(wǎng)上說(shuō)的一樣。

這時(shí)候字節(jié)長(zhǎng)度又不夠了，我們需要擴(kuò)到 100，這時(shí)候，我們發(fā)現(xiàn)了異常，這次不再是秒修改，而是停在了那，現(xiàn)在是不是覺(jué)得有點(diǎn)不妙，這時(shí)候，再看我們的壓測(cè)日志，

這時(shí)候業(yè)務(wù)完全被阻塞了，這時(shí)候，腦瓜子是不是嗡嗡的了。。。

到底怎么回事，我們看下官網(wǎng)怎么說(shuō)：Extending?VARCHAR?column sizeALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;The number of length bytes required by a?VARCHAR?column must remain the same. For?VARCHAR?columns of 0 to 255 bytes in size, one length byte is required to encode the value. For?VARCHAR?columns of 256 bytes in size or more, two length bytes are required. As a result, in-place?ALTER TABLE?only supports increasing?VARCHAR?column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place?ALTER TABLE?does not support increasing the size of a?VARCHAR?column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change?VARCHAR?column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place?ALTER TABLE?returns this error:ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.由上文可知，在 varchar 類(lèi)型中，當(dāng)大小在 0 到 255 字節(jié)時(shí)，需要 1 個(gè)字節(jié)編碼，當(dāng)大于 255 時(shí)，需要 2 個(gè)字節(jié)編碼。所以在相同字節(jié)編碼的長(zhǎng)度內(nèi)變更，可以用 inplace 的方式，當(dāng)字節(jié)編碼長(zhǎng)度變更時(shí)，只能用 copy 模式，這種情況下會(huì)完全阻塞表的讀寫(xiě)。這樣我們大約明白怎么回事了，但細(xì)想，又在哪里有問(wèn)題，剛才我們?cè)谧兏鼤r(shí)，從 varchar(50) 到 varchar(100)，還沒(méi)到 255，為什么會(huì)出現(xiàn) copy 表的現(xiàn)象？其實(shí)這里我們注意的是 varchar(N) 中，N 表示的是字符，而不是字節(jié)。所以，所以根據(jù)字符集不一樣，每個(gè)字符需要的字節(jié)也不一樣，所以在擴(kuò) varchar 長(zhǎng)度時(shí)，N 的范圍也不一樣。在 utf8 字符集中，一個(gè)字符需要三個(gè)字節(jié)，所以，當(dāng) N<=85 時(shí)，需要一個(gè) 1 個(gè)字節(jié)編碼，當(dāng) N>85 時(shí)，需要兩個(gè)。所以我們來(lái)驗(yàn)證下，這里我們建 3 張表：

我們?cè)诒碇胁迦霂讞l數(shù)據(jù)，如下：

我們先修改 t1(字符集為 utf8)表，如下：

如上圖，我們看到，當(dāng)字段擴(kuò)到 85 時(shí)，影響的行數(shù)還為 0，當(dāng)擴(kuò)到 86 之后，影響行數(shù)變?yōu)?3，也就是說(shuō)字符長(zhǎng)度從 85 擴(kuò)到 86，此時(shí)發(fā)生了全表的 copy。當(dāng)大于 86 之后，我們?cè)趺磾U(kuò)，也不會(huì)發(fā)生表的 copy 操作了。我們?cè)賮?lái)驗(yàn)證 utf8mb4：

因?yàn)?utf8mb4 字符集一個(gè)字節(jié)需要 4 個(gè)字符，所以，當(dāng)擴(kuò)到 63 時(shí)，影響行數(shù)為 0，當(dāng)擴(kuò)到 64，影響行數(shù)為 3，發(fā)生了表的 copy。我們進(jìn)一步用 latin1 驗(yàn)證，因?yàn)槔?1 字符集，一個(gè)字符只需要一個(gè)字節(jié)，所以，當(dāng)擴(kuò)大到 255 時(shí)，仍為 0，當(dāng)擴(kuò)大到 256 時(shí)，影響行數(shù)變?yōu)?2。

綜上，我們可以總結(jié)：

• 因?yàn)?varchar 字符集在存儲(chǔ)時(shí)除了數(shù)據(jù)，還需要存 1~2 字節(jié)的前綴，長(zhǎng)度前綴表示 varchar 中的字節(jié)數(shù)，當(dāng)字段的字節(jié)數(shù)不大于 255 時(shí)，需要 1 個(gè)字節(jié)前綴，當(dāng)大于 255 時(shí)，需要 2 個(gè)字節(jié)長(zhǎng)度。

• innodb 存儲(chǔ)引擎，在擴(kuò)大 varchar 字符長(zhǎng)度時(shí)，當(dāng)字節(jié)長(zhǎng)度在 0~255 之間，或者 256 開(kāi)始擴(kuò)大，能用到 inplace 特性，當(dāng)從 255 擴(kuò)大到 256 時(shí)，只能 copy 表。

• varchar(N) 字段類(lèi)型中的 N 表示字符，根據(jù)不同字符集，單個(gè)字符需要的字節(jié)不一樣，所以范圍也不一樣。

所以，我們?cè)谌粘９ぷ髦?#xff0c;可以制定相應(yīng)開(kāi)發(fā)規(guī)范，盡量防止這類(lèi)的事情發(fā)生。

另外，再提醒下，MySQL 5.7.20 之前的版本可能不支持(我測(cè)試的版本是 5.7.29)，生產(chǎn)使用時(shí)需要再測(cè)試下。

此外，varchar 類(lèi)型的縮小字符長(zhǎng)度和字段數(shù)據(jù)類(lèi)型更改都只能用 copy 的方式，有此需求要小心。

---

相關(guān)推薦：

技術(shù)分享 | Online DDL 工具 pt-osc

技術(shù)分享 | Online DDL 工具 gh-ost

新特性解讀 | MySQL 8.0 之原子 DDL

---

社區(qū)近期動(dòng)態(tài)

本文關(guān)鍵字：#DDL# #mysql5.7#?點(diǎn)一下“閱讀原文”了解更多資訊

總結(jié)

以上是生活随笔為你收集整理的java有趣的技术分享ppt_技术分享 | 关于 MySQL Online DDL 有趣的验证的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。