數(shù)據(jù)庫索引

數(shù)據(jù)庫索引

常見的索引數(shù)據(jù)結(jié)構(gòu)，主鍵索引和非主鍵的索引，覆蓋索引，最常見的索引原則，更改緩沖區(qū)等

一、索引

索引在MySQL中也叫鍵(Key)

目的：為了提高數(shù)據(jù)查詢的效率，書籍的目錄一樣，可以快速定位位置

優(yōu)勢：降低 IO 成本，提高效率

勢：會生成索引文件，礦石雜質(zhì)空間

1.各種索引常見數(shù)據(jù)結(jié)構(gòu)

哈希表、結(jié)果、搜索樹

哈希表是一種以鍵值（key-value）存儲數(shù)據(jù)的結(jié)構(gòu)，只要輸入即搜索的值key，就可以找到對應的值，即時間復雜度為O(1)，但是容易發(fā)生沖突，當發(fā)生沖突時，常用法、衣服法、再散列法解決

因為值序列存儲的，所以排序不是排序時間長于查詢的，是很長的查詢范圍，查詢范圍時間只能掃描全表的方式，所以哈希這種結(jié)構(gòu)只有等值的情況，和范圍查詢用于

非常列在等值和范圍查詢場景中，在等值查詢時可以使用等值查詢，查詢時間復雜度為O((N))；范圍時間可以先用二分查詢找到只要有一個數(shù)據(jù)可以查詢，必須列出最好的數(shù)據(jù)結(jié)構(gòu)，然后才能正確地更新所有數(shù)據(jù)。查詢的情況

二叉查找樹

平衡二叉樹

N叉樹

最多是讓索引的數(shù)據(jù)庫存儲不使用。為了讓查詢盡可能少地讀取二塊數(shù)據(jù)，就可以查詢到查詢過程中不存在的數(shù)據(jù)。而不是我們應該使用“N叉樹”，因為在上叉樹的性能優(yōu)勢，以及引擎中替換了二叉樹的訪問模式，這里已經(jīng)廣泛應用了。 ，“叉叉”樹中的“當前沒有數(shù)據(jù)塊的”N個DB的大小，以N個時間排列，這個大約是N個排列的N個，這棵樹高的位置是034次方個億值，這已經(jīng)117了。

B+樹

B+樹屬于N叉樹，MySQL中，InnoDB引擎使用了B+樹索引模型，所有數(shù)據(jù)都存儲在B+樹中的，每個索引都在InnoDB里面一集B+樹。

B+樹的索引從根結(jié)點開始搜索，無需進行全表掃描，而且B+樹對索引列是順序存儲的，很適合范圍查找，

2.主索引鍵和非主鍵索引

現(xiàn)在假設 ID 的表，有作業(yè)名稱和名稱，且主要有一個索引

create table `T`( ID int primary key, k int not null, name varchar(16), index(k) )engine=InnoDB;

表中R1~R5的(ID,k)分別為(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，兩棵樹的示例值示意圖變成

根據(jù)節(jié)點葉子的內(nèi)容，索引類型主分為主鍵索引和非主鍵索引

主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)。在InnoDB里，主鍵索引也被稱為簇索引（clustered index），如果語句是?select * from T where ID=500，即主鍵查詢方式，則搜索ID這只有B+樹；

非索引的葉子節(jié)點內(nèi)容是主鍵值的點。在noDB里，非主鍵索引也被稱為保存主鍵索引（二級索引），復制索引葉子結(jié)的不是指向行的物理位置的索引，替代如果索引是?select * from T where k=5，即索引方式，則需要先查詢k索引，返回的普通ID值為500，搜索一次，這個ID稱為回表語句。基于主非索引的查詢需要多掃描一棵樹索引樹，因此，在應用中應該盡量使用主鍵查詢

3.自增主鍵

B+為了維護索引頁的正常性，在插入新值的時候需要做的維護。根據(jù)B+樹的算法，當一個新的數(shù)據(jù)頁插入數(shù)據(jù)后，數(shù)據(jù)移動后，數(shù)據(jù)移動，稱為刪除頁的數(shù)據(jù)，自然會降低，會合并操作，即頁切片，即頁切片。

解決辦法：自增主鍵

自主增鍵是自增列上定義的主鍵，在建表語句中是這樣定義的：NOT NULL PRIMARY KEY AUTO_INCREMENT

新記錄的時候可以不指定 ID 值，系統(tǒng)會獲取當前的 ID 前面加了 1 個 ID 值，符合我們新記錄的記錄作為其中插入的一個事件。附加操作，不會涉及到移動到其他的情況，也不會觸發(fā)設備的操作，而有邏輯的現(xiàn)場做主鍵，則有可能有這樣的結(jié)果，記錄數(shù)據(jù)關聯(lián)成本

主鍵長度越小，普通占用的空間也小，索引，從性能和存儲空間方面考量，自增鍵是更合理的選擇

適用業(yè)務索引字段直接做主鍵的情況：只有一個索引，該索引必須是唯一的

4.索引覆蓋率

語句select * from T where k between 3 and 5的執(zhí)行流程：

在k索引樹上找到k=3的記錄，取得ID=300

再到索引樹查到ID=300的R3（第一次回表）

在k索引樹一個值k=5，取得ID=500

重新回到索引目錄樹查到ID=500的R4（第二次回表）

在k索引樹一個值k=6，不滿足條件，循環(huán)結(jié)束。

屆時，回程表可以省略兩次。而通過覆蓋索引回表

而select ID from T where k between 3 and 5只需要直接查詢這個ID，就可以查詢出查詢的值，因此，在查詢表中提供的值，不需要返回。已經(jīng)“覆蓋了”我們的查詢需求，我們稱之為覆蓋索引

5.最左邊的宗旨

創(chuàng)建一個三列的聯(lián)合索引包含(col1, col2, col3)，索引會生效于(col1)，(col1, col2)以及(col1, col2, col3)

以下示例：

SELECT * FROM tbl_name WHERE col1=val1; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col1=val1; SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; SELECT * FROM tbl_name WHERE col1=val1 AND col3=val3;

第一條和第三條與第二條和第三條的查詢結(jié)果相同

第一條和第五條包含索引的列,，不會用索引去執(zhí)行查詢，因為(col2)和(col2, col3) 不是(c ol1, col2, col3)的第四個最左邊的目錄

第六條查詢執(zhí)行(col1)的查詢

對于的第一個索引字段，用時必須是固定值，通符出現(xiàn)在右邊，select * from AAA where a like '張%'會索引；而select * from AAA where a like '%三'不會排序索引

6.各種索引類型

創(chuàng)建索引語句差異

alter table `test` add primary key (`id`); #創(chuàng)建主鍵索引 alter table `test` add unique (`title`); #創(chuàng)建唯一索引 alter table `test` add index `title`(`title`); #創(chuàng)建普通索引

各索引區(qū)別

主鍵索引：它是一種特殊的唯一索引，可選擇有空

索引：與“普通唯一索引”類似，不同索引列的值必須唯一允許包含重復值，但允許包含重復值

普通索引：最基本的索引，沒有任何限制

7.更改緩沖區(qū)

在保證不會寫入重復的情況下，從性能的角度還是考慮，應該用唯一的索引普通索引呢？

假設執(zhí)行的語句是select Id from T where k=5

第一個滿足條件的記錄索引(, 500) 5 之后，還需要查找下一個條件記錄，直到索引索引到第一個不滿足條件的記錄，因為普通的列值不是要求唯一的，所以再多一次判斷

對于索引，因為它的唯一列的值必須唯一，所以找到索引(5, 500)后會直接停止檢索

這個不同帶來的性能差距會有多少呢？

為了說明普通索引和唯一索引對更新語句性能的影響，需要先了解更改緩沖區(qū)

當需要更新一個數(shù)據(jù)時，如果直接在內(nèi)存中的數(shù)據(jù)頁中沒有內(nèi)存，中不影響數(shù)據(jù)更新的數(shù)據(jù)更新時，在這些數(shù)據(jù)更新的時候，在更新的數(shù)據(jù)頁中，如果在OODB正常操作時，如果在此操作時，就不需要這樣了從磁盤中讀入這個數(shù)據(jù)頁。在下一個查詢需要訪問這個數(shù)據(jù)頁的時候，將數(shù)據(jù)頁讀入內(nèi)存，然后更改緩沖區(qū)中與這個頁相關的操作。通過這種方式，保證這個數(shù)據(jù)邏輯的正確的名字緩沖區(qū)，實際上它是可以改變名字的緩沖區(qū)，雖然它可以叫作緩沖區(qū)。

將更改緩沖區(qū)中的操作應用到原數(shù)據(jù)頁的，得到最新結(jié)果的過程稱為合并。除了訪問這個數(shù)據(jù)頁會觸發(fā)合并外，系統(tǒng)后臺線程會定期合并。在正常關閉（shutdown）的過程中，顯然，如果將操作先在更改緩沖區(qū)更新，減少讀取記錄，執(zhí)行語句進入執(zhí)行的速度會得到明顯的提升。而且，數(shù)據(jù)讀取內(nèi)存是需要占用緩沖池的，所以這種情況還可以提高電容，提高電容占用

比如說，現(xiàn)在要插入(4,400)這個記錄，現(xiàn)在要先判斷中是否存在k=4的記錄，而這已經(jīng)必須證明數(shù)據(jù)如果都讀入到內(nèi)存頁直接更新內(nèi)存會已經(jīng)有了，那已經(jīng)需要使用更改緩沖區(qū)了

索引，不需要唯一的直接索引相同的先訪問內(nèi)存，所以可以使用change buffer，change buffer因為減少了現(xiàn)在的磁盤訪問，所以對更新的普通性能會很明顯的提升

普通索引的所有場景，使用更改緩沖區(qū)都可以加速作用嗎？

操作前面講到真正進行數(shù)據(jù)會觸發(fā)merge，寫merge之前，change buffer里面的越多，就記錄更新的時候。同時change buffer更適用于讀少的場景，因為讀時間數(shù)據(jù)觸發(fā)合并，觸發(fā)會觸發(fā)操作，即不提升頁面性能訪問

綜合前面所說，唯一和普通索引在查詢能力上的索引不明顯，主要考慮更新時的性能，其中涉及到更改緩沖區(qū)

8.另外的改變緩沖區(qū)與重做日志

假設要在表上執(zhí)行下面這個插入語句

insert into T(ID, k) values(id1, k1), (id2, k2);

假設k1的數(shù)據(jù)頁已經(jīng)在內(nèi)存(InnoDB buffer pool)中，k2的數(shù)據(jù)頁不在內(nèi)存中

血液更新語句做了如下操作：

Page1已經(jīng)在內(nèi)存，插件(id1, k1)操作直接就在內(nèi)存執(zhí)行

Page2沒有在內(nèi)存，所以先在內(nèi)存的change buffer區(qū)域記錄“往Page2插件(id2, k2)”的信息

將 1，2 步的操作記錄在重做日志中

可以看到這條語句插入了兩臺內(nèi)存，寫了一次磁盤（按順序?qū)懥艘淮稳罩荆疫€是通過重做日志

假設現(xiàn)在要執(zhí)行如下查詢語句

select * from t where k in (k1, k2);

語句發(fā)生在插入語句后不久，內(nèi)存中的數(shù)據(jù)都還在，此時和 ibdata1，redo log(_log_fileX) 就不用了，如果下圖沒有畫出這部分。

數(shù)據(jù)庫 數(shù)據(jù)結(jié)構(gòu)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。