Операции запроса являются наиболее часто используемыми операциями в реляционных базах данных и составляют основу других операторов SQL (таких как DELETE и UPDATE). Если вы хотите удалить или обновить некоторые записи, вы должны сначала запросить эти записи, а затем выполнить над ними соответствующие операции SQL. Поэтому операции запросов на основе SELECT очень важны. Для обработки запросов его можно разделить налогический запрособработка иФизический запросиметь дело с.Логическая обработка запроса представляет, что должен дать результат выполнения запроса, в то время как физический запрос представляет, как база данных MySQL получила этот результат.. Два метода запроса могут быть совершенно разными, но результаты должны быть одинаковыми.
Логическая обработка запросов
Язык SQL отличается от других языков программирования (таких как C, C++, Java, Python), и наиболее очевидным отличием является порядок, в котором обрабатывается код. В большинстве языков программирования код обрабатывается в порядке кодирования. Но в языке SQL первым обрабатываемым предложением всегда является предложение FROM.
(8)SELECT (9)DISTINCT <select_list>
(1)FROM <left_table>
(3)<join_type>JOIN <right_table>
(2)ON<join_condition>
(4)WHERE<where_condition>
(5)GROUP BY<group_by_list>
(6)WITH {CUBE|ROLLUP}
(7)HAVING<having_condition>
(10)ORDER BY<order_by_list>
(11)LIMIT<limit_number>
Порядковый номер в операторе запроса — это порядок обработки оператора запроса.
Видно, что всего 11 шагов, операция FROM выполняется первой, а операция LIMIT выполняется последней.Каждая операция создает виртуальную таблицу, которая используется в качестве входных данных для процесса. Эти виртуальные таблицы прозрачны для пользователя, и пользователю будет возвращена только виртуальная таблица, сгенерированная на последнем шаге.. Если в запросе не указано условие, соответствующий шаг будет пропущен.
В частности, проанализируйте различные этапы обработки запроса:
-
FROM: Выполните декартово произведение для левой таблицы
и правой таблицы в предложении FROM, чтобы сгенерировать виртуальную таблицу VT1. - ON: применить фильтр ON к виртуальной таблице VT1, в виртуальную таблицу VT2 вставляются только те строки, которые соответствуют .
- JOIN: Если указано OUTER JOIN (например, LEFT OUTER JOIN, RIGHT OUTER JOIN), то несопоставленные строки в зарезервированной таблице добавляются в виртуальную таблицу VT2 как внешние строки, в результате чего получается виртуальная таблица VT3. Если предложение FROM содержит более двух таблиц, повторите шаги с 1) по 3) для таблицы результатов VT3, сгенерированной предыдущим соединением, и следующей таблицы, пока не будут обработаны все таблицы.
- WHERE: применить условие фильтра WHERE к виртуальной таблице VT3, в виртуальную таблицу VT4 вставляются только записи, соответствующие .
- GROUP BY: В соответствии со столбцами в предложении GROUP BY сгруппируйте записи в VT4 для создания VT5.
- CUBE|ROLLUP: Выполните операцию CUBE или ROLLUP для таблицы VT5, чтобы сгенерировать таблицу VT6.
- HAVING: применить фильтр HAVING к виртуальной таблице VT6, и в виртуальную таблицу VT7 будут вставлены только записи, соответствующие .
- SELECT: выполнить операцию SELECT во второй раз, выбрать указанный столбец и вставить его в виртуальную таблицу VT8.
- DISTINCT: Удалить повторяющиеся данные и создать виртуальную таблицу VT9
-
ORDER BY: Отсортируйте записи в виртуальной таблице VT9 в соответствии с
для создания виртуальной таблицы VT10. 11) 11.LIMIT: извлечь запись указанной строки, сгенерировать виртуальную таблицу VT11 и вернуть ее пользователю запроса.
11 этапов логической обработки подробно описаны ниже на примере запроса. Во-первых, в соответствии со следующим кодом создайте таблицу пользовательских данных клиентов и заказов и заполните определенный объем данных.
create table customers
(
customer_id VARCHAR(10),
city VARCHAR(10) NOT NULL,
PRIMARY KEY(customer_id)
)ENGINE=InnoDB;
INSERT INTO customers VALUES('163', 'HangZhou'),('9you','ShangHai'),('TX','HangZhou'),('baidu','HangZhou');
create table orders
(
order_id INT AUTO_INCREMENT,
customer_id VARCHAR(10),
PRIMARY KEY(order_id)
)ENGINE=InnoDB;
INSERT INTO orders VALUES(1, '163'),(2, '163'),(3, '9you'),(4, '9you'),(5, '9you'),(6, 'TX'),(7, NULL);
записи таблицы клиентов
записи в таблице заказов
[Используйте следующий оператор, чтобы запросить клиентов из Ханчжоу с менее чем двумя заказами и запросить количество их заказов. Результаты запроса отсортированы по количеству заказов от меньшего к большему]
SELECT c.customer_id,count(o.order_id) AS total_orders
FROM customers as c
LEFT JOIN orders as o
ON c.customer_id = o.customer_id
WHERE c.city = 'HangZhou'
GROUP BY c.customer_id
HAVING count(o.order_id) < 2
ORDER BY total_orders DESC;
Клиенты из Ханчжоу с менее чем 2 заказами
Далее анализируется процесс выполнения SQL.
(1) Выполнить декартово произведение Первый шаг — выполнить операцию декартова произведения над двумя таблицами до и после предложения FROM, также известную как перекрестное соединение (CrossJoin), для создания виртуальной таблицы VT1. Если таблица перед предложением FROM содержит строку данных, а таблица после предложения FROM содержит b строк данных, то виртуальная таблица VT1 будет содержать a*b строк данных. Столбцы виртуальной таблицы VT1 определяются исходной таблицей. Для предыдущего оператора SQL-запроса сначала будет выполняться операция декартова произведения таблиц заказов и клиентов.
FROM customers as c ....... JOIN orders as o
Виртуальная таблица VT1, возвращенная декартовым произведением
(2) Применить ВКЛ фильтр Запрос SELECT имеет всего 3 процесса фильтрации, а именно ON, WHERE и HAVING. ON — это первый выполняемый процесс фильтрации. В соответствии с виртуальной таблицей VT1, созданной в предыдущем разделе, условия фильтрации таковы:
ON c.customer_id = o.customer_id
Для большинства языков программирования существует только два значения логических выражений: ИСТИНА и ЛОЖЬ. Но в реляционных базах данных существует не только два вида логических выражений, но и выражение, называемое трехзначной логикой. Это связано с тем, что значения NULL сравниваются в базах данных иначе, чем в большинстве языков программирования. В языке C сравнение NULL == NULL возвращает 1, что равно, а в реляционной базе данных сравнение NULL вообще не имеет места, например:Сравнение первого значения NULL возвращает NULL вместо 0, а сравнение второго значения NULL по-прежнему возвращает NULL вместо 1. В случае, когда сравнение возвращает NULL, пользователь должен рассматривать его как UNKNOWN, что означает неизвестное. Потому что в некоторых случаях возвращаемое значение NULL может представлять 1, то есть NULL равно NULL, а иногда возвращаемое значение NULL может представлять 0.
Для сравнения значений NULL при условии фильтра ON результат сравнения в это время НЕИЗВЕСТЕН, но обрабатывается как FALSE, то есть два значения NULL не совпадают. Однако сравнение двух значений NULL считается равным в следующих двух случаях:
- Предложение GROUP BY группирует все значения NULL в одну группу
- Упорядочить все значения NULL вместе в предложении ORDER BY (Вы можете проверить это сами)
Поэтому при создании виртуальной таблицы VT2 будет добавлен дополнительный столбец для представления возвращаемого значения условия фильтра ON, а возвращаемые значения — TRUE, FALSE, UNKNOWN. Выньте запись, значение сравнения которой равно TRUE, и сгенерируйте виртуальную таблицу VT2.
виртуальная таблица VT2
(3) Добавить внешнюю строку
customers as c LEFT JOIN orders as o
Этот шаг выполняется только в том случае, если тип соединения — ВНЕШНЕЕ СОЕДИНЕНИЕ, например, ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Хотя в большинстве случаев мы можем опускать ключевое слово OUTER, OUTER обозначает внешнюю строку. LEFT OUTER JOIN записывает левую таблицу как зарезервированную таблицу, RIGHT OUTER JOIN записывает правую таблицу как зарезервированную таблицу, а FULL OUTER JOIN записывает как левую, так и правую таблицы как зарезервированные таблицы. Задача добавления внешней строки состоит в том, чтобы добавить данные, отфильтрованные по условиям фильтра, в зарезервированную таблицу на основе таблицы VT2, а данные в незарезервированной таблице получают значение NULL, и, наконец, виртуальную таблицу Генерируется VT3.
виртуальная таблица VT3В этом примере зарезервированная таблица — это клиенты, а baidu клиентов фильтруется в таблице VT2, потому что нет порядка, поэтому baidu добавляется в виртуальную таблицу VT2 как внешняя строка, а данные в незарезервированной таблице присвоен NULL. Если требуемое количество таблиц подключения больше 2, повторите шаги (1)–(3) в начале этого раздела для виртуальной таблицы VT3, и последняя виртуальная таблица будет использоваться в качестве выходных данных следующего шага.
(4) Применить ГДЕ фильтр
Выполните фильтрацию условия WHERE для виртуальной таблицы VT3, созданной на предыдущем шаге, и только записи, соответствующие
Есть два типа фильтрации, которые не разрешены, когда в данный момент применяется фильтр WHERE:
- Поскольку данные не были сгруппированы, пока невозможно использовать where_condition=MIN(col) в фильтре WHERE для фильтрации статистики.
- Поскольку операции выбора столбца нет, использование псевдонимов столбцов в SELECT не допускается, например, SELECT city as c FROM t WHERE c='ShangHai' не допускается.
См. пример ошибки, вызванной использованием запроса группового фильтра в условии фильтра WHERE.
SELECT customer_id,count(customer_id)
FROM orders
WHERE COUNT(customer_id)<2;
Вы можете видеть, что база данных MySQL сообщает о неправильном использовании функции группировки. Давайте рассмотрим пример неправильного использования псевдонимов столбцов:
SELECT order_id as o, customer_id as c
FROM orders
WHERE c='163';
Поскольку операция SELECT для выбора имени столбца не была выполнена на текущем шаге, псевдоним столбца в настоящее время не поддерживается, и база данных MySQL выдает ошибку, указывающую на неизвестный столбец c.
Примените фильтр WHERE: WHERE c.city='HangZhou' и, наконец, получите виртуальную таблицу VT4.
Виртуальный стол VT4Кроме того, существует разница между фильтрацией в фильтре WHERE и фильтрацией в фильтре ON. Для фильтрации в OUTERJOIN после фильтрации по фильтру ON будут добавлены записи, отфильтрованные по условию ON в зарезервированной таблице, а записи, отфильтрованные по условию WHERE, фильтруются постоянно.
(5) Группировка На этом шаге виртуальная таблица, созданная на предыдущем шаге, группируется в соответствии с указанным столбцом, и, наконец, получается виртуальная таблица VT5.
GROUP BY c.customer_id
SELECT * FROM customers as c LEFT JOIN orders as o ON c.customer_id = o.customer_id WHERE c.city='HangZhou' GROUP BY c.customer_id
виртуальная таблица VT5
(6) Применить ROLLUP или CUBE Если указана опция ROLLUP, то будет создана дополнительная запись и добавлена в конец виртуальной таблицы VT5, и будет сгенерирована виртуальная таблица VT6. Этот шаг будет пропущен, так как наш запрос не использует ROLLUP.
(7) Применить фильтр ИМЕЮЩИЙ Это последний условный фильтр, фильтры ON и WHERE применялись ранее. На этом шаге фильтр HAVING применяется к виртуальной таблице, созданной на предыдущем шаге, где HAVING — это фильтр для фильтрации условий группировки. Для оператора запроса из примера его условие группировки:
HAVING count(o.order_id < 2)
SELECT c.customer_id,c.city,o.order_id,o.customer_id FROM customers as c LEFT JOIN orders as o ON c.customer_id = o.customer_id WHERE c.city = 'HangZhou' GROUP BY c.customer_id HAVING count(o.order_id)<2;
Поэтому заказ с customer_id 163 удаляется из виртуальной таблицы, а сгенерированная виртуальная таблица VT6
виртуальная таблица VT6Важно отметить, что COUNT(1) или COUNT(*) нельзя использовать в этой группировке, потому что это приведет к подсчету строк, добавленных через OUTER JOIN, и приведет к тому, что окончательный результат запроса будет отличаться от ожидаемого результата. В этом примере можно использовать только COUNT o.order_id для получения ожидаемого результата.
Примечание. Подзапросы нельзя использовать в качестве агрегатных функций для группировки, например HAVING COUNT(SELECT ...)
(8) Обработка списка SELECT Хотя SELECT является первой частью указанного запроса, фактически он не обрабатывается до шага 8). На этом шаге столбцы, указанные в SELECT, выбираются из виртуальной таблицы, созданной на предыдущем шаге. Часть ВЫБОР:
SELECT c.customer_id,count(o.customer_id) AS total_orders
SELECT c.customer_id,count(o.customer_id) AS total_orders FROM customers as c LEFT JOIN orders as o ON c.customer_id = o.customer_id WHERE c.city = 'HangZhou' GROUP BY c.customer_id HAVING count(o.order_id)<2;
виртуальная таблица VT7
(9) Примените предложение DISTINCT Если в запросе указано предложение DISTINCT, создается временная таблица в памяти (или на диске, если она не помещается в памяти). Структура этой временной таблицы в памяти такая же, как у виртуальной таблицы, созданной на предыдущем шаге, с той разницей, что к столбцу добавляется уникальный индекс для операции DISTINCT для удаления повторяющихся данных.
Пропустите этот шаг, так как в этом SQL-запросе не было указано DISTINCT. Кроме того, для запросов, использующих GROUP BY, использование DISTINCT является излишним, поскольку после группировки строки не удаляются.
(10) Примените предложение ORDER BY Упорядочивает выходные данные виртуальной таблицы на предыдущем шаге в соответствии со столбцами, указанными в предложении ORDER BY, и возвращает новую виртуальную таблицу. Вы также можете указать порядковые номера столбцов в списке SELECT в предложении ORDER BY, как в следующем операторе:
SELECT order_id,customer_id
FROM orders
ORDER BY 2,1;
等同于
SELECT order_id,customer_id
FROM orders
ORDER BY customer_id,order_id;
Обычно этот метод не рекомендуется для сортировки, поскольку программист может изменить столбцы в списке SELECT и забыть изменить список в ORDER BY. Однако, если у пользователя высокие требования к передаче по сети, это также способ сэкономить байты передачи по сети.
Например, предложение ORDER BY:
ORDER BY total_orders DESC
SELECT c.customer_id,count(o.customer_id) AS total_orders FROM customers as c LEFT JOIN orders as o ON c.customer_id = o.customer_id WHERE c.city = 'HangZhou' GROUP BY c.customer_id HAVING count(o.order_id)<2 ORDER BY total_orders DESC;
Наконец получить виртуальную таблицуЯ полагаю, что многие администраторы баз данных и разработчики ошибочно полагают, что при выборке данных в таблице записи будут выбираться последовательно в соответствии с размером первичного ключа в таблице, то есть результат будет таким же, как при ORDER BY. Основная причина этой классической ошибки — непонимание того, что такое настоящая реляционная база данных.
Реляционные базы данных разрабатываются на основе математики, а отношения соответствуют понятию множеств в математике. Обычные операции запросов в базах данных на самом деле соответствуют определенным операциям над множествами: выборке, проецированию, соединению, объединению, пересечению, различию и делению. Хотя окончательный результат представляется пользователю в виде двумерной таблицы, он представляет собой серию операций над множествами изнутри базы данных. Поэтому для записей в таблице пользователям необходимо понимать идею сбора
Потому что данные в таблице — это элементы коллекции, а коллекция неупорядочена. Следовательно, для оператора SQL без предложения ORDER BY результат синтаксического анализа должен быть следующим: выберите нужную вложенную коллекцию из коллекции. Это говорит о том, что результаты не обязательно должны быть упорядочены.
Примечание. В базе данных MySQL значение NULL всегда выбирается первым в восходящем процессе, то есть значение NULL рассматривается как минимальное значение в предложении ORDER BY.
(11) ОГРАНИЧЕНИЕ Примените предложение LIMIT на этом шаге, чтобы выбрать указанные данные строки, начиная с указанной позиции, из виртуальной таблицы предыдущего шага. Для предложений LIMIT, которые не применяют ORDER BY, результат также может быть неупорядоченным, поэтому предложение LIMIT обычно используется с предложением ORDER BY.
Поскольку в инструкции SQL в этом примере нет предложения LIMIT, окончательный результат должен быть следующим:
Физическая обработка запросов
Логическая обработка запросов была представлена выше и описывала, какие результаты должны быть получены при выполнении запроса. Но запросы к базе данных могут быть не совсем такими, как обрабатываются логические запросы. Мы знаем, что на уровне базы данных MySQL есть два компонента: Parser и Optimizer. Работа синтаксического анализатора заключается в анализе оператора SQL, а работа оптимизатора заключается в оптимизации оператора SQL и выборе оптимального пути для выбора данных, но необходимо гарантировать, что окончательный результат обработки физического запроса и обработки логического запроса равны
Если в таблице есть индекс, оптимизатор определит, можно ли оптимизировать оператор SQL с помощью индекса. Если индекса, который можно было бы использовать, нет, стоимость выполнения всего оператора SQL может быть очень высокой. Можно привести пример:
CREATE TABLE x(
a int
)ENGINE=InnoDB;
CREATE TABLE y(
a int
)ENGINE=InnoDB;
Предположим, что данные 10w и 18w вставлены в таблицы x и y соответственно, и в двух таблицах не созданы индексы, поэтому окончательный результат выполнения анализа синтаксического анализатора SQL является логическим этапом обработки, то есть согласно приведенному выше анализу, после в общей сложности 11 шагов для запроса данных. Сначала создается виртуальная таблица VT1 на основе декартова произведения.Таблица x имеет 100 000 строк данных, а таблица y имеет 180 000 строк данных, что означает, что виртуальная таблица VT1, сгенерированная после декартова произведения, имеет всего 18 миллиард строк данных! Таким образом, для запуска этого SQL-оператора на двухъядерном ноутбуке буферный пул InnoDB настроен на 128 МБ, и в целом выполнение занимает более 50 минут.
Кто-то может возразить, что буферный пул InnoDB размером 128 МБ слишком мал, чтобы хранить в памяти столько данных, что их выполнение занимает так много времени. На самом деле размер таблицы x и таблицы y не превышает 20 МБ, что достаточно для хранения в буферном пуле памяти объемом 128 МБ.Основная причина низкой скорости выполнения оператора заключается в том, что ему необходимо сгенерировать 18 миллиардов раз данных. . Даже многократная генерация данных в памяти занимает много времени. Однако, если вы добавите значение первичного ключа в таблицу y в это время, а затем выполните этот оператор SQL, вы будете удивлены, обнаружив, что это занимает менее 1 секунды.
Производительность более чем в 3000 раз выше! Причина такого значительного сокращения времени запроса заключается просто в том, что добавление индекса позволяет избежать создания декартовой таблицы, что значительно сокращает время выполнения оператора. Мы можем использовать команду EXPLAIN, чтобы увидеть, как база данных MySQL на самом деле выбирает выполнение после оптимизации оптимизатором SQL. Относительно индексов в MySQL и значения каждого столбца в плане выполнения EXPLAIN я также составил перед статьей "Статья для ознакомления с индексами myql"
{
"code": 1,
"result": false,
"message": "请先登录",
"meta": {
"request_time": 0.093,
"timestamp": 1594522447
}
}