MySQL的SQL语句优化一例

413次阅读

没有评论

共计 2588 个字符，预计需要花费 7 分钟才能阅读完成。

今天在系统上看到一条 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)

表一共大约有 250 万行记录，查看下满足into_time<=‘2017-01-31 23:59:59’ 这个条件的有多少行

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)

可以看到 score 列有索引，如果能采用这个列的索引是个比较好的选择，去掉强制索引看下执行计划

1. 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;
2. +––––+–––––––––––––+–––––––––––+––––––––––––+–––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––+–––––––+–––––––––+––––––+––––––+––––––––––+–––––––––––––+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +––––+–––––––––––––+–––––––––––+––––––––––––+–––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––+–––––––+–––––––––+––––––+––––––+––––––––––+–––––––––––––+
5. | 1 | SIMPLE | customers | NULL | index | type,into_time,isarea,status,idx_isarea_renew_owner,type_status | score | 2 | NULL | 270 | 0.92 | Using where |
6. +––––+–––––––––––––+–––––––––––+––––––––––––+–––––––+–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––+–––––––+–––––––––+––––––+––––––+––––––––––+–––––––––––––+
7. 1 row in set, 1 warning (0.00 sec)