Lou18

18

SQL查找條件

發布於

誤用或濫用的地雷與使用建議:

---- 使用!=、<>、NOT查詢,會變全掃
SELECT * FROM user WHERE age != 20
SELECT * FROM user WHERE age <> 20
SELECT * FROM user WHERE age NOT IN(20)

---- 用 like 且 % 作為前綴,會變全掃
SELECT * FROM user WHERE name like '%-Mark'

---- 誤用 OR
-- index column: {age} / bad
SELECT * FROM user WHERE age = 18 OR name = 'C-Ian';
-- index column: {age},{name} / good
SELECT * FROM user WHERE age = 18 OR name = 'C-Ian';
-- index column: {age} with AND / good
SELECT * FROM user WHERE age = 18 AND name = 'C-IAn'; 

---- 在WHERE欄位進行運算
-- index column: {age} / bad
SELECT * FROM user WHERE age/2 = 18;
-- good
SELECT * FROM user WHERE age/2 = 18;

---- 使用某些函數
-- full scan while using RAND()
SELECT * FROM test.user where age >= RAND();

---- SELECT * FROM
-- index column: {name} 
-- 覆蓋索引失效。要先至 secondary index 查找再去 clustered Index 抓資料 / bad
SELECT * FROM user;
-- secondary index only/ good
SELECT name FROM user;

---- 使用 ORDER BY RAND(),耗費空間做排序
SELECT * FROM test.user where age <= 18 ORDER BY RAND();


喜歡我的文章嗎?
別忘了給點支持與讚賞,讓我知道創作的路上有你陪伴。

CC BY-NC-ND 2.0 版權聲明

索引類型

使用索引的注意事項

1

看不過癮?

一鍵登入,即可加入全球最優質中文創作社區