mysql - 当同时使用 >= 和 <= 时如何索引 MySQL 表？

Question

假设您有一个查询，例如，

select ID, REGION, START, END from COORD_SYSTEM 
where REGION=? and TYPE=? and START >= ? and END <= ?;

假设该表大约有 50,000 行。 REGION 列有 500 个不同的值，TYPE 列有 50 个不同的值。 ID 列是主键。

索引表的最佳方法是什么？由于 >= 和 <= 符号，我不太确定是否可以实现覆盖索引。以下是一些选项:

1. 在 COORD_SYSTEM(REGION、TYPE)上创建索引
2. 在 COORD_SYSTEM(REGION、TYPE、START)上创建索引
3. 在 COORD_SYSTEM(REGION、TYPE、START、END)上创建索引

更新 - 这是解释语句:

          id: 1
  select_type: SIMPLE
        table: COORD_SYSTEM
         type: range
possible_keys: indx_A
          key: indx_A
      key_len: 50
          ref: NULL
         rows: 590
        Extra: Using where
1 row in set (0.00 sec)

Answer 1

没有理由不能将覆盖索引与范围运算符一起使用。挑战(对于非覆盖索引)是，优化器可能会认为，如果范围很大，则完整扫描可能会导致更少的页面读取，并且索引不会用于某些查询。同样，对于某些参数值，如果覆盖索引并不比某些参数集的扫描好多少，优化器可能会选择进行完整扫描。

因此，根据问题中的描述，实际上不可能为所有情况提供最佳解决方案。

我倾向于做这样的事情:

• 创建数据库的副本
• 猜测哪个索引可以完成这项工作，并创建该索引。
• EXPLAIN 多个具有不同大小范围的查询(如果您还没有涵盖查询，更多范围需要更多 I/O 才能返回表数据，因此您应该尝试常见的范围大小和异常值)
• 删除索引并尝试另一个，也许使用不同的覆盖索引和不同的列顺序

假设您运行此查询的次数比相应的 INSERT 或 UPDATE 多得多，您甚至可以选择创建两个或多个字段顺序不同的覆盖索引，并且索引的大小不是磁盘空间使用的一个因素。