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
-
SET timestamp=1488451310;
- select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 6 AND `into_time`>='2016-12-01'AND `into_time`<='2017-01-02 23:59:59' order by score desc limit 40,20;
-
mysql> explain select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 6 AND `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 | Using where |
-
+----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
-
mysql> select count(*) from customers where `type` = 1;
-
+----------+
-
| count(*) |
-
+----------+
-
| 2373175 |
-
+----------+
-
1 row in set (1.53 sec)
-
- mysql> select count(*) from customers where `status` < 7;
-
-> ;
-
+----------+
-
| count(*) |
-
+----------+
-
| 2494232 |
-
+----------+
-
1 row in set (3.16 sec)
-
- mysql> select count(*) from customers where `isarea` = 6;
-
+----------+
-
| count(*) |
-
+----------+
-
| 135347 |
-
+----------+
-
1 row in set (0.15 sec)
-
- mysql> select count(*) from customers where `into_time`>='2016-12-01'AND `into_time`<='2017-01-02 23:59:59';
-
+----------+
-
| count(*) |
-
+----------+
-
| 106158 |
-
+----------+
- 1 row in set (0.12 sec)
-
mysql> explain select * from customers use index(isarea) where `type` = 1 AND `status` < 7 AND `isarea` = 6 AND `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; Using where; Using filesort |
-
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+--------+----------+----------------------------------------------------+
- 1 row in set, 1 warning (0.00 sec)
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
