求教一个mysql建表分组索引问题

问题描述

我在做一个网站程序，大致要求效果如下。用户分为1-5这五个级别，数字越大权限越高。

我有一堆内容，级别越高的用户可见内容越多。例如有内容：A、B、C、D、E，用户组1的可见：A用户组2的可见：A、B…………用户组5的可见：A、B、C、D、E如果要实现这种功能，该如何建立数据库索引比较好呢？

之前有朋友跟我说在内容（topic）表加上一列'group'，写上可见的用户等级1-5，然后建立group_tid的联合索引。然后查询tid<100周围文章（例如当前用户组为3）时的语句就是：SELECT * FROM topic WHERE group>=3 AND tid<100 LIMIT 10;可实际发现这种索引是先将group>3的所有数据读出来，再进行选择查询。假如有100万条数据，有50万个group>3，该语句执行就要从50万条种筛选，效率极低。

看起来单列的索引只适用于group=*这样的限制条件，而不能是<或>。所以想在此请教各路大神，有没有过类似的需求？如何正确建立索引或分表？深表感谢！

补充1：其实把问题改变一下，就是如何在mysql两个索引中使用<或>限定。这是个逻辑问题，目前的group_tid索引建立后类似于下图：

即便我对group进行了范围限制，后面的tid还是在group的基础上按顺序排列的。如果我想知道group>1且tid<6的这种情况，不得不先把group2/3全部读出再筛选。看起来只有重新规划表结构，各位有没有类似经验？

补充2：刚才收到了热心朋友的帮助回答，说这种情况他曾经遇到过。解决的方法是修改发布机制，将符合条件的帖子发布至各个等级。例如内容A的级别是3，那么发帖时要同时建立三个数据行：group=1,tid=Agroup=2,tid=Agroup=3,tid=A这样在内容读取时直接请求WHERE group=*都可以读出符合条件内容。但这种方法需要添加大量的关联数据，甚至造成重复，有没有其他解决途径呢？

问题解答

其实你的思路已经很对了。

tid上建立索引，根据group分表。

如果group >=3的组，在程序中动态组合sql如下：

select * from group3 where tid < 100union all select * from group4 where tid < 100union all select * from group5 where tid < 100

以上索引生效，逻辑可用。

首先说明一下，在 Innodb 中，索引生不生效跟你使用 < 或 > 没有必然关系。也不是说用 = 就一定能用上索引。当全表查的性能要高于索引检索查询时，MySQL 会智能的放弃索引，选择全表查询。

如图：

回到你的问题，如果某个索引，如 tid<100 检索出的范围相对较小时，索引是能够用上的。

如果这两个索引的结果集都很大的话，是否考虑添加其他过滤条件，比如根据创建时间只查近一个月的内容。

分页问题也可以通过主键ID来再次过滤。

首先，需要明白以下几点：

对于一个表的查询，每次最多只使用一个索引

对于联合索引，从左往右依次进行数据的筛选，所以如果第一个筛选条件针对了大于或者小于的话，第二个筛选条件由于在整个可选区域内没有确切的索引范围，所以会将第一个筛选条件筛除来的数据都跑一遍

B-Tree索引的结构类似于树形结构，见下图，联合索引从左往右的检索，起始就是这个结构从上往下查找分支的过程

索引的机制，简单说来就是创建一个值到数据项的对应表，这样可以快速的从某一字段某个值定位到某一行，省却了跑整个表去找对应行的操作，所以比较快

B-Tree索引的结构：

然后回到你的问题上，如果要大幅度提高效率，那么联合索引的第一步就需要大幅度减少可以用于后续筛选的数据量，所以如果你要查tid < 100的话，先用tid筛选才能够大幅度减少后续的B-Tree索引分支，所以如果要用联合索引，则应该是(tid, group)。

group条件的过滤性很差，单独建立索引意义不大。

根据你描述的场景，只要tid的值不是太大（几千的数量级），针对tid建立索引就可以了。如果还担心tid条件过滤后的数据量大，可以创建tid,group的组合索引。

首先非常感谢各位对我问题的关注和回答！！问题解决之后针对boxsnake的建议有一个思考，在这里发一下。group_tid这种索引方式除了解决读取之外还能解决分页问题，例如我每页文章数量是10，用户级别为3，那么读取时分别从group1、group2、group3中，按范围tid<100各取10篇，即便某group中没有符合条件的结果，几项加起来也可以覆盖全。

但如果用tid_group这种索引方式来读取，如果需要group<=3的情况，我不知道该取多少篇文章。比方说取10篇，tid90-tid99，如果他们的group都是4，那么就无法取出符合条件的数值。而tid_group在限定group之前又必须对tid进行限定，所以就没法使用了。