MySQL 8.0.x+配置优化与查询提速方案咨询

算力阿峰

2026-7-2

MySQL查询性能优化方案

一、优先添加针对性索引

你的查询语句SELECT * FROM db.table1 where Company_ID = 79998 and year = 2009;针对Company_ID和year做等值筛选，当前表仅主键ID有索引，5000万条数据会触发全表扫描，这是耗时30秒的核心原因。

执行以下语句创建联合索引：

CREATE INDEX idx_company_year ON table1(Company_ID, year);

联合索引顺序遵循等值筛选字段优先原则，这里Company_ID和year都是等值条件，顺序不影响查询效率；若后续有范围查询需求，需调整字段顺序。
若后续经常需要查询特定字段而非*，可创建覆盖索引（包含查询所需所有字段），避免回表查询：
```
CREATE INDEX idx_company_year_cover ON table1(Company_ID, year, ID, Fiscal_period_ID); -- 替换为实际需要的字段
```

日期字段类型调整：将Latest_period_end、Entry_date等文本类型的日期字段改为DATE或DATETIME类型：
```
ALTER TABLE table1 MODIFY COLUMN Latest_period_end DATE;
ALTER TABLE table1 MODIFY COLUMN Entry_date DATETIME;
```
文本类型无法利用日期相关优化，且存储占用更大，改为日期类型后能减小表体积，提升查询和排序性能。
数值字段类型优化：将Filing_status_ID、Is_consolidated等DOUBLE类型字段改为INT（如果字段值是整数枚举）：
```
ALTER TABLE table1 MODIFY COLUMN Filing_status_ID INT;
```
DOUBLE类型存储和计算开销远大于整数类型，调整后能降低内存和磁盘IO消耗。
TEXT字段优化：ID_BB_UNIQUE若为固定长度或长度可控的字符串，改为VARCHAR(n)（n设为实际最大长度），TEXT类型的处理开销更高，且无法作为索引前缀（除非设置长度限制）。

根据服务器RAM和CPU资源，优化以下核心配置（修改my.cnf或my.ini后重启MySQL）：

innodb_buffer_pool_size：设置为物理内存的50%-70%（专用数据库服务器），让InnoDB缓存更多表数据和索引，减少磁盘IO。
innodb_log_file_size：设置为1G-4G（根据业务写入量调整），增大日志文件能减少checkpoint频率，提升写性能。
innodb_flush_log_at_trx_commit：若业务允许少量数据丢失风险，设为2（默认1），降低磁盘同步频率，提升性能。
innodb_read_io_threads/innodb_write_io_threads：根据CPU核心数调整，比如8核CPU设为8，提升并发IO处理能力。

避免使用SELECT *，仅查询需要的字段，减少数据传输量和磁盘IO：

SELECT Company_ID, year, Fiscal_period_ID FROM table1 WHERE Company_ID = 79998 AND year = 2009;

先通过EXPLAIN分析查询执行计划，确认索引是否生效：
```
EXPLAIN SELECT * FROM db.table1 where Company_ID = 79998 and year = 2009;
```
查看type列是否为ref或range（表示用到索引），key列是否显示idx_company_year，rows列是否大幅减少。

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