《从程序员到架构师——数据持久层》读书笔记

大数据表场景如何优化？

一、基础操作优化

优化表结构

字段类型精简化：确保表中每列的数据类型选择恰当且精简。例如，对于只存储 0 和 1 的状态列，使用布尔类型（如 MySQL 中的 TINYINT (1)）而非更大的整数类型，可减少存储空间。对于日期时间列，若仅需记录日期，使用 DATE 类型而非 DATETIME 类型，能有效降低每行数据的存储开销。
移除冗余列：仔细审查表结构，删除那些不再使用或可通过其他列计算得出的冗余列。比如在一个记录用户购买商品信息的表中，若已经有商品单价和购买数量列，而存在一个冗余的总价列（可通过单价与数量相乘得到），则可以将其移除，这样不仅减少了存储，还能避免在数据更新时可能出现的不一致问题。

业务代码优化

批量操作替代单行操作：在对大数据表进行数据插入、更新或删除时，尽量使用批量操作。例如，在向数据库插入大量用户数据时，使用 INSERT INTO users (name, age, email) VALUES (‘user1’, 25, ‘user1@example.com’), (‘user2’, 30, ‘user2@example.com’)… 这样的批量插入语句，相较于多次执行单条插入语句，能大大减少数据库与应用程序之间的交互次数，提升操作效率。
缓存常用数据：在业务代码层面，对于大数据表中经常被读取且不频繁变动的数据，使用缓存机制。例如，将热门商品的信息缓存到内存中（如使用 Redis），当业务需要获取这些商品信息时，优先从缓存中读取，只有在缓存未命中时才查询数据库，从而减轻数据库的压力。

SQL 语句优化

使用覆盖索引：通过创建包含查询所需所有列的覆盖索引，可使查询直接从索引中获取数据，避免回表操作。例如，若查询语句为 SELECT product_name, price FROM products WHERE category = ‘electronics’，可以创建一个包含 category、product_name 和 price 列的复合索引，这样查询时就无需再从表中读取数据，提升查询速度。
优化子查询：尽量避免使用子查询，尤其是多层嵌套的子查询，因为它们通常会导致性能下降。可以将子查询改写为 JOIN 操作，例如将 SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = ‘Asia’) 改写为 SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.region = ‘Asia’，一般情况下，JOIN 操作的性能优于子查询。

二、冷热分离优化

数据归档

按时间归档：对于包含时间序列数据的大数据表，如日志表或交易记录表，按照时间维度进行数据归档。例如，将一年前的交易记录归档到历史表中，在日常业务查询时，只涉及当前活跃的数据表，减少了数据扫描范围，提升查询性能。可以通过定时任务在数据库中执行归档操作，将符合条件的数据插入到历史表后，再从原表中删除。
按访问频率归档：分析数据的访问频率，将低频访问的数据归档到低成本的存储介质中。例如，对于一些很少被查询的历史订单数据，可以将其归档到磁带库或云存储的低频访问层级中。同时，在应用程序中建立相应的查询逻辑，当需要查询归档数据时，能够从归档存储中获取。

HBase 应用

存储架构适配：HBase 是一个基于 Hadoop 的分布式 NoSQL 数据库，适用于存储海量稀疏数据。对于大数据表中具有高写入吞吐量和随机读写需求的场景，可将相关数据迁移到 HBase 中。例如，在一个物联网项目中，传感器实时产生大量的时间序列数据，将这些数据存储在 HBase 中，利用其分布式架构和按行存储的特性，能够高效地处理海量数据的写入和查询。
与传统数据库结合：将 HBase 与传统关系型数据库（如 MySQL）结合使用。对于实时性要求高、频繁读写的热点数据存储在 HBase 中，而对于需要进行复杂关联查询和事务处理的数据仍保留在传统数据库中。通过这种方式，充分发挥两者的优势，提升整体系统对大数据表的处理能力。

三、查询分离优化

在大数据表处理中，查询分离也是一种有效的优化手段。其核心解决方案是将更新的数据放置在主数据库，而将查询的数据存储在专门针对搜索的存储系统中。

适用场景

数据量巨大：当单个表的行数达到上千万级别，或者在几百万行数据时就已出现查询缓慢的情况，可考虑采用查询分离。例如，在电商的订单记录表，随着业务的拓展，订单数据飞速增长，查询性能逐渐下降，此时查询分离就可能成为优化的方向。
查询响应效率低：由于表数据量庞大，或者关联查询逻辑极为复杂，导致查询操作耗时过长。像一些金融机构的交易流水表，涉及多个维度的复杂关联查询，严重影响查询效率，这就符合使用查询分离的场景。
写操作响应良好：尽管查询速度较慢，但所有写数据请求的响应效率仍在可接受范围内。例如，在社交媒体平台的用户动态表中，虽然查询用户历史动态较慢，但用户发布新动态的写入操作响应迅速，这种情况下可以探索查询分离方案。
数据随时可被修改和查询：这一点区别于冷热分离，如果数据不存在进入终态不再使用的情况，即所有数据在任何时候都可能被修改和查询，那么查询分离方案更为适用。

方案要点

异步同步触发：采用异步方式来触发查询数据的同步。例如，当工单数据发生修改后，系统会异步启动一个线程，将工单数据同步到查询数据库。这样可以避免同步操作对主业务流程的阻塞，提高系统的整体响应性。
消息队列（MQ）应用：借助 MQ 实现异步效果，同时 MQ 还具备两大重要功能。一是服务解耦，将工单主业务系统和查询系统的服务进行解耦，使得两个系统的开发和维护更加独立，降低系统间的耦合度。二是削峰，当大量并发的工单修改请求出现时，MQ 能够控制同步到查询数据库的线程数量，防止因同步请求过多而对查询数据库造成过大压力。
选择合适的查询存储：将工单的查询数据存储在 Elasticsearch 中。Elasticsearch 作为一个分布式索引系统，天生就适用于处理大数据量的复杂查询。它能够快速响应用户的查询请求，提升查询性能。
处理数据同步延时：由于查询数据同步到 Elasticsearch 会存在一定的时间延迟，这可能导致用户查询到旧的工单数据。因此，需要在用户界面给用户提供相应的提示，告知用户可能存在数据延迟的情况，避免用户误解。
历史数据迁移：在进行历史数据迁移时，通过设置一个标识字段（如 NeedUpdateQueryData）来标记工单是否需要同步。只需将所有历史数据的该标识设置为 true，系统就会自动批量地将历史数据同步到 Elasticsearch，实现历史数据的快速迁移。

四、分表分库优化

在面对大数据表时，分表分库是提升系统性能和可扩展性的重要手段。它通过将数据分散存储在多个数据库和表中，降低单个数据库和表的负载，从而提高查询和写入效率。

分表分库模式

Proxy 模式

如图 3 - 2（参考 ShardingSphere 官方文档中的 Proxy 模式图）所示，重点在于中间的 Sharding - Proxy 层。这种设计模式将 SQL 组合、数据库路由、执行结果合并等关键功能都集中在一个代理服务中。而与分表分库相关的处理逻辑则独立于业务服务之外。其最大的优点在于对业务代码无侵入性，业务开发人员只需专注于自身的业务逻辑，无需关心分表分库的复杂操作。例如，一个电商系统在采用 Sharding - Proxy 模式进行分表分库后，商品管理、订单处理等业务模块的代码无需进行大规模改动，就能适应分表分库后的架构。

Client 模式

参照 ShardingSphere 官方文档中的 Client 模式图（图 3 - 3），此模式将分表分库相关逻辑放置在客户端。一般情况下，客户端应用会引入一个特定的 jar 包。在这个 jar 包中，实现了 SQL 组合、数据库路由以及执行结果合并等功能。例如，一个移动应用客户端在使用 Client 模式进行分表分库时，通过集成相关 jar 包，在本地即可完成对数据请求的合理分发，减轻后端数据库的压力。

分片策略

目前，通用的分片策略主要有以下三种：
根据范围分片：按照数据的某个属性范围进行分片。例如，在一个存储用户交易记录的系统中，可以根据交易时间范围进行分片，将不同时间段的交易记录存储在不同的表或库中。如将 2023 年之前的交易记录存放在一组库表中，2023 年之后的存放在另一组。这种方式适用于经常按照时间范围进行查询的场景，如统计某一时间段内的交易总额。
根据 Hash 值分片：通过对数据的某个关键属性（如用户 ID）进行 Hash 计算，然后根据 Hash 值将数据分配到不同的表或库中。例如，对于一个社交平台的用户信息表，根据用户 ID 的 Hash 值进行分片，使得每个分片的数据分布相对均匀。这样在根据用户 ID 查询用户信息时，能够快速定位到相应的分片，提高查询效率。
根据 Hash 值及范围混合分片：结合上述两种方式，在不同的业务场景下灵活运用。例如，在一个电商订单系统中，对于近期（如近一个月）的订单数据，按照时间范围分片，方便对近期订单进行频繁的统计和查询；而对于历史订单数据，则根据订单 ID 的 Hash 值进行分片，以保证数据的均匀分布和高效查询。

注意要点

在微服务架构中，对于特定表的分表分库，其影响范围通常仅局限于该表所在的服务。例如，在一个电商微服务架构中，订单服务中的订单表进行分表分库，只会对订单服务内部的逻辑产生影响，其他如商品服务、用户服务等不受干扰。
然而，在单体架构中进行分表分库则较为复杂。由于单体架构中存在大量的跨表关联查询，例如，很多地方会直接与订单表进行 Join 查询。若要将订单数据拆分到多个库、多个表中，就需要对大量涉及订单表查询的代码进行修改，这无疑增加了改造的难度和风险。
外键约束：在互联网架构中，基本不使用外键约束。这是因为在分表分库的情况下，外键约束可能会导致数据一致性问题，并且会增加数据库的维护成本。例如，在一个分布式电商数据库架构中，如果使用外键约束，当涉及跨库数据操作时，外键的验证和维护会变得非常复杂，甚至可能影响系统的性能和可用性。
查询优化：分库分表以后，与订单等数据相关的读操作必须要考虑数据所在的具体库和表。应尽量避免跨库或跨表查询，因为跨库或跨表查询通常会涉及多个数据源的访问和数据合并，这会降低查询效率。例如，在一个分表分库的电商系统中，查询某个用户的所有订单信息时，应确保能够准确地定位到该用户订单数据所在的库表，避免不必要的跨库或跨表操作。

来源

《从程序员到架构师:大数据量、缓存、高并发、微服务、多团队协同等核心场景实战》