Ответ 1
Используя пример ваших городов/блоков, ваша схема может выглядеть примерно так:
CREATE TABLE cities (
`city_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`country_id` TINYINT UNSIGNED NOT NULL,
`zip` VARCHAR(50) NOT NULL,
`name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`city_id`)
);
CREATE TABLE blocks (
`block_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
`city_id` SMALLINT UNSIGNED NOT NULL,
`p1` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
`p2` SMALLINT UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`block_id`),
FOREIGN KEY (`city_id`) REFERENCES `cities` (`city_id`)
);
Ваш запрос для данного города (city_id = 123) будет выглядеть следующим образом:
Запрос 1
SELECT AVG(p1/(p1+p2)) AS B
FROM blocks b
WHERE b.city_id = 123
Примечание: AVG(x) = SUM(x) / COUNT(x)
Теперь, если вы беспокоитесь о производительности, вы должны определить некоторые ожидаемые числа:
- Количество городов
- (Средний) Количество блоков в городе
- Оборудование, которое вы будете/можете использовать
- Запросы, которые вы обычно запускаете
- Количество запросов в час/минута/сек
Если вы определили эти числа, вы можете создать некоторые фиктивные/поддельные данные для запуска тестов производительности.
Вот пример с 1000 городами и 100K блоками (в среднем по 100 блоков в каждом городе):
Сначала создайте вспомогательную таблицу с порядковыми номерами 100K:
CREATE TABLE IF NOT EXISTS seq100k
SELECT NULL AS seq
FROM information_schema.COLUMNS c1
JOIN information_schema.COLUMNS c2
JOIN information_schema.COLUMNS c3
LIMIT 100000;
ALTER TABLE seq100k CHANGE COLUMN seq seq MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY;
С помощью MariaDB вы можете использовать плагин последовательности.
Сгенерировать данные:
DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
`city_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`country_id` TINYINT UNSIGNED NOT NULL,
`zip` VARCHAR(50) NOT NULL,
`name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`city_id`)
)
SELECT seq AS city_id
, floor(rand(1)*10+1) as country_id
, floor(rand(2)*99999+1) as zip
, rand(3) as name
FROM seq100k
LIMIT 1000;
CREATE TABLE blocks (
`block_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
`city_id` SMALLINT UNSIGNED NOT NULL,
`p1` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
`p2` SMALLINT UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`block_id`),
FOREIGN KEY (`city_id`) REFERENCES `cities` (`city_id`)
)
SELECT seq AS block_id
, floor(rand(4)*1000+1) as city_id
, floor(rand(5)*11) as p1
, floor(rand(6)*20+1) as p2
FROM seq100k
LIMIT 100000;
Теперь вы можете запускать свои запросы. Обратите внимание, что я не буду использовать точное время выполнения. Если вам нужно, чтобы они были точными, вы должны использовать профилирование.
Запуск Query 1 мой GUI (HeidiSQL) показывает 0.000 sec, который я называю "почти мгновенным".
Возможно, вы захотите выполнить запрос типа:
Запрос 2
SELECT b.city_id, AVG(p1/(p1+p2)) AS B
FROM blocks b
GROUP BY b.city_id
ORDER BY B DESC
LIMIT 10
HeidiSQL показывает 0.078 sec.
Используя индекс покрытия
ALTER TABLE `blocks`
DROP INDEX `city_id`,
ADD INDEX `city_id` (`city_id`, `p1`, `p2`);
вы можете уменьшить время выполнения до 0.031 sec. Если это не достаточно быстро, вы должны подумать о некоторых стратегиях кэширования. Одним из способов (помимо кэширования на уровне приложения) является использование триггеров для управления новым столбцом в таблице cities (просто позвоните ему B):
ALTER TABLE `cities` ADD COLUMN `B` FLOAT NULL DEFAULT NULL AFTER `name`;
Определите триггер обновления:
DROP TRIGGER IF EXISTS `blocks_after_update`;
DELIMITER //
CREATE TRIGGER `blocks_after_update` AFTER UPDATE ON `blocks` FOR EACH ROW BEGIN
if new.p1 <> old.p1 or new.p2 <> old.p2 then
update cities c
set c.B = (
select avg(p1/(p1+p2))
from blocks b
where b.city_id = new.city_id
)
where c.city_id = new.city_id;
end if;
END//
DELIMITER ;
Обновить тест:
Запрос 3
UPDATE blocks b SET p2 = p2 + 100 WHERE 1=1;
UPDATE blocks b SET p2 = p2 - 100 WHERE 1=1;
Этот запрос выполняется в 2.500 sec без триггера и 60 sec с помощью триггера. Это может показаться большим количеством накладных расходов. Но подумайте, что мы обновляем 100K строк дважды - это означает среднее значение 60K msec / 200K updates = 0.3 msec/update.
И теперь вы можете получить тот же результат от Query 2 с помощью
Запрос 4
SELECT c.city_id, c.B
FROM cities c
ORDER BY c.B DESC
LIMIT 10
"почти мгновенно" (0.000 sec).
Вы можете оптимизировать триггер, если вам нужно. Использование дополнительного столбца block_count в таблице cities (которому также необходимо управлять с помощью триггеров).
Добавить столбец:
ALTER TABLE `cities`
ADD COLUMN `block_count` MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' AFTER `B`;
Идентификационные данные:
UPDATE cities c SET c.block_count = (
SELECT COUNT(*)
FROM blocks b
WHERE b.city_id = c.city_id
)
WHERE 1=1;
Перепишите триггер:
DROP TRIGGER IF EXISTS `blocks_after_update`;
DELIMITER //
CREATE TRIGGER `blocks_after_update` AFTER UPDATE ON `blocks` FOR EACH ROW BEGIN
declare old_A, new_A double;
if new.p1 <> old.p1 or new.p2 <> old.p2 then
set old_A = old.p1/(old.p1+old.p2);
set new_A = new.p1/(new.p1+new.p2);
update cities c
set c.B = (c.B * c.block_count - old_A + new_A) / c.block_count
where c.city_id = new.city_id;
end if;
END//
DELIMITER ;
С помощью этого триггера Запрос 3 теперь работает в 8.5 sec. Это означает накладные расходы 0.03 msec за обновление.
Обратите внимание, что вам также необходимо определить триггеры INSERT и DELETE. И вам нужно будет добавить больше логики (например, для обработки изменений в city_id при обновлениях). Но также возможно, что вам вообще не понадобится спусковой крючок.