目录一、索引为什么一定要用索引二、为什么 MySQL 偏偏选 B树1.HASH哈希2.二叉搜索树BST / AVL / 红黑树3.N 叉树4.B 树最终答案B 树 vs B 树强在哪三、深挖底层MySQL 中的页Page1.为什么要用页2.页文件头和页文件尾3.页主体4.页目录从从头遍历到二分查找5.数据页头四、B 树在 MySQL 索引中的应用1.检索过程一次查询要走几步编辑2.算一笔账三层树高的 B 树能存多少数据五、六种索引分类聚集索引 vs 非聚集索引重点理解这两个概念引出两个高频面试词回表 与 索引覆盖六、索引的使用自动 vs 手动1.自动创建2.手动创建3.创建复合索引多列逗号隔开4.查看索引5.删除索引七、创建索引的注意事项一、索引MySQL 的索引本质是一种数据结构用来帮助数据库高效地查询、更新表中的数据。它按一定规则把表里的记录排好序这样查询时只要在索引里找而不是把整张表翻一遍。最形象的类比就是字典目录汉语字典前面有按笔画、偏旁部首、拼音排序的目录页。你要查一个字不会从第一页翻到最后一页而是先翻目录定位到这个字大概在哪一页再跳过去。索引就扮演了这个目录的角色——它指向数据行所在的位置帮我们快速定位。为什么一定要用索引显而易见使用索引的目的只有一个就是提升数据检索的效率在应用程序的运行过程中查询操作的频率远远高于增删改的频率。二、为什么 MySQL 偏偏选 B树1.HASH哈希优点理论上查询时间复杂度是O(1)极快等值查找无敌。缺点查找的时候只能进行相等匹配不能进行 或者范围匹配2.二叉搜索树BST / AVL / 红黑树二叉搜索树中序遍历出来是有序数组听起来不错但有几个硬伤最坏情况退化成链表插入数据恰好有序时树高变成 N时间复杂度退化为O(N)。节点个数多树高不可控哪怕用 AVL、红黑树保持平衡它俩毕竟是二叉树一个节点最多两个子节点数据一多树还是很高。致命的 IO 问题索引数据持久化存储在磁盘中每向下访问一个子节点就可能触发一次磁盘 IO。而磁盘读写速度远慢于内存访问是数据库的核心性能瓶颈。树的高度越高查询途经的节点越多IO 次数就越多查询性能也就越差。3.N 叉树为了解决树高问题很自然的思路就是——一个节点多存几个子节点即N 叉树。相同数据量下N 叉树的树高被有效压低了也就意味着减少磁盘 IO 次数从而提升效率。但 MySQL 认为 N 叉树还不够好最后选择了它的进阶版——B 树。4.B 树最终答案B 树是常用于数据库和文件系统的平衡查找树也是MySQL 索引实际采用的数据结构。B 树的三个核心特点特点说明数据稳定有序能保持数据有序插入和修改的时间复杂度稳定非叶子只做索引非叶子节点仅存储索引key不存真实数据所有真实数据都保存在叶子节点叶子组成有序链表所有叶子节点构成一个有序链表可按 key 排序依次遍历全部数据B 树 vs B 树强在哪对比项B 树B 树非叶子节点也存数据只存索引不存数据 → 一页能装更多 key树更矮叶子节点数据分散在各层连续且互相链接便于区间查找和搜索查找性能有的层有数据、有的没有不均匀相同树高下查找任一元素的时间复杂度一样性能均衡非叶子值归属数据只存一处非叶子节点的值都包含在叶子节点中查询路径统一一句话记住B 树把索引和数据彻底分离——上层只做路标真正的内容全部堆在底层叶子再用链表串起来。这让它既矮IO 少又支持范围查询链表好遍历。三、深挖底层MySQL 中的页Page讲 B 树光看树形结构是虚的得落到 MySQL 物理存储上。这里有个关键概念——页。1.为什么要用页页是内存与磁盘交互的最小单元默认大小 16KB。每次内存和磁盘交互至少读取一页。为什么一次要读 16KB 这么大一块核心依据是局部性原理时间局部性某个数据刚被访问过近期很可能再次被访问。空间局部性将来要用的数据大概率与正在使用的数据在空间地址上临近。所以一次读一整页进内存当下次查询的数据还在这页里时就能直接从内存读取减少磁盘 IO性能自然提升。查看页大小的命令SHOW VARIABLES LIKE innodb_page_size;在MySQL中有多种不同类型的页最常⽤的就是⽤来存储数据和索引的索引⻚也叫做数据页数据页的基本结构如下图所示2.页文件头和页文件尾不论哪种类型的页都包含页头File Header和页尾File Trailer主体用数据行填充。整体结构大概是页头 / 页尾包含元信息。我们只关注两个——上一页页号、下一页页号靠它们把页与页链接起来形成双向链表。3.页主体保存真实数据的主要区域。一个新页会自动分配页内最小行和页内最大行作为链表的头和尾。中间的每行记录下一行的地址偏移量从而组成一个单向链表。数据按主键从小到大的顺序链接。4.页目录从从头遍历到二分查找如果按主键找一条数据最笨的方法是从头行开始沿链表逐个比对。但一个页有 16KB通常有几百行数据每次遍历几百行效率太低。InnoDB 的优化是在每个页里加一个页目录结构——把页内所有行含头行、尾行分组头行单独一组其他每组最多8 条把每组最后一行在页中的地址按主键从小到大记录在页目录里页目录里的每个位置叫一个槽每个槽对应一个分组组内超过 8 行就分裂出新分组。查询某行时先用二分查找定位到对应的槽再在槽内最多 8 行里遍历。这样查询效率大幅提升。举例要找主键为 6 的行先比对各槽记录的主键值定位到槽 2再从槽 2 的第一条记录开始遍历第二条就是目标行。5.数据页头数据页头记录了当前页保存数据相关的信息如下图所示四、B 树在 MySQL 索引中的应用1.检索过程一次查询要走几步在 InnoDB 里非叶子节点保存索引数据叶子节点保存真实数据。假设要找 id 5 的记录完整流程是先看 B 树根节点的索引记录5 小于某个分界值 → 访问左孩子加载索引页 2在索引页 2 里判断 id 大小找到等于 5 的记录命中加载对应的数据页。三次磁盘 IO 的过程加载索引页1 → 加载索引页2 → 加载数据页3。2.算一笔账三层树高的 B 树能存多少数据数据页叶子节点假设单行数据占 1KB忽略页头页尾开销一页 16KB 大约可以存 16 条完整数据。索引页非叶子节点每条索引项 主键BIGINT 占 8 字节 下一页地址6 字节 14 字节。一页 16KB 大约可以存16×1024 ÷ 14 ≈ 1170条索引记录。于一棵三层的 B 树根节点 一层索引节点 叶子节点根节点可以指向约 1170 个二级索引页每个二级索引页可以指向约 1170 个数据页总数据量 1170 × 1170 × 16 ≈2190 万条。这就是矮树的回报IO 次数基本等于树高。树矮 IO 少 快。两千万数据只要 3 次 IO性能自然起飞。五、六种索引分类索引类型说明关键点主键索引表定义了主键InnoDB 就用它做聚集索引推荐每个表都定义主键没有合适的就用自增列普通索引最基本的索引没有唯一性限制可为多列创建此时叫复合索引/组合索引唯一索引与普通索引类似但列不允许重复值表定义了唯一键时自动创建全文索引基于文本列CHAR/VARCHAR/TEXT创建用于全文搜索仅 MyISAM 和 InnoDB 支持聚集索引与主键索引基本是同义词数据行的物理顺序与索引顺序一致索引即数据非聚集索引聚集索引以外的索引也叫二级索引每条记录包含主键列 二级索引指定列聚集索引 vs 非聚集索引重点理解这两个概念聚集索引主键索引叶子节点直接保存真实数据行。表没有主键时InnoDB 会用第一个非空唯一列作为聚集索引再没有就生成一个 6 字节、单调递增的隐藏行号当索引。非聚集索引二级索引叶子节点保存的是主键列 二级索引指定的列而不是真实数据。引出两个高频面试词回表 与 索引覆盖回表查询通过二级索引查到主键后再用主键去聚集索引里走一遍拿真实数据这个过程就叫回表。多了一次 IO。索引覆盖如果一条 SELECT 用了普通索引且查询的列恰好都在这个索引里那么不用回表直接从索引返回数据。这叫索引覆盖是优化查询的常用手段。实战感悟设计索引时如果能把高频查询的列塞进同一个复合索引里让查询走索引覆盖、避免回表是性价比非常高的优化点。六、索引的使用自动 vs 手动1.自动创建给表加主键约束、外键约束、唯一约束时MySQL 会为对应列自动创建索引。如果表没指定任何约束MySQL 也会为每列自动生成一个索引并用行号标识。2.手动创建① 主键索引# 方式一创建表时创建主键 create table t_test_pk ( id bigint primary key auto_increment,-- 这里的 primary key (id) 同时创建了主键约束 主键索引 name varchar(20) ); # 方式二创建表时单独指定主键列 create table t_test_pk1 ( id bigint auto_increment, name varchar(20), primary key (id)-- 这里的 primary key (id) 同时创建了主键约束 主键索引 ); # 方式三建表后通过ALTER语句追加创建 create table t_test_pk2 ( id bigint, name varchar(20) ); alter table t_test_pk2 add primary key (id) ; alter table t_test_pk2 modify id bigint auto_increment;② 唯一索引# 方式一 创建表时创建唯一键 create table t_test_uk ( id bigint primary key auto_increment, name varchar(20) unique ); 方式二 创建表时单独指定唯一列 create table t_test_uk1 ( id bigint primary key auto_increment, name varchar(20), unique (name) ); 方式三建表后修改表中的列为唯一索引 create table t_test_uk2 ( id bigint primary key auto_increment, name varchar(20) ); alter table t_test_uk2 add unique (name) ;③ 普通索引# 方式一创建表时指定索引列 create table t_test_index ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(10), index(sno) ); #方式二修改表中的列为普通索引 create table t_test_index1 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10) ); alter table t_test_index1 add index (sno) ; # 方式三单独创建索引并指定索引名 create table t_test_index2 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10) ); create index index_name on t_test_index2(sno);3.创建复合索引多列逗号隔开创建语法与创建普通索引相同只不过指定多个列列与列之间用逗号隔开# 方式一创建表时指定索引列 create table t_test_index4 ( id bigint primary key auto_increment,-- 主键索引单字段不是复合 name varchar(20), sno varchar(10), class_id bigint, index (sno, class_id)-- 这里就是复合索引一个索引包含 sno class_id 两个字段 ); # 方式二修改表中的列为复合索引 create table t_test_index5 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10), class_id bigint ); alter table t_test_index5 add index (sno, class_id); # 方式三单独创建索引并指定索引名 create table t_test_index6 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10), class_id bigint ); create index index_name on t_test_index6 (sno, class_id);--index_name复合索引有sno, class_id 2个字段4.查看索引# 方式一show keys from 表名 show keys from t_test_index6; # 方式二 show index from t_test_index6; # 方式三简要信息desc 表名; desc t_test_index6;5.删除索引①删除主键注意若列有自增属性需先去掉alter table 表名 drop primary key;例子②删除其他索引alter table 表名 drop index 索引名踩坑提醒删除主键时如果该列是 AUTO_INCREMENT会报错 ERROR 1075不能只有一个自增列且必须定义为主键。正确顺序是先去掉自增属性再删主键。七、创建索引的注意事项加在高频查询的列上索引要建在经常被 WHERE、JOIN、ORDER BY 用到的列而不是随手建。索引占额外存储空间每个索引都是一棵 B 树都要占磁盘空间。增删改会同时维护索引每次插入、更新、删除都要同步修改索引可能影响写性能。过犹不及创建过多或不合理的索引会导致性能下降需要谨慎选择和规划。关于索引的优化与使用细节最左前缀、覆盖索引的实战、EXPLAIN 分析等留到 MySQL 中级课程再深入。
