主键索引

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

• 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
• 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

• 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

二级索引

优点：

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点：

• 依赖于有序的数据:跟聚簇索引一样，非聚簇索引也依赖于有序的数据
• 可能会二次查询(回表):这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

联合索引

使用表中的多个字段创建索引，就是 联合索引。

• 创建方式：

  ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3);

• 存储方式： 索引项按照索引定义中列的顺序进行排序。例如，对于索引 (a, b, c)，索引项会先按照 a 排序，然后在 a 相同的情况下按照 b 排序，最后在 a 和 b 都相同的情况下按照 c 排序。

• 最左前缀原则（Leftmost Prefix Rule）： 这是联合索引最重要的特性。它指的是查询可以利用索引的最左边的列。

  • 如果查询条件中包含了索引的最左边一个或多个列，则可以使用该索引。
  • 如果查询条件中没有包含索引的最左边的列，则无法使用该索引。

  例如，对于索引 (a, b, c)：

  • WHERE a = 1 可以使用索引。
  • WHERE a = 1 AND b = 2 可以使用索引。
  • WHERE a = 1 AND c = 3 仅在 a=1 上使用索引，然后对结果进行 c=1 的过滤。
  • WHERE a = 1 AND b = 2 AND c = 3 可以使用索引。
  • WHERE b = 2 无法使用索引。
  • WHERE b = 2 AND c = 3 无法使用索引。

覆盖索引

当查询只需要访问索引就能获取所有需要的数据，而不需要回表查询数据行时，就使用了覆盖索引。覆盖索引是一种查询优化策略，而不是一种新的索引类型。

前缀索引

前缀索引是 MySQL 中一种优化索引的技术，尤其适用于索引较长的字符串列。它允许你只索引字符串的前缀部分，而不是整个字符串，从而减小索引的大小，提高查询效率。

为什么需要前缀索引？

• 减少索引大小： 对于很长的字符串列（例如 TEXT 或 BLOB 类型），如果索引整个字符串，索引文件会非常大，占用大量的磁盘空间，并且降低索引的效率。使用前缀索引可以显著减小索引的大小。
• 提高查询效率： 更小的索引意味着更快的查找速度，可以提高查询性能。

如何创建前缀索引？

使用 ALTER TABLE 语句的 ADD INDEX 子句，并在索引列名后面加上括号和前缀长度：

ALTER TABLE 表名 ADD INDEX 索引名 (列名(前缀长度));

例如，假设有一个 email 列，类型为 VARCHAR(255)，你想创建一个前缀索引，只索引前 10 个字符：

ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

前缀长度的选择

选择合适的前缀长度非常重要。如果前缀长度太短，会导致索引的选择性（Selectivity）降低，即索引中重复的值很多，查询效率反而不高。如果前缀长度太长，索引的大小又会接近于全索引，失去了使用前缀索引的意义。

如何确定合适的前缀长度？

一个常用的方法是计算不同前缀长度的选择性，并选择一个足够高（接近于全索引的选择性），但又不会太长的前缀长度。

1. 计算全列的基数（Cardinality）：

   SELECT COUNT(DISTINCT 列名) FROM 表名;

2. 计算不同前缀长度的基数：

   SELECT COUNT(DISTINCT LEFT(列名, 前缀长度)) FROM 表名;

3. 比较不同前缀长度的基数与全列基数的比例，选择一个接近全列基数，但前缀长度又相对较短的值。

示例：

假设 users 表的 email 列有 10000 条记录，我们计算不同前缀长度的选择性：

在这个例子中，选择前缀长度为 10 或 15 都是不错的选择，因为它们的选择性都比较高，但 10 的前缀长度更短，索引更小。

前缀索引的缺点

• 无法用于 ORDER BY 和 GROUP BY： 前缀索引无法用于排序和分组操作。
• 无法用于覆盖索引（Covering Index）： 覆盖索引是指查询只需要访问索引而不需要访问数据行。由于前缀索引只包含部分列的值，因此无法用于覆盖索引。

索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

假设有一张 user 表，包含 id、name 和 age 三个字段，其中 id 是主键，name 列上有索引。现在执行以下查询：

SELECT * FROM user WHERE name LIKE '张%' AND age > 20;

• 没有索引下推： 存储引擎会通过 name 索引找到所有以“张”开头的记录，然后将这些记录全部返回给 Server 层。Server 层再根据 age > 20 的条件进行过滤。
• 使用索引下推： 存储引擎会通过 name 索引找到所有以“张”开头的记录，然后在存储引擎层检查这些记录的 age 是否大于 20，只有满足 age > 20 的记录才会返回给 Server 层。

所谓下推，实际上就是把原来 Server 层的条件过滤下放到引擎层来完成，减少 IO 次数。可以通过 EXPLAIN 命令查看查询计划，如果 Extra 列的值包含 Using index condition，则表示使用了索引下推。

索引下推应用范围：

1. 适用于执行计划是 range, ref, eq_ref, ref_or_null 的范围查询。
2. 对于 InnoDB 表，仅用于非聚簇索引。索引下推的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用索引下推 不会减少 I/O。
3. 子查询不能使用索引下推，因为子查询通常会创建临时表来处理结果，而这些临时表是没有索引的。
4. 存储过程不能使用索引下推，因为存储引擎无法调用存储函数。

索引使用建议

1. 不是所有字段都适合创建索引，一般为查询条件频繁的字段创建。
2. 复杂排序，或者数据量大的排序，不在 Sql 中排序，而是交给上层程序排序。
3. 频繁更新的字段不适合创建索引。
4. 索引并不是越多越好。
5. 在合适的情况下使用联合索引。
6. 不要有荣誉的索引，比如索引（a,b）和索引 （a）。
7. 前缀索引仅限于很长的字符串列，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
8. 避免索引失效的情况。