工业网站开发商,怎么把网站排名到百度前三名,wordpress积分插件中文免费,黑龙江建设厅网站 孙宇前言
学完前面我们讲解了InnoDB数据页的7个组成部分#xff0c;知道了各个数据页可以组成一个双向链表#xff0c;而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表#xff0c;每个数据页都会为存储在它里边儿的记录生成一个页目录#xff0c;在通过主键查…前言
学完前面我们讲解了InnoDB数据页的7个组成部分知道了各个数据页可以组成一个双向链表而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表每个数据页都会为存储在它里边儿的记录生成一个页目录在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽然后再遍历该槽对应分组中的记录即可快速找到指定的记录。所以我们画一个简单的关系示意图如下 其中页1、页2、页3…页n这些页可以不在物理结构上相连只用通过双向链表相关联即可。
之前一直听说给数据库创建索引可以提高查询性能但是索引到底是个啥又是怎样工作的今天的就来学习InnoDB存储引擎的B树索引 目录 一、没有索引的查找1.1 一个页中查找1.2 在很多页中查找 二、索引2.1 一个简单的索引方案2.2 InnoDB中的索引方案 三、聚簇索引Clustered Index四、二级索引Secondary Index五、联合索引复合索引六、InnoDB的B树索引的注意事项6.1 根页面永不动窝6.2 内节点中目录项记录的唯一性6.3 一个页面最少存储2条记录 七、MyISAM中的索引方案简单介绍八、MySQL中创建和删除索引的语句九、小结 一、没有索引的查找
在正式的学习索引之前我们需要了解一下没有索引的时候是怎么查找记录的为了方便理解我们只用搜索条件为对某个列精确匹配精确匹配就是搜索条件中用等于连接起的表达式做个案例就像下面这种语句
select[列名列表] from 表名 where 列名 xxx;1.1 一个页中查找
我们知道一个数据页的大小为16KB16384字节除去页中必须的元数据信息需要一部分存储空间之外还会剩下很多空间来存放我们的User Records。假设目前表中的记录比较少所有的记录都可以被存放到一个页中在查找记录的时候可以根据搜索条件的不同分为两种情况 已主键为搜索条件 在页目录中使用二分法快速定位到对应的槽Slot然后再遍历该槽对应分组中的记录即可快速找到指定的记录 以其他列作为搜索条件 对非主键列的查找的过程可就不这么幸运了因为在数据页中并没有对非主键列建立所谓的页目录所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录然后对比每条记录是不是符合搜索条件。很显然这种查找的效率是非常低的。
1.2 在很多页中查找
大部分情况下我们表中存放的记录都是非常多的需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤
定位到记录所在的页。从所在的页中查找相应的记录。
在没有索引的情况下不论是根据主键列或者其他列的值进行查找由于我们并不能快速的定位到记录所在的页所以只能从第一个页沿着双向链表一直往下找在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页所以这种方式显然是超级耗时的如果一个表有一亿条记录使用这种方式去查找记录那要等到猴年马月才能等到查找结果。
所以索引闪亮登场。
二、索引
为了方便展示我们先创建一张表
mysql create table demo6(c1 int primary key,c2 int,c3 char(1)) row_formatcompact;
Query OK, 0 rows affected (0.02 sec)这个新建的demo6表中有2个int类型的列1个char(1)类型的列而且我们规定了c1列为主键这个表使用Compact行格式来实际存储记录的。为了方便理解索引我们对demo6表的行格式示意图做了简化 再来回顾一下展示的这几部分的具体含义
record_type记录头信息的一项属性表示记录的类型0表示普通记录、2表示最小记录、3表示最大记录、1我们还没用过马上就会说到next_record记录头信息的一项属性表示下一条地址相对于本条记录的地址偏移量为了方便理解都用箭头来表明下一条记录是谁各个列的值这里只记录在demo6表中的三个列分别是c1 、c2和c3其他信息 除了上述3种信息以外的所有信息包括其他隐藏列的值以及记录的额外信息。
我们再来对示意图做一下调整将记录格式示意图的其他信息去掉并把它竖起来的效果就是这样 把一些记录放到页里面的示意图如下见颜色知其意哈 2.1 一个简单的索引方案
回到正题根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢因为各个页中的记录并没有规律我们并不知道我们的搜索条件匹配哪些页中的记录所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办还记得我们为根据主键值快速定位一条记录在页中的位置而设立的页目录么我们也可以想办法为快速定位记录所在的数据页而建立一个别的目录建这个目录必须完成下边这些事儿
第一 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
我们这里需要做一个假设假设我们的每个数据页最多能存放3条记录实际上一个数据页非常大可以存放下好多记录。有了这个假设之后我们向demo6表插入3条记录
mysql insert into demo6 values(1, 4, u), (3, 9, d), (5, 3, y);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0那么这些记录已经按照主键值的大小串联成一个单向链表了如图所示 从图中可以看出来demo6表中的3条记录都被插入到了编号为10的数据页中了。此时我们再来插入1条记录
mysql insert into demo6 values(4, 4, a);
Query OK, 1 row affected (0.01 sec)页10最多只能放3条记录所以我们不得不再分配一个新页 新分配的数据页编号可能并不是连续的也就是说我们使用的这些页在存储空间里可能并不挨着。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。
页10中用户记录最大的主键值是5而页28中有一条记录的主键值是4因为5 4所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求所以在插入主键值为4的记录的时候需要伴随着一次记录移动也就是把主键值为5的记录移动到页28中然后再把主键值为4的记录插入到页10中这个过程的示意图如下 将主键值为5的记录移到页28 将主键为4的插入到页10 这个过程表明了在对页中的记录进行增删改操作的过程中我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们也可以称为页分裂。
第二 给所有的页建立一个目录项
由于数据页的编号可能并不是连续的所以在向demo6表中插入许多条记录后可能是这样的效果 因为这些16KB的页在物理存储上可能并不挨着所以如果想从这么多页中根据主键值快速定位某些记录所在的页我们需要给它们做个目录每个页对应一个目录项每个目录项包括下边两个部分
页的用户记录中最小的主键值我们用key 来表示页号我们用page_no表示
所以我们上边几个页做好的目录就像这个样子 以页28为例它对应目录项2这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储比如把他们放到一个数组里就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为20的记录具体查找过程分两步
先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中因为1220209它对应的页是页9。再根据前边说的在页中查找记录的方式去页9中定位具体的记录。
至此针对数据页做的简易目录就搞定了。这个目录有一个别名称为索引。
2.2 InnoDB中的索引方案
上边之所以称为一个简易的索引方案是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储但是这样做有几个问题
InnoDB是使用页来作为管理存储空间的基本单位也就是最多能保证16KB的连续存储空间而随着表中记录数量的增多需要非常大的连续的存储空间才能把所有的目录项都放下这对记录数量非常多的表是不现实的。我们时常会对记录进行增删假设我们把页28中的记录都删除了页28也就没有存在的必要了那意味着目录项2也就没有存在的必要了这就需要把目录项2后的目录项都向前移动一下这种牵一发而动全身的设计不是什么好主意
所以InnoDB需要一种可以灵活管理所有目录项的方式。这些目录项其实长得跟我们的用户记录差不多只不过目录项中的两个列是主键和页号而已所以他们复用了之前存储用户记录的数据页来存储目录项为了和用户记录做一下区分我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢别忘了记录头信息里的record_type属性它的各个取值代表的意思如下
0普通的用户记录1目录项记录2最小记录3最大记录
record_type值为1的意思大家明白了吗我们把前边使用到的目录项放到数据页中的示意图如下
从图中可以看出来我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调一遍目录项记录和普通的用户记录的不同点
目录项记录的record_type值是1而普通用户记录的record_type值是0目录项记录只有主键值和页的编号两个列而普通的用户记录的列是用户自己定义的可能包含很多列另外还有InnoDB自己添加的隐藏列还记得我们之前在唠叨记录头信息的时候说过一个叫min_rec_mask的属性么只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1其他别的记录的min_rec_mask值都是0
除了上述几点外这两者就没啥差别了它们用的是一样的数据页页的类型都是0x45bf页的组成结构也是一样的都会为主键值生成Page Directory页目录从而在按照主键值进行查找时可以使用二分法来加快查询速度。现在以查找主键为20的记录为例根据某个主键值去查找记录的步骤就可以大致拆分成下边两步
先到存储目录项记录的页也就是页30中通过二分法快速定位到对应目录项因为1220209所以定位到对应的记录所在的页就是页9再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录
虽然说目录项记录中只存储主键值和对应的页号比用户记录需要的存储空间小多了但是不论怎么说一个页只有16KB大小能存放的目录项记录也是有限的那如果表中的数据太多以至于一个数据页不足以存放所有的目录项记录该咋办呢
当然是再多整一个存储目录项记录的页喽 为了大家更好的理解新分配一个目录项记录页的过程我们假设一个存储目录项记录的页最多只能存放4条目录项记录请注意是假设哦真实情况下可以存放好多条的所以如果此时我们再向上图中插入一条主键值为320的用户记录的话那就需要分配一个新的存储目录项记录的页喽 从图中可以看出我们插入了一条主键值为320的用户记录之后需要两个新的数据页
为存储该用户记录而新生成了页31因为原先存储目录项记录的页30的容量已满我们前边假设只能存储4条目录项记录所以不得不需要一个新的页32来存放页31对应的目录项
现在因为存储目录项记录的页不止一个所以如果我们想根据主键值查找一条用户记录大致需要3个步骤以查找主键值为20的记录为例
确定目录项记录页我们现在的存储目录项记录的页有两个即页30和页32又因为页30表示的目录项的主键值的范围是[1, 320)页32表示的目录项的主键值不小于320所以主键值为20的记录对应的目录项记录在页30中通过目录项记录页确定用户记录真实所在的页在真实存储用户记录的页中定位到具体的记录
问题来了在这个查询步骤的第1步中我们需要定位存储目录项记录的页但是这些页在存储空间中也可能不挨着如果我们表中的数据非常多则会产生很多存储目录项记录的页那我们怎么根据主键值快速定位一个存储目录项记录的页呢其实也简单为这些存储目录项记录的页再生成一个更高级的目录就像是一个多级目录一样大目录里嵌套小目录小目录里才是实际的数据所以现在各个页的示意图就是这样子 如图我们生成了一个存储更高级目录项的页33这个页中的两条记录分别代表页30和页32如果用户记录的主键值在[1, 320)之间则到页30中查找更详细的目录项记录如果主键值不小于320的话就到页32中查找更详细的目录项记录。随着表中记录的增加这个目录的层级会继续增加。这图像不像一个倒过来的树呀上头是树根下头是树叶其实这是一种组织数据的形式或者说是一种数据结构它的名称是B树。
不论是存放用户记录的数据页还是存放目录项记录的数据页我们都把它们存放到B树这个数据结构中了所以我们也称这些数据页为节点。从图中可以看出来我们的实际用户记录其实都存放在B树的最底层的节点上这些节点也被称为叶子节点或叶节点其余用来存放目录项的节点称为非叶子节点或者内节点其中B树最上边的那个节点也称为根节点。
从图中可以看出来一个B树的节点其实可以分成好多层InnoDB为了讨论方便规定最下边的那层也就是存放我们用户记录的那层为第0层之后依次往上加。真实环境中一个页存放的记录数量是非常大的假设假设假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录那么如果B树有4层最多能存放1000×1000×1000×100100000000000条记录。所以一般情况下我们用到的B树都不会超过4层那我们通过主键值去查找某条记录最多只需要做4个页面内的查找查找3个目录项页和一个用户记录页又因为在每个页面内有所谓的Page Directory页目录所以在页面内也可以通过二分法实现快速定位记录。这便是索引为什么可以加速查询的原因。
三、聚簇索引Clustered Index
我们上边介绍的B树本身就是一个目录或者说本身就是一个索引。它有两个特点 使用记录主键值的大小进行记录和页的排序这包括三个方面的含义 页内的记录是按照主键的大小顺序排成一个单向链表各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。 B树的叶子节点存储的是完整的用户记录 所谓完整的用户记录就是指这个记录中存储了所有列的值包括隐藏列
我们把具有这两种特性的B树称为聚簇索引所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用index语句去创建InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是在InnoDB存储引擎中聚簇索引就是数据的存储方式所有的用户记录都存储在了叶子节点也就是所谓的索引即数据数据即索引。
四、二级索引Secondary Index
聚簇索引只能在搜索条件是主键值时才能发挥作用因为B树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢难道只能从头到尾沿着链表依次遍历记录么
不我们可以多建几棵B树不同的B树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则再建一棵B树效果如下图所示 这个B树与上边介绍的聚簇索引有几处不同 使用记录c2列的大小进行记录和页的排序这包括三个方面的含义 页内的记录是按照c2列的大小顺序排成一个单向链表各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。 B树的叶子节点存储的并不是完整的用户记录而只是c2列主键这两个列的值。 目录项记录中不再是主键页号的搭配而变成了c2列页号的搭配。
所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B树了。以查找c2列的值为4的记录为例查找过程如下
所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B树了。以查找c2列的值为4的记录为例查找过程如下 确定目录项记录页 根据根页面也就是页44可以快速定位到目录项记录所在的页为页42因为24 9 根据根页面也就是页33可以快速定位到目录项记录所在的页为页30。 通过目录项记录页确定用户记录真实所在的页。 在页42中可以快速定位到实际存储用户记录的页但是由于c2列并没有唯一性约束所以c2列值为4的记录可能分布在多个数据页中又因为24≤ 4所以确定实际存储用户记录的页在页34和页35中 在真实存储用户记录的页中定位到具体的记录。 到页34和页35中定位到具体的记录 但是这个B树的叶子节点中的记录只存储了c2和c1也就是主键两个列所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
我们根据这个以c2列大小排序的B树只能确定我们要查找记录的主键值所以如果我们想根据c2列的值查找到完整的用户记录的话仍然需要到聚簇索引中再查一遍这个过程也被称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B树 小提示 如果把完整的用户记录放到叶子节点是可以不用回表但是太占地方了呀相当于每建立一棵B树都需要把所有的用户记录再都拷贝一遍这就有点太浪费存储空间了。因为这种按照非主键列建立的B树需要一次回表操作才可以定位到完整的用户记录所以这种B树也被称为二级索引英文名Secondary Index或者辅助索引。由于我们使用的是c2列的大小作为B树的排序规则所以我们也称这个B树为为c2列建立的索引。 五、联合索引复合索引
我们也可以同时以多个列的大小作为排序规则也就是同时为多个列建立索引比方说我们想让B树按照c2和c3列的大小进行排序这个包含两层含义
先把各个记录和页按照c2列进行排序。在记录的c2列相同的情况下采用c3列进行排序。 如图所示我们需要注意以下几点 每条目录项记录都由c2、c3、页号这三个部分组成各条记录先按照c2列的值进行排序如果记录的c2列相同则按照c3列的值进行排序。 B树叶子节点处的用户记录由c2、c3和主键c1列组成。
千万要注意一点以c2和c3列的大小为排序规则建立的B树称为联合索引本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的不同点如下
建立联合索引只会建立如上图一样的1棵B树。为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B树
六、InnoDB的B树索引的注意事项
6.1 根页面永不动窝
我们前边介绍B树索引的时候为了大家理解上的方便先把存储用户记录的叶子节点都画出来然后接着画存储目录项记录的内节点实际上B树的形成过程是这样的 每当为某个表创建一个B树索引聚簇索引不是人为创建的默认就有的时候都会为这个索引创建一个根节点页面。最开始表中没有数据的时候每个B树索引对应的根节点中既没有用户记录也没有目录项记录。 随后向表中插入用户记录时先把用户记录存储到这个根节点中。 当根节点中的可用空间用完时继续插入记录此时会将根节点中的所有记录复制到一个新分配的页比如页a中然后对这个新页进行页分裂的操作得到另一个新页比如页b。这时新插入的记录根据键值也就是聚簇索引中的主键值二级索引中对应的索引列的值的大小就会被分配到页a或者页b中而根节点便升级为存储目录项记录的页。
这个过程需要大家特别注意的是一个B树索引的根节点自诞生之日起便不会再移动。这样只要我们对某个表建立一个索引那么它的根节点的页号便会被记录到某个地方然后凡是InnoDB存储引擎需要用到这个索引的时候都会从那个固定的地方取出根节点的页号从而来访问这个索引。 小提示 这个存储某个索引的根节点在哪个页面中的信息就是传说中的数据字典中的一项信息关于更多数据字典的内容后边会详细说明的 6.2 内节点中目录项记录的唯一性
我们知道B树索引的内节点中目录项记录的内容是索引列 页号的搭配但是这个搭配对于二级索引来说有点儿不严谨。还拿demo6表为例假设这个表中的数据是这样的
c1c2c311‘u’31‘d’51‘y’71‘a’
如果二级索引中目录项记录的内容只是索引列 页号的搭配的话那么为c2列建立索引后的B树应该长这样 如果我们想新插入一行记录其中c1、c2、c3的值分别是9、1、‘c’那么在修改这个为c2列建立的二级索引对应的B树时便碰到了个大问题由于页3中存储的目录项记录是由c2列 页号的值构成的页3中的两条目录项记录对应的c2列的值都是1而我们新插入的这条记录的c2列的值也是1那我们这条新插入的记录到底应该放到页4中还是应该放到页5中啊答案是对不起懵逼了。
为了让新插入记录能找到自己在那个页里我们需要保证在B树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的
索引列的值主键值页号
也就是我们把主键值也添加到二级索引内节点中的目录项记录了这样就能保证B树每一层节点中各条目录项记录除页号这个字段外是唯一的所以我们为c2列建立二级索引后的示意图实际上应该是这样子的 这样我们再插入记录(9, 1, ‘c’)时由于页3中存储的目录项记录是由c2列 主键 页号的值构成的可以先把新记录的c2列的值和页3中各目录项记录的c2列的值作比较如果c2列的值相同的话可以接着比较主键值因为B树同一层中不同目录项记录的c2列 主键的值肯定是不一样的所以最后肯定能定位唯一的一条目录项记录在本例中最后确定新记录应该被插入到页5中。
6.3 一个页面最少存储2条记录
我们前边说过一个B树只需要很少的层级就可以轻松存储数亿条记录查询速度杠杠的这是因为B树本质上就是一个大的多层级目录每经过一个目录时都会过滤掉许多无效的子目录直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢那就是目录层级非常非常非常多而且最后的那个存放真实数据的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录逗我呢所以InnoDB的一个数据页至少可以存放两条记录这也是我们之前介绍记录行格式的时候说过一个结论
七、MyISAM中的索引方案简单介绍
现在来简单介绍一下MyISAM存储引擎中的索引方案。我们知道InnoDB中索引即数据也就是聚簇索引的那棵B树的叶子节点中已经把所有完整的用户记录都包含了而MyISAM的索引方案虽然也使用树形结构但是却将索引和数据分开存储 将表中的记录按照记录的插入顺序单独存储在一个文件中称之为数据文件磁盘上的.MYD文件。这个文件并不划分为若干个数据页有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。 MyISAM记录也需要记录头信息来存储一些额外数据我们以上边唠叨过的demo6表为例看一下这个表中的记录使用MyISAM作为存储引擎在存储空间中的表示 由于在插入数据的时候并没有刻意按照主键大小排序所以我们并不能在这些数据上使用二分法进行查找。 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件磁盘上的.MYI文件的另一个文件中。MyISAM会单独为表的主键创建一个索引只不过在索引的叶子节点中存储的不是完整的用户记录而是主键值 行号的组合。也就是先通过索引找到对应的行号再通过行号去找对应的记录 这一点和InnoDB是完全不相同的在InnoDB存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在MyISAM中却需要进行一次回表操作意味着MyISAM中建立的索引相当于全部都是二级索引 如果有需要的话我们也可以对其它的列分别建立索引或者建立联合索引原理和InnoDB中的索引差不多不过在叶子节点处存储的是相应的列 行号。这些索引也全部都是二级索引。 小提示 MyISAM的行格式有定长记录格式Static、变长记录格式Dynamic、压缩记录格式Compressed。上边用到的demo6表采用定长记录格式也就是一条记录占用存储空间的大小是固定的这样就可以轻松算出某条记录在数据文件中的地址偏移量。但是变长记录格式就不行了MyISAM会直接在索引叶子节点处存储该条记录在数据文件中的地址偏移量。通过这个可以看出MyISAM的回表操作是十分快速的因为是拿着地址偏移量直接到文件中取数据的反观InnoDB是通过获取主键之后再去聚簇索引里边儿找记录虽然说也不慢但还是比不上直接用地址去访问。希望大家理解InnoDB中的索引即数据数据即索引而MyISAM中却是索引是索引、数据是数据 八、MySQL中创建和删除索引的语句
光顾着学习索引的原理了那我们如何使用MySQL语句去建立这种索引呢InnoDB和MyISAM会主动为主键或者声明为UNIQUE的列去自动建立B树索引但是如果我们想为其他的列建立索引就需要我们显式的去指明。为啥不自动为每个列都建立个索引呢别忘了每建立一个索引都会建立一棵B树每插入一条记录都要维护各个记录、数据页的排序关系这是很费性能和存储空间的。
我们可以在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列
create talbe 表名 (各种列的信息 ··· ,[key|index] 索引名 (需要被索引的单个列或多个列)
)小提示 key和index是同义词任意选用一个就可以 我们也可以在修改表结构的时候添加索引
alter table 表名 add [index|key] 索引名 (需要被索引的单个列或多个列);也可以在修改表结构的时候删除索引
alter table 表名 drop [index|key] 索引名;比⽅说我们想在创建index_demo表的时候就为c2和c3列添加一个联合索引可以这么写建表语句
create table demo7(c1 int,c2 int,c3 char(1),primary key(c1),index idx_c2_c3(c2,c3)
);在这个建表语句中我们创建的索引名是idx_c2_c3这个名称可以随便起不过我们还是建议以idx_为前缀后边跟着需要建⽴索引的列名多个列名之间⽤下划线_分隔开。
如果我们想删除这个索引可以这么写
alter table demo7 drop index idx_c2_c3;如果我们想查询索引
show index from 表名;九、小结
今天的内容又有很多我们来简单做下总结 每个索引都对应一棵B树B树分为好多层最下边一层是叶子节点其余的是内节点。所有用户记录都存储在B树的叶子节点所有目录项记录都存储在内节点。 InnoDB存储引擎会自动为主键如果没有它会自动帮我们添加建立聚簇索引聚簇索引的叶子节点包含完整的用户记录。 我们可以为自己感兴趣的列建立二级索引二级索引的叶子节点包含的用户记录由索引列 主键组成所以如果想通过二级索引来查找完整的用户记录的话需要通过回表操作也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。 B树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表而且每个页内的记录不论是用户记录还是目录项记录都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话则页面和记录先按照联合索引前边的列排序如果该列值相同再按照联合索引后边的列排序。 通过索引查找记录是从B树的根节点开始一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory页目录所以在这些页面中的查找非常快。 MyISAM会单独为表的主键创建一个索引只不过在索引的叶子节点中存储的不是完整的用户记录而是主键值 行号的组合。在InnoDB存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在MyISAM中却需要进行一次回表操作意味着MyISAM中建立的索引相当于全部都是二级索引。 InnoDB的B树索引的注意事项 根页面永不动窝 内节点中目录项记录唯一 一个页面最少存储2条记录。 InnoDB中的索引即数据数据即索引而MyISAM中是索引是索引、数据是数据。
今天我们学习了InnoDB的B树索引知道了它的结构及工作原理今天的文章非常非常重要
至此今天的学习就到此结束了愿您成为坚不可摧的自己~~~ You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life 如果我的内容对你有帮助请 点赞、评论、收藏创作不易大家的支持就是我坚持下去的动力
