ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
(1 оценок, среднее: 5,00 из 5)
Загрузка...
В этой статье мы сосредоточимся на примерах, связанных с MySQL, но те же понятия могут быть применены ко многим другим реляционным базам данных.
Теперь, как никогда ранее, разработчики программного обеспечения должны обладать обширными знаниями в области настройки производительности SQL.
Сдвиг происходит как в небольших стартапах, так и на крупных предприятиях. В настоящее время разработчики пишут SQL-запросы и уровень доступа к базе данных.
По мере развития технологий даже самые начинающие конечные пользователи теряют терпение и ожидают, что ваше приложение будет работать быстрее, даже быстрее, чем вы ожидаете. Поэтому мы, как разработчики программного обеспечения, обязаны удовлетворять эту бесконечную потребность в быстром и немедленном времени отклика в любом месте и в любое время.
На самом деле не имеет значения, используете ли вы уровень абстракции базы данных (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django или другие) или пишете нативные SQL-запросы, вы в конечном итоге столкнетесь с проблемой настройки отправляемых запросов. в вашу базу данных.
Некоторые скажут, что индексирование является наиболее важной частью настройки SQL-запросов. Во многих случаях это определенно может быть правдой. Сначала ознакомьтесь с аспектами, которые следует учитывать при выборе оптимальных индексов.
Помните, что при индексировании вы должны внимательно следить за предложением WHERE и таблицей запроса JOINs, так как эти операторы включают в себя критические фильтрующие части запроса.
Кроме того, основными узкими местами в поиске данных могут быть части GROUP BY и ORDER BY. Потенциальный сбой будет в том, что вы не сможете индексировать их в некоторых случаях, как мы объяснили здесь. Поэтому вам может потребоваться переосмыслить структуру вашего запроса перед созданием индексов, чтобы убедиться, что вы пишете отличные запросы, а также пишете запросы с индексированием.
Как только вы выяснили индексирование для одного запроса, не останавливайтесь на этом. Расширьте свой взгляд и посмотрите на другие важные вопросы в вашем приложении. Чем больше запросов вы просматриваете, тем больше вы будете думать о лучших индексах для создания. Убедитесь, что вы объединяете индексы, когда это возможно, и удаляете индексы, которые больше не нужны. Просмотр области всего приложения всегда будет лучше, чем просмотр области одного запроса.
Тем не менее, наличие большего количества индексов, чем вам нужно, также может иметь неприятные последствия, поскольку они могут замедлить операции записи (такие как операторы INSERT/UPDATE). Поэтому создайте индексы для оптимизации ваших запросов SQL, но делайте это с умом.
К нам часто обращаются клиенты, которые спрашивают нас «почему база данных не использует мой индекс?». Ну, это отличный вопрос с бесконечными возможными ответами. Но в этой статье мы попытаемся предоставить несколько распространенных вариантов, которые мы часто видим, поэтому, надеюсь, вы найдете их полезными для собственного варианта использования.
Рассмотрим этот запрос, который подсчитывает количество хот-догов, купленных в России в 2019 году. На всякий случай, если вам интересно, в 2019 году в России было продано много хот-догов.
SELECT count(*) FROM us_hotdog_purchases WHERE YEAR(purchase_time) = ‘2019’
Как видите, мы используем функцию YEAR, чтобы получить часть года из столбца purchase_time. Этот вызов функции не позволит базе данных использовать индекс для поиска по столбцу purchase_time, потому что мы проиндексировали значение purchase_time, но не возвращаемое значение YEAR (purchase_time).
Чтобы преодолеть эту проблему и настроить этот запрос SQL, вы можете проиндексировать результат функции, используя Generated Columns, которые доступны начиная с MySQL 5.7.5.
Другое решение может заключаться в том, чтобы найти альтернативный способ написания того же запроса без использования вызова функции. В этом примере мы можем преобразовать это условие в двухстороннее условие диапазона, которое будет возвращать те же результаты:
SELECT count(*) FROM us_hotdog_purchases WHERE purchased_at >= ‘2019-01-01’ AND purchased_at < ‘2020-01-01’
Рассмотрим этот запрос, который выбирает количество постов на Facebook, опубликованных после канун Нового года, или опубликованных пользователем по имени Alex.
SELECT count(*) FROM fb_posts WHERE username = ‘Alex’ OR post_time > ‘2019-01-01’
Индекс для столбцов username и post_time может показаться полезным, но в большинстве случаев база данных не будет его использовать, по крайней мере, не полностью. Причиной будет связь между двумя условиями – оператор OR, который заставляет базу данных извлекать результаты каждой части условия в отдельности.
Альтернативный способ взглянуть на этот запрос может состоять в том, чтобы «разделить» условие OR и «объединить» его с помощью предложения UNION. Эта альтернатива позволит вам индексировать каждое из условий отдельно, поэтому база данных будет использовать индексы для поиска результатов, а затем объединять результаты с предложением UNION.
SELECT … FROM … WHERE username = ‘Alex’ UNION SELECT … FROM … WHERE post_time > ‘2019-01-01’
Обратите внимание, что если вы не возражаете против дублирования записей в наборе результатов, вы также можете использовать UNION ALL (который будет работать лучше, чем UNION DISTINCT по умолчанию).
Рассмотрим этот запрос, который выбирает все сообщения из Facebook и сортирует их по имени пользователя в порядке возрастания, а затем по дате публикации в порядке убывания.
SELECT username, post_type FROM fb_posts ORDER BY username ASC, post_type DESC
MySQL (и многие другие реляционные базы данных) не могут использовать индексы при сортировке со смешанным порядком (как ASC, так и DESC в одном и том же предложении ORDER BY). Это изменилось с выпуском функциональности обращенных индексов и MySQL 8.x.
Так что вы можете сделать, если вы еще не обновились до последней версии MySQL? Во-первых, мы рекомендуем пересмотреть сортировку смешанного порядка. Вам это действительно нужно? Если нет, избегайте этого.
Итак, вы решили, что вам это нужно, или ваш менеджер по продукту сказал: «Мы никак не можем обойтись без него»? Другим вариантом будет использование сгенерированных столбцов (доступно в MySQL 5.7.5+) для создания обращенного столбца и сортировки по этому столбцу вместо исходного. Например, предположим, что вы сортируете по числовому столбцу, вы можете создать сгенерированный столбец с отрицательным числовым значением, соответствующим исходному номеру, и отсортировать по этому новому столбцу в обратном порядке. Таким образом, все столбцы будут иметь одинаковый порядок сортировки в предложении ORDER BY, но сортировка будет происходить так, как это было первоначально определено требованиями вашего продукта.
Последнее возможное решение не всегда будет возможным, поэтому последним средством будет обновление до последней версии MySQL, которая поддерживает сортировку по смешанному порядку с использованием индексов.
Рассмотрим этот запрос, который выбирает количество красных фруктов в саду.
SELECT count(*) FROM forest WHERE fruit_color = 5; /* 5 = red */
Предполагая, что тип столбца fruit_color имеет тип VARCHAR, или просто что-то не числовое, индексация этого столбца не будет очень полезной, поскольку требуемое неявное приведение не позволит базе данных использовать индекс для процесса фильтрации.
Итак, как вы можете настроить этот запрос SQL? У вас есть два варианта для оптимизации этого запроса. Первый – сравнить столбец с константным значением, соответствующим типу столбца, поэтому, если это столбец VARCHAR, сравните его с «5» (с одинарными кавычками), а не с 5 (это числовое сравнение, которое приведет к в скрытом исполнении).
Лучшим вариантом будет настроить тип столбца так, чтобы он соответствовал наиболее подходящему типу для значений, которые содержит столбец. В этом примере столбец должен быть изменен на тип INT. Обратите внимание, что изменение типа столбца может быть сложной задачей, поэтому прочитайте о проблемах этой задачи, прежде чем идти к ней.
Рассмотрим этот запрос, который ищет все сообщения в Facebook по имени пользователя, которое содержит строку «Mar», поэтому мы ищем все сообщения, написанные пользователями с именами Mark, Marcus, Almar и т. l.
SELECT * FROM fb_posts WHERE username like ‘%Mar%’
Наличие подстановочного знака «%» в начале шаблона не позволит базе данных использовать индекс для поиска в этом столбце. Такие поиски могут занять некоторое время ..
В этом случае есть два варианта повышения производительности этого запроса. Первый тривиален – подумайте, достаточно ли важен подстановочный знак. Если вы можете обойтись без этого, избавьтесь от этого.
Другим вариантом будет использование полнотекстовых индексов. Обратите внимание, что эти индексы и синтаксис MATCH… AGAINST не свободны от проблем и имеют некоторые различия по сравнению со знакомыми выражениями LIKE в MySQL.
В этой статье по настройке производительности SQL-запросов мы рассмотрели важность разумной индексации, рассмотрели несколько примеров возможных препятствий при использовании индексированных столбцов в запросах и подробно рассмотрели некоторые другие советы и рекомендации, которые могут быть полезны для лучшая производительность запросов.
