Sql优化举例

Query SQL 完整表达式

SELECT DISTINCT 
    -- 尽量避免使用*， 因为
    -- 1. 有些项目中的表字段可能比较多 （100+甚至200+）， 影响性能
    -- 2. 节省资源，减少网络/IO的消耗
    -- 3. 有时候可能导致索引失效
    <column A>,    
    <column B>,
    <...>
FROM <table name>
-- inner/left/right join
-- 在查询结果一致的前提下，尽量使用inner join, 因为它是完全匹配，查询的范围较小
-- 其实用left join的是时候尽量使left table的数量控制得小一点
<join type> JOIN <right table> ON <join condition>

WHERE <where condition>

GROUP BY <group by list>

HAVING <having condition>

ORDER BY <order by list>

LIMIT count OFFSET count -- 分页
    

SQL查询语句的优化

1. 查询语句中尽量写全所有查询的字段 (可以作为Code Convention)

  - 在表字段多（有些表字段数量在50+，甚至100+）的情况下返回值的data可能很大，可以减少存储空间，减少网络消耗

  - 用*可能会会破坏索引（导致全表搜索）

  - 在优化后台代码的时候，可能对应的字段定义改了，那么很容易通过全局搜索发现需要改的DAO层的定义

---

2. 避免在where子句中使用or拼接查询

比如 

select id, name, level from user where id = 1 or name = 'Jay'

建议使用union all：

select  id, name, level from user where id = 1

union all 

select  id, name, level from user where name = 'Jay'

---

3. 优化在子查询中使用in语句 (避免使用in子查询 - 特别是in子查询中返回集特别大的情况)

比如

SELECT * FROM s1 WHERE col1 in (SELECT col2 FROM s2 WHERE col2 = 'hello');

因为

1. 索引可能不生效

2. IN子查询结果集比较大的情况下，按子表查询由于参数过多会导致效率极低（慢）， 每一个值都需要判断

使用物化表 - 

先将in子句的查询内容放至临时表（物化表），在用join语句关联查询

SELECT 
    s1.* 
FROM s1 
    LEFT JOIN (SELECT col2 FROM s2 WHERE col2 = 'hello') t1 ON s1.col1 = t1.col2;