数据库-索引

于 2025-07-29 23:20:04 发布
阅读量528 收藏 12
点赞数 9
CC 4.0 BY-SA版权
分类专栏： 数据库 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/lengxia0/article/details/149757807

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

一、索引的本质：

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

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. 持续监控和调整索引策略

数据库 --- 索引、视图
m0_52018791的博客
03-26 2798
一、什么是索引？ 索引相当于一本书的目录，通过目录可以快速的找到对应的资源。 在数据库方面，查询一张表的时候有两种检索方式：全表扫描、索引检索（效率很高）。 索引虽然可以提高检索效率，但不能随意添加索引，因为索引也是数据库当中的对象，也需要数据库不断维护。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。 添加索引是给某一个字段或者是某些字段添加索引。 例如：select ename,sal from emp where ename = 'SMITH'
MySQL-数据库-索引详解
08-30
MySQL 数据库索引详解 MySQL 数据库索引是一种特殊的数据库结构，可以快速查询数据库表中特定记录。索引是提高数据库性能的重要方式。 索引的概念 ---------------- 索引是表中数据的目录。如果使用索引，则扫描...
MySQL数据库 - 索引（下）
NoobNo2的博客
11-26 2890
目录1. 创建索引1.1 自动创建索引1.2 手动创建索引1.2.1 主键索引1.2.2 唯一索引1.2.3 普通索引2. 查看索引3. 删除索引3.1 删除主键索引3.2 删除普通索引4. 查看执行计划4.1 不加条件，查询所有4.2 使用主键查询4.3 子查询中使用索引4.4 使用普通索引4.5 使用复合索引5. 创建索引的注意事项方式一：建表的时候创建主键 查看表结构：方式二：创建表时单独指定主键列 方式三：修改表中的列为主键 先指定 student_id 列为主键，然后再把主键列设置为自
数据库----索引和事务
m0_61801103的博客
05-04 1486
目录 索引 作用 使用场景 索引的类型 B树和B+树 B树 B+树 区别 基于B+树的索引 使用 查看索引 创建索引 删除索引 事务 事务的特性（ACID） 使用 事务可能产生的问题 解决：隔离级别 索引 索引是一种特殊的文件，使用一定的数据结构，来保存索引字段对应的数据。可以对表中的一列或多列创建索引，并指定索引的类型。 首先我们要知道数据库保存数据的基本单位：page 作用 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录..
SQL Server数据库 -- 索引与视图
m0_62476845的博客
08-02 4483
在学习完创建库表、查询等知识点后，为了更加方便优化数据库的存储和内容，我们需要学习一系列的方法例如索引与视图等等，从而使我们更加熟练和使用数据库，突破表面，触摸内部！一、索引1、索引简介索引是加快数据查询效率的一种有效方法，因为建立索引可以改变数据的搜索结构。多数情况下索引是建立在基础表上的，但也可以建立在视图上。在数据库中建立索引是为了提高数据的查询速度，与书本前面的目录或书后面的术语表类似。利用目录或属于快速查找所需信息，而无需翻阅整本书。首先，索引在数据库中占有一定的存储空间。
数据库--索引的类型及特点
wyx978314453的博客
09-25 1202
前面说了很多关于索引的问题，今天来回顾下索引最基础的东西，分类和特点。 一、什么是索引 索引是对数据库表中一列或多列的值进行排序的一种结构，例如 employee 表的姓（name）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些
数据库之索引＜保姆级文章＞
robin_suli的博客
09-16 3377
将页内包括头行、尾⾏在内的所有⾏进⾏分组，约定头行单独为⼀组，其他每个组最多8条数据，同时把每个组最后⼀行在页中的地址，按主键从⼩到⼤的顺序记录在页⽬录中在，页⽬录中的每⼀个位置称为⼀个槽，每个槽都对应了⼀个分组，⼀旦分组中的数据行超过分组的上限8个时，就会分裂出⼀个新的分组；后续在查询某⾏时，就可以通过⼆分查找，先找到对应的槽，然后在槽内最多8个数据行中进行遍历即可，从⽽⼤幅提高了查询效率，这时⼀个页的核⼼结构就完成了。使⽤索引的⽬的只有⼀个，就是提升数据检索的效率，在应⽤程序的运⾏过程中，查。
MySQL数据库——索引
Jilit_jilit的博客
09-29 4532
一文教你了解MySQL数据库的索引，包括索引的定义、特点、类型、聚簇索引、非聚簇索引、建立索引的原则等内容。
数据库 - 索引、基本表创建与删除
陆讯
05-05 4846
修改基本表ALTER TABLE <表名> [ ADD <新列名> <数据类型> [ 完整性约束 ] ] [ DROP <完整性约束名> ] [ ALTER COLUMN<列名> <数据类型> ]； [例8]向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD S_entrance DATE； 不论基本表中原来是否已有数据，新增加的列一
数据库－分区索引
Criss@陈磊
05-25 2002
分区索引分为本地分区索引（局部分区索引）和全局分区索引。其中本地分区索引又分为有前缀(prefix)的索引和无前缀（nonprefix）的索引。全局索引目前只支持有前缀的索引.B树索引和位图索引都可以分区，但是HASH索引不可以被分区。位图索引必须是本地索引。 本地分区索引定义： 创建了一个分区表后，如果需要在表上面创建索引，并且索引的分区机制和表的分区机制一样，那么这样的索引就叫做本
达梦数据库DCP-索引管理
达梦数据库技术贴
08-01 2203
千里之行始于足下，梦想不付之行动，终究是纸上谈兵 经过一段时间的达梦DCP培训让学习了好多支持点。让我对达梦的理解更加深刻。 今天为大家说一说达梦数据的索引管理 索引概念 /*索引是与表相关的一种结构，它能使对应于表的SQL语句执行的更快，因为索引能快速定位数据库。 DM7索引能提供访问表的数据的更快路径，可以不用重写任何查询二使用索引，其查询结果与不使用索引是一样的，但速度更快。 达梦数据库提供了几种最常见类型的索引，对不同场景有不同的功能。 1、聚集索引：每个普通表有且只有一个聚集索引。注意：在建表的时
mysql-数据库-索引详解.doc
08-27
mysql-数据库-索引详解.doc
Oracle数据库--索引讲义.pptx
05-09
在Oracle数据库中，索引是提高数据检索速度的关键工具。本讲义主要围绕索引的创建、类型以及管理展开，旨在帮助读者理解索引的重要性和使用方法。 1. **索引概述** - 索引是一种特殊的文件，用于快速访问数据库表...
Redis 键值对操作详解：Python 实现指南
最新发布
AI浩
07-29 547
场景推荐操作替代方案添加小数据SETHSET（对象）添加大数据HSET/MSET分批次添加添加临时数据SETEX删除小数据DELETEUNLINK删除大数据UNLINK无批量操作管道 + MSET/UNLINK单独命令添加操作使用set()添加单个键值对使用mset()批量添加多个键值对使用setex()添加带过期时间的键值对删除操作优先使用unlink()进行删除（尤其大型数据）仅在需要立即释放内存时使用delete()批量删除时结合管道提高效率。
MyBatis-Plus 与 Spring 新手指南
qq_31619295的博客
07-25 1159
泛型机制: 确保类型安全和代码提示继承体系: 提供丰富的基础功能注解映射: 简化配置，提高开发效率Spring集成: 无缝融入Spring生态掌握这些核心概念，就能快速理解和使用MyBatis-Plus进行数据访问层开发。
MySQL 事务管理
puppy_1mo的博客
07-25 1687
摘要 本文系统阐述了MySQL事务的ACID特性及其实现原理。首先指出不加控制的CURD操作可能引发数据不一致问题，通过事务的原子性、一致性、隔离性和持久性可解决这些问题。重点分析了四种隔离级别（读未提交、读提交、可重复读、串行化）的表现差异及对应的并发问题（脏读、不可重复读、幻读）。深入探讨了MVCC机制，包括隐藏字段、undo日志和ReadView的工作原理，解释了可重复读级别如何通过版本链和可见性判断实现事务隔离。最后对比了RC和RR级别下ReadView生成时机的本质区别，说明MySQL默认RR级别
Redis 服务挂掉排查与解决
qq_33665793的博客
07-28 997
Redis服务挂掉的排查与解决方法 摘要： 当出现"RedisException in Redis.php line 63 Connection refused"错误时，表明Redis服务无法连接。本文总结了常见原因：服务未启动、网络问题、配置错误或内存不足等，并提供了系统的排查步骤：1)检查Redis进程状态；2)查看服务运行状态；3)启动/重启服务；4)检查配置文件；5)查看错误日志。同时介绍了内存调整、防火墙配置和自动重启等解决方案.
Sql server开挂的OPENJSON
清风弄影
07-27 1026
摘要：SQL Server 2019的OPENJSON功能大幅简化了JSON数据处理。相比SQL Server 2008需要自定义表类型来传递表参数的方式，新版本只需将JSON字符串作为参数传递，通过OPENJSON解析即可。该功能支持单层和嵌套JSON结构，可指定字段名称和类型，还能处理复杂层级数据。从SQL Server 2016开始引入的JSON支持，不仅提高了开发效率，还影响了表结构设计，许多原本需要主从表结构的场景现在用单表配合JSON字段就能实现，大大简化了数据库操作。
数据库审计及安全管理的解决方案
Neozsvc的博客
07-28 467
支持 MySQL、MariaDB、Oracle、SQLServer、PostgreSQL、MongoDB、Redis 等多种数据库，无论您的技术栈多么复杂，Next-DBM都能完美适配。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值