您好,登錄后才能下訂單哦!
本篇文章給大家分享的是有關怎么在MySQL中對查詢進行優(yōu)化,小編覺得挺實用的,因此分享給大家學習,希望大家閱讀完這篇文章后可以有所收獲,話不多說,跟著小編一起來看看吧。
在學習索引優(yōu)化之前,需要對創(chuàng)建索引的規(guī)范有一定的了解,此規(guī)范來自于阿里巴巴開發(fā)手冊。
主鍵索引:pk_column_column
唯一索引:uk_column_column
普通索引:idx_column_column
創(chuàng)建索引需知道在什么情況下索引會失效,只有了解索引失效的原因,在創(chuàng)建索引時才不會出現(xiàn)一些已知錯誤。
1.帶頭大哥不能死
這局經(jīng)典的語句就是涵蓋創(chuàng)建索引時一定要符合最左側原則。
例如表結構為 u_id,u_name,u_age,u_sex,u_phone,u_time
創(chuàng)建索引為 idx_user_name_age_sex
。
查詢條件必須帶上u_name這一列。
2.不在索引列上做任何操作
不在索引列上做任何計算、函數(shù)、自動或者手動的類型轉換,否則會進行全表掃描。簡而言之不要在索引列上做任何操作。
3.倆邊類型不等
例如建立了索引idx_user_name,name字段類型為varchar
在查詢時使用where name = kaka
,這樣的查詢方式會直接造成索引失效。
正確的用法為where name = "kaka"
。
4.不適當?shù)膌ike查詢會導致索引失效
創(chuàng)建索引為idx_user_name
執(zhí)行語句為select * from user where name like "kaka%";
可以命中索引。
執(zhí)行語句為select name from user where name like "%kaka";
可以使用到索引(僅在8.0以上版本)。
執(zhí)行語句為select * from user where name like ''%kaka";
會直接導致索引失效
5.范圍條件之后的索引會失效
創(chuàng)建索引為idx_user_name_age_sex
執(zhí)行語句select * from user where name = 'kaka' and age > 11 and sex = 1;
上面這條sql語句只會命中name和age索引,sex索引會失效。
復合索引失效需要查看key_len的長度即可。
總結:%在后邊會命令索引,當使用了覆蓋索引時任何查詢方式都可命中索引。
以上就是咔咔關于索引失效會出現(xiàn)的原因總結,在很多文章中沒有標注MySQL版本,所以你有可能會看到is null 、or索引會失效的結論。
在寫完SQL語句之后必須要做的一件事情就是使用Explain進行SQL語句檢測,看是否命中索引。
下圖就是使用explain輸出格式,接下來將會對輸出格式進行簡單的解釋。
1.id 這列就是查詢的編號,如果查詢語句中沒有子查詢或者聯(lián)合查詢這個標識就一直是1。
如存在子查詢或者聯(lián)合查詢這個編號會自增。
2.select_type
最常見的類型就是SIMPLE和PRIMARY,此列知道就行了。
3.table
理解為表名即可
4. **type
此列是在優(yōu)化SQL語句時最需要關注的列之一,此列顯示了查詢使用了何種類型。
以下排序從最優(yōu)到最差。
system:表內只有一行數(shù)據(jù)
const:最多只會有一條記錄匹配,常用于主鍵或者唯一索引為條件查詢
eq_ref:當連接使用的索引為主鍵和唯一時會出現(xiàn)
ref:使用普通索引=或<=> 運算符進行比較將會出現(xiàn)
fulltext:使用全文索引
ref_or_null:跟ref類型類似,只是增加了null值的判斷,實際用的不多。語句為where name = 'kaka' and name is null,name
為普通索引。
index_merge:查詢語句使用了倆個以上的索引,常見在使用and、or會出現(xiàn),官方文檔將此類型放在ref_or_null之后,但是在很多的情況下由于讀取索引過多性能有可能還不如range
unique_subquery:用于where中的in查詢,完全替換子查詢,效率更高。語句為value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery:子查詢中的返回結果字段組合是一個索引(或索引組合),但不是一個主鍵或唯一索引
range:索引范圍查詢,常見于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等運算符的查詢中。
index:索引全表掃描,把索引從頭到尾掃一遍
all:全表掃描,性能最差。
5.possible_keys
此列顯示的可能會使用到的索引
6. **key
優(yōu)化器從possible_keys中命中的索引
7.key_len
查詢用到的索引長度(字節(jié)數(shù)),key_len只計算where條件用到的索引長度,而排序和分組就算用到了索引,也不會計算到key_len中。
8.ref
如果是使用的常數(shù)等值查詢,這里會顯示const。
如果是連接查詢,被驅動表的執(zhí)行計劃這里會顯示驅動表的關聯(lián)字段。
如果是條件使用了表達式或者函數(shù),或者條件列發(fā)生了內部隱式轉換,這里可能顯示為func。
9. **rows
這是mysql估算的需要掃描的行數(shù)(不是精確值)。
這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好。
10.filtered
此列表示存儲引擎返回的數(shù)據(jù)在server層過濾后,剩下多少滿足查詢的記錄數(shù)量的比例,注意是百分比,不是具體記錄數(shù)
11. **extra
在大多數(shù)情況下會出現(xiàn)以下幾種情況。
Using index:使用了覆蓋索引,查詢列都為索引字段
Using where:使用了where語句
Using temporary :查詢結果進行排序的時候使用了一張臨時表
Using filesort :對數(shù)據(jù)使用一個外部的索引排序
Using index condition:使用了索引下推,關于索引下推可以查看咔咔之前文章MySQL索引一文
12.總結
以上就是關于Explain所有列的說明,在平時開發(fā)的過程中,一般只會關注type、key、rows、extra這四列。
type優(yōu)化目標至少達到range級別,要求是ref級別,如果可以consts最好。
key是查詢使用到的索引,如果此列為空,要么未建立索引,要么索引失效。
rows是這條SQL語句掃描的行數(shù),越少越好。
extra:此列為擴展列,如果出現(xiàn)臨時表、文件排序則需要優(yōu)化。
上文說到了可以直接使用explain來分析自己的SQL語句是否合理,接下來再聊一個點那就是慢查詢。
查看慢查詢是否打開
查看是否記錄沒有使用索引的SQL語句
開啟慢查詢、開啟記錄沒有使用到索引的SQL語句
set global log_queries_not_using_idnexes='on'; set global log_queries_not_using_indexes='on';
查詢以上倆個配置是否打開
設置慢查詢時間,這個時間由自己把控,一般1s即可 set globle long_query_time=1;
如果查看這個時間沒有變,則關于客戶端在重新連接一次即可。
查看慢查詢存儲位置
然后隨便執(zhí)行一條不執(zhí)行索引的語句即可在這個日志中查看到此語句
上圖中一般需要主要觀察的是Query_time、SQL語句內容。
以上就是關于如何使用慢查詢來查看項目中出現(xiàn)問題的SQL語句。
此處跟大家聊一些常用的SQL語句優(yōu)化方案,以上的倆個工具要好好的利用,輔助我們進行打怪。
禁止使用select *,需要什么字段查詢什么字段
where字段設置索引
group by、order by字段設置索引
舍棄offset,limit分頁,使用延遲關聯(lián)來實現(xiàn)分頁(數(shù)據(jù)量不大時可不用)
寫分頁時當count為0時,直接返回避免執(zhí)行分頁語句
利用覆蓋索引進行查詢避免回表
建立復合索引時區(qū)分度最高的放在最左側
統(tǒng)計數(shù)據(jù)行數(shù)只用count(*),別整的花里胡哨的
關于in和exist,如果查詢的倆個表大小一致則性能差別可忽略,如果子查詢表大用exist,否則使用in
查詢一行數(shù)據(jù)時加上limit 1
選擇合理的數(shù)據(jù)類型,在滿足條件下數(shù)據(jù)類型越小越好
聯(lián)合查詢join最多三個表,并且需要join的字段數(shù)據(jù)類型保持一致
in操作能避免盡量避免,無法避免的情況下in元素控制在1000以內
數(shù)據(jù)更新頻繁,區(qū)分度不高的列不適合建立索引
explain中的type至少要達到range,要求為ref
聯(lián)合索引滿足最左側原則
以上就是怎么在MySQL中對查詢進行優(yōu)化,小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業(yè)資訊頻道。
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內容。