資料庫索引總結(一)-有解無憂

為什么要使用索引？

通過創建唯一性索引，可以保證資料庫表中每一行資料的唯一性，
可以大大加快資料的檢索速度（大大減少的檢索的資料量）, 這也是創建索引的最主要的原因，
幫助服務器避免排序和臨時表，
將隨機IO變為順序IO
可以加速表和表之間的連接，特別是在實作資料的參考完整性方面特別有意義，

索引這么多優點，為什么不對表中的每一個列創建一個索引呢？

當對表中的資料進行增加、洗掉和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度，
索引需要占物理空間，除了資料表占資料空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大，
創建索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加，

使用索引的注意事項？

在經常需要搜索的列上，可以加快搜索的速度；
在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度，
在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
對于中到大型表索引都是非常有效的，但是特大型表的話維護開銷會很大，不適合建索引
在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
避免 where 子句中對欄位施加函式，這會造成無法命中索引，
在使用InnoDB時使用與業務無關的自增主鍵作為主鍵，即使用邏輯主鍵，而不要使用業務主鍵，
~~將打算加索引的列設定為 NOT NULL ，否則將導致引擎放棄使用索引而進行全表掃描，~~

訂正，來自issue758 ，將某一列設定為default null，where 是可以走索引，另外索引列是否設定 null 是不影響性能的， 但是，還是不建議列上允許為空，最好限制not null，因為null需要更多的存盤空間并且null值無法參與某些運算，

《高性能MySQL》第四章如是說：And, in case you’re wondering, allowing NULL values in the index really doesn’t impact performance ，NULL 值的索引查找流程參考：https://juejin.im/post/5d5defc2518825591523a1db ,相關閱讀：MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！，
洗掉長期未使用的索引，不用的索引的存在會造成不必要的性能損耗 MySQL 5.7 可以通過查詢 sys 庫的 chema_unused_indexes 視圖來查詢哪些索引從未被使用
在使用 limit offset 查詢緩慢時，可以借助索引來提高性能

Mysql索引主要使用的兩種資料結構

哈希索引

對于哈希索引來說，底層的資料結構就是哈希表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引，

BTree索引

MyISAM和InnoDB實作BTree索引方式的區別

MyISAM

B+Tree葉節點的data域存放的是資料記錄的地址，在索引檢索的時候，首先按照B+Tree搜索演算法搜索索引，如果指定的Key存在，則取出其 data 域的值，然后以 data 域的值為地址讀取相應的資料記錄，這被稱為“非聚簇索引”，

InnoDB

其資料檔案本身就是索引檔案，相比MyISAM，索引檔案和資料檔案是分離的，其表資料檔案本身就是按B+Tree組織的一個索引結構，樹的葉節點data域保存了完整的資料記錄，這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引，這被稱為“聚簇索引（或聚集索引）”，而其余的索引都作為輔助索引，輔助索引的data域存盤相應記錄主鍵的值而不是地址，這也是和MyISAM不同的地方，在根據主索引搜索時，直接找到key所在的節點即可取出資料；在根據輔助索引查找時，則需要先取出主鍵的值，在走一遍主索引，因此，在設計表的時候，不建議使用過長的欄位作為主鍵，也不建議使用非單調的欄位作為主鍵，這樣會造成主索引頻繁分裂， PS：整理自《Java工程師修煉之道》

覆寫索引介紹

什么是覆寫索引

如果一個索引包含（或者說覆寫）所有需要查詢的欄位的值，我們就稱之為“覆寫索引”，我們知道InnoDB存盤引擎中，如果不是主鍵索引，葉子節點存盤的是主鍵+列值，最侄訓是要“回表”，也就是要通過主鍵再查找一次，這樣就會比較慢覆寫索引就是把要查詢出的列和索引是對應的，不做回表操作！

覆寫索引使用實體

現在我創建了索引(username,age)，我們執行下面的 sql 陳述句

select username , age from user where username = 'Java' and age = 22

在查詢資料的時候：要查詢出的列在葉子節點都存在！所以，就不用回表，

選擇索引和撰寫利用這些索引的查詢的3個原則

單行訪問是很慢的，特別是在機械硬碟存盤中(SSD的隨機I/O要快很多，不過這一點仍然成立），如果服務器從存盤中讀取一個資料塊只是為了獲取其中一行，那么就浪費了很多作業，最好讀取的塊中能包含盡可能多所需要的行，使用索引可以創建位置引，用以提升效率，
按順序訪問范圍資料是很快的，這有兩個原因，第一，順序 I/O 不需要多次磁盤尋道，所以比隨機I/O要快很多（特別是對機械硬碟），第二，如果服務器能夠按需要順序讀取資料，那么就不再需要額外的排序操作，并且GROUPBY查詢也無須再做排序和將行按組進行聚合計算了，
索引覆寫查詢是很快的，如果一個索引包含了查詢需要的所有列，那么存盤引擎就不需要再回表查找行，這避免了大量的單行訪問，而上面的第1點已經寫明單行訪問是很慢的，

為什么索引能提高查詢速度

以下內容整理自：掘金
地址：資料庫兩大神器【索引和鎖】
作者：Java3y

先從 MySQL 的基本存盤結構說起

MySQL的基本存盤結構是頁(記錄都存在頁里邊)：

各個資料頁可以組成一個雙向鏈表
每個資料頁中的記錄又可以組成一個單向鏈表
- 每個資料頁都會為存盤在它里邊兒的記錄生成一個頁目錄，在通過主鍵查找某條記錄的時候可以在頁目錄中使用二分法快速定位到對應的槽，然后再遍歷該槽對應分組中的記錄即可快速找到指定的記錄
- 以其他列(非主鍵)作為搜索條件：只能從最小記錄開始依次遍歷單鏈表中的每條記錄，

所以說，如果我們寫select * from user where indexname = 'xxx'這樣沒有進行任何優化的sql陳述句，默認會這樣做：

定位到記錄所在的頁：需要遍歷雙向鏈表，找到所在的頁
從所在的頁內中查找相應的記錄：由于不是根據主鍵查詢，只能遍歷所在頁的單鏈表了

很明顯，在資料量很大的情況下這樣查找會很慢！這樣的時間復雜度為O（n），

使用索引之后

索引做了些什么可以讓我們查詢加快速度呢？其實就是將無序的資料變成有序(相對)：

要找到id為8的記錄簡要步驟：

很明顯的是：沒有用索引我們是需要遍歷雙向鏈表來定位對應的頁，現在通過 “目錄” 就可以很快地定位到對應的頁上了！（二分查找，時間復雜度近似為O(logn)）

其實底層結構就是B+樹，B+樹作為樹的一種實作，能夠讓我們很快地查找出對應的記錄，

關于索引其他重要的內容補充

以下內容整理自：《Java工程師修煉之道》

最左前綴原則

MySQL中的索引可以以一定順序參考多列，這種索引叫作聯合索引，如User表的name和city加聯合索引就是(name,city)，而最左前綴原則指的是，如果查詢的時候查詢條件精確匹配索引的左邊連續一列或幾列，則此列就可以被用到，如下：

select * from user where name=xx and city=xx ; --可以命中索引
select * from user where name=xx ; -- 可以命中索引
select * from user where city=xx ;  --無法命中索引

這里需要注意的是，查詢的時候如果兩個條件都用上了，但是順序不同，如 city= xx and name ＝xx，那么現在的查詢引擎會自動優化為匹配聯合索引的順序，這樣是能夠命中索引的，

由于最左前綴原則，在創建聯合索引時，索引欄位的順序需要考慮欄位值去重之后的個數，較多的放前面，ORDER BY子句也遵循此規則，

注意避免冗余索引

冗余索引指的是索引的功能相同，能夠命中就肯定能命中，那么就是冗余索引如（name,city ）和（name ）這兩個索引就是冗余索引，能夠命中后者的查詢肯定是能夠命中前者的在大多數情況下，都應該盡量擴展已有的索引而不是創建新索引，

MySQL 5.7 版本后，可以通過查詢 sys 庫的 schema_redundant_indexes 表來查看冗余索引

Mysql如何為表欄位添加索引？？？

1.添加PRIMARY KEY（主鍵索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

作者：Snailclimb
鏈接：資料庫索引總結 1
來源：github

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/224914.html

標籤：其他

上一篇：資料庫索引總結(一)

下一篇：SQL 陳述句中 where 條件后寫上1=1 是什么意思