如何为两类WHERE条件的用户查询正确设置数据库索引？

现有方案合理性判断 #

方案1（针对WHERE is_admin = 0） #

你的方案1是部分覆盖索引，但存在核心缺陷：

• 索引前缀使用了id，而查询条件仅为is_admin = 0。由于id与is_admin无关联，数据库无法通过索引快速定位符合is_admin = 0的行，只能扫描整个索引的所有条目（尽管索引仅包含is_admin = 0的行，但排序逻辑是按id，无法利用条件过滤）。
• 若查询仅需返回索引中包含的id, email, firstname, lastname, role, company字段，该索引能实现覆盖查询避免回表，但查找效率远不如将is_admin作为索引前缀的设计。

方案2（针对WHERE is_admin = 0 AND company = ?） #

这个方案是合理的：

• 索引顺序为(is_admin, company)，完全匹配查询的等值条件顺序。数据库可以先通过is_admin = 0快速定位到对应的索引分组，再在分组内精准匹配company值，过滤效率很高。
• 唯一不足是如果查询需要返回这两个字段之外的其他数据，会触发回表操作，增加IO开销。

优化方向 #

优化方案1（适配第一个查询场景） #

将is_admin作为索引的第一个字段，同时保留覆盖查询所需的字段（MySQL不支持INCLUDE子句，需直接将字段加入索引）：

CREATE INDEX idx_is_admin_cover ON users(is_admin, id, email, firstname, lastname, role, company);

若第一个查询仅需判断is_admin = 0而不需要返回大量字段，也可以简化为基础索引：

CREATE INDEX idx_is_admin ON users(is_admin);

优化方案2（优化第二个查询场景） #

如果第二个查询需要返回除is_admin和company之外的固定字段，将这些字段加入索引末尾，实现覆盖查询避免回表：

CREATE INDEX idx_is_admin_company_cover ON users(is_admin, company, [需要返回的其他字段]);

例如，若需要返回id, email，则：

CREATE INDEX idx_is_admin_company_cover ON users(is_admin, company, id, email);

冗余索引规避 #

• 即使优化了方案1为idx_is_admin_cover，它也无法替代方案2的索引：因为company不是该索引的前缀字段，数据库无法利用它快速过滤，仍需扫描所有is_admin = 0的索引条目，效率远低于(is_admin, company)的索引。
• 保持两个索引各司其职，分别对应不同查询场景，是更高效的选择。

额外建议 #

• 定期更新表的统计信息（例如MySQL执行ANALYZE TABLE users;），确保优化器能准确选择最优索引。
• 测试时使用EXPLAIN语句查看索引的实际使用情况，验证优化效果。

内容的提问来源于stack exchange，提问作者lambdamaster