Mysql索引底层及优化方法是什么


今天小编给大家分享一下Mysql索引底层及优化方法是什么的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下吧。索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。1. 哈希表是一种以键 – 值(key-value)存储数据的结构,我们只要输入待查找的键即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。不可避免地,多个 key 值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表。2. 说到bTree,就不得不提二叉树,二叉树分为很多,例:二叉查找树,平衡二叉树等。当然还有重点红黑树
1) 二叉查找树的特点是: 父节点左子树所有节点的值小于父节点的值。右子树所有节点的值大于父节点的值。 下面以一张图为例来体现二叉查找树。有一个需求,查找张三,如果不使用二叉查找树那么我们需要查找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中提到了普通索引需要回表操作,那有没有不需要回表的普通索引呢,答案是有的,在某个查询里面,索引已经覆盖了我们的查询需求,我们称为覆盖索引。这时是不需要回表操作的。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。举个例子:下面是这个表的初始化语句。在上面这个表 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.) 当组合索引中只要有一列含有null值,索引失效
2.) 在列上做计算索引失效,范围之后的索引全部失效
3.) 在查询条件上使用函数会造成索引失效
4.) 在where字句中使用 != 或 操作符,导致索引失效
5.) 避免使用or,导致索引失效
6.) 使用模糊查询也会造成索引失效,可以用like ‘a%’而不是like ‘%a%’
7.) 尽量使用覆盖索引,减少 select * 语句
8.) 满足最左前缀法则,最左前列开始并且不跳过索引中的列
9.) 字符串不加单引号索引失效新创建一个员工表,有5个属性,如下。第1条sql type的值为ref、字节是288 并且ref有3个const,全部生效。
第2条sql type的值为ref、字节是288 并且ref有3个const,全部生效。可以发现第3条sql type的值为ref、字节是126并且ref有2个const,全部生效。
第4条sql只有122字节并且ref只有1个const,只有name索引生效。
第5条sql type的值为all,字节和ref都是空,全部失效。第6条sql只有126字节并且type的值为range,范围查找,只有name和age索引生效。以上就是“Mysql索引底层及优化方法是什么”这篇文章的所有内容,感谢各位的阅读!相信大家阅读完这篇文章都有很大的收获,小编每天都会为大家更新不同的知识,如果还想学习更多的知识,请关注开发云行业资讯频道。

相关推荐: 安装mysql

一、实验环境在Linux系统中安装mysql二、操作脚本1.光盘挂载cd /mntlsmount /dev/cdrom /mnt2.建立yum仓库cd /etc/yum.repos.d/vim aaa.repo[aaa]name=testbaseurl=fil…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/28 21:01
下一篇 06/28 21:01

相关推荐