anaconda可以做网站吗,商标注册证查询,如何设计软件界面,wordpress 7牛文章目录 主键排序一. InnoDB的索引结构1. 单个page2. 多个page 二. 为什么选择B树三. 聚簇索引和非聚簇索引结束语 主键排序
我们创建一个user表#xff0c;并乱序插入数据
mysql create table if not exists user(- id int primary key,- age int not null,-树三. 聚簇索引和非聚簇索引结束语 主键排序
我们创建一个user表并乱序插入数据
mysql create table if not exists user(- id int primary key,- age int not null,- name varchar(16) not null- );mysql insert into user (id,age,name )values(3,18,杨过),(4,16,小龙女),(2,26,黄蓉),(5,36,郭靖),(1,56,欧阳锋);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql select * from user;
--------------------
| id | age | name |
--------------------
| 1 | 56 | 欧阳锋 |
| 2 | 26 | 黄蓉 |
| 3 | 18 | 杨过 |
| 4 | 16 | 小龙女 |
| 5 | 36 | 郭靖 |
--------------------
5 rows in set (0.00 sec)我们发现虽然是乱序插入但是显示出来却是排好序的。这是MySQL做的吗让我们带着这个疑问开始本章的学习
一. InnoDB的索引结构
MySQL的基本单位是PagePage存储着数据而一个数据表文件因其数据量多少由一个或多个Page构成
1. 单个page 不同的Page在MySQL中都是16KB大小使用page_prev和page_next互相链接构成双向链表
上面构建的user表因为有主键所以MySQL会默认按照主键对数据进行排序让Page内的数据是有序且彼此关联的 排序同时也可以提高查询速度 Page内部存放数据实质是使用了链表链表是增删快查询慢所以需要优化查询效率。 而有序可以保证每次查询都是有效查询当前值一定比前面的值大比后面的值小。 2. 多个page
Page的作用是在查询数据时直接将一整页的数据加载到内存中以减少IO次数从而提高性能。但Page内部采用了链表的结构还是需要线性遍历的效率太低
MySQL使用页目录进一步提高查询效率 页目录
我们在看一本书时前几页是整本书的目录如果我们想查看其中的某一章节那么就可以根据目录中那一章节的页数跳跃查找 但存储目录同样需要纸张所以目录是一种以空间换时间的做法 单页情况
我们在单页Page中加入目录 通过引入目录如果我们要查询id4的数据之前需要线性遍历4次但现在可以先通过目录2[3]直接进行定位新的起始位置提高了效率。
所以为什么MySQL要自动排序呢 因为方便引入目录 多页情况
Page的大小为16KB当数据量不断增大时势必需要多个Page存储数据 在单表数据不断被插入的情况下MySQL会在容量不足时自动开辟新的Page来保存新的数据使用指针的方式将所有的Page组织起来 而当Page越来越多时Page之间也是使用指针连接整体是双向链表结构Page之间仍是线性查询。 如何解决呢其实是一样的给这些Page也带上目录就好了
使用一个目录来指向某一页而这个目录项存放的是指向的Page中存放的最小的数据的键值和Page内目录不同的地方在于这种目录管理的级别是Page页内目录管理级别是行其中每个目录项的构成是键值指针下图没画指针的地址 存在一个目录页来管理页目录目录页中的数据存放的就是指向的那个Page中最小的数据。有数据就可以通过比较找到该访问那个Page进而通过指针找到下一个Page
目录页的本质也是页普通页中存放的是用户数据目录页存放的是普通页的地址
即使数据量变大页目录变大我们依然可以再在上方添加管理页目录的页目录来加快检索效率
这种结构其实就是B树 此时随便查找一个id值查找的Page数减少意味着IO次数也减少了那么效率也就提高了
总结一下
Page分为目录页和数据页目录页只放各个下级Page的最小键值查找的时候自顶向下查找只需要加载部分目录页到内存中即可以完成算法的整个查找过程大大减少了IO次数
二. 为什么选择B树 链表or线性表 链表和线性表肯定是不行的线性查找的效率太低了 二叉搜索树 二叉搜索树如果插入的值一直比起始都大或者小就会出现退化的问题变成线性结构 AVL树红黑树 虽然AVL树是平衡树红黑树是接近平衡但是毕竟是二叉结构相比较多阶B意味着树整体过高。都是自顶向下查找层高越低意味着查找次数越少系统与硬盘的IO次数更少 Hash 官方的索引实现中MySQL是支持Hash的不过InnoDB和MyISAM并不支持Hase跟进其算法特征决定了虽然有时候也很快O(1)不过在面对范围查找就明显不行另外还有其他差别有兴趣可以查一下 图中的BTREE是B树
B树
数据结构演示链接数据结构可视化
B树
B树 B树的节点既有数据又有Page指针而B树只有叶子节点有数据其他目录页只有键值和Page指针 B树的叶子节点是相连的而B树没有
之所以选择B树是因为目录页不存储数据只存储指针可以存储更多的key可以使得树更矮所以IO次数更少 叶子节点相连也更便于进行范围查找
三. 聚簇索引和非聚簇索引
先介绍一下MyISAM的存储结构 MyISAM同样使用B树但不同的是叶节点的数据存放的是数据记录的地址。 如下图所示CoI1为主键
MyISAM最大的特点是将索引Page和数据Page分离也就是叶子节点没有数据只有对应数据的地址
相较于InnoDB索引InnoDB是将索引和数据放在一起的
用MyISAM为存储引擎创建表会形成三个文件 .frm后缀表示表结构数据 .MYD后缀表示用户数据 .MYI后缀表示主键索引数据 其中MyISAM这种用户数据与索引数据分离的索引方案叫做非聚簇索引
用InnoDB为存储引擎创建表会形成两个文件 .from后缀表示表结构数据 .ibd后缀表示主键索引和用户数据 InnoDB这种用户数据与索引数据放在一起的索引方案叫做聚簇索引
MySQL除了默认会建立主键索引以外用户也可能按照需求用其他列信息建立索引一般这种索引叫做普通辅助索引
对于MyISAM建立普通索引和主键索引没有什么差别无非是主键不能重复而非主键可以重复
下图是基于MyISAM的Col2建立的索引和主键索引没有差别 MyISAM建立索引会建立一个新的B树页目录和叶子结点所存储的指针改变不会建立新的数据表 同样InnoDB除了主键索引用户也会创建普通索引以上表的Col3建立普通索引如下图 可以看到InnoDB的非主键索引中叶子节点并没有数据而只有对应记录的key值存储的是主键索引的键值
所以通过普通索引找到目标记录需要两遍索引 首先检索普通索引获得主键 然后用主键在主键索引中检索获得数据。 这个过程叫作回表查询
结束语
感谢你的阅读
如果觉得本篇文章对你有所帮助的话不妨点个赞支持一下博主拜托啦这对我真的很重要。
