Share an example of mysql optimization-Mysql Tutorial-php.cn

Share an example of mysql optimization

零下一度

Release： 2017-07-17 10:03:33

Original

1504 people have browsed it

Today, database operations have increasingly become the performance bottleneck of the entire application, especially for Web applications. Regarding the performance of the database, this is not just something that DBAs need to worry about, but this is something that we programmers need to pay attention to. When we design the database table structure and operate the database (especially SQL statements when looking up tables), we need to pay attention to the performance of data operations. Here, we will not talk too much about the optimization of SQL statements, but only focus on MySQL, the database with the most Web applications. I hope the following optimization tips are useful to you.
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

Copy after login

2.Execution statement

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

Copy after login

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

Copy after login

4.Execution time:

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

5. Description, judging from the execution plan, type is index, key and key_len are normal. It seems that the index is removed, but the rows are almost the entire table record (not accurate, it is a full table scan), more than 3 million The data execution time is actually 17 seconds.

Thinking: After changing the nullable of the field to not null, key_len becomes shorter. Is the judgment logic of whether it is empty added to the data?

Articles about null:

Improvements:

1.Add index

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

Copy after login

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

Copy after login

3. Execution Time : 0.178 sec

Transfer Time : 0 sec

Total Time : 0.179 sec

The above is the detailed content of Share an example of mysql optimization. For more information, please follow other related articles on the PHP Chinese website!