SQL優(yōu)化索引的實操與功能
以下的文章主要介紹的是SQL優(yōu)化索引問題，在一般的數(shù)據(jù)庫中，用MySQLSQL優(yōu)化索引來對相關(guān)數(shù)據(jù)庫進行優(yōu)化的使用率是相當高的。以下的文章主要是通過索引通?？梢詭椭覀兘鉀Q大多數(shù)的SQL性能問題。
1. 索引的存儲分類
MyISAM存儲引擎的表的數(shù)據(jù)和索引時自動分開存儲的，各自是獨立的一個文件；InnoDB存儲引擎的表的數(shù)據(jù)和索引時存儲在同一表空間里面，但可以有多個文件組成。
MySQL中索引的存儲類型目前只有兩種(BTREE和HASH),具體和表的存儲引擎相關(guān)；MyISAM和InnoDB存儲引擎都只支持BTREE索引；MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。
MySQL目前不支持函數(shù)索引，但是能對列的前面某一部分進行索引，例如name字段，可以以只取name的前4個字符進行MySQLSQL優(yōu)化索引，這個特征可以大大縮小索引文件的大小。在設(shè)計表結(jié)構(gòu)的時候也可以對文本列根據(jù)此特性進行靈活設(shè)計。例如
引用
- create index ind_company2_name on company2(name(4))
2. MySQL如何使用索引
索引用于快速找出在某個列中有一特定值的行。對相關(guān)列使用索引時提高SELECT操作性能的***途徑。
查詢要使用索引最主要的條件是查詢條件中需要使用索引關(guān)鍵字，如果是多列索引，那么只有查詢條件使用了多列關(guān)
SQL優(yōu)化索引問題
一般在數(shù)據(jù)中，很多朋友很喜歡用MySQLSQL優(yōu)化索引來對數(shù)據(jù)庫優(yōu)化。通過索引通?？梢詭椭覀兘鉀Q大多數(shù)的SQL性能問題。
1. 索引的存儲分類
MyISAM存儲引擎的表的數(shù)據(jù)和索引時自動分開存儲的，各自是獨立的一個文件；InnoDB存儲引擎的表的數(shù)據(jù)和索引時存儲在同一表空間里面，但可以有多個文件組成。
MySQL中索引的存儲類型目前只有兩種(BTREE和HASH),具體和表的存儲引擎相關(guān)；MyISAM和InnoDB存儲引擎都只支持BTREE索引；MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。
MySQL目前不支持函數(shù)索引，但是能對列的前面某一部分進行索引，例如name字段，可以以只取name的前4個字符進行MySQLSQL優(yōu)化索引，這個特征可以大大縮小索引文件的大小。在設(shè)計表結(jié)構(gòu)的時候也可以對文本列根據(jù)此特性進行靈活設(shè)計。例如
引用
- create index ind_company2_name on company2(name(4))
2. MySQL如何使用索引
索引用于快速找出在某個列中有一特定值的行。對相關(guān)列使用索引時提高SELECT操作性能的***途徑。
查詢要使用索引最主要的條件是查詢條件中需要使用索引關(guān)鍵字，如果是多列索引，那么只有查詢條件使用了多列關(guān)鍵字最左邊的前綴時，才可以使用索引，否則將不能使用索引。
1. 使用索引
在MySQL中，下列幾種情況下可能使用索引。
對于創(chuàng)建的多列索引，只要查詢的條件中用到了最左邊的列，MySQLSQL優(yōu)化索引一般就會使用。
例如：
引用
我們首先按company_id ,Moneys的順序創(chuàng)建一個復(fù)合索引
- create index ind_sales2_companyid_moneys on sales2(company_id,moneys)
如果按company_id進行表查詢
引用
使用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條件查詢表，那么索引就不會被用到。
對于使用like的查詢，后面如果是常量并且只有%號不在***字符，MySQLSQL優(yōu)化索引才能會被使用例如
引用
- explain select * from company2 where name like "%3"\G;
- explain select * from company2 where name like "3%"\G;
以上兩句你可以認為是一樣的。其實是不一樣的。***句其實沒有用到索引，而第二句才能夠利用到索引。另外如果like后面跟的是一個列的名字，那么索引也不會被使用。
如果對大是文本進行搜索，使用全文索引而不用使用like"%..%"
如果列名是索引，使用column_name is null 將使用索引如
查詢name為nll的記錄就用到了索引
引用
- explain select * from company2 where name is null \G;
2. 下面一些情況存在索引但不使用索引，你可能認為它會用，但是實際上它就是沒用。
引用
1. 如果Mysql估計使用索引比全表掃描更慢，則不使用索引。
例如列key_part1均勻分布在1和100之間，下列查詢中使用索引就不是很好
- select * from table_name where key_part1 > 1 and key_part1 < 90;
2. 如果使用MEMORY/HEAP表并且where條件中不使用"="進行索引列，那么不會用到索引。heap表只有在" ="的條件下才會使用索引
3. 用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒用索引，那么涉及的索引都不會被用到
4. 如果不是索引列的***部分，那么也不會使用。
5. 如果like是以"%"開始
6. 如果列類型是字符串，那么一定記得在where條件中把字符常量值用引號引起來，否則即便這個列上有索引，Mysql也不會使用。因為MYSQL默認把輸入的常量值進行轉(zhuǎn)換以后才進行檢索。
***查看索引使用情況
如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被MySQLSQL優(yōu)化索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因為索引經(jīng)常不被使用到。Handler_read_rnd_next的值高則說明查詢運行低效，并且應(yīng)該建立索引補救。
這個值的含義是在數(shù)據(jù)文件中讀取下一行的請求數(shù)。如果正進行大量的表掃描，Handler_read_rnd_next的值較高，則通常說明表索引不正確或者寫入的查詢沒有利用索引。
還記得怎么看Handler_read_rnd_next 嗎？ 使用
- show statuts like 'Handler_read_%';
鍵字最左邊的前綴時，才可以使用索引，否則將不能使用索引。
1. 使用索引
在MySQL中，下列幾種情況下可能使用索引。
對于創(chuàng)建的多列索引，只要查詢的條件中用到了最左邊的列，MySQLSQL優(yōu)化索引一般就會使用。
例如：
引用
我們首先按company_id ,Moneys的順序創(chuàng)建一個復(fù)合索引
- create index ind_sales2_companyid_moneys on sales2(company_id,moneys)
如果按company_id進行表查詢
引用
使用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條件查詢表，那么索引就不會被用到。
對于使用like的查詢，后面如果是常量并且只有%號不在***字符，MySQLSQL優(yōu)化索引才能會被使用例如
引用
- explain select * from company2 where name like "%3"\G;
- explain select * from company2 where name like "3%"\G;
以上兩句你可以認為是一樣的。其實是不一樣的。***句其實沒有用到索引，而第二句才能夠利用到索引。另外如果like后面跟的是一個列的名字，那么索引也不會被使用。
如果對大是文本進行搜索，使用全文索引而不用使用like"%..%"
如果列名是索引，使用column_name is null 將使用索引如
查詢name為nll的記錄就用到了索引
【編輯推薦】
