MySQL æ§è½ä¼åæè·¯åå·¥å ·
ä¸ãä¼åæè·¯
ä½ä¸ºæ¶æå¸æè å¼å人åï¼è¯´å°æ°æ®åºæ§è½ä¼åï¼ä½ çæè·¯æ¯ä»ä¹æ ·çï¼
æè å ·ä½ä¸ç¹ï¼å¦æå¨é¢è¯çæ¶åéå°è¿ä¸ªé®é¢ï¼ä½ ä¼ä»åªäºç»´åº¦æ¥ä¼åæ°æ®åºï¼ä½ ä¼æä¹åçï¼
æ们说å°æ§è½è°ä¼ï¼å¤§é¨åæ¶åæ³è¦å®ç°çç®æ æ¯è®©æ们çæ¥è¯¢æ´å¿«ãä¸ä¸ªæ¥è¯¢çå¨ä½åæ¯ç±å¾å¤ä¸ªç¯èç»æçï¼æ¯ä¸ªç¯èé½ä¼æ¶èæ¶é´ã
æ们è¦åå°æ¥è¯¢ææ¶èçæ¶é´ï¼å°±è¦ä»æ¯ä¸ä¸ªç¯èå ¥æã
äºã è¿æ¥ââé ç½®ä¼å
第ä¸ä¸ªç¯èæ¯å®¢æ·ç«¯è¿æ¥å°æå¡ç«¯ï¼è¿æ¥è¿ä¸åæå¯è½æ¯æå¡ç«¯è¿æ¥æ°ä¸å¤å¯¼è´åºç¨ç¨åºè·åä¸å°è¿æ¥ãæ¯å¦æ¥äºä¸ä¸ª Mysql: error1040: Too many connections çé误ã
æ们å¯ä»¥ä»ä¸¤ä¸ªæ¹é¢æ¥è§£å³è¿æ¥æ°ä¸å¤çé®é¢ï¼
1ãä»æå¡ç«¯æ¥è¯´ï¼æ们å¯ä»¥å¢å æå¡ç«¯çå¯ç¨è¿æ¥æ°ã
å¦ææå¤ä¸ªåºç¨æè å¾å¤è¯·æ±åæ¶è®¿é®æ°æ®åºï¼è¿æ¥æ°ä¸å¤çæ¶åï¼æ们å¯ä»¥ï¼
ï¼1ï¼ä¿®æ¹é ç½®åæ°å¢å å¯ç¨è¿æ¥æ°ï¼ä¿®æ¹ max_connections ç大å°ï¼
ï¼2ï¼æè ï¼æè åæ¶éæ¾ä¸æ´»å¨çè¿æ¥ã交äºå¼åé交äºå¼ç客æ·ç«¯çé»è®¤è¶ æ¶æ¶é´é½æ¯ 28800
ç§ï¼8 å°æ¶ï¼æ们å¯ä»¥æè¿ä¸ªå¼è°å°ã
show global variables like 'wait_timeout'; --åæ¶éæ¾ä¸æ´»å¨çè¿æ¥ï¼æ³¨æä¸è¦éæ¾è¿æ¥æ± è¿
å¨ä½¿ç¨çè¿æ¥
2ãä»å®¢æ·ç«¯æ¥è¯´ï¼å¯ä»¥åå°ä»æå¡ç«¯è·åçè¿æ¥æ°ï¼å¦ææ们æ³è¦ä¸æ¯æ¯ä¸æ¬¡æ§è¡SQL é½å建ä¸
个æ°çè¿æ¥ï¼è¿ä¸ªæ¶åæ们å¯ä»¥å¼å ¥è¿æ¥æ± ï¼å®ç°è¿æ¥çéç¨ã
è¿æ¥æ± ç使ç¨å±é¢
ORM å±é¢ï¼MyBatis èªå¸¦äºä¸ä¸ªè¿æ¥æ± ï¼ï¼
æè 使ç¨ä¸ç¨çè¿æ¥æ± å·¥å ·ï¼é¿éç DruidãSpring Boot 2.x çæ¬é»è®¤çè¿æ¥æ± Hikariãèçç DBCP å C3P0ï¼ã
æ们è¿é说å°äºä»æ°æ®åºé ç½®çå±é¢å»ä¼åæ°æ®åºãä¸ç®¡æ¯æ°æ®åºæ¬èº«çé ç½®ï¼è¿æ¯å®è£ è¿ä¸ªæ°æ®
åºæå¡çæä½ç³»ç»çé ç½®ï¼å¯¹äºé ç½®è¿è¡ä¼åï¼æç»çç®æ é½æ¯ä¸ºäºæ´å¥½å°åæ¥ç¡¬ä»¶æ¬èº«çæ§è½ï¼å æ¬CPUãå åãç£çãç½ç»ã
å¨ä¸åç硬件ç¯å¢ä¸ï¼æä½ç³»ç»å MySQL çåæ°çé ç½®æ¯ä¸åçï¼æ²¡ææ åçé ç½®ã
MySQLä¸æå¾å¤é ç½®åæ°ï¼å æ¬åç§å¼å ³åæ°å¼çé ç½®ï¼å¤§å¤æ°åæ°é½æä¾äºä¸ä¸ªé»è®¤å¼ï¼æ¯å¦é»è®¤ç buffer_pool_sizeï¼é»è®¤ç页大å°ï¼InnoDB 并å线ç¨æ°ççã
è¿äºé»è®¤é ç½®å¯ä»¥æ»¡è¶³å¤§é¨åæ åµçéæ±ï¼é¤éæç¹æ®çéæ±ï¼å¨æ¸ æ¥åæ°çå«ä¹çæ åµä¸åå»ä¿®
æ¹å®ãä¿®æ¹é ç½®çå·¥ä½ä¸è¬ç±ä¸ä¸ç DBA å®æã
é¤äºåç设置æå¡ç«¯çè¿æ¥æ°å客æ·ç«¯çè¿æ¥æ± 大å°ä¹å¤ï¼æ们å¯ä»¥å¼å ¥ç¼åã
ä¸ãç¼åââæ¶æä¼å
3.1 ç¼å
å¨åºç¨ç³»ç»ç并åæ°é常大çæ åµä¸ï¼å¦æ没æç¼åï¼ä¼é æ两个é®é¢ï¼ä¸æ¹é¢æ¯ä¼ç»æ°æ®åºå¸¦æ¥
å¾å¤§çååãå¦ä¸æ¹é¢ï¼ä»åºç¨çå±é¢æ¥è¯´ï¼æä½æ°æ®çé度ä¹ä¼åå°å½±åã
æ们å¯ä»¥ç¨ç¬¬ä¸æ¹çç¼åæå¡æ¥è§£å³è¿ä¸ªé®é¢ï¼ä¾å¦ Redisã
è¿è¡ç¬ç«çç¼åæå¡ï¼å±äºæ¶æå±é¢çä¼åã
为äºåå°åå°æ°æ®åºæå¡å¨ç读åååï¼å¨æ¶æå±é¢æ们è¿å¯ä»¥åå ¶ä»ä¼åæªæ½ã
3.2 主ä»å¤å¶
å¦æåå°æ°æ®åºæå¡æ»¡è¶³ä¸äºè®¿é®éæ±ï¼é£æ们å¯ä»¥åæ°æ®åºçé群æ¹æ¡ã
é群çè¯å¿ ç¶ä¼é¢ä¸´ä¸ä¸ªé®é¢ï¼å°±æ¯ä¸åçèç¹ä¹é´æ°æ®ä¸è´æ§çé®é¢ãå¦æåæ¶è¯»åå¤å°æ°æ®åº
èç¹ï¼æä¹è®©ææçèç¹æ°æ®ä¿æä¸è´ï¼
è¿ä¸ªæ¶åæ们éè¦ç¨å°å¤å¶ææ¯ï¼replicationï¼ï¼è¢«å¤å¶çèç¹ç§°ä¸º masterï¼å¤å¶çèç¹ç§°ä¸ºslaveã
主ä»å¤å¶æ¯æä¹å®ç°çå¢ï¼å¨Mysqlæ¶æä¸å é¨æ¨¡åæ们说è¿ï¼æ´æ°è¯å¥ä¼è®°å½ binlogï¼å®æ¯ä¸ç§é»è¾æ¥å¿ã
æäºè¿ä¸ª binlogï¼ä»æå¡å¨ä¼è·å主æå¡å¨ç binlog æ件ï¼ç¶å解æéé¢ç SQL è¯å¥ï¼å¨ä»æå¡
å¨ä¸é¢æ§è¡ä¸éï¼ä¿æ主ä»çæ°æ®ä¸è´ã
è¿éé¢æ¶åå°ä¸ä¸ªçº¿ç¨ï¼è¿æ¥å° master è·å binlogï¼å¹¶ä¸è§£æ binlog åå ¥ä¸ç»§æ¥å¿ï¼è¿ä¸ªçº¿ç¨å«
å I/O 线ç¨ã
Master èç¹ä¸æä¸ä¸ª log dump 线ç¨ï¼æ¯ç¨æ¥åé binlog ç» slave çã
ä»åºç SQL 线ç¨ï¼æ¯ç¨æ¥è¯»å relay logï¼ææ°æ®åå ¥å°æ°æ®åºçã
è¿ä¸ªæ¯ä¸»ä»å¤å¶æ¶åå°çä¸ä¸ªçº¿ç¨ã
åäºä¸»ä»å¤å¶çæ¹æ¡ä¹åï¼æ们åªææ°æ®åå ¥ master èç¹ï¼è读ç请æ±å¯ä»¥åæ å°slave èç¹ãæ
们æè¿ç§æ¹æ¡å«å读åå离ã
读åå离å¯ä»¥ä¸å®ç¨åº¦ä½åè½»æ°æ®åºæå¡å¨ç访é®ååï¼ä½æ¯éè¦ç¹å«æ³¨æ主ä»æ°æ®ä¸è´æ§çé®é¢ã
æ们å¨åäºä¸»ä»å¤å¶ä¹åï¼å¦æå个 master èç¹æè åå¼ è¡¨åå¨çæ°æ®è¿å¤§çæ¶åï¼æ¯å¦ä¸å¼ 表
æä¸äº¿çæ°æ®ï¼å表çæ¥è¯¢æ§è½è¿æ¯ä¼ä¸éï¼æ们è¦è¿ä¸æ¥å¯¹åå°æ°æ®åºèç¹çæ°æ®è¿è¡æåï¼è¿ä¸ªå°±æ¯ååºå表ã
3.3 ååºå表
åç´ååºï¼åå°å¹¶åååãæ°´å¹³å表ï¼è§£å³åå¨ç¶é¢ã
åç´ååºçåæ³ï¼æä¸ä¸ªæ°æ®åºæç §ä¸å¡æåæä¸åçæ°æ®åºï¼
æ°´å¹³ååºå表çåæ³ï¼æåå¼ è¡¨çæ°æ®æç §ä¸å®çè§ååå¸å°å¤ä¸ªæ°æ®åºã
以ä¸æ¯æ¶æå±é¢çä¼åï¼å¯ä»¥ç¨ç¼åï¼ä¸»ä»ï¼ååºå表ã
第ä¸ä¸ªç¯èï¼
解æå¨ï¼è¯æ³åè¯æ³åæï¼ä¸»è¦ä¿è¯è¯å¥çæ£ç¡®æ§ï¼è¯å¥ä¸åºé就没é®é¢ãç± Sever èªå·±å¤çã
第åæ¥ï¼ä¼åå¨
å ä¼åå¨ââSQL è¯å¥åæä¸ä¼å
ä¼åå¨å°±æ¯å¯¹æ们ç SQL è¯å¥è¿è¡åæï¼çææ§è¡è®¡åã
é®é¢ï¼å¨æ们å项ç®çæ¶åï¼ææ¶ä¼æ¶å° DBA çé®ä»¶ï¼éé¢ååºäºæ们项ç®ä¸å 个èæ¶æ¯è¾é¿çæ¥
询è¯å¥ï¼è®©æ们å»ä¼åï¼è¿äºè¯å¥æ¯ä»åªéæ¥çå¢ï¼
æ们çæå¡å±æ¯å¤©æ§è¡äºè¿ä¹å¤ SQL è¯å¥ï¼å®æä¹ç¥éåªäº SQL è¯å¥æ¯è¾æ ¢å¢ï¼
第ä¸æ¥ï¼æ们è¦æ SQL æ§è¡æ åµè®°å½ä¸æ¥ã
4.1 æ ¢æ¥è¯¢æ¥å¿ slow query log
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
4.1.1 æå¼æ ¢æ¥å¿å¼å ³
å 为å¼å¯æ ¢æ¥è¯¢æ¥å¿æ¯æ代价çï¼è· bin logãoptimizer-trace ä¸æ ·ï¼ï¼æ以å®é»è®¤æ¯å ³éçï¼
é¤äºè¿ä¸ªå¼å ³ï¼è¿æä¸ä¸ªåæ°ï¼æ§å¶æ§è¡è¶ è¿å¤é¿æ¶é´ç SQL æè®°å½å°æ ¢æ¥å¿ï¼é»è®¤æ¯ 10 ç§ã
å¯ä»¥ç´æ¥å¨æä¿®æ¹åæ°ï¼éå¯å失æï¼ã
set @@global.slow_query_log=1;--1å¼å¯ï¼0å
³éï¼éå¯å失æ
set @@global.long_query_time=3;--mysqlé»è®¤çæ
¢æ¥è¯¢æ¶é´æ¯10ç§ï¼å¦å¼ä¸ä¸ªçªå£åæä¼æ¥å°ææ°å¼
show variables like'%long_query%';
show variables like'%slow_query%';
æè ä¿®æ¹é ç½®æ件 my.cnfã
以ä¸é ç½®å®ä¹äºæ ¢æ¥è¯¢æ¥å¿çå¼å ³ãæ ¢æ¥è¯¢çæ¶é´ãæ¥å¿æ件çåæ¾è·¯å¾ã
slow_query_log=ON
long_query_time=2
slow_query_log_file=/var/lib/mysql/localhost-slow.log
模ææ ¢æ¥è¯¢ï¼
æ¥è¯¢ user_innodb 表ç 500 ä¸æ°æ®ï¼æ£æ¥æ¯ä¸æ¯æ²¡æç´¢å¼ï¼ã
4.1.2 æ ¢æ¥å¿åæ
1ãæ¥å¿å 容
show global status like 'slow_queries'; -- æ¥çæå¤å°æ
¢æ¥è¯¢
show variables like '%slow_query%'; -- è·åæ
¢æ¥å¿ç®å½
cat /var/lib/mysql/ localhost-slow.log
2ãmysqldumpslow
https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
MySQL æä¾äº mysqldumpslow çå·¥å ·ï¼å¨ MySQL ç bin ç®å½ä¸ã
ä¾å¦ï¼æ¥è¯¢ç¨æ¶æå¤ç 10 æ¡æ ¢ SQLï¼
mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log
Count 代表è¿ä¸ª SQL æ§è¡äºå¤å°æ¬¡ï¼
Time 代表æ§è¡çæ¶é´ï¼æ¬å·éé¢æ¯ç´¯è®¡æ¶é´ï¼
Lock 表示éå®çæ¶é´ï¼æ¬å·æ¯ç´¯è®¡ï¼
Rows 表示è¿åçè®°å½æ°ï¼æ¬å·æ¯ç´¯è®¡ã
é¤äºæ ¢æ¥è¯¢æ¥å¿ä¹å¤ï¼è¿æä¸ä¸ª SHOW PROFILE å·¥å ·å¯ä»¥ä½¿ç¨
4.2 SHOW PROFILE
https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
SHOW PROFILE æ¯è°·æé«çº§æ¶æå¸ Jeremy Cole è´¡ç®ç» MySQL 社åºçï¼å¯ä»¥æ¥çSQL è¯å¥æ§è¡ç
æ¶å使ç¨çèµæºï¼æ¯å¦ CPUãIO çæ¶èæ åµã
å¨ SQL ä¸è¾å ¥ help profile å¯ä»¥å¾å°è¯¦ç»ç帮å©ä¿¡æ¯
4.2.1 æ¥çæ¯å¦å¼å¯
select @@profiling;
set @@profiling=1;
4.2.2 æ¥ç profile ç»è®¡
ï¼å½ä»¤æå带ä¸ä¸ª sï¼
æ¥çæåä¸ä¸ª SQL çæ§è¡è¯¦ç»ä¿¡æ¯ï¼ä»ä¸æ¾åºèæ¶è¾å¤çç¯èï¼æ²¡æ sï¼ã
6.2E-5ï¼å°æ°ç¹å·¦ç§» 5 ä½ï¼ä»£è¡¨ 0.000062 ç§ã
ä¹å¯ä»¥æ ¹æ® ID æ¥çæ§è¡è¯¦ç»ä¿¡æ¯ï¼å¨åé¢å¸¦ä¸ for query + IDã
é¤äºæ ¢æ¥å¿å show profileï¼å¦æè¦åæåºå½åæ°æ®åºä¸æ§è¡çæ ¢ç SQLï¼è¿å¯ä»¥éè¿æ¥çè¿è¡çº¿
ç¨ç¶æåæå¡å¨è¿è¡ä¿¡æ¯ãåå¨å¼æä¿¡æ¯æ¥åæã
4.2.3 å ¶ä»ç³»ç»å½ä»¤
show processlist è¿è¡çº¿ç¨
https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html
è¿æ¯å¾éè¦çä¸ä¸ªå½ä»¤ï¼ç¨äºæ¾ç¤ºç¨æ·è¿è¡çº¿ç¨ãå¯ä»¥æ ¹æ® id å· kill 线ç¨ã
ä¹å¯ä»¥æ¥è¡¨ï¼ææä¸æ ·ï¼ï¼å¯ä»¥ group order by äºï¼
show status æå¡å¨è¿è¡ç¶æ
说æï¼https://dev.mysql.com/doc/refman/5.7/en/show-status.html
详ç»åæ°ï¼https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
SHOW STATUS ç¨äºæ¥ç MySQL æå¡å¨è¿è¡ç¶æï¼éå¯åä¼æ¸ 空ï¼ï¼æ sessionå global 两ç§ä½
ç¨åï¼æ ¼å¼ï¼åæ°-å¼ã
å¯ä»¥ç¨ like 带éé 符è¿æ»¤ã
show engine åå¨å¼æè¿è¡ä¿¡æ¯
https://dev.mysql.com/doc/refman/5.7/en/show-engine.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-standard-monitor.html
show engine ç¨æ¥æ¾ç¤ºåå¨å¼æçå½åè¿è¡ä¿¡æ¯ï¼å æ¬äºå¡ææç表éãè¡éä¿¡æ¯ï¼äºå¡çéçå¾
æ åµï¼çº¿ç¨ä¿¡å·éçå¾ ï¼æ件 IO 请æ±ï¼buffer pool ç»è®¡ä¿¡æ¯ã
ä¾å¦ï¼
å¦æéè¦å°çæ§ä¿¡æ¯è¾åºå°éè¯¯ä¿¡æ¯ error log ä¸ï¼15 ç§éä¸æ¬¡ï¼ï¼å¯ä»¥å¼å¯è¾åºã
show variables like 'innodb_status_output%'; -- å¼å¯è¾åºï¼
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
æ们ç°å¨å·²ç»ç¥éäºè¿ä¹å¤åææå¡å¨ç¶æãåå¨å¼æç¶æã线ç¨è¿è¡ä¿¡æ¯çå½ä»¤ï¼å¦æè®©ä½ å»å
ä¸ä¸ªæ°æ®åºçæ§ç³»ç»ï¼ä½ ä¼æä¹åï¼
å ¶å®å¾å¤å¼æºçæ ¢æ¥è¯¢æ¥å¿çæ§å·¥å ·ï¼ä»ä»¬çåçå ¶å®ä¹é½æ¯è¯»åçç³»ç»çåéåç¶æã
ç°å¨æ们已ç»ç¥éåªäº SQL æ ¢äºï¼ä¸ºä»ä¹æ ¢å¢ï¼æ ¢å¨åªéï¼
MySQL æä¾äºä¸ä¸ªæ§è¡è®¡åçå·¥å ·ï¼å¨æ¶æä¸æ们æ讲å°ï¼ä¼åå¨æç»çæçå°±æ¯ä¸ä¸ªæ§è¡è®¡
åï¼ï¼å ¶ä»æ°æ®åºï¼ä¾å¦ Oracle ä¹æ类似çåè½ã
éè¿ EXPLAIN æ们å¯ä»¥æ¨¡æä¼åå¨æ§è¡ SQL æ¥è¯¢è¯å¥çè¿ç¨ï¼æ¥ç¥é MySQL æ¯æä¹å¤çä¸æ¡
SQL è¯å¥çãéè¿è¿ç§æ¹å¼æ们å¯ä»¥åæè¯å¥æè 表çæ§è½ç¶é¢ã
MySQL 5.6.3以ååªè½åæ SELECT; MySQL5.6.3以åå°±å¯ä»¥åæupdateãdeleteãinsert äºã
4.3 EXPLAIN æ§è¡è®¡å
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
æ们å å建ä¸å¼ 表ãä¸å¼ 课ç¨è¡¨ï¼ä¸å¼ èå¸è¡¨ï¼ä¸å¼ èå¸èç³»æ¹å¼è¡¨ï¼æ²¡æä»»ä½ç´¢å¼ï¼ã
DROP TABLE
IF
EXISTS course;
CREATE TABLE `course` ( `cid` INT ( 3 ) DEFAULT NULL, `cname` VARCHAR ( 20 )
DEFAULT NULL, `tid` INT ( 3 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET =
utf8mb4;
DROP TABLE
IF
EXISTS teacher;
CREATE TABLE `teacher` ( `tid` INT ( 3 ) DEFAULT NULL, `tname` VARCHAR ( 20 )
DEFAULT NULL, `tcid` INT ( 3 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET =
utf8mb4;
DROP TABLE
IF
EXISTS teacher_contact;
CREATE TABLE `teacher_contact` ( `tcid` INT ( 3 ) DEFAULT NULL, `phone` VARCHAR
( 200 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
INSERT INTO `course`
VALUES
( '1', 'mysql', '1' );
INSERT INTO `course`
VALUES
( '2', 'jvm', '1' );
INSERT INTO `course`
VALUES
( '3', 'juc', '2' );
INSERT INTO `course`
VALUES
( '4', 'spring', '3' );
INSERT INTO `teacher`
VALUES
( '1', 'bobo', '1' );
INSERT INTO `teacher`
VALUES
( '2', 'jim', '2' );
INSERT INTO `teacher`
VALUES
( '3', 'dahai', '3' );
INSERT INTO `teacher_contact`
VALUES
( '1', '13688888888' );
INSERT INTO `teacher_contact`
VALUES
( '2', '18166669999' );
INSERT INTO `teacher_contact`
VALUES
( '3', '17722225555' );
explain çç»ææå¾å¤çå段ï¼æ们详ç»å°åæä¸ä¸ã
å 确认ä¸ä¸ç¯å¢ï¼
select version();
show variables like '%engine%';
4.3.1 id
id æ¯æ¥è¯¢åºåç¼å·ã
id å¼ä¸åçæ¶åï¼å æ¥è¯¢ id å¼å¤§çï¼å 大åå°ï¼ã
-- æ¥è¯¢ mysql 课ç¨çèå¸ææºå·
EXPLAIN SELECT
tc.phone
FROM
teacher_contact tc
WHERE
tcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course
c WHERE c.cname = 'mysql' ) );
æ¥è¯¢é¡ºåºï¼course cââteacher tââteacher_contact tcã
å æ¥è¯¾ç¨è¡¨ï¼åæ¥èå¸è¡¨ï¼æåæ¥èå¸èç³»æ¹å¼è¡¨ãåæ¥è¯¢åªè½ä»¥è¿ç§æ¹å¼è¿è¡ï¼åªææ¿å°å å±çç»
æä¹åæè½è¿è¡å¤å±çæ¥è¯¢ã
id å¼ç¸åï¼ä»ä¸å¾ä¸ï¼
-- æ¥è¯¢è¯¾ç¨ ID 为 2ï¼æè
è系表 ID 为 3 çèå¸
EXPLAIN SELECT
t.tname,
c.cname,
tc.phone
FROM
teacher t,
course c,
teacher_contact tc
WHERE
t.tid = c.tid
AND t.tcid = tc.tcid
AND ( c.cid = 2 OR tc.tcid = 3 );
id å¼ç¸åæ¶ï¼è¡¨çæ¥è¯¢é¡ºåºæ¯ä»ä¸å¾ä¸é¡ºåºæ§è¡ãä¾å¦è¿æ¬¡æ¥è¯¢ç id é½æ¯ 1ï¼æ¥è¯¢ç顺åºæ¯
teacher tï¼3 æ¡ï¼ââcourse cï¼4 æ¡ï¼ââteacher_contact tcï¼3 æ¡ï¼ã
æ¢æç¸åä¹æä¸å
å¦æ ID æç¸åä¹æä¸åï¼å°±æ¯ ID ä¸åçå 大åå°ï¼ID ç¸åçä»ä¸å¾ä¸ã
4.3.2 select type æ¥è¯¢ç±»å
è¿é并没æåä¸¾å ¨é¨ï¼å ¶å®ï¼DEPENDENT UNIONãDEPENDENT SUBQUERYãMATERIALIZEDãUNCACHEABLE SUBQUERYãUNCACHEABLE UNIONï¼ã
ä¸é¢å举äºä¸äºå¸¸è§çæ¥è¯¢ç±»åï¼
SIMPLE
ç®åæ¥è¯¢ï¼ä¸å å«åæ¥è¯¢ï¼ä¸å å«å ³èæ¥è¯¢ unionã
åçä¸ä¸ªå å«åæ¥è¯¢çæ¡ä¾ï¼
-- æ¥è¯¢ mysql 课ç¨çèå¸ææºå·
EXPLAIN SELECT
tc.phone
FROM
teacher_contact tc
WHERE
tcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course
c WHERE c.cname = 'mysql' ) );
PRIMARY
åæ¥è¯¢ SQL è¯å¥ä¸ç主æ¥è¯¢ï¼ä¹å°±æ¯æå¤é¢çé£å±æ¥è¯¢ã
SUBQUERY
åæ¥è¯¢ä¸ææçå å±æ¥è¯¢é½æ¯ SUBQUERY ç±»åçã
DERIVED
è¡çæ¥è¯¢ï¼è¡¨ç¤ºå¨å¾å°æç»æ¥è¯¢ç»æä¹åä¼ç¨å°ä¸´æ¶è¡¨ãä¾å¦ï¼
-- æ¥è¯¢ ID 为 1 æ 2 çèå¸ææç课ç¨
EXPLAIN SELECT
cr.cname
FROM
( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid =
2 ) cr;
对äºå ³èæ¥è¯¢ï¼å æ§è¡å³è¾¹ç tableï¼UNIONï¼ï¼åæ§è¡å·¦è¾¹ç tableï¼ç±»åæ¯DERIVED
UNION
ç¨å°äº UNION æ¥è¯¢ãåä¸ä¾ã
UNION RESULT
主è¦æ¯æ¾ç¤ºåªäºè¡¨ä¹é´åå¨ UNION æ¥è¯¢ã<union2,3>代表 id=2 å id=3 çæ¥è¯¢åå¨ UNIONãå
ä¸ä¾ã
4.3.3 type è¿æ¥ç±»å
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
ææçè¿æ¥ç±»åä¸ï¼ä¸é¢çæ好ï¼è¶å¾ä¸è¶å·®ã
å¨å¸¸ç¨çé¾æ¥ç±»åä¸ï¼system > const > eq_ref > ref > range > index > all
è¿ é 并 没 æ å 举 å ¨ é¨ ï¼ å ¶ ä» ï¼ fulltext ã ref_or_null ã index_merger ãunique_subqueryãindex_subqueryï¼ã
以ä¸è®¿é®ç±»åé¤äº allï¼é½è½ç¨å°ç´¢å¼ã
const
主é®ç´¢å¼æè å¯ä¸ç´¢å¼ï¼åªè½æ¥å°ä¸æ¡æ°æ®ç SQLã
DROP TABLE
IF
EXISTS single_data;
CREATE TABLE single_data ( id INT ( 3 ) PRIMARY KEY, content VARCHAR ( 20 ) );
INSERT INTO single_data
VALUES
( 1, 'a' );
EXPLAIN SELECT
*
FROM
single_data a
WHERE
id = 1;
system
system æ¯ const çä¸ç§ç¹ä¾ï¼åªæä¸è¡æ»¡è¶³æ¡ä»¶ãä¾å¦ï¼åªæä¸æ¡æ°æ®çç³»ç»è¡¨ã
eq_ref
é常åºç°å¨å¤è¡¨ç join æ¥è¯¢ï¼è¡¨ç¤ºå¯¹äºå表çæ¯ä¸ä¸ªç»æ,ï¼é½åªè½å¹é å°å表çä¸è¡ç»æãä¸è¬æ¯
å¯ä¸æ§ç´¢å¼çæ¥è¯¢ï¼UNIQUE æ PRIMARY KEYï¼ã
eq_ref æ¯é¤ const ä¹å¤æ好ç访é®ç±»åã
å å é¤ teacher 表ä¸å¤ä½çæ°æ®ï¼teacher_contact æ 3 æ¡æ°æ®ï¼teacher 表æ 3æ¡æ°æ®ã
DELETE
FROM
teacher
WHERE
tid IN ( 4, 5, 6 );
COMMIT;
-- å¤ä»½
INSERT INTO `teacher`
VALUES
( 4, 'jim', 4 );
INSERT INTO `teacher`
VALUES
( 5, 'bobo', 5 );
INSERT INTO `teacher`
VALUES
( 6, 'seven', 6 );
COMMIT;
为 teacher_contact 表ç tcidï¼ç¬¬ä¸ä¸ªå段ï¼å建主é®ç´¢å¼ã
-- ALTER TABLE teacher_contact DROP PRIMARY KEY;
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
为 teacher 表ç tcidï¼ç¬¬ä¸ä¸ªå段ï¼å建æ®éç´¢å¼ã
-- ALTER TABLE teacher DROP INDEX idx_tcid;
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
æ§è¡ä»¥ä¸ SQL è¯å¥ï¼
æ¤æ¶çæ§è¡è®¡åï¼teacher_contact è¡¨æ¯ eq_refï¼ï¼
å°ç»ï¼
以ä¸ä¸ç§ systemï¼constï¼eq_refï¼é½æ¯å¯éèä¸å¯æ±çï¼åºæ¬ä¸å¾é¾ä¼åå°è¿ä¸ªç¶æã
ref
æ¥è¯¢ç¨å°äºéå¯ä¸æ§ç´¢å¼ï¼æè å ³èæä½åªä½¿ç¨äºç´¢å¼çæå·¦åç¼ã
ä¾å¦ï¼ä½¿ç¨ tcid ä¸çæ®éç´¢å¼æ¥è¯¢ï¼
range
ç´¢å¼èå´æ«æã
å¦æ where åé¢æ¯ between and æ <æ > æ >= æ <=æ in è¿äºï¼type ç±»å就为 rangeã
ä¸èµ°ç´¢å¼ä¸å®æ¯å ¨è¡¨æ«æï¼ALLï¼ï¼æ以å å ä¸æ®éç´¢å¼ã
-- ALTER TABLE teacher DROP INDEX idx_tid;
ALTER TABLE teacher ADD INDEX idx_tid (tid);
æ§è¡èå´æ¥è¯¢ï¼å段ä¸ææ®éç´¢å¼ï¼ï¼
EXPLAIN SELECT * FROM teacher t WHERE t.tid <3;
-- æ
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;
IN æ¥è¯¢ä¹æ¯ rangeï¼å段æ主é®ç´¢å¼ï¼
index
Full Index Scanï¼æ¥è¯¢å ¨é¨ç´¢å¼ä¸çæ°æ®ï¼æ¯ä¸èµ°ç´¢å¼è¦å¿«ï¼ã
all
Full Table Scanï¼å¦æ没æç´¢å¼æè 没æç¨å°ç´¢å¼ï¼type å°±æ¯ ALLãä»£è¡¨å ¨è¡¨æ«æã
å°ç»ï¼
ä¸è¬æ¥è¯´ï¼éè¦ä¿è¯æ¥è¯¢è³å°è¾¾å° range 级å«ï¼æ好è½è¾¾å° refã
ALLï¼å ¨è¡¨æ«æï¼å indexï¼æ¥è¯¢å ¨é¨ç´¢å¼ï¼é½æ¯éè¦ä¼åçã
4.3.4 possible_keyãkey
å¯è½ç¨å°çç´¢å¼åå®é ç¨å°çç´¢å¼ãå¦ææ¯ NULL 就代表没æç¨å°ç´¢å¼ã
possible_key å¯ä»¥æä¸ä¸ªæè å¤ä¸ªï¼å¯è½ç¨å°ç´¢å¼ä¸ä»£è¡¨ä¸å®ç¨å°ç´¢å¼ã
åè¿æ¥ï¼possible_key 为空ï¼key å¯è½æå¼åï¼
表ä¸å建èåç´¢å¼ï¼
ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
æ§è¡è®¡åï¼æ¹æ select name ä¹è½ç¨å°ç´¢å¼ï¼ï¼
ç»è®ºï¼æ¯æå¯è½çï¼è¿éæ¯è¦çç´¢å¼çæ åµï¼ã
å¦æéè¿åæåç°æ²¡æç¨å°ç´¢å¼ï¼å°±è¦æ£æ¥ SQL æè å建索å¼ã
4.3.5 key_len
ç´¢å¼çé¿åº¦ï¼ä½¿ç¨çåèæ°ï¼ãè·ç´¢å¼å段çç±»åãé¿åº¦æå ³ã
表ä¸æèåç´¢å¼ï¼KEY comidx_name_phone ( name , phone )
4.3.6 rows
MySQL 认为æ«æå¤å°è¡æè½è¿å请æ±çæ°æ®ï¼æ¯ä¸ä¸ªé¢ä¼°å¼ãä¸è¬æ¥è¯´è¡æ°è¶å°è¶å¥½ã
4.3.7 filtered
è¿ä¸ªå段表示åå¨å¼æè¿åçæ°æ®å¨ server å±è¿æ»¤åï¼å©ä¸å¤å°æ»¡è¶³æ¥è¯¢çè®°å½æ°éçæ¯ä¾ï¼å®æ¯
ä¸ä¸ªç¾åæ¯ã
4.3.8 ref
使ç¨åªä¸ªåæè 常æ°åç´¢å¼ä¸èµ·ä»è¡¨ä¸çéæ°æ®ã
4.3.9 Extra
æ§è¡è®¡åç»åºçé¢å¤çä¿¡æ¯è¯´æã
using index
ç¨å°äºè¦çç´¢å¼ï¼ä¸éè¦å表ã
using where
使ç¨äº where è¿æ»¤ï¼è¡¨ç¤ºåå¨å¼æè¿åçè®°å½å¹¶ä¸æ¯ææçé½æ»¡è¶³æ¥è¯¢æ¡ä»¶ï¼éè¦å¨ server å±è¿
è¡è¿æ»¤ï¼è·æ¯å¦ä½¿ç¨ç´¢å¼æ²¡æå ³ç³»ï¼ã
using filesort
ä¸è½ä½¿ç¨ç´¢å¼æ¥æåºï¼ç¨å°äºé¢å¤çæåºï¼è·ç£çææ件没æå ³ç³»ï¼ãéè¦ä¼åãï¼å¤åç´¢å¼çå
æï¼
ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
ï¼order by id å¼èµ·ï¼
using temporary
ç¨å°äºä¸´æ¶è¡¨ãä¾å¦ï¼ä»¥ä¸ä¸æ¯å ¨é¨çæ åµï¼ï¼
1ãdistinct éç´¢å¼å
2ãgroup by éç´¢å¼å
3ãä½¿ç¨ join çæ¶åï¼group ä»»æå
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by
t.tid;
éè¦ä¼åï¼ä¾å¦å建å¤åç´¢å¼ã
æ»ç»ä¸ä¸ï¼
模æä¼åå¨æ§è¡ SQL æ¥è¯¢è¯å¥çè¿ç¨ï¼æ¥ç¥é MySQL æ¯æä¹å¤çä¸æ¡ SQL è¯å¥çãéè¿è¿ç§æ¹å¼
æ们å¯ä»¥åæè¯å¥æè 表çæ§è½ç¶é¢ã
åæåºé®é¢ä¹åï¼å°±æ¯å¯¹ SQL è¯å¥çå ·ä½ä¼åã
4.4 SQL ä¸ç´¢å¼ä¼å
SQL è¯å¥çä¼åçç®æ ï¼å¤§é¨åæ¶åé½æ¯ç¨å°ç´¢å¼ã
å¨mysql ç´¢å¼åçå使ç¨éé¢ä¹è®²å°äºç´¢å¼å建çååï¼åä»ä¹æ åµä¼ç¨å°ç´¢å¼ï¼ä»ä¹æ åµä¸ä¼ç¨å°ç´¢å¼ã
äºãåå¨å¼æ
5.1 åå¨å¼æçéæ©
为ä¸åçä¸å¡è¡¨éæ©ä¸åçåå¨å¼æï¼ä¾å¦ï¼æ¥è¯¢æå ¥æä½å¤çä¸å¡è¡¨ï¼ç¨ MyISAMã临æ¶æ°æ®ç¨
Memoryã常è§ç并å大æ´æ°å¤çè¡¨ç¨ InnoDBãã
5.2 å段å®ä¹
ååï¼ä½¿ç¨å¯ä»¥æ£ç¡®åå¨æ°æ®çæå°æ°æ®ç±»åã
为æ¯ä¸åéæ©åéçå段类åã
5.2.1 æ´æ°ç±»å
TINYINT 1 个åè
SMALLINT 2 个åè
MEDIUMINT 3 个åè
INT, INTEGER 4 个åè
BIGINT 8 个åè
INT æ 8 ç§ç±»åï¼ä¸åçç±»åçæ大åå¨èå´æ¯ä¸ä¸æ ·çã
æ§å«ï¼ç¨ TINYINTï¼å 为 ENUM ä¹æ¯æ´æ°åå¨ã
5.2.2 å符类å
åé¿æ åµä¸ï¼varchar æ´èç空é´ï¼ä½æ¯å¯¹äº varchar å段ï¼éè¦ä¸ä¸ªåèæ¥è®°å½é¿åº¦ã
åºå®é¿åº¦çç¨ charï¼ä¸è¦ç¨ varcharã
5.2.3 ä¸è¦ç¨å¤é®ã触åå¨ãè§å¾
éä½äºå¯è¯»æ§ï¼
å½±åæ°æ®åºæ§è½ï¼åºè¯¥ææ计ç®çäºæ 交ç»ç¨åºï¼æ°æ®åºä¸å¿ååå¨ï¼
æ°æ®çå®æ´æ§åºè¯¥å¨ç¨åºä¸æ£æ¥ã
5.2.4 大æ件åå¨
ä¸è¦ç¨æ°æ®åºåå¨å¾çï¼æ¯å¦ base64 ç¼ç ï¼æè 大æ件ï¼
ææ件æ¾å¨ NAS ä¸ï¼æ°æ®åºåªéè¦åå¨ URIï¼ç¸å¯¹è·¯å¾ï¼ï¼å¨åºç¨ä¸é ç½® NAS æå¡å¨å°åã
5.2.5 表æåæå段åä½
å°ä¸å¸¸ç¨çå段æååºå»ï¼é¿å åæ°è¿å¤åæ°æ®éè¿å¤§ã
æ¯å¦å¨ä¸å¡ç³»ç»ä¸ï¼è¦è®°å½æææ¥æ¶ååéçæ¶æ¯ï¼è¿ä¸ªæ¶æ¯æ¯ XML æ ¼å¼çï¼ç¨blob æè text å
å¨ï¼ç¨æ¥è¿½è¸ªåå¤æéå¤ï¼å¯ä»¥å»ºç«ä¸å¼ 表ä¸é¨ç¨æ¥åå¨æ¥æã
å æ»ç»ï¼ä¼åä½ç³»
é¤äºå¯¹äºä»£ç ãSQL è¯å¥ã表å®ä¹ãæ¶æãé ç½®ä¼åä¹å¤ï¼ä¸å¡å±é¢çä¼åä¹ä¸è½å¿½è§ã举两个ä¾
åï¼
1ï¼å¨æä¸å¹´çååä¸ï¼ä¸ºä»ä¹ä¼åä¸ä¸ªå å¼å°ä½é¢å®åä½é¢æå¥éçæ´»å¨ï¼ä¾å¦å 300 é 50ï¼
å 为使ç¨ä½é¢æè ä½é¢å®ä»æ¬¾æ¯è®°å½æ¬å°æè å é¨æ°æ®åºï¼è使ç¨é¶è¡å¡ä»æ¬¾ï¼éè¦è°ç¨æ¥å£ï¼æ
ä½å é¨æ°æ®åºè¯å®æ´å¿«ã
2ï¼å¨å»å¹´çååä¸ï¼ä¸ºä»ä¹å¨åæ¨ç¦æ¢æ¥è¯¢ä»å¤©ä¹å¤çè´¦åï¼
è¿æ¯ä¸ç§é级æªæ½ï¼ç¨æ¥ä¿è¯å½åææ ¸å¿çä¸å¡ã
3ï¼æè¿å å¹´çååä¸ï¼ä¸ºä»ä¹æå个æææ就已ç»æååä¸å½å¤©çä»·æ ¼äºï¼
é¢å®åæµã
4ï¼å ¬å®å±çååæ¥è¯¢ï¼ä¸æ¯å®æ¶è¿åç»æï¼ä¸æ¯å®æ¶æ¥è¯¢æ°æ®åºï¼ï¼èæ¯éè¿å ¬ä¼å·æ¨éã
å¨åºç¨å±é¢åæ ·æå¾å¤å ¶ä»çæ¹æ¡æ¥ä¼åï¼è¾¾å°å°½éåè½»æ°æ®åºçååçç®çï¼æ¯å¦éæµï¼æè å¼
å ¥ MQ åå³°ï¼ççççã
为ä»ä¹åæ ·ç¨ MySQLï¼æçå ¬å¸å¯ä»¥æä½ç¾ä¸åä¸çº§å«ç并åï¼èæçå ¬å¸å ç¾ä¸ªå¹¶åé½æä¸ä½ï¼
å ³é®å¨äºæä¹ç¨ãæ以ï¼ç¨æ°æ®åºæ ¢ï¼ä¸ä»£è¡¨æ°æ®åºæ¬èº«æ ¢ï¼æçæ¶åè¿è¦å¾ä¸å±å»ä¼åã
å½ç¶ï¼å¦æå ³ç³»åæ°æ®åºè§£å³ä¸äºçé®é¢ï¼æ们å¯è½éè¦ç¨å°æç´¢å¼ææè 大æ°æ®çæ¹æ¡äºï¼å¹¶ä¸
æ¯ææçæ°æ®é½è¦æ¾å°å ³ç³»åæ°æ®åºåå¨ã