win10记事本怎么做网站,深圳建设岗位证书报名网站,电子商务网站建设的准备工作有哪些,可以做没有水印的视频网站目录
1、什么是索引
2、索引分类
3、索引的基本操作
3.1、主键索引
3.2、单列索引
3.3、唯一索引
3.4、复合索引
4、索引的底层原理 为什么使用BTree而不是B-Tree?
如果数据量特别大的情况下#xff0c;BTree会不会深度太深影响查询效率#xff1f;
5、聚簇索引和…目录
1、什么是索引
2、索引分类
3、索引的基本操作
3.1、主键索引
3.2、单列索引
3.3、唯一索引
3.4、复合索引
4、索引的底层原理 为什么使用BTree而不是B-Tree?
如果数据量特别大的情况下BTree会不会深度太深影响查询效率
5、聚簇索引和非聚簇索引
5.1、概念
5.2、使用聚簇索引的优势
5.3、聚簇索引需要注意什么
5.4、为什么通常建议使用自增id
6、索引失效的常见场景 1、什么是索引
1.1、索引定义 索引是一种帮助MySQL提高查询效率的数据结构
1.2、索引的优点 加快数据查询的速度
1.3、索引的缺点
维护索引需要耗费数据库资源索引需要占用磁盘空间当对表的数据进行增删改时因为要维护索引速度会受到影响
2、索引分类
主键索引设定为主键后数据库会自动建立索引innodb为聚簇索引单值索引一个索引只包含一个列一个表可以有多个单列索引唯一索引索引列的值必须唯一但允许有空值复合索引一个索引包含多个列
3、索引的基本操作
3.1、主键索引
建表把id设置为主键 查看索引 可以看到我们没有设置索引但数据库已经自动帮我们把主键设置为索引了~
3.2、单列索引
建表把age设置为单列索引 通过上述我们可以观察到主键被数据库自动设置为索引单列索引可以和主键索引同时存在~
3.3、唯一索引
建表把name设置为唯一索引 唯一索引和主键索引的区别 主键索引的值不能为空而唯一索引的值可以为空我在MySQL5.7版本上是支持多个数据为空的的~
3.4、复合索引
建表把name和age设置为复合索引 复合索引底层如何存储的我们先来看看索引的底层原理 4、索引的底层原理
在MySQL中实际存储数据是如何存储的呢
如下 看上图我们会知道存储数据时是按照三部分存储的一个是主键索引一个是数据部分一个是指针。那么指针指向哪里呢指针是指向下一个节点的 数据就是这样组织起来的。有一个点需要注意的是MySQL索引底层的数据是按照主键进行有序放置的也就是说数据是按照主键索引进行有序存储的。那么问题来了当我们存入的数据特多时我们是需要把数据全部遍历一遍吗这样的话时间复杂度是不是有点太大了如果有一万个数据运气不好的话找数据不是得找一万次 那根据我们上述所说的数据是按照主键有序存储的那我们就可以对底层数据存储进行优化把数据划分成一个个的区域例如下面的3个数据一划分 划分后我们就可以把每个区域看成是一页页的数据MySQL中每一页默认是规定存储16KB的数据我们就可以给每一页数据一个目录如下 观察上图我们就可以得知目录中的数据其实就是每一页的数据的第一个数据~ 细心的小伙伴会发现目录中存储的数据并不是完整的数据而是只存放了一个数据的主键和指针~ 如果说如果数据量非常非常大目录这个区域也已经超过了16KB的数据怎么办是不是需要我们再给目录再提一层目录呢~如下 此时看到这里学习过数据结构的小伙伴就会知道这个不就是B树吗对的MySQL索引就是采用了B的数据结构。 BTree是在B-Tree基础上的一种优化使其更适合实现外存储索引结构InnoDB存储引擎(Mysql的默认引擎就是InnoDB就是用BTree实现其索引结构。 为什么使用BTree而不是B-Tree? BTree的非叶子节点只存储键值信息而B-Tree是存储所有数据每一页的存储空间是有限的如果非叶子节点也存储数据会导致每个节点能存储的key的数量变少当存储的数据量很大时会导致T-Tree的深度变大增加查询时的磁盘I/O次数进而影响查询效率而BTree就能很好的解决这些问题BTree所有叶子结点之间都有一个链指针B-Tree没有BTree数据记录都存放在叶子结点中 如果数据量特别大的情况下BTree会不会深度太深影响查询效率 不会哦~BTree的高度一般都是在2~4层而MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作~ 为什么BTree的高度一般都是在2~4层
我们可以来算一算一页能存放多少数据 假设是我们刚才的数据的表有一个id一个name一个age。id是int类型4个字节数据量大时需要使用bigInt8个字节name假设是设置20字节age为int类型4个字节一个指针8个字节一共一条数据36字节。
1KB1024字节一页可以存16KB16*1024/28约等于455条数据。
当我们的BTree的高度为2时也就是说有一层目录我们来算算能存多少数据 目录中只存放id和指针一共12个字节一页能存放数据个数16*1024/12约等于1365个数据。那么1365的数据就对应1365页叶子节点的数据约等于对应621226条数据。 当BTree的高度为3时大家可以自己算算能存的数据量其实是非常大的因此一般BTree的高度都是在2~4层的~ 5、聚簇索引和非聚簇索引
5.1、概念
聚簇索引将数据存储与索引放到一块索引结构的叶子结点保存了行数据非聚簇索引将数据与索引分开存储索引结构的叶子结点指向了数据对应的位置。 聚簇索引 也就是说数据库中的所有数据都是放在聚簇索引的叶子结点中的而其他索引都属于辅助索引例如复合索引、前缀索引、唯一索引等辅助索引叶子结点中存储的不是数据的物理位置而是主键值因此辅助索引访问数据时总是需要二次查找的 说明
我们会看到辅助索引是以age作为单列索引的索引这个索引树就是以age大小排序的~InnoDB使用的是聚簇索引将主键组织到一B树中而行数据就储存在叶子节点上若使用 where d 4这样的条件查找主键则按照B树的检索算法即可查找到对应的叶节点之后获得行数据。若对Name列进行条件搜索则需要两个步: 第一步在辅助索引B树中检索Name到达其叶子节点获取对应的主键第二步使用主键在主索引B树种再执行一次B树检索操作最终到达叶子节点即可获取整行数据。 (重点在于通过其他键需要建立辅助索引)聚簇索引默认是主键如果表中没有定义主键InnoDB 会选择一个唯一且非空的索代如果没有这样的索引InnoDB 会隐式定义一个主键(举似oracle中的Rowld)来作为聚索引如果已经设置了主键为聚索引又希望再单独设置聚索引必须先别除主键然后添加我们想要的聚能索引最后恢复设置主键即可。 非聚簇索引 MYISAM就是使用的非聚簇索引非聚簇索引的两颗B树看上去没有什么不同节点的结构完全一致只是存储的内容不同。主键索引B树的节点存储了主键辅助键索引B树存储了辅助键。表数据存储在独立的地方这两颗B树的叶子结点都使用一个地址指向真正的表数据对于表数据来说这两个建没有任何差别由于索引树是独立的通过辅助建索引无需访问主键的索引树 5.2、使用聚簇索引的优势 每次使用辅助索引检索都要经过两次B树查找看上去聚簇索引的效率明显要低于非聚簇索引这不是多此一举吗聚簇索引的优势在哪
由于行数据和聚簇索引的叶子节点存储在一起同一页中会有多条行数据访问同一数据页不同行记录时已经把页加载到了Buffer中缓存器再次访问时会在内存中完成访问不必访问磁盘。这样主键和行数据是一起被载入内存的找到叶子节点就可以立刻将行数据返回了如果按照主键Id来组织数据获得数据更快。辅助索引的叶子节点存储主键值而不是数据的存放地址。好处是当行数据放生变化时索引树的节点也需要分裂变化或者是我们需要查找的数据在上一次IO读写的缓存中没有需要发生一次新的IO操作时可以避免对辅助索引的维护工作只需要维护聚簇索引树就好了。另一个好处是因为辅助索引存放的是主键值减少了辅助索引占用的存储空间大小。
5.3、聚簇索引需要注意什么 当使用主键为聚簇索引时主键最好不要使用UUID因为UUID的值太过于离散不适合排序且可能出现新增记录的UUID会插入在索引树中间的位置导致索引树调整复杂度变大消耗更多的时间和资源。 建议使用int类型的自增方便排序并且默认会在索引树的末尾增加主键值对索引树的结构影响最小。而且逐渐值占用的存储空间越大辅助索引中保存的主键值也会跟着变大占用存储空间也会影响到IO操作读取到的数据量~
5.4、为什么通常建议使用自增id 聚簇索引的数据的物理存放顺序与索引顺序是一致的即只要索引是相邻的那么对应的数据一定也是相邻的存放在磁盘上的。如果主键不是自增id那么可以想象例如使用UUID每次都是随机值就会导致每次添加数据时都不要不断地调整数据的物理地址、重新分页等。当然也有其他措施来减少这些操作但是这是无法减少这些操作的但却无法彻底避免。那如果说使用自增那就简单了他只需要一页一页的写索引结构相对紧凑磁盘碎片少效率也高~ 6、索引失效的常见场景
复合索引联合索引要使用最左前缀也就是说联合索引索引的叶子结点在排序时其实是按照创建索引时最左边这个值排序的因此要使用最左前缀否则会失效查询时使用like关键字并且以%开头时索引会失效在列上进行函数运算时索引会失效使用in不会造成索引失效而not in会使用or关键字时如果or的前后的列都是索引则会使用索引如果其中一个列不是索引索引就会失效~
好啦本期就到这里咯下期见~~~
