日本vs亚洲vs韩国一区三区_18款禁用表情包在哪儿下载_关晓彤一级做a爰片性色毛片_国产精品偷伦视频免费观看了 - 高清无码不卡视频

1. 索引優缺點

優點

●索引大大減小了服務器需要掃描的數據量

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

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

缺點

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

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

●如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。

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

1.1. 索引類型

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

2. B+樹

B+ 樹是一種樹數據結構，是一個n叉樹，每個節點通常有多個子，一顆B+樹包含根節點、內部節點和葉子節點。B+ 樹通常用于數據庫和操作系統的文件系統中。

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

2.1. 插入操作

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

葉子節點滿 Index Page滿 操作

No No 直接將記錄插入到葉子節點

Yes No 拆分葉子節點

>將中間節點放到Index Page中

Yes Yes 拆分葉子節點

拆分Index Page

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

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

2.2. 刪除操作

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

葉子節點小于填充因子 Index Page小于填充因子 操作

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

Yes No 合并葉子節點

更新Index Page

Yes Yes 合并葉子節點

更新Index Page  

合并Index Page

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

3. B+樹索引

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

3.1. 聚集索引

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

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

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

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

3.3. 索引分裂

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

Page Header 說明

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

PAGE_DIRECTION 最后插入方向: PAGE_LEFT

PAGE_N_DIRECTION 連續插入方向

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

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

3.4. 索引維護

## 創建刪除索引

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

## 更新索引統計信息

analysis table

Cardinality表示索引中唯一值的數量的估計值 特別關鍵，優化器會根據該值判斷使用使用這個索引,該基數越大說明索引的區分度越好

4. 索引使用

4.0.1. 聯合索引

指對表中多列數據進行索引。

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

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'

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

4.0.2. 覆蓋索引

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

select id from table where code = 1

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

查詢過程

執行SQL select id from table where code = 1 ，code有索引

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

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

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

更新過程

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

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

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

4.0.4. MySQL為什么會選錯索引

a. Cardinality統計值與實際嚴重不符[2]

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

解決方法

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

4.0.5. 字符串索引選擇

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

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的順序可以任意調整。

2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式。

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

4.索引列不能參與計算

5. 參考文檔

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

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

[3] MySQL技術內幕-InnoDB存儲引擎