数据库索引怎么用才快？亿级数据实测指南

该用户已不存在
0 阅读5分钟

很多人在本地开发时可能都会遇到这样的情况。数据少的时候，页面秒开，SQL 怎么写都感觉不到卡顿。可一上线，面对百万级流量，查询直接超时，数据库 CPU 飙升。

要避免这种开发时候猛如虎，上线操作二百五的尴尬，最好的办法就是在本地造点数据出来测。只有数据量上去了，那些平时隐藏的性能坑才会原形毕露。

先造它一亿条数据

如果表里只有几千行数据，全表扫描和走索引几乎没区别，甚至全表扫描更快。要验证索引策略，必须上强度。

别傻乎乎地写脚本在应用层循环插入，网络开销会慢到怀疑人生。PostgreSQL 自带的 generate_series 是个神器，下面这个函数能在几分钟内帮你造出一亿条模拟的用户操作日志，足够把坑找出来。

-- 创建一个能快速生成大量模拟数据的函数
CREATE OR REPLACE FUNCTION populate_large_table(target_rows BIGINT)
RETURNS VOID AS $$
BEGIN
  -- 批量插入，避免逐行提交的开销
  INSERT INTO user_events (user_id, event_type, created_at)
  SELECT
    (random() * 1000000)::INTEGER, -- 模拟 100万个不同的用户
    CASE (random() * 3)::INTEGER    -- 随机生成操作类型
      WHEN 0 THEN 'login'
      WHEN 1 THEN 'logout'
      WHEN 2 THEN 'purchase'
      ELSE 'view'
    END,
    NOW() - (random() * INTERVAL '365 days') -- 分布在过去一年
  FROM generate_series(1, target_rows);
END;
$$ LANGUAGE plpgsql;

-- 执行生成（注意：这会占用不少磁盘空间，执行时间取决于机器性能）
-- SELECT populate_large_table(100000000);

当这一亿条数据落盘后，随便跑一个 SELECT * FROM user_events WHERE user_id = 12345，就会发现耗时从毫秒级变成了几秒甚至十几秒。这时候，索引的价值就体现出来了。

索引不是越多越好

新手最容易犯的错就是给每个字段都加索引。要知道，索引本质上是空间换时间，而且是有代价的。

读取变快，写入变慢

每次 INSERT、UPDATE 或 DELETE，数据库不仅要改数据文件，还得同步更新相关的索引树。如果你表上有 5 个索引，插入一行数据就得维护 5 棵树。对于日志、IoT 传感器上报这类写多读少的业务，索引加多了简直灾难

策略建议：

  • 高频查询列：加索引（如外键、时间戳）。

  • 高频更新列：慎加索引。

  • 低区分度列：别加索引。比如“性别”或“状态（0/1）”，数据库扫索引发现要回表一半的数据，通常会直接放弃索引走全表扫描，这索引建了也是白建。

拒绝盲猜，看执行计划

别觉得写了 WHERE user_id = ... 数据库就一定走索引。优化器有时候比我们想象的懒。

一定要用 EXPLAIN（PostgreSQL/MySQL 通用）来看看数据库到底在干什么：

EXPLAIN ANALYZE SELECT * FROM user_events WHERE user_id = 42;

  • 如果看到 Index Scan：恭喜，索引生效了。

  • 如果看到 Seq Scan（Sequential Scan）：说明在全表扫描。这时候就要检查是不是数据分布不均，或者查询条件没对上索引。

几种常用的索引避坑姿势

1. 复合索引：顺序是关键

当查询条件包含多个字段时，比如要查“某用户在某天的记录”，单列索引往往不够快。这时候要建复合索引：

CREATE INDEX idx_user_date ON user_events(user_id, created_at);

注意顺序（最左前缀原则） ：

这个索引对 WHERE user_id = ? 有效，对 WHERE user_id = ? AND created_at = ? 也有效。

但如果查询只有 WHERE created_at = ?，这个索引就废了。把最常用的筛选列放在最左边。

2. 唯一索引：既是约束也是加速

如果业务逻辑要求邮箱或手机号不能重复，直接上唯一索引。它不仅能提升查询速度，还能在数据库层面兜底，防止代码逻辑漏洞导致脏数据写入。

CREATE UNIQUE INDEX idx_unique_email ON users(email);

3. 针对性索引类型

  • 全文索引 (Full-Text) ：不要用 LIKE '%关键词%' 去搜大段文本，慢得要死。MySQL 和 PG 都有专门的全文索引，支持分词。

  • GIN 索引：PG 特有，专门处理 JSONB 或数组数据。

  • 位图索引 (Bitmap) ：适合数据仓库场景下，针对“状态”、“标签”这类低基数字段的组合查询（PG 默认常用 B-Tree，特定场景可用 BRIN 或扩展）。

对于新手来说，安装数据库需要很繁琐的步骤。有了 ServBay 就不同了。ServBay能一键安装数据库。而且它支持多实例并发运行。就是说你可以同时启动 MySQL 8.0 和 MariaDB，或者同时跑一个 PostgreSQL 12 和 16。

这就很方便做数据迁移测试或者性能对比，看看同一条复杂 SQL 在不同版本数据库下的表现差异。

  • 一键部署：囊括了 MySQL, PostgreSQL, MongoDB, Redis, MariaDB 等主流数据库，不用到处找安装包或配置 Brew。

  • 开箱即用：安装完自动配好环境变量，直接在终端敲 psql 或 mysql 就能连，省去了改配置文件的麻烦。

  • 不污染系统：所有服务独立运行，不想用的时候一键停止或卸载，不会在系统里留下垃圾文件。

其实数据库优化从来就没有什么标准答案，只有取舍。

是牺牲写入速度换读取速度？还是牺牲磁盘空间换查询时间？这些都得看具体的业务场景，甚至要看你能不能接受数据会有几秒钟的延迟。

总之，实践是检验真理的唯一标准。自己试试就知道了。

avatar
文章
阅读
粉丝