SEO сервисы

Реклама сайтов


Хотите разместить ссылку здесь?








Пдразделы

Статьи

Всего статей: 225 Показать все (скрыть)




Что искали?


Оптимизация работы с MySQL





0

Проблемы при использовании MySQL можно разделить на следующие три группы(в порядке значимости):

   1. Неиспользование или неправильное использование индексов.
   2. Неправильная структура БД.
   3. Неправильные неоптимальные SQL запросы.


Остановимся на каждой из этих групп подробнее.

Использование индексов.
Неиспользование или неправильное использование индексов - это то, что чаще всего замедляет запросы. Для тех, кто мало знаком с механизмом работы индексов или еще не читал об этом в мануале, очень советую почитать.
Советы по использованию индексов:

   
  • Не нужно индексировать все подряд. Довольно часто, не понимая смысла, люди просто индексируют все поля таблицы. Индексы ускоряют выборки, но замедляют вставки и обновления строк, поэтому выбор каждого индекса должен быть осмыслен.
  • Один из основных параметров, характеризующий индекс - селективность(selectivity) - количество разных элементов в индексе. Нет смысла индексировать поле, в котором два-три возможных значения. Пользы от такого индекса будет мало.
  • Выбор индексов должен начинаться с анализа всех запросов к данной таблице. Очень часто после такого анализа вместо трех-четырех индексов можно сделать один составной.
  • При использовании составных индексов порядок полей в индексе имеет определяющее значение.
  • Не забывайте про покрывающие(covering) индексы. Если все данные в запросе могут быть получены из индекса, то MySQL не будет обращаться непосредственно к таблице. Подобные запросы будут выполняться очень быстро. Например для запроса SELECT name FROM user WHERE login="test" при наличии индекса (login, name) обращения к таблице не потребуется. Порой имеет смысл добавить в составной индекс дополнительное поле, которое сделает индекс покрывающим и ускорит запросы.
  • Для индексов по строкам часто достаточно индексировать лишь часть строки. Это может значительно уменьшить размер индекса.
  • Если % стоит в начале LIKE(SELECT * FROM table WHERE field LIKE '%test') индексы использоваться не будут.
  • FULLTEXT индекс используется только с синтаксисом MATCH ... AGAINST.



Структура БД.
Грамотно спроектированная БД - залог быстрой и эффективной работы с базой. С другой стороны, плохо продуманная БД - это всегда головная боль для разработчиков.

Советы по проектированию БД:

  • Используйте минимально возможные типы данных. Чем больше тип данных, тем больше таблица, тем больше обращений к дискам нужно для получения данных. Используйте очень удобную процедуру: SELECT * FROM table_name PROCEDURE ANALYSE(); для определения минимально возможных типов данных.
  • Не ошибусь, сказав, что в 90% случаев программисты используют тип INT для целочисленных значений. Вы уверены, что в таблице будет 4.2 миллиарда записей? Скорее всего, достаточно использования MEDIUMINT, а часто и SMALLINT.
  • Очень часто используют varchar(255), хотя в подавляющем большинстве случаев 255 символов совсем не нужно.
  • Используйте тип ENUM(или SET) для столбцов с заданным набором данных. Хотя ENUM и противоречит нормальным формам, но, как показывают тесты, он быстрее других способов.
  • Тип данных для IP адреса - INT UNSIGNED(в сочетании с INET_ATON, INET_NTOA), а не VARCHAR.
  • Используйте тип TIMESTAMP для хранения дат позже 1970-го года. Он занимает в 2 раза меньше места, чем DATETIME.
  • На этапе проектирования соблюдайте нормальные формы. Часто программисты прибегают к денормализации уже на этом этапе. Однако в большинстве случаев в начале проекта далеко не очевидно чем это может вылиться. Денормализовать таблицу гораздо проще, чем страдать от неоптимально денормализованной. Да и JOIN порой работает быстрее, чем неверно денормализованные таблицы.
  • Не используйте NULL столбцы кроме случаев, когда они вам осознанно нужны.


SQL запросы.

  • Избегайте запросов в цикле. SQL - язык множеств и к написанию запросов нужно подходить не языком функций, а языком множеств.
  • Избегайте * (звездочки) в запросах. Не поленитесь перечислить именно те поля, которые вы выбираете. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вы действительно выбираете все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочек невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, сегодня в вашей таблице пять INT столбцов, а через месяц добавилось еще одно TEXT и BLOB, а звездочка как была, так и осталась.
  • При постраничном выборе для получения общего количества записей используйте SQL_CALC_FOUND_ROWS и SELECT FOUND_ROWS(); При использовании SQL_CALC_FOUND_ROWS MySQL кеширует выбранное количество строк(до применения LIMIT) и при SELECT FOUND_ROWS() только отдает это закешированное значение без необходимости повторного выполнения запроса.
  • Не забывайте, что у INSERT есть синтаксис для множественной вставки. Один запрос будет выполняться на порядок быстрее, чем множество запросов в цикле.
  • Используйте LIMIT там, где вам не нужны все данные.
  • Используйте INSERT ... ON DUPLICATE KEY UPDATE ... вместо выборки и INSERT или UPDATE после нее, а также часто вместо REPLACE.
  • Не забывайте про замечательную функцию GROUP_CONCAT. Она может выручить при сложных запросах.


Ссылка на эту страницу: