前言
MySQL 索引的设计直接关系到数据库的运行效率。合理的索引能让检索速度产生质的飞跃,反之则可能成为系统瓶颈。我们可以把索引想象成字典的目录页,通过拼音、笔画等排序规则,快速定位到目标内容,而无需逐字翻阅。
索引本质上也是一张表,它保存了主键与索引字段,并指向实体表的记录。虽然索引能大幅提升查询性能,但并非越多越好。过多的索引会增加维护成本,降低 INSERT、UPDATE 和 DELETE 的速度,因为每次数据变更都需要同步更新索引文件,同时也会占用额外的磁盘空间。
索引基础与类型
索引主要用于快速找出在某个列中有一特定值的行。如果不使用索引,MySQL 必须从第一条记录开始扫描整个表,直到找到相关行。对于大表而言,这种全表扫描是灾难性的。有了索引,MySQL 能快速定位到数据文件的中间位置进行搜寻。
大多数 MySQL 索引(PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT)存储在 B+ 树结构中。仅空间列类型的索引使用 R-树,而 MEMORY 表支持 Hash 索引。
单列与组合索引
- 单列索引:一个索引只包含单个列。一个表可以有多个单列索引,但它们彼此独立。
- 组合索引:一个索引包含多个列。创建时通常作为 WHERE 子句的条件使用。
索引设计与选型原则
在实际开发中,如何决定哪些字段需要建索引是一门学问。
1. 区分度是关键
一般来说,列的值唯一性太小不适合建索引。例如性别、状态类型等字段。如果同值的数据超过表的 15%,建立索引的意义就不大了,因为优化器可能会认为全表扫描更快。
2. 字段长度与类型
- Text 类型:可以建索引,但通常需要指定长度前缀。
- 存储引擎限制:MyISAM 存储引擎索引键长度综合不能超过 1000 字节。
- 数据类型匹配:用来筛选的值尽量保持和索引列同样的数据类型。例如手机号存为字符串时,查询时必须加引号,否则无法命中索引。
-- 错误示例:mobile 是 varchar 类型,直接传数字会导致索引失效
SELECT * FROM test WHERE mobile = 13711112222;
-- 正确示例
SELECT * FROM test WHERE mobile = '13711112222';
3. 联合索引的顺序
一次查询只能用到一个索引。如果是多列查询,首先考虑谁的区别度更高(同值最少),将其放在联合索引的前面。例如 AB 联合索引,A 在前还是 B 在前取决于 A 和 B 的区分度。通常情况下,有 AB 联合索引后,就不需要再单独建 A 索引了。
查询优化与常见陷阱
理解了索引原理,还需要注意查询语句的写法,避免'自废武功'。
1. LIKE 模糊查询
尽量减少 LIKE 的使用,但不是绝对不可用。
xxxx%:可以使用索引(前缀匹配)。%xxxx%:无法使用索引(全表扫描)。
其他可用索引的操作符包括:<, <=, =, >, >=, BETWEEN, IN, <>。而不等于 != 或 NOT IN 有时会导致索引失效。


