Процесс запроса MySQL и расширенный запрос

MySQL SQL

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

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

  • Зачем нужен индекс

  • индексированная категория

  • Эволюция индекса MySQL

  • Оптимизация индекса MySQL

  • Введение в HBase

  • Структура хранилища HBase

  • Введение в индексы HBase

  • Бизнес-требования и дизайн

Некоторые из контента извлекли несколько блоггеров, и, наконец, дает ссылку ссылку, благодаря своему замечательному анализу.

Через введение средней части вы узнаете:

  • Процесс запроса MySQL

  • Понятия, связанные с расширенными запросами

  • объясни команду подробно

  • Рекомендации по оптимизации индекса

Процесс запроса MySQL

Чтобы лучше оптимизировать запрос, вы должны сначала понять весь процесс запроса.От отправки клиентом запроса на запрос до получения результата запроса сервер MySQL проделал большую работу.

логическая архитектура

Логическая архитектура MySQL в целом разделена на три уровня: уровень клиента, уровень ядра службы и уровень механизма хранения, которые работают вместе для завершения.

Верхний уровень — это клиентский уровень, такой как обработка соединений, авторизация и аутентификация, безопасность и другие функции.

Средний уровень — это основная служба MySQL, включающая в себя синтаксический анализ запросов, анализ, оптимизацию, кэширование, встроенные функции (такие как: время, математика, шифрование и т. д.) Кроме того, также реализованы все функции кросс-хранилища. в этом слое: хранимые процедуры, триггеры, представления и т. д.

Нижний уровень — это подсистема хранения, которая отвечает за хранение и извлечение данных.Служебный уровень в середине взаимодействует с подсистемой хранения через API.Эти интерфейсы API скрывают различия между разными подсистемами хранения.

конкретный процесс исполнения

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

Позвольте мне поговорить об общем процессе:

  • Клиент отправляет SQL-запрос на сервер;

  • Сервер сначала проверяет кеш запроса, и если он попадает в кеш, он немедленно возвращает результат, хранящийся в кеше;

  • Сервер выполняет синтаксический анализ и предварительную обработку SQL, а затем оптимизатор генерирует соответствующий план выполнения;

  • Механизм выполнения запросов вызывает API механизма хранения для выполнения запроса в соответствии с планом выполнения, сгенерированным оптимизатором;

  • Вернуть результат клиенту;

1. Протокол связи клиент/сервер

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

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

2. Кэш запросов

Если кеш запросов открыт, он проверит, соответствует ли оператор запроса данным в кеше запросов.Если он попадает, результат в кеше будет возвращен сразу после проверки разрешения пользователя один раз.

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

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

Таким образом, вы должны быть осторожны при открытии кеша. Только когда экономия ресурсов, обеспечиваемая кешем, больше, чем ресурсы, которые он потребляет, это приведет к улучшению производительности системы. Вы можете установить для query_cache_type значение DEMAND, тогда только запросы, которые присоединяются к SQL_CACHE пойдет в кеш, других запросов не будет.

3. Разбор синтаксиса и предварительная обработка

Оператор SQL анализируется по ключевым словам для создания дерева синтаксического анализа, а предварительная обработка дополнительно проверяет, является ли дерево синтаксического анализа допустимым в соответствии с правилами MySQL.

4. Оптимизация запросов

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

5. Механизм выполнения запросов

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

6. Вернуть результат клиенту

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

ВЫБЕРИТЕ порядок выполнения

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

Стандартный синтаксис SQL выглядит следующим образом:

SELECT DISTINCT   < select_list >FROM   < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE   < where_condition >GROUP BY   < group_by_list >HAVING   < having_condition >ORDER BY   < order_by_condition >LIMIT < limit_number >скопировать код

Но порядок выполнения таков:

FROM   <left_table>ON <join_condition> <join_type> JOIN <right_table>WHERE   <where_condition>GROUP BY   <group_by_list>HAVING   <having_condition>SELECT DISTINCT   <select_list>ORDER BY   <order_by_condition>LIMIT   <limit_number>скопировать код

1.FROM

Когда задействовано несколько таблиц, выходные данные левой таблицы будут использоваться в качестве входных данных для правой таблицы, а затем будет сгенерирована виртуальная таблица VT1:

  • Вычислить декартово произведение (CROSS JOIN) двух связанных таблиц для создания виртуальной таблицы VT1-J1;

  • Отфильтровать на основе виртуальной таблицы VT1-J1, отфильтровать все строки, удовлетворяющие условию предиката ON, и сгенерировать виртуальную таблицу VT1-J2;

  • Если используется внешнее соединение (LEFT, RIGHT, FULL), столбцы в основной таблице (зарезервированной таблице), которые не соответствуют условию ON, также будут добавлены в VT1-J2 для создания виртуальной таблицы VT1-J3;

2.WHERE

Временная таблица, сгенерированная в процессе VT1, фильтруется, и столбцы, удовлетворяющие предложению WHERE, вставляются в таблицу VT2:

  • Разница с ON: если есть внешнее соединение, ON предназначено для фильтрации связанной таблицы, а основная таблица вернет все столбцы, если внешнего соединения нет, эффект тот же;

  • Фильтрация основной таблицы должна находиться в WHERE;

  • Для реляционных таблиц используйте ON для условного запроса, а затем соединения, и используйте WHERE для условного запроса после первого соединения;

3.GROUP BY

Это предложение сгруппирует таблицы, созданные в VT2, в соответствии со столбцами в GROUP BY для создания таблицы VT3.

Для операторов последующей обработки, таких как SELECT, HAVING, используемые столбцы должны быть включены в GROUP BY, для тех, которые не появляются, должны использоваться агрегатные функции;

4.HAVING

Отфильтруйте различные группы в таблице VT3 только для сгруппированных данных, и предложения, удовлетворяющие условию HAVING, будут добавлены в таблицу VT4.

5.SELECT

Это предложение обрабатывает элементы в предложении SELECT для создания таблицы VT5:

  • Оцените выражение в предложении SELECT, чтобы сгенерировать VT5-J1;

  • DISTINCT: Найдите повторяющийся столбец и удалите его, это создаст в памяти временную таблицу VT5-J2, которая совпадает с виртуальной таблицей VT5-J1, разница в том, что к столбцу DISTINCT добавляется уникальный индекс для удаления дубликатов. данные;

6.ORDER BY

Из таблиц в VT5-J2 результаты сортируются в соответствии с условиями предложения ORDER BY, в результате чего получается таблица VT6, которая является единственным местом, где можно использовать псевдонимы в SELECT.

7.LIMIT

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

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

Данные нескольких таблиц объединяются в соответствии с заданным условием.В SQL запросы на соединение делятся на четыре категории: внутреннее соединение, внешнее соединение, естественное соединение и перекрестное соединение.Среди них редко используются естественные соединения и перекрестные соединения. , так что я не буду их слишком много представлять. .

1. внутреннее соединение внутреннее соединение

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

2. Внешнее соединение левое/правое соединение

Существует два типа внешних соединений:

  • левое соединение: левое внешнее соединение (левое соединение), с левой таблицей в качестве основной таблицы;

  • правое соединение: правое внешнее соединение (правое соединение), с правой таблицей в качестве основной таблицы;

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

подзапрос

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

По расположению подзапроса его можно разделить на:

  • Из подзапроса: Подзапрос следует из;

  • Подзапрос «Где»: подзапрос находится в состоянии «где»;

  • существует подзапрос: подзапрос появляется в существует;

Вот несколько примеров:

Найдите всех сотрудников, название отдела которых имеет префикс «Xiaomi»:

SELECT name , sex ,  sal      FROM emp      WHERE no in (           SELECT no FROM dept               WHERE name LIKE '小米%'      )；скопировать код

Посмотреть зарплаты всех сотрудников, отсортированные по зарплате:

SELECT name , sal      FROM (          SELECT name , sal              FROM emp ORDER BY sal      )；скопировать код
совместный запрос

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

Синтаксис следующий:

Select 语句1Union [union选项]Select语句2...скопировать код

Варианты союза:

  • Все: сохранить все;

  • Отдельный: дедупликация, опция по умолчанию;

Я написал еще, давайте добавим еще один, роман еще не закончен, чтобы продолжать. . .

Справочная статья:

  1. Принцип оптимизации MySQL

  2. Шаг за шагом: порядок разбора SQL

END

Если вы чувствуете себя вознагражденным после прочтения, пожалуйста, поставьте лайк, подпишитесь и добавьте официальную учетную запись [Ingenuity Zero], чтобы узнать больше захватывающей истории! ! !

Категории