我有一个很大的表(大约300万条记录),主要包括以下字段:rowid(int)、deviceid(varchar(20))、格式为1536169459(int(10))的unixtimestamp、具有30到90之间整数的powerlevel(smallint(6))。我正在寻找在特定时间范围内(使用unixtimestamp)为特定的deviceid和powerlevel高于特定数字的记录。有超过300万张唱片,需要一段时间。有没有一种方法可以创建一个索引来对此进行优化?
e3bfsja21#
如果我没听错的话,你希望能加快这种查询的速度。
SELECT something FROM tbl WHERE deviceID = constant AND start <= UnixTimestamp AND UnixTimestamp < end AND Power >= constant
您有一个常量标准(deviceid)和两个范围标准(unixtimestamp和power)。mysql的索引是btree(按顺序排序),mysql只能对每个select进行一次索引范围扫描。所以,你应该选择一个索引 (deviceID, UnixTimestamp, Power) . 为了满足查询,mysql将随机访问deviceid条目的索引,然后进一步随机访问满足unixtimestamp启动条件的第一行。然后它将按顺序扫描索引,并使用每个索引项的幂信息来决定是否应该选择每一行。你也可以用 (deviceID, Power, UnixTimestamp) . 但在本例中,mysql将找到第一个与设备和电源条件匹配的条目,然后扫描索引以查看所有条目的时间戳,以确定应该选择哪一行。您的性能目标是让mysql扫描尽可能少的索引项,因此 (deviceID, UnixTimestamp, Power) 选择是最好的。unixtimestamp上的索引列可能比power上的索引列更有选择性(这是我的猜测。)
(deviceID, UnixTimestamp, Power)
(deviceID, Power, UnixTimestamp)
ALTER TABLE tbl CREATE INDEX tbl_dev_ts_pwr (deviceID, UnixTimestamp, Power);
看看比尔·卡尔文的教程。再看看马库斯·温纳德的https://use-the-index-luke.com
juzqafwq2#
在以下位置创建索引:
DeviceId, PowerLevel, UnixTimestamp
选择时,您将首先缩小到给定设备的记录集,然后它将缩小到只有那些在正确的功率级别范围内的记录。最后,对于每个功率级,它将通过unixtimestamp缩小到正确的记录。
bz4sfanl3#
建议的3列索引仅部分有用。优化器将使用前两列,但忽略第三列。更好:
INDEX(DeviceId, PowerLevel), INDEX(DeviceId, UnixTimestamp)
为什么?优化器将在这两个选项中进行选择,这两个选项似乎更有选择性。如果时间范围“窄”,则使用第二个索引;如果没有多少行具有所需的powerlevel,那么将使用第一个索引。更好的是。。。这个 PRIMARY KEY ... 你可能有 Id 作为pk?也许 (DeviceId, UnixTimestamp) 是独一无二的吗(或者你能在一秒钟内得到一个设备的两个读数吗???)如果这一对是唯一的,就去掉它 Id 完全和有
PRIMARY KEY
Id
(DeviceId, UnixTimestamp)
PRIMARY KEY(DeviceId, UnixTimestamp), INDEX(DeviceId, PowerLevel)
笔记:摆脱 Id 节省空间,从而提供一点速度。当使用二级索引时,执行过程将花费时间在索引的btree和数据btree之间(按主键排序)。通过拥有 PRIMARY KEY(Id) ,你一定会跳的。通过将pk改为该值,可以避免反弹。这可能会使查询速度加倍。(我不确定是否会使用辅助索引。)另一个(次要的)建议:规范 DeviceId 所以它(也许)是一个2字节 SMALLINT UNSIGNED (范围0..64k)而不是 VARCHAR(20) . 即使这需要 JOIN ,查询将运行得更快一些。节省了一堆空间。
PRIMARY KEY(Id)
DeviceId
SMALLINT UNSIGNED
VARCHAR(20)
JOIN
3条答案
按热度按时间e3bfsja21#
如果我没听错的话,你希望能加快这种查询的速度。
您有一个常量标准(deviceid)和两个范围标准(unixtimestamp和power)。mysql的索引是btree(按顺序排序),mysql只能对每个select进行一次索引范围扫描。
所以,你应该选择一个索引
(deviceID, UnixTimestamp, Power)
. 为了满足查询,mysql将随机访问deviceid条目的索引,然后进一步随机访问满足unixtimestamp启动条件的第一行。然后它将按顺序扫描索引,并使用每个索引项的幂信息来决定是否应该选择每一行。
你也可以用
(deviceID, Power, UnixTimestamp)
. 但在本例中,mysql将找到第一个与设备和电源条件匹配的条目,然后扫描索引以查看所有条目的时间戳,以确定应该选择哪一行。您的性能目标是让mysql扫描尽可能少的索引项,因此
(deviceID, UnixTimestamp, Power)
选择是最好的。unixtimestamp上的索引列可能比power上的索引列更有选择性(这是我的猜测。)看看比尔·卡尔文的教程。再看看马库斯·温纳德的https://use-the-index-luke.com
juzqafwq2#
在以下位置创建索引:
选择时,您将首先缩小到给定设备的记录集,然后它将缩小到只有那些在正确的功率级别范围内的记录。最后,对于每个功率级,它将通过unixtimestamp缩小到正确的记录。
bz4sfanl3#
建议的3列索引仅部分有用。优化器将使用前两列,但忽略第三列。
更好:
为什么?
优化器将在这两个选项中进行选择,这两个选项似乎更有选择性。如果时间范围“窄”,则使用第二个索引;如果没有多少行具有所需的powerlevel,那么将使用第一个索引。
更好的是。。。
这个
PRIMARY KEY
... 你可能有Id
作为pk?也许(DeviceId, UnixTimestamp)
是独一无二的吗(或者你能在一秒钟内得到一个设备的两个读数吗???)如果这一对是唯一的,就去掉它Id
完全和有笔记:
摆脱
Id
节省空间,从而提供一点速度。当使用二级索引时,执行过程将花费时间在索引的btree和数据btree之间(按主键排序)。通过拥有
PRIMARY KEY(Id)
,你一定会跳的。通过将pk改为该值,可以避免反弹。这可能会使查询速度加倍。(我不确定是否会使用辅助索引。)
另一个(次要的)建议:规范
DeviceId
所以它(也许)是一个2字节SMALLINT UNSIGNED
(范围0..64k)而不是VARCHAR(20)
. 即使这需要JOIN
,查询将运行得更快一些。节省了一堆空间。