複合主鍵與聯合主鍵、索引與聯合(複合)索引

前幾天面試時被問到了MySql中聯合索引使用與生效問題；回來順便看了一下複合主鍵與聯合主鍵，查漏補缺，慢慢學習吧。

一、複合主鍵與聯合主鍵

1、複合主鍵：指表的主鍵含有一個以上的字段組成,不使用無業務含義的自增id作為主鍵。

create table test

(

   name varchar(19),

   id number,

   value varchar(10),

   primary key (name,id)

)

上面的name和id字段組合起來就是test表的複合主鍵 ,它的出現是因為name字段可能會出現重名，是以要加上id字段，這樣就可以保證記錄的唯一性 ,一般情況下，主鍵的字段長度和字段數目越少越好 。 

“當表中隻有一個主鍵時，它是唯一的索引；當表中有多個主鍵時，稱為複合主鍵，複合主鍵聯合保證唯一索引” 。

某幾個主鍵字段值出現重複是沒有問題的，隻要不是有多條記錄的所有主鍵值完全一樣，就不算重複。

2、聯合主鍵顧名思義就是多個主鍵聯合形成一個主鍵組合。 

聯合主鍵的意義：用2個字段(或者多個字段)來确定一條記錄。說明：這2個字段可以都不唯一，即2個字段可以分别重複，這麼設定的好處，可以很直覺的看到某個重複字段的記錄條數。 

舉例： 主鍵A跟主鍵B組成聯合主鍵 

主鍵A跟主鍵B的資料可以完全相同，聯合就在于主鍵A跟主鍵B形成的聯合主鍵是唯一的。 

下例主鍵A資料是1，主鍵B資料也是1，聯合主鍵其實是11，11是唯一值，不允許再出現11這個值(即為多對多關系)

主鍵A資料   主鍵B資料 

   1　　　　　 1 

   2　　　　　 2 

   3　　　　　 3 

主鍵A與主鍵B的聯合主鍵值最多為： 11、 12、 13、 21、 22、 23、 31、 32、 33。

二、索引、聯合(組合)索引

1.索引是加快查詢速度的有效手段。系統在存取資料時會自動選擇合适的索引作為存取路徑。

建立索引：create [unique] [cluster] index <索引名>

删除索引：drop index <索引名>

索引是各種關系資料庫系統最常見的一種邏輯單元,是關系資料庫系統舉足輕重的重要組成部分,對于提高檢索資料速度有着至關重要的作用,索引的原理是根據索引值得到行指針,然後快速定位到資料庫記錄。

索引的使用

1）. 主鍵（預設是自帶索引的）和外鍵（以及一些可以跟其他表關聯的字段） 

2）. where子句中經常出現的字段 

3）. 索引應該建在小字段上，大資料字段（bit，image，text，blob等）不适用

2.聯合(複合)索引------ 最左比對原則

建立聯合索引：create index <索引名稱> on 表名 (字段1，字段2...);

例如：有聯合索引（a,b,c,d）

select * from test where a=1 and b=2;----a和b索引都起作用

索引生效原則：按照聯合索引的順序，從前往後依次使用生效，如果中間某個索引沒有使用，那麼斷點前面的索引部分起作用，斷點後面的索引沒有起作用。

例如：where a=1 and b=2 and d=4;則隻有a和b索引起作用，中間c斷了，則d索引不起作用。

mysql會一直向右比對直到遇到範圍查詢(>、<、between、like)就停止比對。

比如where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)順序的索引，d是用不到索引的,因為c出現了“>”。

如果建立(a,b,d,c)的索引則where a = 1 and b = 2 and c > 3 and d = 4 中abcd都可以用到索引，并且a,b,d的順序可以任意調整，即：select * from test where b=2 and d=4 and a=1and c>3中a,b,c,d也都用到了索引。

注意：

1）隻要列中包含有NULL值都将不會被包含在索引中，複合索引中隻要有一列含有NULL值，那麼這一列對于此複合索引就是無效的。是以我們在資料庫設計時不要讓字段的預設值為NULL。

2）一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

3）NOT IN和操作都不會使用索引将進行全表掃描。NOT IN可以NOT EXISTS代替。

補充：

可以在Navicat中建立一張表，并建立一個聯合(複合)索引，在表中插入一些資料，之後利用Explain SELECT * FROM 表名 where 索引字段名1= value1 and 索引字段名2=value2 and 索引字段名3>value3;類似的語句來進行分析和查詢聯合索引。

詳情可檢視 https://blog.csdn.net/y1193329479/article/details/78821139