На этой неделе получено часовое предупреждение, время ожидания следующего SQL-запроса истекло.
select * from order_info where uid = 5837661 order by id asc limit 1
воплощать в жизньshow create table order_info обнаруживает, что таблица на самом деле проиндексирована
CREATE TABLE `order_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned,
`order_status` tinyint(3) DEFAULT NULL,
... 省略其它字段和索引
PRIMARY KEY (`id`),
KEY `idx_uid_stat` (`uid`,`order_status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Теоретически выполнение приведенного выше SQL приведет к попаданию в индекс idx_uid_stat, но на самом деле выполните объяснение для просмотра.
explain select * from order_info where uid = 5837661 order by id asc limit 1
Вы можете видеть, что его возможные_ключи (индексы, которые может использовать этот SQL) — это idx_uid_stat, но на самом деле (ключ) использует полное сканирование таблицы.
Мы знаем, что MySQL выбирает на основе стоимости выполнение окончательного плана выполнения на основе полного сканирования таблицы или индекса, поэтому кажется, что стоимость полного сканирования таблицы меньше, чем на основе индекса idx_uid_stat, но мое первое ощущение очень странно, хотя этот SQL является таблицей возврата, его предел равен 1, а это значит, что выбирается только одно из утверждений, удовлетворяющее uid = 5837661. Даже если таблица возвращается, возвращается только одна запись. Эта стоимость практически ничтожна Как оптимизатор выбирает полное сканирование таблицы?
Конечно, сомнения сомнительны.Чтобы понять, почему оптимизатор MySQL выбрал полное сканирование таблицы, я открыл optimizer_trace, чтобы выяснить это.
голос за кадром: В MySQL 5.6 и более поздних версиях мы можем использовать функцию трассировки оптимизатора для просмотра всего процесса создания оптимизатором плана выполнения.
Конкретный процесс использования optimizer_trace выглядит следующим образом.
SET optimizer_trace="enabled=on"; // 打开 optimizer_trace
SELECT * FROM order_info where uid = 5837661 order by id asc limit 1
SELECT * FROM information_schema.OPTIMIZER_TRACE; // 查看执行计划表
SET optimizer_trace="enabled=off"; // 关闭 optimizer_trace
Оптимизатор MySQL сначала вычисляет стоимость полного сканирования таблицы, затем выбирает все индексы, которые могут быть задействованы в SQL, и вычисляет стоимость индекса, а затем выбирает тот, который требует наименьшей стоимости для выполнения, посмотрите на ключ, заданный информация трассировки оптимизатора
{
"rows_estimation": [
{
"table": "`rebate_order_info`",
"range_analysis": {
"table_scan": {
"rows": 21155996,
"cost": 4.45e6 // 全表扫描成本
}
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_uid_stat",
"ranges": [
"5837661 <= uid <= 5837661"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 255918,
"cost": 307103, // 使用idx_uid_stat索引的成本
"chosen": true
}
],
"chosen_range_access_summary": { // 经过上面的各个成本比较后选择的最终结果
"range_access_plan": {
"type": "range_scan",
"index": "idx_uid_stat", // 可以看到最终选择了idx_uid_stat这个索引来执行
"rows": 255918,
"ranges": [
"58376617 <= uid <= 58376617"
]
},
"rows_for_plan": 255918,
"cost_for_plan": 307103,
"chosen": true
}
}
...
Видно, что стоимость полного сканирования таблицы составляет 4,45e6, а стоимость выбора индекса idx_uid_stat равна 307103, что намного меньше стоимости полного сканирования таблицы, а из итогового результата выбора (chosen_range_access_summary) индекс idx_uid_stat действительно выбрано, но почему выбор из объяснения состоит в том, чтобы выполнить PRIMARY, то есть полное сканирование таблицы, этот план выполнения неверен?
Я внимательно посмотрел план выполнения и обнаружил кое-что сложное: в плане выполнения есть опция reconsidering_access_paths_for_index_ordering, которая привлекла мое внимание.
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`rebate_order_info`",
"index_provides_order": true,
"order_direction": "asc",
"index": "PRIMARY", // 可以看到选择了主键索引
"plan_changed": true,
"access_type": "index_scan"
}
}
}
Этот выбор указывает, что другая оптимизация выбора индекса выполняется из-за сортировки.Поскольку наш SQL использует сортировку по идентификатору (порядок по идентификатору asc limit 1), оптимизатор, наконец, выбирает PRIMARY, который представляет собой полное сканирование таблицы, для выполнения, что означает этот выбор проигнорирует предыдущий выбор на основе стоимости индекса.Почему существует такая опция?Основные причины следующие:
The short explanation is that the optimizer thinks- или я должен сказатьhopes— что сканирование всей таблицы (которая уже отсортирована по полю id) достаточно быстро найдет ограниченные строки, и это позволит избежать операции сортировки.Поэтому, пытаясь избежать сортировки, оптимизатор в конечном итоге теряет время на сканирование Таблица.
Из этого объяснения видно, что основная причина заключается в том, что оптимизатор считает сортировку дорогостоящей операцией, потому что мы используем порядок по id asc, который является методом сортировки на основе id, поэтому во избежание сортировки исчитатьЕсли n предельного n очень мало, даже если используется полное сканирование таблицы, оно может быть выполнено быстро, поэтому использование полного сканирования таблицы также позволяет избежать сортировки id (полное сканирование таблицы на самом деле является сканированием кластеризованного индекса на основе по первичному ключу id, сам сортируется по id)
Хорошо, если этот выбор правильный, но на самом деле эта оптимизация глючит! Фактический выбор idx_uid_stat будет выполняться намного быстрее (всего 28 мс)! Многие люди в Интернете сообщали об этой проблеме, и эта проблема в основном возникает только в SQL.order by id asc limit nТакой способ записи связан: если n относительно мало, велика вероятность, что будет выполнено полное сканирование таблицы, а если n относительно велико, будет выбран правильный индекс.
Эта ошибка восходит к 2014. Многие люди обращались к официальному лицу, чтобы вовремя исправить эту ошибку. Это может быть сложно реализовать. Это не было решено до MySQL 5.7 и 8.0. Поэтому мы должны стараться избегать такого типа написания до официального исправления.Есть два основных способа его избежать
- Используйте индекс силы, чтобы принудительно указать указанный индекс, как показано ниже:
select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1
Хотя такой способ написания возможен, он не элегантен Что делать, если от индекса отказались? Так что есть второе, более элегантное решение
- Используйте схему заказа по (id+0) следующим образом
select * from order_info where uid = 5837661 order by (id+0) asc limit 1
Эта схема также позволяет оптимизатору выбрать правильный индекс, что более рекомендуется!
плечи гигантов
- ошибка оптимизатора mysql4zsw5.cn/L1zEi
Наконец, обратите внимание, оригинальность не так проста, вам нужны положительные отзывы, приглашаю всех обратить внимание на мой публичный аккаунт ».код море», вместе продвигаться вперед, вместе быть потрясающими