目錄
一：MySQL 索引
1、MySQL 索引介紹
2、?索引的作用
?3、索引的副作用
4、?創(chuàng)建索引的原則依據(jù)
?二、索引的分類(lèi)和創(chuàng)建
1、?普通索引
（1）?直接創(chuàng)建索引
（2）?修改表方式創(chuàng)建
（3）?創(chuàng)建表的時(shí)候指定索引
2、?唯一索引
（1）?直接創(chuàng)建唯一索引
（2）?修改表方式創(chuàng)建索引
（3）??創(chuàng)建表的時(shí)候指定索引
?3、主鍵索引
（1）?創(chuàng)建表的時(shí)候指定索引
?（2）?修改表方式創(chuàng)建索引
4、?組合索引(單列索引與多列索引)
5、全文索引(FULLTEXT)
（1）直接創(chuàng)建索引
（2）修改表方式創(chuàng)建
（3）?創(chuàng)建表的時(shí)候指定索引
（4）使用全文索引查詢(xún)
6、?查看索引
?7、刪除索引
（1）直接刪除索引
?（2）修改表方式刪除索引
（3）刪除主鍵索引
?三:案例
總結(jié)
一：MySQL 索引
1、MySQL 索引介紹
- 索引是一個(gè)排序的列表，在這個(gè)列表中存儲(chǔ)著索引的值和包含這個(gè)值的數(shù)據(jù)所在行的物理地址(類(lèi)似于C語(yǔ)言的鏈表通過(guò)指針指向數(shù)據(jù)記錄的內(nèi)存地址)。
- 使用索引后可以不用掃描全表來(lái)定位某行的數(shù)據(jù)，而是先通過(guò)索引表找到該行數(shù)據(jù)對(duì)應(yīng)的物理地址然后訪問(wèn)相應(yīng)的數(shù)據(jù)，因此能加快數(shù)據(jù)庫(kù)的查詢(xún)速度。
- 索引就好比是一本書(shū)的目錄，可以根據(jù)目錄中的頁(yè)碼快速找到所需的內(nèi)容。
- 索引是表中一列或者若干列值排序的方法。
- 建立索引的目的是加快對(duì)表中記錄的查找或排序。
?
2、?索引的作用
- 設(shè)置了合適的索引之后，數(shù)據(jù)庫(kù)利用各種快速定位技術(shù)，能夠大大加快查詢(xún)速度，這是創(chuàng)建索引的最主要的原因。
- 當(dāng)表很大或查詢(xún)涉及到多個(gè)表時(shí)，使用索引可以成千上萬(wàn)倍地提高查詢(xún)速度。
- 可以降低數(shù)據(jù)庫(kù)的IO成本，并且索引還可以降低數(shù)據(jù)庫(kù)的排序成本。
- 通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)表中每一行數(shù)據(jù)的唯一 性。
- 可以加快表與表之間的連接。
- 在使用分組和排序時(shí)，可大大減少分組和排序的時(shí)間。
?
?3、索引的副作用
- 索引需要占用額外的磁盤(pán)空間。
- 對(duì)于MyISAM引擎而言，索引文件和數(shù)據(jù)文件是分離的，索引文件用于保存數(shù)據(jù)記錄的地址。而InnoDB引擎的表數(shù)據(jù)文件本身就是索引文件。
- 在插入和修改數(shù)據(jù)時(shí)要花費(fèi)更多的時(shí)間，因?yàn)樗饕惨S之變動(dòng)。
4、?創(chuàng)建索引的原則依據(jù)
索引隨可以提升數(shù)據(jù)庫(kù)查詢(xún)的速度，但并不是任何情況下都適合創(chuàng)建索引。因?yàn)樗饕旧頃?huì)消耗系統(tǒng)資源，在有索引的情況下，數(shù)據(jù)庫(kù)會(huì)先進(jìn)行索引查詢(xún)，然后定位到具體的數(shù)據(jù)行，如果索引使用不當(dāng)，反而會(huì)增加數(shù)據(jù)庫(kù)的負(fù)擔(dān)。
- 表的主鍵、外鍵必須有索引。因?yàn)橹麈I具有唯一性，外鍵關(guān)聯(lián)的是主表的主鍵，查詢(xún)時(shí)可以快速定位。
- 記錄數(shù)超過(guò)300行的表應(yīng)該有索引。如果沒(méi)有索引，每次查詢(xún)都需要把表遍歷一遍，會(huì)嚴(yán)重影響數(shù)據(jù)庫(kù)的性能。
- 經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引。
- 唯一性太差的字段不適合建立索引。
- 更新太頻繁地字段不適合創(chuàng)建索引。
- 經(jīng)常出現(xiàn)在where 子句中的字段，特別是大表的字段，應(yīng)該建立索引。
- 索引應(yīng)該建在選擇性高的字段上。
- 索引應(yīng)該建在小字段.上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引。
?
?二、索引的分類(lèi)和創(chuàng)建
create table member (id int(10),name varchar(10),cardid varchar(10),phone int(11),address varchar(50),remark text);
?
1、?普通索引
最基本的索引類(lèi)型，沒(méi)有唯一性之類(lèi)的限制。
（1）?直接創(chuàng)建索引
CREATE INDEX 索引名 ON 表名(列名[ (length)]);
#(列名(length))：length是可選項(xiàng)，下同。如果忽略length的值，則使用整個(gè)列的值作為索引。如果指定使用列的前l(fā)ength個(gè)字符來(lái)創(chuàng)建索引，這樣有利于減小索引文件的大小。
#索引名建議以“index"結(jié)尾。
create index name_index on member (name);
show create table member;
show index from member;
show keys from member;
（2）?修改表方式創(chuàng)建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
例：
alter table member add INDEX cardid_index (cardid);
select cardid from member; #自動(dòng)進(jìn)行排序
?
（3）?創(chuàng)建表的時(shí)候指定索引
CREATE TABLE 表名 (字段1 數(shù)據(jù)類(lèi)型,字段2 數(shù)據(jù)類(lèi)型[,...],INDEX 索引名 (列名));
2、?唯一索引
與普通索引類(lèi)似，但區(qū)別是唯一 索引列的每個(gè)值都唯一。 唯一索引允許有空值(注意和主鍵不同)。如果是用組合索引創(chuàng)建，則列值的組合必須唯一。添加唯一鍵將自動(dòng)創(chuàng)建唯一索引。
（1）?直接創(chuàng)建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
create unique index cardid_index on member(cardid);
（2）?修改表方式創(chuàng)建索引
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
（3）??創(chuàng)建表的時(shí)候指定索引
CREATE TABLE 表名 (字段1 數(shù)據(jù)類(lèi)型,字段2 數(shù)據(jù)類(lèi)型[,...],UNIQUE 索引名 (列名));
?3、主鍵索引
是一種特殊的唯一索引，必須指定為"PRIMARY KEY"。 一個(gè)表只能有一個(gè)主鍵，不允許有空值。添加主鍵將自動(dòng)創(chuàng)建主鍵索引。
（1）?創(chuàng)建表的時(shí)候指定索引
CREATE TABLE 表名([...],PRIMARY KEY (列名));
?（2）?修改表方式創(chuàng)建索引
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
alter table member add primary key (id);
4、?組合索引(單列索引與多列索引)
可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引。需要滿足最左原則，因?yàn)閟elect語(yǔ)句的where 條件是依次從左往右執(zhí)行的，所以在使用select 語(yǔ)句查詢(xún)時(shí)where條件使用的字段順序必須和組合索引中的排序一致，否則索引將不會(huì)生效。
CREATE TABLE 表名 (列名1 數(shù)據(jù)類(lèi)型,列名2 數(shù)據(jù)類(lèi)型,列名3 數(shù)據(jù)類(lèi)型, INDEX 索引名 (列名1, 列名2,列名3));
select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='；
5、全文索引(FULLTEXT)
適合在進(jìn)行模糊查詢(xún)的時(shí)候使用，可用于在一篇文章中檢索文本信息。在MySQL5.6 版本以前FULLTEXT索引僅可用于MyISAM 引擎，在5.6版本之后innodb 引擎也支持FULLTEXT 索引。全文索引可以在CHAR、VARCHAR 或者TEXT類(lèi)型的列上創(chuàng)建。每個(gè)表只允許有一個(gè)全文索引。
（1）直接創(chuàng)建索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
（2）修改表方式創(chuàng)建
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
alter table member add fulltext remark_index (remark);
?
（3）?創(chuàng)建表的時(shí)候指定索引
CREATE TABLE 表名 (字段1 數(shù)據(jù)類(lèi)型[...], FULLTEXT 索引名 (列名));
#數(shù)據(jù)類(lèi)型可以為CHAR、VARCHAR 或者TEXT
（4）使用全文索引查詢(xún)
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查詢(xún)內(nèi)容');
insert into member2 values(1,'zhangsan',123123,123123,'nanjing','this is test!');
insert into member2 values(2,'lisi',456456,456456,'beijing','this is xyw!');
insert into member2 values(3,'wangwu',789789,78979,'shanghai','this is xyw test!');
select * from member2 where match(remark) against('test');
?
6、?查看索引
show index from 表名;
show keys from 表名;
??各字段的含義
Table：表的名稱(chēng)。
Non_unique：如果索引不能包括重復(fù)詞，則為0;如果可以，則為1。
Key_name：索引的名稱(chēng)。
Seq_in_index：索引中的列序號(hào)，從1開(kāi)始。
Column_name：列名稱(chēng)。
Collation：列以什么方式存儲(chǔ)在索引中。在MySQL中，有值'A'(升序)或NULL(無(wú)分類(lèi))。
Cardinality：索引中唯一值數(shù)目的估計(jì)值。
Sub_part：如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為NULL.
Packed：指示關(guān)鍵字如何被壓縮。如果沒(méi)有被壓縮，則為NULL。
Null：如果列含有NULL， 則含有YES。如果沒(méi)有，則該列含有NO。
Index_type：用過(guò)的索引方法( BTREE，F(xiàn)ULLTEXT， HASH， RTREE) 。
Comment：備注。
?7、刪除索引
（1）直接刪除索引
DROP INDEX 索引名 ON 表名;
?（2）修改表方式刪除索引
ALTER TABLE 表名 DROP INDEX 索引名;
（3）刪除主鍵索引
ALTER TABLE 表名 DROP PRIMARY KEY;
?三:案例
比如為某商場(chǎng)做一個(gè)會(huì)員卡的系統(tǒng)。這個(gè)系統(tǒng)有一一個(gè)會(huì)員表，有下列字段：
會(huì)員編號(hào) INT
會(huì)員姓名 VARCHAR(10)
會(huì)員身份證號(hào)碼 VARCHAR(18)
會(huì)員電話 INT(11)
會(huì)員住址 VARCHAR(50)
會(huì)員備注信息 TEXT
create table member (id int(10),name varchar(10),cardid varchar(10),phone int(11),address varchar(50),remark text);
alter table member add primary key (id);
create index name_index on member (name);
create unique index cardid_index on member (cardid);
alter table member add fulltext remark_index (remark);
那么會(huì)員編號(hào)，作為主鍵，使用PRIMARY KEY
會(huì)員姓名，如果要建索引的話，那么就是普通的INDEX
會(huì)員身份證號(hào)碼，如果要建索引的話，那么可以選擇UNIQUE (唯一 的，不允許重復(fù))
會(huì)員備注信息，如果需要建索引的話，可以選擇FULLTEXT，全文搜索。
不過(guò)FULLTEXT 用于搜索很長(zhǎng)一篇文章的時(shí)候，效果最好。用在比較短的文本，如果就一兩行字的，普通的INDEX 也可以。
?
總結(jié)
索引：是一個(gè)排序的列表，包含索引字段的值和其相對(duì)應(yīng)的行數(shù)據(jù)所在的物理地址
作用：加快表的查詢(xún)速度，還可以對(duì)字段排序
如何實(shí)現(xiàn)的？
沒(méi)有索引的情況下，要查詢(xún)某行數(shù)據(jù)，需要先掃描全表來(lái)定位某行數(shù)據(jù)
有索引后會(huì)通過(guò)查找條件的字段值找到其索引對(duì)應(yīng)的行數(shù)據(jù)的物理地址，然后根據(jù)物理地址訪問(wèn)相應(yīng)的數(shù)據(jù)副作用：會(huì)額外占用磁盤(pán)空間，更新包含索引的表效率會(huì)更慢
創(chuàng)建索引的依據(jù)：
1）表中的記錄行數(shù)較多時(shí)，一般超過(guò)300行的表建議要有索引
2）建議在 表中的主鍵字段、外鍵字段、多表連接使用的字段、唯一性較好的字段，不經(jīng)常更新的字段、經(jīng)常出現(xiàn)在where、group by、order by 子語(yǔ)句的字段、小字段 創(chuàng)建索引
3）不建議在 唯一性較差的字段、更新太頻繁的字段、大文本字段 上創(chuàng)建索引
索引類(lèi)型：
普通索引 ? create index 索引名 on 表名 (字段); ? ? ? alter table 表名 add index 索引名 (字段);
唯一索引 ? create unique index 索引名 on 表名 (字段); ? ?alter table 表名 add unique 索引名 (字段);
主鍵索引 ? alter table 表名 add primary key (字段);
組合索引 ? create ?index 索引名 on 表名 (字段1, 字段2, 字段3); ? ?lter table 表名 add index 索引名 (字段1, 字段2, 字段3);
? ? ? ? ? ? ? ? ?select ... from 表名 where 字段1=XX and 字段2=xx and 字段3=XX ? ? 用 and 做條件邏輯運(yùn)算符時(shí)，要?jiǎng)?chuàng)建組合索引且要滿足最左原則
? ? ? ? ? ? ? ? ?用 or 做條件邏輯運(yùn)算符時(shí)，所有字段都要?jiǎng)?chuàng)建索引
全文索引 ? create fulltext index 索引名 on 表名 (字段); ? ? ? alter table 表名 add fulltext 索引名 (字段);
?模糊查詢(xún)： select ... from 表名 where match(字段) against('查詢(xún)內(nèi)容');
?? ??? ?
查看索引
show create table 表名;
show index from 表名;
show keys from 表名;刪除索引
drop index 索引名 on 表名;
alter table 表名 drop 索引名;
alter table 表名 drop primary key;文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-486911.html
explain select ... 可以用于分析select查詢(xún)語(yǔ)句，看key字段，確定是否使用了索引或索引使用是否正確文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-486911.html
到了這里，關(guān)于MySQL數(shù)據(jù)庫(kù)索引的種類(lèi)、創(chuàng)建、刪除的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
