索引

索引的概念

索引是数据库为了提高数据检索效率而维护的一种数据结构。它类似图书目录，可以帮助数据库减少需要扫描的数据行数。

索引能够提高查询速度，但也会占用额外存储空间，并增加新增、修改和删除数据时的维护成本。因此，索引并不是越多越好，应根据实际查询条件设计。

常见索引类型

主键索引

表的主键会自动创建主键索引。主键值必须唯一且不能为 NULL。

唯一索引

唯一索引用于限制列值不能重复。MySQL 的唯一索引通常允许出现多个 NULL，具体行为还与数据库版本和列定义有关。

普通索引

普通索引主要用于提高查询速度，不负责保证数据唯一性。

复合索引

复合索引由多个列共同组成。例如索引列顺序为 job_id、salary 时，通常可以支持以下查询条件：

where job_id = ?

where job_id = ? and salary >= ?

但仅使用 salary 查询时，通常不能有效利用该复合索引的最左列。这就是复合索引的最左前缀原则。

全文索引

全文索引用于文本内容检索。MySQL 可以结合 MATCH() 和 AGAINST() 进行全文查询，中文全文检索常结合 ngram 分词器使用。

索引的存储结构

MySQL 的 InnoDB 存储引擎主要使用 B+ 树组织普通索引和主键索引。

B+ 树具有以下特点：

数据按键值有序组织。
树的高度较低，适合磁盘和页式存储。
叶子节点之间有序连接，适合范围查询。
查询时可以减少磁盘页访问次数。

哈希结构适合等值查找，但不适合范围查询和排序。InnoDB 的常规索引不是普通哈希索引。

创建和删除索引

创建普通索引

create index employee_index1
on employee(first_name);

创建复合索引

create index employee_index2
on employee(job_id, salary);

创建唯一索引

create unique index employee_index3
on employee(phone_number);

创建全文索引

alter table employee
add address varchar(255);

create fulltext index employee_index_addr
on employee(address)
with parser ngram;

使用全文索引查询

自然语言模式查询：

select address
from employee
where match(address) against('大庆');

布尔模式查询：

select address
from employee
where match(address)
against('+黑龙江 -大庆' in boolean mode);

删除索引

drop index employee_index1 on employee;

也可以通过 ALTER TABLE 删除索引：

alter table employee
drop index employee_index1;

InnoDB 与 MyISAM 的索引差异

InnoDB

InnoDB 使用聚簇索引组织表数据。主键索引的叶子节点保存完整行数据，二级索引的叶子节点保存主键值。

通过二级索引查询非索引列时，数据库通常先从二级索引获得主键，再通过主键索引查找完整行，这个过程通常称为回表。

如果表没有显式主键，InnoDB 会选择合适的唯一非空索引，或创建内部隐藏行标识来组织聚簇索引。因此，InnoDB 表通常应主动设计简洁、稳定的主键。

MyISAM

MyISAM 的索引和数据文件分离。索引叶子节点保存数据记录的物理地址，主键索引和普通索引在数据定位方式上没有 InnoDB 聚簇索引与二级索引的差异。

InnoDB 支持事务和行级锁，是 MySQL 的默认存储引擎。MyISAM 不支持事务，现代业务系统通常优先使用 InnoDB。

使用 `EXPLAIN` 分析查询

可以在查询语句前添加 EXPLAIN 或 DESC，查看优化器选择的执行计划。

explain
select *
from employee
where first_name = 'Tom';

常见字段包括：

字段	说明
`type`	表访问方式，通常从全表扫描到常量查询逐步改善
`possible_keys`	可能使用的索引
`key`	实际选择的索引
`key_len`	使用的索引长度
`rows`	预计扫描的行数
`Extra`	额外执行信息

type 中常见值包括 ALL、index、range、ref、eq_ref、const。一般来说，应重点关注大表上的 ALL 全表扫描，但不能只根据单个字段判断 SQL 是否合理。

eq_ref 常见于连接条件使用主键或唯一非空索引的场景。ref 常见于使用普通索引进行等值查询的场景。

慢查询定位

SQL 优化通常先定位问题，再分析执行计划，最后调整 SQL 或索引。

开启慢查询日志

在 MySQL 配置文件中可以设置慢查询日志。不同系统和安装方式的配置文件位置可能不同。

slow_query_log=ON
slow_query_log_file=WW-slow.log
long_query_time=10

修改配置后通常需要重启 MySQL 服务，或使用相应的动态系统变量。生产环境应根据业务情况设置合理阈值，而不是固定使用十秒。

分析慢 SQL

定位慢 SQL 后，使用 EXPLAIN 检查索引选择、连接顺序、预计扫描行数以及是否出现临时表或额外排序。

SQL 与索引优化原则

合理设计复合索引

复合索引的列顺序会影响可用性。设计时应结合等值条件、范围条件、排序和分组需求，而不是只看单个查询字段。

避免对索引列进行不必要的运算

下面的条件可能使普通索引难以直接定位：

where salary + 1000 > 8000

更合适的写法是：

where salary > 7000

避免对索引列进行不必要的函数处理

where year(create_time) = 2026

可以改写为范围查询：

where create_time >= '2026-01-01'
and create_time < '2027-01-01'

正确理解 `IN` 与 `EXISTS`

IN 不一定比 EXISTS 慢，优化器可能将两者转换为相近的执行计划。应结合数据量、索引和 EXPLAIN 结果选择，而不是机械替换。

谨慎处理 `NULL`

业务含义允许时，可以使用 NOT NULL 和合理默认值简化数据处理。但不能为了索引优化随意用无意义值代替真实的“未知”状态。

避免无意义的全列查询

只查询业务需要的列可以减少网络传输和回表数据量，也有机会形成覆盖索引。

select employee_id, first_name
from employee
where first_name = 'Tom';

选择区分度合适的索引列

大量重复值的列单独建立索引，收益可能较低。前缀索引适合较长字符串，但需要结合区分度和查询需求选择前缀长度。

create index employee_email_prefix
on employee(email(12));

为连接、排序和分组设计索引

连接字段应具有一致的数据类型，并根据查询频率建立索引。外键约束与索引是两个概念，外键用于保证引用完整性，索引用于提高访问效率。InnoDB 创建外键时要求相关列存在可用索引，但不能简单理解为“有外键就一定查询最快”。

避免盲目创建索引

以下列通常不适合单独建立普通索引：

数据量很小的表。
更新频繁且查询很少的列。
区分度极低、且没有其他组合查询价值的列。
从不出现在查询、连接、排序或分组条件中的列。

最终是否需要索引，应以真实查询和执行计划为依据。

索引

索引