前言索引是MySQL数据库性能优化的核心手段合理设计索引可以显著降低查询IO开销提升检索速度。本文将从索引基础概念出发介绍索引底层数据结构重点讲解B树在MySQL索引中的应用同时讲解索引的分类与实际使用方法一.简介1.什么是索引MySQL中的索引是一种数据结构帮助数据库高效查询更新数据索引通过一定的规则排列数据表中的记录MySQL的索引就类似书籍的目录通过目录可以帮助我们快速的定位的需要的内容或者就像字典中的拼音和偏旁部首可以通过这些迅速的找到我们要的字举一个字典的例子帮助大家迅速的理解笔画索引偏旁部首索引拼音索引2.为什么要使用索引非常明显使用索引的原因只有一个提升查询效率二.索引应该使用哪些数据结构1.HASH时间复杂度 O1HASH查找的效率非常高但是不适合范围查找因为hash索引只能快速找到等于某个值的数据存放的顺序也是乱的所以只适合一对一的查询MySQL中使用范围查询比较多所以一般不用HASH2.二叉搜索树时间复杂度 最坏O(N)(单边树)由于二叉搜索树的特性中序遍历出来的数据是有序的所以适合范围查找但是节点过多时无法保持树高这就会导致树很高的情况下IO次数非常多由于数据库的数据都是保存在磁盘上的所以每一次访问子节点都要进行一次磁盘的IO我们又知道磁盘的IO效率跟内存的IO比起来是非常非常慢的导致极大限制了数据库的性能所以二叉搜索树不适合作为索引的数据结构磁盘的IO是限制数据库性能的主要原因3.N叉树B树时间复杂度 ON由于B树一个节点可以有多个子节点解决了树高的问题那么就减少了磁盘IO的次数大大提高查询的效率。但是MySQL认为B树还是不够高效4.B树B树是⼀种经常用于数据库和文件系统等场合的平衡查找树MySQL索引采⽤的数据结构以4阶 B树为例如下图所示B树和B树的对比1.叶子节点之间有一个相互连接的引用可以通过叶子节点找到邻近的兄弟节点B树的组织叶子节点使用的是单向链表MySQL对其进行了优化使用的是双向链表2.非叶子节点的值全部包含于叶子节点MySQL的B树中的非叶子节点只保存了对子节点的引用并没有保存真实的数据真实数据全保存在叶子节点3.对于B树而言在相同树高的情况下查找任一元素的时间复杂度的相同的性能均衡由于全部的真实数据全部都在叶子节点中所以要查询时一定是走到了叶子节点。MySQL中 B 树对比B树的三大优点1.B树中非叶子节点只放key不保存真实数据真实数据的大小远大于key节点可以保存更多的索引信息降低了树高磁盘的IO次数更少2.B树中叶子节点用双向链表组织查询更加高效。B树查询数据是在树中进行随机的IO而B树查询数据是在链表中进行顺序IO在磁盘中顺序IO的效率远高于随机IO3.B树查询数据一定会走到叶子节点查询IO次数稳定性能均衡B树可能在非叶子节点就命中了IO次数时高时低三.MySQL中的页1.为什么要使用页在.ibd文件中Page页是最重要的结构体是磁盘和内存交互的最小单位默认大小为16KB每次磁盘和内存交互时最少读取一页所以磁盘中页内的地址是连续的。为什么要直接读取一页呢这是因为在使用数据时根据局部性原理这次使用的数据以及和这次数据相邻的数据下一次大概率都会用到如果发现下次要用的数据也在本页中就可以使用减少了IO次数.ibd文件每创建一个保存数据的文件就称作独立表空间文件。简单来说就是你创建了一张表就会有一个.ibd文件而页就是存在于这个.ibd文件中的在MySQL中页的类型有很多在这边我们只讨论索引页和数据页。2.页文件头和页文件尾页文件头和页文件尾所包含的信息如下图所示在这边我们可以只关注上一页页号和下一页页号就是有这两个属性将页与页连接起来形成双向链表就算页中的数据没有数据也会使用16KB同时与索引的B树的节点相对应简单来说页就是一个节点3.页主体页主体是保存真实数据的区域每创建一个页都会自动分配两行一个为最小行Infimun一个为最大行Supremun这两个行不保存真实数据只是作为链头和链尾每一行有next_record区域来保存下一行的地址偏移量这样就能将页内的数据组织为一个单向链表每当有一行新数据插入Infimun连接第一个数据行最后一个真实数据行连接Supremun这样就形成了一个单向链表随着插入的数据变多这些数据会按照主键的大小顺序进行连接4.页目录在页内查找数据最简单的方法就是从Infimun依次往下遍历但是每一页的数据是比较多的这样查找的效率显示比较低下所以会为这些数据进行分组而每创建一个分组就会创建一个槽保存在页目录当中分组规则1.最小行Infimun单独一组2.每一组最多八个数据3.最大行Supremun一定在最后一个分组注意事项1.创建一个分组的同时就会在页目录中创建一个槽2.分组的数量和槽是一样的3.槽会指定每个分组的最后一条记录并保存这条记录的主键值5.页数据头了解即可数据页头记录了当前页保存数据相关的信息如下图所示四.B树在MySQL索引中的使用非叶子节点保存索引信息叶子节点保存真实数据如下图所示例找出id5的记录1.比较根节点的索引记录 7 5应访问左孩子节点也就是索引页22.在索引页2中判断大小找到与5相等的记录命中加载对应的数据页此过程中IO次数为三次加载索引页3 - 加载索引页2 - 加载数据页3说到这里大家可能还对B树的效率有什么概念在这边举个例子假设一条真实数据大小1KB数据页16KB也就是能存16条记录索引页中索引记录用数值类型为bigint 8byte下一页的索引 6byte8 6 14 byte那么一页索引页就能保存 1024 * 16 / 14 1170条索引记录那么三层树高的B树就能保存 1170 * 1170 * 16 大概两千多万条数据这么多的数据只需要三次IO就能找到所以说B树的效率是非常高的五.索引分类1.主键索引当在表中定义了主键就会自动创建主键索引索引键的值就是主键的值InnoDB会用其作为聚集索引聚簇索引2.普通索引为了提高效率要为平常查询频繁的列去创建索引可以包含一个或者多个包含多个称为复合索引也叫组全索引3.唯一索引表中定义唯一键时就会自动创建和普通索引一样只不过不允许有重复值4.全文索引基于文本列(CHAR、VARCHAR或TEXT列)上创建以加快对这些列中包含的数据查询和DML操作5.聚集索引有主键索引时InnoDB会用主键索引作为聚集索引 可以理解为主键索引是聚集索引的一种-如果没有主键InnoDB会用第一个使用UNIQUE和NOT NULL的列缺一不可作为聚集索引 -如果都没有InnoDB会为每个新插入的行添加一个行号并用ROW_ID这个隐藏列来标识ROW_ID单调递增并用其作为聚集索引这就是为什么在创建表时就算我们没有指定索引查询出来的结果集也是有一个固定顺序的原因6.非聚集索引除了聚集索引以外的索引都叫非聚集索引或二级索引二级索引中的每条记录都包含了改行的主键列就是说在索引记录中不仅包含了该索引键和下一页的引用还包含了该行的主键值7.回表查询和索引覆盖回表查询二级索引的查询过程1.通过索引键去找到数据页中的用户记录2.通过用户记录中主键值访问主键索引树找到相应的完整记录上述现象叫做回表查询就是需要查询的信息在二级索引树中有缺失就要依靠主键值继续回到主键索引树找到完整的信息索引覆盖在二级索引树查询的过程中所需要的信息刚好是创建二级索引所包含的部分列或者所有列就可以直接返回数据不需要再回表此现象叫做索引覆盖举例此时有张student表 主键为id复合索引为name 和 sno学号草图如下这边草图的索引页和数据页看上去好像一样但是索引页是没有真实数据的而数据页保存的是真实数据大家不要被我的草图误导了selectsnfrom student where name 张三;sn是我们需要通过索引查询的列已经包含在复合索引当中也就是说sno这个数据是保存在这个二级索引树的数据页中所以就不用再通过主键值回表到主键索引树去查询了这个就叫做索引覆盖select*from student where name 张三;如果student还有别的列那么只通过二级索引树中数据页的数据是不完整的所以会通过主键值回表到主键索引树去查询到完整的记录这就叫做回表查询注意事项1.创建索引时就会生成对应的索引树也是占用磁盘空间的2.创建索引时要慎重考虑需不需要3.索引树越多对增删改的影响就越大六.索引的使用1.自动创建在添加主键约束Primary Key唯一约束Unique外键约束Foreign KeyMySQL就会为对应的列自动创建索引外键索引只有在没有合适的索引使用才会创建比如外键是a,b已经有索引a,b,c这种就是合适的此时不会再创建索引如果索引是b,a这种就是不合适的如果没有添加任何的约束MySQL会为每一行添加一个行号用ROW_ID 这个隐藏列标识并用他作为索引2.手动创建1主键索引# 方式一创建表时创建主键create tablet_test_pk (idbigintprimarykey auto_increment,namevarchar(20));# 方式二创建表时单独指定主键列create tablet_test_pk1 (idbigintauto_increment,namevarchar(20),primarykey (id));# 方式三修改表中的列为主键索引create tablet_test_pk2 (idbigint,namevarchar(20));alter tablet_test_pk2add primarykey (id) ;alter tablet_test_pk2 modify idbigintauto_increment;2唯一索引# 方式一创建表时创建唯一键create tablet_test_uk (idbigintprimarykey auto_increment,namevarchar(20)unique);# 方式二创建表时单独指定唯一列create tablet_test_uk1 (idbigintprimarykey auto_increment,namevarchar(20),unique(name));# 方式三修改表中的列为唯一索引create tablet_test_uk2 (idbigintprimarykey auto_increment,namevarchar(20));alter tablet_test_uk2add unique(name);3普通索引# 方式一创建表时指定索引列create tablet_test_index (idbigintprimarykey auto_increment,namevarchar(20)unique,snovarchar(10),index(sno));#方式二修改表中的列为索引列create tablet_test_index1 (idbigintprimarykey auto_increment,namevarchar(20)unique,snovarchar(10),);alter tablet_test_index1addindex (sno);#方式三单独创建索引并指定索引名create tablet_test_index2 (idbigintprimarykey auto_increment,namevarchar(20)unique,snovarchar(10),);createindexindex_nameont_test_index2(sno);4复合索引# 方式一创建表时指定索引列create tablet_test_index4 (idbigintprimarykey auto_increment,namevarchar(20)unique,snovarchar(10),class_idbigint,index(sno,class_id));#方式二修改表中的列为复合索引create tablet_test_index5 (idbigintprimarykey auto_increment,namevarchar(20)unique,snovarchar(10),class_idbigint,);alter tablet_test_index5addindex (sno,class_id);#方式三单独创建复合索引并指定复合索引名create tablet_test_index6 (idbigintprimarykey auto_increment,namevarchar(20)unique,snovarchar(10),);createindexindex_nameont_test_index6(sno,class_id);3.查看索引# 方式一showkeysfrom 表名;# 方式二showindexfrom 表名;# 方式三查看简要信息desc 表名;4.删除索引1主键索引1.需要先将主键修改为非自增# 语法alter table表名modify主键名 数据类型;2.再删除主键# 语法alter table表名drop primarykey;2其他索引# 语法alter table表名dropindex 索引名;七.查看是否使用索引在查询语句前加上explain就可以看到SQL的执行计划我们接下来会用student表来给大家举例以下是索引的信息其中idx_student_sn_name是由sno和name组成的复合索引查询例子例1不加条件直接查询注这边的type指的是访问类型possible_keys是此次查询可能会用到的索引key是真实用到的索引接下来的例子我们也是主要看这几个信息可以看到如果不加任何条件是不会使用索引的且访问类型是 ALL 这个指的就是全表查询性能最差例2使用主键索引在key那一栏显示PRIMARY就是使用了主键索引并且type显示为const为常数效率很高例3使用子查询注select_type 中的SUBQUERY指的是子查询括号里的查询Extra中显示Using index表示发生了索引覆盖可以看到两个查询都是使用了主键索引而且访问类型都是const例4使用普通索引这边possible_keys 中显示了sno 和 idx_student_sno_name 两个索引是因为sno本身就为普通索引还是复合索引的一部分所以会显示两个有眼力尖的小伙伴就发现了此条查询语句不是会回表查询吗怎么没有显示使用主键索引实际上确实使用了主键索引只是在SQL的执行计划不会显示出来同样的在type中显示的访问类型也只是代表二级索引的例5使用复合索引为了方便演示将sno这个普通索引删除5.1这个例子和上一个是类似的只不过使用了复合索引并且也是发生了回表查询5.2在这边我们又看到了索引覆盖Using index5.3这个和5.2的情况相似都是发生了索引覆盖5.4那么我们把5.3的情况反过来呢用name去查询sno可不可以呢其实是可以的但是不是快速定位到的我给大家举个例子大家就明白了就像我们使用字典时用拼音去查字是不是应该先找声母再找韵母不能是先找韵母再找声母吧。那么在MySQL中复合索引也是一个道理由于我们是先指定的sno再指定的name所以可以用sno快速定位到name反过来就不行。所以在Extra中会显示一个Using where这个可以简单理解为进行了全表查询在这个例子下指的是对这个二级索引树进行了全表查询那Using index就是查询后拿的数据是包含sno所以不用回表了5.5按照上一个例子的结论来说我们在where中先写name 再写 sno 按理应该也是不能快速定位的那为什么这个例子违反了我们的结论呢这是因为数据库开发人员对其进行了优化不管顺序如何只要包含了创建复合索引中所有的列那么就会对这个排序进行一个优化所以是不会发生全表查询的还是能快速定位。注意事项1.SQL中的执行计划在使用二级索引查询就算发生了回表查询使用了主键索引也不会显示2.利用复合索引查询时要注意先指定的列是哪一个不然会发生全表查询总结本文详细讲解了索引的概念索引应该使用的数据结构索引的分类和创建查看删除等操作也为大家演示了如何去查看查询语句是否使用了索引以及讲解了十分重要且和索引息息相关的Page(页)。其中比较难理解的是页和索引是怎么关联起来的页其实就可以理解为索引中的节点还有B树的内容非常非常重要简单再解释一下B树就是在B树这个N叉树的基础上再做优化非叶子节点只保存索引信息叶子节点才保存真实数据叶子节点之间组织成双向链表MySQL中才是双向。本文内容非常多也非常杂但是不是能难大家可以多看几遍理解加油