How to use composite index in range query in mysql I have a table：

create table test (
   field1 bigint,
   field2 bigint
);

alter table test 
add index field1_field2 (field1, field2)


Here is a sql : 

select * 
from test 
    force index(field1_field2)
where field1 &gt; 100 
  and field2 = 2 
limit 200 ;


and it doesn't run very well. I Explained it and found that only field1 condition used by index query, field2 condition was ignored.

I want know why, and I need a perfect answer.

There are lots of data with field1 &gt; 100 and lots of data with field2 = 2.
You need a composite index on the columns (they are not fields!), but in reverse order, on (field2, field1) for this query. 

When a condition has equality checks (=) and range/inequality checks (&gt;, &gt;=, &lt;, &lt;=, IN), then the columns involved in the equality checks should be first in the index and the inequality columns afterwards.

alter table test 
    add index ix_field2_field1 
    (field2, field1) ;


Additional notes:


A query with LIMIT and without ORDER BY will yield indeterminate results. Always use ORDER BY with LIMIT unless you don't care if you get different results per execution:

select * 
from test  
                          -- no need to force anything
where field2 = 2          -- the order here doesn't matter
  and field1 &gt; 100        -- but is useful for developers
order by field2, field1
limit 200 ;

Really, choose some proper names for the columns, not "field1", "field2", etc.
Your table does not have a PRIMARY KEY defined. This is not good both from a theoretical and a practical point of view. Assuming that the table uses the InnoDB engine, then by not defining a primary key, InnoDB adds a  6-byte hidden column that is used as the clustered index. This also means that all your indexes have this 6-byte column attached, making them wider. I suggest you have a - preferably narrow - PRIMARY KEY in all tables.