今天在系统上看到一条sql运行时间达到9秒，不符合规范要求，优化之~~
sql如下：sql强制用了into_time索引
-
# Time: 2017-02-14T11:35:01.594499+08:00
-
# User@Host: oms_readonly[oms_readonly] @ [10.44.xxx.xxx] Id: 41636892
-
# Query_time: 9.299612 Lock_time: 0.000124 Rows_sent: 20 Rows_examined: 2439330
-
SET timestamp=1487043301;
- select * from customers force index(`into_time`) where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_time`<='2017-01-31 23:59:59' order by score desc limit 0,20;
-
mysql> show table status like 'customers' \G;
-
*************************** 1. row ***************************
-
Name: customers
-
Engine: InnoDB
-
Version: 10
-
Row_format: Dynamic
-
Rows: 2504609
-
Avg_row_length: 710
-
Data_length: 1780383744
-
Max_data_length: 0
-
Index_length: 1253048320
-
Data_free: 6291456
-
Auto_increment: 2546101
-
Create_time: 2017-01-07 01:59:34
-
Update_time: 2017-02-14 13:58:17
-
Check_time: NULL
-
Collation: utf8_general_ci
-
Checksum: NULL
-
Create_options:
-
Comment:
- 1 row in set (0.00 sec)
-
mysql> select count(*) from customers where `into_time`<='2017-01-31 23:59:59';
-
+----------+
-
| count(*) |
-
+----------+
-
| 2439147 |
-
+----------+
- 1 row in set (0.95 sec)
显然into_time这个列的索引已经不合适了，查看下表上都有那些索引
-
Create Table: CREATE TABLE `customers` (
-
`id` int(11) NOT NULL AUTO_INCREMENT,
-
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 `score` (`score`),
-
FULLTEXT KEY `fdx_cusname` (`cusname_idx`)
-
) ENGINE=InnoDB AUTO_INCREMENT=2546101 DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
-
-
mysql> explain select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_time`<='2017-01-31 23:59:59' order by score desc limit 0,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 | 270 | 0.92 | Using where |
-
+----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
-
mysql> explain select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_time`<='2017-01-31 23:59:59' order by score desc limit 0,20;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20893244/viewspace-2133512/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 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)
