1910万条数据的MySQL查询耗时超5分钟，求优化方案

环境与数据概况 #

• 服务器：Ubuntu Server 20.04，16GB内存 + 2GB交换空间
• sessions表数据量：19,168,798条记录

+----------+-----------------+-----------------+
| count(*) | min(session_id) | max(session_id) |
+----------+-----------------+-----------------+
| 19168798 |               6 |        19170401 |
+----------+-----------------+-----------------+

慢查询语句 #

执行耗时接近5分钟的查询：

SELECT appid, country, COUNT(DISTINCT(uuid)) as total 
FROM sessions 
WHERE appid = (appids) 
  AND created >= (createdFrom) AND created <= (createdTo) 
GROUP BY country 
ORDER BY total DESC;

现有配置与结构 #

mysqld.cnf配置 #

[mysqld]
#
# * Basic Settings
#
user        = mysql
# pid-file  = /var/run/mysqld/mysqld.pid
# socket    = /var/run/mysqld/mysqld.sock
# port      = 3306
# datadir   = /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir        = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
# max_allowed_packet    = 64M
# thread_stack      = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log        = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id     = 1
# log_bin           = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db      = include_database_name
# binlog_ignore_db  = include_database_name

sessions表结构 #

DESCRIBE sessions;

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| session_id     | int          | NO   | PRI | NULL    | auto_increment |
| uuid           | varchar(60)  | NO   | MUL | NULL    |                |
| userid         | int          | YES  |     | NULL    |                |
| appid          | int          | NO   |     | NULL    |                |
| mau_count      | int          | YES  |     | 0       |                |
| systemversion  | varchar(256) | NO   |     | NULL    |                |
| os             | varchar(256) | NO   |     | NULL    |                |
| language       | varchar(256) | YES  |     | NULL    |                |
| country        | varchar(256) | YES  |     | NULL    |                |
| appversion     | varchar(256) | YES  |     | NULL    |                |
| phonename      | varchar(256) | YES  |     | NULL    |                |
| devicemodel    | varchar(256) | YES  |     | NULL    |                |
| city           | varchar(256) | YES  |     | NULL    |                |
| state          | varchar(256) | YES  |     | NULL    |                |
| latitude       | varchar(256) | YES  |     | NULL    |                |
| longitude      | varchar(256) | YES  |     | NULL    |                |
| startdatetime  | datetime     | YES  |     | NULL    |                |
| enddatetime    | datetime     | YES  |     | NULL    |                |
| duration       | int          | YES  |     | NULL    |                |
| phonecarrier   | varchar(256) | YES  |     | NULL    |                |
| created_at     | datetime     | YES  |     | NULL    |                |
| updated_at     | datetime     | YES  |     | NULL    |                |
| is_deleted     | int          | NO   |     | NULL    |                |
| created        | date         | YES  | MUL | NULL    |                |
| api_version    | varchar(50)  | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

索引情况 #

SHOW INDEXES FROM sessions;

+----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sessions |          0 | PRIMARY        |            1 | session_id     | A         |    19169624 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sessions |          0 | session_id     |            1 | session_id     | A         |    19169624 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sessions |          1 | sessionUuid    |            1 | uuid           | A         |     2738517 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sessions |          1 | sessionCreated |            1 | created        | A         |        5189 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| sessions |          1 | sessionCreated |            2 | applicationsid | A         |       25939 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sessions |          1 | sessionCreated |            3 | country        | A         |      259048 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| sessions |          1 | sessionCreated |            4 | language       | A         |      383392 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| sessions |          1 | sessionCreated |            5 | uuid           | A         |     4792406 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

EXPLAIN分析 #

执行语句：

EXPLAIN SELECT appid,country, COUNT(DISTINCT(uuid)) as total FROM sessions WHERE appid = 111 AND created >= '2023-12-01' AND created <= '2024-02-07' GROUP BY country ORDER BY total DESC;

EXPLAIN结果截图：

优化建议 #

1. 创建针对性复合索引 #

当前查询的过滤条件是appid和created，分组字段是country，还要统计DISTINCT(uuid)。现有索引sessionCreated包含的是created, applicationsid（注意字段名与查询用的appid不符，疑似笔误），无法匹配查询需求。需创建前缀为过滤条件，后续包含分组和统计字段的覆盖索引，让MySQL直接通过索引完成查询，避免回表：

CREATE INDEX idx_appid_created_country_uuid ON sessions(appid, created, country, uuid);

2. 调整MySQL内存配置 #

服务器有16GB内存，当前配置未充分利用，修改mysqld.cnf以下参数：

• innodb_buffer_pool_size = 10G：设置为内存的50%-70%，让InnoDB缓存更多表数据和索引，减少磁盘IO
• key_buffer_size = 64M：该参数针对MyISAM引擎，InnoDB环境下无需过大
• sort_buffer_size = 2M：为分组排序分配合理内存，避免过大导致内存耗尽
• join_buffer_size = 2M
• max_connections = 200：根据实际业务需求调整
• 开启log-queries-not-using-indexes：便于排查未命中索引的查询

修改后重启MySQL服务生效。

3. 优化查询语句 #

• 移除冗余字段：WHERE已限定appid为固定值，SELECT中的appid可删除，减少数据传输

SELECT country, COUNT(DISTINCT(uuid)) as total 
FROM sessions 
WHERE appid = 111 
  AND created >= '2023-12-01' AND created <= '2024-02-07' 
GROUP BY country 
ORDER BY total DESC;

4. 其他优化方向 #

• 缩小country字段类型：如果国家代码是2/3位编码，将varchar(256)改为char(2)或char(3)，减少存储空间和索引大小
• 数据归档：将不常用的历史数据迁移至归档表，降低主表数据量
• 分区表优化：按created字段分区，让查询仅扫描指定时间范围的分区数据

内容的提问来源于stack exchange，提问作者Chirag Lukhi