Mysql optimization 1 (20170703), mysql optimization 20170703

Mysql optimization 1 (20170703), mysql optimization 20170703

1. Table Structure

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. Execute the statement

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. Execution Plan

    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 duration:

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

5. description: As for the execution plan, the type is index, key, and key_len. It seems to be indexed, but rows is almost full table record (inaccurate, full table scan ), more than 3 million of Data Execution time is 17 seconds.

 

Thinking: after changing the nullable of a field to not null, key_len becomes short. Is it necessary to add the null judgment logic to the data?

Articles about null:

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

 

 

Improvement:

1. Add an index

Alter table 'test'. 'room _ break_history_tmp_test'
-> Add index 'idx _ handle_time '('handle _ type', 'create _ Time ');

2. Execution Plan

    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 duration

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