我在 MySQL 5.1 非规范化表上运行了这个 sql 查询。它按照我想要的方式工作，但它可能会很慢。我在 day 列上添加了一个索引，但它仍然需要更快。关于如何更快地获得这个的任何建议？ (也许改用连接？)

SELECT DISTINCT(bucket) AS b,
       (possible_free_slots -
          (SELECT COUNT(availability)
           FROM ip_bucket_list
           WHERE bucket = b
           AND availability = 'used'
           AND tday = 'evening'
           AND day LIKE '2012-12-14%'
           AND network = '10_83_mh1_bucket')) AS free_slots
FROM ip_bucket_list
ORDER BY free_slots DESC;

单个查询很快:

SELECT DISTINCT(bucket) FROM ip_bucket_list;
1024 rows in set (0.05 sec)

 SELECT COUNT(availability) from ip_bucket_list WHERE bucket = 0 AND availability = 'used' AND tday = 'evening' AND day LIKE '2012-12-14%' AND network = '10_83_mh1_bucket';
1 row in set (0.00 sec)

表:

mysql> describe ip_bucket_list;
+---------------------+--------------+------+-----+-------------------+----------------+
| Field               | Type         | Null | Key | Default           | Extra          |
+---------------------+--------------+------+-----+-------------------+----------------+
| id                  | int(11)      | NO   | PRI | NULL              | auto_increment |
| ip                  | varchar(50)  | YES  |     | NULL              |                |
| bucket              | int(11)      | NO   | MUL | NULL              |                |
| availability        | varchar(20)  | YES  |     | NULL              |                |
| network             | varchar(100) | NO   | MUL | NULL              |                |
| possible_free_slots | int(11)      | NO   |     | NULL              |                |
| tday                | varchar(20)  | YES  |     | NULL              |                |
| day                 | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+---------------------+--------------+------+-----+-------------------+----------------+

和 DESC:

DESC SELECT DISTINCT(bucket) as b,(possible_free_slots - (SELECT COUNT(availability) from  ip_bucket_list WHERE bucket = b AND availability = 'used' AND tday = 'evening' AND day  LIKE '2012-12-14%' AND network = '10_83_mh1_bucket')) as free_slots FROM ip_bucket_list  ORDER BY free_slots DESC;
+----+--------------------+----------------+------+-----------------------------------------+--------+---------+------+--------+---------------------------------+
| id | select_type        | table          | type | possible_keys                           | key    | key_len | ref  | rows   | Extra                           |
+----+--------------------+----------------+------+-----------------------------------------+--------+---------+------+--------+---------------------------------+
|  1 | PRIMARY            | ip_bucket_list | ALL  | NULL                                    | NULL   | NULL    | NULL | 328354 | Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | ip_bucket_list | ref  | bucket,network,ip_bucket_list_day_index | bucket | 4       | func |    161 | Using where                     |
+----+--------------------+----------------+------+-----------------------------------------+--------+---------+------+--------+---------------------------------+

最佳答案

我会使用连接将相关子查询从 SELECT 子句移动到 FROM 子句中:

SELECT distinct bucket as b,
       (possible_free_slots - a.avail) as free_slots
FROM ip_bucket_list ipbl left outer join
     (SELECT bucket COUNT(availability) as avail
      from ip_bucket_list
      WHERE availability = 'used' AND tday = 'evening' AND
             day LIKE '2012-12-14%' AND network = '10_83_mh1_bucket'
     ) on a
     on ipbl.bucket = avail.bucket
ORDER BY free_slots DESC;

SELECT 子句中的版本可能正在为每一行重新运行(甚至在 distinct 运行之前)。通过将它放在 from 子句中，ip_bucket_list 表将只被扫描一次。

此外，如果您希望每个存储桶只出现一次，那么我建议您使用 group by 而不是 distinct。它将阐明查询的目的。您可以完全消除对表格的第二个引用，例如:

SELECT bucket as b,
       max(possible_free_slots -
           (case when availability = 'used' AND tday = 'evening' AND
                      day LIKE '2012-12-14%' AND network = '10_83_mh1_bucket'
                 then 1 else 0
            end)
           ) as free_slots
FROM ip_bucket_list
group by bucket
ORDER BY free_slots DESC;

为了加快您的查询版本，您需要在 bucket 上建立索引，因为它用于相关子查询。

关于mysql - 使用算术运算优化MySQL嵌套select，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/13920151/