SQL優(yōu)化索引的實(shí)操與功能
以下的文章主要介紹的是SQL優(yōu)化索引問(wèn)題，在一般的數(shù)據(jù)庫(kù)中，用MySQLSQL優(yōu)化索引來(lái)對(duì)相關(guān)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化的使用率是相當(dāng)高的。以下的文章主要是通過(guò)索引通常可以幫助我們解決大多數(shù)的SQL性能問(wèn)題。
1. 索引的存儲(chǔ)分類(lèi)
MyISAM存儲(chǔ)引擎的表的數(shù)據(jù)和索引時(shí)自動(dòng)分開(kāi)存儲(chǔ)的，各自是獨(dú)立的一個(gè)文件；InnoDB存儲(chǔ)引擎的表的數(shù)據(jù)和索引時(shí)存儲(chǔ)在同一表空間里面，但可以有多個(gè)文件組成。
MySQL中索引的存儲(chǔ)類(lèi)型目前只有兩種(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)行MySQLSQL優(yōu)化索引，這個(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操作性能的***途徑。
查詢(xún)要使用索引最主要的條件是查詢(xún)條件中需要使用索引關(guān)鍵字，如果是多列索引，那么只有查詢(xún)條件使用了多列關(guān)
SQL優(yōu)化索引問(wèn)題
一般在數(shù)據(jù)中，很多朋友很喜歡用MySQLSQL優(yōu)化索引來(lái)對(duì)數(shù)據(jù)庫(kù)優(yōu)化。通過(guò)索引通?？梢詭椭覀兘鉀Q大多數(shù)的SQL性能問(wèn)題。
1. 索引的存儲(chǔ)分類(lèi)
MyISAM存儲(chǔ)引擎的表的數(shù)據(jù)和索引時(shí)自動(dòng)分開(kāi)存儲(chǔ)的，各自是獨(dú)立的一個(gè)文件；InnoDB存儲(chǔ)引擎的表的數(shù)據(jù)和索引時(shí)存儲(chǔ)在同一表空間里面，但可以有多個(gè)文件組成。
MySQL中索引的存儲(chǔ)類(lèi)型目前只有兩種(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)行MySQLSQL優(yōu)化索引，這個(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操作性能的***途徑。
查詢(xún)要使用索引最主要的條件是查詢(xún)條件中需要使用索引關(guān)鍵字，如果是多列索引，那么只有查詢(xún)條件使用了多列關(guān)鍵字最左邊的前綴時(shí)，才可以使用索引，否則將不能使用索引。
1. 使用索引
在MySQL中，下列幾種情況下可能使用索引。
對(duì)于創(chuàng)建的多列索引，只要查詢(xún)的條件中用到了最左邊的列，MySQLSQL優(yōu)化索引一般就會(huì)使用。
例如：
引用
我們首先按company_id ,Moneys的順序創(chuàng)建一個(gè)復(fù)合索引
- create index ind_sales2_companyid_moneys on sales2(company_id,moneys)
如果按company_id進(jìn)行表查詢(xú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的組合條件，索引仍然能用到，這就是索引的前綴特性。但是如果只按照moneys條件查詢(xún)表，那么索引就不會(huì)被用到。
對(duì)于使用like的查詢(xún)，后面如果是常量并且只有%號(hào)不在***字符，MySQLSQL優(yōu)化索引才能會(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 將使用索引如
查詢(xún)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之間，下列查詢(xún)中使用索引就不是很好
- select * from table_name where key_part1 > 1 and key_part1 < 90;
2. 如果使用MEMORY/HEAP表并且where條件中不使用"="進(jìn)行索引列，那么不會(huì)用到索引。heap表只有在" ="的條件下才會(huì)使用索引
3. 用or分割開(kāi)的條件，如果or前的條件中的列有索引，而后面的列中沒(méi)用索引，那么涉及的索引都不會(huì)被用到
4. 如果不是索引列的***部分，那么也不會(huì)使用。
5. 如果like是以"%"開(kāi)始
6. 如果列類(lèi)型是字符串，那么一定記得在where條件中把字符常量值用引號(hào)引起來(lái)，否則即便這個(gè)列上有索引，Mysql也不會(huì)使用。因?yàn)镸YSQL默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索。
***查看索引使用情況
如果索引正在工作，Handler_read_key的值將很高，這個(gè)值代表了一個(gè)行被MySQLSQL優(yōu)化索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因?yàn)樗饕?jīng)常不被使用到。Handler_read_rnd_next的值高則說(shuō)明查詢(xún)運(yùn)行低效，并且應(yīng)該建立索引補(bǔ)救。
這個(gè)值的含義是在數(shù)據(jù)文件中讀取下一行的請(qǐng)求數(shù)。如果正進(jìn)行大量的表掃描，Handler_read_rnd_next的值較高，則通常說(shuō)明表索引不正確或者寫(xiě)入的查詢(xún)沒(méi)有利用索引。
還記得怎么看Handler_read_rnd_next 嗎？ 使用
- show statuts like 'Handler_read_%';
鍵字最左邊的前綴時(shí)，才可以使用索引，否則將不能使用索引。
1. 使用索引
在MySQL中，下列幾種情況下可能使用索引。
對(duì)于創(chuàng)建的多列索引，只要查詢(xún)的條件中用到了最左邊的列，MySQLSQL優(yōu)化索引一般就會(huì)使用。
例如：
引用
我們首先按company_id ,Moneys的順序創(chuàng)建一個(gè)復(fù)合索引
- create index ind_sales2_companyid_moneys on sales2(company_id,moneys)
如果按company_id進(jìn)行表查詢(xú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的組合條件，索引仍然能用到，這就是索引的前綴特性。但是如果只按照moneys條件查詢(xún)表，那么索引就不會(huì)被用到。
對(duì)于使用like的查詢(xún)，后面如果是常量并且只有%號(hào)不在***字符，MySQLSQL優(yōu)化索引才能會(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 將使用索引如
查詢(xún)name為nll的記錄就用到了索引
【編輯推薦】
