959   MySQL

一,遇到情况:

有些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;



Leave a Reply

Your email address will not be published. Required fields are marked *