Пара граблей в MySQL, на которые часто любят наступать при разработке CMS

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


CREATE TABLE `test`.`baner_stat` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`url` VARCHAR(255) NOT NULL DEFAULT '',
`baner_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(`id`)
)
ENGINE = InnoDB;

Очевидно, что поле url в данной таблице используется для идентификации страницы, на которой был показан банер. Например будет 10000 показов банера на странице /the_most_popular_chapter/very_delicious_page_in_most_popular_chapter.html
Тогда url этой страницы будет записан 10000 раз в это поле.
При создании отчетов по статистике, с группировкой по странице просмотра, такой запрос будет выполнятся крайне долго. Однако, если вы хотите еще более ухудшить ситуацию, тогда следует создать индекс по полю url.
При каждом просмотре банера, при такой организации хранения данных, будет создаваться команда на вставку строки, следовательно будет происходить построение индекса и вставка замедлится в разы. Очевидно, это плохая идея.
В данном случае есть несколько вариантов решения. Если нас интересуют только просмотры и гибкая сатистика нам не нужна, то следует добавить в эту таблицу целочисленное поле счетчика просмотров. Тогда нам будет необходимо сделать два запроса к базе - 1-й нахождение ID строки по URL (здесь целесообразно использовать индекс), если мы нашли ID, то делаем UPDATE данной таблицы, увеличивая счетчик просмотров на единицу, в противном случае - вставляем новую строку с новым URL.
2-й вариант сводится к разнесению данных на две таблицы - словарную и регистрационную. На мой взгляд, данный вариант более красивый и логичный. Словарная талица:


CREATE TABLE `test`.`baner_url` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`url` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY(`id`),
INDEX `Index`(`url`)
)
ENGINE = InnoDB;


и таблица регистрации просмотров:


CREATE TABLE `test`.`baner_stat` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`url_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
`baner_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(`id`)
)
ENGINE = InnoDB;


Далее, регистрация просмотра банера сопровождается хитрым запросом:


INSERT INTO `test`.`baner_stat` (`url_id`, `baner_id`)
SELECT `id`, константа_id_банера FROM baner_url b
WHERE url='константа_URL_страницы'


Потом, в коде PHP, следует проверить количество вставленных строк (для этого есть оператор mysql_affected_rows), а если вдруг оно равно нулю, то вам следует обновить словарь:


INSERT INTO `test`.`baner_url` (`url`) VALUES ('константа_URL_страницы')


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

Во второй части статьи будет рассмотрена частая ошибка организации счетчика просмотров статей.