MySql — добавление столбцов для выбора снижает производительность
У меня есть таблица с несколькими миллионами строк данных. У меня есть первичный ключ для id и составной уникальный ключ для col2, col3, col4 и my_date (называемый comp_indx). Пример данных показан здесь...
id col2 col3 col4 my_date col5 col6 col7
1 1 1 1 2020-01-03 02:00:00 a 1 a
2 1 2 1 2020-01-03 01:00:00 b 2 1
3 1 3 1 2020-01-03 03:00:00 c 3 b
4 2 1 1 2020-02-03 01:00:00 d 4 2
5 2 2 1 2020-02-03 02:00:00 e 5 c
6 2 3 1 2020-02-03 03:00:00 f 6 3
7 3 1 1 2020-03-03 03:00:00 g 7 d
8 3 2 1 2020-03-03 02:00:00 h 8 4
9 3 3 1 2020-03-03 01:00:00 i 9 e
Если я выполню следующий запрос...
SELECT col2, col3, max(my_date)
FROM table
where col4=1 and my_date <= '2001-01-27'
group by col2, col3
...запрос очень эффективен, и выполнение команды объяснения показывает...
select_type type key key_len rows Extra
----------- ----- --------- ------- ---- -------------------------------------
SIMPLE range comp_indx 11 669 Using where; Using index for group-by
Однако, если я запускаю аналогичную команду (только запрашивая больше столбцов, ни один из которых не является частью индекса), например...
SELECT col2, col3, max(my_date), col5, col7
FROM table
where col4=1 and my_date <= '2001-01-27'
group by col2, col3
... затем производительность сразу падает, и если я снова запускаю команду объяснения, я получаю...
select_type type key key_len rows Extra
----------- ----- --------- ------- ------- -----------
SIMPLE index comp_indx 11 5004953 Using where
Я вижу, что тип изменился с диапазона на индекс, и я вижу, что индекс больше не используется для группировки.
Я пытаюсь понять, почему это происходит, и, что более важно, как я могу решить эту проблему?
Кстати, определение таблицы...
CREATE TABLE `my_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col2` smallint(6) NOT NULL,
`col3` smallint(6) NOT NULL,
`col4` smallint(6) NOT NULL,
`my_date` datetime NOT NULL,
`col5` char(1) NOT NULL,
`col6` char(1) NOT NULL,
`col7` char(1) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `comp_indx` (`col2`,`col3`,`col4`,`my_date`)
) ENGINE=InnoDB;
Ответы 5
Теперь я исправил свою проблему с производительностью, используя 2 выбора в одной таблице и соединение, например...
SELECT *
FROM (
SELECT col2, col3, max(my_date) as max_date
FROM table
where col4=1 and my_date <= '2001-01-27'
group by col2, col3
) aaa
join
(
SELECT col2, col3, my_date, col5, col6, col7
FROM table
where col4=1
) bbb
on (aaa.col2=bbb.col2 and aaa.col3=bbb.col3 and aaa.max_date=bbb.my_date);
Вероятно, вам нужно добавить этот покрывающий индекс, чтобы ускорить второй запрос:
create index comp2_index on my_table(col2, col3, col4, my_date, col5, col7);
Причина, по которой я не добавил покрывающий индекс, заключается в том, что будут случаи, когда я захочу выбрать разные комбинации дополнительных столбцов, например. col5+col7, col6+col7 и т.д. Я был просто удивлен, что это оказало влияние, и это та часть, которую я пытаюсь понять.
Добавьте следующий индекс
alter table my_table add key cl4_dt_cl2_cl3 (col4,my_date,col2,col3);
Более того, следующий запрос недействителен, если включен sql_mode only_full_group_by
SELECT col2, col3, max(my_date), col5, col7
FROM table
where col4=1 and my_date <= '2001-01-27'
group by col2, col3
Спасибо, я даже не знал о «sql_mode only_full_group_by». Я взгляну.
Ваш исходный запрос:
SELECT col2, col3, max(my_date), col5, col7
FROM table
where col4=1 and my_date <= '2001-01-27'
group by col2, col3
col5, col7 тоже нужно добавить в предложение group by, верно?
Если вам ни для чего не нужен id, то это ускорит запрос, независимо от дополнительных столбцов (col5/6/7), которые вам нужно получить.
CREATE TABLE `my_table` (
`col2` smallint(6) NOT NULL,
`col3` smallint(6) NOT NULL,
`col4` smallint(6) NOT NULL,
`my_date` datetime NOT NULL,
`col5` char(1) NOT NULL,
`col6` char(1) NOT NULL,
`col7` char(1) NOT NULL,
PRIMARY KEY (col4,my_date,col2,col3) -- in this order
) ENGINE=InnoDB;
Если вам нужно id из-за того, что на него ссылаются из других таблиц, добавьте
`id` int(11) NOT NULL AUTO_INCREMENT,
INDEX(id) -- This is sufficient to keep auto_inc happy
Мой рекомендуемый PK составляет 11 байтов (по сравнению с 4-байтовым INT). Любой вторичный будет включать эти 11 байтов. Однако любые столбцы, общие для PK и вторичного индекса, повторяться не будут. Например, INDEX(col2, col7) будет эффективно INDEX(col2, col7, col4, my_date, col3).
Имейте в виду, что ПК определяет «локальность ссылки» строк. Любой вторичный индекс, начинающийся с col4, будет почти бесполезен, поскольку с него начинается ПК. (Это, конечно, зависит от кардинальности и т.д. и т.п.)
Я пытался сделать первичный ключ как можно меньше, так как позже мне может понадобиться добавить дополнительные индексы. Кроме того, знаете ли вы, есть ли логическая причина возникновения проблемы, или это просто предположение MySQL?
@Wannabe-Coder - я добавил обсуждение этого вопроса. Не могли бы вы сказать, сколько строк будет в таблице? И какая семантика у столбцов? И другие вопросы, которые могут у вас возникнуть?
Спасибо за дополнительное разъяснение. В настоящее время есть 5 миллионов строк, но скоро будет 25 миллионов строк, а затем в какой-то момент их будет намного больше (цифра, которую мне дали, может быть 1B+!!). Пока не уверен насчет столбцов/запросов, так как все это все еще висит в воздухе и меняется ежедневно! Итак, если я правильно понял, вы говорите, что если я создам другой индекс, который использует некоторые столбцы, которые уже находятся в первичном ключе, тогда столбцы будут объединены, а не дублированы?
На самом деле будет несколько новых строковых столбцов, для которых также потребуется составной индекс, если это поможет?
Я думаю, что "да" - это ответ на ваши комментарии. Каждый индекс, включая PK, обеспечивает эффективный способ выполнения определенных запросов. Я принимаю каждый запрос, разрабатываю оптимальный индекс, устраняю дубликаты, иду на компромиссы, чтобы их не было слишком много. Это нормально, когда столбцы отображаются в разных позициях. Повторяю, ПК — это один из задействованных индексов.
И бывают случаи, когда искусственный идентификатор замедляет некоторые запросы. Я не могу сказать, относится ли этот комментарий к вашему случаю. 25 миллионов строк, даже с индексами, — это всего несколько ГБ, что сегодня не очень много.
Можете ли вы отредактировать свой вопрос и пометить СУБД. Можете ли вы также поделиться своим определением таблицы?