mysql根据索引分页_Mysql利用覆盖索引优化分页查询

1. 打日志，最简单的判断api时间消耗在哪一部分方法，一般检查前方法：

端数据的封装

//处理分页情况

if (pageSize == null || pageSize <= 0) {

pageSize = Constant.DEFAULT_PAGE_SIZE;

}

if (page == null || page <= 0) {

page = 1;

}

//基准时间

long start = System.currentTimeMillis();

//返回结果集

PagingResult pagingResult = null;

try{

ShopVerifyQueryModel queryModel = shopVerifyService.packagQueryModel(pid, cid, did, rid, page, 0, request, pageSize, keyword);

if(roleKey != null && roleKey.length() > 0){

queryModel.setRoleKey(roleKey);

}

queryModel.setSearchIds(Lists.newArrayList(EmployeeType.ALL.getCode(),EmployeeType.INNER.getCode()));

logger.info("employeeList step0 封装查询数据:{}",System.currentTimeMillis()-start);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

sql的查询

//获取数据,分页查询，即是调用sql

List employeeList = employeeService.getEmployeeList(queryModel);

1

2

对查询到的数据的封装

这里特别强调一下，很多时候我们都会用到集合List、Map来存取数据，但是一般需要处理中间的一些数据，此时我们可以用java8的stream流来快速处理，这也是优化点之一

类似下面这种：

//对UserScope按照userID分组

Map> map = allScopes.stream().collect(Collectors.groupingBy(UserScope::getUserId));

1

2

2. 通过步骤一就可以大概判断出耗时主要在哪里，然后进行相应的优化即可。一般大部分时间其实都是在sql语句上面，合理利用索引可以对大多数sql进行优化。

3. 今天我遇到的问题，我按照上面的步骤对之前的sql进行了优化，加了索引，sql查询时间从原来的5s到现在0.5s(本地电脑运行的)，确实快了许多，在test环境(服务器性能更好)更快了，0.2s左右，然后就发布了pre环境，然而在pre上有2000多条数据，分页是每页20条，就有100多页，sql采用的是limit查询：

SELECT

e.*,

GROUP_CONCAT( DISTINCT pr.`name` ) AS role_name

FROM

employee e

LEFT JOIN `pri_user_scope` us ON e.user_id = us.user_id

LEFT JOIN `pri_user_role` role ON e.user_id = role.user_id

LEFT JOIN `pri_role` pr ON role.role_key = pr.`key`

WHERE

e.status = 1

AND us.scope_type = 2

AND us.scope LIKE '0%'

GROUP BY

e.id

ORDER BY

e.id DESC

LIMIT 0,20

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

结果上线后在pre中，前面几页挺快的，越到后面越慢，最后一页还是用了7s，查了一下，就是limit查询的问题，可以用覆盖索引的方法解决这个问题，具体思路：在MySQL中如何使用覆盖索引优化limit分页查询下面是优化过的sql：

SELECT

e1.*,

GROUP_CONCAT(DISTINCT pr.`name`) AS role_name

FROM

employee e1

LEFT JOIN `pri_user_role` role ON e1.user_id = role.user_id

LEFT JOIN `pri_role` pr ON role.role_key = pr.`key`

WHERE

e1.id IN (

SELECT

t.id

FROM

(

SELECT DISTINCT

e.id

FROM

employee e

LEFT JOIN `pri_user_scope` us ON e.user_id = us.user_id

WHERE

e.`status` = 1

AND us.scope_type = 2

AND us.scope LIKE '0%'

ORDER BY(http://www.amjmh.com)

e.id DESC

LIMIT 0,

20

) AS t

)

GROUP BY

e1.id

ORDER BY

e1.id DESC

---------------------