slowlog里查到一条sql执行了10秒钟以上

#Time:2017-03-02T18:41:50.201376+08:00

#User@Host:oms_readonly[oms_readonly]@[10.xx.xx.xx]Id: 54124620

# Query_time: 11.005894 Lock_time: 0.000223 Rows_sent: 20 Rows_examined: 1838114

SETtimestamp=1488451310;

select*fromcustomerswhere`type`=1AND`status`<7AND`isarea`=6AND`into_time`>='2016-12-01'AND`into_time`<='2017-01-02 23:59:59'order by score desc limit 40,20;

查看执行计划

mysql>explainselect*fromcustomerswhere`type`=1AND`status`<7AND`isarea`=6AND`into_time`>='2016-12-01'AND`into_time`<='2017-01-02 23:59:59'order by score desc limit 40,20;

+----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

+----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+

|1|SIMPLE|customers|NULL|index|type,into_time,isarea,status,idx_isarea_renew_owner,type_status|score|2|NULL|603|0.21|Usingwhere|

+----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+

1 rowinset,1 warning(0.00 sec)

看下列的选择性，很明显isarea列的选择性更好

mysql>selectcount(*)fromcustomerswhere`type`=1;

+----------+

|count(*)|

+----------+

|2373175|

+----------+

1 rowinset(1.53 sec)

mysql>selectcount(*)fromcustomerswhere`status`<7;

->;

+----------+

|count(*)|

+----------+

|2494232|

+----------+

1 rowinset(3.16 sec)

mysql>selectcount(*)fromcustomerswhere`isarea`=6;

+----------+

|count(*)|

+----------+

|135347|

+----------+

1 rowinset(0.15 sec)

mysql>selectcount(*)fromcustomerswhere`into_time`>='2016-12-01'AND`into_time`<='2017-01-02 23:59:59';

+----------+

|count(*)|

+----------+

|106158|

+----------+

1 rowinset(0.12 sec)

isarea有索引，强制用这个列的索引

mysql>explainselect*fromcustomers use index(isarea)where`type`=1AND`status`<7AND`isarea`=6AND`into_time`>='2016-12-01'AND`into_time`<='2017-01-02 23:59:59'order by score desc limit 40,20;

+----+-------------+-----------+------------+------+---------------+--------+---------+-------+--------+----------+----------------------------------------------------+

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

+----+-------------+-----------+------------+------+---------------+--------+---------+-------+--------+----------+----------------------------------------------------+

|1|SIMPLE|customers|NULL|ref|isarea|isarea|5|const|249438|1.85|Using index condition;Usingwhere;Using filesort|

+----+-------------+-----------+------------+------+---------------+--------+---------+-------+--------+----------+----------------------------------------------------+

1 rowinset,1 warning(0.00 sec)

优化前10几秒出结果，优化后0.5秒出结果。