索引介绍
索引是什么:
官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。
我们通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。
索引的优势和劣势
优势:
可以提高数据检索的效率,降低数据库的IO成本,类似于书的目录。
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些。
如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多。
劣势:
索引会占据磁盘空间
索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。
索引的数据结构:
二叉树
二叉树:又称为二叉排序数,它的基本特点是左子树上所有节点值小于根节点,右子树上的所有节点值均大于根节点,节点的左右子树又是一颗二叉查找数,二叉查找树中遍历得到的结果是递增排序的结点序列,相比于数组的搜索比较方便,可以直接使用下标,但插入和删除比较麻烦和链表的删除和插入元素很快,但查找很慢,二叉树则是比较折中的方案,有二者的优点,在处理大批量的动态数据时比较有用
平衡二叉树
平衡二叉树:通过一定的算法,将不平衡的树转化为平衡树 因为平衡二叉树在最差情况下,也就是孰不分叉,和顺序查找的效率一样,主要是因为这种情况下BST不够平衡,也就是左右子树的高度差太大,超过了1,通过平衡二叉树来提升检索效率
B树
B树:一个节点可以存储多个键值和数据的平衡树,相比平衡二叉树每个节点存储一个键值和数据,如果存储海量的数据,二叉树的节点会很多,高度也会很高,查找数据时会进行很多次磁盘io,我们查询的效率会很低,为了平衡二叉树的这个弊端,于是出现了一种单个节点可以存储多个键值和数据的平衡树
B+树
B+树是对B树的进一步优化,相比B树节点中不仅存储键值,也存储数据,B+树非叶子节点上是不存储数据,仅存储键值,不同于B树数据分散在各个节点上,B+树索引的所有数据均存储在叶子节点,而且数据是按照顺序排序的
Mysql索引实现
mysql三种存储引擎的实现:MyISAM索引和InnoDB索引和Mermory
分别存储在磁盘 磁盘 内存
myisam不支持事务 不支持外键 支持表锁 不支持高并发 效率更高
存储文件 myisam存储分为三个文件 frm表的元数据 myd表里面的数据 myi表里面的索引 以这三类文件存储数据 每个库都会对应三个文件 innodb把所有的数据存储在一个文件里
innodb相比效率更低 但是功能会更多 支持行锁 支持高并发
聚集索引和非聚集索引
聚集索引:以主键作为B+树索引的键值而构建的B+树索引,以innoDB作为存储引擎的表 表中的数据都会有一个主键,因为innoDB是把数据存放在B+树中的,B+树的键值就是主键,在B+树的叶子节点中,存储了表中所有被的数据
非聚集索引:以主键以外的列值作为键值构建的B+树索引,不同于聚集索引的叶子节点存储表中的所有数据,非聚集索引不存储表中的数据,而是存储该列对应的主键
回表:通过非聚集索引查询出的要查询的数据的id值后,再到聚集索引里面查该id对应的数据,这个过程叫做回调
索引覆盖:写sql语句时查询的字段都在非聚集索引的叶子节点上,不需要回表查,此时叫做索引覆盖
最左匹配:在创建组合索引后,只有查询条件中使用了这个组合索引中的字段的第一个字段,该组合索引才会生效