MySQL索引优化

Cat凯94

已于 2023-03-31 15:31:05 修改

阅读量128

点赞数

分类专栏：数据库文章标签： sql 数据库 Powered by 金山文档

于 2023-02-25 22:16:08 首次发布

本文链接：https://blog.csdn.net/loseyourself94/article/details/129221074

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一文详解MySQL各种锁及MVCC的使用场景及原理

MySQL慢查询优化

Mysql日期格式转化问题

索引优化方法

前缀索引优化

覆盖索引优化

主键索引自增

索引NOT NULL

防止索引失效

前缀索引优化

前缀索引:根据某个字段中字符串的前几个字符建立索引

作用:

使用前缀索引是为了减小索引字段大小

可以增加一个索引页中存储的索引值

有效提高索引的查询速度

局限性:

order by 就无法使用前缀索引

无法把前缀索引用作覆盖索引

覆盖索引优化

覆盖索引:需要查询的字段正好是索引的字段，那么直接根据该索引就能查到数据，而无需回表查询

当查询多个字段时可以使用联合索引防止回表

主键索引自增

使用自增主键，每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

另外,主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

索引NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化.因为为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用.因为 InnoDB 默认行存储格式COMPACT，会用 1 字节空间存储 NULL 值列表.

索引失效原因

创建了组合索引，但查询条件未准守最左匹配原则;

最左匹配原则:

最左优先，在检索数据时从联合索引的最左边开始匹配

对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引将会失效。

在索引列上进行计算、函数、类型转换等操作;

模糊查询,以 % 开头的 LIKE 查询比如 like '%abc';

查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;

发生隐式转换(当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低, 如: where str = 100(str为字符类型))

关联查询优化

外连接查询:

连接字段有索引就走索引

内连接查询:

如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现

如果两个表都存在索引，会选择小表作为驱动表，即“小表驱动大表”。即两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表

Join原理简介

MySQL5.5之前:嵌套循环

假设有A,B两张表，无索引，A为驱动表，B为非驱动表，从表A中取出一条数据a1，遍历B表，将匹配的数据放到result，以此类推，驱动表A中每一条记录与表B的记录进行匹配，

MySQL8.0之前:Block Nested-Loop Join(BNLJ算法:块嵌套循环连接)

Block Nested-Loop Join方式不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer 缓冲区，将驱动表 join 相关的部分数据列(大小受 join buffer 的限制)缓冲到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。