MySQL 5.0中索引的正確設(shè)計(jì)和使用
此文章主要向大家描述的是MySQL 5.0中索引的正確設(shè)計(jì)與具體使用方案的描述， 在一般在數(shù)據(jù)中，很多朋友對數(shù)據(jù)庫進(jìn)行優(yōu)化一般用的是索引。我們通過索引一般可以解決大多數(shù)的SQL性能問題。
MySQL 5.0中索引的設(shè)計(jì)和使用 1. 索引的存儲分類
MyISAM存儲引擎的表的數(shù)據(jù)和索引時(shí)自動(dòng)分開存儲的，各自是獨(dú)立的一個(gè)文件；InnoDB存儲引擎的表的數(shù)據(jù)和索引時(shí)存儲在同一表空間里面，但可以有多個(gè)文件組成。
MySQL中索引的存儲類型目前只有兩種(BTREE和HASH),具體和表的存儲引擎相關(guān)；MyISAM和InnoDB存儲引擎都只支持BTREE索引；MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。
MySQL目前不支持函數(shù)索引，但是能對列的前面某一部分進(jìn)行索引，例如name字段，可以以只取name的前4個(gè)字符進(jìn)行索引，這個(gè)特征可以大大縮小索引文件的大小。在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候也可以對文本列根據(jù)此特性進(jìn)行靈活設(shè)計(jì)。例如
引用
- create index ind_company2_name on company2(name(4))
MySQL 5.0中索引的設(shè)計(jì)和使用 2. MySQL如何使用索引
索引用于快速找出在某個(gè)列中有一特定值的行。對相關(guān)列使用索引時(shí)提高SELECT操作性能的***途徑。
查詢要使用索引最主要的條件是查詢條件中需要使用索引關(guān)鍵字，如果是多列索引，那么只有查詢條件使用了多列關(guān)鍵字最左邊的前綴時(shí)，才可以使用索引，否則將不能使用索引。
1. 使用索引
在MySQL中，下列幾種情況下可能使用索引。
對于創(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來分析下
- explain select * from sales2 where company_id =2000 \G;
- explain select * from sales2 where moneys = 1\G;
通過上面你可以發(fā)現(xiàn)即便where條件中不是用company_id 和 moneys的組合條件，索引仍然能用到，這就是索引的前綴特性。但是如果只按照moneys條件查詢表，那么索引就不會(huì)被用到。
對于使用like的查詢，后面如果是常量并且只有%號不在***字符，索引才能會(huì)被使用例如
引用
- explain select * from company2 where name like "%3"\G;
- explain select * from company2 where name like "3%"\G;
以上兩句你可以認(rèn)為是一樣的。其實(shí)是不一樣的。***句其實(shí)沒有用到索引，而第二句才能夠利用到索引。另外如果like后面跟的是一個(gè)列的名字，那么索引也不會(huì)被使用。
如果對大是文本進(jìn)行搜索，使用全文索引而不用使用like"%..%"
如果列名是索引，使用column_name is null 將使用索引如
查詢name為nll的記錄就用到了索引
引用
- explain select * from company2 where name is null \G;
2. 下面一些情況存在索引但不使用索引，你可能認(rèn)為它會(huì)用，但是實(shí)際上它就是沒用。
引用
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前的條件中的列有索引，而后面的列中沒用索引，那么涉及的索引都不會(huì)被用到
4. 如果不是索引列的***部分，那么也不會(huì)使用。
5. 如果like是以"%"開始
6. 如果列類型是字符串，那么一定記得在where條件中把字符常量值用引號引起來，否則即便這個(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的值高則說明查詢運(yùn)行低效，并且應(yīng)該建立索引補(bǔ)救。
這個(gè)值的含義是在數(shù)據(jù)文件中讀取下一行的請求數(shù)。如果正進(jìn)行大量的表掃描，Handler_read_rnd_next的值較高，則通常說明表索引不正確或者寫入的查詢沒有利用索引。
以上的相關(guān)內(nèi)容就是對MySQL 5.0中索引的設(shè)計(jì)和使用的介紹，望你能有所收獲。
【編輯推薦】
