这篇文章主要讲解了“MySQL SQL优化方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL SQL优化方法是什么”吧！

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秒出结果。

感谢各位的阅读，以上就是“MySQL SQL优化方法是什么”的内容了，经过本文的学习后，相信大家对MySQL SQL优化方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！