mysql關於聚集索引、非聚集索引的總結

總結：

1、mysql的innodb表，就是索引組織表，表中的所有資料行都放在索引上，這就約定了資料是嚴格按照順序存放的，所以不管插入的先後順序，它在那個物理上的那個位置與插入的先後順序無關。

2、聚集索引，葉子節點存的是整行資料，直接透過這個聚集索引的鍵值找到某行

3、聚集索引，資料的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那麼對應的資料一定也是相鄰地存放在磁碟上的。

4、聚集索引，資料行和相鄰的鍵值緊湊地儲存在一起，因為無法同時把資料行存放在兩個不同的地方，所以一個表只能有一個聚集索引。

5、非聚集索引，葉子節點存的是欄位的值，透過這個非聚集索引的鍵值找到對應的聚集索引欄位的值，再透過聚集索引鍵值找到表的某行，類似oracle透過鍵值找到rowid，再透過rowid找到行

6、mysql的innodb表，其聚集索引相當於整張表，而整張表也是聚集索引。預設透過主鍵聚集資料，如果沒有定義主鍵，則選擇第一個非空的唯一索引，如果沒有非空唯一索引，則選擇rowid來作為聚集索引

7、mysql的innodb表，因為整張表也是聚集索引，select出來的結果是順序排序的，比如主鍵欄位的資料插入順序可以是5、3、4、2、1,查詢時不帶order by得出的結果也是按1、2、3、4、5排序

8、通俗理解

聚集索引：類似新華字典正文內容本身就是一種按照一定規則排列的目錄

非聚集索引：這種目錄純粹是目錄，正文純粹是正文的排序方式

每個表只能有一個聚集索引，因為目錄只能按照一種方法進行排序 。

9、oracle一般使用堆表，mysql的innodb是索引組織表

9.1、堆表以一種顯然隨機的方式管理，資料插入時時儲存位置是隨機的，主要是資料庫內部塊的空閒情況決定，資料會放在最合適的地方，而不是以某種特定順序來放置。

9.2、堆表的儲存速度因為不用考慮排序, 所以儲存速度會比較快. 但是要查詢符合某個條件的記錄, 就必須得讀取全部的記錄以便篩選。

9.3、堆表其索引中記錄了記錄所在位置的rowid，查詢的時候先找索引，然後再根據索引rowid找到塊中的行資料。

9.4、堆表的索引和表資料是分離的

9.5、索引組織表，其行資料以索引形式存放，因此找到索引，就等於找到了行資料。

9.6、索引組織表索引和資料是在一起的

基於主鍵索引和普通索引的查詢有什麼區別？

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

(ID,k) 值分別為 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)

主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為聚集索引（clustered index）。

非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）。

如果語句是 select * from T where ID=500，即 主鍵查詢方式，則只需要搜尋 ID 這棵 B+樹 ；

如果語句是 select * from T where k=5，即 普通索引查詢方式，則需要先搜尋 k 索引樹，得到 ID的值為 500，再到 ID 索引樹搜尋一次。這個過程稱為回表

B+ 樹為了維護索引有序性，在插入新值的時候需要做必要的維護。以上面為例，如果插入新的行 ID 值為 700，則只只需要在 R5 的記錄後面插入一個新記錄。如果新插入的 ID值為 400，就相對麻煩了，需要邏輯上挪動後面的資料，空出位置。

test1表 innodb引擎，索引和資料放在一個檔案裡面

-rw-r----- 1 mysql mysql  8678 Nov 20 14:05 test1.frm

-rw-r----- 1 mysql mysql 98304 Nov 20 16:51 test1.ibd

test2表 myisam引擎，索引和資料放在不同檔案

-rw-r----- 1 mysql mysql  8558 Nov 22 10:22 test2.frm

-rw-r----- 1 mysql mysql     0 Nov 22 10:22 test2.MYD

-rw-r----- 1 mysql mysql  1024 Nov 22 10:22 test2.MYI

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_table

table

The rows of an InnoDB table are organized into an index structure known as the clustered index , with entries sorted based on the primary key columns of the table. Data access is optimized for queries that filter and sort on the primary key columns, and each index contains a copy of the associated primary key columns for each entry. Modifying values for any of the primary key columns is an expensive operation. Thus an important aspect of InnoDB table design is choosing a primary key with columns that are used in the most important queries, and keeping the primary key short, with rarely changing values.

InnoDB表的行被組織成稱為聚集索引的索引結構 ，條目根據表的主鍵列進行排序。 資料訪問針對對主鍵列進行篩選和排序的查詢進行了最佳化，每個索引都包含每個條目的關聯主鍵列的副本。 修改任何主鍵列的值是一項昂貴的操作。 因此，InnoDB表設計的一個重要方面是選擇一個主鍵，該主鍵具有在最重要的查詢中使用的列，並保持主鍵很短，很少更改值。

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_clustered_index

clustered index

The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.In the Oracle Database product, this type of table is known as an index-organized table

InnoDB術語表示主鍵索引。 InnoDB表儲存基於主鍵列的值進行組織，以加速涉及主鍵列的查詢和排序。 為獲得最佳效能，請根據效能最關鍵的查詢仔細選擇主鍵列。 因為修改聚集索引的列是一項昂貴的操作，所以選擇很少或從不更新的主列。在Oracle資料庫產品中，此類表稱為索引組織表。

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. 

When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index .

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values . The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

每個InnoDB表都有一個稱為聚集索引的特殊索引，其中儲存了行的資料。通常，聚集索引與主鍵同義。

在表上定義PRIMARY KEY時，InnoDB將其用作聚集索引 。為您建立的每個表定義主鍵。如果沒有邏輯唯一且非空列或一組列，請新增一個新的自動增量列，其值將自動填充。

如果沒有為表定義PRIMARY KEY，MySQL將找到第一個UNIQUE索引，其中所有鍵列都是NOT NULL，而InnoDB將它用作聚集索引。

如果表沒有PRIMARY KEY或合適的UNIQUE索引，InnoDB會在包含行ID值的合成列內部生成名為GEN_CLUST_INDEX的隱藏聚集索引 。這些行按InnoDB分配給此類表中的行的ID排序。行ID是一個6位元組的欄位，在插入新行時會單調增加。因此，由行ID排序的行在物理上處於插入順序。

透過聚集索引訪問行很快，因為索引搜尋直接指向包含所有行資料的頁面。 如果表很大，則與使用與索引記錄不同的頁面儲存行資料的儲存組織相比，聚集索引體系結構通常會儲存磁碟I / O操作。

除聚集索引之外的所有索引都稱為輔助索引。 在InnoDB中，輔助索引中的每個記錄都包含該行的主鍵列以及為輔助索引指定的列。 InnoDB使用此主鍵值來搜尋聚集索引中的行。