mysql - mysql大表如何正确使用索引？

Question

我在 MySQL 5.7.13 中有一个表，它有 3 列(实际上比这个多，但这三列用于搜索) - 它有一个列 company_id，它有一个列 department_id，它具有列status。

现在员工一般都是这样工作的，进入系统，选择工作的公司，选择部门(必选)。之后，他们进入菜单，可以从表中获取数据而不指定状态，或者他们也可以指定他们想要什么状态？

由于 mysql 索引的前缀顺序，我使用复杂索引(company_id、department_id、status)似乎很自然。由于他们总是按公司和部门进行过滤，有时还按身份进行过滤，所以一切看起来都很好。

但是，最近添加了一项新功能。还有一些特殊员工，与普通用户不同，他们只选择公司，而不选择部门。因此，他们的查询按公司和状态进行过滤。这意味着索引会中断，它只会按公司过滤，而不是按状态过滤，因为部门被省略了。

对此有什么解决方法吗？我在这里看到两个解决方案。一是状态非常受限制 - 它基本上具有值 - CREATED(1)、SENT(2)、ACCEPTED(3)、REJECTED(4)。因此，我可以创建索引(company_id、status、department_id)，如果用户没有选择任何状态，我可以在查询中附加如下内容:

AND status in (1,2,3,4)

索引能有效地工作吗？或者，其他解决方案是创建第二个索引并同时具有(company_id，department_id，status)和(company_id，status，department_id)，但是mysql优化器会选择正确的索引吗？我不确定哪种方式更好，其中任何一种都有效。

Answer 1

据我所知，这应该可以正常工作。优化器将利用(x,y,z)组合上的索引并执行使用索引；如果需要的话使用 where 。请注意，假设我的理解是组合的最左边部分已经是 where 子句的一部分，例如 x 允许 y 已经在混合中索引使用。

另一方面，如果 IN 子句很大，甚至在单个列索引上，IN 将很快得到满足。请参阅我在这里写的有关此问题的答案:Index uses IN .

当然，请查看标题为 EXPLAIN Syntax 的 MySQL 手册页。和类似 Using EXPLAIN to Write Better MySQL Queries .