我正在使用MySQL数据库,并具有下表:
CREATE TABLE SomeTable (
PrimaryKeyCol BIGINT(20) NOT NULL,
A BIGINT(20) NOT NULL,
FirstX INT(11) NOT NULL,
LastX INT(11) NOT NULL,
P INT(11) NOT NULL,
Y INT(11) NOT NULL,
Z INT(11) NOT NULL,
B BIGINT(20) DEFAULT NULL,
PRIMARY KEY (PrimaryKeyCol),
UNIQUE KEY FirstLastXPriority_Index (FirstX,LastX,P)
) ENGINE=InnoDB;
该表包含430万行,初始化后永远不会更改.
该表的重要列是FirstX,LastX,Y,Z和P.
如您所见,我在FirstX,LastX和P行上有一个唯一索引.
FirstX和LastX列定义了一系列整数.
我需要在该表上运行的查询为给定的X提取具有FirstX< = X< = LastX的所有行(即,其范围包含输入数X的所有行).
例如,如果表包含行(我只包含相关列):
FirstX LastX P Y Z
------ ------ - --- ---
100000 500000 1 111 222
150000 220000 2 333 444
180000 190000 3 555 666
550000 660000 4 777 888
700000 900000 5 999 111
750000 850000 6 222 333
我需要,例如,包含值185000的行,应返回前3行.
我试过的应该使用索引的查询是:
SELECT P, Y, Z FROM SomeTable WHERE FirstX <= ? AND LastX >= ? LIMIT 10;
即使没有LIMIT,此查询也应该为任何给定的X返回少量记录(小于50).
这个查询是由Java应用程序为120000 X值执行的.令我惊讶的是,它耗时超过10小时(!),每次查询的平均时间为0.3秒.
这是不可接受的,甚至不可接受.它应该快得多.
我检查了一个耗时0.563秒的查询,以确保使用索引.我尝试的查询(与上面的查询相同,使用特定的整数值而不是?)返回2行.
我使用EXPLAIN来了解发生了什么:
id 1
select_type SIMPLE
table SomeTable
type range
possible_keys FirstLastXPriority_Index
key FirstLastXPriority_Index
key_len 4
ref NULL
rows 2104820
Extra Using index condition
正如您所看到的,执行涉及2104820行(几乎占表的行的50%),即使只有2行满足条件,因此检查索引的一半以便仅返回2行.
查询或索引有问题吗?您能否建议对查询或索引进行改进?
编辑:
一些答案建议我为X的多个值批量运行查询.我不能这样做,因为我实时运行此查询,因为输入到达我的应用程序.每次输入X到达时,我必须执行X的查询并对查询的输出执行一些处理.