Mysql索引概述（用于個人學習與回顧）

Mysql索引概述

索引介紹

• 索引是對記錄集的多個字段進行排序的方法。

• 類似于書的目錄

• 索引類型包括：Btree,B+tree,hash
  

索引優缺點

• ?索引優點

  -通過創建唯一性索引，可以保證數據庫中每一行數據的唯一性

  -可以加快數據的檢索速度

• 索引缺點

  -當對表中的數據進行增加，刪除和修改的時候，索引也要動態的維護，降低數據的維護速度

  -索引需要占物理空間

MySQL鍵值類型

鍵值類型

• INDEX：普通索引

• UNIQUE: 唯一索引

• FULLTEXT: 全文索引

• PRIMARY KEY: 主鍵

• POREIGN KEY: 外鍵

INDEX普通索引

• 使用說明

  -一個表中可以有多個INDEX字段

  -字段的值允許有重復，且可以賦予NULL值

  -經常把做查詢條件的字段設置為INDEX字段

  -INDEX字段的key標志位MUL

• 建表指定索引字段

  -index(字段1),index(字段2)...

mysql>?create?table?test1(
????->?id?char(6)?not?null,
????->?name?varchar(4)?not?null,
????->?age?int(3)?not?null,
????->?gender?enum('male','female'),
????->?index(id),index(name)
????->?);
Query?OK,?0?rows?affected?(0.06?sec)
mysql>?desc?test1;
+--------+-----------------------+------+-----+---------+-------+
|?Field??|?Type??????????????????|?Null?|?Key?|?Default?|?Extra?|
+--------+-----------------------+------+-----+---------+-------+
|?id?????|?char(6)???????????????|?NO???|?MUL?|?NULL????|???????|
|?name???|?varchar(4)????????????|?NO???|?MUL?|?NULL????|???????|
|?age????|?int(3)????????????????|?NO???|?????|?NULL????|???????|
|?gender?|?enum('male','female')?|?YES??|?????|?NULL????|???????|
+--------+-----------------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

• 在已有的表中設置index字段

  -create index 索引名 on表名（字段名）；

mysql>?create?index?age?on?test1(age);
Query?OK,?0?rows?affected?(0.02?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test1;
+--------+-----------------------+------+-----+---------+-------+
|?Field??|?Type??????????????????|?Null?|?Key?|?Default?|?Extra?|
+--------+-----------------------+------+-----+---------+-------+
|?id?????|?char(6)???????????????|?NO???|?MUL?|?NULL????|???????|
|?name???|?varchar(4)????????????|?NO???|?????|?NULL????|???????|
|?age????|?int(3)????????????????|?NO???|?MUL?|?NULL????|???????|
|?gender?|?enum('male','female')?|?YES??|?????|?NULL????|???????|
+--------+-----------------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

• 刪除指定表的索引字段

  -drop index 索引名 on 表名；

mysql>?drop?index?name?on?test1;
Query?OK,?0?rows?affected?(0.34?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test1;
+--------+-----------------------+------+-----+---------+-------+
|?Field??|?Type??????????????????|?Null?|?Key?|?Default?|?Extra?|
+--------+-----------------------+------+-----+---------+-------+
|?id?????|?char(6)???????????????|?NO???|?MUL?|?NULL????|???????|
|?name???|?varchar(4)????????????|?NO???|?????|?NULL????|???????|
|?age????|?int(3)????????????????|?NO???|?????|?NULL????|???????|
|?gender?|?enum('male','female')?|?YES??|?????|?NULL????|???????|
+--------+-----------------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

• 查看表的索引信息

  -show index from 表名；

mysql>?show?index?from?test1\G;
***************************?1.?row?***************************
????????Table:?test1
???Non_unique:?1
?????Key_name:?id
?Seq_in_index:?1
??Column_name:?id
????Collation:?A
??Cardinality:?0
?????Sub_part:?NULL
???????Packed:?NULL
?????????Null:?
???Index_type:?BTREE
??????Comment:?
Index_comment:?
***************************?2.?row?***************************
????????Table:?test1
???Non_unique:?1
?????Key_name:?age
?Seq_in_index:?1
??Column_name:?age
????Collation:?A
??Cardinality:?0
?????Sub_part:?NULL
???????Packed:?NULL
?????????Null:?
???Index_type:?BTREE
??????Comment:?
Index_comment:?
2?rows?in?set?(0.00?sec)

primary key主鍵

• 基本概念

  -一個表中只能有一個primary key 字段

  -對應的字段值不允許有重復，且不允許賦NULL值

  -如果有多個字段都作為primary key，稱為復合主鍵，必須一起創建

  -主鍵字段的KEY標志是PRI

  -通常與AUTO_INCREMENT 連用

  -經常把表中能夠唯一標識記錄的字段設置為主鍵字段【記錄編號字段】

• 建表的時候指定主鍵字段

  -primary key(字段名)

mysql>?create?table?test2(
????->?id?int(3)?auto_increment,
????->?name?varchar(4)?not?null,
????->?age?int(2)?not?null,
????->?primary?key(id)
????->?);
Query?OK,?0?rows?affected?(0.05?sec)
mysql>?desc?test2;
+-------+------------+------+-----+---------+----------------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra??????????|
+-------+------------+------+-----+---------+----------------+
|?id????|?int(3)?????|?NO???|?PRI?|?NULL????|?auto_increment?|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|????????????????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|????????????????|
+-------+------------+------+-----+---------+----------------+
3?rows?in?set?(0.00?sec

• 在已有的表中設置primary key字段

  -alter table 表名 add primary key（字段名）；

mysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?????|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.01?sec)
mysql>?alter?table?test2?add?primary?key(name);
Query?OK,?0?rows?affected?(0.05?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?????|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?PRI?|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)

• 移除表中的PRIMARY KEY字段

  -alter table 表名 drop primary key;(如果有auto_increment則需要先移除再移除主鍵，移除auto_incrememt為對該字段type重新賦值)

ysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?PRI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)
mysql>?alter?table?test2?drop?primary?key;
Query?OK,?0?rows?affected?(0.31?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?????|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.01?sec)

foreign key 外鍵

• 基本概念

  -讓當前表字段的值在另一個表中字段值得范圍內選擇。

• 使用外鍵的條件

  -表的存儲引擎必須是innodb

  -字段類型要一致

  -被參照字段必須要是索引類型的一種（primary key）

• 基本用法

mysql>?create?table?yuangong(
????->?yg_id?int(4)?auto_increment,
????->?name?char(16)?not?null,
????->?primary?key(yg_id)
????->?);
Query?OK,?0?rows?affected?(0.31?sec)
mysql>?create?table?gongzi(
????->?gz_id?int(4)?not?null,
????->?name?char(15)?not?null,
????->?gz?float(6,2)?not?null?default?0,
????->?index(name)，
????->?foreign?key(gz_id)?references?yuangong(yg_id)
????->?on?update?cascade?on?delete?cascade
????->?);
Query?OK,?0?rows?affected?(0.32?sec)

• 刪除外鍵字段

  -alter table 表名 drop foreign key 約束名；

mysql>?show?create?table?gongzi\G;
***************************?1.?row?***************************
???????Table:?gongzi
Create?Table:?CREATE?TABLE?`gongzi`?(
??`gz_id`?int(4)?NOT?NULL,
??`name`?char(15)?NOT?NULL,
??`gz`?float(6,2)?NOT?NULL?DEFAULT?'0.00',
??KEY?`name`?(`name`),
??KEY?`gz_id`?(`gz_id`),
??CONSTRAINT?`gongzi_ibfk_1`?FOREIGN?KEY?(`gz_id`)?REFERENCES?`yuangong`?(`yg_id`)?ON?DELETE?CASCADE?ON?UPDATE?CASCADE
)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1
1?row?in?set?(0.01?sec)

mysql>?alter?table?gongzi?drop?foreign?key?gongzi_ibfk_1;
Query?OK,?0?rows?affected?(0.01?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?show?create?table?gongzi\G;
***************************?1.?row?***************************
???????Table:?gongzi
Create?Table:?CREATE?TABLE?`gongzi`?(
??`gz_id`?int(4)?NOT?NULL,
??`name`?char(15)?NOT?NULL,
??`gz`?float(6,2)?NOT?NULL?DEFAULT?'0.00',
??KEY?`name`?(`name`),
??KEY?`gz_id`?(`gz_id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1
1?row?in?set?(0.00?sec)

UNIQUE索引

• 基本概念

  -UNIQUE表示唯一性，同一個字段可以有多個字段具有唯一性

• 創建表時指定UNIQUE索引字段

  查看新建test3表的字段結構，可發現UNIQUE字段的KEY標志為UNI；另外，由于字段name必須滿足“NOT NULL”的非空約束，所以將其設置為UNIQUE后會自動變成了PRIMARY KEY主鍵字段：
  

mysql>?create?table?test3(
????->?id?char(6),
????->?name?varchar(4)?not?null,
????->?age?int(3)?not?null,
????->?unique(id),unique(name),index(age)
????->?);
Query?OK,?0?rows?affected?(0.36?sec)
mysql>?desc??test3;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?char(6)????|?YES??|?UNI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?PRI?|?NULL????|???????|
|?age???|?int(3)?????|?NO???|?MUL?|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)

• 刪除UNIQUE索引，在已有的表中設置UNIQUE索引字段

mysql>?drop?index?name?on?test3;
Query?OK,?0?rows?affected?(0.07?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test3;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?char(6)????|?YES??|?UNI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(3)?????|?NO???|?MUL?|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)

• 在已有表中建立UNIQUE索引

mysql>?create?unique?index?name?on?test3(name);
Query?OK,?0?rows?affected?(0.06?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test3;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?char(6)????|?YES??|?UNI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?PRI?|?NULL????|???????|
|?age???|?int(3)?????|?NO???|?MUL?|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)