mysql - 对 MySQL 表进行分区后创建索引？

Question

我有一个包含 2000 万条记录的 mysql 表 Stop_Times。

我正在为这个表使用 MyISAM 存储引擎。

我的表结构是

| agency_id           | varchar(50) | NO   |     | NULL    |       |
| trip_id             | varchar(50) | NO   | PRI | NULL    |       |
| arrival_time        | time        | NO   |     | NULL    |       |
| departure_time      | time        | NO   |     | NULL    |       |
| stop_id             | varchar(50) | NO   | PRI | NULL    |       |
| stop_sequence       | int(11)     | NO   | PRI | NULL    |       |
| route_id            | varchar(50) | NO   |     | NULL    |       |
| route_type          | int(5)      | NO   |     | NULL    |       |
+---------------------+-------------+------+-----+--------

show create table Stop_Times 的输出是 -

CREATE TABLE `Stop_Times` (
 `agency_id` varchar(50) NOT NULL,
 `trip_id` varchar(50) NOT NULL,
 `arrival_time` time NOT NULL,
 `departure_time` time NOT NULL,
 `stop_id` varchar(50) NOT NULL,
 `stop_sequence` int(11) NOT NULL,
 `route_id` varchar(50) NOT NULL,
 `route_type` int(5) NOT NULL,
 UNIQUE KEY `idx_Stop_Times` (`agency_id`,`trip_id`,`stop_sequence`,`stop_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (stop_id)
PARTITIONS 250 */

我有一个问题

select distinct trip_id, stop_sequence from Stop_Times where agency_id = ? and stop_id = ?

在对表进行分区之前，此查询花费了超过 4-5 分钟。

但现在我已经按照 stop_id 对表进行了分区，例如按键分区(stop_id)

现在执行此查询需要 2-3 秒。

我在上面的查询之后有另一个查询 -

select distinct(stop_id) from Stop_Times where agency_id = ? and trip_id = ? and stop_sequence > ? 

explain 对上述查询的输出是 -

1   SIMPLE  Stop_Times  range   idx_Stop_Times  idx_Stop_Times  308 NULL    250 Using where; Using index; Using temporary

此查询需要更多时间 90 - 150 秒。

所以我的问题是我需要在 trip_id 和 stop_sequence 上创建索引吗？这会加快查询性能吗？

我是否需要更改存储引擎从 MyISAM 到 InnoDB，因为一次有多个用户对该表进行多次读取(选择查询)？

请数据库专家帮助我。

谢谢

Answer 1

在 (agency_id, trip_id, stop_sequence, stop_id) 上创建覆盖索引。请注意，索引中列的顺序很重要。使用不同的顺序可能效率较低。