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

121次閱讀

共計(jì) 5274 個(gè)字符，預(yù)計(jì)需要花費(fèi) 14 分鐘才能閱讀完成。

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

總結(jié)：

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

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

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

4、聚集索引，數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起，因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚集索引。

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

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

7、mysql 的 innodb 表，因?yàn)檎麖埍硪彩蔷奂饕?，select 出來的結(jié)果是順序排序的，比如主鍵字段的數(shù)據(jù)插入順序可以是 5、3、4、2、1, 查詢時(shí)不帶 order by 得出的結(jié)果也是按 1、2、3、4、5 排序

8、通俗理解

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

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

每個(gè)表只能有一個(gè)聚集索引，因?yàn)槟夸浿荒馨凑找环N方法進(jìn)行排序。

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

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

9.2、堆表的存儲(chǔ)速度因?yàn)椴挥每紤]排序, 所以存儲(chǔ)速度會(huì)比較快. 但是要查找符合某個(gè)條件的記錄, 就必須得讀取全部的記錄以便篩選。

9.3、堆表其索引中記錄了記錄所在位置的 rowid，查找的時(shí)候先找索引，然后再根據(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é)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚集索引（clustered index）。

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

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

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

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

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

-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)化，每個(gè)索引都包含每個(gè)條目的關(guān)聯(lián)主鍵列的副本。修改任何主鍵列的值是一項(xiàng)昂貴的操作。因此，InnoDB 表設(shè)計(jì)的一個(gè)重要方面是選擇一個(gè)主鍵，該主鍵具有在最重要的查詢中使用的列，并保持主鍵很短，很少更改值。

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 表存儲(chǔ)基于主鍵列的值進(jìn)行組織，以加速涉及主鍵列的查詢和排序。為獲得最佳性能，請根據(jù)性能最關(guān)鍵的查詢仔細(xì)選擇主鍵列。因?yàn)樾薷木奂饕牧惺且豁?xiàng)昂貴的操作，所以選擇很少或從不更新的主列。在 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.

每個(gè) InnoDB 表都有一個(gè)稱為聚集索引的特殊索引，其中存儲(chǔ)了行的數(shù)據(jù)。通常，聚集索引與主鍵同義。

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

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

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

通過聚集索引訪問行很快，因?yàn)樗饕阉髦苯又赶虬行袛?shù)據(jù)的頁面。如果表很大，則與使用與索引記錄不同的頁面存儲(chǔ)行數(shù)據(jù)的存儲(chǔ)組織相比，聚集索引體系結(jié)構(gòu)通常會(huì)保存磁盤 I / O 操作。

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

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

正文完