Пример оптимизации структуры таблицы
Давно у нас не было статей по оптимизации MySQL. Сегодня мы поговорим об оптимизации структуры самой таблицы. Многие новички не особо задумываются при создании таблиц, и часто используют неоптимальные типы данных. Думаю, мы с вами еще поговорим об оптимальном сохранении данных в MySQL, ну а сегодня будет живой пример.
Итак, начнем.
В 2009 году я начал собирать статистику в одной браузерной игре. Тогда я не особо заботился об оптимизации, поэтому все целочисленные данные были сохранены в полях типа INT. Причем я наивно полагал, что INT(5) — сам ограничит размер поля до 5 символов, и, соответственно, занимаемый размер уменьшится. Сейчас самому очень смешно.
В 2009 году структура таблицы была такой:
CREATE TABLE `s_302clan_old_null` ( `id` int(7) NOT NULL auto_increment, `pl_id` int(11) default NULL, `pl_name` varchar(20) default NULL, `lastupd` int(11) default NULL, `lvl` int(11) default NULL, `exp` bigint(20) default NULL, `rulbet` int(11) default NULL, `rulwin` int(11) default NULL, `bat` int(11) default NULL, `batw` int(11) default NULL, `batl` int(11) default NULL, `r1` float default NULL, `r2` float default NULL, `r3` float default NULL, `r4` float default NULL, `r5` float default NULL, `r6` float default NULL, `r7` float default NULL, `g1` float default NULL, `g2` int(11) default NULL, `g3` int(11) default NULL, `g4` int(11) default NULL, `g5` int(11) default NULL, `g6` float default NULL, `g7` int(11) default NULL, `g8` int(11) default NULL, `g9` int(11) default NULL, `status` int(11) default NULL, `gt` float NOT NULL default '0', `r8` float NOT NULL default '0', PRIMARY KEY (`id`), KEY `lastupd` (`lastupd`) ) ENGINE=MyISAM AUTO_INCREMENT=136439 DEFAULT CHARSET=utf8 |
В таблицу раз в сутки сохраняются данные по каждом игроку (pl_id). Заносится информация по всем существующим параметрам персонажа.
Какие здесь есть возможности оптимизации:
Вместо lastupd, в который сохраняется timestamp добавления, можно было бы использовать DATE (экономия с 4 до 3 байт);
Имя персонажа меняется очень редко, поэтому поле pl_name можно вынести в справочную таблицу;
default NULL — стоит заменить на NOT NULL и установить значение по умолчанию. При реализации задачи мы выяснили, что в каждом поле хранится какой-то параметр игрока, который равен определённому значению. Значение NULL просто не может возникнуть при нормальном выполнении программы. Исключительные ситуации должны обрабатываться в коде. Наличие NULL — плохо сказывается на скорости выборки и размере таблицы;
Возможно, стоит удалить поле id и первичный ключ заменить на составной(pl_id, date_add);
Везде, где только возможно, следует заменить тип INT на более экономичные типы: TINYINT, SMALLINT, MEDIUMINT.
Поля с типом FLOAT допустимы, если не важна большая точность после запятой. К тому же, обычно тип FLOAT более экономичен — всего 4 байта. Предположим, нужно сохранить число с 3 знаками после запятой и 5 знаками до запятой: DECIMAL(8,3) — 5 байт;
Проведем исследование.
Размер начальной таблицы: 17405.9 Кб
Этап 1. Избавимся от NULL, зададим значения по умолчанию.
CREATE TABLE `s_302clan_old_not_null` ( `id` int(7) NOT NULL auto_increment, `pl_id` int(11) NOT NULL default '0', `pl_name` varchar(20) NOT NULL default '0', `lastupd` int(11) NOT NULL default '0', `lvl` int(11) NOT NULL default '0', `exp` bigint(20) NOT NULL default '0', `rulbet` int(11) NOT NULL default '0', `rulwin` int(11) NOT NULL default '0', `bat` int(11) NOT NULL default '0', `batw` int(11) NOT NULL default '0', `batl` int(11) NOT NULL default '0', `r1` float NOT NULL default '0', `r2` float NOT NULL default '0', `r3` float NOT NULL default '0', `r4` float NOT NULL default '0', `r5` float NOT NULL default '0', `r6` float NOT NULL default '0', `r7` float NOT NULL default '0', `g1` float NOT NULL default '0', `g2` int(11) NOT NULL default '0', `g3` int(11) NOT NULL default '0', `g4` int(11) NOT NULL default '0', `g5` int(11) NOT NULL default '0', `g6` float NOT NULL default '0', `g7` int(11) NOT NULL default '0', `g8` int(11) NOT NULL default '0', `g9` int(11) NOT NULL default '0', `status` int(11) NOT NULL default '0', `gt` float NOT NULL default '0', `r8` float NOT NULL default '0', PRIMARY KEY (`id`), KEY `lastupd` (`lastupd`) ) ENGINE=MyISAM AUTO_INCREMENT=136439 DEFAULT CHARSET=utf8 |
Размер таблицы: 16722.9 Кб
Получили выигрыш в размере на 4%. Эта оптимизация возможна из-за того, что MySQL хранит специальный флаг для каждого поля с разрешенным NULL.
Этап 2. Оптимизируем хранение целочисленных данных.
CREATE TABLE `s_302clan_old_optimize` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `pl_id` mediumint(8) unsigned NOT NULL default '0', `pl_name` varchar(20) NOT NULL default '0', `lastupd` int(11) unsigned NOT NULL default '0', `lvl` tinyint(3) unsigned NOT NULL default '0', `exp` bigint(20) unsigned NOT NULL default '0', `rulbet` int(11) unsigned NOT NULL default '0', `rulwin` int(11) unsigned NOT NULL default '0', `bat` mediumint(8) unsigned NOT NULL default '0', `batw` mediumint(8) unsigned NOT NULL default '0', `batl` mediumint(8) unsigned NOT NULL default '0', `r1` float unsigned NOT NULL default '0', `r2` float unsigned NOT NULL default '0', `r3` float unsigned NOT NULL default '0', `r4` float unsigned NOT NULL default '0', `r5` float unsigned NOT NULL default '0', `r6` float unsigned NOT NULL default '0', `r7` float unsigned NOT NULL default '0', `g1` float unsigned NOT NULL default '0', `g2` mediumint(8) unsigned NOT NULL default '0', `g3` mediumint(8) unsigned NOT NULL default '0', `g4` mediumint(8) unsigned NOT NULL default '0', `g5` mediumint(8) unsigned NOT NULL default '0', `g6` float unsigned NOT NULL default '0', `g7` mediumint(8) unsigned NOT NULL default '0', `g8` mediumint(8) unsigned NOT NULL default '0', `g9` mediumint(8) unsigned NOT NULL default '0', `status` int(11) unsigned NOT NULL default '0', `gt` float unsigned NOT NULL default '0', `r8` float unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `lastupd` (`lastupd`) ) ENGINE=MyISAM AUTO_INCREMENT=136439 DEFAULT CHARSET=utf8 |
Размер таблицы: 14869.7
Оптимизация 17%.
Уменьшение размера каждого ряда и размерности индекса благоприятно сказываются на производительности выборок.
Таких таблиц у меня было около 40 штук. В результате оптимизации каждой общий размер таблиц уменьшился с 1060Мб до 920Мб.
Удачной оптимизации!