以下的文章主要介紹的是MySQL SQL優(yōu)化的索引問(wèn)題，我們大家都知道在一般的數(shù)據(jù)中，很多人喜歡用相關(guān)索引來(lái)對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化。我們通過(guò)相關(guān)索引在一般的情況下，是幫助我們解決大多數(shù)的MySQL SQL性能問(wèn)題。

1. 索引的存儲(chǔ)分類

MyISAM存儲(chǔ)引擎的表的數(shù)據(jù)和索引時(shí)自動(dòng)分開存儲(chǔ)的，各自是獨(dú)立的一個(gè)文件；InnoDB存儲(chǔ)引擎的表的數(shù)據(jù)和索引時(shí)存儲(chǔ)在同一表空間里面，但可以有多個(gè)文件組成。

MySQL中索引的存儲(chǔ)類型目前只有兩種(BTREE和HASH),具體和表的存儲(chǔ)引擎相關(guān)；MyISAM和InnoDB存儲(chǔ)引擎都只支持BTREE索引；MEMORY/HEAP存儲(chǔ)引擎可以支持HASH和BTREE索引。

MySQL目前不支持函數(shù)索引，但是能對(duì)列的前面某一部分進(jìn)行索引，例如name字段，可以以只取name的前4個(gè)字符進(jìn)行索引，這個(gè)特征可以大大縮小索引文件的大小。在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候也可以對(duì)文本列根據(jù)此特性進(jìn)行靈活設(shè)計(jì)。例如

引用

create index ind_company2_name on company2(name(4))  

2. MySQL如何使用索引

索引用于快速找出在某個(gè)列中有一特定值的行。對(duì)相關(guān)列使用索引時(shí)提高SELECT操作性能的最佳途徑。
查詢要使用索引最主要的條件是查詢條件中需要使用索引關(guān)鍵字，如果是多列MySQL SQL優(yōu)化索引，那么只有查詢條件使用了多列關(guān)鍵字最左邊的前綴時(shí)，才可以使用索引，否則將不能使用索引。

1. 使用索引

在MySQL中，下列幾種情況下可能使用索引。

對(duì)于創(chuàng)建的多列索引，只要查詢的條件中用到了最左邊的列，索引一般就會(huì)使用。

例如：

引用

我們首先按company_id ,Moneys的順序創(chuàng)建一個(gè)復(fù)合索引

create index ind_sales2_companyid_moneys on sales2(company_id,moneys)  

如果按company_id進(jìn)行表查詢

引用

使用explain來(lái)分析下

explain select * from sales2 where company_id =2000 \G;   
explain select * from sales2 where moneys = 1\G;  

通過(guò)上面你可以發(fā)現(xiàn)即便where條件中不是用company_id 和 moneys的組合條件，MySQL SQL優(yōu)化之索引仍然能用到，這就是索引的前綴特性。但是如果只按照moneys條件查詢表，那么索引就不會(huì)被用到。

對(duì)于使用like的查詢，后面如果是常量并且只有%號(hào)不在第一字符，索引才能會(huì)被使用例如

引用

explain select * from company2 where name like "%3"\G;   
explain select * from company2 where name like "3%"\G;  

以上兩句你可以認(rèn)為是一樣的。其實(shí)是不一樣的。第一句其實(shí)沒(méi)有用到索引，而第二句才能夠利用到索引。另外如果like后面跟的是一個(gè)列的名字，那么索引也不會(huì)被使用。

如果對(duì)大是文本進(jìn)行搜索，使用全文索引而不用使用like"%..%" 如果列名是索引，使用column_name is null 將使用MySQL SQL優(yōu)化之索引如 查詢name為nll的記錄就用到了索引

引用

explain select * from company2 where name is null \G;  

2. 下面一些情況存在索引但不使用索引，你可能認(rèn)為它會(huì)用，但是實(shí)際上它就是沒(méi)用。

引用

1. 如果Mysql估計(jì)使用索引比全表掃描更慢，則不使用索引。

例如列key_part1均勻分布在1和100之間，下列查詢中使用索引就不是很好

select * from table_name where key_part1 > 1 and key_part1 < 90;  

2. 如果使用MEMORY/HEAP表并且where條件中不使用"="進(jìn)行索引列，那么不會(huì)用到索引。heap表只有在" ="的條件下才會(huì)使用索引

3. 用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒(méi)用MySQL SQL優(yōu)化之索引，那么涉及的索引都不會(huì)被用到

4. 如果不是索引列的第一部分，那么也不會(huì)使用。

5. 如果like是以"%"開始

6. 如果列類型是字符串，那么一定記得在where條件中把字符常量值用引號(hào)引起來(lái)，否則即便這個(gè)列上有索引，Mysql也不會(huì)使用。因?yàn)镸YSQL默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索。
 

最后查看索引使用情況

如果索引正在工作，Handler_read_key的值將很高，這個(gè)值代表了一個(gè)行被索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因?yàn)樗饕?jīng)常不被使用到。Handler_read_rnd_next的值高則說(shuō)明查詢運(yùn)行低效，并且應(yīng)該建立索引補(bǔ)救。這個(gè)值的含義是在數(shù)據(jù)文件中讀取下一行的請(qǐng)求數(shù)。如果正進(jìn)行大量的表掃描，Handler_read_rnd_next的值較高，則通常說(shuō)明表索引不正確或者寫入的查詢沒(méi)有利用MySQL SQL優(yōu)化之索引。

還記得怎么看Handler_read_rnd_next 嗎？ 使用

show statuts like 'Handler_read_%';  

以上的相關(guān)內(nèi)容就是對(duì)MySQL SQL優(yōu)化的索引問(wèn)題的介紹，望你能有所收獲。

【編輯推薦】

1. MySQL5 綠色版windows下安裝經(jīng)驗(yàn)總結(jié)
2. 建立MYSQL 外鍵的必備前提
3. MySQL數(shù)據(jù)庫(kù)的可視化管理工具介紹
4. MySQL數(shù)據(jù)庫(kù)分區(qū)的優(yōu)點(diǎn)表現(xiàn)
5. Mysql數(shù)據(jù)庫(kù)編碼的問(wèn)題的解析