0. 什么是索引？
在數(shù)據(jù)之外筒严，數(shù)據(jù)庫系統(tǒng)維護著一種幫助快速獲取數(shù)據(jù)的有序的數(shù)據(jù)結(jié)構(gòu)丹泉，這種數(shù)據(jù)結(jié)構(gòu)實現(xiàn)了高級查找算法，以某種方式指向數(shù)據(jù)鸭蛙。索引會影響查找和排序的效率摹恨。
一般來說索引本身也很大，在不能全部存在內(nèi)存中的情況下娶视，會以索引鍵的形式存儲在磁盤上晒哄。
一、優(yōu)劣
1. 優(yōu)勢
- 對數(shù)據(jù)進行索引
提高查找效率歇万，降低數(shù)據(jù)庫 IO 成本揩晴。 - 對數(shù)據(jù)進行排序
降低排序成本和 CPU 消耗。
2. 劣勢
- 索引實際上也是存于表中贪磺，記錄索引的字段并指向?qū)嶓w的記錄硫兰，同樣占用空間；
- 對數(shù)據(jù)進行更新（增刪改）寒锚，每次更新索引字段等信息也需要更新劫映，造成額外負(fù)擔(dān)违孝；
- 索引不是一勞永逸，而是要不斷的調(diào)整泳赋。
3. 適合使用索引的場景
- 主鍵和唯一約束都會默認(rèn)創(chuàng)建唯一索引雌桑；
- 頻繁查詢的字段適合使用索引；
- 連表查詢的關(guān)聯(lián)字段祖今，外鍵關(guān)心建立索引屿储；
- 排序字段可用索引降低消耗；
- 統(tǒng)計或分組字段適合索引疹味。
4. 不適合索引的場景
- 頻繁更新數(shù)據(jù)的字段不適合使用索引蚪黑；
- 沒用用于查找和排序的字段不適合索引；
- 記錄太少徐绑，沒必要建索引
- 重復(fù)值過多的字段不適合索引邪驮，如性別，即使建了索引也沒有實際效果
二傲茄、索引分類
- 單值索引
即一個索引對應(yīng)一個列 - 復(fù)合索引
一個索引對應(yīng)多個列 - 唯一索引
索引列須是唯一
三毅访、語法
- 創(chuàng)建索引
create index [unique] indexName on tableName(columnName(length))
#或
alter table tableName add [unique] index 【indexName】(columnName)
#indexName 索引名
#tableName 表名
#columnName 列名
#length 列的長度
- 刪除索引
drop index [indexName] on table
- 查看索引
show index from tableName
四、主要索引結(jié)構(gòu)
- B-Tree 索引
- Hash 索引
- Full-text 索引
- R-Tree 索引
五盘榨、索引分析
索引失效不僅會使查詢喻粹、排序變慢，還會使行鎖變表鎖较曼，所以一定要避免索引失效
1. where后使用索引的原則
最好能到到全值匹配
意思就是查詢能夠完全匹配索引磷斧，包括列和順序，比如：
創(chuàng)建了 idx_a_b_c捷犹，那么最好查詢的時候也能按照 a弛饭，b，c 的進行查找萍歉。最佳左前綴法則
查詢要從索引的最左側(cè)開始侣颂，并且中間不能斷，比如：
創(chuàng)建了 idx_a_b_c枪孩，那么按照 a 查詢憔晒，或者按照 a、b 查詢蔑舞，都是滿足最佳最前綴法則的拒担；
而按照 b、c 或者 a攻询、c 或者 c 查詢都違反了此原則从撼；
a、c 還好钧栖，至少還有 a 可以使用索引低零，b婆翔、c 和 c 則索引完全用不上。不要在索引列上做以下操作：計算掏婶，使用函數(shù)啃奴，類型轉(zhuǎn)換（自動或手動）
這類操作會導(dǎo)致索引失效，轉(zhuǎn)向全表掃描字符串不加單引號會導(dǎo)致索引失效
例如雄妥，varchar 類型的字段 a最蕾，where a=1 和 where a='1'查詢結(jié)果一樣，但是 a=1 會導(dǎo)致 mysql 隱式的類型轉(zhuǎn)換老厌，導(dǎo)致索引失效
用單引號揖膜，不要在用雙引號了范圍條件搜索右側(cè)都會失效
例如，創(chuàng)建 idx_a_b_c梅桩，
select * from t1 where a=1 and b>1 and c=1，這條語句中拜隧，a=1 是 ref 類型的宿百，b>1是 range 類型的，這兩個都用到了索引洪添，但是 c=1 就無法使用索引了垦页，因為 b>1 被打斷了。此時干奢，b 用到了索引痊焊，但是是用來排序，所以是 range 級別
in 也是范圍查找忿峻。不等于（!= 或 <>）會導(dǎo)致索引失效薄啥，從而全表掃描
is not null 會導(dǎo)致索引失效
盡量索引字段有 null，可以增加空的默認(rèn)值逛尚，例如''垄惧。like '%...' 或?qū)е滤饕?br> '%....' 會導(dǎo)致索引失效（索引類型變成 all，全表掃描）绰寞，但 '....%' 仍是 range 類型索引(雖然是 range到逊，但是這種比較特殊，和 >,< 不同滤钱，它不會打斷索引觉壶，也就是說，他后邊的索引還可以用)件缸。
盡量使用覆蓋索引铜靶，減少使用 select *
例如 idx_a_b_c，select a,b,c 就可以形成覆蓋索引（最好按順序停团，可以少旷坦，但不可多于a,b,c）掏熬，select * 則不行。
解決方法：利用覆蓋索引秒梅，例如：select id from t1 where a like '%123%'（id 是主鍵旗芬，有唯一索引），這個查詢類型是 index捆蜀，優(yōu)于 all疮丛。
or 會導(dǎo)致索引失效，應(yīng)少用
解決辦法：拆成多個語句辆它，將查詢結(jié)果合并即可誊薄。創(chuàng)建復(fù)合索引的時候，盡量吧過濾性好的字段放在前邊锰茉，例如：手機號姓名部門呢蔫，這樣每一個字段的篩選會過濾掉更多，使得后面的查詢更輕松
具體是全部失效還是失效一般需驗證----------------------------------------
2. order by
order by 使用索引的原則與 where 之后大部分相同飒筑，排序分為 using filesorts（文件內(nèi)排序）和 using index（索引排序）片吊，對它的優(yōu)化主要目標(biāo)就是消除 using filesorts，使用 using index协屡。
- 最佳左前綴原則
- 沒有過濾條件的 order by俏脊，是用不到索引的，會產(chǎn)生 using filesort肤晓。也就是要有 where 過濾條件爷贫，或者加 limit。
- 多個字段排序順序不同（同時存在asc补憾，desc）漫萄，也會產(chǎn)生 filesort
其中，最左前綴原則舉例：
創(chuàng)建了索引 idx_a_b_c余蟹，
order b,c #不滿足最前綴原則
where a='1' order by b,c #a是常量卷胯，不需排序，所以這個滿足左前綴原則
where a like 'a%' order by b,c #同上
order c,a,b #不滿足最前綴原則
order a asc威酒，b desc #不能使用索引窑睁，必須同升同降
但有時 using filesort 是不可避免的，而 filesort 又分為雙路排序（mysql 4.1之前）和單路排序：
雙路排序：要掃描兩次磁盤得到最終數(shù)據(jù)葵孤，先讀鹊Ｅァ（第一次）行指針和 order by 列到 buffer，并進行排序尤仍，按照排序后的虛擬列表重新從實體表中獲润锝颉（第二次）數(shù)據(jù)，需要兩次 IO，所以誕生了單路排序
單路排序：掃描一次得到數(shù)據(jù)苏遥，直接把所有查找列都讀取出來饼拍，并在 buffer 中排序，然后將 buffer 中排序好的結(jié)果輸出田炭，只需要一次 IO
從上面可以看出师抄，通常情況下，單路排序是要由于雙路排序的教硫，但仍存在特殊情況：取出數(shù)據(jù)太大叨吮，buffer 中存不下，單路排序只能每次取出 buffer 的大小的數(shù)據(jù)（創(chuàng)建 tmp 文件瞬矩，多路合并）茶鉴，如此多次操作，完成全部數(shù)據(jù)的查找景用，導(dǎo)致多次 IO涵叮，效果可能比雙路排序更糟。伞插。围肥。
解決辦法就是調(diào)整 my.cnf 配置文件中一下兩個參數(shù)：
sort_buffer_size,
max_length_for_sort_data
另外，需要什么字段就取蜂怎，不要用 select *，避免查詢結(jié)果因多余字段而過大置尔，超過buffer 大小就不好了杠步。
3. group by
group by 適用于分組，實際上是先排序榜轿，然后才分組幽歼。所以上面的 order by 的原則同樣適用于 group by。如果 group by 使用不當(dāng)谬盐，不僅會產(chǎn)生 using filesort甸私，還會有 using temporary。
除了上面的幾點飞傀，能用 where 就不要用 having皇型。
select * 的危害
1. 影響覆蓋索引
例如：有一個索引 idx_a_b_c, select a,b,c 或者 select a,b 都可以形成覆蓋索引，因為查詢字段小于等于索引的字段才能形成砸烦，一旦多于索引字段弃鸦，就會無效。select * 卻很有可能是查詢字段多于索引字段
2. 排序生成臨時表
由于 mysql 4.1 之后使用的都是單路排序（一次查詢所有查詢列到 buffer 中排序）幢痘，由于 buffer 有限唬格，一旦數(shù)據(jù)超出 buffer，就需要將數(shù)據(jù)分批存儲到新創(chuàng)建的多個臨時表中，全部查詢购岗、排序完成要進行整合汰聋，最后刪除臨時表。這一過程極其耗時喊积，select * 增加了 buffer 爆滿的風(fēng)險烹困。
msyql 優(yōu)化器
mysql優(yōu)化器，可以在查詢時對sql進行優(yōu)化注服，達(dá)到更好的查詢效果韭邓，例如 idx_a_b_c, where 中的順序是 a, c, b，這樣 sql 優(yōu)化器會對其進行優(yōu)化成 a溶弟，b女淑，c使索引得到應(yīng)用。
