?一、聚集索引與輔助索引

在數據庫中，B+樹的高度一般都在2~4層，這也就是說查找某一個鍵值的行記錄時最多只需要2到4次IO，這倒不錯。因為當前一般的機械硬盤每秒至少可以做100次IO，2~4次的IO意味著查詢時間只需要0.02~0.04秒。

數據庫中的B+樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index），

聚集索引與輔助索引相同的是：不管是聚集索引還是輔助索引，其內部都是B+樹的形式，即高度是平衡的，葉子結點存放著所有的數據。

聚集索引與輔助索引不同的是：葉子結點存放的是否是一整行的信息

1、聚集索引

?

#InnoDB存儲引擎表示索引組織表，即表中數據按照主鍵順序存放。而聚集索引（clustered index）就是按照每張表的主鍵構造一棵B+樹，同時葉子結點存放的即為整張表的行記錄數據，也將聚集索引的葉子結點稱為數據頁。聚集索引的這個特性決定了索引組織表中數據也是索引的一部分。同B+樹數據結構一樣，每個數據頁都通過一個雙向鏈表來進行鏈接。#如果未定義主鍵，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚簇索引。#如果沒有這樣的列，InnoDB就自己產生一個這樣的ID值，它有六個字節，而且是隱藏的，使其作為聚簇索引。#由于實際的數據頁只能按照一棵B+樹進行排序，因此每張表只能擁有一個聚集索引。在多少情況下，查詢優化器傾向于采用聚集索引。因為聚集索引能夠在B+樹索引的葉子節點上直接找到數據。此外由于定義了數據的邏輯順序，聚集索引能夠特別快地訪問針對范圍值得查詢。

?

?

?聚集索引的好處之一：它對主鍵的排序查找和范圍查找速度非常快，葉子節點的數據就是用戶所要查詢的數據。如用戶需要查找一張表，查詢最后的10位用戶信息，由于B+樹索引是雙向鏈表，所以用戶可以快速找到最后一個數據頁，并取出10條記錄

?

#參照第六小結測試索引的準備階段來創建出表s1 mysql> desc s1; #最開始沒有主鍵 +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(6) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.00 sec)mysql> explain select * from s1 order by id desc limit 10; #Using filesort，需要二次排序 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2633472 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ row in set, 1 warning (0.11 sec)mysql> alter table s1 add primary key(id); #添加主鍵 Query OK, 0 rows affected (13.37 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from s1 order by id desc limit 10; #基于主鍵的聚集索引在創建完畢后就已經完成了排序，無需二次排序 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ row in set, 1 warning (0.04 sec)

聚集索引的好處之二：范圍查詢（range query），即如果要查找主鍵某一范圍內的數據，通過葉子節點的上層中間節點就可以得到頁的范圍，之后直接讀取數據頁即可

?

mysql> alter table s1 drop primary key; Query OK, 2699998 rows affected (24.23 sec) Records: 2699998 Duplicates: 0 Warnings: 0mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(6) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.12 sec)mysql> explain select * from s1 where id > 1 and id < 1000000; #沒有聚集索引，預估需要檢索的rows數如下 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2690100 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ row in set, 1 warning (0.00 sec)mysql> alter table s1 add primary key(id); Query OK, 0 rows affected (16.25 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引，預估需要檢索的rows數如下 +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1343355 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ row in set, 1 warning (0.09 sec)

?

2、輔助索引

表中除了聚集索引外其他索引都是輔助索引（Secondary Index，也稱為非聚集索引），與聚集索引的區別是：輔助索引的葉子節點不包含行記錄的全部數據。

葉子節點除了包含鍵值以外，每個葉子節點中的索引行中還包含一個書簽（bookmark）。該書簽用來告訴InnoDB存儲引擎去哪里可以找到與索引相對應的行數據。

由于InnoDB存儲引擎是索引組織表，因此InnoDB存儲引擎的輔助索引的書簽就是相應行數據的聚集索引鍵。如下圖

?

?

輔助索引的存在并不影響數據在聚集索引中的組織，因此每張表上可以有多個輔助索引，但只能有一個聚集索引。當通過輔助索引來尋找數據時，InnoDB存儲引擎會遍歷輔助索引并通過葉子級別的指針獲得只想主鍵索引的主鍵，然后再通過主鍵索引來找到一個完整的行記錄。

舉例來說，如果在一棵高度為3的輔助索引樹種查找數據，那需要對這個輔助索引樹遍歷3次找到指定主鍵，如果聚集索引樹的高度同樣為3，那么還需要對聚集索引樹進行3次查找，最終找到一個完整的行數據所在的頁，因此一共需要6次邏輯IO訪問才能得到最終的一個數據頁。

二、 MySQL索引管理

?一 功能

?

#1. 索引的功能就是加速查找 #2. mysql中的primary key，unique，聯合唯一也都是索引，這些索引除了加速查找以外，還有約束的功能

?

轉載于:https://www.cnblogs.com/jassin-du/p/8068134.html

總結

以上是生活随笔為你收集整理的索引扩展的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。