分享一篇mysql優化的實例

  1. 今天,數據庫的操作越來越成為整個應用的性能瓶頸了,這點對于web應用尤其明顯。關于數據庫的性能,這并不只是dba才需要擔心的事,而這更是我們程序員需要去關注的事情。當我們去設計數據庫表結構,對操作數據庫時(尤其是查表時的sql語句),我們都需要注意數據操作的性能。這里,我們不會講過多的sql語句的優化,而只是針對mysql這一web應用最多的數據庫。希望下面的這些優化技巧對你有用。

  2. 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</derived2>

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的文章:

改進:

1.添加索引

ALTER?TABLE?`test`.`room_break_history_tmp_test`?????-&gt;???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</derived2>

3.執行時長

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

?

? 版權聲明
THE END
喜歡就支持一下吧
點贊13 分享