- 相關(guān)推薦
SQL優(yōu)化大全
SQL是最重要的關(guān)系數據庫操作語(yǔ)言，并且它的影響已經(jīng)超出數據庫領(lǐng)域，得到其他領(lǐng)域的重視和采用，如人工智能領(lǐng)域的數據檢索，第四代軟件開(kāi)發(fā)工具中嵌入SQL的語(yǔ)言等。那么SQL怎么優(yōu)化?一起來(lái)看看優(yōu)化步驟吧!
1. 優(yōu)化SQL步驟
1. 通過(guò) show status和應用特點(diǎn)了解各種 SQL的執行頻率
通過(guò) SHOW STATUS 可以提供服務(wù)器狀態(tài)信息，也可以使用 mysqladmin extende d-status 命令獲得。 SHOW STATUS 可以根據需要顯示 session 級別的統計結果和 global級別的統計結果。
如顯示當前session： SHOW STATUS like "Com_%"; 全局級別：show global status;
以下幾個(gè)參數對 Myisam 和 Innodb 存儲引擎都計數：
1. Com_select 執行 select 操作的次數，一次查詢(xún)只累加 1 ；
2. Com_insert 執行 insert 操作的次數，對于批量插入的 insert 操作，只累加一次 ；
3. Com_update 執行 update 操作的次數；
4. Com_delete 執行 delete 操作的次數；
以下幾個(gè)參數是針對 Innodb 存儲引擎計數的，累加的算法也略有不同：
1. Innodb_rows_read select 查詢(xún)返回的行數；
2. Innodb_rows_inserted 執行 Insert 操作插入的行數；
3. Innodb_rows_updated 執行 update 操作更新的行數；
4. Innodb_rows_deleted 執行 delete 操作刪除的行數；
通過(guò)以上幾個(gè)參數，可以很容易的了解當前數據庫的應用是以插入更新為主還 是以查詢(xún)操作為主，以及各種類(lèi)型的 SQL大致的執行比例是多少。對于更新操作的計 數，是對執行次數的計數，不論提交還是回滾都會(huì )累加。
對于事務(wù)型的應用，通過(guò) Com_commit 和 Com_rollback 可以了解事務(wù)提交和回 滾的情況，對于回滾操作非常頻繁的數據庫，可能意味著(zhù)應用編寫(xiě)存在問(wèn)題。此外，以下幾個(gè)參數便于我們了解數據庫的基本情況：
1. Connections 試圖連接 Mysql 服務(wù)器的次數
2. Uptime 服務(wù)器工作時(shí)間
3. Slow_queries 慢查詢(xún)的次數
2. 定位執行效率較低的SQL語(yǔ)句
可以通過(guò)以下兩種方式定位執行效率較低的 SQL 語(yǔ)句：
1. 可以通過(guò)慢查詢(xún)日志定位那些執行效率較低的 sql 語(yǔ)句，用 --log-slow-queries[=file_name] 選項啟動(dòng)時(shí)， mysqld 寫(xiě)一個(gè)包含所有執行時(shí)間超過(guò)long_query_time 秒的 SQL 語(yǔ)句的日志文件�？梢枣溄拥焦芾砭S護中的相關(guān)章節。
2. 使用 show processlist查看當前MYSQL的線(xiàn)程， 命令慢查詢(xún)日志在查詢(xún)結束以后才紀錄，所以在應用反映執行效率出現問(wèn)題的時(shí)候查 詢(xún)慢查詢(xún)日志并不能定位問(wèn)題，可以使用 show processlist 命令查看當前 MySQL 在進(jìn)行的線(xiàn)程，包括線(xiàn)程的狀態(tài)，是否鎖表等等，可以實(shí)時(shí)的查看 SQL 執行情況， 同時(shí)對一些鎖表操作進(jìn)行優(yōu)化。
3. 通過(guò)EXPLAIN 分析低效 SQL的執行計劃：
通過(guò)以上步驟查詢(xún)到效率低的 SQL 后，我們可以通過(guò) explain 或者 desc 獲取MySQL 如何執行 SELECT 語(yǔ)句的信息，包括 select 語(yǔ)句執行過(guò)程表如何連接和連接 的次序。
2. MySQL索引
1. mysql如何使用索引
索引用于快速找出在某個(gè)列中有一特定值的行。對相關(guān)列使用索引是提高SELECT 操作性能的最佳途徑。
查詢(xún)要使用索引最主要的條件是查詢(xún)條件中需要使用索引關(guān)鍵字，如果是多列 索引，那么只有查詢(xún)條件使用了多列關(guān)鍵字最左邊的前綴時(shí)（前綴索引），才可以使用索引，否則 將不能使用索引。
下列情況下， Mysql 不會(huì )使用已有的索引：
1、如果 mysql 估計使用索引比全表掃描更慢，則不使用索引。例如：如果 key_part 1均勻分布在 1 和 100 之間，下列查詢(xún)中使用索引就不是很好：
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
2、如果使用 heap 表并且 where 條件中不用＝索引列，其他 > 、 < 、 >= 、 <= 均不使 用索引（MyISAM和innodb表使用索引）；
3、使用or分割的條件，如果or前的條件中的列有索引，后面的列中沒(méi)有索引，那么涉及到的索引都不會(huì )使用。
4、如果創(chuàng )建復合索引，如果條件中使用的列不是索引列的第一部分；（不是前綴索引）
4、如果 like 是以％開(kāi)始；
5、對 where 后邊條件為字符串的一定要加引號，字符串如果為數字 mysql 會(huì )自動(dòng)轉 為字符串，但是不使用索引。
2. 查看索引使用情況
如果索引正在工作， Handler_read_key 的值將很高，這個(gè)值代表了一個(gè)行被索引值讀的次數，很低的值表明增加索引得到的性能改善不高，因為索引并不經(jīng)常使 用。
Handler_read_rnd_next 的值高則意味著(zhù)查詢(xún)運行低效，并且應該建立索引補救。這個(gè)值的含義是在數據文件中讀下一行的請求數。如果你正進(jìn)行大量的表掃描，
該值較高。通常說(shuō)明表索引不正確或寫(xiě)入的查詢(xún)沒(méi)有利用索引。
語(yǔ)法：
mysql> show status like 'Handler_read%';
3. 具體優(yōu)化查詢(xún)語(yǔ)句
1. 查詢(xún)進(jìn)行優(yōu)化，應盡量避免全表掃描
對查詢(xún)進(jìn)行優(yōu)化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引
. 嘗試下面的技巧以避免優(yōu)化器錯選了表掃描：
· 使用ANALYZE TABLEtbl_name為掃描的表更新關(guān)鍵字分布。
· 對掃描的表使用FORCEINDEX告知MySQL，相對于使用給定的索引表掃描將非常耗時(shí)。
SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name；
· 用--max-seeks-for-key=1000選項啟動(dòng)mysqld或使用SET max_seeks_for_key=1000告知優(yōu)化器假設關(guān)鍵字掃描不會(huì )超過(guò)1,000次關(guān)鍵字搜索。
1). 應盡量避免在 where 子句中對字段進(jìn)行 null 值判斷
否則將導致引擎放棄使用索引而進(jìn)行全表掃描，如：
select id from t where num is null
NULL對于大多數數據庫都需要特殊處理，MySQL也不例外，它需要更多的代碼，更多的檢查和特殊的索引邏輯，有些開(kāi)發(fā)人員完全沒(méi)有意識到，創(chuàng )建表時(shí)NULL是默認值，但大多數時(shí)候應該使用NOT NULL，或者使用一個(gè)特殊的值，如0，-1作為默 認值。
不能用null作索引，任何包含null值的列都將不會(huì )被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列 就會(huì )從索引中排除。也就是說(shuō)如果某列存在空值，即使對該列建索引也不會(huì )提高性能。 任何在where子句中使用is null或is not null的語(yǔ)句優(yōu)化器是不允許使用索引的。
此例可以在num上設置默認值0，確保表中num列沒(méi)有null值，然后這樣查詢(xún)：
select id from t where num=0
2). 應盡量避免在 where 子句中使用!=或<>操作符
否則將引擎放棄使用索引而進(jìn)行全表掃描。
MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時(shí)候的LIKE。
可以在LIKE操作中使用索引的情形是指另一個(gè)操作數不是以通配符（%或者_）開(kāi)頭的情形。例如:
SELECT id FROM t WHERE col LIKE 'Mich%'; # 這個(gè)查詢(xún)將使用索引，
SELECT id FROM t WHERE col LIKE '%ike'; #這個(gè)查詢(xún)不會(huì )使用索引。
3). 應盡量避免在 where 子句中使用 or 來(lái)連接條件
否則將導致引擎放棄使用索引而進(jìn)行全表掃描，如：
select id from t where num=10 or num=20
可以 使用UNION合并查詢(xún)： select id from t where num=10 union all select id from t where num=20
在某些情況下，or條件可以避免全表掃描的。
1 .where 語(yǔ)句里面如果帶有or條件, myisam表能用到索引， innodb不行。
2 .必須所有的or條件都必須是獨立索引
mysql or條件可以使用索引而避免全表
4) .in 和 not in 也要慎用，否則會(huì )導致全表掃描，
如：
select id from t where num in(1,2,3)
對于連續的數值，能用 between 就不要用 in 了：
Select id from t where num between 1 and 3
5).下面的查詢(xún)也將導致全表掃描：
select id from t where name like '%abc%' 或者
select id from t where name like '%abc' 或者
若要提高效率，可以考慮全文檢索。
而select id from t where name like 'abc%' 才用到索引
7). 如果在 where 子句中使用參數，也會(huì )導致全表掃描。
因為SQL只有在運行時(shí)才會(huì )解析局部變量，但優(yōu)化程序不能將訪(fǎng)問(wèn)計劃的選擇推 遲到運行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪(fǎng)問(wèn)計劃，變量的值還是未知的，因而無(wú)法作為索引選擇的輸入項。如下面語(yǔ)句將進(jìn)行全表掃描：
select id from t where num=@num
可以改為強制查詢(xún)使用索引： select id from t with(index(索引名)) where num=@num
8). 應盡量避免在 where 子句中對字段進(jìn)行表達式操作，
這將導致引擎放棄使用索引而進(jìn)行全表掃描。如：
select id from t where num/2=100
應改為: select id from t where num=100*2
9). 應盡量避免在where子句中對字段進(jìn)行函數操作，
這將導致引擎放棄使用索引而進(jìn)行全表掃描。如：
select id from t where substring(name,1,3)='abc' --name
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’
生成的id 應改為:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10).不要在 where 子句中的“=”左邊進(jìn)行函數、算術(shù)運算或其他表達式運算，
否則系統將可能無(wú)法正確使用索引。
11). 索引字段不是復合索引的前綴索引
例如 在使用索引字段作為條件時(shí)，如果該索引是復合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統使用該索引，否則該索引將不會(huì )被使用，并且應盡可能的讓字段順序與索引順序相一致。
2 .其他一些注意優(yōu)化：
12). 不要寫(xiě)一些沒(méi)有意義的查詢(xún)，
如需要生成一個(gè)空表結構：
select col1,col2 into #t from t where 1=0
這類(lèi)代碼不會(huì )返回任何結果集，但是會(huì )消耗系統資源的，應改成這樣： create table #t(...)
13). 很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：
select num from a where num in(select num from b)
用下面的語(yǔ)句替換：
select num from a where exists(select 1 from b where num=a.num)
14). 并不是所有索引對查詢(xún)都有效，
SQL是根據表中數據來(lái)進(jìn)行查詢(xún)優(yōu)化的，當索引列有大量數據重復時(shí)，SQL查詢(xún)可能不會(huì )去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢(xún)效率起不了作用。
15). 索引并不是越多越好，
索引固然可以提高相應的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因為 insert 或 update 時(shí)有可能會(huì )重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數最好不要超過(guò)6個(gè)，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
16).應盡可能的避免更新 clustered 索引數據列，
因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個(gè)表記錄的順序的調整，會(huì )耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。
17).盡量使用數字型字段，
若只含數值信息的字段盡量不要設計為字符型，這會(huì )降低查詢(xún)和連接的性能，并會(huì )增加存儲開(kāi)銷(xiāo)。這是因為引擎在處理查詢(xún)和連接時(shí)會(huì )逐個(gè)比較字符串中每一個(gè)字符，而對于數字型而言只需要比較一次就夠了。
18).盡可能的使用 varchar/nvarchar 代替 char/nchar ，
因為首先變長(cháng)字段存儲空間小，可以節省存儲空間，其次對于查詢(xún)來(lái)說(shuō)，在一個(gè)相對較小的字段內搜索效率顯然要高些。
19).最好不要使用"*"返回所有： select * from t ，
用具體的字段列表代替“*”，不要返回用不到的任何字段。
3. 臨時(shí)表的問(wèn)題：
20). 盡量使用表變量來(lái)代替臨時(shí)表。
如果表變量包含大量數據，請注意索引非常有限（只有主鍵索引）。
21).避免頻繁創(chuàng )建和刪除臨時(shí)表，以減少系統表資源的消耗。
22).臨時(shí)表并不是不可使用，
適當地使用它們可以使某些例程更有效，例如，當需要重復引用大型表或常用表中的某個(gè)數據集時(shí)。但是，對于一次性事件，最好使用導出表。
23).在新建臨時(shí)表時(shí)，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；
如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。
24). 如果使用到了臨時(shí)表，在存儲過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統表的較長(cháng)時(shí)間鎖定。
4. 游標的問(wèn)題：
25).盡量避免使用游標，
因為游標的效率較差，如果游標操作的數據超過(guò)1萬(wàn)行，那么就應該考慮改寫(xiě)。
26).使用基于游標的方法或臨時(shí)表方法之前，
應先尋找基于集的解決方案來(lái)解決問(wèn)題，基于集的方法通常更有效。
27).與臨時(shí)表一樣，游標并不是不可使用。
對小型數據集使用 FAST_FORWARD 游標通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數據時(shí)。在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開(kāi)發(fā)時(shí)間允許，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。
28).在所有的存儲過(guò)程和觸發(fā)器的開(kāi)始處設置 SET NOCOUNT ON ，在結束時(shí)設置 SET NOCOUNT OFF 。
無(wú)需在執行存儲過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶(hù)端發(fā)送 DONE_IN_PROC 消息。
5. 事務(wù)的問(wèn)題：
29).盡量避免大事務(wù)操作，提高系統并發(fā)能力。
6. 數據量的問(wèn)題
30).盡量避免向客戶(hù)端返回大數據量，若數據量過(guò)大，應該考慮相應需求是否合理。
7. COUNT優(yōu)化：
31) count(*) 優(yōu)于count(1)和count(primary_key)
很多人為了統計記錄條數，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他們認為這樣性能更好，其實(shí)這是一個(gè)誤區。對于有些場(chǎng)景，這樣做可能性能會(huì )更差，應為數據庫對 count(*) 計數操作做了一些特別的優(yōu)化。
32）count(column) 和 count(*) 是不一樣的
這個(gè)誤區甚至在很多的資深工程師或者是 DBA 中都普遍存在，很多人都會(huì )認為這是理所當然的。實(shí)際上，count(column) 和 count(*) 是一個(gè)完全不一樣的操作，所代表的意義也完全不一樣。
count(column) 是表示結果集中有多少個(gè)column字段不為空的記錄
count(*) 是表示整個(gè)結果集有多少條記錄
1)innodb引擎在統計方面和myisam是不同的，Myisam內置了一個(gè)計數器，
Count(*)在沒(méi)有查詢(xún)條件的情況下使用 select count(*) from table 的時(shí)候，Myisam直接可以從計數器中取出數據。而innodb必須全表掃描一次方能得到總的數量
2. 但是當有查詢(xún)條件的時(shí)候，兩者的查詢(xún)效率一致。
4. 主鍵索引count(*)的時(shí)候之所以慢
InnoDB引擎:
[1] 數據文件和索引文件存儲在一個(gè)文件中，主鍵索引默認直接指向數據存儲位置。
[2] 二級索引存儲指定字段的索引，實(shí)際的指向位置是主鍵索引。當我們通過(guò)二級索引統計數據的時(shí)候，無(wú)需掃描數據文件；而通過(guò)主鍵索引統計數據時(shí)，由于主鍵索引與數據文件存放在一起，所以每次都會(huì )掃描數據文件，所以主鍵索引統計沒(méi)有二級索引效率高。
[3] 由于主鍵索引直接指向實(shí)際數據，所以當我們通過(guò)主鍵id查詢(xún)數據時(shí)要比通過(guò)二級索引查詢(xún)數據要快。
l MyAsm引擎
[1] 該引擎把每個(gè)表都分為幾部分存儲，比如用戶(hù)表，包含user.frm，user.MYD和user.MYI。
[2] User.frm負責存儲表結構
[3] User.MYD負責存儲實(shí)際的數據記錄，所有的用戶(hù)記錄都存儲在這個(gè)文件中
[4] User.MYI負責存儲用戶(hù)表的所有索引，這里也包括主鍵索引。
8. 優(yōu)化order by語(yǔ)句
基于索引的排序
MySQL的弱點(diǎn)之一是它的排序。雖然MySQL可以在1秒中查詢(xún)大約15,000條記錄，但由于MySQL在查詢(xún)時(shí)最多只能使用一個(gè)索引。因此，如果WHERE條件已經(jīng)占用了索引，那么在排序中就不使用索引了，這將大大降低查詢(xún)的速度。我們可以看看如下的SQL語(yǔ)句:
SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
在以上的SQL的WHERE子句中已經(jīng)使用了NAME字段上的索引，因此，在對SALE_DATE進(jìn)行排序時(shí)將不再使用索引。為了解決這個(gè)問(wèn)題，我們可以對SALES表建立復合索引:
ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME,SALE_DATE)
這樣再使用上述的SELECT語(yǔ)句進(jìn)行查詢(xún)時(shí)速度就會(huì )大副提升。但要注意，在使用這個(gè)方法時(shí)，要確保WHERE子句中沒(méi)有排序字段，在上例中就是不能用SALE_DATE進(jìn)行查詢(xún)，否則雖然排序快了，但是SALE_DATE字段上沒(méi)有單獨的索引，因此查詢(xún)又會(huì )慢下來(lái)。
在某些情況中， MySQL可以使用一個(gè)索引來(lái)滿(mǎn)足 ORDER BY子句，而不需要額外的排序。 where條件和order by使用相同的索引，并且order by 的順序和索引順序相 同，并且order by的字段都是升序或者都是降序。例如：下列sql可以使用索引。
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情況不使用索引：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ； --order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ；-- 用于查詢(xún)行的關(guān)鍵字與 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2 ；-- 對不同的關(guān)鍵字使用 ORDER BY ：
9. 優(yōu)化GROUP BY
默認情況下， MySQL 排序所有 GROUP BY col1 ， col2 ， .... 。查詢(xún)的方法如同在查詢(xún)中指定 ORDER BY col1 ， col2 ， ... 。如果顯式包括一個(gè)包含相同的列的 ORDER BY
子句， MySQL 可以毫不減速地對它進(jìn)行優(yōu)化，盡管仍然進(jìn)行排序。如果查詢(xún)包括 GROUP BY 但你想要避免排序結果的消耗，你可以指定 ORDER BY NULL禁止排序。
例如 ：
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
10. 優(yōu)化 OR
具體詳解看：mysql or條件可以使用索引而避免全表
4. Explain解釋說(shuō)明
explain顯示了mysql如何使用索引來(lái)處理select語(yǔ)句以及連接表�？梢詭椭x擇更好的索引和寫(xiě)出更優(yōu)化的查詢(xún)語(yǔ)句。
使用方法，在select語(yǔ)句前加上explain就可以了：
如：
explain select surname,first_name form a,b where a.id=b.id
分析結果形式如下：
table | type | possible_keys | key | key_len | ref | rows | Extra
EXPLAIN列的解釋?zhuān)?/p>
1 table:
顯示這一行的數據是關(guān)于哪張表的
2 type:
這是重要的列，顯示連接使用了何種類(lèi)型。從最好到最差的連接類(lèi)型為：system、const、eg_reg、ref、ref_or_null、 range、indexhe、 ALL。
system:表僅有一行(=系統表)。這是const聯(lián)接類(lèi)型的一個(gè)特例
const:(PRIMARY KEY或UNIQUE)
表最多有一個(gè)匹配行，它將在查詢(xún)開(kāi)始時(shí)被讀取。因為僅有一行，在這行的列值可被優(yōu)化器剩余部分認為是常數。
const表很快，因為它們只讀取一次！
const用于用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時(shí)。
在下面的查詢(xún)中，tbl_name可以用于const表：
SELECT * from tbl_name WHERE primary_key=1；
eq_reg:key
對于每個(gè)來(lái)自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類(lèi)型，除了const類(lèi)型。
它用在一個(gè)索引的所有部分被聯(lián)接使用并且索引是UNIQUE或PRIMARY KEY。
eq_ref可以用于使用= 操作符比較的帶索引的列。比較值可以為常量或一個(gè)使用在該表前面所讀取的表的列的表達式。
在下面的例子中，MySQL可以使用eq_ref聯(lián)接來(lái)處理ref_tables：
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref:key
對于每個(gè)來(lái)自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯(lián)接只使用鍵的最左邊的前綴，
或如果鍵不是UNIQUE或PRIMARY KEY（換句話(huà)說(shuō)，如果聯(lián)接不能基于關(guān)鍵字選擇單個(gè)行的話(huà)），則使用ref。
如果使用的鍵僅僅匹配少量行，該聯(lián)接類(lèi)型是不錯的。
ref可以用于使用=或<=>操作符的帶索引的列。
在下面的例子中，MySQL可以使用ref聯(lián)接來(lái)處理ref_tables：
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref_or_null:Or Is null
該聯(lián)接類(lèi)型如同ref，但是添加了MySQL可以專(zhuān)門(mén)搜索包含NULL值的行。在解決子查詢(xún)中經(jīng)常使用該聯(lián)接類(lèi)型的優(yōu)化。
在下面的例子中，MySQL可以使用ref_or_null聯(lián)接來(lái)處理ref_tables：
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
range:=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN
只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。key列顯示使用了哪個(gè)索引。
key_len包含所使用索引的最長(cháng)關(guān)鍵元素。在該類(lèi)型中ref列為NULL。
當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時(shí)，可以使用range：
SELECT * FROM tbl_name WHERE key_column = 10;
SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);
indexhe:
該聯(lián)接類(lèi)型與ALL相同，除了只有索引樹(shù)被掃描。這通常比ALL快，因為索引文件通常比數據文件小。
當查詢(xún)只使用作為單索引一部分的列時(shí)，MySQL可以使用該聯(lián)接類(lèi)型。
ALL：
對于每個(gè)來(lái)自于先前的表的行組合，進(jìn)行完整的表掃描。如果表是第一個(gè)沒(méi)標記const的表，
這通常不好，并且通常在它情況下很差。通�？梢栽黾痈嗟乃饕灰褂肁LL，
使得行能基于前面的表中的常數值或列值被檢索出。
3 possible_keys :
顯示可能應用在這張表中的索引。如果為空，沒(méi)有可能的索引�？梢詾橄嚓P(guān)的域從WHERE語(yǔ)句中
選擇一個(gè)合適的語(yǔ)句
4 key ：
實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引。很少的情況下，MYSQL會(huì )選擇優(yōu)化不足的索引 。
這種情況下，可以在SELECT語(yǔ)句中使用USEINDEX（indexname）來(lái)強制使用一個(gè)索引或者用IGNORE INDEX（indexname）來(lái)強制MYSQL忽略索引
5key_len:
使用的索引的長(cháng)度。在不損失精確性的情況下，長(cháng)度越短越好
6 ref
顯示索引的哪一列被使用了，如果可能的話(huà)，是一個(gè)常數
7 rows
MYSQL認為必須檢查的用來(lái)返回請求數據的行數 (掃描行的數量)
8 Extra
該列包含MySQL解決查詢(xún)的詳細信息
關(guān)于MYSQL如何解析查詢(xún)的額外信息。將在表4.3中討論，但這里可以看到的壞的例子是Using temporary和Using filesort，
意思MYSQL根本不能使用索引，結果是檢索會(huì )很慢
extra列返回的描述的意義
Distinct:
一旦MYSQL找到了與行相聯(lián)合匹配的行，就不再搜索了
Not exists :
MYSQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行， 就不再搜索了
面是一個(gè)可以這樣優(yōu)化的查詢(xún)類(lèi)型的例子：
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL；
假定t2.id定義為NOT NULL。在這種情況下，MySQL使用t1.id的值掃描t1并查找t2中的行。
如果MySQL在t2中發(fā)現一個(gè)匹配的行，它知道t2.id絕不會(huì )為NULL，并且不再掃描t2內有相同的id值的行。
換句話(huà)說(shuō)，對于t1的每個(gè)行，MySQL只需要在t2中查找一次，無(wú)論t2內實(shí)際有多少匹配的行。
Range checked for each Record（index map:#）
沒(méi)有找到理想的索引，因此對于從前面表中來(lái)的每一個(gè)行組合，MYSQL檢查使用哪個(gè)索引，并用它來(lái)從表中返回行。
這是使用索引的最慢的連接之一
MySQL沒(méi)有發(fā)現好的可以使用的索引，但發(fā)現如果來(lái)自前面的表的列值已知，可能部分索引可以使用。
對前面的表的每個(gè)行組合，MySQL檢查是否可以使用range或index_merge訪(fǎng)問(wèn)方法來(lái)索取行。
關(guān)于適用性標準的描述參見(jiàn)7.2.5節，“范圍優(yōu)化”和7.2.6節，“索引合并優(yōu)化”，
不同的是前面表的所有列值已知并且認為是常量。這并不很快，但比執行沒(méi)有索引的聯(lián)接要快得多。
Using filesort
看到這個(gè)的時(shí)候，查詢(xún)就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現如何對返回的行排序。
它根據連接類(lèi)型以及存儲排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行
Using index
列數據是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，
這發(fā)生在對表的全部的請求列都是同一個(gè)索引的部分的時(shí)候
Using temporary
看到這個(gè)的時(shí)候，查詢(xún)需要優(yōu)化了。這里，MYSQL需要創(chuàng )建一個(gè)臨時(shí)表來(lái)存儲結果，這通常發(fā)生在對不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上
Using where
使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶(hù)。如果不想返回表中的全部行，
并且連接類(lèi)型ALL或index，這就會(huì )發(fā)生，或者是查詢(xún)有問(wèn)題
Impossible WHERE noticed after reading const table...
【SQL優(yōu)化】相關(guān)文章：
oracle的sql語(yǔ)句01-21
SQL語(yǔ)句的理解原則03-30
SQL查詢(xún)語(yǔ)句大全04-25
mysql SQL語(yǔ)句積累參考03-30
執行sql原理l分析03-30
SQL中的單記錄函數03-30
PHP防止SQL注入的例子03-15
SQL語(yǔ)句中的正則表達示03-18