針對多列範圍查詢優化 MySQL 查詢和索引

我有一張這樣的桌子：

CREATE TABLE IF NOT EXISTS `jobs` (
   `job_id` varchar(36) NOT NULL,
   `job_status` varchar(30) NOT NULL,
   `created_at` datetime NOT NULL,
   `lease_date` datetime,
   `priority` int NOT NULL,
   PRIMARY KEY(`job_id`),
   INDEX `job_status_priority_lease_date` (`job_status`, `priority`, `lease_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

我想在表上執行的完整查詢如下：

select * from jobs 
where job_status="IN_PROGRESS"
and lease_date<"10minutesago"
order by 
priority
limit 100

我正在將數據庫實現為作業隊列。10分鐘就像工作超時，我只想對已經超時的工作採取行動。賈伯斯有不同的優先級，所以我按它排序。另外，我想讓我的數據庫作業隊列表現得像 FIFO，這就是為什麼我的索引包含lease_date.

我絕對希望我的結果按優先級排序，例如優先級為 0 和 1 的兩個結果塊。我不需要我的結果嚴格按 排序lease_date，只要它們在自己的塊中按升序排列就可以了對我來說足夠了。例如結果集：

priority lease_date
0        2017-08-19
0        2018-09-20
1        2016-08-20
1        2018-10-20

我的索引是否足夠？我可以在多大程度上優化查詢並建構索引以盡可能提高性能？

你"10minutesago"的意思是NOW() - INTERVAL 10 MINUTE？

order by priority DESC僅在 上排序priority。其中的順序是不可預測的。它可能是上升的，可能是下降的，或者看似隨機的。

你INDEX(job_status, priority, lease_date)不會走得那麼遠lease_date。它會照顧好它，然後where job_status="IN_PROGRESS"它可能會按順序遍歷行priority。但僅此而已。

您可以說ORDER BY priority DESC, lease_date DESC，但and lease_date < "10minutesago"不會使用索引。

考慮這樣建構一個索引：首​​先使用 . 測試任意數量的列=。然後有一個範圍列。

（OP編輯後）

你的例子意味著你需要

ORDER BY priority ASC, lease_date ASC

至於效率問題，…

條款WHERE需要

INDEX(job_status, lease_date)  -- in this order; adding `priority` won't help

如果優化器更願意關注ORDER BY，那麼

INDEX(job_status, priority, lease_date)  -- in this order

筆記：

• job_status是第一個，因為它是用 測試的=。
• 加入只是lease_date使處理WHERE索引中的整個成為可能。但是仍然會有一個“文件排序”來處理ORDER BY.
• 誘使優化器專注於ORDER BY第二個索引（以避免文件排序）可能會也可能不會適得其反。lease_date這取決於由於過濾器而需要跳過多少數據。

如果表中有一千行，只需添加兩個索引；查詢將“足夠快”。如果你有一百萬行，那麼你就會遇到一個棘手的問題，應該重新考慮排隊機制。無論您如何處理索引，有時都會使用錯誤的索引，並且查詢執行速度會非常緩慢。請注意，系統中的一個小問題可能會使隊列偶爾從文明數量的條目跳到巨大的數量。

您是否暗示此表是“隊列”？執行隊列中的一項需要多長時間？如果這是一個“短”時間，那麼“不要排隊，就去做”。

添加priority到哪裡

如果您更改為

select * from jobs 
   where job_status = "IN_PROGRESS"
     AND priority = 1
   and lease_date < NOW() - 10 MINUTE
   order by priority
   limit 100

那麼這些中的任何一個都變得最優：

INDEX(job_status, priority, lease_date)
INDEX(priority, job_status, lease_date)

請參閱我的食譜進行討論。

如果你有 with 和 without 的混合priority，那麼有這兩個：

INDEX(job_status, lease_date),
INDEX(job_status, priority, lease_date)

優化器將在它們之間進行選擇。

引用自：https://dba.stackexchange.com/questions/240109