【思維導圖-索引篇】搞定資料庫索引就是這麼簡單

資料庫 · 發表 2018-10-11 13:49:38

摘要： MySQL的基本儲存結構是頁(記錄都存在頁裡邊)：各個資料頁可以組成一個雙向連結串列每個資料頁中的記錄又可以組成一個單向連結串列每個資料頁都會為儲存在它裡邊兒的記錄生成一個頁目錄，在通過主鍵查詢某條記錄的...

SQL/">MySQL的基本儲存結構是頁(記錄都存在頁裡邊)：

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

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

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

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

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

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

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

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

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

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

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 ，那麼現在的查詢引擎會自動優化為匹配聯合索引的順序，這樣是能夠命中索引的.

由於最左字首原則，在建立聯合索引時，索引欄位的順序需要考慮欄位值去重之後的個數，較多的放前面。ORDERBY子句也遵循此規則。

冗餘索引指的是索引的功能相同，能夠命中就肯定能命中，那麼就是冗餘索引如（name,city ）和（name ）這兩個索引就是冗餘索引，能夠命中後者的查詢肯定是能夠命中前者的在大多數情況下，都應該儘量擴充套件已有的索引而不是建立新索引。

MySQLS.7 版本後，可以通過查詢 sys 庫的 schemal_r dundant_indexes 表來檢視冗餘索引

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` )
複製程式碼