MariaDB双列表COUNT及关联查询过慢的优化咨询

问题背景 #

company_details表包含近10万行数据，其中details为TEXT类型字段，平均存储5000字符。执行COUNT(id)耗时近2分钟，但通过id单条查询仅需4毫秒；关联查询无关联详情的公司数量耗时超10分钟。执行OPTIMIZE TABLE后，COUNT耗时降至5秒，关联查询耗时降至25秒，但仍有优化空间。

表结构 #

MariaDB [companies]> describe company_details;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id      | int(10) unsigned | NO   | PRI | NULL    |       |
| details | text             | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+

COUNT查询执行计划及耗时 #

MariaDB [companies]> explain select count(id) from company_details;
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
| id   | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
|    1 | SIMPLE      | company_details | index | NULL          | PRIMARY | 4       | NULL | 71267 | Using index |
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+

MariaDB [companies]> select count(id) from company_details;
+-----------+
| count(id) |
+-----------+
|     96544 |
+-----------+
1 row in set (1 min 43.199 sec)

关联查询耗时 #

MariaDB [companies]> SELECT COUNT(*) FROM company c LEFT JOIN company_details cd ON c.id = cd.id WHERE cd.id IS NULL;
+----------+
| count(*) |
+----------+
|    42178 |
+----------+
1 row in set (10 min 28.846 sec)

OPTIMIZE TABLE后的效果 #

MariaDB [companies]> optimize table company_details;
+---------------------------+----------+----------+-------------------------------------------------------------------+
| Table                     | Op       | Msg_type | Msg_text                                                          |
+---------------------------+----------+----------+-------------------------------------------------------------------+
| companies.company_details | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| companies.company_details | optimize | status   | OK                                                                |
+---------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (11 min 21.195 sec)

执行后，COUNT(id)耗时降至5秒，关联查询耗时降至25秒。

性能瓶颈分析 #

1. 大字段导致表碎片化：details是大TEXT字段，频繁增删改会造成表空间碎片化。即使COUNT使用主键索引（Using index），数据库仍需扫描索引对应的物理数据块，碎片化会大幅增加IO开销。OPTIMIZE TABLE通过重建表和索引减少碎片化，性能得到提升，但这是临时方案，后续碎片化会再次出现。
2. InnoDB COUNT的本质：InnoDB无内置总行数统计，COUNT(id)需遍历主键索引所有叶子节点，碎片化严重时会产生大量随机IO。
3. 关联查询低效：左连接后筛选cd.id IS NULL的逻辑，若company表数据量大且无合适索引配合，会触发全表扫描和大量匹配计算，碎片化进一步加剧IO负担。

具体优化方案 #

1. 维护独立计数表 #

创建专门的计数表，通过触发器同步更新company_details的总行数，彻底解决COUNT查询慢的问题：

-- 创建计数表
CREATE TABLE table_counts (
    table_name VARCHAR(64) PRIMARY KEY,
    row_count INT UNSIGNED NOT NULL DEFAULT 0
);

-- 初始化计数
INSERT INTO table_counts (table_name, row_count) 
VALUES ('company_details', (SELECT COUNT(id) FROM company_details));

-- 插入触发器
DELIMITER //
CREATE TRIGGER after_company_details_insert
AFTER INSERT ON company_details
FOR EACH ROW
BEGIN
    UPDATE table_counts SET row_count = row_count + 1 WHERE table_name = 'company_details';
END //
DELIMITER ;

-- 删除触发器
DELIMITER //
CREATE TRIGGER after_company_details_delete
AFTER DELETE ON company_details
FOR EACH ROW
BEGIN
    UPDATE table_counts SET row_count = row_count - 1 WHERE table_name = 'company_details';
END //
DELIMITER ;

后续查询总行数直接从计数表获取：

SELECT row_count FROM table_counts WHERE table_name = 'company_details';

该方法可将COUNT查询耗时降至毫秒级。

2. 改写关联查询逻辑 #

将左连接筛选NULL的逻辑改为NOT EXISTS，通常执行效率更高：

SELECT COUNT(*) FROM company c 
WHERE NOT EXISTS (SELECT 1 FROM company_details cd WHERE cd.id = c.id);

确保company和company_details的id均为主键，子查询会利用主键索引快速匹配。

3. 高效清理碎片化 #

OPTIMIZE TABLE耗时过长，可改用ALTER TABLE重建表，效果相同且可在业务低峰期执行：

ALTER TABLE company_details ENGINE=InnoDB;

同时确保开启innodb_file_per_table（默认开启），让每个表拥有独立表空间，碎片化清理更高效。

4. 使用近似计数（业务允许时） #

若业务可接受近似值，可直接查询InnoDB维护的统计行数，耗时极短：

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'companies' AND TABLE_NAME = 'company_details';

该值在ANALYZE TABLE后更新，误差通常在10%以内。

5. 拆分大字段到独立表 #

将details大字段拆分到单独表，仅在需要查询详情时关联，大幅降低主表数据量：

-- 创建详情内容表
CREATE TABLE company_details_content (
    id INT(10) UNSIGNED PRIMARY KEY,
    details TEXT NOT NULL,
    FOREIGN KEY (id) REFERENCES company_details(id) ON DELETE CASCADE
);

-- 迁移数据
INSERT INTO company_details_content (id, details) SELECT id, details FROM company_details;

-- 修改原表
ALTER TABLE company_details DROP COLUMN details;

改造后company_details仅保留主键字段，COUNT和关联查询的IO开销会大幅降低，性能显著提升。

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