MySQL索引概述
專注于為中小企業(yè)提供網(wǎng)站設(shè)計、成都做網(wǎng)站服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)瑪多免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上1000家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。
索引介紹
索引是對記錄集的多個字段進行排序的方法。
類似于書的目錄
索引類型包括：Btree,B+tree,hash
索引優(yōu)缺點
?索引優(yōu)點
-通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫中每一行數(shù)據(jù)的唯一性
-可以加快數(shù)據(jù)的檢索速度
索引缺點
-當對表中的數(shù)據(jù)進行增加，刪除和修改的時候，索引也要動態(tài)的維護，降低數(shù)據(jù)的維護速度
-索引需要占物理空間
MySQL鍵值類型
鍵值類型
INDEX：普通索引
UNIQUE: 唯一索引
FULLTEXT: 全文索引
PRIMARY KEY: 主鍵
POREIGN KEY: 外鍵
INDEX普通索引
使用說明
-一個表中可以有多個INDEX字段
-字段的值允許有重復，且可以賦予NULL值
-經(jīng)常把做查詢條件的字段設(shè)置為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)在已有的表中設(shè)置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 字段
-對應(yīng)的字段值不允許有重復，且不允許賦NULL值
-如果有多個字段都作為primary key，稱為復合主鍵，必須一起創(chuàng)建
-主鍵字段的KEY標志是PRI
-通常與AUTO_INCREMENT 連用
-經(jīng)常把表中能夠唯一標識記錄的字段設(shè)置為主鍵字段【記錄編號字段】
建表的時候指定主鍵字段
-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
在已有的表中設(shè)置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 外鍵
基本概念
-讓當前表字段的值在另一個表中字段值得范圍內(nèi)選擇。
使用外鍵的條件
-表的存儲引擎必須是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表示唯一性，同一個字段可以有多個字段具有唯一性
創(chuàng)建表時指定UNIQUE索引字段
查看新建test3表的字段結(jié)構(gòu)，可發(fā)現(xiàn)UNIQUE字段的KEY標志為UNI；另外，由于字段name必須滿足“NOT NULL”的非空約束，所以將其設(shè)置為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索引，在已有的表中設(shè)置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)
