Методы оптимизации базы данных для любителей

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

Говоря о проекте базы данных, проверьте нормализацию базы данных, например статья в википедии: Нормальные формы .

Если у вас хороший дизайн, но вам все еще нужно оптимизировать производительность, попробуйте денормализация .

Если у вас есть особые потребности, которые эффективно не покрываются реляционной моделью, посмотрите на другие модели, охватываемые термином NoSQL .

Некоторые оптимизации запросов / схем:

• Будьте внимательны при использовании DISTINCT или GROUP BY. Я обнаружил, что многие новые разработчики будут использовать DISTINCT там, где это действительно не нужно или где его можно было бы более эффективно переписать с помощью оператора Exists или производного запроса.

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

Select
From Orders
    Left Join Customers
        On Customers.Id = Orders.CustomerId

Если Orders.CustomerId - обязательный столбец, то нет необходимости использовать левое соединение.

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

• Необязательно везде использовать суррогатные ключи. Могут быть таблицы, лучше подходящие для интеллектуального ключа (например, аббревиатуры штатов США, коды валют и т. Д.), Которые во многих случаях позволят разработчикам избежать дополнительных объединений.

• По возможности найдите способы архивирования данных на OLAP-сервер или сервер отчетов. Чем меньше вы можете сделать производственных данных, тем быстрее они будут работать.

Дизайн, который кратко моделирует вашу проблему, всегда является хорошим началом. Чрезмерное обобщение модели данных может привести к проблемам с производительностью. Например, я слышал отчеты о проектах, стремящихся к сверхгибкости, которые используют СУБД в качестве глупого хранилища «имя / значение», и в результате производительность была ужасающей.

После того, как хороший дизайн будет на месте, используйте инструменты, предоставляемые СУБД, чтобы помочь ему достичь хорошей производительности.PK с одним полем (без составных), но составные бизнес-ключи в качестве индекса с уникальным ограничением, использование соответствующих типов данных, например используя соответствующие числовые типы для числовых значений, а не char или аналогичные. Также следует учитывать физические атрибуты оборудования, на котором работает СУБД, поскольку большая часть времени запроса часто приходится на дисковый ввод-вывод - но, конечно, не принимайте это как должное - используйте профилировщик, чтобы узнать, куда идет время. .

В зависимости от соотношения обновления / запроса материализованные представления / индексированные представления могут быть полезны для повышения производительности для медленно выполняющихся запросов. Альтернатива для бедняков - использовать триггеры для вызова процедуры, которая заполняет таблицу в результате медленного, редко изменяемого представления.

Оптимизация запросов - это своего рода черное искусство, поскольку она часто зависит от базы данных, но здесь приведены некоторые практические правила - Оптимизация SQL .

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

Используйте целостный подход к оптимизации.

Учитывайте влияние медленных дисков, задержки в сети, нехватки памяти и нагрузки на сервер.

По возможности используйте меньше запроса . Используйте «JOIN» и сгруппируйте свои таблицы так, чтобы один запрос давал ваши результаты.

Хорошим примером является Модифицированный просмотр дерева предварительного заказа ( MPTT ) для получения всех упорядоченных родительских узлов дерева в одном запросе.