深入浅出MySQL系列(2)—MySQL索引剖析(下）

最新推荐文章于 2024-11-06 10:59:12 发布

小李子i

最新推荐文章于 2024-11-06 10:59:12 发布

阅读量126

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/li2683056515/article/details/132250425

版权

本文详细探讨了高性能索引策略，包括独立列、前缀索引、多列索引的使用、索引选择性、聚簇索引优化以及覆盖索引的重要性。同时介绍了changebuffer在查询和更新过程中的优化作用，特别是在减少磁盘I/O方面的优势，以及何时选择使用普通索引而非唯一索引。

摘要由CSDN通过智能技术生成

1、有无理想的索引？

过滤因子越强, 其筛选能力越好;

理想索引设计: 三星索引

①在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列；

②将 GROUP BY 和 ORDER BY 中的列加入到索引中

③将 SELECT 字段中剩余的列加入到索引片中

并不能完全遵循三星索引地原因:

①采用三星索引会让索引片变宽，这样每个页能够存储的索引数据就会变少，从而增加了页加载的数量。

②增加了索引维护的成本。如果我们为所有的查询语句都设计理想的三星索引，就会让数据表中的索引个数过多，这样索引维护的成本也会增加。

当然对于数据库来说，数据的更新不一定马上回写到磁盘上，但即使不及时将脏页进行回写，也会造成缓冲池中的空间占用过多，脏页过多的情况。

2、高性能的索引策略

2.1 独立的列

索引列不能是表达式的一部分,也不能是函数的参数, 因为这样索引会失效.

比如：

2.2 前缀索引和索引选择性

有时候索引很长的字符列, 这样索引就会占用比较大的空间, 影响查询效率.

前缀索引: 只索引开始的部分字符, 这样就会大大节约索引空间, 从而提高索引效率.

1)前缀索引占用的空间更小，这就是前缀索引的优势

2)使用前缀索引，可能会导致查询语句读数据的次数变多，增肌扫描行数。

3)使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

4)使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考

索引选择性: 不重复的索引值(基数)和数据表的记录总数的比值; (唯一索引的选择性是1, 这也是最好的索引选择性)

前缀索引用于长字符串:

注意:

选择前缀索引时, 要选择足够长的前缀来保证较高的选择性, 但同时也不能太长.
前缀索引是一种能使索引更小\更快的有效办法
缺点: MySQL无法使用前缀索引做ORDER BY和GROUP BY, 也无法使用前缀索引和覆盖扫描

2.3 多列索引

索引合并策略;

当出现服务器对多个索引做相交操作时, 通常意味着需要一个包含所有相关列的多列索引, 而不是多个独立的单列索引.
当服务器需要多个索引做联合操作时, 通常需要耗费大量CPU和内存资源在算法的缓存, 排序和合并操作上
而且优化器不会把这些计算到“查询成本”中, 优化器只关心随机页面的读取

2.4 选择合适的索引列顺序

索引列的顺序意味着索引首先按照最左列进行排序, 其次是第二列
经验法则: 将选择性最高的列放到索引最前列

2.5 聚簇索引优化

在聚簇索引中要尽可能地按主键顺序插入数据, 并且尽可能地使用单调增加地聚簇键地值插入新行;

如果是不是按主键顺序, 而是随机顺序插入, 会产生地问题:

①写入地目标页可能已经刷到磁盘上并从缓存中移除, 或者是还没有加载到缓存中, InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中. 这会导致大量地随机IO

②写入是乱序, 可能会频繁出现页分裂操作, 以便位新地行分配空间. 页分裂会导致移动大量数据, 一次插入最少需要三个页而不是一个页.

③页分裂, 进而会导致有碎片

2.6 覆盖索引

覆盖索引: 如果一个索引包含所有我们需要查询地字段地值

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

覆盖索引地好处:

①最大的好处就是, 查询数据时无需二次回表. 二级索引在叶子节点保存了行地主键值, 所以如果二级索引能够覆盖查询, 则可以避免主键索引地二次查询

②索引条目通常远小于数据行大小, 所以如果只需要读取索引, 那么会极大减少数据访问量

2.7 最左前缀原则

B+树索引可以利用最左前缀, 来定位记录 这个最左前缀可以是联合索引地最左N个字段

在建立联合索引地时候如何安排索引内地字段顺序?

①第一个原则就是,如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

2.8 索引下推

索引下推优化:

可以在索引遍历过程中, 对索引中包含地字段先做判断, 直接过滤掉不满足条件地记录, 减少回表次数.

无索引下推优化时, 必须每个都需要回表查!

3、change buffer优化

普通索引和唯一索引地选择

1）查询过程：

区别不大，普通索引要多做一次”查找和判断下一条记录的操作“，要一次指针寻找和一次计算。但是，性能上的差异微乎其微。

当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer, 也是持久化的数据, change buffer 在内存中有拷贝，也会被写入到磁盘上

merge :

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

优点：如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool (缓冲池)的，所以这种方式还能够避免占用内存，提高内存利用率。

什么条件下使用change buffer呢?

①唯一索引使用change buffer没啥用，因为需要判断唯一性，都需要将数据读入内存中去判断。所以，唯一索引的更新就不能使用 change buffer。

②普通索引可以使用 :