##五、SQL設計規范 1. 不使用SELECT \*，只獲取必要的字段 > 消耗CPU和IO、消耗網絡帶寬； 無法使用覆蓋索引 <code> 低效查詢 SELECT * FROM t ; -----> 高效查詢 SELECT uid,name,age,address FROM t WHERE LOC_IN IN (10,20,30); </code> 2. 用IN來替換OR <code> 低效查詢 SELECT uid,name,age,address FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30; -----> 高效查詢 SELECT uid,name,age,address FROM t WHERE LOC_IN IN (10,20,30); </code> 3. 避免數據類型不一致導致索引失效 <code> 錯誤寫法 SELECT uid,name,age,address FROM t WHERE id = '19'; -----> 正確寫法 SELECT uid,name,age,address FROM t WHERE id = 19; </code> 4. 減少與數據庫的交互次數 <code> 低效查詢 INSERT INTO t (id, name) VALUES(1,'Bea'); INSERT INTO t (id, name) VALUES(2,'Belle'); INSERT INTO t (id, name) VALUES(3,'Bernice'); -----> 高效查詢 INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice'); Update … where id in (1,2,3,4); Alter table tbl_name add column col1, add column col2; </code> 5. 拒絕大SQL，拆分成小SQL <code> 低效查詢 SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql'; 可以分解成下面這些查詢來代替 -----> 高效查詢 SELECT * FROM tag WHERE tag = 'mysql' SELECT * FROM tag_post WHERE tag_id = 1234 SELECT * FROM post WHERE post_id in (123, 456, 567, 9098, 8904); </code> 6. 禁止使用order by rand() > 因為ORDER BY rand()會將數據從磁盤中讀取，進行排序，會消耗大量的IO和CPU <code> 低效查詢 SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4; 高效查詢 SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4; </code>