從 MySQL 視圖中選擇 MAX() (2x INNER JOIN) 很慢

February 20, 2018

我想優化我的MySQL 視圖 v_booking。目前我想知道以下問題：

我的查詢

查詢 1 很慢（47.48 秒）：

SELECT MAX(Snapshot_Nummer) FROM v_booking;

查詢 2 很快（0.04 秒）：

SELECT MAX(Snapshot_Nummer) FROM snapshot_data;

查詢 3 很快（0.03 秒）：

SELECT MAX(Snapshot_Nummer) FROM snapshot_booking;

查詢 4 很快（0.03 秒）：

SELECT Snapshot_Nummer FROM v_booking ORDER BY Snapshot_Nummer DESC LIMIT 1;

就我而言，所有 4 個查詢都提供相同的結果。

我的問題

有沒有辦法通過使用MySQL 視圖MAX()快速直接地執行該功能？

COUNT(*)3張表中：

snapshot_data: 5213
snapshot_booking: 4113837
booking_data: 1484

創建表

創建表`snapshot_data`（
`Snapshot_Nummer` int(11) NOT NULL AUTO_INCREMENT,
`Snapshot_time` 日期時間 DEFAULT NULL,
主鍵（`Snapshot_Nummer`）
) 引擎=InnoDB AUTO_INCREMENT=5214 預設字元集=utf8

創建表`snapshot_booking`（
`Magic_PK` int(11) NOT NULL AUTO_INCREMENT,
`Snapshot_Nummer` int(11) NOT NULL,
`Action_Link` int(11) 非空，
`bookingState` int(11) 預設 '0',
主鍵（`Magic_PK`），
KEY `snapshot_number` (`snapshot_number`),
KEY `Action_Link`（`Action_Link`），
約束`snapshot_booking_ibfk_1`外鍵（`Snapshot_Nummer`）參考`snapshot_data`（`Snapshot_Nummer`），
約束`snapshot_booking_ibfk_2`外鍵（`Action_Link`）參考`booking_data`（`Action_Link`）
) 引擎=InnoDB AUTO_INCREMENT=4113838 預設字元集=utf8

創建表`booking_data`（
`Action_Link` int(11) 非空，
`FaMaId` int(11) 預設為空，
`BuchId` int(11) 預設為空，
`MadaId` int(11) 預設為空，
`StationId` int(11) 預設為空，
`BOId` int(11) 預設為空，
`BookingCode` int(11) 預設為空，
`DatumVon` 日期時間 DEFAULT NULL,
`DatumBis` 日期時間 DEFAULT NULL,
`UsedBy` 日期時間 DEFAULT NULL，
`UsedUntil` 日期時間 DEFAULT NULL，
`UsesKM` int(11) 預設為空，
`StornoKZ` tinyint(1) 預設為空，
`VorgaengerBuchId` int(11) 預設為空，
`AngelegtDatum` 日期時間 DEFAULT NULL，
`AutostornoDatum` 日期時間 DEFAULT NULL,
`ChangeDateCustomer` 日期時間 DEFAULT NULL,
`WishId` int(11) 預設為空，
`WagenId` int(11) 預設為空，
`修復` tinyint（1）預設NULL，
`FBNr` int(11) 預設為空，
主鍵（`Action_Link`），
KEY `storno_index` (`StornoKZ`)
) 引擎=InnoDB 預設字元集=utf8

創建視圖

創造
算法 = 未定義
DEFINER = `汽車`@`%`
SQL 安全定義器
查看`v_booking` AS
選擇
`booking_data`.`FaMaId` AS `FaMaId`,
`booking_data`.`BuchId` AS `BuchId`,
ing booking_data`.`MadaId` AS `MadaId`,
`booking_data`.`StationId` AS `StationId`,
`booking_data`.`BOId` AS `BOId`,
`booking_data`.`BookingCode` AS `BookingCode`,
`booking_data`.`DatumVon` AS `DatumVon`,
`booking_data`.`DatumBis` AS` DatumBis`,
`booking_data`.`UsedBy` AS `UsedBy`,
`booking_data`.`UsedUntil` AS `UsedUntil`,
`booking_data`.`UsedKM` AS `UsedKM`,
`booking_data`.`StornoKZ` AS `StornoKZ`,
`booking_data`.`VorgaengerBuchId` AS `VorgaengerBuchId`,
`booking_data`.`AngelegtDatum` AS `AngelegtDatum`,
`booking_data`.`AutostornoDatum` AS `AutostornoDatum`,
`booking_data`.`ChangeDateCustomer` AS `ChangeDateCustomer`,
`booking_data`.`DesiredId` AS `DesiredId`,
`snapshot_data`.`Snapshot_Nummer` AS` Snapshot_Nummer`,
`snapshot_data`.`snapshot_time` AS `snapshot_time`
從
((`snapshot_booking`
加入 `booking_data` ON ((`snapshot_booking`.`Action_Link` = `booking_data`.`Action_Link`)))
加入`snapshot_data` ON ((`snapshot_booking`.`Snapshot_Nummer` = `snapshot_data`.`Snapshot_Nummer`)))

解釋選擇

查詢 1

mysql&gt; EXPLAIN SELECT MAX(Snapshot_Nummer) FROM v_booking;
+----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+
| 編號 | 選擇類型 | 表| 隔斷 | 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 過濾 | 額外 |
+----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+
| 1 | 簡單 | 快照數據 | 空 | 索引 | 初級 | 初級 | 4 | 空 | 5213 | 100.00 | 使用索引 |
| 1 | 簡單 | 快照預訂 | 空 | 參考 | Snapshot_Nummer,Action_Link | Snapshot_Nummer | 4 | carsharing.snapshot_data.Snapshot_Nummer | 第797章 100.00 | 空 |
| 1 | 簡單 | 預訂數據 | 空 | eq_ref | 初級 | 初級 | 4 | carsharing.snapshot_booking.Action_Link | 1 | 100.00 | 使用索引 |
+----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+
3 行，1 個警告（0.04 秒）

查詢 2

mysql&gt; EXPLAIN SELECT MAX(Snapshot_Nummer) FROM snapshot_data;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 編號 | 選擇類型 | 表| 隔斷 | 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 過濾 | 額外 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | 簡單 | 空 | 空 | 空 | 空 | 空 | 空 | 空 | 空 | 空 | 選擇優化掉的表 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 行，1 個警告（0.03 秒）

查詢 3

mysql&gt; EXPLAIN SELECT MAX(Snapshot_Nummer) FROM snapshot_booking;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 編號 | 選擇類型 | 表| 隔斷 | 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 過濾 | 額外 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | 簡單 | 空 | 空 | 空 | 空 | 空 | 空 | 空 | 空 | 空 | 選擇優化掉的表 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 行，1 個警告（0.04 秒）

查詢 4

mysql&gt; EXPLAIN SELECT Snapshot_Nummer FROM v_booking ORDER BY Snapshot_Nummer DESC LIMIT 1;
+----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+
| 編號 | 選擇類型 | 表| 隔斷 | 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 過濾 | 額外 |
+----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+
| 1 | 簡單 | 快照數據 | 空 | 索引 | 初級 | 初級 | 4 | 空 | 1 | 100.00 | 使用索引 |
| 1 | 簡單 | 快照預訂 | 空 | 參考 | Snapshot_Nummer,Action_Link | Snapshot_Nummer | 4 | carsharing.snapshot_data.Snapshot_Nummer | 第797章 100.00 | 空 |
| 1 | 簡單 | 預訂數據 | 空 | eq_ref | 初級 | 初級 | 4 | carsharing.snapshot_booking.Action_Link | 1 | 100.00 | 使用索引 |
+----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+
3 行，1 個警告（0.04 秒）

索引

mysql&gt; 從快照數據中顯示索引；
+---------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 表 | 非唯一 | 鍵名 | Seq_in_index | 列名 | 整理 | 基數| 子部分 | 包裝 | 空 | 索引類型 | 評論 | 索引評論 |
+---------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 快照數據 | 0 | 初級 | 1 | Snapshot_Nummer | 一個 | 5213 | 空 | 空 | | BTREE | | |
+---------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 行成組（0.04 秒）

mysql&gt; 從快照預訂中顯示索引；
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 表 | 非唯一 | 鍵名 | Seq_in_index | 列名 | 整理 | 基數| 子部分 | 包裝 | 空 | 索引類型 | 評論 | 索引評論 |
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 快照預訂 | 0 | 初級 | 1 | 魔法_PK | 一個 | 3959571 | 空 | 空 | | BTREE | | |
| 快照預訂 | 1 | Snapshot_Nummer | 1 | Snapshot_Nummer | 一個 | 4969 | 空 | 空 | | BTREE | | |
| 快照預訂 | 1 | 行動_連結 | 1 | 行動_連結 | 一個 | 1405 | 空 | 空 | | BTREE | | |
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 行（0.03 秒）

mysql&gt; 從 booking_data 顯示索引；
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 表 | 非唯一 | 鍵名 | Seq_in_index | 列名 | 整理 | 基數| 子部分 | 包裝 | 空 | 索引類型 | 評論 | 索引評論 |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 預訂數據 | 0 | 初級 | 1 | 行動_連結 | 一個 | 第1484章 空 | 空 | | BTREE | | |
| 預訂數據 | 1 | storno_index | 1 | 斯托諾KZ | 一個 | 1 | 空 | 空 | 是 | BTREE | | |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 行（0,03 秒）

非常感謝你！

更新 1

Gerard H. Pille 的建議：

mysql&gt; 顯示來自 snapshot_booking 的索引；
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 表 | 非唯一 | 鍵名 | Seq_in_index | 列名 | 整理 | 基數| 子部分 | 包裝 | 空 | 索引類型 | 評論 | 索引評論 |
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 快照預訂 | 0 | 初級 | 1 | 魔法_PK | 一個 | 3959571 | 空 | 空 | | BTREE | | |
| 快照預訂 | 1 | Snapshot_Nummer | 1 | Snapshot_Nummer | 一個 | 4969 | 空 | 空 | | BTREE | | |
| 快照預訂 | 1 | 行動_連結 | 1 | 行動_連結 | 一個 | 1405 | 空 | 空 | | BTREE | | |
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 行（0.03 秒）

mysql&gt; ALTER TABLE snapshot_booking ADD INDEX snapshot_nummer_action_link_index(Snapshot_Nummer, Action_Link);
查詢正常，0 行受影響（7.83 秒）
記錄：0 重複：0 警告：0

mysql&gt; 顯示來自 snapshot_booking 的索引；
+------------------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 表 | 非唯一 | 鍵名 | Seq_in_index | 列名 | 整理 | 基數| 子部分 | 包裝 | 空 | 索引類型 | 評論 | 索引評論 |
+------------------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 快照預訂 | 0 | 初級 | 1 | 魔法_PK | 一個 | 3959571 | 空 | 空 | | BTREE | | |
| 快照預訂 | 1 | 行動_連結 | 1 | 行動_連結 | 一個 | 1405 | 空 | 空 | | BTREE | | |
| 快照預訂 | 1 | snapshot_nummer_action_link_index | 1 | Snapshot_Nummer | 一個 | 5363 | 空 | 空 | | BTREE | | |
| 快照預訂 | 1 | snapshot_nummer_action_link_index | 2 | 行動_連結 | 一個 | 3960314 | 空 | 空 | | BTREE | | |
+------------------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 行（0.04 秒）

mysql&gt; EXPLAIN SELECT MAX(Snapshot_Nummer) FROM v_booking;
+----+-------------+------------------+------------+--------+-----------------------------------------------+-----------------------------------+---------+------------------------------------------+------+----------+-------------+
| 編號 | 選擇類型 | 表| 隔斷 | 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 過濾 | 額外 |
+----+-------------+------------------+------------+--------+-----------------------------------------------+-----------------------------------+---------+------------------------------------------+------+----------+-------------+
| 1 | 簡單 | 快照數據 | 空 | 索引 | 初級 | 初級 | 4 | 空 | 5213 | 100.00 | 使用索引 |
| 1 | 簡單 | 快照預訂 | 空 | 參考 | Action_Link,snapshot_nummer_action_link_index | snapshot_nummer_action_link_index | 4 | carsharing.snapshot_data.Snapshot_Nummer | 第738章 100.00 | 使用索引 |
| 1 | 簡單 | 預訂數據 | 空 | eq_ref | 初級 | 初級 | 4 | carsharing.snapshot_booking.Action_Link | 1 | 100.00 | 使用索引 |
+----+-------------+------------------+------------+--------+-----------------------------------------------+-----------------------------------+---------+------------------------------------------+------+----------+-------------+
3 行，1 個警告（0.06 秒）

mysql&gt; 從 v_booking 中選擇 MAX(Snapshot_Nummer)；
+----------------------+
† MAX(Snapshot_Number) |
+----------------------+
| 5213 |
+----------------------+
1 排成套（4,64 秒）

在 snapshot_booking（snapshot_nummer、action_link）上創建一個組合索引。刪除 snapshot_booking.snapshot_nummer 上的索引。這將避免讀取 snapshot_booking 表，讀取索引就足夠了。

由於這聽起來像是snapshot_booking其他兩個表之間的多：多關係，因此沒有真正需要PRIMARY KEY(Magic_id). 擺脫它並用(snapshot_nummer, action_link)Gerard 建議的複合索引替換它。
如有必要，也有規律INDEX地走另一條路： (action_link, snapshot_nummer)。如果您有任何關於的查詢bookingState，則讓其在兩個訂單之間做出決定——讓 PK 使其對該列更有效，然後INDEX為相反的情況設置一個。
INT是4個字節，範圍很大。如果儲存的值不是那麼大，請考慮將大小縮小到，例如SMALLINT UNSIGNED（2 個字節，範圍：0..65K）。等等。
一切都是真的NULLable嗎？將一些東西更改為NOT NULL.
為什麼snapshot_data存在？它只是從 id 到日期時間的映射？為什麼不使用其他表中的日期時間而不是 id？

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

從 MySQL 視圖中選擇 MAX() (2x INNER JOIN) 很慢

我的查詢

我的問題

創建表

創建視圖

解釋選擇

索引

更新 1

相關問答

Mysql：創建一個具有多個自連接的視圖，結果中沒有重複

MySQL - 查看不在預訂範圍內的選擇房間

具有計算列的視圖上的外部聯接執行缺失（“外部”）記錄的計算

有人可以解釋為什麼在mysql中加入兩個視圖這麼慢嗎？

在兩個內聯視圖上使用左連接的 MySQL 查詢不會在第二個視圖中返回沒有值的行

加入兩個 Mysql VIEW 需要很長時間