一、MySQL大表的标准和定义数据量维度单表行数超过1000万行或单表占用空间超过100GB不同业务场景下阈值差异较大如高并发业务中500万行可能就成为大表并发场景性能维度查询耗时稳定超过500ms更新/删除操作出现锁等待索引维护创建、删除、重建耗时过长超过1小时二、分片原则和优化方案选择和建议分片原则(1) 能不分就不分做“单表优化”(2) 分片数量尽量少分片尽量均匀分布在多个数据结点上因为一个查询SQL跨分片越多则总体性能越差虽然要好于所有数据在一个分片的结果只在必要的时候进行扩容增加分片数量(3) 分片规则需要慎重选择做好提前规划分片规则的选择需要考虑数据的增长模式数据的访问模式分片关联性问题以及分片扩容问题最近的分片策略为范围分片枚举分片一致性Hash分片这几种分片都有利于扩容(4) 尽量不要在一个事务中的SQL跨越多个分片分布式事务一直是个不好处理的问题(5) 可以通过数据冗余和表分区赖降低跨库Join的可能。优化方案选择和建议不同业务场景、不同数据量规模下应选择不同的优化方案避免盲目采用架构级优化如分库分表增加系统复杂度分库分表的实施建议数据量 1000万优先采用“查询优化索引优化表结构优化”的低成本方案。检查并优化慢查询语句调整索引设计规范表结构通常能满足性能需求1000万 ≤ 数据量 ≤ 1亿采用“数据归档分区表”方案。将历史数据归档减少活跃数据量通过分区表拆分数据提升查询和写入效率若为读多写少场景可搭配读写分离数据量 ≥ 1亿或者并发极高采用“分库分表”架构级方案。结合业务场景选择合适的分片键和拆分策略使用中间件简化实现同时搭配数据归档、读写分离、缓存等方案全方位提升系统性能和可用性。三、大表优化核心思路表设计层从源头减少数据冗余合理设计表结构和索引避免数据过度堆积查询与索引层提升查询效率减少无效数据扫描降低索引维护成本架构和运维层通过分库分表、读写分离、数据归档等方式分散单表压力提升系统并发能力。四、表设计字段优化 (从源头上避免大表问题)优先使用占用空间小的字段类型。例如存储用户ID时若范围允许使用INT4字节而非BIGINT8字节存储状态、性别时使用TINYINT1字节而非VARCHAR使用整数或枚举代替字符串类型尽量使用多时区 / 全球化的TIMESTAMP4字节而非DATETIME8字节同时TIMESTAMP具有自动赋值以及⾃自动更新的特性单表不要有太多字段建议在30个以内避免使用NULL字段对于非必填字段设置合理的默认值避免大量NULL值存储。MySQL对NULL值的存储和查询效率较低且NULL值无法参与索引避免使用大字段尽量避免在核心业务表中使用TEXT、BLOB等大字段。若必须存储如用户头像、富文本内容可将大字段拆分到单独的附属表中核心表仅存储关联ID减少核心表的数据行大小提升查询时的磁盘I/O效率五、索引优化遵循最左匹配原则创建联合索引时将查询频率高、区分度高的字段放在前面。例如业务中频繁查询“用户ID订单状态”则联合索引应为user_id, order_status而非order_status, user_id控制索引数量单表索引数量建议不超过5个过多的索引会导致INSERT、UPDATE、DELETE操作时需要同步维护多个索引严重降低写入性能。对于大表每增加一个索引写入耗时可能会显著增加使用覆盖索引针对频繁的查询场景创建覆盖索引避免回表查询。例如查询“用户ID、订单金额、订单时间”时创建联合索引user_id, order_amount, order_time查询时可直接从索引中获取所需数据无需访问主键索引避免无效索引删除未使用或重复的索引。可通过MySQL的慢查询日志、sys.schema_unused_indexes视图MySQL 8.0统计索引使用情况清理无效索引避免创建与主键索引重复的索引如主键为id再创建索引id考虑分区索引对于分区表索引会按分区创建每个分区的索引体积更小查询时只需扫描对应分区的索引提升查询效率。避免没必要索引值分布很稀少的字段不适合建索引例如性别这种只有两三个值的字段合理创建联合索引避免冗余如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)根据查询有针对性创建索引考虑在WHERE和ORDER BY命令上涉及的列建立索引可根据EXPLAIN来查看是否用了索引还是全表扫描六、查询SQL优化①避免全表扫描确保查询走索引避免在查询条件中对索引字段进行函数操作如SUBSTR、DATE_FORMAT、隐式类型转换如将VARCHAR类型的字段与INT值比较这些操作会导致索引失效触发全表扫描。例如避免“WHERE SUBSTR(user_phone, 1, 3) 138”可改为“WHERE user_phone LIKE 138%”前提是user_phone字段有索引**避免使用模糊查询前缀%模糊查询“LIKE %xxx”或“LIKE %xxx%”会导致索引失效触发全表扫描。若业务需模糊查询可考虑使用Elasticsearch等搜索引擎替代或通过业务调整改为“LIKE xxx%”**避免使用OR条件无索引时当OR条件中的字段无索引时会触发全表扫描。可将OR改为UNION ALL若结果无重复且确保每个查询分支都走索引。例如“WHERE user_id 1 OR order_id 100”可改为“(SELECT * FROM order WHERE user_id 1) UNION ALL (SELECT * FROM order WHERE order_id 100)”。②优化查询语句结构只查询所需字段避免使用SELECT *只查询业务需要的字段。一方面减少数据传输量另一方面若查询字段可通过覆盖索引获取可避免回控制JOIN表数量JOIN表数量越多查询复杂度越高性能越差。大表查询中JOIN表数量建议不超过3个。对于复杂查询可通过分步骤查询、中间表存储结果等方式简化避免使用子查询尤其是相关子查询相关子查询会导致MySQL重复执行子查询语句效率极低。可将子查询改为JOIN查询或通过临时表存储子查询结果合理使用LIMIT对于分页查询使用LIMIT控制返回结果数量。但需注意当分页页码较大时如LIMIT 100000, 20MySQL会扫描前100020条数据再丢弃前100000条效率较低。可通过“索引主键”优化例如“WHERE id 100000 LIMIT 20”前提是id为自增主键且查询条件可基于id过滤。③优化事务和锁控制事务粒度避免长事务长事务会占用锁资源导致其他操作出现锁等待。大表操作中尽量将事务拆分为短事务只包含必要的SQL语句使用合理的隔离级别根据业务需求选择最低的隔离级别。例如若业务允许脏读可使用READ UNCOMMITTED大多数业务可使用READ COMMITTED避免REPEATABLE READ带来的间隙锁问题减少锁等待避免行锁升级为表锁MySQL中若查询条件未走索引会触发全表扫描此时行锁会升级为表锁导致其他操作无法并发执行。需确保更新、删除操作的查询条件走索引避免表锁。④其他不做列运算SELECT id WHERE age 1 10任何对列的操作都将导致表扫描它包括数据库教程函数、计算表达式等等查询时要尽可能将操作移至等号右边SQL语句尽可能简单一条SQL只能在一个cpu运算大语句拆小语句减少锁时间一条大SQL可以堵死整个库OR改写成INOR的效率是n级别IN的效率是log(n)级别in的个数建议控制在200以内不用函数和触发器在应用程序实现模糊查询避免左模糊%xxx式查询使用同类型进行比较比如用123和123比123和123比尽量避免在WHERE子句中使用!或操作符否则将引擎放弃使用索引而进行全表扫描对于连续数值使用BETWEEN不用INSELECT id FROM t WHERE num BETWEEN 1 AND 5列表数据不要拿全表要使用LIMIT来分页每页数量也不要太大。七、数据归档(减少活跃数据量)数据归档是将历史数据迁移到单独的存储介质或表中减少核心表的活跃数据量。 大表中往往存在大量历史数据如3个月前的订单、1年前的日志这些数据访问频率极低但占用大量存储空间影响活跃数据的查询和写入性能。确定归档条件根据业务场景确定归档的时间范围或数据状态。例如订单表中归档3个月前的已完成订单日志表中归档1年前的所有日志选择归档目标同库不同表将历史数据迁移到同库的归档表中如order表归档到order_hist_202401便于后续查询历史数据时直接关联异库存储将历史数据迁移到独立的归档数据库如MySQL从库、Hive、ClickHouse等减少对核心库的资源占用对于海量历史数据可使用低成本的存储介质如对象存储冷热数据分离核心表只保留热点数据如近1个月历史数据迁移到冷存储查询历史数据时通过专门的服务或接口访问。归档执行方式定时任务归档通过xxljob、crontab、Airflow等工具定时执行归档脚本采用“分页查询批量插入批量删除”的方式避免一次性操作大量数据导致锁等待或事务超时在线归档工具使用pt-archiverPercona Toolkit等专业工具支持增量归档、并行归档且对业务影响较小。例如pt-archiver可通过--limit参数控制每次归档的行数--sleep参数控制归档间隔减少对核心表的性能影响八、分区表设计 拆分数据到多个物理分区分区表是MySQL提供的一种表级优化方案将一个大表在逻辑上拆分为多个小表物理上存储为多个独立的文件。查询时MySQL只需扫描对应的分区无需扫描全表从而提升查询效率同时分区表便于数据归档直接删除整个分区、备份恢复单独备份某个分区。分区类型选择MySQL支持多种分区类型需根据业务场景选择 范围分区RANGE Partition按连续的范围划分分区适用于按时间、ID等有序字段拆分的场景如订单表、日志表。例如订单表按创建时间分区每个分区存储1个月的数据用户表按用户ID分区每个分区存储100万用户的数据。范围分区是最常用的分区类型便于数据归档直接删除旧分区 列表分区List Partition按离散的值列表划分分区适用于数据状态固定的场景如订单状态、用户所属地区。例如订单表按订单状态分区分为“待支付”“已完成”“已取消”三个分区 核销表可以按核销状态分区分为“未核销”、“已核销”、“已退款” 哈希分区Hash Partition按字段的哈希值划分分区适用于数据均匀分布、无明显范围或列表特征的场景。例如将用户表按用户ID哈希分区确保每个分区的数据量相对均衡但哈希分区不便于数据归档 复合分布Composite Partition结合两种分区类型如RANGE-HASH、RANGE-LIST适用于复杂场景。例如订单表先按创建时间范围分区每个范围分区内再按订单状态列表分区。分区表使用注意事项分区键选择分区键需与查询条件高度相关确保查询时能精准定位到少数几个分区即“分区裁剪”。例如若订单表的查询多基于创建时间则分区键选择创建时间若查询多基于用户ID则分区键选择用户ID控制分区数量分区数量并非越多越好过多的分区会增加MySQL的元数据管理成本导致查询时分区裁剪效率下降。一般建议单表分区数量不超过100个避免跨分区查询跨分区查询如查询多个分区的数据会导致MySQL扫描多个分区性能可能与非分区表相当甚至更差。需通过业务优化避免跨分区查询或通过索引优化提升跨分区查询效率分区表限制MySQL 5.7及以下版本中分区表不支持外键某些存储引擎如MyISAM对分区表的支持有限建议使用InnoDB引擎分区表的索引是按分区创建的需确保每个分区的索引设计合理。九、分库分表 (架构级拆分突破单库单表限制)核心概念水平拆分将同一个表的数据按行拆分到多个表中每个表的结构相同。例如将订单表按用户ID哈希拆分到10个表中每个表存储10%的用户订单数据。水平拆分是分库分表的主流方式能有效突破单表数据量限制垂直拆分将同一个表的数据按列拆分到多个表中每个表存储部分字段。例如将用户表拆分为用户基本信息表存储ID、姓名、手机号等核心字段和用户详情表存储地址、简介等大字段核心表数据量小查询效率高分库将拆分后的表分布到多个数据库中避免单库的CPU、内存、磁盘I/O资源瓶颈。例如将10个订单分表分布到2个数据库中每个数据库存储5个分表分片键用于拆分数据的字段如用户ID、订单ID、创建时间分片键的选择直接影响分库分表的效果需确保数据均匀分布、查询能精准定位分片。分库分表策略水平拆分范围拆分按分片键的范围拆分数据如按订单创建时间拆分每个分片存储1个月的数据按用户ID拆分每个分片存储100万用户的数据。范围拆分便于数据归档直接删除旧分片但可能出现数据热点如最新月份的订单分片访问频率极高哈希拆分按分片键的哈希值取模拆分数据如按用户ID % 10拆分到10个分片。哈希拆分能确保数据均匀分布避免热点分片但不便于数据归档查询历史数据时可能需要访问多个分片一致性哈希拆分在哈希拆分的基础上通过一致性哈希算法减少分片扩容时的数据迁移量。适用于业务数据量持续增长、需要频繁扩容的场景。垂直拆分按字段访问频率拆分将高频访问的核心字段如用户ID、姓名、订单金额放在主表低频访问的字段如用户简介、订单备注放在从表按字段类型拆分将大字段TEXT、BLOB拆分到单独的表中主表仅存储关联ID减少主表的数据行大小提升查询效率。分库分表实现方式客户端分片在应用程序中直接实现分库分表逻辑通过代码控制数据的写入和查询分片。优点是灵活性高缺点是开发成本高需维护大量分片逻辑代码后续扩容、迁移困难中间件分片使用专业的分库分表中间件如Sharding-JDBC、MyCat、TDSQL中间件封装了分片逻辑应用程序通过中间件访问数据库无需关注分片细节。优点是开发成本低、易于维护和扩容是目前主流的实现方式。如何保证分布式数据库架构中全局唯一ID的生成分布式ID生成的要求和核心难点(1) 全局唯一性保持生成的ID全局唯一在任何情况下也不会出现重复的值如防止时间回拔时钟周期问题(2) 趋势递增保证下一个ID一定大于上一个ID例如事务版本号、IM聊天中的增量消息、排序等特殊需求(3) 高性能ID的需求场景多中心化生成组件后需要高并发处理以接近 0ms的响应大规模并发执行(4) 高可用 作为ID的生产源头需要100%可用当接入的业务系统多的时候很难调整出各方都可接受的停机发布窗口只能接受无损发布(5) 易接入作为逻辑上简单的分布式ID要推广使用必须强调开箱即用容易上手大厂自研分布式ID框架实现美团分布式ID生成系统Leaf(对Snowflake算法进行了改进) Leaf-segment方案可生成全局唯一、全局有序的ID Leaf-snowflake方案可生成全局唯一、局部有序的ID百度分布式ID生成器UidGenerator(对Snowflake算法进行了改进)滴滴的高性能ID生成器(Tinyid)vivo的自研分布式ID服务各大框架对比分片键选择至关重要分片键需满足“数据均匀分布”和“查询精准定位”两个核心要求。例如订单表若按订单ID哈希拆分查询“某用户的所有订单”时会需要访问所有分片效率极低此时应选择用户ID作为分片键查询时可直接定位到用户所属的分片 避免分布式事务分库分表后跨分片的操作会产生分布式事务分布式事务实现复杂、性能差。需通过业务优化避免跨分片操作或采用最终一致性方案如本地消息表、事务消息替代分布式事务 考虑扩容方案拆分时需预留扩容空间避免后续扩容时大量数据迁移。例如采用一致性哈希拆分或按“2的幂次”拆分如初始拆分为8个分片后续可扩容为16个 运维复杂度提升分库分表后数据库集群的运维难度显著增加需关注分片的健康状态、数据一致性、备份恢复等问题。可借助中间件的监控功能或使用专业的运维工具如Prometheus、Grafana进行监控。十、其他优化方案读写分离对于读多写少的大表场景如商品表、用户表可采用读写分离架构主库负责写入操作INSERT、UPDATE、DELETE从库负责读取操作SELECT通过主从复制同步数据。读写分离能分散单库的读写压力提升查询性能需注意主从复制的延迟问题对于实时性要求高的查询需路由到主库。数据库参数优化(通过优化MySQ的配置参数提升数据库性能)调整缓冲池大小innodb_buffer_pool_size建议设置为服务器物理内存的50%-70%提升数据和索引的缓存命中率调整日志相关参数innodb_log_file_size、innodb_log_buffer_size增大日志文件大小减少日志刷盘次数增大日志缓冲区减少磁盘I/O调整连接数参数max_connections、wait_timeout根据业务需求设置合理的最大连接数避免连接耗尽设置合理的连接超时时间释放空闲连接硬件与存储优化硬件和存储是数据库性能的基础大表场景下需选择高性能的硬件使用SSD硬盘SSD的读写速度远高于机械硬盘能显著提升大表的磁盘I/O效率提升CPU和内存配置大表查询和索引维护需要大量CPU和内存资源选择多核CPU、大容量内存的服务器使用RAID阵列通过RAID 0、RAID 10等阵列方式提升磁盘的读写性能和可靠性。十一、总结MySQL大表优化是一个系统性工程需从表设计、查询、索引、数据归档、分区表、分库分表等多个维度综合考量核心是“减少数据量、降低访问成本、分散压力”。优化过程中应遵循“先易后难、先软后硬”的原则优先采用低成本、低风险的方案再根据业务需求逐步升级到架构级优化。大厂的实战案例表明优化方案需紧密结合业务场景电商订单表适合分库分表数据归档日志表适合分区表冷热分离商品表适合垂直拆分读写分离缓存。同时优化后的监控与运维至关重要能确保系统长期稳定运行。好了今天的分享就到此结束了如果文章对你有所帮助欢迎点赞评论收藏❤原文链接https://juejin.cn/post/7588086766235254822
MySQL的大表优化方案 (实战分享)
一、MySQL大表的标准和定义数据量维度单表行数超过1000万行或单表占用空间超过100GB不同业务场景下阈值差异较大如高并发业务中500万行可能就成为大表并发场景性能维度查询耗时稳定超过500ms更新/删除操作出现锁等待索引维护创建、删除、重建耗时过长超过1小时二、分片原则和优化方案选择和建议分片原则(1) 能不分就不分做“单表优化”(2) 分片数量尽量少分片尽量均匀分布在多个数据结点上因为一个查询SQL跨分片越多则总体性能越差虽然要好于所有数据在一个分片的结果只在必要的时候进行扩容增加分片数量(3) 分片规则需要慎重选择做好提前规划分片规则的选择需要考虑数据的增长模式数据的访问模式分片关联性问题以及分片扩容问题最近的分片策略为范围分片枚举分片一致性Hash分片这几种分片都有利于扩容(4) 尽量不要在一个事务中的SQL跨越多个分片分布式事务一直是个不好处理的问题(5) 可以通过数据冗余和表分区赖降低跨库Join的可能。优化方案选择和建议不同业务场景、不同数据量规模下应选择不同的优化方案避免盲目采用架构级优化如分库分表增加系统复杂度分库分表的实施建议数据量 1000万优先采用“查询优化索引优化表结构优化”的低成本方案。检查并优化慢查询语句调整索引设计规范表结构通常能满足性能需求1000万 ≤ 数据量 ≤ 1亿采用“数据归档分区表”方案。将历史数据归档减少活跃数据量通过分区表拆分数据提升查询和写入效率若为读多写少场景可搭配读写分离数据量 ≥ 1亿或者并发极高采用“分库分表”架构级方案。结合业务场景选择合适的分片键和拆分策略使用中间件简化实现同时搭配数据归档、读写分离、缓存等方案全方位提升系统性能和可用性。三、大表优化核心思路表设计层从源头减少数据冗余合理设计表结构和索引避免数据过度堆积查询与索引层提升查询效率减少无效数据扫描降低索引维护成本架构和运维层通过分库分表、读写分离、数据归档等方式分散单表压力提升系统并发能力。四、表设计字段优化 (从源头上避免大表问题)优先使用占用空间小的字段类型。例如存储用户ID时若范围允许使用INT4字节而非BIGINT8字节存储状态、性别时使用TINYINT1字节而非VARCHAR使用整数或枚举代替字符串类型尽量使用多时区 / 全球化的TIMESTAMP4字节而非DATETIME8字节同时TIMESTAMP具有自动赋值以及⾃自动更新的特性单表不要有太多字段建议在30个以内避免使用NULL字段对于非必填字段设置合理的默认值避免大量NULL值存储。MySQL对NULL值的存储和查询效率较低且NULL值无法参与索引避免使用大字段尽量避免在核心业务表中使用TEXT、BLOB等大字段。若必须存储如用户头像、富文本内容可将大字段拆分到单独的附属表中核心表仅存储关联ID减少核心表的数据行大小提升查询时的磁盘I/O效率五、索引优化遵循最左匹配原则创建联合索引时将查询频率高、区分度高的字段放在前面。例如业务中频繁查询“用户ID订单状态”则联合索引应为user_id, order_status而非order_status, user_id控制索引数量单表索引数量建议不超过5个过多的索引会导致INSERT、UPDATE、DELETE操作时需要同步维护多个索引严重降低写入性能。对于大表每增加一个索引写入耗时可能会显著增加使用覆盖索引针对频繁的查询场景创建覆盖索引避免回表查询。例如查询“用户ID、订单金额、订单时间”时创建联合索引user_id, order_amount, order_time查询时可直接从索引中获取所需数据无需访问主键索引避免无效索引删除未使用或重复的索引。可通过MySQL的慢查询日志、sys.schema_unused_indexes视图MySQL 8.0统计索引使用情况清理无效索引避免创建与主键索引重复的索引如主键为id再创建索引id考虑分区索引对于分区表索引会按分区创建每个分区的索引体积更小查询时只需扫描对应分区的索引提升查询效率。避免没必要索引值分布很稀少的字段不适合建索引例如性别这种只有两三个值的字段合理创建联合索引避免冗余如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)根据查询有针对性创建索引考虑在WHERE和ORDER BY命令上涉及的列建立索引可根据EXPLAIN来查看是否用了索引还是全表扫描六、查询SQL优化①避免全表扫描确保查询走索引避免在查询条件中对索引字段进行函数操作如SUBSTR、DATE_FORMAT、隐式类型转换如将VARCHAR类型的字段与INT值比较这些操作会导致索引失效触发全表扫描。例如避免“WHERE SUBSTR(user_phone, 1, 3) 138”可改为“WHERE user_phone LIKE 138%”前提是user_phone字段有索引**避免使用模糊查询前缀%模糊查询“LIKE %xxx”或“LIKE %xxx%”会导致索引失效触发全表扫描。若业务需模糊查询可考虑使用Elasticsearch等搜索引擎替代或通过业务调整改为“LIKE xxx%”**避免使用OR条件无索引时当OR条件中的字段无索引时会触发全表扫描。可将OR改为UNION ALL若结果无重复且确保每个查询分支都走索引。例如“WHERE user_id 1 OR order_id 100”可改为“(SELECT * FROM order WHERE user_id 1) UNION ALL (SELECT * FROM order WHERE order_id 100)”。②优化查询语句结构只查询所需字段避免使用SELECT *只查询业务需要的字段。一方面减少数据传输量另一方面若查询字段可通过覆盖索引获取可避免回控制JOIN表数量JOIN表数量越多查询复杂度越高性能越差。大表查询中JOIN表数量建议不超过3个。对于复杂查询可通过分步骤查询、中间表存储结果等方式简化避免使用子查询尤其是相关子查询相关子查询会导致MySQL重复执行子查询语句效率极低。可将子查询改为JOIN查询或通过临时表存储子查询结果合理使用LIMIT对于分页查询使用LIMIT控制返回结果数量。但需注意当分页页码较大时如LIMIT 100000, 20MySQL会扫描前100020条数据再丢弃前100000条效率较低。可通过“索引主键”优化例如“WHERE id 100000 LIMIT 20”前提是id为自增主键且查询条件可基于id过滤。③优化事务和锁控制事务粒度避免长事务长事务会占用锁资源导致其他操作出现锁等待。大表操作中尽量将事务拆分为短事务只包含必要的SQL语句使用合理的隔离级别根据业务需求选择最低的隔离级别。例如若业务允许脏读可使用READ UNCOMMITTED大多数业务可使用READ COMMITTED避免REPEATABLE READ带来的间隙锁问题减少锁等待避免行锁升级为表锁MySQL中若查询条件未走索引会触发全表扫描此时行锁会升级为表锁导致其他操作无法并发执行。需确保更新、删除操作的查询条件走索引避免表锁。④其他不做列运算SELECT id WHERE age 1 10任何对列的操作都将导致表扫描它包括数据库教程函数、计算表达式等等查询时要尽可能将操作移至等号右边SQL语句尽可能简单一条SQL只能在一个cpu运算大语句拆小语句减少锁时间一条大SQL可以堵死整个库OR改写成INOR的效率是n级别IN的效率是log(n)级别in的个数建议控制在200以内不用函数和触发器在应用程序实现模糊查询避免左模糊%xxx式查询使用同类型进行比较比如用123和123比123和123比尽量避免在WHERE子句中使用!或操作符否则将引擎放弃使用索引而进行全表扫描对于连续数值使用BETWEEN不用INSELECT id FROM t WHERE num BETWEEN 1 AND 5列表数据不要拿全表要使用LIMIT来分页每页数量也不要太大。七、数据归档(减少活跃数据量)数据归档是将历史数据迁移到单独的存储介质或表中减少核心表的活跃数据量。 大表中往往存在大量历史数据如3个月前的订单、1年前的日志这些数据访问频率极低但占用大量存储空间影响活跃数据的查询和写入性能。确定归档条件根据业务场景确定归档的时间范围或数据状态。例如订单表中归档3个月前的已完成订单日志表中归档1年前的所有日志选择归档目标同库不同表将历史数据迁移到同库的归档表中如order表归档到order_hist_202401便于后续查询历史数据时直接关联异库存储将历史数据迁移到独立的归档数据库如MySQL从库、Hive、ClickHouse等减少对核心库的资源占用对于海量历史数据可使用低成本的存储介质如对象存储冷热数据分离核心表只保留热点数据如近1个月历史数据迁移到冷存储查询历史数据时通过专门的服务或接口访问。归档执行方式定时任务归档通过xxljob、crontab、Airflow等工具定时执行归档脚本采用“分页查询批量插入批量删除”的方式避免一次性操作大量数据导致锁等待或事务超时在线归档工具使用pt-archiverPercona Toolkit等专业工具支持增量归档、并行归档且对业务影响较小。例如pt-archiver可通过--limit参数控制每次归档的行数--sleep参数控制归档间隔减少对核心表的性能影响八、分区表设计 拆分数据到多个物理分区分区表是MySQL提供的一种表级优化方案将一个大表在逻辑上拆分为多个小表物理上存储为多个独立的文件。查询时MySQL只需扫描对应的分区无需扫描全表从而提升查询效率同时分区表便于数据归档直接删除整个分区、备份恢复单独备份某个分区。分区类型选择MySQL支持多种分区类型需根据业务场景选择 范围分区RANGE Partition按连续的范围划分分区适用于按时间、ID等有序字段拆分的场景如订单表、日志表。例如订单表按创建时间分区每个分区存储1个月的数据用户表按用户ID分区每个分区存储100万用户的数据。范围分区是最常用的分区类型便于数据归档直接删除旧分区 列表分区List Partition按离散的值列表划分分区适用于数据状态固定的场景如订单状态、用户所属地区。例如订单表按订单状态分区分为“待支付”“已完成”“已取消”三个分区 核销表可以按核销状态分区分为“未核销”、“已核销”、“已退款” 哈希分区Hash Partition按字段的哈希值划分分区适用于数据均匀分布、无明显范围或列表特征的场景。例如将用户表按用户ID哈希分区确保每个分区的数据量相对均衡但哈希分区不便于数据归档 复合分布Composite Partition结合两种分区类型如RANGE-HASH、RANGE-LIST适用于复杂场景。例如订单表先按创建时间范围分区每个范围分区内再按订单状态列表分区。分区表使用注意事项分区键选择分区键需与查询条件高度相关确保查询时能精准定位到少数几个分区即“分区裁剪”。例如若订单表的查询多基于创建时间则分区键选择创建时间若查询多基于用户ID则分区键选择用户ID控制分区数量分区数量并非越多越好过多的分区会增加MySQL的元数据管理成本导致查询时分区裁剪效率下降。一般建议单表分区数量不超过100个避免跨分区查询跨分区查询如查询多个分区的数据会导致MySQL扫描多个分区性能可能与非分区表相当甚至更差。需通过业务优化避免跨分区查询或通过索引优化提升跨分区查询效率分区表限制MySQL 5.7及以下版本中分区表不支持外键某些存储引擎如MyISAM对分区表的支持有限建议使用InnoDB引擎分区表的索引是按分区创建的需确保每个分区的索引设计合理。九、分库分表 (架构级拆分突破单库单表限制)核心概念水平拆分将同一个表的数据按行拆分到多个表中每个表的结构相同。例如将订单表按用户ID哈希拆分到10个表中每个表存储10%的用户订单数据。水平拆分是分库分表的主流方式能有效突破单表数据量限制垂直拆分将同一个表的数据按列拆分到多个表中每个表存储部分字段。例如将用户表拆分为用户基本信息表存储ID、姓名、手机号等核心字段和用户详情表存储地址、简介等大字段核心表数据量小查询效率高分库将拆分后的表分布到多个数据库中避免单库的CPU、内存、磁盘I/O资源瓶颈。例如将10个订单分表分布到2个数据库中每个数据库存储5个分表分片键用于拆分数据的字段如用户ID、订单ID、创建时间分片键的选择直接影响分库分表的效果需确保数据均匀分布、查询能精准定位分片。分库分表策略水平拆分范围拆分按分片键的范围拆分数据如按订单创建时间拆分每个分片存储1个月的数据按用户ID拆分每个分片存储100万用户的数据。范围拆分便于数据归档直接删除旧分片但可能出现数据热点如最新月份的订单分片访问频率极高哈希拆分按分片键的哈希值取模拆分数据如按用户ID % 10拆分到10个分片。哈希拆分能确保数据均匀分布避免热点分片但不便于数据归档查询历史数据时可能需要访问多个分片一致性哈希拆分在哈希拆分的基础上通过一致性哈希算法减少分片扩容时的数据迁移量。适用于业务数据量持续增长、需要频繁扩容的场景。垂直拆分按字段访问频率拆分将高频访问的核心字段如用户ID、姓名、订单金额放在主表低频访问的字段如用户简介、订单备注放在从表按字段类型拆分将大字段TEXT、BLOB拆分到单独的表中主表仅存储关联ID减少主表的数据行大小提升查询效率。分库分表实现方式客户端分片在应用程序中直接实现分库分表逻辑通过代码控制数据的写入和查询分片。优点是灵活性高缺点是开发成本高需维护大量分片逻辑代码后续扩容、迁移困难中间件分片使用专业的分库分表中间件如Sharding-JDBC、MyCat、TDSQL中间件封装了分片逻辑应用程序通过中间件访问数据库无需关注分片细节。优点是开发成本低、易于维护和扩容是目前主流的实现方式。如何保证分布式数据库架构中全局唯一ID的生成分布式ID生成的要求和核心难点(1) 全局唯一性保持生成的ID全局唯一在任何情况下也不会出现重复的值如防止时间回拔时钟周期问题(2) 趋势递增保证下一个ID一定大于上一个ID例如事务版本号、IM聊天中的增量消息、排序等特殊需求(3) 高性能ID的需求场景多中心化生成组件后需要高并发处理以接近 0ms的响应大规模并发执行(4) 高可用 作为ID的生产源头需要100%可用当接入的业务系统多的时候很难调整出各方都可接受的停机发布窗口只能接受无损发布(5) 易接入作为逻辑上简单的分布式ID要推广使用必须强调开箱即用容易上手大厂自研分布式ID框架实现美团分布式ID生成系统Leaf(对Snowflake算法进行了改进) Leaf-segment方案可生成全局唯一、全局有序的ID Leaf-snowflake方案可生成全局唯一、局部有序的ID百度分布式ID生成器UidGenerator(对Snowflake算法进行了改进)滴滴的高性能ID生成器(Tinyid)vivo的自研分布式ID服务各大框架对比分片键选择至关重要分片键需满足“数据均匀分布”和“查询精准定位”两个核心要求。例如订单表若按订单ID哈希拆分查询“某用户的所有订单”时会需要访问所有分片效率极低此时应选择用户ID作为分片键查询时可直接定位到用户所属的分片 避免分布式事务分库分表后跨分片的操作会产生分布式事务分布式事务实现复杂、性能差。需通过业务优化避免跨分片操作或采用最终一致性方案如本地消息表、事务消息替代分布式事务 考虑扩容方案拆分时需预留扩容空间避免后续扩容时大量数据迁移。例如采用一致性哈希拆分或按“2的幂次”拆分如初始拆分为8个分片后续可扩容为16个 运维复杂度提升分库分表后数据库集群的运维难度显著增加需关注分片的健康状态、数据一致性、备份恢复等问题。可借助中间件的监控功能或使用专业的运维工具如Prometheus、Grafana进行监控。十、其他优化方案读写分离对于读多写少的大表场景如商品表、用户表可采用读写分离架构主库负责写入操作INSERT、UPDATE、DELETE从库负责读取操作SELECT通过主从复制同步数据。读写分离能分散单库的读写压力提升查询性能需注意主从复制的延迟问题对于实时性要求高的查询需路由到主库。数据库参数优化(通过优化MySQ的配置参数提升数据库性能)调整缓冲池大小innodb_buffer_pool_size建议设置为服务器物理内存的50%-70%提升数据和索引的缓存命中率调整日志相关参数innodb_log_file_size、innodb_log_buffer_size增大日志文件大小减少日志刷盘次数增大日志缓冲区减少磁盘I/O调整连接数参数max_connections、wait_timeout根据业务需求设置合理的最大连接数避免连接耗尽设置合理的连接超时时间释放空闲连接硬件与存储优化硬件和存储是数据库性能的基础大表场景下需选择高性能的硬件使用SSD硬盘SSD的读写速度远高于机械硬盘能显著提升大表的磁盘I/O效率提升CPU和内存配置大表查询和索引维护需要大量CPU和内存资源选择多核CPU、大容量内存的服务器使用RAID阵列通过RAID 0、RAID 10等阵列方式提升磁盘的读写性能和可靠性。十一、总结MySQL大表优化是一个系统性工程需从表设计、查询、索引、数据归档、分区表、分库分表等多个维度综合考量核心是“减少数据量、降低访问成本、分散压力”。优化过程中应遵循“先易后难、先软后硬”的原则优先采用低成本、低风险的方案再根据业务需求逐步升级到架构级优化。大厂的实战案例表明优化方案需紧密结合业务场景电商订单表适合分库分表数据归档日志表适合分区表冷热分离商品表适合垂直拆分读写分离缓存。同时优化后的监控与运维至关重要能确保系统长期稳定运行。好了今天的分享就到此结束了如果文章对你有所帮助欢迎点赞评论收藏❤原文链接https://juejin.cn/post/7588086766235254822
