十亿级MySQL大表的查询优化10种方法！

admin • • SQL技巧

十亿级MySQL大表的查询优化10种方法！

作者：程序员Thomas
针对十亿级MySQL大表的查询优化,本文主要介绍了10种经过验证的核心优化策略及对应SQL实现方案,具有一定的参考价值,感兴趣的可以了解一下。

验证的核心优化策略及对应SQL实现方案，结合最新技术实践总结：

一、分区表优化（水平切分）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建时间范围分区表
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- 查询指定分区
SELECT * FROM orders PARTITION (p2022) WHERE user_id=123;

通过将表按时间维度分区，可使查询仅扫描相关数据块。建议每月/季度自动创建新分区

二、分库分表（水平拆分）

1
2
3
4
5
6
7
8
-- 创建分片表（user_id取模分64张表）
CREATE TABLE user_00 LIKE users;
CREATE TABLE user_01 LIKE users;
...
CREATE TABLE user_63 LIKE users;
-- 分片查询路由
SELECT * FROM user_${user_id % 64} WHERE user_id=123456;

采用ShardingSphere等中间件实现透明分片，需注意分片键选择高基数字段避免数据倾斜

三、索引深度优化

1
2
3
4
5
6
7
-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_cover (user_id, order_date, amount);
-- 强制索引使用
SELECT /*+ INDEX(orders idx_cover) */ order_date, amount
FROM orders
WHERE user_id=123 AND order_date > '2024-01-01';

通过覆盖索引减少回表操作，定期使用ANALYZE TABLE更新统计信息。建议单表索引不超过5个

四、冷热数据分离

1
2
3
4
5
6
7
8
9
10
11
-- 归档历史数据
CREATE TABLE orders_archive LIKE orders;
INSERT INTO orders_archive SELECT * FROM orders
WHERE order_date < '2023-01-01';
DELETE FROM orders WHERE order_date < '2023-01-01';
-- 创建联合视图
CREATE VIEW orders_union AS
SELECT * FROM orders
UNION ALL
SELECT * FROM orders_archive;

建议将3年前数据迁移至归档表，使用分区表自动管理可替代该方案

五、查询重写优化

1
2
3
4
5
6
-- 低效分页改造
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;
-- 避免全表扫描
SELECT user_id FROM orders FORCE INDEX(primary)
WHERE create_time BETWEEN '2024-01-01' AND '2024-03-01';

配合EXPLAIN分析执行计划，禁用SELECT *，对范围查询添加FORCE INDEX提示

六、列式存储引擎

1
2
3
4
5
6
-- 创建列式存储表
CREATE TABLE logs (
id BIGINT,
log_time DATETIME,
content TEXT
) ENGINE=Columnstore;

适用于OLAP场景，ClickHouse列式引擎查询速度可提升10倍以上（需配合数据同步机制）

七、服务器参数调优

1
2
3
4
5
6
7
# my.cnf核心参数
[mysqld]
innodb_buffer_pool_size=128G
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=20000
innodb_read_io_threads=16
query_cache_type=0

内存配置建议为物理内存的70%-80%，SSD硬盘需调整IO相关参数

八、异步批处理机制

1
2
3
4
5
6
7
8
9
-- 批量插入优化
INSERT INTO orders (user_id,amount)
VALUES (1,100),(2,200),(3,300);
-- 延迟更新
SET GLOBAL innodb_flush_log_at_trx_commit=2;
START TRANSACTION;
...批量操作...
COMMIT;

批量操作减少事务提交次数，配合LOAD DATA INFILE实现高速导入

九、分布式架构升级

1
2
3
4
-- TiDB分布式查询
SELECT /*+ READ_FROM_STORAGE(tikv[orders]) */ *
FROM orders
WHERE user_id IN (SELECT user_id FROM vip_users);

当单机性能达到瓶颈时，迁移至TiDB集群可实现自动分片和弹性扩展

十、字段类型优化

1
2
3
4
5
6
7
8
-- IP地址存储优化
ALTER TABLE access_log
MODIFY ip INT UNSIGNED,
ADD INDEX idx_ip (ip);
-- 枚举类型改造
ALTER TABLE users
MODIFY gender ENUM('M','F') NOT NULL DEFAULT 'M';

使用INT存储IP（INET_ATON()转换），用ENUM替代字符串字段，可减少50%存储空间

优化实施路线建议：

  • 优先进行架构设计优化（分库分表/分区）
  • 实施索引重构和查询重写
  • 配置合理的服务器参数
  • 建立数据归档机制
  • 最终考虑分布式方案

定期使用SHOW GLOBAL STATUS监控QPS、TPS、缓存命中率等关键指标。当QPS>10万时建议采用TiDB等NewSQL方案

更多实现细节可参考MySQL官方文档及ShardingSphere、TiDB等技术白皮书。实际优化需结合业务特点进行方案组合。

到此这篇关于十亿级MySQL大表的查询优化10种方法的文章就介绍到这了。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>