Одна из таблиц моей базы данных стала довольно большой, и я думаю, что это влияет на производительность моего сайта (это определенно делает резервное копирование намного медленнее).
Он имеет ~ 13 000 000 строк и имеет размер 4,2 ГБ, из которых 1,2 ГБ - это данные.
Структура выглядит следующим образом:
CREATE TABLE IF NOT EXISTS `t1` (
`id` int(10) unsigned NOT NULL,
`int2` int(10) unsigned NOT NULL,
`int3` int(10) unsigned NOT NULL,
`int4` int(10) unsigned NOT NULL,
`char1` varchar(255) NOT NULL,
`int5` int(10) NOT NULL,
`char2` varchar(1024) DEFAULT NULL,
`char3` varchar(1024) NOT NULL,
PRIMARY KEY (`id`,`int2`,`int3`,`int4`),
KEY `key1` (`id`,`int2`,`char1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Обычными операциями в этой таблице являются вставка и выборка, строки никогда не обновляются и редко удаляются. int2 - номер текущей версии, что означает, что обычно выбираются только строки с самым высоким значением int2 для этого идентификатора.
Я думал о нескольких способах оптимизации этого, и мне было интересно, какой из них будет тем, который следует преследовать:
- char1 (который находится в индексе) на самом деле содержит только около 40 000 различных строк. Я мог бы переместить строки во вторую таблицу (idchar -> char), а затем просто сохранить идентификатор в моей основной таблице за счет дополнительного шага поиска идентификатора во время вставки и выбора.
- char2 и char3 часто пусты. Я мог бы переместить их в отдельную таблицу, а затем выполнить LEFT JOIN при выборе.
- Даже если char2 и char3 содержат данные, они обычно короче 1024 символов. Я мог бы, вероятно, сократить их до ~ 200.
Какой из них вы считаете наиболее перспективным? Действительно ли уменьшение размера строки (либо путем преобразования char1 в целое число, либо путем удаления/изменения размера столбцов) в таблицах MySQL InnoDB оказывает большое влияние на производительность?
Спасибо
Ответ 1 · рейтинг 2 · Gordon Linoff
Есть несколько вариантов. Судя по тому, что вы говорите, перемещение char1 на другой стол кажется вполне разумным. Дополнительный поиск может при некоторых обстоятельствах быть даже быстрее, чем сохранение необработанных данных в таблицах. (Это происходит, когда повторяющиеся значения приводят к тому, что таблица становится больше, чем необходимо, особенно когда большая таблица может быть больше, чем доступная память.) И это сэкономит место как в таблице данных, так и в соответствующем индексе.
Точное влияние на производительность трудно сказать, не зная больше о вашей системе и нагрузке запросов.
Перемещение char3 и char4 в другую таблицу окажет минимальное влияние. Накладные расходы на ссылку на другую таблицу съели бы любой выигрыш в пространстве. Вы можете сэкономить пару байтов на запись, сохранив их как varchar(255), а не varchar(1024).
Если у вас есть естественный ключ разбиения, то разбиение на разделы определенно подходит, особенно для сокращения времени резервного копирования. Это очень удобно для таблицы в стиле транзакций, в которой записи вставляются и никогда или очень редко изменяются. Если, с другой стороны, записи содержат записи о клиентах, и любые из них могут быть изменены в любое время, вам все равно потребуется создать резервную копию всех разделов.
Ответ 2 · рейтинг 1 · ericpap
Есть несколько факторов, которые могут повлиять на производительность вашей БД. Разбиение на разделы, безусловно, лучший вариант, но не всегда можно сделать. Если вы ищете char1 перед вставкой, то разделение может быть проблемой, потому что вам нужно искать ключ во всех частях. Вы должны проанализировать, как генерируются данные и, что наиболее важно, как вы делаете запросы для этой таблицы. Это ключ, поэтому вы должны публиковать свои запросы в этой таблице. В случае с char2 и char3 переход на другую таблицу не будет иметь никакого значения. Вы также должны упомянуть о физическом распределении ваших данных. Вы используете один файл данных? Находятся ли файлы данных на том же физическом диске, что и SO? Дайте больше деталей, чтобы мы могли оказать вам больше помощи.
