數(shù)據(jù)庫索引及優(yōu)化

什么是索引？
MySQL官方對索引的定義為：索引（INDEX）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結構。
索引的本質(zhì)： 數(shù)據(jù)結構

為什么要引入索引？
引入索引的目的在于提高查詢效率，就好像是字典一樣，如果要查”mysql“這個單詞，我們肯定要定位到m字母，然后從上往下找y字母,再找到剩下的sql。
如果沒有索引，那么你可能需要a-z進行全表掃描，會非常慢，當數(shù)據(jù)量少的時候適用，數(shù)據(jù)量大的時候不適用，因此很多情況下我們要避免全表掃描的發(fā)生。
所以我們需要映入更高效的機制—索引。
我們可以簡單理解為：排好序的快速查找結構。

圖書館借書就是索引很好的例子，先去圖書館電腦上查找書的位置可以理解為索引，就可以直接去找到對應位置的圖書。

在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結構，這些數(shù)據(jù)結構以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結構上實現(xiàn)高級查找算法。這種數(shù)據(jù)結構，就是索引。
一般來說索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲在磁盤上。

索引的優(yōu)缺點：
優(yōu)點：
1.類似大學圖書館建書目索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本（不需要全表掃描）
2.通過索引對數(shù)據(jù)進行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗
缺點：
1.實際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引也是要占空間的。
2.雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件
3.每次更新添加了索引列的字段，都會調(diào)整因為更新所帶來的鍵值變化后的索引信息
4.索引只提高效率的一個因素，如果你的MySQL有大量數(shù)據(jù)的表，就需要花時間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句，索引都是不停的根據(jù)業(yè)務場景不停試驗修改調(diào)整。 如：index(name) index(name,age,gender)在搜索的時候不是根據(jù)name來搜索

MySQL索引
MySQL目前主要有以下幾種索引類型：
1.普通索引 key
2.唯一索引 unique key
3.主鍵索引 primary key
4.組合索引 index(name,age,gender)
5.全文索引

MySQL的索引結構包含有：
BTree索引
Hash索引
full-text全文索引
R-Tree索引

我們平常說的索引，如果沒有特別指明，都是指B+樹結構組織的索引
InnoDB存儲引擎中的B+樹索引。要介紹B+樹索引，就不得不提二叉搜索樹，平衡查找樹和B樹這三種數(shù)據(jù)結構。B+樹就是從他們仨演化來的。

二叉查找樹：

我們?yōu)閡ser表（用戶信息表）建立了一個二叉查找樹的索引
二叉查找樹的特點就是任何節(jié)點的左子節(jié)點的鍵值都小于當前節(jié)點的鍵值，右子節(jié)點的鍵值都大于當前節(jié)點的鍵值。頂端節(jié)點我們稱為根節(jié)點，沒有子節(jié)點的節(jié)點我們稱之為葉節(jié)點。

如果我們需要查找id=12的用戶信息，利用我們創(chuàng)建的二叉查找樹索引，查找流程如下：
1.將根節(jié)點作為當前節(jié)點，把12與當前節(jié)點的鍵值10比較，12大于10，接下來我們把節(jié)點>10的右子節(jié)點作為當前節(jié)點。
2.繼續(xù)把12和當前節(jié)點的鍵值13比較，發(fā)現(xiàn)12小于13，把當前節(jié)點的左子節(jié)點作為當前節(jié)點。
3.把12和當前節(jié)點的鍵值12對比，12等于12，滿足條件，我們從當前節(jié)點中取出data，即id=12，name=xm。

平衡二叉樹：

這個時候可以看到我們的二叉查找樹變成了一個鏈表。如果我們需要查找id=17的用戶信息，我們需要查找7次，也就相當于全表掃描了。導致這個現(xiàn)象的原因其實是二叉查找樹變得不平衡了，就是高度太高了，從而導致查找效率的不穩(wěn)定。
為了解決這個問題，我們需要保證二叉查找樹一直保持平衡，就需要用到平衡二叉樹了。平衡二叉樹又稱AVL樹，在滿足二叉查找樹的特性的基礎上，要求每個節(jié)點的左右子樹的高度差不能超過1.
下面是平衡二叉樹和非平衡二叉樹的對比：

平衡二叉樹保證了樹的構造是平衡的，當我們插入或刪除數(shù)據(jù)導致不滿足平衡二叉樹不平衡時，平衡二叉樹會進行調(diào)整書上的節(jié)點來保持平衡。

B樹：
因為內(nèi)存的易失性。一般情況下，我們都會選擇將user表中的數(shù)據(jù)和索引存儲在磁盤這種外圍設備中。但是和內(nèi)存相比，從磁盤中讀取數(shù)據(jù)的速度會慢上上百倍千倍甚至萬倍，所以我們應當盡量減少從磁盤中讀取數(shù)據(jù)的次數(shù)。
如果我們用樹這種數(shù)據(jù)結構作為索引的數(shù)據(jù)結構，那我們每查找一次數(shù)據(jù)就需要從磁盤中讀取一個節(jié)點，也就是我們說的一個磁盤塊。我么都知道平衡二叉樹可是每個節(jié)點只存儲一個鍵值和數(shù)據(jù)的。那說明什么？說明每個磁盤塊僅僅存儲一個鍵值和數(shù)據(jù)！那如果我們要存儲海量的數(shù)據(jù)呢？
可以想象到二叉樹的節(jié)點會非常多，高度也會及其高，我們查找數(shù)據(jù)時也會進行很多次磁盤IO，我們查找數(shù)據(jù)的效率將會極低！

為了解決平衡二叉樹這個弊端，我們因該尋找一種單個節(jié)點可以存儲多個鍵值和數(shù)據(jù)的平衡樹。也就是我們接下來要說的B樹。B樹（Balance Tree）即為平衡樹的意思，下圖即為一棵B樹：

根節(jié)點的p1只想<17的節(jié)點，p2指向<17 <35節(jié)點，p3指向>35節(jié)點。
從上圖可以看出，B樹相對于平衡二叉樹每個節(jié)點存儲了更多的鍵值（key）和數(shù)據(jù)（data），并且每個節(jié)點擁有更多的子節(jié)點，子節(jié)點的個數(shù)一般稱為階，上述圖中的B樹為3階B樹，高度也會很低。基于這個特性，B樹查找數(shù)據(jù)讀取磁盤的次數(shù)將會很少，數(shù)據(jù)的查找也會比平衡二叉樹高很多。

假如我們要查找 id=28 的用戶信息，那么我們在上圖 B 樹中查找的流程如下:
1、先找到根節(jié)點也就是頁1(磁盤加載到內(nèi)存，此時發(fā)生一次IO)，判斷 28 在鍵值 17 和 35 之間，那么我們根據(jù)頁1中的指針 p2 找到頁3了
2、將 28 和頁 3(磁盤加載到內(nèi)存，此時發(fā)生一次IO) 中的鍵值相比較，28 在 26 和 30 之間，我們根據(jù)頁3中的指針 p2 找到頁 8(磁盤加載到內(nèi)存，此時發(fā)生一次IO)。
3、將 28 和頁 8 中的鍵值相比較，發(fā)現(xiàn)有匹配的鍵值 28，鍵值 28 對應的用戶信息為(28，bv)
共只要進行三次IO就行。

B+樹：
B+ 樹是對 B 樹的進一步優(yōu)化。讓我們先來看下 B+ 樹的結構圖:
B+樹是一個平衡的多叉樹，從根節(jié)點到每個葉子節(jié)點的高度差值不超過1，而且同層級的節(jié)點間有指針相互鏈接

真實的情況是，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO,性能提高將是巨大的IO，如果沒有索引，每個數(shù)據(jù)項都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。
根據(jù)上圖我們來看下 B+ 樹和 B 樹有什么不同：
①B+ 樹非葉子節(jié)點上是不存儲數(shù)據(jù)的，僅存儲鍵值，而 B 樹節(jié)點中不僅存儲鍵值，也會存儲數(shù)據(jù)。
之所以這么做是因為在數(shù)據(jù)庫中頁的大小是固定的，InnoDB 中頁的默認大小是 16KB。
如果不存儲數(shù)據(jù)，那么就會存儲更多的鍵值，相應的樹的階數(shù)(節(jié)點的子節(jié)點樹)就會更大，樹就會更矮更胖，如此一來我們査找數(shù)據(jù)進行磁盤的IO 次數(shù)又會再次減少，數(shù)據(jù)查詢的效率也會更快。
假設每一頁存數(shù)據(jù)可以存儲100條，非葉子節(jié)點可以存儲1000鍵值
如果B+樹只有1層，也就是只有1個用于存放用戶記錄的節(jié)點，最多能存放100條記錄。
如果B+樹有2層，最多能存放 1000 x100 =100000 條記錄:
如果B+樹有3層，最多能存放 1000 x1000x100 =100000000 條記錄。
如果B+樹有4層，最多能存放 1000 x1000x1000x100 =1000,0000.0000 條記錄。相當多的記錄!!!
一般根節(jié)點是常駐內(nèi)存的，所以一般我們查找1億數(shù)據(jù)，只需要2次磁盤IO。
②因為 B+ 樹索引的所有數(shù)據(jù)均存儲在葉子節(jié)點，而且數(shù)據(jù)是按照順序排列的,
通過上圖可以看到，在 lnnoD8 中，我們通過數(shù)據(jù)頁之間通過雙向鏈表連接以及葉子節(jié)點中數(shù)據(jù)之間通過單向鏈表連接的方式可以找到表中所有的數(shù)據(jù)。

二又樹
二分查找->二插排序樹(斜樹)->平衡二叉樹->紅黑樹->B樹(多插平衡樹)->B+樹
最終讓這個樹:更矮更胖，減少查找的次數(shù)(減少10次數(shù))，
因為查找的次數(shù)和樹的高度是相關的，數(shù)據(jù)庫表的索引是放到硬盤上的，如果樹的高度太高那么IO次數(shù)就會變多。

哈希索引(Hash索引)：
哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節(jié)點到葉子節(jié)點逐級査找，只需一次哈希算法即可立刻定位
到相應的位置，速度非?？臁?br /> 散列表(Hash table，也叫哈希表)它通過把關鍵碼值映射到表中一個位置來訪問記錄，以加快査找的速度，這個映射函數(shù)叫做散列函數(shù)，存放記錄的數(shù)組叫做散列表。
給定表M，存在函數(shù)f(key)，對任意給定的關鍵字值key，代入函數(shù)后若能得到包含該關鍵字的記錄在表中的地址，則稱表M為哈希(Hash)表，函數(shù)
f(key)為哈希(Hash)函數(shù)

通常用的處理沖突的方法:
鏈地址法:
將所有產(chǎn)生沖突的關鍵字所對應的數(shù)據(jù)全部存儲在同一個線性鏈表中。
例如有一組關鍵字為{19,14,23,01,68,20,84,27,55,11,10,79}，其哈希函數(shù)為:H(KeV)=Key MOD13(取余操作可以實現(xiàn)分組，將數(shù)據(jù)分成了13組)，使
用鏈地址法所構建的哈希表如圖 3 所示:


聚集索引 VS 非聚集索引:
在 MySQL中，B+ 樹索引按照存儲方式的不同分為聚集索引和非聚集索引。
**1.聚集索引(聚簇索引):以 InnoDB 作為存儲引擎的表，表中的數(shù)據(jù)都會有一個主鍵，即使你不創(chuàng)建主鍵，系統(tǒng)也會幫你創(chuàng)建一個隱式的主鍵。*這是因為 InnoDB 是把數(shù)據(jù)存放在 B+ 樹中的，而 B+ 樹的鍵值就是主鍵，在 B+ 樹的葉子節(jié)點中，存儲了表中所有的教據(jù)。
這種以主鍵作為 B+ 樹索引的鍵值而構建的 B+ 樹索引，我們稱之為聚集索引。
②非聚集索引(非聚簇索引):以主鍵以外的列值作為鍵值構建的 B+ 樹索引，我們稱之為非聚集索引。
覆蓋索引(Covering Index)，也可以稱為索引覆蓋:
就是select的數(shù)據(jù)列只需要從索引中就能夠取得，不必從數(shù)據(jù)表中讀取。換句話說查詢列要被所建的索引覆蓋。
建立了索引index(col1,col2,clo3)，查詢時候沒有使用select，也沒有用select col1,col2,clo3,col4,col5，而是使用select col1,col2,co3，查詢列要被所建的索引覆蓋。
當一條查詢語句符合覆蓋索引條件時，sql只需要通過索引就可以返回査詢所需要的數(shù)據(jù)，這樣避免了査到索引后再返回數(shù)據(jù)表操作(回表)，減少I/O提高效率。

在執(zhí)行CREATE TABLE語句時可以創(chuàng)建索引，也可以單獨用CREATE INDEX或ALTER TABLE來為表增加索引。
1.ALTER TABLE
ALTER TABLE用來創(chuàng)建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY(column_list)

說明：其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。索引名index_name可選，缺省時，MySQL將根據(jù)第一個索引列賦一個名稱，另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時創(chuàng)建多個索引。

2.CREATE INDEX
CREATE INDEX可對表增加普通索引或UNIQUE索引

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

說明：table_name、inadex_name和column_list具有ALTER TABLE語句中相同的含義。另外，不能用CREATE INDEX語句創(chuàng)建PRIMARY KEY索引

3.刪除索引
可利用ALTER TABLE或DROP INDEX語句來刪除索引。類似于CREATE INDEX語句來刪除索引。類似于CREATE INDEX語句，DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語句處理，語法如下。

DROP INDEX index_name ON table_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中，前兩條語句是等價的，刪除table_name中的索引index_name.
第三條語句只在刪除PRIMARY KEY索引時使用，因為一個表只可能有一個PRIMARY KEY索引，因此不需要指定索引名。如果沒有創(chuàng)建PRIMARY KEY索引，但表有一個或多個UNIQUE索引，則MySQL將刪除第一個UNIQUE索引。
如果從表中刪除了某列，則索引會受影響。對于多列組合的索引，如果刪除其中的某列，則該列也會從索引中刪除。如果刪除組成索引的所有列，則整個列將被刪除。

4.查看索引

mysql>show index from tblname
mysql>show keys from tblname

索引的使用場景 索引優(yōu)化、sql優(yōu)化

需要創(chuàng)建索引的情況
1、主鍵自動建立唯一索引 Primary Key = Unigue Key + Not Null
2、頻繁作為査詢條件的字段應該創(chuàng)建索引(銀行系統(tǒng)的銀行賬號、電信系統(tǒng)的手機號、電商系統(tǒng)商品名字)
3、查詢中與其它表關聯(lián)的字段，外鍵關系建立索引
4、查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
5、直詢中統(tǒng)計或者分組字段
6、單值/組合索引的選擇問題:在高并發(fā)下傾向創(chuàng)建組合索 index(name,age,gender)

不需要創(chuàng)建索引的情況
1、表記錄太少
經(jīng)常增刪改的表:提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件
3、頻繁更新的字段不適合創(chuàng)建索引，因為每次更新不單單是更新了記錄還會更新索引，加重了IO負擔
4、Where條件里用不到的字段不創(chuàng)建索引，如果根據(jù)銀行卡號查找就要建立索引
5、數(shù)據(jù)重復且分布平均的表字段(如果某個數(shù)據(jù)列包含許多重復的內(nèi)容，為它建立索引就沒有太大的實際效果)
舉例:
大家的國籍都是中國，固定且唯一的值，建立索引就沒有任何效果。
性別不是男就是女，數(shù)據(jù)的差異率不高，建立索引也沒有太多意義
假如一個表有10萬行記錄，字段A只有T和F兩種值，且每個值的分布概率大約為50%，那么對這種表A字段建索引一般不會提高數(shù)據(jù)庫的查詢速度。
索引的選擇性是指索引列中不同值的數(shù)目與表中記錄數(shù)的比。如果一個表中有2000條記錄，表索引列有1980個不同的值，那么這個索引的選擇性就是1980/2000=0.99。
個索引的選擇性越接近于1，這個索引的效率就越高。

SQL中的邏輯剛除和物理刪除
在實際開發(fā)中基本都會有刪除數(shù)據(jù)的需求，刪除又分為邏輯刪除和物理刪除。下面說下二者的區(qū)別:
一、所謂的邏輯刪除其實并不是真正的刪除，而是在表中將對應的是否刪除標識(deleted)或者說是狀態(tài)字段(status)做修改操作。比如0是刪除，1是未刪除。在邏輯上數(shù)據(jù)是被刪除的，但數(shù)據(jù)本身依然存在庫中。
對應的sql語句一般是這樣的:update… set status/deleted=…
這樣在做查詢操作的時候，就可根據(jù)此字段進行查詢，有刪除標識的即可不顯示
物理刪除就是真正的從數(shù)據(jù)庫中做刪除操作了，對應的sql語句為 delete…where…做物理刪除操作的數(shù)據(jù)將會不在庫中了。
二、邏輯刪除的目的：1、為了大數(shù)據(jù)分析，直接刪除就沒有數(shù)據(jù)了 2、刪除后索引維護成本高

避免索引失效
1、盡量全值匹配
index(name,age,gender)組合索引, where name=‘zhansgan’ and age=23 and gender='男”，全值匹配，使用索引效率高，當建立了索引列后，能在where條件中使用索引的盡量使用
2、遵循最佳左前綴法則：如果組合索引，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中間列。
帶頭大哥不能死，中間兄弟不能斷。
Index(name,aqe,gender)組合索引，where后面是name才會使用索引，否則都是全表掃貓，可以理解為有name這個火車頭索引就不會失效，有了這個火車頭火車就可以跑，沒有火車頭只有車廂就是全表掃描。
where name=‘zhangsan’ and age=23, where name=‘zhangsan’,name作為開頭上面的索引是有效的
where age=23,gender=‘男’ name不作為開頭索引無效，全表掃描
where name=‘zhansgan’ and gender=‘男’,中間兄弟不能斷，只用了索引的一部分name
3、不在索引列上做任何操作(計算、函數(shù)、(自動or手動)類型轉換)，會導致索引失效而轉向全表掃描
4、盡量使用覆蓋索引(只訪問索引的査詢(索引列和査詢列一致))，減少使用 select*，使用select name,age,gender
5、mysql 在使用不等于(!=或者<>)的時候無法使用索引會導致全表掃描
6、is null,is not null 也無法使用索引
7、like 如果以通配符開頭(’%abc…’)，會導致mysql索引失效，而變成全表掃描的操作
8、少用or，用它來連接時會索引失效