undefinedfix
Sign in

Spring data JPA single table paging slow query.

r4cc00n edited in Tue, 30 Aug 2022

Using druid to connect to the database, monitoring found that there was a serious slow query.

clipboard.png

The SQL is as follows:

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 ?, ?

Contains only two conditions, one sort plus paging

The query is JPA single table pagination query, with about 1.5 million table data and indexed query fields.

The JPA repository code is as follows

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

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

}

Seek the analysis and solution.

The table structure is as follows:

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 slow query statement:

+----+-------------+--------------+-------+---------------+-------------------------+---------+------+------+-------------+
| 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 |
+----+-------------+--------------+-------+---------------+-------------------------+---------+------+------+-------------+
3 Replies
Jee
commented on Tue, 30 Aug 2022

I think the possible reasons are: the offset in pageable is too large, or it is caused by sorting

explain xxxxxx(你的语句);
MenInTheMiddle
commented on Tue, 30 Aug 2022

If the sorting field is not indexed and has a large amount of data, sorting is a time-consuming operation. Try to build an index for these three fields

ILoafNodeJsAndLoaf
commented on Tue, 30 Aug 2022

The bottom layer of JPA paging query will have a selete count operation by default. If you want to improve the speed, you'd better write your own paging SQL

lock This question has been locked and the reply function has been disabled.