哪些字段适合加索引，哪些不适合

  1. 经常作为查询条件（WHERE子句）的字段：

    • 这是最基本也是最重要的原则。为WHERE子句中频繁使用的列创建索引，可以极大地提高查询速度，避免全表扫描。

  2. 经常作为连接条件（JOIN ON子句）的字段：

    • 为连接操作中的关联字段（通常是外键）创建索引，可以显著加快JOIN操作的效率。

  3. 经常作为排序依据（ORDER BY子句）的字段：

    • 为ORDER BY子句中的列创建索引，可以利用索引的有序性来避免昂贵的文件排序（filesort）操作。如果排序方向与索引顺序一致，数据库可以直接按索引顺序读取数据。

  4. 经常作为分组依据（GROUP BY子句）的字段：

    • 为GROUP BY子句中的列创建索引，可以帮助数据库更快地找到相同的分组值，有时也可以利用索引来避免创建临时表进行分组。

  5. 高选择性（高基数，High Cardinality）的字段：

    • 选择性是指列中不同值的数量与总行数的比率。比率越高，选择性越好。例如，存储用户ID、手机号、身份证号的列，其值几乎都是唯一的，选择性非常高，非常适合做索引。
    • 高选择性的索引可以快速地将搜索范围缩小到很少的几行。

  6. 覆盖索引（Covering Index）中包含的字段：

    • 如果一个查询所需要的所有列（SELECT列表和WHERE子句中的列）都包含在一个索引中，那么数据库可以直接从该索引中获取所有数据，而无需回表查询主数据表。这种索引称为覆盖索引。
    • 在设计索引时，可以考虑将一些经常与查询条件一起被查询出来的、长度不大的列加入到联合索引中，以创建覆盖索引。

  7. 字符串前缀索引：

    • 对于很长的字符串列（如VARCHAR或TEXT），为整个列创建索引可能会占用大量磁盘空间并且效率不高。可以只对字符串的前缀部分创建索引。
    • 选择前缀的长度需要权衡索引的选择性和大小。可以通过计算不同前缀长度的选择性来决定一个合适的值。

  8. 联合索引（Composite Index）：

    • 当查询条件经常涉及多个列时，创建一个包含这些列的联合索引通常比为每个列创建单列索引更有效。
    • 联合索引的列顺序非常重要，应遵循最左前缀原则（Leftmost Prefix Principle）。将选择性最高、最常用于等值查询的列放在最前面。

哪些字段不适合加索引？

  1. 选择性非常低（低基数，Low Cardinality）的字段：

    • 例如，存储“性别”（男/女）、“状态”（有效/无效）、“布尔值”（true/false）的列。
    • 为这类列创建索引，其效果可能和全表扫描差不多，因为索引无法有效地过滤数据。数据库优化器甚至可能会放弃使用这类索引。
    • 同时，索引还会带来额外的存储和维护开销。

  2. 很少在查询条件中使用的字段：

    • 如果一个字段几乎不出现在WHERE, JOIN, ORDER BY, GROUP BY子句中，为它创建索引就是一种资源浪费。

  3. 被频繁更新的字段：

    • 每次更新索引列的值，都需要对索引结构进行相应的维护（删除旧的索引条目，插入新的条目），这会增加写操作的开销。
    • 如果一个字段被非常频繁地更新，那么索引带来的查询性能提升可能不足以抵消其维护成本。

  4. 短小的表：

    • 如果一个表的数据量非常小（例如，只有几百或几千行），全表扫描的速度可能比通过索引查找更快，因为索引查找本身也有开销。为小表创建索引通常没有必要。

  5. 过长的文本字段（如TEXT或BLOB类型）：

    • 直接为这些长字段创建索引是不允许的或者效率极低。如果需要对这类字段进行搜索，应考虑使用前缀索引或者更专业的全文索引（Full-Text Index）。

  6. 参与计算或函数操作的列：

    • 如果WHERE子句中的条件是对列进行了函数运算或表达式计算，例如 WHERE YEAR(create_time) = 2023 或 WHERE amount * 1.1 > 100，那么create_time或amount列上的普通索引通常不会被使用。
    • 需要改写SQL，使列本身保持“干净”，例如 WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。

创建索引是一种在空间和写性能与查询性能之间的权衡。 * 适合加索引的字段通常是：频繁用于查询、连接、排序、分组的，并且具有高选择性的列。 * 不适合加索引的字段通常是：选择性极低、不常用于查询、被频繁更新的，或者是数据量很小的表中的列。