首页 » MySQL » MySql 数据库“索引”初体验

MySql 数据库“索引”初体验

原文 http://blog.csdn.net/qq_36608955/article/details/78586027

2017-11-21 02:02:30阅读(157)

    作为程序猿我们不仅要能编写正确的SQL 语句,也要编写高性能的SQL 语句,下面我们就来共同了解一下MySql 数据库索引,它可以帮助MySql 高效获取我们想要读取的数据。

索引是什么:

    MySql 官方对索引的定义为:索引(index) 是帮助MySql 高效获取数据的数据结构。因此我们可以知道索引的本质是一种数据结构。我们也可以理解为“排好序的快速查找的数据结构”。除了数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
    一般来说索引本身也很大,因此不可能全部存储在内存中,索引往往以索引文件的形式存储在磁盘上。我们平时所说的索引,如果在没有特定说明的情况下,指的都是B 树(多路搜索树,并不一定是二叉树)结构组织的索引。其中聚集索引,次要索引,覆盖索引,符合索引,前缀索引,唯一索引默认使用的都是B+ 树索引。当然除了B+ 树这种索引之外,还有哈希索引(hash index)等。

索引的优缺点:

    优点:对于需要大量查找而增删改需求很少的数据,通过建立索引可以提高检索的效率,降低数据库的IO 成本;通过索引可以对数据进行排序,降低数据排序的成本,降低了CPU 的消耗。
    缺点:实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也需要占用空间;虽然索引可以大大提高查询的速度,但是却会降低更新表(增删改)的速度,因为在更新数据表时,MySql 不仅要保存数据,还要保存索引文件每次添加的索引列的字段。

索引分类:

    1. 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引
    2. 唯一索引:索引列的值必须唯一,但允许有空值
    3. 复合索引:即一个索引包含多个列

索引结构:

    MySql 有多种索引结构,有B+Tree 索引,Hash 索引,full-text 索引,R-Tree 索引等。这里主要介绍B+Tree 索引:原B+Tree 介绍博客地址传送门~

MySql 数据库“索引”初体验

    如上图,是一颗b+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

b+树的查找过程
    如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

b+树性质
    1.通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

    2.当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

创建索引的技巧:

     1. 主键自动建立唯一索引
     2.频繁作为查询条件的字段可以创建索引
     3.查询中与其他表关联的字段,外键关系建立索引
     4.查询中排序的字段,排序的字段若建立索引将会大大提高排序速度
     5.WHERE 条件里用不到的字段不适合创建索引
     6.频繁更新(增删改)的字段不适合创建索引
     7.表记录很少的情况下不适合创建索引
     8.数据重复且分布平均的字段


                                                                                                                             参考文献:
                                                                                                                                    无所不能的互联网

最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹

天天可领!最高99块!土豪站长12月发红包啦!

关闭
支付宝红包