MySQL之SQL优化篇（六）：其他查询优化

MySQL之SQL优化篇（六）：其他查询优化

  在了解了索引之后，已经能对有关索引的查询做出优化，本节将探讨索引之外的SQL优化，例如关联查询、分页、排序等。

上一节：MySQL之SQL优化篇（五）：索引查询优化

前言

  “索引之外的优化”，听到这句话心里可能会有疑问，这只是笔者为了方便记忆自己取的名字。如果能对MySQL架构有个简单的认知，可能会很好理解这句话。如下图所示，在《MySQL之SQL优化篇（五）：索引查询优化》一节中，主要解释了存储引擎层的InnoDB的索引优化，而这一节将讲解MySQL服务器存储引擎之外会发生的事情，然后结合原理来讲解常用的经验法则。

MySQL是如何优化和执行查询的？

  在开始优化之前，了解大致流程是很有必要的，优化SQL其实就是优化服务器执行SQL的各个阶段所用的时间。

1. 从上图中可以很清晰地看到客户端发送查询请求后，MySQL服务器先访问查询缓存，看一下当前SQL需要的数据是否已经被缓存，如果是，取出直接返回。如果不是，则发送给解析器，初步解析成解析树（第一个解析器其实叫做语法解析器，使用语法规则验证和解析查询）。之后，预处理器会对解析器生成的解析树进行验证（检查解析树是否合法，如检查表、列是否存在，检查命名是否冲突，之后验证权限）。
2. 解析树合法之后，接下来，到了一个很关键的位置，也是MySQL帮助我们偷懒的一个位置，查询优化器会根据解析树生成一个查询计划（一个查询可以有多种实现方式，MySQL将通过一系列算法选择其认为最合适的查询计划）。以下是一些优化器会执行的优化：
   （1）重新定义关联表的顺序
     在内连接时，不管是大表在前，还是小表在前，效果是一样的，背后的原因就是优化器会帮你选择最佳的连接方式。

-- 该语句可以查看最后一次查询的成本，变换大表和小表的关联顺序，发现查询的成本是一样的
show status like 'last_query_cost'

（2）使用等价变换规则
  优化器可以使用一些等价变换来简化并规范表达式。它可以合并一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如，（1=1 and a > 1）会被改成a>1。
（3）优化COUNT()、MIN()、MAX（）
  索引和列是否为空通常可以帮助MySQL优化这类表达式。可以理解为优化器使用索引结构来优化这类查询值的获取，将其查询结果视作一个常数并暂存起来，之后便不再需要查该结果，直接用这个常数。
（4）预估并转化为常数表达式
  当优化器检测到一个表达式可以转换为常数的时候，就会把该表达式作为常数进行优化处理。
（5）覆盖索引扫描
  当查询列包含在索引中，优化器就直接在索引中取数据，不用回表再查一次，参考上一节。
（6）子查询优化
  优化器能减少多个子查询的相同数据访问，具体细节未知，总之，对于能优化的子查询，优化器就会帮你优化。
（7）提前终止查询
  在发现已经满足查询需求的时候，就立即停止查询，如limit。
（8）等值传播
  在两个表的两个列上进行等式关联，那么会将其中一列的条件传递到另外一列，这通常是我们没有感知的，在逻辑上看确实就是这个逻辑。

select a.city 
from user u
join address a
on u.id = a.user_id
where u.id = 1;
-- 等价于
 select a.city 
from user u
join address a
on u.id = a.id
where u.id = 1 and a.user_id = 1;
-- 但是没必要这么写

（9）列表IN()的比较
  在MySQL中，会将IN()中的条件值进行排序，然后用二分查找来判断当前值是否在列表中。通过or来连接，复杂度为O(n)，而使用二分查找，复杂度则为O(log n)，对于IN()中存在大量数据时，这种方式将会更高效。
MySQL的优化器还是很复杂的，除了这些以外，优化器还会做很多工作。我们需要明白的就是，MySQL的优化器会为你的SQL选择它认为最佳的方式去执行。

3. 得到查询计划，接下来查询执行引擎便会根据查询计划去取数据，然后返回给客户端。在返回的时候，并不是查询完所有结果再返回，而是查到结果就开始返回。

常见SQL优化

1. in 加子查询并不像你想的那样执行（5.5及以下，新版本待研究）

-- 下面这个SQL，我们想当然的是先查出B中所有符合条件的id，然后再执行外层in查询
select A.*
from A
where id in
(
select id from B where name like 'abc%'
);
-- 然而，事实并不是这样的，MySQL会将其改写成下面这个语句
select A.*
from A
where exists
(
select * from B where B.name like 'abc%' and A.id = B.id
)
-- 这样一来，就会对A做全表扫描，如果A数据量很大，那么效率可想而知

因此，对于in加子查询的使用需要谨慎，下面是一些优化的方法
（1）使用group_concat函数提前计算in()中的列表
（2）使用join查询

2. 不惧怕使用关联子查询

-- 下面是一个关联子查询，对于该查询来说，通常可改写为join，具体选用哪个，可以实际测试一下
select A.*
from A
where not exists
(
select * from B where A.id = B.a_id and B.name like 'abc%'
)

3. 优化union查询（除非需要过滤重复行，不然应该使用union all）

-- 下面是一个常见的union查询，在limit之前，会在一个临时表中存放所有结果，
-- 之后再到临时表中取10条数据，很容易就可以发现，这样很浪费空间
(
select id, name
from A
where age < 10
)
union
(
select id, name
from B
where age < 25
) 
order by name
limit 1, 10;
-- 可以把限制语句下放到每个查询中
(
select id, name
from A
where age < 10
order by name
limit 10
)
union
(
select id, name
from B
where age < 25
order by name
limit 10
) 
order by name
limit 1, 10;

3. 优化count()查询
     count()函数用于计数，在计算行数时，如果写成count(列名)这种形式，当那一列可以为null时，count(null)是不计数的。应该使用count(*)来计数，它会忽略所有列的情况，直接统计行数。

4. 使用近似值
     在有些情况下，我们并不需要精确值，近似值就能满足我们的要求。获取精确值的开销一般来说比近似值大，此时采用近似值可以很好地优化查询效率。EXPLAIN命令能返回查询语句大致行数，由于不需要真正地去执行查询，开销非常低。如果获取的是类似行数这种数据，可以使用EXPLAIN返回的估计数据。

5. 优化关联查询
   （1）确保ON或者USING子句中使用的列上有索引。MySQL关联表时，会根据连接使用的列去进行查询，此时就可以用该列上的索引优化速度。
   （2）确保GROUP BY或者ORDER BY中的表达式只涉及一个表，这样就能使用那张表上的索引等进行优化，跨表操作不会使用索引。

6. 优化GROUP BY和DISTINCT
   （1）使用索引列来进行分组效率会更高；
   （2）取消不必要的排序。当只需要分组而不需要排序时，一般只写GROUP BY不写ORDER BY语句，可是这样结果集还是会根据分组的字段自动排序。想要取消排序可以添加ORDER BY NULL语句。如果是按照分组字段进行排序，只需要在GROUP BY后面加DESC或ASC即可

-- 只需要分组
select *
...
group by id
order by null

-- 同字段需要排序
select *
...
group by id desc

7. 优化LIMIT分页
   LIMIT分页优化策略需要结合业务逻辑来选择。
   （1）需要浏览所有数据，有可能看到最后一页，需要知道当前第几页，有多少条数据
   比如说，有一张20000条数据的表，需要查询limit 1500, 10，这时候的效率和limit 1, 10大不相同，这时候需要查询1500页的数据，然后返回最后一页的10条，这样太浪费内存和时间了，有下面的优化策略：利用索引扫描最少的数据，然后和原表关联获取其他列数据

select id, c1, c2
from A a
join 
(
	select id 
	from A 
	order by c3 
	limit 1500, 10
) a_t
on a.id = a_t.id

（2）需要浏览所有数据，有可能看到最后一页的情况，不需要知道当前第几页，有多少条数据
如果每页需要查看10条数据，那么每次查询都查出11条数据，如果第11条数据存在，那么显示下一页按钮，否则不显示，说明已经到最后一页了。
（3）不需要浏览所有数据，限制访问热点数据。举个例子，对于评论来说，很少有人会一页一页翻到10000页这种情况，一般把最新点赞最高的放出来就可以了，限制500条或者更多一点，够用就可以。这样一来，使用普通的分页查询即可满足业务要求。

8. 自定义hash索引列加速查询
   例：对于某张表来说，c列（比较长）经常被查询，需要查全名，对其建立B+树索引的话，存储占用比较大。此时可以新建一个c_hash列，存储c列的hash值(可以使用触发器维护c_hash列的值)，并对其建立索引。在查找的时候通过hash函数计算c列值的hash值，找到对应行即可。为了避免hash冲突，在where条件中还需要将原值条件加上。下面是一个例子：
   有net表如下

id	c	c_hash
1	aaabbbccc	2072686134
2	aaabbbddd	2950740951
3	aaabbbeee	2072686134

-- 根据索引查询的知识，首先会根据c_hash列的索引找到相关行，之后用原值进行精确匹配
-- 示例使用crc32（循环冗余校验，优点速度快）函数（还有md5，sha1等）作为hash函数
-- c_hash的重复值是为了演示
select id, c
from net
where c_hash = crc32('aaabbbeee') 
and c = 'aaabbbeee'