Чуть больше чем просто блог :)

Рекомендации при построении БД на MySQL

При создании архитектуры БД нужно учитывать множество нюансов, которыенеобходимы как для быстрого доступа к данным, так и к устойчивости к сбоям при длительном использовании.

В данном посте постарался собрать как свой опыт, так и опыт других блогеров.

Общее:

  1. Использовать кодировку БД UTF8
  2. На этапе проектирования соблюдайте нормальные формы.
  3. В большинстве случаев лучше использовать движок InnoDB
  4. Меньшие столбцы – быстрее
  5. Избегать запросов в цикле.
  6. Не используйте NULL столбцы кроме случаев, когда они вам осознанно нужны.Столбец должен быть объявлен как NOT NULL, если в нём действительно нет пустых ячеек — таким образом вы слегка ускорите проход по таблиц.

 

Числа:

  1. Старайтесь использовать поле id везде. Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип  из семейства INT. Предпочтительно - UNSIGNED, так как в этом случае значение не может быть отрицательным.
  2. Для хранения булевых значений, нужно использовать TINYINT(1)
  3. Деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99
  4. В случаи подсчета количества записей таблицы или суммы (COUNT или SUM, и др). подсчетов – будем использовать отдельную таблицу и обновлять данные в режиме реального времени. Это значительно сократит время, вместо того, чтобы просматривать всю таблицу целиком.
  5. Используйте для хранения IP-адресов поля типа UNSIGNED INT. Многие разработчики создают для этих целей поля типа VARCHAR (15), в то время как IP-адреса можно было бы хранить в базе в виде десятичных чисел. Поля типа INT предоставляют возможность хранить до 4 байта информации, и при этом для них можно задать фиксированный размер поля. В запросах можно использовать параметр INET_ATON () для преобразования IP-адресов в десятичные числа, и INET_NTOA () - наоборот. PHP имеет и другие аналогичные функции long2ip () и ip2long ().
    UPDATEusrsSETip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHEREuser_id = $u_id;

 

Строки:

  1. В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов
  2. Использовать необходимое количество знаков или символов в строке. Например, для хранения пароля в md5 нужно отводить ровно 32 символа, больше не имеет смысла. Если ожидается, что таблица не будет содержать слишком большое количество записей, то нет причин хранить первичный ключ в полях типа INT, возможно подойдет и MEDIUMINT, SMALLINT, а в отдельных случаях даже TINYINT. Если в формате даты вам не нужны составляющие времени (часы : минуты), то используйте поля типа DATE вместо DATETIME.
  3. Используйте тип CHAR, когда это возможно (вместо VARCHAR, BLOB или TEXT) — когда у значений столбца есть постоянная длина: хэш MD5, номер телефона, индекс и подобные данные. Данные в столбцах CHAR могут быть найдены быстрее, чем в столбцах с другими типами данных.

 

Даты:

  1. DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
  2. TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку

 

Перечисления:

  1. Для перечислений правильно использовать тип ENUM, CHAR либо TINYINT с цифрой и указанием константы в модели на стороне PHP. Я буду использовать 3й вариант. По поводу ENUM можно почитать статью «8 причин, почему тип данных ENUM в MySQL это зло» можно почитать по ссылке.

 

Использование индексов:

  1. Столбцы с высоким количеством уникальных элементов назначать как PRIMARY KEY. Например, уникальный ID пользователя содержит большое количество значений и подходит для того, чтобы стать первичным ключом.
  2. Все связи между таблицами, а так-же, столбцы которые часто используются в выражениях WHERE, ORDER BY или GROUP BY, а так-же если в таблице есть столбцы, по которым вы производите поиск, то эти столбцы необходимо делать с индексами.
  3. Не делать составной индекс из нескольких столбцов. Для этих целей лучше сделать ХЕШ столбец который будет коротким и уникальным. Запросвэтомслучаиможетбытьтакой:
    SELECT * FROM table WHERE hash_column = MD5(CONCAT(col1, col2)) AND col1='aaa' AND col2='bbb';
  4. Не следует добавлять индекс каждому столбцу. Если вы никогда не сравниваете столбец с другими данными и не проводите по нему поиск, незачем ставить на нём индекс.
  5. Один из основных параметров, характеризующий индекс — селективность(selectivity) — количество разных элементов в индексе. Нет смысла индексировать поле, в котором два-три возможных значения. Пользы от такого индекса будет мало.
  6. Отсортировать таблицу в том порядке, в котором будем чаще всего ее использовать. Например, если мы зачастую делаем вывод и сортируем данные не по ID, а по столбцам name, data..., лучше сделать запрос:
    ALTER TABLE table_name ORDER BY name, data
    Лучше как можно реже использовать ORDER BY, поскольку во многих случаях он требует временную таблицу.

Связи между таблицами:

  1. Связанные таблицы «Foreign keys» должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано INTEGER UNSIGNED DEFAULT 0 NOT NULL то и на другой должно быть указано аналогично.

SQL запросы:

  1. По возможности не используйте запросы типа SELECT *. Чем больше данных в таблице обрабатывается при запросе, тем медленнее выполняется сам запрос. Не поленитесь перечислить именно те поля, которые вы выбираете. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вы действительно выбираете все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочек невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, сегодня в вашей таблице пять INT столбцов, а через месяц добавилось еще одно TEXT и BLOB, а звездочка как была, так и осталась.
  2. Лучше не использовать ORDER BY RAND(), для получения нескольких случайных строк. Правильнее будет использовать следующий код:
    $r = mysql_query("SELECT count(*) FROM user");
    $d = mysql_fetch_row($r);
    $rand = mt_rand(0,$d[0] - 1);
    $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
    Есть и другое решение: http://plutov.by/post/order_by_rand_performance
  3. Использовать LIMIT 1 Когда нужно извлечь из таблицы уникальную строку. Иногда, формируя запрос, вы уже знаете, вам нужна только одна уникальная строка в таблице. Вы можете сформировать выборку по уникальной записи. Или вы можете просто запустить проверку на существование любого количества записей, которые удовлетворяют вашему условию. В таких случаях, использование метода LIMIT 1 может существенно увеличить производительность.
  4. У INSERT есть синтаксис для множественной вставки. Один запрос будет выполняться на порядок быстрее, чем множество запросов в цикле.
  5. Используйте INSERTON DUPLICATE KEY UPDATE… вместо выборки и INSERT или UPDATE после нее, а также часто вместо REPLACE.
  6. Разделяйте большие запросы DELETE или INSERT. Если вам нужно выполнить большой запрос DELETE или INSERT на работающем сайте, то нужно быть осторожным, чтобы не нарушить трафик. Когда выполняется большой запрос, то он может заблокировать ваши таблицы и привести к остановке приложения. Apache выполняет много параллельных процессов/потоков. по этой причине он работает более эффективно, когда скрипт заканчивает выполнение как можно быстрее, таким образом сервер не использует слишком много открытых соединений и процессов, потребляющих ресурсы, особенно память. Если вы блокируете таблицы на продолжительное время (например, на 30 и более секунд) на высоко нагруженном веб сервере, вы можете вызвать накапливание  процессов и запросов, что потребует значительного времени на расчистку или даже приведет к остановке  вашего веб сервера. Если у вас есть скрипт, который удаляет большое количество записей, просто используйте предложение LIMIT для разбиения его на маленькие партии, чтобы избежать описанной ситуации.
    while (1) {
        mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
        if (mysql_affected_rows() == 0) {
            // выполняем удаление
            break;
        }
        // вы можете сделать небольшую паузу
        usleep(50000);
    }
Другие материалы в этой категории: Типы данных столбцов и их требования к памяти в MySQL »

Оставить комментарий

Убедитесь, что вы вводите (*) необходимую информацию, где нужно
HTML-коды запрещены

Авторизация