mysql - 非典型查询、必要索引、优化

Question

select ip.ns, ip.u from iparts ip where ip.isF = 0 and ((ip.wl < ?1 and ip.wl > ?2) or (ip.wf = 0 and ip.c < ?1 and ip.c > ?2))

ip.isF = 0 - 这部分是最有选择性的，95%的记录都有isF = 1，但是这个表会很大

如何优化这个查询？您能否澄清我应该添加到表 iparts 中的索引？

Answer 1

以下索引应该对您的查询有帮助:

(`isF`,`wl`)

(`isF`,`wf`,`c`)

添加 SELECTed 列将进一步改进它，使这些索引覆盖索引，因此只允许使用索引，而无需查找主表。

(`isF`,`wl`,`ns`,`u`)

(`isF`,`wf`,`c`,`ns`,`u`)

与另一对索引相比，使用这对索引的唯一缺点是，由于增加了列，插入时间可能会稍微慢一些，并且这些索引将占用更多空间。

我还建议更改您的查询，以便充分利用这些索引。

SELECT ip.ns,
ip.u 
FROM iparts ip 
WHERE ip.isF = 0 
AND ip.wl < ?1 
AND ip.wl > ?2
UNION DISTINCT
SELECT ip.ns,
ip.u 
FROM iparts ip 
WHERE ip.isF = 0 
AND ip.wf = 0 
AND ip.c < ?1 
AND ip.c > ?2

通过使用 UNION，RDBMS 可以单独进行每个查询，仅使用适合每个查询的索引。如果你的数据保证两次查询之间没有重复，你可以将UNION DISTINCT改为UNION ALL，这样会节省更多时间。

注意:您不需要两对索引。我建议尝试第一对(较短的)并查看性能是否可以接受(确保使用我的查询。)如果不够好，则删除它们并添加第二对。

如果您需要更多帮助，请将 SHOW CREATE TABLE iparts 的输出添加到您的问题中。这将使我们能够看到现有的索引和列数据类型，这两者都与任何优化相关。