一、索引的型別及新增

1、主鍵索引，主鍵自動的為主索引 (型別Primary)

當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引。
如果你建立表時，沒有指定主鍵索引，也可以在建立表後，再新增, 語句如下:

alter table 表名 add primary key (列名);

2、唯一索引 (UNIQUE)

當表的某列被指定為unique約束時，這列就是一個唯一索引。

CREATE TABLE ddd (
    id INT PRIMARY KEY auto_increment,
    NAME VARCHAR (32) UNIQUE
);

這時, name 列就是一個唯一索引。
在建立表後，再去建立唯一索引：

create unique index 索引名  on 表名 (列表..);

unique欄位可以為NULL,並可以有多NULL, 但是如果是具體內容，則不能重複.
主鍵欄位，不能為NULL,也不能重複.

3、 普通索引 (INDEX)

一般來說，普通索引的建立，是先建立表，然後在建立普通索引。

create index 索引名 on 表 (列1,列名2);

4、全文索引 (FULLTEXT)

[適用於MyISAM]sphinx + 中文分詞 coreseek [sphinx 的中文版 ]
全文索引，主要是針對對檔案，文字的檢索, 比如文章, 全文索引針對MyISAM有用.
如何使用全文索引？
錯誤用法（不會使用到全文索引）：

select * from articles where body like '%mysql%'; 

證明:

explain  select * from articles where body like '%mysql%'

正確的用法是：

select * from articles where match(body) against('mysql');

1. 在mysql中fulltext 索引只針對 myisam生效
2. mysql自己提供的fulltext針對英文生效->sphinx (coreseek) 技術處理中文
3. 使用方法是 match(欄位名..) against(‘關鍵字’)
4. 全文索引一個 叫 停止詞, 因為在一個文字中，建立索引是一個無窮大的數，因此，對一些常用詞和字元，就不會建立，這些詞，稱為停止詞.

綜合使用=>複合索引

二、查詢索引

1、desc 表名

desc 表名

該方法的缺點是：　不能夠顯示索引名

2、show index(es) from 表名

show index from 表名;
show indexes from 表名;

3、show keys from 表名

show keys from 表名;

三、刪除索引

DROP INDEX index_name ON tbl_name;
alter table table_name drop index index_name;

四、修改索引

先刪除，再重新建立

五、為什麼建立索引後，速度就會變快?

log 2 N 比如N=10 就表示 該演算法搜尋10次，這10次可以搜尋的範圍是2的10次方

btree 方式檢索 次數 log2N 次數

六、索引的代價

1、佔用磁碟空間
2、對dml操作有影響，變慢

七、在哪些列上適合新增索引？

1、較頻繁的作為查詢條件欄位應該建立索引

select * from emp where empno = 1

唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件（二叉樹沒法很好的起作用）。
更新非常頻繁的欄位不適合建立索引（更改後需要重建索引）。

2、不會出現在WHERE子句中欄位不該建立索

3、總結:

滿足以下條件的欄位，才應該建立索引：
a: 肯定在where條經常使用
b: 該欄位的內容不是唯一的幾個值(比如：性別)
c: 欄位內容不是頻繁變化.、

八、使用索引的注意事項

1、對於建立的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。

// dname 左邊的列,loc就是右邊的列
alter table dept add index my_ind (dname,loc); 
//不會使用到索引
explain select * from dept where loc='aaa';
//會使用到索引
explain select * from dept where dname='aaa';

2、對於使用like的查詢，查詢如果是：’%aaa’不會使用到索引’aaa%’ 會使用到索引.

比如：

//不會使用索引
explain select * from dept where dname like '%aaa';

即：在like查詢時，關鍵的’關鍵字’ , 最前面，不能使用 % 或者 _這樣的字元， 如果一定要前面有變化的值，則考慮使用 全文索引->sphinx.

3、如果條件中有or，即使其中有條件帶索引也不會使用。

換言之，就是要求or所使用的所有欄位，都必須建立索引, 我們建議大家儘量避免使用or 關鍵字。

4、如果列型別是字串，那一定要在條件中將資料使用引號引用起來。

否則不使用索引。(新增時,字串必須”), 也就是，如果列是字串型別，就一定要用 ” 把他包括起來.

5、如果mysql估計使用全表掃描要比使用索引快，則不使用索引。

九、explain

explain 以幫助我們在不真正執行某個sql語句時，就執行mysql怎樣執行，這樣利用我們去分析sql指令.

id:SELECT識別符。這是SELECT的查詢序列號
select_type:表示查詢的型別。
- PRIMARY :子查詢中最外層查詢
- SUBQUERY : 子查詢內層第一個SELECT，結果不依賴於外部查詢
- DEPENDENT SUBQUERY:子查詢內層第一個SELECT，依賴於外部查詢
- UNION :UNION語句中第二個SELECT開始後面所有SELECT，
- SIMPLE
- UNION RESULT UNION 中合併結果
table:顯示這一步所訪問資料庫中表名稱
type:對錶訪問方式
- ALL：完整的表掃描 通常不好
- system：表僅有一行(=系統表)。這是const聯接型別的一個特例。
- const：表最多有一個匹配行
Possible_keys：該查詢可以利用的索引，如果沒有任何索引顯示 null
key:Mysql 從 Possible_keys 所選擇使用索引，表示實際使用的索引
Rows:估算出結果集行數
Extra:執行情況的描述和說明即查詢細節資訊
- No tables :Query語句中使用FROM DUAL 或不含任何FROM子句
- Using filesort :當Query中包含 ORDER BY 操作，而且無法利用索引完成排序
- Using temporary:某些操作必須使用臨時表，常見 GROUP BY ; ORDER BY
- Using where:不用讀取表中所有資訊，僅通過索引就可以獲取所需資料
- Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer:通過收集統計資訊不可能存在結果

十、索引的使用

檢視索引的使用情況：

show status like 'Handler_read%';

handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。
handler_read_rnd_next:這個值越高，說明查詢低效。

本文部分內容整理自itcast講義，在此表示感謝。
作者：jiankunking 出處：http://blog.csdn.net/jiankunking