MYSQL 只能高效利用最左前綴索引， 對于不同的存儲引擎孕暇，索引的實現(xiàn)也是不同的
BTree 索引
MyISAM 和 InnoDB的索引數(shù)據(jù)結(jié)構(gòu)都是BTree索引，MyISAM在存儲索引時利用了前綴壓縮技術(shù)進行存儲，可以節(jié)省存儲空間鸠澈。MyISAM通過數(shù)據(jù)的物理位置引用被索引的行卸夕，而InnoDB則根據(jù)主鍵引用被索引的行
B-Tree 對索引列是順序組織存儲的层释。所以很適合查找 范圍內(nèi)數(shù)據(jù)
索引對多個值進行排序的依據(jù)是CREATE TABLE時中定義索引時列的順序
B-Tree索引適用于全鍵值，鍵值范圍或鍵前綴查找快集。其中鍵前綴查找只適用于根據(jù)最左前綴的查找贡羔。
B-Tree索引對如下類型的查詢有效：全值匹配，匹配最左前綴个初，匹配列前綴乖寒，匹配范圍值，精確匹配某一列并范圍匹配另外一列院溺，只訪問索引的查詢楣嘁。
B-tree索引的限制：
如果不是按照索引的最左列開始查找，則無法使用索引珍逸。
不能跳過索引中的列
如果查詢中有某個列的范圍查詢逐虚，則其右邊所有列都無法使用索引優(yōu)化查詢。
全文索引
全文索引是一種特殊類型的索引谆膳，它查找的是文本中的關(guān)鍵詞而不是直接比較索引中的值
全文索引適用的場景叭爱，有點類似于搜索引擎
- 在相同的列上同時使用全文索引并不會有沖突，全文索引匹配的操作是
MATCH AGAINST漱病，而不是普通的WHRER操作
索引的優(yōu)點
- 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量
- 索引可以幫助服務(wù)器避免排序和臨時表
- 索引可以將隨機IO變?yōu)轫樞騃O
- 索引三星評價
評價索引是否適合某查詢
第一星
索引將相關(guān)data行放到一起
第二星
索引的data行按查詢所需順序排序
第三星
索引含 查詢?nèi)苛?/p>
索引的缺陷
- 索引存儲也是需要空間的买雾，所以把曼，索引一般對于中大型的表才有使用價值
索引策略
- 不要在以 索引列為條件查詢時使用表達(dá)式：
select * from actors where action_id + 1 = 5,對于這種查詢語句，MYSQL是無法解析WHERE中的表達(dá)式漓穿，
將索引列單獨放在比較符號的一側(cè)
- 對于一些應(yīng)用場景嗤军，利用前綴索引，不僅僅可以節(jié)省索引表存儲的空間器净，而且可以加快比較的速度
- 當(dāng)出現(xiàn)服務(wù)器對多個索引做交互操作的時候（多個AND條件）型雳，通常意味著需要一個包含所有相關(guān)列的多列索引，而不是多個獨立的單列索引
- 當(dāng)不考慮分組和排序時山害，將選擇性最高的列放到索引的最前列
多列索引
多列索引又叫聯(lián)合索引纠俭，不用于多個列的單獨索引，多列索引能夠很好的適用于類似
select * from auction where auction_id = "xxx" or auction_name = "xxx"
這樣的查詢浪慌。
如果是兩個單獨索引的話冤荆，這樣的查詢會直接走全表的查詢,兩個單獨的索引排不上用場，除非查詢改成
select * from auction where acution_id = "xx" unoin all select * from auction where auction where auciton_name = "xxx" and auction_id != "xxx"
- 當(dāng)應(yīng)用中的sql語句的where 條件中出現(xiàn)大量的 多列的AND 或者OR 操作時权纤，多列索引很有可能能夠派上
- 另外索引的順序也會決定一個索引設(shè)計的好壞钓简，通常來講，將選擇性最高的索引放在第一位是經(jīng)驗方法
聚簇索引
聚簇索引中汹想，索引樹的葉級頁包含實際的數(shù)據(jù)：記錄的索引順序與物理順序相同外邓。在非聚簇索引中，葉級頁指向表中的記錄：記錄的物理順序與邏輯順序沒有必然的聯(lián)系古掏。
一般來說损话，DBMS都會以聚簇索引的形式來存儲實際的數(shù)據(jù)，它是其它二級索引的基礎(chǔ)槽唾。
聚簇索引并不是一種單獨的索引類型丧枪，而是一種數(shù)據(jù)存儲方式。InnoDB的聚簇索引實際上實在同一個結(jié)構(gòu)中保存了BTree索引和數(shù)據(jù)行
-
存儲特點：
- 聚集索引庞萍。表數(shù)據(jù)按照索引的順序來存儲的拧烦，也就是說索引項的順序與表中記錄的物理順序一致。對于聚集索引钝计，葉子結(jié)點即存儲了真實的數(shù)據(jù)行恋博，不再有另外單獨的數(shù)據(jù)頁。
- 在一張表上最多只能創(chuàng)建一個聚集索引私恬，因為真實數(shù)據(jù)的物理順序只能有一種交播。
非聚集索引。表數(shù)據(jù)存儲順序與索引順序無關(guān)践付。對于非聚集索引，葉結(jié)點包含索引字段值及指向數(shù)據(jù)頁數(shù)據(jù)行的邏輯指針缺厉，其行數(shù)量與數(shù)據(jù)表行數(shù)據(jù)量一致永高。
InnoDB默認(rèn)通過主鍵來聚集索引隧土，沒有主鍵，InnoDB會默認(rèn)選擇一個索引命爬，如果沒有索引曹傀，它會創(chuàng)建一個主鍵
聚簇索引帶來的優(yōu)勢
把先關(guān)的數(shù)據(jù)綁定在一起，減少IO的次數(shù)
使用覆蓋索引的查詢可以直接使用頁節(jié)點中的主鍵值
聚簇索引的適用范圍
1饲宛、主鍵列,該列在where子句中使用并且插入是隨機的皆愉。
2、按范圍存取的列艇抠，如pri_order > 100 and pri_order < 200幕庐。
3、在group by或order by中使用的列家淤。
4异剥、不經(jīng)常修改的列。
5絮重、在連接操作中使用的列冤寿。
覆蓋索引
- 覆蓋索引
直接在索引上保存表數(shù)據(jù)，哈希索引青伤，空間索引和全文索引都不存儲索引列的值督怜，MYSQL只能用BTree來覆蓋索引。
InnoDB的二級索引的葉子節(jié)點都包含了主鍵的值狠角，這意味著InnoDB的二級索引可以有效的利用這些額外的主鍵來覆蓋查詢
使用索引掃描來做排序
mysql 的排序
- indexsort 利用有序索引獲取有序數(shù)據(jù)
原理： 我們知道号杠，mysql的基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)是B+樹，任何的一個表都是一顆B+樹擎厢，你在表上建的索引也是一顆B+樹究流，B+樹的特別是在葉子節(jié)點上是有序，且前一個節(jié)點存在指向相鄰節(jié)點的指針动遭。那么在寫SQL中的ORDER BY語句時候芬探，若是ORDER BY的條件和返回的數(shù)據(jù)都在一顆樹上，那么就可以利用B+樹自身的特點來天然排序了厘惦，自然效率會比較高偷仿。
使用條件：
- 查詢的WHERE子句和ORDER BY子句中查詢的字段在同一顆索引樹上，
- ORDER BY 字段的順序是跟建立索引的順序是一致的宵蕉。
- 查詢的字段也在同一顆索引樹
以上三個條件必須同時滿足
2.filesort 文件排序
原理：這里的文件排序并不是字面那表示的意思酝静，利用了磁盤IO來進行排序，不過是優(yōu)化器告訴你羡玛，進行了一個排序操作别智，具體排序的地方還是內(nèi)存，相對應(yīng)的參數(shù)是sort_buffer_size 設(shè)定的大小
- filesort不一定會產(chǎn)生臨時表
- filesort 與臨時表數(shù)據(jù)寫入磁盤是沒有任何直接聯(lián)系
只有當(dāng)索引的列順序和ORDER BY 子句的順序完全一致稼稿，并且所有列的排序方向都一樣時薄榛，MYSQL才能夠使用索引來對結(jié)果排序讳窟。
如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)ORDER BY子句引用的字段全部為第一個表是敞恋，才能使用索引做排序丽啡。
ORDER BY 和WHERE 子句一樣都是需要滿足索引最左前綴的要求，即硬猫，第一個條件需要時索引列
不能用索引排序的查詢:
- 使用了不同的【排序湯相补箍，但是索引列都是正序排列的
..where rental_date = 'xx' order by column_1 desc ,column_2 asc - ORDER BY 子句引用了一個不在索引的列
- WHERE和ORDERBY 中的列無法組合成索引的最左前綴
索引和鎖
InnoDB 只有在訪問行的時才會對其加鎖（行級鎖），而索引能夠減少InnoDB訪問的次數(shù)啸蜜，從而減少鎖的數(shù)量
InnoDB在二級索引上是使用共享（讀）鎖坑雅，但訪問主鍵索引需要排它(寫)鎖，這消除了使用覆蓋索引的可能性盔性，并且霞丧，使得SELECT FOR UPDATE 比LOCK IN SHARE MODE 或費鎖定查詢要慢許多
案例與總結(jié)
考慮表上所有的選項，當(dāng)設(shè)計索引時冕香，不要只為現(xiàn)有的查詢考慮需要的那些索引蛹尝，還需要考慮對查詢進行優(yōu)化，如果發(fā)現(xiàn)某些查詢需要創(chuàng)建新索引悉尾，但是這個索引會降低另一些查詢的效率突那，那么應(yīng)該想一想是否能優(yōu)化原來的查詢。
避免多個范圍的查詢
duib
MYSQL松散索引掃描
維護索引和表
維護表的三個目的： 找到并修復(fù)損壞的表构眯，維護準(zhǔn)確的索引統(tǒng)計信息愕难，減少碎片
InooDB通過抽樣的方式來計算統(tǒng)計信息，首先隨機的讀取少量的索引頁面惫霸，然后一起為樣本計算索引的統(tǒng)計信息猫缭。可以通過innodb_stats_sample_pages 來設(shè)置樣本頁的數(shù)量壹店。設(shè)置的值更大猜丹，理論上來說可以幫助生成更準(zhǔn)確的索引信息
Btree需要隨機磁盤訪問才能定位到葉子頁，所以隨機訪問是不可避免的,如果葉子節(jié)點在物理分布上是順序而且緊密的硅卢，那么查詢的性能就會變得更好射窒。
對于表的數(shù)據(jù)存儲來說，數(shù)據(jù)存的碎片化有三種類型
- 行碎片： 一個行的數(shù)據(jù)被存儲到多個地方的多個片段中
- 行間碎片： 邏輯上循序的行将塑，在磁盤上存儲的不是順序的
- 剩余空間碎片： 剩余空間碎片是指數(shù)據(jù)頁中有大量的剩余空間脉顿，這會導(dǎo)致非要我要去讀取大量不需要的數(shù)據(jù)，從而造成浪費
結(jié)論
在選擇索引和編寫利用這些索引時点寥，有如下的三個原則：
- 單方訪問是很慢的艾疟，如果服務(wù)器從存儲中僅僅是為了獲取其中的一行，那么就浪費了很多工作
- 按順序訪問范圍數(shù)據(jù)是很快的
- 索引覆蓋的的查詢是很快的
