如何提升MySQL数据库查询性能？5亿级数据表查询优化求助

问题背景 #

数据库结构 #

company表

mysql> describe company;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

nameserver表

mysql> describe nameserver;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| companyId | int          | NO   | MUL | NULL    |                |
| ns        | varchar(250) | NO   | MUL | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

domain表

mysql> describe domain;
+--------------+--------------+------+-----+-------------------+-------------------+
| Field        | Type         | Null | Key | Default           | Extra             |
+--------------+--------------+------+-----+-------------------+-------------------+
| id           | int          | NO   | PRI | NULL              | auto_increment    |
| nameserverId | int          | NO   | MUL | NULL              |                   |
| domain       | varchar(250) | NO   | MUL | NULL              |                   |
| tld          | varchar(20)  | NO   | MUL | NULL              |                   |
| createDate   | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updatedAt    | datetime     | YES  |     | NULL              |                   |
| status       | tinyint      | NO   |     | NULL              |                   |
| fileNo       | smallint     | NO   | MUL | NULL              |                   |
+--------------+--------------+------+-----+-------------------+-------------------+

索引与约束 #

-- company表索引
ALTER TABLE `company`
  ADD PRIMARY KEY (`id`);

-- domain表索引
ALTER TABLE `domain`
  ADD PRIMARY KEY (`id`),
  ADD KEY `nameserver` (`nameserverId`),
  ADD KEY `domain` (`domain`),
  ADD KEY `tld` (`tld`),
  ADD KEY `fileNo` (`fileNo`);

-- nameserver表索引
ALTER TABLE `nameserver`
  ADD PRIMARY KEY (`id`),
  ADD KEY `company` (`companyId`),
  ADD KEY `ns` (`ns`);

-- 自增配置
ALTER TABLE `company` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `domain` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `nameserver` MODIFY `id` int NOT NULL AUTO_INCREMENT;

-- 外键约束
ALTER TABLE `domain` ADD CONSTRAINT `nameserver` FOREIGN KEY (`nameserverId`) REFERENCES `nameserver` (`id`);
ALTER TABLE `nameserver` ADD CONSTRAINT `company` FOREIGN KEY (`companyId`) REFERENCES `company` (`id`);

数据量 #

• domain表：约5亿条记录
• nameserver表：约200万条记录

慢查询与执行计划 #

执行以下查询耗时约4小时：

SELECT distinct domain FROM domain 
INNER join nameserver on nameserver.id = domain.nameserverId
 WHERE nameserver.companyId = 2

对应的执行计划：

+----+-------------+------------+------------+------+-------------------+------------+---------+-----------------------+------+----------+------------------------------+
| id | select_type | table      | partitions | type | possible_keys     | key        | key_len | ref                   | rows | filtered | Extra                        |
+----+-------------+------------+------------+------+-------------------+------------+---------+-----------------------+------+----------+------------------------------+
|  1 | SIMPLE      | nameserver | NULL       | ref  | PRIMARY,company   | company    | 4       | const                 | 1738 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | domain     | NULL       | ref  | nameserver,domain | nameserver | 4       | tldzone.nameserver.id |  716 |   100.00 | NULL                         |
+----+-------------+------------+------------+------+-------------------+------------+---------+-----------------------+------+----------+------------------------------+

当前服务器配置：8GB内存、双核CPU的VPS。

优化方案 #

1. 索引优化 #

（1）给domain表添加覆盖索引

当前查询需要nameserverId和domain字段，创建包含这两个字段的联合索引，让查询直接从索引取数，无需回表，同时利用索引有序性降低去重开销：

ALTER TABLE domain ADD INDEX idx_nameserverid_domain (nameserverId, domain);

2. 查询语句优化 #

（1）替换DISTINCT为GROUP BY

部分场景下GROUP BY去重效率更高，结合覆盖索引可直接在索引上完成分组：

SELECT domain FROM domain 
INNER JOIN nameserver ON nameserver.id = domain.nameserverId
WHERE nameserver.companyId = 2
GROUP BY domain;

（2）简化关联逻辑

先获取目标企业对应的域名服务器ID列表，再关联domain表，减少关联数据量：

SELECT DISTINCT domain FROM domain 
WHERE nameserverId IN (SELECT id FROM nameserver WHERE companyId = 2);

3. 硬件与MySQL配置优化 #

（1）调整内存参数

针对8GB内存的服务器，修改my.cnf配置：

• innodb_buffer_pool_size = 5G：分配约60%内存给InnoDB缓存，减少磁盘IO
• tmp_table_size = 512M
• max_heap_table_size = 512M：避免临时表写入磁盘，提升去重/分组速度
• innodb_flush_log_at_trx_commit = 2：非强一致性场景下降低磁盘刷写频率

（2）升级硬件

• CPU：升级为4核及以上，提升并行处理能力
• 存储：更换为SSD硬盘，大幅降低随机IO延迟

4. 数据库结构调整 #

（1）添加冗余字段

在domain表中冗余companyId字段，彻底消除表关联开销：

-- 添加字段
ALTER TABLE domain ADD COLUMN companyId int NOT NULL;
-- 初始化数据
UPDATE domain d JOIN nameserver ns ON d.nameserverId = ns.id SET d.companyId = ns.companyId;
-- 添加索引
ALTER TABLE domain ADD INDEX idx_companyid_domain (companyId, domain);

之后查询可简化为：

SELECT DISTINCT domain FROM domain WHERE companyId = 2;

需通过触发器或业务逻辑维护companyId的一致性。

（2）分表/分区

• 分区：对domain表按companyId或nameserverId分区，查询时仅扫描目标分区
• 分表：按companyId或tld水平拆分domain表，将大表拆为多个小表

5. 更换DBMS #

若MySQL优化空间有限，可考虑以下数据库：

• PostgreSQL：对复杂关联、去重查询的优化更出色，支持多种高级索引
• ClickHouse：列存储OLAP数据库，针对大数据量统计、去重场景性能远超传统关系库
• Elasticsearch：利用倒排索引快速完成去重和聚合查询，适合批量数据检索场景

内容的提问来源于stack exchange，提问作者Sadegh Ghanbari