MySQL的SQL语句优化一例
今天在系统上看到一条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列有索引,如果能采用这个列的索引是个比较好的选择,去掉强制索引看下执行计划
-
- 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)
可以看到用了score索引,执行时间从最9秒多,到优化后的0.0几秒。
来源: http://www.linuxidc.com/Linux/2017-02/140652.htm