要理解优化关联查询的第一个技巧,就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单,它对任何的关联都执行 嵌套循环关联 操作,即先在一个表中循环取出单条数据,然后在嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为为止。然后根据各个表匹配的行,返回查询中需要的各个列。
太抽象了?以上面的示例来说明,比如有这样的一个查询:
SELECT A.xx,B.yy
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)
假设MySQL按照查询中的关联顺序A、B来进行关联操作,那么可以用下面的伪代码表示MySQL如何完成这个查询:
outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
inner_row = inner_iterator.next;
while(inner_row) {
output[inner_row.yy,outer_row.xx];
inner_row = inner_iterator.next;
}
outer_row = outer_iterator.next;
}
可以看到,最外层的查询是根据 A.xx 列来查询的, A.c 上如果有索引的话,整个关联查询也不会使用。再看内层的查询,很明显 B.c 上如果有索引的话,能够加速查询,因此只需要在关联顺序中的第二张表的相应列上创建索引即可。
优化LIMIT分页
当需要分页操作时,通常会使用 LIMIT 加上偏移量的办法实现,同时加上合适的 ORDER BY 字句。如果有对应的索引,通常效率会不错,否则,MySQL需要做大量的文件排序操作。
一个常见的问题是当偏移量非常大的时候,比如: LIMIT 10000 20 这样的查询,MySQL需要查询10020条记录然后只返回20条记录,前面的10000条都将被抛弃,这样的代价非常高。
优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描,而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时,这样做的效率会提升非常大。考虑下面的查询:
SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
如果这张表非常大,那么这个查询最好改成下面的样子:
SELECT film.film_id,film.description
FROM film INNER JOIN (
SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);
这里的延迟关联将大大提升查询效率,让MySQL扫描尽可能少的页面,获取需要访问的记录后在根据关联列回原表查询所需要的列。
有时候如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就可以避免使用 OFFSET ,比如下面的查询:
SELECT id FROM t LIMIT 10000, 10;
改为:
SELECT id FROM t WHERE id > 10000 LIMIT 10;
其他优化的办法还包括使用预先计算的汇总表,或者关联到一个冗余表,冗余表中只包含主键列和需要做排序的列。
优化UNION
MySQL处理 UNION 的策略是先创建临时表,然后再把各个查询结果插入到临时表中,最后再来做查询。因此很多优化策略在 UNION 查询中都没有办法很好的时候。经常需要手动将 WHERE 、 LIMIT 、 ORDER BY 等字句“下推”到各个子查询中,以便优化器可以充分利用这些条件先优化。
除非确实需要服务器去重,否则就一定要使用 UNION ALL ,如果没有 ALL 关键字,MySQL会给临时表加上 DISTINCT 选项,这会导致整个临时表的数据做唯一性检查,这样做的代价非常高。当然即使使用ALL关键字,MySQL总是将结果放入临时表,然后再读出,再返回给客户端。虽然很多时候没有这个必要,比如有时候可以直接把每个子查询的结果返回给客户端。
结语
理解查询是如何执行以及时间都消耗在哪些地方,再加上一些优化过程的知识,可以帮助大家更好的理解MySQL,理解常见优化技巧背后的原理。希望本文中的原理、示例能够帮助大家更好的将理论和实践联系起来,更多的将理论知识运用到实践中。
其他也没啥说的了,给大家留两个思考题吧,可以在脑袋里想想答案,这也是大家经常挂在嘴边的,但很少有人会思考为什么?
有非常多的程序员在分享时都会抛出这样一个观点:尽可能不要使用存储过程,存储过程非常不容易维护,也会增加使用成本,应该把业务逻辑放到客户端。既然客户端都能干这些事,那为什么还要存储过程?
JOIN 本身也挺方便的,直接查询就好了,为什么还需要视图呢?
参考资料
[1] 姜承尧 著;MySQL技术内幕-InnoDB存储引擎;机械工业出版社,2013
[2] Baron Scbwartz 等著;宁海元 周振兴等译;高性能MySQL(第三版); 电子工业出版社, 2013
[3] 由 B-/B+树看 MySQL索引结构
【编辑推荐】
不停止 MySQL 服务增加从库的两种方式
Python操作MySQL基本环境搭建及增删改查实现
在Docker中运行MySQL:多主机网络下Docker Swarm模式的容器管理
Java中如何使用嵌入MySQL
将数据从MySQL迁移到Oracle的注意事项
(责任编辑:admin)