MySQL进阶：优化嵌套查询和分页查询

优化嵌套查询

嵌套查询（子查询）可以用SELECT命令创建单列查询结果，然后在另一个查询中使用这个结果作为过滤条件查询正在处理。嵌套查询编写简单且易于理解。然而，有时可以用更高效的连接（JOIN）来代替。

现在，如果你想查找从未在网站上购买过的客户，即可以查询客户表中但未在付款表中的客户数据。

嵌入式查询：

explain select * from customer where customer_id is not returned (select customer_id from payment);

连接重写：

explain select * from customer left join payment b = b a.customer_id 。 customer_id where b.customer_id is null;

旁白：连接查询效率更高，因为MySQL不需要在内存中创建临时表来完成逻辑，需要两步。查询有效；而不存在表示MYSQL对LEFT JOIN进行了优化，一旦找到符合LEFT JOIN条件的行，就会停止搜索。

优化分页查询

对于MySQL分页查询，MySQL不会跳过偏移行，它会获取offset+N行并在放弃并返回N行之前返回偏移行。如果偏移量特别大，效率就很低。例如“限制 1000.20”。目前，MySQL仅支持1001-1020。前1020条数据排序后需要一条记录。前1000条数据被丢弃，查询和排序的成本非常高。可见MySQL的分页处理并不完善。我们需要对分页SQL做一些优化，要么控制返回的页面总数，或者需要重写SQL，使页面超过某个阈值。 。

画外音：控制返回页数不太靠谱。毕竟每个页面的数据量不能太大。当有更多数据可用时，控制返回页面的总数变得不现实。因此，您仍然需要 对高于特定阈值的页面执行 SQL 重写。

现在假设你想对电影表的电影进行排序，得到某页数据

解释 select * from movie order by title border 50.5;

可以看到优化器其实是一个全表扫描和流程效率低下。

第一个优化思路

对索引进行排序和分页操作，最后根据主键将表查询所需的其他列的内容关联回来。

画外音：这里包含了SQL优化的两个重要概念，索引覆盖率和表后端。我在上一篇文章中详细介绍了这两个概念。通过索引覆盖率在索引上完成扫描和排序（索引没问题），最后通过主键返回表查询（InnoDB引擎索引通过主键返回表），最大限度减少所需的I/O查询表返回。

说明 select * from film a inner join（根据片名限制50.5从电影顺序中选择film_id）b on a.film_id = b.film_id;

第二种优化思路

转换LIMIT查询变成在某个地方进行查询，以减少分页的压力。

假设现在每页有10条数据，应该取第42页的数据。

解释 Select * from film order by title limit 410.10;

现在还需要传递一个参数，即上一页（第 41 页）最后一个数据的主题标题。

SQL 可以重写如下：

解释 select * from movie where title>'HOLES BRANNIGAN' order by title limit 10;

这将 LIMIT m,n 转换为 LIMIT n 查询，但这解决方案仅适用于非显示查询重复值的特定环境，否则可分页结果可能会丢失。

总结

优化嵌套查询和分页查询最终遵循SQL优化的基本原则之一——减少表返回查询的I/O次数。对于分页查询优化，最好使用第一种优化方案，它提供更好的性能和更大的稳定性。

作者：CoderFocus
来源：掘金