Групповой оптимизационный тур

задняя часть MySQL

Недавно я обнаружил интересную проблему при оптимизации системы отчетности CRM моей компании. После агрегированной статистики школьных групп, когда диапазон запроса превышает определенный период времени, время выполнения этого SQL более чем в 10 раз хуже исходного. Есть почти миллионы онлайн данных. Структура десенсибилизированной таблицы и хранимая процедура приведены ниже для моделирования. Скриншоты симуляции все с моей виртуальной машины (2 ядра и 2G памяти).

Создать таблицу SQL

CREATE TABLE `dt_school` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `tea_reg` int(11) NOT NULL DEFAULT '0' COMMENT '老师注册数',
  `stu_reg` int(11) NOT NULL DEFAULT '0' COMMENT '学生注册数',
  `school_id` int(11) NOT NULL COMMENT '学校id',
  `time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间（具体到天）',
  PRIMARY KEY (`id`),
  KEY `key_school_id` (`school_id`),
  KEY `index_time_school` (`time`,`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据统计-学校统计表'

Данные инициализации

delimiter  //
CREATE PROCEDURE proc_init_dt_school() 
BEGIN 
	DECLARE d INT;
	DECLARE sid INT ;
	SET d = 20160101;
	WHILE d < 20190501 DO
		SET sid = 1;
		WHILE sid < 1501 DO
			insert into dt_school(tea_reg,stu_reg,school_id,time) 
 value(floor(rand()*100),floor(rand()*100),sid,d);
			SET sid = sid + 1;
		END WHILE;
		SET d = DATE_FORMAT(date_add(d,INTERVAL 1 day),'%Y%m%d');
	END WHILE;
END //
delimiter ;
call proc_init_dt_school() ;

Поиск данных еще на один день, SQL-затраты времени удваиваются в n раз?

Онлайн SQL, вероятно, такой

select sum(tea_reg),sum(stu_reg) from dt_school 
where time between 20160101 and 20160411 group by school_id\G;

Результаты:

image.png

Проверьте еще один день

select sum(tea_reg),sum(stu_reg) from dt_school where time between 20160101 and 20160412 group by school_id\G;

Результаты

image.png

Confused.jpg , хорошо. Сравните план выполнения:

image.png
image.png

Обнаружено, что как только временной диапазон запроса превысит этот узел, MySQL выберет индекс school_id, что приведет к замедлению выполнения запроса. Поскольку дело обстоит именно так, я использую force index, чтобы заставить index_time_school

select sum(tea_reg),sum(stu_reg) from dt_school force index(index_time_school)
where time between 20160101 and 20160412 group by school_id\G;

image.png

image.png
Но для реальной производственной среды я не хочу использовать индекс силы. Итак, я решил попробовать удалить этот индекс, чтобы устранить его помехи. (Конечно, я не удалял среду онлайн-генерации, потому что есть другие бизнес-запросы, которым нужно использовать этот индекс)

drop index key_school_id on dt_school

Выполните этот запрос еще раз

select sum(tea_reg),sum(stu_reg) from dt_school 
where time between 20160101 and 20160412 group by school_id\G;

image.png
image.png
Обнаружено, что после удаления индекса key_school_id MySQL не выберет индекс index_time_school. Однако мы видим, что даже без индексации полное агрегирование сканирования таблицы занимает всего 0,83 с, что намного быстрее, чем при использовании индекса key_school_id.

Итак, обязательно ли индекс ускоряет запрос? Лучше не строить неподходящий индекс.

решение

Мы обнаружили, что он был удален после индекса key_school_id. MySQL по-прежнему не будет выбирать индекс index_time_school, хотя мы можем использовать force index() для явного указания индекса, но это принципиально не решает проблему. Поскольку MySQL не использует этот индекс, это означает, что, по крайней мере, с точки зрения оптимизатора MySQL, независимо от того, используется этот индекс или нет, имеет большое значение для оптимизации. Мы обнаружили, что как только мы расширили объем этого запроса, затраты времени стали более очевидными.

select sum(tea_reg),sum(stu_reg) from dt_school 
force index(index_time_school) group by school_id\G;

image.png

В это время у меня внезапно возникла идея: поскольку весь этот бизнес связан с суммированием и агрегированием данных, мы можем решить эту проблему, агрегируя данные ежемесячно:

  1. Создайте dt_school_month точно так же, как dt_school
  2. Сценарий синхронизации периодически собирает данные dt_school и вставляет их в dt_school_month каждый месяц.
  3. Бизнес-уровень разделяется, если для запроса с временным диапазоном 20180121-20180402 он разрезается на 20180121-20180131, 20180401-20180402 (проверьте таблицу dt_school), 20180201-20180331 (проверьте таблицу dt_school_month)

Разделенный SQL-запрос становится:

SELECT
  sum(tea_reg) as tea_reg,
  sum(stu_reg) as stu_reg,
  school_id
FROM (
  (SELECT
     sum(tea_reg) as tea_reg,
     sum(stu_reg) as stu_reg,
     school_id
   FROM dt_school
   WHERE time BETWEEN 20180121 AND 20180131
   GROUP BY  school_id)
  UNION ALL (SELECT
               sum(tea_reg) as tea_reg,
               sum(stu_reg) as stu_reg,
               school_id
             FROM dt_school_month
             WHERE time BETWEEN 20180201 AND 20180331
             GROUP BY  school_id)
  UNION ALL (SELECT
               sum(tea_reg) as tea_reg,
               sum(stu_reg) as stu_reg,
               school_id
             FROM dt_school
             WHERE time BETWEEN 20180401 AND 20180402
             GROUP BY  school_id))
GROUP BY  school_id;

SQL становится более сложным, но поскольку в dt_school_month есть только две строки сводных данных time=20180228 и 20180331, а запросы dt_school и dt_school_month могут проходить через индекс index_time_school, скорость возрастает.

SQL_BIG_RESULT

Сразу после завершения этого ежемесячного плана Мэнкан, большой парень в группе технического обмена, опубликовал сообщение в блоге.Группа по + порядок по анализу оптимизации производительностиТакже речь идет о похожем случае, в котором упоминается ключевое слово SQL_BIG_RESULT. Мои глаза сияли, и я дал полную свободу духу маленького белого ученика.

Когда я вижу молоток, я хочу взять его и забить

select SQL_BIG_RESULT sum(tea_reg),sum(stu_reg) from dt_school  
where time between 20190101 and 20190412 group by school_id\G;

image.png
Было обнаружено, что после использования SQL_BIG_RESULT время выполнения стало быстрее. Еще одна нерешенная проблема заключается в том, что использование SQL_BIG_RESULT на онлайн-машине сценариев быстрее, чем использование индекса index_time_school.Если я использую виртуальную машину для воспроизведения в автономном режиме, это будет немного медленнее и использовать индекс index_time_school. Я предполагаю, что это может быть из-за твердотельного накопителя, используемого на онлайн-машине (если у вас есть идеи по отладке, сообщите мне, спасибо). Если вы обнаружите, что использование SQL_BIG_RESULT по-прежнему работает медленно во время тестирования, вы можете выполнить следующую инструкцию.

show variables like '%sort_buffer_size%';

sort_buffer默认配置

SET GLOBAL sort_buffer_size = 1024*1024*2;

Можете ли вы использовать SQL_BIG_RESULT для ускорения запроса? Давайте посмотрим на план реализации

image.png
сделай это снова

help SQL_BIG_RESULT

image.png
image.png
Представься кем-то, кто понимает по-английски, переведи его, в руководстве, наверное, написано

SQL_BIG_RESULT или SQL_SMALL_RESULT можно использовать с GROUP BY или DISTINCT, чтобы сообщить оптимизатору, что результирующий набор содержит много или мало строк соответственно. С SQL_BIG_RESULT MySQL использует временные таблицы на диске непосредственно во время создания и сортирует временные таблицы с ключами элемента GROUP BY в предпочтение. С SQL_SMALL_RESULT MySQL использует временную таблицу в памяти для хранения результирующей таблицы вместо использования сортировки.

Это предложение состоит в том, что некоторые сообщения в блогах в Интернете обобщают использование SQL_BIG_RESULT для сортировки, а затем для группировки. Не используйте SQL_BIG_RESULT, сначала группируйте, а затем сортируйте.

Начиная с группы по принципу

Почему использование SQL_BIG_RESULT может ускорить запрос, это должно начинаться с принципа группировки. Здесь я хочу позаимствовать фотографию колонки г-на Дин Ци, и, кстати, Amway взглянет на его колонку.MySQL45 разговор

Суть группировки по фактической сортировке

за

select  sum(tea_reg) as t ,sum(stu_reg) as s from dt_school 
where time between 20190101 and 20190412 group by school_id\G;

Поток выполнения этого оператора выглядит следующим образом: 1. Создайте в памяти временную таблицу с тремя полями t, s, school_id 2. Просканировать индекс первичного ключа таблицы dt_school, и вынуть значения узлов school_id и tea_reg, stu_reg и time по очереди. 3. Если значение времени не находится в пределах полученного временного диапазона, отбросьте его. Определить, есть ли уже строка со значением school_id в таблице памяти, если нет вставить (school_id, tea_reg, stu_reg), если есть, добавить значение tea_reg и stu_reg в соответствующую строку 4. При вставке данных в таблицу памяти по очереди обнаруживается, что таблица памяти заполнена. Создайте новую временную таблицу диска механизма innodb и переместите данные во временную таблицу диска. 5. Отсортируйте school_id временной таблицы и верните набор результатов клиенту.

image.png
image.png

На самом деле, этот шаг 5 не является необходимым. При использовании для обхода индекса school_id данные, вставляемые во временную таблицу, упорядочены по умолчанию, поэтому оптимизатор выбирает индекс school_id для большого объема данных, так как всегда считает, что сортировка отнимает много времени, а использование school_id Сортировка не требуется. Таким образом, для группы по a, если это не индекс a, операции в mysql5.6 и ниже сортируются по умолчанию после группировки.Если вам это не нужно, вы можете попробовать использовать порядок по нулю, чтобы ускорить этот запрос .

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

за

select  SQL_BIG_RESULT sum(tea_reg) as t ,sum(stu_reg) as s from dt_school 
where time between 20190101 and 20190412 group by school_id\G;

Поток выполнения этого оператора выглядит следующим образом:

  1. Просканируйте индекс первичного ключа таблицы dt_school и выньте значения узлов school_id и tea_reg, stu_reg и time по очереди.
  2. Если значение времени не находится в пределах полученного временного диапазона, отбросьте его. В противном случае вставьте его в буфер сортировки.Если sort_buffer недостаточно, непосредственно используйте временный файл на диске для облегчения сортировки.
  3. Сортировать данные в буфере сортировки по school_id
  4. Сканировать данные в буфере сортировки и возвращать агрегированные результаты

image.png

Вот почему для просмотра плана выполнения инструкции SQL_BIG_RESULT значение опции Extra не используется, временная таблица не используется, но требуется использование файловой сортировки. Конечно, если мы добавим индекс school_id, файловая сортировка не понадобится.

Учитель Дин Ци предложил в столбце отдать приоритет увеличению размера таблицы временной памяти (temp_table_size), но не объяснил причину. Поэтому я спросил одноклассника DBA компании, и он сказал это

Когда данные таблицы малы, использование подсказки делает сортировку файлов очень быстрой.На самом деле, если таблица больше десяти гигабайт, сортировка файлов происходит очень медленно. Буфер сортировки — это сессионная переменная.Выставленное онлайн 1М, как правило, не очень большое, потому что если будет много подключений одновременно, это будет занимать много памяти. База данных — это общий ограниченный ресурс, который необходимо распределять равномерно, а конфигурацию нельзя скорректировать из-за определенного оператора.

Выше приведено краткое изложение group by в моей работе. Из-за ограниченных знаний автора в тексте неизбежно много ошибок. Добро пожаловать, читатели обмениваются исправлениями.

Справочное чтение:

Категории