СОЕДИНЕНИЯ стоят дорого, и, как правило, чем меньше таблиц (для одной и той же базы данных) вы присоединяете, тем выше производительность. Что касается любых правил, есть, однако, исключения
Тот, о котором я говорю, связан с проблемой прекращения работы оптимизатора MySQL с использованием дополнительных частей индекса, как только в предыдущей части ключа есть предложение диапазона. Таким образом, если у вас есть INDEX (A, B) и условие where между A 5 и 10 AND B = 6, будет использоваться только первая часть (A) индекса, что может серьезно повлиять на производительность. Конечно, в этом примере вы можете использовать индекс (B, A), но есть много похожих случаев, когда это невозможно.
Я описал пару решений этой проблемы — использование списка IN вместо диапазона или UNION, которые, однако, требуют довольно серьезных изменений приложения, а также могут привести к огромным спискам IN и неоптимальному выполнению для больших диапазонов.
Давайте рассмотрим очень типичный отчетный запрос, который запрашивает данные для диапазона дат для нескольких групп (это могут быть устройства, страницы, пользователи и т. Д.)
CREATE TABLE `info` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`d` date NOT NULL,
`group_id` int(10) UNSIGNED NOT NULL,
`events` int(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
KEY `d` (`d`,`group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=18007591 DEFAULT CHARSET=latin1
mysql> SELECT sum(events) FROM info WHERE d BETWEEN '2007-01-01' AND '2007-01-31' AND group_id IN (10,20,30,40,50,60,70,80,90,100);
+-------------+
| sum(events)
+-------------+
| 3289092
+-------------+
1 row IN SET (1.04 sec)
mysql> EXPLAIN SELECT sum(events) FROM info WHERE d BETWEEN '2007-01-01' AND '2007-01-31' AND group_id IN (10,20,30,40,50,60,70,80,90,100) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: info
type: range
possible_keys: d
KEY: d
key_len: 7
ref: NULL
rows: 355213
Extra: USING WHERE
1 row IN SET (0.00 sec)
Как вы можете видеть из EXPLAIN, этот запрос должен анализировать более 300 000 строк, что является относительно быстрым для этой (в памяти) таблицы, но станет неприемлемым, как только вы выполните произвольный дисковый ввод-вывод.
Обратите внимание, что это также интересный случай, когда EXPLAIN неверен — он показывает key_len = 7, что соответствует полному ключу, когда используется только первая часть ключа.
Давайте теперь заменим диапазон списком IN в этом запросе:
mysql> EXPLAIN SELECT sum(events) FROM info WHERE d IN('2007-01-01','2007-01-02','2007-01-03','2007-01-04','2007-01-05','2007-01-06','2007-01-07','2007-01-08','2007-01-09','2007-01-10','2007-01-11','2007-01-12','2007-01-13','2007-01-14','2007-01-15','2007-01-16','2007-01-17','2007-01-18','2007-01-19','2007-01-20','2007-01-21','2007-01-22','2007-01-23','2007-01-24','2007-01-25','2007-01-26','2007-01-27','2007-01-28','2007-01-29','2007-01-30','2007-01-31') AND group_id IN (10,20,30,40,50,60,70,80,90,100) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: info
type: range
possible_keys: d
KEY: d
key_len: 7
ref: NULL
rows: 3681
Extra: USING WHERE
1 row IN SET (0.01 sec)
mysql> SELECT sum(events) FROM info WHERE d IN('2007-01-01','2007-01-02','2007-01-03','2007-01-04','2007-01-05','2007-01-06','2007-01-07','2007-01-08','2007-01-09','2007-01-10','2007-01-11','2007-01-12','2007-01-13','2007-01-14','2007-01-15','2007-01-16','2007-01-17','2007-01-18','2007-01-19','2007-01-20','2007-01-21','2007-01-22','2007-01-23','2007-01-24','2007-01-25','2007-01-26','2007-01-27','2007-01-28','2007-01-29','2007-01-30','2007-01-31') AND group_id IN (10,20,30,40,50,60,70,80,90,100);
+-------------+
| sum(events)
+-------------+
| 3289092
+-------------+
1 row IN SET (0.02 sec)
Таким образом, мы получаем тот же результат, но примерно в 50 раз быстрее. В этом отчете у нас были данные за один месяц — что если бы у вас был год? 5 лет ? Что если вы скажете тысячи групп одновременно? Выполняя такой запрос, MySQL должен построить (и выполнить поиск) для всех комбинаций, что в данном случае составляет 31 * 10 = 310. Но если он достигает сотен тысяч, этот метод начинает ломаться (и более новые версии MySQL перестанут использовать этот метод оптимизации, если будет слишком много комбинаций для проверки).
Вместо этого вы можете использовать JOIN, чтобы получить список дней, совпадающих с диапазоном из некоторой предварительно сгенерированной таблицы, и использовать соединение, чтобы получить строки из исходной таблицы:
mysql> SHOW CREATE TABLE dl \G
*************************** 1. row ***************************
TABLE: dl
CREATE TABLE: CREATE TABLE `dl` (
`myday` date NOT NULL,
PRIMARY KEY (`myday`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row IN SET (0.00 sec)
mysql> SELECT * FROM dl LIMIT 5;
+------------+
| myday
+------------+
| 2001-01-01
| 2001-01-02
| 2001-01-03
| 2001-01-04
| 2001-01-05
+------------+
5 rows IN SET (0.00 sec)
mysql> EXPLAIN SELECT sum(events) FROM info,dl WHERE myday BETWEEN '2007-01-01' AND '2007-01-31' AND myday=d AND group_id IN (10,20,30,40,50,60,70,80,90,100) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: dl
type: range
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 3
ref: NULL
rows: 30
Extra: USING WHERE; USING INDEX
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
TABLE: info
type: range
possible_keys: d
KEY: d
key_len: 7
ref: NULL
rows: 355213
Extra: USING WHERE
2 rows IN SET (0.00 sec)
Как вы можете видеть, это не работает, хотя я знаю, что использовал именно этот трюк для оптимизации некоторых неприятных запросов.
Похоже, что здесь работает распространение равенства (обратите внимание, что число строк для второй таблицы в объединении оценивается одинаково в исходном запросе), и мы получаем предложение range в таблице «info» вместо объединения вложенных циклов — именно то, чего мы пытались избежать.
Легко заблокировать распространение равенства с помощью некоторой тривиальной функции:
mysql> EXPLAIN SELECT sum(events) FROM info,dl WHERE myday BETWEEN '2007-01-01' AND '2007-01-31' AND d=date(myday) AND group_id IN (10,20,30,40,50,60,70,80,90,100) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: dl
type: range
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 3
ref: NULL
rows: 30
Extra: USING WHERE; USING INDEX
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
TABLE: info
type: ref
possible_keys: d
KEY: d
key_len: 3
ref: func
rows: 17990
Extra: USING WHERE
2 rows IN SET (0.00 sec)
Таким образом, мы остановили распространение равенства, но теперь у нас есть другая проблема — по какой-то причине MySQL решает делать только «ref» только для даты вместо использования диапазона в день и списка групп для каждой итерации соединения.
Это не имеет смысла, но это так.
Я также попытался увеличить количество элементов, чтобы все строки имели разные group_id, но он все еще не работает.
Трюк, тем не менее, работает, если у вас есть только один group_id (и в этом случае вам даже не нужно хитрить вокруг распространения эквити, чтобы заставить его работать)
mysql> EXPLAIN SELECT sum(events) FROM info,dl WHERE myday BETWEEN '2007-01-01' AND '2007-01-31' AND d=myday AND group_id IN (10) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: dl
type: range
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 3
ref: NULL
rows: 30
Extra: USING WHERE; USING INDEX
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
TABLE: info
type: ref
possible_keys: d
KEY: d
key_len: 7
ref: test.dl.myday,const
rows: 18
Extra:
2 rows IN SET (0.00 sec)
Для оригинальной формы запроса с одним group_id запрос занимал 0,95 сек . Запрос с диапазоном BETWEEN, замененным списком IN, был мгновенным на 0,00 с таким же, как и запрос с использованием таблицы со списком дней.
Таким образом, нам, наконец, удалось повысить производительность, объединив данные в еще одну таблицу, хотя вопрос о том, почему он не работает для нескольких групп, остается вопросом для проверки с командой MySQL Optimizer.
ОБНОВЛЕНИЕ: Я только что услышал от Игоря Бабаева, что он был спроектирован таким образом (потому что первый компонент может проходить через очень много значений). Второй компонент просто не рассматривается для диапазона, если это не равенство. У вас всегда есть что узнать о хитах MySQL Optimizer
В то же время я выяснил, как заставить MySQL Optimizer делать то, что мы хотим сделать — просто добавьте еще одну таблицу в объединение, чтобы в информационной таблице было только несколько ссылок на ссылки:
mysql> SELECT * FROM g;
+-----+
| gr
+-----+
| 10
| 20
| 30
| 40
| 50
| 60
| 70
| 80
| 90
| 100
+-----+
10 rows IN SET (0.00 sec)
mysql> EXPLAIN SELECT sum(events) FROM g,info,dl WHERE myday BETWEEN '2007-01-01' AND '2007-01-31' AND myday=d AND group_id=g.gr \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: dl
type: range
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 3
ref: NULL
rows: 30
Extra: USING WHERE; USING INDEX
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
TABLE: g
type: INDEX
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 4
ref: NULL
rows: 10
Extra: USING INDEX
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
TABLE: info
type: ref
possible_keys: d
KEY: d
key_len: 7
ref: test.dl.myday,test.g.gr
rows: 18
Extra:
3 rows IN SET (0.00 sec)
Этот запрос выглядит очень страшно, но на самом деле он работает намного лучше, чем оригинальный. В реальных запросах вы можете использовать таблицу с идентификаторами так же, как у нас была таблица дней с предложением where вместо предварительно созданной таблицы.