做网站的语言都有什么,学做热干面网站,网站设计站点管理,小米网站建设案例文章目录 #x1f349;1. 索引底层采用什么数据结构#xff1f;为什么不用hash#x1f349;2. B树与B树区别#xff1f;为何用B树#xff1f;#x1f349;3. 自增主键理解#xff1f;#x1f349;4. 为什么自增主键不连续#x1f349;5. Innodb为什么推荐用自增ID1. 索引底层采用什么数据结构为什么不用hash2. B树与B树区别为何用B树3. 自增主键理解4. 为什么自增主键不连续5. Innodb为什么推荐用自增ID6. 索引有哪些类型7. InnoDB与MyISAM的区别7.1 MyISAM与InnoDB区别7.2 MyISAM7.3 Innodb7.4 使用场景 8. 索引设计原则查询快占用空间少9. 索引有哪些失效场景10. 普通索引和唯一索引怎样选 1. 索引底层采用什么数据结构为什么不用hash 索引底层数据结构是B树
不使用hash因为其底层是哈希表实现等值查询可以快速定位一般情况效率很高不稳定当出现大量键重复哈希冲突效率下降不支持范围查询无法用于排序分组无法模糊查询多列索引的最左前缀匹配原则总要回表操作等。 2. B树与B树区别为何用B树 B树非叶子结点不存data只存key查询更稳定增大了广度(B树出度更大树高矮节点小磁盘IO次数少)叶子结点下一级指针范围查询索引冗余。
与红黑树相比
更少查询次数B树出度更大树高更低查询次数更少 磁盘预读原理为了减少IO操作往往不严格按需读取而是预读。B树叶子结点存储相临读取会快一些。
存储更多索引结点B树只在叶子结点储存数据非叶子结点存索引而一个结点就是磁盘一个内存页内存页大小固定那么相比B树这些可以·存更多的索引结点出度更大树高矮查询次数少磁盘IO少。 3. 自增主键理解 自增主键InnoDB引擎的自增值其实是保存在了内存里并且到了MySQL 8.0版本后才有了“自增值持久化”的能力。
也就是才实现了“如果发生重启表的自增值可以恢复为MySQL重启前的值”具体情况是查看表结构会看到自增主键多少
● 在MySQL 5.7及之前的版本自增值保存在内存里并没有持久化。每次重启后第一次打开表的时候都会去找自增值的最大值max(id)然后将max(id)1作为这个表当前的自增值。● 举例来说如果一个表当前数据行里最大的id是10AUTO_INCREMENT11。这时候我们删除id10的行AUTO_INCREMENT还是11。但如果马上重启实例重启后这个表的AUTO_INCREMENT就会变成10。也就是说MySQL重启可能会修改一个表的AUTO_INCREMENT的值。● 在MySQL 8.0版本将自增值的变更记录在了redo log中重启的时候依靠redo log恢复重启之前的值。自增值修改机制
如果插入数据时id字段指定为0、null 或未指定值那么就把这个表当前的 AUTO_INCREMENT值填到自增字段如果插入数据时id字段指定了具体的值就直接使用语句里指定的值。
自增值新增机制
如果准备插入的值当前自增值新的自增值就是“准备插入的值1”否则自增值不变。 4. 为什么自增主键不连续 ● 在MySQL 5.7及之前的版本自增值保存在内存里并没有持久化
● 事务回滚自增值不能回退因为并发插入数据时回退自增ID可能造成主键冲突
● 唯一键冲突由于表的自增值已变但是主键发生冲突没插进去下一次插入主键现在变了的子增值1所以不连续
假设表t里面已经有了(1,1,1)这条记录这时我再执行一条插入数据命令
insert into t values(null, 1, 1); (自增id,唯一键c,普通字段d)
这个语句的执行流程就是
1. 执行器调用InnoDB引擎接口写入一行传入的这一行的值是(0,1,1);
2. InnoDB发现用户没有指定自增id的值获取表t当前的自增值2
3. 将传入的行的值改成(2,1,1)
4. 将表的自增值改成3
5. 继续执行插入数据操作由于已经存在c1的记录所以报Duplicate key error语句返回。
这个表的自增值改成3是在真正执行插入数据的操作之前。这个语句真正执行的时候因为碰到唯一键c冲突所以id2这一行并没有插入成功但也没有将自增值再改回去。所以在这之后再插入新的数据行时拿到的自增id就是3。也就是说出现了自增主键不连续的情况。
5. Innodb为什么推荐用自增ID ①主键页就会近乎于顺序的记录填满提升了页面的最大填充率不会有页的浪费。
②新插入的行一定会在原有的最大数据行下一行mysql定位和寻址很快不会为计算新行的位置而做出额外的消耗。
③减少了页分裂和碎片的产生
UUID大量的随机IO页分裂导致移动大量的数据数据会有碎片。
总结自增ID有序会按顺序往最后插入而UUID无序随机生成随机插入会造成频繁页分裂内存碎片化大量随机IO 6. 索引有哪些类型 ● 排好序的数据结构可以帮助快速查找数据
● 优缺点索引可以提高查询速度查询使用优化隐藏器提高性能但是也会占据物理空间降低增删改的速度因为还要操作索引文件
类型
覆盖索引回表索引下推联合索引
● 普通索引可以重复
● 唯一索引唯一可为空表中只有一个主键索引可多个唯一索引
● 主键索引
唯一不为空叶子结点存出了行记录数据主键索引也称聚簇索引对应非主键索引的叶子结点存的主键的值二级索引用二级索引查需要回表操作根据二级索引查到主键再根据主键去主键索引查一般推荐用自增主键保证空间利用率减少页分裂
● 全文索引
● 覆盖索引索引字段覆盖了查询语句涉及的字段直接通过索引文件就可以返回查询所需的数据不必通过回表操作。
● 回表通过索引找到主键再根据主键id去主键索引查。
● 索引下推
○ 在根据索引查询过程中就根据查询条件过滤掉一些记录减少最后的回表操作
假如执行 select * from stu where name? and age? 没有索引下推先再存储引擎根据name筛选数据返回给server层然后server层再根据age过滤 有索引下推直接根据name和age在存储引擎层就筛选得到结果 7. InnoDB与MyISAM的区别 7.1 MyISAM与InnoDB区别 ● InnoDB聚簇索引MyISAM非聚簇索引
● InnoDB数据与索引一起保存.ibdMyISAM表结构.frm 索引.myi 数据.myd
● InnoDB支持事务、外键、行锁表锁MyISAM不支持事务、外键、只支持表锁
● select count(*)
● MyISAM查询更优InnoDB更新更优
● 都是Btree索引
● MyISAM支持全文索引InnoDB5.6后支持 7.2 MyISAM ● 不支持事务但是每次查询都是原子的
● 支持表级锁每次操作对整个表加锁
● 存储表的总行数
● 一个MyISAM表有三个文件表结构.frm 索引.myi 数据 .myd
● 采用非聚集索引索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致但是辅索引不用保证唯一性。 7.3 Innodb ● 支持ACID事务支持四种隔离级别
● 支持行级锁及外键约束因此支持写并发
● 不存储总行
● 主键索引采用聚集索引(索引的数据域存储数据文件本身)辅索引的数据域存储主键的值;因此从辅索引查找数据需要先通过辅索引找到主键值再访问辅索引最好使用自增主键防止插入数据时为维持B树结构文件的大调整。 7.4 使用场景 大多数时候我们使用的都是 InnoDB 存储引擎在某些读密集的情况下使用 MyISAM 也是合适的。不过前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点可是~我们一般都会介意啊。
● MyISAM适合读多更新少的MyISAM索引跟数据分开放因此有读取更快的说法。
● InnoDB适合插入更新频繁的索引与数据一起放建立索引更复杂使用行锁更新频繁效率更高
● 需要事务高并发场景用InnodbInnodb支持事务采用行锁
● MyISAM查询比InnoDB快更新InnoDB快
场景MyISAM查询更优InnoDB更新更优 8. 索引设计原则查询快占用空间少 ● 出现在where子句或则连接子句中的列
● 基数小的表没必要
● 使用短索引如果索引长字符串列应该指定前缀长度
● 定义有外键的数据列一定索引
● 不要过度索引
● 更新频繁的不适合
● 区分度不高的不适合如性别
● 尽量扩展索引别新建索引如(a)-(a,b)
● 字符串字段建立索引方法
○ 1、直接创建完整索引这样可能比较占用空间
○ 2、创建前缀索引节省空间但会增加查询扫描次数并且不能使用覆盖索引
○ 3、倒序存储再创建前缀索引用于绕过字符串本身前缀的区分度不够的问题
○ 4、额外用一个字段进行索引额外计算开销总结索引设计原则要求查询快占用空间少一般建在where条件匹配度高的要求基数大区分度高不要过大索引尽量扩展用联合索引更新频繁不适合、使用短索引。 9. 索引有哪些失效场景 ● 以“%”开头的like语句索引无效后缀“%”不影响
● or语句前后没有同时使用索引
● 列类型是字符串一定要在条件中将数据用引号引用否则失效隐式转换
● 如果mysql估计使用全表扫描比索引快则不用索引键值少重复数据多
● 组合索引要遵守最左前缀原则——不使用第一列索引 失效
● 在索引字段上使用not 对它处理是全表扫描
● 对索引字段进行计算操作字段使用函数也会失效 10. 普通索引和唯一索引怎样选 ● 查询比较
○ 查询会以页为单位将数据页加载进内存不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果普通索引查到第一条记录往后遍历直到不满足条件由于都在内存中不需要磁盘读取那么大开销带来的额外查询开销忽略不计所以查询性能几乎一致 ● 更新比较
○ 唯一索引由于更新时要检查唯一性所以需要将数据页先加载进内存才能判断此时直接操作内存不需要操作change buffer
○ 补充普通索引若数据再内存中直接内存中更新否则会将更新操作先记录到channge buffer中等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数据返回这样一来再写多读少的情况下就减少了磁盘IO若写完就马上查询就大可不必用change buffer不但没提高多少效率还造成维护change buffer额外消耗
○ 将change buffer的操作对应到原始数据页的操作称为merge可以查询来时读到内存再修改数据后台线程也会merge数据库正常关闭也会merge
● 适合场景
○ 写多读少选用普通索引更好可以利用change buffer进行性能优化减少磁盘IO将更新操作记录到change bufer等查询来了将数据读到内存再进行修改.
