1、尽量避免使用子查询
例:
SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='Yoona');
子查询在MySQL5.5版本里，内部执行计划器是这样执行的:
先查外表再匹配内表，而不是我们认为的先查出整个内表t2，作为临时表给外表使用。
( 先从t1表中取出一条记录，查询内表，从内表查询中的结果判断此次取出的外表的记录是否符合要求，依次一条一条取、一条一条查，循环N遍，浪费时间资源 )
有以下子查询示例：
SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
你肯定认为这个 SQL 会这样执行：
1、SELECT t2.b FROM t2 WHERE id < 10;
2、得到结果: 1,2,3,4,5,6,7,8,9
3、select * from t1 where t1.a in(1,2,3,4,5,6,7,8,9);
但实际上 MySQL 并不是这样做的。MySQL 会将相关的外层表压到子查询中，优化器认为这样效率更高。也就是说，优化器会将上面的 SQL 改写成这样：
select * from t1 where exists(select b from t2 where id < 10 and t1.a=t2.b);
因此，当外表的数据很大时，查询速度会非常慢。
MySQL子查询优化的技术或优化策略，包括三种，分别为：
- semi-join：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做 semi-join 的操作。关键词是“上拉”。
MySQL提供5种优化策略，来进一步优化semi-join操作，分别是： - DUPS_WEEDOUT/重复剔除 - LOOSE_SCAN/松散扫描 - FIRST_MATCH/首次匹配 - MATERIALIZE_LOOKUP/索引式物化 - MATERIALIZE_SCAN/扫描式物化 这5种子优化策略，需要通过代价估算完成最优选择。
- Materialization：物化子查询，子查询的结果通常缓存在内存或临时表中
- EXISTS strategy：把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词是“下推”。(如上例)
| 子查询格式 | 可选的优化策略 |
|---|---|
| IN / = / ANY | Semi-join， Materialization，EXISTS strategy |
| NOT IN / <> / ALL | Materialization， EXISTS strategy |
注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略，会以 exists 方式执行，优化的方法也很简单，改成 join 即可（这里是 delete，不用担心重复行问题）：
DELETE biz_customer_incoming_path
FROM biz_customer_incoming_path a
JOIN biz_customer_incoming b
WHERE a.bizCustomerIncoming_id=b.id and b.cid='7Ex46Dz22Fqq6iuPCLPlzQ';
延伸：为什么子查询比连接查询（LEFT JOIN）效率低
示例：SELECT goods_id,goods_name FROM goods WHERE goods_id = (select max( goods_id ) from goods );执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
优化方式：
可以使用连接查询（JOIN）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。
MySQL 子查询优化【他建的表有主键索引、a索引】
Semi-join 之 Materialization 子查询优化策略
Semi-join 之 FirstMatch 子查询优化策略
Semi-join 之 DuplicateWeedout 子查询优化策略
Semi-join 之 LooseScan 子查询优化策略
2、用IN来替换OR
- 低效查询
SELECT * FROMt WHERE LOC_ID= 10ORLOC_ID= 20 ORLOC_ID= 30;
- 高效查询
SELECT * FROM t WHERE LOC_IN IN(10,20,30);
对于许多数据库服务器而言，IN( )列表不过是多个OR语句的同义词而已，因为IN和OR在逻辑上是等同的。不仅是在MySQL数据库服务器，对于许多其他的数据库服务器使用到IN查询时，都是按照如下方式处理的:
- 对IN列表中的数值进行排序。
- 对于查询的匹配，每次使用二分查找去匹配IN列表的数值。
所以对于第2步，每次比较的算法复杂度大概为O(log n)。相反，对于同样逻辑的OR列表，每次都要遍历，所以OR相应的算法复杂度为O(n)(因此对于遍历非常大的OR列表，会很缓慢！)。
因此，在了解了IN和OR的区别之后，每次优化，我们可以采用如下方式：
- 尽量将能使用IN来代替OR查询。
- 对IN列表中的数据，写SQL的时候就排好序，避免MySQL来做这个工作。
延伸：同理，对于连续的数字能用between就不要用in了，between只需要比对两个数字，而in全都要比对。
3、读取适当的记录LIMIT M,N，而不要读多余的记录
SELECT * FROM t WHERE 1;
----->
SELECT * FROM t WHERE 1 LIMIT 10;
以及快速定位范围：
select id,name
from table_name limit 866613, 20
使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：
select id,name from table_name
where id> 866612 limit 20
据数据库这种查找的特性，就有了一种想当然的方法，利用自增索引（假设为id）：
由于普通搜索是全表搜索，适当的添加 WHERE 条件就能把搜索从全表搜索转化为范围搜索，大大缩小搜索的范围，从而提高搜索效率。
这个优化思路就是告诉数据库：「你别数了，我告诉你，第10001条数据是这样的，你直接去拿吧。」
4、避免数据类型不一致
SELECT COUNT(*)
FROM p_video_circle_relation a
LEFT JOIN p_video_info b
ON a.video_id = b.work_id
WHERE a.circle_id = 212307047 ;
SQL关联查询消耗662ms
仔细观察发现关联字段video_id和work_id的数据类型并不一致，video_id是bigint类型，work_id是varchar类型，关联查询时必须将关联字段转换成相同的类型才能进行比较，数据越多，转换需要的时间越长
将work_id修改为bigint类型后，查询仅需13ms
5、总和查询可以禁止排重用union all
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据（业务上需要不重复）。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。
另外，如果排序字段没有用到索引，就尽量少排序。
6、批量INSERT插入
常用的插入语句如：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
修改成：
INSERT INTO`insert_table`(`datetime`,`uid`,`content`,`type`)
VALUES('0','userid_0','content_0',0),('1','userid_1','content_1',1);
修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。
7、尽量不用select *
SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名。
8、区分in和exists
select * from 表A
where id in ( select id from 表B )
上面sql语句相当于
select * from 表A
where exists ( select * from 表B where 表B.id=表A.id )
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键）
如果是exists，那么以外层表为驱动表，先被访问，然后根据外表的数据去和内表进行比较和判断；
而如果是IN，那么先执行子查询，再将内表的数据拿去和外表进行比较与判断。
因此，如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
延伸：如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
