SQL查询优化

发表于 | 分类于 |

SQL COUNT查询优化

查询记录表总量有两种方法

1
2
SELECT COUNT(*) FROM table;
SELECT COUNT(col_name) FROM table;

这两种方法效果有所不同，COUNT(*)返回的一定是表记录总量，COUNT(col_name)返回的是该字段不为NULL值得记录总量。这两种查询可能一直也可能不一致。

在表记录较多的情况下，COUNT(*)的执行时间会比较长。在生产环境这非常可怕。因为这一慢查询的执行，可能引起其他SQL的效率低下，有可能引起链式反应像核爆炸那样耗尽服务器资源。

我们尽量要在生产环境避免这类语句执行。如果一定要，那么在满足一定条件下可以使用下面的小技巧来优化。

  1. 第一个条件是该表拥有自增长字段，并定义为主键。
  2. 第二个条件是该表不删除记录或只从头部删除数据，即保持表内记录的连续性。

满足以上条件，我们就可以使用下面的语句来替代COUNT(*)

1
2
3
4
SELECT (MAX(id) - MIN(id) + 1) AS total FROM table;
-- id是自增长字段，且是主键
SELECT MAX(id) AS total FROM table;
-- 在确定一定不会删除数据的情况下的简化

如果要使用WHERE来缩减范围，只要不破坏数据的连续性的条件也可以使用这个方法优化。比如id大于或者小鱼某个值，创建记录大于或者小于某个时间点等等。从优化原则来说，where可能会降低效率。但是如果where设定的合理，符合一定条件，也可以实现查询优化效果。如果条件是索引列，那么查询效率可能会较高。不过这是对于一般的SQL查询来说，如果前提是“查询记录总数”，那就不一定了。

如果这个索引列具有跟自增长字段一致的顺序且连续，这个对于“查询记录总条数”是很好的，在缩小数据集范围的同时，还可以利用上文给出的小技巧，利用自增长字段高效得出结果。在利用这一条件时，需要注意以下几点：

  1. 不要对时间字段使用函数 Exp. year(par_day)
  2. 正确使用时间段：尽量给出开始和结束时间，尽量避免单独使用大于或小于号
  3. 使用BETWEEN比使用大于小于号更好

当条件不具有连续性和顺序性时，如果能大量缩减数据集范围，也会有较高效率。但是就不能使用上文的小技巧了。

当使用SELECT COUNT(*) FROM table WHERE conditions时，速度快的原因是数据经过过滤之后条数较少。当条件不具有连续性和顺序性，且过滤之后数据集依旧庞大时，效率也依然不会提高，这种情况下应该怎么办?

此时可以设计一些辅助统计的表来帮助我们达成提升效率的问题。虽然这些标的数据也是通过低效的查询得来的，但是这种方法可以避免低效SQL影响生产系统。可以使用主从结构，专门使用一个从库来执行类似任务。另外，这种计算一般是一天一次或者一天数次，不会反复执行。

一般使用COUNT(*)的很多情况是为了实现分页功能，如果记录是一个千万级以上的表，分页显示实际用户很少能浏览全部页数。基于这个原因，记录总数的计算是没有必要特别精准。换句话说使用一个数量级差不多的估算值也是可以的。因此，我们就可以通过explain来获得估算值。

1
explain SELECT COUNT(*) AS aggregate FROM 'table'

结果可以获得一个估计值（数量级相同，相差10%左右？）

在网上经常会看到一个观点，推荐使用COUNT(1)来替代COUNT(*)，认为可以提高性能。首先我们来了解一下COUNT()函数的不同用法：

COUNT(*)计算全部行总和

COUNT(1)计算全部行总和，1代表一个固定值，不是第一个字段。其效果跟COUNT(*)`一样。

COUNT(col_name)计算该列值非null行总和

经过测试我们发现这两种方法的速度相差无几。使用explain可以看到，优化器对两种写法的分析结果是一致的。在优化器中，我们看到，它会使用主键索引。这对于提升性能有帮助。所以，在有计算COUNT()的表内尽量创建索引，尽量使用自增长字段作为主键。不仅在这种情况有帮助，对于很多情况都有好处。

最后，如果要使用COUNT(col_name)，那么你要非常清晰的理解你要的结果。

参考资料

  1. mysql查询优化count(*)-查询记录总条数