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

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

И чтобы не быть слишком расплывчатым, допустим, что мы используем базу данных maintstream, такую ​​как MySQL или Oracle, и что эта база данных будет содержать 500 000-1 млн. Записей в ~10 таблицах, некоторые с ограничениями внешнего ключа, все с использованием наиболее типичные механизмы хранения (например, InnoDB для MySQL). И, конечно, основы, такие как PK, определены также как и ограничения FK.

Источник

7 ответов

Решение

Узнайте об индексах и используйте их правильно. Вообще говоря, следуйте этим рекомендациям:

  • Каждая таблица должна иметь кластерный индекс
  • Поля, используемые для фильтров и сортировок, являются хорошими кандидатами для индексации
  • Более селективные поля - лучшие кандидаты для индексации
  • Для обеспечения максимальной производительности по ключевым запросам разработайте "покрывающие индексы" для этих запросов.
  • Убедитесь, что ваши индексы действительно используются, и удалите те, которые не
  • Если в вашей таблице 15 полей, а вы создаете 15 индексов, в каждом из которых только одно поле, вы делаете это неправильно:)

* Есть некоторые исключения из этих правил, если вы знаете, что делаете. Мой опыт работы с Microsoft SQL Server, но я бы предположил, что большая часть этого совета будет по-прежнему относиться к другой RDMS.

Источник

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 с одним полем (без композитов), но составные бизнес-ключи в качестве индекса с уникальным ограничением, использование соответствующих типов данных, например, использование соответствующих числовых типов для числовых значений, а не символов или аналогичных. Следует также учитывать физические атрибуты оборудования, на котором работает СУБД, поскольку основную часть времени запроса часто составляет дисковый ввод-вывод - но, конечно, не принимайте это как должное - используйте профилировщик, чтобы узнать, куда идет время,

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

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

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

Источник

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

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

Источник

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

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

Источник
Другие вопросы по тегам