MySQL排行榜查询优化：百万级用户表单查询耗时2秒的问题排查

AI 小北

2026-8-24

排行榜查询性能优化问题

我正在开发一个排行榜功能，用以下Java代码实现排名查询，但在100万用户的表中，单条查询耗时高达2秒。我已经尝试强制使用索引，已知索引是包含uuid和目标列的复合索引，相关列都是BIGINT类型，想请教如何优化这个查询，是不是索引用错了，或者是SELECT COUNT(*)导致的性能问题？

当前Java实现代码

public long checkPosition(String table,String column, UUID uuid){
        DataAPI dataAPI = Core.getInstance().getDataAPI();

        String index = table + "_index_" + column;

        //SELECT id, name, score, FIND_IN_SET( score, (SELECT GROUP_CONCAT(score ORDER BY score DESC ) FROM scores )) AS rank FROM scores WHERE name =  'Assem'
        //SELECT 1 + COUNT(*) AS rank FROM table WHERE "+column+" > (SELECT "+column+" FROM "+table+" WHERE uuid='"+uuid.toString()+"')

        int level = 300;

        String query = "SELECT 1 + COUNT(*) AS rank FROM "+table+" FORCE INDEX("+index+") WHERE "+column+" > (SELECT `"+column+"` FROM week_statistics_users FORCE INDEX(week_statistics_users_index_uuid) WHERE `uuid`='"+uuid.toString()+"');";

        try (Connection connection = dataAPI.getConnection()
             ;PreparedStatement statement = connection.prepareStatement(query)){
            ResultSet resultSet;
            resultSet = statement.executeQuery();
            resultSet.next();
            return (long) resultSet.getObject(1);
        } catch (Exception ex){
            ex.printStackTrace();
        }
        return -1;
    }

EXPLAIN查询结果

MariaDB [s2_goliath]> EXPLAIN SELECT 1 + COUNT(*) AS rank FROM lifetime_statistics_users WHERE kills > (SELECT kills FROM week_statistics_users WHERE uuid='2bd9d043-8187-444c-8536-ebd843885ad2');
+------+-------------+---------------------------+-------+---------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------+--------------------------+
| id   | select_type | table                     | type  | possible_keys                                                                   | key                                   | key_len | ref   | rows   | Extra                    |
+------+-------------+---------------------------+-------+---------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------+--------------------------+
|    1 | PRIMARY     | lifetime_statistics_users | range | lifetime_statistics_users_index_kills,lifetime_statistics_users_index_killsuuid | lifetime_statistics_users_index_kills | 9       | NULL  | 118320 | Using where; Using index |
|    2 | SUBQUERY    | week_statistics_users     | const | PRIMARY,week_statistics_users_index_uuid                                        | PRIMARY                               | 402     | const | 1      |                          |
+------+-------------+---------------------------+-------+---------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------+--------------------------+
2 rows in set (0.000 sec)

更新信息：表状态

| Name                      | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary

| month_statistics_users    | InnoDB |      10 | Dynamic    | 248956 |            200 |    50020352 |               0 |    392118272 |   6291456 |           NULL | 2022-07-30 17:28:22 | 2022-07-31 16:53:50 | NULL       | utf8mb4_general_ci |     NULL |                |         |                0 | N

建表语句

MariaDB [s2_goliath]> SHOW CREATE TABLE lifetime_statistics_users \G
*************************** 1. row ***************************
       Table: lifetime_statistics_users
Create Table: CREATE TABLE `lifetime_statistics_users` (
  `uuid` varchar(100) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `json` mediumtext DEFAULT NULL,
  `kills` bigint(20) DEFAULT NULL,
  `deaths` bigint(20) DEFAULT NULL,
  `playtime` bigint(20) DEFAULT NULL,
  `money` decimal(14,2) DEFAULT NULL,
  `level` bigint(20) DEFAULT NULL,
  `texture` longtext DEFAULT NULL,
  `blocks_broken` decimal(14,2) DEFAULT NULL,
  `blocks_placed` bigint(20) DEFAULT NULL,
  `mobs_killed` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `lifetime_statistics_users_index_uuid` (`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_name` (`name`) USING BTREE,
  KEY `lifetime_statistics_users_index_nameuuid` (`name`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_kills` (`kills`) USING BTREE,
  KEY `lifetime_statistics_users_index_killsuuid` (`kills`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_deaths` (`deaths`) USING BTREE,
  KEY `lifetime_statistics_users_index_deathsuuid` (`deaths`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_playtime` (`playtime`) USING BTREE,
  KEY `lifetime_statistics_users_index_playtimeuuid` (`playtime`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_money` (`money`) USING BTREE,
  KEY `lifetime_statistics_users_index_moneyuuid` (`money`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_level` (`level`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_broken` (`blocks_broken`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_brokenuuid` (`blocks_broken`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_placed` (`blocks_placed`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_placeduuid` (`blocks_placed`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_mobs_killed` (`mobs_killed`) USING BTREE,
  KEY `lifetime_statistics_users_index_mobs_killeduuid` (`mobs_killed`,`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

优化建议

1. 修复逻辑bug+精简索引

先修正代码硬编码问题：子查询表名写死为week_statistics_users，但方法参数包含table变量，这会导致查询逻辑错误，同时跨表查询带来额外性能损耗，必须改为使用传入的table参数。
删除冗余单字段索引：目前每个统计字段都同时存在单字段索引和复合索引（比如kills与kills,uuid），单字段索引完全多余——复合索引的前缀列已覆盖单字段索引的查询场景，保留复合索引即可，减少写入时的索引维护开销。查询时无需FORCE INDEX，优化器会自动选择合适的复合索引。

2. 替换COUNT(*)的低效计数方式

InnoDB的COUNT(*)即使使用覆盖索引，也需要遍历所有符合条件的行计数，当匹配行数较多时（比如执行计划中的11万行），耗时必然很高。推荐两种解决方案：

预计算排名表：创建专门的排名表（如user_ranks），包含uuid、kills_rank、deaths_rank等字段，通过定时任务（如每5分钟）批量计算所有用户的排名并更新。查询时直接从该表取数，耗时可降至毫秒级，这是排行榜场景的主流优化方案。
窗口函数实时查询（仅适合低并发/小数据量）：若必须实时计算且MariaDB版本≥10.2，可使用窗口函数：
```
SELECT rank FROM (
    SELECT uuid, 
           RANK() OVER(ORDER BY kills DESC) AS rank
    FROM lifetime_statistics_users
) t WHERE uuid = '2bd9d043-8187-444c-8536-ebd843885ad2';
```
该方案本质仍为全表扫描，数据量大时性能依然不佳，仅适合临时场景。

3. 修复SQL注入风险

代码直接拼接SQL字符串存在严重注入漏洞：

对表名、索引名这类无法用占位符的部分，必须做白名单校验（仅允许预定义的合法表/列名）；

对uuid这类参数，必须使用PreparedStatement的占位符设置：

// 示例：先校验表名和列名是否合法
if (!allowedTables.contains(table) || !allowedColumns.contains(column)) {
    throw new IllegalArgumentException("Invalid table or column");
}
String index = table + "_index_" + column + "uuid";
String query = String.format("SELECT 1 + COUNT(*) AS rank FROM `%s` WHERE `%s` > (SELECT `%s` FROM `%s` WHERE uuid=?)", 
                            table, column, column, table);
try (Connection connection = dataAPI.getConnection();
     PreparedStatement statement = connection.prepareStatement(query)) {
    statement.setString(1, uuid.toString());
    ResultSet resultSet = statement.executeQuery();
    resultSet.next();
    return (long) resultSet.getObject(1);
} catch (Exception ex){
    ex.printStackTrace();
}
return -1;

4. 优化UUID存储

当前uuid字段为varchar(100)，字符串类型索引体积大、查询效率低。建议将UUID转换为BINARY(16)存储：

插入时用UUID_TO_BIN(uuid_str)将字符串UUID转为二进制；
查询时用BIN_TO_UUID(uuid_bin)转回字符串。
这能大幅减少索引存储空间，提升索引扫描速度。

内容的提问来源于stack exchange，提问作者Pedro Paulo Monte Pagani

MySQL排行榜查询优化：百万级用户表单查询耗时2秒的问题排查

当前Java实现代码 #

EXPLAIN查询结果 #

更新信息：表状态 #

建表语句 #

优化建议 #

1. 修复逻辑bug+精简索引 #

2. 替换COUNT(*)的低效计数方式 #

3. 修复SQL注入风险 #

4. 优化UUID存储 #