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

Let's say you have a query like,

select ID, REGION, START, END from COORD_SYSTEM

where REGION=? and TYPE=? and START >= ? and END <= ?;

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:

create index on COORD_SYSTEM (REGION, TYPE)

create index on COORD_SYSTEM (REGION, TYPE, START)

create index on COORD_SYSTEM (REGION, TYPE, START, END)

Update - Here's the explain statement:

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:

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

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.