MySQL Row Format Tuning
При создании или модифицировании таблиц используя MyISAM, вы можете запросить MySQL хранить строки в фиксированном или динамическом формате. Если таблица не содержит BLOB и TEXT полей, то фиксированный формат выбирается по умолчанию, который автоматически конвертирует VARCHAR в CHAR. Иначе, если выбрать динамический формат, то MySQL конвертирует все колонки из типа CHAR в VARCHAR.
Для MySQL, фиксированный формат легче в доступе, кешировании и обновлении информации. Также этот формат менее подвержен порче данных. Если дисковое пространство не является критическим, то фиксированный формат будет хорошим выбором.
Динамический формат использует меньше дискового пространства, но более подвержен риску фрагментации и/или порче данных.
Но давайте сначала посмотрим, на тесты и потом сделаем окончательное заключение.
Я создал 2 таблицы – одну с фиксированным форматом, вторую с динамическим и в каждую занес по 100.000 строк случайных данных.
CREATE TABLE `fixed_demo` (
`id` int(11) NOT NULL auto_increment,
`col2` varchar(255) default NULL,
`col3` char(40) default NULL,
`col4` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `col2` (`col2`),
KEY `col3` (`col3`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
CREATE TABLE `dynamic_demo` (
`id` int(11) NOT NULL auto_increment,
`col2` varchar(255) default NULL,
`col3` char(40) default NULL,
`col4` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `col2` (`col2`),
KEY `col3` (`col3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
Внести 100.000 строк в таблицу с фиксированным форматом заняло 43 секунды, тогда как в таблицу с динамическим форматом 47 секунд.
В итоге данные в фиксированном формате заняли 29 Мб, а данные в динамическом формате всего 11 мегабайт. Что интересно, их индексы заняли почти одинаковое количество дискового пространства и это составило около 14 Мб для каждой таблицы, в более поздних версиях MySQL 5 разница в размере индексов составляла почти 2 раза больше в фиксированном формате, чем в динамическом.
Далее сделаем несколько SELECT запросов и посмотрим, что они нам покажут:
SELECT COUNT(*) FROM fixed_demo WHERE col4 > 34500 AND col4 < 50990;
SELECT COUNT(*) FROM dynamic_demo WHERE col4 > 34500 AND col4 < 50990;
Разница в скорости выполниния запросов SELECT была практически незначительная и составляет не более 100 милисекунд, что можно списать на погрешность ОС.
В заключении можно сказать, что таблица с динамическим форматом предпочтительней, чем с фиксированным т.к. требует намного меньше дискового пространства, но если скорость выполнения sql запросов является главным фактором при выборе формата, то стоит предпочесть фиксированный формат динамическому.
Тестирование производилось на: MySQL 5.0.45, ОС Ubuntu Linux, 1Gb Ram, 2.4Ghz Pentium 4
Автор данной статьи не учёл, что данные могли быть закешированы как на уровне mysql так и на уровне файловой системы ОС. В этом случае формат записи действительно не будет влиять на скорость чтение. Со вставкой – тоже всё просто. Дело в том, что varchar дописывается к концу файла при вставке, поэтому нет случайного доступа до диска, поэтому тут тоже нет разницы. Правда при этом происходит переиндексация данных, но индексы имеют фиксированный размер записи (это кстати видно из того, что размер у них получился одинаковый), а значит на неё тоже будет затрачено примерно одинаковое время.
А вот где можно увидеть реальную разницу, так это на изменениях случайной записи в таблице. Если автор статьи попробует изменять много раз случайную запсь, то разница в производительности его сильно удивит. Она будет не в разы, она будет на порядки (в некоторых ситуациях).