В прошлом Paodingjie Niu, но никогда не видел целую корову, полагаясь на свое понимание внутренней структуры скелета коровы, то же самое верно и для MySQL, только более всесторонне понимая каждый процесс выполнения оператора SQL, вы можете лучше спроектировать SQL и оптимизация. Если вы хотите, чтобы MySQL могла выполнять запрос с более высокой производительностью, лучше всего выяснить, как MySQL оптимизирует и выполняет запрос. Как только это будет понято, большая часть работы по оптимизации запросов фактически будет следовать нескольким принципам, которые работают, как и ожидалось, и являются разумными. Как показано на рисунке ниже, что именно делает MySQL при отправке запроса в MySQL:
- Клиент отправляет запрос на сервер.
- Сервер сначала проверяет кеш запроса, и если он попадает в кеш, он немедленно возвращает результат, хранящийся в кеше. В противном случае переходите к следующему этапу.
- Сервер выполняет синтаксический анализ и предварительную обработку SQL, а оптимизатор генерирует соответствующий план выполнения.
- В соответствии с планом выполнения, сгенерированным оптимизатором, MySQL вызывает API механизма хранения для выполнения запроса.
- Верните результат клиенту.
кэш запросов
Кэш запросов MySQL содержит полную структуру, возвращаемую запросом. Когда запрос попадает в кеш, MySQL немедленно возвращает результат, пропуская этапы синтаксического анализа, оптимизации и выполнения. Система кэширования запросов отслеживает каждую таблицу, участвующую в запросе, и если эти таблицы изменятся, все кэшированные данные, связанные с этой таблицей, станут недействительными. MySQL хранит кэш в справочной таблице, на которую ссылается хэш-значение.Это хеш-значение включает следующие факторы, а именно сам запрос, текущую запрашиваемую базу данных, версию клиентского протокола и другую информацию, которая может повлиять на возвращенный результат. При оценке попадания в кеш MySQL не будет анализировать оператор запроса, а напрямую использует оператор SQL и другую исходную информацию, отправленную клиентом. Следовательно, любое различие в символах, таких как пробелы, комментарии и т. д., приведет к промаху кеша. Если в операторе запроса есть какие-то неопределенные данные, они не будут кэшироваться. Например, запросы, содержащие функции NOW() или CURRENT_DATE(), не будут кэшироваться. Таблицы, содержащие любые пользовательские функции, хранимые функции, пользовательские переменные, временные таблицы, системные таблицы в базе данных mysql или таблицы, содержащие какие-либо разрешения на уровне столбца, не будут кэшироваться. Следует отметить, что MySQL не проверяет кеш запроса, поскольку запрос содержит неопределенную функцию, поскольку MySQL не будет анализировать оператор запроса до проверки кеша запроса, поэтому он не может знать, есть ли в операторе неопределенная функция. Дело в том, что если оператор запроса содержит какую-либо неопределенную функцию, результат запроса не будет закэширован, потому что соответствующий закэшированный результат не может быть найден в кеше запроса. Конфигурация кэша запросов показана ниже.
- query_cache_type: открывать ли кеш запросов. Можно установить на ВЫКЛ., ВКЛ. и ПО ЗАПРОСУ. DEMAND означает, что в кэш запросов будут помещены только те операторы, которые явно указывают SQL_CACHE в операторе запроса.
- query_cache_size: общий объем памяти, используемый кешем запросов.
- query_cache_min_res_unit: минимальная единица при выделении блоков памяти в кэше запросов. Меньшие значения могут уменьшить неиспользуемый объем памяти, вызванный фрагментацией, но приводят к более частым операциям с блоками памяти.
- query_cache_limit: максимальный результат запроса, который может запросить MySQL. Если результат запроса больше этого значения, он не будет кэшироваться. Поскольку кеш запроса пытается кэшировать данные, когда данные генерируются, MySQL знает, превышает ли результат запроса ограничение, только когда возвращаются все результаты. После этого результат удаляется из кэша запросов.
Оптимизация кэша запросов является важной частью оптимизации производительности базы данных. Процесс оценки примерно такой, как показано на рисунке ниже.
Коэффициент попаданий в кэш можно рассчитать по следующей формуле: Qcache_hits/(Qcache_hits + Com_select).
Разбор и предварительная обработка
Синтаксический анализатор анализирует операторы SQL по ключевым словам и создает соответствующие деревья разбора. Синтаксический анализатор MySQL проверит и разберет запрос, используя правила синтаксиса MySQL. Препроцессор далее проверяет, является ли проанализированная книга допустимой в соответствии с некоторыми правилами MySQL, например, проверяет, существуют ли таблица данных и столбец данных, а также анализирует имя и псевдоним, чтобы увидеть, не являются ли они неоднозначными.
оптимизатор запросов
Оптимизатор запросов преобразует дерево синтаксического анализа в план выполнения. Запрос может иметь несколько методов выполнения, каждый из которых в конце возвращает один и тот же результат. Роль оптимизатора состоит в том, чтобы найти среди них наилучший план выполнения. Процесс создания плана выполнения займет больше времени, особенно при наличии большого количества необязательных планов выполнения. Если окончательный план выполнения, соответствующий оператору SQL, кэшируется во время выполнения оператора, когда аналогичный оператор снова вводится на сервер, кэшированный план выполнения может использоваться напрямую, тем самым пропуская весь план выполнения, сгенерированный оператором SQL. , что, в свою очередь, может повысить скорость выполнения оператора.
MySQL использует оптимизатор запросов на основе затрат (Cost-Based Optimizer, CBO). Он пытается предсказать стоимость запроса, используя определенный план выполнения, и выбирает наименее дорогой. Оптимизатор преобразует реляционное выражение в соответствии с правилами оптимизации. Преобразование здесь означает, что реляционное выражение сгенерирует другое реляционное выражение после прохождения правил оптимизации. В то же время исходное выражение также будет сохранено. Множественные планы выполнения, а затем CBO рассчитает стоимость каждого плана выполнения в соответствии со статистической информацией и моделью затрат (Cost Model) и выберет план выполнения с наименьшей стоимостью. Как видно из вышеизложенного, в CBO есть две зависимости: статистическая информация и стоимостная модель. Является ли статистическая информация точной или нет, а модель затрат разумной или нет, повлияет на выбор CBO оптимального плана. Принцип работы оптимизатора очень сложен, поэтому я не буду здесь его подробно объяснять, вы можете изучить его самостоятельно.
механизм выполнения запросов
На этапе анализа и оптимизации MySQL сгенерирует план выполнения, соответствующий запросу, а механизм выполнения запросов MySQL выполнит весь запрос в соответствии с планом выполнения. План выполнения здесь представляет собой структуру данных, а не создание соответствующих байт-кодов, как в других реляционных базах данных.
Возвращаем результат клиенту
Если запрос можно кэшировать, то MySQL на этом этапе сохранит результат в кеше запросов. MySQL возвращает результирующий набор клиенту в виде постепенного пошагового процесса. Когда запрос сгенерирует первый результат, MySQL может начать постепенно возвращать набор результатов клиенту.
Подпишитесь на последние статьи, добро пожаловать в мою публичную учетную запись WeChat
Ссылаться на
- Принцип работы SQL Optimizer — Обзор оптимизатора запросовzhuanlan.zhihu.com/p/40478975
- «Высокопроизводительный MySQL»
- "Инсайдер технологии MySQL - механизм хранения InnoDB"