mysql count(*)、limit分页慢解决方案

第1步：建一个表，命名为 test，一个id字段，再加一个content内容字段吧；

第2步：往test里塞1000万条数据，当然你也可以塞10亿条；

第3步：建一个表：命名为 table_delete_id_aggregate，三个字段，id、did(删除的id）,tableName(删除数据的表名称)；

第4步：test表删除数据时需要往table_delete_id_aggregate表中记录被删除id和数据表名（如果是连续的id，最好存成[1,10000],假设1到10000的数据都删了，不必存10000条数据）；

第5步：test表添加数据时，需要从table_delete_id_aggregate中随意获得一个已删除的id，用来填补已经缺失的id，添加成功后从table_delete_id_aggregate中删除这个id，目的是为了减少table_delete_id_aggregate的数据量以及让A表的id变得更连续（这个有个问题得从[1,10000]中拆出一个数，然后再更新一下变成[1,9999]）；

第6步：test表查询时，需要先查出这个查询条件的最小id，得到这个id后，需要用到一个方法，用来找到数据之间存在多少断点，譬如已经删除的id值为1、2、3、4、5、6、7、8、9、10、30、300、1000，最小id是1，分页需要往后取10条数据，需要遍历一下这个已删除的数组，找到满足取10条的最大id值，并统计出哪些id已经删除了，获得总数，然后交给查询语句between minid and maxid查出所需的10条数据，limit就不需要了。

 

一些算总数的相关代码如下：

SQL:

//查两个id之间间隔的数的sql语句
select * from ((select id as minID from data_info where id>0 limit 1) as a, (select id as maxID from data_info where id>0 order by id desc limit 1) as b)

 

PHP：

/* 获得总数，相当于count(*) */
    function queryGetTotal($minID, $maxID, $deleteIDArr)
    {
        //排序
        sort($deleteIDArr);
        $num=0;
        foreach ($deleteIDArr as $v) {
            if ($v>=$minID && $v<=$maxID) {
                $num++;
            }
        }
        return $maxID-$num-$minID+1;
    }

 

PHP：

/*根据最小ID和需要显示的行数查找最大ID，deleteIDArr是记录删除的ID*/
    function queryGetMaxID($minID, $rowNum, $deleteIDArr)
    {
        //排序
        sort($deleteIDArr);
        
        //预估最大id
        $max = $minID+$rowNum-1;
        
        //没有删除的直接返回预估
        if (!count($deleteIDArr)) {
            return $max;
        }
        
        //只有一个
        if (count($deleteIDArr)==1) {
            if($deleteIDArr[0]<=$max){
                return $max+1;
            }
            if($deleteIDArr[0]>$max){
                return $max;
            }
        }
        
        //循环查出
        if (count($deleteIDArr)>1) {
            
            $num=0;
            $fMax=0;
            
            foreach ($deleteIDArr as $k=>$v) {
                if($v>=$minID){
                    $num++;
                }
                //如果超出预估
                if ($v-$num>=$max) {
                    $fMax = $v;
                    break;
                }
            }

            //没有满足任何条件
            if (!$fMax) {
                return $max+$num;
            }
            
            //刚好等于预估值或大于
            if ($fMax-$num==$max) {
                return $fMax;
            }else if ($fMax-$num>$max) {
                return $max+$num;
            }else{
                return $max+$num;
            }
        }
    }

 

至此，10亿+的mysql表，就再也没有count(*)、limit查询语句性能差效率低问题了，亲测1亿+的数据，查询和分页都是毫秒级的。当然，这种方法只适合在带查询的分页时使用，不过别的场景应该也没有多大问题。

 

补充：请求时需要加个参数maxID的参数，第二页时用于当作between minid and maxid中的minid）甚至可以把第一次查的总数带上，这样就不需要执行查总数的sql和方法了。

不过，唯一不足就是需要记录一下已经删除的id和新增id时使用旧的id，查询时多了个语句。

 

目前我删除的id是用php文件预存的数组，一般已经彻底删除的数据行不会太多，如果用这种方法你的数据表删除的id很多，那就没办法了，1亿条数据的表删除数量别超过100万条应该问题不大~`。但即使如此，也有解，譬如1---1亿条之间的数据间隔，不必存1亿个id，可以用数组[1,100000000]表示，上面第4、5步有说明，最好在添加、删除时用算法处理一下，这样记录删除表的输出的数组就会变得很小，最终输出可能就是[1,20],[22,34],[20,90]，用算法自动维护一下，至此就变得无比完美了，如果这个算法实现了记得评论分享给我，以后有时间再研究这个算法。

 

这可能是比较完美的方案之一，就是麻烦了点，就是需要多写几个方法，分页多带个参数，再写个算法。

 

本方案只适合大表中少许被删数据，表id不连续的情况下使用，对了，本方法仅做学习交流只用，如果使用此方法出问题不负责噢，千万记得备份数据库。