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<Employee> 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<Employee> employeeList = employeeService.getEmployeeList(queryModel);
1
2
对查询到的数据的封装
这里特别强调一下，很多时候我们都会用到集合List、Map来存取数据，但是一般需要处理中间的一些数据，此时我们可以用java8的stream流来快速处理，这也是优化点之一
类似下面这种：

//对UserScope按照userID分组
Map<Long,List<UserScope>> 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
---------------------