网站建设排版页面,哪个网站使用vue 做的,男孩子怎么做网站赚钱,wordpress json数据一、前言 首先这个博客会介绍一些关于MySQL中索引的基本内容以及一些基本的语法#xff0c;当然里面也会有些常见的面试题的解答。
二、关于索引
1、概念 索引是一种能够帮助MySQL高效的去磁盘检索数据的一种数据结构。在MySQL的Innodb存储引擎中呢#xff0c;采用的是B树的…一、前言 首先这个博客会介绍一些关于MySQL中索引的基本内容以及一些基本的语法当然里面也会有些常见的面试题的解答。
二、关于索引
1、概念 索引是一种能够帮助MySQL高效的去磁盘检索数据的一种数据结构。在MySQL的Innodb存储引擎中呢采用的是B树的结构去实现索引和数据的存储。
2、原理
未添加索引 如图所示当我们未添加索引时假如以id查询某个学生信息时我们的MySQL会做一个全表的扫描查询不管你查询的是id1还是id5。所以当数据量增多时我们的查询时间就会增多。
假设现在我们以id建立索引 如图所示当我们建立以id为索引时MySQL在查询的时候我们的id索引会形成二叉树的结构该二叉树的前提是平衡二叉树当然还有B树、B树的数据结构如这时我们要查找id3的学生MySQL就从id2开始32,走右子树找到443走左子树找到3。这时查找速度远快于没有索引时的结构。
三、常见索引的种类
1、主键索引Primary key
只要我们创建了主键primary key那么它就主动成了一个索引称为主键索引。
2、唯一索引(Unique)
在我们的表的字段中我们创建了唯一约束unique,那么该字段是唯一的同时也是索引称为唯一索引。
3、普通索引(Index)
普通索引是最基本的索引它没有任何限制。这也是我们用的最多的索引机制。
4、全文索引(Fulltext)
全文索引适用于MyISAM存储引擎。
四、常见的索引的指令
①查询索引
show indexs from 表名
②添加索引
普通索引
1alter table 表名 add index 索引名称(列名);
2create index 索引名称 on 表名(列名);
唯一索引 create unique index 索引名称 on 表名(列名);
主键索引
alter table 表名 add primary key 列名;
③删除索引
drop index 索引名称 on 表名
删除主键索引
alter table 表名 drop primary key
④查询索引
1show index from 表名
2show inedexs from 表名
3show keys from 表名
4desc 表名。
五、适合索引的情况
①比较频繁的作为查询条件的字段应该创建索引
②唯一性太差的字段不合适 单独作为索引即使频繁的作为查询条件如人的性别有男、女两种状态唯一性差
③更新非常频繁的字段不适合创建索引
④不会出现在where或者having子句中的字段不应创建索引。
六、市面上高频常见的索引的面试回答
这里呢是博主找了一些市面上常见的有关索引的面试题做个总结以便于后期的复习。
1、谈谈MySQL索引的优缺点
优点
①通过B树的结构来存储数据可以大大减少数据检索时的磁盘IO的次数从而提升数据查询的性能
②B树索引在进行范围查找的时候只需要找到起始节点然后基于叶子节点的链表结构往下读取即可查询效率较高
③通过唯一索引的约束可以保证数据表中每一行数据的唯一性
缺点
①数据的增加、修改、删除需要涉及到索引的维护当数量较大的情况下索引的维护会带来较大的性能开销
②一个表中允许存在一个聚簇索引和多个非聚簇索引但是索引数不能创建多个否则会造成索引维护成本过高
③创建索引的时候需要考虑到索引字段值的分散性如果字段的重复数据过多创建索引反而会带来性能的降低。
2、聚簇索引与非聚簇索引区别
MySQL的索引从物理存储的角度对索引进行分类可以分为聚簇索引Innodb与非聚簇索引(MyISAM)
聚簇索引所谓聚簇索引就是指主索引文件和数据文件为同一份文件聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中BTree的叶子节点上的data就是数据本身。因此从聚簇索引中获得数据要比在非聚簇索引中查找更快。
首先我们可以看到我们的Innodb存储引擎中有以下两个文件 其中.frm文件表示表的结构.ibd文件表示Innodb 数据表索引数据它是索引与数据在同一个文件中是聚合在一起的。
其次聚集索引的结构如下我们可以清晰看到数据和索引存储在同一个文件之中的。 非聚簇索引 非聚簇索引就是指BTree的叶子节点上的data并不是数据本身而是保存的实际指向存放数据块的指针。通过辅助索引首先找到的是主键值再通过主键值找到数据行的数据页主要用在MyISAM存储引擎中。 非聚簇索引需要先查询一遍索引文件得到索引根据索引获取数据比聚簇索引多了一次读取数据的IO操作所以查找性能上会差。
首先我们可以看到我们的MyISAM存储引擎中有以下三个文件 其中.frm文件表示我们表的结构.MYD文件表示我们MyISAM的表数据.MYI文件表示我们MyISAM的索引所以它的表的数据和索引是通过非聚合的方式储存的。
其次聚集索引的结构如下我们可以清晰看到数据和索引存储是不在同一个文件之中的
3、什么情况下mysql会索引失效
①where 后面使用函数 ②使用or条件③ 模糊查询 %放在前边 ④类型转换 ⑤组合索引 最佳左前缀匹配原则
4、什么是联合索引以及其优点
联合索引是指两个或更多个列上的索引被称作联合索引联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段一个查询可以只使用索引中的一部份但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找但不支持 b,c进行查找 .当最左侧字段是常量引用时索引就十分有效。
优点
①减少开销
建一个联合索引(a,b,c),实际相当于建了(a),(a,b),(a,b,c)三个索引.每多一个索引,都会增加写操作的开销和磁盘空间的开销.对于大量数据的表,使用联合索引会大大的减少开销!
②效率高
索引列多,通过联合索引筛选出的数据越少。
③覆盖索引
对联合索引(a,b,c),如果有如下sql的 select a,b,c from table where axxx and bxx; 那么mysql可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
5、什么是索引下推、回表查询、索引覆盖
①回表查询
当我们的SQL查询要的是全部数据无法从普通索引里面去获得时需要做二次查询通过聚集索引中把所有的数据取出来。这个过程就是回表查询。如下图所示
如图所以假设有个user表里面包含id、name、phone、age这些字段我们以name建立普通索引那么此时就会以name的数据经过B树算法形成了BTree存储到硬盘如图中右图所示其叶子节点存的是最终数据包含name当前索引列的数据、id主键列的数据非叶子节点存储的是一个键值通过键值定位到最终的数据。此时我们的查询语句是select * from user where name ***,这时执行流程先name形成的二叉树查数据找到name和id;其次我们需要的是完整数据select *就会通过id去我们的聚簇索引上去查的最终完整的数据。这个过程就是回表。
PS:对于主键索引的树如果用户设置了主键则会生成主键索引若没有主键Innodb会优先选择一个unique键作为主键若主键和unique都没有的话则Innodb会自动为用户添加一个叫做DB_ROW_ID的键作为默认主键只不过这个键我们看不见。所以对于Innodb来讲主键索引一定是存在的。
②索引下推
索引下推简称ICP,是在MySQL5.6的版本上推出用于优化查询。
用以下案例做个讲解
select * from student where name like 李% and age18;
未设置索引下推的情况
首先设置了index(nameage)那么在执行语句时根据最左前缀法则该语句搜索索引树时只能匹配到名字里第一个字为李的记录接下来从该记录开始逐个回表到主键索引上找到相应的记录再和age这个字段做比较看值是否合适。
如上图所示未配置索引下推那么在语句执行时先找到姓李的用户而不会看age的值然后分别去主键索引中根据id查询数据再结合age做过滤一共要回表4次。
若做了索引下推
Innodb在name、age索引内部就做了age是否为18的判断对于不符合的数据直接跳过减少了回表的次数从而提高整体的性能。如下图所示 ③索引覆盖
索引覆盖是一种避免回表查询的优化策略只需在一棵索引树上就能获取SQL所需的所有列数据无需回表速度更快。
实现方式将查询的字段建立 普通索引或者联合索引这样就可以直接返回索引中的数据不需要通过聚集索引去定位行记录避免了回表的情况发生。案例上面那个联合索引的优点提到。
注意事项
如果一个索引包含了所需的查询的所有字段的值不需要回表这个索引就是覆盖索引。
MySQL只能使用BTree索引做覆盖索引因为只有BTree能储存索引列值。
七、总结
这篇博客记录索引的一些基本原理基本使用以及常见的面试题。通过博客的记录方便博主后期的复习也希望对大家有所帮助记得点赞、关注支持博主一波哦~后期还有更多内容
