mysql索引优化实践 – 汪明鑫的个人博客

有一个场景：每个赛季用户都有自己的积分，积分前100的有一个榜单top100

榜单展示按积分由大到小

这样会有个问题，积分相同的谁排前谁排后，比如100名和101名积分相同怎么整？

上了榜单多有排面

因此相同积分的，谁先达到该积分谁在前面 update_time

即我们的排序策略，按照积分降序、按照更新时间升序

猛一想没问题，mysql 2个字段 score、update_time

order by score desc, update_time asc

user_score1 | CREATE TABLE `user_score1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `season_id` bigint(20) unsigned NOT NULL COMMENT '赛季id',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `score` bigint(20) unsigned NOT NULL COMMENT '分数',
  `create_time` bigint(20) unsigned NOT NULL COMMENT '创建时间',
  `update_time` bigint(20) unsigned NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_round_id_user_id` (`season_id`,`user_id`),
  KEY `idx_round_id_score_update_time` (`season_id`,`score`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=20301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户赛季积分表'

索引 idx_round_id_score_update_time

package pers.wmx.springbootfreemarkerdemo.entity;

/**
 * @author: wangmingxin03
 * @date: 2020-05-28
 */
public class UserScore {
    private long seasonId;

    private long userId;

    private long score;

    private long createTime;

    private long updateTime;

    private long inverseUpdateTime;

    public long getSeasonId() {
        return seasonId;
    }

    public void setSeasonId(long seasonId) {
        this.seasonId = seasonId;
    }

    public long getUserId() {
        return userId;
    }

    public void setUserId(long userId) {
        this.userId = userId;
    }

    public long getScore() {
        return score;
    }

    public void setScore(long score) {
        this.score = score;
    }

    public long getCreateTime() {
        return createTime;
    }

    public void setCreateTime(long createTime) {
        this.createTime = createTime;
    }

    public long getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(long updateTime) {
        this.updateTime = updateTime;
    }

    public long getInverseUpdateTime() {
        return inverseUpdateTime;
    }

    public void setInverseUpdateTime(long inverseUpdateTime) {
        this.inverseUpdateTime = inverseUpdateTime;
    }
}

/**
 *
 * @author: wangmingxin03
 * @date: 2020-05-28
 */
@Mapper
public interface UserScoreMapper {

    @Insert({
            "<script>",
            "insert into user_score1(season_id, user_id, score, create_time, update_time) ",
            "values ",
            "<foreach collection='userScoreList' item='item' index='index' separator=','>",
            "(#{item.seasonId}, #{item.userId}, #{item.score}, #{item.createTime}, #{item.updateTime})",
            "</foreach>",
            "</script>"
    })
    int batchInsert1(@Param("userScoreList") List<UserScore> userScoreList);

    @Insert({
            "<script>",
            "insert into user_score2(season_id, user_id, score, create_time, update_time, inverse_update_time) ",
            "values ",
            "<foreach collection='userScoreList' item='item' index='index' separator=','>",
            "(#{item.seasonId}, #{item.userId}, #{item.score}, #{item.createTime}, #{item.updateTime}, #{item.inverseUpdateTime})",
            "</foreach>",
            "</script>"
    })
    int batchInsert2(@Param("userScoreList") List<UserScore> userScoreList);

}

注入2w条模拟数据

/**
 * @author: wangmingxin03
 * @date: 2020-05-28
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class CreateUserDataTest {

    @Autowired
    private UserScoreMapper userScoreMapper;

    private final Random random = new Random();

    @Test
    public void test() {
        List<UserScore> userScoreList = new ArrayList<>();
        IntStream.range(0, 20000).forEach(index -> {
            UserScore userScore = new UserScore();
            userScore.setSeasonId(1);
            userScore.setUserId(index + 1);
            userScore.setScore(random.nextInt(10000));
            userScore.setCreateTime(System.currentTimeMillis());
            userScore.setUpdateTime(random.nextInt(100000));
            userScore.setInverseUpdateTime(0); //这个字段先不管
            userScoreList.add(userScore);
        });

        userScoreMapper.batchInsert1(userScoreList);

    }

}

select * from user_score1 where season_id = 1 order by score desc, update_time desc limit 100;

100 rows in set (0.00 sec)

select * from user_score1 where season_id = 1 order by score desc, update_time asc limit 100;

100 rows in set (0.03 sec)

我们用update_time asc 耗时更多

且没有用到idx_round_id_score_update_time 索引

因为一个降序、一个升序使用的filesort

既然按照更新时间升序，我们可以加个字段对更新时间的取反 inverse_update_time

user_score2 | CREATE TABLE `user_score2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `season_id` bigint(20) unsigned NOT NULL COMMENT '赛季id',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `score` bigint(20) unsigned NOT NULL COMMENT '分数',
  `create_time` bigint(20) unsigned NOT NULL COMMENT '创建时间',
  `update_time` bigint(20) unsigned NOT NULL COMMENT '更新时间',
  `inverse_update_time` bigint(20) unsigned NOT NULL COMMENT '反向更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_round_id_user_id` (`season_id`,`user_id`),
  KEY `idx_round_id_score_update_time` (`season_id`,`score`,`update_time`),
  KEY `idx_round_id_score_inverse_update_time` (`season_id`,`score`,`inverse_update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户赛季积分表'

加个索引 idx_round_id_score_inverse_update_time

/**
 * @author: wangmingxin03
 * @date: 2020-05-28
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class CreateUserDataTest {

    @Autowired
    private UserScoreMapper userScoreMapper;

    private final Random random = new Random();

    @Test
    public void test() {
        List<UserScore> userScoreList = new ArrayList<>();
        IntStream.range(0, 50000).forEach(index -> {
            UserScore userScore = new UserScore();
            userScore.setSeasonId(1);
            userScore.setUserId(index + 1);
            userScore.setScore(random.nextInt(10000));
            userScore.setCreateTime(System.currentTimeMillis());
            int updateTime = random.nextInt(100000);
            userScore.setUpdateTime(updateTime);
            userScore.setInverseUpdateTime(Long.MIN_VALUE - updateTime); //逆序更新时间 占位 排序使用
            userScoreList.add(userScore);
        });

        userScoreMapper.batchInsert2(userScoreList);

    }

}

注意这个

userScore.setInverseUpdateTime(Long.MIN_VALUE - updateTime); //逆序更新时间占位排序使用

我们就可以用inverse_update_time 的降序代替update_time 的升序

mysql> select * from user_score2 where season_id = 1 order by score desc, update_time asc limit 100;     
100 rows in set (0.07 sec)      
          
mysql> select * from user_score2 where season_id = 1 order by score desc, inverse_update_time desc limit 100;
100 rows in set (0.00 sec)

明显耗时减少！

我这几万条数据就会查询时间就差了一些，数百万数据效果会更明显

转载请注明：汪明鑫的个人博客 » mysql索引优化实践

说点什么