我們知道MySQL Innodb 對於索引長度的限制為 767 位元組,並且UTF8mb4字符集是4位元組字符集,則 767位元組 / 4位元組每字元 = 191字元(默認索引最大長度),所以在varchar(255)或char(255) 類型欄位上創建索引會失敗,提示最大索引長度為767位元組。
那麼怎麼去計算mysql資料庫索引長度呢?
先看網上一道題目,針對錶t,包含了三個欄位a、b、c,假設其默認值都非空,現創建組合索引index(a,b,c) 分析select * from t where a=1 and c=1 和select * from t where a=1 and b=1區別?
1、創建表
2、分別執行這兩條語句
3、思路
這裡可以發現,前面兩個的區別主要是在於key_len上,我的理解是:
將組合索引想成書的一級目錄、二級目錄、三級目錄,如index(a,b,c),相當於a是一級目錄,b是一級目錄下的二級目錄,c是二級目錄下的三級目錄。要使用某一目錄,必須先使用其上級目錄,除了一級目錄除外。
所以
where a=1 and c=1隻使用了一級目錄,c在三級目錄,沒有使用二級目錄,那麼三級目錄就沒法使用
where a=1 and b=1隻使用了一級目錄、二級目錄。
於是第二條查詢的key_len更大。
但是,具體key_len怎麼計算的,上面怎樣計算出是4和8的呢?
4、key_len的計算.
1.所有的索引欄位,如果沒有設置not null,則需要加一個位元組。
2.定長欄位,int佔四個位元組、date佔三個位元組、char(n)佔n個字元。
3.對於變成欄位varchar(n),則有n個字元+兩個位元組。
4.不同的字符集,一個字元佔用的位元組數不同。latin1編碼的,一個字元佔用一個位元組,gbk編碼的,一個字元佔用兩個位元組,utf8編碼的,一個字元佔用三個位元組。
5.索引長度 char()、varchar()索引長度的計算公式:
(Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列長度 + 1(允許null) + 2(變長列)
所以從上面可以得出
where a=1 and c=1而言,key_len=4
where a=1 and b=1而言,key_len=4+4=8
5、創建新的測試表t2
創建一個t2表,數據結構如下
6、計算key_len
分析key_len=4+5*1+2=11,因為欄位都是not null,int類型4個位元組,varchar(5) 佔用5個字元+2個位元組,latin1編碼的表一個字元佔1個位元組,故varchar(5) 佔用7個位元組。
因為MySQL具有查詢優化器,所以對where a=1 and c=1類型的查詢,欄位順序沒有任何影響,查詢優化器會自動優化。where c=1 and a=1會被優化成where a=1 and c=1,但是建議還是使用where a=1 and c=1吧,便於理解以及查詢緩衝。
後面會分享更多devops和DBA方面的內容,感興趣的朋友可以關注一下~
文末分享:(給大家準備了一份深入淺出MySQL文檔學習)
這是一份阿里架構師精心整理的深入淺出MySQL文檔,共分為4個部分31章。其中4個部分分別為基礎篇、開發篇、優化篇、管理維護篇。
這是一份阿里架構師精心整理的深入淺出MySQL文檔,共分為4個部分31章。
推薦閱讀: