mysql版本:
今天早上看到一个sql执行慢，要8秒左右才能出结果，sql如下：
查看此sql的执行计划：
查看表上都有哪些索引：
查看选择性：
看下全表有多少数据:
选择性最好就是INTO_TIME，因为这个列也有索引，强制走这个索引
用上这个索引了，实际执行不到0.3秒出结果，还是可以接受的。
最后给研发提了几个建议：
1.尽量不要用select *
2.这个表200多万数据，可以考虑分表。
点击(此处)折叠或打开
-
mysql> select version();
-
+------------+
-
| version() |
-
+------------+
-
| 5.7.12-log |
-
+------------+
- 1 row in set (0.00 sec)
点击(此处)折叠或打开
-
SELECT *
-
FROM CUSTOMERS
-
WHERE TYPE = 1
-
AND STATUS < 7
-
AND ISAREA = 6
-
AND INTO_TIME >= '2016-11-01'
-
AND INTO_TIME <= '2016-12-01'
- ORDER BY SCORE DESC LIMIT 1140, 20;
点击(此处)折叠或打开
-
mysql> explain SELECT *
-
-> FROM CUSTOMERS
-
-> WHERE TYPE = 1
-
-> AND STATUS < 7
-
-> AND ISAREA = 6
-
-> AND INTO_TIME >= '2016-11-01'
-
-> AND INTO_TIME <= '2016-12-01'
-
-> ORDER BY SCORE DESC LIMIT 1140, 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 | 11314 | 0.25 | Using where |
-
+----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+-------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
查看表上都有哪些索引：
点击(此处)折叠或打开
-
PRIMARY KEY (`id`),
-
KEY `newdata` (`newdata`),
-
KEY `cusname` (`cusname`),
-
KEY `type` (`type`,`ownerid`),
-
KEY `operator` (`operator`),
-
KEY `into_time` (`into_time`),
-
KEY `isarea` (`isarea`),
-
KEY `linkcase` (`linkcase`),
-
KEY `status` (`status`),
-
KEY `operate_id` (`operate_id`),
-
KEY `isparticiple` (`isparticiple`),
-
KEY `idx_level_op` (`level_op`),
-
KEY `idx_status_op` (`status_op`),
-
KEY `renew_ownerid` (`renew_ownerid`),
-
KEY `renew` (`renew`),
-
KEY `idx_ownerid` (`ownerid`),
-
KEY `idx_isarea_renew_owner` (`isarea`,`renew_ownerid`),
-
KEY `idx_create_time` (`create_time`),
-
KEY `idx_source` (`source`),
-
KEY `type_status` (`type`,`status`),
-
KEY `end_month` (`end_month`),
-
KEY `score` (`score`),
-
FULLTEXT KEY `fdx_cusname` (`cusname_idx`)
- ) ENGINE=InnoDB AUTO_INCREMENT=2529287 DEFAULT CHARSET=utf8 |
点击(此处)折叠或打开
-
mysql> SELECT COUNT(*) FROM CUSTOMERS where TYPE = 1;
-
+----------+
-
| COUNT(*) |
-
+----------+
-
| 2347457 |
-
+----------+
-
1 row in set (4.66 sec)
-
-
mysql> select count(*) from customers where STATUS < 7;
-
+----------+
-
| count(*) |
-
+----------+
-
| 2468461 |
-
+----------+
-
1 row in set (3.51 sec)
-
-
mysql> select count(*) from customers where ISAREA = 6;
-
+----------+
-
| count(*) |
-
+----------+
-
| 134726 |
-
+----------+
-
1 row in set (0.17 sec)
-
-
mysql> SELECT COUNT(*) FROM CUSTOMERS WHERE INTO_TIME >= '2016-11-01' AND INTO_TIME <= '2016-12-01';
-
+----------+
-
| COUNT(*) |
-
+----------+
-
| 110859 |
-
+----------+
- 1 row in set (0.26 sec)
看下全表有多少数据:
点击(此处)折叠或打开
-
mysql> select count(*) from customers;
-
+----------+
-
| count(*) |
-
+----------+
-
| 2481386 |
-
+----------+
- 1 row in set (2.01 sec)
选择性最好就是INTO_TIME，因为这个列也有索引，强制走这个索引
点击(此处)折叠或打开
-
mysql> explain SELECT *
-
-> FROM CUSTOMERS USE KEY(into_time)
-
-> WHERE TYPE = 1
-
-> AND STATUS < 7
-
-> AND ISAREA = 6
-
-> AND INTO_TIME >= '2016-11-01'
-
-> AND INTO_TIME <= '2016-12-01'
-
-> ORDER BY SCORE DESC LIMIT 1140, 20;
-
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
-
| 1 | SIMPLE | CUSTOMERS | NULL | range | into_time | into_time | 6 | NULL | 244448 | 0.05 | Using index condition; Using where; Using filesort |
-
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
- 1 row in set, 1 warning (0.00 sec)
最后给研发提了几个建议：
1.尽量不要用select *
2.这个表200多万数据，可以考虑分表。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20893244/viewspace-2131988/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Linux里面去掉文件特殊字符的命令
- 在线将传统模式复制改为GTID复制模式
- 如何确定Single-Primary模式下的MGR主节点(文档 ID 2214438.1)
- centos 7 二进制安装mysql 5.7.25
- Mysql5.7半同步复制
- MySQL8.0变化之密码策略
- MySQL8.0的坑之lower_case_table_names
- MySQL8.0安装
- ORA-00000 normal, successful completion
- ORA-12899: value too large for column ORG_NAME (actual: 145, maximum: 128)
