Введение
Всем привет.Любовь повлияет только на скорость, с которой мы набираем коды.Для Фестиваля Циси, конечно, я набираю коды! ! !
Ха-ха-ха, хватит болтать. На этой неделе продукт неожиданно пришел ко мне и сказал, что запрос страницы в онлайн-файловом центре очень медленный.Я думал о том, сколько данных эта система только что развернула. Хороший парень, большинство данных меньше 5000 штук, а время запроса на самом деле занимает 8 секунд. Затем лидер все еще призывает продукт сказать, кто написал этот мусорный код, и он так застрял ...
Процесс был очень ухабистым, я обнаружил проблему за 1 день, но не знал, что ее вызвало. Я консультировался с друзьями из нескольких крупных интернет-компаний и их администратором баз данных, но понятия не имел.
В конце концов, коллега, который только что присоединился к компании (разрабатывал код хранимых процедур более 1 года), добавил индекс для решения этой проблемы, но принцип, лежащий в основе этого, стоит изучить.
Эта статья покажет вам решение этой проблемы и спецификации дизайна SQL.
2. Описание проблемы
2.1 Описание бизнес-сценария
1. Файловый центр единообразно поддерживает все системные файлы, загружаемые бизнес-системой.
2. Сохраняется только одна копия одного и того же файла MD5, и загруженный файл должен иметь то же имя, что и загруженный файл.
3. Загруженный файл может быть связан с несколькими обращениями [бизнес-атрибуты, предполагающие тип данных заказа] или несколькими типами обращений.
4. В списке управления файловым центром отображаются 5 полей: идентификатор файла, имя файла, файл MD5, связанные дела [связанные дела будут отображать идентификатор дела, связанные типы дел будут отображать типы дел, а все дела будут отображаться как все дела, которые могут отображаться одновременно. Связанные дела и типы дел, разделенные запятыми], примечания
5. Интерфейс управления может фильтровать запросы на основе идентификатора файла, имени файла, поля md5, идентификатора дела и типа дела.
2.2 Идеи дизайна
Исходя из потребностей вышеуказанных продуктов, я разработал таблицу базы данных в виде 4 таблиц.
| Имя таблицы | описывать |
|---|---|
| t_file | Основная информация о файле, отличающаяся md5 файла как уникальный индекс |
| t_file_info | Расширенная информация о файле, разные имена файлов, один и тот же файл представляет собой 1 запись в t_file, эта таблица представляет собой 2 записи, связанные с идентификатором первичного ключа t_file |
| t_file_incident_type | Ассоциированный тип дела, связанный с идентификатором первичного ключа t_file_info |
| t_file_incident | Связанные дела, связанные с идентификатором первичного ключа t_file_info |
соотношение данных
t_file 1:n t_file_info 1:n t_file_incident
t_file 1:n t_file_info 1:n t_file_incident_type
Структура таблицы не публикуется, так как она включает бизнес-атрибуты.
2.3 Описание проблемы
Данные подзапроса очень быстрые [50 мс], а после объединения всего набора результатов [8 с] время выполнения SQL отличается в 160 раз!
2.4.Проблема sql
(SELECT tf.id AS id,
tf.md5 AS md5,
tf.object_name AS objectName,
tfi.id AS fileInfoId,
tfi.file_name AS fileName,
tfi.remark AS remark,
tfi.visibility AS visibility,
tfi.gmt_create AS gmtCreate,
tfin.incident_id AS incidentId,
NULL AS incidentType
FROM t_file AS tf
JOIN t_file_info AS tfi ON tf.id = tfi.file_id AND tfi.deleted = 0 AND tfi.visibility = 0 AND
tfi.business_type = 'business'
LEFT JOIN t_file_incident AS tfin
ON tfi.id = tfin.file_info_id AND (tfin.deleted = 0 OR tfin.deleted IS NULL)
WHERE tf.deleted = 0)
UNION all
(SELECT tf.id AS id,
tf.md5 AS md5,
tf.object_name AS objectName,
tfi.id AS fileInfoId,
tfi.file_name AS fileName,
tfi.remark AS remark,
tfi.visibility AS visibility,
tfi.gmt_create AS gmtCreate,
NULL AS incidentId,
tfit.incident_type AS incidentType
FROM t_file AS tf
JOIN t_file_info AS tfi ON tf.id = tfi.file_id AND tfi.deleted = 0 AND tfi.visibility = 0 AND
tfi.business_type = 'business'
LEFT JOIN t_file_incident_type AS tfit
ON tfi.id = tfit.file_info_id AND (tfit.deleted = 0 OR tfit.deleted IS NULL)
WHERE tf.deleted = 0)
union all
(SELECT tf.id AS id,
tf.md5 AS md5,
tf.object_name AS objectName,
tfi.id AS fileInfoId,
tfi.file_name AS fileName,
tfi.remark AS remark,
tfi.visibility AS visibility,
tfi.gmt_create AS gmtCreate,
'所有案件' AS incidentId,
'所有类型' AS incidentType
FROM t_file AS tf
JOIN t_file_info AS tfi ON tf.id = tfi.file_id AND tfi.deleted = 0 AND tfi.visibility = 1 AND
tfi.business_type = 'business'
WHERE tf.deleted = 0)
Из-за подкачки действие агрегации данных нецелесообразно реализовывать в памяти, из-за особых требований к отображению и фильтрации sql-блогеры думают только о вышеописанном подходе. Некоторые из приведенных выше sql могут сказать, что написание слишком сложное.Когда я общался с друзьями на Али, они также сказали, что слишком много ассоциаций таблиц, но бизнес-ограничения таковы, что никаких лишних полей не предусмотрено.
Приведенный выше результат выполнения sql состоит из трех подзапросов, и результат используетunion allсоздать ассоциацию
1. объединение: выполнить операцию объединения двух наборов результатов, исключая повторяющиеся строки, и одновременно сортировать по правилам по умолчанию;
2. Объединение всех: выполнение операции объединения двух наборов результатов, включая повторяющиеся строки, без сортировки;
3. Как union, так и union all могут объединять несколько наборов результатов, а не только два, вы можете объединять несколько наборов результатов. Использование union и union all должно гарантировать, что результаты каждого набора выбора имеют одинаковое количество столбцов, а тип каждого столбца одинаков.
Описание данных в таблице
| поверхность | Объем данных |
|---|---|
| t_file | 5000- |
| t_file_info | 5000- |
| t_file_incident | 8000+ |
| t_file_incident_type | 0 |
Три подзапроса выполняются отдельно, а время выполнения составляет от 50 до 100 мс.
Выполнить весь sql, выполнить sql в 8s+
3. Идеи решения проблем
3.1.выполнение sql и выполнение подзапроса
Когда продукт впервые сообщил мне о проблеме, я получил sql из фонового журнала для выполнения всего сегмента и обнаружил, что время выполнения sql превысило 8 с. Более того, после получения результата запроса его необходимо обработать. дважды на уровне Java, поэтому фактический дисплей показывает, что на странице может быть меньше 9S или около того. Затем я выполняю подзапросы union все соответственно, и время выполнения составляет около 50-100 мс.
3.2.explain для просмотра плана выполнения
Честно говоря, после того, как первый шаг был выполнен, я был на самом деле довольно сбит с толку.Я однажды подумал, что я использовал union all неправильно.Union all предназначен для прямого склеивания результирующего множества подзапроса. Но после того, как я его использую, время выполнения увеличивается в геометрической прогрессии.
Или идиоматическая процедура настройки sql, проверьте план выполнения
План выполнения подзапроса sql соответствует приведенному выше.
Это покрыто. . .
3.3.процесс выполнения sql
Просмотрите процесс выполнения sql
Детали каждого узла не будут расширены.Заинтересованные студенты могут сделать это самостоятельно.Вот краткое упоминание о роли узлов.
Соединитель: подключиться к серверу mysql
кэш запросов: Buffer Pool (буферный пул) поддерживается в mysql, даInnoDBОчень важная структура памяти в механизме хранения, как следует из названия, буферный пул фактически похож на Redis, играя роль кеша.
Анализатор: Анализ синтаксиса SQL
оптимизатор: оптимизировать порядок выполнения sql
Актуатор: выполнить sql
Так как наш sql может выполняться нормально, то первые три узла в принципе исключены, и проблему можно посмотреть только со стороны оптимизатора и исполнителя. Версия mysql, используемая в производстве,5.7.26, а сервер настроен с сетевой картой 64G, 10 Gigabit. Верхняя команда для запроса процессора и использования памяти является нормальной. Так что проблема с исполнителем исключена. Так что я подозреваю, что это проблема оптимизатора.
3.4. Местонахождение проблемы и решение
После того, как попал сюда, потому что сам больше использую mysql в sql и index use. Реализация на уровне механизма хранения мне не знакома, поэтому я спросил начальника базы данных компании
Отсюда можно узнать два ключевых информационных момента.Медленный SQL-запрос вызван двумя причинами:
1. Левое соединение выполняет полное сканирование таблицы, а запрос выполняется медленно [но прямое выполнение подзапросов выполняется очень быстро]
2. mysql внутренне изменил логику выполнения sql, определенную мной
Пока что у меня нет выбора, я уже думал о том, чтобы дождаться, пока администратор базы данных подстроит под меня подсказку mysql, и позволить механизму хранения применять мою логику.
Примерно через полчаса мой коллега вдруг попросил меня снова попробовать sql, и мгновенное время выполнения заняло всего 137 мс.【优化后的速度比优化前快了58倍】. Это почти сумма времени трех подзапросов, что соответствует ожидаемому времени объединения набора результатов.
План выполнения после добавления правильного индекса
я был шокирован
он просто на высотеt_file_incidnetа такжеt_file_incident_typeСвязанные поля в двух таблицахfile_info_idДобавился индекс, и скорость мгновенно увеличилась.
3.5 Принцип
Возвращаясь к плану выполнения 3.2, на самом деле более важное место, которое я упустил из виду при первом просмотре плана выполнения, уже было подсказано дополнительно.Using join buffer (Block Nested Loop). Но поскольку предыдущийusing where, а еще в подзапросе есть это предложение, мне все равно, я не думаю, что это проблема.
MySQL выполнил оптимизацию запросов в версии 5.7:Block Nested-Loop
Первоначально алгоритм MySQL BNL поддерживался только内连接, теперь поддерживается外连接а также半连接операций, в том числе嵌套外连接.
Принцип алгоритма BNL: Сохраните набор строк/результатов внешнего цикла в буфере соединения и сравните каждую строку данных в цикле памяти с записями во всем буфере, что может уменьшить количество сканирований во внутреннем цикле.
Возьмем простой пример: набор результатов внешнего цикла содержит строки данных 1000. При использовании алгоритма NLJ необходимо сканировать внутреннюю таблицу 1000 раз, но если используется алгоритм BNL, сначала извлеките 100 строк набора результатов внешней таблицы и сохраните их вjoin buffer, а затем используйте каждую строку данных во внутренней таблице для сравнения со 100 строками набора результатов, которые можно сравнить со 100 строками данных одновременно, поэтому внутренней таблице на самом деле нужно всего лишь выполнить цикл 1000/100 = 10 раз. , уменьшение 9/10 .
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
Если длины столбцов t1 и t2, участвующих в соединении, равны только s, а c — количество комбинаций этих двух, то количество просмотров таблицы t3 равно
(S * C)/join_buffer_size + 1
Количество сканирований t3 зависит отjoin_buffer_sizeувеличивается и уменьшается до тех пор, покаjoin bufferМожно разместить все комбинации t1, t2, а затем увеличить размер буфера соединения, скорость запроса не станет выше.
optimizer_switchсистемная переменнаяblock_nested_loopФлаги, которые определяют, использует ли оптимизатор алгоритм блочного вложенного цикла.
по умолчанию,block_nested_loopактивирован.
В выводе EXPLAIN, когдаExtraзначение содержитUsing join buffer（Block Nested Loop）а такжеtypeзначениеALL，index或range时, что указывает на использование BNL.
Пример
mysql> explain SELECT a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 298936 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
вернуться к нашему вопросу
5.6версия сделана临时表, а затем присоединиться к временной таблице. а также5.7используется напрямуюUsing join buffer (Block Nested Loop).
filtered=100% означает правильную таблицу没有应用索引下推（ICP), потому что условие where没有索引.
Это использование буфера соединения (блочный вложенный цикл) вызвано тем, что правая таблица не индексируется в столбце соединения.嵌套循环.
Похоже, первопричина здесь: во-первых, нет ICP, что приводит к тому, что полные данные таблицы выводятся на уровень сервера, во-вторых, левый столбец соединения не имеет индекса, что приводит к вложенным циклам.
Видно, что оптимизатор mysql сначала выполнит индексированный результирующий набор, а затем соединится с неиндексированной таблицей.
4. Резюме
1. Это все еще необходимо в процессе ежедневных исследований и разработок谨慎使用left join,尽量使用join, если вы можете сделать ассоциацию в коде, эффект может быть лучше.
2. Должен использоватьleft joinКогда , лучше всего добавить индекс к связанному полю с обеих сторон,右边必须加索引.
3. Установление индекса столбца устанавливается в поле с высокой степенью дискриминации. Формула дискриминации:count(distinct col)/count(*)
5. Ссылка
Блокирование вложенных циклов и пакетных объединений доступа к ключу для оптимизации запросов MySQL
Использование буфера соединения (блочный вложенный цикл), вызванное левым соединением mysql
6. Свяжитесь со мной
Если есть неточности в тексте, поправьте меня, текст писать непросто, ставьте лайк, ладно~
Диндин: louyanfeng25
WeChat: baiyan_lou