Что 'корректный' запрос состоит в том, чтобы выбрать совокупную сумму в MySQL?
У меня есть таблица, где я храню информацию о файлах, один список столбцов содержит размер файлов в байтах. (фактические файлы сохранены на диске где-нибудь),
Я хотел бы получить кумулятивный размер файла как это:
+------------+---------+--------+----------------+
| fileInfoId | groupId | size | cumulativeSize |
+------------+---------+--------+----------------+
| 1 | 1 | 522120 | 522120 |
| 2 | 2 | 316042 | 316042 |
| 4 | 2 | 711084 | 1027126 |
| 5 | 2 | 697002 | 1724128 |
| 6 | 2 | 663425 | 2387553 |
| 7 | 2 | 739553 | 3127106 |
| 8 | 2 | 700938 | 3828044 |
| 9 | 2 | 695614 | 4523658 |
| 10 | 2 | 744204 | 5267862 |
| 11 | 2 | 609022 | 5876884 |
| ... | ... | ... | ... |
+------------+---------+--------+----------------+
20000 rows in set (19.2161 sec.)
Прямо сейчас я использую следующий запрос для получения вышеупомянутых результатов
SELECT
a.fileInfoId
, a.groupId
, a.size
, SUM(b.size) AS cumulativeSize
FROM fileInfo AS a
LEFT JOIN fileInfo AS b USING(groupId)
WHERE a.fileInfoId >= b.fileInfoId
GROUP BY a.fileInfoId
ORDER BY a.groupId, a.fileInfoId
Мое решение однако, чрезвычайно медленно. (приблизительно 19 секунд без кэша).
Объясните дает следующие подробности выполнения
+----+--------------+-------+-------+-------------------+-----------+---------+----------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------+-------+-------------------+-----------+---------+----------------+-------+-------------+
| 1 | SIMPLE | a | index | PRIMARY,foreignId | PRIMARY | 4 | NULL | 14905 | |
| 1 | SIMPLE | b | ref | PRIMARY,foreignId | foreignId | 4 | db.a.foreignId | 36 | Using where |
+----+--------------+-------+-------+-------------------+-----------+---------+----------------+-------+-------------+
Как я могу оптимизировать вышеупомянутый запрос?
Обновление
Я обновил вопрос для обеспечения структуры таблицы и процедуры для заполнения таблицы 20 000 данных тестирования записей.
CREATE TABLE `fileInfo` (
`fileInfoId` int(10) unsigned NOT NULL AUTO_INCREMENT
, `groupId` int(10) unsigned NOT NULL
, `name` varchar(128) NOT NULL
, `size` int(10) unsigned NOT NULL
, PRIMARY KEY (`fileInfoId`)
, KEY `groupId` (`groupId`)
) ENGINE=InnoDB;
delimiter $$
DROP PROCEDURE IF EXISTS autofill$$
CREATE PROCEDURE autofill()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE gid INT DEFAULT 0;
DECLARE nam char(20);
DECLARE siz INT DEFAULT 0;
WHILE i < 20000 DO
SET gid = FLOOR(RAND() * 250);
SET nam = CONV(FLOOR(RAND() * 10000000000000), 20, 36);
SET siz = FLOOR((RAND() * 1024 * 1024));
INSERT INTO `fileInfo` (`groupId`, `name`, `size`) VALUES(gid, nam, siz);
SET i = i + 1;
END WHILE;
END;$$
delimiter ;
CALL autofill();
О возможном дублирующемся вопросе
Вопросом, связанным Точкой с запятой, о которой Забывают, не является тот же вопрос. Мой вопрос имеет дополнительный столбец. из-за этого дополнительного groupId столбца принятый ответ там не работает на мою проблему. (возможно, это может быть адаптировано к работе, но я не знаю как, следовательно мой вопрос),
Вы можете использовать переменную - это намного быстрее, чем любое соединение:
SELECT
id,
size,
@total := @total + size AS cumulativeSize,
FROM table, (SELECT @total:=0) AS t;
Вот небольшой тестовый пример на Pentium III с 128 МБ ОЗУ под управлением Debian 5.0:
Создайте таблицу:
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`id` int(11) NOT NULL auto_increment,
`size` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Заполните 20 000 случайными числами :
DELIMITER //
DROP PROCEDURE IF EXISTS autofill//
CREATE PROCEDURE autofill()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 20000 DO
INSERT INTO table1 (size) VALUES (FLOOR((RAND() * 1000)));
SET i = i + 1;
END WHILE;
END;
//
DELIMITER ;
CALL autofill();
Проверить количество строк:
SELECT COUNT(*) FROM table1;
+----------+
| COUNT(*) |
+----------+
| 20000 |
+----------+
Выполнить совокупный итоговый запрос:
SELECT
id,
size,
@total := @total + size AS cumulativeSize
FROM table1, (SELECT @total:=0) AS t;
+-------+------+----------------+
| id | size | cumulativeSize |
+-------+------+----------------+
| 1 | 226 | 226 |
| 2 | 869 | 1095 |
| 3 | 668 | 1763 |
| 4 | 733 | 2496 |
...
| 19997 | 966 | 10004741 |
| 19998 | 522 | 10005263 |
| 19999 | 713 | 10005976 |
| 20000 | 0 | 10005976 |
+-------+------+----------------+
20000 rows in set (0.07 sec)
ОБНОВЛЕНИЕ
Я пропустил группировку по groupId в исходном вопросе, и это определенно усложнило задачу. Затем я написал решение, в котором использовалась временная таблица, но она мне не понравилась - она была запутанной и слишком сложной. Я ушел, провел еще несколько исследований и придумал что-то гораздо более простое и быстрое.
Я не могу претендовать на всю заслугу в этом - на самом деле, я почти не могу требовать чего-либо вообще, так как это всего лишь модифицированная версия Эмуляция номера строки из Common MySQL Queries .
Это красиво, просто, элегантно и очень быстро:
SELECT fileInfoId, groupId, name, size, cumulativeSize
FROM (
SELECT
fileInfoId,
groupId,
name,
size,
@cs := IF(@prev_groupId = groupId, @cs+size, size) AS cumulativeSize,
@prev_groupId := groupId AS prev_groupId
FROM fileInfo, (SELECT @prev_groupId:=0, @cs:=0) AS vars
ORDER BY groupId
) AS tmp;
Вы можете удалить внешний SELECT ... AS tmp
, если вас не волнует возвращаемый столбец prev_groupID
. Я обнаружил, что без него он работал немного быстрее.
Вот простой тестовый пример:
INSERT INTO `fileInfo` VALUES
( 1, 3, 'name0', '10'),
( 5, 3, 'name1', '10'),
( 7, 3, 'name2', '10'),
( 8, 1, 'name3', '10'),
( 9, 1, 'name4', '10'),
(10, 2, 'name5', '10'),
(12, 4, 'name6', '10'),
(20, 4, 'name7', '10'),
(21, 4, 'name8', '10'),
(25, 5, 'name9', '10');
SELECT fileInfoId, groupId, name, size, cumulativeSize
FROM (
SELECT
fileInfoId,
groupId,
name,
size,
@cs := IF(@prev_groupId = groupId, @cs+size, size) AS cumulativeSize,
@prev_groupId := groupId AS prev_groupId
FROM fileInfo, (SELECT @prev_groupId := 0, @cs := 0) AS vars
ORDER BY groupId
) AS tmp;
+------------+---------+-------+------+----------------+
| fileInfoId | groupId | name | size | cumulativeSize |
+------------+---------+-------+------+----------------+
| 8 | 1 | name3 | 10 | 10 |
| 9 | 1 | name4 | 10 | 20 |
| 10 | 2 | name5 | 10 | 10 |
| 1 | 3 | name0 | 10 | 10 |
| 5 | 3 | name1 | 10 | 20 |
| 7 | 3 | name2 | 10 | 30 |
| 12 | 4 | name6 | 10 | 10 |
| 20 | 4 | name7 | 10 | 20 |
| 21 | 4 | name8 | 10 | 30 |
| 25 | 5 | name9 | 10 | 10 |
+------------+---------+-------+------+----------------+
Вот образец последних нескольких строк из таблицы из 20 000 строк:
| 19481 | 248 | 8CSLJX22RCO | 1037469 | 51270389 |
| 19486 | 248 | 1IYGJ1UVCQE | 937150 | 52207539 |
| 19817 | 248 | 3FBU3EUSE1G | 616614 | 52824153 |
| 19871 | 248 | 4N19QB7PYT | 153031 | 52977184 |
| 132 | 249 | 3NP9UGMTRTD | 828073 | 828073 |
| 275 | 249 | 86RJM39K72K | 860323 | 1688396 |
| 802 | 249 | 16Z9XADLBFI | 623030 | 2311426 |
...
| 19661 | 249 | ADZXKQUI0O3 | 837213 | 39856277 |
| 19870 | 249 | 9AVRTI3QK6I | 331342 | 40187619 |
| 19972 | 249 | 1MTAEE3LLEM | 1027714 | 41215333 |
+------------+---------+-------------+---------+----------------+
20000 rows in set (0.31 sec)
Я думаю, что MySQL использует только один из индексов таблицы. В этом случае он выбирает индекс для foreignId.
Добавьте покрывающий составной индекс, который включает как primaryId, так и foreignId.