mysql基础(4):索引

一:索引的三种常见模型

    1:哈希表

        a, 什么是哈希表?

一种键-值存储数据的结构。把值放在数组里,用一个哈希函数把key换算成一个确定的位置,再把value放在这个数组的位置上。

        b, 怎么避免多个key换算成同一个值?拉出一个链表。

        c, 适用的场景? 适用于等值查询。

    2:有序数组

        适合等值查询和范围查询。如果仅仅看查询效率,有序数组是最好的数据结构,在需要更新的时候,往中间插入一个记录就必须挪动后面所有的记录,成本太高。所以只适用于静态存储引擎,如2017年某个城市的人口数据。

    3:搜索树

        阿群博客

        a,  二叉树的特点?

            每个节点的左儿子小于父节点,父节点又小于二儿子。查ID_card_n2,按照图中的搜索顺序:UserA->UserC->UserF->User2路径得到,时间复杂度O(log(N))

        b, 为什么使用N叉树?

        因为索引不仅存在内存中,还要写到磁盘上。为了让一个查询尽量少的读磁盘,就必须让查询过程访问尽量少的数据块。如:一颗100万节点的平衡二叉树,树高20。一次查询可能需要访问20个数据块。在机械硬盘时代,从机械硬盘读一个数据需要10ms左右的寻址时间。如果一个100W行的数据,使用二叉树存储,单独访问一个行可能需要20*10ms时间。


二:InnoDB的索引模型

    1:什么是索引组织表?

        在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表叫做索引组织表。InnoDB使用了B+树索引模型,所以数据都是存储在B+树中。

        B+树能够很好地配合磁盘的读写特性,减少单词查询的磁盘访问次数。

    2:主键索引和非主键索引

        主键索引:叶子节点存的是整行数据,在InnoDB,主键索引也称为聚簇索引。

        非主键索引:叶子节点内容是主键的值,在InnoDB,非主键索引也称为二级索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k)
)engine=InnoDB;

表中R1-R5(ID ,k) 值分别为(100,1)(200,2)(300,3)(500,5)(600,6)

        阿群博客

    

    3:主键索引和普通索引的查询有什么区别?

        a, 如果sql是select * from T where ID=500, 即主键查询方式,则只需要搜索ID这颗B+树。

        b, 如果sql是select * from T where k=5, 即普通索引查询方式,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次,这个过程称为回表。

        c, 非主键索引的查询需要多扫描一颗索引树,因此,在应用中尽量使用主键查询

三:索引维护

    1:索引维护的过程

        B+树为了维护索引有序性,在插入新值的时候需要做必要的维护。如插入新的行ID值为700,则只需在R5后面插入一个新纪录;如新插入ID值为400,那就相对麻烦了,需要在逻辑上挪动后面的数据,空出位置。

        更糟糕的是,如果R5所在的数据页已经满了,根据B+树算法,这时候需要申请一个新的数据页,然后挪动部分数据过去,这个过程称为页分裂,性能自然会受影响。

        除性能外,页分裂操作还影响数据页的利用率,原来放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。

当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

所以建表规范里,语句中一定要有自增主键。

    2:那些场景应该使用自增主键,那些场景不应该?

        自增主键:

            a, 递增插入的场景,每插入一条新纪录都是追加,不涉及挪动其他记录,也不会触发叶子节点的分裂。业务逻辑字段做主键,往往不容易保证有序插入,写数据成本高。(性能上)

            b, 如果身份证号做主键,由于每个非主键索引的叶子节点上都是主键的值,那么每个二级索引的叶子节点占用约20个字节,如果是int4个字节。显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。(存储空间上)

        业务字段做主键:

            a, 只有一个索引

            b, 该索引必须是唯一索引。

            典型的KV场景,由于没有其他索引,所以不用考虑其他索引的叶子节点大小的问题。


四:实战演示一条select需要执行几次树的搜索操作,会扫描多少行?

    sql语句:select * from T  where k between 3 and 5的执行流程

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),在这个例子,由于查询结果所需的数据只在主键索引上有,所以不得不会表。


五:索引优化,怎么避免回表过程?

    1:覆盖索引

        a:什么是覆盖索引?

            如果执行的语句是 select ID from T where k between 3 and 5, 这时只需要查ID的值,而ID的值已经在k索引树上,因此可以直接提供查询结果,不需要回表,这种索引k已经覆盖了我们的查询请求,成为覆盖索引。

        b:  在一个市民信息表中,是否有必要将身份证号和名字建立联合索引?

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

    

            如果有一个高频请求,根据身份证号查询姓名,建立(身份证号、姓名)的联合索引,可以用到覆盖索引,不再需要回表查整行记录。

            索引字段的维护总是有代价的,因此在建立冗余索引支持覆盖索引时,就需要权衡考虑了。

        c:由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

    2:最左前缀原则

        a: 什么是最左前缀原则?

            阿群博客

            B+树索引机构,可以利用索引的最左前缀,来定位记录。

            查找name为张三,快速定位ID4,然后向后遍历所有需要的结果。

            查找name第一个字张的人, where name like '张%' 也能够用上这个索引,查找到第一个符合条件的记录ID3,然后向后遍历,直到不满足条件。

            不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左的M个字符。

        b: 在建立联合索引,如何安排索引内的字段顺序?

            第一原则:如果通过调整顺序,可以少维护一个索引,这个顺序往往就是需要优先考虑使用的。

            其次:考虑原则是空间:name、age,name字段比age字段大,创建一个(name,age)联合索引和(age)单字段索引

    3:索引下推

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
检索出表中名字第一个字是张,而且年龄是10岁的所有男孩

        a: mysql5.6之前版本,

            只能从ID3开始一个个回表,到主键索引上找出数据行,再对比字段值

        b: mysql5.6+引入索引下推优化,

            可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

    4:总结

        a:在满足语句需求的情况下,尽量少地访问资源是数据库设计的重要原则之一;使用数据库的时候,尤其在设计表结构时,也要以减少资源消耗为目标

        b: 覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据

        c : 最左前缀:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符

        d : 联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。

        e : 索引下推:like 'hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度。

    

    


阿群博客
请先登录后发表评论
  • 最新评论
  • 总共0条评论