SQL优化总结
目录
一、SQL优化重要性
在提升系统性能时，SQL优化是成本最低的，却是效果最佳的，如果整个项目在SQL上优化的很优秀，系统性能会有质的跨越，又能让老板省下不少成本开支。
- 优化成本：硬件>系统配置>表结构>SQL及索引。
- 优化效果：硬件<系统配置<表结构<SQL及索引。
二、原则
- 减少数据访问
- 返回更少的数据
- 减少交互次数
- 利用更多资源
三、SQL执行顺序
1. SELECT 2. DISTINCT <select_list> 3. FROM <left_table> 4. <join_type> JOIN <right_table> 5. ON <join_condition> 6. WHERE <where_condition> 7. GROUP BY <group_by_list> 8. HAVING <having_condition> 9. ORDER BY <order_by_condition> 10.LIMIT <limit_number>
四、SQL优化策略
适用于数据量较大的业务场景，数据量不大的、或者可预知系统最终数据量不会太大的没必要画蛇添足。
（一）避免不走索引的场景
模糊查询
模糊查询，避免前通配符 %，这会让数据库引擎放弃索引而进行全表扫描，在搜索词后面使用不影响。
SELECT sid FROM tb WHERE sname like '%周%'
如需求使用在搜索词前面匹配，可以考虑：
使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置
使用FullText全文索引，用match against 检索
数据量较大的情况，建议引用ElasticSearch、solr
IN 和 NOT IN
会导致引擎走全表扫描，避免使用
SELECT sname FROM tb WHERE sid IN ( 1, 2, 3 );
优化：
SELECT sname FROM tb WHERE sin BETWEEN 1 AND 3
IN 子查询：
select * from A where A.id in (select id from B);
优化
select * from A where exists (select * from B where B.id = A.id);
条件或 OR
SELECT sname FROM tb WHERE sid = 1 OR sid = 5;
优化：
SELECT sname FROM tb WHERE sid = 1 UNION SELECT sname FROM tb WHERE sid = 5
判断 null
SELECT * FROM tb WHERE score IS NULL；
优化：为字段设置默认值，用默认值来判断，例如0
SELECT * FROM tb WHERE score = 0；
where 条件
避免等号左边进行函数、表达式操作，尽量移到右边
-- 全表扫描 SELECT * FROM tb WHERE score/10 = 9 -- 走索引 SELECT * FROM tb WHERE score = 10*9
where 1 = 1 避免使用
通过代码或者动态SQL判断，有条件就 where条件，没条件就不要where
不等 != 或<>
使用索引列作为条件进行查询时，需要避免使用<>或者!=。如确实业务需要，使用到不等于符号，需要在重新评估索引建立。
类型转换
避免字段类型的隐式转换，如给varchar赋值类型为数值，涉及隐式类型转换，造成不能正确走索引
select col1 from table where col_varchar=123;
排序 ORDER BY
order by 条件要与 where 的条件一致，不然不走索引
-- 不走age索引 SELECT * FROM t order by age; -- 走age索引 SELECT * FROM t where age > 0 order by age;
（二）SELECT的其他优化
避免SELECT *
使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。
建议提出业务实际需要的列数，指定列名以取代select *
避免不确定结果的函数
now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。
关联查询
小表在前，大表在后
在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
别名
多表查询，使用别名带上列（列上加表前缀），减少解析的时间并减少哪些友列名歧义引起的语法错误
优化 GROUP BY
默认情况下，MySQL 会对GROUP BY分组的所有值进行排序。如果不需要排序可以禁掉以提高效率。
如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;”
如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。
因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：
SELECT sid,score FROM sc GROUP BY sid,score ORDER BY NULL
使用JOIN
使用子查询一般能有一个比较直观的结构，能把一些复杂的查询细分，但是有些情况下，换用JOIN效率更高。
因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
优化UNION
MySQL通过创建并填充临时表的方式来执行union查询。
除非确实要消除重复的行，否则建议使用union all。
原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
五、建表时优化
- 建立索引时，优先考虑where、order by经常使用到的字段。
- 使用数字型字段
如果只存数字却设计为字符，这增加了存储开销，也降低了查询和连接时的性能。
因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
- 用varchar/nvarchar 代替 char/nchar
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
