Несколько дней назад я помогал коллеге исследовать причину медленного SQL, и мне показалось, что это немного интересно, я запишу это здесь.
описание проблемы
Есть такая таблица:
Примечания: Таблица копируется непосредственно мной, но первичный ключ innodb в таблице UUID, что неразумно.Innodb вообще требует, чтобы первичный ключ монотонно увеличивался, иначе при частой вставке дерево B+ innodb будет часто split. , что сильно влияет на производительность.
CREATE TABLE `spider_record` (
`id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`platform_id` int(11) DEFAULT NULL COMMENT '平台推文id',
`titile` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '标题',
`description` varchar(800) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '描述',
`updated_at` datetime DEFAULT NULL COMMENT '更新日期',
`news_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '原文链接',
`published_at` datetime DEFAULT NULL COMMENT '推送日期',
`create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`is_analyze` tinyint(2) DEFAULT '0' COMMENT '是否分析 0否 1是',
KEY `platform_id_idx` (`platform_id`),
KEY `create_tm_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Данных в таблице всего 140 000 (очень маленькая таблица), версия mysql 5.7, а окружение представляет собой компьютер, разработанный мной, со средней и высокой конфигурацией (память 8G + жесткий диск SSD)
Существует следующий sql (sql1), который относительно быстр в начале выполнения, но когда один и тот же выполняется тысячи раз, время выполнения одного sql становится очень медленным, и самый медленный может достигать более 30 секунд. .
SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time desc LIMIT 10; // sql1
Тогда если desc после order by убрать, то есть следующий sql2, то время выполнения становится десятками миллисекунд
SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time LIMIT 10; // sql2
Итак, вопрос:
- Почему запрос 140 000 данных такой медленный, даже если полное сканирование таблицы не такое медленное?
- Почему не тормозит после удаления desc?
Чтобы найти похожие вопросы в Интернете, существует несколько поговорок:
- В mysql не включен кеш, но проверьте, включена ли локальная (показать переменные, такие как '%query_cache%';) конфигурация.
- order by не использует индекс, но даже если индекс не используется, он не должен быть таким медленным (на самом деле наш sql использует индекс, см. объяснение ниже).
Таким образом, онлайн-решение к нам не относится, мы можем решить его только сами.
Устранение неполадок
explain
Прежде всего, видя медленное выполнение sql, первой реакцией должна быть проверка плана выполнения:
mysql> EXPLAIN
SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time desc LIMIT 10;
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | spider_record | NULL | index | NULL | create_tm_idx | 6 | NULL | 10 | 10.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
1 row in set (0.05 sec)
mysql> EXPLAIN
SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time LIMIT 10;
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | spider_record | NULL | index | NULL | create_tm_idx | 6 | NULL | 10 | 10.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)
Однако планы выполнения двух SQL-запросов абсолютно одинаковы.Тип «index», что означает сканирование дерева индексов, и ключ «create_tm_idx», что означает сканирование дерева индексов «create_tm_idx». чтобы найти данные.Короче из плана выполнения Посмотрите, на уровне sql нет проблем, и он не такой уж и медленный.
show profiles
Так как через план выполнения ничего ненормального не видно, то придем ко второму приему, показывать профили
- Сначала выполните sql, вы можете видеть, что весь sql занимает более 10 секунд.
mysql> SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time desc LIMIT 10;
+----------------------------------+--------------------------------------------+---------------------+
| id | titile | published_at |
+----------------------------------+--------------------------------------------+---------------------+
| 980a0aee8ca0eab8c9d7e830ae8dad89 | AT&T明年或收购沃达丰：中移动卷入交易传闻 | 2013-11-01 09:02:05 |
| 95412ea5c82f4c7878ed9ce59f6ec496 | 58同城上市首日上涨41.88% | 2013-11-01 07:59:28 |
| 93142044d8cfe8ab3ed5c21be2a538b3 | 腾讯联席CTO熊明华离职 | 2013-11-01 17:02:55 |
| 896fe75c842ef2c6e28ed9f6a7884873 | 阿里小微金融公布股权架构：马云持股不超7.3% | 2013-11-01 09:23:14 |
| 851c98dbddc1883d8733b713ea682325 | 手机预装软件今日起“被规范” 谁将受到冲击？ | 2013-11-01 14:22:52 |
| 85172df9b1c6ee02cb1afcd6ffe2ae66 | 消息称搜狐总编辑刘春将离职 | 2013-10-14 16:24:28 |
| 7c3334c97abd81a8631adc69d95b3a25 | 网易筹备游戏海外战略部 | 2013-11-01 12:24:31 |
| 63e7dd5a6374052661cf7bb97638e905 | Nexus 5万圣夜低调上线 | 2013-11-01 07:59:06 |
| 60f7b46485af71dc375bfd3ae38fd776 | 传Google Hangouts 将整合短信 | 2013-10-09 07:58:55 |
| 5b0f87cf90c27ed9161693c88951afeb | 黑莓或被Facebook收购：双方高管展开接触 | 2013-10-30 10:12:26 |
+----------------------------------+--------------------------------------------+---------------------+
10 rows in set (10.67 sec)
- Потом покажи профили, в профилях уже есть несколько SQL, и последний - это то, что я только что выполнил
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------+
| 1 | 10.62811400 | SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time desc LIMIT 10 |
| 2 | 10.78871825 | SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time desc LIMIT 10 |
| 3 | 0.05494200 | SHOW FULL TABLES WHERE Table_type != 'VIEW' |
| 4 | 0.01013250 | SHOW TABLE STATUS |
| 5 | 0.00034200 | SET profiling = 1 |
| 6 | 10.65613175 | SELECT id,titile,published_at from spider_record where is_analyze=0 ORDER BY create_time desc LIMIT 10 |
+----------+------------+---------------------------------------------------------------------------------------------------------+
6 rows in set (0.07 sec)
3. Посмотрим на конкретную реализацию 6-го sql, выполним
mysql> show profile for query 6;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000215 |
| checking permissions | 0.000017 |
| Opening tables | 0.000085 |
| init | 0.000013 |
| System lock | 0.000020 |
| optimizing | 0.000020 |
| statistics | 0.000049 |
| preparing | 0.000034 |
| Sorting result | 0.000010 |
| executing | 0.000007 |
| Sending data | 10.655393 |
| end | 0.000036 |
| query end | 0.000030 |
| closing tables | 0.000023 |
| freeing items | 0.000151 |
| cleaning up | 0.000033 |
+----------------------+----------+
16 rows in set (0.07 sec)
Из приведенных выше профилей мы можем узнать, что наиболее трудоемкой частью всего SQL является средний этап отправки данных.
Итак, давайте посмотримофициальная документацияКак описать это состояние:
The thread is reading and processing rows for a SELECT statement, and sending data to the client.
Because operations occurring during this state tend to perform large amounts of disk access (reads),
it is often the longest-running state over the lifetime of a given query.
当前线程正在读取和处理一个select语句涉及到的行记录, 并发送到客户端.
出现这个状态时一般是由于当前线程正在频繁地访问磁盘(读磁盘),
所以这个状态一般会占据整个查询的生命周期的大部分时间
Другими словами, общий оператор select заключается в том, что отправка данных занимает большую часть времени (хотя она не должна занимать 10 секунд). Итак, через профили мы до сих пор не знаем, почему этот sql такой медленный.
innodb_buffer_pool_size
После объяснений и показа профиля причину не нашел, сильно сомневаюсь, что с самим sql проблем нет.Остановимся на экземпляре mysql. Первое, что нужно проверить, это параметр innodb_buffer_pool_size.
mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 8388608 |
+-------------------------------------+----------------+
10 rows in set (0.09 sec)
Зацени, innodb_buffer_pool_size всего около 8M, ничего не могу сказать.
Так что взгляните еще разофициальная документация mysql
A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.
设置更大的buffer pool可以避免多次重复地从硬盘读取同一个表的数据, 所以可以减少磁盘I\O. 在专业的数据库服务器中, 你可以把buffer_pool_size的大小设置为物理内存的80%
На официальном сайте указано, что innodb_buffer_pool_size должен занимать 80% физической памяти, поэтому сервер с памятью 8G здесь выставлен на 8m, что точно не подходит.
Поэтому позже мы устанавливаем innodb_buffer_pool_size в размер 1G, а затем выполняем тот же sql, время выполнения сокращается до десятков миллисекунд.
проблема решена.
анализ проблемы
Чтобы сначала рассмотреть наши проблемы, на самом деле их две:
- Почему innodb_buffer_pool_size вызывает медленное выполнение sql
- Почему innodb_buffer_pool_size влияет только на sql, отсортированный в порядке убывания
Чтобы ответить на этот вопрос, давайте рассмотрим несколько терминов движка mysql innodb.
buffer
Буфер обычно представляет собой часть дискового пространства или памяти, используемую для временного хранения.
-
Буферизация данных в памяти может повысить производительность записи, поскольку запись больших блоков данных может сократить количество дисковых операций ввода-вывода по сравнению с многократной записью небольших блоков данных.
-
Буферизация данных на жестком диске более надежна, поскольку в некоторых крайних случаях данные можно восстановить с жесткого диска в случае сбоя системы.
В основном в innodb используется несколько типов буферов, пул буферов, буфер двойной записи, буфер изменений.
buffer pool
Пул буферов — это область памяти, в которой innodb хранит кэшированные данные, включая таблицы и индексы innodb.
-
Для повышения производительности параллельного чтения пул буферов будет разделен на страницы, каждая из которых может хранить несколько строк данных.
-
Для облегчения управления кешем пул буферов выполнен в виде связанного списка со страницей в качестве узла, поэтому некоторые редко используемые данные могут быть удалены в соответствии с алгоритмом LRU.
-
В некоторых системах с относительно большим объемом памяти, если пул буферов относительно велик, пул буферов также можно разделить на несколько экземпляров пула буферов для улучшения параллелизма.
Поскольку данные в пуле буферов находятся в памяти, при завершении работы экземпляра MySQL данные в пуле буферов также будут потеряны.
Когда экземпляр mysql перезапускается, это занимает много времени Повторно согрейте данные в буферном пуле (называется на официальном сайте mysql)warm up).
Начиная с mysql 5.6, путем настройки некоторых системных параметров состояние пула буферов может быть сохранено при завершении работы mysql.После перезапуска mysql пул буферов может быть восстановлен. Благодаря этим двум действиям время прогрева может быть значительно сокращено.
После mysql5.7 автоматическое сохранение и загрузка буферного пула стало конфигурацией по умолчанию.
innodb_buffer_pool_size
innodb_buffer_pool_size — это размер буферного пула. Вообще говоря, значение по умолчанию равно 128 М. В 32-разрядных системах максимальное значение равно 2 в 32-й степени минус 1 байт, а в 64-битных системах максимальное значение равно 2 в 64-й степени минус 1 байт.
Поскольку пул буферов используется как кеш для данных и индексов innodb, в случае бесконечной физической памяти, чем больше пул буферов, тем лучше.Но mysql рекомендует использовать только до 80% физической памяти, в основном для следующие причины Пункты для рассмотрения:
- Конкуренция с операционной системой за память может привести к частым ошибкам страниц в операционной системе, что приведет к снижению производительности всей машины.
- MySQL зарезервирует часть памяти для некоторых других своих структур данных, поэтому фактическое использование памяти будет примерно на 10% больше, чем буферный пул.
- Пул буферов обычно должен выделять непрерывную память, иначе в операционной системе Windows возникнут некоторые проблемы.
- Время инициализации пула буферов пропорционально его размеру.
кластеризованный индекс и вторичный индекс
Кластерный индекс — это кластерный индекс, обычно он относится к индексу первичного ключа, а вторичный индекс относится к обычному индексу.
И кластеризованный индекс, и вспомогательный индекс представляют собой дерево B+. И нелистовые узлы не хранят фактических данных. Данные хранятся в листовых узлах. И каждый листовой узел имеет указатель на следующий листовой узел и предыдущий лист. Кроме того, все листовые узлы образуют двусвязный список, который можно использовать для запросов диапазона.
Кластерный индекс использует первичный ключ (или, возможно, уникальный ключ) в качестве индекса, а конечный узел хранит все данные строки, соответствующей первичному ключу Таким образом, любой столбец данных в этой строке может быть непосредственно найден через первичный ключ.
Вспомогательный индекс использует столбец индекса в качестве индекса, а конечный узел хранит данные столбца индекса и соответствующего первичного ключа.Поэтому, если столбец в выборке является столбцом в индексе, данные можно найти непосредственно в конечный узел Это «покрытие индекса». Но если столбец в операторе выбора не является столбцом в индексе, вы должны найти первичный ключ в листовом узле, а затем использовать первичный ключ для поиска данных соответствующего столбца, который является «таблицей возврата».
До mysql8.0 все индексные деревья innodb располагались в порядке возрастания.Хотя при создании индекса можно указать, будет ли это asc или desc, при фактическом создании индекса все будет asc.До mysql8.0 индекс не поддерживается описание
Хорошо, после разговора о многом, давайте начнем отвечать на первые два вопроса.
-
Почему innodb_buffer_pool_size вызывает медленное выполнение sql
Это очевидно, когда пула буферов не хватает, большинство запросов данных будет приходиться на количество дисков, а производительность дискового ввода-вывода будет на много порядков выше, чем скорость чтения памяти.
-
Почему innodb_buffer_pool_size влияет только на sql, отсортированный в порядке убывания
На самом деле, у меня нет очень определенного ответа на этот вопрос, я могу только строить догадки, используя существующие условия.
Во-первых, мы знаем, что create_time_idx — это восходящее дерево B+, а данные хранятся в листовых узлах, меньшее create_time обычно сосредоточено в левой части дерева, а большее create_time — в правой части. дерево.
Когда sql сортируется в порядке возрастания, mysql должен найти крайний левый конечный узел этого дерева B+, а затем использовать двусвязный список конечных узлов для прямого обхода 10 элементов, соответствующих условиям (is_analyze=0).
Когда sql сортируется в порядке убывания, mysql должен найти самый правый конечный узел этого дерева B+, а затем использовать двусвязный список конечных узлов для прямого обхода 10 фрагментов данных, которые удовлетворяют условиям (is_analyze=0).
Кроме того, поскольку внутренние узлы B+-дерева обычно имеют тысячи указателей, при поиске самого правого конечного узла обычно необходимо пройти по большинству указателей этих внутренних узлов, а при поиске самого левого конечного узла относительно мало указателей пройдено. Таким образом, поиск самого правого листового узла займет немного больше времени, чем поиск самого левого конечного узла.
После нахождения самого левого и самого правого листовых узлов следующим шагом будет просмотр 10 фрагментов данных, соответствующих условиям (is_analyze=0).На данный момент это зависит от того, где в основном распределены целевые данные.Если данные распределены по слева, затем отсортируйте в обратном порядке (начните обход с крайнего правого конечного узла), необходимо пройти больше конечных узлов, что приведет к недостаточному буферному пулу, и, в конечном итоге, потребуется выполнить дисковый ввод-вывод, что приведет к снижению производительности. распределены справа, производительность запроса будет намного лучше.
Чисто текстовое описание будет запутанным и трудным для понимания, на него можно сослаться.эта статьяДве картинки на картинке.Данные цели на картинке распределены "справа", поэтому если сканировать в обратном направлении, то можно сканировать очень быстро.
-
Прямое сканирование
-
обратное сканирование
-
Следующее подтвердит мою гипотезу.
Во-первых, давайте посмотрим на распределение create_time в этой таблице.Вы можете видеть, что create_time имеет данные только за четыре дня, большая часть которых относится к первым двум дням (левая сторона дерева b+).
mysql> SELECT date(create_time) , count(0) from spider_record GROUP BY date(create_time);
+-------------------+----------+
| date(create_time) | count(0) |
+-------------------+----------+
| 2019-06-17 | 52 |
| 2019-06-18 | 141042 |
| 2019-06-19 | 100 |
| 2019-06-20 | 55 |
+-------------------+----------+
4 rows in set (0.18 sec)
Глядя на распределение данных is_analyze=0, действительно все распределено на «левой стороне» дерева b+.
mysql> SELECT date(create_time) , count(0) from spider_record where is_analyze=0 GROUP BY date(create_time);
+-------------------+----------+
| date(create_time) | count(0) |
+-------------------+----------+
| 2019-06-18 | 141042 |
| 2019-06-20 | 55 |
+-------------------+----------+
2 rows in set (0.18 sec)
Затем мы увеличиваем create_time данных 18 июня на два дня.
update spider_record set create_time = create_time + INTERVAL 2 day where date(create_time) = '2019-06-18';
Наконец, посмотрите на распределение данных: все ли это данные или данные с is_analyze=0, большинство из них распределены по «правильной стороне».
mysql> SELECT date(create_time) , count(0) from spider_record GROUP BY date(create_time);
+-------------------+----------+
| date(create_time) | count(0) |
+-------------------+----------+
| 2019-06-17 | 52 |
| 2019-06-19 | 100 |
| 2019-06-20 | 141097 |
+-------------------+----------+
3 rows in set (0.28 sec)
mysql> SELECT date(create_time) , count(0) from spider_record where is_analyze=0 GROUP BY date(create_time);
+-------------------+----------+
| date(create_time) | count(0) |
+-------------------+----------+
| 2019-06-20 | 140683 |
| 2019-06-17 | 52 |
+-------------------+----------+
2 rows in set (0.92 sec)
Наконец, давайте посмотрим на время выполнения этого sql (я выполняю его более 1000 раз подряд в navicat), как показано на рисунке, время находится в пределах нескольких миллисекунд...
Учитывая, что статус mysql мог измениться, или запрос, вызванный другими причинами кеширования, стал быстрее, я изменил данные create_time с 20 июня на 18 июня, то есть выполнил следующий sql
update spider_record set create_time = create_time - INTERVAL 2 day where date(create_time) = '2019-06-20'
Затем взгляните на трудоемкое выполнение этого sql (также выполняется более 1000 раз непрерывно в navicat), результат показан на рисунке, он действительно намного медленнее...
Суммировать
На самом деле, общность этой проблемы не должна быть очень большой, потому что innodb_buffer_pool_size общей производственной среды никогда не будет настроен на 8M.
Кроме того, утверждение о том, что данные, упомянутые выше, распределяются «налево» и «направо», на самом деле немного натянуто, потому что дерево B+ в конце концов является сбалансированным деревом, и нет такой вещи, как смещение к слева направо (однако экспериментальные результаты подтверждают это, я тоже разочарован~~)
Однако из этого устранения неполадок мы можем с высокой степенью уверенности распознать как минимум 2 вещи:
-
Параметр innodb_buffer_pool_size не должен быть слишком маленьким, иначе это сильно повлияет на производительность mysql.
-
Обязательно обратите внимание на порядок по описанию. Хотя mysql 8.0 уже поддерживает индексы по убыванию, но если ваш индекс восходящий и порядок по указанному описанию, дополнительный план запроса mysql все равно укажет, что этот sql будет выполнять "обратный" индекс scan» (на фото ниже), обратите внимание, что он использует обратное сканирование.
Это означает, что обратное сканирование по-прежнему требует больше времени, чем прямое сканирование, поэтому, если можно избежать обратного сканирования в sql, лучше избегать обратного сканирования.