10 простых советов по оптимизации MySQL.

Большая часть советов на тему оптимизации производительности сервера баз данных MySQL даются в расчете на то что читающий статью является администратором компьютера с правами на редактирование конфига, запуск/остановку сервера и т.п. Но для тех вебмастеров и владельцев сайтов, кто еще не успел обзавестись собственным VPS или даже дедиком и размещают свои сайты на виртуальных хостингах, такие советы имеют нулевую полезность. Поэтому в этой статье я постарался собрать только те советы по поводу увеличения быстродействия MySQL, которые реально выполнить имея доступ только к своему сайту.

Утилиты измерения производительности

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

Популярными утилитами, которые вам помогут, будут SuperSmack, AB (из утилит Apache), SysBench, DBench, TioBench – вам стоит на них посмотреть и выбрать наиболее подходящую (я уже давно собираюсь сделать обзор подобных утилит – напишите в комментарии если ваша любимая программа не попала в этот список.).

Получение “внутренней” информации о работе сервера

При помощи вышеперечисленных утилит вы сможете генерировать тысячи запросов к вашему сайту, создавая для него высокую нагрузку. Теперь остается определить узкие места в производительности. На что именно ваше приложение тратит больше всего времени? Мощность процессора, наличие свободной оперативной памяти, производительность операций файлового ввода-вывода – все это и многое другое влияют на скорость вашего скрипта, но в этой статье речь пойдет только о сервере MySQL.

Во-первых, вам нужно включить запись в лог всех медленных запросов к MySQL и установить программу mtop (работает аналогично юниксовой программе top, но отображает не список процессов в системе, а список выполняющихся запросов к БД). Если ваша производительность старадает от неэффективных запросов, которые выполняются по 10 секунд, то таким образом вы сможете их обнаружить.

После того как будут найдены самые медленные запросы настает черед внутренних утилит MySQL, таких как команды EXPLAIN, SHOW STATUS, SHOW PROCESSLIST. Это даст ответ на вопросы – какие ресурсы тратятся, где и к каким побочным эффектам это приводит. Кроме того пригодятся такие команды как top, procinfo, vmstat, которые позволяют получить более широкую картину происходящего на сервере.

3. Проектирование базы данных

Проектирование базы данных должно выполняться еще до того как вы начнете составлять первые запросы! И будьте реалистичнее выбирая типы данных для полей таблиц. Например, если вы не рассчитываете что каждый пользователь Интернета зарегистрируется на вашем сайте несколько триллионов раз, то незачем выбирать тип BIGINT для поля id зарегистрированного пользователя.

Помните, что размер таблицы определяется (примерно) как количество записей размер_одной_записи * количество_записей_в_таблице. Старайтесь не увеличивать размер записи без необходимости. В тех случаях, когда данные имеют фиксированный размер (серия и номер паспорта, налоговый номер) лучше отказаться от типа VARCHAR.

Некоторые вебмастера не хотят делать нормализацию для таблиц, объясняя это тем что в результате получаются слишком сложные схемы данных. Но правильная нормализация позволяет уменьшит размер базы данных за счет избавления от избыточных данных. Что в результате, к сожалению, приводит к уменьшению производительности. Поэтому лучшим подходом будет сначала выполнить нормализацию, и потом “денормализовать” отдельные части, там где можно будет достичь наибольшего выигрыша в производительности.

4. Разделяйте таблицы на несколько частей

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

1

2

3

4

5

6

7

8

9

10

11

12

CREATE TABLE posts (

 id int UNSIGNED NOT NULL AUTO_INCREMENT,

 author int UNSIGNED NOT NULL,

 posted timestamp NOT NULL,

 PRIMARY KEY(id)

);

 

CREATE TABLE posts_content (

 post int UNSIGNED NOT NULL.

 content text,

 PRIMARY KEY(post)

);

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

5. Избегайте создания лишних первичных ключей.

С одной стороны, искуственные первичные ключи полезны потому что делают базу данных более устойчивой. С другой стороны в большинстве случаев достаточно одних только обычных ключей. Посмотрем на пример таблицы, которая имеет отношение многие-ко-многим: Так лучше не делать!

1

2

3

4

5

6

7

CREATE TABLE posts_tags (

 relation_id int UNSIGNED NOT NULL AUTO_INCREMENT,

 post_id int UNSIGNED NOT NULL,

 tag_id int UNSIGNED NOT NULL,

 PRIMARY KEY(relation_id),

 UNIQUE INDEX(post_id, tag_id)

);

Лучше было бы сделать так:

1

2

3

4

5

REATE TABLE posts_tags (

 post_id int UNSIGNED NOT NULL,

 tag_id int UNSIGNED NOT NULL,

 PRIMARY KEY(post_id, tag_id)

);