索引是数据库中用于快速查找数据的数据结构，类似书籍的目录，能够避免全表扫描，大幅减少查询时需要扫描的数据量，是优化SQL查询性能最常用也最有效的手段之一。合理的索引设计可以让原本需要几秒甚至几十秒的查询在毫秒级完成，而错误的索引使用反而会增加数据写入的开销，降低整体性能。
索引优化的核心原则
优先为高频查询字段创建索引
首先需要梳理业务中执行频率最高的查询语句，找出这些语句中作为查询条件、连接条件或者排序分组的字段，优先为这些字段创建索引。比如用户表经常需要根据手机号查询用户信息，那么用户表的手机号字段就适合创建索引。
控制索引数量避免冗余
索引并不是越多越好，每个索引都会占用额外的存储空间，并且在执行插入、更新、删除操作时，数据库需要同时维护对应的索引，会增加写操作的开销。对于更新频率极高的字段，不建议创建索引，同时要避免创建功能重复的冗余索引。
适合创建索引的场景
- 经常作为WHERE子句查询条件的字段
- 多表连接查询中使用的连接字段
- 经常用于ORDER BY、GROUP BY操作的字段
- 字段值区分度高的字段，比如用户ID、订单编号，而性别这类只有少数几个值的字段不适合单独创建索引
索引创建与使用示例
MySQL中创建索引
MySQL支持普通索引、唯一索引、联合索引等多种类型，以下是常见的创建语句：
-- 为user表的phone字段创建普通索引 CREATE INDEX idx_user_phone ON user(phone); -- 为user表的id_card字段创建唯一索引，确保身份证号不重复 CREATE UNIQUE INDEX idx_user_id_card ON user(id_card); -- 为order表的user_id和create_time字段创建联合索引，适合查询某个用户的所有订单并按时间排序的场景 CREATE INDEX idx_order_user_time ON `order`(user_id, create_time); -- 查看user表的所有索引 SHOW INDEX FROM user;
PostgreSQL中创建索引
PostgreSQL的索引创建语法和MySQL类似，同时支持更多特殊的索引类型：
-- 为product表的name字段创建普通索引
CREATE INDEX idx_product_name ON product(name);
-- 为article表的content字段创建全文搜索索引
CREATE INDEX idx_article_content ON article USING gin(to_tsvector('chinese', content));
-- 查看product表的索引信息
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'product';
索引使用的常见误区
索引字段参与函数运算
如果在查询条件中对索引字段使用了函数或者运算，索引会失效，比如以下查询不会使用phone字段的索引：
-- 错误示例：对索引字段使用函数，索引失效 SELECT * FROM user WHERE SUBSTR(phone, 1, 3) = '138'; -- 正确示例：避免对索引字段做运算 SELECT * FROM user WHERE phone LIKE '138%';
联合索引不遵循最左前缀原则
联合索引是按照字段创建顺序排序的，查询时必须从联合索引的最左侧字段开始使用，否则索引无法生效。比如创建了idx_order_user_time(user_id, create_time)联合索引，以下查询可以使用索引：
-- 可以使用联合索引的查询 SELECT * FROM `order` WHERE user_id = 10; SELECT * FROM `order` WHERE user_id = 10 AND create_time > '2024-01-01'; -- 无法使用联合索引的查询，缺少最左侧的user_id条件 SELECT * FROM `order` WHERE create_time > '2024-01-01';
模糊查询以通配符开头
使用LIKE进行模糊查询时，如果通配符%放在最前面，索引会失效，比如以下查询无法使用name字段的索引：
-- 索引失效的模糊查询 SELECT * FROM product WHERE name LIKE '%手机%'; -- 可以使用索引的模糊查询 SELECT * FROM product WHERE name LIKE '苹果%';
索引效果验证
创建索引后可以通过数据库的执行计划功能验证索引是否被使用，MySQL中使用EXPLAIN关键字查看执行计划：
-- 查看查询语句的执行计划 EXPLAIN SELECT * FROM user WHERE phone = '13800138000';
执行后如果type列显示为ref、eq_ref或者range，说明索引被正常使用，如果显示为ALL则表示进行了全表扫描，需要检查索引是否合理。
需要注意的是，索引优化只是SQL性能优化的一部分，还需要结合查询语句本身的合理性、数据库参数配置、硬件资源等多方面因素共同调整，才能达到最佳的性能效果。