我有一个很大的表(8gb)，其中包含有关文件的信息，并且我需要针对该表运行一个报告，该报告看起来像这样：

(select * from fs_walk_scan where file_path like '\\server1\groot$\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\server1\hroot$\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\server1\iroot$\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\server2\froot$\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\server2\groot$\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\server3\hroot$\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\server4\iroot$\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\server5\iroot$\%' order by file_size desc limit 0,30)
[...]
order by substring_index(file_path,'\',4), file_size desc

此方法完成了我需要做的事情：获取每个卷的30个最大文件的列表。但是，这非常慢，并且即使"喜欢"的搜索位于另一个表中也可以进行硬编码。

我正在寻找一种无需多次浏览巨大表就可以做到这一点的方法。有人有想法么？

谢谢。

P.S.我无法以任何方式更改巨大源表的结构。

更新：在file_path和file_size上有索引，但是每个这些sub(？)查询仍然需要大约10分钟，而且我必须最少执行22分钟。

解决方案

我们在那张桌子上有什么样的索引？该指标：

在fs_walk_scan上创建索引fs_search_idx(file_path，file_size desc)

如果我们还没有这样的查询，它将大大加快此查询的速度。

更新：

我们说过file_path和file_size上已经有索引...它们是单独的索引吗？还是只有一个索引，两个列都被索引在一起？对于此查询，差异将是巨大的。即使有22个子查询，如果索引正确，这也应该很快。

这样的事情怎么样(还没有测试过，但是看起来很接近)：

select * from fs_walk_scan where file_path like '\\server' and file_path like 'root$\%' order by file_size desc

这样，我们可以在单个字段上进行一对比较，这些比较通常会与我们所描述的相匹配。也可以使用正则表达式，但是我还没有这样做。

我们可以使用正则表达式：

select * from fs_walk_scan
  where file_path regexp '^\\server(1\[ghi]|2\[fg]|3\h|[45]\i)root$\'

否则，如果我们可以修改表结构，请添加两列以保存服务器名称和基本路径(并对其进行索引)，以便可以创建一个更简单的查询：

select * from fs_walk_scan
  where server = 'server1' and base_path in ('groot$', 'hroot$', 'iroot$')
     or server = 'server2' and base_path in ('froot$', 'groot$')

我们可以设置触发器以在插入记录时初始化字段，也可以随后进行批量更新以填充两个额外的列。

我们可以执行以下操作...假设fs_list包含" LIKE"搜索列表：

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`proc_fs_search` $$
CREATE PROCEDURE `test`.`proc_fs_search` ()
BEGIN

DECLARE cur_path VARCHAR(255);
DECLARE done INT DEFAULT 0;

DECLARE list_cursor CURSOR FOR select file_path from fs_list;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @sql_query = '';

OPEN list_cursor;

REPEAT
  FETCH list_cursor INTO cur_path;

  IF NOT done THEN
    IF @sql_query <> '' THEN
      SET @sql_query = CONCAT(@sql_query, ' UNION ALL ');
    END IF;

    SET @sql_query = CONCAT(@sql_query, ' (select * from fs_walk_scan where file_path like ''', cur_path , ''' order by file_size desc limit 0,30)');
  END IF;

UNTIL done END REPEAT;

SET @sql_query = CONCAT(@sql_query, ' order by file_path, file_size desc');

PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

试试这个。
我们希望获得每条记录，其中少于30条记录的文件大小更大且文件路径相同。

SELECT * 
FROM   fs_walk_scan a
WHERE  ( SELECT COUNT(*) 
         FROM   fs_walk_scan b 
         WHERE  b.file_size  > a.file_size 
         AND    b.file_path  = a.file_path
       ) < 30

编辑：

显然，这就像狗一样。那么...这种循环语法怎么样？

SELECT DISTINCT file_path
INTO tmp1
FROM   fs_walk_scan a

DECLARE path VARCHAR(255);

SELECT MIN(file_path)
INTO   path
FROM   tmp1 

WHILE  path IS NOT NULL DO
    SELECT * 
    FROM   fs_walk_scan
    WHERE  file_path = path
    ORDER BY file_size DESC
    LIMIT 0,30

    SELECT MIN(file_path)
    INTO   path
    FROM   tmp1
    WHERE  file_path > path 
END WHILE

这里的想法是
1.获取文件路径列表
2.循环，对每个路径进行查询，这将获得30个最大的文件大小。

(我确实查过语法，但是我对MySQL不太热衷，因此如果应用程序还不很完善，请继续使用。请随时进行编辑/注释)