Запрос является наиболее часто используемой операцией в технологии базы данных. Процесс операции запроса относительно прост. Хотя процесс очень прост, различные методы запроса и настройки базы данных окажут большое влияние на производительность запроса.
Поэтому в этой статье обсуждаются методы оптимизации запросов, обычно используемые в MySQL. Дискуссии включают в себя: улучшение скорости запроса через буферизацию запросов;
1. Улучшение скорости запроса через буферизацию запросов
Как правило, когда мы используем операторы SQL для запроса, сервер базы данных будет выполнять этот оператор SQL каждый раз, когда он получает SQL от клиента. Но когда то же самое оператор SQL получен в определенном интервале (например, в течение 1 минуты), оно будет выполнено таким же образом. Хотя это может обеспечить природу данных в реальном времени, большую часть времени данные не требуют полного в реальном времени, что означает, что может быть определенная задержка. Если это так, то выполнение того же SQL за короткий период времени не стоит.
К счастью, MySQL предоставляет нам функцию буферизации запросов (буферизация запросов может использоваться только в MySQL 4.0.1 и выше). Мы можем в определенной степени улучшить производительность запроса с помощью кэширования запросов.
Мы можем установить буфер запроса через файл my.ini в каталоге установки MySQL. Настройка также очень проста, просто установите Query_cache_type на 1. После установки этого атрибута, перед выполнением какого -либо оператора SELECT, MySQL проверит свой буфер, был ли тот же оператор SELECT был выполнен. Но при написании операторов SQL обратите внимание, что буфер запроса MySQL чувствителен к случаям. Следующие два оператора выбора следующие: Выберите * Из таблицы 1
Выберите * из таблицы 1
Два оператора SQL, выше, являются совершенно разными выборами для буферизации запросов. Кроме того, буфер запроса не обрабатывает пространства. начало и конец).
Хотя не настройка буфера запроса может иногда вызывать потери производительности, существуют некоторые операторы SQL, которые должны запросить данные в режиме реального времени или не используются часто (возможно, выполняются один или два раза в день). Это требует отключения буферизации. Конечно, вы можете отключить кэш запроса, установив значение Query_cache_type, но это навсегда отключит кеш запроса. MySQL 5.0 предоставляет метод временного отключения кэша запроса: выберите SQL_NO_CACHE FILED1, FEELD2 из таблицы 1
Поскольку приведенный выше оператор SQL использует SQL_NO_CACHE, независимо от того, был ли этот оператор SQL был выполнен ранее, сервер не будет искать в буфере и будет выполнять его каждый раз.
Мы также можем установить Query_cache_type в my.ini на 2, чтобы кэш запроса использовался только после использования SQL_CACHE. Выберите SQL_CALHE * из таблицы1
2. Автоматическая оптимизация запросов MySQL
Индексы очень важны для баз данных. Индексы могут использоваться для повышения производительности во время запросов. Но иногда использование индексов может снизить производительность. Мы можем посмотреть на следующую таблицу продаж: создать продажи таблиц
(
Id int (10) не знака не null auto_increment,
Имя Varchar (100) не null,
Цена плавает, а не нулевой,
Sale_count int not null,
Sale_date date не null,
Первичный ключ (ID) ，
Индекс (имя),
Индекс (sale_date)
)
Предположим, что в этой таблице хранятся миллионы данных, и мы хотим запросить среднюю цену продукта № 1000 в 2004 и 2005 годах. Мы можем написать следующий оператор SQL: выберите AVG (Price) из продаж
Где id = 1000 и sale_date между '2004-01-01' и '2005-12-31';
Если количество этого продукта очень большое, на долю почти 50% или более записей в таблице продаж. Затем использование индекса в поле sale_date для вычисления среднего значения немного медленно. Потому что, если вы используете индекс, вы должны сортировать индекс. Когда существует очень много записей, которые соответствуют условиям (например, составляют 50% или более записей во всей таблице), скорость замедлится, поэтому лучше сканировать всю таблицу. Поэтому MySQL автоматически решает, использовать ли индекс для запроса на основе доли данных, которые соответствуют условиям во всей таблице.
Для MySQL индекс не используется, когда доля вышеупомянутого запроса результатов для записей во всей таблице составляет около 30%. Однако фактическое значение масштаба будет варьироваться в зависимости от используемого двигателя базы данных.
3. Сортировка на основе индекса
Одной из слабостей MySQL является его сортировка. Хотя MySQL может запросить примерно 15 000 записей за 1 секунду, MySQL может использовать не более одного индекса при запросе. Следовательно, если условие, где уже занимает индекс, индекс не будет использоваться при сортировке, что значительно снизит скорость запроса. Мы можем посмотреть на следующее оператор SQL: SELECT * Из продаж, где name = «name», порядок sale_date desc;
Индекс в поле «Имя» использовался в предложении «Где приведен выше SQL», поэтому индекс больше не будет использоваться при сортировке sale_date. Чтобы решить эту проблему, мы можем создать композитный индекс в таблице продаж: Alter Table Sales Drop Name, добавить индекс (имя, sale_date)
Таким образом, скорость будет значительно улучшена при использовании вышеупомянутого оператора SELECT для запроса. Но будьте осторожны, используя этот метод, убедитесь, что в предложении WHERE нет поля сортировки. , поэтому запрос замедлится.
4. Обнаружение недоступных запросов
При выполнении операторов SQL вы неизбежно столкнетесь с некоторыми условиями, которые должны быть ложными. Так называемое условие обязательного промежутка заключается в том, что независимо от того, как меняются данные в таблице, это условие является ложным. Например, где значение <100 и значение> 200. Мы никогда не сможем найти число, которое составляет менее 100 и более 200.
Если вы сталкиваетесь с такими условиями запроса, не нужно выполнять такие операторы SQL. К счастью, MySQL может автоматически обнаружить эту ситуацию. Например, мы можем взглянуть на следующее оператор SQL: SELECT * FROM SALES, где имя = «name1» и name = «name2»
Приведенный выше оператор запроса ищет записи, имя которого равна как названию1, так и на имя 2. Очевидно, что это недоступный запрос, и условие, где должно быть ложным. Перед тем, как MySQL выполняет оператор SQL, он сначала проанализирует, является ли условие, которое является недоступным запросом. Чтобы проверить это. Сначала используем объяснение, чтобы проверить следующий SQL: Объясните Select * из продаж, где name = "name1"
Приведенный выше запрос является обычным запросом. Это показывает, что MySQL управляет продажами. Посмотрите на следующие операторы еще раз: Объясните Select * из продаж, где name = «name1» и name = «name2»
Мы видим, что элемент таблицы пуст, что означает, что MySQL не работал в таблице продаж.
5. Используйте различные выборы запросов, чтобы повысить производительность
В дополнение к нормальному использованию оператора SELECT, MySQL также предоставляет нам множество вариантов, которые могут повысить производительность запросов. Как упомянуто выше, SQL_NO_CACHE и SQL_CACHE, которые используются для управления буферизацией запросов, являются двумя из параметров. В этом разделе я представлю несколько часто используемых вариантов запросов.
1. raight_join: порядок подключения к силу
Когда мы подключаем две или более таблиц для запроса, нам не нужно заботиться о том, какая таблица подключается сначала MySQL, а какая таблица она подключает последнюю. Все это определяется последовательности соединения, определяемой MySQL, посредством ряда внутренних расчетов и оценок. В следующих операторах SQL, таблица 1 и таблица 2 не обязательно подключены друг к другу: выберите Table1.field1, Table2.field2 из таблицы 1, Таблица 2, где…
Если разработчикам необходимо вмешать вручную в порядке соединений, они должны использовать ключевое слово raight_join, например, следующее оператор SQL: выберите Table1.field1, Table2.field2 из Table1 raste_join table2, где…
Как видно из вышеуказанного оператора SQL, прямой_оин используется для заставления MySQL присоединиться к таблицам в порядке таблицы 1 и таблицы 2. Если вы думаете, что присоединиться к вашему собственному заказу более эффективно, чем заказ, рекомендованный MySQL, вы можете использовать прямую_оин для определения порядка подключения.
2. Вмешиваться в использование индекса и повысить производительность
Использование индексов было упомянуто выше. При нормальных обстоятельствах MySQL решит, использовать ли индекс и какой индекс использовать при запросе. Но в некоторых особых случаях мы хотим, чтобы MySQL использовал только один или несколько индексов, или мы не хотим использовать определенный индекс. Это требует использования некоторых вариантов запроса MySQL для управления индексом.
Ограничьте область использования индексов
Иногда мы создаем много индексов в таблице данных. Но иногда мы хотим, чтобы MySQL рассмотрел только несколько индексов вместо всех индексов. Выберите * Из таблицы 1 Используйте индекс (Field1, Field2)…
Как видно из вышеуказанного утверждения SQL, независимо от того, сколько индексов было установлено в таблице 1, MySQL рассматривает только индексы, установленные на поле 1 и поле2 при выборе индексов.
Ограничьте диапазон индексов, которые не используются
Если у нас есть много индексов для рассмотрения и мало неиспользованных индексов, мы можем использовать индекс игнорировать для обратного выбора. В приведенном выше примере выбирается индекс, который рассматривается, в то время как использование индекса игнорирования состоит в том, чтобы выбрать индекс, который не учитывается. Выберите * из таблицы 1 Игнорировать индекс (Field1, Field2)…
В приведенном выше операторе SQL только индексы на Field1 и Field2 в таблице Table1 не используются.
Принудительно использовать индекс
Приведенные выше два примера оба предоставляют MySQL выбор, что означает, что MySQL не должен использовать эти индексы. Иногда мы надеемся, что MySQL должен использовать определенный индекс (поскольку MySQL может использовать только один индекс при запросе, он может заставить MySQL использовать только один индекс). Это требует использования индекса силы для выполнения этой функции. Выберите * Из таблицы 1 Индекс силы (Field1)…
Приведенный выше оператор SQL использует только индекс, построенный на поле1, а не индексы на других полях.
3. Используйте временные таблицы для повышения производительности запроса
Когда в наборе нашего запроса есть много данных, мы можем привести к тому, что результат установлена во временной таблице через опцию SQL_Buffer_Result. записи)) и может обслуживать большие рекордные сетки клиентам в течение длительного периода времени. Выберите sql_buffer_result * из таблицы 1, где…
Подобно опции SQL_BUFFER_RESULT. Выберите SQL_BUFFER_RESULT FEELD1, COUNT (*) из группы TABLE1 по FEELD1
6. Заключение
Существует также «принцип 20/80» в программировании, то есть 20% кода занимает 80% времени. То же самое касается разработки приложений базы данных. Оптимизация приложений базы данных фокусируется на эффективности выполнения SQL. Основное внимание к оптимизации запросов данных состоит в том, чтобы сервер базы данных считывался меньше данных с дисков и страниц чтения последовательно, а не в последующих.