一、MySQL 索引匹配原则
-
全值匹配
- 指的是和索引中的所有列进行匹配。例如，对于一个有多个列的复合索引，查询条件中使用了所有这些列的值进行精确匹配，这种情况下索引的效果最好。
-
最左前缀匹配
- MySQL 会根据索引的创建顺序，从左到右依次进行匹配。如果查询条件中只包含索引的最左边的一部分列，也可以使用索引，但匹配的范围会逐渐缩小。
- 例如，有一个复合索引（col1, col2, col3），查询条件中包含 col1 和 col2 或者只包含 col1 时，都可以使用这个索引进行匹配，但只包含 col2 或者 col3 时则无法使用该索引。
-
匹配列前缀
- 可以使用索引列的值的前缀进行匹配。例如，对于一个字符串类型的列创建了索引，如果查询条件中只使用了该列值的前一部分字符进行匹配，也可能会使用到索引，但这种情况下索引的效率可能会降低。
-
范围匹配
- 当查询条件中有范围查询（如大于、小于、BETWEEN 等）时，MySQL 可以使用索引进行范围匹配。但是，一旦出现范围查询，其右边的列将无法再使用索引进行精确匹配。
- 例如，对于复合索引（col1, col2, col3），如果查询条件中有 col1 的范围查询，那么只有 col1 可以使用索引进行范围匹配，col2 和 col3 则无法使用索引进行精确匹配。
-
精确匹配某一列并范围匹配另一列
- 对于复合索引，如果查询条件中对某一列进行精确匹配，对另一列进行范围匹配，那么可以使用索引，但同样范围匹配列右边的列将无法使用索引进行精确匹配。
-
只访问索引的查询
- 某些查询可以只通过索引就能够获取到所需的全部信息，而不需要再访问表中的数据行。这种情况被称为 “覆盖索引”，可以极大地提高查询性能。
二、MySQL 索引设计原则
-
选择合适的列创建索引
- 选择经常在查询条件中出现的列创建索引，这样可以提高查询性能。例如，WHERE 子句、JOIN 子句中经常使用的列。
- 选择区分度高的列创建索引。区分度高意味着列中的不同值较多，如果一个列的取值只有很少的几种，那么创建索引的效果可能不明显。可以通过计算列的基数（cardinality）来判断区分度。
-
避免过多索引
- 过多的索引会占用大量的磁盘空间，并且在对表进行插入、更新和删除操作时，需要维护更多的索引，这会降低数据操作的性能。
- 只在必要的情况下创建索引，对于很少使用的列或者查询性能提升不明显的列，不要创建索引。
-
考虑索引的长度
- 对于字符串类型的列，如果创建索引，可以考虑只对列的前一部分字符创建索引，这样可以减少索引占用的空间，提高索引的维护效率。但需要注意的是，这样可能会降低索引的选择性。
- 例如，对于一个长度为 200 的 VARCHAR 类型的列，如果前 50 个字符已经具有较高的区分度，可以只对前 50 个字符创建索引。
-
复合索引的设计
- 当多个列经常一起出现在查询条件中时，可以考虑创建复合索引。复合索引的设计要遵循最左前缀匹配原则，将最常用的列放在最左边。
- 合理安排复合索引中列的顺序，根据查询的特点和频率来确定列的先后顺序，以提高索引的使用效率。
-
避免在频繁更新的列上创建索引
- 如果一个列经常被更新，那么在这个列上创建索引会增加索引的维护成本，降低数据更新的性能。
- 对于频繁更新的表，可以考虑在一些相对稳定的列上创建索引，或者在查询性能和数据更新性能之间进行权衡。
-
定期维护索引
- 随着数据的不断插入、更新和删除，索引可能会变得碎片化，影响查询性能。可以定期使用 OPTIMIZE TABLE 命令来优化表和重建索引，以提高索引的效率。
-
考虑使用覆盖索引
- 如果查询可以通过索引就能够获取到所需的全部信息，那么可以使用覆盖索引来避免访问表的数据行，从而提高查询性能。
- 在设计索引时，可以考虑将查询中需要的列都包含在索引中，以实现覆盖索引。
