数据库索引设计全面指南：从基础原则到慢查询优化
参考文献
[1] Abraham Silberschatz, Henry F. Korth, S. Sudarshan. Database System Concepts (7th Edition). McGraw-Hill Education, 2019.
[2] Baron Schwartz, Peter Zaitsev, Vadim Tkachenko. High Performance MySQL (4th Edition). O'Reilly Media, 2017.
[3] 唐成. PostgreSQL 修炼之道：从小工到专家（第2版）. 机械工业出版社, 2020.
[4] Kristina Chodorow, Michael Dirolf. MongoDB: The Definitive Guide (3rd Edition). O'Reilly Media, 2018.
1. 索引设计核心原则
1.1 基础原则
- 查询优先：索引设计应优先考虑实际查询场景，基于应用的查询模式进行设计 [1]
- 选择性原则：选择区分度高的列作为索引，避免在低选择性列上创建索引 [2]
- 最小化原则：索引列应尽可能少，避免冗余索引和过度索引 [1]
- 覆盖查询：尽可能让索引覆盖常用查询，减少回表操作 [2]
- 避免过度索引：索引会增加写操作成本，需权衡查询性能和写性能 [1]
1.2 性能权衡
- 读多写少场景：可适当增加索引数量，优化查询性能 [2]
- 写多读少场景：应减少索引数量，降低写操作开销 [2]
- 大数据量场景：更需要合理的索引设计，避免全表扫描 [1]
2. 索引类型与适用场景
2.1 常用索引类型
索引类型的选择取决于数据类型、查询模式和性能需求，这是数据库设计中的核心决策之一 [1]
| 索引类型 | 适用场景 | 优势 | 劣势 |
|---|---|---|---|
| B+树索引 | 范围查询、排序、分组、精确匹配 | 支持多种查询类型，查询效率稳定 | 索引大小较大，写操作开销较高 |
| 哈希索引 | 精确匹配查询 | 查询速度快，适合等值查询 | 不支持范围查询、排序和分组 |
| 唯一索引 | 确保数据唯一性，同时提供查询性能 | 保证数据完整性，查询效率高 | 写操作开销较高 |
| 复合索引 | 多列查询场景 | 减少索引数量，优化多列查询 | 索引维护成本高，需考虑列顺序 |
| 部分索引 | 数据分布不均匀，仅需索引部分数据 | 减少索引大小，提高查询效率 | 适用场景有限 |
| GIN索引 | JSON、数组等复杂类型查询 | 支持复杂数据类型的高效查询 | 索引创建和维护成本高 |
| 全文索引 | 文本内容的全文搜索 | 支持自然语言搜索，查询效率高 | 索引维护成本高，配置复杂 |
| 倒排索引 | 全文搜索、关键词查询 | 支持高效的关键词搜索，适合搜索引擎场景 | 索引维护复杂，写操作开销高 |
| 地理空间索引 | 地理坐标查询、距离计算、区域查询 | 支持高效的地理空间查询 | 索引创建和维护成本高，查询语法复杂 |
| BRIN索引（区块索引） | 大型表、时间序列数据、有序数据 | 索引大小极小，维护成本低，适合超大数据集 | 查询效率低于B+树，仅适合有序数据 |
2.2 索引类型选择策略
- 精确匹配查询：优先考虑B+树索引或哈希索引
- 范围查询、排序和分组：选择B+树索引
- 多列查询：考虑复合索引
- JSON/数组类型：使用GIN索引
- 文本搜索：使用全文索引
- 数据唯一性保证：使用唯一索引
3. 索引设计通用策略
3.1 单列索引设计
- 选择高选择性列：选择性 = 不同值数量 / 总行数，值越大越好 [2]
- 避免在频繁更新的列上创建索引 [1]
- 避免在长文本列上创建索引（可考虑前缀索引） [2]
- 避免在NULL值比例高的列上创建索引 [3]
3.2 复合索引设计
- 列顺序原则：将选择性高的列放在前面 [2]
- 最左前缀原则：查询需匹配索引的最左前缀才能使用索引 [1]
- 包含排序和分组列：将排序和分组列放在复合索引的后面 [2]
- 覆盖查询优化：将常用查询列包含在索引中，避免回表 [2]
3.3 索引设计常见误区
- 为所有列创建索引
- 忽视索引的维护成本
- 不考虑索引的选择性
- 复合索引列顺序不当
- 忽视查询模式的变化
4. 索引设计生命周期管理
4.1 需求分析阶段
- 收集应用的查询模式
- 分析查询的WHERE、ORDER BY、GROUP BY子句
- 识别核心查询和高频查询
4.2 设计阶段
- 根据查询模式设计初始索引
- 考虑索引的选择性和覆盖范围
- 评估索引的维护成本
- 设计复合索引时考虑最左前缀原则
4.3 实施阶段
- 创建索引（建议在低峰期进行）
- 测试查询性能
- 验证索引的正确性和有效性
4.4 监控阶段
- 监控索引使用情况
- 监控查询性能变化
- 识别性能瓶颈
4.5 优化阶段
- 根据监控结果调整索引设计
- 添加新索引以优化性能
- 删除或修改低效索引
- 更新索引设计文档
4.6 索引添加时机决策（基于性能指标）
索引的添加时机需要结合应用的性能指标、查询模式和业务场景进行综合判断，主要考虑响应时间和QPS（每秒查询数）等关键指标 [2]：
4.6.1 基于响应时间的判断依据
- 响应时间阈值：设定合理的响应时间阈值，不同业务场景有不同要求：
- 核心交易系统：P99响应时间 < 100ms
- 电商搜索系统：P95响应时间 < 200ms
- 数据分析系统：P95响应时间 < 500ms
- 响应时间趋势：监控响应时间的变化趋势，若呈上升趋势且持续恶化，需及时分析
- 慢查询占比：统计慢查询（如执行时间 > 1s）占总查询的比例，若比例超过5%，需重点优化
4.6.2 基于QPS的判断依据
- QPS峰值：当系统QPS接近或超过设计容量时，需通过索引优化提高系统吞吐量
- QPS增长趋势：随着业务增长，QPS持续上升时，需提前规划索引优化
- 资源利用率：当CPU或磁盘IO利用率过高，且主要由数据库查询引起时，需考虑添加索引
4.6.3 综合判断依据与实战案例
| 性能指标 | 警告阈值 | 严重阈值 | 建议行动 | 实战案例 |
|---|---|---|---|---|
| P95响应时间 | > 200ms | > 500ms | 分析慢查询，考虑添加索引 | 电商平台商品列表页，P95响应时间从400ms优化到120ms |
| P99响应时间 | > 500ms | > 1s | 立即优化，优先考虑添加索引 | 金融交易系统，P99响应时间从1.2s优化到300ms |
| 慢查询占比 | > 5% | > 10% | 全面分析慢查询，添加必要索引 | 社交媒体平台，慢查询占比从8%降至1.5% |
| 数据库CPU利用率 | > 70% | > 90% | 分析查询负载，添加索引减少CPU消耗 | 大数据分析平台，CPU利用率从85%降至45% |
| 磁盘IO利用率 | > 60% | > 80% | 添加索引减少磁盘IO，特别是随机读 | 日志系统，磁盘IO利用率从75%降至30% |
| QPS | 接近设计容量的80% | 超过设计容量 | 优化索引提高系统吞吐量 | 直播平台，QPS从10000提升到15000 |
4.6.4 实战：索引添加时机判断流程
案例1：电商平台商品搜索性能优化
背景：
- 商品表有1000万条数据
- 搜索接口P95响应时间达到400ms，超过告警阈值
- QPS峰值达到5000，接近设计容量
- 慢查询日志显示大量全表扫描
判断流程：
-
收集性能指标：
- P95响应时间：400ms（超过警告阈值200ms）
- 慢查询占比：7%（超过警告阈值5%）
- CPU利用率：75%（超过警告阈值70%）
- 磁盘IO利用率：65%（超过警告阈值60%）
-
分析查询模式：
SELECT * FROM products WHERE category_id = ? AND price BETWEEN ? AND ? ORDER BY sales DESC LIMIT 20; -
评估索引需求：
- 查询条件包含category_id和price
- 排序字段为sales
- 选择性分析：category_id选择性0.3，price选择性0.8
-
设计索引：
CREATE INDEX idx_products_category_price_sales ON products(category_id, price, sales DESC); -
实施与验证：
- 在测试环境验证性能提升：P95响应时间从400ms降至120ms
- 在生产环境低峰期（凌晨2点）创建索引
- 创建过程耗时15分钟，未对业务造成影响
- 验证生产环境性能：P95响应时间从400ms降至130ms，慢查询占比降至1.5%
案例2：社交媒体平台消息查询优化
背景：
- 消息表有5亿条数据
- 用户查询历史消息响应时间长
- 系统经常出现CPU使用率过高告警
判断流程：
-
收集性能指标：
- P99响应时间：1.5s（超过严重阈值1s）
- 慢查询占比：12%（超过严重阈值10%）
- CPU利用率：92%（超过严重阈值90%）
-
分析查询模式：
SELECT * FROM messages WHERE user_id = ? AND create_time > ? ORDER BY create_time DESC LIMIT 50; -
评估索引需求：
- 查询条件包含user_id和create_time
- 排序字段为create_time
- 选择性分析：user_id选择性0.95，create_time选择性0.85
-
设计索引：
CREATE INDEX idx_messages_user_create_time ON messages(user_id, create_time DESC); -
实施与验证：
- 使用PostgreSQL的CONCURRENTLY选项创建索引，避免锁表
- 创建过程耗时2小时，生产环境无明显影响
- 验证性能：P99响应时间从1.5s降至200ms，CPU利用率降至50%
4.6.5 索引添加的最佳实践
- 低峰期添加：选择业务低峰期（如凌晨2-4点）添加索引，减少对在线业务的影响
- 测试环境验证：在测试环境验证索引的有效性和性能提升，避免直接在生产环境添加
- 分批添加：对于大型表，考虑分批添加索引或使用并发索引创建
- 监控添加过程：实时监控索引添加过程，观察数据库负载变化
- 回滚机制：准备回滚方案，若添加索引导致性能问题，能快速恢复
4.6.6 索引添加后的性能验证
- 响应时间对比：比较添加索引前后的响应时间，验证性能提升
- QPS提升效果：观察系统QPS是否有所提升，特别是在高并发场景下
- 资源利用率变化：监控CPU、磁盘IO等资源利用率的变化
- 慢查询减少情况：统计慢查询数量的变化，验证索引的有效性
- 写性能影响：评估索引对写操作性能的影响，确保在可接受范围内
5. 索引使用与性能监控
5.1 索引使用监控
- 索引访问统计：监控索引的扫描次数、读取行数、命中次数等
- 未使用索引识别：定期查找并清理未使用的索引
- 索引使用效率：分析索引的命中率和使用效率
5.2 查询性能监控
- 慢查询分析：监控并分析慢查询，找出性能瓶颈
- 查询计划分析：使用EXPLAIN/EXPLAIN ANALYZE分析查询计划
- 查询执行统计：监控查询的执行时间、扫描行数、索引使用情况等
5.3 监控工具与方法
- 数据库内置视图：如pg_stat_user_indexes、pg_stat_statements等
- 慢查询日志：启用慢查询日志，记录执行时间超过阈值的查询
- 性能监控工具：Prometheus+Grafana、DataDog、pgAdmin等
6. 索引与慢查询分析优化
慢查询是数据库性能问题的主要来源之一，而索引优化是解决慢查询的核心手段。本章将详细介绍慢查询的监控、分析、排查方法以及基于慢查询的索引优化策略 [2]
6.1 慢查询概述
6.1.1 慢查询定义
慢查询是指执行时间超过预设阈值的SQL查询语句。不同数据库和业务场景下，慢查询阈值的设定有所不同：
- 互联网业务：通常设置为100ms-1s
- 数据分析业务：可设置为5s-30s
- 批处理任务：可设置为1min以上
6.1.2 慢查询的危害
- 系统资源消耗：长时间占用CPU、内存和IO资源
- 锁竞争加剧：延长事务持有锁的时间，导致锁等待和死锁
- 用户体验下降：应用响应时间延长，甚至超时
- 数据库稳定性降低：可能引发数据库性能雪崩
6.1.3 慢查询的常见原因
- 缺少合适的索引：全表扫描或索引选择不当
- 索引设计不合理：复合索引列顺序错误，或索引选择性差
- 查询语句问题：复杂的JOIN操作、子查询嵌套过深
- 数据量增长：原有索引设计无法适应数据量变化
- 数据库配置问题：内存分配、缓存设置不合理
6.2 慢查询监控与收集
6.2.1 MySQL慢查询日志配置
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 慢查询阈值（单位：秒）
long_query_time = 0.1
# 记录未使用索引的查询
log_queries_not_using_indexes = 1
# 记录管理语句
log_slow_admin_statements = 1
# 每分钟允许记录的慢查询数量（0表示无限制）
log_throttle_queries_not_using_indexes = 10
6.2.2 PostgreSQL慢查询日志配置
# 开启日志收集
logging_collector = on
# 日志文件路径
log_directory = 'pg_log'
# 日志文件名称格式
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# 记录慢查询（单位：毫秒）
log_min_duration_statement = 100
# 记录查询计划
auto_explain.log_min_duration = 100
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_format = text
6.2.3 MongoDB慢查询监控
// 开启慢查询日志
db.setProfilingLevel(1, { slowms: 100 })
// 查看慢查询日志
db.system.profile.find().sort({ ts: -1 }).limit(10)
// 查看特定集合的慢查询
db.system.profile.find({ ns: 'mydb.mycollection' }).sort({ ts: -1 })
6.3 慢查询分析工具
6.3.1 命令行工具
MySQL：
-
mysqldumpslow：MySQL自带的慢查询分析工具# 按查询时间排序，显示前10条 mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # 按查询次数排序，显示前10条 mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log # 分析包含特定字符串的慢查询 mysqldumpslow -g "select" /var/log/mysql/mysql-slow.log -
pt-query-digest：Percona Toolkit中的强大慢查询分析工具# 分析慢查询日志 pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt # 实时分析慢查询 pt-query-digest --processlist h=localhost,u=root,p=password
PostgreSQL：
pgBadger：PostgreSQL日志分析工具# 生成HTML格式的慢查询报告 pgBadger -f stderr -o slow_report.html /var/log/postgresql/postgresql.log
6.3.2 GUI工具
- MySQL Workbench：提供可视化的查询分析和性能监控
- Navicat：支持慢查询日志分析和可视化展示
- DataGrip：智能SQL编辑器，提供查询计划分析
- Percona Monitoring and Management (PMM)：开源的数据库监控平台
- Prometheus + Grafana：结合使用可实现慢查询的可视化监控和告警
6.3.3 云服务工具
- 阿里云RDS慢查询分析：提供自动化的慢查询分析和优化建议
- 腾讯云CDB慢查询分析：可视化展示慢查询统计和趋势
- AWS RDS Performance Insights：基于DB负载的性能分析工具
6.4 慢查询排查流程
6.4.1 第一步：收集和筛选慢查询
- 确定分析时间范围（如最近24小时或特定峰值时段）
- 按执行时间、查询次数或影响行数排序
- 筛选出TOP N的慢查询进行重点分析
- 分类统计慢查询类型（SELECT、UPDATE、DELETE等）
6.4.2 第二步：分析查询执行计划
-- MySQL查看执行计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND created_at > '2023-01-01';
-- PostgreSQL查看执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123 AND created_at > '2023-01-01';
重点关注执行计划中的以下信息：
- 访问类型（type）：ALL（全表扫描）、index（索引扫描）、range（范围扫描）等
- key：实际使用的索引
- key_len：使用的索引长度
- rows：估计扫描的行数
- Extra：额外信息，如Using where、Using index、Using temporary等
6.4.3 第三步：检查索引使用情况
-- MySQL查看索引使用统计
SHOW INDEX FROM orders;
SELECT * FROM sys.schema_index_statistics WHERE table_name = 'orders';
-- PostgreSQL查看索引使用统计
SELECT * FROM pg_stat_user_indexes WHERE relname = 'orders';
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';
6.4.4 第四步：分析数据分布
-- 查看列的选择性
SELECT COUNT(DISTINCT user_id) / COUNT(*) AS selectivity FROM orders;
-- 查看数据分布情况
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY 2 DESC LIMIT 10;
6.4.5 第五步：验证优化方案
- 在测试环境中执行优化后的查询
- 比较优化前后的执行时间和资源消耗
- 验证索引是否被正确使用
- 评估对其他查询的影响
6.5 基于慢查询的索引优化
6.5.1 索引缺失的优化
- 场景：执行计划显示全表扫描（ALL），且查询条件列上无索引
- 优化方案：为查询条件列创建合适的索引
- 示例：
-- 慢查询 SELECT * FROM orders WHERE user_id = 123 AND created_at > '2023-01-01'; -- 优化：创建复合索引 CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
6.5.2 索引选择性差的优化
- 场景：索引选择性低（如性别、状态等枚举字段）
- 优化方案：考虑复合索引，将选择性高的列放在前面
- 示例：
-- 优化前：单列索引选择性差 CREATE INDEX idx_orders_status ON orders(status); -- 优化后：复合索引 CREATE INDEX idx_orders_status_created ON orders(status, created_at);
6.5.3 复合索引列顺序优化
- 场景：复合索引列顺序与查询条件不匹配
- 优化方案：根据查询条件调整复合索引列顺序，将选择性高的列放在前面
- 示例：
-- 常见查询：WHERE user_id = ? AND created_at > ? CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); -- 而不是 CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);
6.5.4 覆盖索引优化
- 场景：查询需要回表获取数据（Using where in Extra）
- 优化方案：将查询列包含在索引中，实现覆盖查询
- 示例：
-- 慢查询 SELECT user_id, order_no, total_amount FROM orders WHERE created_at > '2023-01-01'; -- 优化：创建覆盖索引 CREATE INDEX idx_orders_created_covering ON orders(created_at) INCLUDE (user_id, order_no, total_amount);
6.5.5 索引冗余的清理
- 场景：存在多个相似索引或未使用的索引
- 优化方案：删除冗余索引，保留最有效的索引
- 示例：
-- 查看未使用的索引（MySQL） SELECT * FROM sys.schema_unused_indexes; -- 删除未使用的索引 DROP INDEX idx_orders_old ON orders;
6.6 最佳实践与案例分析
6.6.1 电商平台订单查询优化
问题：某电商平台的订单查询接口响应时间超过2秒，慢查询日志显示：
SELECT * FROM orders WHERE user_id = ? AND order_status = ? ORDER BY created_at DESC LIMIT 10;
分析：
- 执行计划显示全表扫描（ALL）
- user_id列有单列索引，但order_status列选择性低
- 查询需要排序和分页
优化方案：
-- 创建复合索引，包含查询条件和排序字段
CREATE INDEX idx_orders_user_status_created ON orders(user_id, order_status, created_at DESC);
效果：查询响应时间从2.1秒降低到0.05秒，性能提升42倍。
6.6.2 社交媒体消息查询优化
问题：社交媒体平台的消息查询接口在高峰时段响应缓慢，慢查询日志显示：
SELECT * FROM messages WHERE receiver_id = ? AND is_read = 0 ORDER BY sent_time DESC;
分析：
- receiver_id列有索引，但is_read列选择性极低（大部分为0）
- 消息表数据量超过1亿条
- 查询需要排序
优化方案：
-- 创建复合索引，针对未读消息场景优化
CREATE INDEX idx_messages_receiver_unread_sent ON messages(receiver_id, is_read, sent_time DESC);
效果：未读消息查询响应时间从1.8秒降低到0.08秒，同时减少了CPU和IO资源消耗。
6.6.3 慢查询优化最佳实践
- 建立监控体系：实时监控慢查询数量和性能趋势
- 定期分析报告：每周或每月生成慢查询分析报告
- 优先优化高频查询：关注执行次数多的慢查询
- 测试环境验证：所有索引变更必须在测试环境验证
- 分阶段实施：大表索引变更应在低峰期分阶段实施
- 持续监控效果：优化后持续监控性能变化
- 文档化管理：记录慢查询优化过程和结果
6.7 索引设计与慢查询的闭环优化
- 监控：收集慢查询日志，建立性能基线
- 分析：使用工具分析慢查询，定位问题根源
- 优化：设计或调整索引，优化查询语句
- 验证：在测试环境验证优化效果
- 实施：在生产环境部署优化方案
- 反馈：监控优化后的性能，形成闭环
通过建立这样的闭环优化机制，可以确保索引设计始终适应业务需求和数据变化，持续提升数据库性能。
索引优化是一个持续的过程，需要结合慢查询分析、执行计划评估和业务场景理解，才能设计出高效、合理的索引。慢查询是索引优化的重要依据，通过深入分析慢查询，可以发现索引设计中的问题，从而进行针对性的优化 [2]
7. 不同数据库引擎的索引特点
不同数据库引擎在索引实现上有显著差异，了解这些差异对于跨数据库开发至关重要 [1][3][4]
7.1 PostgreSQL
- 支持多种索引类型：B+树、哈希、GIN、GiST、SP-GiST、BRIN等
- 支持部分索引和表达式索引
- 支持并发索引创建和重建
- 倒排索引实现：通过GIN或GiST索引结合tsvector类型实现
- 地理空间索引：支持PostGIS扩展，提供点、线、面等多种地理类型的索引
- BRIN索引：PostgreSQL 9.5引入，专为大型表和有序数据设计
- 索引维护成本相对较高
7.2 MySQL
- InnoDB默认使用B+树索引
- 支持全文索引和空间索引
- 支持前缀索引
- 复合索引遵循最左前缀原则
- 倒排索引实现：通过FULLTEXT索引实现
- 地理空间索引：支持Spatial索引，基于R树实现
7.3 MongoDB
- 支持单字段索引、复合索引、多键索引、地理空间索引、文本索引等
- 支持TTL索引（自动过期数据）
- 支持部分索引和稀疏索引
- 倒排索引实现：通过text索引实现
- 地理空间索引：支持2d、2dsphere等地理空间索引类型
7.4 BRIN索引（区块索引）详解
7.4.1 原理
BRIN（Block Range Index，区块范围索引）是一种空间效率极高的索引结构，通过存储每个数据块范围（Block Range）的摘要信息来实现索引 [3]。
- 数据块范围：一组连续的数据块（默认128个）
- 摘要信息：每个数据块范围的最小值和最大值
- 查询过程：先查询BRIN索引确定可能包含目标数据的数据块范围，然后扫描这些数据块
7.4.2 适用场景
- 大型表（TB级或更大）
- 时间序列数据（如日志、监控数据）
- 有序数据（如自增ID、按时间排序的数据）
- 低选择性列但有一定顺序的数据
- 写密集型场景，需要最小化索引维护成本
7.4.3 实现方式
PostgreSQL：
-- 创建BRIN索引
CREATE INDEX idx_events_created_at ON events USING BRIN(created_at);
-- 自定义数据块范围大小（默认128）
CREATE INDEX idx_events_created_at ON events USING BRIN(created_at) WITH (pages_per_range = 64);
-- 为多列创建BRIN索引
CREATE INDEX idx_events_created_at_type ON events USING BRIN(created_at, type);
-- 使用BRIN索引查询
SELECT * FROM events WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';
7.4.4 设计和优化策略
- 选择合适的pages_per_range：根据数据分布调整，有序性高的数据可增大范围
- 仅在有序列上使用：BRIN索引对无序数据效果极差
- 结合分区表使用：BRIN索引与分区表结合，可进一步提高查询效率
- 定期维护：使用VACUUM维护索引统计信息
- 监控索引效率：使用EXPLAIN ANALYZE分析查询计划
7.4.5 BRIN vs B+树对比
| 特性 | BRIN索引 | B+树索引 |
|---|---|---|
| 索引大小 | 极小（MB级） | 较大（GB级） |
| 索引创建时间 | 极快 | 较慢 |
| 写操作开销 | 极低 | 较高 |
| 有序数据查询效率 | 良好 | 优秀 |
| 无序数据查询效率 | 极差 | 良好 |
| 精确匹配查询 | 较差 | 优秀 |
| 范围查询 | 良好 | 优秀 |
| 适用表大小 | 大型表（TB级） | 中小型表（GB级） |
- InnoDB默认使用B+树索引
- 支持全文索引和空间索引
- 支持前缀索引
- 复合索引遵循最左前缀原则
- 倒排索引实现：通过FULLTEXT索引实现
- 地理空间索引：支持Spatial索引，基于R树实现
7.5 倒排索引详解
7.5.1 原理
倒排索引（Inverted Index）是一种将文档中的词语映射到文档的索引结构，用于快速定位包含特定词语的文档 [1]。
- 正向索引：文档ID → 文档内容中的词语
- 倒排索引：词语 → 包含该词语的文档ID列表
7.5.2 适用场景
- 全文搜索：如搜索引擎、文档管理系统
- 关键词查询：如商品搜索、内容检索
- 文本分析：如舆情分析、文本分类
7.5.3 实现方式
-
PostgreSQL：
-- 创建tsvector列存储文本向量 ALTER TABLE articles ADD COLUMN tsv tsvector; -- 创建GIN索引 CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv); -- 更新tsvector列 UPDATE articles SET tsv = to_tsvector('english', title || ' ' || content); -- 创建触发器自动更新 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON articles FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger( tsv, 'pg_catalog.english', title, content ); -
MySQL：
-- 创建FULLTEXT索引 CREATE FULLTEXT INDEX idx_articles_title_content ON articles(title, content); -- 使用全文搜索 SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database index'); -
MongoDB：
// 创建text索引 db.articles.createIndex({ title: "text", content: "text" }); // 使用全文搜索 db.articles.find({ $text: { $search: "database index" } });
7.5.4 设计和优化策略
- 选择合适的分词器：根据语言和业务需求选择合适的分词器
- 控制索引字段数量：避免将过多字段纳入倒排索引
- 定期优化索引：删除停用词，优化索引结构
- 使用前缀索引：对于长文本，考虑使用前缀索引
- 结合其他索引：将倒排索引与其他索引结合使用，提高查询效率
7.6 地理空间索引详解
7.6.1 原理
地理空间索引是一种专门用于处理地理空间数据的索引结构，支持点、线、面等地理对象的高效查询 [4]。
常见的地理空间索引结构：
- R树：适用于多维空间数据，支持范围查询
- Quad树：将空间递归划分为四个象限，适用于点数据
- GeoHash：将地理空间编码为字符串，支持近似查询
7.6.2 适用场景
- 位置搜索：如附近的餐厅、酒店
- 地理围栏：如判断点是否在区域内
- 路径规划：如最短路径计算
- 空间分析：如区域统计、缓冲区分析
7.6.3 常见地理类型
- 点（Point）：表示经纬度坐标
- 线（LineString）：表示路径、道路等
- 面（Polygon）：表示区域、边界等
- 多点（MultiPoint）：表示多个点
- 多线（MultiLineString）：表示多条线
- 多面（MultiPolygon）：表示多个面
7.6.4 实现方式
-
PostgreSQL（PostGIS）：
-- 启用PostGIS扩展 CREATE EXTENSION postgis; -- 创建带有点类型的表 CREATE TABLE places ( id SERIAL PRIMARY KEY, name VARCHAR(100), location GEOMETRY(Point, 4326) ); -- 创建地理空间索引 CREATE INDEX idx_places_location ON places USING GIST(location); -- 查询附近的地点 SELECT * FROM places WHERE ST_DWithin(location, ST_MakePoint(116.4074, 39.9042)::geography, 1000); -
MySQL：
-- 创建带有点类型的表 CREATE TABLE places ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), location POINT ) ENGINE=InnoDB; -- 创建空间索引 CREATE SPATIAL INDEX idx_places_location ON places(location); -- 查询附近的地点 SELECT * FROM places WHERE ST_Distance_Sphere(location, POINT(116.4074, 39.9042)) <= 1000; -
MongoDB：
// 创建地理空间索引 db.places.createIndex({ location: "2dsphere" }); // 插入数据 db.places.insertOne({ name: "Beijing", location: { type: "Point", coordinates: [116.4074, 39.9042] } }); // 查询附近的地点 db.places.find({ location: { $near: { $geometry: { type: "Point", coordinates: [116.4074, 39.9042] }, $maxDistance: 1000 } } });
7.6.5 设计和优化策略
- 选择合适的地理类型：根据业务需求选择点、线、面等类型
- 使用合适的坐标系：根据数据范围选择4326（WGS84）或其他坐标系
- 优化查询条件：使用ST_DWithin等高效的空间函数
- 结合其他索引：将地理空间索引与其他索引结合使用
- 定期更新统计信息：确保查询优化器使用正确的索引
- 考虑数据分布：对于分布不均匀的数据，考虑使用分区表
