mysql 全表扫描优化_MySQL千万级数据的全表扫描优化

数据场景

一个很简单 的业务数据表，2300W规模的数据，没有复杂数据类型，不想分表，还需要全表遍历

Select全表性能

查询接口

@Mapper

public interface UserMapper {

@Select("SELECT * FROM loadtimeslot")

public Listselect();

}

性能：耗时721s,查询效率相对较低，内存一次性增长过快，很容易撑不住

分页查询

查询接口

@Mapper

public interface UserMapper {

@Select("SELECT * FROM loadtimeslot order by id limit #{offset},#{limit}")

public ListselectByPage(@Param("offset") long offset,@Param("limit") int limit);

}

//递归查询

private void selectData(long offset, Listres)

{

int limit =100000;

long start = System.currentTimeMillis();

Listdata = oMapper.selectByPage(offset, limit);

res.addAll(data);

System.out.print(String.format("Load page data,offset[%d],cost[%d]ms\r\n", offset,System.currentTimeMillis()-start));

if (data.size()

性能：最开始的页码查询速度很快，但是越到最后查询效率越低,第一页需要2.4s，最后一页全量数据需要10.5s,总耗时1452s

Line 27: Load data from db start

Line 38: Load page data,offset[0],cost[2486]ms

Line 39: Load page data,offset[100000],cost[2048]ms

Line 40: Load page data,offset[200000],cost[1974]ms

Line 41: Load page data,offset[300000],cost[2032]ms

Line 42: Load page data,offset[400000],cost[2048]ms

Line 43: Load page data,offset[500000],cost[2095]ms

Line 44: Load page data,offset[600000],cost[2297]ms

Line 45: Load page data,offset[700000],cost[2407]ms

Line 46: Load page data,offset[800000],cost[2240]ms

.

.

.

.

Line 262: Load page data,offset[22400000],cost[10708]ms

Line 263: Load page data,offset[22500000],cost[10287]ms

Line 264: Load page data,offset[22600000],cost[10153]ms

Line 265: Load page data,offset[22700000],cost[10355]ms

Line 266: Load page data,offset[22800000],cost[10210]ms

Line 267: Load page data,offset[22900000],cost[10421]ms

Line 268: Load page data,offset[23000000],cost[10523]ms

Line 269: Load page data,offset[23100000],cost[9231]ms

Line 270: Load end:[1451794]ms

分析原因：limit 1000，100，意思是扫描满足条件的1000+100 行，丢弃掉前边的1000行，返回最后的100行，当偏移量足够大的时候，每次检索都要执行offset的偏移量处理，性能肯定会越来越差

分页查询优化

根据where条件跳过offset的偏移量处理

@Select("SELECT * FROM loadtimeslot where id>#{offset} order by id asc limit #{limit}")

public ListselectByPage(@Param("offset") long offset,@Param("limit") int limit);

性能: 分页查询单次时间消耗不随offset增大而增多，最后一次10000W条查询耗时2s,总耗时494s，相比之前的分页查询快了将近5倍

Line 27: Load data from db start

Line 38: Load page data,offset[0],cost[4707]ms

Line 39: Load page data,offset[100000],cost[3254]ms

Line 40: Load page data,offset[200000],cost[2251]ms

Line 41: Load page data,offset[300000],cost[2064]ms

Line 42: Load page data,offset[400000],cost[2034]ms

Line 43: Load page data,offset[500000],cost[1971]ms

Line 44: Load page data,offset[600000],cost[2100]ms

Line 45: Load page data,offset[700000],cost[2146]ms

Line 46: Load page data,offset[800000],cost[1973]ms

.

.

.

.

.

Line 264: Load page data,offset[22600000],cost[1925]ms

Line 265: Load page data,offset[22700000],cost[2079]ms

Line 266: Load page data,offset[22800000],cost[2015]ms

Line 267: Load page data,offset[22900000],cost[2175]ms

Line 268: Load page data,offset[23000000],cost[2065]ms

Line 269: Load page data,offset[23100000],cost[2015]ms

Line 270: Load page data,offset[23200000],cost[1204]ms

Line 271: Load end:[494943]ms