Описание статьи
В этой статье в основном фиксируются случаи медленных запросов, с которыми я столкнулся в реальной работе в последнее время, а затем выполняется процесс настройки и анализа.Приглашаем обсудить опыт настройки. (Имена таблиц и столбцов, представленные ниже, являются псевдонимами, а фактические данные были немного изменены. Возможно, статья ближе к практике и была перепечатана редакционной заявкой 51CTO)
1. Оптимизация сложных задач глубокого пейджинга
задний план
Существует таблица статей, которая используется для хранения основной информации о статье, включая идентификатор статьи, идентификатор автора и другие атрибуты, и таблица содержимого, которая в основном используется для хранения содержимого статьи.Первичный ключ — article_id. , и некоторые авторы, которые соответствуют условиям, должны быть опубликованы.Статья была импортирована в другую библиотеку, поэтому мой коллега сначала запросил идентификатор квалифицированного автора в проекте, а затем открыл несколько потоков, каждый поток брал идентификатор автора за раз, и выполнил запрос и работу по импорту.
Чтобы запросить всю информацию о статье под идентификатором автора 1111, SQL для информации, связанной с содержанием статьи, выглядит следующим образом:
SELECT
a.*, c.*
FROM
article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100
Поскольку запрашиваемая база данных представляет собой механический жесткий диск, когда запрос смещения достигает 200 000, время запроса очень велико.Коллега по эксплуатации и обслуживанию получил сигнал тревоги напрямую, говоря, что база данных была заблокирована вводом-выводом, а мастер- переключение ведомого выполнялось много раз.Теперь мы пошли в navicat и попытались выполнить этот оператор, и мы продолжали ждать, а затем выполнили команду show processlist в базе данных для проверки и обнаружили, что каждый запрос находится в состоянии Запись в сеть.Импортированный проект временно находится в автономном режиме, а затем выполните команду kill, чтобы убить текущий процесс запроса (потому что, если только клиент остановится, сервер MySQL продолжит выполнение запроса).
Затем начинаем анализировать причину медленного выполнения этой команды:
Это проблема совместного индекса
Текущий индекс выглядит следующим образом:
article表的主键是id，author_id是一个普通索引
content表的主键是article_id
Поэтому я думаю, что текущий процесс выполнения заключается в том, чтобы сначала перейти к обычному индексу author_id таблицы статей, чтобы найти все идентификаторы статей 1111, а затем перейти к кластеризованному индексу таблицы статей, чтобы найти все статьи в соответствии с этими идентификаторами статей, а затем взять каждый идентификатор статьи в таблицу содержимого, найти содержимое статьи и другую информацию, а затем оценить, соответствует ли create_time требованиям, отфильтровать и, наконец, найти 100 фрагментов данных после смещения 20000.
Итак, мы изменили индекс author_id статьи на совместный индекс (author_id, create_time), так что дерево B+ в объединенном индексе (author_id, create_time) должно сначала установить сортировку author_id, а затем сортировать по create_time, чтобы при начало в соединении (author_id, create_time) create_time) запрошенного идентификатора статьи, который удовлетворяет условию create_time
Процесс действительно этот процесс, но при запросе, если предел все еще 210000, 100, все равно не удается найти данные, нет данных в течение нескольких минут, пока навица не предложит тайм-аут, если вы используете Объяснение, чтобы увидеть, это попадет в индекс, если вы установите смещение, уменьшите его до 6000, 100, вы едва сможете найти данные, но это займет 46 секунд, так что узкого места здесь нет.
Причина медленного запроса
SELECT
a.*, c.*
FROM
article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100
Прежде всего, нам нужно знать, что движок innodb не понимает наши бизнес-правила при его выполнении, он не знает, что если в таблице article есть статья, то в таблице содержимого должна быть информация о содержимом этой статьи. , то есть он не знает, что id таблицы article должен иметь соответствующий ему article_id в таблице содержимого. Таким образом, поток выполнения механизма innodb выглядит следующим образом:
1. Сначала перейдите к таблице статей, чтобы найти удовлетворениеa.author_id = 1111 AND a.create_time < '2020-04-29 00:00:00'Все поля из 22000 элементов данных условия загружаются в память. (Когда MySQL присоединяется, то в память загружается не только поле присоединения, а все поля SELECT. Разобраться несложно. Если только поле присоединения, то нужно вернуть таблицу в таблицу в соответствии с полем соединения.)
2. Затем по данным 22000 перейти в таблицу содержания, чтобы найти поля, относящиеся к содержанию статьи. (Поскольку в таблице содержимого хранится содержимое статьи, некоторые поля очень велики и не будут храниться в листовых узлах кластеризованного индекса, а будут храниться в другом месте, поэтому будет сгенерировано много случайных операций ввода-вывода, поэтому этот запрос такая медленная причина.)
3. Наконец, 22 000 фрагментов данных возвращаются на сервер MySQL, а последние 100 фрагментов данных берутся и возвращаются клиенту.
Используйте команду show table status для просмотра средней длины строк данных, отображаемых в таблице article и таблице содержимого.
| Name | Engine | Row_format | Rows | Avg_Row_length |
|---|---|---|---|---|
| article | InnoDB | Compact | 2682682 | 266 |
| content | InnoDB | Compact | 2824768 | 16847 |
Обнаружено, что объем данных двух таблиц составляет более 2 миллионов, средняя длина строки таблицы article составляет 266, а средняя длина таблицы содержимого составляет 16847. Проще говоря, когда InnoDB использует Компактный или избыточный формат для хранения очень длинных VARCHAR или Для больших объектов, таких как BLOB, мы не храним все содержимое напрямую в узле страницы данных, а сохраняем первые 768 байт в данных строки на странице данных, на которые будет указываться по смещению позже.
(Подробнее читайте в этой статьеПодробные хорошие статьи помогут вам понять MySQL и InnoDB.)
Таким образом, когда 100 последовательных строк данных запрашиваются из таблицы содержимого, при чтении каждой строки данных необходимо читать данные страницы переполнения, что требует большого количества случайных операций ввода-вывода. Гораздо медленнее. Так что мы проверили это позже,
Просто запросите ограничение 200000, 100 данных из таблицы статей и обнаружите, что даже если есть проблема глубокого пейджинга, время запроса составляет всего 0,5 с, поскольку средняя длина столбца таблицы статей составляет 266, все данные сохраняются в узле страницы данных нет переполнения страницы, поэтому все это последовательный ввод-вывод, поэтому он быстрее.
//查询时间0.51s
SELECT a.* FROM article a
WHERE a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 200100, 100
Напротив, мы напрямую находим 100 article_id и переходим к таблице содержимого для запроса данных, и обнаружили, что это относительно медленно, и первый запрос занимает около 3 с (то есть информация, связанная с содержанием статей с этими ids не переданы и кэша нет), потому что данные страницы переполнения были загружены в пул буферов во втором запросе, поэтому около 0,04 с.
SELECT SQL_NO_CACHE c.*
FROM article_content c
WHERE c.article_id in(100个article_id)
решение
Таким образом, есть два основных решения этой проблемы:
Сначала узнайте идентификатор первичного ключа, а затем внутреннее соединение
В случае прерывистого запроса, то есть, когда мы проверяем данные на странице 100, мы не обязательно проверяем страницу 99, то есть, когда запрос на переход страницы разрешен, то мы используемСначала проверьте первичный ключ, а затем присоединяйтесьЭтот метод переписывает наш бизнес-SQL следующим образом, запрашивая идентификатор первичного ключа с номером 210000, 100, как временную таблицу temp_table, внутреннее соединение таблицы article и таблицы temp_table, запрашивая информацию, относящуюся к статье в середине, и переходя влево. Таблица содержимого соединения запрашивает информацию, относящуюся к содержимому статьи. Первый запрос занимает около 1,11 с, а каждый последующий запрос занимает около 0,15 с.
SELECT
a.*, c.*
FROM article a
INNER JOIN(
SELECT id FROM article a
WHERE a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000 ,
100
) as temp_table ON a.id = temp_table.id
LEFT JOIN content c ON a.id = c.article_id
Результаты оптимизации
До оптимизации, когда смещение достигает порядка 200 000, время запроса слишком велико, пока не истечет время ожидания.
После оптимизации, когда смещение достигает порядка 200 000, время запроса составляет 1,11 с.
Используйте условия запроса диапазона, чтобы ограничить извлекаемые данные
Общая идея этого метода заключается в следующем.Предположим, мы хотим запросить последние 100 фрагментов данных, смещение которых равно 10000 в test_table.Предположим, что мы заранее знаем id 10000-го фрагмента данных, и значение равно min_id_value
select * from test_table where id > min_id_value order by id limit 0, 100, то есть с помощью условия id > min_id_value пропустить 10000 записей в индексе сканирования, а затем взять 100 данных, значение смещения этого метода обработки становится равным 0, но этот метод имеет ограничения, необходимо знать смещение Соответствующий id , а затем в качестве min_id_value добавьте в фильтр условие id > min_id_value, если оно используется для постраничного поиска, то есть вы должны знать наибольший id предыдущей страницы, поэтому за раз можно проверять только одну страницу, и вы не можете пропускать страницы , но поскольку наше бизнес-требование состоит в том, чтобы импортировать данные пакетами по 100 фрагментов данных за раз, наш сценарий можно использовать. Для этого метода наш бизнес-SQL переписывается следующим образом:
//先查出最大和最小的id
SELECT min(a.id) as min_id , max(a.id) as max_id
FROM article a
WHERE a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
//然后每次循环查找
while(min_id<max_id) {
SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id WHERE a.author_id = 1111 AND a.id > min_id LIMIT 100
//这100条数据导入完毕后，将100条数据数据中最大的id赋值给min_id，以便导入下100条数据
}
Результаты оптимизации
До оптимизации, когда смещение достигает порядка 200 000, время запроса слишком велико, пока не истечет время ожидания.
После оптимизации, когда смещение достигает порядка 200 000, поскольку известен идентификатор 200 000-го фрагмента данных, время запроса составляет 0,34 с.
2. Оптимизация задачи совместного индекса
Совместный индекс фактически выполняет две функции:
1. В полной мере используйте условия, чтобы сузить область
Например, нам нужно запросить следующий оператор:
SELECT * FROM test WHERE a = 1 AND b = 2
Если для поля a установлен одностолбцовый индекс, а для b — одностолбцовый индекс, то при запросе можно выбрать только индекс a, запросить все идентификаторы первичных ключей с a=1, а затем вернуться к таблице. Прочитайте каждую строку данных в , а затем отфильтруйте набор результатов b = 2 или возьмите индекс b, что представляет собой тот же процесс. Если для a и b установлен совместный индекс (a, b), то при запросе узел с a=1 будет непосредственно найден в совместный индекс, а затем продолжить проверку по b=2, чтобы узнать результаты отвечающие заданным условиям, вернуться к таблице.
2. Избегайте возврата к таблице (в настоящее время также называемой покрывающим индексом).
В этом случае, если мы запрашиваем только определенные общие поля, например, запросы a и b выглядят следующим образом:
SELECT a,b FROM test WHERE a = 1 AND b = 2
Чтобы установить одностолбцовый индекс для поля a и установить одностолбцовый индекс для b, как упоминалось выше, после нахождения набора идентификаторов первичных ключей, соответствующих условиям, вам нужно перейти к кластеризованному индексу, чтобы вернуть запрос таблицы, но если само поле, которое мы хотим запросить, находится в объединенном индексе, все включено, поэтому нет необходимости возвращаться к таблице.
3. Уменьшите количество строк данных, которые необходимо вернуть в таблицу.
Это ситуация, если нам нужно запросить данные с a> 1 и b = 2
SELECT * FROM test WHERE a > 1 AND b = 2
Если установлен одностолбцовый индекс a, то в одностолбцовом индексе a будут найдены все идентификаторы первичных ключей a>1, которые затем будут возвращены в таблицу. Если установлен совместный индекс (a, b), он основан на принципе сопоставления самого левого префикса, поскольку условием запроса a является поиск в диапазоне (все условия запроса, кроме = или in, являются поиском в диапазоне), поэтому, хотя запрос в объединенном индексе. Только часть индекса a может быть найдена, а часть b не может быть найдена и может быть запрошена только в соответствии с a>1, но поскольку каждый конечный узел в объединенном индексе содержит информацию о b, когда запрашиваются все идентификаторы первичного ключа a> 1, он также будет фильтровать b = 2, так что идентификатор первичного ключа, который необходимо вернуть в таблицу, будет только a> 1 и b = 2, поэтому объем данных возвращается в таблицу будет меньше.
В нашем бизнесе мы столкнулись с третьим случаем. Наш бизнес-SQL более сложен и будет присоединяться к другим таблицам. Однако, поскольку узким местом оптимизации является создание совместного индекса, мы сделали некоторые упрощения. Ниже приведен упрощенный SQL:
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a
where
a.create_time between '2020-03-29 03:00:00.003'
and '2020-04-29 03:00:00.003'
and a.status = 1
На самом деле наше требование состоит в том, чтобы запрашивать статьи со статусом 1 за последний месяц из таблицы article.Мы изначально построили одностолбцовый индекс для create_time.В результате мы нашли этот оператор в журнале медленных запросов.Время запроса занимает около 0,91 с. Так что начните пытаться оптимизировать.
Чтобы облегчить тестирование, мы установили в таблице одностолбцовый индекс create_time для create_time и объединенный индекс idx_createTime_status для (create_time, status).
Принудительный запрос с idx_createTime
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a FORCE INDEX(idx_createTime)
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
Принудительные запросы с idx_createTime_status (этот индекс будет выбран, даже если не принудительно)
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a FORCE INDEX(idx_createTime_status)
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
Результаты оптимизации:
При использовании одностолбцового индекса idx_createTime перед оптимизацией время запроса составляет 0,91 с.
Совместный индекс idx_createTime_status используется перед оптимизацией, а время запроса составляет 0,21 с.
Результат EXPLAIN выглядит следующим образом:
| id | type | key | key_len | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| 1 | range | idx_createTime | 4 | 311608 | 25.00 | Using index condition; Using where |
| 2 | range | idx_createTime_status | 6 | 310812 | 100.00 | Using index condition |
Принципиальный анализ
Давайте сначала представим значение различных значений столбца Extra в EXPLAIN.
Using filesort
Когда запрос содержит операцию ORDER BY и индекс нельзя использовать для завершения операции сортировки, оптимизатор запросов MySQL должен выбрать соответствующий алгоритм сортировки для реализации. Сортировать из памяти, когда данных меньше, в противном случае сортировать с диска. Объяснение явно не сообщает клиенту, какой вид использовать.
Using index
Информация о столбцах извлекается из таблицы с использованием только информации в дереве индексов, никаких дополнительных поисков не требуется для чтения фактической строки (используйте вторичный покрывающий индекс для получения данных). Эту стратегию можно использовать, когда в запросе используются только столбцы, являющиеся частью одного индекса.
Using temporary
Чтобы разрешить запрос, MySQL необходимо создать временную таблицу для хранения результатов. Обычно это происходит, если запрос содержит предложения GROUP BY и ORDER BY для разных столбцов. Официальное объяснение: «Чтобы решить запрос, MySQL должен создать временную таблицу для хранения результатов. Типичными случаями являются случаи, когда запрос содержит предложения GROUP BY и ORDER BY, которые могут перечислять столбцы в соответствии с различными ситуациями. Очевидно, что он извлекается. одновременно с помощью условия where Набор результатов слишком велик для сохранения в памяти, и обработка может быть облегчена только добавлением временной таблицы.
Using where
Указывает, что когда поле в условии фильтра where не имеет индекса, слой MySQL Sever будет фильтровать в соответствии с условиями в условии where после получения набора результатов механизма хранения (например, innodb).
Using index condition
При использовании условия индекса сначала будет фильтроваться индекс, после фильтрации индекса будут найдены все строки данных, соответствующие условиям индекса, а затем используются другие условия в предложении WHERE для фильтрации этих строк данных;
В нашем реальном случае, когда мы берем один индекс idx_createTime, мы можем узнать это только из индекса.a.create_time between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'Идентификатор первичного ключа условия, а затем вернуться в таблицу, поскольку в индексе idx_createTime нет информации о состоянии, вы можете узнать все строки, соответствующие идентификатору первичного ключа, только после возвращения в таблицу. Затем innodb возвращает набор результатов серверу MySQL.Сервер MySQL фильтрует в соответствии с полем состояния и отфильтровывает поле со статусом 1. Таким образом, дополнительный результат объяснения первого запроса будет отображать использование где.
Отфильтрованное поле указывает долю количества записей, оставшихся для удовлетворения запроса после того, как данные, возвращенные механизмом хранения, отфильтрованы на уровне сервера.Это оценочное значение, поскольку значение состояния равно нулю, 1, 2 , 3, 4, поэтому приведенное здесь значение равно 25%.
Следовательно, разница между вторым запросом и первым запросом в основном заключается в том, что набор результатов, найденный в idx_createTime_status в начале, представляет собой идентификатор со статусом 1. Следовательно, при обратном запросе к таблице по кластеризованному индексу количество просканированных строк будет намного меньше ( речь идет о разнице между 27 000 строк и 150 000 строк ), и тогда данные, возвращаемые innodb на сервер MySQL, представляют собой набор результатов (27 000 строк), который удовлетворяет состоянию состояния 1, нет необходимости фильтровать, поэтому второй запрос будет быстрее Настолько, время составляет 23% до оптимизации. (Предполагаемое количество строк, просканированных EXPLAIN для обоих методов запроса, составляет около 300 000 строк, потому что idx_createTime_status попадает только в createTime, поскольку createTime проверяет не одно значение, а диапазон)
//查询结果行数是15万行左右
SELECT count(*) from article a
where a.post_time
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
//查询结果行数是2万6行左右
SELECT count(*) from article a
where a.post_time
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
and a.audit_status = 1
Дивергентное мышление: что, если совместный индекс (createTime, status) изменить на (status, createTime)?
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
По принципу самого левого сопоставления, потому что наше условие запроса where такое, если оно (createTime, status), то индекс может использовать только createTime, если оно (status, createTime), потому что status - это запрос одного значение, поэтому статус, CreateTime может попасть, и количество сканируемых строк в индексе (status, createTime) будет уменьшено, но поскольку значение самого индекса (createTime, status) содержит информацию о трех полях createTime , статус и идентификатор, объем данных относительно невелик, в то время как размер страницы данных составляет 16 КБ, что позволяет хранить более 1000 узлов данных индекса, а последовательный ввод-вывод выполняется после запроса createTime, поэтому чтение выполняется быстрее , а общее время запроса практически одинаково. Вот результаты теста:
Первый созданный (status, createTime) с именем idx_status_createTime,
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a FORCE INDEX(idx_status_createTime)
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
Время запроса составляет 0,21, что в основном совпадает со временем запроса индекса во втором методе (createTime, status).
Объясните сравнение результатов:
| id | type | key | key_len | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| 2 | range | idx_createTime_status | 6 | 310812 | 100.00 | Using index condition |
| 3 | range | idx_status_createTime | 6 | 52542 | 100.00 | Using index condition |
Количество просмотренных строк действительно будет меньше, потому что в индексе idx_status_createTime изначально исключен статус = 1 из случая, когда значением статуса являются другие значения.
Перед созданием группы технического обмена вы можете получить PDF-версию «Руководства по собеседованию» при входе в группу. Надеюсь, вы сможете учиться и прогрессировать вместе со всеми!