Mysql B+树索引 笔记

935 阅读5分钟

声明

本文内容是简单整理了链接为 juejin.cn/post/684490… 的博客内容。如有侵权请告知,谢谢!

索引原理

通过不断缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机事件变成顺序事件。通俗解释,通过一组规则来缩小数据查询范围、减少查询次数,这组规则就是索引。

B+树索引

B+树

描述

  • 真实的数据存在于叶子节点;
  • 非叶子结点不存储真实数据,只存储指引搜索方向的数据项;

B+树如何提高数据库性能

数据库中的数据保存在磁盘上,访问磁盘的成本是访问内存的十万倍左右。所以想要提高数据库性能,必须控制访问磁盘次数,即控制磁盘IO次数。

B+树可以把磁盘IO次数控制在一个常数量级。举例说明:如上图所示,要查找43所代表的数据(以下简称43)。

  1. 将根节点所在的磁盘块从磁盘上读取到内存中,在内存中查找43,发现43在21-48之间;
  2. 将21-48所在的磁盘块从磁盘上读取到内存中,在内存中查找43,发现43在41-48之间;
  3. 将41-48所在的磁盘款从磁盘上读取到内存中,在内存中查找到43;

读取磁盘块3次,即产生了3次IO。如果没有索引,则需要遍历所有的磁盘块。

真实情况下,3层的B+树可以表示上百万的数据。

索引实现

MyISAM引擎

  • MyISAM的索引方式也叫做“非聚集”的。
  • MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
  • MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
  • 在MyISAM中,主索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。

InnoDB引擎

InnoDB的数据文件本身就是索引文件。在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。InnoDB的索引也叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

InnoDB的所有辅助索引都引用主键作为data域。辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

索引最左匹配特性

B+树是从左到右的顺序来建立搜索树的,所以检索数据时也是按照从左到右的顺序来检索的。

联合索引为 <a, b, c> , a、b、c均为表中一列。

数据举例 使用索引 备注
a,b,c a,b,c -
a a -
a,b a,b -
a,c a 因为缺失b索引,c索引不会使用
b,c - 因为缺失a索引,b,c索引不会使用
b - 因为缺失a索引,b,c索引不会使用
c - 因为缺失a,b索引,c索引不会使用

建立索引技巧

  1. 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

  2. =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

  4. 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

  5. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可,当然要考虑原有数据和线上使用情况。