假设我有一个以下格式的表:
image(
i char(5) PRIMARY KEY,
v INT,
s INT,
p TINYINT,
m TINYINT,
d DATE)
所有的ints/tinyint也都是无符号的,我不知道这是否会对性能产生影响
无论如何,除了主键之外,我还在剩余5个字段中的上创建了索引。作为参考,该表目前填充了约160万行用于测试的伪数据。这些值随机分布在以下范围内:
0 ≤ v ≤ 10000
0 ≤ s ≤ 5000
0 ≤ p ≤ 50
0 ≤ m ≤ 10
因此,我尝试测试一些像SELECT * FROM image WHERE v > 9000 AND s < 3000 AND p > 25 AND m > 8 ORDER BY d
这样的查询,认为它可能会工作,但发现它需要11s
。。。因此,我决定在vspmd
中添加一个覆盖/复合索引(不确定这是否是正确的术语),认为它会自动更正所有内容。。。但它没有。。。即使加上这个指数,仍然需要11秒。然后我将FORCE INDEX(vspmd)
添加到查询中,瞧,它起作用了,SELECT * FROM image FORCE INDEX(vspmd) WHERE v > 9000 AND s < 3000 AND p > 25 AND m > 8 ORDER BY d
只需要0.01s
就可以执行。然后,我尝试按d date
列以外的其他列排序,所有查询都在0.01s
下。
由于该项目将允许用户打开/关闭搜索参数;嘿,让我们从WHERE子句中删除v
";,假设这会把事情搞砸,因为索引vspmd
的最左边部分已经不在查询中了。因此,我添加了索引spmd
和pmd
。对于相关查询,这两种方法都有效。然后,我尝试在WHERE子句中只保留v
和m
字段,即WHERE v > 9000 AND m > 8
,而不保留任何FORCE INDEX
,这个查询使用了2.40s
,并且通过强制v
作为索引的看似糟糕的选择,它成功地获得了44s
。令人惊讶的是,EXPLAIN
建议使用v.m.vspmd
,尽管似乎没有使用vspdm
,因为当我强制使用时,查询只使用0.11s
,而不是MySQL自己选择索引时使用的2.40s
。我还认为,如果我只想基于p
和m
进行筛选,我可以使用vspmd
索引,但强制它们为始终为真的值(即v > -1 AND s > -1 AND p > 25 AND m > 8
)-这很好-这比让MySQL选择索引更快,但它需要0.8s
,而且随着每增加一个"始终为真"的条件,情况会变得更糟。因此,我回到了对每个组合都有覆盖索引的地步(它只有n-1,所以在这种情况下只有4,我有5列要基于它进行筛选)。同样,虽然SQL看起来很粗糙,但用PHP编写它很容易。。。只需根据用户正在搜索的字段选择正确的索引即可。INSERT
仍然是0.03s
,所以没关系。然而,我想我的主要问题是:
这在任何情况下都是典型的吗/你曾经这样做过吗?毕竟,对于购物网站这样的网站,你可以想象用户可以切换20多个搜索参数,尤其是对于电脑/汽车等物品。当然,这些网站不需要有
abc...z
、bcd...z
、cde...z
等索引。如果不进行测试,如果用户只更改参数a
和z
,则必须使用具有26个部分的覆盖索引CCD_ 37。。。我想这会很慢(?)。这种方法的主要缺点是什么?空间很便宜,所以这不是问题(返回快速结果、停止服务器上充斥的120s+查询更重要),但我在这样做时可能会遇到其他问题吗?
毕竟,这似乎是一种非常巧妙的方式来实现你所期望的行为;表演因此,如果有人有任何建议,我将不胜感激
这是您想要优化的查询类型:
SELECT *
FROM image
WHERE v > 9000 AND
s < 3000 AND
p > 25 AND
m > 8
ORDER BY d;
不幸的是,MySQL并没有一种有效的方法来优化这一点——这是因为不平等。MySQL确实对多列索引以及如何使用它们有很好的解释。
你能做什么?首先,如果条件返回大量数据,那么ORDER BY
就是问题所在。如果没有,那么扫描表格就是问题所在。
如果问题出在ORDER BY
上,则可以通过在d
上构建聚集索引来提高性能。然后扫描表格将结果按正确的顺序排列,查询速度会更快一些。
如果扫描表格寻找条件是个问题。那么我唯一能想到的就是弄清楚如何将条件转化为等式。也许您可以将列分类为";"低"中等";,以及";高";,并且只允许用户选择一个范围。如果每个列都需要一个范围,那么可以为可能的查询使用一个索引。
对要测试的4个范围中的每一个都有一个单列索引。然后希望优化器在大多数时候都能选择最有选择性的一个。
而且,正如Gordon所指出的,d
上的索引(或PK)可能也是最好的。但这只会避免排序——这通常发生在所有筛选之后。
您的基准与";"随机";如果";真实的";数据不平衡。