1、什么是索引？
索引是一種數(shù)據(jù)結(jié)構(gòu)
索引的目的在于提高查詢效率
2、索引的原理
通過不斷的縮小數(shù)據(jù)查找的范圍篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)的事件變成順序的事件，也就是我們總是通過同一種查找方式來(lái)鎖定數(shù)據(jù)
通俗點(diǎn)說(shuō)，我們拿出一本新華字典，它的目錄實(shí)際上就是一種索引：非聚集索引，我們可以通過目錄迅速定位我們要查的字。而字典的內(nèi)容部分一般都是按照拼音排序的，這實(shí)際上又是一種索引：聚集索引。
3、索引的優(yōu)點(diǎn)與劣勢(shì)
3.1、創(chuàng)建索引的好處：
- 1、加快經(jīng)常被搜索字段的搜索速度。
- 2、利用索引的唯一性來(lái)控制記錄的唯一性
- 3、等
3.2、創(chuàng)建索引的壞處：
- 1、額外的存儲(chǔ)空間（單列索引占原表5%至15%空間，想象一下如果為一個(gè)表創(chuàng)建三四個(gè)索引）
- 2、額外的創(chuàng)建和維護(hù)時(shí)間：執(zhí)行數(shù)據(jù)修改操作(INSERT、UPDATE、DELETE)產(chǎn)生索引維護(hù)
4、索引的數(shù)據(jù)結(jié)構(gòu)
一般來(lái)說(shuō)，索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗，相對(duì)于內(nèi)存存取，I/O存取的消耗要高幾個(gè)數(shù)量級(jí)，每次查找數(shù)據(jù)時(shí)把磁盤IO次數(shù)控制在一個(gè)很小的數(shù)量級(jí)，最好是常數(shù)數(shù)量級(jí)。那么我們就想到如果一個(gè)高度可控的多路搜索樹是否能滿足需求呢？就這樣，b+樹應(yīng)運(yùn)而生
5、Btree數(shù)據(jù)結(jié)構(gòu)
使用B-tree結(jié)構(gòu)可以顯著減少定位記錄時(shí)所經(jīng)歷的中間過程，從而加快存取速度。而B+tree是B-tree的一個(gè)變種，大名鼎鼎的MySQL就普遍使用B+tree實(shí)現(xiàn)其索引結(jié)構(gòu)。
6、Btree的查找過程
首先從根節(jié)點(diǎn)進(jìn)行二分查找，如果找到則返回對(duì)應(yīng)節(jié)點(diǎn)的data，否則對(duì)相應(yīng)區(qū)間的指針指向的節(jié)點(diǎn)遞歸進(jìn)行查找，直到找到節(jié)點(diǎn)或找到null指針，前者查找成功，后者查找失敗。
真實(shí)的情況是，3層的b+樹可以表示上百萬(wàn)的數(shù)據(jù)，如果上百萬(wàn)的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬(wàn)次的IO，顯然成本非常非常高
7、聯(lián)合索引
當(dāng)b+樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候，b+數(shù)是按照從左到右的順序來(lái)建立搜索樹的，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來(lái)檢索的時(shí)候，b+樹會(huì)優(yōu)先比較name來(lái)確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來(lái)的時(shí)候，b+樹就不知道下一步該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來(lái)搜索才能知道下一步去哪里查詢。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來(lái)檢索時(shí)，b+樹可以用name來(lái)指定搜索方向，但下一個(gè)字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性。
8、最左前綴匹配
多列索引還有另外一個(gè)優(yōu)點(diǎn)，它通過稱為最左前綴（Leftmost Prefixing）的概念體現(xiàn)出來(lái)。繼續(xù)考慮前面的例子，現(xiàn)在我們有一個(gè)firstname、lastname、age列上的多列索引，我們稱這個(gè)索引為fname_lname_age。當(dāng)搜索條件是以下各種列的組合時(shí)，MySQL將使用fname_lname_age索引：
- 1、firstname，lastname，age
- 2、firstname，lastname
- 3、firstname
從另一方面理解，它相當(dāng)于我們創(chuàng)建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的索引
9、建索引的幾大原則
- 1.最左前綴匹配原則，非常重要的原則，mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。
- 2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式
- 3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會(huì)問，這個(gè)比例有什么經(jīng)驗(yàn)值嗎？使用場(chǎng)景不同，這個(gè)值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄
- 4.索引列不能參與計(jì)算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡(jiǎn)單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語(yǔ)句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);
- 5.盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來(lái)的索引即可
10、索引選擇與前綴索引
索引雖然加快了查詢速度，但索引也是有代價(jià)的：索引文件本身要消耗存儲(chǔ)空間，同時(shí)索引會(huì)加重插入、刪除和修改記錄時(shí)的負(fù)擔(dān)，另外，MySQL在運(yùn)行時(shí)也要消耗資源維護(hù)索引，因此索引并不是越多越好。
第一種情況是表記錄比較少，例如一兩千條甚至只有幾百條記錄的表，沒必要建索引，讓查詢做全表掃描就好了。2000為分界線
另一種不建議建索引的情況是索引的選擇性較低。所謂索引的選擇性（Selectivity），是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T
11、用不用索引
索引至少會(huì)帶來(lái)如下的overhead
額外的創(chuàng)建時(shí)間
額外的存儲(chǔ)空間（單列索引占原表5%至15%空間，想象一下如果為一個(gè)表創(chuàng)建三四個(gè)索引）
額外的維護(hù)時(shí)間
這一切只為了一個(gè)目的：減少在small range query的情況下的query time。如果是大range query 返回1%以上的數(shù)據(jù)，這情況下索引的意義不大
12、選用哪種索引
low selectivity情況下 比如parent column為性別，bitmap index什么的可以考慮
high selectivity可以用btree什么的來(lái)維持二分查找的查找效率
13、公司小貸系統(tǒng)優(yōu)化
- 1、部分like模糊匹配，只要是涉及到前后都進(jìn)行%通配符模糊查詢的都修改成全值匹配
- 2、將一些or并行查詢改成in
- 3、count統(tǒng)計(jì)可以考慮去除
- 4、關(guān)于進(jìn)件列表查詢的一些條件選擇，可以統(tǒng)計(jì)業(yè)務(wù)人員最常用的一些組合是哪些，然后將這些組合建立聯(lián)合索引，根據(jù)最左匹配規(guī)則
- 5、根據(jù)索引的選擇性優(yōu)化部分索引（基數(shù)判斷，status字段）
- 6、explain分析工具
- 7、用in或者exists來(lái)替換or
- 8、用union來(lái)替換or（適用于索引列）
- 9、列類型為字符串類型，查詢時(shí)沒有用單引號(hào)引起來(lái)
- 10、在where查詢語(yǔ)句中對(duì)字段進(jìn)行NULL值判斷
- 11、在where查詢語(yǔ)句中使用表達(dá)式
- 12、先limit再進(jìn)行join連接查詢，還是先join再limit
14、例子說(shuō)明
Status這個(gè)索引效果不是很大
我們可以考慮給USERNAME這個(gè)字段建一個(gè)索引
另外，我們還可以調(diào)查統(tǒng)計(jì)業(yè)務(wù)人員的操作習(xí)慣，哪些查詢條件是常用的，然后可以考慮給這些條件一起建一個(gè)組合索引（需要考慮最左匹配原則，where條件后面）
索引優(yōu)化之前的延時(shí)統(tǒng)計(jì)
索引優(yōu)化之后的延時(shí)統(tǒng)計(jì)
根據(jù)業(yè)務(wù)情況創(chuàng)建組合索引
查詢已處理的進(jìn)件單列表（沒有使用索引）
使用索引之后
ok，到這里已經(jīng)結(jié)束了，感謝各位的耐心！前段時(shí)間被安排了一個(gè)性能優(yōu)化的任務(wù)，經(jīng)過這幾天的學(xué)習(xí)做了一個(gè)小小的總結(jié)，希望能夠幫到大家，非常感謝！
