sql优化（mysql）

mysql版本:

点击(此处)折叠或打开

1. mysql> select version();
   
2. +------------+
   
3. | version() |
   
4. +------------+
   
5. | 5.7.12-log |
   
6. +------------+
   
7. 1 row in set (0.00 sec)

今天早上看到一个sql执行慢，要8秒左右才能出结果，sql如下：

点击(此处)折叠或打开

1. SELECT *
   
2. FROM CUSTOMERS
   
3. WHERE TYPE = 1
   
4. AND STATUS < 7
   
5. AND ISAREA = 6
   
6. AND INTO_TIME >= '2016-11-01'
   
7. AND INTO_TIME <= '2016-12-01'
   
8. ORDER BY SCORE DESC LIMIT 1140, 20;

查看此sql的执行计划：

点击(此处)折叠或打开

1. mysql> explain SELECT *
   
2.     -> FROM CUSTOMERS
   
3.     -> WHERE TYPE = 1
   
4.     -> AND STATUS < 7
   
5.     -> AND ISAREA = 6
   
6.     -> AND INTO_TIME >= '2016-11-01'
   
7.     -> AND INTO_TIME <= '2016-12-01'
   
8.     -> ORDER BY SCORE DESC LIMIT 1140, 20;
   
9. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+-------+----------+-------------+
   
10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    
11. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+-------+----------+-------------+
    
12. | 1 | SIMPLE | CUSTOMERS | NULL | index | type,into_time,isarea,status,idx_isarea_renew_owner,type_status | score | 2 | NULL | 11314 | 0.25 | Using where |
    
13. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+-------+----------+-------------+
    
14. 1 row in set, 1 warning (0.00 sec)

查看表上都有哪些索引：

点击(此处)折叠或打开

1. PRIMARY KEY (`id`),
   
2.   KEY `newdata` (`newdata`),
   
3.   KEY `cusname` (`cusname`),
   
4.   KEY `type` (`type`,`ownerid`),
   
5.   KEY `operator` (`operator`),
   
6.   KEY `into_time` (`into_time`),
   
7.   KEY `isarea` (`isarea`),
   
8.   KEY `linkcase` (`linkcase`),
   
9.   KEY `status` (`status`),
   
10.   KEY `operate_id` (`operate_id`),
    
11.   KEY `isparticiple` (`isparticiple`),
    
12.   KEY `idx_level_op` (`level_op`),
    
13.   KEY `idx_status_op` (`status_op`),
    
14.   KEY `renew_ownerid` (`renew_ownerid`),
    
15.   KEY `renew` (`renew`),
    
16.   KEY `idx_ownerid` (`ownerid`),
    
17.   KEY `idx_isarea_renew_owner` (`isarea`,`renew_ownerid`),
    
18.   KEY `idx_create_time` (`create_time`),
    
19.   KEY `idx_source` (`source`),
    
20.   KEY `type_status` (`type`,`status`),
    
21.   KEY `end_month` (`end_month`),
    
22.   KEY `score` (`score`),
    
23.   FULLTEXT KEY `fdx_cusname` (`cusname_idx`)
    
24. ) ENGINE=InnoDB AUTO_INCREMENT=2529287 DEFAULT CHARSET=utf8 |

查看选择性：

点击(此处)折叠或打开

1. mysql> SELECT COUNT(*) FROM CUSTOMERS where TYPE = 1;
   
2. +----------+
   
3. | COUNT(*) |
   
4. +----------+
   
5. | 2347457 |
   
6. +----------+
   
7. 1 row in set (4.66 sec)
   
8. 
   
9. mysql> select count(*) from customers where STATUS < 7;
   
10. +----------+
    
11. | count(*) |
    
12. +----------+
    
13. | 2468461 |
    
14. +----------+
    
15. 1 row in set (3.51 sec)
    
16. 
    
17. mysql> select count(*) from customers where ISAREA = 6;
    
18. +----------+
    
19. | count(*) |
    
20. +----------+
    
21. | 134726 |
    
22. +----------+
    
23. 1 row in set (0.17 sec)
    
24. 
    
25. mysql> SELECT COUNT(*) FROM CUSTOMERS WHERE INTO_TIME >= '2016-11-01' AND INTO_TIME <= '2016-12-01';
    
26. +----------+
    
27. | COUNT(*) |
    
28. +----------+
    
29. | 110859 |
    
30. +----------+
    
31. 1 row in set (0.26 sec)

看下全表有多少数据:

点击(此处)折叠或打开

1. mysql> select count(*) from customers;
   
2. +----------+
   
3. | count(*) |
   
4. +----------+
   
5. | 2481386 |
   
6. +----------+
   
7. 1 row in set (2.01 sec)

选择性最好就是 INTO_TIME，因为这个列也有索引，强制走这个索引

点击(此处)折叠或打开

1. mysql> explain SELECT *
   
2.     -> FROM CUSTOMERS USE KEY(into_time)
   
3.     -> WHERE TYPE = 1
   
4.     -> AND STATUS < 7
   
5.     -> AND ISAREA = 6
   
6.     -> AND INTO_TIME >= '2016-11-01'
   
7.     -> AND INTO_TIME <= '2016-12-01'
   
8.     -> ORDER BY SCORE DESC LIMIT 1140, 20;
   
9. +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
   
10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    
11. +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
    
12. | 1 | SIMPLE | CUSTOMERS | NULL | range | into_time | into_time | 6 | NULL | 244448 | 0.05 | Using index condition; Using where; Using filesort |
    
13. +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
    
14. 1 row in set, 1 warning (0.00 sec)

用上这个索引了，实际执行不到0.3秒出结果，还是可以接受的。

最后给研发提了几个建议：

1.尽量不要用select *

2.这个表200多万数据，可以考虑分表。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20893244/viewspace-2131988/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20893244/viewspace-2131988/