MySQL有哪些索引（按字段特性分类）

MySQL有主键索引、唯一索引、普通索引、前缀索引、联合索引（tip：看建立在什么字段上面就是什么索引）

主键索引：就是建立在主键上的索引，通常与表一起创建，一张表最多只有一个主键索引，索引列的值不能为NULL
唯一索引：建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值
普通索引：建立在普通字段上的索引，既不要求字段为主键，也不要求字段为UNIQUE
前缀索引：对字符类型字段的前几个字符建立的索引，而不是整个字段，目的是减少索引占用的空间，提升查询效率
联合索引：将多个字段组合成一个索引

MySQL主键是聚簇索引吗

是的。聚簇索引就是在定义索引时，将数据和索引放在同一课B+树上

每张表只有一个聚簇索引，会根据不同的情况选择不同的列作为索引
如果定义了主键，默认使用主键作为聚簇索引
如果没有主键，会选择第一个不包含NULL的唯一列作为聚簇索引的索引键
如果上述都没有，InnoDB将自动生成一个隐式自增id（row_id）作为聚簇索引的索引键

主键索引为什么不推荐有业务含义

首先业务具有变动的可能性，可能导致主键字段发生了变动，然而维护主键是成本很高的一件事
其次业务含义的主键可能不是顺序递增的，有可能发生页分裂的问题，从而影响性能

主键用自增还是UUID

用自增ID比较好，因为UUID是随机值，在数据插入的过程中，会导致页分裂的问题，性能下降
其次自增ID在分库分表的环境下不适用，因为没法保证全局唯一，需要使用雪花算法等来作为主键

普通索引和唯一索引有什么区别，哪个更好

普通索引的值可以是任意的，唯一索引的值必须是唯一的。

我认为普通索引性能更好，因为普通索引在更新的时候，如果更新的数据页不在内存的话，可以直接把更新操作缓存在change buffer中，更新操作就结束了
然而唯一索引因为需要有唯一性约束，如果更新的页不在内存中的话，需要从磁盘读取对应的数据页到内存中，判断是否发生唯一性冲突，会涉及到磁盘IO的访问，性能不如普通索引

介绍一下什么是外键约束

外键就是从表中用来引用主表中数据的那个字段，外键约束确保了数据的引用完整性，也就是从表中的外键必须存在于主表中的主键。如果发现要删除的主键记录，正在被从表的外键引用，就会发生外键约束错误，从而保证了两个表的数据一致性

外键有什么优劣势

优点：

一致性：如果一个表的键被另外一个表引用，外键可以保证这个字段一定存在于那个表中，从而保证了数据的一致性
完整性：外键可以防止在引用表中删除正在被其他表引用的记录，从而保证了数据的完整性

劣势：

性能问题：数据库的每次操作都要检查外键约束，硬性保证数据的一致性，会造成性能下降
锁竞争问题：在使用外键的情况下，每次修改数据库都要检查外键约束，这需要额外获取读锁，在高并发情况下很容易造成死锁
无法使用分库分表场景：因为外键难以跨越不同的库来建立关系，然而现在大部分项目为了应付高并发都会采用分库分表，因此外键也不适用了

为什么要建立索引

加快查询速度，在不加索引的情况下查询一条记录是O（n），有索引的情况下查询复杂度是O（logn）
避免外部排序和使用临时表等问题
在表数据量大的时候，为索引分配就不是按页，而是按区。每个区的大小是1MB,可以放下64个页，这样使得链表中相邻的页物理位置也相邻。可以将随机IO变成顺序IO

一般选择什么样的字段来建立索引

使用于索引的场景：

字段具有唯一性的建立唯一性索引
经常用于WHERE查询条件的字段，为了提高查询速度，可以建立索引
经常用于GROUP BY 和 ORDER BY的字段建立索引，这样查询的时候就不用再做一次排序了，因为索引在B+树中就是排序好的

不适合索引的场景：

WHERE、GROUP BY、ORDER BY里用不到的字段，不需要建立索引
字段中存在大量重复数据的不需要建立索引，比如性别字段。就算建立了索引每次也可能搜索到一半的数据，在这种情况下，MySQL的优化器发现某个值在表中出现比例较高的时候，会忽略索引，进行全表扫描，这时候建立索引就没起到作用，反而还占用空间
经常更新的字段不需要建立索引，经常维护索引会影响数据库性能

索引越多越好吗

不是

因为索引需要占用空间，当索引过多的时候，占用空间很大
其次索引过多数据库维护成本过高，每次对表增删查改时，都需要维护B+树的各个索引

什么时候不用索引最好

建立了索引，空间上索引需要占用磁盘空间。其次时间上每次对数据库表增删查改时，需要维护B+树的各个索引，需要性能开销
首先如果一张表写多读少的情况下，不建议建立索引，因为维护索引的开销可能超过索引性能的提升
其次当一张表中某个列的值高度重复，那么建立了索引也没用，优化器会忽略索引，进行全表扫描，这样不仅占用了存储空间，还影响了增删查改的效率

字段为什么要定义成NOT NULL

如果查询的列包含NULL，对MySQL优化器来说，可能成本过高而使用全盘扫描
如果某列存在NULL的情况，可能导致count（）函数不准确，因为count（）函数不会统计值为NULL的列
NULL虽然是没有意义的值，但会占用物理空间，行格式中会至少用1字节来存储NULL值列表

索引怎么优化

对于只需要查询几个字段的SQL来说，对这些字段建立联合索引，这样查询方式就变成了覆盖索引，避免了回表，减少了大量的I/O操作
主键索引最好是递增的值，因为随机值，在数据插入的情况下可能会造成页分裂，而页分裂会产生内存碎片，导致索引结构不紧凑，影响查询效率
避免写出发生索引失效的SQL，比如不要对索引进行计算、函数、类型转换操作，联合索引要遵循最左匹配原则
对于一些大字符串的索引，我们可以用前缀索引只对索引列的前缀部分建立索引，节省索引的空间，提高查询效率

建立了索引，查询的时候一定用得到索引吗

索引失效：对索引字段进行左模糊匹配，对索引进行计算、函数、类型转换操作，联合索引不遵循最左匹配，就会造成索引失效，查询不会走索引
优化器成本：在使用二级索引的时候，优化器会计算回表的成本和全盘扫描的成本，如果回表的代价太大，优化器会选择走全盘扫描

一个VARCHAR类型的字段time，WHERE time = 20230922 会命中索引吗

不会，因为MySQL在遇到字符串类型和数字类型比较的时候，会自动把字符串转换成数字，这个过程会执行CAST函数
于是在执行过程中会索引字段使用了函数，会导致索引失效
但如果反过来，where id = ‘1’，如果id是整数不会导致索引失效，因为字符串对象是‘1’，函数发生在它的身上，而不是索引身上

MySQL最新版本解决了索引失效的问题了吗

MySQL8.0新特性：函数索引和索引跳跃扫描机制

函数索引：可以针对函数计算后的值建立索引，可以解决对索引使用函数导致索引失效的问题
索引跳跃扫描：在使用联合索引扫描的场景下，即使不满足最左匹配原则，在部分场景下依然会使用联合索引

索引跳跃扫描的条件：

查询只能涉及一张表，多表关联不能使用该特性
查询SQL不能涉及GROUY BY 和 DISTINCT子句
查询字段必须是索引中的字段（不能发生回表）
满足一定的组合索引形式，比如对于联合索引（a，b，c）来说，a，c可以为空，但是b不能为空

什么是最左匹配原则

假设有一个联合索引（a，b，c），它的存储顺序是先按a排序，a相同的情况下再按b排序，b相同的情况下再按c排序。最左匹配原则：

从联合索引最左边的索引列开始匹配查询条件，然后依次从左到右的顺序匹配，如果查询条件右边没有用到某个列，那么该列右边的所有列都无法走索引
当查询条件中使用了某个列，但是该列的值包含范围查询，范围查询的字段可以走联合索引，但是该字段后面的所有字段不能走索引

建立联合索引需要注意什么

把区分度大的字段放在联合索引最左侧，有助于提高索引的过滤效果，比如UUID这类字段放最左边
区分度小的放在联合索引最左侧，优化器可能根据成本考虑会选择全盘扫描，不走索引

索引下推是什么

索引下推能够减少二级索引在查询时的回表操作，将Server层负责的事情交给存储引擎层去处理

比如联合索引（a，b，c），查询条件为a = ？和 c = ？时，由于最左匹配原则，c无法走索引。在没有索引下推之前，查询语句走二级索引的时候，会回表读取c的值，然后在server层进行过滤。如果有索引下推，由于二级索引中存在c，那么将server层的过滤交给存储引擎层，在二级索引中将c的值过滤，减少了回表的次数
查询条件 where a > 100 and b = 100 and c = 100 order by d 怎么建立索引
建立（b，c，d，a）的联合索引，这样b，c字段都会走索引，然后d能利用索引的有序性，避免了filesort。order by d 相当于范围查询，最后的字段a无法用到索引，但是可以通过索引下推，避免了回表

select id, name from XX where age > 10 and name like ‘xx%’, 有联合索引(name，age)，说一下查询过程

tip：

先说哪些能走索引，哪些不能走索引再说哪个字段可以索引下推最后说查询需不需要回表（发不发生索引覆盖）

联合索引先走name，再age。结构上是name先排序，然后age排序。查询的过程先匹配name，由于name是右模糊匹配，并不会发生索引失效，但是在右模糊匹配后（相当于范围查询），age并不是有序，因此age无法走索引
但是age在二级索引中，因此age可以索引下推
最后，id由于是主键，因此id和name都可以在二级索引中找到，会发生索引覆盖，不需要回表

where id not in （X, X, X）会走索引吗

要看查询成本，如果走某个索引花费的随机IO从聚簇索引查（顺序IO）的成本还要高，就不会走索引
比如，num字段（非唯一二级索引）只包含3个值，1，2，3。然而1，2各有100W行，3只有几行，那么not in （1， 2）会走索引，not in （3）不会走索引
如果查询条件包括索引列和非索引列，查询过程是怎么样的
查询过程先根据二级索引拿到主键id，再回表过滤非索引列，查询过程会查两个B+树，涉及回表
比如select a from XX where a = ? and d = ? ，a是索引，d不是索引，会先按索引查询，再回表过滤d