優化查詢或模型，及時獲得人口概況

我想我有一個非常簡單的模型和查詢，但是我找不到利用索引來提高性能的方法。

我想知道我的模型或 MySQL 查詢中是否有一個技巧來加快執行時間，以便在特定時間了解我的人口概況。

我有下表，其中包含我的使用者的狀態（歷史）：

CREATE TABLE `status` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(9) NOT NULL,
`status` enum('NEW','REGISTERED','CANCELED') NOT NULL,
`timestamp` datetime NOT NULL,
`explanation` varchar(25) NOT NULL,

PRIMARY KEY (`id`),
KEY `index1` (`user_id`,`timestamp`,`id`),
KEY `index2` (`timestamp`,`user_id`,`id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8

我的表將容納很多使用者（500 萬），它們可以有一個或多個狀態（因此該表可能會容納 10+ 百萬條記錄）。記錄按時間順序插入，因此 ID 和時間戳是“同步的”（時間戳不需要唯一，但自動增量 ID 是）

為了在特定時間了解我的人口概況，我所做的是：

SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;

這給了我正確的 ID，但是由於 MySQL 在對不在 GROUP BY 中的列（對於 where 子句）進行排序時不能使用索引，所以沒有性能。

解釋告訴我：

explain SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys       | key                 | key_len | ref  | rows     | Extra                                                     |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | range | index2              | index2              | 8       | NULL | 13897517 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+

有誰知道我如何優化我的模型或查詢（例如使用子查詢）（以允許 MySQL 使用索引），從而提高性能？

我最終想要的是：

SELECT user_id, status, timestamp, explanation FROM `status` WHERE id IN (
 SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id
);

好的，我試圖重現您的案例：

mysql> insert into status values (1,'a','NEW',NOW(),'test'); Query OK, 1 row affected (0.00 sec)

mysql> insert into status values (2,'b','NEW',NOW(),'test2'); Query OK, 1 row affected (0.00 sec)

mysql> insert into status values (3,'c','NEW',NOW(),'test3'); Query OK, 1 row affected (0.00 sec)

mysql> explain  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+
|  1 | SIMPLE      | status | index | index2        | index1 | 41      | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+

Mysql 使用你想要的索引。

然後：

mysql> alter table status drop index index1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra                                                     |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | index | index2        | index2 | 41      | NULL |    3 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

計劃和你一樣。請確保 index1 確實存在。

我正在執行 mysql 5.5.33-31.1-log 你甚至可以嘗試 5.6。

也請嘗試強制 index1 並發布結果。

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