【MySQL实战】索引

database

主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。

主键索引与普通索引的区别在于,主键索引不需要回表遍历,普通索引在普通索引拿到索引值后,需要根据主键值再去主键索引遍历一次,称为回表。

    InnoDB所有的数据文件idb文件每页的大小是16K,操作系统文件系统块是4K,磁盘扇区是512字节,页可以存放数据或者指针,每个表的索引页所在页存储在表空间信息中,通常索引节点由键值+指针地址组成,默认指针大小是6字节(2^48bit),自增主键bigint大小是8字节(2^64bit),所以bigint的索引节点(非叶子节点)大小为14字节,16K的页可以存储16384/14=1170条数据的索引,由于是主键索引,叶子节点存放着数据记录,假设每条数据记录大小为1k,每页叶子节点可存放16条记录,索引树B+树会分为两层,树高为2时,该课树可最多存放记录数1170*16=18720条数据;树高为3时,可存放最多记录数为1170*1170*16=21902400条记录,上千万级别了。结合通常说的单表数据超过千万时(单表1G),索引页数、数据页数会很大,需要考虑拆表、拆库,甚至改用其他数据库,如HBase、MyCat、Greenplum、Redis,应该也是磁盘IO瓶颈所致,所以MySQL的在B+树索引的索引方式下还是无法绕过磁盘IO的问题。

    参考《mysql b+树能存多少条数据?b+树每层有多少分支》https://blog.csdn.net/csdnlijingran/article/details/102309593

    

索引维护

    当索引页数据部署顺序时,需要从中间插入、删除索引节点时,需要移动索引数据,特别是数据处于索引页临界点时,会造成索引页分裂成N+1张,或者合并成N-1张。

    所以主键ID建议使用自增长,可以避免一部分这样的问题,而且可以带来一部分其他的收益,主键为自增长的bigint,占据的空间为8字节,比自定义的UUID32、64位都要小,这样普通索引的叶子节点占据的空间就会更小,这样就可以节省普通索引的空间。

    联合索引

        根据索引顺序,逐个索引过滤满足条件的数据,直到所有索引筛选完成,如有index(A,B,C),假设命中了联合索引(A,B,C),先用A索引的全部记录与条件组筛选得出满足条件A的索引树(A,B,C)的子集合A,逐行回表到主键索引获取数据,得到数据集合A;再拿B的条件与索引树(A,B,C)的子集合A中B字段索引做运算,得到满足A+B条件的索引树(A,B,C)的子集合AB,逐行回表到主键索引树上拿到数据集合A+B;再拿C的条件与索引树(A,B,C)的自己和AB中的C字段索引做运算,得到满足A+B+C条件的索引树(A,B,C)的索引子集ABC,再逐行回表到主键索引中获取数据,得到数据集合ABC,返回查询结果ABC。

    从上述过程可以看出需要多次回表,且存在重复回表的记录,与其这样,不如在可以通过索引包含的字段先做判断筛选,减少回表的次数,在MySQL5.6及其以后的版本有这样的能力,中称作索引下沉/索引下推(index condition pushdown)

    存在like > < 的判断时根据索引最左前缀匹配原则,最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。

    覆盖索引是指需要查询的数据字段包含在索引的叶子节点上,不需要再到数据页上获取数据,减少树的搜索和IO。

普通索引与唯一索引的区别

    在查询上对于命中索引的查询来说,唯一索引在取得满足条件的时候会结束搜索,普通索引还需要继续往下搜索查找和判断下一条记录,但是由于InnoDB的数据是按页为单位存储的,当索引指向的数据需要被读到时,会整页读入内存,对内存的查找和判断还是比较快的,特别是一页只有16K大小的情况;当然如果存在跨页的情况就会复杂一些,主键索引的优势会明显多一些。

    在更新时主键索引需要做唯一性检查,change buffer的情况相对较好,但是如果数据不在内存中,可能导致磁盘IO严重,普通索引则不存在这种情况。

以上是 【MySQL实战】索引 的全部内容, 来源链接: utcz.com/z/534462.html

回到顶部