spring data JPA 单表分页的慢查询。

Question

使用Druid进行数据库连接，监控发现存在严重慢查。

sql如下：

SELECT songrankda0_.id AS id1_3_, songrankda0_.change_time AS change_t2_3_, songrankda0_.is_batch_update AS is_batch3_3_, songrankda0_.job_record_id AS job_reco4_3_, songrankda0_.pic_url AS pic_url5_3_
    , songrankda0_.rank_change AS rank_cha6_3_, songrankda0_.singer AS singer7_3_, songrankda0_.song AS song8_3_, songrankda0_.song_id AS song_id9_3_, songrankda0_.target_userid AS target_10_3_
FROM song_rank_data_diff songrankda0_
WHERE songrankda0_.target_userid = ?
    AND songrankda0_.is_batch_update = ?
ORDER BY songrankda0_.change_time DESC
LIMIT ?, ?

仅包含两个条件一个排序加分页

查询为JPA单表分页查询，表数据量150万左右，查询字段已加索引。

JPA Repository 代码如下

@Repository
public interface SongRankDataDiffRepository extends JpaRepository<SongRankDataDiff,Integer> {

    Page<SongRankDataDiff> findByTargetUserIdAndIsBatchUpdateOrderByChangeTimeDesc(String targetUserId, Integer isBatchUpdate, Pageable pageable);

}

求分析解决思路。

表结构如下：

DROP TABLE IF EXISTS `song_rank_data_diff`;
CREATE TABLE `song_rank_data_diff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_record_id` varchar(64) NOT NULL COMMENT '定时实例id',
  `rank_change` int(3) NOT NULL COMMENT '排行变化',
  `change_time` datetime DEFAULT NULL COMMENT '记录时间',
  `song` varchar(255) DEFAULT NULL COMMENT '歌曲名称',
  `singer` varchar(255) DEFAULT NULL COMMENT '歌手名称',
  `target_userid` varchar(100) DEFAULT NULL COMMENT '目标昵称',
  `is_batch_update` int(1) NOT NULL COMMENT '是否系统批量插入',
  `pic_url` varchar(255) DEFAULT NULL,
  `song_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_song_rank_data_diff` (`change_time`,`target_userid`,`is_batch_update`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3578466 DEFAULT CHARSET=utf8mb4 COMMENT='排行变化表';

explain慢查语句：

+----+-------------+--------------+-------+---------------+-------------------------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys | key                     | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+---------------+-------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | songrankda0_ | index | NULL          | idx_song_rank_data_diff | 413     | NULL |   20 | Using where |
+----+-------------+--------------+-------+---------------+-------------------------+---------+------+------+-------------+

Answer 1

我觉得可能的原因有:pageable中的offset过大,或者排序导致的.
具体的你可以贴一下执行计划吗?
在mysql中执行:

explain xxxxxx(你的语句);

Answer 2

排序字段没加索引并且数据量大的话，排序就是一个比较耗时的操作，把这三个字段建一个索引试试

Answer 3

jpa 分页查询底层默认会有一个selete count操作,想提高速度还是自己写分页sql