1. 从一次慢查询说起为什么我们需要索引那天下午我正喝着咖啡突然收到一条告警说某个核心报表接口的响应时间从平时的200毫秒飙升到了15秒。用户那边已经炸锅了。我立刻登录服务器抓取了那条正在执行的SQL。它看起来平平无奇就是一个多表关联查询带几个WHERE条件。但EXPLAIN命令的结果让我倒吸一口凉气全表扫描type: ALL。这张表有将近两千万条数据数据库引擎正像一只没头苍蝇逐行检查每一笔记录试图找到符合条件的那几百条。那一刻我脑子里只有一个念头索引又是索引的问题。这大概是我们每个和数据库打交道的人都会经历的“至暗时刻”。你可能听过无数次“索引是数据库的‘目录’”但只有当你真正被慢查询折磨过才会深刻理解这个“目录”的价值。它不仅仅是让查询变快更是决定了你的应用在高并发、大数据量下是平稳运行还是直接崩溃。今天我们不谈那些教科书上的定义就从实战出发掰开揉碎了聊聊数据库索引。我会结合我这些年踩过的坑、调优过的案例把索引的原理、设计、使用和那些“坑爹”的失效场景一次给你讲透。无论你是刚入门的新手还是有一定经验的开发者相信都能从中找到对你有用的东西。2. 索引的本质它到底是如何工作的很多人把索引想象成书的目录这个类比很形象但不够深入。我更愿意把它比作一本电话簿。假设你有一本按姓名拼音排序的电话簿这就是一个索引你想找“张三”的电话。你绝不会从第一页开始一页一页翻而是直接根据“Zhang”跳到“Z”开头的部分然后快速定位到“张三”。数据库的B树索引干的就是这个事。2.1 B树索引的骨架与灵魂目前绝大多数关系型数据库MySQL的InnoDB、PostgreSQL等的默认索引结构都是B树。为什么是它而不是二叉搜索树或者哈希表想象一下二叉搜索树。在理想情况下它的查找效率是O(log N)看起来不错。但数据库的数据是存储在磁盘上的磁盘IO读取数据的速度比内存慢好几个数量级。二叉搜索树在极端情况下比如插入有序数据会退化成链表深度变得非常大这意味着一次查询可能需要进行数十次甚至上百次磁盘IO这是无法接受的。B树就是为了减少磁盘IO而生的。它是一种多路平衡搜索树。它的关键特性是矮胖一个节点可以存放很多个键值比如几百个这使得树的“高度”非常低。通常对于千万级别的表B树的高度也就在3到4层。查找任何一条记录最多只需要3-4次磁盘IO。有序所有数据都存储在叶子节点并且叶子节点之间通过指针双向链接形成了一个有序链表。这对于范围查询WHERE age BETWEEN 20 AND 30和排序操作是巨大的优势因为引擎只需要找到范围的起点然后顺着链表遍历即可。数据聚集在InnoDB中主键索引聚簇索引的叶子节点直接存储了完整的行数据。而非主键索引二级索引的叶子节点存储的是主键值。这意味着通过二级索引查找到数据后如果需要获取其他列的数据还需要根据主键值回到主键索引中再查一次这个过程叫做“回表”。这里有个关键点索引是一种“空间换时间”的典型策略。它需要额外的磁盘空间来存储索引数据结构B树并且会在数据插入、更新、删除时维护这个结构带来额外的开销。但这一切都是为了换取查询时几个数量级的性能提升。2.2 索引的类型与适用场景理解了B树我们来看看常见的索引类型主键索引PRIMARY KEY特殊的唯一索引不允许为空。一张表只能有一个。在InnoDB中它就是聚簇索引决定了表中数据的物理存储顺序。所以主键的选择至关重要通常建议使用自增整型INT/BIGINT这样插入数据时永远是追加能避免页分裂带来的性能损耗。唯一索引UNIQUE KEY保证索引列的值唯一但允许有空值但只能有一个空值具体看数据库实现。常用于业务上需要唯一约束的字段如身份证号、邮箱等。普通索引KEY/INDEX最基本的索引没有任何唯一性约束。是我们最常创建来加速查询的索引。复合索引联合索引这是实战中的重中之重。它是由多个列组合起来构建的一个索引。比如INDEX idx_name_age (name, age)。最左前缀匹配原则这是复合索引的黄金法则。查询条件必须从索引的最左列开始并且不能跳过中间的列才能用到这个索引。能用上索引WHERE name ‘张三’WHERE name ‘张三’ AND age 25不能用上索引或只能部分使用WHERE age 25跳过了最左的name列WHERE name ‘张三’ AND score 90跳过了中间的age列虽然name能用但索引效果打折扣。索引下推ICP这是MySQL 5.6引入的优化。对于WHERE name ‘张三’ AND age 20这样的查询在旧版本中即使有(name, age)索引服务器层也需要把所有name’张三’的记录都捞出来再在服务器层过滤age20。有了ICP存储引擎层在索引中就会进行age20的过滤只返回符合条件的记录大大减少了回表次数和传输的数据量。全文索引FULLTEXT用于大文本字段的模糊匹配LIKE ‘%关键词%’效率极低它通过分词等技术实现高效的文本搜索。在MySQL中从5.6开始InnoDB也支持了全文索引。空间索引SPATIAL用于地理空间数据类型。注意网上常说的“聚簇索引”和“非聚簇索引”是从索引组织数据的方式角度分类的。InnoDB的主键索引是聚簇索引其他都是非聚簇索引二级索引。而像SQL Server你可以指定任意索引为聚簇索引但一张表也只能有一个。3. 如何设计一个好的索引从原则到实战知道了索引是什么接下来就是怎么用好它。设计索引不是拍脑袋需要遵循一些核心原则并结合业务查询模式。3.1 索引设计核心原则考虑列的区分度Cardinality索引列不同值的数量占总行数的比例。区分度越高索引过滤掉的数据就越多效果越好。比如“性别”列只有‘男’、‘女’两个值区分度极低建索引几乎没用除非和别的列组成复合索引且查询条件能固定性别。而“用户ID”、“订单号”这类列区分度极高是理想的索引候选。如何评估在MySQL中SHOW INDEX FROM table_name;命令结果中的Cardinality列就是一个估算值。你也可以用SELECT COUNT(DISTINCT column)/COUNT(*) FROM table;来估算区分度。考虑查询频率为WHERE子句、JOIN连接条件、ORDER BY和GROUP BY子句中频繁出现的列创建索引。数据库监控慢查询日志是发现这些候选列的最佳途径。利用复合索引覆盖查询这是高级技巧。如果一个查询所需要的所有列都包含在某个复合索引中那么存储引擎只需扫描索引就能返回结果无需回表。这被称为“覆盖索引”Covering Index是性能最优的查询之一。例如表有id, name, age, city列有索引idx_name_age_city (name, age, city)。查询SELECT name, age FROM users WHERE name ‘Alice’;因为name和age都在索引中所以可以直接从索引中取数据无需回表。短小精悍原则索引键的长度越短越好。因为索引本身也占空间键值越短单个索引页能存放的键数量就越多B树的高度就越低IO次数就越少。这也是为什么推荐用整型做主键而不是很长的字符串。3.2 实战案例电商订单表索引设计假设我们有一张电商订单表orders主要字段和常见查询如下CREATE TABLE orders ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 订单ID主键 user_id BIGINT NOT NULL, -- 用户ID status TINYINT NOT NULL, -- 订单状态 (1待支付2已支付3已发货...) amount DECIMAL(10,2) NOT NULL, -- 订单金额 create_time DATETIME NOT NULL, -- 创建时间 pay_time DATETIME, -- 支付时间 INDEX idx_user_id (user_id), INDEX idx_create_time (create_time) );常见查询用户查看自己的订单列表SELECT * FROM orders WHERE user_id ? ORDER BY create_time DESC LIMIT 20;后台按时间范围搜索订单SELECT * FROM orders WHERE create_time BETWEEN ? AND ? AND status ?;统计某个用户的消费总额SELECT SUM(amount) FROM orders WHERE user_id ? AND status 2;分析现有索引问题idx_user_id对查询1有帮助但排序ORDER BY create_time需要额外的文件排序filesort因为索引只按user_id排序。idx_create_time对查询2有帮助但status条件在索引中无法有效过滤需要回表后过滤。查询3用到了user_id和status但现有索引不包含amount列需要回表后求和。优化方案针对查询1将idx_user_id改为复合索引idx_user_id_create_time (user_id, create_time DESC)。这样对于特定用户的查询数据在索引中已经是按创建时间倒序排好的数据库可以直接按顺序读取避免排序并且可以利用索引进行分页LIMIT优化。针对查询2创建复合索引idx_status_create_time (status, create_time)。根据最左前缀原则WHERE status ? AND create_time BETWEEN ? AND ?可以高效利用这个索引。由于status的区分度可能不高状态种类少放在前面可以利用索引快速定位到某个状态的数据块再在这个块里按时间范围快速筛选。针对查询3创建覆盖索引idx_user_id_status_amount (user_id, status, amount)。这个索引包含了查询所需的所有列user_id,status,amount数据库引擎只需要扫描这个索引就可以完成WHERE过滤和SUM聚合计算完全不需要回表效率极高。设计心得索引设计是一个动态权衡的过程。新增索引会加快查询但会降低写性能插入/更新/删除时需要维护更多索引树并占用更多空间。你需要根据业务的读写比例、数据量、核心查询路径来做出决策。通常优先保证核心交易链路查询的索引覆盖。4. 索引失效的八大“陷阱”与排查这是最让人头疼的部分。明明建了索引EXPLAIN一看type还是ALL全表扫描。我总结了几种最常见的索引失效场景你肯定遇到过。4.1 函数操作与隐式类型转换陷阱在索引列上使用函数或进行运算。-- 失效对create_time列使用了DATE函数 SELECT * FROM orders WHERE DATE(create_time) ‘2023-10-01’; -- 正确使用范围查询 SELECT * FROM orders WHERE create_time ‘2023-10-01 00:00:00’ AND create_time ‘2023-10-02 00:00:00’; -- 失效对索引列进行运算 SELECT * FROM products WHERE price * 0.8 100; -- 正确将运算移到等号另一边 SELECT * FROM products WHERE price 100 / 0.8;原因B树中存储的是列的原始值。当你使用DATE(create_time)时数据库无法从索引的原始值中直接计算出函数结果只能对每一行数据都计算一次函数然后比较导致索引失效。隐式类型转换也类似比如索引列是字符串类型varchar你用了WHERE id 123数字数据库会默默地将列值转换为数字再比较同样导致索引失效。4.2 前导通配符 LIKE ‘%xxx’陷阱使用以通配符开头的LIKE查询。-- 失效无法利用索引的有序性 SELECT * FROM users WHERE name LIKE ‘%三’; -- 可能有效如果name是索引至少可以用到索引的前半部分定位 SELECT * FROM users WHERE name LIKE ‘张%’;原因B树索引是按照列值排序的。‘张%’意味着“以‘张’开头”索引可以快速定位到所有‘张’开头的条目。而‘%三’意味着“以‘三’结尾”索引的有序性在这里毫无用处引擎不知道哪些值以‘三’结尾只能全表扫描。对于这种需求可以考虑使用全文索引或者将数据冗余一份并反转如存储reverse_name并建索引。4.3 OR 连接非索引列陷阱使用OR连接条件且OR前后的列并非都有索引。-- 假设user_id有索引status没有索引 SELECT * FROM orders WHERE user_id 100 OR status 2;原因对于user_id 100可以用索引对于status 2需要全表扫描。数据库优化器发现需要将两个结果集合并它可能认为全表扫描一遍同时检查两个条件比走一次索引再全表扫描合并结果更快于是选择了全表扫描。解决方案是为status也建立索引或者重写查询有时可以用UNION替代OR。4.4 不符合最左前缀原则这是复合索引最经典的坑前面提过再强调一次。-- 索引是 (a, b, c) WHERE b 1 AND c 2; -- 失效跳过了a WHERE a 1 AND c 2; -- a能用索引但c不能用于索引过滤在ICP开启下c可以用于索引内过滤但索引效果非最优 WHERE a 1 AND b 10 AND c 2; -- a和b范围查询能用索引b之后c的等值查询在索引中失效ICP下c可用于过滤4.5 索引列参与范围查询 BETWEEN后的列失效陷阱在复合索引中如果某一列使用了范围查询那么它后面的索引列将无法被用于索引过滤但ICP可以优化部分场景。-- 索引 (create_time, status) WHERE create_time ‘2023-01-01’ AND status 1;在这个查询中create_time使用了范围查询那么索引中排在它后面的status就无法再被用于在索引结构中快速定位了。因为create_time ‘2023-01-01’对应的索引条目中status的值是乱序的。优化器可能只会用索引的create_time部分然后回表过滤status。如果status过滤性很强可以考虑建立(status, create_time)索引。4.6 使用不等于! 或 或 NOT IN陷阱对索引列使用!或NOT IN。SELECT * FROM users WHERE status ! 1;原因不等于操作无法有效利用索引的有序性。它需要排除掉所有等于1的行本质上还是需要检查几乎所有行。如果status只有少数几个值且不等于某个值的行数很少有些数据库优化器可能会选择走索引。但多数情况下它会选择全表扫描。对于这种查询考虑能否用status IN (2,3,4)来改写。4.7 索引列上有 IS NULL 或 IS NOT NULL陷阱在可为空的列上使用IS NULL或IS NOT NULL判断。SELECT * FROM users WHERE phone IS NULL;原因这取决于数据库的优化器实现和数据的分布。如果表中绝大多数行的phone都不为空那么查询phone IS NULL可能会走索引因为结果集小。反之查询phone IS NOT NULL可能就会全表扫描。同样如果phone列建立了索引但允许为NULL索引中会包含NULL值但优化器需要根据成本来决定是否使用索引。4.8 数据量太少或索引区分度极低陷阱表里就几百条数据或者索引列如“性别”只有两三个值。原因数据库优化器非常“聪明”它会计算各种执行计划的成本。如果它发现通过索引查完还要回表这个成本可能比直接全表扫描把所有数据页读入内存还要高它就会选择全表扫描。对于小表全表扫描往往是最快的。排查工具EXPLAIN是你的最佳伙伴。重点关注type列访问类型从好到坏system const eq_ref ref range index ALL、key列实际使用的索引、rows列预估扫描行数、Extra列额外信息如Using where、Using index、Using filesort、Using temporary等。5. 高级话题与生产环境维护当你掌握了基础一些更深入的问题和日常维护工作就浮出水面了。5.1 索引的代价不只是空间创建索引不是免费的午餐。写代价每次INSERT、UPDATE、DELETE操作都需要更新所有相关的索引B树。这意味着写操作会变慢并且可能引发页分裂、页合并等操作影响性能。在高并发写入的场景下需要谨慎评估索引数量。空间代价索引需要占用额外的磁盘空间。一个大表的多个复合索引其大小可能接近甚至超过数据本身。选择代价优化器在选择执行计划时如果有多个索引可用它需要花费时间选择“最优”的一个。索引太多可能会增加优化器选择错误计划的风险。5.2 前缀索引与索引选择性对于很长的字符串列如VARCHAR(255)为整个列建索引会非常庞大。这时可以使用前缀索引只对列的前N个字符建立索引。ALTER TABLE users ADD INDEX idx_email_prefix (email(10));关键是如何选择前缀长度N目标是保证足够的选择性区分度。可以通过计算不同前缀长度的选择性来决定SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel20, COUNT(DISTINCT email) / COUNT(*) AS full_sel FROM users;选择那个选择性接近full_sel但长度更短的前缀。缺点是前缀索引无法用于ORDER BY和GROUP BY操作也无法用作覆盖索引。5.3 不可见索引与索引调优从MySQL 8.0开始支持不可见索引INVISIBLE。你可以将一个索引设置为不可见优化器在生成执行计划时会忽略它但索引本身依然被维护。这有什么用安全删除索引当你怀疑某个索引没用想删除时可以先设为不可见观察一段时间业务是否有性能回退。如果没有再真正删除。A/B测试对比某个查询在有索引和无索引情况下的性能。ALTER TABLE orders ALTER INDEX idx_test INVISIBLE; -- 隐藏索引 ALTER TABLE orders ALTER INDEX idx_test VISIBLE; -- 恢复可见5.4 定期维护Analyze Table 与索引碎片整理索引随着数据的增删改会产生碎片比如页分裂后留下的空位。碎片化的索引会降低查询效率因为磁盘上存储不连续需要更多的IO。ANALYZE TABLE更新表的索引统计信息。优化器依赖这些统计信息如每个索引的区分度来选择执行计划。当数据发生大量变化后统计信息可能过时导致优化器选择错误的索引。定期或在重大数据变更后执行ANALYZE TABLE table_name;是很好的习惯。重建索引对于InnoDB表可以通过ALTER TABLE table_name ENGINEInnoDB;来重建表从而整理碎片。或者使用OPTIMIZE TABLE table_name;对于InnoDB它相当于ALTER TABLE ... FORCE。注意这些操作都是DDL会锁表需要在业务低峰期进行。索引是数据库性能的基石但也是一把双刃剑。它需要精心设计、持续观察和适时调整。没有一劳永逸的索引方案随着业务发展和数据增长昨天的银弹可能成为今天的瓶颈。我的习惯是将核心业务的慢查询监控常态化定期审查执行计划像呵护应用代码一样去维护数据库的索引结构。毕竟在深夜里被一个索引失效的慢查询告警叫醒滋味可不好受。
