1. 优化的方向
- 优化成本：硬件>系统配置>数据库表结构>SQL及索引
- 优化效果：硬件<系统配置<数据库表结构<SQL及索引
SQL及索引优化是成本最低且效果最好的一种方式
2. 查询SQL的执行过程
1 . 通过网络的通讯协议接收客户端传入的SQL
2 . 查看该SQL对应的结果在查询缓存中是否存在
- 存在则直接返回结果
- 不存在则继续往下走
3 . 由解析器来解析当前SQL，最终形成初步的解析树
4 . 再由预处理器对解析树进行调整，完成占位符赋值等操作
5 . 查询优化器对最终的解析树进行优化，包括调整SQL顺序等
6 . 根据优化后的结果得出查询语句的执行计划，就是查询数据的具体实施方案，交给查询的执行引擎
7 . 查询执行引擎调用存储引擎提供的API，最后由存储引擎来完成数据的查询，然后返回结果
3. SQL的执行顺序
写的顺序
select … from … join … on … where … group by … having … order by … limit
执行的顺序
from … on … join … where … group by … having … select … order by … limit …
4. SQL优化的方法
4.1. EXPLAIN
用EXPLAIN查看SQL执行计划
EXPLAIN SELECT * FROM `user`
JOIN `post` ON `user`.id = `post`.uid
WHERE user.`created_at` < '2018-10-01 00:00:00' AND `post`.status = 1;
要重点关注的数据
type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。key列，使用到的索引名。如果没有选择索引，值是NULL。key_len列，索引长度。不损失精确性的情况下，长度越短越好rows列，扫描行数。该值是个预估值。extra列，详细说明。注意，常见的不太友好的值，如下：Using temporary（临时表）、Using filesort（文件排序）
针对explain命令生成的执行计划，这里有一个查看心法。我们可以先从查询类型type列开始查看，如果出现all关键字，后面的内容就都可以不用看了，代表全表扫描。再看key列，看是否使用了索引，null代表没有使用索引。然后看rows列，该列用来表示在SQL执行过程中被扫描的行数，该数值越大，意味着需要扫描的行数越多，相应的耗时越长，最后看Extra列，在这列中要观察是否有Using filesort 或者Using temporary 这样的关键字出现，这些是很影响数据库性能的
4.2. 避免使用select *
任何地方都不要使用 select * from t ，用具体的字段列表代替*，用哪些字段就写哪些字段，不要返回用不到的任何字段。 原因如下：
SELECT *会增加很多不必要的消耗（CPU、IO、内存、网络带宽）- 增加了使用覆盖索引的可能性
- 当表结构发生改变时，前端也需要更新
所以要求直接在select后面接上字段名
4.3. 区分in和exists
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键）。如果是exists，那么以外层表为驱动表，先被访问；如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
4.4. in 和 not in
in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in。如果一定要使用in，那么in包含的值不应过多
4.5. where子句
避免在where子句中对字段进行null值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。针对为null的数据可以给个默认值代替
应尽量避免在 where 子句中使用!=操作符
where 子句中使用!=或<>操作符，将导致引擎放弃使用索引而进行全表扫描，可以使用 or 代替。
num!=10 换为 num<10 or num>10
避免在where子句中对字段进行表达式操作
这会造成引擎放弃使用索引
where salary=20000/2
尽量避免在where子句中对字段进行函数操作
这将导致引擎放弃使用索引而进行全表扫描
where substring(name,1,3)='abc'
4.6. or
如果限制条件中其他字段没有索引，尽量少用or
or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替or会得到更好的效果。
4.7. 尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。
4.8. 不建议使用%前缀模糊查询
比如like '%abc'或者like '%abc%'，这种查询会导致索引失效而进行全表扫描。但是可以使用like 'abc%'。
4.9. 避免隐式类型转换
比如is_success是varchar(1)类型，如果你传数值型的1或0，数据库会隐式转换为字符串形势的'1'或'0'则会导致索引失效。
4.10. 如果排序字段没有用到索引，就尽量少排序
4.11. 关于JOIN优化
驱动表是join优化的突破口！ 那什么是驱动表呢？
- 指定了联接条件时，满足查询条件的记录行数少的表为驱动表
- 未指定联接条件时，行数少的表为驱动表（Important!）
如果你搞不清楚该让谁做驱动表、谁 join 谁，就别指定谁 left/right join 谁了，直接使用join即可
