В последнее время возникает линейность данных обстановки данных SQL ETL, возникает обновление SQL RAN два дня не закончилось:
update t_order_record set archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', update_time = update_time where order_id in (select order_id from t_retailer_order_record force index (idx_archive_id) where archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa')
Этот SQL на самом делеt_retailer_order_recordсерединаarchive_idза420a7fe7-4767-45e8-a5f5-72280c192faaИдентификатор заказа всех записейorder_id, соответствующие записи в таблице заказовarchive_idТакже обновлено до420a7fe7-4767-45e8-a5f5-72280c192faaА время обновления остается прежним (потому что на таблице стоят триггеры, которые обновляют_время по текущему времени).
Для оптимизации SQL мы можем использовать следующие три инструмента для анализа:
- EXPLAIN: Это относительно простой анализ, фактически не выполняющий SQL, поэтому анализ может быть недостаточно точным и подробным. Но можно найти некоторые ключевые проблемы.
- ПРОФИЛИРОВАНИЕ: Пройдено
set profiling = 1Включить выборку выполнения SQL. Можно проанализировать, на какие этапы выполнения SQL делится и сколько времени занимает каждый этап. SQL должен выполняться и выполняться успешно, а проанализированные этапы недостаточно детализированы.Как правило, оптимизация может быть выполнена только в зависимости от того, существуют ли определенные этапы и как избежать возникновения этих этапов (например, избежать возникновения сортировки памяти и т. д. .). - OPTIMIZER TRACE: Подробно показывает каждый шаг оптимизатора, который необходимо выполнить и успешно выполнить SQL. Оптимизатор MySQL учитывает слишком много факторов, слишком много итераций, а конфигурация довольно сложна. Конфигурация по умолчанию в большинстве случаев подходит, но в некоторых особых случаях могут возникнуть проблемы, и нам потребуется вмешательство человека.
Во-первых, мы ОБЪЯСНЯЕМ этот SQL:
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+
| 1 | UPDATE | t_order_record | NULL | index | NULL | PRIMARY | 8 | NULL | 668618156 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t_retailer_order_record | NULL | ref | idx_archive_id | idx_archive_id | 195 | const | 1 | 10.00 | Using where |
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+
Находитьt_order_recordСтранно, что использование индекса:
- t_order_record имеет индекс для order_id, но здесь полное сканирование первичного ключа (первичный ключ не order_id, а id)
- На самом деле в подзапрос попало только более 30 000 фрагментов данных.
Обычно так и должно бытьОптимизатор SQL обманывает.
Это не может полностью обвинить оптимизатор SQL
Когда мы ежедневно разрабатываем и проектируем таблицы, трудно избежать неразумного использования, будет много индексов и могут быть большие строки. Оптимизатору SQL действительно сложно найти оптимальное решение в такой странной ситуации. Приведу простой пример: допустим, у нас есть таблица с id первичного ключа, есть запись с id = 1, а через год есть запись с id = 1000000. Затем в это время мы одновременно обновляем записи с id = 1 и id = 1000000, тогда данные, которые проходят через другие индексы, но находят только id = 1 и id = 1000000, скорее всего, будут искаться по первичному ключу вместо показатель.Из-за недавнего обновления эти два данных работали на одной странице и в памяти..
Оптимизатор SQL учитывает множество таких сложных ситуаций и может оптимизировать SQL в большинстве случаев, чтобы он больше подходил для текущей ситуации, но поскольку логика слишком сложна, в некоторых простых случаях оптимизация неудовлетворительна, что требует от нас оптимизации в соответствии с результаты OPTIMIZER TRACE были оптимизированы вручную.
Используйте тестовую базу данных для OPTIMIZER TRACE, сначала проанализируйте, нет ли проблем на шагах перед анализом индекса.
Поскольку Optimizer_Trace требует, чтобы SQL действительно выполнялся, этот SQL не выполняется. Optimizer_Trace может анализировать полные шаги оптимизатора, мы можем сначала протестировать тестовую среду с небольшим объемом данных, см. перед входом в анализ статистики (Например, анализ дискретных данных индекса, чтобы решить, к какому индексу перейти, это невозможно смоделировать в тестовой среде, потому что должны быть различия между данными и онлайном, даже если данные онлайн копируются, это не получится, т.к. на каких страницах данные, и как обновлялся индекс, структура файла отличается от онлайна, информация статистика точно не будет точно такой же), есть ли проблема с преобразованием перезаписи SQL.
воплощать в жизнь:
mysql> set session optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.20 sec)
mysql> update t_order_record set archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', update_time = update_time where order_id in (select order_id from t_retailer_order_record force index (idx_archive_id) where archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa');
Query OK, 0 rows affected (2.95 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE;
steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `main`.`t_retailer_order_record`.`order_id` from `main`.`t_retailer_order_record` FORCE INDEX (`idx_archive_id`) where (`main`.`t_retailer_order_record`.`archive_id` = '420a7fe7-4767-45e8-a5f5-72280c192faa')"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": false
}
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "EXISTS (CORRELATED SELECT)",
"chosen": true,
"evaluating_constant_where_conditions": [
]
}
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"condition_processing": {
"condition": "WHERE",
## 以下省略
С Optimizer_trace мы обнаружили, что есть проблема с оптимизацией!Оптимизировать IN до EXISTS. Это заставляет нас думать, чтоИспользуйте каждую запись подзапроса для сопоставления с записями внешней таблицы порядка.,сталПросмотрите каждую запись во внешней таблице порядка, чтобы увидеть, существует ли она в подзапросе., что также объясняет, почему результат объясненияПройдите каждую запись таблицы заказов по первичному ключусделать запрос.
Если это нужно изменить,Вы можете изменить способ написания только для адаптации, не отключая опцию оптимизатора.
Итак, мы переписываем и оптимизируем SQL (используя JOIN, JOIN — самый близкий способ написания SQL, наиболее понятный оптимизатору), и добавляем временные условия (мы хотим оперировать данными только 179 дней назад, этот archive_id соответствует данные все 179 дней назад), так как сам идентификатор заказа имеет время (начиная со времени, например, 211211094621ord123421 представляет заказ в 9:46:21 11 декабря 2021 года), поэтому используйте идентификатор заказа, чтобы ограничить время:
UPDATE t_order_record
JOIN t_retailer_order_record ON t_order_record.order_id = t_retailer_order_record.order_id
SET t_order_record.archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa',
t_order_record.update_time = t_order_record.update_time
WHERE
t_order_record.order_id < DATE_FORMAT( now() - INTERVAL 179 DAY, '%y%m%d' )
AND t_retailer_order_record.order_id < DATE_FORMAT( now() - INTERVAL 179 DAY, '%y%m%d' )
AND t_retailer_order_record.archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa'
Последующий опыт оптимизации
Если вы столкнулись с такой ситуацией, когда выполнение выполняется очень медленно, но обновление затрагивает очень мало данных и есть индексы, которые должны быть там, вы можете сначала использовать тестовую среду с небольшим объемом данных, чтобы просмотреть их, прежде чем приступить к статистическому анализу данных. (Например, анализ дискретных данных индекса, чтобы решить, к какому индексу перейти, это невозможно смоделировать в тестовой среде, потому что должны быть различия между данными и онлайном, даже если данные онлайн копируются, это не получится, т.к. на каких страницах данные, и как обновлялся индекс, структура файла отличается от онлайна, информация статистика точно не будет точно такой же), есть ли проблема с преобразованием перезаписи SQL.
Если есть проблемы, рассмотрите возможность вмешательства человека для ручной оптимизации. К методам ручной оптимизации относятся:
- заставить индекс использовать индекс
- Отключите определенные параметры оптимизатора MySQL для текущего сеанса.
- Перепишите SQL, чтобы его было легче понять оптимизатору (JOIN проще всего понять оптимизатору SQL).
Ищите «My Programming Meow» в WeChat, подписывайтесь на официальный аккаунт, чистите каждый день, легко улучшайте свои технологии и получайте различные предложения.: