Скажите интервьюеру, что я умею оптимизировать groupBy и хорошо это знаю!

Управляемое чтение

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

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

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

CREATE TABLE `t_user_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `viewed_user_id` bigint(20) DEFAULT NULL COMMENT '被查看用户id',
  `viewed_user_sex` tinyint(1) DEFAULT NULL COMMENT '被查看用户性别',
  `viewed_user_age` int(5) DEFAULT NULL COMMENT '被查看用户年龄',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_viewed_user` (`user_id`,`viewed_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

Давайте посмотрим на записи в этой таблице:

Теперь, объединив приведенную выше структуру таблицы и записи таблицы, я начну сuser_id=1Например, групповая статистика по количеству просмотренных пользователем девушек в возрасте от 18 до 22 лет:

SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

Статистика такова:

видимый:

Количество пользователей женского пола в возрасте 18 лет – 2.
Этот пользователь просмотрел 1 пользователей женского пола в возрасте 19 лет
Этот пользователь просматривает 3 пользователей женского пола в возрасте 20 лет

так,user_id=1пользователей больше заинтересованы в женщинах в возрасте 20 лет и могут порекомендовать ему больше женщин в возрасте 20 лет.

Если в это время,t_user_viewРекордное количество этой таблицы достигает десятков миллионов масштабов, предположительно эффективность этого SQL-запроса резко упадет, и почему? Есть ли способ оптимизировать его?

Если вы хотите узнать причину, вы должны сначала посмотреть на процесс выполнения этого SQL.

Explain

мы сначала используемexplainВзгляните на этот SQL:

EXPLAIN SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

выполнить вышеуказанноеexplainзаявление, получаем следующий результат:

существуетExtraВ этом столбце появляются триUsing, эти 3UsingпредставляетgroupByЗаявления проходят 3 этапа исполнения:

Использование где: путем поиска возможноidx_user_viewed_userИндексное дерево расположено для удовлетворения некоторых условийviewed_user_id, а затем вернуться к таблице, чтобы продолжить поиск записей, удовлетворяющих другим условиям.
Использование временного: используйте временную таблицу для временного храненияgroupByГруппировка и статистическая информация о полях
Использование сортировки файлов: используйтеsort_bufferСортировать по полю группировки

Существительное появляется на этих 3 стадиях:临时表. В этом термине я нахожусь в «Время подтаблицы MySQL: 100 Вт? 300 Вт? 500 Вт? Ни то, ни другое! «Как упоминалось в статье, это область памяти, к которой потоки подключения MySQL могут обращаться и обрабатывать независимо. Итак, как выглядит эта временная таблица?

Далее я сначала расскажу об этой временной таблице MySQL, а затем, в сочетании с тремя упомянутыми выше этапами, подробно объясню процесс выполнения SQL во «Введении».

Временные таблицы

Давайте сначала посмотрим на эту статью во «Введении».groupBySQL для оператора, который содержит поле группировкиviewed_user_ageи поле статистикиcount(*), эти два поля являются частями, необходимыми для статистики в этом SQL.Если мы хотим сделать такую статистику и группировку, а также закрепить результаты, нам потребуется память или область диска, чтобы удалить результаты первой статистики, а затем , Используйте этот результат для получения следующей статистики, поэтому, как и эта область для хранения промежуточных результатов и использования этого результата для дальнейшей обработки, MySQL называет это临时表.

Только что упоминалось, что промежуточный результат может быть помещен в память или результат может быть помещен на диск, поэтому в MySQL существует два вида временных таблиц:内存临时表и磁盘临时表.

Временная таблица памяти

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

К этому моменту вы уже можете почувствовать, что MySQL сохраняет промежуточные результаты во временной таблице памяти, и производительность гарантирована. 300 Вт? 500 Вт? Ни то, ни другое! ", я упомянул, что частый доступ к памяти вызовет фрагментацию. По этой причине MySQL разработал новый механизм выделения и освобождения памяти, который может уменьшить или даже избежать фрагментации памяти временных таблиц и улучшить использование памяти временных таблиц.

В этот момент вы можете подумать: «Почему я увеличил sort_buffer_size, количество параллелизма велико, а сортировка запросов медленная? «В статье я рассказал о распределителе памяти пользовательского режима:ptmallocиtcmalloc, Каким бы ни был дистрибьютор, его роль состоит в том, чтобы избежать частого обращения пользователя к пространству памяти ядра Linux, что приводит кCPUЧастое переключение между режимом пользователя и режимом ядра влияет на эффективность доступа к памяти. Вы можете использовать их для решения проблемы использования памяти, почему MySQL должен делать это сам?

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

Далее я возьму SQL из «Введения» в качестве примера, чтобы подробно объяснить, как используется групповая статистика.MEM_ROOTмеханизм выделения и освобождения памяти?

MEM_ROOT

давайте сначала посмотримMEM_ROOTСтруктура,MEM_ROOTДизайн относительно простой, в основном, включая эти части, как показано ниже:

свободный: односвязный список, каждая единица в связанном списке называетсяblock,blockХранится в свободной области памяти, каждыйblockСодержит 3 элемента:

левый:blockОставшийся объем памяти
размер:blockСоответствующий объем памяти
следующий: указать на следующийblockуказатель

Как показано выше,freeгде линияfreeСвязанный список, часть, соединенная каждой стрелкой в связанном списке,block,blockимеютleftиsize, каждыйblockСтрелки между нимиnextуказатель

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

min_malloc: управлятьblockСколько места осталосьfreeСписок удален, добавлен вusedсвязанный список

размер блока:blockСоответствующий объем памяти

блок_номер:MEM_ROOTудалосьblockколичество

first_block_usage:freeпервый в спискеblockСколько раз запрошенный размер пространства не был достигнут

pre_alloc: при освобождении всегоMEM_ROOTможно управлять параметрами, выберите сохранитьpre_allocзаостренныйblock

Далее я возьму в качестве примера сгруппированный статистический SQL в «Руководстве».MEM_ROOTКак распределяется память?

распространять

инициализацияMEM_ROOT, см. выше:

min_malloc = 32

block_num = 4

first_block_usage = 0

pre_alloc = 0

block_size = 1000

err_handler = 0

free = 0

used = 0
Подать заявку на память, см. рисунок выше:

из-за инициализацииMEM_ROOTчас,free = 0, инструкцияfreeСвязанного списка не существует, поэтому обращайтесь к ядру Linux за 4-мя размерами1000/4=250изblock, построитьfreeСвязанный список, как показано выше, связанный список содержит 4block, в сочетании с предыдущимfreeОписание структуры связанного списка, каждыйblockсерединаsize250,leftтакже 250
Выделить память, см. выше:

(1) Траверсfreeсвязанный список, изfreeВозьмите первый из головы связанного спискаblock, стрелка вниз, как показано выше

(2) Взято изblockсредний дивизион220Размер области памяти, как показано над стрелкой вправо вверху-220,blockсерединаleftот250стать30

(3) будут разделены220Размер области памяти, выделенной для SQLgroupbyполеviewed_user_ageи поля статистикиcount(*), Используется в задней статистическом пакете, чтобы собрать область памяти

(4) Поскольку на шаге (2)blockсерединаleftстать30,30 < 32, то есть меньше инициализированного на шаге (1)min_malloc, поэтому, объединяя вышеперечисленноеmin_mallocпояснение смыслаblockбудет вставлятьusedХвост связанного списка, как показано в нижней части рисунка выше, потому чтоusedСвязанный список равен 0 при инициализации на шаге (1), поэтомуblockвставлятьusedХвост связанного списка, то есть начало вставки

освобожден

Возьмем для примера групповую статистику в "Путеводителе", посмотрим еще разMEM_ROOTКак выпустить память?

Как показано выше,MEM_ROOTПроцесс освобождения памяти выглядит следующим образом:

траверсusedВ связанном списке найдите тот, который нужно освободитьblock, как показано выше,block(30,250)Для ранее назначенной групповой статистикиblock
будетblock(30,250)серединаleft + 220,Сейчас30 + 220 = 250, выпуститьblockиспользовал220Размер области памяти после освобожденияblock(250,250)
будетblock(250,250)вставлятьfreeХвост таблицы ссылок, например часть кривой со стрелкой

пройти черезMEM_ROOTПри объяснении выделения и освобождения памяти мы обнаружили, чтоMEM_ROOTСпособ управления памятью в каждомBlockНепрерывное выделение сверху, внутренняя фрагментация в основном в каждомBlockхвост, поmin_mallocуправление переменной-членом, ноmin_mallocЗначение жестко закодировано в коде, что немного менее гибко. Итак, дляblock, Когдаleftменьше, чемmin_malloc, чем больше память, из которой нужно применить, тоblockсерединаleftЧем меньше значение, темblockЧем выше использование памяти, тем меньше фрагментация, и наоборот, тем больше фрагментация. Эта мертвая запись является дефектом в распределении памяти MySQL.

Дисковая временная таблица

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

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

Процесс реализации

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

Создать временную таблицуtemporaryВ таблице два поляviewed_user_ageиcount(*), первичный ключviewed_user_age, как показано выше, предпоследний ящикtemporaryПредставляет временную таблицу с двумя полями в полеviewed_user_ageиcount(*), поле является соответствующим значением этих двух полей, гдеviewed_user_ageявляется первичным ключом этой временной таблицы
вспомогательное индексное дерево таблицы сканированияidx_user_viewed_user, вынимаем листовые узлы по очередиidЗначение, то есть идентификатор первичного ключа таблицы, получается из дочерних узлов дерева индексов. как на фото вышеidx_user_viewed_userПоле представляет собой дерево индексов, а стрелка справа от поля указывает идентификатор первичного ключа таблицы.
По идентификатору первичного ключа к кластеризованному индексуcluster_indexНайти записи в листовых узлах , т. е. просмотретьcluster_indexЛистовой узел:

(1) поставить запись, тогда запись беретсяviewed_user_ageзначение поля. Как показано выше,cluster_indexполе, крайний правый столбец поляviewed_user_ageзначение поля

(2) Если во временной таблице нет первичного ключа дляviewed_user_ageстрока, вставьте запись (viewed_user_age, 1). как на фото вышеtemporaryполе, стрелка слева от которого указывает на то, чтоcluster_indexв коробкеviewed_user_ageзаписать значение поляtemporaryВременные таблицы

(3) Если первичный ключ во временной таблицеviewed_user_age, это будетviewed_user_ageэта линияcount(*)值加 1。 как на фото вышеtemporaryРамка
После завершения обхода, согласно полюviewed_user_ageсуществуетsort_bufferВыполните сортировку, получите набор результатов и верните его клиенту. Как показано самой правой стрелкой на рисунке выше, это означает, чтоtemporaryв коробкеviewed_user_ageиcount(*)напишите стоимостьsort_buffer, затем вsort_bufferсредний прессviewed_user_ageСортировать по полю

Благодаря объяснению процесса выполнения SQL во «Введении» мы обнаружили, что процесс прошел 4 части:idx_user_viewed_user,cluster_index,temporaryиsort_buffer, по сравнению с вышеуказаннымexplain, из которых первые 2 соответствуютUsing where,temporaryсоответствуетUsing temporary,sort_bufferсоответствуетUsing filesort.

Оптимизация

На данный момент, как мы можем оптимизировать этот SQL?

Поскольку это выполнение SQL должно пройти через 4 части, можем ли мы удалить последние две части, то есть удалитьtemporaryиsort_buffer?

Ответ да, нам просто нужно дать таблицу в SQLt_user_viewДобавьте следующий индекс:

ALTER TABLE `t_user_view` ADD INDEX `idx_user_age_sex` (`user_id`, `viewed_user_age`, `viewed_user_sex`);

Вы можете попробовать сами! использоватьexplainЧто изменилось в Канкане!

резюме

Эта глава посвящена групповой статистике в «Guide» SQL,explainПосле анализа этапа выполнения SQL в сочетании со структурой временной таблицы далее анализируется подробный процесс выполнения SQL, и, наконец, вводится схема оптимизации:Добавьте индексы, чтобы избежать статистики по сгруппированным полям во временных таблицах, иsort_bufferСортировка полей группировки и статистики.

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

мыслительные вопросы

Почему был добавлен индекс?idx_user_age_sexЭто может избежать статистики поля группировки во временной таблице иsort_bufferСортировать по полям группировки и статистики?

Подсказка: объедините принцип поиска по индексу.