MySQL 索引速通指南：从原理到面试

序言本篇文章是我学习 MySQL 索引时整理的简洁版笔记主要面向初学和面试复习。内容不会过度深入源码而是围绕索引的基本概念、底层结构、使用方式、失效场景和常见面试题展开。一、初识索引1.1 索引是什么索引可以理解为数据库中的“目录”。如果没有索引MySQL 查询数据时可能需要从第一行开始一行一行扫描也就是全表扫描。如果有索引MySQL 就可以根据索引快速定位到数据减少扫描的数据量提高查询效率。简单来说索引的作用提高查询效率 索引的代价占用额外空间降低写入、更新、删除效率二、索引的优缺点2.1 优点提高查询效率。减少数据库扫描的数据量。可以加速排序和分组。唯一索引可以保证数据唯一性。2.2 缺点索引会占用额外的磁盘空间。插入、更新、删除数据时需要维护索引写入性能会下降。索引不是越多越好过多索引会增加维护成本。三、索引的底层结构3.1 为什么不用普通二叉树普通二叉树在极端情况下可能退化成链表查询效率会变低。例如数据按顺序插入时树可能变成这样1 \ 2 \ 3 \ 4这种情况下查询效率接近全表扫描。3.2 为什么不用红黑树红黑树虽然可以保持平衡但是它是二叉树。数据量很大时树的高度仍然可能比较高。树越高查询时需要的磁盘 IO 次数就越多。MySQL 数据通常存储在磁盘中磁盘 IO 比内存操作慢很多所以 MySQL 更希望树的高度尽可能低。3.3 为什么不用 HashHash 查询等值条件很快。例如select * from user where id 1;Hash 可以快速定位。但是 Hash 有几个明显问题不支持范围查询。select * from user where id 100;不支持排序。select * from user order by id;不适合最左前缀匹配。哈希冲突需要额外处理。MySQL 中很多查询都不是单纯的等值查询还包括范围查询、排序、分组、联合索引匹配等场景。所以 Hash 不适合作为 InnoDB 的主要索引结构。3.4 为什么不用 B 树B 树是一种多路平衡搜索树。相比二叉树B 树一个节点可以存储多个 key也可以有多个子节点因此树的高度更低。但是 B 树的节点中既存索引也存数据。这会导致一个页中能存放的索引数量变少树的分叉数量也会减少树的高度可能变高。而 MySQL 更希望一次磁盘 IO 能读取尽可能多的索引项从而减少磁盘 IO 次数。所以 InnoDB 最终使用的是 B 树而不是普通 B 树。3.5 B 树的优势MySQL InnoDB 默认使用 B 树作为索引结构。B 树的特点非叶子节点只存储索引不存储完整数据。叶子节点存储完整数据或者主键值。叶子节点之间通过指针连接适合范围查询。树的高度较低可以减少磁盘 IO 次数。四、InnoDB 中的索引4.1 聚簇索引聚簇索引就是数据和索引放在一起。InnoDB 中主键索引就是聚簇索引。叶子节点保存的是完整的一行数据。如果表中有主键InnoDB 会使用主键作为聚簇索引。如果没有主键InnoDB 会选择一个唯一非空索引。如果还没有InnoDB 会自动生成一个隐藏字段作为聚簇索引。4.2 二级索引除了主键索引以外其他索引都可以理解为二级索引也叫辅助索引。二级索引的叶子节点保存的不是完整数据而是主键值。所以通过二级索引查询数据时可能需要先找到主键再根据主键去聚簇索引中查完整数据。这个过程叫做回表。4.3 回表回表就是通过二级索引找到主键后再根据主键去聚簇索引中查询完整数据。例如select * from user where name 张三;如果name字段有索引MySQL 会先通过name索引找到对应的主键 ID然后再根据 ID 查询完整数据。如果查询的字段只在索引中就能拿到就不需要回表。4.4 覆盖索引覆盖索引指的是查询需要的字段都可以从索引中直接获取不需要回表。例如有一个联合索引(name, age)查询select name, age from user where name 张三;这时查询字段name和age都在索引中可以直接从索引中拿到不需要回表。五、索引的分类5.1 主键索引主键索引是一种特殊的唯一索引不能为 null。primary key(id)5.2 唯一索引唯一索引用来保证字段值不能重复。unique key uk_phone(phone)5.3 普通索引普通索引只用于提高查询效率不限制字段值是否重复。index idx_name(name)5.4 联合索引联合索引是多个字段组成的索引。index idx_name_age(name, age)联合索引需要重点理解最左前缀原则。六、联合索引和最左前缀原则6.1 什么是最左前缀原则联合索引会按照字段顺序建立索引。例如index idx_name_age_address(name, age, address)这个索引可以支持where name ? where name ? and age ? where name ? and age ? and address ?但是不能很好支持where age ? where address ? where age ? and address ?因为没有从最左边的name字段开始使用。6.2 为什么要遵守最左前缀联合索引底层会先按照第一个字段排序。第一个字段相同再按照第二个字段排序。第二个字段相同再按照第三个字段排序。所以如果跳过最左边的字段后面的字段就无法直接利用索引的有序性。七、索引失效场景7.1 对索引列使用函数select * from user where substring(phone, 1, 3) 138;可能导致索引失效。7.2 对索引列进行计算select * from user where age 1 18;可能导致索引失效。7.3 字符串不加引号select * from user where phone 13800138000;如果phone是字符串类型不加引号可能发生隐式类型转换导致索引失效。7.4 like 以 % 开头select * from user where name like %三;前面有%时索引可能失效。如果是select * from user where name like 张%;一般可以使用索引。7.5 or 使用不当如果or前后的字段不是都有索引可能导致索引失效。7.6 违反最左前缀原则联合索引没有从最左字段开始使用也可能导致索引失效。八、如何查看索引是否生效可以使用EXPLAIN查看 SQL 执行计划。例如explain select * from user where name 张三;重点看几个字段type访问类型常见有 all、index、range、ref、const 等。possible_keys可能使用的索引。key实际使用的索引。rows预估扫描的行数。Extra额外信息比如是否使用覆盖索引、是否需要临时表、是否文件排序。一般来说type越好、rows越少SQL 性能越好。九、索引设计原则9.1 给查询频繁的字段加索引经常出现在where、order by、group by后面的字段可以考虑加索引。9.2 区分度高的字段适合加索引比如手机号、用户 ID 这种区分度高的字段适合加索引。性别这种字段区分度很低一般不适合单独加索引。9.3 尽量使用联合索引多个条件经常一起查询时可以建立联合索引。9.4 避免索引过多索引不是越多越好。索引越多写入、更新、删除时维护成本越高。9.5 尽量使用覆盖索引如果查询字段都能从索引中拿到就可以避免回表提高查询效率。十、面试高频问题10.1 MySQL 索引是什么索引可以理解为数据库中的目录用来提高查询效率。没有索引时MySQL 可能需要全表扫描有索引时可以根据索引快速定位数据。10.2 索引为什么能提高查询效率因为索引底层使用了更适合查找的数据结构比如 B 树可以减少扫描的数据量和磁盘 IO 次数。10.3 InnoDB 为什么使用 B 树因为 B 树树高低磁盘 IO 次数少并且叶子节点之间有链表连接适合范围查询。10.4 什么是聚簇索引InnoDB 中主键索引就是聚簇索引。聚簇索引的叶子节点保存完整的一行数据。10.5 什么是回表通过二级索引查询时先找到主键值再根据主键去聚簇索引中查询完整数据这个过程叫回表。10.6 什么是覆盖索引查询需要的字段都在索引中不需要再回表查询完整数据这就是覆盖索引。10.7 什么是最左前缀原则联合索引需要从最左边的字段开始使用。如果跳过最左字段后面的字段可能无法使用索引。10.8 索引一定会生效吗不一定。比如对索引字段使用函数、计算、隐式类型转换、like %xxx、违反最左前缀原则等都可能导致索引失效。10.9 索引是不是越多越好不是。索引会占用空间并且插入、更新、删除时需要维护索引。索引过多反而会影响写入性能。10.10 如何判断 SQL 有没有走索引可以使用EXPLAIN查看执行计划重点看key字段是否实际使用了索引以及type、rows、Extra等字段。