问题描述
生产环境中经常会出现需要查询数据的同时查询数据总数,然后根据总数来进行分页或其他操作。如果分成两个SQL会出现问题,就是查询到记录后总数也发生了变化。
如查询所有状态为1的记录.
select * from t1 where status = 1;
select count(*) from t1 where stauts = 1;
第一条SQL查询出10条数据,而第二条查询出的count可能只有9条或者11条。当二者不在一个事务中时,由于没有innodb可重复读级别来解决幻读问题,所以会出现二者不一致。
解决方法
使用SQL_CALC_FOUND_ROWS和SELECT FOUND_ROWS()来解决这个问题。
SELECT SQL_CALC_FOUND_ROWS id, status FROM t1 WHERE status = 1;
SELECT FOUND_ROWS();
其中SQL_CALC_FOUND_ROWS 告诉MySQL将sql所处理的行数记录下来,FOUND_ROWS() 则取到了这个纪录。SQL_CALC_FOUND_ROWS只会保留上一条SQL的行数,如果执行了其他SQL就失效了。
虽然也是两个语句,但是只执行了一次主查询,看起来效率比较高。
存在的问题
当执行分页查询时,往往会加limit,limit会限制mysql扫描的行数。 然而,当我们使用SQL_CALC_FOUND_ROWS时,Mysql会强制扫描所有满足条件的行数,才能获取到总数。我个人的理解是将有limit的SQL变成了没有Limit的SQL。两条小sql(limit查询和count查询)变成了一个大SQL。生产环境慎用,我觉得还是通过可重复读级别解决不一致问题,分解为2个SQL。