在上一篇文章《count(1)、count(*)、count(字段)哪個(gè)更靠譜》中，我們提到過主鍵是優(yōu)化不了count的查詢效率的，需要建索引才可以，那么，是不是意味著主鍵的效率還不如一般的索引呢？懷著這個(gè)疑問，我們一起來了解下mysql主鍵和索引的相關(guān)知識(shí)。
mysql數(shù)據(jù)庫的MYISAM和InnoDB引擎所采用的索引的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)是不一樣的，本文所闡述的內(nèi)容都是基于InnoDB引擎下。
什么是主鍵
我們引用上一篇文章最后的一段內(nèi)容：
-
主鍵是一種約束，唯一索引是一種索引，兩者在本質(zhì)上是不同的。
-
主鍵創(chuàng)建后一定包含一個(gè)特殊的唯一性索引，唯一性索引不一定就是主鍵。
-
唯一性索引列允許空值， 而主鍵列不允許為空值。
-
主鍵可以被其他表引用為外鍵，而唯一索引不能。
-
一個(gè)表最多只能創(chuàng)建一個(gè)主鍵，但是可以創(chuàng)建多個(gè)唯一索引。
-
主鍵更適合那些不容易改變的唯一標(biāo)識(shí)，如自動(dòng)遞增列，身份證號(hào)等。
劃重點(diǎn)：
1、主鍵是一種約束，從本質(zhì)上來說并不是索引。
2、一個(gè)表最多只有一個(gè)主鍵。
3、主鍵定義后一定會(huì)按主鍵順序生成一個(gè)唯一性的索引，所以一般來說，我們會(huì)把主鍵和主鍵索引等同看待。
索引的類型
關(guān)于索引的類型說法有很多，如聚簇索引、非聚簇索引、主鍵索引、輔助索引、二級(jí)索引、次級(jí)索引、唯一索引、單列索引、復(fù)合索引等等。我們先引用mysql官方的一段話來解釋：
-
在InnoDB，每張表都有一個(gè)特殊的索引叫聚簇索引（也叫聚集索引），聚簇索引的B+Tree的葉子節(jié)點(diǎn)存的是主鍵值和整行數(shù)據(jù)，整張表的數(shù)據(jù)其實(shí)就是存儲(chǔ)在聚簇索引中，實(shí)際上聚簇索引就是一張按主鍵順序存儲(chǔ)的表。主鍵一定是聚簇索引。
-
除了聚簇索引外的其它索引都叫二級(jí)索引，與聚簇索引的區(qū)別在于二級(jí)索引的葉子節(jié)點(diǎn)中只存了索引列和主鍵值，索引和數(shù)據(jù)是分開的。非聚簇索引、輔助索引、次級(jí)索引都是二次索引的不同說法，唯一索引、單列索引、復(fù)合索引都屬于二次索引，只是從邏輯角度進(jìn)行的分類。
-
通過主鍵可以直接在聚簇索引找到對(duì)應(yīng)的行數(shù)據(jù)，通過二級(jí)索引需要先找到主鍵值，再根據(jù)主鍵值到聚簇索引找到對(duì)應(yīng)的行，也就是平常說的要進(jìn)行一次回表操作，因此，要獲取行數(shù)據(jù)，主鍵索引效率是最高的（注意：這里強(qiáng)調(diào)的是獲取到行數(shù)據(jù)）。
-
InnoDB要求每張表都要定義主鍵，并建議主鍵采用自增ID的形式，這樣可以減少二級(jí)索引占用空間，提升索引效率。
下面是聚簇索引和二級(jí)索引的結(jié)構(gòu)：
通過上面的索引結(jié)構(gòu)可以看出，聚簇索引保存了整張表的數(shù)據(jù)，所以要獲取行數(shù)據(jù)通過聚簇索引是最快的，但如果只是獲取索引列和主鍵列數(shù)據(jù)，二次索引結(jié)構(gòu)更小，通過二級(jí)索引的效率是最高的。
-
聚簇索引的建立遵循以下的原則：
1、如果一個(gè)主鍵被定義了，那么這個(gè)主鍵就是作為聚簇索引。
2、如果沒有主鍵被定義，那么該表的第一個(gè)唯一非空索引被作為聚簇索引。
3、如果沒有主鍵也沒有合適的唯一索引，那么InnoDB內(nèi)部會(huì)生成一個(gè)隱藏的主鍵作為聚簇索引，這個(gè)隱藏的主鍵是一個(gè)6個(gè)字節(jié)的列，該列的值會(huì)隨著數(shù)據(jù)的插入自增，但用戶不可見且不能用于查詢。
那么，我們思考兩個(gè)問題：
1、主鍵一定是聚簇索引，聚簇索引是否一定是主鍵？
2、聚簇索引不是主鍵，那二級(jí)索引葉子節(jié)點(diǎn)存放的主鍵值是什么？
帶著這兩個(gè)問題，我們來做個(gè)實(shí)驗(yàn)，先建個(gè)測試表：
create table test(
?id int not null,
?c1 varchar(10) not null,
?c2 varchar(10) null);
查看索引情況：
select i.* from information_schema.INNODB_SYS_INDEXES i join information_schema.INNODB_SYS_TABLES t on i.table_id=t.table_id where t.name='ecos/test';
可以看到，未定義主鍵的情況下，mysql自動(dòng)創(chuàng)建了一個(gè)隱藏的、用戶不可見的主鍵并作為聚簇索引，符合聚簇索引建立的第3條原則。（在INNODB_SYS_INDEXES系統(tǒng)表中type代表索引的類型，0：一般的索引，1:(GEN_CLUST_INDEX)系統(tǒng)生成的隱藏主鍵索引，2：唯一索引，3：主鍵索引）
再創(chuàng)建一個(gè)唯一索引：
create unique index unx_c1 on test(c1);
再查看索引情況：
可以看到創(chuàng)建的唯一索引自動(dòng)轉(zhuǎn)變成了主鍵索引，那么c1是否也轉(zhuǎn)成了主鍵呢，我們?cè)倏幢淼那闆r：
c1并沒有變成primary key，改變的只是索引的類型，這時(shí)候從用戶視角主鍵實(shí)際上是不存在的。
我們?cè)俳o表添加主鍵：
alter table test add PRIMARY key(id);
再看索引和表的主鍵情況：
可以看到原來的主鍵索引已經(jīng)變成了唯一索引，新加的主鍵ID成了主鍵索引。
總結(jié)：
1、主鍵一定是聚簇索引，聚簇索引不一定是主鍵，但一定是主鍵索引（我們暫且把系統(tǒng)自動(dòng)生成的也當(dāng)主鍵索引）。
2、二次索引葉子節(jié)點(diǎn)存放的主鍵值準(zhǔn)確的說是主鍵索引值。
3、在mysql的官方文檔里面，一直都是以primary key出現(xiàn)的，并且也不支持以create index方式創(chuàng)建，primary key既代表了主鍵也代表了主鍵索引，這也造成了我們?cè)诶斫馍系睦Щ螅栽诹私鈓ysql索引結(jié)構(gòu)的時(shí)候建議還是把primary key理解為主鍵索引。
4、在建表的時(shí)候就應(yīng)該定義好主鍵，并且不再修改，特別是生產(chǎn)環(huán)境，主鍵的修改會(huì)影響到所有索引的重建。
5、官方建議主鍵用自增ID，但在實(shí)際業(yè)務(wù)場景中，應(yīng)該結(jié)合業(yè)務(wù)實(shí)際需要來定義主鍵，更好的利用主鍵索引的優(yōu)勢，但要避免用uuid等無序的值。如在訂單數(shù)據(jù)表，訂單號(hào)本身就按遞增規(guī)則生成，且具有唯一性，那么用訂單號(hào)做主鍵更符合業(yè)務(wù)需要。
最后回到最開始的問題，count(*)之所以在只有主鍵的情況下查詢效率無法提升，正是由于mysql InnoDB索引結(jié)構(gòu)導(dǎo)致的，count在where條件沒有的情況下是要走全表掃描，而主鍵索引是聚簇索引，包含了整個(gè)表的數(shù)據(jù)，占用空間和分頁更多，查詢優(yōu)化器在有二級(jí)索引的情況下會(huì)優(yōu)先查找二級(jí)索引，二級(jí)索引的B+Tree更小，查找效率會(huì)更高，覆蓋索引優(yōu)化同樣是利用的二級(jí)索引的這種優(yōu)勢。
思考：id為主鍵，下面兩個(gè)查詢返回的結(jié)果是一樣的嗎？
1、select a.* from tbl_order a limit 1;
2、select?a.* from tbl_order a where id=(select b.id from tbl_order b limit 1);文章來源：http://www.zghlxwxcb.cn/news/detail-481629.html
淺談mysql的主鍵和索引 (qq.com)文章來源地址http://www.zghlxwxcb.cn/news/detail-481629.html
到了這里，關(guān)于淺談mysql的主鍵和索引的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
