大家好莺褒,我是程序員啊粥。
今天給大家分享一個(gè)我遇到過(guò)的比較少見(jiàn)的面試題雪情,那就是 MySQL 中如何計(jì)算一個(gè)索引的長(zhǎng)度遵岩。
說(shuō)實(shí)話,我第一次遇到這個(gè)問(wèn)題的時(shí)候想當(dāng)然的以為索引長(zhǎng)度就是我們建表時(shí)定義的字段長(zhǎng)度巡通,如果是聯(lián)合索引尘执,那就是多個(gè)字段長(zhǎng)度相加。
事實(shí)證明宴凉,在我說(shuō)出上述回答之后誊锭,面試官就讓我?guī)е?jiǎn)歷跑路了。
于是乎弥锄,我仔細(xì)查閱了相關(guān)資料丧靡,發(fā)現(xiàn)索引長(zhǎng)度這個(gè)計(jì)算方式還是有點(diǎn)意思的蟆沫,索性給大家分享一下。
首先温治,我們要知道 MySQL Innodb 引擎對(duì)于索引的長(zhǎng)度是有限制的饭庞,最大為 767 字節(jié)。
你采用不同的字符編碼方式罐盔,對(duì)應(yīng)的字節(jié)數(shù)是不一樣的但绕,比如我們最常用的 utf8mb4 字符集是 4 字節(jié)字符集,則 767 字節(jié) / 4 字節(jié)每字符 = 191字符(默認(rèn)索引最大長(zhǎng)度)惶看,所以在 varchar(255) 或 char(255) 類型字段上創(chuàng)建索引會(huì)失敗捏顺,報(bào)錯(cuò)會(huì)提示最大索引長(zhǎng)度為 767 字節(jié)。
其他的一些編碼方式對(duì)應(yīng)關(guān)系分別是:latin1 編碼的纬黎,一個(gè)字符占用一個(gè)字節(jié)幅骄,gbk 編碼的,一個(gè)字符占用兩個(gè)字節(jié)本今,utf8 編碼的拆座,一個(gè)字符占用三個(gè)字節(jié)。
總的來(lái)說(shuō)冠息,索引長(zhǎng)度計(jì)算規(guī)則為:
所有的索引字段挪凑,如果沒(méi)有設(shè)置 Not Null,則需要加一個(gè)字節(jié)(這也是我們?yōu)槭裁唇ㄗh建表時(shí)不要有 Null 字段的原因之一)逛艰。
對(duì)于定長(zhǎng)字段躏碳,int 類型占四個(gè)字節(jié)、date 占三個(gè)字節(jié)散怖、char(n) 占 N 個(gè)字符菇绵。
對(duì)于變成字段 varchar(n),則是 N 個(gè)字符 + 兩個(gè)字節(jié)镇眷。
不同的字符集咬最,一個(gè)字符占用的字節(jié)數(shù)不同。latin1編碼的欠动,一個(gè)字符占用一個(gè)字節(jié)永乌,gbk編碼的,一個(gè)字符占用兩個(gè)字節(jié)具伍,utf8編碼的铆遭,一個(gè)字符占用三個(gè)字節(jié)。
索引長(zhǎng)度 char()沿猜、varchar() 索引長(zhǎng)度的計(jì)算公式:
Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列長(zhǎng)度 + 1(允許 Null) + 2(變長(zhǎng)列)
基于以上原則枚荣,我們建張表來(lái)驗(yàn)證下:
CREATE TABLE `tb_item` (
`id` bigint NOT NULL COMMENT '書籍id,同時(shí)也是書籍編號(hào)',
`title` varchar(100) NOT NULL COMMENT '書籍名稱',
`sell_point` varchar(500) DEFAULT NULL COMMENT '書籍賣點(diǎn)',
`price` bigint NOT NULL COMMENT '書籍價(jià)格啼肩,單位為:分',
`num` int NOT NULL COMMENT '庫(kù)存數(shù)量',
`barcode` varchar(30) DEFAULT NULL COMMENT '書籍條形碼',
`image` varchar(500) DEFAULT NULL COMMENT '書籍圖片',
`cid` bigint NOT NULL COMMENT '所屬類目橄妆,葉子類目',
`status` tinyint NOT NULL DEFAULT '1' COMMENT '書籍狀態(tài)衙伶,1-正常,2-下架害碾,3-刪除',
`created` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
`updated` datetime NOT NULL COMMENT '更新時(shí)間',
`upload_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cid` (`cid`),
KEY `status` (`status`),
KEY `updated` (`updated`),
KEY `tb_item_title_price_num` (`title`,`price`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='書籍表';
在這張表上我們建立了聯(lián)合索引 tb_item_title_price_num
矢劲,由三個(gè)字段組成,分別是變長(zhǎng)字段 varchar慌随,定長(zhǎng)字段 price 以及 num芬沉。
我們先來(lái)執(zhí)行如下這條語(yǔ)句:
explain select * from tb_item where title = '編譯原理' and price = 45 and num = 23232
;
我們看到 key 是 tb_item_title_price_num
,同時(shí)索引長(zhǎng)度 key_len 為 314 阁猜,證明是使用到了聯(lián)合索引 tb_item_title_price_num
的三個(gè)完整字段的丸逸。
這個(gè) 314 的具體計(jì)算方式為:
字符集我們建表時(shí)用的為 utf8 編碼,所以 title 字段的索引長(zhǎng)度是 3 * 100 + 0 + 2 = 302剃袍,price 字段的索引長(zhǎng)度是 8黄刚,num 字段的索引長(zhǎng)度是 4。
因此 tb_item_title_price_num 索引總共長(zhǎng)度是 302 + 8 + 4 = 314民效。
為了驗(yàn)證我們的計(jì)算方式憔维,接下來(lái)我們?cè)賵?zhí)行如下語(yǔ)句,使得索引部分失效畏邢。
explain select * from tb_item where title = '編譯原理' and num = 23232
這條語(yǔ)句因?yàn)槲覀兲^(guò)了 price 字段业扒,所以聯(lián)合索引中只會(huì)有 title 字段生效,剩余部分都會(huì)失效舒萎,如果我們計(jì)算方式?jīng)]有問(wèn)題的話程储,那么此時(shí)執(zhí)行計(jì)劃中的 key_len 應(yīng)該為 302。
果然逆甜,我們看到 key_len 已經(jīng)變成了 302虱肄,這意味著索引部分失效了致板,只有 title 字段索引起了作用交煞,同時(shí) Extra 為 Using index condition
,說(shuō)明使用了索引斟或,但是需要回表查詢數(shù)據(jù)素征。
具體 explain 執(zhí)行計(jì)劃中其他字段的具體含義,參考這篇文章萝挤。
好了御毅,我們今天的內(nèi)容就到這里了。
簡(jiǎn)單總結(jié)一下怜珍,在 MySQL 中端蛆,索引長(zhǎng)度不僅取決于我們建表時(shí)設(shè)置的字段長(zhǎng)度,還和具體的字符集編碼以及字段是否允許為 Null 等多個(gè)條件相關(guān)酥泛,字段長(zhǎng)度只能作為索引長(zhǎng)度的預(yù)估項(xiàng)今豆,而不是準(zhǔn)確值嫌拣。