公考在哪个网站上做试题,外贸平台实训总结,中山精品网站建设方案,wordpress 图片 二级域名前言
索引是存储引擎用于快速查找数据纪录的一种数据结构#xff0c;索引是数据库中经常提及的一个词#xff0c;究竟什么是索引#xff0c;索引的数据结构是什么#xff0c;索引有什么类型#xff1f;
本篇博客尝试阐述数据库索引的相关内容#xff0c;涉及什么是索引…
前言
索引是存储引擎用于快速查找数据纪录的一种数据结构索引是数据库中经常提及的一个词究竟什么是索引索引的数据结构是什么索引有什么类型
本篇博客尝试阐述数据库索引的相关内容涉及什么是索引索引的数据结构对比了聚集索引和非聚集索引分析了索引的类型以及使用原则对于MySQL中关于索引的技术名词进行了解释。 本系列文章合集如下
【合集】MySQL的入门进阶强化——从 普通人 到 超级赛亚人 的 华丽转身 目录 前言引出一、索引介绍1、什么是索引2、索引的优缺点 二、索引的数据结构1、MySQL索引的数据结构2、二叉查找树3、平衡二叉树4、B树5、B树 三、聚集索引与非聚集索引1、聚集索引聚簇索引2、非聚集索引非聚簇索引3、利用聚集索引查找数据4、利用非聚集索引查找数据 四、索引的类型1、主键索引2、唯一索引3、组合索引 五、索引的使用原则1、什么情况下不建索引2、索引失效场景 六、MySQL技术名词1、回表2、索引覆盖3、最左匹配4、索引下推 总结 引出 1.索引是存储引擎用于快速查找数据纪录的一种数据结构 2.索引的数据结构B树 3.主键作为B树索引的键值称为聚集索引以主键以外的列值作为键值构建的B树索引称为非聚集索引 4.索引不满足左前缀select *or分割的条件order by 非主键 5.%开头的Like模糊查询– 解决select和where条件中的字段都出现在索引 6.回表先查到主键再通过主键查数据查了B树两次 7.索引覆盖输出的列就是索引列无需回表
一、索引介绍
1、什么是索引 索引是存储引擎用于快速查找数据纪录的一种数据结构。 最典型的例子就是查新华字典通过查找目录快速定位到要查找的字。数据是存储在磁盘上的查询数据时如果没有索引会加载所有的数据到内存依次进行检索读取磁盘次数较多。使用索引就不需要加载所有数据MySQL是以B数的数据结构存储索引B树的高度一般在2-4层最多只需要读取2-4次磁盘查询速度大大提升。
2、索引的优缺点
1优点
避免进行数据库全表的扫描大多数情况只需要扫描较少的索引页和数据页提升查询语句的执行效率在使用分组和排序语句进行数据检索时可以显著减少查询中分组和排序的时间多表连接查询提高从其他表检测行数据的性能如果表具有多列索引则优化器可以使用索引的最左匹配前缀来查找提升数据检索的性能
2缺点
会降低表的增删改的效率因为每次对表记录进行增删改需要进行动态维护索引
二、索引的数据结构
1、MySQL索引的数据结构
MySQL索引常见的数据存储结构有哈希结构B树结构R树结构。其中R树结构用于空间索引不常见。
要介绍B树索引就不得不提二叉查找树平衡二叉树和B树这三种数据结构。B树就是在此基础上演化而来的。
2、二叉查找树
首先让我们先看一张图 从图中可以看到我们为user表用户信息表建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点节点中存储了键(key)和数据(data)。键对应user表中的id数据对应user表中的行数据。
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值右子节点的键值都大于当前节点的键值。 顶端的节点我们称为根节点没有子节点的节点我们称之为叶节点。
二叉查找树的演示动画图解可通过如下网址进行操作演示
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 如果我们需要查找id12的用户信息利用我们创建的二叉查找树索引查找流程如下
将根节点作为当前节点把12与当前节点的键值10比较12大于10接下来把比当前节点大的右子节点作为当前节点。继续把12和当前节点的键值13比较发现12小于13把当前节点的左子节点作为当前节点。把12和当前节点的键值12对比12等于12满足条件我们从当前节点中取出data即id12,namexm。
利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话我们需要6次才能找到。
3、平衡二叉树
上面我们讲解了利用二叉查找树可以快速的找到数据。但是如果上面的二叉查找树是这样的构造 这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id17的用户信息我们需要查找7次也就相当于全表扫描了。导致这个现象的原因其实是二叉查找树变得不平衡了也就是高度太高了从而导致查找效率的不稳定。
为了解决这个问题我们需要保证二叉查找树一直保持平衡就需要用到平衡二叉树了。
平衡二叉树又称AVL树在满足二叉查找树特性的基础上要求每个节点的左右子树的高度差不能超过1。
平衡二叉树保证了树的构造是平衡的当我们插入或删除数据导致不满足平衡二叉树不平衡时平衡二叉树会进行调整树上的节点来保持平衡。
4、B树
1因为内存的易失性。一般情况下我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。如果我们采用平衡二叉树这种数据结构作为索引的数据结构每查找一次数据就需要从磁盘中读取一个节点即我们说的一个磁盘块。平衡二叉树每个节点只存储一个键值和数据也就是说每个磁盘块仅仅存储一个键值和数据。如果要存储海量的数据二叉树的节点将会非常多高度也会很高查找数据时就会进行很多次磁盘IO查找数据的效率降低。
2为了解决平衡二叉树的这个弊端我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的B树。
3B树Balance Tree即为平衡树的意思下图即是一颗B树。 图中的p节点为指向子节点的指针二叉查找树和平衡二叉树其实也有图中的每个节点称为页页就是我们上面说的磁盘块在mysql中数据读取的基本单位都是页。从上图可以看出B树相对于平衡二叉树每个节点存储了更多的键值(key)和数据(data)并且每个节点拥有更多的子节点子节点的个数一般称为阶上述图中的B树为3阶B树高度也会很低。基于这个特性B树查找数据读取磁盘的次数将会很少数据的查找效率也会比平衡二叉树高很多。
4假如我们要查找id28的用户信息那么我们在上图B树中查找的流程如下
先找到根节点也就是页1判断28在键值17和35之间我们那么我们根据页1中的指针p2找到页3。将28和页3中的键值相比较28在26和30之间我们根据页3中的指针p2找到页8。将28和页8中的键值相比较发现有匹配的键值28键值28对应的用户信息为 28hello
5、B树
B树是对B树的进一步优化。让我们先来看下B树的结构图 1B树非叶子节点上是不存储数据的仅存储键值这么做是因为在数据库中页的大小是固定的innodb中页的默认大小是16KB。如果不存储数据那么就会存储更多的键值相应的树的阶数节点的子节点树就会更大树就会更矮更胖如此一来我们查找数据进行磁盘的IO次数有会再次减少。
2B树的阶数是等于键值的数量如果我们的B树一个节点可以存储1000个键值那么3层B树可以存储1000×1000×100010亿个数据。一般根节点是常驻内存的所以一般我们查找10亿数据只需要2次磁盘IO。
3因为B树索引的所有数据均存储在叶子节点而且数据是按照顺序排列的。那么B树使得范围查找排序查找分组查找以及去重查找变得非常简单。
4B树中各个页之间是通过双向链表连接的叶子节点中的数据是通过单向链表连接的。上图中的B树索引就是innodb中B树索引的实现方式准确的说应该是聚集索引在MyISAM中B树索引的叶子节点并不存储数据而是存储数据的文件地址。
三、聚集索引与非聚集索引
在MySQL中B树索引按照存储方式的不同分为聚集索引和非聚集索引
1、聚集索引聚簇索引
以innodb作为存储引擎的表表中的数据都会有一个主键即使你不创建主键系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B树中的而B树的键值就是主键在B树的叶子节点中存储了行数据可以直接在聚集索引中查找到想要的数据。这种以主键作为B树索引的键值而构建的B树索引我们称之为聚集索引。
2、非聚集索引非聚簇索引
1以主键以外的列值作为键值构建的B树索引我们称之为非聚集索引。
2非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据而是存储该列对应的主键想要查找数据我们还需要根据主键再去聚集索引中进行查找这个再根据聚集索引查找数据的过程我们称为回表。
3如果使用了覆盖索引则不需要回表直接通过辅助索引就可以查找到想要的数据。覆盖索引就是指select查询的数据只需要在索引中就能取得而不必读取数据行换句话说就是查询列要被所建的索引覆盖。
3、利用聚集索引查找数据
查找id18并且id40的用户数据。对应的sql语句为select * from user where id18 and id 40其中id为主键。具体的查找过程如下
1根节点是常驻内存的也就是说页1已经在内存中了此时不需要到磁盘中读取数据直接从内存中读取即可。从内存中读取到页1要查找这个id18 and id 40或者范围值我们首先需要找到id18的键值。从页1中我们可以找到键值18此时我们需要根据指针p2定位到页3。
2要从页3中查找数据我们就需要拿着p2指针去磁盘中进行读取页3。从磁盘中读取页3后将页3放入内存中然后进行查找我们可以找到键值18然后再拿到页3中的指针p1定位到页8。
3同样的页8页不在内存中我们需要再去磁盘中将页8读取到内存中。将页8读取到内存中后。因为页中的数据是链表进行连接的而且键值是按照顺序存放的此时可以根据二分查找法定位到键值18。
4因为是范围查找而且此时所有的数据又都存在叶子节点并且是有序排列的那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据然后页8中就没有数据了此时我们需要拿着页8中的p指针去读取页9中的数据。因为页9不在内存中就又会加载页9到内存中并通过和页8中一样的方式进行数据的查找直到将页12加载到内存中发现41大于40此时不满足条件。那么查找到此终止。
4、利用非聚集索引查找数据
1非聚集索引叶子节点中不再存储所有的数据存储的是键值和主键。非聚集索引也称之为辅助索引普通索引、二级索引一个表中只有一个聚集索引可以有多个非聚集索引。
2辅助索引的搜索过程
比如我们给name字段建立索引InnoDB 都会给每个加了索引的字段生成索引树此时会建立name字段的索引B树节点中存储的是name索引字段的数据叶子节点中存储了索引字段的数据和主键的值。拿到主键 KEY 后InnoDB 才会去主键索引树里根据刚在name 索引树找到的主键 KEY 查找到对应的数据。【两次查询回表操作】
3执行select * from user where name ‘jim’ 的执行过程
根节点是常驻内存的也就是说页1已经在内存中了从内存中读取到页1根据指针P1定位到页2根据页2中的P2指针定位到页6比较找到索引对应的值为jim同时获取到主键为20在根据20这个主键到主键索引中获取到这个叶子节点中存储的行数据。 四、索引的类型
1、主键索引
在创建表的时候添加了主键PRIMARY KEY 的字段会在该字段上默认创建主键索引。
创建表employee create table employee(empno int primary key not null auto_increment,ename varchar(10),job varchar(10),sal decimal(10,2),hiredate datetime,deptno int
);2、唯一索引
唯一索引索引列的值必须唯一唯一索引和主键索引的区别就是唯一索引允许出现空值而主键索引不能为空
-- 创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))-- 修改表结构的方式创建索引
ALTER TABLE table_name ADD UNIQUE index indexName (column(length))-- 删除索引
DROP INDEX index_name ON table创建索引 删除索引 create table employee(empno int primary key not null auto_increment,ename varchar(10),job varchar(10),sal decimal(10,2),hiredate datetime,deptno int
);create unique index idx_name on employee(ename);alter table employee add unique index idx_name (ename);-- 删除索引
DROP INDEX idx_name ON employee-- 创建表的时候同时创建索引
CREATE TABLE table (id INT ( 11 ) NOT NULL AUTO_INCREMENT,title CHAR ( 255 ) NOT NULL,content text NULL,time INT ( 10 ) NULL DEFAULT NULL,PRIMARY KEY ( id ),index idx_title (title)
);3、组合索引
在多个列上创建的索引就是组合索引。
alter table t add index index_name(a,b,c);示例
alter table employee add index idx_name_job_sal(ename,job,sal);组合索引的用处假设我现在表有个多个字段id、name、age、gender然后我经常使用以下的查询条件
select * from user where name xx and age xx这个时候我们就可以通过组合 name 和 age 来建立一个组合索引加快查询效率。
在多个字段上创建索引遵循最左匹配原则。
五、索引的使用原则
1、什么情况下不建索引
表记录太少区分度不高的字段不适合建立索引如性别等where条件中用不到的字段不适合建立索引经常插入、删除、修改的表要减少索引textimage 等类型不适合建立索引。MySQL能估计出全表扫描比使用索引更快的时候不使用索引参与列计算的列不适合建索引
2、索引失效场景
1不满足最左前缀
所谓最左前缀可以想象成依次执行的过程假设我们有一个复合索引【ename,sal,job】那这个依次顺序是enamesaljob。
create index idx_ename_job_sal on employee(ename,sal,job)以下sql会走索引
select * from employee where enamezs and sal1000 and jobsales最左前缀出现跳跃的情况会导致索引失效。
select * from employee where jobsales
select * from employee where jobsales and sal1000出现跳跃的情况满足最左前缀会走索引
select * from employee where enamezs and sal1000
select * from employee where enamezs and jobsales
select * from employee where sal1000 and enamezs -- 内部优化器会进行调整2范围查询之后
范围查询之后的索引字段会失效但本身用来范围查询的那个索引字段依然有效。
以下示例job列的索引失效通过长度可以看出。
select * from employee where enamezs and sal1000 and jobsales3索引字段做运算
对索引字段做运算使用函数等都会导致索引失效。
select * from employee where substring(ename,2,3)aa;4隐式类型转换
索引字段为字符串类型由于在查询时没有对字符串加单引号MySQL的查询优化器会自动的进行类型转换造成索引失效。
select * from employee where ename115避免使用select *
无法使用覆盖索引消耗更多的 CPU 和 IO 以网络带宽资源。
6or分割的条件
用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。
select * from employee where enamezs or deptno207 以%开头的Like模糊查询
解决方法使用覆盖索引
如果一个索引包含了或覆盖了满足查询语句中字段与条件的数据就叫做覆盖索引。即select和where条件中的字段都出现在索引中即为覆盖索引
select ename from employee where ename like %A%8order by导致索引失效
在基于order by和limit进行使用时是否走索引涉及到数据库版本。
主键使用order by时可以正常走索引。
六、MySQL技术名词
1、回表
首先我们需要知道建立几个索引就会生成几棵BTree但是带有原始数据行的BTree只有一棵另外一棵树上的叶子节点存储的是主键值。
例如我们通过主键建立了主键索引在叶子节点上存放的是行数据。 当我们创建了两个索引时一个是主键一个是name此时会在生成两棵BTree。name索引这棵树的叶子节点存放的是name列的值和主键值当我们通过name进行查找数据时会得到一个主键然后在通过主键再去上面的这个主键BTree中进行查找数据这个操作称之为回表 当执行下面的SQL语句时会查找第一颗树直接返回数据。
select * from tb where id 1当执行下面的SQL查询时会先查找第二棵树得到主键的值然后根据主键的值再去主键索引的BTree中查找数据。
select * from tb where name xm2、索引覆盖
我们看下面的两个SQL语句看看它们的查询过程是一样的么
select * from tb where id 1
select name from tb where name zs答案是不一样的首先我们看第二个语句输出的列就是索引列。当我们通过name建立的BTree进行查询时此时叶子节点就已经包含要查找的name数据无需再到主键索引BTree上进行数据查找这样的查询称之为索引覆盖。索引覆盖不需要进行回表查询大大提示性能。
3、最左匹配
这里提到的 最左匹配 和 索引下推 都是针对于组合索引的。
例如我们有这样一个索引
name age组合索引
必须要先匹配name才能匹配到age。这个我们就被称为最左匹配。
例如下面的几条SQL语句那些语句不会使用组合索引
where name ? and age ?
where name ?
where age ?
where age ? and name ?根据最左匹配原则第 3条SQL 不会使用组合索引。
那为什么4的顺序不一样也会使用组合索引呢
其实内部的优化器会进行调整例如下面的一个连表操作
select * from tb1 join tb2 on tb1.id tb2.id其实在加载表的时候并不一定是先加载tb1在加载tb2而是根据表的大小决定的小的表优先加载进内存中。
4、索引下推
索引下推index condition pushdown 简称ICP意思是解析索引列找到符合条件的数据。
在Mysql5.6的版本上推出用于优化查询。索引下推在非主键索引上的优化可以有效减少回表的次数大大提升了查询的效率。
先使用where条件过滤索引过滤完索引后找到所有符合索引条件的数据行然后用 WHERE 子句中的其他条件去过滤这些数据行。
创建表
create table student(id int not null auto_increment,name varchar(10),age int,sex char(1),address varchar(20),primary key(id),key idx_name_age (name,age)
)ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8explain select * from student where name like 王% and age20执行如下SQL
explain select * from student where name like 王% and age205.6之前的版本是没有索引下推优化执行的过程如下图
会忽略age这个字段直接通过name进行查询在(name,age)这课树上查找到了两个结果id分别为2,1然后拿着取到的id值一次次的回表查询因此这个过程需要回表两次。 5.6版本添加了索引下推这个优化执行的过程如下图
InnoDB并没有忽略age这个字段而是在索引内部就判断了age是否等于20对于不等于20的记录直接跳过因此在(name,age)这棵索引树中只匹配到了一个记录此时拿着这个id去主键索引树中回表查询全部数据这个过程只需要回表一次。 总结
1.索引是存储引擎用于快速查找数据纪录的一种数据结构 2.索引的数据结构B树 3.主键作为B树索引的键值称为聚集索引以主键以外的列值作为键值构建的B树索引称为非聚集索引 4.索引不满足左前缀select *or分割的条件order by 非主键 5.%开头的Like模糊查询– 解决select和where条件中的字段都出现在索引 6.回表先查到主键再通过主键查数据查了B树两次 7.索引覆盖输出的列就是索引列无需回表
