Производительность MySQL: несколько таблиц и индекс для одной таблицы и разделов

Мне интересно, что более эффективно и быстро в производительности:
Имея индекс на одной большой таблице или несколько меньших таблиц без индексов?

Поскольку это довольно абстрактная проблема, позвольте мне сделать ее более практичной:
У меня есть одна таблица со статистикой о пользователях (20 000 пользователей и около 30 миллионов строк в целом). Таблица содержит около 10 столбцов, включая user_id, actions, timestamps и т.д.
Наиболее распространенными приложениями являются: Вставка данных с помощью user_id и извлечение данных с помощью user_id (SELECT утверждения никогда не включают несколько user_id's).

Теперь у меня есть INDEX в user_id, и запрос выглядит примерно так:

SELECT * FROM statistics WHERE user_id = 1

Теперь, когда все больше и больше строк, таблица становится медленнее и медленнее. Операторы INSERT замедляются, потому что INDEX становится все больше и больше; Операторы SELECT замедляются, ну, потому что для поиска требуется больше строк.

Теперь мне было интересно, почему бы не иметь одну таблицу статистики для каждого пользователя и изменить синтаксис запроса на что-то вроде этого:

SELECT * FROM statistics_1

где 1, очевидно, обозначает user_id.
Таким образом, не требуется INDEX, и в каждой таблице гораздо меньше данных, поэтому операторы INSERT и SELECT должны быть намного быстрее.

Теперь мои вопросы снова:
Есть ли какие-либо недостатки реального мира для обработки большого количества таблиц (в моем случае 20 000) вместо использования одной таблицы с INDEX?
Будет ли мой подход на самом деле ускорить работу или может быть поиск таблицы в конечном итоге замедлит работу больше всего?

Ответ 1

Создание 20 000 таблиц - плохая идея. Скоро вам понадобится 40 000 столов, а затем еще.

Я назвал этот синдром Metadata Tribbles в моей книге SQL Antipatterns. Вы видите, что это происходит каждый раз, когда вы планируете создавать "таблицу за X" или "столбец за X".

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

Существуют также практические операционные последствия. Вы действительно хотите создать систему, которая требует, чтобы вы создавали новую таблицу каждый раз, когда подписывается новый пользователь?

Вместо этого я бы рекомендовал использовать MySQL Partitioning.

Вот пример разбиения таблицы:

CREATE TABLE statistics (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 101;

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

Например, когда вы запускаете запрос, подобный вашему примеру, MySQL обращается к только правильному разделу, содержащему определенный user_id:

mysql> EXPLAIN PARTITIONS SELECT * FROM statistics WHERE user_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: statistics
   partitions: p1    <--- this shows it touches only one partition 
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
        Extra: Using where; Using index

Метод разделения HASH означает, что строки помещаются в раздел по модулю целочисленного ключа раздела. Это означает, что многие user_id сопоставляются с одним и тем же разделом, но каждый раздел будет иметь в среднем только 1/Nth столько строк (где N - количество разделов). И вы определяете таблицу с постоянным количеством разделов, поэтому вам не нужно ее расширять каждый раз, когда вы получаете нового пользователя.

Вы можете выбрать любое количество разделов до 1024 (или 8192 в MySQL 5.6), но некоторые люди сообщили о проблемах с производительностью, когда они идут так высоко.

Рекомендуется использовать простое число разделов. Если ваши значения user_id следуют шаблону (например, с использованием только четных чисел), использование простого количества разделов помогает распределить данные более равномерно.

Ответьте на свои вопросы в комментарии:

Как я могу определить количество резонансных номеров?

Для HASH-разбиения, если вы используете 101 раздел, как показано в примере выше, то любой раздел имеет примерно 1% ваших строк в среднем. Вы сказали, что ваша таблица статистики содержит 30 миллионов строк, поэтому, если вы используете этот раздел, у вас будет только 300 тыс. Строк на раздел. MySQL намного проще читать. Вы можете (и должны) использовать индексы, а также - каждый раздел будет иметь свой собственный индекс, и он будет только на 1% больше, чем индекс во всей нераспределенной таблице.

Итак, ответ на вопрос о том, как вы можете определить разумное количество разделов, таков: насколько велика ваша целая таблица и насколько велики вы хотите, чтобы разделы были в среднем?

Не должно ли количество разделов расти со временем? Если да: как я могу автоматизировать это?

Количество разделов не обязательно должно увеличиваться, если вы используете разделение HASH. В конце концов, у вас может быть 30 миллиардов рядов, но я обнаружил, что, когда ваш объем данных растет на порядки, это требует новой архитектуры. Если ваши данные растут настолько большими, вам, вероятно, потребуется очертить на нескольких серверах, а также разделить на несколько таблиц.

Тем не менее, вы можете переразделить таблицу с помощью ALTER TABLE:

ALTER TABLE statistics PARTITION BY HASH(user_id) PARTITIONS 401;

Это должно реструктурировать таблицу (как и большинство изменений ALTER TABLE), поэтому ожидайте, что это займет некоторое время.

Вы можете отслеживать размер данных и индексов в разделах:

SELECT table_schema, table_name, table_rows, data_length, index_length
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE partition_method IS NOT NULL;

Как и в любой таблице, вы хотите, чтобы общий размер активных индексов соответствовал вашему буферному пулу, потому что если MySQL должен обменивать части индексов в пуле буфера во время запросов SELECT и из него, производительность страдает.

Если вы используете разделение RANGE или LIST, то добавление, удаление, слияние и разделение разделов гораздо более распространены. См. http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-range-list.html

Я рекомендую вам прочитать раздел руководства по разделению, а также проверить эту приятную презентацию: Повысить производительность с разделами MySQL 5.1.

Ответ 2

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

С учетом сказанного я бы ожидал, что одна (большая) таблица с индексом улучшится в целом, потому что большинство систем СУБД сильно оптимизированы для решения конкретной ситуации поиска и вставки данных в большие таблицы. Если вы попытаетесь сделать много маленьких столов в надежде на повышение производительности, вы добьетесь борьбы с оптимизатором (что обычно лучше).

Кроме того, имейте в виду, что одна таблица, вероятно, более практична для будущего. Что делать, если вы хотите получить статистическую статистику по всем пользователям? Наличие 20 000 таблиц сделало бы это очень трудным и неэффективным. Стоит также учитывать гибкость этих схем. Если вы разделите свои таблицы таким образом, вы можете создать себя в углу на будущее.

Ответ 3

В ответ Билла Карвинса немного добавить. Но один из намеков: проверьте, нужны ли все данные для пользователя в полной мере за все время.

Если вы хотите указать статистику использования или количество посещений или что-то подобное, вы обычно получаете не единичные действия и секунды, например, за 2009 год из сегодняшнего представления. Таким образом, вы можете создавать таблицы агрегации и таблицу архивов (а не архив движка, конечно), чтобы иметь последние данные о базе действий и обзор более старых действий.

Старые действия не меняются, я думаю.

И вы по-прежнему можете подробно остановиться на агрегации с помощью файла week_id в таблице архива.

Ответ 4

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

Вы также можете сохранить статистику для пользователей, чтобы попытаться переместить "активных" пользователей в 1 таблицу, чтобы уменьшить количество таблиц, к которым вы должны иметь доступ со временем.

Суть в том, что вы можете многое сделать, но в основном вы должны создавать прототипы и тесты и просто оценивать влияние различных изменений, которые вы делаете.