Mysql-索引应用

目录

索引应用

MySQL有哪些索引?

普通索引和唯一索引有什么区别? 哪个更新性能更好? 、

聚簇索引的主键索引怎么设置? 追问:假如你不设置会怎么样?

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

索引越多越好吗?

索引怎么优化? （覆盖索引优化、防止索引失效、主键递增、前缀索引优化）

建立了索引，查询的时候一定会用到索引吗? （索引失效、优化器基于成本选择执行计划）

如果我定义了一个varchar类型的日期字段，并且有一个数据是‘20230922’，如果这个日期字段上有索引，那如果我查询的where条件是where time=20230922不加单引号，还会命中索引吗? 为什么?

MySQL最新版本解决了索引失效的哪些情况了吗? （函数索引：函数计算后的值也能走索引、索引跳跃扫描机制（最左前缀））

什么是最左匹配原则?

建立联合索引有什么需要注意的? （区分度大的放在最左侧，最左匹配原则、范围查询后的不走索引）

最左匹配原则查询顺序

索引下推是什么？ MySQL5.6 添加的，用于优化数据查询

where a>1 and b=2 and c <3 怎么建立索引?

(A,B,C) 联合索引 select * from tbn where a=? and b in (?,?) and c>? 会走索引吗？

where a>100 and b=100 and c=123 order by d 怎么建立联合索引?

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

---

索引应用

MySQL有哪些索引?

我了解到 MySQL有主键索引、唯一索引、普通索引、前缀索引、联合索引这几种索引。

Innodb 引擎会要求每一张数据库表都必须要有一个主键索引，索引列的值不允许有空值。比如表里的 id 字段就是主键索引

唯一索引: 保证数据列中每行数据的唯一性，但允许有空值。

然后针对查询比较频繁的字段，我们可以对这个字段建立普通索引，如果是多个字段的话，可以考虑建立联合索引，利用索引覆盖的特性提高查询效率。

对于长文本、字符串等类型的字段，比如文章标题、商品名称等，我们可以只对这些字段的前缀部分建立索引，也就是建立前缀索引，这样可以减少索引的存储空间。

普通索引和唯一索引有什么区别? 哪个更新性能更好? 、

• 查询单个值时，唯一索引可能略快，因为它在找到第一个匹配项后可以终止搜索。

• 插入和更新操作，普通索引可能略快，因为它不需要进行唯一性检查。

1. 普通索引列的值是可以重复的，而唯一索引列的值是必须唯一的，当我们对唯一索引插入了一条重复的值，会因为唯一性约束而报错。

2. 我认为普通索引的更新性能会更好，因为普通索引在更新的时候，如果更新的数据页不在内存的话，可以直接把更新操作缓存在 change buffer 中，更新操作就结束了。（不需要唯一性检查）

3. 但是，唯一索引因为需要有唯一性约束，如果更新的数据页不在内存的话，需要从磁盘读取对应的数据页到内存，判断有没有冲突，这里会涉及磁盘随机IO的访问。

4. 普通索引因为能使用change buffer 特性，所以普通索引的更新相比于唯一索引，减少了随机磁盘访问，所以更新性能更好

聚簇索引的主键索引怎么设置? 追问:假如你不设置会怎么样?

InnoDB在创建聚簇索引时，会根据不同的场景选择不同的列作为索引:

1. 如果有主键，默认会使用主键作为聚簇索引的索引键

2. 如果没有主键，就选择第一个不包含 NULL值的唯一列作为聚簇索引的索引键

3. 在上面两个都没有的情况下，InnoDB将自动生成一个隐式自增rowid列作为聚簇索引的索引键

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

适用索引的场景:

1. 字段有唯一性限制的，比如商品编码

2. 经常用于WHERE查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引

3. 经常用于GROUPBY和ORDER BY的字段，这样在查的时候就不需要再去做一次排序了，因为建立索引之后在 B+ Tree 中的记录都是排序好的。

不适合索引的场景

1. WHERE条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。

2. 区分度低的字段，不需要创建索引，比如性别字段只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

3. 频繁更新的字段，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

4. 不建议用无序的值(例如身份证、UUID )作为索引，当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

• 数据表较小：当表中的数据量很小，或者查询需要扫描表中大部分数据时，数据库优化器可能会选择全表扫描而不是使用索引。在这种情况下，维护索引的开销可能大于其带来的性能提升。

索引越多越好吗?

不是的，索引虽然能提高查询效率，但是多建立一个索引，就意味着新生成一个 B+树索引，是需要占用存储空间的，特别是在表数据量非常大的时候，索引占用的空间越大

索引越多，数据库的写入性能会下降，因为每次对表进行增删改操作的时候，都需要去维护各个 B+ 树索引的有序性

索引怎么优化? （覆盖索引优化、防止索引失效、主键递增、前缀索引优化）

我用过这几种优化的方式

1. 对于需要查询几个字段数据的 SQL 来说，我们可以对这些字段建立联合索引，这样查询方式就变成了覆盖索引，避免了回表，减少了大量的I/O 操作。

2. 我们的主键索引最好是递增的值，因为我们索引是按顺序存储数据的，如果主键的值是随机的值，可能会引发页分裂的现象，页分裂会导致大量的内存碎片，这样索引结构不紧凑了，就会影响查询效率。

3. 我们要避免写出发生索引失效的 SQL 的语句，比如不要对索引列进行左或者左右模糊匹配、不要对索引进行计算、函数、类型转换操作，联合索引要能正确使用要遵循最左匹配原则等等。在WHERE子句中，如果在OR 前的条件列是索引列，而在OR 后的条件列不是索引列，那么索引会失效。

• 使用不等于（<>）或者 NOT 操作符：这些操作符通常会使索引失效，因为它们会扫描全表。

• OR 操作符：如果查询条件中使用了 OR，并且 OR 两边的条件分别涉及不同的索引，那么这些索引可能都无法使用。

  • 使用 OR 操作符时，如果 OR 两边的条件涉及不同的索引，数据库引擎在大多数情况下无法同时使用多个索引来优化查询。这是因为 OR 操作符要求满足任意一边的条件即可，这增加了查询优化的复杂性。

1. 对于一些大字符串的索引，我们可以考虑用前缀索引只对索引列的前缀部分建立索引，节省索引的存储空间，提高查询性能。

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

   1. 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化 如count操作

   2. NULL 值是一个没意义的值，但是它会占用物理空间，存在null值列，至少会用 1 字节空间存储 NULL 值列表

建立了索引，查询的时候一定会用到索引吗? （索引失效、优化器基于成本选择执行计划）

不是的。

1. 我了解到即使查询使用到了索引，也是可能不走索引的

   1. 比如:当我们查询语句对索引字段进行左模糊匹配、表达式计算、函数、隐式类型转换操作，这时候查询语句就无法走索引了，查询方式就变成了全表扫描的方式。

   2. 还有我们使用联合索引进行查询的时候，如果没有遵循最左匹配原则，也是会发生索引失效的。

2. 优化器是基于成本考虑来选择查询的方式，在使用二级索引进行查询的时候，优化器会计算回表的成本和全表扫描的成本，如果回表的代价太高，优化器会选择不走索引，而是走全表扫描

如果我定义了一个varchar类型的日期字段，并且有一个数据是‘20230922’，如果这个日期字段上有索引，那如果我查询的where条件是where time=20230922不加单引号，还会命中索引吗? 为什么?

不会命中索引。

因为 mysql在遇到字符串和数字比较的时候，会发生隐式类型转换，会将字符串的对象转为数字，这个转换的过程实际上会涉及到函数。你说的这个查询，日期字段是字符串，那么发生隐式类型转换的时候，就会作用在日期这个索引字段上，对索引进行函数计算的话，是会发生索引失效的。

对于整型类型的索引列，例如id列，它的值是直接存储在索引中的，而不会发生函数计算。这意味着在查询中使用id进行匹配时，不需要对id进行任何函数计算或转换，只是简单地比较整数值。

MySQL最新版本解决了索引失效的哪些情况了吗? （函数索引：函数计算后的值也能走索引、索引跳跃扫描机制（最左前缀））

我了解到 MySQL8.0可以给字段增加函数索引，这个新特性可以解决对索引使用函数的时候，索引失效的问题。

还有一个新特性是索引跳跃式扫描，5.7 版本之前，使用联合索引的时候，如果不满足最左匹配原则，就会发生索引失效，而 8.0出了索引跳跃式扫描特性之后，即使没有遵循最左匹配原则，依然可以使用联合索引。

什么是最左匹配原则?

假设有一个(a，b，c) 联合索引，它的存储顺序是先按 a 排序，在 a 相同的情况再按b 排序，在 b 相同的情况再按 c 排序。由于这个特性，在使用联合索引时，存在最左匹配原则，具体的规则:

1. MySQL的联合索引会从最左边的索引列开始匹配查询条件，然后依次从从左到右的顺序匹配，如果查询条件没有使用到某个列，那么该列右边的所有列都无法使用走索引。

2. 当查询条件中使用了某个列，但是该列的值包含范围查询，范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

所以，我们在使用联合索引的时候，要遵守最左匹配原则，否则可能会出现部分索引字段走不了索引。

建立联合索引有什么需要注意的? （区分度大的放在最左侧，最左匹配原则、范围查询后的不走索引）

1. 最好把区分度比较大的字段放在联合索引最左侧，有助于提高索引的过滤效果，比如UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

2. 如果区分度很低的字段放在了联合索引最左侧，有可能会导致查询优化器会选择全表扫描，而不走索引了。

3. 联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引。但是，对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询，并不会停止匹配。

   1. 在 MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =<。

   2. 参考链接 https://zhuanlan.zhihu.com/p/573138586

最左匹配原则查询顺序

select * from T where c=1 and a=2 and b=3;

abc都能走索引，因为 where 查询条件字段的顺序并不会影响，MySQL优化器会帮我们调整字段的查询顺序所以也是符合最左匹配原则的。

索引下推是什么？ MySQL5.6 添加的，用于优化数据查询

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给 MySQL Server，MySQL Server 进行过滤条件的判断。

• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server 将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL Server 传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。

select * from t_user where age > 20 and reward = 100000;

where a>1 and b=2 and c <3 怎么建立索引?

1. 创建(abc)、(acb)、(ab)、(ac)联合索引，只有 a 能索引

2. 创建(cab)、(cba)、(ca)、(cb)联合索引，只有 c能索引

3. 创建(ba)联合索引，b和a都能走索引

4. 创建(bc)联合索引，b和c都能走索引

5. 创建(bac) 联合索引，b 和 a都能走索引，但比(ba)联合索引多了一个好处，c 字段能索引下推，会减少回表的次数;

6. 创建(bca) 联合索引，b和c都能走索引，但比(bc)联合索引多了一个好处，a 字段能索引下推，会减少回表的次数;

(A,B,C) 联合索引 select * from tbn where a=? and b in (?,?) and c>? 会走索引吗？

这个查询会使用到联合索引 (A,B,C)，因为条件是按照索引列 A、B、C 的顺序来的，这是理想的使用场景。

1. 对于 A=?：这个条件是一个精确匹配，MySQL 会使用索引来定位到满足条件 A=? 的记录。

2. 对于 B IN (?, ?)：这个条件指定了 B 列可以取两个可能的值。MySQL 会利用索引来查找所有匹配 A=? 且 B 列为这两个值中任意一个的记录。

3. 对于 C>?：这个条件是一个范围查询。在已经根据 A 和 B 筛选的基础上，MySQL 会继续利用索引来查找 C 列值大于指定值的记录。

where a>100 and b=100 and c=123 order by d 怎么建立联合索引?

我觉得建立 bcda 顺序的联合索引比较好，这时候b和c字段都能走索引，而且d能用索引有序性，避免 file sort（额外排序），最后的 a 字段虽然无法走索引(a无序)，但是可以利用索引下推, 减少回表的次数。

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

联合索引的顺序是先 name，再age，结构上是先根据 name 排序，name 相等的情况下再根据age 排序。所以优化器需要先匹配 name，name 这时候是右模糊查询，并不会发生索引失效，所以这条sql是能走联合索引的

具体的话，只有 name 能走索引，这是因为由于name右模糊查询后，age 字段的值并不是有序的，因此age 无法走索引，但是age可以进行索引下推。

最后查询的字段是id和name，这两个字段都能在联合索引上查找到，所以不需要回表,是索引覆盖查询。

name右模糊查询属于范围查询，后面字段不能用索引