简单来说,索引的出现就是为了提高数据的查询效率,就像书的目录一样。
索引模型
哈希表:是一种以键值对存储数据的结构,只要输入待查找的值即key,就可以找到对应的值value。哈希表的思路是,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后拉出一一个链表。有一个需要注意的是,哈希冲突的时候就拉出一个链表
好处是:新增数据非常快;缺点是:由于不是有序的,区间查询的速度是很慢的。
有序数组:有序数组在等职查询和范围查询的性能都非常优秀。但是在更新数据的时候就需要挪动数据,成本很高。
搜索树:
二叉搜索树:特点是每个节点的左儿子小于父节点,父节点又小于右儿子。
多叉树:每个节点有多个儿子,儿子之间的大小保证从左到右递增。
InnoDB的索引模型
在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称作索引组织表。
回表:
回到主键索引树的过程叫做回表。
覆盖索引:
在一个查询里面,索引k已经“覆盖了”我们的查询需求,我们称了“索引覆盖”。
最左前缀原则:
因为索引项是按照索引定义里面出现的字段顺序排序的
索引下推:
5.6版本之后,可以在遍历过程中,对索引包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
普通索引和唯一索引的选择:
查询的时候的性能差距是微乎其微的。
更新的时候,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB会将这更新操作缓存在chang buffer中,这样就不需要从磁盘中读入这个数据库页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。这样就保证了数据逻辑的正确性。需要说明的是,change buffer名字虽然叫做change buffer,实际上它是可以持久化的数据。也就是说,change buffer在内存上有拷贝,也会被写入到磁盘。将change buffer中的操作应用到原始数据页,得到最新结果的过程叫做merger。除了访问这个数据页的时候会出发merge,系统后台线程会定期merger。在数据库正常关闭的时候也会进行merger。这样就减少了读磁盘,语句的执行速度会得到明显的提升。而读磁盘是需要占buffer pool的,所以这种方式还能变面占用内存。
唯一索引更新操作都要先判断这个操作是否违反唯一性约束。这必须要将数据页读入到内存中,因此唯一索引的更新不能使用change buffer,世界上也只有普通索引可用使用change buffer。
回到原先的问题,唯一索引的使用和普通索引的使用,如果数据页在内存中,对于普通索引来说,执行更新只要找到更新位置,更新数据;而唯一索引,找到更新的位置,判断唯一性,没有冲突则更新。这个只会耗费微小的CPU时间。数据页不在内存中时,对于唯一索引,需要将数据页读到内存中,然后执行更新操作,而对于普通索引只需要将更新记录在change buffer,语句就执行完成了。
change buffer的使用场景:
change buffer是讲更新操作当作缓存缓存下来,从而过程加速更新操作,merger的时候是将数据真正更新的时候,在一个数据页做merger之前,change buffer记录的变更越多,收益就越大。反过来,进行一个更新操作之后,又立马需要查询这个数据的时候,就会立即出发merger操作,这样访问磁盘IO次数不会变少,相反,增加了change buffer的维护代价。所以,唯一索引适合写多读少的场景。