一起聊聊Mysql索引底层及优化

相关案例演示
本篇文章给大家带来了关于mysql中索引底层以及优化的相关知识,下面我们就整理一下mysql中索引的知识点,希望对大家有帮助。

Mysql索引篇

最近在很多网站上看了索引的相关知识,各种说法的都有,但是又不是很全,有的概念很模糊,下面是由小编整理的Mysql索引知识点。

一.首先我们说下什么是索引,为什么要用索引

索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

二. 索引类型分为两类:

1.hash索引

2.bTree

三.下面我们简单分析一下hash索引和bTree索引。

1. 哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的键即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。

不可避免地,多个 key 值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表。

2. 说到bTree,就不得不提二叉树,二叉树分为很多,例:二叉查找树,平衡二叉树等。当然还有重点红黑树
1) 二叉查找树的特点是: 父节点左子树所有节点的值小于父节点的值。右子树所有节点的值大于父节点的值。 下面以一张图为例来体现二叉查找树。

IDname
5张五
6张六
7张七
2张二
1张一
4张四
3张三

在这里插入图片描述有一个需求,查找张三,如果不使用二叉查找树那么我们需要查找7次,使用二叉查找树我们只需要查找4次就可以找到我们想要的值。
根据上面说的使用二叉查找树的确可以减少查询次数,但是大家有没有想过,如果数据库的数据是 1,2,3,4,5,6,7这样依次递增的数据呢,继续使用二叉查找树就会变成一个链表了。那这样如果我们想要查找7那么需要查找7次,扫描表也是需要7次。这样跟没有建立索引没有区别,这也是弊端之一。下图为例说明。
在这里插入图片描述
2) 平衡二叉树:又被称为AVL树,它的左右两个子树的高度差的绝对值不超过1,并且左右两个子树都是一棵平衡二叉树,AVL树是最早发明的自平衡二叉查找树。在AVL树中,任何节点的两个子树的高度最大差别只能为1,所以它又被称为高度平衡树。查询、增加和删除在平均和最坏情况下都是O(log n)。增加和删除会需要通过一次或多次树旋转来重新平衡这个树。
我们引入二叉树的目的是为了提高二叉树的搜索的效率,从而减少树的平均搜索长度,为此,就必须在每颗二叉树插入一个结点时调整树的结构,让二叉树搜索能够保持平衡,从而可能降低树的高度,减少的平均树的搜索长度。
平衡二叉树特点如下:
1.它的左子树和右子树都是AVL树
2.左子树和右子树的高度差不能超过1

例图:
在这里插入图片描述3) 红黑树:可以理解为红黑树是凌驾于平衡二叉树之上的一棵树,红黑树不会追求“完全平衡 ”,它只会求部分达到平衡要求,降低了对旋转的要求,从而提高性能。此外,由于它的设计,所有不平衡都能够在三次旋转之内解决。在红黑树中,它的算法时间复杂度与AVL相同,并且统计性能会逼AVL树更高。所以红黑树相对于平衡二叉树来说,不是严格意义上的平衡二叉树,红黑树插入和删除效率更高一些,查询的效率比平衡二叉树来说相对低一些,但是二者查询效率差值做对比,基本可以忽略不计。红黑树特点如下:
1. 节点是红色或黑色。
2. 根节点是黑色。
3. 每个红色节点的两个子节点都是黑色。(红色节点的子节点必须是黑色节点)
4. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。
故红黑树是黑色平衡的树,左子树与右子树高度差不会超过2。红节点的父节点、子节点只能是黑节点。
例图:
在这里插入图片描述
4) BTree(B树):当然上面说到了红黑树,性能非常高。以上图为例,树的高度最高才为4,共9条数据,但是对于Mysql数据库,动则几百万条数据,几千万条数据,那树的高度就不可估量了,比如说上百万条数据需要经过30-50次磁盘IO才能查询到数据,甚至更多的次数,显然不能满足Mysql索引高效的查询效率。那如果我们控制树的高度呢,那这样就会极大减少了请求磁盘IO的请求次数,如果高度控制在4,那只需要经过4次磁盘IO就可以查询到数据。
但是怎么样控制树的高度呢,红黑树是每个节点只存储一个元素,如果每个节点存多个元素呢,这样就可以解决高度问题了,肯定有同学有疑问,把所有的元素都放到一个节点上,那高度值就是1了,不是更快吗?这样想肯定是错的,Mysql每一次跟磁盘IO打交道是有大小限制的,Mysql限制每一个节点的大小是16K。 想查看自己Mysql限制节点大小的同学可以执行下面的sql。
show global status like ‘Innodb_page_size’
下面以图为例体现BTree
在这里插入图片描述BTree特点如下:
1.所有索引元素不重复
2.节点的数据索引从左到右依次递增
3.叶节点具有相同的深度,叶节点的指针为空
4.叶子节点和非叶子结点都存储索引和数据

5) B+树:上面说到了BTree控制了树的高度的问题,可以满足Mysql对于索引的需求,但是最终Mysq索引实现不是BTree而是B+树,Mysql对B树做了一点点改造,得到了B+树,也可以理解为B+树是B树的升级版。
下面以图为例说明:
在这里插入图片描述

从这张图可以看到,我们的非叶子节点只存储了索引并没有存储data,而且叶子节点间用指针相连。B树的叶子节点和非叶子节点都存储了索引和数据,而且叶子结点的指针为空,B+树把数据放在了叶子节点上,这样非叶子节点就可以存放更多的索引,每次从磁盘IO也能获取更多的索引。
B+树特点如下:
1.非叶子节点不存储data,只存储索引(冗余)和下层指针,可以放更多的索引
2.叶子节点包含所有索引字段,和数据
3.叶子节点用双指针连接,提高区间访问的性能

在百度上和很多博客上画的B+树是错误的哦,一定要避坑哦。
有兴趣看Mysql官方对B+树的解释的可以去看看。
链接: Mysql官网.

四.索引分类

1.按照索引的存储关联分类:分为两大类
1.)聚集索义(聚簇索引):叶节点包含了完整的数据记录,不需要回表。
2.)非聚集索引:需要回表,二次查树,影响性能。

1.1) 大家都知道Mysql常用的存储引擎有两种MyISAM和InnoDB,但是大家实际了解过两种存储引擎底层的数据存储结构吗?
下面以图为例为大家说明:
在这里插入图片描述其中test.myisam表是MyISAM存储引擎,actor表是InnoDB存储引擎,可以看到MyISAM存储引擎有三个文件,分别是frm、MYD、MYI,很容易理解frm-frame的简称,存的是表的结构,MYD-MYData存的是数据,MYI-MYIndex存的是索引,索引和数据是分开存储的,再看InnoDB只有frm、IBD,其中frm一样也是存的表的结构,IBD文件存的是索引和数据,这点InnoDB和MyISAM不一样。
下面以图为例说明MyISAM存储引擎主键索引是需要回表操作(非聚集索引
在这里插入图片描述其中15存的是主键索引,0x07存的是15所在行记录的磁盘文件地址指针,比如我们想找到15的数据,那首先应该先通过主键索引树,找到15所对应的指针,然后找到了这个指针再去MyD文件中找具体的数据,需要进行二次查找,这个过程称为回表操作。
2.1) 下面以图为例说明InnoDB存储引擎主键索引不需要进行回表操作。(聚集索引
在这里插入图片描述InnoDB存储引擎子节点首先15那一行存放的是索引,15下面的那一列存放的是索引所在行的其他所有字段,如果我们想要查15的数据,直接就可以找到,不需要在经过二次查树。

2. 按照功能分类:主要分为五大类
2.1 主键索引:InnoDB主键索引不需要回表操作
2.2 普通索引(二级索引):InnoDB普通索引需要回表操作,对于二级索引,会默认和主键做联合索引。
2.3 唯一索引
2.4 全文索引
2.5 联合索引:需要满足最左前缀原则

3. 在2.2中提到了普通索引需要回表操作,那有没有不需要回表的普通索引呢,答案是有的,在某个查询里面,索引已经覆盖了我们的查询需求,我们称为覆盖索引。这时是不需要回表操作的。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

举个例子:下面是这个表的初始化语句。

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),
(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

在上面这个表 T 中,如果我执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?
现在,我们一起来看看这条 SQL 查询语句的执行流程。看下图。
在这里插入图片描述
1.) 在 k 索引树上找到 k=3 的记录,取得 ID = 300;
2.) 再到 ID 索引树查到 ID=300 对应的 R3;
3.) 在 k 索引树取下一个值 k=5,取得 ID=500;
4.) 再回到 ID 索引树查到 ID=500 对应的 R4;
5.) 在 k 索引树取下一个值 k=6,不满足条件,循环结束。

在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。

在这个例子中,由于查询结果所需要的数据只在主键索引上有,所以不得不回表。

如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。

在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。又因为前面我们提到的,InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

五.索引优化

1.上面描述了索引基本概念、分类以及底层的基本结构相关知识。下面聊一聊索引优化的相关知识吧。

1.) 当组合索引中只要有一列含有null值,索引失效
2.) 在列上做计算索引失效,范围之后的索引全部失效
3.) 在查询条件上使用函数会造成索引失效
4.) 在where字句中使用 != 或 <> 操作符,导致索引失效
5.) 避免使用or,导致索引失效
6.) 使用模糊查询也会造成索引失效,可以用like ‘a%’而不是like ‘%a%’
7.) 尽量使用覆盖索引,减少 select * 语句
8.) 满足最左前缀法则,最左前列开始并且不跳过索引中的列
9.) 字符串不加单引号索引失效

2.下面以实战说明索引优化。

新创建一个员工表,有5个属性,如下。

create table employees(
id int primary key auto_increment comment '主键自增',
name varchar(30) not null default '' comment'名字',
age int not null default 1 comment '年龄',
id_card varchar(40) not null default '' comment '身份证号',
position varchar(40) not null default '' comment '位置'
);

-- 创建联合索引
create index name_index on employees (name,age,position);

-- 插入一条数据
insert into employees(name,age,id_card,position) values('张三',15,
'201124199011035321','北京');
--  下面以10条sql测试,注意建立的联合索引顺序是 name,age,position
1.explain select * from employees where age=15 and position='北京' and name='张三';

2.explain select * from employees where name='张三' and age=15 and position='北京';

3.explain select * from employees where age=15 and name='张三';

4.explain select * from employees where position='北京' and name='张三';

5.explain select * from employees where position='北京' and age=15;

6.explain select * from employees where position='北京' and age>15 and name='张三';

7.explain select * from employees where position='北京';

8.explain select * from employees where age=15;

9.explain select * from employees where name='张三';

10.explain select * from employees where name != '张三';
以上10条sql有哪些是索引失效,有哪些是索引没有失效的呢?
相信同学们已经有了答案,但是答案对不对呢,下面我们一起分析下。
首先说第1条,查询条件把3个索引全部用上了,但是索引的顺序有变化,由name,age,position变成
了age,position,name,想到这里肯定有很多同学给出的答案就是索引失效,但是事实证明这个结果
是错的,索引生效,肯定有很多同学疑惑,为什么呢,这条sql不满足最左原则法则呀,这就要涉及到sql
的执行流程了,这里博主简单说下,sql执行有1个优化器的过程,优化器的作用之一就是索引的选择优化,
所以优化器帮我们把索引的顺序变成正确的了,所以索引生效。
下面是第1条按照索引顺序sql和第2条没有按照索引顺序sql的执行结果。

执行结果入下图:可以发现全部生效。

第1条sql type的值为ref、字节是288 并且ref有3个const,全部生效。

在这里插入图片描述
第2条sql type的值为ref、字节是288 并且ref有3个const,全部生效。

在这里插入图片描述

想学习sql的执行流程的可以看博主的另一篇关于sql执行流程的文章哦。
有的同学有疑问了,那最左原则没有用了吗?
答案:有用的。
现在我们说下第3、4、5条sql
第3条:
explain select * from employees where age=15 and name='张三';
sql在执行的时候,优化器替我们把索引的顺序优化了,由 age -> name 变成 name -> age,这时
索引是生效的。
第4条:
explain select * from employees where position='北京' and name='张三';
索引顺序优化为name - > position,但是这时索引只有name索引生效,position没有生效,因为我
们建立的索引顺序是 name  -> age - > position,你会发现跳过了age,索引本质也是一棵树,少
了一个节点,下面的索引当然不会生效了,这就没有满足最左原则法则。
第5条:
explain select * from employees where position='北京' and age=15;
这就和第4条sql一样的道理了,第一个索引都不见了,后面的不可能生效。

执行结果如下:

可以发现第3条sql type的值为ref、字节是126并且ref有2个const,全部生效。

在这里插入图片描述
第4条sql只有122字节并且ref只有1个const,只有name索引生效。

在这里插入图片描述
第5条sql type的值为all,字节和ref都是空,全部失效。

在这里插入图片描述

下面说第6条sql,剩下的sql都是和之前的sql一样的道理。
explain select * from employees where position='北京' and age>15 and name='张三';
这条sql我们会发现,把索引字段全部使用了并且当作条件查询,不一样的是age是范围查找,优化器替我
们把索引顺序优化成 name  -> age - > position ,按照我们索引优化第2条:在列上做计算索引失效,范围之后的索引全部失效,想必答案同学们都知道了。

执行结果如下:

第6条sql只有126字节并且type的值为range,范围查找,只有name和age索引生效。

在这里插入图片描述

推荐学习:mysql视频教程

以上就是一起聊聊Mysql索引底层及优化的详细内容,更多请关注php中文网其它相关文章!

上一篇:php怎么给指定日期增加天数

下一篇:没有了

免责声明:本站所有文章和图片均来自用户分享和网络收集,文章和图片版权归原作者及原出处所有,仅供学习与参考,请勿用于商业用途,如果损害了您的权利,请联系网站客服处理。


来源:网友投稿 关注: 时间:2022-02-14 20:59
首先声明,只要是我们的vip会员所有源码均可以免费下载,不做任何限制(了解更多)
☉本站的源码不会像其它下载站一样植入大量的广告。为了更好的用户体验以后坚持不打水印
☉本站只提供精品织梦源码,源码在于可用,不在多!!希望在这里找到你合适的。
☉本站提供的整站织梦程序,均带数据及演示地址。可以在任一源码详情页查看演示地址
☉本站所有资源(包括源码、模板、素材、特效等)仅供学习与参考,请勿用于商业用途。
☉如有其他问题,请加网站客服QQ进行交流。
相关织梦教程
QQ在线咨询