1. 索引的基本概念
- 定义:索引是数据库中用于快速定位数据的有序数据结构。
- 作用:加速数据检索,减少查询时间。
2. 索引的分类与结构
- 单列索引:基于单个列的索引,如:
CREATE INDEX idx_employee_id ON employees(employee_id);
- 复合索引:基于多列的索引,列顺序影响查询效率,如:
CREATE INDEX idx_last_name_first_name ON employees(last_name, first_name);
- 唯一索引:保证数据唯一性,如:
CREATE UNIQUE INDEX idx_unique_email ON users(email);
- 全文索引:适用于文本搜索,如MySQL的FULLTEXT索引:
ALTER TABLE articles ADD FULLTEXT(idx_article_content, content);
- 空间索引:适用于地理空间数据,如PostGIS的GIST索引:
CREATE INDEX idx_spatial ON locations USING GIST (geom);
3. 索引失效的原因
- 索引选择不当:如对低选择性列创建索引。
- 查询条件不匹配:如使用
LIKE '%value'
导致无法使用索引。
4. 索引优化策略
- 选择性分析:选择高选择性列创建索引,如:
SELECT column_name, COUNT(*) / (SELECT COUNT(DISTINCT column_name) FROM table_name) AS selectivity FROM table_name;
- 复合索引优化:根据查询模式设计复合索引。
- 避免冗余索引:删除不常用或重复的索引。
5. 回表查询
- 定义:使用索引查找到数据后,需要回原表检索其他列数据的过程。
- 示例:假设有一个非覆盖索引
idx_last_name
,查询如下:
数据库首先通过SELECT first_name, email FROM employees WHERE last_name = 'Smith';
idx_last_name
找到所有姓Smith的员工的ID,然后回表查询以获取first_name
和email
。
6. 覆盖索引
- 定义:索引包含查询所需的所有列,避免了回表查询。
- 示例:创建覆盖索引
idx_last_name_email
,查询可以直接从索引中获取数据:CREATE INDEX idx_last_name_email ON employees(last_name, email); SELECT last_name, email FROM employees WHERE last_name = 'Smith';
7. 使用场景
- 大数据量查询:索引显著提高查询效率。
- 频繁查询操作:索引加快响应速度。
8. 索引的特点和优点
- 快速定位:索引允许快速访问数据。
- 提高性能:减少数据访问量,提高查询性能。
9. 索引的缺点
- 写操作成本:增加数据变更时的开销。
- 存储空间占用:索引占用额外存储空间。
10. 优化实例
- 创建索引:
CREATE INDEX idx_department ON employees(department);
- 覆盖索引:减少回表查询,如:
CREATE INDEX idx_cover ON employees(department, salary); SELECT department, salary FROM employees WHERE department = 'Sales';
11. 维护索引
- 重建索引:定期优化索引,如:
ALTER INDEX idx_department REBUILD;
12. 监控性能
- 使用数据库监控工具跟踪索引使用情况和回表查询的频率。