mysql中key 、primary key 、unique key 與index區別

liftsail發表於2024-05-31
MySqlIndex

索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始並然後讀完整個表直到它找出相關的行。

表越大，花費時間越多。如果表對於查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到資料檔案的中間，沒有必要考慮所有資料。

如果一個表有1000行，這比順序讀取至少快100倍。注意你需要存取幾乎所有1000行，它較快的順序讀取，因為此時我們避免磁碟尋道。

所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中儲存。字串是自動地壓縮字首和結尾空間。

索引用於：

快速找出匹配一個WHERE子句的行；
當執行聯結時，從其他表檢索行；
對特定的索引列找出MAX()或MIN()值；
如果排序或分組在一個可用鍵的最左面字首上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。

如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。
在一些情況中，一個查詢能被最佳化來檢索值，不用諮詢資料檔案。

如果對某些表的所有使用的列是數字型的並且構成某些鍵的最左面字首，為了更快，值可以從索引樹被檢索出來。

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

下面是建表的語句:

CREATE TABLE `phpcolor_ad` (
`id` mediumint(8) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`type` mediumint(1) NOT NULL,
`code` text,
PRIMARY KEY (`id`),
KEY `type` (`type`)
);

最後一句的KEY `type` (`type`)是什麼意思？

如果只是key的話，就是普通索引。

mysql的key和index多少有點令人迷惑，單獨的key和其它關鍵詞結合的key(primary key)實際表示的意義是不同，這實際上考察對資料庫體系結構的瞭解的。
1 ：

key 是資料庫的物理結構，它包含兩層意義和作用，

一是約束（偏重於約束和規範資料庫的結構完整性），

二是索引（輔助查詢用的）。

包括primary key, unique key, foreign key 等。

primary key 有兩個作用，一是約束作用（constraint），用來規範一個儲存主鍵和唯一性，但同時也在此key上建立了一個主鍵索引；

PRIMARY KEY 約束：唯一標識資料庫表中的每條記錄；

主鍵必須包含唯一的值；

主鍵列不能包含 NULL 值；

每個表都應該有一個主鍵，並且每個表只能有一個主鍵。（PRIMARY KEY 擁有自動定義的 UNIQUE 約束）

unique key 也有兩個作用，一是約束作用（constraint），規範資料的唯一性，但同時也在這個key上建立了一個唯一索引；

UNIQUE 約束：唯一標識資料庫表中的每條記錄。
UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。
（每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束）

foreign key也有兩個作用，一是約束作用（constraint），規範資料的引用完整性，但同時也在這個key上建立了一個index；

可見，mysql的key是同時具有constraint和index的意義，這點和其他資料庫表現的可能有區別。

（至少在oracle上建立外來鍵，不會自動建立index），因此建立key也有如下幾種方式：
（1）在欄位級以key方式建立， 如 create table t (id int not null primary key);
（2）在表級以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
（3）在表級以key方式建立，如create table t(id int, primary key (id));

其它key建立類似，但不管那種方式，既建立了constraint，又建立了index，只不過index使用的就是這個constraint或key。


2： index是資料庫的物理結構，它只是輔助查詢的，它建立時會在另外的表空間（mysql中的innodb表空間）以一個類似目錄的結構儲存。索引要分類的話，分為字首索引、全文字索引等；
因此，索引只是索引，它不會去約束索引的欄位的行為（那是key要做的事情）。如，create table t(id int,index inx_tx_id (id));


3 總結，最後的釋疑：
（1）我們說索引分類，分為

主鍵索引（必須指定為“PRIMARY KEY”，沒有PRIMARY Index）、

唯一索引（unique index，一般寫成unique key）、

普通索引(index，只有這一種才是純粹的index)等，也是基於是不是把index看作了key。
比如 create table t(id int, unique indexinx_tx_id (id));--index當作了key使用

（2）最重要的也就是，不管如何描述，需要理解index是純粹的index（普通的key，或者普通索引index），還是被當作key（如：unique index、unique key和primary key），若當作key時則會有兩種意義或起兩種作用。

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

MySQL Key值（PRI, UNI, MUL）的含義：

PRI主鍵約束；

UNI唯一約束；

MUL可以重複。

注：若是普通的key或者普通的index（實際上，普通的key與普通的index同義）。

當我們在desc 表名; 的時候,有一個Key值，表示該列是否含有索引
假設表結構如下所示

mysql> desc aa;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
| xx    | int(11) | YES  | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
| yy    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
| zz    | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

我們看到Key那一欄，可能會有4種值，即'啥也沒有','PRI','UNI','MUL'
1. 如果Key是空的, 那麼該列值的可以重複，表示該列沒有索引, 或者是一個非唯一的複合索引的非前導列
2. 如果Key是PRI, 那麼該列是主鍵的組成部分
3. 如果Key是UNI, 那麼該列是一個唯一值索引的第一列(前導列)，且不能含有空值(NULL)
4. 如果Key是MUL, 那麼該列的值可以重複, 該列是一個非唯一索引的前導列(第一列)或者是一個唯一性索引的組成部分但是可以含有空值NULL

注：
1、如果對於一個列的定義，同時滿足上述4種情況的多種，比如一個列既是PRI，又是UNI（如果是PRI，則一定是UNI）
那麼"desc 表名"; 的時候，顯示的Key值按照優先順序來顯示 PRI->UNI->MUL
那麼此時，顯示PRI。

2、如果某列不能含有空值，同時該表沒有主鍵，則一個唯一性索引列可以顯示為PRI，

3、如果多列構成了一個唯一性複合索引，那麼一個唯一性索引列可以顯示為MUL。（因為雖然索引的多列組合是唯一的，比如ID+NAME是唯一的，但是每一個單獨的列依然可以有重複的值，因為只要ID+NAME是唯一的即可）

一、key與primary key區別 

CREATE TABLE wh_logrecord ( 
logrecord_id int(11) NOT NULL auto_increment, 
user_name varchar(100) default NULL, 
operation_time datetime default NULL, 
logrecord_operation varchar(100) default NULL, 
PRIMARY KEY (logrecord_id), 
KEY wh_logrecord_user_name (user_name) 
);

解析：
KEY wh_logrecord_user_name (user_name)
本表的user_name欄位與wh_logrecord_user_name表user_name欄位建立外來鍵
括號外是建立外來鍵的對應表，括號內是對應欄位
類似還有 KEY user(userid)
當然，key未必都是外來鍵

總結：
Key是索引約束，對錶中欄位進行約束索引的，都是透過primary foreign unique等建立的。常見有foreign key，外來鍵關聯用的。

KEY forum (status,type,displayorder) # 是多列索引（鍵）
KEY tid (tid) # 是單列索引（鍵）。

如建表時： KEY forum (status,type,displayorder)
select * from table group by status,type,displayorder 是否就自動用上了此索引，
而當 select * from table group by status 此索引有用嗎？

key的用途：主要是用來加快查詢速度的。 

CREATE TABLE `admin_role` (
  `adminSet_id` varchar(32) NOT NULL,
  `roleSet_id` varchar(32) NOT NULL,
  PRIMARY KEY (`adminSet_id`,`roleSet_id`),
  KEY `FK9FC63FA6DAED032` (`adminSet_id`),
  KEY `FK9FC63FA6C7B24C48` (`roleSet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


主鍵，兩個列組合在一起，是唯一的，內建唯一性索引，並且不能為NULL
另外，兩個Key定義，相當於分別對這兩列建立索引。

innodb
primary key 主鍵聚集索引
key 普通索引

二、KEY與INDEX區別

KEY通常是INDEX同義詞。如果關鍵字屬性PRIMARY KEY在列定義中已給定，則PRIMARY KEY也可以只指定為KEY。

這麼做的目的是與其它資料庫系統相容。

PRIMARY KEY是一個唯一KEY，此時，所有的關鍵字列必須定義為NOT NULL。

如果這些列沒有被明確地定義為NOT NULL，MySQL應隱含地定義這些列。一個表只有一個PRIMARY KEY。

MySQL 中Index 與Key 的區別

Key即鍵值，是關係模型理論中的一部份，比如有主鍵（Primary Key)，外來鍵（Foreign Key）等，用於資料完整性檢否與唯一性約束等。

而Index則處於實現層面，比如可以對錶的任意列建立索引，那麼當建立索引的列處於SQL語句中的Where條件中時，就可以得到快速的資料定位，從而快速檢索。

至於Unique Index，則只是屬於Index中的一種而已，建立了Unique Index表示此列資料不可重複，猜想MySQL對Unique Index型別的索引可以做進一步特殊最佳化吧。


於是乎，在設計表的時候，Key只是要處於模型層面的，而當需要進行查詢最佳化，則對相關列建立索引即可。

另外，在MySQL中，對於一個Primary Key的列，MySQL已經自動對其建立了Unique Index，無需重複再在上面建立索引了。

搜尋到的一段解釋：

Note that “primary” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).

三、mysql中UNIQUE KEY和PRIMARY KEY有什麼區別

1，Primary key的1個或多個列必須為NOT NULL，如果列為NULL，在增加PRIMARY KEY時，列自動更改為NOT NULL。

而UNIQUE KEY 對列沒有此要求

2，一個表只能有一個PRIMARY KEY，但可以有多個UNIQUE KEY

3，主鍵和唯一鍵約束是透過參考索引實施的，如果插入的值均為NULL，

則根據索引的原理，全NULL值不被記錄在索引上，所以插入全NULL值時，可以有重複的，而其他的則不能插入重複值。

alter table t add constraint uk_t_1 unique (a,b);
insert into t (a ,b ) values (null,1); # 不能重複
insert into t (a ,b ) values (null,null);#可以重複

四、使用UNIQUE KEY 

CREATE TABLE `secure_vulnerability_warning` ( 
  `id` int(10) NOT NULL auto_increment, 
  `date` date NOT NULL, 
  `type` varchar(100) NOT NULL, 
  `sub_type` varchar(100) NOT NULL, 
  `domain_name` varchar(128) NOT NULL, 
  `url` text NOT NULL, 
  `parameters` text NOT NULL, 
  `hash` varchar(100) NOT NULL, 
  `deal` int(1) NOT NULL, 
  `deal_date` date default NULL, 
  `remark` text, 
  `last_push_time` datetime default NULL, 
  `push_times` int(11) default '1', 
  `first_set_ok_time` datetime default NULL, 
  `last_set_ok_time` datetime default NULL, 
  PRIMARY KEY  (`id`), 
  UNIQUE KEY `date` (`date`,`hash`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

UNIQUE KEY的用途：主要是用來防止資料插入的時候重複的。

1，建立表時 

CREATE TABLE Persons 
( 
Id_P int NOT NULL, 
LastName varchar(255) NOT NULL, 
FirstName varchar(255), 
Address varchar(255), 
City varchar(255), 
UNIQUE (Id_P) 
)

如果需要命名 UNIQUE 約束，以及為多個列定義 UNIQUE 約束，請使用下面的 SQL 語法： 

CREATE TABLE Persons 
( 
Id_P int NOT NULL, 
LastName varchar(255) NOT NULL, 
FirstName varchar(255), 
Address varchar(255), 
City varchar(255), 
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) 
)

2，當表已被建立時，如需在 "Id_P" 列建立 UNIQUE 約束，請使用下列 SQL： 

ALTER TABLE Persons 
ADD UNIQUE (Id_P)

如需命名 UNIQUE 約束，並定義多個列的 UNIQUE 約束，請使用下面的 SQL 語法： 

ALTER TABLE Persons 
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

3，撤銷 UNIQUE 約束

如需撤銷 UNIQUE 約束，請使用下面的 SQL： 

ALTER TABLE Persons 
DROP INDEX uc_PersonID

相關文章