mysql - 最佳性能索引结构

Question

我有一个具有以下结构的表和经常查询的列表。仅查看索引，建议的表索引结构是什么才能提供最佳性能？该表有超过 200 万行。

Table Structure:
id int(10) unsigned not_null auto_increment
dateDeleted datetime null
tenantId int(10) unsigned not_null
userId int(10) unsigned default 0
status tinyint(3) unsigned default 0
priority tinyint(3) unsigned default 0
docnum varchar(20) not_null

Frequent Queries:
where tenantId=? and dateDeleted is null;
where tenantId=? and dateDeleted is null and docnum=?
where tenantId=? and dateDeleted is null and status=?
where tenantId=? and dateDeleted is null and priority=?
where tenantId=? and dateDeleted is null and userId=?
where tenantId=? and dateDeleted is null and status=? and priority=?
where tenantId=? and dateDeleted is null and status=? and priority=? and userId=?
where tenantId=? and dateDeleted is null and status=? and userId=?
where tenantId=? and dateDeleted is null and priority=? and userId=?

Answer 1

如果 tenantId 是外键(顾名思义就是)，那么它显然是索引的选择:

create index mytable_tenantId_index on mytable(tenantId);

如果租户数量合理(十几个左右)，您会发现该索引会带来很大的性能提升。租户越多，改善就越大。

这个索引就足够了，因为您只需在 tenantId 上应用条件就已经大幅减少了行数。此外，您的查询条件没有 tenantId 为 null 的条件，因此您不必考虑这种边缘情况。