在mysql中,hint指的是“查詢優化提示”,會提示優化器按照一定的方式來產生執行計劃進行優化,讓用戶的sql語句更具靈活性;Hint可基於表的連接順序、方法、存取路徑、平行度等規則對DML(資料操縱語言,Data Manipulation Language)語句產生作用。
本教學操作環境:windows7系統、mysql8版本、Dell G3電腦。
我們可以在操作表、欄位或索引時加入 comment 來增強程式碼可讀性,以便他人快速讀取程式碼,這是對使用資料庫的人的一種提示;同樣的,還有一種提示,叫做hint,是給資料庫的提示。
何謂hint
hint指的是“查詢最佳化提示”,它會提示優化器按照一定的方式去優化,讓你的sql語句更有彈性,這會讓你的查詢更快,當然也可能更慢,這完全取決於你對優化器的理解和場景的了解。
我們知道執行一條SQL語句時,MySQL會產生一個執行計劃,而hint就是告訴查詢最佳化器需要按照我們告訴它的方式來產生執行計劃。
Hint可基於表格的連接順序、方法、存取路徑、平行度等規則對DML(資料操縱語言,Data Manipulation Language)語句產生作用,範圍如下:
使用的优化器类型; 基于代价的优化器的优化目标,是all_rows还是first_rows; 表的访问路径,是全表扫描,还是索引扫描,还是直接用rowid; 表之间的连接类型; 表之间的连接顺序; 语句的并行程度;
常用hint
強制索引FORCE INDEXSELECT * FROM tbl FORCE INDEX (FIELD1) …
忽略索引IGNORE INDEXSELECT * FROM tbl IGNORE INDEX (FIELD1, FIELD2) …
#關閉查詢緩衝SQL_NO_CACHESELECT SQL_NO_CACHE field1, field2 FROM tbl;
需要查詢即時資料且頻率不高時,可以考慮把緩衝關閉,即無論此是否已執行,MySQL都不會在緩衝區中查找。
強制查詢緩衝SQL_CACHESELECT SQL_CACHE * FROM tbl;
功能同上一條相反,但僅在my.ini中的query_cache_type設為2時起作用。
優先操作 HIGH_PRIORITY
HIGH_PRIORITY可以使用在select和insert操作中,讓MYSQL知道,這個操作優先進行。 SELECT HIGH_PRIORITY * FROM tbl;
滯後操作LOW_PRIORITY
LOW_PRIORITY可以使用在insert和update操作中,讓mysql知道,這個操作滯後。 update LOW_PRIORITY tbl set field1= where field1= …
INSERT DELAYED INTO tbl set field1= … 指客戶端提交插入資料申請,MySQL返回OK狀態卻並未實際執行,而是儲存在記憶體中排隊,當mysql有剩餘時再插入。
一個重要的好處是,來自多個客戶端的插入請求被集中在一起,編寫入一個區塊,比獨立執行許多插入要快很多。
壞處是,不能回傳自增ID,以及系統崩潰時,MySQL也未來得及被插入的資料將會遺失。
SELECT tbl.FIELD1, tbl2.FIELD2 FROM tbl STRAIGHT_JOIN tbl2 WHERE …由上面的SQL語句可知,透過STRAIGHT_JOIN強迫MySQL依照tbl、tbl2的順序連接表。如果你認為以自己的順序比MySQL推薦的順序進行連接的效率高的話,就可以透過STRAIGHT_JOIN來決定連接順序。
SELECT SQL_BUFFER_RESULT * FROM tbl WHERE …當我們查詢的結果集中的資料比較多時,可以透過SQL_BUFFER_RESULT.選項強制將結果集放到臨時表中,這樣就可以很快地釋放MySQL的表鎖(這樣其它的SQL語句就可以對這些記錄進行查詢了),並且可以長時間地為客戶端提供大記錄集。
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM tbl GROUP BY FIELD1;對SELECT語句告訴MySQL優化去對GROUP BY和DISTINCT查詢如何使用臨時表排序,SQL_SMALL_RESULT表示結果集很小,可以直接在記憶體的臨時表排序;反之則很大,需要使用磁碟臨時表排序。
SQL_CALC_FOUND_ROWS
它其實不是優化器提示,也不影響優化器的執行計劃,但會讓mysql傳回的結果集中包含本次操作影響的總行數,需與FOUND_ROWS()
聯用。 SQL_CALC_FOUND_ROWS
通知MySQL將本處理的行數記錄下來; FOUND_ROWS()
用於取出被記錄的行數,可以套用到分頁場景。
一般的分頁寫法為:先查總數,計算頁數,再查詢某一頁的詳情。 SELECT COUNT(*) 從 tbl WHERE …
SELECT * FROM tbl WHERE …limit m,n
但使用SQL_CALC_FOUND_ROWS
#可以簡化成如下寫法:SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE … limit m,n;
SELECT FOUND_ROWS();
第二條# SELECT
將傳回第一條SELECT
不帶limit時的總行數,如此只需執行一次較耗時的複雜查詢就可同時得到總行數。
LOCK IN SHARE MODE、 FOR UPDATE
同樣的,這兩個也不是最佳化提示,是控制SELECT語句的鎖定機制,只對行級鎖定有效,即InnoDB支持。
擴充知識:
#概念與差異
SELECT ... LOCK IN SHARE MODE
新增的是IS鎖(意向共享鎖),即在符合條件的rows上都加了共享鎖,其他session可讀取記錄,亦可繼續添加IS鎖,但無法修改,直到這個加鎖的session done(否則直接鎖定等待逾時)。
SELECT ... FOR UPDATE
新增的是IX鎖(意向排它鎖),即符合條件的rows上都加了排它,其他session無法為這些記錄添加任何S鎖或X鎖。如果不存在一致性非鎖定讀的話,則其他session是無法讀取和修改這些記錄的,但innodb有非鎖定讀(快照讀不需要加鎖)。
因此,for update
的加鎖方式只是比lock in share mode
的方式多阻塞了select...lock in share mode
的查詢方式,並不會阻塞快照讀取。
應用場景
LOCK IN SHARE MODE
的適用於兩張存在關係的表的寫場景,以mysql官方例子來說,一個表是child表,一個是parent表,假設child表的某一列child_id映射到parent表的c_child_id列,從業務角度講,此時直接insert一條child_id=100記錄到child表是存在風險的,因為insert的同時可能存在parent表執行了刪除c_child_id=100的記錄,業務資料有不一致的風險。正確方法是先執行select * from parent where c_child_id=100 lock in share mode
,鎖定parent表的這條記錄,然後執行insert into child(child_id) values (100)
。
【相關推薦:mysql影片教學】
#以上是mysql hint是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章!