Будет ли DATE в первичном ключе повысить производительность при сканировании диапазона?

У нас есть большая (и постоянно растущая) база данных MySQL записей о птицах. В настоящее время у нас есть три основные таблицы, которые в упрощенном виде выглядят следующим образом:

RECORDS (id, birdid, tripid, gender, age) PRIMARY KEY id
BIRDTRIPS (id, locid, date, starttime, endtime, notes) PRIMARY KEY id
LOCATIONS (id, description, centerlat, centerlng) PRIMARY KEY id

Все идентификаторы имеют тип INT, а дата - тип DATE.

Для повышения производительности я решил сделать некоторую денормализацию, реплицируя дату и локид в ЗАПИСИ:

RECORDS (id, birdid, tripid, gender, age, locid, date) PRIMARY KEY id

Таким образом, многие запросы позволят избежать дорогостоящих соединений с BIRDTRIPS и LOCATIONS.

MySQL имеет только один кластеризованный индекс для каждой таблицы, и это всегда первичный ключ. Я хочу попробовать PRIMARY KEY (date, id) на RECORDS, чтобы использовать кластеризованный индекс для более быстрого сканирования диапазона в столбце даты и разделения таблицы. Идентификатор включается в ключ только потому, что на одну дату может быть много записей. Теоретически первичный ключ часто «тратится впустую» для идентификатора, где некластеризованный индекс UNIQUE будет достаточным для поиска.

А теперь мой вопрос:

Есть ли у кого-нибудь из вас практический опыт использования этого подхода? Есть ли недостатки, которые я мог упустить?


mysql sql clustered-index non-clustered-index
person Thomas Stjernegaard Jeppesen    schedule 04.08.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я отвечу на свой вопрос, если кто-нибудь наткнется на него. Я нашел это очень хорошее сообщение в блоге, где производительность была проверена для этого подхода: Кластерное индексирование и производительность запросов

Выводы экспериментов, проведенных в блоге:

ЗА:

  1. Использование составного первичного ключа, в моем случае это PRIMARY KEY (date, id), ускоряет запросы диапазона дат примерно в девять раз.

  2. Производительность запросов диапазона дат будет оставаться близкой к постоянной по мере роста таблицы, тогда как она будет постепенно ухудшаться, если первичный ключ находится только на «id».

МИНУСЫ:

  1. Простой поиск в столбце id (например, соединения в таблице) будет почти в три раза медленнее с составным индексом.

  2. Больший кластерный индекс приводит к заметно большему использованию диска. Это связано с тем, что все остальные индексы указывают на кластерный индекс, что означает, что большой кластерный индекс увеличивает размер всех других индексов. Это, вероятно, приведет к меньшему количеству попаданий в кеш для запросов, поскольку меньшая часть таблицы может поместиться в памяти.

Так что, если основная часть запросов к таблице - это критически важные для бизнеса запросы диапазона дат и ваша система может позволить себе штраф за поиск, этот подход может вам помочь.

Мой собственный вывод состоит в том, что я не решаюсь делать это в продакшене. Вместо этого я сделаю разбиение таблицы на ГОД столбца даты, чтобы предотвратить полное сканирование таблицы в запросах диапазона дат.

Надеюсь, это поможет кому-то, кто сталкивается с подобными проблемами.

person Thomas Stjernegaard Jeppesen    schedule 05.08.2014