数据库索引
索引是依靠某些数据结构和算法来组织数据,最终引导用户快速检索出所需要的数据。即对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
如何提升访问速度?
1、数据存储结构:减少磁盘I/O次数,
2、检索算法:快速从内存的多条记录中检索出目标数据
索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因:因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。
创建索引和维护索引要耗费时间; 索引需要占物理空间; 当对表中的数据执行增删改,索引也要动态的维护,降低了数据的维护速度。
创建原则:
在查询中,很少使用或者参考的列 不应该创建索引。既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
只有很少数据值的列 也不应该增加索引。由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
定义为text、image和bit数据类型的列 不应该增加索引。这些列的数据量要么相当大,要么取值很少。
当修改性能远远大于检索性能时,不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
1 平衡二叉树AVL b-tree b+ tree
1. 叶子节点(最下面的一层)存储关键字(索引字段的值)信息及对应的data,叶子节点存储了所有记录的关键字信息
2. 其他非叶子节点只存储关键字的信息及子节点的指针
3. 每个叶子节点相当于mysql中的一页,同层级的叶子节点以双向链表的形式相连
4. 每个节点(页)中存储了多条记录,记录之间用单链表的形式连接组成了一条有序的链表,顺序是按照索引字段排序的
5. b+树中检索数据时:每次检索都是从根节点开始,一直需要搜索到叶子节点
2.1 InnoDB中的索引-----聚集(主键)索引
聚集索引(主键)是通往真实数据所在的唯一路径。每个表只有一个主键索引,b+树结构,叶子节点同时保存了主键的值及数据记录,其他节点只存储主键的值
2.2 InnoDB中的索引-----非聚集(辅助)索引
每个表可以有多个,b+树结构,叶子节点保存了索引字段的值以及主键的值,其他节点只存储索引指端的值。
非聚集索引和聚集索引一样, 同样是采用B+树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 假如给user表的name字段加上索引 , 那么索引就是由name字段中的值构成,在数据改变时, DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。 每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。
2.3 MyISAM引擎中的索引
B+树结构,MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
2.4 非聚集索引和聚集索引的区别
通过聚集索引可以查到需要查找的数据, 通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,如下图
用的最多的是innodb存储引擎,innodb中最好是采用主键查询,这样只需要一次索引,如果使用辅助索引检索,涉及到回表操作,比主键查询要耗时一些。
MYSQL中的索引
1、聚集索引(主键索引)
每个表有且一定会有一个聚集索引,整个表的数据存储在聚集索引中,mysql索引是采用B+树结构保存在文件中,叶子节点存储主键的值以及对应记录的数据,非叶子节点不存储记录的数据,只存储主键的值。当表中未指定主键时,mysql内部会自动给每条记录添加一个隐藏的rowid字段(默认4个字节)作为主键,用rowid构建聚集索引。
2、非聚集索引(辅助索引)
也是b+树结构,不过有一点和聚集索引不同,非聚集索引叶子节点存储字段(索引字段)的值以及对应记录主键的值,其他节点只存储字段的值(索引字段)。每个表可以有多个非聚集索引。
通过非聚集索引检索记录的时候,需要2次操作,先在非聚集索引中检索出主键,然后再到聚集索引中检索出主键对应的记录,该过程比聚集索引多了一次操作。
非聚集索引分为:
单列索引 即一个索引只包含一个列
多列索引(又称复合索引) 即一个索引包含多个列
唯一索引 索引列的值必须唯一,允许有一个空值。
索引管理
创建索引 CREATE INDEX 索引名称 ON 表名(列名[(length)]);
创建唯一索引 CREATE UNIQUE INDEX 索引名称 ON 表名(列名[(length)]);
创建索引方法二 alter 表名 add [unique] index 索引名称 on (列名[(length)]);
删除索引 DROP INDEX 索引名称 ON 表名;
查看索引 SHOW INDEX 索引名称 ON 表名;
索引修改 先删除,后重建
删除索引 DROP INDEX 索引名称 ON 表名;
参考: 1 数据库常面试总结