优化关联查询

如果想要优化使用关联的查询，我们需要特别留意以下几点：

1. 确保ON或者USING子句中的列上有索引。在创建索引的时候需要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建索引。除非有其他理由，否则只需要在关联顺序的第二个表的对应列上创建索引，没有用的索引只会带来额外的负担。

2. 确保任何的GROUP BY 和 ORDER BY 中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

3. 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。

优化GROUP BY 和 DISTINCT

在很多场景下，MySQL都使用同样的办法优化这两种查询，事实上MySQL优化器会在内部处理的时候相互转换这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。

当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。对于任何查询语句，这两种策略的性能都可有可以提升的地方。我们可以通过提示 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来让优化器按照你希望的方式运行。

优化LIMIT 分页

在系统中需要进行分页操作的时候，我们通常会使用LIMIT 加上偏移量的方法实现，同时加上合适的ORDER BY 子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

但是有一个非常常见又头疼的问题就是，在偏移量非常大的时候，例如可能是 LIMIT 10000,20这样的查询，此时MySQL需要查询10020 条记录并只返回最后的20条，这样的代价非常高。

如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作最后返回所需要的列。对于偏移量很大的时候，这样做的效率会提高很多。考虑下面这个查询：

SELECT file_id, description FROM sakila.filmORDER BY title LIMTI 50, 5;

如果这个表很大，最好可以将这个查询修改成下面这个样子：

SELECT file.file_id, file.descriptionFROM sakila.film    INNER JOIN(        SELECT film_id FROM sakila.film        ORDER BY title LIMIT 50, 5
    )

这里的“延迟关联”将大大提高查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询所需要的所有列。

还有另外一种优化的方法，就是使用应用程序记录上次数据的位置，在下次查询时就可以直接从该记录的位置开始扫描，这样就避免了使用OFFSET。

使用这样的方法可以将其变成一个范围查询，无论翻页到多后面，其性能都会很好。

Java 填坑手册，欢迎fork我的GitHub仓库

作者：AmosH

原文链接：https://www.cnblogs.com/AmosH/p/10280788.html