当同时使用> =和< =时如何索引MySQL表? [英] How to index a MySQL table when both >= and <= is used?

问题描述

假设您有一个类似的查询

Let's say you have a query like,

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

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

And let's say this table has around 50,000 rows. The REGION column has 500 distinct values and the TYPE column has 50 distinct values. The ID column is the primary key.

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

What would be the best way to index the table? I'm not quite sure if one could achieve a covering index due to the >= and <= signs. Here are a few options:

1. 在COORD_SYSTEM(区域，类型)上创建索引
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)

推荐答案

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

There's no reason that you can't use a covering index with range operators. The challenge (for non-covering indexes) is that the optimizer might think that a full scan might result in fewer page reads if your range is large, and the index won't be used for some of your queries. Similarly, for some parameter values, the optimizer may choose to do a full scan if the covering index isn't much better than a scan for some sets of parameters.

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

So, given the description in the question it's not actually possible to give the optimal solution for all cases.

我倾向于做这样的事情:

What I tend to do with things like that is:

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

• Create a copy of the database
• Guess which index might do the job, and create that index.
• EXPLAIN several queries with different sized ranges (more ranges need more I/O to get back to the table data if you haven't covered the query, so you should try for common range sizes and outliers)
• Drop the index and try another, perhaps with a different covering index with the columns in a different order

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

You might even choose to create two or more covering indexes with the fields in different orders, assuming you run this query considerably more times than corresponding INSERTs or UPDATEs, and that the size of the index isn't a factor for disk space usage.

这篇关于当同时使用&gt; =和&lt; =时如何索引MySQL表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！