Ответ 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.