MySQL中怎樣創(chuàng)建聚集索引和非聚集索引，求創(chuàng)建這兩種索引的SQL語句。謝謝

InnoDB按照主鍵進行聚集，如果沒有定義主鍵，InnoDB會試著使用唯一的非空索引來代替。如果沒有這種索引，InnoDB就會定義隱藏的主鍵然后在上面進行聚集。

成都創(chuàng)新互聯(lián)公司主營永安網(wǎng)站建設的網(wǎng)絡公司,主營網(wǎng)站建設方案,重慶APP開發(fā)公司,永安h5小程序制作搭建,永安網(wǎng)站營銷推廣歡迎永安等地區(qū)企業(yè)咨詢

所以，對于 聚集索引 來說，你創(chuàng)建主鍵的時候，自動就創(chuàng)建了主鍵的聚集索引。

而普通索引（非聚集索引）的語法，大多數(shù)數(shù)據(jù)庫都是通用的：

CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_type]

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH | RTREE}

[java] view plaincopy

-- 創(chuàng)建無索引的表格

create table testNoPK (

id int not null,

name varchar(10)

);

-- 創(chuàng)建普通索引

create index IDX_testNoPK_Name on testNoPK (name);

「Mysql索引原理（六）」聚簇索引

? ?本節(jié)課主要關(guān)注InnoDB，但是這里討論的原理對于任何支持聚簇索引的存儲引擎都是適用的。

? ?葉子節(jié)點包含了全部數(shù)據(jù)，其他節(jié)點只包含索引列。InnoDB將通過主鍵聚集數(shù)據(jù)，也就是說上圖中的“被索引的列”就是主鍵列。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引InnoDB會隱式定義一個主鍵來作為聚簇索引。

? ?如果主鍵比較大的話，那輔助索引將會變的更大，因為 輔助索引的葉子存儲的是主鍵值；過長的主鍵值，會導致非葉子節(jié)點占用占用更多的物理空間

所以建議使用int的auto_increment作為主鍵

? ?主鍵的值是順序的，所以 InnoDB 把每一條記錄都存儲在上一條記錄的后面。當達到頁的最大值時，下一條記錄就會寫入新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會近似于被順序的記錄填滿。

? ?聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對應的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一 頁一頁地寫，索引結(jié)構(gòu)相對緊湊，磁盤碎片少，效率也高。

? ?因為MyISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進行I/O讀取，于是開始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。（強烈的對比）

? ?不過，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢些，因為索引所占空間小，這些操作是需要在內(nèi)存中完成的。

? ?MyISM使用的是非聚簇索引， 非聚簇索引的兩棵B+樹看上去沒什么不同 ，節(jié)點的結(jié)構(gòu)完全一致只是存儲的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表數(shù)據(jù)存儲在獨立的地方，這兩顆B+樹的葉子節(jié)點都使用一個地址指向真正的表數(shù)據(jù)，對于表數(shù)據(jù)來說，這兩個鍵沒有任何差別。由于 索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹 。

? ?所以說，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設置。 一般要根據(jù)這個表最常用的SQL查詢方式來進行選擇，某個字段作為聚簇索引，或組合聚簇索引 ，這個要看實際情況。

? ?聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，主鍵索引和二級索引的數(shù)據(jù)分布也有區(qū)別，通常會讓人感到困擾和以外，下面通過一個列子來講解InnoDB和MyISAM是如何存儲數(shù)據(jù)的：

? ?該表的主鍵取值1~10000，按照隨機順序插入并使用optimize table命令做了優(yōu)化。換句話說，數(shù)據(jù)在磁盤上的存儲方式已是最優(yōu)，但行的順序是隨機的。列col2的值是從1~100之間隨機賦值，所以有很多重復的值。

? ?MyISAM的數(shù)據(jù)分布很簡單，所以先介紹它。MyISAM按照數(shù)據(jù)插入的順序存儲在磁盤上，如下圖所示：

在行的旁邊顯示行號，從0開始遞增。因為行是定長的，所以MyISAM可以從表的開頭跳過所需的字節(jié)找到需要的行。

col2上的索引

? ?事實上，MyISAM中主鍵索引和其他索引在結(jié)構(gòu)上沒有什么不同。主鍵索引就是一個名為PRIMARY的唯一非空索引。

? ?InnoDB支持聚簇索引，所以使用不同的方式存儲同樣的數(shù)據(jù)。

? ?第一眼看上去，感覺和前面的沒什么區(qū)別，但是該圖顯示了整個表，而不是只有索引。因為在InnoDB中，聚簇索引就是表，所以不像MyISAM那樣需要獨立的行存儲，這也是為什么MyISAM索引和數(shù)據(jù)結(jié)構(gòu)是分開的。

? ?聚簇索引的每一個葉子節(jié)點都包含了主鍵值。事務ID、用于事務和MVCC的回滾指針以及所有的剩余列。如果主鍵是一個列前綴索引，InnoDB也會包含完整的主鍵列和剩下的其他列。

? ?還有一點和MyISAM不同的是，InnoDB的二級索引和聚簇索引很不相同。InnoDB的二級索引的葉子節(jié)點中存儲的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。這樣的策略減少了當出現(xiàn)行移動或者數(shù)據(jù)頁分裂時二級索引的維護工作。使用主鍵值當作指針會讓二級索引占用更多的空間，換來的好處是，InnoDB在移動時無需更新二級索引中的這個“指針”。

? ?我們在來看一下 col2索引 。

? ?每一個葉子節(jié)點包含了索引列（這里是col2），緊接著是主鍵值（col1），上圖我們省略了非葉子節(jié)點這樣的細節(jié)。InnoDB非葉子節(jié)點包含了索引列和一個指向下一級節(jié)點的指針。

? ?最后，以一張圖表示InnoDB和MyISAM保存數(shù)據(jù)和索引的區(qū)別。

? ?前面講過，最好使用AUTO_INCREMENT自增列來聚集數(shù)據(jù)，避免隨機的、不連續(xù)的、值分布范圍大的列做聚簇索引，特別是對于I/O密集型的應用。例如，從性能角度考慮，使用UUID來作為聚簇索引則會很糟糕：他使得聚簇索引的插入變得完全隨機，這是最壞的情況，使得數(shù)據(jù)沒有任何聚集特性。

? ?為了演示這一點，我們做兩個基準測試：

1、使用證書ID插入userinfo表，和uuid作為主鍵的userinfo_uuid表

? ?userinfo_uuid表跟userinfo表除了主鍵給為UUID，其他字段都一樣

? ?測試這兩個表的設計，首先在一個有足夠內(nèi)存容納索引的服務器上向這兩個表各插入100萬條記錄。然后向兩個表繼續(xù)插入300萬數(shù)據(jù)，使索引的大小超過服務器的內(nèi)存容量。測試結(jié)果如下：

? ?向UUID主鍵插入行不僅花費的時間更長，而且索引占用的空間也更大。這一方面是由于主鍵字段更長，另一方面毫無疑問是由于頁分裂和碎片導致的。

? ?為了明白為什么會這樣，來看看往第一個表中插入數(shù)據(jù)時，索引發(fā)生了什么變化。

自整型主鍵插入

? ?因為主鍵是順序的，所以InnoDB把每一條記錄都存在上一條記錄的后面。當達到頁的最大容量后，下一條記錄就會寫入到新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會近似于被順序的記錄填滿，這也正是所期望的結(jié)果。

UUID插入

? ?因為新行的主鍵值不一定比之前插入的大，所以InnoDB無法簡單的總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置，通常是已有數(shù)據(jù)的中間位置，并且分配空間。這會正價很多的額外工作，并導致數(shù)據(jù)分布不夠優(yōu)化。

缺點：

把這些隨機值載入到聚簇索引后，也許需要做一次OPTIMIZE TABLE來重建表并優(yōu)化頁的填充。

結(jié)論 ：使用InnoDB時應盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地單調(diào)增加聚簇鍵的值來插入新行。

聚簇索引

??mysql的索引策略中有一條是聚簇索引，而聚簇索引并不是唯一索引，普通索引之類的索引類型，而是一種數(shù)據(jù)的存儲方式。大多數(shù)索引存在的形式為B-tree，葉子節(jié)點的索引則和其對應的數(shù)據(jù)行數(shù)據(jù)緊湊的存儲在一起，這就是術(shù)語聚簇的含義。實現(xiàn)數(shù)據(jù)存儲形式的是存儲引擎，但并不是所有存儲引擎都支持聚簇索引，而著名的InnoDB則是支持的引擎之一，下面都以InnoDB為例。

??而存儲引擎不能管理兩份同樣的數(shù)據(jù)，所以聚簇索引在同一張數(shù)據(jù)表中只能存在一個，其他的索引只能是非聚簇索引，也就是二級索引。數(shù)據(jù)表的如果有指定primary key，那么InnoDB就會把primary key作為聚簇索引來存儲，如果沒有，則會取第一個not null，unique的索引作為聚簇索引，unique的索引也不存在的話，InnoDB就會自行的，隱式的指定一個row ID列作為聚簇索引存儲，但這個row ID不會被用戶管理。

聚簇索引一些重要的優(yōu)點：

??1. 在有聚簇索引的數(shù)據(jù)表中，使用聚簇索引進行查詢的時候，因為索引和數(shù)據(jù)聚集在同一個B-tree中，能夠直接從索引獲取到數(shù)據(jù)行，比非聚簇索引的性能要好。

??2. 反之在沒有聚簇索引的數(shù)據(jù)表中，因為不能通過unique的值去聚集數(shù)據(jù)，所以需要通過非聚簇索引查詢數(shù)據(jù)的物理地址或者全表掃描來獲取數(shù)據(jù)，這樣每一行數(shù)據(jù)可能都會導致一次磁盤I/O。

在提升性能的同時，聚簇索引也存在著缺點：

??1. 更新聚簇索引的代價會很大，因為需要將數(shù)據(jù)行和主鍵進行重排，移動到新的位置，并且二級索引可能也需要更新。

??2. 聚簇索引的插入速度嚴重依賴插入順序，嚴格的升序主鍵是性能最好的方式，但如果主鍵是亂序的插入，例如用uuid作為主鍵，當主鍵值需要插入到某一頁已經(jīng)寫滿的page中，存儲引擎就需要將page分裂成兩個頁面來容納數(shù)據(jù)，這一個 頁分裂(page split) 操作，page split會使得數(shù)據(jù)表占用更多的磁盤空間。

??3. 通過二級索引獲取需要兩次索引查找，因為二級索引保存的是聚簇索引的主鍵，而不是指向數(shù)據(jù)的邏輯指針，所以獲取主鍵后需要再進行一次搜索才能獲取數(shù)據(jù)。

??聚簇索引和二級索引的數(shù)據(jù)分布方式不同，在MyISAM和InnoDB的數(shù)據(jù)文件組織方式中也有體現(xiàn)。

??MyISAM的數(shù)據(jù)由3個文件組成：1. .frm(表結(jié)構(gòu)描述文件) ，2. .MYD(數(shù)據(jù)行文件) ，3. .MYI(索引文件) 。

??InnoDB則有2個文件：1. .frm(表結(jié)構(gòu)描述文件) ，2. .MYD(數(shù)據(jù)行文件和索引信息) 。

??MyISAM引擎沒有使用索引和數(shù)據(jù)的聚集的分布方式，所以主鍵和其他索引的是沒有區(qū)別，就都存儲在索引文件中。

InnoDB的鎖機制是使用索引來實現(xiàn)，表現(xiàn)的等級為行級鎖，而MyISAM則是表級鎖，這也跟數(shù)據(jù)分布方式有關(guān)。InnoDB的主鍵索引與數(shù)據(jù)緊湊的聚集在一起，并且包含了事務ID，用于事務MVCC的回滾指針，而MyISAM則是數(shù)據(jù)與索引分離，無法實現(xiàn)如此細粒度的鎖。

??1. InnoDB暫時不能由用戶選定索引作為聚簇索引，InnoDB有自己的聚簇索引選取規(guī)則，所以在創(chuàng)建表的時候最好設置一個與業(yè)務無關(guān)的主鍵id作為聚簇索引，這樣修改二級索引和數(shù)據(jù)的時候，無需移動數(shù)據(jù)位置，提升性能。

??2. 聚簇索引的主鍵id不要使用uuid，uuid會使得數(shù)據(jù)的插入添加額外的頁分裂操作，降低性能，最好使用單調(diào)自增的id。

MySql 索引（聚集索引，輔助索引，聯(lián)合索引，覆蓋索引..）

引入一個面試問題:

看完以下以后再回顧，會發(fā)現(xiàn)迎刃而解

Mysql 可以為每一張表設置 存儲引擎 這里我們只說 InnoDB 存儲引擎.

由于實際情況，數(shù)據(jù)頁只能按照一棵 B+樹 進行排序， 因此每張表只能擁有一個 聚集索引（即 主鍵）。

栗子：

每個葉子節(jié)點的索引行中包含了一個書簽（bookmark）. 該書簽是用來告訴 InnoDB存儲引擎哪里可以找到該索引對應的數(shù)據(jù)行或者說 行數(shù)據(jù)！ 由于InnoDB存儲引擎表， 是按照主鍵來構(gòu)建的， 所以 ，該書簽內(nèi)其實包含或者說指向了 數(shù)據(jù)行所對應的聚集索引鍵

也就是說 輔助索引的 葉結(jié)點保存了 指向?qū)獢?shù)據(jù)的 聚集索引， 可以通過該聚集索引 找到對應的數(shù)據(jù)行

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因為每張表上可以有多個輔助索引。

當通過輔助索引來尋找數(shù)據(jù)時，InnoDB 存儲引擎會遍歷輔助索引并通過葉級別的指針獲得指向主鍵索引(聚集索引)的主鍵，然后再通過聚集索引找到一個完整的數(shù)據(jù)行。

例如：

聚集索引輔助索引關(guān)系:

： 又叫做組合索引 ， 輔助索引的一種 ， 和普通創(chuàng)建索引的方式一樣，不同的是 可以同時添加多列來作為索引項;

從本質(zhì)上來說，聯(lián)合索引也是一課B+樹

個人理解： 所謂最左原則， 是因為 存儲引擎構(gòu)建組合索引時 是根據(jù)最左邊的那一列索引項進行排序的 ，所以使用組合索引，必須滿足 條件中必須存在 最左邊那一列的索引項，這樣 才可以找到對應的索引，繼而 去尋找對應的數(shù)據(jù)

： 又叫做 索引覆蓋，InnoDB中支持覆蓋索引，即 從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。

比如 這里沒有根據(jù)最左原則使用組合索引，但是 優(yōu)化器依然進行選擇

共勉，歡迎指導謝謝~

mysql聚簇索引 非聚簇索引 覆蓋索引 聯(lián)合索引

1 ：clustered index 其實數(shù)據(jù)存儲結(jié)構(gòu)，索引和記錄（全部）內(nèi)容保存同一個結(jié)構(gòu)中。“聚簇”就是索引和記錄緊密在一起，分開就不是聚簇索引了，所以一張表只能有唯一的聚簇索引。

--（除此之外的表上的每個非聚簇索引都是 ，又叫輔助索引（secondary indexes））

————————————————

2 非聚簇索引：secondary index ，葉子節(jié)點保存了主鍵值，要定位記錄還要再查一遍聚簇索引。

————————————————

3 覆蓋索引：覆蓋索引是指索引的葉子節(jié)點已包含所有要查詢的列，因此不需要訪問表數(shù)據(jù)（回表~~有學個名詞，查詢聚簇索引）

查找時能不能直接定位：聚簇索引的葉節(jié)點就是數(shù)據(jù)節(jié)點，而非聚簇索引的頁節(jié)點仍然是索引檢點，并保留一個鏈接指向?qū)獢?shù)據(jù)塊。

————————————————

4 聯(lián)合索引又叫復合索引。

對于復合索引:Mysql從左到右的使用索引中的字段，一個查詢可以只使用索引中的一部份，但只能是最左側(cè)部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 。

網(wǎng)站欄目：mysql怎么建聚簇索引,mysql 簇索引
分享鏈接：http://chinadenli.net/article29/dsgecch.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供標簽優(yōu)化、響應式網(wǎng)站、外貿(mào)網(wǎng)站建設、網(wǎng)站設計、網(wǎng)站維護、虛擬主機

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)