предисловие
Привет всем, ямаленький мальчик собирает улиток.
В повседневной разработке мы часто используемgroup by. Дорогие друзья, вы знаетеgroup byКак это работает?group byиhavingкакие отличия есть?group byВ чем идея оптимизации? использоватьgroup byКакие вопросы требуют внимания? Эта статья будет учиться и побеждать вместе с вамиgroup by~
- Простой пример с использованием группы по
- Как работает группировка
- Разница между группировкой по + где и группировкой по + наличием
- сгруппировать по идеям оптимизации
- Примечания по использованию group by
- Как оптимизировать производственный медленный SQL
публика:маленький мальчик собирает улиток
1. Простой пример с использованием group by
group byобычно используется дляСтатистика группы, логика, которую он выражает,根据一定的规则，进行分组. Давайте начнем с простого примера и рассмотрим его вместе.
Предположим, что используется таблица сотрудников, и структура таблицы следующая:
CREATE TABLE `staff` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`id_card` varchar(20) NOT NULL COMMENT '身份证号码',
`name` varchar(64) NOT NULL COMMENT '姓名',
`age` int(4) NOT NULL COMMENT '年龄',
`city` varchar(64) NOT NULL COMMENT '城市',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';
Данные таблицы инвентаризации выглядят следующим образом:
Теперь у нас есть такое требование:Подсчитайте количество сотрудников в каждом городе. Соответствующий оператор SQL можно записать следующим образом:
select city ,count(*) as num from staff group by city;
Результат выполнения следующий:
Логика этого оператора SQL очень ясна, но каков основной поток его выполнения?
2. Группировка по принципу анализа
2.1 объяснить анализ
мы сначала используемexplainОзнакомьтесь с планом выполнения
explain select city ,count(*) as num from staff group by city;
- Дополнительно этого поля
Using temporaryУказывает, что он используется при выполнении группировкиВременные таблицы - Дополнительно этого поля
Using filesortуказывает на то, что он был использованСортировать
group byкак пользоваться临时表和排序какие? Давайте посмотрим на поток выполнения этого SQL.
2.2 Простой процесс выполнения группы по
explain select city ,count(*) as num from staff group by city;
Давайте посмотрим на процесс выполнения этого SQL.
- Создайте временную таблицу памяти с двумя полями в таблице
cityиnum; - полное сканирование таблицы
staff, и извлеките записи с городом = 'X' по очереди.
- судитьВременные таблицыЕсли есть строка с city='X', вставьте запись, если нет (X,1);
- Если во временной таблице есть строка с city='X', добавьте 1 к числовому значению строки x;
- После завершения обхода, согласно полю
cityДелатьСортировать, получить результирующий набор и вернуть его клиенту.
Схема выполнения этого процесса выглядит следующим образом:
Каков порядок временной таблицы?
Это помещение полей, которые необходимо отсортировать, в буфер сортировки и возврат после сортировки. Обратите внимание здесь, сортировка точекСортировать по всем полямиСортировать по идентификатору строки
- если
全字段排序, поля, которые должны быть возвращены запросом, помещаются вsort buffer,в соответствии сПоле сортировкиГотово, возвращайтесь прямо- если
rowid排序, просто поместите поля, которые необходимо отсортировать, вsort buffer, то еще разформа возвратаоперации, а затем вернуться.- Как определить, следует ли использовать сортировку по полному полю или сортировку по строке? управляется параметром базы данных,
max_length_for_sort_data
Для тех, кому интересно узнать больше о сортировке, вы можете прочитать мою статью.
3. Разница между где и иметь
- Процесс выполнения группы по + где
- Процесс выполнения группы + наличие
- В то же время есть порядок выполнения где, группировать и иметь
3.1 Процесс выполнения группы по + где
Некоторые друзья считают, что SQL в предыдущем разделе слишком прост, если вы добавите егогде условиеПосле этого и столбец условия where индексируется,Как проходит процесс исполнения?
Хорошо, давайте добавим к нему условие и добавимidx_ageиндекс следующим образом:
select city ,count(*) as num from staff where age> 30 group by city;
//加索引
alter table staff add index idx_age (age);
Давайте еще раз проанализируем:
explain select city ,count(*) as num from staff where age> 30 group by city;
Из результатов плана выполнения объяснения видно, что условие запроса выполнено.idx_ageиндекс и использует临时表和排序
Using index condition: указывает, что индекс смещается для оптимизации, максимально возможной фильтрации данных в соответствии с индексом, а затем возврата их на серверный уровень для фильтрации в соответствии с другими условиями. Почему здесь есть индекс для одного индекса? Появление объяснения не обязательно означает, что используется проталкивание индекса, это просто означает, что его можно использовать, но оно не обязательно используется. Если у вас есть идеи или вопросы, вы можете добавить меня в WeChat для обсуждения.
Последовательность выполнения следующая:
- Создайте временную таблицу памяти с двумя полями в таблице
cityиnum; - сканировать дерево индексов
idx_age, найти идентификатор первичного ключа больше, чем возраст больше 30 - Через идентификатор первичного ключа вернитесь к таблице, чтобы найти город = 'X'
- судитьВременные таблицыЕсли есть строка с city='X', вставьте запись, если нет (X,1);
- Если во временной таблице есть строка с city='X', добавьте 1 к числовому значению строки x;
- Продолжайте повторять шаги 2 и 3, чтобы найти все данные, соответствующие условиям.
- Наконец, по полю
cityДелатьСортировать, получить результирующий набор и вернуть его клиенту.
3.2 Выполнение группы + наличие
Если вы хотите запросить количество сотрудников в каждом городе и получить города, в которых количество сотрудников не менее 3, наличие может очень хорошо решить вашу проблему.SQL Jiangzi написал:
select city ,count(*) as num from staff group by city having num >= 3;
Результаты запроса следующие:
havingНазываемый группирующим фильтром, он работает с возвращаемым набором результатов.
3.3 Порядок выполнения где, группировать по и иметь одновременно
Если SQL содержит обаwhere、group by、havingстатьи, каков порядок исполнения?
Например, этот SQL:
select city ,count(*) as num from staff where age> 19 group by city having num >= 3;
- воплощать в жизнь
whereпункт для поиска данных для сотрудников, возраст которых больше 19 лет -
group byпункт о данных сотрудников, сгруппированных по городам. - правильно
group byГородская группа, образованная пунктом, запускает агрегатную функцию для расчета количества сотрудников в каждой группе; - последнее использование
havingПункт выбирает городские группы с 3 или более сотрудниками.
3.4 где + наличие сводки различий
-
havingоговорка дляФильтр после группировки, где условие дляРядУсловный фильтр -
havingв целом сотрудничатьgroup byпоявляется с агрегатными функциями, такими как (count(),sum(),avg(),max(),min()) -
whereАгрегатные функции нельзя использовать в условных предложениях, в то время какhavingпункт. -
havingМожет использоваться только после group by, где выполняется до group by
4. Проблемы с группировкой
Основные моменты, на которые следует обратить внимание при использовании группировки:
-
group byДолжен ли он использоваться с агрегатными функциями? -
group byПоле должно появиться в списке -
group byВызвано проблемами с медленным SQL
4.1 Нужно ли использовать group by с агрегатными функциями?
группировать поСтатистика группыЭто означает, что общий случай заключается в сотрудничестве с функцией агрегации. 如（count(),sum(),avg(),max(),min())использовать вместе.
- порядковый номер
- сумма() сумма
- среднее() среднее
- max() максимальное значение
- min() минимальное значение
Можно ли его использовать без агрегатной функции?
я используюMysql 5.7,разрешено. Об ошибке не сообщается, и возвращается первая строка данных группы.
Например, этот SQL:
select city,id_card,age from staff group by city;
Результат запроса
Давайте сравним это, возвращаются первые данные каждой группы
Конечно, когда вы обычно используете его, группировка по-прежнему используется с функцией агрегирования, если только нет каких-то особых сценариев, таких как вы хотитедедупликация, конечно для повторного использованияdistinctэто тоже хорошо.
4.2 Поля, за которыми следует группа, должны появиться при выборе.
Не обязательно, например следующий SQL:
select max(age) from staff group by city;
Результат выполнения следующий:
поле группировкиcityНе после выбора, и об ошибке не будет сообщено. Конечно, это можетДругая база данных, другая версияОб этом. Когда вы используете его, вы можете сначала проверить его. Есть поговорка под названиемНа бумаге я чувствую себя поверхностным в конце, и я абсолютно точно знаю, что это дело должно быть сделано..
4.3 group byВызвано проблемами с медленным SQL
Теперь к самому важному пункту внимания,group byНеправильное использование, легко генерировать медленный SQL
проблема. потому что он использует обаВременные таблицы, который используется по умолчаниюСортировать. Иногда также можно использоватьвременная таблица диска.
- Если в процессе выполнения вы обнаружите, что размер временной таблицы памяти достигверхний предел(Параметр, управляющий этим верхним пределом,
tmp_table_size), будетПреобразование временной таблицы памяти во временную таблицу диска.- Если объем данных велик, вполне вероятно, что временная таблица на диске, необходимая для этого запроса, займет много места на диске.
Все эти x-факторы приводят к медленному SQL. Давайте вместе обсудим решения по оптимизации.
5. Некоторые схемы оптимизации группировки по
В каком направлении оптимизировать?
- Направление 1: Поскольку он будет отсортирован по умолчанию, давайте не ранжировать его.
- Направление 2: Поскольку временная таблица является фактором X, влияющим на производительность группировки, можем ли мы не использовать временную таблицу?
Давайте подумаем об этом вместеgroup byЗачем оператору нужна временная таблица?group byСемантическая логика , заключается в подсчете количества вхождений различных значений. если этоЗначения упорядочены для начала, мы можем просто сканировать статистику напрямую, нам не нужноВременная таблица для записи и подсчета результатовЛа?
- Поле после group by индексируется
- упорядочить по нулю без сортировки
- Старайтесь использовать только временные таблицы в памяти
- Используйте SQL_BIG_RESULT
5.1 Добавьте индекс к полю после группы
Как гарантироватьgroup byСледующие значения полей упорядочены с начала? Конечно, это являетсяпоказательЛа.
Вернемся к этому SQL
select city ,count(*) as num from staff where age= 19 group by city;
его план выполнения
Если мы добавим к нему совместный индексidx_age_city（age,city）
alter table staff add index idx_age_city(age,city);
Еще раз взглянув на план выполнения, я обнаружил, что ни сортировка, ни временные таблицы не нужны.
добавить соответствующий индексоптимизированgroup byСамый простой и эффективный способ оптимизации.
5.2 упорядочить по нулю без сортировки
Не все сценарии подходят для индексации, если мы столкнемся со сценарием, который не подходит для создания индекса, как мы можем его оптимизировать?
Если ваши потребности не требуют сортировки набора результатов, вы можете использовать
order by null.
select city ,count(*) as num from staff group by city order by null
План выполнения следующий, нетfilesortЛа
5.3 Старайтесь использовать только временные таблицы в памяти
еслиgroup byДанных для подсчета не так много, мы можем попробовать использовать толькоВременная таблица в памяти; Потому что, если группа по процессу использует временную таблицу на диске из-за того, что данные не могут быть размещены, это отнимает много времени. Поэтому его можно соответствующим образом отрегулировать.tmp_table_sizeпараметры, чтобы не использоватьвременная таблица диска.
5.4 Оптимизация с помощью SQL_BIG_RESULT
Что делать, если объем данных слишком велик? Нельзя бесконечно увеличиватьtmp_table_sizeБар? Но вы не можете сначала просто просмотреть данные, помещаемые во временную таблицу памяти,С вставленными даннымиВыясняется, что достигнут верхний предел, а потом превращать его в дисковую временную таблицу? Это немного неразумно.
Поэтому, если предполагаемый объем данных относительно велик, мы используемSQL_BIG_RESULTЭта подсказка напрямую использует временные таблицы диска. Оптимизатор MySQl обнаружил, что временная таблица диска хранится в дереве B+, а эффективность хранения не так высока, как у массива. Следовательно, он будет храниться непосредственно в массиве
Пример SQ1 выглядит следующим образом:
select SQL_BIG_RESULT city ,count(*) as num from staff group by city;
план выполненияExtraКак видно из поля, выполнение не использует временную таблицу, а только сортирует
Последовательность выполнения следующая:
- Инициализируйте sort_buffer и поместите его в поле города;
- Сканируем таблицу staff, вынимаем по очереди значения city и сохраняем их в sort_buffer;
- После завершения сканирования отсортируйте поле города в sort_buffer.
- После завершения сортировки получается упорядоченный массив.
- В соответствии с упорядоченным массивом подсчитайте количество вхождений каждого значения.
6. Как оптимизировать производственный медленный SQL
Недавно я столкнулся с медленным производством SQL, связанным с группировкой, позвольте мне показать вам, как его оптимизировать.
Структура таблицы следующая:
CREATE TABLE `staff` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`id_card` varchar(20) NOT NULL COMMENT '身份证号码',
`name` varchar(64) NOT NULL COMMENT '姓名',
`status` varchar(64) NOT NULL COMMENT 'Y-已激活 I-初始化 D-已删除 R-审核中',
`age` int(4) NOT NULL COMMENT '年龄',
`city` varchar(64) NOT NULL COMMENT '城市',
`enterprise_no` varchar(64) NOT NULL COMMENT '企业号',
`legal_cert_no` varchar(64) NOT NULL COMMENT '法人号码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';
SQL для запроса таков:
select * from t1 where status = #{status} group by #{legal_cert_no}
Давайте не будем обсуждать, является ли разумным = в этом SQL. Если бы это был такой SQL, как бы вы его оптимизировали? Друзья, у которых есть идеи, могут оставить сообщение для обсуждения или добавить меня в WeChat и групповые обсуждения. Если вы считаете, что статья неверна, вы также можете поднять ее, давайте вместе добиваться прогресса, давай!