当前位置： 首页 > news >正文

数据库-索引

news 2025/7/30 11:55:32

数据库索引是数据库系统中至关重要的性能优化手段，但很多开发者对它的理解仅停留在表面。本文将全面剖析索引的工作原理、常见类型、设计策略以及实战优化技巧，帮助您彻底掌握这一核心技术，提升数据库查询性能。

一、索引的本质：

索引的本质是一种特殊的数据结构，它就像书籍的目录一样，帮助数据库系统快速定位到数据所在位置，而不必扫描整张表。

1.1 没有索引时会发生什么？

当执行SELECT * FROM users WHERE username = '张三'这样的查询时：

  • 数据库必须执行全表扫描(Full Table Scan)

  • 逐行检查每一行数据是否满足条件

  • 对于100万行的表，可能需要读取100万条记录

-- 示例：没有索引的慢查询
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;
-- 执行计划显示"Seq Scan"（顺序扫描），性能极差

1.2 索引如何工作？

以最常见的B-Tree索引为例：

  1. 索引将数据按照特定列的值排序存储

  2. 构建一个多层次的树状结构

  3. 查询时从根节点开始二分查找

  4. 只需几次磁盘IO即可定位数据

-- 创建索引后
CREATE INDEX idx_customer_id ON orders(customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;
-- 执行计划显示"Index Scan"，性能显著提升

性能对比：

  • 100万行数据的表

  • 无索引：约100万次比较

  • 有B-Tree索引：约20次比较（树高通常很小）

二、索引类型全景图：不止B-Tree那么简单

2.1 B-Tree索引（平衡树索引）

适用场景：

  • 等值查询（=）

  • 范围查询（>, <, BETWEEN）

  • 排序（ORDER BY）

  • 最左前缀匹配

数据结构特点：

  • 保持数据有序

  • 每个节点包含多个键和指针

  • 自动平衡，保证查询效率

-- 多列B-Tree索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);
-- 能加速以下查询：
SELECT * FROM users WHERE last_name = '张' AND first_name = '三';
SELECT * FROM users WHERE last_name = '张' ORDER BY first_name;

2.2 哈希索引

特点：

  • 只支持等值比较（=）

  • 查询速度极快（O(1)时间复杂度）

  • 不支持范围查询

  • MySQL的Memory引擎默认使用哈希索引

-- MySQL中创建哈希索引
CREATE TABLE hash_index_demo (id INT PRIMARY KEY,data VARCHAR(100),INDEX idx_data USING HASH (data)
) ENGINE=MEMORY;

2.3 全文索引

专为文本搜索设计：

  • 支持关键词搜索

  • 支持模糊匹配

  • 可实现搜索引擎功能

-- MySQL全文索引示例
CREATE TABLE articles (id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(200),content TEXT,FULLTEXT INDEX ft_idx (title, content)
);-- 使用MATCH AGAINST语法查询
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库 优化' IN NATURAL LANGUAGE MODE);

2.4 空间索引（R-Tree）

用于地理数据：

  • 存储点、线、多边形等空间数据

  • 支持"附近查询"等空间操作

-- MySQL空间索引示例
CREATE TABLE locations (id INT PRIMARY KEY,name VARCHAR(100),position POINT NOT NULL SRID 4326,SPATIAL INDEX(position)
);-- 查询5公里范围内的地点
SELECT id, name, ST_Distance_Sphere(position, POINT(116.404, 39.915)) AS distance
FROM locations
WHERE ST_Distance_Sphere(position, POINT(116.404, 39.915)) <= 5000
ORDER BY distance;

2.5 其他特殊索引

索引类型适用数据库特点
位图索引Oracle适合低基数列，多个位图索引可以做位运算
倒排索引Elasticsearch文档检索专用，存储词项到文档的映射
函数索引PostgreSQL基于列的计算结果建立索引
部分索引PostgreSQL只对表中部分行建立索引
覆盖索引所有主流数据库索引包含查询所需全部字段，避免回表

 

三、索引设计黄金法则：如何创建高效索引？

3.1 索引选择的三要素

  1. 高选择性：列的不同值多，如用户ID、手机号

  2. 高频查询：经常出现在WHERE、JOIN、ORDER BY中的列

  3. 数据量大小：小表不需要索引，大表关键字段必须建索引

选择率计算公式：

选择率 = 不同值的数量 / 总行数

选择率越低（不同值多），索引效果越好

3.2 多列索引设计技巧

最左前缀原则：

对于索引(A,B,C)，能加速以下查询：

  • WHERE A = ?

  • WHERE A = ? AND B = ?

  • WHERE A = ? AND B = ? AND C = ?

但不能加速：

  • WHERE B = ?

  • WHERE C = ?

  • WHERE B = ? AND C = ?

列顺序决策：

  1. 等值查询列在前，范围查询列在后

  2. 高选择性列在前

  3. 常用列在前

3.3 避免索引失效的常见陷阱

  1. 在索引列上使用函数：

    -- 索引失效
SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';
-- 优化为
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';

  2. 隐式类型转换：

    -- phone是varchar类型，但用数字查询（索引失效）
SELECT * FROM users WHERE phone = 13800138000;
-- 应改为
SELECT * FROM users WHERE phone = '13800138000';

  3. 使用前导通配符LIKE

    -- 索引失效
SELECT * FROM users WHERE name LIKE '%张%';
-- 如果必须用，考虑全文索引

  4. OR条件使用不当：

    -- 如果age无索引，整个查询索引失效
SELECT * FROM users WHERE user_id = 100 OR age = 25;
-- 优化为UNION
SELECT * FROM users WHERE user_id = 100
UNION
SELECT * FROM users WHERE age = 25;

四、高级索引优化策略

4.1 覆盖索引（Covering Index）

定义：索引包含查询所需的所有字段，无需回表查询数据页

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(order_date, customer_id, amount);-- 以下查询可以直接从索引获取数据
SELECT order_date, customer_id, amount 
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

优势：

  • 减少IO操作

  • 提升查询速度

  • 减轻服务器负载

4.2 索引条件下推（ICP）

MySQL特性：将WHERE条件推到存储引擎层过滤

-- 没有ICP时：
1. 存储引擎按索引查找记录
2. 返回所有匹配索引条件的记录给服务器层
3. 服务器层过滤数据-- 启用ICP后：
1. 存储引擎按索引查找记录
2. 在存储引擎层就过滤掉不符合条件的记录
3. 只返回符合条件的记录给服务器层

查看ICP优化：

EXPLAIN SELECT * FROM employees 
WHERE last_name = 'Smith' AND first_name LIKE 'J%';
-- Extra列显示"Using index condition"

4.3 索引合并优化

当查询有多个条件，每个条件都有独立索引时，MySQL可能使用索引合并：

-- 两个单列索引
CREATE INDEX idx_lastname ON employees(last_name);
CREATE INDEX idx_firstname ON employees(first_name);-- 可能使用索引合并
EXPLAIN SELECT * FROM employees 
WHERE last_name = 'Smith' OR first_name = 'John';
-- type: index_merge

注意：通常不如复合索引高效，应考虑创建合适的复合索引

五、实战：索引优化案例分析

5.1 电商平台订单查询优化

原始表结构：

CREATE TABLE orders (order_id BIGINT PRIMARY KEY,user_id BIGINT,order_status TINYINT,create_time DATETIME,total_amount DECIMAL(10,2),-- 其他字段...
);

常见查询：

  1. 用户查看自己的订单列表

  2. 后台按状态和时间范围查询订单

  3. 统计某时间段订单金额

优化方案：

-- 用户查询优化
CREATE INDEX idx_user_status ON orders(user_id, order_status);-- 后台管理查询优化
CREATE INDEX idx_status_time ON orders(order_status, create_time);-- 统计查询优化
CREATE INDEX idx_time_amount ON orders(create_time, total_amount);

5.2 社交平台好友动态查询

场景：用户查看好友的最新动态

原始查询：

SELECT * FROM posts 
WHERE user_id IN (SELECT friend_id FROM user_friends WHERE user_id = 100)
ORDER BY create_time DESC
LIMIT 20;

优化方案：

-- 创建复合索引
CREATE INDEX idx_friends ON user_friends(user_id, friend_id);
CREATE INDEX idx_user_posts ON posts(user_id, create_time DESC);-- 优化后查询（MySQL 8.0+）
WITH friend_list AS (SELECT friend_id FROM user_friends WHERE user_id = 100
)
SELECT * FROM posts 
WHERE user_id IN (SELECT friend_id FROM friend_list)
ORDER BY create_time DESC
LIMIT 20;

六、索引监控与维护

6.1 如何知道索引是否被使用？

MySQL查看索引使用情况：

-- 查看表索引
SHOW INDEX FROM table_name;-- 查看索引使用统计
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_db' AND table_name = 'your_table';-- 长期监控（需开启performance_schema）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

6.2 索引维护操作

  1. 重建索引（解决索引碎片化）：

    -- MySQL
ALTER TABLE orders REBUILD INDEX idx_order_date;-- PostgreSQL
REINDEX INDEX idx_order_date;

  2. 在线DDL操作（MySQL 5.6+）：

    ALTER TABLE orders 
ADD INDEX idx_new_index (col1, col2), 
ALGORITHM=INPLACE, LOCK=NONE;

  3. 定期分析表：

    ANALYZE TABLE orders;

6.3 何时应该删除索引？

  1. 索引从未被查询使用（通过监控确认）

  2. 索引选择性极低（如性别列索引）

  3. 索引维护成本高于查询收益（频繁更新的列）

  4. 有更好的复合索引可以替代

    -- 删除索引前确认使用情况
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db' AND object_name = 'your_table';-- 安全删除索引
DROP INDEX idx_unused ON orders;

 

七、不同数据库的索引特性对比

特性MySQL(InnoDB)PostgreSQLOracleSQL Server
默认索引类型B+TreeB-TreeB-TreeB-Tree
哈希索引仅Memory引擎支持不支持支持不支持
函数索引8.0+支持支持支持支持
部分索引不支持支持支持支持
倒排索引全文索引GIN/GiST索引上下文索引全文索引
索引组织表聚簇索引不支持IOT聚簇索引
在线创建索引支持支持支持支持

结语： 

  1. 不要过度索引：每个索引都会增加写操作开销

  2. 测量而非猜测：通过EXPLAIN和性能监控验证索引效果

  3. 考虑整体性能：有时稍微降低查询性能可以显著提升整体吞吐量

  4. 与时俱进：数据库版本升级常带来新的索引优化可能

"最好的索引是不存在的索引，第二好的索引是完美的索引。" — 数据库优化专家谚语

建议：

  1. 分析生产环境中的慢查询

  2. 设计合适的索引方案

  3. 测量优化前后的性能差异

  4. 持续监控和调整索引策略

http://www.xdnf.cn/news/1209241.html

相关文章：

  • 13、select_points_object_model_3d解析
  • 安卓逆向2-安卓刷机和获取root权限和安装LSPosed框架
  • Linux安装ragflow（含一键安装脚本）
  • vue中使用wavesurfer.js绘制波形图和频谱图
  • sqli-labs通关笔记-第25关GET字符注入(过滤or和and 脚本法)
  • buuctf_crypto26-30
  • 基于变频与移相混合控制（PFM+PSM）的全桥LLC谐振变换器仿真模型
  • 车载诊断架构 --- 关于诊断时间参数P4的浅析
  • QML 3D曲面图(Surface3D)技术
  • K-近邻算法（KNN算法）的K值的选取--交叉验证+网格搜索
  • 【C++算法】72.队列+宽搜_二叉树的最大宽度
  • adb reboot 与 adb shell svc power reboot 的区别
  • 【C++】1. C++基础知识
  • 【HTML】浅谈 script 标签的 defer 和 async
  • 企业高性能web服务器
  • EnergyMath芯详科技 EMS4100/MES4000/MES3900
  • 如何保证DoIP的网络安全?
  • 基于 xlsx-js-style 的 Excel 导出工具实现导出excel
  • 40+个常用的Linux指令——下
  • haproxy应用详解
  • 从github同步新项目的两次挫折-2025.7.29
  • 【WRF工具】服务器中安装编译GrADS
  • 信创国产Linux操作系统汇总：从桌面到服务器，百花齐放
  • 【Golang】Go语言Map数据类型
  • 随缘玩 一： 代理模式
  • 计算器4.0：新增页签功能梳理页面，通过IO流实现在用户本地存储数据
  • MySQL数据库 mysql常用命令
  • 再谈亚马逊云科技（AWS）上海AI研究院7月22日关闭事件
  • 实现视频实时马赛克
  • P1098 [NOIP 2007 提高组] 字符串的展开