Низкая производительность базы данных является проблемой для каждого администратора базы данных. И не всегда легко изолировать основную причину проблем с производительностью, которая только усугубляет стресс.
Настройка баз данных SQL Server - отличный способ решить некоторые проблемы с производительностью, но не всегда очевидно, с чего следует начать процесс оптимизации. Если вы исключаете дисковое пространство, память и другие факторы, снижающие производительность сети и оборудования, а производительность вашего SQL Server все еще отстает, пора углубиться в свои запросы.
Неоптимизированные запросы могут вызвать множество проблем с производительностью ваших систем. С другой стороны, несколько распространенных ошибок запроса ответственны за большую часть снижения производительности базы данных.
Если ваши запросы страдают от любой из этих шести проблем, приготовьтесь к серьезной настройке производительности.
Проблемный запрос № 1: выражения Like с ведущими подстановочными знаками
Ведущие подстановочные знаки не позволяют MySQL использовать индексы, вызывая полное сканирование таблицы, даже если вы проиндексировали поле в таблице. Сканирование всех строк в таблице значительно замедляет доставку результатов запроса, поэтому удалите ведущий подстановочный знак для повышения эффективности.
Проблемный запрос № 2: Неиндексированные столбцы, используемые в предложениях "Where" и "Group by"
Индексирование столбцов помогает быстрее возвращать результаты запроса, поскольку устраняет необходимость полного сканирования таблицы. Индексирование также помогает отсортировать записи при их возврате и гарантирует, что эти записи однозначно идентифицируются, что особенно полезно в таблицах с более чем 10 строками.
Для некоторой точки зрения рассмотрите возможность использования оператора «explain» в анализе запроса до и после индексации. Это даст вам представление о том, сколько строк сканирования вы только что сохранили.
Проблемный запрос № 3: подобные утверждения, в которых используется оператор 'or' вместо предложения Union
Выполнение запросов с использованием оператора сравнения «or» в полях или столбцах таблицы может быть полезным, но слишком частое применение «or» в предложении «where» - еще один способ полного сканирования таблицы.
Предложение union может ускорить выполнение SQL-запроса, особенно если разные индексы оптимизируют каждую сторону запроса. По сути, оператор объединения берет результаты двух быстрых индексированных запросов и объединяет их в один.
Проблемный запрос № 4: поиск по шаблону
Если вы застряли в ситуации, когда вам нужно выполнять поиск с использованием подстановочных знаков, но вы не хотите снижать производительность, попробуйте использовать полнотекстовый поиск MySQL. Полнотекстовый поиск значительно быстрее, чем поиск с использованием подстановочных знаков, и вы получаете дополнительное преимущество в виде более релевантных результатов при поиске в огромных базах данных.
Проблемный запрос № 5: неоптимизированная схема базы данных
Вы можете значительно повысить производительность SQL-запросов, только лишь оптимизировав схему базы данных. Вот несколько советов по улучшению:
Нормализовать таблицы
Избыточность данных снижает производительность, поэтому обязательно представляйте факт в базе данных только один раз. Например, если вы ссылаетесь на клиента более чем в одной таблице, используйте «customer_name» только один раз, а затем используйте «customer_ID» для последующих ссылок.
Используйте оптимальные типы данных
Некоторые важные моменты, о которых следует помнить, когда речь идет о типах данных:
- Чем короче, тем лучше при оформлении таблиц. Например, используйте тип данных «TINYINT» для поля «user_id» для системной пользовательской таблицы с менее чем 100 пользователями.
- Используйте тип данных «date_time», если поле ожидает значение даты, поэтому вам не нужно преобразовывать записи в формат даты после факта.
- MySQL лучше работает с целочисленными значениями, чем с текстовыми типами данных, включая varchar.
Избегайте нулевых значений
Нулевые значения в столбце могут повредить результаты вашего запроса, поэтому вам может потребоваться назначить значение по умолчанию для полей, в которых записи не требуют обязательного значения.
Не используйте слишком много столбцов
Для обработки широких таблиц (более 100 столбцов) требуется много ЦП и ресурсов. Если вам не нужно обязательно включать широкую таблицу, лучше разбить ее на более мелкие логические таблицы.
Оптимизировать соединения
Операторы SQL со слишком большим количеством объединений (и объединения со слишком большим количеством таблиц) отрицательно влияют на производительность. Стремитесь к не более 12 объединениям для каждого запроса.
Проблемный запрос № 6: запросы MySQL без кеширования
Сайты и приложения, которые выполняют множество выборочных запросов, выиграют от кэширования запросов MySQL. Кэширование запросов MySQL увеличивает производительность во время операций чтения, поскольку оно кэширует запрос выбора вместе с результирующим набором данных и извлекает из памяти (не с диска), если запрос выполняется более одного раза.
Настройка производительности важна для поддержания высокой доступности баз данных SQL Server и обеспечения удовлетворенности конечных пользователей. Но, к сожалению, не всегда сразу видно, где требуется настройка. Если вы устранили очевидные сетевые и аппаратные источники проблем с производительностью, переключите свое внимание на запросы.
Если вы какое-то время проработали администратором баз данных, вы, вероятно, столкнулись с одним (или всеми) из этих проблемных запросов. Теперь, когда вы знаете, на что обращать внимание, проявите инициативу по повышению производительности и исправьте эти распространенные проблемы с запросами, чтобы вы могли пожинать плоды оптимизации запросов SQL Server.