我有一個非常大的 IOT 樣本表，我正在嘗試針對它運行一個相對簡單的查詢。使用 MySql CLI 正常運行查詢會在 ~0.07 秒內回傳結果。如果我首先通過 PDO 或通過運行 SQL PREPARE陳述句準備查詢，則請求需要一分鐘以上。
我啟用了優化器跟蹤功能，看起來在準備陳述句時，MySql 會忽略它應該使用的索引并對整個表進行檔案排序。如果我做錯了什么或者這看起來像一個 MySql 錯誤，我想要任何見解。
該表本身包含超過 1 億個樣本，至少有 30 萬個與此處查詢的設備相關聯。我使用 MySql 8.0.23 運行了這些測驗，但是當我升級到 8.0.25 時問題仍然存在。
表定義（部分資料行省略）
Create Table: CREATE TABLE `samples` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`organization_id` int unsigned NOT NULL,
`device_id` int unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`raw_reading` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `samples_organization_id_foreign` (`organization_id`),
KEY `samples_reverse_device_id_created_at_organization_id_index` (`device_id`,`created_at` DESC,`organization_id`),
CONSTRAINT `samples_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `samples_organization_id_foreign` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=188315314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
在 < 1 秒內運行的 Sql
select *
from `samples`
where `samples`.`device_id` = 5852
and `samples`.`device_id` is not null
and `id` != 188315308
order by `created_at` desc
limit 1;
運行超過一分鐘的 Sql
prepare test_prep from 'select * from `samples` where `samples`.`device_id` = ? and `samples`.`device_id` is not null and `id` != ? order by `created_at` desc limit 1';
set @a = 5852;
set @b = 188315308;
execute test_prep using @a, @b;
可以在我的要點中找到未準備好的 SQL 的跟蹤，但相關部分是
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
],
"index_order_summary": {
"table": "`samples`",
"index_provides_order": true,
"order_direction": "asc",
"index": "samples_reverse_device_id_created_at_organization_id_index",
"plan_changed": false
}
}
},
可以在我的其他要點中找到準備好的查詢的跟蹤，但相關部分是
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
],
"index_order_summary": {
"table": "`samples`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "samples_reverse_device_id_created_at_organization_id_index",
"plan_changed": false
}
}
},
uj5u.com熱心網友回復：
您要使用的索引還不錯：
`samples_reverse_device_id_created_at_organization_id_index`
(`device_id`,`created_at` DESC,`organization_id`)
但是，不是覆寫索引。如果查詢性能真的很重要，我會添加一個至少涵蓋過濾謂詞的索引。由于您正在檢索所有列，因此您不需要真正的覆寫索引。我會嘗試：
create index ix1 on samples (device_id, created_at, id);
編輯
另一個可以促進索引使用的技巧是盡可能延遲謂詞id != 188315308。如果您知道此謂詞將與其余謂詞生成的前 100 行中的至少一行匹配，您可以嘗試將查詢重新表述為：
select *
from (
select *
from `samples`
where `samples`.`device_id` = 5852
order by `created_at` desc
limit 100
) x
where `id` != 188315308
order by `created_at` desc
limit 1
uj5u.com熱心網友回復：
擺脫這個，因為= 5852保證它會是假的：
and `samples`.`device_id` is not null
那么您的索引或這個索引應該可以正常作業。
INDEX(device_id, created_at, id)
不要使用@variables；優化器似乎沒有查看它們包含的值。也就是說，而不是
set @a = 5852;
set @b = 188315308;
execute test_prep using @a, @b;
簡單地做
execute test_prep using 5852, 188315308;
考慮在 bugs.mysql.com 上撰寫錯誤報告
我懷疑"order_direction": "undefined"是問題的一部分。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/318718.html