Mysql

在對 LIMIT 進行最小更改時，對兩個索引列的 BETWEEN 查詢以指數方式花費更長的時間

  • November 26, 2018

這是我的問題的一個最小範例，但我用這種結構重現了這個問題。我有下表：

| column name    | type        |
|----------------+-------------|
| id             | PRIMARY KEY |
| id_start       | INT         |
| id_end         | INT         |
|----------------+-------------|

該表包含大約十億 (1,000,000,000) 行。和_start列_end被索引。這是使用 Btree 索引的 InnoDB。

一些背景資訊：這是帶有開始和結束時間戳的感測器數據。時間戳本身保存在另一個表中，該表引用自該表。因此，我只使用id這裡的列。

以下查詢返回大約 15,000 行：

SELECT * FROM table WHERE 310000 BETWEEN id_start AND id_end;

問題是查詢需要很長時間才能返回任何內容（MySQL Workbench 持續時間 1 秒/獲取 220 秒）。

如果我附加LIMIT 14000到查詢中，它幾乎會立即返回。參數越接近LIMIT預期的 15000 結果行，查詢所需的時間就越長。

獲取 14,000 行與 15,000 行所需的時間呈指數增長，這可能是什麼原因？

還有一些我不確定它們是否與這個問題相關的資訊：

  • DB 伺服器配置良好（InnoDB 緩衝區等）。
  • DB 伺服器的 CPU 功率和記憶體比所需的要多。整個數據集適合記憶體。
  • 該表中的所有列都不能為空。

此處要求提供有關表結構和查詢的附加資訊。

CREATE TABLE `table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_start` int(11) NOT NULL,
 `id_end` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_end` (`id_end`),
 KEY `idx_start` (`id_start`),
 KEY `idx_start_end` (`id_start`,`id_end`),
 KEY `idx_end_start` (`id_end`,`id_start`)
) ENGINE=InnoDB;

這裡是查詢的 EXPLAIN 語句：

+----+-------------+-------+------------+-------+-------------------------------------------------+---------------+---------+------+----------+----------+---------------+
| id | select_type | table | partitions | type  |                  possible_keys                  |      key      | key_len | ref  |   rows   | filtered |     Extra     |
+----+-------------+-------+------------+-------+-------------------------------------------------+---------------+---------+------+----------+----------+---------------+
|  1 | SIMPLE      | table | NULL       | range | "idx_start,idx_end,idx_start_end,idx_end_start" | idx_end_start |       9 | NULL | 20509120 |    50.00 | "Using where" |
+----+-------------+-------+------------+-------+-------------------------------------------------+---------------+---------+------+----------+----------+---------------+

這是低的結果LIMIT EXPLAIN：

'{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "27795648.61"
   },
   "table": {
     "table_name": "table",
     "access_type": "range",
     "possible_keys": [
       "idx_start",
       "idx_end",
       "idx_start_end",
       "idx_end_start"
     ],
     "key": "idx_end_start",
     "used_key_parts": [
       "id_end"
     ],
     "key_length": "9",
     "rows_examined_per_scan": 19854034,
     "rows_produced_per_join": 9927018,
     "filtered": "50.00",
     "index_condition": "(310300 between `table`.`id_start` and `table`.`id_end`)",
     "cost_info": {
       "read_cost": "25810244.97",
       "eval_cost": "1985403.64",
       "prefix_cost": "27795648.61",
       "data_read_per_join": "4G"
     },
     "used_columns": [
       "id",
       "id_start",
       "id_end"
     ]
   }
 }
}'

這是高的結果LIMIT EXPLAIN：

'{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "27203639.01"
   },
   "table": {
     "table_name": "table",
     "access_type": "range",
     "possible_keys": [
       "idx_start",
       "idx_end",
       "idx_start_end",
       "idx_end_start"
     ],
     "key": "idx_end_start",
     "used_key_parts": [
       "id_end"
     ],
     "key_length": "9",
     "rows_examined_per_scan": 19431170,
     "rows_produced_per_join": 9715586,
     "filtered": "50.00",
     "index_condition": "(310300 between `table`.`id_start` and `table`.`id_end`)",
     "cost_info": {
       "read_cost": "25260521.78",
       "eval_cost": "1943117.23",
       "prefix_cost": "27203639.01",
       "data_read_per_join": "4G"
     },
     "used_columns": [
       "id",
       "id_start",
       "id_end"
     ]
   }
 }
}'

我看到兩個問題：

  • 有什麼LIMIT影響性能？
  • （真正的問題。）如何加快對這種表的這種查詢。

這是 MySQL 中經典的“艱鉅任務”——在“開始”和“結束”值之間尋找元素。

我在http://mysql.rjweb.org/doc.php/ipranges中使用了一些額外的複雜性來解決它。但是，它要求範圍不重疊。（目前尚不清楚您的範圍是否會重疊。）

你說這id_start真的是指向另一個保存實際時間戳的表的指針嗎？然而你使用WHERE 310000 BETWEEN id_start AND id_end. 這意味著id_start並且id_end是按時間順序排列的，儘管實際上不是“時間”的表示？同上310000？也許這是使用 4 字節 INT 而不是更大的TIMESTAMP(6)東西？

有了EXPLAIN FORMAT=JSON SELECT ...，我們或許能夠弄清楚為什麼更改LIMIT值會影響性能。

時間分佈有多均勻？以下是您所看到的差異的猜測： 對於 的值較低LIMIT，優化器的統計數據表明某個查詢計劃會很快找到 14K，但如果值較大，它會推斷它可能需要搜尋很遠才能找到 15K行。優化器沒有“時間範圍”的概念。因此它只能使用INDEX(start)or INDEX(end); 它不知道您知道並可以用來優化查詢的start<end其他內容。

你還需要id什麼嗎？表中是否還有其他列？ 這兩個都是重要的問題，因為以下優化可能會失敗，具體取決於您的答案。

對於這個 3 列表，我會考慮以下替代方案之一：

CREATE TABLE x (
   start_id INT UNSIGNED NOT NULL,
   end_id   INT UNSIGNED NOT NULL,
   PRIMARY KEY(start_id, end_id),  -- assumed unique
   INDEX      (end_id, start_id)
ENGINE=InnoDB;
-- assumes start-end pairs are unique
-- with a billion rows; fewer indexes is beneficial

CREATE TABLE x (
   start_id INT UNSIGNED NOT NULL,
   end_id   INT UNSIGNED NOT NULL,
   id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(start_id, end_id, id),  -- id assures uniqueness
   INDEX      (end_id, start_id),
   INDEX(id) -- sufficient to keep AUTO_INCREMENT happy
ENGINE=InnoDB;
-- does not assume start-end pairs are unique
-- allows for FK to `id`

筆記：

  • 通過讓 PK 開始start_id可能有助於提高性能。
  • start..end 查詢的性能沒有得到解決。
  • 如果有更多列，我還沒有評估這些模式的好處。

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

相關問答

Mysql

mysql 簡單更新 4000 萬和 128GB RAM 花費太多時間

  • March 3, 2022
檢視問答
Mysql

優化慢查詢

  • January 9, 2022
檢視問答
Mysql

針對 SUM 和 Order By 優化 MySQL InnoDB 查詢

  • April 19, 2019
檢視問答
Mysql

如何使用各種排序優化 MySQL 查詢的索引

  • March 25, 2017
檢視問答
Mysql

為什麼 Mysql 在連接時不使用索引

  • February 14, 2017
檢視問答
Mysql

查詢中的 int 比較非常慢

  • July 22, 2015
檢視問答