select count(0)或者select count(*)非常慢的优化

查询表的数据总行数，select count(0)或者select count(*)，mysql已经自动使用的最优的索引，我自己折腾了好久也没能弄出来更加快速的查找，总是1秒多！~~~

所以我直接这样搞

explain select count(0) from news_library

这个可快的不是一星半点，原来1秒多才能查出来的数据总数，现在只要0.002秒，但是这个有一点小问题，count (0)或者count (*)查询的结果是精确结果，explain select count(0)查出来的是一个mysql内置的一个结果，结果要稍微小于精确结果（网上说这个结果可以手动刷新，结果还能接近一点），但是相差不大，像查看总数据量这种要求不是很精确的场景下完全可以使用。

如果用mybatis的话，应该新建一个对象来接收返回结果

直接复制用

package com.qiangqiang.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

/**
 * \* Created with IntelliJ IDEA.
 * \* @author: xiyue
 * \* Date: 2020/12/18
 * \* Time: 16:26
 * \* To change this template use File | Settings | File Templates.
 * \* Description:
 * \
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExplainResult implements Serializable {
    private int id;

    private String selectType;

    private String table;

    private String partitions;

    private String type;

    private String possibleKeys;

    private String key;

    private String keyLen;

    private String ref;

    private int rows;

    private int filtered;

    private String Extra;

}

xml文件中内容也不用更改，直接复制用

<resultMap id="ExplainResultMap" type="com.qiangqiang.entity.ExplainResult">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="select_type" property="selectType" jdbcType="VARCHAR"/>
        <result column="table" property="table" jdbcType="VARCHAR"/>
        <result column="partitions" property="partitions" jdbcType="VARCHAR"/>
        <result column="type" property="type" jdbcType="VARCHAR"/>
        <result column="possible_keys" property="possibleKeys" jdbcType="VARCHAR"/>
        <result column="key" property="key" jdbcType="VARCHAR"/>
        <result column="key_len" property="keyLen" jdbcType="VARCHAR"/>
        <result column="ref" property="ref" jdbcType="VARCHAR"/>
        <result column="rows" property="rows" jdbcType="INTEGER"/>
        <result column="filtered" property="filtered" jdbcType="INTEGER"/>
        <result column="Extra" property="Extra" jdbcType="VARCHAR"/>
    </resultMap>

<select id="selectCount" resultMap="ExplainResultMap">
       explain select count(0) from news_library
    </select>

Impl实现类

@Override
    public PageInfo<NewsLibrary> selectByPage(int page, int limit) {
        page = page * limit;
        List<NewsLibrary> newsLibraries = newsLibraryMapper.selectByPage(page, limit);
        PageInfo<NewsLibrary> pageInfo = new PageInfo<>(newsLibraries);
        ExplainResult explainResult = newsLibraryMapper.selectCount();
        int count = explainResult.getRows();
        pageInfo.setTotal(count);
        return pageInfo;
    }