為什麼這兩個查詢的查詢時間不同？

我有一個帶有大約 70,000,000 行的大表的 MySQL 5.7 數據庫。有 4 列：Id、Time、Value、Monitor_Id。Id是主鍵，Time被索引並且Monitor_Id是外鍵。Value是一個varchar(256)。

我正在做兩個不同的查詢：

SELECT * FROM monitorsamples where Time > '2019-06-28' and Monitor_Id = 19 order by Time desc;

SELECT * FROM monitorsamples where Time > '2019-06-28' and Monitor_Id >= 19 and Monitor_Id <= 35 order by Time desc;

第一個查詢返回大約 10,500 行。第二個查詢返回 50,000 行的上限。第一個查詢大約需要 10 秒才能完成，第二個查詢不到 1/10 秒。我試圖理解為什麼更嚴格的查詢需要更長的時間？

這對我來說毫無意義。有人對此有解釋嗎？

更新：

這是用於創建表的 DDL：

CREATE TABLE `monitorsamples` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Time` datetime NOT NULL,
 `Value` varchar(256) CHARACTER SET utf8 NOT NULL,
 `Monitor_Id` int(11) NOT NULL,
 PRIMARY KEY (`Id`),
 UNIQUE KEY `Id` (`Id`),
 KEY `Monitor_Id` (`Monitor_Id`),
 KEY `Time` (`Time`),
 CONSTRAINT `MonitorSample_Monitor` FOREIGN KEY (`Monitor_Id`) REFERENCES `monitors` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

希望這可以為問題提供一些見解。我認為在優化“x >= y 和 x <= z”而不是更簡單的“x = y”的引擎蓋下肯定發生了一些基本的 DB 魔法。這是非常違反直覺的。

更新 2：

以下是這兩個查詢的解釋。第一個是更簡單的查詢（上面的第一個查詢）。

select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'SIMPLE', 'monitorsamples', NULL, 'ref', 'Monitor_Id,Time', 'Monitor_Id', '4', 'const', '1040640', '5.60', 'Using index condition; Using where; Using filesort'
'SIMPLE', 'monitorsamples', NULL, 'range', 'Monitor_Id', 'Monitor_Id', '4', NULL, '9702114', '100.00', 'Using index condition; Using filesort'

我不確定為什麼第二個查詢不使用where. 看起來第二個查詢使用的排序和選擇機制很少，而且執行速度更快。

更新 3：

我正在為每個查詢添加 JSON 格式的解釋以提供更多詳細資訊。

查詢一：

{
   "query_block" : {
       "cost_info" : {
           "query_cost" : "773972.00"
       },
       "ordering_operation" : {
           "table" : {
               "access_type" : "ref",
               "attached_condition" : "(`monitorsamples`.`Time` > '2019-06-28')",
               "cost_info" : {
                   "data_read_per_join" : "53M",
                   "eval_cost" : "14293.85",
                   "prefix_cost" : "773972.00",
                   "read_cost" : "576000.00"
               },
               "filtered" : "7.22",
               "index_condition" : "(`monitorsamples`.`Monitor_Id` <=> 19)",
               "key" : "Monitor_Id",
               "key_length" : "4",
               "possible_keys" : [
                   "Monitor_Id",
                   "Time"
               ],
               "ref" : [
                   "const"
               ],
               "rows_examined_per_scan" : 989860,
               "rows_produced_per_join" : 71469,
               "table_name" : "monitorsamples",
               "used_columns" : [
                   "Id",
                   "Time",
                   "Value",
                   "Monitor_Id"
               ],
               "used_key_parts" : [
                   "Monitor_Id"
               ]
           },
           "using_filesort" : true
       },
       "select_id" : 1
   }
}

查詢 2：

{
   "query_block" : {
       "cost_info" : {
           "query_cost" : "7243060.61"
       },
       "ordering_operation" : {
           "table" : {
               "access_type" : "range",
               "attached_condition" : "((`monitorsamples`.`Monitor_Id` >= 19) and (`monitorsamples`.`Monitor_Id` <= 35))",
               "cost_info" : {
                   "data_read_per_join" : "804M",
                   "eval_cost" : "215254.77",
                   "prefix_cost" : "7243060.61",
                   "read_cost" : "7027805.84"
               },
               "filtered" : "20.80",
               "index_condition" : "(`monitorsamples`.`Time` > '2019-06-28')",
               "key" : "Time",
               "key_length" : "5",
               "possible_keys" : [
                   "Monitor_Id",
                   "Time"
               ],
               "rows_examined_per_scan" : 5173614,
               "rows_produced_per_join" : 1076273,
               "table_name" : "monitorsamples",
               "used_columns" : [
                   "Id",
                   "Time",
                   "Value",
                   "Monitor_Id"
               ],
               "used_key_parts" : [
                   "Time"
               ]
           },
           "using_filesort" : false
       },
       "select_id" : 1
   }
}

請提供SHOW CREATE TABLE！

你有INDEX(Monitor_Id, Time)嗎？它是第一個查詢的最佳索引。同時，第二個查詢沒有“完美”的索引。我想那INDEX(Time, Monitor_id)將是最好的。

您將如何處理 50K 行的結果集？這將扼殺任何網站。如果您正在處理數據，請考慮在 SQL 中進行部分或全部處理。它可能比將 50K 行推送給客戶端要快得多。

列的某些組合（除 之外id）是唯一的嗎？如果是這樣，我可能可以加快它們的速度。

索引食譜

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