MySQL 索引的10 个核心要点

文章目录

🍉1. 索引底层采用什么数据结构？为什么不用hash

索引底层数据结构是B+树

不使用hash：因为其底层是哈希表实现，等值查询，可以快速定位，一般情况效率很高，不稳定，当出现大量键重复哈希冲突，效率下降，不支持范围查询，无法用于排序分组，无法模糊查询，多列索引的最左前缀匹配原则，总要回表操作等。

🍉2. B树与B+树区别？为何用B+树？

B+树：非叶子结点不存data，只存key，查询更稳定，增大了广度(B+树出度更大，树高矮，节点小，磁盘IO次数少)；叶子结点下一级指针（范围查询）；索引冗余。

与红黑树相比：

更少查询次数：B+树出度更大，树高更低，查询次数更少
磁盘预读原理：为了减少IO操作，往往不严格按需读取，而是预读。B+树叶子结点存储相临，读取会快一些。

存储更多索引结点：B+树只在叶子结点储存数据，非叶子结点存索引，而一个结点就是磁盘一个内存页，内存页大小固定，那么相比B树这些可以·存更多的索引结点，出度更大，树高矮，查询次数少，磁盘IO少。

🍉3. 自增主键理解？

自增主键：InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力。

也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：（查看表结构，会看到自增主键=多少）

● 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。

● 举例来说：如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。

● 在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

自增值修改机制：

1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段；
2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

自增值新增机制：

1. 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；
2. 否则，自增值不变。

🍉4. 为什么自增主键不连续

● 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化

● 事务回滚（自增值不能回退，因为并发插入数据时，回退自增ID可能造成主键冲突）

● 唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值+1，所以不连续）

假设，表t里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1)

; (自增id,唯一键c,普通字段d)

这个语句的执行流程就是：

1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);
2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；
3. 将传入的行的值改成(2,1,1)；
4. 将表的自增值改成3；
5. 继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。
这个表的自增值改成3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键c冲突，所以id=2这一行并没有插入成功，但也没有将自增值再改回去。

所以，在这之后，再插入新的数据行时，拿到的自增id就是3。也就是说，出现了自增主键不连续的情况。

🍉5. Innodb为什么推荐用自增ID

①主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。

②新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗。

③减少了页分裂和碎片的产生

UUID：大量的随机IO+页分裂导致移动大量的数据+数据会有碎片。

总结：自增ID有序，会按顺序往最后插入，而UUID无序，随机生成，随机插入，会造成频繁页分裂，内存碎片化，大量随机IO

🍉6. 索引有哪些类型

● 排好序的数据结构，可以帮助快速查找数据

● 优缺点：索引可以提高查询速度，查询使用优化隐藏器提高性能，但是也会占据物理空间，降低增删改的速度，因为还要操作索引文件

类型：

覆盖索引+回表+索引下推+联合索引

● 普通索引：可以重复

● 唯一索引：唯一，可为空，表中只有一个主键索引，可多个唯一索引

● 主键索引

• 唯一，不为空，叶子结点存出了行记录数据，主键索引也称聚簇索引，对应非主键索引的叶子结点存的主键的值（二级索引），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）
• 一般推荐用自增主键，保证空间利用率，减少页分裂

● 全文索引

● 覆盖索引：索引字段覆盖了查询语句涉及的字段，直接通过索引文件就可以返回查询所需的数据，不必通过回表操作。

● 回表：通过索引找到主键，再根据主键id去主键索引查。

● 索引下推

○ 在根据索引查询过程中就根据查询条件过滤掉一些记录，减少最后的回表操作

假如执行

select * from stu where name=? and age=?

没有索引下推先再存储引擎根据name筛选数据返回给server层，然后server层再根据age过滤
有索引下推直接根据name和age在存储引擎层就筛选得到结果

🍉7. InnoDB与MyISAM的区别？

7.1 MyISAM与InnoDB区别

● InnoDB聚簇索引，MyISAM非聚簇索引

● InnoDB数据与索引一起保存.ibd，MyISAM表结构.frm 索引.myi 数据.myd

● InnoDB支持事务、外键、行锁表锁，MyISAM不支持事务、外键、只支持表锁

● select count(*)

● MyISAM查询更优，InnoDB更新更优

● 都是B+tree索引

● MyISAM支持全文索引，InnoDB5.6后支持

7.2 MyISAM

● 不支持事务，但是每次查询都是原子的

● 支持表级锁，每次操作对整个表加锁

● 存储表的总行数

● 一个MyISAM表有三个文件：表结构.frm 索引.myi 数据 .myd

● 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

7.3 Innodb

● 支持ACID事务，支持四种隔离级别

● 支持行级锁及外键约束，因此支持写并发

● 不存储总行

● 主键索引采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值;因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

7.4 使用场景

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意啊！）。

● MyISAM适合读多更新少的：MyISAM索引跟数据分开放，因此有读取更快的说法。

● InnoDB适合插入更新频繁的：索引与数据一起放，建立索引更复杂，使用行锁，更新频繁效率更高

● 需要事务，高并发场景用Innodb：Innodb支持事务，采用行锁

● MyISAM查询比InnoDB快，更新InnoDB快

场景：MyISAM查询更优，InnoDB更新更优

🍉8. 索引设计原则（查询快，占用空间少）

● 出现在where子句或则连接子句中的列

● 基数小的表没必要

● 使用短索引，如果索引长字符串列，应该指定前缀长度

● 定义有外键的数据列一定索引

● 不要过度索引

● 更新频繁的不适合

● 区分度不高的不适合，如性别

● 尽量扩展索引，别新建索引，如(a)->(a,b)

● 字符串字段建立索引方法

○ 1、直接创建完整索引，这样可能比较占用空间；
○ 2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
○ 3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
○ 4、额外用一个字段进行索引，额外计算开销

总结：索引设计原则要求查询快，占用空间少；一般建在where条件，匹配度高的；要求基数大，区分度高，不要过大索引，尽量扩展，用联合索引，更新频繁不适合、使用短索引。

🍉9. 索引有哪些失效场景

● 以“%”开头的like语句，索引无效，后缀“%”不影响

● or语句前后没有同时使用索引

● 列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）

● 如果mysql估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）

● 组合索引要遵守最左前缀原则——不使用第一列索引 失效

● 在索引字段上使用not，<>，！= （对它处理是全表扫描）

● 对索引字段进行计算操作，字段使用函数也会失效

🍉10. 普通索引和唯一索引怎样选

● 查询比较

○ 查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致

● 更新比较

○ 唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作change buffer

○ 补充：普通索引若数据再内存中直接内存中更新，否则会将更新操作先记录到channge buffer中，等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数据返回，这样一来，再写多读少的情况下就减少了磁盘IO，若写完就马上查询，就大可不必用change buffer，不但没提高多少效率还造成维护change buffer额外消耗

○ 将change buffer的操作对应到原始数据页的操作称为merge（可以查询来时读到内存再修改数据，后台线程也会merge，数据库正常关闭也会merge）

● 适合场景

○ 写多读少，选用普通索引更好，可以利用change buffer进行性能优化减少磁盘IO，将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改.