uid | type | password | |
11 | 0 | zhang@qq.com | *** |
22 | 1 | li@qq.com | *** |
33 | 0 | wang@qq.com | *** |
44 | 2 | zhao@qq.com | *** |
uid | type |
22 | 1 |
44 | 2 |
单单使用user表可以查出type=1的用户记录集合
select * from user where type=1;
为了提高性能,我们会设计多一张user_type辅助查询,因为绝大部分的用户是type=0普通用户,通过inner join可以以小表user_type去扫描关联的记录。假如user表有2亿条记录,user_type表有2条记录,inner join之后,只需要扫描2条记录,就可以把结果返回,所以性能要提升2亿倍
select main.* from user as main inner join user_type as typeTable on main.uid = typeTable.uid where main.type=1