国产超清无码视频_中文字幕精品久久久人妻_亚洲综合久久成人AV_丰满少妇一级毛片试看一分钟

1. 索引優(yōu)缺點

優(yōu)點

●索引大大減小了服務(wù)器需要掃描的數(shù)據(jù)量

●索引可以幫助服務(wù)器避免排序和臨時表

●索引可以將隨機IO變成順序IO

缺點

●雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存索引文件。

●建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。

●如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實際效果。

●對于非常小的表，大部分情況下簡單的全表掃描更高效；

1.1. 索引類型

InnoDB支持的幾種常見索引類型：B+樹索引、哈希索引(InnoDB支持的哈希索引是自適應(yīng)的，InnoDB存儲引擎會根據(jù)表的使用情況自動生成哈希索引)、全文索引

2. B+樹

B+ 樹是一種樹數(shù)據(jù)結(jié)構(gòu)，是一個n叉樹，每個節(jié)點通常有多個子，一顆B+樹包含根節(jié)點、內(nèi)部節(jié)點和葉子節(jié)點。B+ 樹通常用于數(shù)據(jù)庫和操作系統(tǒng)的文件系統(tǒng)中。

注意：B+樹索引不能找到給定鍵值所在的行，B+樹索引能找到的只是被查找數(shù)據(jù)所在的也，然后數(shù)據(jù)庫通過將頁讀入到內(nèi)存，然后在內(nèi)存中進行查找。

2.1. 插入操作

B+樹插入必須保證葉子節(jié)點記錄的排序，以下這三種情況會導(dǎo)致不同的插入算法

葉子節(jié)點滿 Index Page滿 操作

No No 直接將記錄插入到葉子節(jié)點

Yes No 拆分葉子節(jié)點

>將中間節(jié)點放到Index Page中

Yes Yes 拆分葉子節(jié)點

拆分Index Page

Index Page的中間值放入上層Index Page

可以看出，B+樹總是保持平衡，但是為了平衡對于新插入的數(shù)據(jù)就要做大量的拆分頁，為了減少拆分操作，所以B+樹提供了類似平滑二叉樹的旋轉(zhuǎn)功能。當葉子節(jié)點滿了，但是其左右節(jié)點不滿就會將記錄移到所在頁的兄弟節(jié)點

2.2. 刪除操作

B+樹使用填充因子控制樹的刪除，50%是填充因子的最小值,也就是數(shù)據(jù)空間有一半是空閑的。

葉子節(jié)點小于填充因子 Index Page小于填充因子 操作

No No 直接將記錄從葉子節(jié)點刪除，如果該節(jié)點還是Index Page的節(jié)點，則用該節(jié)點的右節(jié)點代替

Yes No 合并葉子節(jié)點

更新Index Page

Yes Yes 合并葉子節(jié)點

更新Index Page  

合并Index Page

MySQL填充因子是頁大小的1/16，頁默認16k就是是預(yù)留1k的空間.

3. B+樹索引

B+樹可以分為聚集索引（主鍵索引）和非聚集索引

3.1. 聚集索引

索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序。但是實際上維持索引物理順序一致的成本會非常高，所以聚集索引的才能出并不是物理連續(xù)的，而是邏輯連續(xù)的。主要依靠：1. 頁之間通過雙向鏈表連接，頁是有序的 2.頁內(nèi)記錄通過雙向鏈表維護，物理存儲上并不按照主鍵順序存儲

InnoDB表是索引組織表，即表中數(shù)據(jù)按照主鍵順序存放，所以每張表只能擁有一個聚集索引。大多數(shù)情況下，查詢優(yōu)化器傾向于采用聚集索引，因為聚集索引可以直接在葉子節(jié)點上查詢到數(shù)據(jù)。

3.2. 非聚集索引/輔助索引

索引的邏輯順序與磁盤上的物理存儲順序不同。非聚集索引的鍵值在邏輯上也是連續(xù)的，但是表中的數(shù)據(jù)在存儲介質(zhì)上的物理順序是不一致的。索引的記錄節(jié)點有一個數(shù)據(jù)指針指向真正的數(shù)據(jù)存儲位置。

3.3. 索引分裂

InnoDB的Page Header保存了插入的順序信息,通過這些信息InnoDB可以決定是向左還是向右分裂

Page Header 說明

PAGE_LAST_INSERT 指向最后插入記錄的指針

PAGE_DIRECTION 最后插入方向: PAGE_LEFT

PAGE_N_DIRECTION 連續(xù)插入方向

●隨機插入 取頁的中間記錄作為分裂點

●往同一方向插入 自增插入向右分裂僅插入記錄本身

3.4. 索引維護

## 創(chuàng)建刪除索引

CREATE/DROP [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]

ON table_name (index_col_name,...)

ALTER TABLE table_name ADD/DROP [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]

## 查看表的索引

show index from table

## 更新索引統(tǒng)計信息

analysis table

Cardinality表示索引中唯一值的數(shù)量的估計值 特別關(guān)鍵，優(yōu)化器會根據(jù)該值判斷使用使用這個索引,該基數(shù)越大說明索引的區(qū)分度越好

4. 索引使用

4.0.1. 聯(lián)合索引

指對表中多列數(shù)據(jù)進行索引。

優(yōu)點:a.聯(lián)合索引可以支持最左前綴查找,建立(code,name)的聯(lián)合索引，下面這兩個SQL都可以使用這個索引，可以減少索引數(shù)量b. 聯(lián)合索引已經(jīng)對第二個字段進行了排序，可以避免多做一次排序操作

alert table table_name add INDEX `index_name` (`a`,`b`)  

select id from table where code = 1

select id from table where code = 1 and name = 'a'

如何選擇：第一原則是通過調(diào)整索引可以少維護一個索引，其次要考慮索引對空間的占用

4.0.2. 覆蓋索引

直接通過索引就可以得到我們想要的數(shù)據(jù)，就是覆蓋索引。此時查詢只獲取了索引數(shù)據(jù)頁，可以減少大量的IO操作

select id from table where code = 1

4.0.3. 普通索引和唯一索引如何選擇

查詢過程

執(zhí)行SQL select id from table where code = 1 ，code有索引

●對應(yīng)普通索引來說，查找到滿足條件的第一條記錄，然后繼續(xù)查找下一個記錄，直到不滿足code = 1

●對于唯一索引，由于索引有唯一性，查找到滿足條件的第一條記錄，就會停止

這兩者的消耗是差不多的，因為InnoDB是按頁讀取數(shù)據(jù)，當讀取code=5時，該數(shù)據(jù)頁已經(jīng)在內(nèi)存中了，只是多了一次鏈表查找

更新過程

對于唯一索引，首先需要將數(shù)據(jù)頁讀入緩存判斷唯一鍵是否沖突，此時直接將數(shù)據(jù)更新就行

對于普通索引， 插入記錄時，會先將更新操作寫入change buffer (寫緩沖)[1]，等下一次查詢訪問該數(shù)據(jù)頁時再執(zhí)行更新操作

通過上面說明看出，普通索引和唯一索引在查詢上沒有什么區(qū)別，主要考慮更新性能的影響，一般來說普通索引就可以了。唯一索引一般用來做重復(fù)數(shù)據(jù)驗證

4.0.4. MySQL為什么會選錯索引

a. Cardinality統(tǒng)計值與實際嚴重不符[2]

b. 當查詢需要返回記錄的大部分字段，索引的過濾后還需要訪問表中很大一部分數(shù)據(jù)（20%左右），優(yōu)化器可能通過聚集索引查找數(shù)據(jù)，因為順序讀速度大約離散讀...

解決方法

一種方法: 強制MySQL使用指定索引 force index第二種方法:修改語句引導(dǎo)MySQL使用預(yù)期的索引第三種方法：新建一個更符合的索引，或者將誤用的索引刪除

4.0.5. 字符串索引選擇

字符串可以選擇添加普通索引或者前綴索引，當字符串過長是，前綴索引可以節(jié)省索引空間，但是如果前綴的字符不夠長時，就會導(dǎo)致過多的回表查詢，并且不能使用覆蓋索引、無法排序。

alter table SUser add index index1(email);

## 前綴索引

alter table SUser add index index2(email(6));

前綴長度選擇可以使用下面方法，判斷當長度增加到何時，選擇性提升的幅度很小了。

select count(distinct left(code,3))/count(*),count(distinct left(code,4))/count(*) from table

4.1. 建索引的幾大原則

1.最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、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)化器會幫你優(yōu)化成索引可以識別的形式。

3.盡量選擇區(qū)分度高的列作為索引

4.索引列不能參與計算

5. 參考文檔

[1] 寫緩沖(change buffer)，這次徹底懂了：https://www.sohu.com/a/322957463_178889

[2] MySQL為什么有時候會選錯索引？：https://www.jianshu.com/p/e1f50ffddc29

[3] MySQL技術(shù)內(nèi)幕-InnoDB存儲引擎