声明
本文内容是简单整理了链接为 juejin.cn/post/684490… 的博客内容。如有侵权请告知,谢谢!
索引原理
通过不断缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机事件变成顺序事件。通俗解释,通过一组规则来缩小数据查询范围、减少查询次数,这组规则就是索引。
B+树索引
描述
- 真实的数据存在于叶子节点;
- 非叶子结点不存储真实数据,只存储指引搜索方向的数据项;
B+树如何提高数据库性能
数据库中的数据保存在磁盘上,访问磁盘的成本是访问内存的十万倍左右。所以想要提高数据库性能,必须控制访问磁盘次数,即控制磁盘IO次数。
B+树可以把磁盘IO次数控制在一个常数量级。举例说明:如上图所示,要查找43所代表的数据(以下简称43)。
- 将根节点所在的磁盘块从磁盘上读取到内存中,在内存中查找43,发现43在21-48之间;
- 将21-48所在的磁盘块从磁盘上读取到内存中,在内存中查找43,发现43在41-48之间;
- 将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索引不会使用 |
建立索引技巧
-
最左前缀匹配原则,非常重要的原则,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的顺序可以任意调整。
-
=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
-
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
-
索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
-
尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可,当然要考虑原有数据和线上使用情况。