ä¸ï¼åºç¡æå¡åå²ä»ç»
æå± æ¥å£All in oneåå¨çä¸äº(ç¹ç¹)é®é¢ï¼ä»¥åéçä¸å¡çæ¥çå¢é¿ï¼çåäºä¸äºé®é¢ã(æ¤æ¶ææè åæ³ä¹å¸¦çä¸å¡æ¸¡è¿äºä¸å®çå¨æï¼ä¸åé®é¢ççåé½æ¯éçéç积累åºç°çï¼å¦ï¼ç¨æ·éï¼æ°æ®éï¼å¹¶åéï¼æè å¼å人åçæ°é)
1ï¼ æå± å ä¹ææçä¸å¡æ¥å£é½å¨ä¸ä¸ªåºç¨ç¨åºéã(2020-04ä¹å)
â ï¼å¼åãæµè¯ãé¨ç½²å°é¾ãå³ä½¿åªæ¹å¨ä¸ä¸ªå°åè½ï¼ä¹éè¦æ´ä¸ªåºç¨ä¸èµ·åå¸ãææ¶å人为ç忽ä¸å°å¿å¸¦ä¸äºä¸äºæªç»æµè¯ç代ç ï¼æè ä¿®æ¹ äºä¸ä¸ªåè½åï¼å¦ä¸ä¸ªææ³ä¸å°çå°æ¹åºéäºã为äºåè½»åå¸å¯è½äº§ççé®é¢çå½±åå线ä¸ä¸å¡åé¡¿çå½±åï¼ææåºç¨é½è¦å¨åæ¨ä¸ç¹ç¹æ§è¡åå¸ãåå¸å为äºéªè¯åºç¨æ£å¸¸è¿è¡ï¼è¿å¾ç¯å°ç¬¬äºå¤©ç½å¤©çç¨æ·é«å³°æã
â¡ï¼jvm oomã线ç¨å 积ãæ ¢sqlãcpu 100%ãç£çãå åãcpuãç½ç» ä¸ä¸ªæ é导è´ï¼æ¬æºå¨ï¼æ´ä¸ªä¸å¡ä¸å¯ç¨ã
â¢ï¼ä¸å¡æ²¡æ主次ä¹åï¼ä¸ä¸ªè¾¹ç¼ä¸å¡å¼å¸¸å¯è½å¯¼è´æ´ä¸ªä¸å¡æµç¨ä¸å¯ç¨ãå¯æ©å±æ§æ¥ï¼ç¨ææ¹å¨å°±ä¼çµè¿å¾å¤ä¸å¡ä»£ç ã
â£ï¼æ交代ç é¢ç¹åºç°å¤§éå²çª ï¼ åå 个人å¼åä¸ä¸ªæ¨¡åï¼ä½¿ç¨gitå代ç 管çï¼åç»å¸¸ä¼éå°çäºæ å°±æ¯ä»£ç æ交å²çªã è¦çï¼äººè¶å¤ å²çªæ¦çè¶å¤§ ï¼
â¤ï¼å个åºç¨ä¸ºäºç»å ¶ä»åºç¨æä¾æ¥å£ï¼æ¸æ¸å°è¶æ¹è¶å¤§ï¼å å«äºå¾å¤æ¬æ¥å°±ä¸å±äºå®çé»è¾ãåºç¨è¾¹ç模ç³ï¼åè½å½å±æ··ä¹±
â¥ï¼å¼å人å太å¤ï¼ä»£ç è´¨éé£æ ¼ååä¸åï¼æ常è§çæ¯ä¸è¡ä»£ç æ§è½å¯¼è´æ´ä¸ªæå¡ä¸å¯ç¨ã
â¦ï¼åºç¨é´è¦å严éï¼ä¸å¡æ©å±æ§å·®ãåä¸åè½å¨å个åºç¨ä¸é½æå®ç°ï¼æ¹ä¸å¤åè½ï¼å ¶ä»ç³»ç»é½å¾ä¸èµ·æ¹å¨ã
æ äºä¸ï¼è°ç¨å¤§æ°æ®æ¥å£å ä¸ºè¶ æ¶æ¶é´è®¾ç½®é®é¢å¤ªé¿ï¼å¯¼è´çº¿ç¨å¤§éå 积ï¼ä¸ä¸ªæ¥å£å½±åäºæ´ä¸ªæå± æ ¸å¿æå¡ã
æ äºäºï¼å 为ä¸å¡é»è¾çé®é¢ä»æ°æ®åºæ¥è¯¢åºäº600å¤ä¸æ¡æ°æ®ï¼å¯¼è´ç³»ç»OOMï¼ä¸ä¸ªè¾¹ç¼å½±åäºæ´ä¸ªæå± æ ¸å¿æå¡
äºæ ä¸ï¼æµéå²å»å¯¼è´æå¡é«è´è½½ï¼å½±åäºæ´ä¸ªæå± æ ¸å¿æå¡
2ï¼äºå¹³å°å¤§å¤æå¡å ±ç¨ä¸ä¸ªæ°æ®åºï¼å³smartåºã(2021-09ä¹å)
å¦access-serverãmbsãsubãmessageãiot-rpcãiot-sceneãthird-accessã ç½å ³äºgateway ãmcloudboxçå å个æå¡ã
â ï¼å ä¸ªæ ¢SQLï¼ä¼é ææ°æ®åºååæ¥å§ä¸åï¼åºç¨è¶ æ¶å¢å¤ï¼å端åºç¨è¶ æ¶ï¼ç¨æ·éè¯ï¼æµéé£åï¼èµæºèå°½
â¡ï¼ææåºç¨é½å¨ä¸ä¸ªæ°æ®åºä¸æä½ï¼æ°æ®åºåºç°æ§è½ç¶é¢ãç¹å«æ¯æ°æ®åæè·èµ·æ¥çæ¶åï¼æ°æ®åºæ§è½æ¥å§ä¸éã
â¢ï¼æ°æ®åºæ为æ§è½ç¶é¢ï¼å¹¶ä¸æåç¹æ éçé£é©ã
â£ï¼å¹¶åæ°èå®ã æ°æ®åºçè¿æ¥æ°æ为åºç¨æå¡å¨æ©å®¹çç¶é¢ï¼å 为è¿æ¥ MySQL ç客æ·ç«¯æ°éæ¯æéå¶ç
æ äºä¸ï¼MBSæè æ¥å ¥å±åçï¼å¤§éä¸æ¥æ¶æ¯updateæè select设å¤å±æ§è¡¨ï¼å¯¹æ°æ®åºé æå²å»ï¼å ¶ä»ä¾èµäºsmartåºçæå¡é½æ¶å°å½±å
äºæ äºï¼ä»»ä½ä¸ä¸ªåºç¨ä¸åçä¸ä¸ªä½è´¨éçSQL导è´æ ¢æ¥è¯¢ï¼å ¶ä»ä¾èµäºsmartåºçæå¡é½æ¶å°å½±å
äºæ ä¸ï¼å¤§è¡¨å å段导è´æ°æ®åºåç主ä»åæ¢ï¼æ´ä¸ªå¹³å°åè¦
3ï¼äºå¹³å°å¤§å¤æå¡å ±ç¨ä¸ä¸ªredis(ä¸è¬æ¯å¦è¿ç»´é®è¦æä½åªä¸ªredisï¼ä¸è¬åçå°±æ¯ï¼é£ä¸ªå¤§redisãå ±ç¨redisã主redis)ã(2021-06ä¹å)
å¦access-serverãmbsãsubãmessageãiot-rpcãiot-sceneãthird-accessã ç½å ³äºgateway ãmcloudboxçå å个æå¡ã
â ï¼rediså ±ç¨ï¼redisåºç°ä¸å¯ç¨ï¼é£ä¹æ´ä¸ªå¹³å°å°±ä¼ç«çªï¼å¤§keyï¼keysã
keysï¼keysè¿è¡æ¨¡ç³å¹é å¼åRediséï¼é æRediséä½ï¼CPUé£åï¼å¼èµ·äºææè°ç¨é¾è·¯çè¶ æ¶å¹¶ä¸å¡ä½ï¼çRediséçé£å ç§ç»æï¼ææç请æ±æµéå ¨é¨è¯·æ±å°RDSæ°æ®åºä¸ï¼ä½¿æ°æ®åºäº§çäºéªå´©ï¼ä½¿æ°æ®åºå®æºã
â¡ï¼rediså åä¸è¶³æ´ä¸ªæå± ä¸å¡é½ä¸å¯ç¨
â¢ï¼ é¤äºå¸¦æ¥æ大çå åå ç¨å¤ï¼å¨è®¿é®éé«æ¶ï¼å¾å®¹æå°±ä¼å°ç½å¡æµéå 满ï¼è¿èé ææ´ä¸ªæå¡å¨ä¸çæææå¡ä¸å¯ç¨ï¼å¹¶å¼åéªå´©æåºï¼é æå个系ç»ç«çª
äºæ ä¸ï¼keysç使ç¨å¯¼è´ä¾èµäºä¸»redisçä¸å¡å ¨é¨ä¸å¯ç¨
äºæ äºï¼å¤§keyé¿æ¶é´é»å¡redis线ç¨å ¶ä»è¯·æ±å¾ä¸å°å¤çï¼å¯¼è´ä¾èµäºä¸»redisçä¸å¡å ¨é¨ä¸å¯ç¨
äºæ ä¸ï¼æ¥å£æµéçå²å»å¯¼è´rediså åä¸è¶³ï¼ä¾èµäºä¸»redisçä¸å¡å ¨é¨ä¸å¯ç¨
4ï¼äºå¹³å°å¤§å¤æå¡é½ä¾èµäºä¸ä¸ªcommon jarå ï¼è¿ä¸ªjarå çå 容å æ¬ï¼
对smartåºä¸äºæ ¸å¿è¡¨çå¢å æ¹æ¥ï¼å¯¹ä¸»redisçä¸äºæä½ï¼ä¸äºéç¨çå¦VO对象çã
â ï¼æ°æ®åºè¡¨ç»æå¯è½è¢«å¤ä¸ªæå¡ä¾èµï¼çµä¸åèå¨å ¨èº«ï¼å¾é¾è°æ´ ï¼å¦éè¦å å段ï¼å ä¹å ¨é¨çæå¡é½å¾éæ°æå ï¼mybatis.sqléè¦selectåºè¿ä¸ªå段ï¼commonéçVO对象éè¦å ä¸è¿ä¸ªå±æ§ãå¦åæ¾å ¥redisçkeyå°äºå±æ§å¯¼è´åºé®é¢ã
â¡ï¼ä¸ç产åæ建æå è费大éçæ¶é´ã
â¢ï¼æå¡ä¼åé¾ï¼å¦æ°æ®æ´æ¹å ¥å£åæ£å¨è¿å å个æå¡éï¼å¯¼è´æ们ä¸è½åï¼å¦ ä¸è½åæ¬å°ç¼åï¼æ æ³åæ°æ®å¼æç
5ï¼éç¨é®é¢
â ï¼åç¹æ éå½±åæ´ä¸ªæå¡
â¡ï¼SLAä¸éï¼é¢ç¹äºæ
â¢ï¼æ©å®¹æ¶åæµéä¸å¥½é¢ä¼°
â£ï¼æ æ³æ»¡è¶³å¿«éè¿ä»£çéæ±
对äºä»¥ä¸åå¨çåç§é®é¢ï¼å·²ç»ä¸æ¯æ©å®¹ï¼æºå¨ï¼æ°æ®åºãreidsï¼å¯ä»¥è§£å³çäºãæ以æ们è±äºå¤§æ¦ä¸å¹´åçæ¶é´å¯¹ä»¥ä¸åå¨çé®é¢è¿è¡äºæ¹é ãä»ä»£ç æåãé¨ç½²ç¬ç«ãç¼å解è¦ãä¸å¡æåãæ°æ®åºç¬ç«çä¸ç³»åæªæ½ãéè¿è¿äºæ¥éª¤åï¼æ们çæå¡è§£å³äºä¸é¢æå°ç大é¨åçç¹ï¼å¦ï¼
1ï¼ä¸»ä¸å¡æµç¨åéå ä¸å¡å离
2ï¼åå°åç¹é®é¢é æçå½±åé¢
3ï¼å¤æ¨¡åå¼åä¸ç¸äºå½±åï¼è´£ä»»æ´å æ¸ æ°ï¼æ¯ä¸ªäººä¸å¿è´è´£ä¸ºå ¶ä»äººæä¾æ´å¥½çæå¡
4ï¼éç¨ä¸å¡ä¸æ²ä¸ºåºç¡æå¡
5ï¼æ¶æ¢æ°æ®ç»´æ¤å ¥å£ï¼å个æå¡å¯ä»¥æ¨ªåæ©å±å¦ååºå表读åå离ç
6ï¼å¿«éåçï¼æ»å¨åå¸ï¼å½±åé¢å°
æåæ¯éä½é¾åº¦æéè¦ï¼æææçæ¹æ³ï¼äººä»¬æ»ä¼éå°é¾åº¦å¤§äºè½åçé®é¢ï¼æ以就éè¦æåè¿ç§æ¹æ³ï¼æé®é¢çé¾åº¦éä½ï¼ä»è使å¾è½å大äºé®é¢é¾åº¦ï¼ä»èæé®é¢è§£å³
æå¡æååï¼å¨æ¥å£ç¨³å®çæ åµä¸ï¼ä¸åç模åå¯ä»¥ç¬ç«ä¸çº¿ãè¿æ ·ä¸çº¿ç次æ°å¢å¤ï¼å次ä¸çº¿çéæ±å表åå°ï¼å¯ä»¥éæ¶åæ»ï¼é£é©åå°ï¼æ¶é´åçï¼å½±åé¢å°ï¼ä»èè¿ä»£é度å å¿«ã
æå¡æåè½ç¶è§£å³äºæ§é®é¢ï¼ä¹å¼å ¥äºæ°çé®é¢ã
â ï¼å¾®æå¡æ¶ææ´ä¸ªåºç¨åæ£æå¤ä¸ªæå¡ï¼å®ä½æ éç¹é常å°é¾ã[é¾è·¯è¿½è¸ª]
â¡ï¼ç¨³å®æ§ä¸éãæå¡æ°éåå¤å¯¼è´å ¶ä¸ä¸ä¸ªæå¡åºç°æ éçæ¦çå¢å¤§ï¼å¹¶ä¸ä¸ä¸ªæå¡æ éå¯è½ä¼äº§çéªå´©æç¨ï¼å¯¼è´æ´ä¸ªç³»ç»æ éãäºå®ä¸ï¼å¨å¤§è®¿é®éçç产åºæ¯ä¸ï¼æ éæ»æ¯ä¼åºç°çã[æ éé离ï¼æ éä¼ æ]
â¢ï¼æå¡æ°éé常å¤ï¼é¨ç½²ã管ççå·¥ä½éå¾å¤§ã[ devOps ï¼CI/CD]
â£ï¼ä¸æ¹é¢å°½éåå°æ éåççæ¦çï¼å¦ä¸æ¹é¢éä½æ éé æçå½±å.[æå¡èªæä¿æ¤:â ï¼ä¸å¡é´é离ï¼â¡ï¼åºå¯¹ä¸æ¸¸çå²å»ï¼â¢ï¼åºå¯¹ä¸æ¸¸çé»å¡ï¼å¸¸è§çè¯è¯ï¼èµæºé离ï¼çæï¼éæµï¼é级]
以ä¸æåååå¨çå ³äºå¾®æå¡æ²»çãæå¡èªæä¿æ¤çä¸äºç¸å ³çé®é¢ï¼åç»å空ä¼å大家ä¸èµ·å享ã
æä»å¤©æ们主è¦å´ç»çï¼sqlä¼åï¼çº¿ç¨æ± çç¸å ³çç¥è¯ç»å¤§å®¶å享ãå ¶ä¸ç©¿æçä¸äºç产äºè§£æï¼è¿æ ·å¯ä»¥è®©å¤§å®¶å¨ä»¥åçå·¥ä½ä¸é¿å ç¯åæ ·çé误ã
äºï¼ä¸å¡ä»£ç ä¼å
è¿ä¸é¨åçå 容主è¦æ¯ä»æ们äºç«¯ç代ç ä»åºéé便æ½æ¥äºä¸äºä»£ç ä½ä¸ºæ¡ä¾æ¥è®²è§£ã主è¦æ¯åè¯å¤§å®¶ä¸ä¸ªéçï¼ä¼åææ¶åå¯è½å°±æ¯é¡ºæå°±å¯ä»¥åçäºæ ï¼å¹¶ä¸éè¦ä»ä¹é«å¤§ä¸çä¸è¥¿ã
ç¨åºé»è¾ä¼åï¼æ¯å¦å°å¤§æ¦çé»ææ§è¡æµç¨çå¤æé»è¾åç½®ãFor循ç¯ç计ç®é»è¾ä¼åï¼æè éç¨æ´é«æçç®æ³ ã
åç§æ± åææ¯ç使ç¨åæ± å¤§å°ç设置ï¼å æ¬HTTP请æ±æ± ã线ç¨æ± ï¼èèCPUå¯éåè¿æ¯IOå¯éåè®¾ç½®æ ¸å¿åæ°ï¼ãæ°æ®åºåRedisè¿æ¥æ± ç
æ± åå®é ä¸æ¯é¢å¤çå延åå¤ççä¸ç§åºç¨åºæ¯ï¼éè¿æ± åå°åç±»èµæºçå建æååéæ¯å»¶åã
åå°IOæä½
æ¯å¦æ们使ç¨sqlæ¥è¯¢æ°æ®ï¼å¦æä¸æ¡æ¡å»æ¥è¯¢çè¯ï¼é£ä¹æ¯æ¬¡æ§è¡æ¥è¯¢ï¼æ´ä¸ªæ¥è¯¢æµç¨é½ä¼æ§è¡ä¸é(è¿æ¥ãæ¥è¯¢ç¼åãåæå¨ãä¼åå¨ãæ§è¡å¨çï¼æç»ioè¿åç»è°ç¨æ¹)ï¼è¿æ ·ä¸ä½èæ¶èä¸èè´¹æ°æ®åºè¿æ¥ï¼æ们å¯ä»¥ä½¿ç¨æ¹éæ¥è¯¢çæ¹å¼ï¼æéè¦æ¥è¯¢çæ¡ä»¶ä¸æ¬¡æ§çç»å°mysqlæå¡ã
List ids = new ArrayList<>(Arrays.asList(1,2,3,4,5,6,7,8,9,10));
List<User> list = new ArrayList();
for(int id : ids){
User user = redis.get(id);//ä¸æ¬¡IO
if(user == null){
user = Dao.get(id);//ä¸æ¬¡IO
}
list.add(u);
}
ä¼ååï¼
List<User> list = redis.multiGet(ids);//keyè¿å¤§åæ¹
ids.removeAll(list.stream.map(User::getId).collect(Collectors.toList()));
if(!ids.isEmpty()){
list.addAll(Dao.batchGet(ids));//INè¿å¤§å¯ä»¥åæ¹æ¥è¯¢
}
å°è¯·æ±æ¦æªå¨ç³»ç»ä¸æ¸¸
å°½éæåæ°æ ¡éªæè ä¸ä¸æ¶èèµæºçæ ¡éªæ¾å¨æåæ¹ï¼é¿å ç¨åºååæ§è¡å°sqlå±æ¶èæ°æ®åºèµæºã
<select id="queryUserByCondition" parameterType="com.midea.iot.svc.user.entity.vo.UserVo"
resultType="com.midea.iot.svc.user.entity.UserAll">
select
id,owner_app_id,src_app_id,nick_name,password,mobile,email,address,account_status,update_time,register_time,
signature,profile_pic_url,sex,phone,age,uid from t_ms_user where 1=1
<if test="uid != null and uid != ''">
and uid=#{uid}
</if>
<if test="mobile != null and mobile != '' and ownerAppId == null">
and mobile = #{mobile,jdbcType=VARCHAR} and owner_app_id is null
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</select>
é®é¢åå ï¼ä»¥ä¸æ¥å£å±å¼ååå¦æªååæ°æ ¡éªï¼å¯¼è´ä»¥ä¸æ¡ä»¶é½ä¸æ»¡è¶³ï¼ ä»è导è´çæç sql 缺失äºæ¥è¯¢æ¡ä»¶ï¼å¯¼è´å ¨è¡¨æ«ç æ«æ545688æ¡ãå¤æ¬¡è¯·æ±å è½½å导è´ç¨åºå¯¼è´OOMã
for (Room_restore room_restore : roomList) {
Long roomId = createRoom(room_restore, homegroupId, user.getId(),req.getReqId());//ä¸æ¬¡IO
if (CollectionUtils.isNotEmpty(room_restore.getGatewayList())) {
for (GateWay gateWay : room_restore.getGatewayList()) {
Long gateWayId = createGateway(gateWay, homegroupId, roomId,user.getId(),reqId);//ä¸æ¬¡IO
if (CollectionUtils.isNotEmpty(gateWay.getSubdeviceList()) && gateWayId != null) {
for (Subdevice subdevice : gateWay.getSubdeviceList()) {
roomId = createRoom(new Room_restore(subdevice.getRoomId(), subdevice.getRoomName(), subdevice.getRoomType()), homegroupId, user.getId(),req.getReqId());//ä¸æ¬¡IO
createSubDevice(subdevice, homegroupId, roomId, gateWayId,user.getId(),reqId);//ä¸æ¬¡IO
}
}
}
}
}
å¨æ们ç代ç ä¸ï¼ä¹çå°ç±»ä¼¼ä»¥ä¸çæ åµï¼
insertçæ¶åä¸å¡ä»£ç ä¸æ ¡éªæ¯å¦ä¸ºç©ºï¼ææ¯å¦ä¸ºç©ºçæ ¡éªäº¤ç»mysqlçnot null约æå»æ ¡éªï¼è¿æ ·æµªè´¹äºä¸å¿ è¦çèµæºå¼éã
并è¡è°ç¨
httpå¤æ¬¡è¯·æ±å¤é¨æ¥å£ï¼å¦æè°ç¨ä¹é´æ²¡æç»æä¸çä¾èµï¼é£ä¹éå½çæ åµä¸å¯ä»¥èè使ç¨å¤çº¿ç¨çæ¹å¼è¿è¡ãå 为å¨è¿ä¸ªè¿ç¨ä¸cpu并ä¸è¿å¤åä¸ioï¼æ以æ们å¯ä»¥å¼å¯æ´å¤çº¿ç¨å»æ¢å cpuæ¶é´çã
CompletableFuture<Void> applianceStatusFeature = CompletableFuture.runAsync(()->{
applianceStatusQueryService.batchGetStatus(applianceList,status);
}, applianceListExecutor);
CompletableFuture<Void> appliancePropertiesFeature = CompletableFuture.runAsync(()->{
batchGetApplianceProperties(applianceList,nfcDetail,bluetoothDetail);
fillApplianceBindType(applianceList);
}, applianceListExecutor);
try {
CompletableFuture.allOf(applianceStatusFeature,appliancePropertiesFeature).get(5, TimeUnit.SECONDS);
} catch (TimeoutException e) {
logger.error("appliance home list error",e);
throw new ApplianceApiException(CommonErrorCode.SYSTEM_ERROR);
}
æ¥å¿è¾åº
å 为å符串常éæ± æå¨çå åå没æ空é´ï¼æ ¹æ¬åå æ¯å 为e.printStackTrace() è¯å¥äº§ççå符串记å½çæ¯å æ ä¿¡æ¯ï¼å¤ªé¿å¤ªå¤ï¼å åè¢«å¡«æ»¡äº ãåæ ·çè¿ä¸ªæ¯æ åè¾åºå°æ§å¶å°çï¼å¯¹å®ä½é®é¢ä¹æ²¡å¥ç¨ã
try {
Map<String, String> v2Bean = beanToMap(v1Bean.getMap());
v2Bean.put("meijuVersion", "4.0");
v2.params = v2Bean;
} catch (Exception) {
e.printStackTrace();
}
æ¥å¿è¾åºè§èï¼ç¬¬äºç« ä¼ç»è®²ã
æ°æ®å¼æ
æ¯å¦ä¿åä¸äºå ¶ä»ç³»ç»ä¸ç»å¸¸ååçæ°æ®æ¾å¨æ¬å°ç¼åï¼å ¸ååºæ¯ï¼è®¾å¤æ§å¶æ¶åå¤æ设å¤æ¯å¦å±äºæ个ç¨æ·ï¼å¯ä»¥æç»æç¼åèµ·æ¥ï¼æ¯æ¬¡æ§å¶è®¾å¤ï¼å¦ææ¯åç´æ¥æ§å¶ï¼å¦æä¸å±äºåè°ç¨è®¾å¤æå¡æ¥å£æ¥è¯¢ï¼æ¥è¯¢ç»æç¼åèµ·æ¥ãåæ¶å¨è®¾å¤ï¼ç¨æ·å ³ç³»åæ´åå¸äºä»¶ï¼æ§å¶ç³»ç»å é¤æ¬å°ç¼åã å¨é«å¹¶åæè æ¶å»¶ææçåºæ¯ä¸ï¼ä¸ºäºæåæ¥å£æ§è½ï¼å¯ä»¥ä¿ååä½æ°æ®çæ°æ®å¼ææ¹æ¡ æ¥æ¿ä»£è¿ç¨æ¥å£çè°ç¨ã
æ°æ®å¼æçæ¹æ¡ï¼ å°±ä¼åºç°æ°æ®ä¸è´æ§é®é¢ ãæ¯ç§æ¹æ¡é½æ¯ç©ºé´åæ¶é´ç平衡ï¼å¨ä»¥ä¸åºæ¯ä¸å³ä½¿æ°æ®ä¸ä¸è´ä¹ä¸ä¼å½±åç¨æ·ä½éªãå¦ææä¸å®çä¸è´æ§è¦æ±ï¼åå¯ä»¥æ³ä¸äºæ°æ®åæ¥æ¹æ¡ï¼å¦æ°æ®åæ´æ¹åäºä»¶çï¼æ¶è´¹æ¹çå¬äºä»¶åç¼åæ¸ ççæä½ã
éä½éç²åº¦
éä½éç²åº¦ concurrenthashmap ï¼å°½å¯è½åªå¨ä¸´çç¹å éï¼æ éï¼æè ä¹è§éï¼è¯»å¤åå°çåºæ¯ï¼ï¼é¿å å¨å¾ªç¯ä¸å é(é»å¡åå¤éå¾è´¹æ¶é´)ï¼ä¸å¦ç´æ¥ææ´ä¸ªå¾ªç¯å é(å¦æä¸å½±åå¤çº¿ç¨é´çé»è¾å ³ç³»)
æ¤ç段æèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
ã强å¶ãé«å¹¶åæ¶ï¼åæ¥è°ç¨åºè¯¥å»èééçæ§è½æèãè½ç¨æ éæ°æ®ç»æï¼å°±ä¸è¦ç¨éï¼è½
éåºåï¼å°±ä¸è¦éæ´ä¸ªæ¹æ³ä½ï¼è½ç¨å¯¹è±¡éï¼å°±ä¸è¦ç¨ç±»éã
说æï¼å°½å¯è½ä½¿å éç代ç åå·¥ä½éå°½å¯è½çå°ï¼é¿å å¨é代ç åä¸è°ç¨ RPC æ¹æ³ã
åä¾ï¼æçä½ä¸ï¼
private static Singleton singleton;
private Singleton() {
}
public static synchronized Singleton getInstance() {
if (singleton == null) {
Thread.sleep(1000);
singleton = new Singleton();
}
return singleton;
}
DCLä¼ååï¼
public class Singleton {
private static volatile Singleton singleton;
private Singleton() {
}
public static Singleton getInstance() {
if (singleton== null) {
Thread.sleep(1000);
synchronized (Singleton.class) {
if(null == singleton) {
singleton = new Singleton();
}
}
}
return singleton;
}
}
ä¹è§éï¼å¦javaä¸çCASï¼AtomicInteger ï¼æ°æ®åºå®ç°ä¹è§é并åæ´æ°çã
æºç ä¸çæ¡ä¾ï¼
protected Object getSingleton(String beanName, boolean allowEarlyReference) {
Object singletonObject = this.singletonObjects.get(beanName);
if (singletonObject == null && isSingletonCurrentlyInCreation(beanName)) {
synchronized (this.singletonObjects) {
singletonObject = this.earlySingletonObjects.get(beanName);
if (singletonObject == null && allowEarlyReference) {
ObjectFactory<?> singletonFactory = this.singletonFactories.get(beanName);
if (singletonFactory != null) {
singletonObject = singletonFactory.getObject();
this.earlySingletonObjects.put(beanName, singletonObject);
this.singletonFactories.remove(beanName);
}
}
}
}
return singletonObject;
}
线ç¨æ± /åå°å¼é
æ¤ç段æèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
ã强å¶ã线ç¨èµæºå¿ é¡»éè¿çº¿ç¨æ± æä¾ï¼ä¸å 许å¨åºç¨ä¸èªè¡æ¾å¼å建线ç¨ã
说æï¼çº¿ç¨æ± ç好å¤æ¯åå°å¨å建åéæ¯çº¿ç¨ä¸ææ¶èçæ¶é´ä»¥åç³»ç»èµæºçå¼éï¼è§£å³èµæºä¸è¶³çé®é¢ã
å¦æä¸ä½¿ç¨çº¿ç¨æ± ï¼æå¯è½é æç³»ç»å建大éå类线ç¨è导è´æ¶èå®å åæè âè¿åº¦åæ¢âçé®é¢ã
public void updateSceneDataAsyn(Map<Long, Long> sceneIdMap, String uid, Long homegroupId, String reqId,
String stamp) throws Exception {
logger.info("updateSceneDataAsyn sceneIdMap:{},uid:{},homegroupId{},reqId:{}",sceneIdMap,uid,homegroupId,reqId);
if (sceneIdMap != null && !sceneIdMap.isEmpty()) {
new Thread(() -> {
logger.info("begin {} updateSceneDataAsyn",reqId);
try {
Iterator<Entry<Long, Long>> iterator = sceneIdMap.entrySet().iterator();
while (iterator.hasNext()) {
Entry<Long, Long> entry = iterator.next();
Long oldSceneId = entry.getKey(), newSceneId = entry.getValue();
if(oldSceneId.longValue() != newSceneId.longValue()) {
updateSceneActionResult(oldSceneId, newSceneId);
updateSceneResult(oldSceneId, newSceneId, homegroupId);
}
}
} catch (Exception e) {
logger.error("updateSceneData error", e);
}
logger.info("end {} updateSceneDataAsyn",reqId);
},"updateSceneDataAsyn").start();
}
}
è¿éæ¶åå°çº¿ç¨æ± åæ°è®¾ç½®é®é¢ï¼ä¼å¨ä¸é¢è®²è§£ã
åå°ä¸å¿ è¦çæ¥è¯¢
æ¯å¦ï¼å¨ if(A && B) è¿ç®çæ¶åå¦æ两边æè ä¸è¾¹åå¨ioæä½æ¥å°å¾å°ç»æï¼å¦æå¯è½çè¯æå¼æ¥åï¼æ代价å°çå¤æå æ§è¡ï¼è¿æ ·å½è¿é¨åä¸æ»¡è¶³çæ åµä¸å¯ä»¥çæåé¢ä¸å¿ è¦çæä½ã
public void updateSubscribe(AppSubscribeTypeUpdateRequest appSubscribeTypeUpdateRequest) throws Exception {
Integer appId = Integer.parseInt(OpenSecurityUtil.decrypt(appSubscribeTypeUpdateRequest.getClientId()));
//ä¿®æ¹æç¼å
App app = openAppCache.getByAppId(appSubscribeTypeUpdateRequest.getReqId(), appId); 代ç â
AppSrc appSrc = openAppCache.getSrcByAppId(appSubscribeTypeUpdateRequest.getReqId(), appId); 代ç â¡
if (app != null && appSrc != null) {
//ä¿®æ¹ææç¸å
³ä¿¡æ¯
}
}
ä»¥ä¸ ä»£ç â ï¼ä»£ç â¡ æ 论ä½æ¶é½æ¯ä¼æ§è¡çãä½æ¯çiféçé»è¾å½é½ä¸ä¸ºç©ºçæ¶åææ§è¡é»è¾ãæ以è¿ç§æ åµæ们å¯ä»¥å æ¥è¯¢ä»£ç â (æ¯å¦ä»£ç â æ¥è¯¢DBçæ§è½æ¯ä»£ç â¡é«ä¸äº)ã
å½ç¶ï¼å¦æä¸å¡ä¸ä¸åå¨ä¼ éæ³åæ°å¯¼è´æ¥è¯¢dbæ¥è¯¢App为空æè 说dbéä¸å®åå¨è¿äºæ°æ®çæ åµä¸ï¼åæ²¡å¿ è¦ä¼åã
public void updateSubscribe(AppSubscribeTypeUpdateRequest appSubscribeTypeUpdateRequest) throws Exception {
Integer appId = Integer.parseInt(OpenSecurityUtil.decrypt(appSubscribeTypeUpdateRequest.getClientId()));
//ä¿®æ¹æç¼å
App app = openAppCache.getByAppId(appSubscribeTypeUpdateRequest.getReqId(), appId); 代ç â
if (app != null ) {
AppSrc appSrc = openAppCache.getSrcByAppId(appSubscribeTypeUpdateRequest.getReqId(), appId); 代ç â¡
if(appSrc != null){
//ä¿®æ¹ææç¸å
³ä¿¡æ¯
}
}
}
以ä¸ä»£ç ä¹å¯ä»¥è¿è¡ä¼å
private void updateSubscribeInfo(List<String> subTypes, Integer appId, Integer srcId,String reqId){
SubscriptionThird subscriptionThird = subscriptionApiManager.getSubscriptionThird(appId,DEFAULT_THIRD_TOPIC,reqId);
if (subscriptionThird == null && CollectionUtils.isNotEmpty(subTypes)) {
subscriptionApiManager.insertSubscriptionThird(appId,DEFAULT_THIRD_TOPIC,reqId);
}
}
}
æ¹ä¸ºï¼
private void updateSubscribeInfo(List<String> subTypes, Integer appId, Integer srcId,String reqId){
if (CollectionUtils.isNotEmpty(subTypes)) {//å¤æ代价å¾å°ï¼è¿éä¸æ»¡è¶³å°ä¼çå»ä¸é¢çioæ¥è¯¢æä½
SubscriptionThird subscriptionThird = subscriptionApiManager.getSubscriptionThird(appId,DEFAULT_THIRD_TOPIC,reqId);
if( subscriptionThird == null){
subscriptionApiManager.insertSubscriptionThird(appId,DEFAULT_THIRD_TOPIC,reqId);
}
}
}
}
æ¤ç段æèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
åå°ä¸å¿ è¦çæ´æ°ï¼ä¸è¦åä¸ä¸ªå¤§èå ¨çæ°æ®æ´æ°æ¥å£ãä¼ å ¥ä¸º POJO ç±»ï¼ä¸ç®¡æ¯ä¸æ¯èªå·±çç®æ æ´æ°å 段ï¼é½è¿è¡ update table set c1=value1,c2=value2,c3=value3; è¿æ¯ä¸å¯¹çãæ§è¡ SQL æ¶ï¼ ä¸è¦æ´æ°æ æ¹å¨çå段ï¼ä¸æ¯æåºéï¼äºæ¯æçä½ï¼ä¸æ¯å¢å binlog åå¨;åæ¯çæundolog;äºæ¯æ´æ°æ¬èº«æ¯ä¼å æä»éï¼æ æçæ´æ°éä½äºå¹¶åæ§ã
å°è±¡ææ·±çæ¯MBS(å¾æ©ä¹åçmbs)ä¸æ¥è®¾å¤å±æ§ç并åå¾é«ï¼ç¹å«æ¯æ¥å ¥å±åçåæ´ä¸ªäºå¹³å°å¯è½é½ä¼ç«çª(大å¤æ°æå¡å ±ç¨smartåº)ãå 为ï¼
大éç设å¤ä¸æ¥ï¼mbs大éçæ´æ°å±æ§è¡¨ï¼å ¶å®å±æ§çé¢çååå¾ä½ï¼æ²¡å¿ è¦æ¯æ¬¡/æ¯ä¸ªå段é½å»æ´æ°ï¼æ以mbså©ç¨å æ¥è¯¢åå¤æä¸æ¥çæ°æ®æ¯å¦åä¸æ¥çæååï¼åªæ´æ°ååçå段ï¼æ ååçä¸ç¨æ´æ°ã
é¿å é¿äºç©
å¦æç¨åºä½¿ç¨äºäºç©ï¼é£ä¸å®è¦æ³¨æäºç©çä½ç¨èå´ï¼å°½é以æå¿«çé度å®æäºç©æä½ãå¦æäºç©çæ§è¡æ¶é´è¿é¿ï¼åä¸äºç©ç¸å ³çæ°æ®å°±ä¼è¢«éä½ï¼å½±åç³»ç»ç并åæ§ä¸æ´ä½æ§è½ã
æ¡ä¾ä¸ï¼é¿å å¨äºç©ä¸è¿è¡è¿ç¨è°ç¨(http,mq,rpcç)
å¨ä»¥ä¸æ¡ä¾ä¸ï¼ç»å®å®æ设å¤è¿ä¸ªéè¦äºç©æä½ï¼ä½æ¯ç»å®å®æåçhttpè°ç¨æ¯ä¸éè¦äºç©çï¼å¦ææ¾å¨äºç©æä½éï¼å¦æè¿ç¨æå¡ä¸å¯ç¨æè ç½ç»ä¸éçæ åµä¸æ导è´httpæ¥å£æè MQåå ¥é»å¡ï¼ä»èå¯¼è´ è¿é¿æ¶ä¼å ç¨æ°æ®åºè¿æ¥ ï¼é«å¹¶åä¸èå°½æ°æ®åºè¿æ¥ã
@Transactional
public void applianceBind(Appliance applianceï¼Long homegroupId){
dao.updateAppliance(appliance);
dao.updateApplianceHomeGroup(appliance.getId(),homegroupId);
msgHandler.send(appliance,homegroupId);//è¿ç¨httpè°ç¨æ¶æ¯ä¸å¿æ¨éæ¶æ¯ï¼æè
æ¾å
¥MQ
//æ¹æ³è¿è¡å®æäºç©æç»æ
}
æ°æ®åºæä½ä¸åéæ¶æ¯è§£è¦ ï¼ å¨æ°æ®æåä¿åä¸äºå¡æ交äºå°±åéæ¶æ¯
@Transactional
public void applianceBind(Appliance applianceï¼Long homegroupId){
dao.updateAppliance(appliance);
dao.updateApplianceHomeGroup(appliance.getId(),homegroupId);
TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronizationAdapter(){
@Override
public void afterCommit() {//äºç©æ交æåä¹åæ§è¡
msgHandler.send(appliance,homegroupId);//è¿ç¨httpè°ç¨æ¶æ¯ä¸å¿æ¨éæ¶æ¯æè
æ¾å
¥MQ
}
});
}
æè 使ç¨springæä¾çæ¶é´çå¬å¨å»è§£å³è¿ç§ å¨å¤çæ°æ®åºäºå¡æ交æåååæ§è¡æäºæä½ ï¼å¯ä»¥äºè§£@TransactionalEventListener注解çåçã
æ¡ä¾äºï¼çç¹æ°æ®æ´æ°é®é¢
æ¯å¦å11æä¸æ·å®åå大åï¼ç¨æ·ä¸ååçé±é½æ¯ä»ç»æ·å®ä¼ä¸é¶è¡è´¦å·ï¼æ¤æ¶å¾å¤æ¯ä»è¯·æ±ä¼åæ¶æ´æ°æ·å®ä¼ä¸é¶è¡è´¦å·çä½é¢ï¼ç¶ååçæ订åä¿¡æ¯åæ¶è®°å½ä¸ç¬æä½æµæ°´ã
@Transactional
public void paymentOrder(Order order){
aliAccount.updateBalance(order.getAmount());//æ´æ°æ·å®è´¦æ·ä½é¢å±äºçç¹è®°å½çæä½ 1s
orderDao.updateOrderStatus(order.getId(),1);//æ´æ°è®¢åç¶æ 1s
logDao.Log(order);//è®°å½ä¸æ¡æ¥å¿ 1s
}
以ä¸å¨ä¸ä¸ªäºç©ä¸ï¼æ´æ°æ·å®è´¦æ·ä½é¢ çæä½å±äºå¯¹å ±æèµæºçæä½ï¼è¿éåå¨æä»é ,为äºæé«å¹¶åï¼æ们å¯ä»¥æè¿ä¸ªæä½æ¾å¨æåä¸è¡ã
å¤æ³¨ï¼å享çæ¶åæå¯è½æ²¡æè¿é讲çå¾æç½ï¼è¿éå次解éä¸
å设ï¼ä»¥ä¸ä¸ä¸ªdaoæä½ï¼æ¯ä¸ªæä½é½è±è´¹1sçæ¶é´ï¼é£ä¹aliAccount.updateBalance(order.getAmount()) è¿æ¡è¢«æ´æ°çæ°æ®å°ææéçæ¶é´æ¯å¤§æ¦3sï¼å¨è¿3så ï¼ä»»ä½è°ç¨è¿ä¸ªæ¹æ³ç请æ±é½ä¼é»å¡å¨è¿è¡ä»£ç ãç´å°ç¬¬ä¸ä¸ªè¯·æ±æ§è¡å®æ´ä¸ªæ¹æ³æä¼éæ¾éãå ¶ä»çå¾ éç请æ±ä¸ä¼æä¸ä¸ªè¯·æ±è·åå°é继ç»æ§è¡ï¼å ¶ä»ç请æ±ç»§ç»å¨aliAccount.updateBalance(order.getAmount())çå¾ ã
å¦ææ们æaliAccount.updateBalance(order.getAmount())æ¾å¨æåä¸è¡ï¼é£ä¹aliAccount.updateBalance(order.getAmount())è¿æ¡è¢«æ´æ°çæ°æ®å°ææéçæ¶é´æ¯å¤§æ¦1sï¼å ç¨éçæ¶é´ä¼åçï¼å ¶ä»è¯·æ±ä¼å æ§è¡å ¶ä»ç两个æ´æ°è¯å¥åæçå¾ éçéæ¾ã
ç¥è¯ç¹ï¼ä¸¤é¶æ®µéåè®®
两é¶æ®µå éåè®®ï¼ç¨äºåæºäºå¡ä¸çä¸è´æ§åé离æ§
å¨ä¸ä¸ªäºå¡æä½ä¸ï¼å为å éé¶æ®µå解éé¶æ®µï¼ä¸ææçå éæä½å¨è§£éæä½ä¹å ã
å éæ¶æºï¼å½å¯¹è®°å½è¿è¡æ´æ°æä½æè select for update(Xé)ãlock in share mode(Sé)æ¶ï¼ä¼å¯¹è®°å½è¿è¡å éã
ä½æ¶è§£éï¼ å¨ä¸ä¸ªäºå¡ä¸ï¼åªæå¨commitæè rollbackæ¶ï¼ææ¯è§£éé¶æ®µã
é¢å¤çé¿åæå ï¼äºç©ä¸åå¸å¼éçåºæ¯è¦æ éã
æè¿°ï¼å¨èç设å¤æå¡ä¸åå¨ä»¥ä¸ä»£ç ï¼åºç°çé®é¢æ¯ç»å®è®°å½æå ¥éå¤ã
åå ï¼ééæ¾ï¼äºç©è¿æªæ交ï¼å ¶ä»äºç©åå°è¾¾æ¡ä»¶åºå导è´ä»£ç æ§è¡éå¤ã
@Transactional
public void activeBind(Long applianceCode,Long homegroupId){
DistributedLock lock = null;
try{
lock = getDistributedLock("activeBind:"+applianceCode);
if (lock.lock()) {
Appliance appliance = selectOne(applianceCode);
if (appliance != null && shoppingOrder.getStatus() == 0) {//æ¡ä»¶åºå
appliance.setActiveStatus(1);
appliance.setActiveTime(new Date());
xxxService.updateAppliance(appliance);
xxxService.addBindRecord(applianceCode,homegroupId);//æå
¥ç»å®è®°å½
}
}catch (Exception e) {
}finally {
if (lock != null) {
lock.unlock();
}
}
}
请æ±å并
å¨é«å¹¶åæ¥å£è®¾è®¡çè¿ç¨ä¸æä¸ä¸ªæå·§æ¯å¯ä»¥éç¨æ¥å£å并ççç¥ãå®çåææ¯è¦æ±ä¸æ¸¸æå¡è¦æä¾æ¹éæ¥å£ã
é¦å 为æ¯ä¸ä¸ªè¯·æ±åé ä¸ä¸ªå¯ä¸çæ è¯æ¥è¡¨ç¤ºè¯¥æ¬¡è¯·æ±ï¼ç¶åå°è¯·æ±æ¾å ¥éåï¼éè¿å®æ¶ä»»å¡æ¯é10mså»éåéé¢è·åä¸æ¹è¯·æ±ï¼ç¶åç»è£ ææ¹é请æ±çåæ°åæ¹éæ¥å£å起请æ±ãå¨æ¶å°è¿åç»æ以åï¼åæ ¹æ®å¯ä¸æ è¯æ¥è®²ç»æèµäºå¯¹åºç请æ±ãè¿æ ·å¯ä»¥éä½io请æ±æ¬¡æ°çç®çï¼æé«å¹¶åæ§è½ã
å个请æ±ï¼
å并请æ±
请æ±å并åç示æå¾
声æï¼æ¤å¾æ¯ç §çå«äººçå¾ç»çï¼å¾å³ä»£ç ãè¿é主è¦ç¨å°çº¿ç¨æ± ãå®æ¶ä»»å¡ãçå¾ /å¤éçç¸å ³å·¥å ·ç±»ãç»æååçä¸äºåºç¡ç¥è¯ã
package com.example.demo.service;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.Executors;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import java.util.function.Function;
import java.util.stream.Collectors;
import javax.annotation.PostConstruct;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Component;
import com.example.demo.third.ApplianceSvcService;
import com.example.demo.vo.Appliance;
import com.midea.framework.commons.http.R;
import com.midea.framework.commons.json.JsonUtil;
@Component
public class ApplianceService {
private static final Logger LOGGER = LoggerFactory.getLogger(ApplianceService.class);
private LinkedBlockingDeque<ApplianceBatchSendRequest> applianceBatchSendRequestQueue = new LinkedBlockingDeque<>();
@Autowired
private ApplianceSvcService applianceSvcService;
public void put(CompletableFuture<Appliance> completedFuture, Long applianceCode) {
ApplianceBatchSendRequest applianceBatchSendRequest = new ApplianceBatchSendRequest();
applianceBatchSendRequest.setApplianceCode(applianceCode);
applianceBatchSendRequest.setCompletedFuture(completedFuture);
applianceBatchSendRequestQueue.add(applianceBatchSendRequest);
}
@PostConstruct
public void init() {
ScheduledExecutorService scheduledExecutorService = Executors.newScheduledThreadPool(50);
scheduledExecutorService.scheduleAtFixedRate(() -> {
List<ApplianceBatchSendRequest> applianceBatchSendRequestList = new ArrayList<>();
List<Long> applianceCodeList = new ArrayList<>();
int size = applianceBatchSendRequestQueue.size();
for (int i = 0; i < size; i++) {
ApplianceBatchSendRequest deviceCreateRequest = applianceBatchSendRequestQueue.poll();
if (Objects.nonNull(deviceCreateRequest)) {
applianceBatchSendRequestList.add(deviceCreateRequest);
applianceCodeList.add(deviceCreateRequest.getApplianceCode());
}
}
if (!applianceBatchSendRequestList.isEmpty()) {
try {
List<Appliance> response = mutilGetApplianceByCodes(applianceCodeList);
Map<Long, Appliance> collect = response.stream().collect(
Collectors.toMap(Appliance::getApplianceCode, Function.identity(), (key1, key2) -> key2));
for (ApplianceBatchSendRequest applianceBatchSendRequest : applianceBatchSendRequestList) {
applianceBatchSendRequest.getCompletedFuture()
.complete(collect.get(applianceBatchSendRequest.getApplianceCode()));
}
} catch (Throwable throwable) {
applianceBatchSendRequestList.forEach(deviceCreateRequest -> deviceCreateRequest
.getCompletedFuture().obtrudeException(throwable));
}
}
}, 0, 10, TimeUnit.MILLISECONDS);
}
public List<Appliance> mutilGetApplianceByCodes(List<Long> applianceCodeList) {
LOGGER.info("mutilGetApplianceByCodes,param:{}", applianceCodeList);
R r = applianceSvcService.mutilGetApplianceByCodes(applianceCodeList);
if (r == null || r.getStatusCode() != HttpStatus.OK.value()) {
LOGGER.error("mutilGetApplianceByCodes error,result:{}", r == null ? null : JsonUtil.toJson(r));
return Collections.emptyList();
}
return JsonUtil.parseArray(r.getResponseText(), Appliance.class);
}
public Appliance getByCode(Long applianceCode) {
LOGGER.info("get appliance by code,param:{}", applianceCode);
R r = applianceSvcService.getApplianceByCode(applianceCode);
if (r == null || r.getStatusCode() != HttpStatus.OK.value()) {
LOGGER.error("mutilGetApplianceByCodes error,result:{}", r == null ? null : JsonUtil.toJson(r));
return null;
}
return JsonUtil.parse(r.getResponseText(), Appliance.class);
}
}
10_111_0_121
ab -n 1000000 -c 590 http://10.xxx.1.136:8800/thread/request/defered/merge?applianceCode=211106233032263
以ä¸ç»è¿æµè¯ï¼æå¡ç«¯æ¥å£æ¯æçæ大QPS为10000å¤ä¸ç¹ï¼è¯·æ±å并åï¼è°ç¨æ¹ç«¯è°ç¨çQPSè¾¾å°å°è¿20000.
â
请æ±å并çå¼ç«¯ï¼
- å¯ç¨è¯·æ±çææ¬æ¯æ§è¡å®é é»è¾ä¹åå¢å ç延è¿ï¼è¿ææ¹éæ¥å£å¸¦æ¥ç延æ¶(å¯ä»¥å¿½ç¥ä¸è®¡)ã
- å¦æå¹³åä» éè¦5毫ç§çæ§è¡æ¶é´ï¼æ¾å¨ä¸ä¸ª10毫ç§çåä¸æ¬¡æ¹å¤ççå并åºæ¯ä¸ï¼åå¨æåçæ åµä¸ï¼æ§è¡æ¶é´å¯è½ä¼å为15毫ç§ã(ä¸éåä½å»¶è¿çRPCåºæ¯ãä¸éåä½å¹¶ååºæ¯)
使ç¨åºæ¯ ï¼
- å¦æå¾å°æè¶ è¿1æ2个请æ±ä¼å¹¶åå¨ä¸èµ·ï¼å没æå¿ è¦ç¨ã
- ä¸ä¸ªç¹å®çæ¥è¯¢åæ¶è¢«å¤§é使ç¨ï¼å¹¶ä¸å¯ä»¥å°å +个çè³æ°ç¾ä¸ªæ¹å¤çå¨ä¸èµ·ï¼é£ä¹å¦æè½æ¥åå¤çæ¶é´åé¿ä¸ç¹ç¹ï¼ç¨æ¥åå°ç½ç»è¿æ¥ï¼è¿æ¯å¼å¾çã(å ¸åå¦:æ°æ®åºãHttpæ¥å£ï¼
ç¸å ³çå¼æºæ¡æ¶ ï¼
å¨SpringCloudçç»ä»¶spring-cloud-starter-netflix-hystrixä¸å·²ç»æå°è£ 好çè½®åHystrixçHystrixCollapseræ¥å®ç°è¯·æ±çå并ï¼ä»¥åå°éä¿¡æ¶èå线ç¨æ°çå ç¨ãè¿ä¸ªç»ä»¶æ¯è¾å¤æï¼ä¹æ´å ¨é¢ï¼æ¯æå¼æ¥ï¼åæ¥ï¼è¶ æ¶ï¼å¼å¸¸ççå¤çæºå¶ã
package com.example.demo.service.merge;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Future;
import java.util.function.Function;
import java.util.stream.Collectors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Component;
import com.example.demo.third.ApplianceSvcService;
import com.example.demo.vo.Appliance;
import com.midea.framework.commons.http.R;
import com.midea.framework.commons.json.JsonUtil;
import com.netflix.hystrix.contrib.javanica.annotation.HystrixCollapser;
import com.netflix.hystrix.contrib.javanica.annotation.HystrixCommand;
import com.netflix.hystrix.contrib.javanica.annotation.HystrixProperty;
@Component
public class ApplianceBatchGetWithHystrixService {
private static final Logger LOGGER = LoggerFactory.getLogger(ApplianceBatchGetWithHystrixService.class);
@Autowired
private ApplianceSvcService applianceSvcService;
@HystrixCollapser(scope = com.netflix.hystrix.HystrixCollapser.Scope.GLOBAL,batchMethod = "mutilGetApplianceByCodes",collapserProperties = {
@HystrixProperty(name = "timerDelayInMilliseconds", value = "7"),
@HystrixProperty(name = "maxRequestsInBatch",value = "80"),
//@HystrixProperty(name = "requestCache.enabled", value = "true")
})
public Future<Appliance> getByCode(Long applianceCode) {
throw new RuntimeException("the method will never be executed");
}
@HystrixCommand(
groupKey = "mutilGetApplianceByCodes",
threadPoolKey = "mutilGetApplianceByCodes",
threadPoolProperties = {
@HystrixProperty(name = "coreSize", value = "50"),
@HystrixProperty(name = "maxQueueSize", value = "2000"),
@HystrixProperty(name = "queueSizeRejectionThreshold", value = "1000")
})
public Collection<Appliance> mutilGetApplianceByCodes(List<Long> applianceCodeList) {
LOGGER.info("mutilGetApplianceByCodes,param:{}", applianceCodeList);
R r = applianceSvcService.mutilGetApplianceByCodes(applianceCodeList);
if (r == null || r.getStatusCode() != HttpStatus.OK.value()) {
LOGGER.error("mutilGetApplianceByCodes error,result:{}", r == null ? null : JsonUtil.toJson(r));
return new ArrayList<>();
}
Map<Long, Appliance> collect = JsonUtil.parseArray(r.getResponseText(), Appliance.class).stream().collect(
Collectors.toMap(Appliance::getApplianceCode, Function.identity(), (key1, key2) -> key2));
List<Appliance> list = new ArrayList<>();
for(Long code : applianceCodeList) {
list.add(collect.get(code));
}
return list;
}
}
ä»åºå±æè·¯æ¥è¯´ï¼å°±æ¯çº¿ç¨ä¹é´çéä¿¡ï¼çº¿ç¨çåæ¢ï¼éåçä¸äºå¹¶åç¼ç¨ç¸å ³çææ¯ã
æ»ç»ä¸æ¥çå ·æç¸åç»æçç¬ç«è¯·æ±æå¦ä¸æ¹å¼å并ï¼
- çå¾ ä¸æ®µæ¶é´æ±éæ¹éæä½
- éè¿å®ç°æ¹éæ§è¡æ¹æ³ï¼æ§è¡æ¹éæä½
- å°æ¹éæä½ç»æä¸è¯·æ±æ å°ï¼æç»è¿å
ä¸ï¼æ°æ®åºç´¢å¼
ç´¢å¼çæ¦å¿µ
ç´¢å¼æ¯å¨Mysqlåå¨å¼æå±å®ç°ç为äºå é对表ä¸æ°æ®è¡çæ£ç´¢èå建çä¸ç§æ好åºçæ°æ®ç»æã
ç´¢å¼çåç±»
MySQLçç´¢å¼å æ¬æ®éç´¢å¼ãå¯ä¸æ§ç´¢å¼ãå ¨æç´¢å¼ãååç´¢å¼ãå¤åç´¢å¼çã
- ä» åè½é»è¾ ä¸è¯´ï¼ç´¢å¼ä¸»è¦æ 4 ç§ï¼åå«æ¯æ®éç´¢å¼ãå¯ä¸ç´¢å¼ã主é®ç´¢å¼ãå ¨æç´¢å¼ã
- æç § ç©çå®ç°æ¹å¼ ï¼ç´¢å¼å¯ä»¥å为 2 ç§ï¼èç°ç´¢å¼åéèç°ç´¢å¼ã
- æç § ä½ç¨å段个æ°è¿è¡ååï¼åæååç´¢å¼åèåç´¢å¼ã
å建索å¼åå
åªäºæ åµéåå建索å¼
- å段çæ°å¼æå¯ä¸æ§çéå¶
- é¢ç¹ä½ä¸º WHERE æ¥è¯¢æ¡ä»¶çå段
- ç»å¸¸ GROUP BY å ORDER BY çå
- UPDATEãDELETE ç WHERE æ¡ä»¶å
- DISTINCT å段éè¦å建索å¼
- å¤è¡¨ JOIN è¿æ¥æä½æ¶ï¼å建索å¼æ³¨æäºé¡¹
- 使ç¨åçç±»åå°çå建索å¼
- 使ç¨å符串åç¼å建索å¼
- åºå度é«(æ£åæ§é«)çåéåä½ä¸ºç´¢å¼
- 使ç¨æé¢ç¹çåæ¾å°èåç´¢å¼ç左侧
- å¨å¤ä¸ªå段é½è¦å建索å¼çæ åµä¸ï¼èåç´¢å¼ä¼äºåå¼ç´¢å¼ï¼å°½éæ©å±ç´¢å¼ï¼ä¸è¦æ°å»ºç´¢å¼ï¼
- éå¶ç´¢å¼çæ°ç®
åªäºæ åµä¸éåå建索å¼
- å¨whereä¸ä½¿ç¨ä¸å°çå段ï¼ä¸è¦è®¾ç½®ç´¢å¼
WHEREæ¡ä»¶ï¼å æ¬GROUP BYãORDER BYï¼éç¨ä¸å°çå段ä¸éè¦å建索å¼ï¼ç´¢å¼çä»·å¼æ¯å¿«éå®ä½ï¼
å¦æèµ·ä¸ å°å®ä½çå段é常 æ¯ä¸éè¦å建索å¼çã
2.æ°æ®éå°ç表æ好ä¸è¦ä½¿ç¨ç´¢å¼
å¦æ表记å½å¤ªå°ï¼æ¯å¦å°äº1000个ï¼é£ä¹å°±ä¸éè¦å»ºç«ç´¢å¼ãç±äºè¡¨è®°å½å¤ªå°ï¼æ¯å¦å»ºç«ç´¢å¼ï¼å¯¹æ¥è¯¢æççå½±å并ä¸å¤§ã
- æ大ééå¤æ°æ®çåä¸ä¸è¦å»ºç«ç´¢å¼(éæ©æ§ä½)
- é¿å 对ç»å¸¸æ´æ°ç表å建è¿å¤çç´¢å¼
- ä¸å»ºè®®ç¨æ åºçå¼ä½ä¸ºç´¢å¼
- å é¤ä¸å使ç¨æè å¾å°ä½¿ç¨çç´¢å¼
- ä¸è¦å®ä¹åä½æéå¤çç´¢å¼
ç´¢å¼ä¼åçç¥
å ³é®è¯ï¼ è¦çç´¢å¼ æå·¦åç¼åå ç´¢å¼ä¸æ¨ åç¼ç´¢å¼ é¿å ç´¢å¼å¤±æ
çç¥1ï¼å°½éèèè¦çç´¢å¼
çç¥2ï¼éµå¾ªæå·¦åç¼å¹é
çç¥3ï¼èå´æ¥è¯¢å段æ¾æå
çç¥4ï¼ä¸å¯¹ç´¢å¼å段è¿è¡é»è¾æä½(å½æ°æä½)
çç¥5ï¼å°½éé¿å select *
çç¥6ï¼Likeæ¥è¯¢ï¼å·¦ä¾§å°½éä¸è¦å %
çç¥7ï¼æ³¨ænull/not null å¯è½å¯¹ç´¢å¼æå½±å
çç¥8ï¼å°½éåå°ä½¿ç¨ä¸çäº
çç¥9ï¼é¿å ç±»å转æ¢
çç¥10ï¼ORå ³é®åå·¦å³å°½éé½ä¸ºç´¢å¼å
ç´¢å¼çæ°æ®ç»æ
èç°ç´¢å¼(èéç´¢å¼ï¼ä¸»é®ç´¢å¼)
èç°ç´¢å¼å°±æ¯æç §æ¯å¼ 表ç主é®æé ä¸é¢B+æ ï¼å¶åèç¹ä¸åæ¾çå°±æ¯æ´å¼ 表çè¡è®°å½æ°æ®ï¼ä¹å°èéç´¢å¼çå¶åèç¹ç§°ä¸ºæ°æ®é¡µãè¿ä¸ªç¹æ§å³å®äºç´¢å¼ç»ç»è¡¨ä¸æ°æ®ä¹æ¯ç´¢å¼çä¸é¨åï¼æ¯å¼ 表åªè½æ¥æä¸ä¸ªèç°ç´¢å¼ã
ããInnodbéè¿ä¸»é®èéæ°æ®ï¼å¦æ没æå®ä¹ä¸»é®ï¼innodbä¼éæ©é空çå¯ä¸ç´¢å¼ä»£æ¿ãå¦æ没æè¿æ ·çç´¢å¼ï¼innodbä¼éå¼çå®ä¹ä¸ä¸ªä¸»é®æ¥ä½ä¸ºèç°ç´¢å¼ã
éèç°ç´¢å¼
å¨innoDBä¸ï¼é¤äºèç°ç´¢å¼ä»¥å¤ï¼å ¶ä½çç´¢å¼é½å¯ä»¥æ为éèç°ç´¢å¼ãåèç°ç´¢å¼ç¸æ¯ï¼è¾ å©ç´¢å¼çå¶åèç¹åæ¾ä¸»é®IDï¼èä¸æ¯æ´æ¡æ°æ®ã éè¿éèç°ç´¢å¼é¦å æ¾å°çæ¯æ°æ®ç主é®å¼ï¼åéè¿ä¸»é®å¼ä»èç°ç´¢å¼æ ä¸æ¥æ¾å¯¹åºçæ°æ®ãä¸å¼ 表å¯ä»¥æå¤ä¸ªéèç°ç´¢å¼ãå¨innodbä¸ä¹ç§°éèç°ç´¢å¼ä¸º è¾ å©ç´¢å¼ä¸ºé主é®ç´¢å¼ï¼äºçº§ç´¢å¼ã
å¤åç´¢å¼(èåç´¢å¼)
å¨å¤ä¸ªåä¸å»ºç«çç´¢å¼ï¼è¿ç§ç´¢å¼å«åå¤åç´¢å¼ã使ç¨èåç´¢å¼å¯ä»¥è¿æ»¤ææ´å¤çæ°æ®ï¼æåç´¢å¼çå½ä¸çã
主é®ç´¢å¼åè¾ å©ç´¢å¼çåºå«
主é®ç´¢å¼åé主é®ç´¢å¼çåºå«æ¯ï¼é主é®ç´¢å¼çå¶åèç¹åæ¾çæ¯ä¸»é®çå¼ï¼è主é®ç´¢å¼çå¶åèç¹åæ¾çæ¯æ´è¡æ°æ®ï¼å ¶ä¸é主é®ç´¢å¼ä¹è¢«ç§°ä¸ºäºçº§ç´¢å¼ï¼è主é®ç´¢å¼ä¹è¢«ç§°ä¸ºèç°ç´¢å¼ã
æ ¹æ®è¿ä¸¤ç§ç»ææ们æ¥è¿è¡ä¸æ¥è¯¢ï¼ççä»ä»¬å¨æ¥è¯¢ä¸æä»ä¹åºå«ã
1ãå¦ææ¥è¯¢è¯å¥æ¯ select * from table where ID = 100,å³ä¸»é®æ¥è¯¢çæ¹å¼ï¼ååªéè¦æç´¢ ID è¿æ£µ B+æ ã
2ãå¦ææ¥è¯¢è¯å¥æ¯ select * from table where k = 1ï¼å³é主é®çæ¥è¯¢æ¹å¼ï¼åå æç´¢kç´¢å¼æ ï¼å¾å°ID=100,åå°IDç´¢å¼æ æç´¢ä¸æ¬¡ï¼è¿ä¸ªè¿ç¨ä¹è¢«ç§°ä¸ºå表ã
SQLä¼ååå
æ ¢SQL ï¼å°±æ¯æ§è¡æçå¾æ ¢çsqlè¯å¥ã
å±å®³ï¼mysqlæå¡å¨çèµæºï¼å¦CPUãIOãå åç æ¯æéçï¼å°¤å ¶å¨é«å¹¶ååºæ¯ä¸éè¦å¿«éçå¤çæ请æ±ï¼å¦åä¸æ¦åºç°æ ¢SQLå°±ä¼é»å¡æå¾å¤æ£å¸¸ç请æ±ï¼é æ大é¢ç§¯ç失败æè è¶ æ¶ã
SQLä¼åç®çï¼æ¯ä¸ºäºæé«æ§è¡æçï¼è¾¾å°å¿«éæ£ç´¢çç®ç ã
为äºå好é«æçSQLï¼æ¥ä¸æ¥æ主è¦ä»ä»¥ä¸å 个æ¹é¢è®²è§£ä¸äºSQLä¼åçç¥è¯ï¼
1ï¼æ¯å¦å»ºç«æ£ç¡®ç/é«æçç´¢å¼
2ï¼æ¯å¦ä½¿ç¨å°ç´¢å¼/失æ没起å°ä½ç¨
ç´¢å¼å¤±ææçæ¯æ²¡æå©ç¨å°ç´¢å¼çäºåæ¥è¯¢è¿è¡æ°æ®è¿æ»¤ã
æ»ç»å 个索å¼å¤±æçåºæ¯(æçåºæ¯æ¯éè¦ä¼åå¨æ ¹æ®å ·ä½çä¸äºæ§è½ææ å»ä¼°ç®çï¼ä¸æ¯ä¸æä¸åç)ï¼
ç»åç´¢å¼å·¦å¹é åå
åçéå¼è½¬æ¢
ç»åç´¢å¼ï¼in + order by ä¼é»ææåºç¨ç´¢å¼
èå´æ¥è¯¢ä¼é»æç»åç´¢å¼,ç´¢å¼æ¶åå°èå´æ¥è¯¢çç´¢å¼å段è¦æ¾å¨ç»åç´¢å¼çæåé¢ã
å模ç³å¹é 导è´ç´¢å¼å¤±æ
oræ¥è¯¢ï¼æ¥è¯¢æ¡ä»¶é¨åæç´¢å¼ï¼é¨åæ ç´¢å¼ï¼å¯¼è´ç´¢å¼å¤±æã
æ¥è¯¢æ¡ä»¶ä½¿ç¨äºå½æ°è¿ç®ãååè¿ç®çã
使ç¨äº!=ãnot in
éæ©æ§è¿ä½
ascådescæ··ç¨
3ï¼æ¯å¦ä½¿ç¨å°äºé«æçç´¢å¼
SQLä¼åå®æ
以ä¸mysqléç¨ççæ¬å·æ¯ï¼5.6.16ï¼InnoDBåå¨å¼æ
CREATE TABLE `t_employees` (
`emp_no` bigint(20) NOT NULL,
`name` varchar(14) NOT NULL,
`gender` enum('M','F') NOT NULL DEFAULT 'M',
`age` int(11) NOT NULL DEFAULT '0',
`mobile` varchar(32) DEFAULT NULL COMMENT 'å¹´é¾',
`department_id` int(11) NOT NULL DEFAULT '0',
`hire_date` date NOT NULL DEFAULT '1970-01-01',
`birth_date` date NOT NULL DEFAULT '1970-01-01',
`address` varchar(30) DEFAULT NULL,
PRIMARY KEY (`emp_no`),
UNIQUE KEY `mobile` (`mobile`),
KEY `idx_name_age_address` (`name`,`age`,`address`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
å¨è¿è¡å®æä¹åï¼æå ç¨å å¼ B+æ çå¾è®©å¤§å®¶çæä¸ä¸ä»¥ä¸æ¦å¿µï¼
- èç°ç´¢å¼
- éèç°ç´¢å¼
- ç´¢å¼é¡µ
- æ°æ®é¡µ
- å ¨è¡¨æ«æ
- ç´¢å¼å ¨æ«æ
- èåç´¢å¼
- å表
- è¦çç´¢å¼
- æå·¦åç¼åå
- ç´¢å¼ä¸æ¨
- åç¼ç´¢å¼
ä¸ï¼é¿å ç±»åéå¼è½¬æ¢
è¥varcharç±»åå段å¼ä¸å åå¼å·ï¼å¯è½ä¼åçæ°æ®ç±»åéå¼è½¬åï¼èªå¨è½¬æ¢ä¸ºintåï¼ä½¿ç´¢å¼æ æ
æ£ä¾
EXPLAIN SELECT * FROM `t_employees` WHERE mobile='13600000002'
åä¾
EXPLAIN SELECT * FROM `t_employees` WHERE mobile=13600000002;
为ä»ä¹ä¸é¢æ¥è¯¢ä¼ éçå¼æ¯æ°å导è´ç´¢å¼å¤±æï¼å 为å段æ¯å符类åï¼ä¼ åæ¯æ°åï¼mysqlåäºéå¼ç±»å转æ¢ã
ç¥è¯ç¹ï¼EXTENDED + SHOW WARNINGS æ¥çsqlæ§è¡è®¡åè¿åçè¦åä¿¡æ¯
EXPLAIN EXTENDED SELECT * FROM `t_employees` WHERE mobile=13600000002;
SHOW WARNINGS
mysqlè¿æä¸äºå ¶ä»ç转æ¢è§åï¼
- ä¸ä»¥æ°åå¼å¤´çå符串é½å°è½¬æ¢ä¸º0ãå¦'abc'ã'a123bc'ã'abc123'é½ä¼è½¬å为0ï¼
- 以æ°åå¼å¤´çå符串转æ¢æ¶ä¼è¿è¡æªåï¼ä»ç¬¬ä¸ä¸ªå符æªåå°ç¬¬ä¸ä¸ªéæ°åå 容为æ¢ãæ¯å¦'123abc'ä¼è½¬æ¢ä¸º123ï¼'012abc'ä¼è½¬æ¢ä¸º012ä¹å°±æ¯12ï¼'5.3a66b78c'ä¼è½¬æ¢ä¸º5.3ï¼å ¶ä»åçã
å¦ï¼è¿è¡
SELECT CAST('111a' AS UNSIGNED);
è¾åºï¼ 111
select 'a'+'b'='c';
è¾åºï¼1
a+b=cç»æ为1ï¼1å¨MySQLä¸å¯ä»¥ç解为trueï¼å 为'a'+'b'çç»æ为0ï¼cä¹ä¼éå¼è½¬å为0ï¼å æ¤æ¯è¾å ¶å®æ¯ï¼0=0ä¹å°±æ¯trueï¼ä¹å°±æ¯1.
æ¡ä¾ï¼
SELECT '1234781712345555456' = 1234781712345555455;
è¾åºï¼1
æ们åç°ï¼å°ä¸¤ä¸ªä¸åçå符串转å为浮ç¹æ°åï¼ç»ææ¯ä¸æ ·ç ã
å æ¤ï¼å½MySQLéå°å段类åä¸å¹é çæ¶åï¼ä¼è¿è¡åç§éå¼è½¬åï¼ä¸å®è¦å°å¿ï¼æå¯è½å¯¼è´ç²¾åº¦ä¸¢å¤±
SELECT * FROM t_ms_applian WHERE sn = 1234781712345555455;
SELECT * FROM t_ms_applian WHERE appliance_code = '18446744073709551615aaa';
åç°æ们è¾å ¥çæ°ååé¢å¸¦äºå符串ä¾ç¶æ¥è¯¢åºäºè®°å½ï¼åå å°±æ¯ä¸é¢è®²ç转æ¢è§åè¡å¿½ç¥æåé¢çéæ°åå符ã
æ´å¤çè§åè§å®ç½ï¼
https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_cast
https://dev.mysql.com/doc/refman/5.7/en/out-of-range-and-overflow.html
äºï¼èåç´¢å¼-æå·¦åç¼åå
èåç´¢å¼å½ä¸å¿ é¡»éµå¾ªæå·¦åç¼ååãå³SQLè¿æ»¤æ¡ä»¶åæ®µå¿ é¡»ä»ç´¢å¼çæ左侧çåå¼å§å¹é ï¼ä¸è½è·³è¿ç´¢å¼ä¸çå ãä¹å°±æ¯ï¼
- éè¦æ¥è¯¢çååèåç´¢å¼çå顺åºä¸è´( æ左边çå段è¦åºç°å¨æ¥è¯¢æ¡ä»¶ä¸ )
- æ¥è¯¢ä¸è¦è·¨å
æ以ï¼å¨å建èåç´¢å¼æ¶ï¼whereä¸ä½¿ç¨æé¢ç¹çå段æ¾å¨ç»åç´¢å¼çæ左侧ã
æ£ä¾ï¼
whereæ¡ä»¶ä¸å å«äºå¤åç´¢å¼çæå·¦åï¼åä¼å½ä¸ç´¢å¼
explain SELECT * FROM t_employees WHERE NAME='å´æ';
explain SELECT * FROM t_employees WHERE NAME='å´æ' AND age=41;
explain SELECT * FROM t_employees WHERE NAME='å´æ' AND age=41 AND address='ä¿å®å¸';
explain SELECT * FROM t_employees WHERE age=41 AND NAME='å´æ' AND address='ä¿å®å¸';
åä¾ï¼
è·¨åç´¢å¼å¤±æ
å¦æwhereæ¡ä»¶ä¸ä¸å å«èåç´¢å¼çæ左索å¼åæ®µï¼ é£ä¹å°ä¸ä¼èµ°ä»»ä½ç´¢å¼ã
EXPLAIN SELECT * FROM t_employees WHERE age=41 AND address='ä¿å®å¸';
ââ
跨索å¼ååªè½ä½¿ç¨æå·¦ä¾æ¬¡è¿ç»çç´¢å¼ãä»key_lenå¯ä»¥ç¥éåªä½¿ç¨å°äºnameç´¢å¼ã(è¿éUsing index condition表示索å¼ä¸æ¨ï¼åé¢ä¼è®²)
explain SELECT * FROM t_employees WHERE NAME='å´æ' AND address='ä¿å®å¸';
èå´æ¥è¯¢é»æèåç´¢å¼
å¨ä»¥ä¸sqlä¸ï¼èå´æ¥è¯¢ageåé¢çaddressä¸ä¼èµ°ç´¢å¼ï¼éè¿æ§è¡è®¡åç»èéçused_key_partså¯ä»¥åè¯æ们è¿ä¸ç¹ã
ç¥è¯ç¹ï¼FORMAT=json 以jsonæ ¼å¼è¾åºæ§è¡è®¡åæ´æ¹ä¾¿æ¥ç使ç¨å°çç´¢å¼å段
EXPLAIN FORMAT=json SELECT * FROM t_employees WHERE NAME='å´æ' AND age > 40 AND address='ä¿å®å¸';
以ä¸SQLçå¹é è¿ç¨ï¼é¦å å¹é å°nameå段ï¼äºåæ¥æ¾å®ä½å°'å´æ'åï¼åäºåæ¥æ¾å®ä½å°age=40ï¼æææage>40çæ°æ®ååºæ¥ãæ¤æ¶å¹å°çæ°æ®çageå¼æ 40ï¼41ï¼42ï¼ä½æ¯æ¤æ¶addressæ¯æ åºç(åªæç²¾åå®ä½å°ageæ¶åaddressææåº)ï¼æ以ä¸è½å¨B+æ éè¿è¡äºåæ³æ¥æ¾ï¼address使ç¨ä¸å°ç´¢å¼ã
è¾åºï¼
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t_employees",
"access_type": "range",
"possible_keys": [
"idx_name_age_address"
],
"key": "idx_name_age_address",# å®é
使ç¨çç´¢å¼
"used_key_parts": [ # 使ç¨å°çç´¢å¼å
"name",
"age"
],
"key_length": "62",
"rows": 45798,
"filtered": 100,
"index_condition": "((`appliance`.`t_employees`.`name` = 'å´æ') and (`appliance`.`t_employees`.`age` > 40) and (`appliance`.`t_employees`.`address` = 'ä¿å®å¸'))"
}
}
}
éè¿ä»¥ä¸çåºï¼ä½¿ç¨å°çç´¢å¼å为name,age.
æ们ä¹å¯ä»¥æ ¹æ®key_lenæ¨æåºçæçç´¢å¼åã
ç¥è¯ç¹ : éè¿key_lençå¼å¯ä»¥æ¨å¯¼åºå¨èåç´¢å¼ä¸çæçç´¢å¼åã
key_len ç¨äºè¡¨ç¤ºæ¬æ¬¡æ¥è¯¢ä¸ï¼æéæ©çç´¢å¼é¿åº¦æå¤å°åèï¼é常æ们å¯åæ¤å¤æèåç´¢å¼æå¤å°å被éæ©äºã
key_lenç®æ³ï¼
å符串
1) åé¿åº¦nï¼
2) åæ¯å¦ä¸ºç©ºï¼ NULL(+1)ï¼NOT NULL(+0)
3) 表å符éé¿åº¦ï¼ å¦ utf8mb4=4,utf8=3,gbk=2,latin1=1
4) åç±»å为åç¬¦ï¼ å¦ varchar(+2), char(+0)
计ç®å ¬å¼ï¼key_len= åé¿åº¦n * (表å符éé¿åº¦) + 1(Null) + 2(åé¿å)
å¦utf8mb4ç¼ç ï¼
varchar(n) = n*4+(å 许为null ? 1: 0) + 2
char(n) = n*4+(å 许为null ? 1: 0)
æ°å¼ç±»å
1) tinyint: 1åè
2) smallint:2åè
3) int: 4åè
4) bigintï¼8åè
æ¶é´ç±»å
1) date: 3åè
2) timstampï¼4åè
3) datetime:8åè
以ä¸key_len=62ç计ç®å¦ä¸ï¼
nameï¼ charact_set=utf8mb4, varchar(14), not null
ageï¼int, not null
key_len=(14*4+0+2) + (4) = 62
æ以å¾åºnameï¼age使ç¨å°äºç´¢å¼ï¼åé¢çaddress没使ç¨å°ç´¢å¼ã
æ¥è¯¢è®¡åå¯ä»¥åè¯æ们æ´å¤çä¿¡æ¯ï¼å ·ä½è§å®ç½åå®æ¹å客ï¼
å®ç½ï¼https://dev.mysql.com/doc/refman/5.6/en/execution-plan-information.html
å®æ¹å客ï¼https://blogs.oracle.com/mysql/post/mysql-query-optimization-top-3-tips?share_token=DE434EBC-5040-47A0-8BA7-F69807FDA10C&tt_from=copy_link&utm_source=copy_link&utm_medium=toutiao_ios&utm_campaign=client_share%20MySQL%20Query%20Optimization:%20Top%203%20Tips
ç¹æ®æ åµï¼ä¸æ»¡è¶³æå·¦åç¼ååï¼ä½æ¯æ»¡è¶³è¦çç´¢å¼ï¼é¿å äºå表ã
EXPLAIN SELECT `emp_no`,age,`address` FROM t_employees WHERE age=41 AND address='ä¿å®å¸';
èåç´¢å¼çå¼éï¼
å¦ä¾åä¸ï¼å»ºäºä¸ä¸ªèåç´¢å¼(name,age,address)ï¼å®é ç¸å½äºå»ºäº(name),(name,age),(name,age,address)ä¸ä¸ªç´¢å¼ãæ¯å¤ä¸ä¸ªç´¢å¼ï¼é½ä¼å¢å åæä½çå¼éåç£ç空é´çå¼éã
æå·¦åç¼ååä¸ä» ç¨å¨whereä¸ï¼è¿è½ç¨å¨order byä¸ãå¨mysqlä¸ï¼æ两ç§æ¹å¼çææåºç»æï¼
- éè¿æåºç´¢å¼é¡ºåºæ«æç´æ¥è¿åæåºæ°æ®
- filesortæåºï¼å¯¹è¿åçæ°æ®è¿è¡æåº
å 为索å¼çæ°æ®ç»ææ¯B+æ ï¼ç´¢å¼ä¸çæ°æ®æ¯æç §ä¸å®é¡ºåºè¿è¡æåºçï¼æ以å¨æåºæ¥è¯¢ä¸å¦æè½å©ç¨ç´¢å¼ï¼å°±è½é¿å é¢å¤çæåºæä½ãæ§è¡è®¡ååææ¶ï¼Extraæ¾ç¤ºä¸ºUsing indexã
ææä¸æ¯éè¿ç´¢å¼ç´æ¥è¿åæåºç»æçæä½é½æ¯fileSortæåºï¼ä¹å°±æ¯è¯´è¿è¡äºé¢å¤çæåºæä½ãæ¥è¯¢è®¡ååææ¶ï¼Extraæ¾ç¤ºä¸ºUsing filesortãå½åºç°Using filesortæ¶å¯¹æ§è½æ失è¾å¤§ï¼æ以è¦å°½éé¿å using filesortã
å ·ä½order by示ä¾è§ï¼[å «ï¼order by]
ä¸ï¼å©ç¨è¦çç´¢å¼æ¥è¿è¡æ¥è¯¢æä½ï¼é¿å å表
è¦çç´¢å¼ï¼è¦çç´¢å¼ä¸æ¯ä¸ç§ç´¢å¼ç±»åï¼èæ¯ä¸ç§ç´¢å¼çæ¥è¯¢æ¹å¼ã å½æ¥è¯¢æ¡ä»¶ä½¿ç¨äºç´¢å¼ï¼å¹¶ä¸selectçå段é½å¨æ¤ç´¢å¼ä¸å¯ä»¥æ¾å°ï¼ä¹å°±æ¯ä¸é¢ç´¢å¼æ°æ¢æ»¡è¶³äºæ£ç´¢ä¹æ»¡è¶³äºæ¥è¯¢ç»æï¼æ é为äºæ¿å°éè¦çå段èå»å表ãä¸è¬æ们ä¸ä¼åªselectä¸ä¸ªå段ï¼æ以è¦çç´¢å¼ä¸è¬ç¨äºèåç´¢å¼ã
å表ï¼SQLæ¥è¯¢çåï¼è¶ åºäºè¦çç´¢å¼çåèå´ï¼SQLæ§è¡å¼æä¼æ ¹æ®è¾ å©ç´¢å¼å¶åèç¹ä¸åæ¾ç主é®IDï¼å»ä¸»é®ç´¢å¼ä¸æ¥æ¾å¯¹åºçæ°æ®ã
使ç¨ååï¼å°½é使ç¨è¦çç´¢å¼ï¼åå°å¯¹select *ç使ç¨ãä¹ä¸è¦æ¥è¯¢ä¸ä½¿ç¨çå段ï¼å°½éæ¥è¯¢ç»æé½ä»ç´¢å¼å段ä¸è·åï¼ä»èåå°å表æä½ã
è¦çç´¢å¼çä¼ç¹ï¼
å¦æè¦æ¥è¯¢è¾ å©ç´¢å¼ä¸ä¸å å«çå段ï¼éè¦å éåè¾ å©ç´¢å¼ï¼åéåèéç´¢å¼ï¼èå¦æéè¦æ¥è¯¢çå段å¨è¾ å©ç´¢å¼æ ä¸å¯ä»¥ç´æ¥æ¿å°ï¼å°±ä¸éè¦åå»æ¥è¯¢èéç´¢å¼ï¼è¿æ ·çå»äºä¸äºIOæä½ãå¨å®é åºç¨ä¸ï¼è¦çç´¢å¼æ¯æåæ§è½ç主è¦æ段ã
æ¤ç段æèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
说æï¼å¦æä¸æ¬ä¹¦éè¦ç¥é第 11 ç« æ¯ä»ä¹æ é¢ï¼ä¼ç¿»å¼ç¬¬ 11 ç« å¯¹åºçé£ä¸é¡µåï¼ç®å½æµè§ä¸ä¸å°±å¥½ï¼è¿
个ç®å½å°±æ¯èµ·å°è¦çç´¢å¼çä½ç¨ã
æ£ä¾ï¼è½å¤å»ºç«ç´¢å¼çç§ç±»å为主é®ç´¢å¼ãå¯ä¸ç´¢å¼ãæ®éç´¢å¼ä¸ç§ï¼èè¦çç´¢å¼åªæ¯ä¸ç§æ¥è¯¢çä¸ç§æ
æï¼ç¨ explain çç»æï¼extra åä¼åºç°ï¼using indexã
满足è¦çç´¢å¼(æ¥è¯¢çå段å¨è¾ å©ç´¢å¼æ ä¸å¯ä»¥ç´æ¥æ¿å°ï¼ä¸éè¦åå»æ«æèåç´¢å¼æ )
EXPLAIN SELECT `emp_no`,NAME,age,address FROM t_employees WHERE NAME='å´';
满足è¦çç´¢å¼([name,age,address]ç´¢å¼ï¼ä¸é¢æ ¹æ®ageåaddressæ¥è¯¢ï¼æ«æäºæ´ä¸ªç´¢å¼æ ï¼è¯æäºä¸ç¹ï¼æå·¦åç¼ååä¸æ¯ç»å¯¹ç)
EXPLAIN SELECT `emp_no`,age,`address` FROM t_employees WHERE age=41 AND address = 'ä¿å®å¸';
ç»è¿éªè¯ï¼è¦çç´¢å¼ ä¸ä»¥æå·¦åç¼å¼å§ï¼åkey_lençäºå¤åç´¢å¼å个é¿åº¦ä¹åï¼ä¹å°±æ¯è¯´å个å段é½æ¯ç¨å°äºç´¢å¼ã
Using indexï¼ è¡¨ç¤ºå·²ç»ä½¿ç¨äºè¦çç´¢å¼ã
ç´¢å¼æ ä¸å·²ç»å å«emp_no,age,addressï¼åä¸ç¨å次å表ã
éè¦å表ï¼(mobileå段å¨idx_name_age_addressç´¢å¼æ ä¸æ¥è¯¢ä¸å°ï¼éè¦æ ¹æ®idå»æ«æ主é®ç´¢å¼æ ä¸å»æ¥è¯¢)ï¼
EXPLAIN SELECT `emp_no`,name,age,`address`,mobile FROM t_employees WHERE name='å´æ' and age=41;
å ¨è¡¨æ«æï¼1ï¼ä¸æ»¡è¶³æå·¦åç¼åå 2ï¼éè¦æ¥è¯¢çå段ä¹ä¸æ»¡è¶³è¦çç´¢å¼
EXPLAIN SELECT `emp_no`,age,`address`,mobile FROM t_employees WHERE age=41 AND address = 'ä¿å®å¸';
åï¼likeæ¥è¯¢
likeæ¥è¯¢æ¶ååºè¯¥æéé 符%æ¾å¨å³è¾¹ï¼åªæ左边æ¯ä¸ªç¡®å®çå¼æè½ä½¿ç¨ç´¢å¼çæåºæ§æ¥å¿«éå®ä½æ°æ®ã
æ£ä¾ï¼
EXPLAIN SELECT * FROM t_employees WHERE NAME LIKE 'å´%'
åä¾ï¼
EXPLAIN SELECT * FROM t_employees WHERE NAME LIKE '%å´'
æ¤ç段æèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
ã强å¶ã页é¢æ索严ç¦å·¦æ¨¡ç³æè å ¨æ¨¡ç³ï¼å¦æéè¦è¯·èµ°æç´¢å¼ææ¥è§£å³ã
说æï¼ç´¢å¼æä»¶å ·æ B-Tree çå·¦åç¼å¹é ç¹æ§ï¼å¦æ左边çå¼æªç¡®å®ï¼é£ä¹æ æ³ä½¿ç¨æ¤ç´¢å¼ã
ç´¢å¼å¤±æçåå ï¼ è¦æ¥è¯¢çæ°æ®å°±ä¸è½åªå¨äºçº§ç´¢å¼æ éæ¾äºï¼å¾éè¦å表æä½æè½å®ææ¥è¯¢çå·¥ä½ï¼åå ä¸æ¯å·¦æ¨¡ç³å¹é ï¼æ æ³å©ç¨ç´¢å¼æ çæåºæ§æ¥å¿«éå®ä½æ°æ®ï¼æ以å¾å¨äºçº§ç´¢å¼æ éä¸éåï¼è·å主é®å¼åï¼åå°èç°ç´¢å¼æ æ£ç´¢å°å¯¹åºçæ°æ®è¡ï¼è¿æ ·è±è´¹ç代价æ¯å ¨è¡¨æ«ææ´å¤§ãæ以ï¼ä¼åå¨è®¤ä¸ºä¸é¢è¿æ ·çæ¥è¯¢è¿ç¨çææ¬å®å¨å¤ªé«äºï¼æ以ç´æ¥éæ©å ¨è¡¨æ«æçæ¹å¼æ¥æ¥è¯¢æ°æ®ã
ç¹æ®æ åµï¼%åç½®ï¼ä½æ¯æ»¡è¶³è¦çç´¢å¼ï¼é¿å äºå表ãè¿ä¹æ¯ä¸ºä»ä¹ä¸å»ºè®®é便使ç¨select *çåå ä¹ä¸
EXPLAIN SELECT `emp_no`,NAME,age,`address` FROM t_employees WHERE NAME LIKE '%å´'
为ä»ä¹éæ©æ«æäºçº§ç´¢å¼ï¼
å 为äºçº§ç´¢å¼æ çè®°å½ä¸è¥¿å¾å°ï¼å°±åªæ [ç´¢å¼å+主é®å¼]ï¼èèç°ç´¢å¼è®°å½çä¸è¥¿ä¼æ´å¤ï¼æ¯å¦èç°ç´¢å¼ä¸çå¶åèç¹åè®°å½äºä¸»é®å¼ãäºå¡ idãç¨äºäºå¡å mvcc åæ»æé以åææçå©ä½åãåå ä¸ï¼è¿ä¸ªSELECT emp_no,NAME,age,address ä¸ç¨æ§è¡å表æä½ã æä»¥ï¼ MySQL ä¼åå¨è®¤ä¸ºç´æ¥éåäºçº§ç´¢å¼æ è¦æ¯éåèç°ç´¢å¼æ çææ¬è¦å°çå¤ï¼å æ¤ MySQL éæ©äºå ¨æ«æäºçº§ç´¢å¼æ æ¥æ¥è¯¢æ°æ®ã
åç±»çæ åµå¦ï¼
myqlä¼åæåç§æ¥è¯¢çææ¬ï¼æç»ä¼éæ©æä¼çæ¹æ¡å»æ§è¡ãå¦ä¸å 个æ¥è¯¢è¯å¥ï¼æç»é½ä¼éæ©idx_birth_dateç´¢å¼ã
åå ï¼æ«æçæ¯äºçº§ç´¢å¼ãäºçº§ç´¢å¼å¶åèç¹å°ï¼æ¯ä¸ªé¡µåæ¾çè®°å½æ´å¤ï¼è¯»åæ件ç次æ°å°±è¶å°ã
EXPLAIN SELECT emp_no FROM `t_employees` EXPLAIN SELECT emp_no FROM t_employees WHERE `emp_no` IS NOT NULL; EXPLAIN SELECT emp_no FROM t_employees WHERE `birth_date` IS NOT NULL; EXPLAIN SELECT `birth_date` FROM t_employees WHERE `birth_date` IS NOT NULL;
Using indexï¼ è¡¨ç¤ºå·²ç»ä½¿ç¨äºè¦çç´¢å¼ã
type为indexï¼è¡¨ç¤ºæ¯éè¿å ¨æ«æäºçº§ç´¢å¼ç B+ æ çæ¹å¼æ¥è¯¢å°æ°æ®çï¼ä¹å°±æ¯éåäºæ´é¢ç´¢å¼æ ã
äºï¼å计ç®/å½æ°/ååæ¥è¯¢ç´¢å¼å¤±æ
类似以ä¸æä½å°ä¸ä¼ä½¿ç¨ç´¢å¼ï¼ä½ä¸æ¯ç»å¯¹çï¼ä¸è¯»åç´¢å¼è®°å½ææ¬æå ³ç³».
EXPLAIN SELECT * FROM t_employees WHERE mobile !='13600000002';//ææ¬ä¼°ç®é®é¢
EXPLAIN SELECT * FROM t_employees WHERE mobile NOT IN ('13600000002');//è¿éä¸èµ°ç´¢å¼ï¼ä½æ¯not inä¸èµ°ç´¢å¼è¿ä¸ªä¸æ¯ç»å¯¹çï¼åªæ¯ææ¬ä¼°ç®é®é¢
å¦ï¼
EXPLAIN SELECT * FROM t_employees WHERE `department_id` not in( 10001);ä¼èµ°ç´¢å¼
EXPLAIN SELECT * FROM t_employees WHERE emp_no not in( 103730260);//主é®not inä¼èµ°ç´¢å¼
EXPLAIN SELECT * FROM t_employees WHERE left(mobile,8) = '13600000';//å¨ç´¢å¼åä¸åäºæªåæä½ç´¢å¼å¤±å»äºåæ¥ç顺åºæ æ³è¿è¡äºåæ³æ¥æ¾
EXPLAIN SELECT * FROM t_employees WHERE name = 'å´æ' and age+1=32;
ç¹æ®æ åµï¼å¦è¦çç´¢å¼æ/å ¨ç´¢å¼æ æ«æ/ç´¢å¼ä¸æ¨ï¼
EXPLAIN SELECT mobile FROM t_employees WHERE mobile !='13600000002'; //è¿éæ«ææ´ä¸ªç´¢å¼æ å¯ä»¥æ¿å°æ°æ®ï¼æ éå表
以ä¸ä¸¤ç§æ åµæ¹åæä½ç´¢å¼ä¹æ¯ææçï¼ä½æ¯ä¼æªæåé¢çç´¢å¼
EXPLAIN SELECT * FROM t_employees WHERE `name` = 'å´æ' AND age NOT IN (40) AND address= 'é西ç';
EXPLAIN SELECT * FROM t_employees WHERE `name` = 'å´æ' AND age !=40 AND address= 'é西ç';
æ»ç»ï¼æ¥è¯¢æ¡ä»¶ä½¿ç¨not inæ¶ï¼
å¦ææ¯ä¸»é®åèµ°ç´¢å¼ã
å¦ææ¯æ®éç´¢å¼ï¼åç´¢å¼å¯è½å¤±æï¼åå³äºè½è¯»åç´¢å¼è®°å½ææ¬ã
ä¸è¬å»ºè®®ä½¿ç¨intæç¡®åæ°ï¼not in使å¾å½ä¸çèå´ä¸å¯æ§ä»è导è´å ¨è¡¨æ«æã
å ï¼ç´¢å¼(æ¡ä»¶)ä¸æ¨
æ¦å¿µï¼https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
ç´¢å¼æ¡ä»¶ä¸æ¨ (ICP) è¿ä¸ªåªæè¾ å©ç´¢å¼ææçç¹ç¹ï¼å½æªå½ä¸è¦çç´¢å¼ï¼éè¦æ¥è¯¢ä¸è¡ä¸çå ¶ä»çåä¿¡æ¯æ¶ï¼æ§è¡å¼æå¨è¾ å©ç´¢å¼çå±é¢å è¿æ»¤åºç¬¦åæ¡ä»¶çæ°æ®ï¼å°è¿äºæ°æ®å表å°èéç´¢å¼ä¸æ£ç´¢è¡æ°æ®ã å®è½åå°å表æ¥è¯¢æ¬¡æ°ï¼æé«æ¥è¯¢æçã
对äºInnoDB表ï¼ICP ä» ç¨äºäºçº§ç´¢å¼ãICP çç®æ æ¯åå°å ¨è¡è¯»å次æ°ï¼ä»èåå° I/O æä½ãå¯¹äº InnoDBèéç´¢å¼ï¼å®æ´çè®°å½å·²ç»è¯»å ¥InnoDB ç¼å²åºãå¨è¿ç§æ åµä¸ä½¿ç¨ ICP ä¸ä¼åå° I/Oã
ç´¢å¼ä¸æ¨çä¸æ¨å ¶å®å°±æ¯æå°é¨åä¸å±ï¼æå¡å±ï¼è´è´£çäºæ ï¼äº¤ç»äºä¸å±ï¼å¼æå±ï¼å»å¤çã
å¨æ²¡æ使ç¨ICPçæ åµä¸ï¼MySQLçæ¥è¯¢ï¼
- åå¨å¼æ读åç´¢å¼è®°å½ï¼
- æ ¹æ®ç´¢å¼ä¸ç主é®å¼ï¼å®ä½å¹¶è¯»åå®æ´çè¡è®°å½ï¼
- åå¨å¼ææè®°å½äº¤ç»Serverå±å»æ£æµè¯¥è®°å½æ¯å¦æ»¡è¶³WHEREæ¡ä»¶ã
使ç¨ICPçæ åµä¸ï¼æ¥è¯¢è¿ç¨ï¼
- åå¨å¼æ读åç´¢å¼è®°å½ï¼ä¸æ¯å®æ´çè¡è®°å½ï¼ï¼
- å¤æWHEREæ¡ä»¶é¨åè½å¦ç¨ç´¢å¼ä¸çåæ¥åæ£æ¥ï¼æ¡ä»¶ä¸æ»¡è¶³ï¼åå¤çä¸ä¸è¡ç´¢å¼è®°å½ï¼
- æ¡ä»¶æ»¡è¶³ï¼ä½¿ç¨ç´¢å¼ä¸ç主é®å»å®ä½å¹¶è¯»åå®æ´çè¡è®°å½ï¼å表ï¼ï¼
- åå¨å¼ææè®°å½äº¤ç»Serverå±ï¼Serverå±æ£æµè¯¥è®°å½æ¯å¦æ»¡è¶³WHEREæ¡ä»¶çå ¶ä½é¨å
å ·ä½ä¾åï¼
EXPLAIN SELECT * FROM t_employees WHERE NAME LIKE 'å´%' AND age=41;
- â é¦å å¨ idx_name_age_address ç´¢å¼æ ï¼æ¥æ¾ç¬¬ä¸ä¸ªä»¥ 'å´' å¼å¤´çè®°å½å¯¹åºç主é®id
- â¡ æ ¹æ®ä¸»é®idä»ä¸»é®ç´¢å¼æ æ¾å°æ´è¡è®°å½ï¼å¹¶æ ¹æ®ageåå¤æè¿æ»¤ï¼çäº41ççä¸ï¼å¦å丢å¼ãè¿ä¸ªè¿ç¨ç§°ä¸ºå表
- ⢠ç¶åï¼å¨ idx_name_age_address èåç´¢å¼æ ä¸åå³éåï¼æ¾å°ä¸ä¸ä¸ªä¸»é®id
- ⣠åæ§è¡ç¬¬äºæ¥
- ⤠åé¢éå¤æ§è¡ç¬¬ä¸æ¥ã第åæ¥ï¼ç´å°nameä¸æ¯ä»¥ 'å´' å¼å¤´ï¼åç»æ
- ⥠è¿åæææ¥è¯¢ç»æ
ç±äºænameçåç¼å¹é ï¼idx_name_age_address äºçº§ç´¢å¼ä¸ç ageé¨å并没æåæ¥ä½ç¨ã导è´äºå¤§éå表æ¥è¯¢ï¼æ§è½è¾å·®ã
å¨mysqlç5.6çæ¬å¯¹ä»¥ä¸æ åµè¿è¡äºä¼åï¼å¼å ¥äºç´¢å¼ä¸æ¨çæ¦å¿µ
ä¼ååï¼æ§è¡æµç¨ï¼
- â é¦å å¨ idx_name_age_address ç´¢å¼æ ï¼æ¥æ¾ç¬¬ä¸ä¸ªä»¥ 'å´'å¼å¤´çç´¢å¼è®°å½
- â¡ ç¶åï¼å¤æè¿ä¸ªç´¢å¼è®°å½ä¸ç ageæ¯å¦çäº 41ãå¦ææ¯ï¼å表 ååºæ´è¡æ°æ®ï¼ä½ä¸ºåé¢çç»æè¿åï¼å¦æä¸æ¯ï¼å丢å¼
- â¢ å¨ idx_name_age_address èåç´¢å¼æ ä¸åå³éåï¼éå¤ç¬¬äºæ¥ï¼ç´å°nameä¸æ¯ä»¥ 'å´'å¼å¤´ï¼åç»æ
- ⣠è¿åæææ¥è¯¢ç»æ
è·ä¸é¢çè¿ç¨å·®å«ï¼å¨äºå¤æ name以'å´' å¼å¤´ç人çå¹´é¾æ¯å¦çäº 41` æ¾å¨äºéåèåç´¢å¼è¿ç¨ä¸è¿è¡ï¼ä¸éè¦å表å¤æï¼å¤§å¤§éä½äºå表ç次æ°ï¼æåæ§è½ã
æ ¹æ®ä¸é¢çä¸¤å¼ å¾å¯¹æ¯å¯ä»¥çåºï¼å设æ°æ®åºå§ å´ çéåæ10ä¸ï¼åå¦åªæå¼å§ç å´ä¸ï¼å´åæ¯41å²ï¼é£ä¹ç´¢å¼ä¸æ¨å¯ä»¥èç99998次å表ã
Using index conditionï¼ è¡¨ç¤ºä½¿ç¨äºç´¢å¼ä¸æ¨ã
æç §ä»¥ä¸æ¥éª¤å¯ä»¥æ¥çå¯ç¨ç´¢å¼ä¸æ¨åç¦ç¨ç´¢å¼ä¸æ¨
show variables like '%ptimizer_switch%'
show variables where value like '%index_condition_pushdown=on%'
set optimizer_switch="index_condition_pushdown=off";
ç¦ç¨ç´¢å¼ä¸æ¨åï¼æ¥è¯¢
set optimizer_switch="index_condition_pushdown=off";
EXPLAIN SELECT * FROM t_employees WHERE NAME LIKE 'å´%' AND age=41;
æ们å¯ä»¥å¼å¯profilingæ¥ç å¯ç¨ç´¢å¼ä¸æ¨ååç¦ç¨ç´¢å¼ä¸æ¨æ¥è¯¢è¯å¥çæ§è¡æ¶é´åç³»ç»èµæºæ¶èæ åµã
æ¥ç/å¼å¯profilingåè½
æ¥ç/å¼å¯profilingåè½
SHOW VARIABLES LIKE '%profiling%' çä»·äº SELECT @@profiling;
set profiling=1;
profilingè¯æ³
SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
type: ALL --æ¾ç¤ºææçå¼éä¿¡æ¯ BLOCK IO --æ¾ç¤ºåIOç¸å ³å¼é CONTEXT SWITCHES --ä¸ä¸æåæ¢ç¸å ³å¼é CPU --æ¾ç¤ºCPUç¸å ³å¼éä¿¡æ¯ IPC --æ¾ç¤ºåéåæ¥æ¶ç¸å ³å¼éä¿¡æ¯ MEMORY --æ¾ç¤ºå åç¸å ³å¼éä¿¡æ¯ PAGE FAULTS --æ¾ç¤ºé¡µé¢é误ç¸å ³å¼éä¿¡æ¯ SOURCE --æ¾ç¤ºåSource_functionï¼Source_fileï¼Source_lineç¸å ³çå¼éä¿¡æ¯ SWAPS --æ¾ç¤ºäº¤æ¢æ¬¡æ°ç¸å ³å¼éçä¿¡æ¯
æ们åå«ç¦ç¨ç´¢å¼ä¸æ¨åå¯ç¨ç´¢å¼ä¸æ¨ï¼åç° Sending dataè¿ä¸é¡¹è±è´¹çæ¶é´æé¿ç¦ç¨åæ¯å¯ç¨ç40å¤åã
ââ
Sending dataå æ¬ï¼å表æ¥è¯¢ +è¿åç»å®¢æ·ç«¯ç æ¶é´ã
误åº
ç»è¿å¤§éå®è·µè¯æï¼åºç°äºUsing index conditionä¸ä¸å®æ¯ç´¢å¼ä¸æ¨ã
EXPLAIN SELECT * FROM `t_employees` WHERE `gender`='M' AND `birth_date` > '2001-01-01';
EXPLAIN SELECT * FROM `t_employees` WHERE `gender`='M' ;
EXPLAIN SELECT * FROM `t_employees` WHERE `birth_date` > '2001-01-01';
ä¸ï¼å¹é è¿å¤æ°æ®å¯¼è´ç´¢å¼å¤±æ
éæ©æ§è¿ä½ä¼å¯¼è´ç´¢å¼å¤±æãç±äºéè¿äºçº§ç´¢å¼æ¥è¯¢åè¿æå表æ¥è¯¢çå¼éï¼å¦æéè¿è¯¥å段åªè½è¿æ»¤å°éçæ°æ®ï¼æ´ä½ä¸è¿ä¸å¦ç´æ¥æ¥è¯¢æ°æ®è¡¨çæ§è½ï¼åMySQLä¼æ¾å¼è¿ä¸ªç´¢å¼ï¼ç´æ¥ä½¿ç¨å ¨è¡¨æ«æãåºå±ä¼æ ¹æ®è¡¨å¤§å°ãIOå大å°ãè¡æ°çä¿¡æ¯è¿è¡è¯ä¼°å³å®ã
线ä¸è¿è¡äºå¥½ä¹ çç¨åºï¼çªç¶æ¶å°æ ¢sqlè¦åãåæ¥æ¯ä»¥åæ°æ®éå°èµ°äºç´¢å¼ï¼éçæ°æ®è¶æ¥è¶å¤æ ¢æ ¢çåä¸ºå ¨è¡¨æ«æ
é对birth_dateå ç´¢å¼
ALTER TABLE `t_employees` ADD INDEX idx_birth_date (`birth_date`);
EXPLAIN SELECT * FROM `t_employees` WHERE `birth_date`>'1981-01-02' //使ç¨å°ç´¢å¼
EXPLAIN SELECT * FROM `t_employees` WHERE `birth_date`>'1980-01-01'; //å¹é
è®°å½å¤æ¾å¼ä½¿ç¨ç´¢å¼
以ä¸åæ ·çæ¥è¯¢è¯å¥ï¼åªæ¯æ¥è¯¢çåæ°å¼ä¸åï¼å´ä¼åºç°ä¸ä¸ªèµ°ç´¢å¼ï¼ä¸ä¸ªä¸èµ°ç´¢å¼çæ åµãè¿æ¯å 为æ°æ®åºå¼æåç°å ¨è¡¨æ«ææ¯èµ°ç´¢å¼æçæ´é«ï¼å æ¤æ¾å¼äºä½¿ç¨ç´¢å¼ã
å½mysqlåç°éè¿ç´¢å¼æ«æçè¡è®°å½æ°è¶ è¿å ¨è¡¨ç10%-30%æ¶ï¼ä¼åå¨å¯è½ä¼æ¾å¼ä½¿ç¨ç´¢å¼ï¼åä¸ºå ¨è¡¨æ«æã
å¨å·¥ä½ä¸çº¿ä¸éè§çåºæ¯ä¸è¬æ¯ï¼åºç°å¨èå´æ¥è¯¢ï¼ä¸å¡èµ·åæ°æ®éå°ï¼è½å¹é å°çæ°æ®è¾å°å¯ä»¥å½ä¸ç´¢å¼ï¼å½æ°æ®å¢å¤åå 为å¹é å°çæ°æ®å¾å¤è导è´ç´¢å¼å¤±æã
ç¹æ®ï¼æ 论å¦ä½åªè¦æ»¡è¶³è¦çç´¢å¼ï¼å¨äºçº§ç´¢å¼ä¸å°±å¯ä»¥æ¿å°æ°æ®ï¼ä¸éè¦è¿è¡åè¡¨å ¨è¡¨æ«æãå¦ä»¥ä¸ï¼å ¨è¡¨ç1000ä¸æ°æ®çageé½>1ï¼ä½æ¯è¦æ¥è¯¢çå段å®å ¨å¯ä»¥å¨äºçº§ç´¢å¼ä¸æ¾å°ï¼ä¸å¿ å ¨è¡¨æ«æã
EXPLAIN SELECT `emp_no`,age,NAME,address FROM t_employees WHERE age>1;
强å¶è®©ä¼åå¨ä½¿ç¨ç´¢å¼ï¼ä¸é¢å¹é è®°å½å¤æ¾å¼ä½¿ç¨ç´¢å¼ï¼æ们å¯ä»¥ä½¿ç¨FORCE INDEX让ä¼åå¨å¼ºå¶ä½¿ç¨ç´¢å¼
ä¼åååèæ¶æªå¾
EXPLAIN FORMAT=json SELECT * FROM `t_employees` FORCE INDEX(`idx_birth_date`) WHERE `birth_date`>'1980-01-01';
è¾åºä»¥ä¸å 容ï¼æ ¹æ®æ§è¡è®¡åçæ¯ç¨å°äºbirth_dateå段索å¼ï¼
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t_employees",
"access_type": "range",
"possible_keys": [
"idx_birth_date"
],
"key": "idx_birth_date",
"used_key_parts": [
"birth_date"
],
"key_length": "3",
"rows": 1682738,
"filtered": 100,
"index_condition": "(`appliance`.`t_employees`.`birth_date` > '1980-01-01')"
}
}
}
åæ¯å¦å¦ä¸å¦ä¸æ åµä½¿ç¨ is null å is not nullæ¥è¯¢ã
ææ°æ®åºçä¸æ¡æ°æ®çbirth_dateå¼è®¾ç½®ä¸ºnullï¼æ¤æ¶è¿è¡ä»¥ä¸SQLåç°Is nullæ¯å¯ä»¥èµ°ç´¢å¼çã
EXPLAIN SELECT * FROM `t_employees` WHERE `birth_date` IS NULL; --èµ°ç´¢å¼ï¼å 为æ°æ®åºé为nullçæ°æ®å¾å°
EXPLAIN SELECT * FROM `t_employees` WHERE `birth_date` IS NOT NULL;--ä¸èµ°ç´¢å¼
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t_employees",
"access_type": "ref",
"possible_keys": [
"idx_birth_date"
],
"key": "idx_birth_date",
"used_key_parts": [
"birth_date"
],
"key_length": "3",
"ref": [
"const"
],
"rows": 1,
"filtered": 100,
"index_condition": "(`appliance`.`t_employees`.`birth_date` = '0000-00-00')"
}
}
}
å¦ææ们åææ°æ®åºéçbirth_date设置为nullï¼åªä¿çä¸æ¡æ°æ®birth_dateæå¼ï¼æ¤æ¶æ§è¡ä»¥ä¸sqlï¼åç°Is nullä¸èµ°ç´¢å¼ï¼is not nullèµ°äºç´¢å¼ã
éè¿ä¸é¢çä¾åå¯ä»¥çåºï¼å¸¦ç´¢å¼å段使ç¨nullåå¤ææ¯å¦èµ°ç´¢å¼ä¸æ°æ®éæå ³,å½çº³èµ·æ¥å°±æ¯ææ¬é®é¢
æ ¹æ®ä»¥ä¸æ åµï¼æ»ç»ä¸ºï¼
ç´¢å¼(äºçº§ç´¢å¼)æ«æææ¬:
1ã读åç´¢å¼è®°å½ææ¬
2ãåæ¥ä¸»é®ç´¢å¼æ¥æ¾å®æ´æ°æ®ææ¬å³å表
å «ï¼ order by
å 为索å¼æ°æ¯æåºçï¼å¦æorder byè½å¤ä½¿ç¨ç´¢å¼æ°æåºçç¹æ§ï¼é£ä¹å¯ä»¥é¿å äºæ¬¡æåºå¸¦æ¥çæ§è½é®é¢ã
å¨ä½¿ç¨order byå ³é®åçæ¶åï¼å¦æå¾ æåºçå 容ä¸è½ç±æ使ç¨çç´¢å¼ç´æ¥å®ææåºçè¯ï¼é£ä¹mysqlæå¯è½å°±è¦è¿è¡æ件æåºã
æ¤ç段æèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
å¦ææ order by çåºæ¯ï¼è¯·æ³¨æå©ç¨ç´¢å¼çæåºæ§ãorder by æåçå段æ¯ç»åç´¢
å¼çä¸é¨åï¼å¹¶ä¸æ¾å¨ç´¢å¼ç»å顺åºçæåï¼é¿å åºç° file_sort çæ åµï¼å½±åæ¥è¯¢æ§è½ã
æ£ä¾ï¼where a=? and b=? order by c; ç´¢å¼ï¼a_b_c
åä¾ï¼ç´¢å¼å¦æåå¨èå´æ¥è¯¢ï¼é£ä¹ç´¢å¼æåºæ§æ æ³å©ç¨ï¼å¦ï¼WHERE a>10 ORDER BY b; ç´¢å¼ a_b æ
æ³æåºã
ãè¿ä¸ª filesort 并ä¸æ¯è¯´éè¿ç£çæ件è¿è¡æåºï¼èåªæ¯åè¯æ们è¿è¡äºä¸ä¸ªæåºæä½èå·²ãã
å½ç¶ï¼using filesortä¸ä¸å®å¼èµ·mysqlçæ§è½é®é¢ãä½æ¯å¦ææ¥è¯¢æ¬¡æ°é常å¤ï¼é£ä¹æ¯æ¬¡å¨mysqlä¸è¿è¡æåºï¼è¿æ¯ä¼æå½±åçã
1ï¼æåºçå段没æå¨ç´¢å¼ä¸ä¼åºç°Using filesort
EXPLAIN EXTENDED SELECT * FROM t_employees order by `hire_date`;
2ï¼ä¸è½ç±ç´¢å¼ç´æ¥å®ææåº
EXPLAIN EXTENDED SELECT * FROM t_employees where name ='å´æ' order by address;
3ï¼å¯ä»¥ç´æ¥æåº
EXPLAIN EXTENDED SELECT * FROM t_employees WHERE NAME ='å´æ' ORDER BY age;
EXPLAIN EXTENDED SELECT * FROM t_employees WHERE NAME ='å´æ' ORDER BY age,address;
åæ¶ï¼æç §é¿éå·´å·´javaå¼åè§èä¸çãå¦ææ order by çåºæ¯ï¼è¯·æ³¨æå©ç¨ç´¢å¼çæåºæ§ãorder by æåçå段æ¯ç»åç´¢ å¼çä¸é¨åï¼å¹¶ä¸æ¾å¨ç´¢å¼ç»å顺åºçæåï¼é¿å åºç° file_sort çæ åµï¼å½±åæ¥è¯¢æ§è½ããä¹éªè¯äºè¿ä¸ç¹ã
4ï¼ç´¢å¼å¦æåå¨èå´æ¥è¯¢ï¼é£ä¹ç´¢å¼æåºæ§æ æ³å©ç¨ï¼å¦ï¼WHERE a>10 ORDER BY b; ç´¢å¼ a_b æ æ³æåºã
5ï¼ desc åascæ··ç¨æ¶ä¼å¯¼è´ç´¢å¼å¤±æï¼ä¸å»ºè®®æ··ç¨
EXPLAIN SELECT * FROM t_employees WHERE `name` ='å´æ' ORDER BY age DESC,address DESC ;
以ä¸desc åascæ··ç¨ï¼å¯¼è´åºç° file_sort çæ åµ
EXPLAIN SELECT * FROM t_employees WHERE `name` = 'å´æ' ORDER BY age ASC,address DESC ;
æ»ç»order byçä¼åååï¼
- whereæ¡ä»¶åorder by使ç¨ç¸åçç´¢å¼
- order byå段ç顺åºåç´¢å¼ç顺åºä¸è´
- order byå段é½æ¯ååºæè é½æ¯éåº
ä¹ï¼åºåæ§å·®
ç´¢å¼åºå度ä½çå段ä¸è¦å ç´¢å¼ã
对äºéèç°ç´¢å¼ï¼æ¯è¦å表çãåå¦æ 10000 æ¡æ°æ®ï¼å¨ sex å段建ç«ç´¢å¼ï¼æ«æå° 5000个 Fï¼éè¦åå表æ«æ 5000è¡ãè¿ä¸å¦ç´æ¥ä¸æ¬¡å ¨è¡¨æ«æã
æ以ï¼InnoDB å¼æ对äºè¿ç§åºæ¯å°±ä¼æ¾å¼ä½¿ç¨ç´¢å¼ï¼è³äºåºå度å¤ä½å¤å°ä¼æ¾å¼ï¼å¤§è´æ¯æç±»åçæ°æ®å å°æ»ç 30% å·¦å³æ¶ï¼å°±ä¼æ¾å¼ä½¿ç¨è¯¥å段çç´¢å¼ã
EXPLAIN SELECT * FROM `t_employees` WHERE `gender`='F';
why?æ ¹æ®ãä¸ï¼å¹é è¿å¤æ°æ®å¯¼è´ç´¢å¼å¤±æãéªè¯ï¼å¹é è®°å½å¤æ¾å¼ä½¿ç¨ç´¢å¼çï¼ä½æ¯è¿ä¸ªä¾åä¸gender=Få ä¹ç«50%çæ°æ®ï¼æ§è¡è®¡åç¡®æ示使ç¨äºç´¢å¼ã
åï¼ORæ¥è¯¢
å½ORå·¦å³æ¥è¯¢å段åªæä¸ä¸ªæ¯ç´¢å¼ï¼ä¼ä½¿è¯¥ç´¢å¼å¤±æï¼åªæå½ORå·¦å³æ¥è¯¢å段å为索å¼åæ¶ï¼è¿äºç´¢å¼æä¼çæ
æ£ä¾ï¼
EXPLAIN SELECT * FROM `t_employees` WHERE NAME='å´æ' OR `birth_date`='1972-01-01';
EXPLAIN SELECT * FROM `t_employees` WHERE NAME='å´æ' OR `gender`='M';
è¿ä¸¤ä¸ªåç¬çé½æ¯ç¨äºç´¢å¼ï¼ä½æ¯ORæ¥è¯¢ç¡®å®å ¨è¡¨æ«æï¼ å¯ä¸è§£éå°±æ¯genderæ¬èº«æ²¡èµ°ç´¢å¼ï¼ä¸é¢çãåºåæ§å·®ãè¿é大æ¦çåºæ§è¡è®¡åæ误å¤çå¯è½ã
åä¾ï¼ ORçä¸è¾¹æ²¡æ使ç¨ç´¢å¼ãè¿è¡äºå ¨è¡¨æ«æ
EXPLAIN SELECT * FROM `t_employees` WHERE name='å´æ' OR `address`='é西ç'
åä¸ï¼å°½éé¿å select *
é¿éå·´å·´javaå¼åæåä¸æ确说è¿ï¼æ¥è¯¢sqlä¸ç¦æ¢ä½¿ç¨select* ã ä¸å¾ä¸è¦ä½¿ç¨ * ä½ä¸ºæ¥è¯¢çå段å表ï¼éè¦åªäºåæ®µå¿ é¡»æç¡®åæã
EXPLAIN extended SELECT * FROM t_employees where address='西å®å¸';
å¨è¯¥sqlä¸ç¨äºselect *ï¼ä»æ§è¡ç»æçï¼èµ°äºå ¨è¡¨æ«æï¼æ²¡æç¨å°ä»»ä½ç´¢å¼ï¼æ¥è¯¢æçæ¯é常ä½çã
å¦æéè¦æ¥è¯¢çå段åé½æ¯ç¬¦ååå¼çç´¢å¼åï¼åé¿å äºå ¨è¡¨æ«æã
EXPLAIN EXTENDED SELECT `emp_no`,NAME,`address` FROM t_employees WHERE address='西å®å¸';
该sqlè¯å¥è¿æ¬¡èµ°äºå ¨ç´¢å¼æ«æï¼æ¯å ¨è¡¨æ«ææçæ´é«ãå ¶å®è¿éç¨å°äºï¼è¦çç´¢å¼ã
å¦æselectè¯å¥ä¸çæ¥è¯¢åï¼é½æ¯ç´¢å¼åï¼é£ä¹è¿äºå被称为è¦çç´¢å¼ãè¿ç§æ åµä¸ï¼æ¥è¯¢çç¸å ³å段é½è½èµ°ç´¢å¼ï¼ç´¢å¼æ¥è¯¢æçç¸å¯¹æ¥è¯´æ´é«ä¸äºã
å¤æ³¨ï¼ä»¥ä¸ä¼åç°ï¼è½ç¶ä¸æ»¡è¶³æå·¦åç¼ååï¼ä½æ¯æ»¡è¶³äºè¦çç´¢å¼ï¼æ以ä¾ç¶æ¯ä¼ç¨å°ç´¢å¼çã
åäºï¼å¤ç´¢å¼æ··ç¨
å½å¤åç´¢å¼åååç´¢å¼æ··å使ç¨æ¶(AND)ï¼ä¼å 使ç¨ååç´¢å¼(ææ¬æä½ç)
explain SELECT * FROM `t_employees` WHERE NAME ='å´æ' and age=41 and `birth_date`='2001-01-01';
å½ä»¥ä¸æ¥è¯¢ænameå¼æ¹ä¸º'å´ææ'ï¼åèµ°äºèåç´¢å¼
EXPLAIN SELECT * FROM `t_employees` WHERE NAME ='å´ææ' AND age=41 AND `birth_date`='2001-01-01';
两个ååç´¢å¼åæ¶ä½¿ç¨ï¼åªæå ¶ä¸ä¸ä¸ªçæ(ORæè å ¶ä»ç¹æ®çé¤å¤,å¦index merge)ï¼ä¼éå¨ä¼è¯ä¼°ä½¿ç¨åªä¸ªæ¡ä»¶çç´¢å¼çæçæé«
EXPLAIN SELECT * FROM `t_employees` WHERE gender='F' AND `birth_date`='1977-11-29';
åä¸ï¼åç¼ç´¢å¼
ä»ä¹æ¯åç¼ç´¢å¼ï¼
å¦ï¼ç»èº«ä»½è¯çå10ä½æ·»å ç´¢å¼ï¼ç±»ä¼¼è¿ç§ç»æåé¨åä¿¡æ¯æ·»å ç´¢å¼çæ¹å¼å«å åç¼ç´¢å¼ã
为ä»ä¹è¦ç¨åç¼ç´¢å¼
åç¼ç´¢å¼è½ææçåå°ç´¢å¼æ件ç大å°(å ç¨ç空é´æ´å°)ï¼è®©æ¯ä¸ªæå¼é¡µå¯ä»¥ä¿åæ´å¤çç´¢å¼å¼ï¼ä»èæé«ç´¢å¼çæ¥è¯¢æçã
ä½æ¯ï¼åç¼ç´¢å¼ä¹æ缺ç¹ï¼ä¸è½å¨order by æè group by ä¸ä½¿ç¨ï¼ä¹ä¸è½ç¨äºè¦çç´¢å¼ã
ä»ä¹æ åµä¸éå使ç¨åç¼ç´¢å¼
å½å符串æ¬èº«å¯è½æ¯è¾é¿ï¼èä¸ä»åå 个å符串就å¼å§ä¸ç¸åï¼éå使ç¨åç¼ç´¢å¼ï¼
å¨ varchar å段ä¸å»ºç«ç´¢å¼æ¶ï¼å¿ é¡»æå®ç´¢å¼é¿åº¦ï¼æ²¡å¿ è¦å¯¹å ¨å段建ç«ç´¢å¼ï¼æ ¹æ®å®é ææ¬åºå度å³å®ç´¢å¼é¿åº¦ãæèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
é®é¢æ¯ï¼æªåå¤å°ï¼æªåå¾å¤äºï¼è¾¾ä¸å°èçç´¢å¼åå¨ç©ºé´çç®çï¼ æªåå¾å°äºï¼éå¤å 容太å¤ï¼å段çæ£å度(éæ©æ§)ä¼éä½ã
æä¹è®¡ç®ä¸åçé¿åº¦çéæ©æ§ï¼
å¦ï¼å设å¨åå·¥ä½ç³»ç³»ç»éæ10ä¸åå·¥ï¼æ¯ä¸ªåå·¥é½æå¯ä¸çemaié®ç®±ï¼é®ç®±åç¼é½æ¯ä½¿ç¨ç@midea.comã å¨emailä¸å»ºç«å ¨å¼ç´¢å¼ä¼æµªè´¹ä¸å®ç空é´ï¼æ¤æ¶å¯ä»¥èè使ç¨åç¼ç´¢å¼ãåç¼çé¿åº¦åå³äºéæ©åº¦(è¿é举å¾ä¾åå 为æé çemailæ¯å¯ä¸çï¼æ以éæ©åº¦ä¸º1)ï¼
select count(distinct left(email,6)) / count(*) from t_employees;
select count(distinct left(email,7)) / count(*) from t_employees;
select count(distinct left(email,8)) / count(*) from t_employees;
select count(distinct left(email,9)) / count(*) from t_employees;//æ¥è¿äºå
¨è¡¨éæ©åº¦äº,å 为æé çæ°æ®ä½¿ç¨ç主é®æ¼æ¥ç@midea.com
å ¨è¡¨çéæ©åº¦ï¼
select count(distinct email) / count(*) from t_employees;
ç»è¿å¯¹æ¯ï¼å½é¿åº¦ä¸º9çæ¶åéæ©æ§è¾¾å°æé«ï¼æ¤æ¶å»ºç«ç´¢å¼
ALTER TABLE `t_employees` ADD key(email(9));
è¿è¡ä»¥ä¸æ¥è¯¢åç°å½ä¸äºemailç´¢å¼ï¼
EXPLAIN SELECT * FROM `t_employees` WHERE email = '[email protected]';
ç±äºä½¿ç¨äºåç¼ç´¢å¼ï¼åªæ¯å¨è®¾å®çåç¼å符串å¨ç´¢å¼åä¸æåºï¼æ以å¦æ使ç¨order by对æ¤å段æåºï¼å¯è½ä¼æä¸å®çå½±åãå¦å¤å¯è½ä¼é¢å¤çå¢å æ«æè®°å½ç次æ°
对è¦çç´¢å¼çå½±åï¼å 为äºçº§ç´¢å¼ä¸åå¨çä¸æ¯æ´ä¸ªemailå¼ï¼æ以ä¸è½èµ°è¦çç´¢å¼ï¼éè¦å表æ¥è¯¢
EXPLAIN SELECT emp_no,email FROM t_employees WHERE email='[email protected]';
ååï¼æ§è¡è®¡å
å¨å¼åçè¿ç¨ä¸ï¼å½æ们æåä¸æ¡SQLçæ¶åï¼åºå½ç¬¬ä¸æ¶é´ç¡®ä¿SQLçæ§è¡æçï¼èä¸æ¯çå°ä¸çº¿åºç°æ ¢SQLè¦æè äºæ æåç¥åè§ã
æ们第ä¸æ¶é´æ³å°çå°±æ¯ä½¿ç¨SQLæ§è¡è®¡åæ¥çSQLæ§è¡æ¯å¦å½ä¸ç´¢å¼ï¼æ¯å¦è¿è¡äºç´¢å¼è¦çï¼è¿æ¯æ¯å ¨è¡¨æ«æçï¼è¿äºé½å¯ä»¥éè¿æ§è¡è®¡åå»åé¦ã
MySQLæ°æ®åºçæ§è¡è®¡åå¯ä»¥éè¿explainå ³é®åæ¥çï¼ä½¿ç¨explainå¯ä»¥æ¥çSELECT,DELETE,INSERT,REPLACE,UPDATEè¯å¥çæ§è¡è®¡åã对äºSELECTè¯å¥ï¼è¿å¯ä»¥ä½¿ç¨SHOW WARNINGSæ¥çé¢å¤çæ§è¡è®¡åä¿¡æ¯ ã
常ç¨å ³é®å explainçä½ç¨çä»·äº DESCRIBE | DESC
å®ç½ææ¡£ï¼https://dev.mysql.com/doc/refman/5.6/en/using-explain.html
æ常使ç¨çå¦ï¼
-- ä»¥è¡¨æ ¼æ ¼å¼è¾åºæ§è¡è®¡åï¼é»è®¤æ¹å¼
EXPLAIN sql_stmt
-- 以jsonæ ¼å¼è¾åºæ§è¡è®¡å
EXPLAIN FORMAT=JSON sql_stmt
å¦ï¼
explain FORMAT=JSON select * from `t_employees` where mobile='13600000002';
æ§è¡è®¡åå ³é®å解éï¼
1.typeï¼èæ¥ç±»åãä¸é¢ç»åºåç§èæ¥ç±»å,æç §ä»æ佳类åå°æåç±»åè¿è¡æåº:ï¼éç¹çref,rang,indexï¼
systemï¼è¡¨åªæä¸è¡è®°å½ï¼çäºç³»ç»è¡¨ï¼ï¼è¿æ¯constç±»åçç¹ä¾ï¼å¹³æ¶ä¸ä¼åºç°ï¼å¯ä»¥å¿½ç¥ä¸è®¡ constï¼è¡¨ç¤ºéè¿ç´¢å¼ä¸æ¬¡å°±æ¾å°äºï¼constç¨äºæ¯è¾primary key æè uniqueç´¢å¼ãå 为åªéå¹é ä¸è¡æ°æ®ï¼ææå¾å¿«ãå¦æå°ä¸»é®ç½®äºwhereå表ä¸ï¼mysqlå°±è½å°è¯¥æ¥è¯¢è½¬æ¢ä¸ºä¸ä¸ªconst eq_refï¼å¯ä¸æ§ç´¢å¼æ«æï¼å¯¹äºæ¯ä¸ªç´¢å¼é®ï¼è¡¨ä¸åªæä¸æ¡è®°å½ä¸ä¹å¹é ã常è§äºä¸»é® æ å¯ä¸ç´¢å¼æ«æã refï¼éå¯ä¸æ§ç´¢å¼æ«æï¼è¿åå¹é æ个åç¬å¼çææè¡ãæ¬è´¨æ¯ä¹æ¯ä¸ç§ç´¢å¼è®¿é®ï¼å®è¿åææå¹é æ个åç¬å¼çè¡ï¼ç¶èä»å¯è½ä¼æ¾å°å¤ä¸ªç¬¦åæ¡ä»¶çè¡ï¼æ以å®åºè¯¥å±äºæ¥æ¾åæ«æçæ··åä½ã rangeï¼åªæ£ç´¢ç»å®èå´çè¡ï¼ä½¿ç¨ä¸ä¸ªç´¢å¼æ¥éæ©è¡ãkeyåæ¾ç¤ºä½¿ç¨äºé£ä¸ªç´¢å¼ãä¸è¬å°±æ¯å¨whereè¯å¥ä¸åºç°äºbettweenã<ã>ãinççæ¥è¯¢ãè¿ç§ç´¢å¼åä¸çèå´æ«ææ¯å ¨ç´¢å¼æ«æè¦å¥½ãåªéè¦å¼å§äºæ个ç¹ï¼ç»æäºå¦ä¸ä¸ªç¹ï¼ä¸ç¨æ«æå ¨é¨ç´¢å¼ã indexï¼Full Index Scanï¼indexä¸ALLåºå«ä¸ºindexç±»ååªéåç´¢å¼æ ãè¿é常为ALLåï¼åºä¸ºç´¢å¼æ件é常æ¯æ°æ®æ件å°ãï¼Indexä¸ALLè½ç¶é½æ¯è¯»å ¨è¡¨ï¼ä½indexæ¯ä»ç´¢å¼ä¸è¯»åï¼èALLæ¯ä»ç¡¬ç读åï¼ ALLï¼Full Table Scanï¼éåå ¨è¡¨ä»¥æ¾å°å¹é çè¡ããã
possible_keysï¼å¨è¯¥æ¥è¯¢ä¸ï¼MySQLå¯è½ä½¿ç¨çç´¢å¼ï¼å¦ææ¤åæ¯NULLï¼å没æç¸å ³çç´¢å¼ï¼å¨è¿ç§æ åµä¸ï¼éè¦æ£æ¥WHEREåå¥ï¼ä»¥ç¡®å®æ¯å¦éåå建索å¼
keyï¼MySQLå®é 使ç¨çç´¢å¼ãå¨å¤§å¤æ°æ åµä¸ï¼keyä¸çå¼é½å¨possible_keyéé¢ï¼ä½ä¹ä¼åºç°possible_keyä¸åå¨è¯¥å¼ï¼ä½keyéé¢åå¨çæ
key_lenï¼è¯¥åæMySQLå³å®ä½¿ç¨çç´¢å¼é¿åº¦ã该å¼ä½ç°äºå¨ä½¿ç¨å¤åç´¢å¼çæ¶åï¼ä½¿ç¨äºå¤åç´¢å¼çåé¢åªå 个å(éè¦æ ¹æ®å段é¿åº¦è®¡ç®)ï¼å¦ækeyå为NULLï¼å该åä¹ä¸ºNULLãç±äºkeyåå¨çæ ¼å¼åå ï¼å¯ä»¥ä¸ºNULLçåçkeyé¿åº¦æ¯NOT NULLçåé¿åº¦å¤§1ã
rowsï¼MySQLæ¥è¯¢éè¦éåçè¡æ°ï¼å¯¹äºinnodb表ï¼å¯è½å¹¶ä¸æ»æ¯åç¡®ç
Extraï¼
Using index conditionï¼ ä»£è¡¨ä½¿ç¨äºçº§ç´¢å¼ä¸å¤è¿è¦å表ï¼ä½å表ä¹åä¼è¿æ»¤æ¤äºçº§ç´¢å¼è½è¿æ»¤çwhereæ¡ä»¶ (表示使ç¨äºç´¢å¼ä¸æ¨)
Using indexï¼ æ¥è¯¢çå被索å¼è¦çï¼å¹¶ä¸whereçéæ¡ä»¶æ¯ç´¢å¼çæ¯å导å ï¼è¡¨ç¤º 使ç¨è¦çç´¢å¼ï¼ä¸ç¨å表 ï¼
Using whereï¼ ä»£è¡¨æ°æ®åºå¼æè¿åç»æåmysql serverè¿ä¼å次çéã
1ï¼æ¥è¯¢çåæªè¢«ç´¢å¼è¦çï¼whereçéæ¡ä»¶éç´¢å¼çå导å
2ï¼ æ¥è¯¢çåæªè¢«ç´¢å¼è¦çï¼whereçéæ¡ä»¶éç´¢å¼å
using where æå³çéè¿ç´¢å¼æè 表æ«æçæ¹å¼è¿ç¨whereæ¡ä»¶çè¿æ»¤ãä¹å°±è¯´æ¯æ²¡æå¯ç¨çç´¢å¼æ¥æ¾ï¼å½ç¶è¿éä¹è¦èèç´¢å¼æ«æ+å表ä¸è¡¨æ«æç代价ãè¿éçtypeé½æ¯allï¼è¯´æMySQLè®¤ä¸ºå ¨è¡¨æ«ææ¯ä¸ç§æ¯è¾ä½ç代价ã
using index,using whereï¼
Using filesortï¼
1ãMysqlæ¯æ两ç§æ¹å¼çæåºfileSortåindexï¼Using indexæ¯æMysqlæ«æç´¢å¼æ¬èº«å®ææåºãindexæçé«ï¼filesortæçä½ã
2ãorder by满足两ç§æ åµä¼ä½¿ç¨Using indexã
1ï¼order byè¯å¥ä½¿ç¨ ç´¢å¼æå·¦ååã
2ï¼ä½¿ç¨whereåå¥ä¸order byåå¥ æ¡ä»¶ç»å满足æå·¦åç¼ååã
3ãå°½éå¨ç´¢å¼åä¸å®ææåºï¼éµå¾ªç´¢å¼å»ºç«æ¶çæå·¦åç¼æ³åã
4ã使ç¨order byçæ¡ä»¶ä¸å¨ç´¢å¼åä¸ï¼å°±ä¼äº§çUsing filesortã
5ãè½ç¨è¦çç´¢å¼å°±å°½é使ç¨è¦çç´¢å¼ã
6ãgroup byä¸order by类似ï¼å®è´¨æ¯çº¿æåºååç»ï¼éµå¾ªåååç¼ååãå¯¹äº group by çä¼åå¦æä¸éè¦æåºå¯ä»¥å order by nullç¦æ¢æåºã注æï¼whereé«äºhaving ï¼è½åå¨whereä¸çæ¡ä»¶å°±ä¸è¦å¨having ä¸éå®äºã
åäºï¼ç产æ°æ®åºç¸å ³é®é¢
ä¸ï¼å¼ååå¦åçmybatiså¨ææ¡ä»¶å¤æï¼è°ç¨æ¹æ²¡æä¼ éä»»ä½åæ°å¯¼è´select没任ä½æ¡ä»¶æ¥è¯¢å ¨è¡¨ï¼æç»æå¡OOM
<select id="queryUserByCondition" parameterType="com.midea.iot.svc.user.entity.vo.UserVo"
resultType="com.midea.iot.svc.user.entity.UserAll">
select
id,owner_app_id,src_app_id,nick_name,password,mobile,email,address,account_status,update_time,register_time,
signature,profile_pic_url,sex,phone,age,uid from t_ms_user where 1=1
<if test="uid != null and uid != ''">
and uid=#{uid}
</if>
<if test="mobile != null and mobile != '' and ownerAppId != null and ownerAppId != ''">
and mobile = #{mobile,jdbcType=VARCHAR} and owner_app_id=#{ownerAppId,jdbcType=SMALLINT}
</if>
<if test="mobile != null and mobile != '' and ownerAppId == null">
and mobile = #{mobile,jdbcType=VARCHAR} and owner_app_id is null
</if>
</select>
ç»è®ºï¼éè¦å¨ä¸å¡ä»£åæ°æ ¡éª
äºï¼ä½¿ç¨mybatis plusæ¡æ¶ï¼åæ°ç±»ååå®ä½å段ä¸ä¸è´ï¼sqlæ¡ä»¶å¼åå®é æ°æ®åºå段类åä¸ä¸è´ï¼ï¼åçäºç±»å转æ¢ï¼å¯¼è´ç´¢å¼å¤±æã
@Data
@TableName("t_ms_door_pwd")
public class DoorPwd implements Serializable {
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@TableField("nodeId")
private String nodeId;
}
private List<DoorPwd> getDoorPwd(Long nodeid) {
return lambdaQuery().eq(DoorPwd::getNodeId, nodeid).list();
}
ä¸ï¼æ¥è¯¢åºå¤§éçæ°æ®
CREATE TABLE `t_ms_appliance_remind_switch` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`appliance_code` bigint(16) unsigned DEFAULT '0' COMMENT '家çµcode',
`remind_code` varchar(16) DEFAULT NULL COMMENT 'æé代ç ',
`ENABLE` tinyint(4) NOT NULL COMMENT 'æéå¼å
³\r\n0ï¼å
³ 1ï¼å¼',
`user_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_appliance_code_remind_code` (`appliance_code`,`user_id`,`remind_code`),
KEY `tmsapplianceremindswitch_ix2` (`remind_code`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=529 DEFAULT CHARSET=utf8mb4 COMMENT='设å¤æéå¼å
³è¡¨'
å 为ç«å¨ä¸å¡æ¹è¿ä¸ªéå¿ é¡»å段ï¼æ°æ®åºåå¨700ä¸+ appliance_code=0çæ°æ®ãæ¬èº«é»è®¤ä¸º0ä¹æ²¡å¥å¤§æ¯ç ï¼ä¹æ²¡äººå¨æè¿ä¸ªã
ç´å°æä¸å¤©ï¼çº¿ä¸æå¡æ¥å£å¤§éè¶ æ¶ï¼æç»OOMï¼å®ä½å°çé®é¢æ¯ï¼å好æ个设å¤çcode为0ï¼ä¸å¡ä¸æ ¹æ®è®¾å¤codeå»æ¥è¯¢ä¸é¢ç表çæ¶åæ¥è¯¢åº700ä¸æ¡æ°æ®ï¼è¿ç»æ¥è¯¢å¤æ¬¡å¯¼è´æ´ä¸ªæå¡OOMã
åï¼è¿è¡¨æ¥è¯¢éè¦å å«å(é¿éè§è)
说æï¼å¯¹å¤è¡¨è¿è¡æ¥è¯¢è®°å½ãæ´æ°è®°å½ãå é¤è®°å½æ¶ï¼å¦æ对æä½å没æéå®è¡¨çå«åï¼æ表åï¼ï¼å¹¶ä¸æä½åå¨å¤ä¸ªè¡¨ä¸åå¨æ¶ï¼å°±ä¼æå¼å¸¸ã
æ£ä¾ï¼select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
åä¾ï¼å¨æä¸å¡ä¸ï¼ç±äºå¤è¡¨å ³èæ¥è¯¢è¯å¥æ²¡æå 表çå«åï¼æ表åï¼çéå¶ï¼æ£å¸¸è¿è¡ä¸¤å¹´åï¼æè¿å¨
æ个表ä¸å¢å ä¸ä¸ªååå段ï¼å¨é¢åå¸ç¯å¢åæ°æ®åºåæ´åï¼çº¿ä¸æ¥è¯¢è¯å¥åºç°åº 1052 å¼å¸¸ï¼Column 'name' in field list is ambiguousã
ambiguous è±[æmËbɪɡjuÉs] [ 模棱两å¯ç; å«æ··ä¸æ¸ ç; ä¸æç¡®ç ]
åå ï¼æ»ç»
å好SQLä¼å大æ¦æ»ä»¥ä¸ä¸ä¸ªæ¹é¢å ¥æï¼
SQLè¦æç´¢å¼(建ç«æ£ç¡®çç´¢å¼)
ç´¢å¼è¦å¯ç¨ï¼é¿å ç´¢å¼å¤±æï¼
é«æï¼è¦çç´¢å¼ãç´¢å¼çéæ©æ§ï¼
åï¼çº¿ç¨æ±
éè¿æ¬ç« ï¼å°ä¼äºè§£å°ä»¥ä¸ç¥è¯ï¼
ä¸ï¼çº¿ç¨æ± ç好å¤
äºï¼å¦ä½å建线ç¨æ±
ä¸ï¼åºå±æ¯å¦ä½å®ç°çï¼æ ¸å¿åæ°çä½ç¨
åï¼çº¿ç¨æ± æ¯å¦ä½å®ç°çº¿ç¨å¤ç¨ç
äºï¼å¦ä½é 置线ç¨æ± åæ°
å ï¼çº¿ç¨æ± æç»çç¥ä»¥åå¨å·¥ä½ä¸ä½¿ç¨ä¸å½é æçç产äºæ
ä¸ï¼çº¿ç¨æ± çæ§ä»¥ååæ°å¨æè°æ´çç¥
å «ï¼çº¿ç¨æ± é离
ä¹ï¼ä¸ºä»ä¹ä¸æ¨è使ç¨éææ¹æ³æ¹å¼å建线ç¨
åï¼çº¿ç¨çç¶æ
å¨æå¡å¨å¼åé¢åï¼æ们ç»å¸¸ä¼ä¸ºæ¯ä¸ªè¯·æ±åé ä¸ä¸ªçº¿ç¨å»å¤çï¼ä½æ¯çº¿ç¨çå建éæ¯ãè°åº¦é½ä¼å¸¦æ¥é¢å¤çå¼éï¼çº¿ç¨å¤ªå¤ä¹ä¼å¯¼è´ç³»ç»æ´ä½æ§è½ä¸éãå¨è¿ç§åºæ¯ä¸ï¼æ们é常ä¼æåå建è¥å¹²ä¸ªçº¿ç¨ï¼éè¿çº¿ç¨æ± æ¥è¿è¡ç®¡çãå½è¯·æ±å°æ¥æ¶ï¼åªéä»çº¿ç¨æ± éä¸ä¸ªçº¿ç¨å»æ§è¡å¤çä»»å¡å³å¯ã
æ± åææ¯ç使ç¨åæ± å¤§å°ç设置ï¼å æ¬HTTP请æ±æ± ã线ç¨æ± ï¼èèCPUå¯éåè¿æ¯IOå¯éåè®¾ç½®æ ¸å¿åæ°ï¼ãæ°æ®åºåRedisè¿æ¥æ± ç ã
线ç¨æ± ç好å¤
线ç¨æ¯ç¨ç¼ºèµæºï¼å¦æ被æ éå¶çå建ï¼ä¸ä» ä¼æ¶èç³»ç»èµæºï¼è¿ä¼éä½ç³»ç»ç稳å®æ§ï¼åçç使ç¨çº¿ç¨æ± 对线ç¨è¿è¡ç»ä¸åé ãè°ä¼åçæ§ï¼æ以ä¸å¥½å¤: 1ãéä½èµæºæ¶èï¼ éè¿éå¤å©ç¨å·²å建ç线ç¨éä½çº¿ç¨å建åéæ¯é æçæ¶èã 2ãæé«ååºéåº¦ï¼ å½ä»»å¡å°è¾¾æ¶ï¼ä»»å¡å¯ä»¥ä¸éè¦ççå°çº¿ç¨å建就è½ç«å³æ§è¡ã 3ãæé«çº¿ç¨çå¯ç®¡çæ§ï¼ 线ç¨æ¯ç¨ç¼ºèµæºï¼å¦ææ éå¶çå建ï¼ä¸ä» ä¼æ¶èç³»ç»èµæºï¼è¿ä¼éä½ç³»ç»ç稳å®æ§ï¼ä½¿ç¨çº¿ç¨æ± å¯ä»¥è¿è¡ç»ä¸çåé ï¼è°ä¼åçæ§ã
å¦ä½å建线ç¨æ±
Java1.5ä¸å¼å ¥çExecutoræ¡æ¶æä»»å¡çæ交åæ§è¡è¿è¡è§£è¦ï¼åªéè¦å®ä¹å¥½ä»»å¡ï¼ç¶åæ交ç»çº¿ç¨æ± ï¼èä¸ç¨å ³å¿è¯¥ä»»å¡æ¯å¦ä½æ§è¡ã被åªä¸ªçº¿ç¨æ§è¡ï¼ä»¥åä»ä¹æ¶åæ§è¡ã
以ä¸æ¯jdkä¸çº¿ç¨æ± çæ§è¡æµç¨å¾ã
æ们å¨å·¥ä½ä¸æ常ç¨çå°±æ¯ä½¿ç¨ThreadPoolExecutorå»å建线ç¨æ± ï¼å ·ä½ä»£ç å¦ä¸ï¼è¿éä¼æ ¹æ®é¨åæ ¸å¿æºç æ¥è®²çº¿ç¨æ± å·¥ä½åçï¼ï¼
package java.util.concurrent;
.....
public ThreadPoolExecutor(int corePoolSize,
int maximumPoolSize,
long keepAliveTime,
TimeUnit unit,
BlockingQueue<Runnable> workQueue) {
this(corePoolSize, maximumPoolSize, keepAliveTime, unit, workQueue,
Executors.defaultThreadFactory(), defaultHandler);
}
corePoolSize
线ç¨æ± ä¸çæ ¸å¿çº¿ç¨æ°ï¼å½æ交ä¸ä¸ªä»»å¡æ¶ï¼çº¿ç¨æ± å建ä¸ä¸ªæ°çº¿ç¨æ§è¡ä»»å¡ï¼ç´å°å½å线ç¨æ°çäºcorePoolSizeï¼å¦æå½å线ç¨æ°ä¸ºcorePoolSizeï¼ç»§ç»æ交çä»»å¡è¢«ä¿åå°é»å¡éåä¸ï¼çå¾ è¢«æ§è¡ï¼å¦ææ§è¡äºçº¿ç¨æ± çprestartAllCoreThreads()æ¹æ³ï¼çº¿ç¨æ± ä¼æåå建并å¯å¨æææ ¸å¿çº¿ç¨ã
maximumPoolSize
线ç¨æ± ä¸å 许çæ大线ç¨æ°ãå¦æå½åé»å¡éå满äºï¼ä¸ç»§ç»æ交任å¡ï¼åå建æ°ç线ç¨æ§è¡ä»»å¡ï¼åææ¯å½å线ç¨æ°å°äºmaximumPoolSizeï¼
keepAliveTime
线ç¨ç©ºé²æ¶çåæ´»æ¶é´ï¼å³å½çº¿ç¨æ²¡æä»»å¡æ§è¡æ¶ï¼ç»§ç»åæ´»çæ¶é´ï¼é»è®¤æ åµä¸ï¼è¯¥åæ°åªå¨çº¿ç¨æ°å¤§äºcorePoolSizeæ¶ææç¨ï¼
workQueue
ç¨æ¥ä¿åçå¾ è¢«æ§è¡çä»»å¡çé»å¡éåï¼ä¸ä»»å¡å¿ é¡»å®ç°Runableæ¥å£ï¼å¨JDKä¸æä¾äºå¦ä¸é»å¡éåï¼ 1ãArrayBlockingQueueï¼åºäºæ°ç»ç»æçæçé»å¡éåï¼æFIFOæåºä»»å¡ï¼ 2ãLinkedBlockingQueneï¼åºäºé¾è¡¨ç»æçé»å¡éåï¼æFIFOæåºä»»å¡ï¼ååéé常è¦é«äºArrayBlockingQueneï¼ 3ãSynchronousQueneï¼ä¸ä¸ªä¸åå¨å ç´ çé»å¡éåï¼æ¯ä¸ªæå ¥æä½å¿ é¡»çå°å¦ä¸ä¸ªçº¿ç¨è°ç¨ç§»é¤æä½ï¼å¦åæå ¥æä½ä¸ç´å¤äºé»å¡ç¶æï¼ååéé常è¦é«äºLinkedBlockingQueneï¼ 4ãpriorityBlockingQueneï¼å ·æä¼å 级çæ çé»å¡éåï¼
threadFactory
å建线ç¨çå·¥åï¼éè¿èªå®ä¹ç线ç¨å·¥åå¯ä»¥ç»æ¯ä¸ªæ°å»ºç线ç¨è®¾ç½®ä¸ä¸ªå ·æè¯å«åº¦ç线ç¨åã
handler
线ç¨æ± ç饱åçç¥ï¼å½é»å¡éå满äºï¼ä¸æ²¡æ空é²çå·¥ä½çº¿ç¨ï¼å¦æ继ç»æ交任å¡ï¼å¿ é¡»éåä¸ç§çç¥å¤ç该任å¡ï¼çº¿ç¨æ± æä¾äº4ç§çç¥ï¼ 1ãAbortPolicyï¼ç´æ¥æåºå¼å¸¸ï¼é»è®¤çç¥ï¼ 2ãCallerRunsPolicyï¼ç¨è°ç¨è æå¨ç线ç¨æ¥æ§è¡ä»»å¡ï¼ 3ãDiscardOldestPolicyï¼ä¸¢å¼é»å¡éåä¸é æåçä»»å¡ï¼å¹¶æ§è¡å½åä»»å¡ï¼ 4ãDiscardPolicyï¼ç´æ¥ä¸¢å¼ä»»å¡ï¼ å½ç¶ä¹å¯ä»¥æ ¹æ®åºç¨åºæ¯å®ç°RejectedExecutionHandleræ¥å£ï¼èªå®ä¹é¥±åçç¥ï¼å¦è®°å½æ¥å¿ææä¹ ååå¨ä¸è½å¤ççä»»å¡ã
poll.allowCoreThreadTimeOut æ¯å¦å è®¸æ ¸å¿çº¿ç¨ç©ºé²è¶ æ¶ååæ¶
prestartAllCoreThreadsï¼ å¨çº¿ç¨æ± å建ï¼ä½è¿æ²¡ææ¥æ¶å°ä»»ä½ä»»å¡çæ åµä¸ï¼å è¡å建符åcorePoolSizeåæ°å¼ç线ç¨æ° ã
å¦æéè¦çæºç å¯ä»¥executeæ¹æ³å ¥æï¼æ¤æ¹æ³ä¸é¢çä¸æ¥æ³¨éå°±æ¯çº¿ç¨æ± çæ§è¡æ¥éª¤ã
public void execute(Runnable command) {
if (command == null)
throw new NullPointerException();
/*
* Proceed in 3 steps:
*
* 1. If fewer than corePoolSize threads are running, try to
* start a new thread with the given command as its first
* task. The call to addWorker atomically checks runState and
* workerCount, and so prevents false alarms that would add
* threads when it shouldn't, by returning false.
*
* 2. If a task can be successfully queued, then we still need
* to double-check whether we should have added a thread
* (because existing ones died since last checking) or that
* the pool shut down since entry into this method. So we
* recheck state and if necessary roll back the enqueuing if
* stopped, or start a new thread if there are none.
*
* 3. If we cannot queue task, then we try to add a new
* thread. If it fails, we know we are shut down or saturated
* and so reject the task.
*/
int c = ctl.get();
if (workerCountOf(c) < corePoolSize) {
if (addWorker(command, true))
return;
c = ctl.get();
}
if (isRunning(c) && workQueue.offer(command)) {
int recheck = ctl.get();
if (! isRunning(recheck) && remove(command))
reject(command);
else if (workerCountOf(recheck) == 0)
addWorker(null, false);
}
else if (!addWorker(command, false))
reject(command);
}
æç»çç¥|ç产äºæ
CallerRunsPolicyï¼
注æç¹ï¼å½æ¬èº«åºå½å¨çº¿ç¨æ± ç线ç¨ä¸å¤ççèæ¶ä»»å¡ï¼å¨æ§è¡é¥±åçç¥ï¼è¿äºä»»å¡äº¤ç±ä¸»çº¿ç¨å¤çï¼é£ä¹æ¯ä¸ªä¸»çº¿ç¨è¢«å ç¨çæ¶é´å°å¢é¿ï¼é«å¹¶åæ åµä¸ï¼å¤§éç主线ç¨è¢«å ç¨ï¼å¦tomcat线ç¨ï¼é£ä¹tomcat线ç¨å°è¢«å 满ï¼æ°ç请æ±å°è¢«æç»ï¼å®¢æ·ç«¯åºç°è¶ æ¶ã
å 大å°é ç½®ï¼-Xms512m -Xmx512m
线ç¨åæ°ï¼
undertow:
threads:
io: 12
worker: 450
corepoolSize: 50
maximumPoolSize: 100
capacity: 1000
policy: callerRuns
模æ代ç ï¼
@Autowired
private Executor executor;
@RequestMapping("testpolicy")
public void testpolicy() {
executor.execute(() -> {
demoService.getLongTime();
});
}
模æ请æ±ï¼
ab -n 10000000 -c 600 -T "application/json" -H "Content-Type: application/json" http://10.xxx.1.136:8800/thread/test/testpolicy
ç°è±¡ï¼ç³»ç»è¿è¡ç¼æ ¢ï¼å¤§éæ¥å£è¶ æ¶ï¼ç³»ç»è´è½½ä¸é«ï¼cpu使ç¨çä¹å¾ä½ã
导åºçº¿ç¨å¿«ç §ååç°å¤§éå¤äºWAITING(onobjectmonitor)ç¶æï¼å ¶ä¸450æ¡XNIO-1 taské½å¤äºæ¤ç¶æã
[mcloud@10_111_1_136 bin]$ jstack -l 12748 | grep java.lang.Thread.State | awk '{print $2$3$4$5}' | sort | uniq -c
31 RUNNABLE
5 TIMED_WAITING(parking)
1 TIMED_WAITING(sleeping)
552 WAITING(on objectmonitor)
100 WAITING(parking)
æåºå ¶ä¸ä¸æ¡ï¼ç±æ¤è¯´æ使ç¨CallerRunsPolicy使å¾å¼æ¥ååæ¥ï¼å¨æ¬ä¾ä¸é»å¡äºå¤§éç容å¨çº¿ç¨å¯¼è´ç产äºæ ã
注æç¹ï¼threadLocalå±äºçº¿ç¨ç§æçä¿¡æ¯ï¼è¢«å线ç¨çç§æä¿¡æ¯ç»è¦çæã
package test;
import java.io.IOException;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
public class CallerRunsPolicyTest {
private static ThreadLocal<String> securityContext = new ThreadLocal<>();
private static ThreadPoolExecutor executor = new ThreadPoolExecutor(1, 2, 60, TimeUnit.SECONDS,
new ArrayBlockingQueue<>(5), new ThreadPoolExecutor.CallerRunsPolicy());;
public static void main(String[] args) throws IOException, InterruptedException, ExecutionException {
testThread();
}
public static void testThread() throws IOException, InterruptedException, ExecutionException {
securityContext.set("main-thread");//主线ç¨
System.out.println(securityContext.get());
for (int i = 1; i <= 10; i++) {
executor.execute(() -> {
securityContext.set("sub-thread");//å线ç¨
System.out.println(Thread.currentThread().getName());
});
}
TimeUnit.SECONDS.sleep(3);
System.out.println(securityContext.get());
}
}
è¾åº:
main-thread
sub-thread
åå å¦ä¸ï¼threadLocal.set("sub-thread");æ¬åºæ¯ç»å线ç¨èªå·±è®¾ç½®çº¿ç¨ç§ææ°æ®ï¼çThreadLocalæºç Thread.currentThread()æçæ¯å线ç¨èªå·±ãå¦ææç»çç¥ä¸ºCallerRunsPolicyï¼ä¸»çº¿ç¨å°ä¼æ§è¡securityContext.set("sub-thread")æ¹æ³ï¼Thread.currentThread()æçæ¯ä¸»çº¿ç¨ï¼é£ä¹ä¸»çº¿ç¨èªå·±ä¹åçvalueå¼å°è¢«è¦çã
åçï¼å线ç¨è°ç¨ThreadLocal.remove(),交ç»ä¸»çº¿ç¨æ§è¡åä¼æ主线ç¨ç线ç¨ç§ææ°æ®ç»removeæã
DiscardPolicyï¼
注æç¹ ï¼å 为任å¡è¢«ä¸¢å¼ï¼ä¸»çº¿ç¨å¨CompletableFuture.get()[LockSupport.parkNanos]å¤é»å¡ç´å°è¶ æ¶.
CompletableFutureçget()æ¹æ³å¼ä¼é»å¡ä¸»çº¿ç¨ï¼ç´å°å线ç¨æ§è¡ä»»å¡å®æè¿åç»ææä¼åæ¶é»å¡ãå¦æå线ç¨ä¸ç´ä¸è¿åæ¥å£é£ä¹ä¸»çº¿ç¨å°±ä¼ä¸ç´é»å¡ï¼æ以æ们ä¸è¬ä¸å»ºè®®ç´æ¥ä½¿ç¨CompletableFutureçget()æ¹æ³ï¼èæ¯ä½¿ç¨future.get(5, TimeUnit.SECONDS);æ¹æ³æå®è¶ æ¶æ¶é´ã
å½æ们ç线ç¨æ± æç»çç¥ä½¿ç¨çæ¯DiscardPolicyæè DiscardOldestPolicyï¼å¹¶ä¸çº¿ç¨æ± 饱åäºçæ¶åï¼æ们å°ä¼ç´æ¥ä¸¢å¼ä»»å¡ï¼ä¸ä¼æåºä»»ä½å¼å¸¸ãè¿ä¸ªæ¶ååæ¥è°ç¨getæ¹æ³æ¯ä¸»çº¿ç¨å°±ä¼ä¸ç´çå¾ å线ç¨è¿åç»æï¼ç´å°è¶ æ¶æåºTimeoutExceptionã å½çº¿ç¨æ± 满äºä¼ç´æ¥ä¸¢å¼ä»»å¡ï¼èä¸ä¼ç»æ¢ä¸»çº¿ç¨ãè¿ä¸ªæ¶åæ§è¡getæ¹æ³çæ¶åï¼ä¸»çº¿çº¿ç¨ä¸ç´ä¼çå¾ ç´å°è¶ æ¶ä¸ºæ¢ ãå¦æ请æ±éå¾å¤§çæ åµï¼ä¼å¯¼è´å¤§éç主线ç¨(å¦tomcat线ç¨)å¨æ¤å¤çå¾ ç´å°è¶ æ¶ï¼è¿æ ·å°±é»å¡äºå¤§é线ç¨ä»è导è´ç产äºæ ã
package test;
import java.io.IOException;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.RejectedExecutionHandler;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
@SuppressWarnings("all")
public class DiscardPolicyPolicyTest {
private static ThreadPoolExecutor executor = new ThreadPoolExecutor(1, 2, 60, TimeUnit.SECONDS,
new ArrayBlockingQueue<>(5), new SelfDiscardPolicy());
public static void main(String[] args) throws IOException, InterruptedException, ExecutionException {
testThread();
}
public static void testThread() throws IOException, InterruptedException, ExecutionException {
CompletableFuture<Integer>[] completableFutures = new CompletableFuture[10];
long beginTime = System.currentTimeMillis();
for (int i = 0; i < 10; i++) {
CompletableFuture<Integer> c = CompletableFuture.supplyAsync(() -> {
try {
System.out.println(Thread.currentThread().getName());
TimeUnit.MILLISECONDS.sleep(200);
} catch (InterruptedException e) {
System.out.println(e);
}
return 10;
}, executor);
completableFutures[i] = c;
}
try {
CompletableFuture.allOf(completableFutures).get(10, TimeUnit.SECONDS);
} catch (InterruptedException | ExecutionException | TimeoutException e) {
System.out.println(e);
}
System.out.println("finish,cost:"+(System.currentTimeMillis()-beginTime));
}
public static class SelfDiscardPolicy implements RejectedExecutionHandler {
public SelfDiscardPolicy() { }
public void rejectedExecution(Runnable r, ThreadPoolExecutor e) {
System.out.println("discar");
}
}
}
è¾åºï¼
discar
discar
pool-1-thread-2
pool-1-thread-1
pool-1-thread-3
pool-1-thread-1
pool-1-thread-2
pool-1-thread-3
pool-1-thread-2
pool-1-thread-1
java.util.concurrent.TimeoutException
finish,cost:5162
导åºçº¿ç¨æ ï¼åç°ä¸»çº¿ç¨é»å¡å¨CompletableFuture.get()æ¹æ³å¤ã
"pool-1-thread-3" #13 prio=5 os_prio=0 tid=0x000000001f5dc000 nid=0x1dc0 waiting on condition [0x000000002009e000]
java.lang.Thread.State: TIMED_WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
- parking to wait for <0x000000076b836e50> (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
at java.util.concurrent.ArrayBlockingQueue.poll(ArrayBlockingQueue.java:418)
at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1073)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Locked ownable synchronizers:
- None
"pool-1-thread-2" #12 prio=5 os_prio=0 tid=0x000000001f5d6000 nid=0x51f8 waiting on condition [0x000000001ff9e000]
java.lang.Thread.State: TIMED_WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
- parking to wait for <0x000000076b836e50> (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
at java.util.concurrent.ArrayBlockingQueue.poll(ArrayBlockingQueue.java:418)
at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1073)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Locked ownable synchronizers:
- None
"pool-1-thread-1" #11 prio=5 os_prio=0 tid=0x000000001f5d3800 nid=0x3910 waiting on condition [0x000000001fe9e000]
java.lang.Thread.State: TIMED_WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
- parking to wait for <0x000000076b836e50> (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
at java.util.concurrent.ArrayBlockingQueue.poll(ArrayBlockingQueue.java:418)
at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1073)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Locked ownable synchronizers:
- None
"main" #1 prio=5 os_prio=0 tid=0x0000000002f20800 nid=0x5168 waiting on condition [0x0000000002f1f000]
java.lang.Thread.State: TIMED_WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
- parking to wait for <0x000000076bad4eb8> (a java.util.concurrent.CompletableFuture$Signaller)
at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
at java.util.concurrent.CompletableFuture$Signaller.block(CompletableFuture.java:1695)
at java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3323)
at java.util.concurrent.CompletableFuture.timedGet(CompletableFuture.java:1775)
at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1915)
at test.DiscardPolicyPolicyTest.testThread(DiscardPolicyPolicyTest.java:43)
at test.DiscardPolicyPolicyTest.main(DiscardPolicyPolicyTest.java:19)
Locked ownable synchronizers:
- None
解å³æ¹æ¡ï¼
1ï¼ä½¿ç¨CompletableFutureçæ¶å线ç¨æ± æç»çç¥æ好使ç¨AbortPolicyï¼å¦æ线ç¨æ± 满äºç´æ¥æåºå¼å¸¸ä¸æ主线ç¨ï¼è¾¾å°å¿«é失败çææ
2ï¼ä¸å»ºè®®ä½¿ç¨CompletableFutureçget()æ¹æ³ï¼èæ¯ä½¿ç¨CompletableFuture.get(long time, TimeUnit.SECONDS)æå®è¶ æ¶æ¶é´
注æç¹ ï¼ä»»å¡è¢«ä¸¢å¼ï¼è®¡æ°å¨æ æ³å½0ï¼ä¸»çº¿ç¨å¨countDownLatch.await(10, TimeUnit.SECONDS)å¤é»å¡ç´å°è¶ æ¶.
countdownLatchç使ç¨åºæ¯ä¸è¬ç¨å¨å¤çº¿ç¨æ§è¡åä»»å¡ï¼å°ä¸²è¡æµç¨åéæ并è¡è°ç¨ï¼åå°å¤çæ¶é´ã大æ¦æè·¯æ¯ï¼ 对象åå§åæ¶è®¾ç½®ä¸ä¸ªintå¼ä¸ºéè¦çå¾ ç线ç¨æ°éï¼ä¸ä¸ªçå¾ çº¿ç¨æ§è¡å®æåè°ç¨countdownæ¹æ³è®¡æ°å¨å1ï¼å¨è°ç¨awaitæ¹æ³çå¾ ç线ç¨ç´å°è®¡æ°å¨å¼ä¸º0(ææå线ç¨é½æ§è¡å®æ)å°±å¯ä»¥ç»§ç»æ§è¡äºã
ç产çé®é¢ï¼countdownLatchæ æ³å½0ï¼awaitå¤çå¾ ç´å°è¶ æ¶ï¼æè 没设置çè¶ æ¶å°æ éçå¾ ä¸å»ã常è§äºä»¥ä¸ä¸¤ç§åºæ¯ï¼
ä¸ï¼çº¿ç¨æ± æç»çç¥ä½¿ç¨ç丢å¼çç¥ï¼å¦æé»å¡éå满äºæ°çä»»å¡ä¼è¢«æå¼ï¼countdownLatchæ°¸è¿ä¸ä¼ä¸º0ï¼è°ç¨awaitæ¹æ³ä¼ä¸ç´é»å¡ã
package test;
import java.io.IOException;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
public class CountDownLatchTest {
static ThreadPoolExecutor executor = null;
public static void main(String[] args)
throws InterruptedException, IOException, ExecutionException, TimeoutException {
int corePoolSize = 1;
int queueSize = 1;
int maximumPoolSize = 2;
BlockingQueue<Runnable> workQueue = new ArrayBlockingQueue<>(queueSize);
long keepAliveTime = 60;
TimeUnit unit = TimeUnit.SECONDS;
executor = new ThreadPoolExecutor(corePoolSize, maximumPoolSize, keepAliveTime, unit, workQueue,
new ThreadPoolExecutor.DiscardOldestPolicy());
testThread();
}
public static void testThread() throws IOException, InterruptedException, ExecutionException {
int taskSise = 4;
CountDownLatch countDownLatch = new CountDownLatch(taskSise);
Long begin_main = System.currentTimeMillis();
for (int i = 1; i <= taskSise; i++) {
executor.submit(() -> {
try {
Thread.sleep(1000);
System.out.println("-----å线ç¨--------");
} catch (InterruptedException e) {
}
countDownLatch.countDown();
});
};
countDownLatch.await(10, TimeUnit.SECONDS);
System.out.println("-----主线ç¨æ§è¡ï¼èæ¶ï¼" + (System.currentTimeMillis() - begin_main));
}
}
以ä¸ä»£ç æ§è¡èæ¶10sï¼å¦æç¨å¨çå®çä¸å¡ä¸ï¼è¯·æ±é大çæ åµä¸æ¯é»å¡å¤§é线ç¨å¯¼è´æå¡ä¸å¯ç¨ã
ç»è®ºï¼
1ï¼AbortPolicyç±äºå¨executor.submitæ¶åå°±æåºå¼å¸¸ï¼ä¸»çº¿ç¨ç´æ¥å¼å¸¸éåºã
2ï¼DiscardPolicy / DiscardOldestPolicyç´æ¥ä¸¢å¼ä»»å¡ï¼å¯¼è´è®¡æ°å¨æç»ä¸è½å½0ï¼ä¸»çº¿ç¨å¿ é¡»å¨await()æ¹æ³å¤çå¾ è¶ æ¶ï¼å¦ææ²¡è®¾ç½®è¶ æ¶æ¶é´å°ä¼æ°¸è¿çå¾ é»å¡ãé«å¹¶åä¸hangä½å¤ªå¤çº¿ç¨æç»æå¡æ ååº
3ï¼CallerRunsPolicy è°ç¨è æå¨ç线ç¨æ¥æ§è¡ä»»å¡ï¼ä¼æ§è¡countDown()æ¹æ³ï¼è®¡æ°å¨æç»å½0ï¼ä¸»çº¿ç¨ç«å³ç»§ç»æ§è¡ã
äºï¼countdownéè¦å¨finallyéæ§è¡åä¸æä½ã
public static void testThread() throws IOException, InterruptedException, ExecutionException {
int taskSise = 4;
CountDownLatch countDownLatch = new CountDownLatch(taskSise);
Long begin_main = System.currentTimeMillis();
for (int i = 1; i <= taskSise; i++) {
executor.submit(() -> {
System.out.println("-----å线ç¨--------");
int a = 1/0;
countDownLatch.countDown();
});
};
countDownLatch.await(10, TimeUnit.SECONDS);
System.out.println("-----主线ç¨æ§è¡ï¼èæ¶ï¼" + (System.currentTimeMillis() - begin_main));
}
ç±äºå线ç¨å¨countDown()æ¹æ³ä¹åçä¸å¡æ§è¡å¼å¸¸æªæè·ï¼å¯¼è´è®¡æ°å¨ä¸è½å½0ï¼ä¸»çº¿ç¨å¿ é¡»å¨await()æ¹æ³å¤çå¾ è¶ æ¶ï¼å¦ææ²¡è®¾ç½®è¶ æ¶æ¶é´å°ä¼æ°¸è¿çå¾ é»å¡ãæ¹ä¸ºæè·å¼å¸¸å¨finallyéè¿è¡è®¡æ°å¨æä½ï¼
public static void testThread() throws IOException, InterruptedException, ExecutionException {
int taskSise = 4;
CountDownLatch countDownLatch = new CountDownLatch(taskSise);
Long begin_main = System.currentTimeMillis();
for (int i = 1; i <= taskSise; i++) {
executor.submit(() -> {
System.out.println("-----å线ç¨--------");
try {
int a = 1/0;
} catch (Exception e) {
}finally {
countDownLatch.countDown();
}
});
};
countDownLatch.await(10, TimeUnit.SECONDS);
System.out.println("-----主线ç¨æ§è¡ï¼èæ¶ï¼" + (System.currentTimeMillis() - begin_main));
}
abortPolicyï¼ä¹æ¯é»è®¤çæç»çç¥ï¼ç´æ¥ä¸¢å¼ä»»å¡æåºRejectedExecutionExceptionå¼å¸¸,å¼å¸¸é»æ¢ç³»ç»æ£å¸¸è¿è¡ ï¼å¦ææ们éè¦ä¸å¡æ£å¸¸è¿è¡åéè¦æè·æ¤å¼å¸¸ãï¼æ¤å¤è¯´çæ¯å¨ä½¿ç¨CompletableFutureçæ åµä¸ï¼
package test;
import java.io.IOException;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.RejectedExecutionException;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
@SuppressWarnings("all")
public class AbortPolicyPolicyTest {
private static ThreadPoolExecutor executor = new ThreadPoolExecutor(1, 3, 60, TimeUnit.SECONDS,
new ArrayBlockingQueue<>(5), new SelfAbortPolicy());
public static void main(String[] args) throws IOException, InterruptedException, ExecutionException {
testThread();
}
public static void testThread() throws IOException, InterruptedException, ExecutionException {
CompletableFuture<Integer>[] completableFutures = new CompletableFuture[10];
for (int i = 0; i < 10; i++) {
CompletableFuture<Integer> c = CompletableFuture.supplyAsync(() -> {
try {
System.out.println(Thread.currentThread().getName());
TimeUnit.MILLISECONDS.sleep(200);
} catch (InterruptedException e) {
}
return 10;
}, executor);
completableFutures[i] = c;
}
try {
CompletableFuture.allOf(completableFutures).get(2, TimeUnit.SECONDS);
} catch (InterruptedException | ExecutionException | TimeoutException e) {
}
System.out.println("finish");
}
public static class SelfAbortPolicy extends ThreadPoolExecutor.AbortPolicy {
public void rejectedExecution(Runnable r, ThreadPoolExecutor e) {
System.out.println("abort policy");
throw new RejectedExecutionException("abort");
}
}
}
èªå®ä¹æç»çç¥
æ ¹æ®å®é ä¸å¡å æ¬æ¯å¦å 许ä¸å¡ä¸¢å¤±çï¼èªå·±å®ä¹æç»çç¥ã
AbortPolicyWithReportï¼dubboçæç»çç¥ï¼ä»»å¡è¢«æç»åæå°è¯¦ç»çæ¥å¿ä¿¡æ¯å¹¶å¯¼åºçº¿ç¨å¿«ç §ä¿¡æ¯ä¾å®ä½çº¿ä¸é®é¢ã
è¿è¡ç¶æçæ§
ThreadPoolExecutoræä¾äºå¤ä¸ªgetæ¹æ³ä¾å¼åè è·å线ç¨æ± è¿è¡æ¶ä¿¡æ¯ï¼å¼åè å¯ä»¥ä»¥æå°æ¥å¿çæ¹å¼æè éæspringboot Prometheusæ件ï¼ä½¿ç¨Grafanaé¢æ¿æ¥ç线ç¨æ± çå项ææ ã
æç»æ¶çæ§ï¼
public void rejectedExecution(Runnable r, ThreadPoolExecutor e) {
String msg = String.format("thread-monitor-abort:%s"
+ ", poolSize: %d (activeCount: %d, corePoolSize: %d, maxPoolSize: %d, largestPoolSize: %d), taskCount: %d (completed: "
+ "%d, rejectCount:%d)" + ", Executor status:(isShutdown:%s, isTerminated:%s, isTerminating:%s) !",
threadName, e.getPoolSize(), e.getActiveCount(), e.getCorePoolSize(), e.getMaximumPoolSize(),
e.getLargestPoolSize(), e.getTaskCount(), e.getCompletedTaskCount(), count.incrementAndGet(),
e.isShutdown(), e.isTerminated(), e.isTerminating());
logger.debug(msg);
dumpJStack();
throw new RejectedExecutionException(msg);
}
å®æ¶çæ§ï¼
继æ¿ThreadPoolExecutorï¼éåbeforeExecute(Thread t, Runnable r)åafterExecute(Runnable r, Throwable t) æ¹æ³ï¼å¨æ¹æ³å/åæå°åºä¸é¢çææ ã
public class TraceThreadPool extends ThreadPoolExecutor {
private final ThreadLocal<Long> startTime = new ThreadLocal<Long>();
private final Logger log = Logger.getLogger("TimingThreadPool");
private final AtomicLong numTasks = new AtomicLong();
private final AtomicLong totalTime = new AtomicLong();
@Override
protected void beforeExecute(Thread t, Runnable r) {
......
super.beforeExecute(t, r);
}
@Override
protected void afterExecute(Runnable r, Throwable t) {
.......
super.afterExecute(r, t);
}
注æï¼ä¸åä¸å¡ç线ç¨ä¸å®è¦ç¨ååå»åºåï¼å¨å¯¼åºçº¿ç¨å¿«ç §/å ådumpåæé®é¢çæ¶åæå ¶éè¦
å¨æåæ°è°æ´
éç线ä¸çä¸å¡éå¢é¿ï¼çº¿ä¸çåæ°éè¦è·çè°æ´ï¼çº¿ç¨æ± åæ°ä¹æ¯å ¶ä¸çä¸é¨åãå¨ThreadPoolExecutorä¸é对corePoolSizeåmaximumPoolSizeçæ ¸å¿åæ°æä¾äºsetæ¹æ³ï¼å¯ä»¥æ¹ä¾¿æ们å¨è¿è¡æ¶è®¾ç½®çº¿ç¨æ± åæ°ï¼å¦å¨é ç½®ä¸å¿çç³»ç»ä¿®æ¹ãä½æ¯éå容é确没æ³è°æ´ãå¦æéè¦ä¿®æ¹éå容éï¼åéè¦copyä¸ä»½LinkedBlockingQueue代ç ç¨ä½ä¿®æ¹ã
public class LinkedBlockingQueue<E> extends AbstractQueue<E>
implements BlockingQueue<E>, java.io.Serializable {
...
private final int capacity;
}
æ以æ们éè¦éåè¿ä¸ªéåãä¸è¬æ¯å¤å¶ä¸ä»½LinkedBlockingQueueæºç ï¼ä¿®æ¹capacityå»æfinalï¼å ä¸setæ¹æ³ã
private volatile int capacity;
public int getCapacity() {
return capacity;
}
public void setCapacity(int capacity) {
if (capacity <= 0) throw new IllegalArgumentException();
final int oldCapacity = this.capacity;
this.capacity = capacity;
final int size = count.get();
if (capacity > size && size >= oldCapacity) {
signalNotFull();
}
}
使ç¨å°capacityçå¤æéè¦æ¹æ£ãç¹å«æ¯ count.get() == capacityçå°æ¹ã
å¨è®¾ç½®åæ°çæ¶åéè¦å è®¾ç½®æ ¸å¿çº¿ç¨æ°ï¼å设置æ大线ç¨æ°ï¼ä»£ç å¦ä¸ï¼
ThreadPoolExecutor threadPoolExecutor = traceThreadPoolTaskExecutor.getThreadPoolExecutor();
threadPoolExecutor.setCorePoolSize(conf.getCorePoolSize());
threadPoolExecutor.setMaximumPoolSize(conf.getMaxPoolSize());
BlockingQueue<Runnable> queue = threadPoolExecutor.getQueue();
if (queue instanceof ResizableCapacityLinkedBlockIngQueue) {
((ResizableCapacityLinkedBlockIngQueue<?>) queue).setCapacity(conf.getQueueCapacity());
}
é ç½®åæ°æ¨èä¼°ç®
åççè¯ä¼°æ ¸å¿çº¿ç¨æ°åæ大线ç¨æ°ï¼æ²¡æåºå®çå ¬å¼
è¾ä¸ºåºå®çå ¬å¼ï¼ 计ç®å¯é 线ç¨æ° â CPUæ ¸æ°ã
åå§ä¼°ç®å ¬å¼ï¼ 线ç¨æ°= CPUæ ¸å¿æ°/(1-é»å¡ç³»æ°) ,é»å¡ç³»æ°åå¼0.8-0.9
IOå¯éå没æåºå®çå ¬å¼ï¼åªè½å¤§ä½åä¸ä¸ªåå¼ï¼ç¶åæ ¹æ®åæµå线ç¨å¿«ç §åæå»å®è·µä¸ä¸ªåçç线ç¨æ± æ°éã
åæµçè¿ç¨ä¸éè¦æ£æµæå¡å¨ä»¥åä¸é´ä»¶çå项ææ ãåæ¶æ ¹æ®æ åµä¸æçè°æ´çº¿ç¨æ°éåéå大å°ï¼è°èéµå¾ªä»¥ä¸ååï¼
1ï¼æ大线ç¨æ°è®¾ç½®å¤ªå°ï¼å·¥ä½éå设置åå°ï¼å¯¼è´æå¡æ¥å£å¤§éåºç°RejectedExecutionException
2ï¼æ大线ç¨æ°è®¾ç½®å¤ªå°ï¼å·¥ä½éå设置è¿å¤§ï¼ä»»å¡å 积è¿å¤ï¼æ¥å£ååºæ¶é´åé¿ã
3ï¼æ大线ç¨æ°è®¾ç½®å¤ªå¤§ï¼çº¿ç¨è°åº¦å¼éå¢å¤§ï¼å¤çé度åèä¸éã
4ï¼æ ¸å¿çº¿ç¨æ°è®¾ç½®è¿å¤§ï¼ç©ºé²çº¿ç¨å¤ªå¤ï¼å ç¨ç³»ç»èµæºã
å¦æå¨è°è线ç¨æ°åéåæ°åï¼å¨æµç»æçè¿ç¨ä¸å¯¼åºçº¿ç¨å¿«ç §ï¼è¿ç»å¯¼åº3次ï¼å¯¹æ¯ä¸æ¬¡çº¿ç¨å¿«ç §ï¼å¦æåç°å¤§é¨å线ç¨é½å¤äºè¿è¡ç¶æï¼è¯´æ线ç¨æ°é设置çè¿ç®åçï¼æ¤æ¶è¿å¯ä»¥ç¨å¾®è°å¤§ç¹ãå¦æ对æ¯åç°å¤§é¨å线ç¨é½å¤äºwaitingç¶æï¼å¯è½æ¯çº¿ç¨æ°è®¾ç½®è¿å¤ï¼å¤ç¨ï¼ï¼éè¦è°å°åè¯ã
jstack -l pid > pid.log
Thread Name
appliance-svc-task-36
State
Waiting on condition
Java Stack
at sun.misc.Unsafe.park(Native Method)
at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
at java.util.concurrent.LinkedBlockingQueue.take(LinkedBlockingQueue.java:442)
at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1067)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1127)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Native Stack
No Native stack trace available
å¦æwaiting on condition/monitorçæ¯ä¸å¡ä»£ç ï¼é£ä¹å¯è½æå³çè¿ç代ç å¯è½åå¨é»å¡æè æ§è½é®é¢ï¼å¦ææ¯ææ§è½é®é¢åæ ¹æ®å®é æ åµåä¼ååååæµã
éå大å°ä¼°ç®ï¼å¦æä¸ä¸ªä»»å¡çæ§è¡æ¶é´å¨50~100msï¼å¦æ以ä¸åæµä¼°ç®çæ ¸å¿çº¿ç¨æ°æ¯50,å设æ¤æ¥å£è®¤ä¸ºå¨1så è¿åç®åçï¼æç §1sç®ï¼1så 50个线ç¨å¯ä»¥å¤ç(ä¸èèå¤é¨å ç´ )ï¼1*1000ms / 75 ms * 50= 666ï¼åå¨æ¤èå´æµ®å¨å³å¯ãè¿ä¹ç®æ¯ä¸ªä¼°ç®å¼ï¼ç¶åæ ¹æ®åæµç»ææ¯å¦ååºæ¶é´+QPSåç»åä¸é¢è¯´ççæ§æç»æ°éè°èä¸ä¸ªå®é çåæ°ã
线ç¨æ± ä¸å¡é离|ç产äºæ
线ç¨æ± é离åçï¼ç»æ¯ä¸ªè¯·æ±åé åç¬ç线ç¨æ± ï¼æ¯ä¸ªè¯·æ±åå°äºä¸å½±åï¼ä¹å¯ä»¥ä½¿ç¨ä¸äºæççæ¡æ¶æ¯å¦Hystrixï¼é¿éçSentinel çã
为ä»ä¹éè¦é离ï¼çº¿ç¨æ± å ±ç¨ï¼å«çä¸å¡æ§è¡æ¶é´è¿é¿ï¼å ç¨äºæ ¸å¿çº¿ç¨ï¼å¦å¤çä¸å¡çä»»å¡å°è¾¾å°±ä¼ç´æ¥è¿å ¥çå¾ éåãå¦æé¿æ¶é´å¾ä¸å°æ§è¡å°±ä¼å°±ä¼ä½¿å¾æ¥å£ååºç¼æ ¢ã
ä¼ç¹ï¼
(1)ï¼æäºä¸æ¸¸æå¡ç请æ±æ¶é´è¿é¿ï¼å建å¤ä¸ªçº¿ç¨æ± ï¼é对ä¸åçä¸æ¸¸æå¡ç请æ±åªå¨åèªç线ç¨æ± ä¸ç线ç¨å¤çãä¸ä¼å å ¶ä»ä¸æ¸¸æå¡ç请æ±èæ¶è导è´ä¸å¡å¼å¸¸ï¼ä»èå®ç°èµæºé离ã
(2)ï¼å个线ç¨åèªåªååèªçäºæ ï¼äºä¸å¹²æ°ï¼å¯ä»¥é¿å 线ç¨é»å¡ï¼æåååéã
æ¯å¦æå¡ä¾èµå¤é¨æå¡Açæ¥å£ï¼ä¾èµå¤é¨æå¡Bçæ¥å£ï¼åå¦loginæ¥å£ä¾èµçAæå¡å¤çç¨å¾®èæ¶ï¼å线ç¨åä¸æ¶é´å åçäºæ å°ä¼åå°ï¼ä»»å¡å 积å°éåï¼home/getè¿æ¥ç请æ±åªè½å¨éåéçå¾ ï¼å½±åäºhome/getçåè½ã
home/getçªåæµéå 满线ç¨åï¼ç¨æ·ç»å½åè½åå½±åãå¤ä¸ªä¸å¡äºç¸å½±åï¼æ欲éè¦é离ã
package com.example.demo.controller;
import java.util.concurrent.Executor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("user")
public class CommonPoolTestController {
@Autowired
private Executor commonExecutor;
@RequestMapping("login")
public void login(@RequestParam("userName") String userName) {
commonExecutor.execute(()->{
A.getNickName(userName);//å¤é¨Aç³»ç»çæ¥å£
});
}
@RequestMapping("home/get")
public void homeGet(@RequestParam("userName") String userName) {
commonExecutor.execute(()->{
B.getHome(userName);//å¤é¨Bç³»ç»çæ¥å£
});
}
}
ç产æ¡ä¾ä¸ï¼
æ¶æ¯ä¸å¿åºç°ä¸å¯ç¨ï¼ç¨æ·æå¡å为æ¶æ¯çç产æ¹(httpè°ç¨æ¶æ¯ä¸å¿åéæ¶æ¯)ï¼
ä¸ï¼æªå线ç¨é离
æ´ä¸ªç³»ç»å ±ç¨ä¸ä¸ªçº¿ç¨æ± ï¼é»è®¤ä½¿ç¨LinkedBlockingQueueæªè®¾ç½®éå大å°(Integer.MAX_VALUE),å¥ä¸å¡é½è®©éé¢ä¸¢(åå¨OOMé£é©)ã
æç»å 为æ¶æ¯ä¸å¿çä¸å¯ç¨(æ¶æ¯åéå 满æ´ä¸ªçº¿ç¨æ± å ¶ä»æ ¸å¿ä¸å¡å¨éåéæé?å 满çæ ¹æ¬åå è¿æåªäºï¼),导è´å ¶ä»æ ¸å¿ä¸å¡ç«çªã
äºï¼æªè®¾ç½®è¶ æ¶æ¶é´
è°ç¨æ¶æ¯ä¸å¿ä½¿ç¨çæ¯å人对apache httpclientçå°è£ å·¥å ·ç±»ï¼éé¢åæ»ç30sè¶ æ¶(æ¶æ¯ä¸å¿è´è½½é«ï¼å¤çä¸è¿æ¥ï¼å®¢æ·ç«¯åªè½çå°è¶ æ¶ SocketTimeoutException )ï¼ä¹å°±æ¯ä¸ä¸ªçº¿ç¨30så åªè½çå¾ ç´å°è¶ æ¶(ä¸åªæ¯30s)ï¼æå³çå å个请æ±è¿æ¥å°±å¯ä»¥æ满线ç¨æ± (æ ¸å¿çº¿ç¨æ°80)ï¼å¨è¿30så æºæºä¸æçç¸å ³çå ¶ä»è¯·æ±ä¸å¡é½ç§¯åå¨éåï¼ä¸å¡çº¿ç¨æ± çéåï¼éå¾ä¸å°å¤çï¼ä¸»çº¿ç¨ï¼å®¹å¨çº¿ç¨ï¼å¦tomatï¼å¨çå¾ ç´å°è¶ æ¶ï¼åäºä¹å æ®äºå¤§éç容å¨çº¿ç¨(æ°ç请æ±å¾é¾è¿æ¥ï¼ç³»ç»è´è½½é«ï¼å¤çç¼æ ¢)
ä¸ï¼éè¯
ä¸é¢è¯´çä¸ä¸ªçº¿ç¨30så åªè½çå¾ ç´å°è¶ æ¶ï¼è¿ä¸ªå ¶å®æ¯90sçï¼æå½±ååæ¾å¤§äºå¥½å¤åã ç«å¨ä¸å¡ä¸æ¯å¦å 许丢失ï¼éè¯æ¯å¦å¿ é¡»ï¼åªäºå¼å¸¸éè¦éè¯ï¼æ¶è´¹æ¹æ¯å¦è½ä¿è¯å¹çæ§ï¼éè¯æ¬¡æ°/é´éï¼
ç产æ¡ä¾äºï¼
åä¸æ åµï¼åæ ·æ¯å¤§æ°æ®æå¡ä¸å¯ç¨ï¼APP--->设å¤nginx--->设å¤æå¡httpclient(30sè¶ æ¶)--->大æ°æ®é¦é¡µç¯å¢ä¿¡æ¯æ¥å£ãå 满tomcat线ç¨æ± ï¼ä¸è½å¤çå ¶ä»è¯·æ±ãå°æ¶è®¾å¤æå¡å ¨é¨ç«çªãï¼é£æ¶åååå®é¨ç½²é离-代ç ä»åºé离ï¼iot-rpcæå为iot-applianceåiot-userï¼ï¼ä¸ç¶ççæ¯æå± é¦é¡µé½ç«äºãè¿å°±æ¯æåç好å¤ä¹ä¸(好å¤è¿è¿ä¸æ¢è¿äº)
äºæ ç°åºè¿åï¼
client端ï¼
package com.example.demo.controller.thread;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.example.demo.third.DemoService;
import com.midea.framework.commons.http.R;
import com.midea.framework.commons.json.JsonUtil;
@RestController
@RequestMapping("test")
public class ClientLongTimeController {
private static final Logger LOGGER = LoggerFactory.getLogger(ClientLongTimeController.class);
@Autowired
private DemoService demoService;
@RequestMapping("longTime")
public void longTime() {
R r = demoService.getLongTime();
LOGGER.info("result:{}",JsonUtil.toJson(r));
}
}
Server端ï¼
package com.example.demo.controller.server;
import java.util.Random;
import java.util.concurrent.TimeUnit;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("v1")
public class ServerLongTimeController {
@ResponseBody
@RequestMapping("server/getLongTime")
public String getLongTime() throws InterruptedException {
int number = new Random().nextInt(5) + 1;
TimeUnit.SECONDS.sleep(number);
return String.valueOf(number);
}
}
模æ客æ·ç«¯å起请æ±ï¼
ab -n 1000000 -c 600 -T "application/json" -H "Content-Type: application/json" http://10.xxx.1.136:8800/test/longTime
è§å¯å®¢æ·ç«¯ç线ç¨å¿«ç §ï¼
å¯ä»¥åç°450个线ç¨å¤äºwaitç¶æï¼ä¸450为undertowçå·¥ä½çº¿ç¨æ°éï¼è¯´æundertowå·¥ä½çº¿ç¨å ¨é¨å¨æ¤é»å¡ã
server:
port : 8800
undertow:
threads:
io: 12
worker: 450
æ¤æ¶åºç°çç°è±¡ï¼
1ï¼ç³»ç»å ¨é¨æ¥å£ååºç¼æ ¢æè è¶ æ¶ï¼å®¹å¨å·¥ä½çº¿ç¨å¤§éçå¾ ï¼åä¸æ¶é´æ²¡æ空é²ç线ç¨å»å¤ç请æ±ï¼
2ï¼cpu使ç¨çä¸é«
解å³åæ³ï¼
1ï¼æå¡ç«¯ä¼åæ¥å£èæ¶æ¶é¿
2ï¼å®¢æ·ç«¯å线ç¨é离ï¼ä¸è¦å½±åå ¶ä»ä¸å¡
3ï¼ä½¿ç¨ä¸å¡çº¿ç¨å»é»å¡èæ¶ï¼é¿å 容å¨çº¿ç¨é»å¡
@RequestMapping("longTime/async")
public DeferredResult<R> longTimeDef() {
DeferredResult<R> d = new DeferredResult<>(1000L,new R());
executor.execute(()->{
R r = demoService.getLongTime();
d.setResult(r);
LOGGER.info("result:{}",JsonUtil.toJson(r));
});
return d;
}
以ä¸åªæ¯æ¢äºä¸ªé»å¡æ¹å¼ï¼ä½¿ç¨èªå®ä¹ç线ç¨å»é»å¡ï¼å 为容å¨çº¿ç¨æ们没æ³åä¸å¡é离ï¼ä½æ个ä¸å¡èæ¶è尽容å¨çææå·¥ä½çº¿ç¨åå ¶ä»ä¸å¡åæ ·åå½±åã
建议æç»ç解å³åæ³æ¯ä»æå¡ç«¯èæ¶å»è§£å³ï¼åæ¶å®¢æ·ç«¯å好线ç¨é离ï¼é¿å æ éå½±åå ¶ä»ä¸å¡ã
Executors è°¨æ 使ç¨
JUCä¸çå·¥å ·ç±»ï¼æä¾äºéææ¹æ³ä¾ä½¿ç¨è å¿«éçå建线ç¨æ± ãä½æ¯å®å¯¹ä½¿ç¨è æ¥è¯´å±è½äºå ³é®çè°ä¼åæ°ï¼æ以ç¦æ¢å¨ç产ç¯å¢ä½¿ç¨ã
æ¤ç段æèªé¿éå·´å·´javaå¼åè§è(æ³°å±±ç)
ã强å¶ã线ç¨æ± ä¸å è®¸ä½¿ç¨ Executors å»å建ï¼èæ¯éè¿ ThreadPoolExecutor çæ¹å¼ï¼è¿
æ ·çå¤çæ¹å¼è®©åçåå¦æ´å æ确线ç¨æ± çè¿è¡è§åï¼è§é¿èµæºèå°½çé£é©ã
说æï¼Executors è¿åç线ç¨æ± 对象çå¼ç«¯å¦ä¸ï¼
1ï¼ FixedThreadPool å SingleThreadPoolï¼
å 许ç请æ±éåé¿åº¦ä¸º Integer.MAX_VALUEï¼å¯è½ä¼å 积大éç请æ±ï¼ä»èå¯¼è´ OOMã
2ï¼ CachedThreadPoolï¼
å 许çå建线ç¨æ°é为 Integer.MAX_VALUEï¼å¯è½ä¼å建大éç线ç¨ï¼ä»èå¯¼è´ OOMã
é ç½®ï¼-Xms256m -Xmx256m -XX:+PrintGCDetails -XX:+PrintGCDateStamps -Xloggc:/app/demo/bin/gc.log -XX:-UseAdaptiveSizePolicy
private static final Executor executor = Executors.newFixedThreadPool(30);
@RequestMapping("newFixedThreadPool")
public void testNewFixedThreadPoolOOM() {
executor.execute(()->{
demoService.getLongTime();
});
}
模æ请æ±ï¼
ab -n 1000000 -c 600 -T "application/json" -H "Content-Type: application/json" http://10.xxx.1.136:8800/thread/test/newFixedThreadPool
æ§è¡ä¸æ®µæ¶é´ååç°ç³»ç»ååºç¼æ ¢ï¼æ¤æ¶æ¥çjvmå å使ç¨ä¿¡æ¯
jmap -heap 21280
[mcloud@10_xxx_1_136 bin]$ jmap -heap 21280
Attaching to process ID 21280, please wait...
Debugger attached successfully.
Server compiler detected.
JVM version is 25.92-b14
using thread-local object allocation.
Parallel GC with 8 thread(s)
Heap Configuration:
MinHeapFreeRatio = 40
MaxHeapFreeRatio = 70
MaxHeapSize = 268435456 (256.0MB)
NewSize = 89128960 (85.0MB)
MaxNewSize = 89128960 (85.0MB)
OldSize = 179306496 (171.0MB)
NewRatio = 2
SurvivorRatio = 8
MetaspaceSize = 21807104 (20.796875MB)
CompressedClassSpaceSize = 1073741824 (1024.0MB)
MaxMetaspaceSize = 17592186044415 MB
G1HeapRegionSize = 0 (0.0MB)
Heap Usage:
PS Young Generation
Eden Space:
capacity = 67108864 (64.0MB)
used = 67108864 (64.0MB)
free = 0 (0.0MB)
100.0% used
From Space:
capacity = 11010048 (10.5MB)
used = 0 (0.0MB)
free = 11010048 (10.5MB)
0.0% used
To Space:
capacity = 11010048 (10.5MB)
used = 0 (0.0MB)
free = 11010048 (10.5MB)
0.0% used
PS Old Generation
capacity = 179306496 (171.0MB)
used = 178943424 (170.65374755859375MB)
free = 363072 (0.34625244140625MB)
99.79751319216008% used
14581 interned Strings occupying 1354632 bytes.
åç°å¹´è代è¯ç¨çæ¥è¿99.79%
代ç æ§è¡ä¸æ®µæ¶é´ä¹åï¼æ§è¡ä»¥ä¸å½åç°fullGCå¾é¢ç¹æ jstat -gc 21280 5000
[mcloud@10_111_1_136 ~]$ jstat -gcutil 21280 1000
S0 S1 E O M CCS YGC YGCT FGC FGCT GCT
0.00 0.00 100.00 99.91 94.74 93.12 847 26.130 59 54.985 81.115
0.00 0.00 84.09 99.91 94.74 93.12 847 26.130 59 56.050 82.180
0.00 0.00 100.00 99.91 94.74 93.12 847 26.130 60 56.050 82.180
0.00 0.00 100.00 99.91 94.74 93.12 847 26.130 61 57.046 83.176
0.00 0.00 100.00 99.91 94.74 93.12 847 26.130 62 58.082 84.212
æ们å å ¥jvmåæ°è¾åºå ·ä½çGCä¿¡æ¯ï¼é ç½®ï¼-XX:+PrintGCDetails -XX:+PrintGCDateStamps -Xloggc:/app/demo/bin/gc.log
2022-01-12T11:27:30.414+0800: 262.093: [GC (Allocation Failure) [PSYoungGen: 68768K->3712K(76288K)] 243466K->178642K(251392K), 0.0327598 secs] [Times: user=0.23 sys=0.00, real=0.03 secs]
2022-01-12T11:27:38.242+0800: 328.921: [Full GC (Ergonomics) [PSYoungGen: 65536K->12498K(76288K)] [ParOldGen: 174953K->174951K(175104K)] 240489K->187450K(251392K), [Metaspace: 45234K->45234K(1091584K)], 1.0362795 secs] [Times: user=7.05 sys=0.02, real=1.03 secs]
2022-01-12T11:27:39.479+0800: 330.158: [Full GC (Ergonomics) [PSYoungGen: 65536K->12991K(76288K)] [ParOldGen: 174951K->174950K(175104K)] 240487K->187942K(251392K), [Metaspace: 45234K->45234K(1091584K)], 0.9920889 secs] [Times: user=6.67 sys=0.02, real=0.99 secs]
2022-01-12T11:27:40.659+0800: 331.338: [Full GC (Ergonomics) [PSYoungGen: 65536K->12856K(76288K)] [ParOldGen: 174950K->174950K(175104K)] 240486K->187807K(251392K), [Metaspace: 45234K->45234K(1091584K)], 1.0017935 secs] [Times: user=6.88 sys=0.02, real=1.00 secs]
æ ¼å¼ä»ç»ï¼
第ä¸æ¡æ¥å¿ï¼
1ï¼328.921 GCåçæ¶åèææºè¿è¡äºå¤å°ç§
2ï¼GC (Allocation Failure)ï¼åçäºä¸æ¬¡åå¾åæ¶ï¼è¿æ¯ä¸æ¬¡Minor GCãæ¬å·é表示GCåççåå ï¼Allocation Failureçç¼ç±æ¯ 年轻代没æ足å¤çåºåå¯ä»¥åæ¾éè¦åé ç对象 è失败ã
3ï¼PSYoungGenï¼ä½¿ç¨çåå¾æ¶éå¨çåå
4ï¼68768K->3712K(76288K) æçæ¯ åå¾æ¶éå->åå¾æ¶éåï¼å¹´è½»ä»£å æ»å¤§å°ï¼
5ï¼243466K->178642K(251392K) æçæ¯åå¾æ¶éååï¼javaå ç大å°æ»å ±å¤§å°ï¼251392K å æ¬æ°ç代åå¹´è代ï¼ï¼å¹´è代大å°=251392K-76288K
6ï¼0.0327598 secs ï¼GCè±è´¹çæ¶é´
7ï¼[Times: user=0.23 sys=0.00, real=0.03 secs]ï¼åå«è¡¨ç¤ºç¨æ·æèæ¶ï¼å æ ¸æèæ¶ï¼æ»èæ¶ã
第äºæ¡æ¥å¿ï¼
åºæ¬åä¸ï¼å¯¹åºå代空é´çåååæ»å¤§å°ã
ä»ä¸é¢è¿æ®µè¾åºæ¥å¿ä¸å¯ä»¥çå°ï¼
- è¿æ®µæ¥å¿è¾åºçæ¯JVMå¯å¨å328ç§å·¦å³çä¿¡æ¯
- fullGCæ¯ç§ä¸æ¬¡ï¼æ¯æ¬¡èæ¶1så·¦å³ï¼å¨è¿æé´JVMåºæ¬æåã
- åæ¶ï¼å¯ä»¥è§å¯å° è¿ä¸ªåºç¨è¿è¡ç¶åµä¸å¥½ï¼JVMå ä¹è¢«åå¾åæ¶ç»åæ¢äºï¼GCæ¶èäºåºç¨è¿è¡ç99%çæ¶é´ã并ä¸Full GCä¹å·²ç»æ æ³åæ¶å°å¤å°å å空é´äºãè¿ä¸ªåºç¨å¨è¿è¡å åéåï¼ä¹æåºjava.lang.OutOfMemoryError: GC overhead limit exceededçé误并ç»æ¢äº
éè¿åæGCæ¥å¿å¯ä»¥åç°ï¼
- åºç¨çGCè´è½½è¿é«ãGCæåæ¶é´è¶é¿ï¼åºç¨çååéè¶ä½ãä¸è¬æ¥è¯´ï¼GCæåæ¶é´è¶ è¿åºç¨è¿è¡æ¶é´ç10%æ¶ï¼è¯¥åºç¨å·²ç»å¤äºä¸æ£å¸¸ç¶åµäº
- å次æåæ¶é´è¿é¿ãå个æåèæ¶è¶é¿ï¼åºç¨ç延è¿è¶æ¾èãå½åºç¨ç延è¿æ§è½è¦æ±åºç¨çæ¯æ¬¡äºå¡å¤çå¿ é¡»å¨1000毫ç§å å®ææ¶ï¼æ们就è¦ç¡®ä¿GCæåæ¶é´ä¸è½è¶ è¿1000毫ç§
- è年代å å使ç¨è¾¾å°æéãå½åçå 次Full GCåï¼è年代空é´çå å使ç¨ä»ç¶è¾¾å°å ¶å®¹éæéæ¶ï¼æ们å¯ä»¥ç¥éè年代空é´å·²ç»æ为åºç¨çç¶é¢äºãè¿å¯è½æ¯ç±äºè年代空é´åé ä¸è¶³ï¼æè åºç¨åçäºå åæ³æ¼
æ们dumpåºå åå¿«ç §æ¥è¿è¡åæ
jmap -dump:live,format=b,file=heap-dump.bin 21280
ç±æ¤è¯æ使ç¨Executorså建çnewFixedThreadPoolå 为é»è®¤ä½¿ç¨æ çéå(éåé¿åº¦intæ大å¼)导è´åå¨OOMçé£é©ã
åæ ·ï¼ä½¿ç¨å ¶å建çnewCachedThreadPoolä¹åå¨å¼å¯å¤§é线ç¨å¯¼è´ç³»ç»èµæºä¸è¶³ççé£é©ã
类似çå ¶ä»æ± å-httpclientè¿æ¥æ±
1.å¤ç¨httpè¿æ¥,çå»äºtcpç3次æ¡æå4次æ¥æçæ¶é´,æ大éä½è¯·æ±ååºçæ¶é´ 2.èªå¨ç®¡çtcpè¿æ¥,ä¸ç¨äººä¸ºå°éæ¾/å建è¿æ¥
å¦ææªä½¿ç¨è¿æ¥æ± çæ åµä¸ï¼å¦æè°ç¨ä¸æ¸¸ç请æ±å¾å¤ï¼é£ä¹ç±äºæ¯ä¸ªè¯·æ±é½éè¦å»ºç«ä¸ä¸ªè¿æ¥ï¼åä¼ç»å¸¸åºç°Address already in use (Bind failed)çé®é¢ãè¿æ¯ä¸ä¸ªç«¯å£ç»å®å²çªçé®é¢ï¼ææ¥äºä¸ä¸å½åç³»ç»çç½ç»è¿æ¥æ åµå端å£ä½¿ç¨æ åµï¼åç°æ¯æ大étime_waitçè¿æ¥ä¸ç´å ç¨ç端å£æ²¡éæ¾ï¼å¯¼è´ç«¯å£è¢«å 满ï¼æé«çæ¶å6w+个ï¼ï¼å æ¤å»ºç«è¿æ¥çæ¶åä¼åºç°ç³è¯·ç«¯å£å²çªçæ åµ ã
æ以å¨javaä¸æ常使ç¨çæ¯apacheçhttpclientä½ä¸ºhttp请æ±å·¥å ·ã
使ç¨httpclientæå 个éè¦åæ°é¤äºè¶ æ¶æ¶é´ç¸å ³ç设置ï¼è¿æ两个åæ°éè¦æ³¨æï¼é£å°±æ¯è¿æ¥æ± 设置çæ大è¿æ¥æ°(MaxTotal) åæ¯ä¸ªè·¯ç±åé çæ大è¿æ¥æ°(DefaultMaxPerRoute)ãå¦æ设置ä¸åçä¹å¯è½å¯¼è´ç产äºæ ã
1:MaxTotal表示è¿æ¥æ± å¯ä»¥ç®¡ççæ大çè¿æ¥æ°é
2:defaultMaxPerRouteå¯ä»¥ç解为ç»æ¯ä¸ªåååé çæ大è¿æ°ã
å¦ï¼å¦æ设置defaultMaxPerRoute=50ï¼åapplianceæå¡æ appliance-svcæå¡æ大å¯ä»¥åé 50个è¿æ¥ï¼æuser-svcä¹ä¼éæ°æ大åé 50个è¿æ¥ ã
public void init () {
requestConfig = RequestConfig.custom().setSocketTimeout(1000).setConnectTimeout(1000).build();//设置 SocketTimeoutæ¶é´
PoolingHttpClientConnectionManager cm = new PoolingHttpClientConnectionManager();
cm.setMaxTotal(200);
cm.setDefaultMaxPerRoute(100);
client = HttpClients.custom().setDefaultRequestConfig(requestConfig).setConnectionManager(cm).build();
}
ç产æ¡ä¾ï¼
iot-rpcæå¡è°ç¨cmsæ¥å£è·åç¨æ·æµç§°ï¼ç±äºå¼ååå¦æ²¡æä»ç»çè¿å个åæ°çå«ä¹ï¼ä»å«çå°æ¹å¤å¶äºä¸ä»½httpclientç±»åé ç½®ï¼ä¿®æ¹äºdefaultMaxPerRouteçåæ°ä¸º10ï¼ç±äºç¸å ³ä¸å¡æ¯æå± é¦é¡µçä¸å¡è¯·æ±éè¿æ¯æ¯è¾å¤§çï¼ä¸çº¿åï¼è¿åä¸å¡åºç°äºå¤§éçè¶ æ¶ï¼åæ¶æ¤æå¡éçå ¶ä»æ¥å£ä¹åºç°å¤§éè¶ æ¶(é£æ¶åè¿æ²¡æçæ§æ¥è¦é¾è·¯æ¥å¿ï¼æ¥å£æ¥éæè èæ¶å ¨é 客è¯æ¾ä¸é¨æè åè´¨èªå¨åèæ¬æ¥éåæ¾æ们)ï¼ç¶å客è¯ä¸æã
å¼åå®ä½é®é¢ä¸æ¿æ§ï¼å çlogï¼åtop free dfï¼fullgc,é¿éåå°æ°æ®åºï¼redisï¼ç½ç»ãä¸åé½æ£å¸¸ãæååªè½jstack线ç¨å¿«ç §ã
ä»jstackçæ¥å¿ä¸å¯ä»¥çåºæ¥ï¼æ大éç线ç¨å¨çå¾ ä»è¿æ¥æ± éè·åçè¿æ¥èè¿è¡æéï¼å æ¤å¯¼è´äºçº¿ç¨å 积(tomcat线ç¨)ï¼æ以平åååºæ¶é´ä¸åãç±äºå 积äºå¤ªå¤çtomcat线ç¨ï¼å ¶ä»çå£å¹³åä¹ä¼å æ¤åé«ï¼å¯¼è´æ´éå¤ç线ç¨å 积ã
â â
åæå°æ¯å¤§é线ç¨ä»httpclientè¿æ¥æ± è·åè¿æ¥èè¿è¡æéï¼éè¿çæºç å¾ç¥æ¯DefaultMaxPerRouteå¼è®¾ç½®ç太å°ï¼éè¿ä»¥ä¸å½ä»¤çå°è¯·æ±å¤å´ç³»ç»ä¹åªæ10个è¿æ¥ãä¿®æ¹defaultMaxPerRoute为100åï¼ç»è¿ç®ååæµåç°å¹¶åéæé«äºï¼æ²¡æåºç°å¤§é线ç¨é»å¡çç°è±¡ã
netstat -n | grep 10.xxx.1.46 | awk '/^tcp/ {++S[$NF]} END {for(a in S) print a, S[a]}'
netstat -ant | grep 10.xxx.1.46 |awk '{print $6}' | sort |uniq -c
[mcloud@10_xxx_1_136 log]netstat -n | grep 10.xxx.1.46 | awk '/^tcp/ {++S[$NF]} END {for(a in S) print a, S[a]}'
ESTABLISHED 10
httpclientéè§çåï¼
HttpClient4 è°ç¨springboot(undertow)ï¼å®¢æ·ç«¯åºç°å¤§éçCLOSE_WAIT
private String doRequestInner(HttpRequestBase httpRequestBase) throws IOException {
CloseableHttpResponse response = null;
long l1 = System.currentTimeMillis();
try {
response = client.execute(httpRequestBase);
HttpEntity entity = response.getEntity();
String result = EntityUtils.toString(entity, Consts.UTF_8);
EntityUtils.consume(entity);
long l2 = System.currentTimeMillis();
logger.info(" http client cost:{} response:{} url:{}", (l2 - l1), result, httpRequestBase.getURI());
return result;
} finally {
if (response != null) {
logger.info("----close");
response.close();
}
if(httpRequestBase != null) {
logger.info("----releaseConnection");
httpRequestBase.releaseConnection();
}
}
}
说ææ¯æå¡ç«¯springboot(undertow)主å¨æå¼è¿æ¥ãå ·ä½åå å¦ä¸ï¼
HttpClient4åéçå ¶å®æ¯http1.1çï¼èæä¸è¾¹çæå¡å¨èªå¨å ³éçç¥è¯ç¹æ¯http1.0çï¼
http1.0 é»è®¤çè¿æ¥ï¼æå¡å¨ä¸»å¨å ³é, éè¦é¿è¿æ¥éè¦é ç½®keepalive
http1.1 é»è®¤é¿è¿æ¥ï¼å æ¤æå¡å¨ä¸ä¼ä¸»å¨å ³éï¼èhttpclient4æ们ä¸è¬ä¼ç´æ¥åhttpPost.releaseConnection(),å æ¤èªç¶æ¯æ们客æ·ç«¯ä¸»å¨å ³éäº
å æ¤ï¼å¦æè¿æ¯å¸ææå¡å¨çè¿æ¥å¹¶ä¸ä¸»å¨å ³éï¼å¯ä»¥é ç½®Connection:closeå¨http header .
äºï¼å¿è§è
常è§çæ¥å¿çº§å«æ5ç§ï¼åå«æ¯errorãwarnãinfoãdebugãtraceãæ¥å¸¸å¼åä¸ï¼æ们éè¦éæ©æ°å½çæ¥å¿çº§å«
- errorï¼é误æ¥å¿ï¼ææ¯è¾ä¸¥éçé误ï¼å¯¹æ£å¸¸ä¸å¡æå½±åï¼éè¦è¿ç»´é ç½®çæ§çï¼
- warnï¼è¦åæ¥å¿ï¼ä¸è¬çé误ï¼å¯¹ä¸å¡å½±åä¸å¤§ï¼ä½æ¯éè¦å¼åå ³æ³¨ï¼
- infoï¼ä¿¡æ¯æ¥å¿ï¼è®°å½ææ¥é®é¢çå ³é®ä¿¡æ¯ï¼å¦è°ç¨æ¶é´ãåºåå ¥åççï¼
- debugï¼ç¨äºå¼åDEBUGçï¼å ³é®é»è¾éé¢çè¿è¡æ¶æ°æ®ï¼
- traceï¼æ详ç»çä¿¡æ¯ï¼ä¸è¬è¿äºä¿¡æ¯åªè®°å½å°æ¥å¿æ件ä¸ã
é¢å å¤ææ¥å¿çº§å«ï¼å¯¹äºDEBUGãINFO级å«æ¥å¿ï¼å¿ 须使ç¨æ¡ä»¶è¾åºæè 使ç¨å ä½ç¬¦çæ¹å¼æå°ãç±äºç产ç¯å¢æ¯ç¦æ¢è¾åºDEBUGæ¥å¿ä¸æéæ©å°è¾åºINFOæ¥å¿ï¼æ以ä¸ä½¿ç¨ä¸è¿°æ¹å¼åéè¦å¤æ§è¡ä¸æ¥ä½¿ç¨å符串æ¼æ¥/åç¬¦ä¸²æ ¼å¼åæä½ï¼å½±åç¨åºæ§è¡æçã
é¿å æ ææ¥å¿æå°ï¼ç产ç¯å¢æ¯ç¦æ¢DEBUGæ¥å¿ä¸æéæ©å°è¾åºINFOæ¥å¿ã使ç¨INFOãWARN级å«æ¥è®°å½ä¸å¡è¡ä¸ºä¿¡æ¯æ¶ï¼ä¸å®è¦æ§å¶æ¥å¿è¾åºéï¼ä»¥å åºç°ç£ç空é´ä¸è¶³ãåæ¶è¦ä¸ºæ¥å¿è®¾ç½®åçççå½å¨æï¼åæ¶æ¸ çè¿ææ¥å¿ãé¿å éå¤æå°ï¼å¡å¿ å¨æ¥å¿é ç½®æ件ä¸è®¾ç½®additivity=falseã
åºå«å¯¹å¾ é误æ¥å¿ï¼WARNæ¥å¿ï¼è®°å½ä¸äºä¸å¡å¼å¸¸å¯ä»¥éè¿å¼å¯¼éè¯å°±è½æ¢å¤æ£å¸¸çæ¥å¿ä¿¡æ¯ï¼å¦ç¨æ·è¾å ¥åæ°é误ãERRORæ¥å¿ï¼è®°å½ç³»ç»é»è¾é误ãå¼å¸¸æè¿æ³éè¦ä¸å¡è§åçæ¥å¿ä¿¡æ¯ï¼éè¦äººå·¥å¹²é¢ï¼ã
ä¿è¯è®°å½å 容å®æ´ï¼æ¥å¿è®°å½çå 容å æ¬ç°åºä¸ä¸æä¿¡æ¯ä¸å¼å¸¸å æ ä¿¡æ¯ã
对äºä¸å¡ç³»ç»æ¥è¯´ï¼æ¥å¿å¯¹äºç³»ç»çæ§è½å½±åé常大ï¼ä¸éè¦çæ¥å¿å°½éä¸è¦æå°ï¼é¿å å ç¨IOèµæºã
æ£ç¡®çæ¥å¿æ ¼å¼å¦ä¸ï¼
//ç´æ¥å符串æ¼æ¥æå°(é误),æ 论æ¯ä»ä¹æ¥å¿çº§å«ï¼ç¨åºæ¯æ¬¡è¿è¡å°è¿éé½ä¼æé ä¸ä¸ªå符串
logger.debug("business is start,startTime:" + System.currentTimeMillis());
//使ç¨æ¡ä»¶å¤æ(æ£ç¡®)
if (logger.isDebugEnabled()){
logger.debug("business is start,startTime:" + System.currentTimeMillis());
}
//使ç¨å ä½ç¬¦å½¢å¼ï¼ä¸ç¨æ¯æ¬¡é½æå¨æ·»å isDebugEnabledå¤æ(æ£ç¡®)
logger.debug("business is start,startTime:{}", System.currentTimeMillis());
//é误æ¡ä¾,å¨ç³»ç»ä¸åºç°çå¾å¤
logger.info("ä¸å°ç»éç¾åSRC:{}" + signSrc);
åå°ä¸å¿ è¦ç计ç®
é误æ¡ä¾ï¼ä»»ä½çº§å«é½ä¼è¿è¡åºåå
if (CollectionUtils.isEmpty(sceneInfoList)) {
logger.debug("The house={} mei zhi sceneInfoList is empty.", JSON.toJSONString(sceneInfoList));
return sceneSuccess;
}
private void updateSupportedApplianceTypeOrSubtype(String reqId, int appId, List<String> typesFromReq, Integer limitType, List<AppApplianceType> applianceTypes) {
//å
¼å®¹æ§çå¼åè
å¹³å°è°ç¨ï¼åæå¯ä»¥å»æ
List<String> types = getApplianceTypeAfterDeal(typesFromReq);
OpenSupportedApplianceType type = new OpenSupportedApplianceType(appId, StringUtils.join(types, ";"));
Logger.debug("reqId: {},Update supported type with : {}", reqId, JsonUtil.toJson(type));
openSupportedApplianceTypeCache.updateSupportedApplianceTypeByAppId(type);
}
æ£ç¡®æ¡ä¾ï¼
if (CollectionUtils.isEmpty(sceneInfoList)) {
if(logger.isDebugEnabled()){
logger.debug("The house={} mei zhi sceneInfoList is empty.", JSON.toJSONString(sceneInfoList));
}
return sceneSuccess;
}
线ç¨å å¯è½ä½¿ç¨INFOæè æ´é«ç级å«ï¼å¦ERROR
ä¸è¦ä½¿ç¨e.printStackTrace()
é误ï¼com.midea.smart.thirdpart.access.rpc.service.impl.GatewayApplianceServiceImpl.subdeviceAddReport(Long, String)
try{
// ä¸å¡ä»£ç å¤ç
}catch(Exception e){
e.printStackTrace();
}
æ£ç¡®ï¼
try{
// ä¸å¡ä»£ç å¤ç
}catch(Exception e){
log.error("ç¨æ·æ³¨åå¼å¸¸",e);
}
- e.printStackTrace()æå°åºçå æ æ¥å¿è·ä¸å¡ä»£ç æ¥å¿æ¯äº¤éæ··åå¨ä¸èµ·çï¼é常ææ¥å¼å¸¸æ¥å¿ä¸å¤ªæ¹ä¾¿ã
- e.printStackTrace()è¯å¥äº§ççå符串记å½çæ¯å æ ä¿¡æ¯ï¼å¦æä¿¡æ¯å¤ªé¿å¤ªå¤ï¼å符串常éæ± æå¨çå åå没æ空é´äº,å³å å满äºï¼ç¨æ·ç请æ±å°±é»å¡äº
è¾åºå ¨é¨é误信æ¯
é误ï¼
try {
//ä¸å¡ä»£ç å¤ç
} catch (Exception e) {
// é误
LOG.error('ä½ çç¨åºæå¼å¸¸å¦');
}
é误ï¼
try {
//ä¸å¡ä»£ç å¤ç
} catch (Exception e) {
// é误
LOG.error('ä½ çç¨åºæå¼å¸¸å¦', e.getMessage());
}
e.getMessage()ä¸ä¼è®°å½è¯¦ç»çå æ å¼å¸¸ä¿¡æ¯ï¼åªä¼è®°å½é误åºæ¬æè¿°ä¿¡æ¯ï¼ä¸å©äºææ¥é®é¢ã
æ£ç¡®ï¼
try {
//ä¸å¡ä»£ç å¤ç
} catch (Exception e) {
LOG.error('ä½ çç¨åºæå¼å¸¸å¦', e);
}
ç¦æ¢å¨çº¿ä¸ç¯å¢å¼å¯ debug
ç¦æ¢å¨çº¿ä¸ç¯å¢å¼å¯debugï¼è¿ä¸ç¹é常éè¦ã
å 为ä¸è¬ç³»ç»çdebugæ¥å¿ä¼å¾å¤ï¼å¹¶ä¸åç§æ¡æ¶å¦Spring,Mybatis大éä½¿ç¨ debugçæ¥å¿ï¼çº¿ä¸å¼å¯debugä¸ä¹ å¯è½ä¼æ满ç£çï¼å½±åä¸å¡ç³»ç»çæ£å¸¸è¿è¡ã
è®°å½å¼å¸¸ï¼åæåºå¼å¸¸
log.error("IO exception", e);
throw new MyException(e);
- è¿æ ·å®ç°çè¯ï¼é常ä¼ææ ä¿¡æ¯æå°ä¸¤æ¬¡ãè¿æ¯å 为æè·äºMyExceptionå¼å¸¸çå°æ¹ï¼è¿ä¼åæå°ä¸æ¬¡ã
- è¿æ ·çæ¥å¿è®°å½ï¼æè å è£ ååæåºå»ï¼ä¸è¦åæ¶ä½¿ç¨ï¼å¦åä½ çæ¥å¿çèµ·æ¥ä¼è®©äººå¾è¿·æã
- æä½³å®è·µç´æ¥æåºå¼å¸¸ ï¼å¨ç»ä¸å¼å¸¸å¤çå¤è¾åºå¼å¸¸ä¿¡æ¯ãå¦filterç»æï¼aopåç½®ç»æï¼æè ExceptionHandlerç»ä¸å¼å¸¸å¤çã
é¿å éå¤æå°æ¥å¿
if(user.isVip()){
log.info("该ç¨æ·æ¯ä¼å,Id:{}",user,getUserId());
//åä½ï¼å¯ä»¥è·åé¢çæ¥å¿å并ä¸èµ·
log.info("å¼å§å¤çä¼åé»è¾,id:{}",user,getUserId());
//ä¼åé»è¾
}else{
//éä¼åé»è¾
}
å¼æ¥æ¥å¿
æ¨è使ç¨log4j2ï¼ä¹æ¯æ°çæ¬springbootå®æ¹æ¨èï¼ç»åäºlogbackçç¹æ§å¯¹log4jè¿è¡äºå级ï¼æ§è½ä¼è¶ã
- æ¥å¿æç»ä¼è¾åºå°æ件æè å ¶å®è¾åºæµä¸çï¼IOæ§è½ä¼æè¦æ±çãå¦æå¼æ¥ï¼å°±å¯ä»¥æ¾èæåIOæ§è½ã
- 使ç¨å¼æ¥çæ¹å¼æ¥è¾åºæ¥å¿ã以logback为ä¾ï¼è¦é ç½®å¼æ¥ï¼ä½¿ç¨AsyncAppender
<appender name="FILE_ASYNC" class="ch.qos.logback.classic.AsyncAppender">
<appender-ref ref="ASYNC"/>
</appender>
模ææ¡ä¾
大家å¯ä»¥è®¿é®ä»¥devæºå¨10.xxx.0.121 æ§è¡ä»¥ä¸åæµèæ¬ï¼
ab -n 1000000 -c 590 http://10.xxx.1.136:8800/thread/request/defered/merge?applianceCode=211106233032263
æ´æ¹æ¥å¿çº§å«ä¸ºdebug对以ä¸æ¥å£è¿è¡åæµãåæµè¿ç¨ä¸å¯¼åºçº¿ç¨å¿«ç §ä¿¡æ¯ï¼çå°éé¢æ大éçlog4j2ç¸å ³çé»å¡ä»£ç ãåæ¶ä¼åç°æ´æ¹äºæ¥å¿çº§å«ååæµQPSä¸éäºå¾å¤ã
å ï¼å½ä»¤/èæ¬
示ä¾ä¸å¾å工表 建表è¯å¥ï¼
CREATE TABLE t_employees (
`emp_no` BIGINT NOT NULL,
`name` VARCHAR(14) NOT NULL,
`gender` ENUM ('M','F') NOT NULL DEFAULT 'M',
`age` INT NOT NULL DEFAULT 0,
`department_id` INT NOT NULL DEFAULT 0,
`hire_date` DATE NOT NULL DEFAULT '1970-01-01',
`birth_date` DATE NOT NULL DEFAULT '1970-01-01',
`address` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`emp_no`),
KEY `idx_name_age_address` (`name`,`age`,`address`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
ALTER TABLE t_employees ADD COLUMN mobile VARCHAR(32) DEFAULT NULL COMMENT 'ææºå·' AFTER age;
ALTER TABLE `t_employees` ADD UNIQUE (`mobile`)
ALTER TABLE t_employees ADD COLUMN email VARCHAR(20) DEFAULT NULL COMMENT 'email' AFTER mobile;
ALTER TABLE `t_employees` ADD key(email(9));
ALTER TABLE `t_employees` ADD INDEX idx_department_id (`department_id`);
示ä¾ä¸æé æ°æ®-åå¨è¿ç¨ï¼
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE COUNT INT DEFAULT 0;
DECLARE chars_str VARCHAR(450) DEFAULT 'èµµé±åæå¨å´éçå¯é楮å«èæ²é©æ¨æ±ç§¦å°¤è®¸ä½åæ½å¼ åæ¹ä¸¥åééé¶å§æè°¢é¹å»ææ°´çª¦ç« äºèæ½èå¥èå½éé²é¦æ马èå¤è±æ¹ä¿ä»»è¢æ³é
é²å²åè´¹å»å²èé·è´ºåªæ±¤æ»æ®·ç½æ¯éé¬å®å¸¸ä¹äºæ¶å
ç®åé½åº·ä¼ä½å
å顾åå¹³é»åç©è§å°¹å§éµæ¹æ±ªç¥æ¯ç¦¹çç±³è´æè§è®¡ä¼ææ´è°å®è
åºç纪èå±é¡¹ç¥è£æ¢æé®èé½å¸å£éº»å¼ºè´¾è·¯å¨å±æ±ç«¥é¢éæ¢
çæåéºå¾ä¸éªé«å¤è¡ç°æ¨è¡åéèä¸æ¯æ¯æ管å¢è«ç»æ¿è£ç¼ªå¹²è§£åºå®ä¸å®£è´²ééåææ´ªå
诸左ç³å´åé®é¾ç¨åµé¢æ»è£´éè£ç¿èç¾æ¼æ ç麹家å°è®ç¾¿å¨é³æ±²é´ç³æ¾äºæ®µå¯å·«ä¹ç¦å·´å¼ç§é山谷车侯å®è¬å
¨éçä»°ç§ä»²ä¼å®«å®ä»æ ¾æ´çæåæç¥æ¦ç¬¦åæ¯è©¹æé¾å¶å¹¸å¸é¶éé»èèå°å®¿ç½æè²é°ä»éç´¢å¸ç±èµåèºå± èæ± ä¹é´éè¥è½èåé»èå
ç¿è°è´¡å³é姬ç³æ¶å µåå®°é¦éé¤ç©æ¡æ¡æ¿®ç寿éè¾¹æçåé浦å°å温å«åºææ´ç¿éå
æ
è¿è¹ä¹ 宦è¾é±¼å®¹åå¤ææ
æå»åº¾ç»æ¨å±
è¡¡æ¥é½è¿æ»¡å¼å¡å½æå¯å¹¿ç¦éä¸æ¬§æ®³æ²å©èè¶å¤éå¸å·©åèæå¾æèå·è¨¾è¾éé£ç®é¥¶ç©ºæ¾æ¯æ²ä¹å
»é 须丰巢å
³è¯ç¸æ¥åè红游竺æéççæ¡å
¬ä»ç£ææ¥éæ³æ±é¢æ¶é¦å½æµ·å²³å¸
ç¼äº¢åµåæç´åçä½ä½´ä¼¯èµå¢¨å谯笪年ç±é³ä½èµµé±åæå¨å´éç';
DECLARE birth_date DATE DEFAULT '1970-01-01';
DECLARE init_date DATE DEFAULT '1970-01-01';
DECLARE hire_date DATE DEFAULT NULL;
DECLARE gender DATE DEFAULT 'M';
DECLARE i INT DEFAULT 1;
SET autocommit = 0; #设置æå¨æ交äºå¡
WHILE i<=10000000 DO
# æ°å¢æ°æ®
SET birth_date = DATE_ADD(init_date,INTERVAL FLOOR(1 + RAND()*4000) DAY);
IF(birth_date > '2002-01-01') THEN
SET init_date = '1970-01-01';
SET birth_date = DATE_ADD(init_date,INTERVAL FLOOR(1 + RAND()*4000) DAY);
END IF;
SET hire_date = DATE_ADD(birth_date,INTERVAL 6570+FLOOR(1 + RAND()*500) DAY);
INSERT INTO t_employees
( emp_no, birth_date, `name` , gender,age,hire_date,department_id) VALUES
( 100000001 + i,birth_date, CONCAT(SUBSTRING(chars_str , FLOOR(1 + RAND()*444),1),"æ"), CASE WHEN MOD(10001 + i,2)=0 THEN 'M' ELSE 'F' END,
TIMESTAMPDIFF(YEAR, birth_date, NOW()),hire_date,10001); #æ§è¡çsqlè¯å¥
SET i = i+1;
END WHILE;
commit;
END $
# è°ç¨åå¨è¿ç¨
CALL proc_initData();