接上文，其他的一些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，是一个更好的队列容器。

查询两点之间的距离

这个主要是需要注意，什么情况下使用索引。显然转化为矩形的情况下会使用索引。 使用矩形得出晓得数据集之后再进行精确计算。