索引
索引是一个排序的列表,列表包含索引的值以及包含这个值所在行的物理位置。
优缺点:
优点: 加速检索,减少io次数。
缺点:索引本身也是表,占用存储空间。维护以及创建需要时间成本,而且操作数据库也会修改索引表。
分类
主键索引、唯一索引、普通索引、全文索引、组合索引
主键索引: 根据主键建立的索引,不允许重复,不允许空值
唯一索引: 建立索引的字段必须唯一,但是允许空值
普通索引: 普通字段构建的索引,没有任何限制
全文索引:用大文本对象的列构建的索引
组合索引: 多个字段组合构建的索引,不允许空值。
最左前缀原则,将最常用的检索字段字段放在最左侧。A,B,C相当于建立了 A, AB, ABC三个索引。
索引实现原理
MySQL支持多种存储引擎,各种存储引擎对索引支持也不相同,因此MySQL支持多种索引类型。
-
Hash索引
只有memory引擎支持,使用散列算法计算索引列的hashcode,在对应位置存放所在行的物理地址。
不支持范围查找以及排序 -
全文索引
对于文本这类大对象,为其生成一份单词清单,索引时根据单词清单进行索引。 -
BTree索引
BTree 平衡多叉搜索树, -
B+ Tree索引
是BTree的变种,不同在于:
B+Tree非叶子界定不存储数据,只存储key
每个非叶子节点由n个键值以及n个指针组成。
一个优化: 带有顺序索引的B+Tree(添加指向相邻叶节点的指针)
聚簇索引以及非聚簇索引
聚簇索引: 聚簇索引的顺序就是数据的物理存储顺序
将数据与索引存放在一起,找到索引也就找到数据
基于此的辅助索引,叶子节点保存的是主键。
非聚簇索引:索引顺序与数据物理存储顺序无关
将数据与索引分开存储,索引结构的叶子节点保存数据行的物理地址(索引表数据表分开放)。
InnoDB使用聚簇索引表,MyISAM使用非聚簇索引表。
聚簇索引之上创建的索引成为 辅助索引, 按照辅助键索引访问数据需要二次查找。
聚簇索引一些特性:
1.聚簇索引具有唯一性, 因为将数据与索引结构放在一起。
2.聚簇索引默认是主键,如果没有定义主键会选择一个唯一的非空索引来代替。如果没有符合的,隐式定义一个主键作为聚簇索引。
聚簇索引与非聚簇索引的优缺
- 聚簇索引的每个节点保存的是页数据,同一页有多个行数据,当访问同一个数据页多行数据时,已经把页加载到buff中。主键与行数据时一起载入内存的,找到主键就可以直接将行数据返回
- 辅助索引的叶子节点,存储的是主键值,而非数据的物理存放地址。一方面减少了辅助索引占用存储空间,另外一方面当数据发生变化时,只需要维护聚簇索引树,无需维护辅助索引树
- 非聚簇索引叶子节点对应的页数据对应的物理行地址是凌乱的,可能需要多次磁盘IO操作