如何在MySQL中索引查找表

Question

我有一张拥有1000万行数据的表格，名为product，其中包含了color（整数）、price（浮点数）、weight（浮点数）和unitprice（整数）等字段。现在Web端的用户会动态生成查询条件，以便从该表中查找数据，并按照随机条件进行排序（这里必须要有颜色作为查询条件）。

select * from product where color=1 and price >5 and price <220 and .... order by unitprice limit 75, 25;

select count(*) from product where color=3 and weight <500 and price <30 ... ;

如何在MySQL中对一个具有约10个可能的过滤字段（包括范围、排序等）的表格（InnoDB或NDB）进行索引？

---

编辑：据我所知，MySQL很可能只会为一个查询选择一个索引，并且仅左侧部分的组合索引才有效。显然，对所有可能的组合进行索引不是可行的选择，例如(颜色、价格、重量、创建日期、单价，...)，(颜色、重量、价格、创建日期、单价，...)，(颜色、单价、重量，...)等。并非所有条件都必须存在于所有查询中。

你会如何对这个表格进行索引呢？

- QWJ QWJ

Answer 1

如果您想在任何字段上进行快速查找/筛选/排序，必须在所有字段上放置索引。

如果颜色是必须的（即在每个查询中都使用），最好为每个字段创建复合索引(color, field)。

如果color确实是每个常见查询的一部分，将聚集索引放在(color, product_id)上也值得一试。

- Tomalak

---

谢谢你的建议。我在这个表中有大约10个这样的字段。你是建议只使用2个关键部分的复合索引？还是全部10个？是的，颜色将是所有索引的第一个部分。 - QWJ QWJ

@QWJ 我的建议是为每个索引使用两个关键部分，对于您的表格使用10个索引。这样MySQL就可以通过查看单个索引（颜色+x）来满足查询的最重要部分。在这里有更多关于这个主题的阅读材料：https://dev59.com/JXM_5IYBdhLWcg3wrFIt。 - Tomalak

您可以考虑收集有关查询的统计信息（例如，将查询写入日志），并删除几乎永远不使用的索引，如果大量索引开始变得麻烦的话。 - Brian Hooper

@QWJ 索引有两个缺点：a）它们增加了表的大小；b）它们减慢了对表的任何写操作（插入、更新、删除），因为不仅表而且每个受影响的索引都需要进行更改。抛弃不必要的索引通常可以提高写入性能，创建必要的索引通常可以提高读取性能。找到正确的平衡取决于使用情况。 - Tomalak

谢谢大家。我会尝试创建具有2个部分的10个索引。但是在将来，当表增长时，我可能需要更多复合索引的部分 - 还是一个头痛的问题。关于插入，我会研究集群或复制等方案。 - QWJ QWJ

Answer 2

正如Tomalak所回答的那样，您可能应该为所有字段添加索引（根据查询需要添加复合索引）。但是当然这可能会减慢写入速度。

如果您不确定索引将如何使用，可以使用explain命令。

- Waleed Eissa