MySQ进阶—索引篇

小龙coding大约 43 分钟

MySQL (2)

什么是索引？

简易版本：

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

详细版本：

官方介绍索引是帮助 MySQL 高效获取数据的数据结构。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚簇索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用 B+树结构组织的索引

索引优势和劣势？

优势：提高数据检索的效率，降低数据库的 IO 成本。通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。

如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多（原理后讲）

劣势：索引会占据磁盘空间索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还要维护索引文件。

索引有哪些类型？

这个问题按照不同概念划分，可有不同种类划分。

如果按照底层数据结构实现来划分，可列为一下几种：

B-Tree索引：B-Tree索引是MySQL最常用的索引类型，它能够处理所有可以使用比较运算符的查询（=、<、<=、>、>=、<>、BETWEEN、LIKE等）。B-Tree索引特别适合处理高基数数据的查询，也就是说，对于那些在列中只包含少量重复值的数据，B-Tree索引非常有效。
哈希索引：哈希索引基于哈希表实现，只能满足"="、"<=>"等精确查找查询，不能使用范围查询和排序。哈希索引的优点是查找速度非常快，但是数据量大的情况下，哈希冲突的概率会增大，性能会下降。
FULLTEXT（全文索引）：全文索引用于全文搜索。与其他索引类型不同，全文索引并不是通过索引列的值来查找行的位置，而是通过对一些文档的内容进行索引，然后通过关键字来查找文档。目前，全文索引只有MyISAM和InnoDB存储引擎支持。
空间数据索引（R-Tree）：空间数据索引是对地理空间数据的索引，InnoDB和MyISAM都支持该类型索引，但是具体的使用方法和性能都与存储引擎有关。
组合索引：组合索引是指多个列上创建的索引，只有在查询条件中使用了创建索引时的最左前列，索引才会被使用。使用组合索引可以更有效地进行查询优化。

也可分为：

普通索引：可以重复
唯一索引：唯一，可为空，表中只有一个主键索引，可多个唯一索引
主键索引
- 唯一，不为空，叶子结点存出了行记录数据，主键索引也称聚簇索引，对应非主键索引的叶子结点存的主键的值（二级索引），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）
- 一般推荐用自增主键，保证空间利用率，减少页分裂
全文索引
覆盖索引：索引字段覆盖了查询语句涉及的字段，直接通过索引文件就可以返回查询所需的数据，不必通过回表操作。

MySQL 如何实现的索引机制？

索引的数据结构和具体存储引擎的实现有关，MySQL 中通常维护一个 Hash 和 B+ 树数据结构作为索引。

Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据.

B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询.

InnoDB 索引与 MyISAM 索引实现的区别？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描。
通常情况下，主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可
注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

一个表如果没有创建索引，那么还会创建B+树吗？

如果一个表没有创建任何索引，那么MySQL不会为该表创建B+树索引。但是，需要注意的是，如果表定义了主键，那么MySQL会自动为主键创建一个聚集索引（B+树索引）。如果表没有显示定义主键，MySQL会为每一行生成一个隐藏的行ID，并以此为基础创建一个聚集索引。

在没有索引的情况下，当我们对表进行查询操作时，MySQL会进行全表扫描，这在数据量大的情况下会非常低效。因此，为了提高查询效率，我们通常会根据查询需求为表创建合适的索引。

普通索引和唯一索引怎样选？

查询比较
- 查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致
更新比较
- 唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作change buffer
- 补充：普通索引若数据再内存中直接内存中更新，否则会将更新操作先记录到channge buffer中，等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数据返回，这样一来，再写多读少的情况下就减少了磁盘IO，若写完就马上查询，就大可不必用change buffer，不但没提高多少效率还造成维护change buffer额外消耗
- 将change buffer的操作对应到原始数据页的操作称为merge（可以查询来时读到内存再修改数据，后台线程也会merge，数据库正常关闭也会merge）
适合场景
- 写多读少，选用普通索引更好，可以利用change buffer进行性能优化减少磁盘IO，将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改.

聚簇索引和非聚簇索引 B+ 树实现有什么区别？

简易版本：

聚簇索引和非聚簇索引的主要区别在于它们如何存储数据，以及在B+树中的实现方式。

聚簇索引：在聚簇索引中，B+树的叶子节点就是实际的数据页。也就是说，索引的键值决定了数据在磁盘上的物理存储位置。因此，每个表只能有一个聚簇索引。在MySQL的InnoDB存储引擎中，如果没有显式定义主键，系统会生成一个6字节的内部ID作为聚簇索引。
非聚簇索引：在非聚簇索引中，B+树的叶子节点并不包含实际的数据，而是存储了指向数据页的指针（在InnoDB中，这个指针就是聚簇索引的键值）。因此，非聚簇索引也被称为二级索引或辅助索引。一个表可以有多个非聚簇索引。

在实际操作中，聚簇索引的查询效率通常会比非聚簇索引高，因为聚簇索引只需要一次磁盘IO就可以获取到数据，而非聚簇索引则需要两次磁盘IO（一次是在索引页，一次是在数据页）。但是，如果数据表的查询操作主要是基于非主键的查询，那么非聚簇索引就非常有用，因为它可以直接找到对应的数据，而不需要扫描整个数据表。

详细版本：

每个 InnoDB 表都有一个聚簇索引，也叫聚集索引。聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID 字段来构建聚簇索引。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据都是该行的主键值。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

InnoDB创建索引的具体规则如下：

在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB 会选择第一个不为NULL的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID 字段构建聚簇索引。该 ROWID 字段会在插入新行时自动递增。

主键索引

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。 InnoDB要求表必须有一个主键索引(MyISAM 可以没有)。

辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

使用辅助索引需要检索两遍索引：

首先检索辅助索引获得主键
然后使用主键到主索引中检索获得记录。

以表 t_user_innodb 的 age 列为例，age 索引的索引结果如下图。底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

说一下 B+树中聚簇索引的查找（匹配）逻辑？

1）等值查询

select * from t_user_innodb where id=30

先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）

磁盘IO次数：3次

流程分析：

2）范围查询

select * from t_user_innodb where id between 30 and 49;

先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历比较20<30，30<=30<49。查找到值等于30的索引项。获取行数据缓存到结果集中。（1次磁盘IO）
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，30<49<=49，获取行数据缓存到结果集中。（1次磁盘IO）
最后得到2条符合筛选条件，将查询结果集返给客户端。

可以看到，因为在主键索引中直接存储了行数据，所以InnoDB在使用主键查询时可以快速获取行数据。当表很大时，与在索引树中存储磁盘地址的方式相比，因为不用再去磁盘中获取数据，所以聚簇索引通常可以节省磁盘IO操作。

磁盘IO次数：2次+检索叶子节点数量

说一下 B+树中非聚簇索引的查找（匹配）逻辑？

1）等值查询

select * from t_user_innodb where age=22

先在索引树中从根节点开始检索，将根节点加载到内存，比较22<77，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较22<34，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）

- 第一项5：5<22不符合要求，丢弃。
- 第二项22：等于22，符合要求，获取主键id=18，去主键索引树中检索id=18的数据放入结果集中。（回表查：3次磁盘IO）。
- 第三项22：等于22，符合要求，获取主键id=49，去主键索引树中检索id=49的数据放入结果集中。（回表查：3次磁盘IO）

向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较。（1次磁盘IO）第一项34：34>22不符合要求，丢弃。查询结束。
最后得到2条符合筛选条件，将查询结果集返给客户端。
磁盘IO次数：2次+检索叶子节点数量+记录数*3

2）什么是回表查询？

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree：从两个方面来回答

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：

虽然可以快速定位，但是没有顺序，IO复杂度高；
基于Hash表实现，只有Memory存储引擎显式支持哈希索引；
适合等值查询，如=、in()、<=>，不支持范围查询；
因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序open in new window ；
Hash索引在查询等值时非常快；
因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找；
如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

一个B+树中大概能存放多少条索引记录？

本题注在分析理解 InnoDB 索引数据结构与数据在存储器中的存储组织方式

需要从两方面考虑：

数据在存储器中的存储组织方式
InnoDB 索引数据结构

首先需要考虑数据在存储器中如何存储：

1、数据持久化存储磁盘里，磁盘的最小单元是扇区，一个扇区的大小是 512个字节

2、文件系统的最小单元是块，一个块的大小是 4KB

3、InnoDB存储引擎的最小存储单元称之为页，一个页的大小是16KB（如下图）

InnoDB从磁盘中读取一个数据页时，操作系统会分4次从磁盘文件中读取数据到内存。写入也是一样的，需要分4次从内存写入到磁盘中。

表中的记录都是存储在页，假如一行数据的大小约为1K字节，那么按 16K / 1K = 16，可以计算出一页大约能存放16条数据。

而 B+树索引非叶子节点都是存键值和指向子节点的指针（注意：这里主键+指针占大小=8字节+6字节=14），叶子节点才存行记录 data。

现在开始计算分析：

假设当前表的行平均大小为 153 个字节

计算过程：

单个叶子节点（页）中的记录数 = 16K / 153 = 105（页大小（16K）/ 行记录大小）

非叶子节点能存放多少指针， 16k / 14 = 1170 (页大小（16K）/ 非叶子节点大小（主键+指针占大小）)

如果树的高度为 3，可以存放的记录行数 = 1170 * 1170 * 105 = 143,734,500（指针数 * 单个叶子节点记录条数）

千万级的数据存储只需要约3层B+树，查询数据时，每加载一页（page）代表一次IO。所以说，根据主键id索引查询约3次IO便可以找到目标结果

使用B+树存储的索引crud执行效率如何？

为什么官方建议使用自增长主键作为索引?（说一下自增主键和字符串类型主键的区别和影响)

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续的数据：

图片来自：https://www.javazhiyin.com/40232.htmlopen in new window

插入非连续的数据：

自增主键理解？

自增主键：

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恢复重启之前的值。

自增值修改机制：

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

自增值新增机制：

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

为什么自增主键不连续

在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化
事务回滚（自增值不能回退，因为并发插入数据时，回退自增ID可能造成主键冲突）
唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值+1，所以不连续）

eg：

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

insert into t values(null, 1, 1); (自增id,唯一键c,普通字段d)

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

执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);
InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；
将传入的行的值改成(2,1,1);
将表的自增值改成3；
继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。

这个表的自增值改成3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键c冲突，所以id=2这一行并没有插入成功，但也没有将自增值再改回去。

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

InnoDB 为什么推荐用自增 ID

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

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

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

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

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

使用int自增主键后最大id是10，删除id 10和9,再添加一条记录，最后添加的id是几?

在MySQL中，如果你使用了INT自增主键，当你删除了ID为10和9的记录后，再添加一条新的记录，新记录的ID将会是11

（具体原因，请看上述自增主键理解）

删除后重启 MySQL 然后添加一条记录最后 Id 是几?

在MySQL中，如果你删除了最大的ID（比如10和9），然后重启MySQL，再添加一条新的记录，新记录的ID将会是10（但是8.0后，即时重启，也会将自增主键记在redo log，也就是11）

使用索引一定能提高效率吗？

不一定。有时你会发现使用 explain 分析SQL执行计划时，明明使用了索引，但最后却走的全部扫描。

第一点：索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。
第二点：若索引在字段在 where 条件经常用不到，数据量不多，索引字段区分度不高，需要经常增删改操作，不建议使用索引。可能反而带去其他消耗，不如直接查询来的快
第三点：回到开始，explain 显示全表扫描，是因为优化器分析使用全表扫描比索引来得更快，从侧面反映出索引不一定提高效率。

如果是大文本内存，如何创建优化索引？

对于大文本字段，创建全文索引是一种常见的优化方式。全文索引主要用于在大文本字段中进行文本搜索。MySQL的MyISAM和InnoDB存储引擎都支持全文索引。

创建全文索引的语法如下：

ALTERTABLE table_name ADD FULLTEXT(column_name);

其中，table_name是表名，column_name是需要创建全文索引的大文本字段。

全文索引使用倒排索引的方式，将每个唯一的词作为键值，然后记录该词在哪些文档中出现。这样，在进行文本搜索时，就可以直接通过词找到包含该词的文档，大大提高了查询效率。

但是，全文索引也有一些限制和注意事项：

全文索引需要更多的存储空间和处理时间。
全文索引不支持通配符搜索。
在使用全文索引进行查询时，需要使用特定的MATCH AGAINST语法。

此外，对于非常大的文本字段，还可以考虑使用其他的文本搜索引擎，如Elasticsearch等。

什么是聚簇索引和非聚簇索引？

见上面聚簇索引和非聚簇索引底层实现

一张表可以有多个（非）聚簇索引吗？

对于聚簇索引，一张表只能有一个。因为聚簇索引决定了表中数据的物理存储顺序，所以一张表只能有一个聚簇索引。

对于非聚簇索引，一张表可以有多个。非聚簇索引，也被称为二级索引或辅助索引，它是对表中一列或多列的索引，但并不影响数据的物理存储顺序。你可以在表的任何列上创建非聚簇索引，以提高查询性能。

但是，需要注意的是，索引并不总是越多越好，过多的索引会增加数据库的存储空间，并可能降低插入、删除和更新操作的性能。因此，在创建索引时，需要根据实际的查询需求和数据特性来进行权衡。

CRUD时聚簇索引和非聚簇索引的区别是什么？

聚簇索引：在数据库中，聚簇索引决定了表中数据的物理存储顺序。也就是说，当你在表上创建聚簇索引时，它会按照索引的顺序来存储数据。因此，当进行范围查询时，聚簇索引通常会有更好的性能。同时，因为聚簇索引包含了所有的列，所以它可以直接返回查询的结果，无需进行额外的IO操作。但是，当你需要频繁地插入、删除或更新数据时，聚簇索引可能会导致一些性能问题，因为它需要重新排序数据。
非聚簇索引：非聚簇索引，也被称为二级索引或辅助索引，它是对表中一列或多列的索引，但并不影响数据的物理存储顺序。非聚簇索引包含索引的键值和一个指向每行数据的指针。因此，当查询的列不在索引中时，数据库需要通过这个指针回到数据表中获取数据，这就是所谓的"回表"操作。在进行大量的读取操作时，非聚簇索引通常比聚簇索引更快，因为它只需要读取索引，而不需要读取整个表。但是，如果需要频繁地更新非聚簇索引的列，那么这可能会导致一些性能问题，因为每次更新都需要更新索引。

总的来说，聚簇索引和非聚簇索引各有优势，选择使用哪种索引取决于你的具体需求和数据特性。

详细谈谈你对myisam与innodb使用场景的理解？

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

myisam适合读多更新少的：myism索引跟数据分开放，因此有读取更快的说法。
innodb适合插入更新频繁的：索引与数据一起放，建立索引更复杂，使用行锁，更新频繁效率更高
需要事务，高并发场景用innodb：innodb支持事务，采用行锁
myisam查询比innodb快，更新innodb快

场景：myisam查询更优，innodb更新更优

myisam适合读多，更新少的场景。myisam使用非聚簇索引，数据和索引分开存的，而innodb数据和索引存一起的，数据量大时，一个内存页大小固定，读进内存的数据myiam就多一点(数据量小看不出差距，数据量大时差距就明显），因为myisam只把索引指针读进内存，可以存更多，查询速度也就更快，而且innodb还需要维护其他数据，比如其他隐藏字段 row_id tx_id等

非聚簇索引为何不存数据地址而存主键？

非聚簇索引存储主键而不是数据地址的原因主要有以下两点：

空间效率：在许多数据库系统中，主键通常比数据的物理地址要小，因此，使用主键作为非聚簇索引的指针可以节省存储空间。
数据移动：当数据行被更新并导致数据移动时，如果非聚簇索引存储的是数据地址，那么就需要更新所有指向该数据行的非聚簇索引，这将导致大量的IO操作。而如果非聚簇索引存储的是主键，那么即使数据行移动，主键也不会改变，因此无需更新非聚簇索引。

因此，非聚簇索引通常会存储主键，然后通过主键查找聚簇索引，再通过聚簇索引找到数据行，这种方式被称为"回表"。虽然这会增加一次查找操作，但是在大多数情况下，这种额外的开销都是可以接受的。

什么是覆盖索引？

覆盖索引，又称全值索引或索引覆盖，是一种数据库查询的优化策略。当一个查询的所有列都包含在一个索引中时，我们就说这个查询使用了覆盖索引。

覆盖索引的优点在于：数据库系统可以仅通过索引就获取查询所需的所有信息，无需再回到数据表中查找（也就是所谓的"回表"操作）。这样可以大大提高查询效率，特别是在处理大型数据库时。

举个例子，假设我们有一个包含id（主键）、name、age和address字段的用户表，我们在name和age上建立了一个复合索引。那么，当我们执行以下查询时：

SELECT name, age FROM users WHERE name ='John';

因为查询的字段（name和age）和WHERE条件都包含在索引中，所以数据库可以直接通过索引获取查询结果，无需回表，这就是覆盖索引的应用。

但是，如果我们执行以下查询：

SELECT name, address FROM users WHERE name ='John';

虽然WHERE条件在索引中，但是查询的字段address不在索引中，所以数据库需要回到数据表中获取address的值，这种情况就不能使用覆盖索引。

非聚簇索引（聚簇索引）一定回表操作吗？

非聚簇索引（也称为二级索引或辅助索引）查询数据时，是否需要回表操作，取决于查询的字段是否都包含在索引中。

如果查询的字段都包含在非聚簇索引中，那么查询操作可以直接在索引上完成，无需回表。这种情况被称为"覆盖索引"（Covering Index）。例如，如果有一个非聚簇索引包含了字段A和字段B，那么查询SELECT A, B FROM table WHERE A = value就可以直接在索引上完成，无需回表。
如果查询的字段不完全包含在非聚簇索引中，那么查询操作需要先在索引上查找到符合条件的记录，然后通过索引中的聚簇索引键（通常是主键）回到主表，获取其他字段的值。这种情况被称为"回表"。例如，如果有一个非聚簇索引只包含了字段A，那么查询SELECT A, B FROM table WHERE A = value就需要回表。

聚簇索引（也称为主索引或聚集索引），是按照表中主键的顺序构建的一种特殊类型的索引。在聚簇索引中，表中的记录就是索引的一部分，因此，使用聚簇索引查询数据时，无需进行回表操作。

回表？为什么要回表查询？直接存储数据不好吗？

"回表查询"是指在非聚簇索引查询过程中，首先通过非聚簇索引找到主键，然后再通过主键查找聚簇索引，最后通过聚簇索引找到数据行的过程。

之所以要进行回表查询，而不是在非聚簇索引中直接存储数据，主要有以下几个原因：

空间效率：如果在非聚簇索引中直接存储数据，那么每个非聚簇索引都需要存储一份完整的数据，这将占用大量的存储空间。而如果只存储主键，那么可以大大减少存储空间的需求。
数据一致性：如果在非聚簇索引中直接存储数据，那么当数据更新时，需要同时更新所有的非聚簇索引，这不仅会增加IO操作，还可能导致数据一致性问题。而如果只存储主键，那么只需要更新聚簇索引即可，非聚簇索引无需更新。
查询效率：虽然回表查询需要进行两次查找操作，但是由于聚簇索引是按照主键顺序存储的，因此主键查找的效率非常高。而且，由于非聚簇索引只存储主键，因此非聚簇索引的大小会比较小，可以更好地利用缓存，提高查询效率。

因此，虽然回表查询会增加一次查找操作，但是在大多数情况下，这种额外的开销都是可以接受的。

如果把一个 InnoDB表的主键删掉，是不是就没有主键,就没办法进行回表查询了?

如果你删除了一个InnoDB表的主键，InnoDB会自动选择一个唯一非空索引来替代主键。如果没有这样的索引，InnoDB会生成一个隐藏的ROWID作为主键。

InnoDB的表总是有一个主键，因为InnoDB使用聚簇索引来存储数据，聚簇索引就是按照主键的顺序来存储数据的。如果表定义中没有明确指定主键，InnoDB会尝试使用唯一非空索引作为主键，如果没有这样的索引，InnoDB会生成一个隐藏的ROWID作为主键。

所以，即使你删除了表的主键，InnoDB依然可以通过主键进行回表查询。但是，由于隐藏的ROWID是由InnoDB自动生成的，你无法直接访问和操作这个ROWID，因此，如果可能的话，最好总是为你的表定义一个主键。

最左匹配原则？

简述：从左往右匹配，直到遇到范围查询

结合实例理解：建立联合索引（a,b,c）

索引是先根据a排序，a相同时b有序，a不同无序，以此类推。总之遇到范围查询就停。

  （a,b）联合索引     [(2,4),(),()]
                    \|/    \|/
   [(1,1),(1,2),(2,1)]     [(2,4),(3,1),(3,2)]
规律：a有顺序（1，1，2，2，2，3，3）b无顺序，a相同时b又有顺序，不同a之间b没有顺序，所以a=1,b>2走联合索引；a>1,b>2不走索引。

select * from table_name where a = '1' and b = '2' and c = '3'
//全值匹配查询，用到索引，与顺序无关，查询优化器，会自动优化查询顺序 

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'
//匹配左边的列时，用到了索引

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'
//没有用到索引

select * from table_name where a = '1' and c = '3' 
//a用到了索引，b、c没有到

select * from table_name where  a > 1 and a < 3 and b > 1;
//只有a用到索引，在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤

select * from table_name where  a = 1 and b > 3;
// a=1的情况下b是有序的，进行范围查找走的是联合索引 走 a b索引(a相同时b有序)

复合索引创建时字段顺序不一样使用效果一样吗?

复合索引（也称为联合索引）的字段顺序对查询效率有很大的影响。在创建复合索引时，应该将查询条件中最常用的字段放在前面。

这是因为MySQL查询时会从索引的最左边开始匹配，一旦遇到范围查询（比如：>, <, BETWEEN, LIKE等）就会停止使用后面的索引字段。这就是所谓的“最左前缀原则”。

例如，假设有一个复合索引(index_name_age)包含name和age两列，以下查询可以使用这个复合索引：

SELECT * FROM table WHERE name = 'Tom' AND age =20;

但是，如果查询条件只包含age，那么这个复合索引就无法使用：

SELECT * FROM table WHERE age =20;

所以，如果你的查询条件经常只包含age，那么应该考虑将age放在复合索引的前面。

总的来说，复合索引的字段顺序应根据实际的查询需求来确定，以达到最优的查询效率。

什么是索引下推？

索引下推（Index Condition Pushdown，ICP）是MySQL 5.6版本开始引入的一种优化查询的技术。

在没有索引下推之前，当我们使用联合索引进行查询时，MySQL会先通过索引找到满足部分查询条件的记录，然后将这些记录的全部数据（即整行数据）取出，再在服务器端进行剩余查询条件的过滤。

而索引下推技术的出现，改变了这种处理方式。它允许MySQL在存储引擎层就对索引中的部分数据进行过滤，只有满足全部查询条件的记录的数据才会被取出。这样就可以减少大量无用数据的IO操作，提高查询效率。

例如，有一个联合索引(index_name_age)，我们要查询年龄大于20且名字以'Li'开头的记录：

SELECT * FROM table WHERE name LIKE 'Li%' AND age >20;

在没有索引下推的情况下，MySQL会先通过索引找到所有名字以'Li'开头的记录，然后将这些记录的全部数据取出，最后在服务器端过滤出年龄大于20的记录。

而在开启索引下推的情况下，MySQL会在存储引擎层就过滤掉年龄不大于20的记录，只有满足全部查询条件的记录的数据才会被取出。这样就可以大大减少无用数据的IO操作，提高查询效率。

需要注意的是，索引下推并不是在所有情况下都能提高查询效率，是否开启应根据实际的查询需求和数据分布来确定。

如果表中有字段为null，又被经常查询该不该给这个字段创建索引（阿里二面原题）?

*看你是否真正理解 MySQL 索引原理与使用准则了*

在数据库设计中，是否为某个字段创建索引，主要取决于这个字段在查询中的使用频率和数据的分布情况，而不仅仅是这个字段是否可能包含NULL值。

对于包含NULL值的字段，你完全可以为其创建索引。在MySQL中，NULL值也可以被索引，且可以在查询中使用IS NULL和IS NOT NULL条件来快速查找NULL或非NULL值。

但是，如果一个字段的值大部分都是NULL，且你的查询主要是在非NULL值之间进行的，那么这个字段可能不适合创建索引。因为索引的主要目的是为了提高查询效率，如果大部分的索引项都指向NULL值，那么这个索引可能并不会带来太大的性能提升。

另外，如果一个字段的值大部分都是NULL，且你的查询主要是查找NULL值，那么这个字段可能适合创建索引。因为在这种情况下，索引可以帮助你快速找到所有的NULL值。

总的来说，是否为一个字段创建索引，需要根据这个字段在查询中的使用情况和数据的分布情况来决定。如果你不确定是否需要为某个字段创建索引，你可以尝试创建索引，然后通过实际的查询性能来评估这个索引的效果。

索引在什么场景下适合创建？索引设计原则（查询快，占用空间少）

出现在where子句或则连接子句中的列
基数小的表没必要
使用短索引，如果索引长字符串列，应该指定前缀长度
定义有外键的数据列一定索引
不要过度索引
更新频繁的不适合
区分度不高的不适合，如性别
尽量扩展索引，别新建索引，如(a)->(a,b)
字符串字段建立索引方法
- 1、直接创建完整索引，这样可能比较占用空间；
- 2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
- 3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
- 4、额外用一个字段进行索引，额外计算开销

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

索引失效场景？

以“%”开头的like语句，索引无效，后缀“%”不影响
or语句前后没有同时使用索引
列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）
如果mysql估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）
组合索引要遵守最左前缀原则——不使用第一列索引失效
在索引字段上使用not，<>，！= （对它处理是全表扫描）
对索引字段进行计算操作，字段使用函数也会失效
is null

为什么LIKE以%开头索引会失效?

在SQL中，LIKE操作符用于在WHERE子句中搜索列中的指定模式。当LIKE操作符的模式以百分号（%）开头时，索引通常会失效，原因如下：

索引的工作原理：MySQL索引是按照B+树的数据结构来存储的。当你执行一个LIKE查询时，如果模式以一个常量开头，然后利用索引快速找到满足这个常量的所有值。然后，它可以遍历这些值，找到满足整个模式的值。
百分号的含义：在LIKE操作符的模式中，百分号（%）表示任意数量（包括零）的任意字符。因此，当模式以%开头时，数据库系统无法确定搜索的起始点，它必须查看所有的值以确定哪些值满足模式。这就使得索引无法发挥作用，因为索引的主要优点是能够快速定位到满足特定条件的值。

因此，如果你的查询经常需要使用以%开头的LIKE模式，你可能需要考虑其他的优化策略，例如全文搜索或者使用其他的数据结构（如倒排索引）。

有字段为null索引是否会失效?

在MySQL的InnoDB存储引擎中，NULL值是可以被索引的。这意味着，如果你在一个包含NULL值的列上创建了索引，那么这个索引会包含NULL值，你可以在查询这些值时利用索引。

当你在包含NULL值的列上执行查询时，InnoDB存储引擎会使用索引来提高查询性能。例如，你可以使用IS NULL或者IS NOT NULL条件来查询NULL值或者非NULL值，InnoDB存储引擎都会使用索引来快速找到满足条件的值。

不过虽然InnoDB存储引擎支持在NULL值上使用索引，但是这并不意味着所有的查询都会使用索引。实际上，MySQL查询优化器会根据查询的具体情况决定是否使用索引。例如，如果表中大部分的行都满足查询条件，那么查询优化器可能会选择全表扫描，而不是使用索引。

索引优化建议

表记录很少不需创建索引 ：索引是要有存储的开销
一个表的索引个数不能过多：

- 空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。
- 时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。

频繁更新的字段不建议作为索引：频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。
区分度低的字段，不建议建索引：比如性别，男，女；比如状态。区分度太低时，会导致扫描行数过多，再加上回表查询的消耗。如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。姓名，手机号就非常适合建索引。
在 InnoDB 存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段：主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少，比较臃肿，查询时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值，主键值越长，一个页可存储的数据量就会越少，查询时磁盘IO次数会增多，查询效率会降低。
不建议用无序的值作为索引：例如身份证、UUID。更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。
尽量创建组合索引，而不是单列索引： 优点：（1）1个组合索引等同于多个索引效果，节省空间。（2）可以使用覆盖索引 创建原则：组合索引应该把频繁用到的列、区分度高的值放在前面。频繁使用代表索引的利用率高，区分度高代表筛选粒度大，这样做可最大限度利用索引价值，缩小筛选范围

索引基本需求？

索引的数据结构，至少需要支持两种最常用的查询需求：

等值查询：根据某个值查找数据，比如： select * from t_user where age=76;
范围查询：根据某个范围区间查找数据，比如： select * from t_user where age>=76 and age<=86;
排序
分组
...

同时需要考虑 时间和空间 因素：性价比高

在 执行时间 方面，我们希望通过索引，查询数据的时间尽可能小；

在 存储空间 方面，我们希望索引不要消耗太多的内存空间和磁盘空间。

MySQ进阶—索引篇

# 什么是索引？

# 简易版本：

# 详细版本：

# 索引优势和劣势？

# 索引有哪些类型？

# MySQL 如何实现的索引机制？

# InnoDB 索引与 MyISAM 索引实现的区别？

# 一个表如果没有创建索引，那么还会创建B+树吗？

# 普通索引和唯一索引怎样选？

# 聚簇索引和非聚簇索引 B+ 树实现有什么区别？

# 主键索引

# 辅助索引

# 说一下 B+树 中聚簇索引的查找（匹配）逻辑？

# 1）等值查询

# 2）范围查询

# 说一下 B+树 中非聚簇索引的查找（匹配）逻辑？

# 1）等值查询

# 2）什么是回表查询？

# 为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

# 一个B+树中大概能存放多少条索引记录？

# 使用B+树存储的索引crud执行效率如何？

# 为什么官方建议使用自增长主键作为索引?（说一下自增主键和字符串类型主键的区别和影响)

# 自增主键理解？

# 为什么自增主键不连续

# InnoDB 为什么推荐用自增 ID

# 使用int自增主键后最大id是10，删除id 10和9,再添加一条记录，最后添加的id是几?

# 删除后重启 MySQL 然后添加一条记录最后 Id 是几?

# 使用索引一定能提高效率吗？

# 如果是大文本内存，如何创建优化索引？

# 什么是 聚簇索引和 非聚簇索引 ？

# 一张表可以有多个（非）聚簇索引吗？

# CRUD时 聚簇索引和非聚簇索引的区别是什么？

# 详细谈谈你对myisam与innodb使用场景的理解？

# 非聚簇索引为何不存数据地址而存主键？

# 什么是覆盖索引？

# 非聚簇索引（聚簇索引）一定回表操作吗？

# 回表？为什么要回表查询？直接存储数据不好吗？

# 如果把一个 InnoDB表的主键删掉，是不是就没有主键,就没办法进行回表查询了?

# 最左匹配原则？

# 复合索引创建时字段顺序不一样使用效果一样吗?

# 什么是索引下推？

# 如果表中有字段为null，又被经常查询该不该给这个字段创建索引（阿里二面原题）?

# 索引在什么场景下适合创建？索引设计原则（查询快，占用空间少）

# 索引失效场景？

# 为什么LIKE以%开头索引会失效?

# 有字段为null索引是否会失效?

# 索引优化建议

# 索引基本需求 ？

什么是索引？

简易版本：

详细版本：

索引优势和劣势？

索引有哪些类型？

MySQL 如何实现的索引机制？

InnoDB 索引与 MyISAM 索引实现的区别？

一个表如果没有创建索引，那么还会创建B+树吗？

普通索引和唯一索引怎样选？

聚簇索引和非聚簇索引 B+ 树实现有什么区别？

主键索引

辅助索引

说一下 B+树中聚簇索引的查找（匹配）逻辑？

1）等值查询

2）范围查询

说一下 B+树中非聚簇索引的查找（匹配）逻辑？

1）等值查询

2）什么是回表查询？

为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

一个B+树中大概能存放多少条索引记录？

使用B+树存储的索引crud执行效率如何？

为什么官方建议使用自增长主键作为索引?（说一下自增主键和字符串类型主键的区别和影响)

自增主键理解？

为什么自增主键不连续

InnoDB 为什么推荐用自增 ID

使用int自增主键后最大id是10，删除id 10和9,再添加一条记录，最后添加的id是几?

删除后重启 MySQL 然后添加一条记录最后 Id 是几?

使用索引一定能提高效率吗？

如果是大文本内存，如何创建优化索引？

什么是聚簇索引和非聚簇索引？

一张表可以有多个（非）聚簇索引吗？

CRUD时聚簇索引和非聚簇索引的区别是什么？

详细谈谈你对myisam与innodb使用场景的理解？

非聚簇索引为何不存数据地址而存主键？

什么是覆盖索引？

非聚簇索引（聚簇索引）一定回表操作吗？

回表？为什么要回表查询？直接存储数据不好吗？

如果把一个 InnoDB表的主键删掉，是不是就没有主键,就没办法进行回表查询了?

最左匹配原则？

复合索引创建时字段顺序不一样使用效果一样吗?

什么是索引下推？

如果表中有字段为null，又被经常查询该不该给这个字段创建索引（阿里二面原题）?

索引在什么场景下适合创建？索引设计原则（查询快，占用空间少）

索引失效场景？

为什么LIKE以%开头索引会失效?

有字段为null索引是否会失效?

索引优化建议

索引基本需求？