mysql索引总结

数据结构

  • innodb使用了b+树作为索引
  • 主键索引的叶子节点存的是整行数据,也被称为聚簇索引
  • 非主键索引的叶子节点存的是主键的值,也被称为二级索引
  • 基于非主键索引的查询,先搜索树得到主键的值,再到主键的索引树搜索一次,这个过程称为回表,要多扫描一棵索引树,在应用中应该尽量使用主键查询

    索引维护

  • B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护
  • 当插入一个中间值,可能会涉及到数据页的挪动,包括页分裂、页合并
  • 自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT,它每次插入都是追加操作,不涉及到挪动其他记录,不会触发叶子节点分裂
  • 有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高(性能)
  • 主键使用自增主键,长度比使用字符串更小,普通索引的叶子节点更小,占用的空间也更小(存储空间)
  • 当业务场景是只有一个索引、且必须是唯一索引,可以使用业务字段做主键

覆盖索引

  • 在查询中,索引已经覆盖了查询所需的所有字段,称为覆盖索引
  • 覆盖索引可以减少树的搜索次数,显著提升查询性能,使用覆盖索引是一个常见的优化手段
  • 最左前缀原则

    • 最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符
    • 索引下推优化,如果一个查询条件包含覆盖索引的最左字段与其余字段,查询时会通过覆盖索引去比对其余字段是否满足要求,若不满足则不会去回表

    唯一索引和普通索引

  • 对于普通索引来说,查找到满足条件的第一个记录 后,需要查找下一个记录,直到碰到第一个不满足普通索引条件的记录
  • 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索
    InnoDB 的数据是按数据页为单位来读写的,对于整型字段,一个数据页可以放近千个 key,因此出现这种情况的概率会很低。所以,我们计算平均性能差异时,仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计

change buffer

  • 当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了
  • 在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性
  • 将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作
  • 显然,如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率

Change Buffer 的相关设置

上面就是写缓存(Change Buffer)的相关知识,写缓存(Change Buffer)我们也是可以使用命令参数来控制,MySQL 数据库提供了两个对写缓存(Change Buffer)的参数。
Change Buffer 参数

  1. innodb_change_buffer_max_size

innodb_change_buffer_max_size 表示 Change Buffer 最大大小占 Buffer Pool 的百分比,默认为 25%。最大可以设置为 50%。

  1. innodb_change_buffering

innodb_change_buffering 参数用来控制对哪些操作启用 Change Buffer 功能,默认是:all

change buffer 适用场景

change buffer 并不是适用于所有场景,以下两种情况不适合开启change buffer :

  1. 数据库都是唯一索引
    如果数据库都是唯一索引,那么在每次操作的时候都需要判断索引是否有冲突,势必要将数据加载到缓存中对比,因此也用不到 Change Buffer
  2. 写入一个数据后,会立刻读取它
    写入一个数据后,会立刻读取它,那么即使满足了条件,将更新先记录在 change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。所以,对于这种业务模式来说,change buffer 反而起到了副作用

以下几种情况开启 Change Buffer,会使得 MySQL 数据库明显提升:

  1. 数据库大部分是非唯一索引
  2. 业务是写多读少
  3. 写入数据之后并不会立即读取它

change buffer和redo log

  • 写请求
  • 如果要插入的行所对应的页在内存中,直接更新内存
  • 如果要插入的行所对应的页不在内存中,就在内存的change buffer区域,记录下要往该页插入该行的信息
  • 将上述操作记录在redolog中
  • 以上操作写了两次内存(直接更细内存、更新change buffer),写了一次磁盘(两次内存操作合起来写一次redolog到磁盘,并且是顺序写)
  • 读请求
  • 读内存中存在的页所对应数据 的时候,直接从内存返回。
  • 读内存中不存在的页所对应数据,存在于change buffer的数据的时候,需要把该页 从磁盘读入内存中,然后应用 change buffer 里面的操作日志,生成一个正确的版本并返回结果。可以看到,直到需要读内存不存在的页 的时候,这个数据页才会被读入内存。因为有了change buffer ,写之前,需要更新的行不在数据页,并不需要将硬盘里的数据读到数据页,只要写change buffer 。等下次读数据时,从硬盘读出数据页,然后利用change buffer 去变更数据页
redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

索引优化器

  • MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数
  • 这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好
  • 我们可以使用 show index 方法,看到一个索引的基数

    • MySQL通过采样统计得到索引的基数
    • 采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数
    • 而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计
  • explain的结果中,rows 这个字段表示的是预计扫描行数
  • 如果使用索引 a,每次从索引 a 上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的
  • analyze table t 命令,可以用来重新统计索引信息,如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以采用这个方法来处理

    索引选择异常和处理

  • 采用 force index 强行选择一个索引
  • 考虑修改语句,引导 MySQL 使用我们期望的索引

    字符串索引

  • MySQL 是支持前缀索引的,可以定义字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串
  • 比如,这两个在 email 字段上创建索引的语句:

    mysql> alter table SUser add index index1(email);
    mysql> alter table SUser add index index2(email(6));

    第一个语句创建的 index1 索引里面,包含了每个记录的整个字符串;而第二个语句创建的 index2 索引里面,对于每个记录都是只取前 6 个字节

  • 由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节(即:zhangs),所以占用的空间会更小,这就是使用前缀索引的优势。但,这同时带来的损失是,可能会增加额外的记录扫描次数
  • 如果使用的是 index1(即 email 整个字符串的索引结构),执行顺序是这样的:

    1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录,取得 ID2 的值;
    2. 到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;
    3. 取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email='zhangssxyz@xxx.com’的条件了,循环结束。
    4. 这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。
  • 如果使用的是 index2(即 email(6) 索引结构),执行顺序是这样的:

    1. 从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
    2. 到主键上查到主键值是 ID1 的行,判断出 email 的值不是’zhangssxyz@xxx.com’,这行记录丢弃;
    3. 取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
    4. 重复上一步,直到在 idxe2 上取到的值不是’zhangs’时,循环结束。
    5. 在这个过程中,要回主键索引取 4 次数据,也就是扫描了 4 行。通过这个对比,你很容易就可以发现,使用前缀索引后,可能会导致查询语句读数据的次数变多
  • 对于这个查询语句来说,如果你定义的 index2 不是 email(6) 而是 email(7),也就是说取 email 字段的前 7 个字节来构建索引的话,即满足前缀’zhangss’的记录只有一个,也能够直接查到 ID2,只扫描一行就结束了
  • 使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本
  • 在建立索引时关注的是区分度,区分度越高越好。因为区分度越高,意味着重复的键值越少
  • 如果使用 index1(即 email 整个字符串的索引结构)的话,可以利用覆盖索引,从 index1 查到结果后直接就返回了,不需要回到 ID 索引再去查一次。而如果使用 index2(即 email(6) 索引结构)的话,就不得不回到 ID 索引再去判断 email 字段的值。即使你将 index2 的定义修改为 email(18) 的前缀索引,这时候虽然 index2 已经包含了所有的信息,但 InnoDB 还是要回到 id 索引再查一下,因为系统并不确定前缀索引的定义是否截断了完整信息

    参考资料

    极客时间,mysql实战45讲

你可能感兴趣的