優化 MySQL SELECT 語句中 TIMESTAMP 欄位的 WHERE 條件

我正在研究用於跟踪使用時間的分析系統的架構，並且需要查看特定日期範圍內的總使用時間。

舉一個簡單的例子，這種類型的查詢會經常執行：

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

此查詢通常需要大約 7 秒的時間在一個高度填充的表上。它有大約 3500 萬行，MyISAM on MySQL 在 Amazon RDS (db.m3.xlarge) 上執行。

去掉 WHERE 子句使得查詢只需要 4 秒，而添加第二個子句 (time_off > XXX) 則額外增加了 1.5 秒，使查詢時間達到 8.5 秒。

因為我知道這些類型的查詢通常會被完成，所以我想優化一些東西，使它們更快，最好低於 5 秒。

我首先在 time_on 上添加一個索引，儘管這大大加快了 WHERE “=” 查詢的速度，但它對 “>” 查詢沒有影響。有沒有辦法創建一個索引來加速 WHERE “>” 或 “<” 查詢？

或者如果對此類查詢的性能有任何其他建議，請告訴我。

注意：我使用“diff_ms”欄位作為非規範化步驟（它等於 time_off - time_on），它將聚合性能提高了大約 30%-40%。

我正在使用以下命令創建索引：

ALTER TABLE writetest_table ADD INDEX time_on (time_on) USING BTREE;

在原始查詢上執行“解釋”（使用“time_on >”）表示 time_on 是“possible_key”，而 select_type 是“SIMPLE”。“extra”列顯示“Using where”，“type”是“ALL”。添加索引後，表格顯示“time_on”是“MUL”鍵類型，這似乎是正確的，因為同一時間可以出現兩次。

這是表架構：

CREATE TABLE `writetest_table` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `sessionID` int(11) DEFAULT NULL,
 `time_on` timestamp NULL DEFAULT NULL,
 `time_off` timestamp NULL DEFAULT NULL,
 `diff_ms` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `time_on` (`time_on`)
) ENGINE=MyISAM AUTO_INCREMENT=50410902 DEFAULT CHARSET=latin1;

更新：我根據 ypercube 的響應創建了以下索引，但這會將第一個查詢的查詢時間增加到 17 秒左右！

ALTER TABLE writetest_table  ADD INDEX time_on__diff_ms__ix (time_on, diff_ms) ;

更新 2：解釋輸出

mysql> explain select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56';
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
| id | select_type | table               | type  | possible_keys        | key                  | key_len | ref  | rows     | Extra                    |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | writetest_table_old | index | time_on__diff_ms__ix | time_on__diff_ms__ix | 10      | NULL | 35831102 | Using where; Using index |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

更新 3：請求查詢的結果

mysql> SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;
+---------------------+
| time_on             |
+---------------------+
| 2015-07-13 15:11:56 |
+---------------------+
1 row in set (0.01 sec)

我想我開始明白了。

當我讓你跑步時

SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;

你說這是2015-07-13 15:11:56你的WHERE條款

當您進行查詢時

select sum(diff_ms) from writetest_table;

它執行了 3580 萬行的全表掃描。

當您進行查詢時

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

它執行了 3580 萬行的完整索引掃描。

沒有 WHERE 子句的查詢更快是完全有道理的。為什麼 ？

表掃描將在一次線性傳遞中讀取 3580 萬行。

使用 WHERE 查詢的 EXPLAIN 也出現了 3580 萬行。索引掃描的行為會有所不同。雖然 BTREE 保持鍵的順序，但進行範圍掃描是可怕的。在您的特定情況下，您正在執行最差的範圍掃描，它的 BTREE 條目數與表中的行數相同。MySQL 必須遍歷 BTREE 頁面（至少跨葉節點）才能讀取值。此外，time_on必須按照索引指定的順序對列進行比較。因此，也必須遍歷非葉 BTREE 節點。

請參閱我在 BTREE 上的文章

• Aug 06, 2013：在 MySQL 中，如果 X 列具有唯一值，那麼 UNIQUE 索引和 B-Tree 索引有什麼區別
• Jun 28, 2012: BTREE 在 MySQL 中的好處

如果查詢截至今天午夜

select sum(diff_ms) from writetest_table where time_on >= ("2015-07-14 00:00:00");

甚至今天中午

select sum(diff_ms) from writetest_table where time_on >= ("2015-07-14 12:00:00");

它應該花費更少的時間。

**故事的道德：**不要使用 WHERE 子句，它執行的有序範圍掃描等於目標表中的行數。

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