首頁 > 文章 > 資料庫 > mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

王林轉載: 2023-05-27 12:25:571893瀏覽

聚集索引（Clustered Index）

聚集索引就是依照每張表的主鍵建構一棵B 樹，同時葉子節點中存放的即為整張表的行記錄資料。

舉個例子，直覺感受下聚集索引。

建立表t，並以人為的方式讓每頁只能存放兩個行記錄（不清楚怎麼人為控制每頁只存放兩個行記錄）：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

最後《MySQL技術內幕》的作者透過分析工具得到這棵聚集索引樹的大致構造如下：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

The leaf nodes of a clustered index are referred to as data pages, each of which is linked by a doubly linked list, and the data pages are arranged in the order of primary keys.。

如圖所示，每個資料頁上存放的是完整的行記錄，而在非資料頁的索引頁中，存放的只是鍵值及指向資料頁的偏移量，而不是一個完整的行記錄。

如果定義了主鍵，InnoDB會自動使用主鍵來建立聚集索引。當沒有定義主鍵時，InnoDB會選擇一個唯一且非空的索引來充當主鍵。 InnoDB會隱式定義一個主鍵作為聚集索引，如果沒有唯一的非空索引。

輔助索引(Secondary Index)

輔助索引，也稱為非聚集索引。和聚集索引相比，葉子節點中並不包含所有行記錄的資料。葉子節點除了包含鍵值以外，每個葉子節點的索引行還包含了一個書籤（bookmark），該書籤用來告訴InnoDB哪裡可以找到與索引相對應的行資料。

還是以《MySQL科技內幕》中的例子，來直觀感受下輔助索引的模樣。

還是以上面的表t為例，在列c上建立非聚集索引：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

然後作者透過分析工作得到輔助索引和聚集索引的關係圖：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

可以看到輔助索引idx_c的葉子節點中包含了列c的值和主鍵的值。

舉例來說，假設Key的值為0x7fffffff，其中7的二進位表示為0111，0表示為負數。實際的整數值應取反加1，因此得到的結果是-1，且這是c列的數值。主鍵值為正數1，以指標值80000001表示，其中8位元代表二進位數1000。

覆蓋索引（Covering index）

使用InnoDB儲存引擎，可以透過輔助索引覆寫索引，直接取得查詢記錄，無需查詢聚集索引中的記錄。

使用覆蓋索引有啥好處？

可以減少大量的IO操作

上圖中我們知道，如果要查詢輔助索引中不含有的字段，得先遍歷輔助索引，再遍歷聚集索引，而如果要查詢的欄位值在輔助索引上就有，就不用再查聚集索引了，這顯然會減少IO操作。

例如上圖中，以下sql可以直接使用輔助索引，

select a from where c = -2;

#有助於統計

##假設存在如下表：

  CREATE TABLE `student` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` varchar(255) NOT NULL,
  `school` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_school_age` (`school`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果在該表上執行：

select count(*) from student

優化器會怎麼處理？

優化器會選擇輔助索引來統計，因為儘管遍歷聚集索引和輔助索引都可以得到結果，但輔助索引的大小遠小於聚集索引。執行explain指令：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

key和Extra顯示使用了idx_name這個輔助索引。

還有，假設執行以下sql：

select * from student where age > 10 and age < 15

因為聯合索引idx_school_age的欄位順序是先school再age，依照age做條件查詢，通常不走索引：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

#但是，如果保持條件不變，查詢所有欄位改為查詢條目數：

select count(*) from student where age > 10 and age < 15

優化器會選擇這個聯合索引：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

聯合索引

聯合索引是指將資料表上的多個資料列進行索引。

以下為建立聯合索引idx_a_b的範例：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

#共同索引的內部結構：

mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用

联合索引也是一棵B+树，其键值数量大于等于2。键值都是排序的，通过叶子节点可以逻辑上顺序的读出所有数据。数据（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）是按照（a，b）先比较a再比较b的顺序排列。

基于上面的结构，对于以下查询显然是可以使用（a，b）这个联合索引的：

select * from table where a=xxx and b=xxx ;

select * from table where a=xxx;

但是对于下面的sql是不能使用这个联合索引的，因为叶子节点的b值，1,2,1,4,1,2显然不是排序的。

select * from table where b=xxx

联合索引的第二个好处是对第二个键值已经做了排序。举个例子：

create table buy_log(
    userid int not null,
    buy_date DATE
)ENGINE=InnoDB;

insert into buy_log values(1, &#39;2009-01-01&#39;);
insert into buy_log values(2, &#39;2009-02-01&#39;);

alter table buy_log add key(userid);
alter table buy_log add key(userid, buy_date);

当执行

select * from buy_log where user_id = 2；

时，优化器会选择key(userid)；但是当执行以下sql：

select * from buy_log where user_id = 2 order by buy_date desc;

时，优化器会选择key(userid, buy_date)，因为buy_date是在userid排序的基础上做的排序。

如果把key（userid,buy_date）删除掉，再执行：

select * from buy_log where user_id = 2 order by buy_date desc;

优化器会选择key（userid），但是对查询出来的结果会进行一次filesort，即按照buy_date重新排下序。所以联合索引的好处在于可以避免filesort排序。

以上是mysql中聚集索引、輔助索引、覆蓋索引、聯合索引怎麼用的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文轉載於：yisu.com。如有侵權，請聯絡admin@php.cn刪除

上一篇：mysql中表空間傳輸的範例分析下一篇：mysql中表空間傳輸的範例分析

看更多