一，遇到情况：

有些MySQL语句类似

mysql> select * from test where sex='M' and city='Guangzhou' order by create_at asc limit 100;

如果不使用索引的话，会非常慢；
如果使用索引，那建立怎样的索引呢？

二，解决方法：

当然是组合索引啦，因为这里有where条件和orderby排序；组合索引是采用左链接方式，所以创建索引的SQL如下

mysql> alter table test add index sex_city_create_at (sex,city,create_at);

三，检验方法：

检查下是否使用索引，直接explain一下

mysql> explain select * from test where sex='M' and city='Guangzhou' order by create_at asc limit 100;
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+---------+--------------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref         | rows    | Extra                    |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+---------+--------------------------+
|  1 | SIMPLE      | test  | ref  | sex_city_create_at | sex_city_create_at | 16      | const,const | 1144988 | Using where; Using index |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

如果还不行的话，强制使用索引

mysql> explain select * from test FORCE index(sex_city_create_at) where sex='M' and city='Guangzhou' order by create_at asc limit 100;