取经之路 - 数据库索引

在取经路上,你不得走走停停,你要一直向前😶

数据库索引

• 有点优点
  • 加快检索速度
  • 加快表之间的连接
  • 减少排序和分组时间
  • 保证唯一性 (唯一索引)
• 缺点
  • 占物理空间
  • 对数据的增删改需要动态维护索引
• 分类
  • 聚簇索引： 索引结构和数据一起存放的索引
    1. 数据存放的物理位置为顺序，能提高多行检索的速度。(普通索引、唯一索引、主键索引和聚集索引)
    2. 数据和主键存放在一起
    3. 叶子节点存储数据
    4. 非叶子节点存储索引
       1. 优点
          1. 一定范围的数据获取比较好
          2. 查找数据快,少一次主键寻址的IO
       2. 缺点
          1. 插入速度依赖插入顺序
          2. 更改主键代价高,更新的行会移动
          3. 二级索引需两次所以查找(第一次查找主键,第二次查找行数据)
          4. 插入比非聚簇索引慢
  • 非聚簇索引：索引结构和数据分开存放的索引(也称二级索引)
    1. 不按数据存放的物理位置顺序，对于单行的检索很快。
    2. 数据和索引分开存储
    3. 所有节点都是索引
    4. 叶节点有索引也有数据
    5. 回表操作: 在叶节点拿到主键,再去查找数据
  • 聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录

索引的数据结构

• 哈希表 (少用)
  • 底层是哈希表
  • 合适单条记录查询
• B+Tree (推荐使用)
  • 叶节点存放数据的地址 (相当于存储数据)

如何选择索引

• 仅仅是为了单行访问是不必要的
• 顺序访问数据是很快的
  • 顺序IO不需要多次磁盘寻道
  • 顺序读取避免额外的排序
  • group by也避免不必要的排序和聚合计算
• 索引覆盖很快
  • 一个索引包含（或者说覆盖）所有需要查询的字段的值
  • 覆盖索引就是把要查询出的列和索引是对应的，不做回表操作

为什么索引能提供查询速度

• MySQL的存储结构
  • 页是基本存储结构
    • 文件头
    • 页头
    • 存储数据 (不确定大小)
    • 空余空间 (不确定大小)
    • 页的目录 (根据主键二分算法定位,非主键就是依次遍历,底层B+树实现)
    • 文件尾
  • 数据页可以组成一个双向链表
  • 页中的记录又可以组成一个单链表
  • 将无序的数据变得相对有序

索引降低增删改的速度

• B+树是一种平衡树,平衡树是左右子树高度差绝对值不超过1,左右子树也是一棵平衡树
• 平衡树不会退化成链表,符合矮胖(均衡)的结构,查询的时间复杂度就是O(log n)
• 增删改对平衡树的原有结构造成破环,需要再次调整就会有相当的开销
• 哈希索引没有办法完成排序,不支持最左匹配原则,哈希碰撞存在,不支持范围查询

最左匹配原则

• 只存在一个列的索引,存在多列的 联合索引,联合索引存在最左匹配
• 联合索引包含(col1, col2, col3), 你的索引会生效于(col1), (col1, col2), 以及(col1, col2, col3)

数据库的锁

• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5ixM0mez-1585053475378)(C:\Users\Primer4\Documents\学习笔记\1585052594419.png)]

• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FxxoFPM9-1585053475379)(C:\Users\Primer4\Documents\学习笔记\1585052626312.png)]

添加索引

ALTER TABLEtable_nameADD PRIMARY KEY (column)

哪些字段合适或不合适创建索引

合适

• 不为null
• 频繁查找
• 条件查询
• 表之间的连接

不合适

• 频繁更改
• 不被经常查找
• 考虑联合索引而不是单列索引 (单列索引是: 每个索引对应一颗B+树; 联合索引就是: 多列对应一颗B+树)

B树和B+树的区别

• B树
  • 所有节点存放 键和数据
  • 叶结点时独立的
  • 检索时候对范围内的叶节点进行 二分查找
• B+树
  • 只有叶节点存放 键和数据
  • 非叶节点存放 键
  • 叶节点有一条引用链
  • 检索都是从根节点到叶节点 (稳定)