数据库——索引

目录1. 核心概念2. 核心数据结构BTree3. 两种最重要的索引类型4. 索引语法4.1 创建索引4.2 查看索引4.3 删除索引5. 核心使用原则5.1 最左前缀法则 (联合索引)5.2 索引失效的几种常见情况5.3 覆盖索引6. 索引设计最佳实践7. 执行计划 (EXPLAIN)8. 常用命令速查9. 核心速记口诀1. 核心概念索引是什么帮助MySQL高效获取数据的有序数据结构。核心作用提高查询速度降低排序成本。代价占用额外空间降低增、删、改的效率。2. 核心数据结构BTree默认索引结构InnoDB存储引擎默认使用BTree。BTree的核心特点所有数据都在叶子节点非叶子节点只存索引指针不存数据。叶子节点之间通过双向链表连接便于范围查询。为什么选BTree而不是其他对比二叉树/红黑树层级更少IO次数更少。对比B-Tree非叶子节点不存数据能存放更多索引项树更矮。对比Hash索引BTree支持范围查询和排序。3. 两种最重要的索引类型索引类型叶子节点存储内容特点聚集索引(主键索引)整行数据一个表只有一个一般是主键二级索引(非主键索引)主键的值可以有多个查询可能需要回表回表通过二级索引找到主键ID再到聚集索引查询完整数据的过程。尽量用覆盖索引避免回表。4. 索引语法4.1 创建索引-- 普通索引 CREATE INDEX index_name ON table_name (column1, column2...); ​ -- 唯一索引 CREATE UNIQUE INDEX index_name ON table_name (column); ​ -- 全文索引 CREATE FULLTEXT INDEX index_name ON table_name (column);4.2 查看索引SHOW INDEX FROM table_name;4.3 删除索引DROP INDEX index_name ON table_name;5. 核心使用原则5.1 最左前缀法则 (联合索引)规则查询条件必须从联合索引的最左列开始并且不跳过中间的列否则索引部分或全部失效。关键点执行时字段的书写顺序不影响但字段必须存在。5.2 索引失效的几种常见情况在索引列上进行运算或使用函数。字符串类型字段不加引号发生隐式类型转换。模糊查询时通配符%在开头(例如LIKE %关键词)。使用OR连接的条件如果OR前后的字段并非都有索引。MySQL评估认为全表扫描比使用索引更快例如查询结果是大批量数据。5.3 覆盖索引概念需要查询的所有列都包含在同一个索引中。优势查询数据可直接从索引中获取无需回表极大提升性能。实践尽量只查询必要的字段避免使用SELECT *。6. 索引设计最佳实践推荐做的事 (DO)不建议做的事 (DONT)高频查询、排序、分组的字段上建索引在频繁增删改的字段上建过多索引选择区分度高的列如手机号、身份证号在低区分度的列上建索引如性别、状态使用联合索引并遵循最左前缀法则只为每个查询条件单独建单列索引字符串长字段使用前缀索引在很长的字符串上建完整索引尽量使用覆盖索引避免SELECT *查询返回超过表中大部分数据索引反而慢7. 执行计划 (EXPLAIN)字段重点关注项说明typeref、range、index、ALL性能从好到差。至少要达到range或ref避免ALL(全表扫描)。possible_keys可能用到的索引与key对比看是否用了预期的索引。key实际使用的索引如果为NULL说明没用到索引。ExtraUsing index、Using whereUsing index表示使用了覆盖索引是好的Using filesort、Using temporary是坏现象。8. 常用命令速查操作命令创建普通索引CREATE INDEX idx_name ON table_name(column);创建唯一索引CREATE UNIQUE INDEX idx_name ON table_name(column);查看索引SHOW INDEX FROM table_name;删除索引DROP INDEX idx_name ON table_name;查看执行计划EXPLAIN SELECT ...;9. 核心速记口诀BTree数据全在叶子非叶子只存指针叶子连成链表。聚集索引叶子挂数据一个表一个。二级索引叶子挂主键查询可能回表。覆盖索引索引包所有查询不回表。最左前缀联合索引从左起跳过中间就失效。索引失效运算、隐式转换、%在前、OR无索引、全表更快。