索引优化、慢查询排查、分库分表基础

当业务数据量从几万行成长到几百万甚至上亿行，再配合复杂查询条件，数据库性能问题往往最先暴露出来。这一节我们围绕 MySQL（思路同样适用于 PostgreSQL 等关系型数据库）来讨论性能优化的三个核心方面：索引如何设计才能高效、如何发现并分析慢查询、数据量超出单库容量后的拆分策略。无论是直接写 SQL 还是通过 Sequelize、TypeORM、Prisma 等 ORM 操作，这些能力都是一个可靠的 Node.js 后端开发者必须掌握的。

索引优化：让查询从全表扫描变成精确查找

数据库默认查找数据的方式是全表扫描：一行行地把数据读出来，判断是否满足 WHERE 条件。当表只有几千行时，扫描几乎感觉不到延迟，但在百万行级别时，全表扫描就意味着几百万次的磁盘读取，请求耗时可能从毫秒级飙升到秒级。

索引的作用就是为某一列或多列建立一种快速查找的数据结构（通常是 B+Tree），数据库可以利用索引在 log 级别的时间内找到目标行，而无需遍历整张表。

1. 什么情况下应该创建索引

在下列场景下，为字段添加索引通常能带来明显的性能提升：

频繁出现在 WHERE 子句中的字段，例如 WHERE user_id = 100；
用作表关联的字段（JOIN 的列）；
ORDER BY 和 GROUP BY 所涉及的列；
用于范围查询的列，比如 WHERE created_at BETWEEN ...；
具有高选择性的列（选择性 = 不重复的行数 / 总行数），选择性越接近 1，索引效果越好。例如用户的身份证号、订单号就比性别字段更适合建索引。

2. 常见索引类型与选择

| 索引类型 | 特点 | 适用场景 |
| ------------ | ---------------------------------------------- | -------------------------------- |
| 普通索引 | 纯粹的 B+Tree 索引，加速查询 | 大多数查询优化 |
| 唯一索引 | 保证列值唯一，同时具备查询加速 | 邮箱、用户名等唯一字段 |
| 复合索引 | 多列联合索引，遵循最左前缀原则 | 多条件查询、排序、覆盖索引 |
| 全文索引 | 用于文本内容的全文搜索 | 文章内容搜索 |
| 空间索引 | 地理空间数据（很少在常规业务中使用） | GIS 应用 |

在实际 Web 应用中，复合索引 的使用频率最高，也是最容易用错的地方。例如有一个查询：

SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' ORDER BY created_at DESC;

最好的索引设计是将这三个字段建为一个复合索引 (user_id, status, created_at)。MySQL 可以利用索引过滤前两列，同时 created_at 已经排序，避免了额外的 filesort 操作。

3. 最左前缀原则与索引失效

复合索引的列顺序至关重要。MySQL 会按照索引的定义顺序从左到右匹配查询条件，一旦遇到范围查询（>, <, BETWEEN），后面的列就无法继续使用索引排序，但仍可能用于过滤。

索引失效的常见场景（务必避免）：

在索引列上使用函数或表达式，如 WHERE YEAR(created_at) = 2024；
模糊查询以 % 开头，如 WHERE name LIKE '%张三'；
WHERE 条件中隐式类型转换，例如索引列为字符串却传入数字比较；
OR 连接的条件中，如果一部分列没有索引；
不符合最左前缀，即跳过索引最左边列直接使用后面的列。

在 ORM 框架中，我们需要清楚生成的 SQL 是否用到了索引。例如使用 Sequelize 的 findAll 时，可以开启日志查看生成的 SQL：

const users = await User.findAll({
  where: {
    email: 'test@example.com',
  },
  logging: console.log,
});

如果查询时间突然变长，就需要检查是否有索引可用。

4. 索引维护的代价

索引不是免费的，它会降低写操作（INSERT、UPDATE、DELETE）的速度，因为数据库在更新数据的同时还要维护索引结构。同时，索引本身会占用磁盘空间。因此，应该只为真正需要优化的查询创建索引，避免建立冗余索引。可以定期使用 MySQL 的 SHOW INDEX FROM table_name 查看已有索引，并用 pt-duplicate-key-checker 等工具检测重复索引。

慢查询排查：从发现到分析的全流程

即使索引设计得不错，随着业务迭代，某些未预料到的慢查询仍可能出现。我们需要一套流程来快速定位和解决。

1. 开启慢查询日志

MySQL 提供了慢查询日志，可以将执行时间超过指定阈值的 SQL 记录下来。在开发环境或低流量线上环境可以开启（高流量环境建议抽样或使用性能模式视图）。

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.1;  -- 超过 100ms 即记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

在 Node.js 应用中，也可以通过 ORM 的查询日志功能进行应用层面的慢查询监控。例如 Sequelize 可以配置基准时间：

const sequelize = new Sequelize(/* ... */, {
  benchmark: true,
  logging: (sql, timing) => {
    if (timing > 100) { // 超过 100ms 视为慢查询
      console.warn(`Slow query (${timing}ms): ${sql}`);
    }
  }
});

Prisma 可以在开发时使用 log 选项记录查询及耗时：

const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
  ],
});

prisma.$on('query', (e) => {
  if (e.duration > 100) {
    console.warn(`Slow query: ${e.query}, params: ${e.params}, duration: ${e.duration}ms`);
  }
});

2. 使用 EXPLAIN 分析执行计划

拿到一条慢查询 SQL 后，最有效的手段是使用 EXPLAIN 查看其执行计划。

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

输出字段中需要重点关注：

type：连接类型，从优到劣依次为 const、eq_ref、ref、range、index、ALL。All 表示全表扫描，必须优化。
key：实际使用的索引名，如果为 NULL 表示没有用到索引。
rows：MySQL 估算的需要扫描的行数，显然越小越好。
Extra：额外信息，Using filesort 表示需要额外的排序操作，通常通过加索引消除；Using temporary 表示需要临时表，也要尽量避免。
key_len：使用的索引长度，可以推断出复合索引中有几列被实际使用。

如果发现查询没有使用合适的索引，解决方案可能是：

调整 WHERE 条件的写法以避免索引失效；
新建或修改索引；
改写 SQL，比如将 OR 改写成 UNION ALL；
在某些情况下，MySQL 错误估计数据分布而没有使用索引，可以用 FORCE INDEX 提示。

3. 实用排查案例

假设有这样一个查询：

SELECT * FROM articles WHERE status = 'published' ORDER BY created_at DESC LIMIT 10;

通过 EXPLAIN 发现 type 为 ALL，rows 高达 50 万。分析后为 (status, created_at) 建立复合索引，再次 EXPLAIN 显示 type 变为 ref，rows 降到 200，查询时间从 2 秒降至 10 毫秒。

分库分表基础：当单表千万级时如何应对

即使索引优化到了极致，当数据量超过单台数据库服务器的承载能力（通常建议单表行数控制在千万以内，实际情况取决于硬件和查询模式），就必须考虑将数据拆分到多个库或多张表中。

1. 垂直拆分 vs 水平拆分

垂直拆分：将一个大表按照列拆成多个表，例如将用户表拆分为用户基本信息表和用户扩展信息表。通常是根据字段的访问频率来拆分，热点字段放在一起。垂直拆分在业务设计中比较常见，但并未减少单表的数据行数。

水平拆分：将同一个表的数据按照某种规则分散到多个结构相同的表中，这些表可以分布在同一个数据库实例的不同数据库，或者不同的物理服务器上。这是解决大表容量的主要手段。

2. 水平拆分的常见方式

范围分片：比如按照时间范围，每个月一张表 order_202401、order_202402。实现简单，但可能造成热点（最新月份访问最高）。
哈希分片：对某个字段（如用户ID）取模，将数据均匀分散到 N 个表或库中，如 user_0、user_1...user_7。数据分布均匀，但跨分片查询比较困难。
目录表/路由表：维护一张映射表记录每个用户所在的分片，查询时先查路由表获取目标分片。灵活，但增加了额外的查询和依赖。

在实际的 Node.js 应用中，数据库中间件（如 MyCat、ShardingSphere-Proxy）可以屏蔽大部分分片逻辑，应用层无需感知分片细节。但若希望更轻量地控制，可以在应用层实现简单的分片路由。

例如，使用 Sequelize 时可以根据分片键动态切换所使用的数据库连接：

const shardId = userId % 4;
const sequelize = getShardConnection(shardId);
const user = await sequelize.models.User.findByPk(userId);

这种做法要求分片键必须出现在绝大部分查询的 WHERE 条件中（因为无法跨分片进行关联查询或全局排序），因此在设计分片策略前，一定要梳理清楚核心查询的高频模式。

3. 分库分表带来的挑战与应对思路

拆分数据并非没有代价，它会引入几个棘手的问题：

跨分片查询：如果业务需要查询“所有用户中注册时间最早的前100位”，就需要查询所有分片然后合并结果，性能会大幅下降。通常需要设计避免此类全局查询，或者借助搜索引擎（Elasticsearch）或汇总表来满足。
分布式事务：下单操作可能涉及订单表（分片）和库存表（分片），跨分片的事务一致性难以保证。一般需要引入分布式事务组件（如 Seata）或使用最终一致性的方案（如事务消息、补偿机制），但这在 Node.js 生态中成熟度仍较低。理想情况下，应尽量让一次事务的所有数据落在同一个分片（如按订单ID分片包含所有相关表）。
分片扩容：数据量增长后增加分片数量（如从4个扩到8个）需要重新分配数据，往往涉及数据迁移和路由规则的变更，必须设计平滑的迁移方案。

对于大多数中小型项目来说，先通过索引优化、读写分离、缓存等方案应对性能问题，在单表接近瓶颈时再考虑分库分表是明智的策略。若数据规模从一开始就可能在短期内达到分片级别，建议在架构设计时就预留分片方案，至少保证分片键可以作为绝大部分查询的条件。

总结：从单库优化到分布式架构的进阶路径

对于数据库性能，我们建议遵循 “先优化，再拆分” 的渐进式原则：

第一步：确保所有查询都有合适的索引，消除慢查询；
第二步：引入 Redis 等缓存来减少数据库读压力；
第三步：实施读写分离，主写从读，提升并发能力；
第四步：当数据量达到单机瓶颈时，考虑分库分表或数据归档。

在 Node.js 应用中，我们可以通过 ORM 的日志功能监控查询性能，利用 EXPLAIN 精确调优，并将这些数据库优化知识转化为可靠的服务性能。掌握这三项技能，便可以应对从初创产品到大规模分布式应用逐步演进过程中的主要数据层挑战。