前一陣子，又跑出去搞了一場面試，心態(tài)算是崩了，關(guān)于MySQL索引的原理及使用被面試官懟的體無完膚，立志要總結(jié)一番，然后一直沒有時間（其實是懶……），準(zhǔn)備好了嗎？

一、MySQL中索引的語法

創(chuàng)建索引

在創(chuàng)建表的時候添加索引

CREATE TABLE mytable(      ID INT NOT NULL,       username VARCHAR(16) NOT NULL,      INDEX [indexName] (username(length))  );

在創(chuàng)建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);或者CREATE INDEX index_name ON my_table(column_name);

注意：

1、索引需要占用磁盤空間，因此在創(chuàng)建索引時要考慮到磁盤空間是否足夠

2、創(chuàng)建索引時需要對表加鎖，因此實際操作中需要在業(yè)務(wù)空閑期間進(jìn)行

根據(jù)索引查詢

具體查詢：SELECT * FROM table_name WHERE column_1=column_2;(為column_1建立了索引)
或者模糊查詢SELECT * FROM table_name WHERE column_1 LIKE '%三'SELECT * FROM table_name WHERE column_1 LIKE '三%'SELECT * FROM table_name WHERE column_1 LIKE '%三%'
SELECT * FROM table_name WHERE column_1 LIKE '_好_'
如果要表示在字符串中既有A又有B，那么查詢語句為：SELECT * FROM table_name WHERE column_1 LIKE '%A%' AND column_1 LIKE '%B%';
SELECT * FROM table_name WHERE column_1 LIKE '[張李王]三';  //表示column_1中有匹配張三、李三、王三的都可以SELECT * FROM table_name WHERE column_1 LIKE '[^張李王]三';  //表示column_1中有匹配除了張三、李三、王三的其他三都可以
//在模糊查詢中，%表示任意0個或多個字符；_表示任意單個字符（有且僅有），通常用來限制字符串長度;[]表示其中的某一個字符；[^]表示除了其中的字符的所有字符
或者在全文索引中模糊查詢SELECT * FROM table_name WHERE MATCH(content) AGAINST('word1','word2',...);

刪除索引

DROP INDEX my_index ON tablename；或者ALTER TABLE table_name DROP INDEX index_name;

查看表中的索引

SHOW INDEX FROM tablename

查看查詢語句使用索引的情況

//explain 加查詢語句explain SELECT * FROM table_name WHERE column_1='123';

二、索引的優(yōu)缺點

優(yōu)勢：可以快速檢索，減少I/O次數(shù)，加快檢索速度；根據(jù)索引分組和排序，可以加快分組和排序；

劣勢：索引本身也是表，因此會占用存儲空間，一般來說，索引表占用的空間的數(shù)據(jù)表的1.5倍；索引表的維護(hù)和創(chuàng)建需要時間成本，這個成本隨著數(shù)據(jù)量增大而增大；構(gòu)建索引會降低數(shù)據(jù)表的修改操作（刪除，添加，修改）的效率，因為在修改數(shù)據(jù)表的同時還需要修改索引表；

三、索引的分類

常見的索引類型有：主鍵索引、唯一索引、普通索引、全文索引、組合索引

1、主鍵索引：即主索引，根據(jù)主鍵pk_clolum（length）建立索引，不允許重復(fù)，不允許空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

2、唯一索引：用來建立索引的列的值必須是唯一的，允許空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列構(gòu)建的索引，沒有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：用大文本對象的列構(gòu)建的索引（下一部分會講解）

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5、組合索引：用多個列組合構(gòu)建的索引，這多個列中的值不允許有空值

ALTER?TABLE?'table_name'?ADD?INDEX?index_name('col1','col2','col3')；

*遵循“最左前綴”原則，把最常用作為檢索或排序的列放在最左，依次遞減，組合索引相當(dāng)于建立了col1,col1col2,col1col2col3三個索引，而col2或者col3是不能使用索引的。

*在使用組合索引的時候可能因為列名長度過長而導(dǎo)致索引的key太大，導(dǎo)致效率降低，在允許的情況下，可以只取col1和col2的前幾個字符作為索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4個字符和col2的前3個字符作為索引

未完待續(xù)。。。

面試又給我問到MySQL索引，最全的一次整理

一、MySQL中索引的語法

二、索引的優(yōu)缺點

三、索引的分類