青海海东住房和城乡建设局网站,wordpress图书类主题,微博营销的优势和劣势,app ui设计网站索引
概念
索引#xff08;index#xff09;是帮助MySQL高效获取数据的数据结构(有序)。在数据之外#xff0c;数据库系统还维护着满足特定查找算法的数据结构**#xff08;B树#xff09;**#xff0c;这些数据结构以某种方式引用#xff08;指向#xff09;数据index是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足特定查找算法的数据结构**B树**这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。
底层数据结构——B树
其他树相关的数据结构 只要是二叉树最好的搜索时间复杂度就是Olog2n如果一张表的数据量有一千万那么依旧要很长时间。
B-TreeB树是一种多叉路衡查找树相对于二叉树B树每个节点可以有多个分支即多叉。 以一颗最大度数max-degree为5(5阶)的b-tree为例那这个B树每个节点最多存储4个key那么其搜索时间复杂度就是Olog4n
B树
BTree是在BTree基础上的一种优化使其更适合实现外存储索引结构。 不同的是B树只有叶子节点才存储数据非叶子节点不存储数据只存储指针使得存储更少查询效率更稳定。 且叶子节点之间使用双向指针连接更方便扫库和区间查询。 B树与B树对比: ①磁盘读写代价B树更低②查询效率B树更加稳定③B树便于扫库和区间查询
索引分类聚集索引和二级索引 聚集索引选取规则:
如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 回表查询
通过二级索引找到对应的主键值到聚集索引中查找整行数据这个过程就是回表。
覆盖索引
覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到 。 我真的觉得覆盖索引这个词用的不好搞得像聚集索引和二级索引一样像是索引分类的一种但实际上它更像是回表查询之类的查询分类我自己就是把覆盖索引当作覆盖查询来记得
举例如下
超大分页查询
在数据量比较大时如果进行limit分页查询在查询时越往后分页查询效率越低。 因为当在进行分页查询时如果执行 limit 9000000,10 此时需要MySQL排序前9000010 记录仅仅返回 9000000 - 9000010 的记录其他记录丢弃查询排序的代价非常大 。
为此我们可以通过覆盖索引加子查询形式进行优化。
select * from tb_sku t,(select id from tb_sku order by id limit 9000000,10) awhere t.id a.id;效率如下 覆盖索引加子查询的方式之所以比limit要快是因为 前者是先在索引B树上进行查找后直接返回id然后根据id再去真表中拿数据。 后者是在真表中先把每一行的数据都拿出来再判断是否是想要的那一条。按照这个逻辑查了很多条而且前面大部分都是无用的数据。
索引创建原则
1). 针对于数据量较大且查询比较频繁的表建立索引。 2). 针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。 3). 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。 4). 如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。 5). 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。 6). 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。 7). 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。
相关面试题回答模板
面试官 了解过索引吗什么是索引
候选人 嗯索引在项目中还是比较常见的它是帮助MySQL高效获取数据的数据结构主要是用来提高数据检索的效率降低数据库的IO成本同时通过索引列对数据进行排序降低数据排序的成本也能降低了CPU的消耗
面试官 索引的底层数据结构了解过嘛 ?
候选人 MySQL的默认的存储引擎InnoDB采用的B树的数据结构来存储索引选择B树的主要的原因是第一阶数更多路径更短第二个磁盘读写代价B树更低非叶子节点只存储指针叶子阶段存储数据第三是B树便于扫库和区间查询叶子节点是一个双向链表
面试官 B树和B树的区别是什么呢
候选人 第一在B树中非叶子节点和叶子节点都会存放数据而B树的所有的数据都会出现在叶子节点在查询的时候B树查找效率更加稳定
第二在进行范围查询的时候B树效率更高因为B树都在叶子节点存储并且叶子节点是一个双向链表
面试官 什么是聚簇索引什么是非聚簇索引 ?
候选人
好的~聚簇索引主要是指数据与索引放到一块B树的叶子节点保存了整行数据有且只有一个一般情况下主键在作为聚簇索引的
非聚簇索引值的是数据与索引分开存储B树的叶子节点保存对应的主键可以有多个一般我们自己定义的索引都是非聚簇索引
面试官 知道什么是回表查询嘛 ?
候选人 嗯其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的回表的意思就是通过二级索引找到对应的主键值然后再通过主键值找到聚集索引中所对应的整行数据这个过程就是回表
【备注如果面试官直接问回表则需要先介绍聚簇索引和非聚簇索引】
面试官 知道什么叫覆盖索引嘛 ?
候选人 嗯~清楚的
覆盖索引是指select查询语句使用了索引在返回的列必须在索引中全部能够找到如果我们使用id查询它会直接走聚集索引查询一次索引扫描直接返回数据性能高。
如果按照二级索引查询数据的时候返回的列中没有创建索引有可能会触发回表查询尽量避免使用select *尽量在返回的列中都包含添加索引的字段
面试官 MYSQL超大分页怎么处理 ?
候选人 嗯超大分页一般都是在数据量比较大时我们使用了limit分页查询并且需要对数据进行排序这个时候效率就很低我们可以采用覆盖索引和子查询来解决
先分页查询数据的id字段确定了id之后再用子查询来过滤只查询这个id列表中的数据就可以了
因为查询id的时候走的覆盖索引所以效率可以提升很多
面试官 索引创建原则有哪些
候选人 嗯这个情况有很多不过都有一个大前提就是表中的数据要超过10万以上我们才会创建索引并且添加索引的字段是查询比较频繁的字段一般也是像作为查询条件排序字段或分组的字段这些。
还有就是我们通常创建索引的时候都是使用复合索引来创建一条sql的返回值尽量使用覆盖索引如果字段的区分度不高的话我们也会把它放在组合索引后面的字段。
如果某一个字段的内容较长我们会考虑使用前缀索引来使用当然并不是所有的字段都要添加索引这个索引的数量也要控制因为添加索引也会导致新增改的速度变慢。
面试官 什么情况下索引会失效 ?
候选人 嗯这个情况比较多我说一些自己的经验以前遇到过的
比如索引在使用的时候没有遵循最左匹配法则第二个是模糊查询如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。
我们之前还遇到过一个就是如果使用了复合索引中间使用了范围查询右边的条件索引也会失效
所以通常情况下想要判断出这条sql是否有索引失效的情况可以使用explain执行计划来分析
