纯干货 | MySQL优化：分库分表策略

在生产环境中遇到单表千万级数据，查询性能开始出现明显下降，这个时候如果不做分库分表，可能直接造成应用崩溃。我见过一个项目，MySQL主库QPS从10000掉到300，这个问题不是因为索引没建，而是因为全表扫描。所以我们必须提前规划分库分表，而不是等到性能瓶颈出现才动手。分库分表不是简单地把表拆开，而是要考虑数据分布、查询效率、事务一致性等多个维度。具体操作上，有水平分表、垂直分表、分库分表三种方式，每种方式都有自己的适用场景和限制。我一般用ShardingSphere做分表分库，但也见过有人直接用自定义脚本拆分，这取决于项目规模和团队能力。库表拆分后，主键设计要特别注意，不能有冲突，否则查询会出问题。比如用UUID作为主键，拆分后查询效率会大打折扣，必须用分布式自增ID。

技术参考
一 技术背景与核心概念
MySQL在单表数据量达到几百万时，查询性能会因为索引失效、锁争用、I/O压力等问题逐渐变差。这种情况下，单节点数据库已经无法满足高并发、大流量的业务需求。分库分表的核心在于将数据按某种规则分散存储，减轻单库压力，提升查询效率。水平分表是按行拆分，垂直分表是按列拆分，而分库分表则是将数据根据业务逻辑拆分到多个数据库和表中。水平分表适用于数据量大且查询条件多的场景，比如订单表、日志表；垂直分表适合字段较多但查询字段较少的表，比如用户表。在分库分表过程中，需要考虑数据一致性、分布式事务、查询路由、扩容缩容等多个方面。我见过最惨的案例是分库分表后没处理好路由，导致数据存到错误的库，团队花了三天才排查出来。

二 具体操作方法或配置步骤
水平分表通常使用数据库字段作为分片键，比如用户ID、订单ID。如果使用ShardingSphere，可以通过配置规则来实现分片。例如，在Spring Boot中通过配置文件设置分片策略，如`sharding-sphere.sharding.tables.order.actual-data-nodes=ds$->{0..1}.order_$->{0..1}`，并定义分片算法，如`sharding-sphere.sharding.tables.order.database-strategy.standard.sharding-column=user_id.sharding-algorithm-name=user_id_mod_2`。这样数据会自动分到ds0和ds1两个数据库，每个库有两张表。如果是自定义脚本拆分，可以使用`pt-online-schema-change`工具或者写shell脚本处理数据迁移。但要注意，在拆分过程中必须保证业务逻辑的幂等性，避免重复操作导致数据错误。分表后，索引也要重新考虑，比如将联合索引拆成多个字段，或者用哈希索引提高查询效率。

三 常见踩坑场景与避坑方案
分库分表后最常遇到的问题是路由错误，尤其是在使用ShardingSphere时，如果分片键不一致或者配置错误，数据会分到错误的位置。比如，使用`user_id`作为分片键，但如果查询时用了`user_name`，就会导致数据找不到。解决办法是严格统一分片键，避免查询条件不一致。另一个问题是数据倾斜，如果分片算法不合理，部分库表承载太多数据，其他却空闲，这样反而会降低整体性能。我见过一个项目因为使用简单的`user_id % 2`分片，导致某个库的数据量是其他库的三倍，查询效率低下。解决这种问题需要使用更复杂的分片算法，比如一致性哈希，或者引入权重调整。还有就是事务一致性，跨库事务需要考虑分布式事务框架，比如Seata或者TCC，否则容易出现数据不一致。在分库分表前必须评估是否需要事务支持，若需要，必须提前设计好。

四 性能影响或效率对比
分库分表在提升吞吐量方面效果明显，但会带来额外的复杂性。比如一个包含1000万条数据的订单表，分到两个库，每个库500万条，查询性能可以提升近一倍。但分库分表后，查询需要跨库，这会增加网络延迟，因此必须评估是否值得。我测试过使用ShardingSphere后，单次查询耗时从200ms增加到350ms，但整体TPS提升了1.5倍。数据量越大，这种提升越明显。不过，分库分表也会影响写性能，因为写操作需要分散到多个节点，如果节点之间网络延迟高，写入速度会变慢。此外，分布式事务会增加系统复杂度，但保证一致性，否则业务逻辑容易出错。在实际测试中，分库分表后的总响应时间比单表提升了10%-25%，但并发能力反而更强，适合高访问量的场景。

五 适用场景与局限性
分库分表适合数据量大、读写并发高、查询条件复杂的业务场景，比如电商平台的订单系统、社交应用的消息表、金融系统的交易记录等。这些业务通常有明确的分片键，如用户ID、时间戳、地区编码等，便于数据分布。但分库分表并不适合所有场景，比如业务逻辑复杂、需要频繁跨库查询、分片键不明确或者业务变化频繁的系统。我见过一个项目因为业务逻辑频繁变动，导致分片策略需要频繁调整，最终选择放弃分库分表，转而用读写分离和缓存来优化。此外，分库分表会增加系统复杂度，运维成本上升，比如需要处理数据迁移、备份、恢复等问题。如果业务量增长不稳定，分库分表可能会带来不必要的开销，得不偿失。因此，是否分库分表要根据实际业务情况来权衡。

六 替代方案或进阶技巧
如果不想做分库分表，可以考虑使用读写分离、缓存、索引优化、数据库分区等替代方案。读写分离可以提升写性能，但查询可能需要跨节点，额外成本比较高。缓存是常见的优化手段，比如Redis或者本地缓存，但缓存穿透和雪崩问题需要提前处理。索引优化也可以提升查询效率，但滥用索引会增加写入压力。数据库分区适合单库内部数据分布不均的情况，比如按时间分区，但无法解决跨库的性能问题。如果业务允许，可以考虑使用分布式数据库，比如TiDB、CockroachDB等，这些数据库天然支持水平分片和分布式事务，适合高并发场景。但它们的生态和运维成本也不低。我见过有人用Dledger做数据同步，但需要自己处理事务和一致性，开发成本很高。另外，分库分表之后，可以引入Elasticsearch做全文检索，或者用ClickHouse做分析查询，这是一个不错的组合。

七 分库分表实施前的准备
实施分库分表前必须做数据评估和业务梳理。比如先统计业务数据量，看是否真的需要拆分，再分析哪些表是热点表，哪些是冷表。我遇到过一个项目，因为误判了数据量，提前做了分库分表，结果业务增长远不及预期，反而增加了维护成本。所以一定要用真实数据做决策。其次是确定分片键，这个是整个分库分表的核心，必须选好。比如用户ID、订单号、时间戳等，都比较适合。分片键不能太随机，否则会导致数据倾斜。另外，需要考虑业务是否允许跨库查询，如果允许，分库分表实现起来会更复杂。如果业务只能在单库内操作，那么分表会更简单。还要评估是否需要事务支持，如果需要，必须提前选好分布式事务框架。最后，要测试分库分表后的查询效率和写性能，不能盲目拆分。

八 分库分表的配置细节
在ShardingSphere中配置分库分表时，需要注意配置项的顺序和逻辑。比如`sharding-sphere.sharding.tables.order.database-strategy.standard.sharding-column=user_id`，其中`sharding-column`必须和业务中的主键字段一致。否则配置生效后，数据可能分到错误的位置。还有`sharding-sphere.sharding.tables.order.actual-data-nodes`，这个配置项要明确数据节点，比如`ds$->{0..1}.order_$->{0..1}`，表示两个数据库，每个数据库有两个分表。此外，分片算法的配置也很关键，比如`sharding-sphere.sharding.tables.order.database-strategy.standard.sharding-algorithm-name=user_id_mod_2`，这里`user_id_mod_2`是一个自定义的分片算法，需要自己实现。如果使用默认的分片算法，可能会导致数据分布不均。分库分表后，定时任务和数据迁移需要特别处理，比如使用`pt-archiver`做数据归档，或者用`mysqldump`做冷备，确保数据一致性。还要注意分片后的主键生成方式，比如用UUID可能无法保证唯一性，需要使用分布式自增ID。

九 分库分表后的运维挑战
分库分表后，运维难度大大提升。比如数据迁移、备份、恢复、监控都需要重新设计。我见过一个项目在分库分表后，备份策略没有调整，导致整个系统无法恢复，最终只能重新部署。因此，必须在分库分表前就设计好运维方案。监控方面，可以使用Prometheus+Grafana对各个分库分表的性能进行监控，比如QPS、TPS、延迟、CPU使用率等。日志分析也要同步调整，比如使用ELK（Elasticsearch+Logstash+Kibana）统一收集各个节点的日志，便于排查问题。另外，扩容和缩容也是难点，比如新增一个数据库节点，如何将数据重新分布？这时候需要使用ShardingSphere的自动扩容功能，或者写脚本处理数据迁移。如果业务允许，可以采用二进制日志同步的方式，将旧库的数据同步到新库，这种方式虽然稳定，但耗时较长。分库分表后，网络分区和一致性问题也需要特别关注，比如使用Raft或者Paxos做一致性协议，这需要额外的组件支持。

十 分库分表与缓存的结合
分库分表后，缓存可以作为重要补充。比如使用Redis缓存热点数据，如用户信息、商品详情等，这样可以避免频繁查询数据库。缓存的更新策略也很关键，比如用缓存失效+定时更新的方式，或者采用写穿透策略。但缓存不是万能的，比如交易类数据，必须保证一致性，不能随便缓存。我见过有人在分库分表后，把订单状态缓存起来，结果因为缓存过期时间设置错误，导致用户看到错误的订单状态。因此，缓存的策略必须和业务逻辑对齐。另外，分库分表后的缓存需要考虑缓存穿透和雪崩问题，比如使用布隆过滤器防止缓存穿透，或者设置不同的过期时间防止雪崩。在实际操作中，缓存的命中率和更新效率直接影响系统的性能，所以必须认真设计。

十一 分库分表后的分布式事务处理
分布式事务是分库分表后必须面对的问题之一。如果业务需要保证多个分库分表之间的事务一致性，必须引入分布式事务框架。比如使用Seata的AT模式，或者TCC模式，或者Saga模式。我见过一个项目因为没有处理好事务，导致两个分库的数据不一致，最终业务逻辑出现错误。在Seata中，可以通过配置`spring.datasource.primary.name=master`来指定主数据源，然后在服务层用`@GlobalTransactional`注解开启事务。但这种方案对系统性能有一定影响，特别是在高并发场景下，事务参与方多了，性能会下降。如果业务允许，可以考虑用最终一致性，比如用消息队列异步处理事务，这样可以减少分布式事务的开销。但最终一致性可能需要容忍短暂的数据不一致，这需要业务层面的考虑。

十二 分库分表后的查询优化策略
分库分表后，查询效率可能会下降，尤其是在需要跨库查询时。这时候需要优化查询策略，比如避免全表扫描，尽量使用分片键。我见过一个错误的查询是用了`user_name`作为条件，导致无法命中分片键，查询效率极低。正确的做法是使用`user_id`作为条件，因为它是分片键。此外，分库分表后，可以考虑使用多表联合查询，或者使用ES做全文搜索，这样能减少对数据库的直接查询。在ShardingSphere中，可以通过配置`sharding-sphere.sharding.tables.order.query-strategy=sharding`来启用分片查询，这样就能自动将查询路由到正确的分库分表。但要注意，如果查询条件不包含分片键，就会导致全表扫描，这时候需要业务层做优化，比如增加索引、调整查询条件、使用缓存等。

十三 分库分表后的主键设计与处理
主键设计是分库分表中的关键环节，必须确保每个分库分表的主键唯一。如果使用UUID作为主键，可能会出现冲突，因为UUID是全局唯一的，但分库分表后，同一个UUID可能出现在不同库或表中。这就导致查询时无法准确定位。我见过一个项目因为主键冲突，导致数据无法正确写入，最终只能用自定义主键生成器。比如使用Snowflake算法生成自增ID，每个分库的ID范围不同，这样就避免了冲突。此外，主键生成器还要考虑高可用和一致性，比如使用Zookeeper或者Redis做锁，确保生成ID的原子性。如果业务允许，可以使用数据库自增ID，但需要在分库分表时做调整，比如使用`sequence`或`auto_increment`分片，这样生成的ID就能保证唯一性和分片效果。主键设计必须提前规划，否则后期改起来成本很高。

十四 分库分表后的数据分布与负载均衡
数据分布不均是分库分表中最常见的问题之一，尤其是在数据增长不均衡的情况下。比如某个分片的写入量是其他分片的三倍，这样会导致资源浪费和性能瓶颈。我见过一个项目因为数据分布不均，导致某个节点CPU使用率高达90%，而其他节点空闲。解决这个问题的方法是使用一致性哈希算法，或者引入权重调整。比如在ShardingSphere中，可以通过`sharding-sphere.sharding.tables.order.database-strategy.consistenthash.sharding-column=user_id`来设置一致性哈希，这样数据分布会更均匀。但一致性哈希在扩容时需要重新计算分片位置，这会带来额外的开销。如果业务允许，可以采用简单的`user_id % N`分片，但需要定期监控数据分布，及时调整分片数。负载均衡方面，可以使用Nginx或HAProxy做反向代理，将请求分发到不同的数据库节点，避免单点过载。

十五 分库分表后的监控与调优
分库分表后，系统监控变得尤为重要。可以使用Prometheus+Grafana监控各个分库分表的性能指标，比如QPS、TPS、延迟、连接数等。我见过一个项目因为没有监控，导致某个分库负载过高，引发连锁宕机。监控数据可以帮助及时发现瓶颈，比如某个分表的查询耗时突然增加，可能是索引失效或者数据倾斜。此外，调优方面，可以使用`EXPLAIN`分析查询计划，确保查询条件包含分片键。如果发现某个分库的查询效率低下，可以考虑增加索引、调整分片策略、优化SQL等方式。还可以用`pt-query-digest`分析慢查询日志，找出性能问题点。如果分库分表后，系统延迟较高，可以考虑引入连接池、调整线程数、优化缓存策略等。总之，分库分表不是一劳永逸的解决方案，需要持续监控和调优。