什么样的场景适合加索引,什么场景不适合加索引,索引失效的场景

具体来说，适合加索引的字段通常包括以下几类：

• 主键字段：主键字段通常会自动创建索引，因为它们用于唯一标识表中的每一行，这有助于提高查询效率。

• 外键字段：外键字段用于建立表之间的关系，对这些字段建立索引可以提高连接操作的效率,通常是一个表中的字段与另一个表中的主键字段关联.

• 离散度高的字段：离散度指的是字段值的唯一性程度。一个字段的值越分散，它作为索引的效率就越高。因此，具有高离散度的字段，如主键和具有唯一性约束的字段，非常适合作为索引。

• 存储空间小的字段：占用存储空间较少的字段更适合作为索引，例如整数字段相对于字符串字段来说，占用的空间更少，因此更适合作为索引。

• 存储空间固定的字段：存储空间固定的字段，如char类型，相对于text类型等可变长度字段，更适合作为索引。

• 经常出现在查询条件中的字段：经常出现在WHERE子句、ORDER BY或GROUP BY操作中的字段，应该考虑建立索引，因为这有助于提高查询和排序的效率。

• 连接字段：如果一个表经常与其他表进行连接操作，那么连接字段上建立索引可以显著提高连接查询的效率。

• 经常用于排序的字段：如果某个字段经常用于排序操作,，应该考虑建立索引，因为这有助于提高排序的效率。

以下是一些不适合加索引的具体场景：

• 频繁更新的字段：如果一个表需要经常进行更新操作，那么在该表上建立索引可能会导致写入效率降低，因为每次数据更新后，索引也需要同步更新。

• 数据重复度高的字段：对于数据重复度很高的字段，如性别、省份等，建立索引的效果不佳，因为这些字段的区分度不高，无法有效地缩小查询范围。

• 使用左模糊查询的字段：如果对某个字段进行左模糊查询（即查询条件为'%xxx'），那么即使该字段上有索引，索引也无法发挥作用，因为索引是从左到右进行匹配的。

• 选择性低的字段：如果一个字段的选择性很低，即该字段的不同值的数量与表中总行数相差无几，那么这样的字段不适合建立索引，因为索引的优势在于快速定位到少量数据。

• 空间有限的数据库：在存储空间有限的数据库中，过多的索引会占用宝贵的存储空间，这可能会影响数据库的整体性能。

• 小表查询优化不足：对于数据量较小的表，全表扫描可能比使用索引更高效，因为索引的维护也需要成本。

此外，在某些数据库管理系统中，如果查询优化器认为全表扫描比使用索引更高效，它也可能会选择不使用索引。

索引失效是指在某些查询条件下，数据库没有使用已经创建的索引来查找数据，而是进行了全表扫描。这通常会导致查询效率降低，特别是当表中的数据量很大时。

索引失效的常见情况包括：

• OR条件：当查询中使用OR连接条件，尤其是当OR条件中的一个操作数是非索引列时，索引可能会失效。

• 不等式比较：使用不等于（<> 或 !=）操作符进行比较时，索引可能不会生效，因为数据库需要查找所有不满足条件的行。

• 范围查询：如果查询涉及到范围，如BETWEEN或者>、<操作符，且这些范围条件不是索引的最左列，索引可能会部分失效。

• 模糊查询：使用LIKE操作符进行模糊查询时，如果通配符（%）不在字符串的开头，索引可能会失效。

• 函数和运算：在WHERE子句中对索引列使用函数或算术运算，可能会导致索引失效。

• 类型转换：如果查询中的列类型与索引列类型不一致，需要进行隐式类型转换，这可能导致索引失效。

• 多表查询：在多表联接查询中，如果没有正确地使用索引，尤其是在关联条件中使用非索引列时，索引可能会失效。

• 排序和分组：如果ORDER BY或GROUP BY子句中使用的列没有建立索引，或者索引列不在排序或分组的前列，索引可能会失效。

• 不符合最左匹配原则：对于联合索引，如果查询条件没有包含索引的最左列，或者不满足最左前缀原则，索引可能会失效。

• IS NULL或IN条件：在某些情况下，如果查询条件中包含IS NULL或者IN（特别是当IN列表很长时），索引可能不会生效。

综上所述，为了避免索引失效，应当仔细设计查询语句，确保它们能够充分利用索引。同时，定期分析查询执行计划，检查索引是否被正确使用，以及是否需要对索引进行调整或优化。