在select查询中重复累积函数的性能

Question

我有一个SQL查询(在MySQL中)，它选择一个总计，并计算有多少个已完成的任务拥有该表：

SELECT
  count(*) as total, IF(SUM(NOT `completed`) IS NULL,0,SUM(NOT `completed`)) as incomplete
FROM
  tasks

这张桌子可以做成这样的东西：

CREATE TABLE `tasks` (
  `clave` int(11) NOT NULL AUTO_INCREMENT,
  `completed` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'If it is 0 isn\'t completed, otherwise is completed',
  PRIMARY KEY (`clave`)
) ENGINE=InnoDB;

您可以观察到，在查询中，我使用了两次SUM(NOT completed)，一次检查它是否返回null (并返回0)，另一次返回计数(当它不是null时)。如果表(或查询)为空，则SUM()函数返回null，即表根本没有行。

当你试图求和两次时，我想MySQL会计算两次求和。

我已经测试过是否可以为该列设置别名，然后在if中使用它，因此mysql不需要重新计算它。

而且，在任何情况下(例如，如果表任务是空的)，不完全列都不能为空。

我的问题是，这是有效的吗？MySQL每次都需要重新计算和或者它记得它吗？

Answer 1

这可能是一种更好的方法，因为您对性能进行的计算较少。

使用richard发布的模式-请参阅小提琴

SELECT 
    total, 
    num_complete, 
    total - num_complete as num_incomplete
FROM(
    SELECT
        COUNT(*) as total,
        SUM(IF(t.completed > 0, 1, 0)) as num_complete
    FROM status_log t
) as t

检查每个OP请求中的空表

SELECT 
    total, 
    num_complete, 
    total - num_complete as num_incomplete
FROM(
    SELECT
        COUNT(*) as total,
        IF(COUNT(*) > 0, SUM(IF(t.completed > 0, 1, 0)), 0) as num_complete
    FROM status_log t
) as t

您应该在另一种编程语言中检查空表或空表。一般来说，应该使用SQL从表中进行查询。但当它是空的时候..。如果它是完全空的，那么在以另一种编程语言运行此查询时，应该检查是否为空响应。这将大大提高它的性能。

Answer 2

效率(或缺乏效率)将更多地与表上的索引以及存储引擎本身有关。同样，结果是否存储在缓存中将更多地与存储引擎有关，而不是与您正在编写的语句有关。

如果我是在基于INNODB的存储引擎上编写这篇文章的话，我会这样做：

 SELECT   
       count(*) as total,    
       SUM(CASE WHEN completed = 0 OR completed IS NULL THEN  1 ELSE 0 END) AS incomplete 
 FROM tasks;

为了做到这一点，我会索引我的“已完成”一栏。

我将从"IF“改为case--主要是做代码可移植性的原因。如果需要的话，什么时候更容易移到其他数据库。

此外，已完成的索引将允许此查询简单地计算索引，而非表值本身。对于LRU来说，这会给你带来更多的效率。

Answer 3

下面是使用CASE语句的解决方案。可以从单个SQL语句获得两个聚合值。第二个查询示例显示了与直接聚合查询语句相比，CASE语句如何获得一个旋转输出：

[SQL Fiddle][1]

MySQL 5.5.32模式设置

CREATE TABLE status_log 
    (
     id int auto_increment primary key, 
     type varchar(20), 
     status varchar(30)
    );

INSERT INTO status_log
(type, status)
VALUES
('Alpha', 'COMPLETE'),
('Bravo', 'INCOMPLETE'),
('Charlie', 'INCOMPLETE'),
('Delta', 'COMPLETE'),
('Echo', 'COMPLETE'),
('Foxtrot', 'INCOMPLETE'),
('Golf', 'COMPLETE'),
('Hotel', 'COMPLETE')

查询1

SELECT count(1) as count_by_status, status
  FROM status_log
 GROUP BY status

结果

| COUNT_BY_STATUS |     STATUS |
|-----------------|------------|
|               5 |   COMPLETE |
|               3 | INCOMPLETE |

查询2

SELECT count(*) as total_count, 
  sum(case when status = 'COMPLETE' then 1
           else 0 end) as completed_count,
  sum(case when status = 'INCOMPLETE' then 1
           else 0 end) as incomplete_count
  FROM status_log

结果

| TOTAL_COUNT | COMPLETED_COUNT | INCOMPLETE_COUNT |
|-------------|-----------------|------------------|
|           8 |               5 |                3 |