mysql聚集索引和非聚集索引的區(qū)別

本篇內(nèi)容介紹了“mysql聚集索引和非聚集索引的區(qū)別”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

總結(jié)：

1、mysql的innodb表，就是索引組織表，表中的所有數(shù)據(jù)行都放在索引上，這就約定了數(shù)據(jù)是嚴(yán)格按照順序存放的，所以不管插入的先后順序，它在那個物理上的那個位置與插入的先后順序無關(guān)。

2、聚集索引，葉子節(jié)點存的是整行數(shù)據(jù)，直接通過這個聚集索引的鍵值找到某行

3、聚集索引，數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。

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

5、非聚集索引，葉子節(jié)點存的是字段的值，通過這個非聚集索引的鍵值找到對應(yīng)的聚集索引字段的值，再通過聚集索引鍵值找到表的某行，類似oracle通過鍵值找到rowid，再通過rowid找到行

6、mysql的innodb表，其聚集索引相當(dāng)于整張表，而整張表也是聚集索引。默認(rèn)通過主鍵聚集數(shù)據(jù)，如果沒有定義主鍵，則選擇第一個非空的唯一索引，如果沒有非空唯一索引，則選擇rowid來作為聚集索引

7、mysql的innodb表，因為整張表也是聚集索引，select出來的結(jié)果是順序排序的，比如主鍵字段的數(shù)據(jù)插入順序可以是5、3、4、2、1,查詢時不帶order by得出的結(jié)果也是按1、2、3、4、5排序

8、通俗理解

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

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

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

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

9.1、堆表以一種顯然隨機(jī)的方式管理，數(shù)據(jù)插入時時存儲位置是隨機(jī)的，主要是數(shù)據(jù)庫內(nèi)部塊的空閑情況決定，數(shù)據(jù)會放在最合適的地方，而不是以某種特定順序來放置。

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

9.3、堆表其索引中記錄了記錄所在位置的rowid，查找的時候先找索引，然后再根據(jù)索引rowid找到塊中的行數(shù)據(jù)。

9.4、堆表的索引和表數(shù)據(jù)是分離的

9.5、索引組織表，其行數(shù)據(jù)以索引形式存放，因此找到索引，就等于找到了行數(shù)據(jù)。

9.6、索引組織表索引和數(shù)據(jù)是在一起的

基于主鍵索引和普通索引的查詢有什么區(qū)別？

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)

主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚集索引（clustered index）。

非主鍵索引的葉子節(jié)點內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級索引（secondary index）。

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

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

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

test1表innodb引擎，索引和數(shù)據(jù)放在一個文件里面

-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引擎，索引和數(shù)據(jù)放在不同文件

-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表的行被組織成稱為聚集索引的索引結(jié)構(gòu)，條目根據(jù)表的主鍵列進(jìn)行排序。 數(shù)據(jù)訪問針對對主鍵列進(jìn)行篩選和排序的查詢進(jìn)行了優(yōu)化，每個索引都包含每個條目的關(guān)聯(lián)主鍵列的副本。 修改任何主鍵列的值是一項昂貴的操作。 因此，InnoDB表設(shè)計的一個重要方面是選擇一個主鍵，該主鍵具有在最重要的查詢中使用的列，并保持主鍵很短，很少更改值。

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術(shù)語表示主鍵索引。 InnoDB表存儲基于主鍵列的值進(jìn)行組織，以加速涉及主鍵列的查詢和排序。 為獲得最佳性能，請根據(jù)性能最關(guān)鍵的查詢仔細(xì)選擇主鍵列。 因為修改聚集索引的列是一項昂貴的操作，所以選擇很少或從不更新的主列。在Oracle數(shù)據(jù)庫產(chǎn)品中，此類表稱為索引組織表。

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表都有一個稱為聚集索引的特殊索引，其中存儲了行的數(shù)據(jù)。通常，聚集索引與主鍵同義。

在表上定義PRIMARY KEY時，InnoDB將其用作聚集索引。為您創(chuàng)建的每個表定義主鍵。如果沒有邏輯唯一且非空列或一組列，請?zhí)砑右粋€新的自動增量列，其值將自動填充。

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

如果表沒有PRIMARY KEY或合適的UNIQUE索引，InnoDB會在包含行ID值的合成列內(nèi)部生成名為GEN_CLUST_INDEX的隱藏聚集索引。這些行按InnoDB分配給此類表中的行的ID排序。行ID是一個6字節(jié)的字段，在插入新行時會單調(diào)增加。因此，由行ID排序的行在物理上處于插入順序。

通過聚集索引訪問行很快，因為索引搜索直接指向包含所有行數(shù)據(jù)的頁面。 如果表很大，則與使用與索引記錄不同的頁面存儲行數(shù)據(jù)的存儲組織相比，聚集索引體系結(jié)構(gòu)通常會保存磁盤I / O操作。

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

“mysql聚集索引和非聚集索引的區(qū)別”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！