一个数据库表t中包含有3个int字段A,B,C，请问怎样建索引最合适

为了优化这两个最频繁查询的语句，我们需要考虑索引的以下几个原则：

  1. 复合索引的列顺序： 对于复合索引，列的顺序非常重要。通常将用于等值查询（=）的列放在前面，然后是范围查询（>, <, BETWEEN, LIKE 'prefix%'）的列，最后是仅用于查询结果（SELECT子句中，但不在WHERE或ORDER BY中）的列，以实现覆盖索引。

  2. 覆盖索引： 如果索引包含了查询所需的所有列（无论是WHERE子句中的条件列还是SELECT子句中的结果列），那么数据库可以直接从索引中获取所有数据，而无需回表查询，从而大大提高性能。

现在我们来分析两条查询语句：

查询语句1: select A,B,C from t where B=100 and C > 200;

  • WHERE条件：B=100 (等值查询), C > 200 (范围查询)。

  • SELECT列：A, B, C。

  • 对于此查询，一个理想的索引应该是 (B, C, A)。这样：

    • 索引可以首先利用 B=100 进行快速定位。

    • 接着可以对 C > 200 进行范围扫描。

    • 由于 A, B, C 都包含在索引中，这会是一个覆盖索引，避免了回表操作。

查询语句2: select A from t where A = 20 and B = 10 and C = 80;

  • WHERE条件：A=20 (等值查询), B=10 (等值查询), C=80 (等值查询)。

  • SELECT列：A。

  • 对于此查询，一个理想的索引应该是 (A, B, C)（或者任意的排列，如 (B, C, A) 等，因为都是等值查询，只要所有条件列都在索引中，性能都很好）。同时，A在索引中，也是一个覆盖索引。

综合考虑两条查询：

我们需要找到一个能同时优化这两条查询的最优索引方案。

  • 如果创建索引 (A, B, C)：

    • 对于查询2 (where A = 20 and B = 10 and C = 80)：完美命中，所有条件都在索引中，且顺序匹配，是覆盖索引。

    • 对于查询1 (where B=100 and C > 200)：索引的第一个字段 A 没有出现在 WHERE 条件中。数据库将无法有效利用此索引来快速定位 B=100 的记录，很可能会导致全表扫描或全索引扫描，性能较差。

  • 如果创建索引 (B, C, A)：

    • 对于查询1 (where B=100 and C > 200)：索引的第一个字段 B 是等值条件，第二个字段 C 是范围条件，第三个字段 A 是覆盖字段。完美命中，高效利用索引，是覆盖索引。

    • 对于查询2 (where A = 20 and B = 10 and C = 80)：索引的第一个字段 B 可以用于 B=10 的等值匹配。定位到 B=10 的记录后，数据库可以在索引内部对 C=80 和 A=20 进行过滤。由于 A, B, C 都包含在索引中，这也是一个覆盖索引，避免了回表操作。

结论：

创建一个单一的复合索引 (B, C, A) 是最合适的方案。

  1. 它能够充分优化查询1，因为索引顺序与查询条件（等值在前，范围在后）完美匹配，并且是一个覆盖索引。

  2. 它也能非常有效地优化查询2。虽然 A 不是索引的第一个字段，但由于 B 在索引头部且是等值条件，索引可以首先通过 B 进行过滤，然后在索引内部继续过滤 C 和 A。同时，它也是一个覆盖索引，查询所需的所有数据都可以在索引中找到，无需回表。

因此，建议的索引创建语句为：

CREATE INDEX idx_b_c_a ON t (B, C, A);