分享一篇mysql優(yōu)化的案例

1. 今天，數(shù)據(jù)庫的操作越來越成為整個應(yīng)用的性能瓶頸了，這點對于Web應(yīng)用尤其明顯。關(guān)于數(shù)據(jù)庫的性能，這并不只是DBA才需要擔心的事，而這更是我們程序員需要去關(guān)注的事情。當我們?nèi)ピO(shè)計數(shù)據(jù)庫表結(jié)構(gòu)，對操作數(shù)據(jù)庫時（尤其是查表時的SQL語句），我們都需要注意數(shù)據(jù)操作的性能。這里，我們不會講過多的SQL語句的優(yōu)化，而只是針對MySQL這一Web應(yīng)用最多的數(shù)據(jù)庫。希望下面的這些優(yōu)化技巧對你有用。
   

2. 1.表結(jié)構(gòu)

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.執(zhí)行語句

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.執(zhí)行計劃

    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.執(zhí)行時長：

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

5.描述，就執(zhí)行計劃看，type為index，key及key_len正常，看似是走了索引，但是rows幾乎是全表記錄（不準確，就是全表掃描），300多萬的數(shù)據(jù)執(zhí)行時長居然17秒。

思考：將字段的nullable改為not null后，key_len變短了，是不是將是否為空的判斷邏輯添加到了數(shù)據(jù)上？

有關(guān)null的文章：

改進：

1.添加索引

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

2.執(zhí)行計劃

    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.執(zhí)行時長

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

以上就是分享一篇mysql優(yōu)化的實例的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！