数据库索引
数据库索引使用总结：原理、场景、优缺点，本文主要以 MySQL InnoDB 为例。
1. 什么是索引
索引是数据库为了提高查询速度而维护的一种数据结构
可以把索引理解成一本书的目录。没有目录时，要找到某一章内容，只能从第一页开始一页一页翻；有目录后，可以直接根据目录定位到目标位置
数据库也是一样。如果表中没有索引，查询某个字段时可能需要从第一行扫描到最后一行，这叫全表扫描。数据量少时问题不明显，数据量大后查询会越来越慢
MySQL InnoDB 中常见的普通索引通常采用 B+ 树结构。B+ 树会让索引值保持有序，因此不仅适合等值查询，也适合范围查询、排序和分组等操作。
2. 为什么需要索引
索引的核心作用是提高查询效率
数据库定语索引之后，就可以先快速定位到指定索引对应的数据
3. 不加索引会有什么问题
不加索引时，常见问题是：
查询慢
数据量越大，查询越慢
全表扫描
数据库可能需要扫描整张表才能找到目标数据,导致数据库压力紧张，甚至崩溃
排序慢
如果经常使用 ORDER BY created_at DESC，没有合适索引时数据库还需要额外排序。
分页慢
后台列表、订单记录、验券记录这类数据，一般都需要分页。如果没有索引，分页查询会越来越慢。4. 常见索引种类
普通索引（NORMAL）
- 适合普通查询字段，例如店铺 ID、订单 ID、状态字段
唯一索引（UNIQUE）
- 字段值不能重复，适合身份证号、手机号、订单号、券 ID 这类不能重复的数据
主键索引
- 主键字段自带索引，一张表只能有一个主键，一般用于自增 ID，主键索引通常不需要手动在“索引类型”里选择，而是在表字段里把 id 设置为主键
全文索引（FULLTEXT）
- 全文索引用于文本搜索，适合大段文本内容的关键词检索，适合文章内容、商品描述、日志文本、评论内容等字段，普通的 LIKE '%关键词%' 在大文本场景下效率较低，全文索引可以提升文本检索能力
空间索引（SPATIAL）
- 空间索引用于地理空间数据，适合地图定位、附近门店、地理围栏、路线范围查询等场景
5. 单列索引和联合索引
5.1 单列索引
- 适合主要根据单个字段进行查询的场景：
CREATE INDEX idx_user_id ON orders (user_id);
SELECT *
FROM orders
WHERE user_id = 10001;5.2 联合索引
- 联合索引由多个字段共同组成
CREATE INDEX idx_shop_status_created ON orders (shop_id, status, created_at);
SELECT *
FROM orders
WHERE shop_id = 1001
AND status = 2
ORDER BY created_at DESC;
因为联合索引 (shop_id, status) 本身通常就可以支持只根据 shop_id 查询 （最左匹配原则）5.3覆盖索引
- 如果一条查询需要的字段都能直接从某个索引中取得，数据库就不必再回表读取完整行，这种情况称为覆盖索引。
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);
SELECT status, created_at
FROM orders
WHERE user_id = 10001;
这些字段全部包含在索引中，因此数据库可能直接从索引返回结果，不需要回表。6. 什么是最左前缀原则
- 联合索引在 B+ 树中会按照索引字段从左到右依次排序。因此，查询通常需要从联合索引最左边的字段开始，才能有效利用该索引，这就是最左前缀原则。
CREATE INDEX idx_shop_status_created ON orders (shop_id, status, created_at);
shop_id → status → created_at
真正重要的是查询条件中是否包含联合索引最左侧的字段，而不是 SQL 文本中的书写顺序。
下面这些查询跳过了最左侧的 shop_id，不能充分利用 (shop_id, status, created_at) 联合索引
SELECT *
FROM orders
WHERE status = 2
AND created_at >= '2026-07-01';7. 索引的应用场景
经常作为查询条件的字段
订单号经常用于精确查询，并且通常要求唯一，适合建立唯一索引：
SELECT *
FROM orders
WHERE order_no = '202607080001';
CREATE UNIQUE INDEX uk_order_no
ON orders (order_no);经常用于关联查询的字段
SELECT o.*, u.username
FROM orders AS o
JOIN users AS u ON u.id = o.user_id;
关联字段两侧的数据类型应保持一致。
在这个查询中：
users.id 是主键，自带主键索引。
orders.user_id 可以根据查询情况建立普通索引。
CREATE INDEX idx_user_id
ON orders (user_id);如果关联字段没有索引，大表关联时可能需要扫描大量数据。
经常用于排序的字段
SELECT *
FROM orders
WHERE shop_id = 1001
ORDER BY created_at DESC;
可以考虑使用联合索引：
CREATE INDEX idx_shop_created
ON orders (shop_id, created_at);这样筛选和排序可能同时受益
后台分页列表
后台列表、订单记录、核销记录、日志记录通常都会分页：
SELECT *
FROM orders
WHERE shop_id = 1001
AND status = 2
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
CREATE INDEX idx_shop_status_created
ON orders (shop_id, status, created_at);- 如果是深分页，例如
LIMIT 20 OFFSET 100000，即使有索引也可能慢。这时可以考虑使用上一页最后一条记录的 ID 或时间作为游标条件，减少跳过大量数据的成本。
8. 索引的缺点
索引可以提高查询速度，但不是越多越好。索引本身也有成本。
占用额外存储空间
索引本身需要存储在磁盘中。表数据越大、索引越多、索引字段越长，占用空间就越大。
例如给一个 VARCHAR(255) 字段建立索引，通常比给一个 BIGINT 字段建立索引占用更多空间。
降低写入速度
新增、修改、删除数据时，数据库不仅要维护表数据，还要维护相关索引。
例如：
INSERT INTO orders (...) VALUES (...);
UPDATE orders
SET status = 3
WHERE id = 1;
DELETE FROM orders
WHERE id = 1;这些操作都可能触发索引维护。索引越多，写入成本越高。
增加优化器选择成本
当一张表上索引很多时，数据库优化器需要判断使用哪个索引更合适。索引设计混乱时，可能出现看似有索引、实际执行效果不理想的情况。
低区分度字段单独建索引效果有限
低区分度字段是指字段的可选值很少，例如：
is_deleted: 0 / 1
gender: 男 / 女
status: 0 / 1 / 2这类字段单独建索引不一定有明显效果，因为命中的数据比例可能很高。它们更常见的用法是放到联合索引中，配合其他高频查询字段一起使用。
索引会增加维护成本
业务 SQL 变化后，原来的索引可能不再适合；表数据量变化后，原来不慢的 SQL 也可能变慢。因此索引需要结合真实查询持续观察和调整。
9. 索引失效的常见情况
索引失效不是指索引被删除，而是指 SQL 执行时没有充分利用索引，可能仍然出现大量扫描、额外排序或回表。
对索引字段使用函数
SELECT *
FROM orders
WHERE DATE(created_at) = '2026-07-08';这种写法对 created_at 使用了函数，可能导致索引无法正常用于范围定位。
建议改成范围查询：
SELECT *
FROM orders
WHERE created_at >= '2026-07-08 00:00:00'
AND created_at < '2026-07-09 00:00:00';对索引字段进行计算
SELECT *
FROM products
WHERE price + 10 > 100;建议尽量把计算放到常量侧：
SELECT *
FROM products
WHERE price > 90;使用左模糊 LIKE
SELECT *
FROM products
WHERE name LIKE '%手机';普通 B+ 树索引通常不能很好支持左侧模糊匹配。
相对更容易利用索引的是右模糊：
SELECT *
FROM products
WHERE name LIKE '手机%';如果需要复杂关键词搜索，可以考虑全文索引或专门的搜索服务。
字段类型不一致导致隐式转换
如果字段是字符串类型，但查询时没有加引号，可能导致隐式类型转换，从而影响索引使用。
SELECT *
FROM users
WHERE phone = 13800138000;建议：
SELECT *
FROM users
WHERE phone = '13800138000';联合索引不符合最左前缀原则
例如索引是：
CREATE INDEX idx_shop_status_created
ON orders (shop_id, status, created_at);但查询只写：
SELECT *
FROM orders
WHERE status = 2;这种查询跳过了最左侧字段 shop_id，不能充分利用这个联合索引。
OR 条件使用不当
SELECT *
FROM orders
WHERE user_id = 10001
OR remark LIKE '%退款%';如果 OR 两边有一边无法使用索引，整个查询可能变慢。复杂条件可以考虑拆分 SQL、调整索引或优化业务逻辑。
使用不等于条件
SELECT *
FROM orders
WHERE status <> 2;<>、!=、NOT IN 这类条件通常选择范围较大，可能导致数据库扫描大量数据。是否能利用索引，要结合数据分布和 EXPLAIN 判断。
查询返回数据比例太高
即使字段有索引，如果数据库判断使用索引还不如直接全表扫描，也可能不使用索引。
例如一张表 90% 的数据 status = 1：
SELECT *
FROM orders
WHERE status = 1;这种情况下，单独给 status 建索引的效果可能不明显。
10. 使用 EXPLAIN 查看索引是否生效
EXPLAIN可以查看 SQL 的执行计划，用来判断数据库是否使用了索引。
回表
- 当查询使用普通索引找到主键后，如果还需要获取普通索引中没有保存的字段，就必须再根据主键到主键索引中查找完整数据。这个过程叫作回表。
简单来说，索引优化的核心不是“给字段加索引”，而是“根据真实查询 SQL 设计合适的索引”。
版权所有
版权归属：念宇