db的知识真的是又深又广,本着务实的态度,只简单谈谈我对数据库的索引一些方面的理解。说道索引,要简单说说索引的结构也就是b-tree的一些知识。
b+tree和btree是innodb磁盘等外存贮时查找的一种结构,因为磁盘读写涉及到大量的io,为了需要加快读写速度避免大量io操作,因此设计了一种平衡二叉树,平衡的意思就是树的每个分支的高度差保证在1之内,这样可以减小查找复杂度。
btree有下面有几个特征:
- 每个节点都有一个key,data。key就是索主键,data就是数据。
- 如果不是终端节点,每个节点有n个分支。
- 所有的终端节点都是在同一层。
- 终端节点没有指针,其余节点是有指针的指向它的下一层数据范围。
b+tree是在btree上的一种优化,它和btree的区别有下面两点。
- 所有的终端节点才存有数据,其他节点只放指针和key。
- 所有的终端节点用链表链接。
这样同样的页大小,间接放置的索引值增多,索引量加大,一次性读入内存的关键词加多,相对的io读写次数下降,效率就会提升。另外,终端链表可以快速查找范围数据也加快了查询。
主键
主键是唯一索引的一种,并且不能有null的记录存在,但唯一索引并不一定是主键,唯一索引可以很多,并且允许为null。一般都手动设置id为主键。如果我们不设置主键,sql会从唯一主键中挑出一个没有null的字段作为主键;如果这也不满足,它会自己产生以行号变化的字段作为主键,这个主键的内容我们可以用_rowid来查。
聚集索引
当主键产生后,会根据主键生成这张表的唯一一种聚合索引,它的叶子结点存的是真实数据的物理地址。id一般作为聚合索引,也会有使用时间作为聚合索引也是不错的实践选择。自增型还是比较友好的。
非聚集索引
联合索引和普通索引,可以有多个。联合索引使用多个字段联合查询,遵循最左侧原理,使用频繁的字段靠左,可以提高速度。例如abc联合索引的本质就是分别创建a ab abc的索引,普通索引就是分别建立a b c的索引。当查a and b时,联合就比较快,而查b and c或者a or b时普通就比较快。
回表查询
因为只有主键的索引树才会把数据的真实地址放在叶子结点上,而一般索引树的叶子结点放的只是主键值,需要通过本索引key先查到主键的key,然后再按主键查到数据。这整个过程叫“回表”。
索引覆盖
当普通索引查询一次即可获得结果,不用回表再查。
select id,name,sex ... where name='shenjian';
将name和sex建立联合索引,就可以直接取一次拿到数据,如果只有name活着sex一个单索引,是需要通过id再查一次的。
非聚集索引和聚集索引的区别在于:
- 聚集索引的叶子结点上的数据是真实数据的物理地址,非聚集索引的b+tree的叶子结点上的数据是主键值。
- 不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据。