索引与性能基础
理解索引的作用与原理
掌握索引的创建、删除与类型
学会用执行计划分析 SQL 性能
核心讲解
（1）索引是什么
索引是数据库的目录，用来加速查询，类似书的目录。
优点：大幅提升查询速度、排序速度
代价：占用磁盘空间、增删改会变慢（更新索引）
原则：经常查询的字段建索引，更新频繁、区分度低的字段不要建
（2）常用索引类型
表格
类型 | 适用场景 |
|---|---|
B 树索引 | 默认，最常用，等值、范围、排序查询 |
唯一索引 | 唯一约束，同时加速查询 |
联合索引 | 多个字段，最左前缀匹配 |
表达式索引 | 基于函数、表达式的查询 |
GIN 索引 | JSON、数组、全文搜索 |
哈希索引 | 等值查询，速度快，不支持范围 |
（3）索引操作
创建：
CREATE [UNIQUE] INDEX 索引名 ON 表名(字段);删除：
DROP INDEX 索引名;查看：
\d 表名可以看到索引
（4）执行计划 EXPLAIN
查看 SQL 的执行计划，判断有没有走索引，扫描方式，是优化 SQL 的核心工具。
EXPLAIN ANALYZE SELECT * FROM 表 WHERE 条件;
Seq Scan：全表扫描，没走索引，数据量大时慢
Index Scan：索引扫描，正常走索引
看 cost 值：越小越快
代码示例
-- 准备测试表，插入1万条测试数据
CREATE TABLE test_index (
id serial PRIMARY KEY,
name varchar(50),
age int,
city varchar(50)
);
-- 批量插入测试数据
INSERT INTO test_index(name, age, city)
SELECT
'user_' || i,
floor(random() * 60 + 18),
(ARRAY['北京','上海','广州','深圳','成都'])[floor(random() * 5 + 1)::int]
FROM generate_series(1, 10000) i;
-- 1. 没建索引前查询，看执行计划
EXPLAIN ANALYZE SELECT * FROM test_index WHERE name = 'user_5000';
-- Seq Scan，全表扫描
-- 2. 创建索引
CREATE INDEX idx_name ON test_index(name);
-- 3. 再看执行计划
EXPLAIN ANALYZE SELECT * FROM test_index WHERE name = 'user_5000';
-- Index Scan，索引扫描，速度大幅提升
-- 4. 联合索引
CREATE INDEX idx_city_age ON test_index(city, age);
-- 5. 查看表上所有索引
\d test_index
-- 6. 删除索引
DROP INDEX idx_name;
常见误区
索引建得越多越好：索引不是越多越好，增删改会变慢，按需创建
联合索引不遵循最左前缀：联合索引 (a,b,c)，查 b、查 c 都走不了索引，必须从左边开始
模糊查询 % 在前面也走索引：
%xxx开头的模糊查询不走 B 树索引，要用全文索引只要有索引就一定快：数据量小的时候，全表扫描可能比索引还快
课后练习
给学生表的姓名字段创建索引
用 EXPLAIN 分析查询语句，对比建索引前后的成本
创建一个城市 + 年龄的联合索引，验证最左前缀原则
思考：什么情况下索引会失效？
