SQL查找條件

Lou18
·
·
IPFS
·

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

---- 使用!=、<>、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 授权

喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!