##五、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>