搞定面試官 - MySQL 中你知道如何計(jì)算一個(gè)索引的長(zhǎng)度嘛亏较?

大家好莺褒,我是程序員啊粥。

今天給大家分享一個(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ī)則為:

  1. 所有的索引字段挪凑,如果沒(méi)有設(shè)置 Not Null,則需要加一個(gè)字節(jié)(這也是我們?yōu)槭裁唇ㄗh建表時(shí)不要有 Null 字段的原因之一)逛艰。

  2. 對(duì)于定長(zhǎng)字段躏碳,int 類型占四個(gè)字節(jié)、date 占三個(gè)字節(jié)散怖、char(n) 占 N 個(gè)字符菇绵。

  3. 對(duì)于變成字段 varchar(n),則是 N 個(gè)字符 + 兩個(gè)字節(jié)镇眷。

  4. 不同的字符集咬最,一個(gè)字符占用的字節(jié)數(shù)不同。latin1編碼的欠动,一個(gè)字符占用一個(gè)字節(jié)永乌,gbk編碼的,一個(gè)字符占用兩個(gè)字節(jié)具伍,utf8編碼的铆遭,一個(gè)字符占用三個(gè)字節(jié)。

  5. 索引長(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è)完整字段的丸逸。

image.png

這個(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。

image.png

果然逆甜,我們看到 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)確值嫌拣。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市呆躲,隨后出現(xiàn)的幾起案子异逐,更是在濱河造成了極大的恐慌,老刑警劉巖插掂,帶你破解...
    沈念sama閱讀 217,734評(píng)論 6 505
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件灰瞻,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡辅甥,警方通過(guò)查閱死者的電腦和手機(jī)酝润,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,931評(píng)論 3 394
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)肆氓,“玉大人袍祖,你說(shuō)我怎么就攤上這事⌒痪荆” “怎么了蕉陋?”我有些...
    開封第一講書人閱讀 164,133評(píng)論 0 354
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)拨扶。 經(jīng)常有香客問(wèn)我凳鬓,道長(zhǎng),這世上最難降的妖魔是什么患民? 我笑而不...
    開封第一講書人閱讀 58,532評(píng)論 1 293
  • 正文 為了忘掉前任缩举,我火速辦了婚禮,結(jié)果婚禮上匹颤,老公的妹妹穿的比我還像新娘仅孩。我一直安慰自己,他們只是感情好印蓖,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,585評(píng)論 6 392
  • 文/花漫 我一把揭開白布辽慕。 她就那樣靜靜地躺著,像睡著了一般赦肃。 火紅的嫁衣襯著肌膚如雪溅蛉。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 51,462評(píng)論 1 302
  • 那天他宛,我揣著相機(jī)與錄音船侧,去河邊找鬼。 笑死厅各,一個(gè)胖子當(dāng)著我的面吹牛镜撩,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播队塘,決...
    沈念sama閱讀 40,262評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼袁梗,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼卫旱!你這毒婦竟也來(lái)了?” 一聲冷哼從身側(cè)響起围段,我...
    開封第一講書人閱讀 39,153評(píng)論 0 276
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤顾翼,失蹤者是張志新(化名)和其女友劉穎,沒(méi)想到半個(gè)月后奈泪,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體适贸,經(jīng)...
    沈念sama閱讀 45,587評(píng)論 1 314
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,792評(píng)論 3 336
  • 正文 我和宋清朗相戀三年涝桅,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了拜姿。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 39,919評(píng)論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡冯遂,死狀恐怖蕊肥,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情蛤肌,我是刑警寧澤壁却,帶...
    沈念sama閱讀 35,635評(píng)論 5 345
  • 正文 年R本政府宣布,位于F島的核電站裸准,受9級(jí)特大地震影響展东,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜炒俱,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,237評(píng)論 3 329
  • 文/蒙蒙 一盐肃、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧权悟,春花似錦砸王、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,855評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至拇派,卻和暖如春荷辕,著一層夾襖步出監(jiān)牢的瞬間凿跳,已是汗流浹背件豌。 一陣腳步聲響...
    開封第一講書人閱讀 32,983評(píng)論 1 269
  • 我被黑心中介騙來(lái)泰國(guó)打工, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留控嗜,地道東北人茧彤。 一個(gè)月前我還...
    沈念sama閱讀 48,048評(píng)論 3 370
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像疆栏,于是被迫代替她去往敵國(guó)和親曾掂。 傳聞我的和親對(duì)象是個(gè)殘疾皇子惫谤,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,864評(píng)論 2 354

推薦閱讀更多精彩內(nèi)容