直播网站app下载,58网站怎么做品牌推广,网站后台管理系统的主要功能,网站弹出广告代码概述
从上一章节我们了解到InnoDB 的数据页都是由7个部分组成#xff0c;然后各个数据页之间可以组成一个双向链表 #xff0c;而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表 #xff0c;每个数据页都会为存储在它里边儿的记录生成一个页目录 #xff…概述
从上一章节我们了解到InnoDB 的数据页都是由7个部分组成然后各个数据页之间可以组成一个双向链表 而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表 每个数据页都会为存储在它里边儿的记录生成一个页目录 在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽然后再遍历该槽对应分组中的记录即可快速找到指定的记录。页和记录的关系示意图如下 不同页之间在物理结构上不一定就相连只要通过双向链表相关联即可。
没有索引的查找
首先我们看看在没有设置索引的情况下是怎么查找记录的。方便理解我们先以搜索条件为对某个列精确匹配的情况所谓精确匹配就是搜索条件中用等于 连接起的表达式比如 SELECT [列名列表] FROM 表名 WHERE 列名 xxx;
在一个页中的查找
假设目前表中的记录比较少所有的记录都可以被存放到一个页16k中在查找记录的时候可以根据搜索条件的不同分为两种情况
以主键为搜索条件 可以在页目录中使用二分法快速定位到对应的槽然后再遍历该槽对应分组中的记录即可快速找到指定的记录。以其他列作为搜索条件 对非主键列的查找的过程就没有这么容易了因为在数据页中并没有对非主键列建立所谓的页目录 可以理解为某页的目录页目录中存放的是槽所以我们无法通过二分法快速定位相应的槽 。这种情况下只能从最小记录开始依次遍历单链表中的每条记录然后对比每条记录是不是符合搜索条件。很显然这种查找的效率是非常低的。
在多页中查找
大部分情况下我们表中存放的记录都是非常多的需要好多的数据页来存储这些记录。在很多页中找记录的话可以分为两个步骤 1 . 定位到记录所在的页。 2 . 从所在的页内中查找相应的记录。 在没有索引的情况下不论是根据主键列或者其他列的值进行查找由于我们并不能快速的定位到记录所在的页所以只能从第一个页沿着双向链表一直往下找在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页所以这种方式显然是超级耗时的如果一个表数据很多使用这种方式去查找记录等待的时间可能就相当的久了 为了解决这个问题就设计出了索引。
索引
方便测试先建一个表
mysql create table index_demo(c1 int,c2 int,c3 char(1), primary key(c1)) row_formatcompact;
Query OK, 0 rows affected (0.13 sec)新建的 index_demo 表中有2个 INT 类型的列1个 CHAR(1) 类型的列而且我们设置了 c1 列为主键使用 Compact 行格式来实际存储记录的。简化的 index_demo 表的行格式示意图如下其他字段被省略 示意图里展示记录的这几个部分说明
record_type 记录头信息的一项属性表示记录的类型0 表示普通记录、2 表示最小记录、3 表示最大记录、 1其实就是索引next_record 记录头信息的一项属性表示下一条地址相对于本条记录的地址偏移量为了方便理解我们都会用箭头来表明下一条记录是谁。各个列的值 这里只记录在 index_demo 表中的三个列分别是 c1 、 c2 和 c3 。其他信息 除了上述3种信息以外的所有信息包括其他隐藏列的值以及记录的额外信息
为了方便理解把记录示意图竖放起来更直观并省略掉一些无关字段然后把一些记录放到页里边的示意图就如下 一个简单的索引方案
实际使用中我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢因为各个页中的记录并没有规律我们并不知道我们的搜索条件匹配哪些页中的记录所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办还记得我们为根据主键值快速定位一条记录在页中的位置而设立的页目录么我们也可以想办法为快速定位记录所在的数据页而建立一个别的目录建这个目录必须完成下边这些事儿下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。注意是两种目录数据页目录和页目录有点绕 为方便说明我们做一个假设假设我们的每个数据页最多能存放3条记录但是实际上一个数据页一般是16K可以存放很多记录。有了这个假设之后我们向 index_demo 表插入3条记录
mysql insert into index_demo VALUES(1, 4, u), (3, 9, d), (5, 3, y);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0那么这些记录已经按照主键值的大小串联成一个单向链表了如图所示
从图中可以看出来 index_demo 表中的3条记录都被插入到了编号为 10 的数据页中了。此时我们再来插入 一条记录
mysql insert into index_demo values(4, 4, a);
Query OK, 1 row affected (0.02 sec)因为页10最多只能放3条记录假设过的所以我们需要再分配一个新页
可以看到新分配的页号是 28而不是11 为什么呢因为新分配的数据页编号并不一定就是连续的也就是说我们使用的这些页在存储空间里可能并不相邻。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外页10中用户记录最大的主键值是5而页28中有一条记录的主键值是4因为54所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求所以在插入主键值为4的记录的时候需要伴随着一次记录移动也就是把主键值为5的记录移动到 页28中然后再把主键值为4的记录插入到 页10 中这个过程的示意图如下
这个过程表明了在对页中的记录进行增删改操作的过程中我们需要通过一些诸如记录移动的操作来始终保证这个状态一直成立下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们也可以称为页分裂 。
给所有的页建立一个目录项。 由于数据页的编号可能并不是连续的所以在向 index_demo 表中插入许多条记录后可能变成如下所示
因为这些16KB的页在物理存储上可能并不挨着所以如果想从这么多页中根据主键值快速定位某些记录所在的页我们需要给它们做个目录每个页对应一个目录项每个目录项包括下边两个部分
页的用户记录中最小的主键值我们用 key 来表示页号我们用 page_no 表示。 所以我们为上边几个页做好的目录就像这样子 以页28为例它对应目录项2 这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5 。我们只需要把几个目录项在物理存储器上连续存储比如把他们放到一个数组里就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为 20 的记录具体查找过程分两步
先从目录项中根据二分法快速确定出主键值为 20 的记录在 目录项3 中因为 12 20 209 它对应的页是页9 。再根据前边说的在页中查找记录的方式二分法去页9 中定位具体的记录。 至此针对数据页做的简易目录就完成了。其实这个目录就是我们常说的索引数据页目录 。
InnoDB中的索引方案
上面这个简易的索引方案我们是假设所有目录项都可以在物理存储器上连续存储根据主键值进行查找时可以使用二分法快速定位具体的目录项但是这样做有几个问题
InnoDB 是使用页来作为管理存储空间的基本单位也就是最多能保证 16KB 的连续存储空间而随着表中记录数量的增多需要非常大的连续的存储空间才能把所有的目录项都放下这对记录数量非常多的表是不现实的。我们时常会对记录进行增删假设我们把页28中的所有记录都删除了页28也就没有存在的必要了那意味着目录项2也就没有存在的必要了这就需要把目录项2后的目录项都向前移动一下这种牵一发而动全身的设计影响太大。
所以InnoDB采用了一种可以灵活管理所有目录项的方式。将目录项中的两个列是主键和页号组成一个新的表就可以复用之前存储用户记录的数据页来存储目录项为了和用户记录做一下区分我们把这些用来表示目录项的记录称为目录项记录 。那 InnoDB 怎么区分一条记录是普通的用户记录还是目录项记录呢这里就用到了之前说过的record_type 属性它的各个取值代表的意思如下 0 普通的用户记录 1 目录项记录 2 最小记录 3 最大记录 从上图可以看出我们新分配了一个编号为30的页来专门存储目录项记录 。这里再次说明一遍目录项记录和普通的用户记录的不同点
目录项记录 的 record_type 值是1而普通用户记录的 record_type 值是0。目录项记录只有主键值和页的编号两个列而普通的用户记录的列是用户自己定义的可能包含很多列另外还有 InnoDB 自己添加的隐藏列。还有前面说过的一个叫 min_rec_mask 的属性么只有在存储目录项记录的页中的主键值最小的目录项记录 的 min_rec_mask 值为 1 其他别的记录的 min_rec_mask 值都是 0 。
除了上述几点外这两者就没啥差别了它们用的是一样的数据页页面类型都是 0x45BF 这个属性在 File Header 中页的组成结构也是一样一样的就是我们前边介绍过的7个部分都会为主键值生成 Page Directory 页目录从而在按照主键值进行查找时可以使用二分法来加快查询速度。现在以查找主键为 20 的记录为例根据某个主键值去查找记录的步骤就可以大致拆分成下边两步 1 . 先到存储 目录项记录的页也就是页30中通过二分法快速定位到对应目录项因为 12 20 209 所 以定位到对应的记录所在的页就是9 。 2 . 再到存储用户记录的 页9 中根据二分法快速定位到主键值为 20的用户记录。
虽然说目录项记录中只存储主键值和对应的页号比用户记录需要的存储空间小多了但是不论怎么说一个页只有16KB大小能存放的目录项记录也是有限的那如果表中的数据太多以至于一个数据页不足以存放所有的目录项记录 该咋办呢
当然是增加一个存储目录项记的页为了更好的理解新分配一个目录项记录页的过程我们假设一个存储目录项记录的页最多只能存放4条 目录项记录 请注意是假设真实情况下可以存放好多条的所以如果此时我们再向上图中插入一条主键值为 320 的用户记录的话那就需要分配一个新的存储目录项记录的页 从图中可以看出我们插入了一条主键值为 320 的用户记录之后需要两个新的数据页为存储该用户记录而新生成了页31 。 因为原先存储目录项记录的页3 的容量已满我们前边假设只能存储4条目录项记录 所以需要一个新的页32来存放页31对应的目录项。 现在因为存储目录项记录 的页不止一个所以如果我们想根据主键值查找一条用户记录大致需要3个步骤以查找主键值为220的记录为例 1 . 确定目录项记录页 我们现在的存储目录项记录的页有两个页30和页32 又因为页30 表示的目录项的主键值的范围是[1, 320) 页32表示的目录项的主键值不小于320所以主键值为220的记录对应的目录项记录在页30中120320。 2 .通过目录项记录页确定用户记录真实所在的页。 在一个存储目录项记录的页中通过主键值定位一条目录项记录的方式说过了参考上面。 3 . 在真实存储用户记录的页中定位到具体的记录。 参考前面二分法
那么问题来了在这个查询步骤的第1步中我们需要定位存储目录项记录的页但是这些页在存储空间中也可能不挨着如果我们表中的数据非常多则会产生很多存储 目录项记录的页那我们怎么根据主键值快速定位一个存储目录项记录的页呢其实也简单为这些存储 目录项记录 的页再生成一个更高级的目录就像是一个多级目录一样大目录里嵌套小目录小目录里才是实际的数据所以现在各个页的示意图就是这样子 如图我们生成了一个存储更高级目录项的 页33 这个页中的两条记录分别代表页30和页32 如果用户记录的主键值在 [1, 320) 之间则到页30中查找更详细的目录项记录 如果主键值不小于320的话就到 页32中查找更详细的目录项记录 。随着表中记录的增加这个目录的层级会继续增加如果简化一下那么我们可以用下边这个图来描述它 这看起来就像一个倒过来的树上头是树根下头是树叶其实这是一种组织数据的形式或者说是一种数据结构就是我们常说的 B 树。
不论是存放用户记录的数据页还是存放目录项记录的数据页我们都把它们存放到 B 树这个数据结构中了所以我们也称这些数据页为节点 。从图中可以看出来我们的实际用户记录其实都存放在B树的最底层的节点上这些节点也被称为叶子节点 或叶节点 其余用来存放目录项的节点称为非叶子节点或者内节点 B数的上层节点其中 B 树最上边的那个节点也称为根节点 。
从上图可以看出来一个 B树的节点其实可以分成好多层InnoDB引擎规定最下边的那层也就是我们存放用户记录的那层为第 0 层之后依次往上加。之前的讨论我们做了一个非常极端的假设存放用户记录的页最多存放3条记录存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的假设假设假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录因为列数一般比用户真实记录列数少那么
如果 B 树只有1层也就是只有1个用于存放用户记录的节点最多能存放 100 条记录。如果 B 树有2层最多能存放 1000×100100000 条记录。如果 B 树有3层最多能存放 1000×1000×100100000000 条记录。如果 B 树有4层最多能存放 1000×1000×1000×100100000000000 条记录。
思考一下我们实际使用中一个表的数据量有100000000000 条么所以一般情况下我们用到的 B 树都不会超过4层那我们通过主键值去查找某条记录最多只需要做4个页面内的查找查找3个目录项页和一个用户记录页又因为在每个页面内有所谓的 Page Directory页目录所以在页面内也可以通过二分法实现快速定位记录所以一般来说通过索引来查找数据速度都非常快。
聚簇索引
我们上边介绍的 B 树本身就是一个目录或者说本身就是一个索引。它有两个特点 1 . 使用记录主键值的大小进行记录和页的排序这包括三个方面的含义
页内的记录是按照主键的大小顺序排成一个单向链表。各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。 2 . B 树的叶子节点存储的是完整的用户记录。 所谓完整的用户记录就是指这个记录中存储了所有列的值包括隐藏列。
我们把具有这两种特性的 B 树称为聚簇索引 所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在 MySQL 语句中显式的使用 INDEX 语句去创建后边会介绍索引相关的语句InnoDB 存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是在 InnoDB 存储引擎中 聚簇索引就是数据的存储方式所有的用户记录都存储在了叶子点也就是所谓的索引即数据数据即索引。
二级索引
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用因为 B 树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢难道只能从头到尾沿着链表依次遍历记录么不我们可以多建几棵 B 树不同的 B 树中的数据采用不同的排序规则。比如说我们用 c2 列的大小作为数据页、页中记录的排序规则再建一棵 B 树效果如下图所示 这个 B 树与上边介绍的聚簇索引有几处不同
使用记录 c2 列的大小进行记录和页的排序这包括三个方面的含义 页内的记录是按照 c2 列的大小顺序排成一个单向链表。各个存放用户记录的页也是根据页中记录的 c2 列大小顺序排成一个双向链表。存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的 c2 列大小顺序排 成一个双向链表。 B 树的叶子节点存储的并不是完整的用户记录而只是 c2列主键 这两个列的值。目录项记录中不再是主键页号的搭配而是 c2列页号 的搭配。
所以如果我们现在想通过 c2 列的值查找某些记录的话就可以使用我们刚刚建好的这个B树了。以查找c2列的值为4的记录为例查找过程如下 1 . 确定目录项记录页 根据根页面 也就是页44 可以快速定位到目录项记录所在的页为页42 因为 2 4 9 。 2 . 通过目录项记录页确定用户记录真实所在的页。 在页42中可以快速定位到实际存储用户记录的页但是由于c2列并没有唯一性约束所以c2列值为4的记录可能分布在多个数据页中又因为 2 4 ≤ 4 所以确定实际存储用户记录的页在页34和页35中。 3 . 在真实存储用户记录的页中定位到具体的记录。 到页34和页35 中定位到具体的记录。 4 . 但是这个 B 树的叶子节点中的记录只存储了 c2 和 c1 也就是主键 两个列所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
各位看到步骤4的操作了么我们根据这个以 c2 列大小排序的 B 树只能确定我们要查找记录的主键值所以如果我们想根据 c2 列的值查找到完整的用户记录的话仍然需要到聚簇索引中再查一遍这个过程也被称为回表 。也就是根据 c2 列的值查询一条完整的用户记录需要使用到2棵 B 树
为什么我们还需要一次回表操作呢直接把完整的用户记录放到叶子节点不就好了么确实如果把完整的用户记录放到叶子节点是可以不用回表 但是太占地方了呀相当于每建立一棵 B 树都需要把所有的用户记录再都拷贝一遍这就有点太浪费存储空间了。因为这种按照非主键列建立的 B 树需要一次回表操作才可以定位到完整的用户记录所以这种 B 树也被称为 二级索引 英文名 secondary index 或者辅助索引 。由于我们使用的是 c2 列的大小作为 B 树的排序规则所以我们也称这个B树为为c2列建立的索引。
联合索引
我们也可以同时以多个列的大小作为排序规则也就是同时为多个列建立索引比方说我们想让 B 树按照 c2和 c3 列的大小进行排序这个包含两层含义
先把各个记录和页按照 c2 列进行排序。在记录的 c2 列相同的情况下采用 c3 列进行排序
为 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树索引的注意事项
根页面万年不动窝
我们前边介绍 B 树索引的时候为了大家理解上的方便先把存储用户记录的叶子节点都画出来然后接着画 存储目录项记录的内节点实际上 B 树的形成过程是这样的
每当为某个表创建一个 B 树索引聚簇索引不是人为创建的默认就有的时候都会为这个索引创建一个根节点页面。最开始表中没有数据的时候每个 B 树索引对应的、根节点 中既没有用户记录也没有目录项记录。随后向表中插入用户记录时先把用户记录存储到这个根节点中。当根节点中的可用空间用完时继续插入记录此时会将根节点中的所有记录复制到一个新分配的页比如页a中然后对这个新页进行页分裂的操作得到另一个新页比如页b 。这时新插入的记录根据键值也就是聚簇索引中的主键值二级索引中对应的索引列的值的大小就会被分配到页a或者页b中而根节点便升级为存储目录项记录的页。
需要注意的是一个B树索引的根节点自诞生之日起便不会再移动。这样只要我们对某个表建立一个索引那么它的根节点 的页号便会被记录到某个地方然后凡是 InnoDB 存储引擎需要用到这个索引的时候都会从那个固定的地方取出根节点的页号从而来访问这个索引。
内节点中目录项记录的唯一性
我们知道 B 树索引的内节点中目录项记录的内容是索引列 页号的搭配但是这个搭配对于二级索引来说有点儿不严谨。还拿 index_demo 表为例假设这个表中的数据是这样的
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 中。
一个页面最少存储2条记录
我们前边说过一个B树只需要很少的层级就可以轻松存储数亿条记录而且添加了索引查询速度也很快。这是因为B树本质上就是一个大的多层级目录每经过一个目录时都会过滤掉许多无效的子目录直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢?那就是目录层级非常非常多看起来就是一条直线而且最后的那个存放真实数据的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录?所以 InnoDB 的 一个数据页至少可以存放两条记录这也是我们之前唠叨记录行格式的时候说过一个结论(我们当时依据这个结论推导了表中只有一个列时该列在不发生行溢出的情况下最多能存储多少字节)。
MyISAM中的索引方案简单介绍
上面介绍的都是 InnoDB 存储引擎中的索引方案为了内容的完整性以及各位可能在面试的时候遇到这类的问题我们有必要再简单介绍一下 MyISAM 存储引擎中的索引方案。我们知道 InnoDB 中索引即数据也就是聚簇索引的那棵 B 树的叶子节点中已经把所有完整的用户记录都包含了而 MyISAM 的索引方案虽然也使用树形结构但是却将索引和数据分开存储:
将表中的记录按照记录的插入顺序单独存储在一个文件中称之为数据文件 。这个文件并不划分为若干个数据页有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。MyISAM 记录也需要记录头信息来存储一些额外数据我们以上边的 index_demo 表为例看一下这个表中的记录使用 MyISAM 作为存储引擎在存储空间中的表示 由于在插入数据的时候并没有刻意按照主键大小排序所以我们并不能在这些数据上使用二分法进行查找。使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。 MyISAM 会单独为表的主键创建一个索引只不过在索引的叶子节点中存储的不是完整的用户记录而是主键值 行号的组 合。也就是先通过索引找到对应的行号再通过行号去找对应的记录! 这一点和 InnoDB 是完全不相同的在 InnoDB 存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在 MyISAM 中却需要进行一次回表操作说明MyISAM 中建立的索引相当于全部都是二级索引。 如果有需要的话我们也可以对其它的列分别建立索引或者建立联合索引原理和 InnoDB 中的索引类似不过在叶子节点处存储的是相应的列 行号 。这些索引也全部都是二级索引 。
注意MyISAM的行格式有定长记录格式(Static)、变长记录格式(Dynamic)、压缩记录格式(Compres sed)。上边的index_demo表采用定长记录格式也就是一条记录占用存储空间的大小是固定的这样就可以轻松算出某条记录在数据文件中的地址偏移量。但是变长记录格式就不行了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 index_demo( c1 int, c2 int, c3 char(1), primary key(c1), index idx_c2_c3 (c2, c3) ); 在这个建表语句中我们创建的索引名是 idx_c2_c3 这个名称可以随便起不过我们还是建议以 idx_ 为前缀后边跟着需要建立索引的列名多个列名之间用下划线 _ 分隔开。 如果我们想删除这个索引可以这么写: alter table index_demo drop index idx_c2_c3;
更多关于mysql的知识分享请前往博客主页。编写过程中难免出现差错敬请指出
