MySQL索引與索引最佳化

標籤：

      說起提高資料庫效能，索引是最物美價廉的東西了。不用加記憶體，不用改程式，不用調sql，只要執行個正確的’create index’，查詢速度就可能提高百倍千倍，這可真有誘惑力。可是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的I/O。 MySQL的索引種類

Mysql索引包含：普通索引、主鍵索引、唯一索引、全文索引，為了適應不同的查詢、資料結構，提高查詢速度，選擇合適的索引方式。

1.普通索引

這是最基本的索引，它沒有任何限制。

普通索引的建立方式：

一般來說，普通索引的建立，是先建立表，然後在建立普通索引,代碼如下

create table ccc(id int unsigned,name varchar(32))create index ccc_index on ccc (id,name);

修改表結構建立索引：

ALTER mytable ADD INDEX [indexName] ON (username(length))

建立表是指定索引：

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

刪除索引：

DROP INDEX [indexName] ON ccc;

註：上述執行個體代碼中的 length，如果是CHAR，VARCHAR類型，length可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 length。　　

2.主鍵索引

主鍵沒有著明確的概念定義，其是索引的一種，並且是唯一性索引的一種，且必須定義為“PRIMARY KEY”，主鍵不能重複，一個表只能有一個主鍵。

主鍵欄位，不能為NULL,也不能重複；一般是在建表的時候同時建立主鍵索引；

當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引。

建立表時指定主鍵索引，執行個體

create table aaa(id int unsigned primary key auto_increment ,name varchar(32) not null defaul ‘’);

建表完成後建立索引：

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

3.全文索引

全文索引，主要是針對對檔案，文本的檢索, 比如文章, 全文索引針對MyISAM有用.

建立全文索引，執行個體：

CREATE TABLE articles (       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,       title VARCHAR(200),       body TEXT,       FULLTEXT (title,body)     )engine=myisam charset utf8;

全文索引的使用方式，執行個體：match（）  against（）

select * from articles where match(title,body) against(‘database’); 

註：

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

以下全文停止詞部分展示，其他可以查閱MySQL手冊

 4.唯一索引

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

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

建立表時聲明唯一索引，執行個體：

create table ddd(id int primary key auto_increment ,name varchar(32) unique);

在建立表後，再去建立唯一索引，執行個體：

create table eee(id int primary key auto_increment, name varchar(32));create unique index eee_index  on eee(name);

MySQL索引的sql操作

1.查詢索引

show index[es] from tableName;#或者show keys from tableName;

2.刪除索引

alter table tableName drop index indexName; 

3.修改索引

先刪除，再重新建立.

MySQL索引的的優點和不足不足：I.磁碟佔用；

例如MyISAM在磁碟上儲存成3個檔案，其中檔案名稱和表名都相同，但是副檔名分別為：

• .frm(儲存表定義)
• MYD(MYData，儲存資料)
• MYI(MYIndex，儲存索引) 佔用一定的磁碟空間，隨著資料的增加索引檔案也會增加

 II.對dml(update delete insert)語句的效率影響；    資料的變化，需要同時維護索引優點：

查詢速度變快，但是為什麼變快？

簡單理解：沒有索引的情況，要進行全表查詢，加上索引後利用BTree方式進行資料檢索，要檢索的資料量大大減少，索引檢索速度要快的多。

可以參考MySQL索引的實現方式，進行理解，參考文章

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

MySQL索引的應用

適合索引的情況：

• 肯定在where條經常使用
• 該欄位的內容不是唯一的幾個值(sex)
• 欄位內容不是頻繁變化.

使用索引的注意事項：

#執行個體說明：在dept表中建立符合索引# dname 左邊的列,loc就是右邊的列alter table dept add index my_ind (dname,loc); 

說明，如果我們的表中有複合索引(索引作用在多列上)， 此時我們注意:

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

explain select * from dept where loc=‘aaa‘\G  #就不會使用到索引

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

explain select * from dept where dname=‘aaa‘\G  #就會使用到索引

• 如果條件中有or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有欄位，都必須建立索引, 我們建議大家盡量避免使用or 關鍵字；

• select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45#上述條件出現的條件欄位必須建立索引

   

• 對於使用like的查詢，查詢如果是  ‘%aaa’ 不會使用到索引，利用‘aaa%就可以’；

• explain select * from dept where dname like ‘%aaa‘\G  #不能使用索引，即，在like查詢時，關鍵的 ‘關鍵字’ , 最前面，不能使用 % 或者 _這樣的字元.， 如果一定要前面有變化的值，則考慮使用 全文索引->sphinx.

• 如果列類型是字串，那一定要在條件中將資料使用引號引用起來。否則不使用索引。(添加時,字串必須’’)，也就是，如果列是字串類型，就一定要用 ‘’ 把他包括起來；
• 如果mysql估計使用全表掃描要比使用索引快，則不使用索引。

 explain工具介紹

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

 

•Possible_key :該查詢可以利用的索引，如果沒有任何索引可以使用null•Key                : Mysql 從 Possible_key 中所選擇使用索引•Key_len         :被選中使用索引的索引長度•Rows             :顯示MySQL認為它執行查詢時必須檢查的行數

•extra   :查詢細節資訊

No tables 

Query語句中使用FROM DUAL 或不含任何FROM子句

Using filesort 

當Query中包含 ORDER BY 操作，而且無法利用索引完成排序，

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer通過收集統計資訊不可能存在結果Using temporary某些操作必須使用暫存資料表，常見 GROUP BY  ; ORDER BYUsing where不用讀取表中所有資訊，僅通過索引就可以擷取所需資料; 如何查看索引使用的情況

show status like ‘Handler_read%’;

大家可以注意：
handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。

handler_read_rnd_next:這個值越高，說明查詢低效。

MySQL索引與索引最佳化