一、MySQL 索引

（一）、索引的概念

1、索引是一個排序的串列，在這個串列中存盤著索引的值和包含這個值的資料所在行的物理地址 (類似于C語言的鏈表通過指標指向資料記錄的記憶體地址) ，
2、使用索引后可以不用掃描全表來定位某行的資料，而是先通過索引表找到該行資料對應的物理地址然后訪問相應的資料，因此能加快資料庫的查詢速度，
3、索引就好比是一本書的目錄，可以根據目錄中的頁碼快速找到所需的內容，
4、索引是表中一列或者若干列值排序的方法，
5、建立索引的目的是加快對表中記錄的查找或排序，

（二）、索引的作用

1、設定了合適的索引之后，資料庫利用各種快速定位技術，能夠大大加快查詢速度，這是創建索引的最主要的原因，
2、當表很大或查詢涉及到多個表時，使用索引可以成千，上萬倍地提高查詢速度，
3、可以降低資料庫的I/O成本，并且索引還可以降低資料庫的排序成本，
4、通過創建唯一性索引，可以保證資料表中每一行資料的唯一性，
5、可以加快表與表之間的連接，
6、在使用分組和排序時，可大大減少分組和排序的時間，

（三）、索引的副作用

1、索引需要占用額外的磁盤空間，

• 對于 MyISAM 引擎而言，索引檔案和資料檔案是分離的，索引檔案用于保存資料記錄的地址，
• 而 InnoDB 引擎的表資料檔案本身就是索引檔案，

2、在插入和修改資料時要花費更多的時間，因為索引也要隨之變動，

（四）、創建索引的原則依據

索引隨可以提升資料庫查詢的速度，但并不是任何情況下都適合創建索引，因為索引本身會消耗系統資源，在有索引的情況下，資料庫會先進行索引查詢，然后定位到具體的資料行，如果索引使用不當，反而會增加資料庫的負擔，

1、表的主鍵、外鍵必須有索引，因為主鍵具有唯一性，外鍵關聯的是主表的主鍵，查詢時可以快速定位，
2、記錄數超過300行的表應該有索引，如果沒有索引，每次查詢都需要把表遍歷一遍，會嚴重影響資料庫的性能，
3、經常與其他表進行連接的表，在連接欄位上應該建立索引，
4、唯一 性太差的欄位不適合建立索引，
5、更新太頻繁地欄位不適合創建索引，
6、經常出現在 where 子句中的欄位，特別是大表的欄位，應該建立索引，
7、索引應該建在選擇性高的欄位上，
8、索引應該建在小欄位.上，對于大的文本欄位甚至超長欄位，不要建索引，

（五）、索引的分類和創建

create table member (id int(10),name varchar(10),Cardid varchar(10),phone int(11),address varchar(50),remark text);

(1)、普通索引: 最基本的索引型別，沒有唯一性之類的限制，
直接創建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
# (列名(length)): length是可選項， 下同， 如果忽略 length 的值，則使用整個列的值作為索引，如果指定使用列的前length個字符來創建索引，這樣有利于減小索引檔案的大小，
#索引名建議以 “_index" 結尾，

create index name_index on member (name);

修改表方式創建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

創建表的時候指定索引
CREATE TABLE 表名 (欄位1 資料型別,欄位2 資料型別 [,...],INDEX 索引名 (列名));

(2)、唯一索引: 與普通索引類似，但區別是唯一索引列的每個值都唯一，唯一索引允許有空值(注意和主鍵不同)，如果是用組合索引創建，則列值的組合必須唯一，添加唯一鍵將自動創建唯一索引，

直接創建唯一索引:
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

create unique index cardid_index on member (cardid);

修改表方式創建:
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

創建表的時候指定:
CREATE TABLE 表名 (欄位1 資料型別,欄位2 資料型別 [,...],UNIQUE 索引名 (列名));

(3)、主鍵索引: 是一種特殊的唯一索引，必須指定為 “PRIMARY KEY"，一個表只能有一個主鍵，不允許有空值，
添加主鍵將自動創建主鍵索引，

創建表的時候指定:
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

修改表方式創建:
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

alter table member add primary key(id);

(4)、組合索引 (單列索引與多列索引): 可以是單列上創建的索引，也可以是在多列上創建的索引，需要滿足最左原則，因為 select陳述句的 where 條件是依次從左往右執行的，所以在使用 select 陳述句查詢時 where 條件使用的欄位順序必須和組合索引中的排序一致，否則索引將不會生效，

CREATE TABLE 表名 (列名1 資料型別,列名2 資料型別,列名3 資料型別,INDEX 索引名(列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

(5)、全文索引 (FULLTEXT): 適合在進行模糊查詢的時候使用，可用于在一篇文章中檢索文本資訊，在MySQL5.6 版本以前FULLTEXT索引僅可用于MyISAM引擎，在5.6版本之后innodb 引擎也支持FULLTEXT 索引，全文索引可以在CHAR、VARCHAR 或者 TEXT型別的列上創建，每個表只允許有一個全文索引，

直接創建索引：
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

修改表方式創建：
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

alter table member add fulltext remark_index (remark);

創建表的時候指定索引：
CREATE TABLE 表名 (欄位1 資料型別 [,..., FULLTEXT 索引名 (列名));
#資料型別可以為CHAR、 VARCHAR 或者 TEXT

使用全文索引查詢：
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查詢內容');

insert into member values(1,'zhangsan',123123,123123,'nanjing','this is test!');
insert into member values(2,'lisi',456456,456456,'beijing','this is kgc!');
insert into member values (3,'wangwu',789789,789789,'shanghai','this is kgc test!');
select * from member where match (remark) against('test');

（六）、查看索引

show index from 表名;
show keys from 表名;

各欄位的含義如下:
Table: 表的名稱，
Non_unique: 如果索引不能包括重復詞，則為0;如果可以，則為1，
Key_name: 索引的名稱，
Seq_in_index: 索引中的列序號，從1開始，
Column_name: 列名稱，
Cpllation: 列以什么方式存盤在索引中，在MySQL中，有值‘A’(升序) 或 NULL(無分類)，
Cardinality: 索引中唯一值數目的估計值，
Sub_part: 如果列只是被部分地編入索引，則為被編入索引的字符的數目，如果整列被編入索引，則為NULL，
Packed: 指示關鍵字如何被壓縮，如果沒有被壓縮，則為NULL，
Null: 如果列含有NULL， 則含有YES， 如果沒有，則該列含有NO，
Index_type: 用過的索引方法 (BTREE，FULLTEXT，HASH，RTREE)，
Comment:  備注，

（七）、洗掉索引

1、直接洗掉索引
DROP INDEX 索引名 ON 表名;

2、修改表方式洗掉索引
ALTER TABLE 表名 DROP INDEX 索引名;

3、洗掉主鍵索引
ALTER TABLE 表名 DROP PRIMARY KEY;

（八）、實際案例

案例：為某商場做一個會員卡的系統，這個系統有一個會員表，有下列欄位:
會員編號  INT
會員姓名  VARCHAR(10)
會員身份證號碼  VARCHAR(18)
會員電話  INT(11)
會員住址  VARCHAR (50)
會員備注資訊  TEXT 

create table member (id int(10),name varchar(10),cardid varchar(18),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);

會員編號，作為主鍵，使用 PRIMARY KEY
會員姓名,如果要建索引的話，那么就是普通的 INDEX
會員身份證號碼,如果要建索引的話，那么可以選擇 UNIQUE (唯一的，不允許重復)
會員備注資訊，如果需要建索引的話，可以選擇 FULLTEXT，全文搜索，
不過 FULLTEXT 用于搜索很長一篇文章的時候，效果最好，用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以，