介紹
前文已經說了explain指令的大部分參數，接着圖解： EXPLAIN 實戰-1這篇文章把explain的key_len參數分享完，接着分享最左字首原則，建立如下的表，其中name列和address列都建立了索引
CREATE TABLE `teacher` (
`id` int(10) NOT NULL,
`name` char(20) NOT NULL,
`address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_addr` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 複制
執行如下指令
explain select * from teacher where name = "張三" 複制
explain select * from teacher where address = "北京" 複制
問題來喽，這些key_len是怎麼算出來的呢？
key_len
key_len表示索引使用的位元組數，根據這個值，就可以判斷索引使用情況，特别是在組合索引的時候，判斷所有的索引字段是否都被查詢用到
字元串類型
char和varchar跟字元編碼也有密切的聯系
latin1占用一個位元組，gbk占用2個位元組，utf8占用3個位元組，utf8mb4占用4個位元組（不同字元編碼占用的存儲空間不同）
char和varchar跟字元編碼也有密切的聯系
latin1占用一個位元組，gbk占用2個位元組，utf8占用3個位元組，utf8mb4占用4個位元組（不同字元編碼占用的存儲空間不同）
字元類型-索引字段為char類型+不可為Null時
char(n)=n*(utf8mb4=4,utf8=3,gbk=2,latin1=1)
是以上面第一個列子（查詢name=張三）的key_len為20*3=60
下文中為了描述友善，編碼類型預設為utf8
字元類型-索引字段為char類型+允許為Null時
char(n)=n*3+1（允許null，是否為空的标記）
字元類型-索引字段為varchar類型+不可為Null時
varchar(n)=n*3+2（變長列，記錄目前資料存了多少）
字元類型-索引字段為varchar類型+允許為Null時
varchar(n)=n*3+1（允許null）+2（變長列）
是以上面第二個例子（查詢住址=北京）的key_len為100*3+1+2=303
整數/浮點數/時間類型的索引長度
Not Null=字段本身的長度
Null=字段本身的長度+1
再回頭看看前面的例子，是不是很容易就了解了呢？整數時間類型的小編就不再舉例，自己寫個例子就很容易就了解了
最左字首原則
查詢
主要針對組合索引，滿足如下2個條件即可滿足左字首原則
- 需要查詢的列群組合索引的列順序一緻
- 查詢不要跨列
構造資料如下，其中在name，address，country上建了聯合索引
CREATE TABLE `people` (
`name` varchar(50) NOT NULL,
`address` varchar(50) NOT NULL,
`country` varchar(50) NOT NULL,
KEY `idx_name_addr_country` (`name`,`address`,`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 複制
先簡單舉個例子，然後總結一下
explain select * from people where name = "jack"
and address = "beijing" and country = "china" 複制
type為ref，key_len為456=（50*3+2）*3，聯合索引的所有列都使用了
explain select * from people where name = "jack" 複制
type為ref，key_len為152=50*3+2，聯合索引隻使用了name列
explain select * from people where address = "beijing" 複制
type為index，并沒有走索引，簡單說一下index和ref的差別
index：這種類型表示mysql會對整個該索引進行掃描。要想用到這種類型的索引，對這個索引并無特别要求，隻要是索引，或者某個聯合索引的一部分，mysql都可能會采用index類型的方式掃描。但是呢，缺點是效率不高，mysql會從索引中的第一個資料一個個的查找到最後一個資料，直到找到符合判斷條件的某個索引。是以，上述語句會觸發索引掃描
ref：這種類型表示mysql會根據特定的算法快速查找到某個符合條件的索引，而不是會對索引中每一個資料都進行一一的掃描判斷，也就是所謂你平常了解的使用索引查詢會更快的取出資料。而要想實作這種查找，索引卻是有要求的，要實作這種能快速查找的算法，索引就要特定的資料結構。簡單說，也就是索引字段的資料必須是有序的，才能實作這種類型的查找，才能利用到索引。
總結幾個典型的例子，聯合索引為key idx_a_b_c(a,b,c)
排序
最左字首原則不僅用在查詢中，還能用在排序中。MySQL中，有兩種方式生成有序結果集：
- 通過有序索引順序掃描直接傳回有序資料
- Filesort排序，對傳回的資料進行排序
因為索引的結構是B+樹，索引中的資料是按照一定順序進行排列的，是以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using index。
所有不是通過索引直接傳回排序結果的操作都是Filesort排序，也就是說進行了額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using filesort，當出現Using filesort時對性能損耗較大，是以要盡量避免Using filesort
還是先舉2個例子，然後總結
explain select * from people order by name 複制
Extra列隻有Using index，即根據索引順序進行掃描
explain select * from people order by address 複制
Extra列有Using filesort，進行了額外排序
總結：假如說有如下聯合索引，key idx_a_b_c(a,b,c)
order by 能使用索引排序
order by a
order by a,b
order by a,b,c
order by a desc, b desc, c desc
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c 複制
order by 不能使用索引進行排序
order by b
order by c
order by b, c
order by a asc, b desc, c desc //排序不一緻
where g = const order by b,c //丢失a索引
where a = const order by c //丢失b索引
where a = const order by a,d //d不是索引的一部分
where a in (...) order by b,c //範圍查詢 複制
有時間會單開一篇文章介紹order by優化，這裡隻做個粗淺的介紹
聯合索引特性
增加開銷。建一個聯合索引(col1,col2,col3)，實際相當于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量資料的表，使用聯合索引會大大增加開銷！
覆寫索引。對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那麼MySQL可以直接通過周遊索引取得資料，而無需回表，這了很多的随機io操作。io操作，特别的随機io其實是dba主要的優化政策。是以，在真正的實際應用中，覆寫索引是主要的提升性能的優化手段之一。
效率高。索引列越多，通過索引篩選出的資料越少。有1000W條資料的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的資料，如果隻有單值索引，那麼通過該索引能篩選出1000W10%=100w條資料，然後再回表從100w條資料中找到符合col2=2 and col3= 3的資料，然後再排序，再分頁；如果是聯合索引，通過索引篩選出1000w*10%*10% *10%=1w，效率提升可想而知！