MySQL：MySQL索引10连问，顶住！

一、MySQL索引有哪些类型

1.物理存储维度

  聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。

  非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。

2.逻辑维度

  主键索引：一种特殊的唯一索引，不允许有空值。

  普通索引：MySQL中基本索引类型，允许空值和重复值。

  联合索引：多个字段创建的索引，使用时遵循最左前缀原则。

  唯一索引：索引列中的值必须是唯一的，但是允许为空值。

  空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

二、MySQL索引可能会失效的场景

1.条件中有OR：
  当查询条件中包含OR，并且OR连接的字段中有未建立索引的字段时，索引可能会失效。
  例如：WHERE col1 = ‘value1’ OR col2 = ‘value2’，如果col1有索引而col2没有，那么索引可能不会被使用。

2.LIKE查询以%开头：
  当使用LIKE查询并且模式串以通配符%开头时，索引通常不会被使用。
  例如：WHERE column_name LIKE ‘%value’。

3.字段类型不匹配：
  如果查询条件中的数据类型与表中的数据类型不匹配，并且MySQL需要进行隐式类型转换时，索引可能会失效。
  例如：表中字段是VARCHAR类型，但查询时未加引号，MySQL将其视为数字类型进行转换。

4.函数或运算操作：
  如果在查询条件中对索引列使用了函数或进行了运算（如加减乘除），索引可能会失效。
  例如：WHERE YEAR(date_column) = 2023。

5.未使用联合索引的最左前缀：
  对于联合索引（也称为复合索引或多列索引），如果查询条件没有使用索引的最左侧列，那么索引可能不会被使用。
  例如：有一个由col1和col2组成的联合索引，但查询条件是WHERE col2 = ‘value’。

6.范围查询后的列：
  如果在联合索引的某个列上进行了范围查询（如BETWEEN、<、>等），那么该列之后的索引列将不会被用于查询优化。

7.使用NOT IN、NOT EXIST：
  这些操作可能会导致索引失效，因为它们需要扫描表中大部分或全部数据来确定不满足条件的记录。

8.索引列上包含NULL值：
  对于某些类型的索引（如B-tree索引），当查询条件中包含IS NOT NULL时，索引可能不会被使用。但是，请注意，这并非所有情况都适用，因为索引对NULL值的处理方式取决于具体的存储引擎和配置。

9.优化器判断使用索引效率不高：
  MySQL优化器会根据统计信息和查询的具体情况来判断使用索引是否比全表扫描更高效。如果优化器认为全表扫描更快，那么即使存在索引，MySQL也可能选择不使用它。

10.字段的字符集类型不相同：
  左连接查询或者右连接查询查询关联的字段编码（字符集）格式不一样，可能导致索引失效。

三、哪些场景不适合建立索引

1.在WHERE中使用不到的字段：
  如果某个字段在WHERE子句（包括GROUP BY和ORDER BY）中从未被用作查询条件，那么在这个字段上创建索引通常是没有必要的。索引的价值在于能够快速定位数据，如果字段在查询中从未被使用，那么索引就不会被利用，反而会浪费存储空间和维护索引的开销。

2.数据量小的表：
  当表中的数据量很小（例如少于1000条记录）时，创建索引可能并不会带来明显的性能提升。因为此时全表扫描的开销可能并不大，而索引的维护（如插入、删除、更新时索引的更新）可能会成为性能瓶颈。

3.有大量重复数据的列：
  如果某个字段的值有大量的重复（例如性别字段只有“男”和“女”两个值），那么在这个字段上创建索引可能并不会提高查询效率。因为索引的主要目的是快速定位不同的值，当值大量重复时，索引的效果会大打折扣。此外，这样的索引还可能会降低更新操作的速度，因为每次更新都需要更新索引。

4.经常更新的表或字段：
  对于经常更新的表或字段，过多的索引可能会成为性能瓶颈。因为每次更新数据，相关的索引也需要被更新，这会增加写操作的开销。因此，在频繁更新的字段或表上应该避免创建过多的索引，或者在必要时选择创建低选择性的索引（即索引中包含大量重复值的索引）。

5.无序的值：
  对于一些无序的值（如UUID、MD5值等），创建索引可能并不是一个好的选择。因为这些值在索引中的分布是随机的，无法有效地利用索引的排序特性来加速查询。此外，这些值通常较长，可能会增加索引的存储空间和维护开销。

6.删除不再使用或很少使用的索引：
  随着数据库的使用和数据的变化，一些原有的索引可能不再需要或很少被使用。这些不再需要的索引不仅浪费了存储空间，还可能成为性能瓶颈（因为它们仍然需要被维护和更新）。因此，数据库管理员应该定期检查和删除这些不再需要的索引。

7.避免定义冗余或重复的索引：
  在创建索引时，应该避免定义冗余或重复的索引。例如，如果一个表已经有一个包含多个列的联合索引（如(col1, col2, col3)），那么再单独为col1或(col1, col2)创建索引就是冗余的。因为MySQL在查询时会优先选择使用最长的匹配前缀的索引来加速查询。因此，应该仔细规划索引的创建，避免冗余和重复。

四、MySQL索引为什么要用B+树

  参考我的文章，有详细说明：《MySQL索引结构为什么选用B+树》

五、什么是覆盖索引？

  1. 覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行。

  2. 一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

关键特点：

  包含查询所需的所有字段：覆盖索引中，索引本身包含了查询语句中涉及的所有字段，避免了需要额外去主键索引中查找的操作。

  避免回表操作：回表操作指的是在通过索引定位到行后，还需要通过主键再去表中检索数据的操作。覆盖索引避免了这种额外的操作，减少了I/O消耗，提高了查询效率。

六、什么是回表？有什么优化策略？

  当你在非聚簇索引上执行查询时，MySQL会首先通过非聚簇索引找到主键的值，然后再使用这个主键值去聚簇索引中查找具体的行数据。这个过程涉及到两次查找操作，一次是在非聚簇索引中，另一次是在聚簇索引中，因此被称为“回表”。

示例
  假设你有一个用户表，表中有ID（主键）、Name和Age三个字段，并且你为这个表创建了一个基于Name的非聚簇索引。当你执行以下查询时：

SELECT * FROM users WHERE Name = 'John';

  MySQL会首先通过Name索引找到Name为’John’的用户的主键ID，然后再使用这个主键ID去聚簇索引中查找对应的行数据，这就是一个回表操作。如果你只查询Name字段，那么就可以避免回表：

SELECT Name FROM users WHERE Name = 'John';

  这个查询就可以利用覆盖索引来直接获取Name字段的值，而不需要回表。

优化策略：

  1.使用覆盖索引：确保查询的列都包含在索引中，这样就不需要回表去查找聚簇索引中的数据了。

  2.调整查询语句：尽量只查询需要的列，避免SELECT *。

  3.使用聚簇索引的主键：由于聚簇索引是按照主键的顺序来组织存储的，因此使用主键进行查询通常比使用非主键查询要快。

七、什么是索引的最左前缀原则

  索引的最左前缀原则是指在创建复合索引时，如果索引包含了多个列（A, B, C），那么查询条件必须包含索引的最左侧列（A），才能使用该索引。同时，查询条件可以包含索引的最左侧列的连续子集（如A, A和B, A、B和C），但不能跳过中间的列。

示例：
假设有一个复合索引(a, b, c)，以下查询可以使用该索引：

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

八、大表如何添加索引

1.影子表策略：
①.创建一个与原表结构相同的新表。
②.在新表上添加索引。
③.重命名原表和新表，使新表承担业务。
④.同步原表和新表之间的数据。

2.在线DDL工具：
  如pt-online-schema-change，它可以在不锁定原表的情况下，在线地修改表结构，如添加索引。

九、什么是索引下推？

  索引下推是MySQL 5.6版本后引入的一项新特性，用于优化数据查询。

原理：
  1. 在不使用索引下推优化时，存储引擎首先通过索引检索到数据，然后将这些数据返回给MySQL服务器。服务器在接收到数据后，会进一步判断这些数据是否符合查询条件。

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

应用场景：
  主要应用于那些涉及到多个列的查询条件，并且这些列都被包含在索引中的情况。通过索引下推，可以更加高效地利用索引，提高查询性能。

十、Varchar(10)和int(10)代表什么含义？

VARCHAR(10)
  VARCHAR 是一个可变长度的字符串数据类型。

  括号中的数字（在这个例子中是10）指定了该字段可以存储的最大字符数。在这个例子中，VARCHAR(10) 可以存储最多10个字符的字符串。

  需要注意的是，这个长度指的是字符数，而不是字节数。因此，对于多字节字符集（如UTF-8），一个字符可能占用多个字节。

  VARCHAR 类型字段的实际存储需求是字符串的长度加1或2个字节（用于记录字符串的长度）。

INT(10)
  INT 是一个整数数据类型。

  括号中的数字（在这个例子中是10）实际上是一个显示宽度，它并不限制存储值的范围或大小。也就是说，一个 INT 字段总是存储一个4字节的整数，其值的范围是从 -2,147,483,648 到 2,147,483,647（对于有符号整数）或从 0 到 4,294,967,295（对于无符号整数）。

  显示宽度主要用于与 ZEROFILL 选项一起使用时，确保整数总是显示特定数量的数字，通过在左侧填充零来达到这个宽度。但是，这只是一个显示特性，并不影响存储或计算。