mysql - 为什么我的类似查询的处理方式大不相同？

Question

我们有一张 table

mysql> show create table channeldata\G
*************************** 1. row ***************************
Table: channeldata
Create Table: CREATE TABLE channeldata (
channel_id smallint(3) unsigned NOT NULL,
station_id smallint(5) unsigned NOT NULL,
time datetime NOT NULL,
reading double NOT NULL DEFAULT '0',
average double NOT NULL DEFAULT '0',
location_lat double NOT NULL DEFAULT '0',
location_lon double NOT NULL DEFAULT '0',
location_alt double(8,3) DEFAULT '0.000',
quality smallint(3) unsigned DEFAULT '0',
PRIMARY KEY (channel_id,station_id,time),
KEY composite3 (station_id,channel_id,quality) USING BTREE,
KEY composite (channel_id,station_id,time,quality) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

最近我注意到我们做的一些选择查询需要很长时间才能完成。奇怪的是，根据 where 子句中列的值，选择要么非常快地完成，要么需要很长时间来阻止对同一个表的更新。我通过解释运行了这些查询:

mysql> explain SELECT reading FROM channeldata WHERE station_id = 6001
AND channel_id = 1 AND time < '2018-09-20T14:58:00'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: channeldata
partitions: NULL
type: ref
possible_keys: PRIMARY,composite3,composite
key: PRIMARY
key_len: 4
ref: const,const
rows: 176539
filtered: 33.33
Extra: Using index condition 1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT reading FROM channeldata WHERE station_id = 6001 AND channel_id = 4 AND time < '2018-09-20T14:58:00'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: channeldata
partitions: NULL
type: range
possible_keys: PRIMARY,composite3,composite
key: PRIMARY
key_len: 9
ref: NULL
rows: 428073
filtered: 100.00
Extra: Using index condition 1 row in set, 1 warning (0.00 sec)

为什么过滤不同的值(channel_id 是 4 而不是 1)会有所不同？两个结果集的大小相等。为什么两次MySQL都选择使用PRIMARY key，但是keylen却有很大的不同。

Answer 1

您有一个错误。时间常量中的T不被MySQL识别；将其更改为空格。

你应该切换到 InnoDB。

在 MyISAM 中，这会加速查询:

INDEX(channel_id, station_id,  -- in either order
      time,
      reading)   -- last

这将是“覆盖”，因此比在索引和数据之间来回边界更快。

回到为什么他们是不同的......我不知道。但是，以下任何一个可能都有助于 MyISAM 表:ANALYZE TABLE 或 OPTIMIZE TABLE。