關於SQL優化的小知識

Berton Lee發表於2019-02-19

負向查詢不命中索引

不命中

select account from user where id not in (1,2,3);
複製程式碼

命中

select account from user where id in (4,5,6);
複製程式碼

前置模糊查詢不命中索引

不命中

select account from user where name like '%lufei'
複製程式碼

命中

select account from user where name like 'lu%fei%'
複製程式碼

建議可以考慮使用 Lucene 等全文索引工具來代替頻繁的模糊查詢。

資料區分不明顯不建議索引

對非唯一的欄位,例如“性別”這種大量重複的重複值的欄位,增加索引也沒有什麼意義。可以採用唯一賬號等欄位。

越小越簡單的資料型別建議索引

越小越簡單的資料型別通常在磁碟、記憶體中佔用少,處理起來更快,例如整型資料比字元處理開銷小,因為字串的比較更復雜,處理非常耗時。

儘量避免null

索引欄位應該制定列為NOT NULL 。含有空值得列很難進行查詢優化,因為他們使得索引、索引的統計資訊以及比較運算增加複雜,應該用0或者特殊值、空字元代替。

在欄位上進行計算不能命中索引

索引列不能參與計算,儘量保持列“乾淨”。比如,FROM_UNIXTIME(create_time) = '2016-06-06' 就不能命中索引。 不命中

select account from user where FROM_UNIXTIME(create_time) = CURDATE();
複製程式碼

命中

select account from user where create_time = FROM_UNIXTIME(CURDATE());
複製程式碼

表表連線索引

表與表連線用於多表聯合查詢的約束條件的欄位應當建立索引,並且進行 join 的欄位兩表的欄位型別要相同,不然也不會命中索引。

欄位型別強制轉換不命中索引

不命中

select account from user where phone = 1341111111
複製程式碼

命中

select account from user where phone = '1341111111'
複製程式碼

如果知道是一條記錄,使用limit

select account from user where phone = '1341111111' limit 1
複製程式碼

可以提高效率,讓資料庫停止遊標移動。

最左匹配

最左字首匹配原則。MySQL會一直向右匹配直到遇到範圍查詢(>,<,BETWEEN,LIKE)就停止匹配。 如有索引(a, b, c, d),查詢條件a = 1 and b = 2 and c > 3 and d = 4,則會在每個節點依次命中a、b、c,而無法命中d。(很簡單:索引命中只能是相等的情況,不能是範圍匹配)

個人部落格

www.ccode.live/bertonlee/l…

歡迎關注公眾號“碼上開發”,每天分享最新技術資訊

關於SQL優化的小知識

相關文章