MySQL索引及索引数据结构

索引是帮助MySQL高效获取数据的排好序数据结构

  • 主键和unique约束的字段会自动添加索引(根据主键查询效率较高,尽量根据主键检索)

  • 索引操作

    • 添加索引 create index 索引名 on 表名(字段名);
    • 删除索引 drop index 索引名 on 表名(字段名);
  • 索引分类:

    • 单一索引:给单个字段添加索引
    • 联合索引:多个字段联合起来添加一个索引
    • 主键索引:主键上会自动添加索引
    • 唯一索引:unique约束的字段会自动添加索引
  • 索引失效的情况:

    模糊查询时,第一个查询字符是不确定值(’%‘或’_’)时,索引失效

    select name from stu where name like '%e%'; 索引失效

    select name from stu where name like 'h%'; 索引有效


数据结构

  • 索引数据结构

    • 二叉树
    • 红黑树
    • Hash表
    • B-Tree树

  • 红黑树结构:
    在这里插入图片描述

  • Hash表:效率高,但是无法支持范围查找

  • B-Tree结构

    • 叶节点具有相同的深度,叶节点的指针为空
    • 所有索引元素不重复
    • 节点中的数据索引从左到右递增排列
      在这里插入图片描述

  • B+Tree (B-Tree变种)

    • 非叶子节点不存储data,只存储冗余索引,可以存放更多的索引

    • 叶子节点包含所有的索引字段

    • 叶子节点用指针连接,提高区间访问的性能
      在这里插入图片描述

MyISAM存储引擎索引实现

MyISAM索引文件和数据文件是分离的(非聚集)

  • .frm文件 存储表结构和数据定义的信息

  • .MYD 存储数据

  • .MYI 存储索引

  • 在使用了索引查找的情况下,先在MYI文件中定位到目标节点,再去MYD文件快速查找
    在这里插入图片描述

InnoDB索引实现

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件

  • 聚集索引 - 叶节点包含完整的数据记录

在这里插入图片描述

  • 为什么InnoDB必须有主键,并且推荐使用整型的自增主键?
    • 需要一个主键索引,用B+Tree来组织维护数据。
    • 没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。
    • 如果没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)
    • 使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+Tree叶子节点分裂顺序一致,无须频繁的移动、分页操作。
  • 为什么非主键索引结构叶子节点存储的是主键值?
    • 保持一致性:当数据库表进行DML操作时,同一行的页地址会发生改变,因非主键索引保存的是主键的值,无需进行更改。
    • 节省存储空间:InnoDB数据本身就已经汇聚到主键索引所在的B+Tree上了,如果普通索引还继续保存一份数据,就会导致有多少索引就要存多少份数据。
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 黑客帝国 设计师:上身试试 返回首页