福田网站建设结业论文,农业公园网站建设,网站logo下载,一 网站建设管理基本情况众所众知#xff0c;MySql的查询效率以及查询方式#xff0c;基本上和索引息息相关#xff0c;所以#xff0c;我们一定要对MySql的索引有一个具体到数据底层上的认知。
这一次也是借着整理的机会#xff0c;和大家一起重新复习一下MySql的索引底层。
本节也主要有一下的…众所众知MySql的查询效率以及查询方式基本上和索引息息相关所以我们一定要对MySql的索引有一个具体到数据底层上的认知。
这一次也是借着整理的机会和大家一起重新复习一下MySql的索引底层。
本节也主要有一下的几个内容
索引数据结构红黑树HashB树详解千万级数据表如何用B树索引快速查找聚集索引非聚集索引到底是什么为什么总推荐使用自增主键做索引联合索引底层数据结构又是怎样的Mysql最左前缀优化原则是怎么回事
各位看官可以各取所需希望能够对大家有所帮助。
一句话总结索引是帮助MySQL高效获取数据的排好序的数据结构
为什么需要索引 各位都知道对一个没有索引的表来说我们只能按行查找。
这似乎看起来也不会慢嘛一个个往下找嘛。
但是问题在于对于MySql来讲数据是存在磁盘的数据存在磁盘是逻辑上的连续而不是物理上的连续。 假设我们要执行以下的Sql
select * from t where t.col2 89那我们需要进行多次I/O到磁盘中找我们所需要的数据那这就会导致我们的查询速度很慢。
注意和磁盘进行I/O是一件很慢的事情。
索引的数据结构
我们知道索引的存在是为了帮助我们能够快速的获取数据的排好序的数据结构
有这种功能的我们平时学的数据结构中主要有下面几个
二叉树 二叉排序树平衡二叉树 红黑树Hash表B-TreeB Tree
对于二叉树和红黑树来说两个的区别在各位学习HashMap的时候就应该都知道了这里我们就直接跳过。
但是对于这两者来说都有一个很严重的问题
随着数据量的增大树的高度会逐渐增大。 就比如上面这棵红黑树你要找这个0007你是不是要进行4次I/O这肯定是没办法接受的。
所以我们的目标是能够尽可能的把更多的数据一起放到树的每一层来减少我I/O的次数 B-Tree B树是一种多路平衡树用这种存储结构来存储大量数据它的整个高度会相比二叉树来说会矮很多 B树的每一个节点其实可以理解成一个K-V
相比于红黑树由于每一层能够存储的数据量多了高度肯定是变低了磁盘I/O次数少了性能就提升了。
看似不错但是几个问题 首先我们要知道MySql为了能够更快速的查询数据会将数据通过内存页的方式读入到内存中一个页是16K。可以通过以下语句查看 SHOW GLOBAL STATUS like Innodb_page_size;我们的每一个节点都存了数据如果这个数据稍微的打了一点那我这一页才能存多少数据啊这种方式就会导致我们的B树的高度会有问题。 还有一个问题如果是进行范围查找呢B树似乎并不是很方便。
所以MySql并不是以B树作为我们索引的数据结构 B Tree 基于上面的问题我们引出了B树 B树相比于B树有两个特点
B树的只有叶子节点存储数据其他节点只存储索引冗余B树的叶子节点是一个双向链表。
我们先看看第一个特点其实B树的非叶子节点和我们的跳表差不多 每个节点都不存储数据只是为了构建这个B树。那这样带来了什么好处呢
前面我们说的mysql的内存页是16KB也就是说每次我们一个节点最多存储16KB的索引假设我们用bigint作为索引值也就是8个字节加上向下的指针大概是6个字节 算出来16KB / 14B 1170。
下面的带数据的叶子节点正常也不会太大我们就算一个1KB算出来就是16个
1170 * 1170 * 16 21,902,400
可以看到当树的高度为3的时候就可以通过3次查询覆盖2000万的数据。
所以对于B树来说相比于B树更加的矮壮。
同时因为下面是一个双向链表对于范围查询来说是不是能够更加快速的查出结果。 Hash 默认来说mysql都是使用B Tree来存储索引的但是你仔细看其实是可以选择Hash的
对于Hash的话其实大家都不会陌生。 Hash的特点就是查的够快只要一次就能查出来
缺点也很明显Hash冲突啊不支持范围查找。
索引是怎么存储的
因为现在MyISAM基本上没遇到过所以这里只对InnoDB进行介绍
注意存储引擎是基于表的而不是数据库 我们打开mysql对应的文件位置进入到里面的data由于我目前是在test库所以点进去test文件 我们可以看到对应的表有这两个文件
.frm表结构信息.ibd数据和索引
.ibd文件其实就是存储我们整张表的所有数据他会通过B树的方式进行存储
换句话说表数据文件本身就是按BTree组织的一个索引结构文件其实也就是我们的聚集索引
这里可以简单的说明这个聚集的概念 聚集索引其实就是将索引和文件放在同一个文件里像InnoDB就都放在.ibd文件中 而相对应的叫做非聚集索引也就是索引和文件不放在同一个文件里在MyISAM中就是这么设计的索引在.MYD文件数据在.MYI文件中。 而对于InnoDB的二级索引其实也是非聚集索引
建议InnoDB表必须建主键
知道了索引是怎么存储的那我们也就能知道为什么建议InnoDB表必须建主键
如果有了主键我们可以直接根据主键生成一个聚集索引来保存我们的数据。
所以在大多数情况下聚集索引 主键索引 没有主键是怎么存储的 InnoDB表没有主键也是可以建表的
如果没有主键它会从第一列开始找如果第一列所有数据都不相等他就会直接拿第一列来组织B树
如果第一列没选到就继续往后如果都没选到它会帮你建一个隐藏列隐藏列给每一行一个唯一的ID然后通过这个隐藏列来构建B树
但是对于MySql来说这其实不应该由它来做的。这纯属浪费资源
推荐使用整型的自增主键 整型 首先我们知道我们通过索引找数据的过程其实就是一个数据比大小的过程。
所以相比于整型其他的肯定没有它快
就那字符串来说字符串比较是要逐位进行比较的特别是越长的字符串比较的次数越多速度肯定越慢。虽然影响没有那么大毕竟是在内存中比较的但是肯定是有影响的。
同时字符串的空间占用一般要比整型的大
一方面存储的成本提高了另一方面按照我们上面的分析B树的矮壮性肯定是会受影响的。 自增 首先我们要先知道对于B树来说非顺序的插入会增加B树的工作具体的话大家可以通过数据结构网址来模拟这个过程在这里我就不再说了。
但是如果是顺序插入那B树就会一直往后去增加这种效率肯定要比非顺序的效率高。
非主键索引二级索引 非主键索引也成为二级索引非聚集索引和主键索引一样也是才用B树来进行存储。
不一样的是二级索引下面的值是存储主键的值。
这样是为了能够节省空间和保持一致性
节省空间很好理解如果把所有数据都丢进来占用的空间太大
保持一致性也很好理解如果把所有数据丢进来那修改数据的时候主键索引和非主键索引都要改这就很麻烦。
当然这样的索引也有一个问题就是由于数据和索引不是在一起的所以往往我们查出来数据之后就需要进行回表根据主键索引找到对应的数据。
联合索引
联合索引就是多个字段共同构成的索引 最左匹配原则 各位可以先想一想如果让你来设计你要怎么存储来做到有序呢
是不是就是一个字段一个字段的排序先第一个有序然后在考虑第二个。
那这也就引出了我们的最左匹配原则
就拿我们上面的例子他会先比较name然后比较age最后比较position
SELECT * FROM employees WHERE nameBill and age31;
SELECT * FROM employees WHERE age 30 AND positiondev;从上面的图我们可以知道第一条Sql肯定能走索引但是第二条呢
是不是发现了走不了了因为出去name了之后这个索引已经不是有序的了
这就是我们的最左匹配原则。
那我们继续往下走如果遇到范围查询like左匹配呢
SELECT * FROM employees WHERE nameBill and age 30 and positiondev;前面两个走索引大家应该没什么疑问但是到了第三个呢从上面的图看好像没问题
但如果是这样呢 是不是发现又是无序的了
但是这里有一个比较有意思的如果是、、between等这些带等于的他其实是走了索引的。
SELECT * FROM employees WHERE nameBill and age 30 and positiondev;虽然在符合 age 30 条件的二级索引记录的范围里position 字段的值是「无序」的但是对于符合 age 30 的二级索引记录的范围里position 字段的值是「有序」的因为对于联合索引是先按照 age 字段的值排序然后在 age 字段的值相同的情况下再按照 position 字段的值进行排序。
于是在确定需要扫描的二级索引的范围时当二级索引记录的 age 字段值为 30 时可以通过 position ‘dev’ 条件减少需要扫描的二级索引记录范围position 字段可以利用联合索引进行索引查询的意思。也就是说从符合 age 30 and position ‘dev’ 条件的第一条记录开始扫描而不需要从第一个 age 字段值为 30 的记录开始扫描。
所以这条查询语句都用到了联合索引进行索引查询。
这其实也用到了我们另一个知识点索引下推
索引下推可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。
