mysql优化一则(20170703)

2018-06-17 23:53:29来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

1.表结构

CREATE TABLE `room_break_history_tmp_test ` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`break_type` INT(11) DEFAULT NULL,
`app_id` INT(11) DEFAULT NULL,
`room_id` INT(11) DEFAULT NULL,
`from_user_id` INT(11) DEFAULT NULL,
`to_user_id` INT(11) DEFAULT NULL,
`content_type` INT(11) DEFAULT NULL,
`content_name` VARCHAR(300) DEFAULT NULL,
`source_message` VARCHAR(1536) DEFAULT NULL,
`send_message` VARCHAR(1536) DEFAULT NULL,
`request_type` INT(4) DEFAULT NULL,
`report_relation` VARCHAR(1536) DEFAULT NULL,
`handle_type` INT(11) DEFAULT NULL,
`handle_uid` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_from_user_id` (`room_id`,`from_user_id`,`handle_type`,`create_time`)
) ENGINE=INNODB AUTO_INCREMENT=3416971 DEFAULT CHARSET=utf8mb4

2.执行语句

DESC SELECT
COUNT(1)
FROM
(SELECT
COUNT(1)
FROM
room_break_history_tmp_test
WHERE `create_time` BETWEEN '2017-07-01 22:25:33'
AND '2017-07-01 22:27:00'
AND handle_type = 5
GROUP BY room_id,
from_user_id) AS keywordtemp

3.执行计划

    id  select_type  table               type    possible_keys     key               key_len  ref        rows  Extra                     
------  -----------  ------------------  ------  ----------------  ----------------  -------  ------  -------  --------------------------
     1  PRIMARY      <derived2>          ALL     (NULL)            (NULL)            (NULL)   (NULL)  3438331  (NULL)                    
     2  DERIVED      room_break_history  index   idx_from_user_id  idx_from_user_id  21       (NULL)  3438331  Using where; Using index  

4.执行时长:

Execution Time : 17.182 sec
Transfer Time : 0.001 sec
Total Time : 17.184 sec

5.描述,就执行计划看,type为index,key及key_len正常,看似是走了索引,但是rows几乎是全表记录(不准确,就是全表扫描),300多万的数据执行时长居然17秒。

 

思考:将字段的nullable改为not null后,key_len变短了,是不是将是否为空的判断逻辑添加到了数据上?

有关null的文章:

https://dev.mysql.com/doc/refman/5.6/en/problems-with-null.html

 

 

改进:

1.添加索引

ALTER TABLE `test`.`room_break_history_tmp_test`
-> ADD INDEX `idx_handle_time` (`handle_type`, `create_time`);

2.执行计划

    id  select_type  table                        type    possible_keys                     key              key_len  ref       rows  Extra                                                   
------  -----------  ---------------------------  ------  --------------------------------  ---------------  -------  ------  ------  --------------------------------------------------------
     1  PRIMARY      <derived2>                   ALL     (NULL)                            (NULL)           (NULL)   (NULL)       2  (NULL)                                                  
     2  DERIVED      room_break_history_tmp_test  range   idx_from_user_id,idx_handle_time  idx_handle_time  7        (NULL)       1  Using index condition; Using temporary; Using filesort  

3.执行时长

Execution Time : 0.178 sec
Transfer Time : 0 sec
Total Time : 0.179 sec

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL--当mysqldump --single-transaction遇到alter table(2)

下一篇:MySQL函数—字符串函数