数据库查询中的回表

数据库查询中的回表

数据库查询中的回表问题是一个常见的性能瓶颈，尤其是在处理大规模数据时。本文通过一个实际案例，深入浅出地解释了回表的原理，并提供了有效的解决方案。

今天在知乎的一个话题下看到一个同学的回答：
前段时间刚出的bug，公司估计损失了1000w。问题也很简单，下午2点上线了一个需求，新增了一条sql语句：select aid from xx where bid = ?看着没有问题吧，bid字段也是有普通索引的，上完线验收了业务功能正常就结束了。凌晨2点被电话叫醒，加入语音会议后才知道数据库在晚上流量的高峰期io飙升了5倍，导致整个核心交易链路全部挂掉，dba剔除dbproxy以后无法再次挂起，最终定位到可能是代码问题，回滚代码业务恢复正常，从定位问题到业务恢复花了一小时时间。这张表有80亿数据，导致了整个数据库层面的io变高。

这确实是一个简单的再不过的问题了，一般看到这种超大表查询出现问题，多半都是因为查询回表导致的，后面同学也提到，确实是因为回表。
回表是数据库中再常见不过的现象了，在数据量不大的系统中，很多人人会无意识，甚至几乎随时都有回表动作，因为数据量小也不会有什么问题，但是一旦数据量亚上来，像上面这个case的80亿条数据，则后果很严重。
这也侧面说了其实同样的sql在不同数据量的数据库下不能直接拷贝，得重写。
说回来回表的问题，在数据库中如果一个查询触发两次索引扫描，就称为回表，举一个简单的例子，我有一张用于用户登录的user表：

假如现在有一个用户名为admin，密码为123的用户要登录，那我会先找出username为admin的那条用户数据：
SELECT*FROMuserWHEREusername ='admin'

再根据查出来的user信息去对比密码是否正确，如果username字段是唯一的又经常作为where条件所以可以给username字段建一个索引，于是有的同学会考虑给username建了一个普通的B+Tree索引。
问题就会发生在这里，如果不加还好，加了反而会出问题，为什么呢？因为MySQL的InnoDB使用聚簇索引，具体的数据只和主键索引放在一起，其他的索引只存储了数据的地址（主键id）。
比如上面的例子中，根据username索引找到的只是一个username为admin这条数据的id而不是这条数据信息，所以要找到整条数据信息要根据得到的id再去找。
看完上面的流程，应该已经发现问题了，通过username找到id，再根据id找整条数据，这里有两个查找过程，这是影响效率的，这个发生两次查询的动作，就是回表。
解决问题也比较容易，如果是知乎的那个问题的话，其实直接
limit 1
就行了，
limit 1
和
limit 6000 1
是完全不一样的机制。
另外也可也使用联合索引来解决，还是拿上面上面登录的例子来说，其实登录只需要判断用户名和密码，如果user表中有其他用户信息也是不需要的那我们能不能只查询一次就找到这个用户名对应的密码呢。
这个是可以的，上面所说的分两步查找，第一步根据username查找是肯定不能少的，那只要把password和索引username放到一起就可以了。
可以建立一个（username、password）的组合索引，这里username一定要放在前面，然后把sql语句改一下：
SELECTusername,passwordFROMuserWHEREusername ='admin'

这样建立组合索引后根据username查找password，只要一步查找就可以查找到，因为password已经是username索引的一部分了，直接可以查出来，不再需要通过id找对应的整条数据。
扫码手机观看或分享：