九九久久久2,a在线视频,欧美另类在线观看,国产xx在线观看,久热中文字幕在线精品首页,久久精品亚洲精品国产欧美,免费在线观看视频a

MySQL索引
日期:2024年11月06日     新聞分類(lèi): 技術(shù)中心      瀏覽:423次

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

優(yōu)點(diǎn)

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

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

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

缺點(diǎn)

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

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

●如果某個(gè)數據列包含許多重復的內容,為它建立索引就沒(méi)有太大的實(shí)際效果。

●對于非常小的表,大部分情況下簡(jiǎn)單的全表掃描更高效;

1.1. 索引類(lèi)型

InnoDB支持的幾種常見(jiàn)索引類(lèi)型:B+樹(shù)索引、哈希索引(InnoDB支持的哈希索引是自適應的,InnoDB存儲引擎會(huì )根據表的使用情況自動(dòng)生成哈希索引)、全文索引

2. B+樹(shù)

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

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

2.1. 插入操作

B+樹(shù)插入必須保證葉子節點(diǎn)記錄的排序,以下這三種情況會(huì )導致不同的插入算法

葉子節點(diǎn)滿(mǎn) Index Page滿(mǎn) 操作

No No 直接將記錄插入到葉子節點(diǎn)

Yes No 拆分葉子節點(diǎn)

>將中間節點(diǎn)放到Index Page中

Yes Yes 拆分葉子節點(diǎn)

拆分Index Page

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

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

2.2. 刪除操作

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

葉子節點(diǎn)小于填充因子 Index Page小于填充因子 操作

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

Yes No 合并葉子節點(diǎn)

更新Index Page

Yes Yes 合并葉子節點(diǎn)

更新Index Page  

合并Index Page

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

3. B+樹(shù)索引

B+樹(shù)可以分為聚集索引(主鍵索引)和非聚集索引

3.1. 聚集索引

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

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

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

索引的邏輯順序與磁盤(pán)上的物理存儲順序不同。非聚集索引的鍵值在邏輯上也是連續的,但是表中的數據在存儲介質(zhì)上的物理順序是不一致的。索引的記錄節點(diǎn)有一個(gè)數據指針指向真正的數據存儲位置。

3.3. 索引分裂

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

Page Header 說(shuō)明

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

PAGE_DIRECTION 最后插入方向: PAGE_LEFT

PAGE_N_DIRECTION 連續插入方向

●隨機插入 取頁(yè)的中間記錄作為分裂點(diǎn)

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

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

## 更新索引統計信息

analysis table

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

4. 索引使用

4.0.1. 聯(lián)合索引

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

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

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'

如何選擇:第一原則是通過(guò)調整索引可以少維護一個(gè)索引,其次要考慮索引對空間的占用

4.0.2. 覆蓋索引

直接通過(guò)索引就可以得到我們想要的數據,就是覆蓋索引。此時(shí)查詢(xún)只獲取了索引數據頁(yè),可以減少大量的IO操作

select id from table where code = 1

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

查詢(xún)過(guò)程

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

●對應普通索引來(lái)說(shuō),查找到滿(mǎn)足條件的第一條記錄,然后繼續查找下一個(gè)記錄,直到不滿(mǎn)足code = 1

●對于唯一索引,由于索引有唯一性,查找到滿(mǎn)足條件的第一條記錄,就會(huì )停止

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

更新過(guò)程

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

對于普通索引, 插入記錄時(shí),會(huì )先將更新操作寫(xiě)入change buffer (寫(xiě)緩沖)[1],等下一次查詢(xún)訪(fǎng)問(wèn)該數據頁(yè)時(shí)再執行更新操作

通過(guò)上面說(shuō)明看出,普通索引和唯一索引在查詢(xún)上沒(méi)有什么區別,主要考慮更新性能的影響,一般來(lái)說(shuō)普通索引就可以了。唯一索引一般用來(lái)做重復數據驗證

4.0.4. MySQL為什么會(huì )選錯索引

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

b. 當查詢(xún)需要返回記錄的大部分字段,索引的過(guò)濾后還需要訪(fǎng)問(wèn)表中很大一部分數據(20%左右),優(yōu)化器可能通過(guò)聚集索引查找數據,因為順序讀速度大約離散讀...

解決方法

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

4.0.5. 字符串索引選擇

字符串可以選擇添加普通索引或者前綴索引,當字符串過(guò)長(cháng)是,前綴索引可以節省索引空間,但是如果前綴的字符不夠長(cháng)時(shí),就會(huì )導致過(guò)多的回表查詢(xún),并且不能使用覆蓋索引、無(wú)法排序。

alter table SUser add index index1(email);

## 前綴索引

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

前綴長(cháng)度選擇可以使用下面方法,判斷當長(cháng)度增加到何時(shí),選擇性提升的幅度很小了。

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

4.1. 建索引的幾大原則

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

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

4.索引列不能參與計算

5. 參考文檔

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

[2] MySQL為什么有時(shí)候會(huì )選錯索引?:https://www.jianshu.com/p/e1f50ffddc29

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

版權所有: 山西科達自控股份有限公司 備案號:晉ICP備09004627號-2   

郵箱

keda@sxkeda.com

電話(huà)

400-0351-150

微信

專(zhuān)屬
客服

留言

右側導航