mysql提供了另一種神奇的功能讓我們去引導優化器進行更好的優化。
它就是 查詢優化提示（Query Optimizer Hints）;
查詢優化提示會提示優化器按照一定的方式去優化，讓你的sql語句更具靈活性，這會讓你的查詢更快，當然也可能更慢，這完全取決於你對優化器的理解和場景的了解。
現在讓我們來了解有哪些查詢優化提示：
優先操作HIGH_PRIORITY
HIGH_PRIORITY可以使用在select和insert操作中，讓MYSQL知道，這個操作優先進行。
SELECT HIGH_PRIORITY * FROM TABLE1;
滯後操作LOW_PRIORITY
LOW_PRIORITY可以使用在select,delete,insert和update操作中，讓mysql知道，這個操作滯後。
update LOW_PRIORITY table1 set field1= where field1= …
這兩個提示都只在基於表鎖的存儲引擎非常有效。在innoDB和其他基於行鎖的存儲引擎，你可能永遠用不上。在MyISAM中使用它們時，也要十分小心，因為它們會讓並發插入失效，可能會嚴重下降性能。
延時插入DELAYED
這個操作只能用於insert 和replace
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO，是客戶端提交數據給MySQL，MySQL返回OK狀態給客戶端。而這是並不是已經將數據插入表，而是存儲在內存裡面等待排隊。
當mysql有空餘時，再插入。另一個重要的好處是，來自許多客戶端的插入被集中在一起，並被編寫入一個塊。這比執行許多獨立的插入要快很多。
壞處是，不能返回自動遞增的ID，以及系統崩潰時，MySQL還沒有來得及插入數據的話，這些數據將會丟失。並且導致last_insert_id()無法正常工作。
強制連接順序straight_join
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE...;
由上面的SQL語句可知，通過STRAIGHT_JOIN強迫MySQL按TABLE1、TABLE2的順序連接表。如果你認為按自己的順序比MySQL推薦的順序進行連接的效率高的話，就可以通過STRAIGHT_JOIN來確定連接順序。
分組使用臨時表SQL_BIG_RESULT和SQL_SMALL_RESULT
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
這兩個提示只對select語句有效，它們告訴優化器對group by 或者distinct 查詢如何使用臨時表及排序。
sql_small_result 告訴優化器結果集會很小，可以將結果集放在內存中的索引臨時表，以避免排序操作；
sql_big_result 則告訴優化器結果集會很大，建議使用磁盤臨時表做排序操作；
強制使用臨時表sql_buffer_result
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …;
這個提示告訴優化器將查詢放入到一個臨時表，然後儘可能地釋放鎖。這和前面提到的由客戶端緩存結果不同。當你設法使用客戶端緩存的時候，使用服務器端的緩存通常很有效。
帶來的好處是無須在客戶端消耗太多的內存，還可以盡可能快的釋放對應的表鎖。代價是，服務器端需要更多的內存。
SQL_NO_CACHE
SQL_NO_CACHE hint 會使用特殊的查詢來關閉MySQL內置的查詢緩存機制。在動態性很強或者執行頻率很低的查詢上使用SQL_NO_CACHE hint，可以幫助MySQL提高緩存的使用效率。不過確保在使用SQL_NO_CACHE hint時，MySQL已經開啟了查詢緩存，否則沒有必要使用。
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
關於MySQL查詢緩存的更多信息可以查看這裡
SQL_CACHE
如果你已經配置了query_cache_type = 2(僅在使用SQL_CACHE時進行緩衝)，那麼可以使用SQL_CACHE hint來告訴MySQL哪些查詢需要進行緩存。
SELECT SQL_CALHE * FROM TABLE1;
sql_calc_found_rows
嚴格來說，這並不是一個優化器提示。它不會告訴優化器任何關於執行計劃的東西。
它會讓mysql返回的結果集包含更多的信息。查詢中加上該提示，mysql會計算出去limit子句後這個查詢返回的結果集的總數。
而實際上只返回limit要求的結果集。可以通過函數found_row()獲得這個值。
鎖相關for update 和lock in share mode
這兩個提示主要控制select 語句的鎖機制。但只對實現了行級鎖的存儲引擎有效。使用該提示會對符合查詢條件的數據加鎖。
對於insert...select 語句不需要這兩個提示，因為會默認添加上鎖。
唯一內置的支持這兩個提示的引擎是innoDB。另外需要記住的是，這兩個提示會讓某些優化無法進行。例如索引覆蓋掃描。
沒有留言:
張貼留言