Основные рекомендации по использования СУБД MySQL

Наличие флага NULL, увеличивает объем данных при хранении на диске. И нагружает индексы. Определяйте типы полей как NOT NULL.

IP- адреса лучше всего хранить как UNSIGNED INT и использовать MySQL-функции INET_ATON() и INET_NTOA().

VARCHAR имеет переменную длину в файле. Занимает столько места сколько записано данных. При UPDATE если данных стало больше - выделяется место в другом месте файла. Это создает дополнительную нагрузку и является причиной фрагментации. Используйте VARCHAR если данные обновляются редко, либо используется сложная кодировка, например UTF-8. Идеально для хранения неизменяемых данных.

CHAR имеет фиксированную длину в файле. Фрагментация ему не страшна. CHAR полезен когда нужно хранить короткие строки приблизительно одинаковой длины. Частое обновление не ведет к фрагментации. Старайтесь использовать CHAR, сортировка по такому полю производится быстрее.

Используйте TIMESTAMP, он занимает на диске меньше места.

Не используйте JOIN между CHAR/VARCHAR и ENUM.

Применять значения по умолчанию (DEFAULT). При вызове запроса INSERT в таблицу будут записываться только те поля, значения которых отличаются от DEFAULT.

Используйте настолько малые типы INT, насколько это возможно.

Если у вас нет записей с переменной длиной (ни одного поля с типом VARCHAR, BLOB или TEXT), то таблица сохраняется в формате

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

При использовании нескольких последовательных INSERT запросов, лучше все данные указать в одном INSERT, чем делать несколько INSERT.

При загрузке данных в таблицу лучше использовать LOAD DATA INFILE, чем INSERT, такой метод в 20 раз быстрее.

Если ожидается много запросов INSERT или UPDATE, работающих одновременно, то для большей скорости рекомендуется приметь LOCK TABLES.

Время от времени нужно дефрагметировать таблицы. Это делается утилитой isamchk с опциями - evi.

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

В условиях WHERE задавайте вместо функций переменные и/или константы.

Используйте EXPLAIN для ваших запросов SELECT.

Используйте LIMIT 1, если нужно получить уникальную строку в запросе SELECT.

Не используйте ORDER BY RAND().

Старайтесь не использовать SELECT *.

Рекомендуется использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно – UNSIGNED. Даже в вашей таблице есть поле с уникальным именем пользователя, не делайте его основным ключом. Поля с типом VARCHAR медленно работают в качестве основных ключей. Также структура вашей базы данных будет лучше, если в ней внутри использовать ссылки на записи на основании id.

Используйте ENUM вместо VARCHAR. Столбцы типа ENUM очень компактные и быстрые. Они хранятся в базе данных как и TINYINT, но еще они могут содержать строчные значения.

Изучите предложения PROCEDURE ANALYSE(). Она позволяет MySQL анализировать структуру столбцов и действительных данных в вашей таблице и на основании анализа выдавать "предложения". Это действует только если в вашей таблице есть реальные данные, так как их наличие играет существенную роль при принятии решений.

Выбирайте правильный механизм хранения данных. Есть два основных механизма хранения

1) MyISAM отлично подходит для приложений с большой нагрузкой по чтению, но он не очень хорошо масштабируется при наличии большого количества записей - вся таблица будет заблокирована и ни один процесс не сможет ничего прочитать пока запрос не завершится. MyISAM быстро выполняет вычисления для запросов типа SELECT COUNT(*).

2) InnoDB является более сложным механизмом хранения данных, и он может быть более медленным, чем MyISAM для большинства маленьких приложений. Но он поддерживает блокирование строк, что лучше для масштабирования таблиц. Также он поддерживает некоторые дополнительные особенности, такие как транзакции.

Не рекомендуется использовать без консультации с системным администратором функции постоянного соединения mysql_pconnect().

Если запрос оставляется вручную, то все числовые параметры должны быть приведены к нужному типу, а все остальные параметры должны быть обработаны функцией mysql_real_escape_string() и заключены в кавычки или в апосторофы и в них должны быть экранированы слешами спецсимволы.

Пример. $z=”SELECT * FROM table WHERE name = 'Д\'Артаньян'”

Распространенной ошибкой является применение фунции stripslashes при получении данных из базы.

Однако для простоты и надёжности можно и с числами работать, как со строками (проскольку mysql всё равно преобразует их к нужному типу). Таким образом, любые данные, вставляемые в запрос, рекомендуется прослешивать и заключать в кавычки.

Имена полей и таблиц рекомендуется заключать в обратные одинарные кавычки - "`" (клавиша с этим символом находится на стандартной клавиатуре слева от клавиши "1") Ведь имя поля может совпадать с ключевыми словами mysql, но если мы используем обратную кавычку, то MySQL поймёт всё правильно.

Пример. SELECT * FROM `table` WHERE `date` = '2006-04-04'

Обратные кавычки слешами не экранируются.

Наличие флага NULL, увеличивает объем данных при хранении на диске. И нагружает индексы. Определяйте типы полей как NOT NULL.

IP- адреса лучше всего хранить как UNSIGNED INT и использовать MySQL-функции INET_ATON() и INET_NTOA().

VARCHAR имеет переменную длину в файле. Занимает столько места сколько записано данных. При UPDATE если данных стало больше - выделяется место в другом месте файла. Это создает дополнительную нагрузку и является причиной фрагментации. Используйте VARCHAR если данные обновляются редко, либо используется сложная кодировка, например UTF-8. Идеально для хранения неизменяемых данных.

CHAR имеет фиксированную длину в файле. Фрагментация ему не страшна. CHAR полезен когда нужно хранить короткие строки приблизительно одинаковой длины. Частое обновление не ведет к фрагментации. Старайтесь использовать CHAR, сортировка по такому полю производится быстрее.

Используйте TIMESTAMP, он занимает на диске меньше места.

Не используйте JOIN между CHAR/VARCHAR и ENUM.

Применять значения по умолчанию (DEFAULT). При вызове запроса INSERT в таблицу будут записываться только те поля, значения которых отличаются от DEFAULT.

Используйте настолько малые типы INT, насколько это возможно.

Если у вас нет записей с переменной длиной (ни одного поля с типом VARCHAR, BLOB или TEXT), то таблица сохраняется в формате

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

При использовании нескольких последовательных INSERT запросов, лучше все данные указать в одном INSERT, чем делать несколько INSERT.

При загрузке данных в таблицу лучше использовать LOAD DATA INFILE, чем INSERT, такой метод в 20 раз быстрее.

Если ожидается много запросов INSERT или UPDATE, работающих одновременно, то для большей скорости рекомендуется приметь LOCK TABLES.

Время от времени нужно дефрагметировать таблицы. Это делается утилитой isamchk с опциями - evi.

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

В условиях WHERE задавайте вместо функций переменные и/или константы.

Используйте EXPLAIN для ваших запросов SELECT.

Используйте LIMIT 1, если нужно получить уникальную строку в запросе SELECT.

Не используйте ORDER BY RAND().

Старайтесь не использовать SELECT *.

Рекомендуется использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно – UNSIGNED. Даже в вашей таблице есть поле с уникальным именем пользователя, не делайте его основным ключом. Поля с типом VARCHAR медленно работают в качестве основных ключей. Также структура вашей базы данных будет лучше, если в ней внутри использовать ссылки на записи на основании id.

Используйте ENUM вместо VARCHAR. Столбцы типа ENUM очень компактные и быстрые. Они хранятся в базе данных как и TINYINT, но еще они могут содержать строчные значения.

Изучите предложения PROCEDURE ANALYSE(). Она позволяет MySQL анализировать структуру столбцов и действительных данных в вашей таблице и на основании анализа выдавать "предложения". Это действует только если в вашей таблице есть реальные данные, так как их наличие играет существенную роль при принятии решений.

Выбирайте правильный механизм хранения данных. Есть два основных механизма хранения

1) MyISAM отлично подходит для приложений с большой нагрузкой по чтению, но он не очень хорошо масштабируется при наличии большого количества записей - вся таблица будет заблокирована и ни один процесс не сможет ничего прочитать пока запрос не завершится. MyISAM быстро выполняет вычисления для запросов типа SELECT COUNT(*).

2) InnoDB является более сложным механизмом хранения данных, и он может быть более медленным, чем MyISAM для большинства маленьких приложений. Но он поддерживает блокирование строк, что лучше для масштабирования таблиц. Также он поддерживает некоторые дополнительные особенности, такие как транзакции.

Не рекомендуется использовать без консультации с системным администратором функции постоянного соединения mysql_pconnect().

Если запрос оставляется вручную, то все числовые параметры должны быть приведены к нужному типу, а все остальные параметры должны быть обработаны функцией mysql_real_escape_string() и заключены в кавычки или в апосторофы и в них должны быть экранированы слешами спецсимволы.

Пример. $z=”SELECT * FROM table WHERE name = 'Д\'Артаньян'”

Распространенной ошибкой является применение фунции stripslashes при получении данных из базы.

Однако для простоты и надёжности можно и с числами работать, как со строками (проскольку mysql всё равно преобразует их к нужному типу). Таким образом, любые данные, вставляемые в запрос, рекомендуется прослешивать и заключать в кавычки.

Имена полей и таблиц рекомендуется заключать в обратные одинарные кавычки - "`" (клавиша с этим символом находится на стандартной клавиатуре слева от клавиши "1") Ведь имя поля может совпадать с ключевыми словами mysql, но если мы используем обратную кавычку, то MySQL поймёт всё правильно.

Пример. SELECT * FROM `table` WHERE `date` = '2006-04-04'

Обратные кавычки слешами не экранируются.

Дата добавления: 2014-12-10