首先尋找出一支欲調效的Table或Query
再來我們可以先使用左上方工具列 'Query' 內的 Explain Current Statement
便可以得到如以下的連結表
由圖中可以清楚的看見每一段Query後的資料量,並發現有數張表單是呈現紅色 'Full Table Scan'
,這代表該段Query對這張表單的每一行欄位都做了掃描。
再來我們在我們的Query前方加上 'EXPLAIN' 並執行
便得到了該段Query所關聯的表單與其詳細資訊
其中針對EXPLAIN的欄位說明如下:
table:關連到的資料表(Table)會顯示在此。
type:顯示使用了何種類型。從最優至最差的類型為const、eq_reg、ref、range、indexhe、ALL。
possible_keys:顯示可能使用到的索引。此為從WHERE語法中選擇一個適合的欄位名稱。
key:實際使用到的索引。如果為NULL,則是沒有使用索引。
key_len:使用索引的長度。長度越短 準確性越高。
ref:顯示那一列的索引被使用。一般是一個常數(const)。
rows:MySQL用來返回資料的筆數。
Extra:MySQL用來解析額外的查詢訊息。如果此欄位的值為:Using temporary和Using filesort,表示MySQL無法使用索引。
Extra為MySQL用來解析額外的查詢訊息,其中欄位值所代表的意義如下:
Distinct:當MySQL找到相關連的資料時,就不再搜尋。
Not exists:MySQL優化 LEFT JOIN,一旦找到符合的LEFT JOIN資料後,就不再搜尋。
Range checked for each Record(index map:#):無法找到理想的索引。此為最慢的使用索引。
Using filesort:當出現這個值時,表示此SELECT語法需要優化。因為MySQL必須進行額外的步驟來進行查詢。
Using index:返回的資料是從索引中資料,而不是從實際的資料中返回,當返回的資料都出現在索引中的資料時就會發生此情況。
Using temporary:同Using filesort,表示此SELECT語法需要進行優化。此為MySQL必須建立一個暫時的資料表(Table)來儲存結果,此情況會發生在針對不同的資料進行ORDER BY,而不是GROUP BY。
Using where:使用WHERE語法中的欄位來返回結果。
System:system資料表,此為const連接類型的特殊情況。
Const:資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行,這個值就是常數,因為MySQL會先讀這個值然後把它當做常數。
eq_ref:MySQL在連接查詢時,會從最前面的資料表,對每一個記錄的聯合,從資料表中讀取一個記錄,在查詢時會使用索引為主鍵或唯一鍵的全部。
ref:只有在查詢使用了非唯一鍵或主鍵時才會發生。
range:使用索引返回一個範圍的結果。例如:使用大於>或小於<查詢時發生。
index:此為針對索引中的資料進行查詢。
ALL:針對每一筆記錄進行完全掃描,
此為最壞的情況,應該盡量避免。
我們可以注意到 `browse_history` 這個表單在Query中並沒有使用索引,
可以從上方的QueryString中發現該段Query的Left Join是查詢`product_id`這個欄位,前往這個Table並幫其建立Index來增加檢索效率。
步驟三的時候可以選擇複數欄位來建立Index,但是要注意的是在使用索引欄位作為條件時,如果該索引是複合索引,那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引,否則該索引將不會被使用,並且應儘可能的讓欄位順序與索引順序相一致。
再來是在Query語句中應該注意以下事項
1.避免在索引列上進行運算, 這將導致引擎放棄使用索引而進行全表掃描。
2.不使用NOT IN和<>操作, NOT IN和<>操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替,id<>9則可使用id>9 or id<9來代替。
3.檢查where條件與order by 欄位,避免全表掃描。
4.應儘量避免在 where 子句中對欄位進行 null 值判斷,否則將導致引擎放棄使用索引而進行全表掃描,如: 可以在num上設定預設值0,確保表中num列沒有null值,然後這樣查詢: select id from t where num = 0
5.應儘量避免在 where 子句中使用 or 來連線條件,如果一個欄位有索引,一個欄位沒有索引,將導致引擎放棄使用索引而進行全表掃描。可以拆分條件,進行子句的union all查詢,如: select id from t where num=10 or name = 'admin' 拆分 select id from t where num = 10 union all select id from t where name = 'admin'
6.in 和 not in 也要慎用,否則會導致全表掃描,如: select id from t where num in(1,2,3) 對於連續的數值,能用 between 就不要用 in 了: select id from t where num between 1 and 3,
用 exists 代替 in 是一個好的選擇: select num from a where num in(select num from b) 換成 select num from a where exists(select 1 from b where num=a.num)
7.like語句的%不要前置, 否則索引失效 將導致全表掃描。
8.如果在 where 子句中使用引數,也會導致全表掃描。 因為SQL只有在執行時才會解析區域性變數,但優化程式不能將訪問計劃的選擇推遲到執行時;它必須在編譯時進行選擇。然 而,如果在編譯時建立訪問計劃,變數的值還是未知的,因而無法作為索引選擇的輸入項。
9.應儘量避免在where子句中對欄位進行函式操作,這將導致引擎放棄使用索引而進行全表掃描。
10.不要在 where 子句中的“=”左邊進行函式、算術運算或其他表示式運算,否則系統將可能無法正確使用索引。
11.在使用索引欄位作為條件時,如果該索引是複合索引,那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引,否則該索引將不會被使用,並且應儘可能的讓欄位順序與索引順序相一致。
12.Update 語句,如果只更改1、2個欄位,不要Update全部欄位,否則頻繁呼叫會引起明顯的效能消耗,同時帶來大量日誌。
對於多張大資料量(這裡幾百條就算大了)的表JOIN,要先分頁再JOIN,否則邏輯讀會很高,效能很差。
13.select count(*) from table;這樣不帶任何條件的count會引起全表掃描,並且沒有任何業務意義,是一定要杜絕的。
14.任何地方都不要使用 select * from t ,用具體的欄位列表代替“*”,不要返回用不到的任何欄位。
15.避免頻繁建立和刪除臨時表,以減少系統表資源的消耗。臨時表並不是不可使用,適當地使用它們可以使某些例程更有效,例如,當需要重複引用大型表或常用表中的某個資料集時。但是,對於一次性事件, 最好使用匯出表。
16.在新建臨時表時,如果一次性插入資料量很大,那麼可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果資料量不大,為了緩和系統表的資源,應先create table,然後insert。
17.儘量拆分大的 DELETE 或INSERT 語句,批量提交SQL語句。
18.儘量避免使用遊標,因為遊標的效率較差,如果遊標操作的資料超過1萬行,那麼就應該考慮改寫。
參考來源:
1. http://blog.twbryce.com/mysql-explain/
2. https://www.gushiciku.cn/pl/gkis/zh-tw
3. https://www.itread01.com/content/1548581229.html
梨子, 2022/3/28 下午 09:00:29
寫一下, 以備不時之需, Codepage 和 Name 可以用來取得 encoding.
CodePage, DisplayName, Name
37, IBM EBCDIC (美國-加拿大), IBM037
437, OEM 美國, IBM437
500, IBM EBCDIC (國際), IBM500
708, 阿拉伯文 (ASMO 708), ASMO-708
720, 阿拉伯文 (DOS), DOS-720
737, 希臘文 (DOS), ibm737
775, 波羅的海文 (DOS), ibm775
850, 西歐語系 (DOS), ibm850
852, 中歐語系 (DOS), ibm852
855, OEM 斯拉夫文, IBM855
857, 土耳其文 (DOS), ibm857
858, OEM 多語系拉丁文 I, IBM00858
860, 葡萄牙文 (DOS), IBM860
861, 冰島文 (DOS), ibm861
862, 希伯來文 (DOS), DOS-862
863, 加拿大法文 (DOS), IBM863
864, 阿拉伯文 (864), IBM864
865, 北歐字母 (DOS), IBM865
866, 斯拉夫文 (DOS), cp866
869, 希臘文,現代 (DOS), ibm869
870, IBM EBCDIC (多語系拉丁文 2), IBM870
874, 泰文 (Windows), windows-874
875, IBM EBCDIC (希臘現代), cp875
932, 日文 (Shift-JIS), shift_jis
936, 簡體中文 (GB2312), gb2312
949, 韓文, ks_c_5601-1987
950, 繁體中文 (Big5), big5
1026, IBM EBCDIC (土耳其拉丁文 5), IBM1026
1047, IBM 拉丁文 1, IBM01047
1140, IBM EBCDIC (美國-加拿大-歐洲), IBM01140
1141, IBM EBCDIC (德國-歐洲), IBM01141
1142, IBM EBCDIC (丹麥-挪威-歐洲), IBM01142
1143, IBM EBCDIC (芬蘭-瑞典-歐洲), IBM01143
1144, IBM EBCDIC (義大利-歐洲), IBM01144
1145, IBM EBCDIC (西班牙-歐洲), IBM01145
1146, IBM EBCDIC (英國-歐洲), IBM01146
1147, IBM EBCDIC (法國-歐洲), IBM01147
1148, IBM EBCDIC (國際-歐洲), IBM01148
1149, IBM EBCDIC (冰島-歐洲), IBM01149
1200, Unicode, utf-16
1201, Unicode (位元組由大到小), utf-16BE
1250, 中歐語系 (Windows), windows-1250
1251, 斯拉夫文 (Windows), windows-1251
1252, 西歐語系 (Windows), Windows-1252
1253, 希臘文 (Windows), windows-1253
1254, 土耳其文 (Windows), windows-1254
1255, 希伯來文 (Windows), windows-1255
1256, 阿拉伯文 (Windows), windows-1256
1257, 波羅的海文 (Windows), windows-1257
1258, 越南文 (Windows), windows-1258
1361, 韓文 (Johab), Johab
10000, 西歐語系 (Mac), macintosh
10001, 日文 (Mac), x-mac-japanese
10002, 繁體中文 (Mac), x-mac-chinesetrad
10003, 韓文 (Mac), x-mac-korean
10004, 阿拉伯文 (Mac), x-mac-arabic
10005, 希伯來文 (Mac), x-mac-hebrew
10006, 希臘文 (Mac), x-mac-greek
10007, 斯拉夫文 (Mac), x-mac-cyrillic
10008, 簡體中文 (Mac), x-mac-chinesesimp
10010, 羅馬尼亞文 (Mac), x-mac-romanian
10017, 烏克蘭文 (Mac), x-mac-ukrainian
10021, 泰文 (Mac), x-mac-thai
10029, 中歐語系 (Mac), x-mac-ce
10079, 冰島文 (Mac), x-mac-icelandic
10081, 土耳其文 (Mac), x-mac-turkish
10082, 克羅埃西亞文 (Mac), x-mac-croatian
12000, Unicode (UTF-32), utf-32
12001, Unicode (UTF-32 位元組由大到小), utf-32BE
20000, 繁體中文 (CNS), x-Chinese-CNS
20001, TCA 台灣, x-cp20001
20002, 繁體中文 (Eten), x-Chinese-Eten
20003, IBM5550 台灣, x-cp20003
20004, TeleText 台灣, x-cp20004
20005, Wang 台灣, x-cp20005
20105, 西歐語系 (IA5), x-IA5
20106, 德文 (IA5), x-IA5-German
20107, 瑞典文 (IA5), x-IA5-Swedish
20108, 挪威文 (IA5), x-IA5-Norwegian
20127, US-ASCII, us-ascii
20261, T.61, x-cp20261
20269, ISO-6937, x-cp20269
20273, IBM EBCDIC (德國), IBM273
20277, IBM EBCDIC (丹麥-挪威), IBM277
20278, IBM EBCDIC (芬蘭-瑞典), IBM278
20280, IBM EBCDIC (義大利), IBM280
20284, IBM EBCDIC (西班牙), IBM284
20285, IBM EBCDIC (UK), IBM285
20290, IBM EBCDIC (日文片假名), IBM290
20297, IBM EBCDIC (法國), IBM297
20420, IBM EBCDIC (阿拉伯文), IBM420
20423, IBM EBCDIC (希臘文), IBM423
20424, IBM EBCDIC (希伯來文), IBM424
20833, IBM EBCDIC (韓文擴充), x-EBCDIC-KoreanExtended
20838, IBM EBCDIC (泰國), IBM-Thai
20866, 斯拉夫文 (KOI8-R), koi8-r
20871, IBM EBCDIC (冰島), IBM871
20880, IBM EBCDIC (斯拉夫俄文), IBM880
20905, IBM EBCDIC (土耳其), IBM905
20924, IBM 拉丁文 1, IBM00924
20932, 日文 (JIS 0208-1990 和 0212-1990), EUC-JP
20936, 簡體中文 (GB2312-80), x-cp20936
20949, 韓文 Wansung, x-cp20949
21025, IBM EBCDIC (斯拉夫塞爾維亞文-保加利亞文), cp1025
21866, 斯拉夫文 (KOI8-U), koi8-u
28591, 西歐語系 (ISO), iso-8859-1
28592, 中歐語系 (ISO), iso-8859-2
28593, 拉丁文 3 (ISO), iso-8859-3
28594, 波羅的海文 (ISO), iso-8859-4
28595, 斯拉夫文 (ISO), iso-8859-5
28596, 阿拉伯文 (ISO), iso-8859-6
28597, 希臘文 (ISO), iso-8859-7
28598, 希伯來文 (ISO-Visual), iso-8859-8
28599, 土耳其文 (ISO), iso-8859-9
28603, 愛沙尼亞文 (ISO), iso-8859-13
28605, 拉丁文 9 (ISO), iso-8859-15
29001, 歐洲, x-Europa
38598, 希伯來文 (ISO-Logical), iso-8859-8-i
50220, 日文 (JIS), iso-2022-jp
50221, 日文 (JIS-Allow 1 byte Kana), csISO2022JP
50222, 日文 (JIS-Allow 1 byte Kana - SO/SI), iso-2022-jp
50225, 韓文 (ISO), iso-2022-kr
50227, 簡體中文 (ISO-2022), x-cp50227
51932, 日文 (EUC), euc-jp
51936, 簡體中文 (EUC), EUC-CN
51949, 韓文 (EUC), euc-kr
52936, 簡體中文 (HZ), hz-gb-2312
54936, 簡體中文 (GB18030), GB18030
57002, ISCII 梵文語系, x-iscii-de
57003, ISCII 孟加拉文, x-iscii-be
57004, ISCII 坦米爾文, x-iscii-ta
57005, ISCII 特拉古文, x-iscii-te
57006, ISCII 阿薩姆文, x-iscii-as
57007, ISCII 歐利亞文, x-iscii-or
57008, ISCII 坎那達文, x-iscii-ka
57009, ISCII 馬來亞拉姆文, x-iscii-ma
57010, ISCII 古吉拉特文, x-iscii-gu
57011, ISCII 旁遮普語, x-iscii-pa
65000, Unicode (UTF-7), utf-7
65001, Unicode (UTF-8), utf-8
Bike, 2016/11/12 上午 10:12:03