MySQL - 实战(3) - 索引（上）

MySQL - 实战(3) - 索引

目录

• MySQL - 实战(3) - 索引
• 1 索引常见模型
  • • 1.1 哈希表
      • 1.1.1 优点
      • 1.1.2 缺点
    • 1.2 有序数组
      • 1.2.1 优点
      • 1.2.2 缺点
    • 1.3 二叉树
      • 1.3.1 优点
      • 1.3.2 缺点
    • 1.4 N叉树
      • 1.4.1 优点
  • 2 InnoDB索引模型
    • 2.1 索引组织表
    • 2.2 索引类型
      • 2.2.1 主键索引 - 聚簇索引 - clustered index
      • 2.2.2 非主键索引 - 二级索引 - secondary index
      • 2.2.3 主键索引和非主键索引的区别
      • 2.2.4 索引维护
        • 2.2.4.1 防止页分裂和合并
        • 2.2.4.2 逻辑删除和物理删除
        • 2.2.4.3 自增主键的使用场景
  • 3 思考

1 索引常见模型

1.1 哈希表

• 键值存储数据的结构（key-value）
• 出现多key值经过哈希函数计算得到相同value的情况可以使用链表

1.1.1 优点

• 值不是递增的，新增数据只需要往后追加，速度很快

1.1.2 缺点

• 无序数组，哈希索引做区间查询速度很慢

哈希表结构适用于只有等值查询的场景，比如Memcached 及其他一些 NoSQL 引擎

1.2 有序数组

1.2.1 优点

• 等值查询和范围查询场景下性能都比较优秀
• 等值查询 - 二分法 - 时间复杂度 O(log(N))
• 范围查询 - 二分法查左范围 - 向右顺序遍历查右范围

1.2.2 缺点

• 更新数据需要移动数据之后的所有数据

有序数组索引适合静态存储引擎，比如某年某城市人口信息，不会修改的数据

1.3 二叉树

1.3.1 优点

• 等值查询 - 时间复杂度 - O(log(N))

• 维持时间复杂度，更新需要保证平衡二叉树 - 更新时间复杂度也为O(log(N))

1.3.2 缺点

• 索引不止存在内存中还要写入磁盘
• 二叉树树高高 - 磁盘I/O操作多

1.4 N叉树

• 查询少读磁盘 - 少访问数据块 - 低树高

InnoDB整数字段索引 - N = 1200

• 数据页默认大小 16KB
• bigint 8个字节
• 指针大小在InnoDB源码中6字节
• N = 16*1024/(8+6)

1.4.1 优点

• 读写性能好
• 适配磁盘访问模式

数据库的数据模型决定适用场景

MySQL中索引在存储引擎层实现

2 InnoDB索引模型

2.1 索引组织表

• 表根据主键顺序以索引的形式存放，所有数据存在B+树中
• 在InnoDB中每一个索引对应一颗B+树

2.2 索引类型

2.2.1 主键索引 - 聚簇索引 - clustered index

• 主键索引的叶子节点存的是整行数据
• key：主键的值
• value：整行数据

2.2.2 非主键索引 - 二级索引 - secondary index

• 非主键索引的叶子节点存的是主键的值
• key：索引列的值
• value：主键的值

2.2.3 主键索引和非主键索引的区别

• 主键索引查询搜索主键索引的B+树
• 非主键索引查询搜素非主键索引的B+树得到主键值再搜索主键索引B+树，也就是回表

2.2.4 索引维护

• B+树为了索引有序性从，插入数据需要挪动后面的数据，如果数据页满了则需要申请新数据页并挪动数据 - 页分裂
• 页分裂 - 性能降低 - 数据页利用率降低
• 页合并 - 相邻页因为删除数据后利用率低 会合并

B+树的插入和删除过程：

https://www.cnblogs.com/nullzx/p/8729425.html

2.2.4.1 防止页分裂和合并

页分裂：

• 使用自增主键防止页分裂

页合并：

• 数据表删数据使用逻辑删除而非物理删除防止页合并

2.2.4.2 逻辑删除和物理删除

逻辑删除：

• 本质是修改操作
• 修改表中对应是否删除标值(is_delete) 或 状态字段(status)
• 逻辑上已经删除但数据本身仍然存在库中

update 表名 set is_delete = 1 where id = 1；

物理删除：

• 从数据库中删除

delete from 表名 where 条件；

回收站：利用了逻辑删除

• 对于删除文件进入回收站的本质只是在操作系统的帮助下对文件加上了 某个标记，资源管理器中对含有这种标记的文件不会显示。
• 当从回收站恢复的时候只是移除了加上的标记而已，而清空回收站就是进行了物理删除。

2.2.4.3 自增主键的使用场景

性能：

• 自增主键的插入数据是递增插入。插入新记录是追加操作，不涉挪动其他记录，也不会叶子节点分裂
• 相比较，业务逻辑的字段做主键，不保证有序插入，写数据成本相对高

存储空间：

• 业务逻辑的字段做主键，比如身份证，非主键索引的叶子节点存储主键的值，二级索引的叶子节点占用约 20 个字节
• 自增主键，非主键索引的叶子节点存储自增主键值，整型4 字节，长整型（bigint） 8 字节
• 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

业务字段做主键场景：

• 只有一个索引
• 该索引必须是唯一索引
• 没有其他索引不用考虑其他索引的叶子节点大小

3 思考

1.“N叉树”的N值在MySQL中是可以被人工调整的么？

改变页的大小

• 页越大，一页存放的索引就越多，N就越大

• 主要还是通过调整数据页的大小间接来调整N叉树N的值

• 默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变 innodb_page_size 选项对默认大小进行修改

• 数据页调整后，如果数据页太小层数会太深，数据页太大，加载到内存的时间和单个数据页查询时间会提高，需要达到平衡才行

改变key值得大小

• N叉树中非叶子节点存放的是索引信息，索引包含Key和Point指针。Point指针固定为6个字节，假如Key为10个字节，那么单个索引就是16个字节。如果B+树中页大小为16K，那么一个页就可以存储1024个索引，此时N就等于1024。我们通过改变Key的大小，就可以改变N的值
• key的值也可以认为是固定的，通常就是4或者8，int或者bigint.主要还是page大小

2.没有主键的表，有一个普通索引如何回表？

• 没有主键的表，innodb会给默认创建一个Rowid做主键

3.innodb B+树主键索引的叶子节点存的是什么？

• B+树的叶子节点是page （页），一个页里面可以存多个行

4.一个innoDB引擎的表，数据量非常大，根据二级索引搜索会比主键搜索快？

• 二级索引比主键索引快的场景是 使用了覆盖索引