Базовые методы оптимизации базы данных

Решил посвятить этой теме целый отдельный пост на своём блоге т.к. это действительно очень важно, особенно когда речь идёт о высоконагруженных проектах. Безусловно переоценить роль оптимизации базы данных очень сложно, ведь от этого зависит многое и в первую очередь производительность вашего продукта работающего с базой данных. Вообще оптимизации баз данных это целая наука, которой можно посвятить целую книгу, в этом деле есть свои тонкости и нюансы. Обычно оптимизацией БД должен заниматься архитектор базы ещё при создании самой модели таблиц и связей. Т.е. об оптимизации думаем сразу при создании базы. В общем я поведаю о базовых методах распределения нагрузки СУБД. Вообще когда говорят об оптимизации баз данных, то в первую очередь речь идёт о правильной простановке индексов и о выборе наиболее подходящих движков таблиц (систем хранения данных). Все остальные аспекты базируются на этих основных моментах.

Индексы

Существуют не правильное мнение, что для лучшей оптимизации индексы стоит ставить для всех полей всех таблиц. Это не верно! Так делать не следует ибо это ведёт к лишним вычислительным нагрузкам. Индексы надо ставить с умом! Ставим их только на те столбцы, которые отвечают определённым условиям описанным ниже. Ставим именованные индексы на все поля, которые участвуют в условиях WHERE (> больше, < меньше, = равно, <> не равно). Именованные индексы всегда лучше анонимных т.к. ими можно легко управлять, поэтому рекомендую именно их. Так же ставим индексы на все поля отвечающие за связи таблиц, т.е. внешние ключи даже при том, что внешние ключи, как таковые, у вас в таблицах не используются, а лишь присутствуют в схеме связей, всё равно ставим для этих полей индексы. На ограничения первичного и уникального ключа индексы ставить не нужно т.к. для этих ограничений индексы создаются автоматически. На ровне с этим не следует ставить индексы на поля, которые участвуют в операторах LIKE и REGEXP ваших SQL запросов, т.к. эти операторы часто не используют индексы (в зависимости от строки поиска).

Движки таблиц

Неотъемлемой частью оптимизации баз данных является логика выбора движка таблицы. Универсального движка, который будет лучше всех, нет! У каждой системы хранения данных есть свою плюсы и минусы. Например, операции добавления и обновления данных (OLTP) быстрее всего протекают в MyISAM, поэтому для разных журналов логов лучше выбрать именно этот движок. Операции  агрегации, чтения и выборки данных (OLAP) быстрее работают на InnoDB, поэтому для подобных целей выставляем именно его. Само собой для временных таблиц лучше всего подойдёт движок MEMORY, который хранит данные в оперативной памяти, за счёт чего наблюдается высочайшая скорость работы с любым типом операций.

Программный код

Все SQL запросы пишем в отдельных файлах, а не в программном коде в виде строк, так удобнее. В программу лишь загружаем файлы этих запросов.

Составление запросов

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

  • Старайтесь не использовать такие операторы как LIKE если есть возможность применить WHERE = «string».
  • Лучше использовать присоединение таблиц JOIN чем указывать дополнительные условия сопоставления колонок разных таблиц в операторе WHERE.

Отладка и тестирование SQL запросов

После создания структуры базы данных следует всё тщательно проверить. Т.е. посмотреть все ли добавленные индексы используются СУБД как это было задумано или нет. Для этого к каждому запросу дописываем префиксом оператор EXPLAIN, который выводит сводную информацию относительно выполнения введённого запроса. Там будут указаны используемые индексы. Если какой-либо индекс не используется сервером БД при исполнении запроса, то его можно удалить а политику оптимизации пересмотреть.

Собственно вот все базовые рекомендации по оптимизации баз данных.

Поделиться!
Tags: , ,

49.7MB | MySQL:52 | 0,287sec