接上文，其他的一些Mysql对于查询的优化，或者Mysql的不足，我们应该注意怎样优化。
关联子查询
Mysql的子查询实现比较有问题， 特别是对于IN(子查询)，这样的方式。
比如:查询一个订单表中，所有支付方式为4也就是京东网银钱包的订单：
EXPLAIN SELECT * FROM virtual_order vo WHERE vo.jd_order_id IN (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4);// 执行结果：
+—-+——————–+——-+—————–+—————+————-+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+——-+—————–+—————+————-+———+——+——+————-+
| 1 | PRIMARY | vo | ALL | NULL | NULL | NULL | NULL | 50 | Using where |
| 2 | DEPENDENT SUBQUERY | p | unique_subquery | jd_order_id | jd_order_id | 8 | func | 1 | Using where |
+—-+——————–+——-+—————–+—————+————-+———+——+——+————-+
实际上，上面的sql会被转化为：
SELECT * FROM virtual_order vo WHERE EXISTS (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4 AND vo.jd_order_id=p.jd_order_id);这意味着，先对vo进行了全表扫描，然后再对每一个jd_order_id去执行子查询，看是否返回true
EXISTS关键字决定了 其子查询是否能够有返回结果。
这种情况下，我们期望怎样执行呢？当然是先执行子查询，有结果了之后利用IN的二分查找来加快检索速度。
这样的话，我们就可以使用GROUP_CONCAT()来手工拼一个IN里面的内容，
或者改写为如下的内连接：
SELECT * FROM virtual_order vo INNER JOIN biz_pay_task p USING(jd_order_id) WHERE p.pay_type=4 ;但是关联子查询并不是一定比左连，内联等慢的。
比如NOT EXISTS的情况下，会有终止返回的优化，还是有可能变得比较快。 针对于这种，最好的方式就是进行测试，得到实际的执行结果，来进行判断。
UNION ALL的限制
(SQL1) UNION ALL (SQL2) LIMIT 20;
这种情况会把 SQL1和SQL2中的所有记录放到临时表中然后去LIMIT20.比较好的做法是。分别LIMIT 如下：
(SQL1 LIMIT 20) UNION ALL (SQL2 LIMIT 20) LIMIT 20;
等值传递
当遇到ON, USING的时候会诱发等值传递的优化，但是如果要传递的是IN()，或者其他比较复杂的等值时，有可能传递给内层之后影响内层的性能。
不能并行执行
没有哈希关联
可以使用自建哈希字段加索引
松散索引扫描
也就是跳着扫描。比如有一个聚合索引(a, b)如果WHERE中只有b的条件，那么因为用不到这个索引，就会变成全表扫描。 我们期望的是先从a的索引开始扫描，然后对于a的没一个索引键中因为b是有序的，因此这个时候又可以对于每个键中的b的区间范围进行索引扫描。比如如下的结构：
|a|b|
|1|1|
|1|2|
|1|3|
|1|4|
|2|1|
|2|2|
|2|3|
|2|4|
WHERE b between 2 and 3 扫描方式变为a1-b2-b3-a2-b2-b3，这样跳着扫描可以减少扫描行数，大幅度增加性能。
Mysql的主要应用掉在于GROUP BY 的语句中。Extra字段显示”Using index for group-by”
这里有一个更好的说明： 请点击
最大值和最小值优化
SELECT MIN(id) from user WHERE name=”wzj”;
这里会做全表扫描。 但是如果是从主键开始扫描的话，可以快很多。Mysql自己不能完成这种行为，但是我们可以手工的完成它，比如使用如下的语句：
SELECT id FROM user USE INDEX(PRIMARY) WHERE name=”wzj” LIMIT 1;
查询优化器的提示 hint
主要是认为的控制一下Mysql的优化过程，列举一些，更具体的需要看Mysql的官方文档了。
- DELAYED 插入数据放入缓冲区，提高对客户端的响应
- STRAIGHT_JOIN 这个之前使用过，可以强制指定驱动表
- SQL_SMALL_RESULT 和SQL_BIG_RESULT 告诉优化器对于SELECT GROUPBY这种的返回集的大概大小，如果小则用内存而不用临时表
- SQL_CACHE 告诉优化器这个结果存在缓存中
- USE INDEX 这个告诉优化器使用或者不适用哪些索引
- 还有一些配置项，之前也已经讲过了，有深度太多跳过选择执行计划的等等。
另外随着版本的更新可能会引起不一样的行为，因此最好每次升级都进行测试。可以使用Percona Tookit 的pt-upgrade工具来检查结果。
优化特定类型的查询
优化COUNT()
- COUNT(fieldname) 统计字段中不为NULL的值
- COUNT(*) 统计所有行，这个的性能要好一些
- MYISAM之所以COUNT(*)快，是因为实时更新了总行数，但是对于加了WHERE的就不快了。
- 利用MYISAM很快的特性，对于WHERE b>5,可以使用COUNT(*) - WHERE b < 5的特性，这样可以加快一些性能。
- 使用估算值，在特定场景下，可以使用去除DISTINCT，或者是WHERE后面对于结果集影响很小的条件，只求出估算值
- 更复杂的方式可以使用汇总表，索引覆盖，Memcached这样的外部缓存。快速，精确和简单，最多只能满足其二，所以优化肯定会有一个限度
优化关联查询
- 却表ON和USING子句的列上有索引， 创建索引时考虑好关联的顺序，只需要在驱动表上创建索引
- 确保任何GROUP BY和ORDER BY 表达式只涉及一个表的列，这样才能使用索引
- 注意不要使用全连，用内联和外联
优化子查询
尽量用关联代替
Mysql5.6中已经自带了这种优化，因此可以不是很关注了
优化LIMIT分页
- 利用覆盖索引，先查出有索引的列，然后使用连接操作
- 再有是先记录下当前查找到的最后的位置，然后再下一次查找是上WHERE子句过滤掉这个位置之前的值。比如SELECT * FROM user ORDER BY id DESC LIMIT 20;
这个时候得到最小的id是10000，那么下面就可以使用SELECT * FROM user WHERE id < 10000 ORDER BY id DESC LIMIT 20;来得到下20行，这样子来提升效率。 - 其他的方法，还可以使用冗余表，只包含主键和需要排序的数据列。
分页优化
- SQL_CALC_FOUND_ROWS 这个hint并不能真正的提高效率，其还是要统计所有的行的数量
- 使用EXPLAIN语句中的rows结果来做估算
- 读取21条，看是否有第21条来决定是否有下一页
- 读取1000条，其他的数据使用更多按钮来展示。
UNION优化
- 把优化的语句放到各个子查询中
-
确定只有必须要去除重复行的时候采用UNION,否则使用UNION ALL，免得对所有结果进行DISTINCT
静态查询分析
Percona 的pt-query-advisor
例子
构建队列表
mysql并不是最好的选择，一般会有如下两个问题要解决：
- 增长。 这种可以使用归档的方式
- 并发。 即有可能多个用户操作同一个任务，为了避免种种情况，可以加锁，但是加锁效率比较低。
对于增长可以归档，可以分片。
对于并发，我们可以使用乐观锁
或者使用如下的优化，我们一般基本的做法可以是这个样子的：给任务表增加一个woner字段。 然后执行任务的时候：
BEGIN;
SELECT id FROM task WHERE owner = 0 AND status=0 LIMIT 10 FOR UPDATE;
-- result 1,2
// 代码中dosomething.
UPDATE task SET status=1 , owner = CONNECTON_ID() WHERE id IN (--result);
COMMIT;这样会造成使用相同索引的相同查询有锁定， 可以使用如下的方式代替：
BEGTIN;
UPDATE task set status=1 , owner=CONNECTION_ID() WHERE owner = 0 AND status=0 LIMIT 10;
COMMIT;
SET AUTOCOMMIT = 0;
SELECT id FROM task where owner = CONNECTION_ID() AND status = 1;
// do something这种可以大幅的减少锁的时间，但是后续的处理可能会比较多，比如如果执行具体的事情失败，则还需要回写内容。
上面的这种也稍显复杂，最好还是使用悲观锁。
或者是直接使用redis，是一个更好的队列容器。
查询两点之间的距离
这个主要是需要注意，什么情况下使用索引。显然转化为矩形的情况下会使用索引。 使用矩形得出晓得数据集之后再进行精确计算。
