Самодельные гаджеты значительно ускоряют оптимизацию операторов MySQL SQL (с исходным кодом)

введение

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

Так какой же язык выбрать для разработки инструментов?

Администратору баз данных очень важно владеть языком, чтобы соответствовать его работе. По сравнению с простотой оболочки и элегантностью Perl Python — строгий язык высокого уровня. Он имеет много преимуществ, таких как быстрый старт, простой синтаксис, богатое расширение и кросс-платформенность. Многие люди называют его «связующим» языком. Благодаря большому количеству богатых библиотек классов и модулей вы можете быстро создавать необходимые инструменты.

Итак, этот небольшой инструмент стал моим первым домашним заданием по изучению Python, и я назвал его «Помощник по оптимизации операторов MySQL». И с тех пор я глубоко влюбился в Python и разработал множество гаджетов, связанных с базами данных, которые будут представлены вам в будущем.

1. Средства и шаги оптимизации

Прежде чем знакомиться с использованием инструментов, мы сначала опишем часто используемые средства, методы и проблемы, на которые необходимо обратить внимание при оптимизации операторов в MySQL. Это также то, что каждый должен понимать и освоить в ежедневной ручной оптимизации.

1. План выполнения - команда EXPLAIN

План выполнения является основной точкой входа для оптимизации оператора, и процесс выполнения оператора понимается через интерпретацию плана выполнения. С точки зрения генерации плана выполнения MySQL существенно отличается от Oracle, он не кэширует планы выполнения и каждый раз выполняет «жесткий разбор». Чтобы просмотреть план выполнения, используйте команду EXPLAIN.

1) Основное использование

EXPLAIN QUERY

При использовании ключевого слова EXPLAIN перед оператором Select MySQL объяснит, как запустить оператор Select, который показывает, как объединяются таблицы, порядок, в котором они соединяются, и так далее.

EXPLAIN EXTENDED QUERY

При использовании ключевого слова EXTENDED EXPLAIN выдает дополнительную информацию, которую можно просмотреть с помощью SHOW WARNINGS. Эта информация показывает, что оптимизатор уточняет имена таблиц и столбцов в операторе SELECT, как выглядит оператор SELECT после перезаписи и выполнения правил оптимизации, а также может включать другие аннотации для процесса оптимизации. Его можно использовать в MySQL 5.0 и более поздних версиях.В MySQL 5.1 он имеет дополнительный столбец фильтра (filtered).

EXPLAIN PARTITIONS QUERY

Показывает сегмент данных, к которым будет обращаться запрос, если таковой имеется. Его можно использовать только в MySQL 5.1 и новее.

EXPLAIN FORMAT=JSON (новое в версии 5.6)

В другом формате отображается план выполнения. Вы можете увидеть информацию, например, как связаны таблицы.

2) Поле вывода

Далее объясняется значение полей, выводимых командой EXPLAIN, и рассказывается, как оценивать план выполнения.

Порядковый номер запроса в выбранном плане выполнения MySQL. Если в операторе нет подзапросов и т. д., то во всем выводе будет только один SELECT, поэтому каждая строка будет отображать 1 в этом столбце. Если оператор использует подзапросы, операции над множествами, временные таблицы и т. д., столбец идентификаторов сильно усложнится. В приведенном выше примере часть WHERE использует подзапрос, а строка с id=2 представляет коррелированный подзапрос.

select_type

Тип запроса, используемый оператором. Это простой SELECT или сложный SELECT (если последний, покажите, к какому сложному типу он принадлежит). Обычно используются следующие типы маркеров.

DEPENDENT SUBQUERY

Первый SELECT во внутреннем подзапросе зависит от набора результатов внешнего запроса.

DEPENDENT UNION

UNION в подзапросе и все последующие SELECT в UNION, начиная со второго SELECT, также зависят от набора результатов внешнего запроса.

PRIMARY

Самый внешний запрос в подзапросе, обратите внимание, что это не запрос первичного ключа.

SIMPLE

Запросы, отличные от подзапросов или UNION.

SUBQUERY

Первый SELECT внутреннего запроса подзапроса, результат не зависит от набора результатов внешнего запроса.

UNCACHEABLE SUBQUERY

Подзапрос, результирующий набор которого не может быть кэширован.

UNION

Второй SELECT в операторе UNION запускает все последующие SELECT, а первый SELECT является PRIMARY.

UNION RESULT

Объединенные результаты в UNION. SELECT, чтобы получить результаты из временной таблицы UNION.

DERIVED

Запросы к производным таблицам (подзапросы в предложении FROM). MySQL выполняет эти подзапросы рекурсивно, помещая результаты во временные таблицы. Внутри сервер обращается к ней как к «производной таблице», поскольку временная таблица является производной от подзапроса.

table

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

type

Как получить доступ к таблице. Различные типы соединений таблиц перечислены ниже в порядке от лучшего к худшему.

system

Системная таблица, таблица имеет только одну строку записей. Это частный случай соединения таблицы const.

const

Константа чтения, не более одной строки совпадающих записей. Поскольку имеется только одна строка записей, значение поля этой строки записей в оптимизаторе можно рассматривать как постоянное значение. const используется при сравнении с индексом PRIMARY KEY или UNIQUE с фиксированным значением.

eq_ref

Будет не более одного совпадающего результата, доступ к которому обычно осуществляется через первичный ключ или индекс уникального ключа. Ряд записей из этой таблицы считывается, чтобы присоединиться к записям из предыдущей таблицы. В отличие от типа const, это лучший тип подключения. Он используется, когда все части индекса используются для соединений, а индекс имеет тип PRIMARY KEY или UNIQUE. eq_ref можно использовать для извлечения полей при выполнении сравнения "=". Сравниваемое значение может быть фиксированным значением или выражением.Выражение может использовать поля в таблице, подготовленные перед чтением таблицы.

Запрос, который управляет ссылкой на индекс таблицы в операторе JOIN. Все записи в этой таблице, соответствующие полученному значению, будут выбраны и объединены с записями, полученными из предыдущей таблицы. ref используется, когда объединитель использует крайний левый префикс ключа или когда ключ не является PRIMARY KEY или UNIQUE индексом (другими словами, объединитель не может получить только одну запись на основе значения ключа). Это хороший тип объединения, когда найдено только несколько совпадающих записей на основе значения ключа. ref также можно использовать, когда полученные поля сравниваются с использованием оператора «=».

ref_or_null

Единственная разница с ref — это запрос с нулевым значением в дополнение к запросу, использующему ссылку на индекс. Этот тип объединения похож на ref, за исключением того, что MySQL будет дополнительно искать записи, содержащие значения NULL, во время выборки. Эта оптимизация типа соединения была введена в MySQL 4.1.1 и часто используется для подзапросов.

index_merge

Запрос использует два (или более) индекса одновременно, затем результаты индексирования объединяются (merge), а затем считываются данные таблицы. Этот тип подключения означает, что используется метод оптимизации Index Merge.

unique_subquery

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

index_subquery

Возвращаемая комбинация полей результата в подзапросе является индексом (или комбинацией индексов), но не первичным ключом или уникальным индексом. Этот тип соединения похож на unique_subquery. Он заменяет IN подзапросом, но используется, когда в подзапросе нет уникального индекса.

range

Сканирование диапазона индексов. Будут извлечены только записи в заданном диапазоне, и для извлечения записи используется индекс.

index

Полное сканирование индекса. Тип соединения такой же, как и ALL, за исключением того, что он сканирует только дерево индексов. Обычно это будет быстрее, чем ALL, потому что индексные файлы обычно меньше, чем файлы данных. MySQL использует этот тип соединения в случаях, когда запрашиваемое поле является частью отдельного индекса.

fulltext

Полнотекстовое сканирование индекса.

Полное сканирование таблицы.

possible_keys

Это поле указывает, какой индекс MySQL может использовать при поиске записей таблицы. Если индекс недоступен, он будет отображаться как нулевой.

key

Индекс, выбранный оптимизатором запросов из возможных_ключей для использования. Ключевое поле показывает, какой индекс MySQL фактически использует. Когда индекс не используется, значение этого поля равно NULL.

key_len

Длина ключа индекса, выбранная для использования индекса. Поле key_len показывает длину индекса, используемого MySQL. Когда значение ключевого поля равно NULL, длина индекса равна NULL.

ref

Укажите, следует ли фильтровать по константе или по полю таблицы. Поле ref показывает, какие поля или константы используются для запроса записей из таблицы в сочетании с ключом.

rows

В этом поле отображается количество записей набора результатов, оцененное оптимизатором запросов на основе статистики, собранной системой.

Extra

В этом поле отображается дополнительная информация о MySQL в запросе.

filtered

Этот тип столбца недавно добавлен в MySQL 5.1 и появляется только при использовании EXPLAIN EXTENDED. Он показывает пессимистическую оценку процента записей в таблице, удовлетворяющих условию (предложение WHERE или условие соединения).

3) переписывание SQL

EXPLAIN может отображать перезапись SQL в дополнение к плану выполнения. Так называемая перезапись SQL означает, что прежде чем MySQL оптимизирует оператор SQL, он перепишет оператор на основе некоторых принципов, чтобы облегчить последующему оптимизатору оптимизацию и создание лучшего плана выполнения. Эта функция используется в сочетании с EXPLAIN EXTENDED+SHOW WARNINGS. Проиллюстрируем на примере.

Из приведенного выше примера видно, что подзапрос IN в исходном операторе был переписан в способ связи между таблицами.

2. Статистика

Просмотр статистики также является важным шагом в оптимизации выписок. Статистика может быстро понять, каковы характеристики хранения объекта. Ниже описаны два основных типа статистики — таблицы и индексы.

1) Статистика таблицы — ПОКАЗАТЬ СТАТУС ТАБЛИЦЫ

Имя: имя таблицы
Механизм: тип механизма хранения таблицы (ISAM, MyISAM или InnoDB).
Row_format: формат хранения строк (фиксированный-фиксированный, динамический-динамический или сжатый-сжатый)
Ряды: количество строк. В некоторых механизмах хранения, таких как MyISAM и ISAM, они хранят точное количество записей. Однако в других механизмах хранения это может быть только приблизительное значение.
Avg_row_length: средняя длина строки.
Data_length: длина файла данных.
Max_data_length: максимальная длина файла данных.
Index_length: длина индексного файла.
Data_free: количество выделенных, но не использованных байтов.
Auto_increment: Следующее значение автоинкремента (автоинкремент на 1).
Create_time: время создания таблицы.
Update_time: время последнего обновления файла данных.
Check_time: Время последней проверки таблицы. Обновите после выполнения команды mysqlcheck, которая действительна только для MyISAM.
Create_options: Дополнительные параметры, зарезервированные для CREATE TABLE.
Комментарий: комментарий для использования при создании таблицы (или некоторая информация о том, почему MySQL не может получить доступ к информации таблицы).
Версия: номер версии файла .frm таблицы данных.
Сопоставление: набор символов и набор корректирующих символов таблицы.
Контрольная сумма: значение контрольной суммы в реальном времени (если есть).

3. Статистика индекса — ПОКАЗАТЬ ИНДЕКС

Таблица: имя таблицы.
Non_unique: 0, если индекс не может содержать дубликаты.
Key_name: имя индекса
Seq_in_index: порядковый номер столбца в индексе, начиная с 1.
Имя_столбца: имя столбца.
Сопоставление: способ сортировки столбца в индексе. В MySQL это может иметь значение A (в порядке возрастания) или NULL (без порядка).
Кардинальность: количество уникальных значений в индексе.
Sub_part: если столбец индексирован только частично, количество символов индекса. Когда все поле индексируется, его значение равно NULL.
Packed: указывает, как сжато значение ключа, NULL указывает на отсутствие сжатия.
Null: Когда поле содержит записи NULL, установлено значение YES, его значение равно ''.
Index_type: используемый алгоритм индексирования (BTREE, FULLTEXT, HASH, RTREE).
Комментарий: замечания.
Системные параметры: Системные параметры также влияют на эффективность выполнения оператора. Для просмотра системных параметров используйте команду SHOW VARIABLES.

1) Описание параметра

Существует множество системных параметров, некоторые из них описаны ниже.

sort_buffer_size

Размер области сортировки. Его размер напрямую влияет на используемый алгоритм сортировки. Если сортировка в системе относительно велика, памяти достаточно, а параллелизм не очень велик, этот параметр можно соответствующим образом увеличить. Этот параметр предназначен для одного Thead.

join_buffer_size

Операции соединения используют размер области памяти. Буфер присоединения используется только в том случае, если для присоединения установлено значение ALL, index, range или index_merge. Если имеется много операторов соединения, вы можете соответствующим образом увеличить join_buffer_size. Следует отметить, что это значение относится к одному потоку. Каждый поток будет создавать свой собственный независимый буфер вместо буфера, используемого всей системой. Не устанавливайте его слишком большим, так как это может привести к нехватке системной памяти.

tmp_table_size

Если временная таблица в памяти превышает это значение, MySQL автоматически преобразует ее в таблицу MyISAM на диске. Если вы выполняете много расширенных запросов GROUP BY и имеете много памяти, вы можете увеличить значение tmp_table_size.

read_buffer_size

Размер буфера, который может использоваться операциями запроса на чтение. Этот параметр предназначен для одного Thead.

4. Переключатель оптимизатора

В MySQL также есть параметры, которые можно использовать для управления поведением оптимизатора.

1) Описание параметра

optimizer_search_depth

Этот параметр управляет пределом оптимизатора, когда он исчерпывает план выполнения. Если запрос долгое время находится в состоянии «статистика», рассмотрите возможность уменьшения этого параметра.

optimizer_prune_level

По умолчанию он включен, что позволяет оптимизатору решать, следует ли пропускать определенные планы выполнения, в зависимости от количества строк, которые необходимо просмотреть.

optimizer_switch

Эта переменная содержит несколько флагов, которые включают/отключают функции оптимизатора.

Пример — вмешательство в работу оптимизатора (функция ICP)

По умолчанию функция ICP включена. Посмотрите на поведение оптимизатора.

Запрос фильтрации, основанный на вторичном индексе, использует функцию ICP, которую можно увидеть из «Использование условия индекса» в Extra. Что, если вы вмешаетесь в поведение оптимизатора с помощью переключателей оптимизатора?

Как видно из Extra, функция ICP отключена.

5. Состояние системы (ПОКАЗАТЬ СОСТОЯНИЕ)

В MySQL также есть несколько встроенных состояний, эти переменные состояния также могут отражать некоторые условия выполнения операторов, что удобно для локализации проблем. Если вы выполняете его вручную, вы можете выполнить команду SHOW STATUS до и после выполнения инструкции, чтобы проверить изменение состояния. Конечно, поскольку переменных состояния много, сравнивать неудобно, гаджеты, которые я представлю позже, могут решить эту проблему.

1) Переменная состояния

Существует много переменных состояния, вот некоторые из них.

Sort_merge_passes

Количество объединений, выполненных алгоритмом сортировки. Если значение этой переменной велико, рассмотрите возможность увеличения значения системной переменной sort_buffer_size.

Sort_range

Количество сортировок, выполненных в диапазоне.

Sort_rows

Количество уже отсортированных строк.

Sort_scan

Количество сортировок, сделанных при сканировании таблицы.

Handler_read_first

Сколько раз была прочитана первая запись в индексе. Количество прочтений заголовка индекса. Если это значение велико, это означает, что выполнено много полных просмотров индекса.

Handler_read_key

Количество запросов на чтение строки по ключу. Если выше, то запрос и индекс таблицы верны.

Handler_read_next

Количество запросов на чтение следующей строки в ключевом порядке. Это значение увеличивается, если вы запрашиваете индексированный столбец с ограничением диапазона или выполняете сканирование индекса.

Handler_read_prev

Количество запросов на чтение предыдущей строки в ключевом порядке.

Handler_read_rnd

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

Handler_read_rnd_next

Количество запросов на чтение следующей строки в файле данных. Это значение выше, если выполняется большое количество просмотров таблицы. Обычно указывает, что индекс таблицы неверен или что запрос был написан без использования индекса.

6. Профилировщик запросов

MySQL Query Profiler — это очень удобный инструмент для диагностики запросов.С помощью этого инструмента вы можете получить потребление различных ресурсов во время выполнения запроса, таких как CPU, IO, IPC, SWAP и т. д., а также возникновение ОШИБКИ СТРАНИЦЫ, ПЕРЕКЛЮЧЕНИЕ КОНТЕКСТА и т. д., в то же время вы также можете получить местоположение в исходном файле каждой функции, вызываемой MySQL во время выполнения запроса.

1) Как использовать

включи

mysql> select @@profiling;

mysql> set profiling=1;

По умолчанию значение профилирования равно 0, что означает, что MySQL SQL Profiler находится в состоянии OFF, а значение профилирования равно 1 после включения анализатора производительности SQL.

Выполнить оператор SQL

mysql> select count(*) from t1;

Получить сводную информацию

Используйте команду «показать профиль», чтобы получить сводную информацию о нескольких профилях запросов, сохраненных в текущей системе.

mysql> show profiles;

+----------+------------+-----------------------+

| Query_ID | Duration   | Query                   |

+----------+------------+-----------------------+

|        1 | 0.00039300 | select count(*) from t1 |

+----------+------------+-----------------------+

Получить подробную информацию о профиле для одного запроса

После получения сводной информации подробная информация о профиле во время выполнения определенного запроса может быть получена в соответствии с Query_ID сводной информации.

mysql> show profile for query 1;

mysql> show profile cpu,block io for query 1;

2. Описание инструмента

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

1. Условия приготовления

Модуль - MySQLDB

Модуль - sqlparse

Версия Python = 2.7.3 (версия 2.6.x должна подойти, версия 3.x не тестировалась)

2. Метод вызова

python mysql_tuning.py -p tuning_sql.ini -s 'select xxx'

1) Описание параметра

-p указывает имя файла конфигурации

-s указывает оператор SQL

3. Файл конфигурации

Существует два раздела информации, а именно [база данных], описывающая информацию о подключении к базе данных, и [опция] информация о текущей конфигурации.

1) [database]

server_ip   = 127.0.0.1

db_user     = testuser

db_pwd      = testpwd

db_name     = test

2) [option]

sys_parm    = ON     //是否显示系统参数

sql_plan    = ON //是否显示执行计划

obj_stat    = ON //是否显示相关对象(表、索引)统计信息

ses_status  = ON //是否显示运行前后状态信息(激活后会真实执行SQL)

sql_profile = ON   //是否显示PROFILE跟踪信息(激活后会真实执行SQL)

4. Описание выхода

1) Заголовок

Содержит адресную информацию и информацию о версии данных работающей базы данных.

2) Необработанный SQL

Пользователь выполняет введенный SQL, эта часть в основном используется для последующего сравнения перезаписи SQL. Оператор отображается с использованием форматирования.

3) Параметры системного уровня

Выбор сценария показывает некоторые параметры, связанные с производительностью SQL. Эта часть жестко закодирована в коде, и сценарий необходимо изменить, если его необходимо расширить.

4) Переключатель оптимизатора

Ниже приведены некоторые параметры, относящиеся к оптимизатору. Настраивая эти параметры, вы можете влиять на поведение оптимизатора.

5) План выполнения

Это результат вывода расширенного объяснения. Если результат слишком длинный, может быть проблема с серийным номером дисплея (пока не решена).

6) SQL переписан оптимизатором

Благодаря этому можно судить, выполнил ли оптимизатор какую-либо оптимизацию SQL (например, обработку подзапросов).

7) Статистика

Здесь будет отображаться статистика всех таблиц и их индексов, задействованных в операторе SQL.

8) Информация о рабочем состоянии

Состояние до и после выполнения (SHOW STATUS) сравнивается на уровне сеанса, и отображаются измененные части. Следует отметить, что, поскольку метод SELECT используется для сбора данных о состоянии, он вызовет ошибки в отдельных индикаторах (например, Com_select).

9) ПРОФИЛЬ детали

Подробная информация получена по телефону SHOW PROFILE.

10) Сводная информация ПРОФИЛЯ

По потреблению ресурсов PROFILE отображается сравнение потребления различных этапов (TOP N), а также визуально отображается «узкое место».

Адрес загрузки исходного файла:disk.baidu.com/is/1 — это LF3ZS5?…

Автор: Хан Фэн

Источник контента: Технологический институт CreditEase.