æ¬æå°éè¿å®é æ¡ä¾ï¼æ¢è®¨MySQLè°ä¼çå ³é®æ¥éª¤åæå·§ï¼å¸®å©å¤§å®¶äºè§£å¦ä½è¯å«å解å³å¸¸è§çæ§è½é®é¢ï¼ä»èä¼åæ°æ®åºç³»ç»çæ§è½è¡¨ç°ã
å¨æ¥ä¸æ¥çå 容ä¸ï¼æ们éè¿ç¸å ³æ¡ä¾æ¥æ·±å ¥æ¢è®¨çæµåè¯æãæ¥è¯¢ä¼åãæ°æ®åºé ç½®è°ä¼ã硬件åæä½ç³»ç»ä¼åçæ¹é¢çå ·ä½ææ¯åå®è·µï¼ä»¥å¸®å©è¯»è å ¨é¢ç解MySQLè°ä¼çå®æè¿ç¨ï¼å¹¶æç»æåå ¶åºç¨ç¨åºçæ§è½å稳å®æ§ã
让âæ ¢SQL"æé100åçç»å½10æ
MySQLæ¥è¯¢ä¼åãè°ä¼æ¯ç产ä¸å¸¸è§é¾é¢ï¼ä¹æ¯å¸¸è§é¢è¯é¢ï¼æ»ç»äºå¤§éçç产æ¡ä¾åå®è·µç»éªï¼ä¸ºå¤§å®¶æç¼äºè®©âæ ¢SQL"æé100åçç»å½ä¸å¤§æ ï¼
第1æï¼ç´¢å¼ä¼åï¼
- åç设计索å¼ï¼æ ¹æ®æ¥è¯¢çæ¡ä»¶å访é®æ¨¡å¼ï¼è®¾è®¡éå½çç´¢å¼ï¼å æ¬ååç´¢å¼ãç»åç´¢å¼ãå¯ä¸ç´¢å¼çã
- é¿å è¿å¤ç´¢å¼ï¼è¿å¤çç´¢å¼ä¼å¢å æ°æ®ç»´æ¤çå¼éï¼åæ¶ä¹ä¼éä½æ´æ°æä½çæ§è½ã
- å®æç»´æ¤ç´¢å¼ï¼å é¤ä¸å使ç¨çç´¢å¼ï¼éæ°æ建æéç»ç´¢å¼ï¼ä»¥æé«ç´¢å¼çæçåæ§è½ã
第2æï¼è¦çç´¢å¼ï¼
- å©ç¨è¦çç´¢å¼é¿å å表æä½ï¼å¦ææ¥è¯¢çå段é½å å«å¨ç´¢å¼ä¸ï¼å¯ä»¥é¿å 访é®ä¸»è¡¨çæ°æ®è¡ï¼ä»èæé«æ¥è¯¢æ§è½ã
第3æï¼ç´¢å¼ä¸æ¨ï¼
- å©ç¨ç´¢å¼ä¸æ¨ï¼Index Condition Pushdownï¼ä¼åæ¥è¯¢ï¼MySQL 5.6+çæ¬æ¯æç´¢å¼ä¸æ¨ï¼å¯ä»¥å¨ç´¢å¼å±é¢è¿è¡é¨åæ¡ä»¶çè¿æ»¤ï¼åå°å表æä½ï¼æé«æ¥è¯¢æçã
第4æï¼åæ¥è¯¢ä¼åï¼
- å°½éé¿å 使ç¨å¤§éçåæ¥è¯¢ï¼è¿å¤çåæ¥è¯¢ä¼å¢å æ¥è¯¢çå¤æ度åå¼éãå¯ä»¥éè¿ä¼åæ¥è¯¢è¯å¥ï¼å°åæ¥è¯¢è½¬å为è¿æ¥æ¥è¯¢ã临æ¶è¡¨çæ¹å¼æ¥æé«æ§è½ã
- 使ç¨åéçåæ¥è¯¢ç±»åï¼æ ¹æ®å ·ä½åºæ¯éæ©éåçåæ¥è¯¢ç±»åï¼å¦æ éåæ¥è¯¢ãå ³èåæ¥è¯¢ãåå¨åæ¥è¯¢çã
第5æï¼æåºä¼åï¼
- é¿å å¨æ¥è¯¢ä¸è¿è¡ä¸å¿ è¦çæåºï¼å¦ææ¥è¯¢ä¸éè¦æåºç»æï¼å¯ä»¥éè¿è°æ´æ¥è¯¢æ¡ä»¶æç´¢å¼è®¾è®¡æ¥é¿å æåºæä½ï¼æé«æ¥è¯¢æ§è½ã
- ä¼åæåºæä½ï¼å¯¹äºéè¦æåºçæ¥è¯¢ï¼å¯ä»¥éè¿åççç´¢å¼è®¾è®¡ãå¢å æåºç¼å²åºç大å°ãè°æ´æåºç®æ³çæ¹å¼æ¥ä¼åæåºæä½ã
第6æï¼æ¥è¯¢ç¼åç使ç¨ï¼
- åç使ç¨æ¥è¯¢ç¼åï¼æ¥è¯¢ç¼åå¯ä»¥ç¼åæ¥è¯¢ç»æï¼æé«æ¥è¯¢æ§è½ãä½æ¯å¨é«å¹¶åç¯å¢ä¸ï¼æ¥è¯¢ç¼åçææå¯è½ä¸çæ³ï¼å æ¤éè¦æ ¹æ®å ·ä½æ åµè¿è¡é ç½®å使ç¨ã
第7æï¼SQLè¯å¥ä¼åï¼
- ä¼åæ¥è¯¢è¯å¥çåæ³ï¼åçéæ©æ¥è¯¢æ¹å¼ã使ç¨æ£ç¡®çå ³é®ååå½æ°ï¼åå°ä¸å¿ è¦ç计ç®åæ°æ®æä½ã
- é¿å å ¨è¡¨æ«æï¼å°½é使ç¨ç´¢å¼è¦çæ¥è¯¢ï¼é¿å å ¨è¡¨æ«ææä½ï¼åå°IOå¼éåæ¥è¯¢æ¶é´ã
第8æï¼è°æ´æ°æ®åºåæ°ï¼æ ¹æ®å ·ä½çæ°æ®åºç³»ç»ï¼è°æ´ç¸å ³çåæ°è®¾ç½®ï¼ä»¥æ大ç¨åº¦å°æé«æ§è½ãè¿äºåæ°å¯è½å æ¬ç¼å大å°ã并åè¿æ¥æ°ãæ¥è¯¢è¶ æ¶æ¶é´çã
第9æï¼åæ¹å¤çï¼å¦ææ¥è¯¢ç»æéé常大ï¼å¯ä»¥èèå°æ¥è¯¢æåæå¤ä¸ªè¾å°çæ¹æ¬¡è¿è¡å¤çï¼èä¸æ¯ä¸æ¬¡æ§æ£ç´¢æææ°æ®ãè¿å¯ä»¥åå°å ååèµæºç使ç¨ï¼æé«æ§è½ã
第10æï¼ä¼åæ°æ®åºè®¾è®¡ï¼åççæ°æ®åºè®¾è®¡å¯ä»¥æé«æ¥è¯¢æ§è½ãç¡®ä¿è¡¨çç»æåå ³ç³»åçï¼é¿å åä½åä¸å¿ è¦çå段ãæ£ç¡®ä½¿ç¨ä¸»é®ãå¤é®åç´¢å¼ã
ä¸é¢å°±çä¸ä¸ªå ¨é¢çè°ä¼æ¡ä¾ã
æ¡ä¾èæ¯ï¼
å¨æ¬æä¸ï¼æ们å°ä»¥ä¸ä¸ªå¨çº¿çµåç½ç«ä¸ºä¾ï¼æ¢è®¨MySQLè°ä¼çå®ææ¡ä¾ã该çµåç½ç«ä½ä¸ºä¸ä¸ªçé¨çå¨çº¿è´ç©å¹³å°ï¼æ¯å¤©é½æ大éç¨æ·è¿è¡ååæµè§ãä¸åçæä½ã
ç¶èï¼éçç¨æ·æ°éçå¢å å交æéçå¢é¿ï¼ä»ä»¬çæ°æ®åºæ§è½éå°äºä¸äºææã
该çµåç½ç«çæ°æ®åºä¸»è¦åå¨äºäº§åä¿¡æ¯ãç¨æ·ä¿¡æ¯ã订åçéè¦æ°æ®ãéçä¸å¡çåå±ï¼æ°æ®åºè§æ¨¡éæ¸å¢å¤§ï¼è¡¨ä¸çè®°å½æ°éåç´¢å¼æ°éä¹æ¥å§å¢å ãéä¹èæ¥çæ¯ä¸ç³»åæ§è½é®é¢çåºç°ã
é¦å ï¼ç¨æ·å¨è¿è¡ååæç´¢ææµè§æ¶ï¼ä¸äºæ¥è¯¢è¯å¥çæ§è¡é度åæ ¢ï¼å¯¼è´ç½ç«ååºæ¶é´å»¶é¿ãç¨æ·ä½éªä¸éï¼çè³å¯è½å¯¼è´ç¨æ·æµå¤±ã
å ¶æ¬¡ï¼ç½ç«å¨ç¹å®æ¶é´æ®µï¼å¦ä¿éæ´»å¨æé´ï¼ä¼åºç°é«å¹¶åçæ åµã大éç¨æ·åæ¶è®¿é®æ°æ®åºï¼å¯è½å¯¼è´éç«äºãæ»éç并å访é®é®é¢ï¼è¿èå½±åç³»ç»çæ§è½åå¯ç¨æ§ã
æ¤å¤ï¼æ°æ®åºæå¡å¨ç硬件èµæºåæä½ç³»ç»åæ°é ç½®ä¹å¯è½æ为æ§è½ç¶é¢ãåæç硬件åæä½ç³»ç»è®¾ç½®æ æ³æ»¡è¶³å½åæ°æ®åºè´è½½çéæ±ï¼å¯¼è´æ°æ®åºæ§è½æ æ³å ååæ¥ã
å¨é¢å¯¹è¿äºæææ¶ï¼å¿ é¡»è¿è¡MySQLè°ä¼ï¼ä»¥æåæ°æ®åºæ§è½åå¯æ©å±æ§ãæ们å¯ä»¥éè¿ä¼åæ¥è¯¢è¯å¥ãè°æ´æ°æ®åºé ç½®åæ°ãä¼ååå¨å¼æå硬件çæªæ½ï¼è§£å³æ§è½ç¶é¢å并å访é®é®é¢ï¼å¹¶ç¡®ä¿æ°æ®åºè½å¤ç¨³å®é«æå°æ¯ææ¥çå¢é¿çä¸å¡éæ±ã
å¨æ¥ä¸æ¥çå 容ä¸ï¼æ们å°è¯¦ç»ä»ç»è¯¥çµåç½ç«æéåçè°ä¼æ¥éª¤åå®è·µç»éªï¼å±ç¤ºå¦ä½éè¿ç³»ç»çMySQLè°ä¼æ¹æ³ï¼æåæ°æ®åºæ§è½ãä¼åæ¥è¯¢æçï¼å¹¶å®ç°å¿«éãå¯é çå¨çº¿è´ç©ä½éªã
MySQLè°ä¼ç¯è主è¦å æ¬ï¼
- æ°æ®åºé ç½®è°ä¼
- 硬件åæä½ç³»ç»è°ä¼
- SQLè¯å¥æ¥è¯¢ä¼å
ä¸é¢æ们é¦å äºè§£ä¸æ°æ®åºé ç½®è°ä¼ï¼
æ°æ®åºé ç½®è°ä¼
æ°æ®åºé ç½®æ¯MySQLè°ä¼çéè¦ä¸ç¯ï¼éè¿åçå°è°æ´æ°æ®åºçåæ°è®¾ç½®ï¼å¯ä»¥æ¾èæ¹åæ§è½åèµæºå©ç¨çã
å¨æ¬ç« ä¸ï¼æ们å°æ¢è®¨ä¸äºå ³é®çæ°æ®åºé ç½®è°ä¼çç¥ï¼å¹¶æä¾ç¸åºçå®é èæ¬æå½ä»¤ç¤ºä¾ã
è°æ´æ°æ®åºç¼å²åºå¤§å°å线ç¨æ± 设置
1.InnoDBç¼å²æ± 大å°ï¼innodb_buffer_pool_sizeï¼ï¼
对äºçµåç½ç«å¹³å°ï¼æ们建议å°è¯¥åæ°è®¾ç½®ä¸ºæ´ä¸ªæå¡å¨å åç70%-80%ã
ä¾å¦ï¼å¦ææå¡å¨å ·æ16GBçå åï¼åå¯ä»¥å°ç¼å²æ± 大å°è®¾ç½®ä¸º12GB-14GBï¼ä»¥ç¡®ä¿å¤§é¨åæ°æ®åç´¢å¼å¯ä»¥ç¼åå¨å åä¸ï¼æé«è¯»åæ§è½ã
示ä¾ï¼
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_buffer_pool_size = 12*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)
2.æ¥è¯¢ç¼å设置ï¼query_cache_typeãquery_cache_sizeï¼ï¼
å¨çµåç½ç«å¹³å°ä¸ï¼ç±äºæ´æ°é¢çè¾é«ï¼æ们建议ç¦ç¨æ¥è¯¢ç¼åã
æ¥è¯¢ç¼åå¨é«å¹¶ååé¢ç¹æ´æ°çç¯å¢ä¸å¯è½å¸¦æ¥æ´å¤çæ§è½è´æ ã
示ä¾ï¼
mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)
å¨MySQLé ç½®æ件ä¸è¿è¡æ¥è¯¢ç¼å设置ï¼
[mysqld]
query_cache_type = 0
query_cache_size = 0
3.线ç¨æ± 设置ï¼thread_pool_sizeãthread_pool_max_threadsï¼ï¼
é对çµåç½ç«å¹³å°çé«å¹¶å访é®ï¼æä»¬å»ºè®®æ ¹æ®é¢ä¼°ç并åè¿æ¥æ°åç³»ç»è´è½½æ åµï¼éå½è°æ´çº¿ç¨æ± ç大å°åæ大线ç¨æ°ã
å¯ä»¥å æ ¹æ®å®é æ åµè®¾ç½®è¾å°çå¼ï¼ç¶åéè¿çæ§ç³»ç»è´è½½åè¿æ¥æ± ç¶ææ¥è¿è¡å¨æè°æ´ã
示ä¾ï¼
SHOW VARIABLES LIKE 'thread_pool%';
å¨MySQLé ç½®æ件ä¸è¿è¡çº¿ç¨æ± 设置ï¼
[mysqld]
thread_pool_size = 100
thread_pool_max_threads = 200
ä¼åInnoDBåå¨å¼æçåæ°é ç½®
1.æ¥å¿é ç½®ï¼innodb_log_file_sizeãinnodb_log_buffer_sizeï¼ï¼
对äºçµåç½ç«å¹³å°ï¼æä»¬å»ºè®®æ ¹æ®æ°æ®åºçæ¥å¿åå ¥é度åäºå¡å¤çéæ¥è°æ´æ¥å¿æ件大å°åç¼å²åºå¤§å°ã
ä¸è¬èè¨ï¼æ¥å¿æ件大å°åºè¯¥è¶³å¤å¤§ï¼ä»¥åå°æ¥å¿åæ¢çé¢çï¼åæ¶ç¼å²åºå¤§å°åºè¯¥è½å®¹çº³ä¸å®æ°éçæ¥å¿åå ¥ã
示ä¾ï¼
mysql> SHOW VARIABLES LIKE 'innodb_log_file%';
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
+---------------------------+----------+
2 rows in set (0.00 sec)
è°æ´æ¥å¿æ件大å°åç¼å²åºå¤§å°ï¼
SET GLOBAL innodb_log_file_size = 1G;
SET GLOBAL innodb_log_buffer_size = 32M;
å¨MySQLé ç½®æ件ä¸è¿è¡æ°¸ä¹ 设置ï¼
[mysqld]
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M
2.éå®é ç½®ï¼innodb_lock_wait_timeoutï¼ï¼
对äºçµåç½ç«å¹³å°ç并å访é®ï¼æ们建议å°éå®çå¾ è¶ æ¶æ¶é´è®¾ç½®ä¸ºè¾ççå¼ï¼ä»¥åå°é¿æ¶é´çéå®çå¾ ã
ä¸è¬èè¨ï¼å¯ä»¥å°éå®çå¾ è¶ æ¶æ¶é´è®¾ç½®ä¸ºå ç§éã
示ä¾ï¼
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)
è°æ´éå®çå¾ è¶ æ¶æ¶é´ï¼
SET GLOBAL innodb_lock_wait_timeout = 5;
å¨MySQLé ç½®æ件ä¸è¿è¡æ°¸ä¹ 设置ï¼
[mysqld]
innodb_lock_wait_timeout = 5
3.èªå¨å¢é¿åæ°ï¼innodb_autoinc_lock_modeï¼ï¼
对äºçµåç½ç«å¹³å°çé«å¹¶åç¯å¢ï¼å»ºè®®å°èªå¨å¢é¿éå®æ¨¡å¼è®¾ç½®ä¸º"2"ï¼å³ä½¿ç¨è¿ç»çæ¹éæå ¥æ¨¡å¼ãè¿æ ·å¯ä»¥åå°èªå¨å¢é¿å段çéå®å²çªï¼æé«å¹¶åæ§è½ã
示ä¾ï¼
mysql> SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+
1 row in set (0.00 sec)
è°æ´èªå¨å¢é¿éå®æ¨¡å¼ï¼
SET GLOBAL innodb_autoinc_lock_mode = 2;
å¨MySQLé ç½®æ件ä¸è¿è¡æ°¸ä¹ 设置ï¼
[mysqld]
innodb_autoinc_lock_mode = 2
è°æ´æ¥å¿åéå®çç¥ä»¥æé«å¹¶åæ§è½
1.äºå¡é离级å«ï¼transaction-isolationï¼ï¼
对äºçµåç½ç«å¹³å°ï¼å»ºè®®å°äºå¡é离级å«è®¾ç½®ä¸º"READ-COMMITTED"ï¼ä»¥å¹³è¡¡å¹¶åæ§è½åæ°æ®ä¸è´æ§çéæ±ã
示ä¾ï¼
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
设置äºå¡é离级å«ï¼
mysql> SET GLOBAL tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)
å¨MySQLé ç½®æ件ä¸è¿è¡æ°¸ä¹ 设置ï¼
[mysqld]
transaction-isolation = READ-COMMITTED
2.并åæ§å¶çç¥ï¼innodb_thread_concurrencyï¼ï¼
æ ¹æ®çµåç½ç«å¹³å°ç并åè¿æ¥æ°åç³»ç»è´è½½æ åµï¼éå½è°æ´å¹¶åæ§å¶çç¥ã
å¯ä»¥å å°innodb_thread_concurrency设置为0ï¼è®©MySQLæ ¹æ®ç³»ç»è´è½½å¨æè°æ´çº¿ç¨å¹¶åæ°ã
示ä¾ï¼
mysql> SHOW VARIABLES LIKE 'innodb_thread_concurrency';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)
å¨MySQLé ç½®æ件ä¸è¿è¡çº¿ç¨å¹¶åæ§å¶è®¾ç½®ï¼
[mysqld]
innodb_thread_concurrency = 0
3.éå®ä¼åï¼
对äºçµåç½ç«å¹³å°ä¸é¢ç¹æ´æ°ç表ï¼åºå°½é使ç¨è¡çº§éå®ä»£æ¿è¡¨çº§éå®ï¼ä»¥åå°éå²çªåæé«å¹¶åæ§è½ãå¨éè¦ä½¿ç¨è¡çº§éå®ç表ä¸å ä¸éå½çç´¢å¼ï¼å¯ä»¥è¿ä¸æ¥æé«å¹¶åæ§è½ã
éè¿ç»è´çæ°æ®åºé ç½®è°ä¼ï¼æ们å¯ä»¥æ大ç¨åº¦å°æåMySQLæ°æ®åºçæ§è½åå¯é æ§ï¼ä»èç¡®ä¿çµåç½ç«å¹³å°çé«å¹¶å访é®åäºå¡å¤çè½åã
å¨å®é åºç¨ä¸ï¼å»ºè®®æ ¹æ®å ·ä½ä¸å¡éæ±åç³»ç»ç¶åµï¼è¿è¡æ§è½æµè¯åè¯ä¼°ï¼å¹¶å®æè¿è¡è°ä¼åä¼åçæ£æ¥ã
å¨æ¥ä¸æ¥çç« èä¸ï¼æ们å°æ·±å ¥æ¢è®¨ç¡¬ä»¶åæä½ç³»ç»ä¼åçå ³é®æ¥éª¤ï¼ä»¥å ¨é¢æåMySQLæ°æ®åºçæ§è½åå¯é æ§ã
硬件åæä½ç³»ç»ä¼å
硬件åæä½ç³»ç»çä¼å对äºMySQLæ°æ®åºçæ§è½å稳å®æ§åæ ·è³å ³éè¦ã
å¨çµåç½ç«å¹³å°ä¸ï¼é对é«å¹¶å访é®å大éæ°æ®å¤ççéæ±ï¼æ们å¯ä»¥éè¿ä»¥ä¸é ç½®åä¼åçç¥æ¥æåç³»ç»æ§è½ã
硬件é ç½®
硬件é ç½®æ¯æ ¹æ®å ·ä½çéæ±åé¢ç®æ¥ç¡®å®çï¼å æ¤å¯è½å æ åµèå¼ã
ç¶èï¼å¯ä»¥æ ¹æ®ä¸è¿°çµåç½ç«å¹³å°çç¹ç¹ï¼æä¾ä¸ä¸ªåçç硬件é ç½®åèï¼
1.å åï¼RAMï¼ï¼
åé 足å¤çå å以容纳æ°æ®åºçç¼ååç´¢å¼æ°æ®ã
对äºä¸åå°å¤§åççµåç½ç«å¹³å°ï¼å»ºè®®éæ©è³å°64GBå°128GBçå åã
对äºæ´å¤§è§æ¨¡çå¹³å°ï¼å¯è½éè¦æ´é«å®¹éçå åã
2.åå¨è®¾å¤ï¼
éæ©é«æ§è½çåå¨è®¾å¤ä»¥æä¾å¿«éçæ°æ®è¯»åè½åã
对äºæ°æ®æ件åæ¥å¿æ件ï¼å»ºè®®ä½¿ç¨åºæ硬çï¼SSDï¼æè NVMe SSDï¼ä»¥æé«æ°æ®è®¿é®é度ã
对äºè¾å¤§çæ°æ®éï¼å¯ä»¥èè使ç¨RAIDéµå以æä¾æ´å¥½çæ§è½ååä½ã
3.CPUï¼
éæ©å¤æ ¸å¿çCPU以æé«å¹¶åå¤çè½åã
对äºå¤§åçµåç½ç«å¹³å°ï¼å»ºè®®éæ©é«æ§è½çæå¡å¨çº§CPUï¼ä¾å¦Intel Xeonç³»åæAMD EPYCç³»åï¼èèé ç½®4æ ¸ææ´å¤æ ¸å¿çCPUåé«é¢ççå¤çå¨ã
4.ç½ç»å¸¦å®½ï¼
ç¡®ä¿æ足å¤çç½ç»å¸¦å®½æ¥å¤ç大éç并å请æ±åæ°æ®ä¼ è¾ã
对äºçµåç½ç«å¹³å°ï¼å»ºè®®éæ©é«éç½ç»è¿æ¥ï¼ä¾å¦åå 以太ç½ææ´é«éççç½ç»ã
请注æï¼è¿ä» æ¯ä¸ä¸ªå¤§è´ç硬件é ç½®åèï¼å ·ä½ç硬件éæ±è¿åºæ ¹æ®å®é æ åµãé¢ç®åæ§è½è¦æ±è¿è¡ç»¼åèèã
å½ç¶ï¼ä¸è¬ä¸å¤§åççµåå¹³å°åºæ¬é½ä¼éç¨mysqlé群é¨ç½²ï¼é群é¨ç½²è¿éè¦èè以ä¸æ¹é¢ï¼
- æ°æ®åçï¼å°æ°æ®åºçæ°æ®åæ£å°å¤ä¸ªèç¹ä¸ãæ¯ä¸ªèç¹è´è´£ç®¡çä¸é¨åæ°æ®ãå¨é ç½®åå¸å¼MySQLæ¶ï¼éè¦ç¡®å®æ°æ®åçççç¥åè§åï¼ä¾å¦æç §ç¨æ·IDãå°çä½ç½®æå ¶ä»ä¸å¡ç¸å ³çæ¹å¼è¿è¡åçã
- 主ä»å¤å¶ï¼å¨æ¯ä¸ªèç¹ä¸é 置主ä»å¤å¶ï¼ä»¥å®ç°æ°æ®çå¤å¶ååæ¥ã主èç¹è´è´£å¤çåæä½ï¼ä»èç¹è´è´£å¤ç读æä½ãéè¿ä¸»ä»å¤å¶å¯ä»¥æé«ç³»ç»çå¯ç¨æ§åæ©å±æ§ã
- è´è½½åè¡¡ï¼ä½¿ç¨è´è½½åè¡¡å¨å°è¯·æ±ååå°ä¸åçæ°æ®åºèç¹ä¸ãè´è½½åè¡¡å¨å¯ä»¥æ ¹æ®è´è½½æ åµãèç¹ç¶æåå ¶ä»çç¥æ¥å³å®å°è¯·æ±åéå°åªä¸ªèç¹ã常è§çè´è½½åè¡¡å¨å æ¬NginxãHAProxyçã
- é«å¯ç¨æ§ï¼é ç½®æ éåæ¢åèªå¨æ éæ¢å¤æºå¶ï¼ä»¥ç¡®ä¿ç³»ç»å¨èç¹æ éæ¶ä»ç¶å¯ç¨ãå¯ä»¥ä½¿ç¨ä¸»ä»å¤å¶åå¿è·³çæµæ¥å®ç°é«å¯ç¨æ§ã
- å®å ¨æ§ï¼ä¸ºåå¸å¼MySQLé ç½®éå½çå®å ¨æªæ½ï¼å æ¬è®¿é®æ§å¶ãå å¯ä¼ è¾ãé²ç«å¢è®¾ç½®çï¼ä»¥ä¿æ¤æ°æ®çå®å ¨æ§åå®æ´æ§ã
- ç½ç»éä¿¡ï¼ç¡®ä¿èç¹ä¹é´çç½ç»éä¿¡ç éï¼å»¶è¿è¾ä½ï¼å¯ä»¥èè使ç¨é«éç½ç»è¿æ¥åä¼åç½ç»è®¾ç½®ã
- çæ§åæ§è½ä¼åï¼é ç½®çæ§å·¥å ·åæ§è½ä¼åå·¥å ·ï¼å¯¹åå¸å¼MySQLè¿è¡å®æ¶çæ§åæ§è½è°ä¼ãå¯ä»¥ä½¿ç¨å·¥å ·å¦PrometheusãGrafanaçè¿è¡çæ§åæ°æ®åæã
- æ©å±æ§ï¼èèæªæ¥çæ©å±éæ±ï¼è®¾è®¡å¯æ©å±çæ¶æåé ç½®ãä¾å¦ï¼æ·»å æ´å¤çèç¹ãè°æ´åççç¥çã
以ä¸æ¯åå¸å¼MySQLæå¡å¨çä¸è¬é ç½®ååãå ·ä½çé ç½®åå³äºåºç¨éæ±ãæ°æ®éãé¢ç®åæ§è½è¦æ±ã
æä½ç³»ç»é ç½®
å¨æä½ç³»ç»å±é¢é 置以满足MySQLä¼åï¼ä»¥ä¸æ¯ä¸äºå¸¸è§çé 置项åå ¶è¯¦ç»æè¿°ãä½ç¨ãé ç½®æ¹å¼ãèæ¬åå½ä»¤ï¼
1.æ件æ述符éå¶ï¼
æ件æ述符æ¯æä½ç³»ç»ç¨äºè¿½è¸ªæå¼çæ件çæ è¯ç¬¦ã
è°æ´æ件æ述符éå¶å¯ä»¥æé«MySQLå¤ç并åè¿æ¥åæ件æä½çè½åã
æ件é ç½®æ¹å¼ï¼
-
ç¼è¾ /etc/security/limits.conf æ件ï¼å¢å 以ä¸é ç½®ï¼* soft nofile 65536
* hard nofile 65536
å½ä»¤é ç½®æ¹å¼ï¼
- 设置软éå¶ï¼ulimit -n 65536
- 设置硬éå¶ï¼ulimit -Hn 65536
说æï¼
MySQL使ç¨æ件æ述符æ¥ç®¡çæ°æ®åºæ件ãæ¥å¿æ件çã
éè¿å¢å æ件æ述符éå¶ï¼å¯ä»¥è®©MySQLåæ¶æå¼æ´å¤çæ件ï¼æé«å¹¶åè¿æ¥åæ件æä½çè½åã
2.ç½ç»åæ°è°ä¼ï¼
è°æ´ç½ç»åæ°å¯ä»¥æ¹åæ°æ®åºçç½ç»éä¿¡æ§è½ã
é ç½®æ¹å¼ï¼
-
ç¼è¾ /etc/sysctl.conf æ件ï¼å¢å 以ä¸é ç½®ï¼net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_tw_reuse = 1
- éæ°å è½½é ç½®æ件ï¼sysctl -p
说æï¼
- net.core.somaxconnï¼æå®ç³»ç»å¨å¤äºéåç¶æä¸çæ大è¿æ¥æ°ï¼å¢å 该å¼å¯ä»¥å¢å åæ¶è¿æ¥å°MySQLæå¡å¨ç客æ·ç«¯æ°éã
- net.ipv4.tcp_max_syn_backlogï¼æå®æä½ç³»ç»å¤çæªå®æä¸æ¬¡æ¡æçè¿æ¥è¯·æ±çæ大æ°éï¼å¢å 该å¼å¯ä»¥å®¹çº³æ´å¤çè¿æ¥è¯·æ±ã
- net.ipv4.tcp_tw_reuseï¼å¯ç¨TCPè¿æ¥çå°åéç¨åè½ï¼å¯ä»¥åå°TIME-WAITç¶æçè¿æ¥æ°éï¼éæ¾ç³»ç»èµæºã
3.æ¶é´åæ¥ï¼
æ¶é´åæ¥ç¡®ä¿æ°æ®åºæå¡å¨çæ¶é´åç¡®æ§ï¼é²æ¢å æ¶é´ä¸åæ¥å¯¼è´çæ°æ®é®é¢ã
é ç½®æ¹å¼ï¼
- å®è£ 并é ç½® NTPï¼Network Time Protocolï¼æå¡ï¼ä½¿æå¡å¨æ¶é´ä¸æ åæ¶é´åæ¥ã
å®è£ å½ä»¤ï¼
- å®è£ NTP æå¡ï¼ä»¥Ubuntu为ä¾ï¼ï¼apt-get install ntp
- å¯å¨ NTP æå¡ï¼service ntp start
说æï¼
æ¶é´åæ¥æ¯ä¸ºäºç¡®ä¿æ°æ®åºæå¡å¨ä¸å ¶ä»æå¡å¨å客æ·ç«¯ä¹é´çæ¶é´ä¸è´æ§ã
MySQLç许å¤åè½åæ¥å¿è®°å½é½ä¾èµäºåç¡®çæ¶é´ãéè¿å®è£ åé ç½®NTPæå¡ï¼å¯ä»¥ä½¿æ°æ®åºæå¡å¨ä¸æ åæ¶é´æºè¿è¡åæ¥ï¼é¿å å æ¶é´ä¸åæ¥è导è´çæ°æ®é®é¢ã
以ä¸æ¯å¨æä½ç³»ç»å±é¢å¯¹MySQLè¿è¡ä¼åçä¸äºå¸¸è§é 置项ã请注æï¼å ·ä½çé ç½®å¯è½å æä½ç³»ç»çæ¬ååè¡çèå¼ãå¨è¿è¡é ç½®æ¶ï¼å»ºè®®åèå®æ¹ææ¡£åæä½ç³»ç»çæä½³å®è·µæåï¼å¹¶æ ¹æ®å®é éæ±è¿è¡éå½çè°æ´ã
æ¥ä¸æ¥ï¼æ们继ç»è¿è¡SQLå±é¢çæ¥è¯¢ä¼åã
æ¥è¯¢ä¼å
è¿è¡æ¥è¯¢ä¼åé¦ééè¦äºè§£æ¥è¯¢ä¼åå¨ï¼æ¥è¯¢ä¼åå¨æ¯MySQLä¸çä¸ä¸ªå ³é®ç»ä»¶ï¼å®è´è´£åææ¥è¯¢è¯å¥å¹¶çææä¼çæ¥è¯¢æ§è¡è®¡åã
æ¥è¯¢ä¼åå¨æ ¹æ®æ¥è¯¢çå¤æ度ã表çç»è®¡ä¿¡æ¯åç´¢å¼çå ç´ ï¼è¯ä¼°ä¸åçæ§è¡è®¡åï¼å¹¶éæ©ä»£ä»·æä½çæ§è¡è®¡åæ¥æ§è¡æ¥è¯¢ã
æ¥è¯¢ä¼åå¨çå·¥ä½åçåç¸å ³æ¦å¿µå¦ä¸ï¼
1.æ¥è¯¢ä¼åå¨çå·¥ä½æµç¨ï¼
- 解ææ¥è¯¢è¯å¥ï¼æ¥è¯¢ä¼åå¨é¦å ä¼å¯¹æ¥è¯¢è¯å¥è¿è¡è§£æï¼å°å ¶è½¬å为å é¨çæ¥è¯¢æ æé»è¾è¡¨è¾¾å¼ã
- æ¥è¯¢éåï¼ä¼åå¨å¯è½å¯¹æ¥è¯¢è¿è¡éåï¼ä»¥ä¼åæ¥è¯¢ç»æåæ¥è¯¢æ¡ä»¶ã
- æ¥è¯¢ä¼åï¼ä¼åå¨æ ¹æ®ç»è®¡ä¿¡æ¯ãç´¢å¼åå ¶ä»ç¸å ³ä¿¡æ¯ï¼çæä¸åçæ§è¡è®¡åï¼å¹¶è¯ä¼°æ¯ä¸ªæ§è¡è®¡åç代价ã
- éæ©æä¼æ§è¡è®¡åï¼ä¼åå¨éæ©ä»£ä»·æä½çæ§è¡è®¡åï¼å¹¶çææ§è¡è®¡åçæ§è¡æ令ã
- æ§è¡æ¥è¯¢ï¼MySQLçæ§è¡å¼ææ ¹æ®ä¼åå¨çæçæ§è¡è®¡åï¼æ§è¡æ¥è¯¢å¹¶è¿åç»æã
2.æ¥è¯¢ä¼åå¨çä¼åè¿ç¨ï¼
- æ¥è¯¢é¢ä¼°ï¼ä¼åå¨æ ¹æ®ç»è®¡ä¿¡æ¯åæ¥è¯¢æ¡ä»¶é¢ä¼°æ¥è¯¢ç»æéç大å°ï¼ä»¥å³å®ä½¿ç¨åªä¸ªæ§è¡è®¡åã
- ç´¢å¼éæ©ï¼ä¼åå¨æ ¹æ®ç´¢å¼çéæ©æ§ååçéæ©æ§ï¼å³å®æ¯å¦ä½¿ç¨ç´¢å¼ä»¥å使ç¨åªä¸ªç´¢å¼ã
- è¿æ¥é¡ºåºéæ©ï¼å¯¹äºæ¶åå¤ä¸ªè¡¨çæ¥è¯¢ï¼ä¼åå¨éæ©åéç表è¿æ¥é¡ºåºï¼ä»¥åå°ä¸é´ç»æéç大å°åè¿æ¥æä½ç代价ã
- åæ¥è¯¢ä¼åï¼ä¼åå¨å°è¯å°åæ¥è¯¢è½¬å为è¿æ¥æä½æåºç¨ä¼åçææ¯ï¼ä»¥åå°åæ¥è¯¢çæ§è¡æ¬¡æ°åå¼éã
- éåæ¥è¯¢ï¼ä¼åå¨å¯è½å¯¹æ¥è¯¢è¿è¡éåï¼ä½¿ç¨çä»·çæ¥è¯¢ç»æï¼ä»¥æ¹è¿æ¥è¯¢çæ§è¡æçã
3.ç»è®¡ä¿¡æ¯ç使ç¨ï¼
- 表ç»è®¡ä¿¡æ¯ï¼ä¼åå¨ä½¿ç¨è¡¨çç»è®¡ä¿¡æ¯ï¼å¦è¡æ°ãåçå¯ä¸å¼æ°éçï¼æ¥ä¼°è®¡æ¥è¯¢çéæ©æ§å代价ã
- ç´¢å¼ç»è®¡ä¿¡æ¯ï¼ä¼åå¨ä½¿ç¨ç´¢å¼çç»è®¡ä¿¡æ¯ï¼å¦ç´¢å¼çéæ©æ§ãå¹³åæ°æ®é¡µç大å°çï¼æ¥è¯ä¼°ç´¢å¼ç使ç¨ä»£ä»·ã
- æ´æ°ç»è®¡ä¿¡æ¯ï¼ç»è®¡ä¿¡æ¯ä¼éçæ°æ®çååèååï¼ä¼åå¨å¯è½éè¦å®ææ´æ°ç»è®¡ä¿¡æ¯ï¼ä»¥ä¿ææ¥è¯¢ä¼åçåç¡®æ§ã
4.æ¥è¯¢ä¼åå¨çå½±åå ç´ ï¼
- æ¥è¯¢å¤æ度ï¼æ¥è¯¢çå¤æ度è¶é«ï¼ä¼åå¨éè¦èèçæ§è¡è®¡åè¶å¤ï¼ä¼åçæ¶é´å代价ä¹ä¼å¢å ã
- æ°æ®åå¸ï¼æ°æ®çåå¸æ åµä¼å½±åä¼åå¨çç´¢å¼éæ©åè¿æ¥é¡ºåºçå³çï¼ä¸åçæ°æ®åå¸å¯è½å¯¼è´ä¸åçæ§è¡è®¡åã
åªæäºè§£æ¥è¯¢ä¼åå¨çæ¶åé»è¾ï¼æè½æ´å¥½çææ¡æ¥è¯¢ä¼åçç¸å ³æ¹æ³ã
æ¥è¯¢ä¼å主è¦æ段
SQLæ¥è¯¢ä¼åæ¶åå°å¤ä¸ªæ¹é¢ï¼ä»¥ä¸æ¯ä¸äºå¸¸è§çSQLæ¥è¯¢ä¼åææ¯åç¸å ³æ¹é¢ç详ç»æè¿°ï¼
ä¼åæ¥è¯¢è¯å¥ï¼
- 使ç¨æ°å½çSQLè¯å¥ï¼æ ¹æ®æ¥è¯¢éæ±éæ©åéçSQLè¯å¥ï¼é¿å åä½æå¤æçæ¥è¯¢æä½ã
- åå°æ°æ®è¿åéï¼åªéæ©éè¦çåï¼é¿å è¿åä¸å¿ è¦çæ°æ®ï¼åå°ç½ç»ä¼ è¾åç»æéå¤çå¼éã
å建éå½çç´¢å¼ï¼
MySQLç´¢å¼æ¯ä¸ç§ç¨äºå å¿«æ°æ®æ£ç´¢é度åæé«æ¥è¯¢æ§è½çæ°æ®ç»æã
å®ç±»ä¼¼äºä¹¦ç±çç®å½ï¼éè¿æç §æ个æå¤ä¸ªåçå¼è¿è¡æåºååå¨ï¼ä½¿å¾æ°æ®åºå¯ä»¥æ´å¿«å°å®ä½å访é®ç¹å®çæ°æ®è¡ã
å ¶ä¸ç´¢å¼çç±»åæ以ä¸å ç±»ï¼
- B-Treeç´¢å¼ï¼B-Treeï¼å¹³è¡¡æ ï¼æ¯MySQLæ常ç¨çç´¢å¼ç±»åãå®å°ç´¢å¼æ°æ®ä»¥æ å½¢ç»æåå¨ï¼æ¯æå¿«éçèå´æ¥æ¾å精确æ¥æ¾ã
- åå¸ç´¢å¼ï¼åå¸ç´¢å¼å°ç´¢å¼åçå¼æ å°ä¸ºåå¸å¼ï¼å¹¶å°å ¶åå¨å¨åå¸è¡¨ä¸ãå®éç¨äºçå¼æ¥è¯¢ï¼ä½ä¸æ¯æèå´æ¥è¯¢åæåºã
- å ¨æç´¢å¼ï¼å ¨æç´¢å¼ç¨äºå¯¹ææ¬å段è¿è¡å ¨ææç´¢ï¼æä¾æ´é«çº§çææ¬æç´¢åè½ã
ç´¢å¼çå建åæ¥çæ¹å¼ï¼
- å建索å¼ï¼ä½¿ç¨CREATE INDEXè¯å¥å¯ä»¥å建索å¼ï¼å¯ä»¥éæ©ååç´¢å¼æå¤åç´¢å¼ï¼ä¹å¯ä»¥ä¸ºç´¢å¼æå®æåºæ¹å¼ååå¨ç±»åã
- æ¥çç´¢å¼ï¼ä½¿ç¨SHOW INDEXè¯å¥å¯ä»¥æ¥ç表çç´¢å¼ä¿¡æ¯ï¼å æ¬ç´¢å¼å称ãååãç±»åçã
- ä¿®æ¹åå é¤ç´¢å¼ï¼ä½¿ç¨ALTER TABLEè¯å¥å¯ä»¥ä¿®æ¹æå é¤ç°æçç´¢å¼ã
å¨å建索å¼æ¶ï¼å¯ä»¥éµå¾ªä»¥ä¸ååæ¥ç¡®ä¿ç´¢å¼çæææ§åæ§è½ï¼
- éæ©åéçåï¼éæ©ç»å¸¸ç¨äºæ¥è¯¢æ¡ä»¶ãè¿æ¥åæåºçåä½ä¸ºç´¢å¼åãè¿äºåé常æ¯é¢ç¹è¿è¡æ°æ®æ£ç´¢åè¿æ»¤çåï¼éè¿ç´¢å¼å¯ä»¥æé«æ¥è¯¢æ§è½ã
- èèåçéæ©æ§ï¼éæ©å ·æé«éæ©æ§çåä½ä¸ºç´¢å¼åï¼å³ä¸éå¤çå¼è¶å¤è¶å¥½ãé«éæ©æ§çåå¯ä»¥æ´å¿«å°ç¼©å°æç´¢èå´ï¼æé«æ¥è¯¢æçã
- é¿å è¿å¤çç´¢å¼ï¼è¿å¤çç´¢å¼ä¼å¢å æ°æ®æå ¥ãæ´æ°åå é¤çå¼éï¼åæ¶å ç¨æ´å¤çåå¨ç©ºé´ãéè¦æè¡¡ç´¢å¼çæ°éåæ§è½æåçç¨åº¦ï¼é¿å å建ä¸å¿ è¦çç´¢å¼ã
- èèå¤åç´¢å¼ï¼å½å¤ä¸ªåç»å¸¸åæ¶ç¨äºæ¥è¯¢æ¡ä»¶æè¿æ¥æä½æ¶ï¼å¯ä»¥å建å¤åç´¢å¼ãå¤åç´¢å¼å¯ä»¥åå°ç´¢å¼çæ°éï¼æé«æ¥è¯¢æ§è½ã
- ç´¢å¼é¿åº¦ï¼å¯¹äºå符串类åçåï¼å°½éæå®éå½çç´¢å¼é¿åº¦ï¼ä»¥åå°ç´¢å¼çåå¨ç©ºé´å ç¨åæ¥è¯¢æ§è½çå¼éã
- èèæ¥è¯¢çæåºéæ±ï¼å¦ææ¥è¯¢ç»å¸¸æ¶åæåºæä½ï¼å¯ä»¥å¨ç´¢å¼ä¸å å«æåºåï¼ä»¥é¿å 使ç¨ä¸´æ¶è¡¨åæåºæä½ï¼æé«æ¥è¯¢æ§è½ã
- çæµåè¯ä¼°ç´¢å¼çææï¼å¨å建索å¼åï¼éè¿æ§è½çæµå·¥å ·åæ¥è¯¢åææ¥è¯ä¼°ç´¢å¼çææãæ ¹æ®å®é æ§è½è¡¨ç°ï¼è¿è¡ç´¢å¼çè°æ´åä¼åã
- å®æç»´æ¤ç´¢å¼ï¼éçæ°æ®çåååæ¥è¯¢æ¨¡å¼çæ¼åï¼ç´¢å¼çææå¯è½ä¼åçååãå®æè¿è¡ç´¢å¼çé建æä¼åï¼ä»¥ä¿æç´¢å¼çæçåæ§è½ã
æ»çæ¥è¯´ï¼ç´¢å¼çå建éè¦ç»¼åèèæ¥è¯¢æ¨¡å¼ãæ°æ®ç¹æ§åæ§è½éæ±ã
éè¿æ£ç¡®è®¾è®¡å使ç¨ç´¢å¼ï¼å¯ä»¥å å¿«æ¥è¯¢é度ï¼åå°æ°æ®æ«æçå¼éï¼å¹¶æé«æ°æ®åºçæ´ä½æ§è½ã
å¨å建å管çç´¢å¼æ¶ï¼éè¦æ ¹æ®å®é éæ±åæ¥è¯¢æ¨¡å¼éæ©åéçç´¢å¼ç±»åãç´¢å¼ååç´¢å¼æ°éï¼ä»¥è¾¾å°æä½³çæ¥è¯¢ä¼åææã
ä¼åæ°æ®æ¨¡åå表ç»æï¼
- æ£è§åæ°æ®æ¨¡åï¼éµå¾ªæ°æ®åºè®¾è®¡çè§èï¼æ¶é¤æ°æ®åä½ï¼æé«æ¥è¯¢æçã
- åçåå表åååºï¼å°å¤§è¡¨åå为æ´å°ç表æ使ç¨ååºææ¯ï¼æé«æ¥è¯¢æçåæ°æ®ç»´æ¤æ§è½ã
çæµååææ¥è¯¢æ§è½ï¼
- 使ç¨æ§è½çæ§å·¥å ·ï¼çæµæ°æ®åºçæ§è½ææ ï¼å¦æ¥è¯¢ååºæ¶é´ãéçå¾ æ¶é´çï¼åæ¶åç°æ§è½ç¶é¢ã
- åææ§è¡è®¡åï¼ä½¿ç¨EXPLAINè¯å¥åææ¥è¯¢çæ§è¡è®¡åï¼æ¥çç´¢å¼ä½¿ç¨æ åµåæ§è½ç¶é¢ï¼ä¼åæ¥è¯¢è¯å¥åç´¢å¼è®¾è®¡ã
å®æç»´æ¤åä¼åï¼
- å®ææ¶éç»è®¡ä¿¡æ¯ï¼éè¿æ¶é表çç»è®¡ä¿¡æ¯ï¼ä¼åæ¥è¯¢ä¼åå¨çå³çï¼æé«æ¥è¯¢è®¡åçåç¡®æ§åæ§è½ã
- å®æé建索å¼ï¼å½ç´¢å¼ç¢çå严éæ¶ï¼å®æé建索å¼ï¼æé«ç´¢å¼çæçã
SQLæ¥è¯¢ä¼åæ¯ä¸ä¸ªç»¼åæ§çå·¥ä½ï¼éè¦ç»¼åèèæ°æ®åºç»æãç´¢å¼è®¾è®¡ãæ¥è¯¢è¯å¥ãç³»ç»é ç½®çå¤ä¸ªæ¹é¢ãéè¿ä¸æä¼åæ¥è¯¢æ§è½ï¼å¯ä»¥æé«æ°æ®åºçååºé度åç³»ç»çæ´ä½æ§è½ã
çµååºæ¯æ¡ä¾å®æ
åå¤å·¥ä½
å¨è¿è¡å®ææ¡ä¾æ¼ç¤ºåï¼æ们éè¦åå¤ç¸å ³æ°æ®ï¼
æ们é½ç¥éï¼å¨çµåå¹³å°ä¸ï¼ææ ¸å¿çæ°æ®ä¸ºï¼ç¨æ·ãååã订åï¼
å æ¤ï¼æ们éè¦å建äºå¯¹åºä¸å¼ 表ï¼ä»¥åæ¹éåå§å大éæ°æ®ï¼
å ¶ä¸ï¼è¡¨ç»æç®å设计å¦ä¸ï¼
CREATE TABLE `my_customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL DEFAULT '' COMMENT 'å§å',
`age` int(3) DEFAULT '20' COMMENT 'å¹´é¾',
`gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'æ§å« 0-女 1-ç·',
`phone` varchar(20) DEFAULT '' COMMENT 'å°å',
`address` varchar(100) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `my_customer_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客æ·';
CREATE TABLE `my_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL DEFAULT '1' COMMENT 'æ°é',
`total_price` int(11) NOT NULL DEFAULT '1' COMMENT 'æ»ä»·',
`order_status` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '订åç¶æ 0-æªæ¯ä» 1-å·²æ¯ä» 2-æ´¾éä¸ 3-å·²ç¾æ¶',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订å';
CREATE TABLE `my_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL COMMENT 'ååå',
`type` int(11) NOT NULL DEFAULT '1' COMMENT 'ç±»å 1-è¡£æ 2-é£å 3-书ç±',
`brand` varchar(100) DEFAULT '' COMMENT 'åç',
`shop_id` int(11) NOT NULL DEFAULT '1' COMMENT 'åºéºID',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='åå';
å ¶ä¸ï¼ç¨æ·æ°æ®é为100ä¸ï¼ååæ°æ®é10ä¸ï¼è®¢åæ°æ®éè¿åä¸ã
æ¥ä¸æ¥, æä»¬æ ¹æ®å®é çµåå¹³å°å¸¸è§æ¥è¯¢åºæ¯è¿è¡åæåä¼åã
åºæ¯1ï¼ç¨æ·æç´¢
çµååå°ç®¡çç³»ç»é常éè¦æ ¹æ®ç¨æ·å称ãææºå·ãå°åæç´¢ç¸å ³ç¨æ·ä¿¡æ¯ï¼
常è§çæ¥è¯¢SQLè¯å¥å¦ä¸ï¼
select * from `my_customer` where phone like '%176%'
æ们å©ç¨explain åæä¸è¯¥sqlè¯å¥çæ§è¡è¿ç¨ã
MySQL ç EXPLAIN æ¯ä¸ä¸ªé常æç¨çå·¥å ·ï¼å®å¯ä»¥åæå解éæ¥è¯¢è¯å¥çæ§è¡è®¡åï¼å¸®å©å¼åè ä¼åæ¥è¯¢æ§è½ãéè¿æ§è¡ EXPLAIN å½ä»¤ï¼å¯ä»¥è·åæ¥è¯¢æ§è¡è®¡åç详ç»ä¿¡æ¯ï¼å æ¬ä»¥ä¸å段ï¼
id: æ¥è¯¢çå¯ä¸æ è¯ç¬¦ï¼ç¨äºæ è¯æ¯ä¸ªæ¥è¯¢çä¸åæ¥éª¤æåæ¥è¯¢ã
select_type: æ¥è¯¢ç±»åï¼è¡¨ç¤ºæ¥è¯¢çç±»ååæ¹å¼ï¼å¸¸è§çåå¼å æ¬ï¼
SIMPLE: ç®åæ¥è¯¢ï¼ä¸å å«åæ¥è¯¢æèæ¥ã
PRIMARY: å¤å±æ¥è¯¢ã
SUBQUERY: åæ¥è¯¢ã
DERIVED: æ´¾ç表çæ¥è¯¢ã
UNION: UNION æ¥è¯¢ã
UNION RESULT: UNION æ¥è¯¢çç»æã
DEPENDENT UNION: ä¾èµäºå¤é¨æ¥è¯¢ç UNION æ¥è¯¢ã
UNION RESULT: UNION æ¥è¯¢çç»æã
DEPENDENT UNION: ä¾èµäºå¤é¨æ¥è¯¢ç UNION æ¥è¯¢ã
UNION RESULT: UNION æ¥è¯¢çç»æã
DEPENDENT UNION: ä¾èµäºå¤é¨æ¥è¯¢ç UNION æ¥è¯¢ã
table: 表åï¼è¡¨ç¤ºæ¥è¯¢æ¶åç表åæ表çå«åã
partitions: ååºä¿¡æ¯ï¼å¦ææ¥è¯¢æ¶åå°ååºè¡¨ï¼åæ¾ç¤ºååºä¿¡æ¯ã
type: 访é®ç±»åï¼è¡¨ç¤ºæ¥è¯¢ä½¿ç¨ç访é®æ¹æ³åç®æ³ï¼å¸¸è§çåå¼å æ¬ï¼
ALL: å ¨è¡¨æ«æï¼éè¦æ«ææ´ä¸ªè¡¨ã
index: åªè®¿é®ç´¢å¼ï¼æ éæ«æ表æ°æ®ã
range: 使ç¨ç´¢å¼èå´è¿è¡æ¥è¯¢ã
ref: 使ç¨éå¯ä¸ç´¢å¼æå¯ä¸ç´¢å¼åç¼è¿è¡æ¥è¯¢ã
eq_ref: 使ç¨å¯ä¸ç´¢å¼è¿è¡çå¼æ¥è¯¢ã
const: 常éæ¥è¯¢ï¼ä½¿ç¨å¸¸éå¼è¿è¡æ¥è¯¢ã
system: ç³»ç»è¡¨æ¥è¯¢ã
NULL: æ æææªç¥çæ¥è¯¢ç±»åã
possible_keys: å¯è½ä½¿ç¨çç´¢å¼ï¼è¡¨ç¤ºæ¥è¯¢å¯è½ä½¿ç¨çç´¢å¼å表ã
key: å®é 使ç¨çç´¢å¼ï¼è¡¨ç¤ºæ¥è¯¢å®é 使ç¨çç´¢å¼ã
key_len: 使ç¨ç´¢å¼çé¿åº¦ï¼è¡¨ç¤ºç´¢å¼ä¸ä½¿ç¨çåèæ°ã
ref: è¿æ¥æ¡ä»¶ï¼è¡¨ç¤ºè¿æ¥ä½¿ç¨çåæ常éã
rows: 估计çè¡æ°ï¼è¡¨ç¤ºæ¥è¯¢æ«æçè¡æ°ä¼°è®¡å¼ã
filtered: è¿æ»¤çè¡ç¾åæ¯ï¼è¡¨ç¤ºæ¥è¯¢ç»æä¸å®é è¿åçè¡æ°ç¾åæ¯ã
Extra: é¢å¤ä¿¡æ¯ï¼è¡¨ç¤ºæ¥è¯¢çéå ä¿¡æ¯ï¼å¯è½å æ¬æåºã临æ¶è¡¨ã使ç¨çæ件çã
éè¿åæ EXPLAIN çè¾åºç»æï¼å¯ä»¥äºè§£æ¥è¯¢çæ§è¡è®¡åã访é®æ¹æ³åå¯è½åå¨çæ§è½é®é¢ãå¯ä»¥æ ¹æ®è¾åºç»æä¸çå段信æ¯ï¼ä¼åæ¥è¯¢è¯å¥ãç´¢å¼è®¾è®¡åæ°æ®åºé ç½®ï¼ä»¥æé«æ¥è¯¢æ§è½åæçã
mysql> explain select * from `my_customer` where phone like '%157%';
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | my_customer | NULL | ALL | NULL | NULL | NULL | NULL | 995164 | 11.11 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
æ们å¯ä»¥çå°è¯¥sqlè¯å¥çæ§è¡è®¡åä¸ï¼typeå段为ALL , è¡¨ç¤ºå ¨è¡¨æ«æï¼è¿ä¼å¯¼è´æ¥è¯¢æçè¿ä½ï¼èæ¶è¿é¿ã
é¦å æ们åºè¯¥èè为æ¥è¯¢å段å ä¸ç´¢å¼ï¼ä¾å¦phoneå段ã
mysql> CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer (phone);
è¿éè¦æ³¨æï¼æ¨¡ç³å¹é æ¥è¯¢ä½¿ç¨ % å¨å¼å¤´ä¼å¯¼è´ç´¢å¼å¤±æã
å¯ä»¥å°è¯å°æ¥è¯¢æ¡ä»¶æ¹ä¸ºä»¥ % ç»å°¾ç模ç³å¹é ï¼ä¾å¦
select * from `my_customer` where phone like '157%';
æ¥ä¸æ¥ä½¿ç¨explain å½ä»¤å次æ¥çæ§è¡è®¡åï¼
mysql> explain select * from `my_customer` where phone like '157%';
+----+-------------+-------------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | my_customer | NULL | range | my_customer_phone_IDX | my_customer_phone_IDX | 83 | NULL | 103520 | 100.00 | Using index condition |
+----+-------------+-------------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
æ们å¯ä»¥çå°sqlæ§è¡è¿ç¨ä¸å®é ç¨å°äºmy_customer_phone_IDX ç´¢å¼, ç¸æ¯å ¨è¡¨æ«æï¼è¿éé¢è®¡æ«æå½æ°ä» 10wå¤è¡ã
å¨å®é å¼åè¿ç¨ä¸ï¼åºè¯¥é¿å ä½¿ç¨ SELECT *ï¼åªéæ©éè¦çå段ï¼èä¸æ¯ä½¿ç¨éé 符 *ãåªéæ©å¿ è¦çå段å¯ä»¥åå°æ°æ®ä¼ è¾åå åå¼éï¼æé«æ¥è¯¢æ§è½ã
ä¾å¦ï¼æä»¬ä» éè¦æ ¹æ®ç¨æ·ææºå·æ¥è¯¢ç¨æ·idåå§å,
é£ä¹ï¼sqlåºè¯¥æ¹åå¦ä¸ï¼
select id, name from `my_customer` where phone like '157%';
é£ä¹å°è¿éï¼å½åsqlè¯å¥è½å¦è¿ä¸æ¥ä¼åå¢ï¼
çæ¡æ¯è¯å®çï¼
é¦å æ们è¦äºè§£ä¸å表æ¥è¯¢è¿ä¸ªæ¦å¿µ,
å表æ¥è¯¢æ¯æå¨ä½¿ç¨éè¦çç´¢å¼ï¼Non-Clustered Indexï¼è¿è¡æ¥è¯¢æ¶ï¼å½éè¦è·åæ¥è¯¢ç»ææéçæ°æ®åä¸å¨ç´¢å¼ä¸æ¶ï¼MySQL éè¦éè¿ç´¢å¼çæéåå°ä¸»ç´¢å¼ï¼Clustered Indexï¼ææ°æ®é¡µä¸è·å缺失çæ°æ®åã
å¨å表æ¥è¯¢ä¸ï¼é¦å æ ¹æ®éè¦çç´¢å¼å®ä½å°ç¬¦åæ¥è¯¢æ¡ä»¶çç´¢å¼è®°å½ï¼ç¶åéè¿ç´¢å¼ä¸çæéè·å主索å¼ææ°æ®é¡µä¸çç¸åºæ°æ®åãè¿ä¸ªè¿ç¨æ¶åäºä¸¤æ¬¡ç£ç访é®ï¼å³é¦å 访é®ç´¢å¼é¡µï¼å次访é®ä¸»ç´¢å¼ææ°æ®é¡µï¼å æ¤ç¸å¯¹äºè¦çç´¢å¼çæ¥è¯¢ï¼å表æ¥è¯¢ä¼å¼å ¥é¢å¤çç£ç读åæä½ï¼å¢å äºæ¥è¯¢çå¼éåååºæ¶é´ã
å表æ¥è¯¢å¯è½ä¼å¯¹æ¥è¯¢æ§è½äº§çä¸å®çå½±åï¼ç¹å«æ¯å¨å¤§æ°æ®éåé«å¹¶åæ¥è¯¢çæ åµä¸ãå æ¤ï¼ä¸ºäºåå°å表æ¥è¯¢çå¼éï¼å¯ä»¥èèéè¿è¦çç´¢å¼ï¼Covering Indexï¼ æ¥è¿è¡ä¼åï¼
è¦çç´¢å¼ï¼Covering Indexï¼æ¯æå¨æ¥è¯¢è¿ç¨ä¸ï¼ç´¢å¼å å«äºæ¥è¯¢æéçæææ°æ®åï¼æ éå表æ¥è¯¢ä¸»ç´¢å¼ææ°æ®é¡µãæ¢å¥è¯è¯´ï¼è¦çç´¢å¼è½å¤ç´æ¥æä¾æ¥è¯¢æéçæ°æ®ï¼èä¸éè¦åå»è®¿é®ä¸»ç´¢å¼ææ°æ®é¡µï¼ä»èæé«æ¥è¯¢æ§è½åæçã
å¨ä¸è¬çç´¢å¼ä¸ï¼åªå å«äºè¢«ç´¢å¼çå以å主索å¼çå¼ç¨æéãå½æ§è¡æ¥è¯¢æ¶ï¼MySQL é¦å éè¿ç´¢å¼å®ä½å°ç¬¦åæ¡ä»¶çè®°å½ï¼ç¶ååéè¿ä¸»ç´¢å¼ææ°æ®é¡µè·å缺失çæ°æ®åï¼è¿ä¸ªè¿ç¨è¢«ç§°ä¸ºå表æ¥è¯¢ãèè¦çç´¢å¼åé¿å äºå表æ¥è¯¢çå¼éï¼å 为索å¼æ¬èº«å°±å å«äºæ¥è¯¢æéçæææ°æ®åã
è¦çç´¢å¼ç好å¤ä¸»è¦ä½ç°å¨ä»¥ä¸å 个æ¹é¢ï¼
æé«æ¥è¯¢æ§è½ï¼ç±äºè¦çç´¢å¼è½å¤ç´æ¥æä¾æ¥è¯¢æéçæ°æ®ï¼åå°äºç£ççéæºè®¿é®åé¢å¤çå表æ¥è¯¢æä½ï¼ä»èå å¿«äºæ¥è¯¢çæ§è¡é度ã
åå°ç£ç I/Oï¼å表æ¥è¯¢éè¦è¿è¡é¢å¤çç£ç读åæä½ï¼èè¦çç´¢å¼å¯ä»¥åå°ç£ç I/O æä½ï¼éä½ç³»ç»çç£çè´è½½ã
åå°å åæ¶èï¼è¦çç´¢å¼å¯ä»¥åå°éè¦å è½½å°å åä¸çæ°æ®éï¼èçäºå åç使ç¨ï¼æé«äºæ¥è¯¢çæçã
è¦å建è¦çç´¢å¼ï¼éè¦éæ©éå½çç´¢å¼åï¼ä»¥å å«æ¥è¯¢è¯å¥ä¸æ¶åçææåãè¿éè¦ç»¼åèèæ¥è¯¢çéæ±ãæ°æ®åçéæ©æ§åç´¢å¼ç大å°çå ç´ ãéè¦æ³¨æçæ¯ï¼å建è¿å¤çè¦çç´¢å¼å¯è½ä¼å¢å ç´¢å¼çç»´æ¤ææ¬ååå¨ç©ºé´å ç¨ã
æ»ä¹ï¼è¦çç´¢å¼æ¯ä¸ç§ä¼åæ段ï¼éè¿ç´¢å¼å å«æ¥è¯¢æéçæææ°æ®åï¼é¿å äºå表æ¥è¯¢ï¼æé«äºæ¥è¯¢çæ§è½åæçã使ç¨è¦çç´¢å¼å¯ä»¥å¨éå½çæ åµä¸ä¼åæ¥è¯¢ï¼ä½éè¦æè¡¡ç´¢å¼ç设计åç»´æ¤ææ¬ã
è¿éï¼æ们éæ°å建my_customer_phone_IDX ç´¢å¼ï¼èæ¬å¦ä¸ï¼
CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer (phone,name);
éæ°ä½¿ç¨explain å½ä»¤å次æ¥çæ§è¡è®¡åï¼
mysql> explain select id, name from `my_customer` where phone like '157%';
+----+-------------+-------------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | my_customer | NULL | range | my_customer_phone_IDX | my_customer_phone_IDX | 83 | NULL | 100018 | 100.00 | Using where; Using index |
+----+-------------+-------------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
è¿éï¼æ们å¯ä»¥çå°Extra å段çå¼å å«Using index, 表æ触åäºç´¢å¼è¦çï¼æ²¡æè¿è¡å表æ¥è¯¢ï¼æ¥è¯¢æ¶é´å¤§å¤§åå°ã
åçï¼å¦æSQLå¦ä¸ï¼
select count(name) from `my_customer` where phone like '157%';
è¦çç´¢å¼ä¹ä¼çæã
åºæ¯2ï¼è®¢åæ¥è¯¢
ä¸ç®¡æ¯ç¨æ·App端è¿æ¯å¨çµååå°ï¼é½åå¨è®¢åæ¥è¯¢çåºæ¯ï¼
ä¾å¦æ们éè¦æ ¹æ®åçæ¥è¯¢å¯¹åºåçä¸ååç订åï¼
æ们é¦å ç»åå表å 个以åçå段ä½ä¸ºç´¢å¼ï¼
CREATE INDEX my_product_brand_IDX USING BTREE ON store.my_product (brand);
æ们å ç»åºä¸æ¡å¸¸è§çæ¥è¯¢SQL:
select * from my_order mo where product_id in (select id from my_product mp where brand = 'Apple');
sqlæ¥è¯¢èæ¶è¿6000ms, æ¥çæ§è¡è®¡åï¼
mysql> explain select * from my_order mo where product_id in (select id from my_product mp where brand = 'Apple');
+----+-------------+-------+------------+--------+------------------------------+---------+---------+---------------------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+------------------------------+---------+---------+---------------------+---------+----------+-------------+
| 1 | SIMPLE | mo | NULL | ALL | NULL | NULL | NULL | NULL | 7529130 | 100.00 | NULL |
| 1 | SIMPLE | mp | NULL | eq_ref | PRIMARY,my_product_brand_IDX | PRIMARY | 4 | store.mo.product_id | 1 | 5.00 | Using where |
+----+-------------+-------+------------+--------+------------------------------+---------+---------+---------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
å¯ä»¥çå°æ两æ¡æ§è¡è®¡åï¼å ¶ä¸è®¢å表çæ¥è¯¢ä½¿ç¨äºå ¨è¡¨æ«æï¼
æ们åç»è®¢å表çprodcut_idå段å ä¸ç´¢å¼ï¼
CREATE INDEX my_order_product_id_IDX USING BTREE ON store.my_order (product_id);
å次æ¥çæ§è¡è®¡åï¼
mysql> explain select * from my_order mo where product_id in (select id from my_product mp where brand = 'Apple');
+----+-------------+-------+------------+------+------------------------------+-------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------------------+-------------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | mp | NULL | ref | PRIMARY,my_product_brand_IDX | my_product_brand_IDX | 403 | const | 1027 | 100.00 | Using index |
| 1 | SIMPLE | mo | NULL | ref | my_order_product_id_IDX | my_order_product_id_IDX | 4 | store.mp.id | 75 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------------------+-------------------------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
è¿éï¼æ们å¯ä»¥çå°ä¸¤æ¡è®¡åé½ç¨å°äºprodcut_idå段索å¼ï¼å å¿«äºæ¥è¯¢æçã
è½ç¶åæ¥è¯¢å¨å½åæ åµä¸å®ç°äºæ¥è¯¢éæ±ï¼ä½ä½¿ç¨åæ¥è¯¢å¯è½ä¼å¯¼è´ä¸äºæ§è½é®é¢ï¼å æ¤å¨ä¼åæ¥è¯¢æ¶ï¼é常ä¸å»ºè®®è¿åº¦ä¾èµåæ¥è¯¢ã以ä¸æ¯ä¸äºåå ï¼
- æ§è¡å¤æ¬¡æ¥è¯¢ï¼æç太差ï¼æ§è¡åæ¥è¯¢æ¶ï¼MYSQLéè¦å建临æ¶è¡¨ï¼æ¥è¯¢å®æ¯ååå é¤è¿äºä¸´æ¶è¡¨ï¼æ以ï¼åæ¥è¯¢çé度ä¼åå°ä¸å®çå½±åï¼è¿éå¤äºä¸ä¸ªå建åéæ¯ä¸´æ¶è¡¨çè¿ç¨ã
- å¯è¯»æ§åç»´æ¤æ§å·®ï¼å¤æçåµå¥åæ¥è¯¢å¯è½ä¼ä½¿æ¥è¯¢è¯å¥åå¾é¾ä»¥ç解åç»´æ¤ãåæ¥è¯¢é常éè¦ç解åµå¥å±æ¬¡åå个åæ¥è¯¢ä¹é´çå ³ç³»ï¼ä½¿æ¥è¯¢è¯å¥åå¾åé¿ä¸é¾ä»¥é 读ã
- 缺ä¹ä¼åçµæ´»æ§ï¼æ°æ®åºä¼åå¨å¨å¤çåæ¥è¯¢æ¶çä¼åè½åç¸å¯¹è¾å¼±ãä¼åå¨å¾é¾å¯¹å¤æçåµå¥åæ¥è¯¢è¿è¡å ¨é¢çä¼åï¼å¯è½æ æ³éæ©æä½³æ§è¡è®¡åï¼å¯¼è´æ§è½ä¸éã
- å¯è½å¼åæ§è½é®é¢ï¼åæ¥è¯¢å¯è½å¯¼è´å ¨è¡¨æ«ææ临æ¶è¡¨çå建ï¼å¢å ç³»ç»ç I/O è´æ åå åæ¶èãç¹å«æ¯å½åæ¥è¯¢æ¶å大éæ°æ®ææ¶åå¤è¡¨å ³èæ¶ï¼æ§è½é®é¢å¯è½æ´å ææ¾ã
对äºè½å¤ä½¿ç¨è¿æ¥æ¥è¯¢ï¼JOINï¼æå ¶ä»æ´æææ¹æ³æ¿ä»£çåæ¥è¯¢ï¼é常建议使ç¨æ´ç®æ´åé«æçæ¥è¯¢æ¹å¼ãè¿æ¥æ¥è¯¢å¯ä»¥æ´å¥½å°å©ç¨ç´¢å¼åä¼åæ§è¡è®¡åï¼åæ¶æä¾æ´å¥½çå¯è¯»æ§åç»´æ¤æ§ã
ç¶èï¼å¹¶éæææ åµä¸é½ä¸æ¨è使ç¨åæ¥è¯¢ãå¨æäºç¹å®çåºæ¯ä¸ï¼åæ¥è¯¢æ¯åççéæ©ï¼ä¾å¦éè¦è¿è¡åå¨æ§æ£æ¥æå¨æ¥è¯¢ä¸åµå¥èåå½æ°çæ åµãå¨ä½¿ç¨åæ¥è¯¢æ¶ï¼éè¦æ ¹æ®å®é æ åµç»¼åèèæ§è½ãå¯è¯»æ§åç»´æ¤æ§çæè¡¡ï¼ç¡®ä¿è¾¾å°æä½³çæ¥è¯¢ææã
è¿éï¼æ们åºè¯¥å°SQLè¯å¥æ¹å为è¿æ¥æ¥è¯¢ï¼JOIN),
SELECT mo.id as orderId, mo.customer_id as customerId, mp.name as productName, mo.order_status as orderStatus FROM my_order mo JOIN my_product mp ON mo.product_id = mp.id WHERE mp.brand = 'Apple';
è½ç¶å¤è¡¨è¿æ¥æ¥è¯¢ï¼å¤è¡¨ JOINï¼æ¯å¸¸è§çæ¥è¯¢æ¹å¼ä¹ä¸ï¼ä½æ¯ä¸æ¦joinæ¶åå°çæ°æ®éå¾å¤§æçå°±å¾é¾ä¿è¯ï¼è¿ç§æ åµä¸å¼ºçæ¨èåå«æ ¹æ®ç´¢å¼å表åæ°æ®ï¼ç¶åå¨åºç¨å±éé¢åjoinï¼mergeæ°æ®ã
å¨åºç¨å±å ³èçä¼å¿å¦ä¸ï¼
- æé«ç¼åæçï¼åºç¨ç¨åºå¯ä»¥æ¹ä¾¿å°ç¼åå表æ¥è¯¢çç»æ对象ãéè¿æåå ³èæ¥è¯¢ï¼å½å ³è表ä¸çæ°æ®åçååæ¶ï¼ä¸ä¼å½±åå°æ¥è¯¢ç¼åï¼ä»èæé«ç¼åçæçã
- åå°éç«äºï¼æåæ¥è¯¢å¯ä»¥åå°éçç«äºãæ§è¡å个æ¥è¯¢æ¶ï¼åªæ¶åå°å个表ï¼åå°äºéçå²çªï¼æé«äºå¹¶åæ§è½ã
- æäºæ°æ®åºæåï¼å¨åºç¨å±è¿è¡å ³èæ¥è¯¢ï¼æ´å®¹æå®ç°æ°æ®åºçæåï¼æä¾é«æ§è½åå¯æ©å±æ§çè½åã
- æåæ¥è¯¢æçï¼ä½¿ç¨ IN() æ¿ä»£å ³èæ¥è¯¢æ¶ï¼MySQLå¯ä»¥æç § ID ç顺åºè¿è¡æ¥è¯¢ï¼è¿å¯è½æ¯éæºçå ³èæ¥è¯¢æ´é«æã
- åå°åä½è®°å½æ¥è¯¢ï¼åºç¨å±å ³èæ¥è¯¢æå³çæ¯æ¡è®°å½åªéè¦æ¥è¯¢ä¸æ¬¡ï¼èå¨æ°æ®åºä¸è¿è¡å ³èæ¥è¯¢å¯è½éè¦éå¤è®¿é®é¨åæ°æ®ãå æ¤ï¼è¿ç§éæè¿å¯ä»¥åå°ç½ç»åå åçå¼éã
- åå¸å ³èæçæ´é«ï¼åºç¨å±å ³èç¸å½äºå¨åºç¨ä¸å®ç°äºåå¸å ³èï¼èä¸æ¯ä½¿ç¨MySQLçåµå¥å¾ªç¯å ³èãå¨æäºåºæ¯ä¸ï¼åå¸å ³èçæçè¦é«å¾å¤ã
åä¹ä¸æ¨èä½¿ç¨ JOIN çåå ï¼
- 大è§æ¨¡è¡¨çæ§è½ååï¼å½è¡¨çæ°æ®éè¾¾å°ç¾ä¸çº§å«æ¶ï¼ä½¿ç¨ JOIN å¯è½å¯¼è´æ§è½ä¸éã
- åå¸å¼ååºå表ï¼è·¨åº JOIN ä¸æ¨è使ç¨ï¼å 为ç®åMySQLçåå¸å¼ä¸é´ä»¶å¯¹è·¨åº JOIN çæ¯æä¸ä½³ã
- 表ç»æä¿®æ¹çå¤ææ§ï¼ä¿®æ¹å表æ¥è¯¢ç¸å¯¹å®¹æï¼èä¿®æ¹ JOIN ç SQL è¯å¥è¾ä¸ºå¤æï¼ç»´æ¤ææ¬è¾é«ã
å½ç¶joinå¨é¨ååºæ¯ä½¿ç¨ä¹æ好å¤ï¼
ä¾å¦å页æ¥è¯¢ï¼JOIN æ¥è¯¢å¯ä»¥æ¹ä¾¿å°è¿è¡å页ï¼å¯ä»¥ä½¿ç¨å¯è¡¨çå段ä½ä¸ºæ¥è¯¢æ¡ä»¶ï¼å¨æ¥è¯¢æ¶å°å¯è¡¨çå¹é å段ä½ä¸ºç»æéï¼ä½¿ç¨ä¸»è¡¨è¿è¡ IN() æ¥è¯¢ã
åºæ¯3ï¼å页æ¥è¯¢
é£ä¹æ¥ä¸æ¥ï¼æ们åççå页æ¥è¯¢æ åµä¸çä¼åï¼
ä¸è¬å ¸åå页æ¥è¯¢è¯å¥å¦ä¸ï¼
SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus FROM my_order mo where mo.order_status = 1 order by mo.id asc limit 1000000, 10
limitæ¯æ常ç¨çå页æ¹æ³,å®å¨æ§è¡è¿ç¨ä¸ï¼ç¸å½äºå éåäºå1000000个ï¼ç¶ååäºç¬¬1000000å°1000010个ï¼èå¼äºå1000000个, limitè¶å¤§æ¥è¯¢æ§è½è¶ä½ï¼limitä» éç¨äºå°æ°æ®èå´å çå页æ¥è¯¢ã
mysql> explain SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus FROM my_order mo where mo.order_status = 1 order by mo.id asc limit 1000000, 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | mo | NULL | index | NULL | PRIMARY | 4 | NULL | 1000010 | 10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
é£ä¹æ们åºè¯¥æä¹ä¼åå¢ï¼
æ们å¯ä»¥å©ç¨ç´¢å¼æ¥è¿è¡ä¼åï¼ä¾å¦æ们å页æ¥è¯¢å°ç¬¬1000000æ¡æ°æ®ï¼è®¢åID为3997806ï¼é£ä¹ä¸ä¸ªå页çææ订åIDé½æ¯å¤§äº3997806ï¼
sqlè¯å¥å¯ä»¥æ¹å为ï¼
SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus FROM my_order mo inner join (select id from my_order where id > 3997806 and order_status = 1 limit 100) mo2 on mo.id = mo2.id order by mo.id asc
sqlè¯å¥çæ§è¡ä»10séä½å°100msï¼æåè¿100åï¼
æ们å¯ä»¥çä¸æ§è¡è®¡åï¼
mysql> explain SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus FROM my_order mo inner join (select id from my_order wh
ere id > 3997806 and order_status = 1 limit 100) mo2 on mo.id = mo2.id order by mo.id asc
-> ;
+----+-------------+------------+------------+--------+---------------+---------+---------+--------+---------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------+---------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | mo | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mo2.id | 1 | 100.00 | NULL |
| 2 | DERIVED | my_order | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3764565 | 10.00 | Using where |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------+---------+----------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)
ä»æ¥è¯¢è®¡åæ们çå°ï¼é¦å åæ¥è¯¢æ ¹æ®ä¸»é®ç´¢å¼ï¼è·åæå¤10æ¡è®¢åID, ç¶ååæ ¹æ®è¿10æ¡id è·åæ°æ®è¯¦æ ãä¸éè¦åæ¥è¯¢ä¸ç¾ä¸æ¡æ°æ®åæåºåæéå è¡æ°æ®ã
åºæ¯4ï¼è®¢åç»è®¡æ¥è¡¨æ¥è¯¢
æ¥ä¸æ¥ï¼æ们å¨çä¸ç¬¬4个åºæ¯ï¼è®¢åç»è®¡ã
çµåå¹³å°ç»å¸¸éè¦ä»å¤ä¸ªç»´åº¦ç»è®¡è®¢åæ°æ®ï¼ä¾å¦è®¢åæ°ã订åæ»é¢ãçé¨ååæè¡ççã
è¿éå设æ们éè¦æ¥è¯¢ä¸åååç订åæ°å订åæ»é¢ï¼
ç»åºç第ä¸çsql å¦ä¸æ示ï¼
select mo.product_id , count(*) as num , sum(mo.total_price) from my_order mo group by mo.product_id
æ§è¡é¨åæ åµå¦ä¸:
| 99995 | 65 | 21528 |
| 99996 | 85 | 24549 |
| 99997 | 75 | 23156 |
| 99998 | 89 | 27123 |
| 99999 | 90 | 24190 |
| 100000 | 79 | 26625 |
+------------+-----+---------------------+
100000 rows in set (1 min 48.82 sec)
10000个ååèæ¶å°è¾¾äº48ç§å¤ï¼
对äºåç»ç»è®¡æ¥è¯¢ï¼ä»¥ä¸æ¯ä¸äºä¼åæè·¯ï¼
- 使ç¨åéçç´¢å¼ï¼ä¸ºæ¯æåç»åç»è®¡æä½ï¼å¯ä»¥èèå建åéçç´¢å¼ãä¼åæè·¯å æ¬ï¼
- 为åç»å段åç»è®¡å段å建索å¼ï¼ä»¥æé«åç»åèåæä½çæçã
- èèè¦çç´¢å¼ï¼å³ç´¢å¼å å«ææéè¦çå段ï¼é¿å å表æ¥è¯¢ã
- é对ä¸åçæ¥è¯¢åºæ¯åæ¡ä»¶ï¼éæ©éå½çç´¢å¼ç±»åï¼å¦B-treeç´¢å¼ãåå¸ç´¢å¼çï¼ã
- ç¼åç»æéï¼å¯¹äºé¢ç¹è¿è¡çåç»ç»è®¡æ¥è¯¢ï¼å¯ä»¥èèç¼åç»æéï¼é¿å æ¯æ¬¡é½éæ°è®¡ç®ãä¼åæè·¯å æ¬ï¼
- 使ç¨ç¼åææ¯ï¼å¦Redisï¼åå¨ç»æéï¼ä»¥ä¾¿å¿«éè·åç»è®¡æ°æ®ã
- 设置åéçç¼å失æçç¥ï¼æ ¹æ®æ°æ®çæ´æ°é¢çè¿è¡å®ææ´æ°ææå¨æ´æ°ã
- é¢èåæ°æ®ï¼å¯¹äºå¤§æ°æ®éåå¤æçç»è®¡æ¥è¯¢ï¼å¯ä»¥èèé¢å 计ç®ååå¨èåç»æï¼ä»¥åå°æ¥è¯¢æ¶ç计ç®éãä¼åæè·¯å æ¬ï¼
- å建å®ææå®æ¶çé¢èåä»»å¡ï¼å°ç»è®¡ç»æåå¨å°ç¹å®ç表ä¸ã
- å¨æ¥è¯¢æ¶ç´æ¥ä»é¢èå表ä¸è·åç»æï¼é¿å éå¤ç计ç®ååç»æä½ã
- åç设置åç»å段ï¼å¯¹äºåç»ç»è®¡æ¥è¯¢ï¼åç»å段çéæ©ä¼å½±åæ¥è¯¢æ§è½ãä¼åæè·¯å æ¬ï¼
- å°½ééæ©å ·æé«åºæ°ï¼ä¸ååå¼è¾å¤ï¼çå段ä½ä¸ºåç»å段ï¼ä»¥åå°åç»çæ°éå计ç®éã
- é¿å å¨æ¥è¯¢ä¸ä½¿ç¨è¿å¤å¤æç表达å¼æå½æ°ä½ä¸ºåç»å段ï¼ä»¥åå°è®¡ç®çå¼éã
- èè并è¡è®¡ç®ï¼å¯¹äºå¤§è§æ¨¡æ°æ®çåç»ç»è®¡æ¥è¯¢ï¼å¯ä»¥èè使ç¨å¹¶è¡è®¡ç®æ¥æé«æ¥è¯¢æçãä¼åæè·¯å æ¬ï¼
- å°æ¥è¯¢ä»»å¡æå为å¤ä¸ªå¹¶è¡çåä»»å¡ï¼æ¯ä¸ªåä»»å¡å¤çä¸åçæ°æ®åéã
- 使ç¨å¹¶è¡è®¡ç®æ¡æ¶ææ°æ®åºå¼ææ¯æ并è¡æ¥è¯¢ï¼ä»¥å å¿«æ¥è¯¢é度åæé«ååéã
å½ç¶å ·ä½çä¼åçç¥éè¦æ ¹æ®å ·ä½çä¸å¡åºæ¯åæ°æ®ç¹ç¹è¿è¡éæ©åè°æ´ã
å 为æ们å¨ä¹åçåºæ¯æ¡ä¾éï¼å·²ç»å¯¹product_id å段å äºç´¢å¼ï¼æ们å¯ä»¥æ ¹æ®ç¬¬ä¸æ¡å第äºæ¡ä¼å建议ï¼å¹¶è¡è®¡ç®ï¼å¨åºç¨å±èåæ°æ®ï¼
èèæ¯æ¡sqlä» å¯¹é¨åååè¿è¡ç»è®¡ï¼ä¾å¦ï¼
select mo.product_id , count(*) as num , sum(mo.total_price) from my_order mo where mo.product_id between 1000 and 2000 group by mo.product_id;
è¿éä» å¯¹ååIDå¨ï¼1000,2000ï¼èå´å ç订åè¿è¡ç»è®¡ï¼æ们å¯ä»¥åå¤æ¬¡æ¥è¯¢ä¸åçæ°æ®ã
使ç¨å段ç»è®¡åï¼æ们å¯ä»¥çä¸æ§è¡æçï¼
| 1997 | 91 | 27524 |
| 1998 | 54 | 14298 |
| 1999 | 74 | 24560 |
| 2000 | 68 | 23343 |
+------------+-----+---------------------+
1001 rows in set (1.26 sec)
mysql> explain select mo.product_id , count(*) as num , sum(mo.total_price) from my_order mo where mo.product_id between 1000 and 2000 group by mo.product_id
;
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | mo | NULL | range | my_order_product_id_IDX | my_order_product_id_IDX | 4 | NULL | 147998 | 100.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
å¯ä»¥çå°è¿ééç¨äºmy_order_product_id_IDXç´¢å¼å å¿«æ¥è¯¢ï¼å¦å¤ç±äºæ°æ®éçåå°ï¼è¿è¡æåºåç»è®¡çèæ¶ä¹å¤§å¤§åå°ã
å¦å¤å¨æ§è¡è®¡åéï¼Extra å段ä¸å å«Using filesortå¼, è¿è¡¨æå¨åç»çè¿ç¨ä¸ï¼è¿é»è®¤ç¨å°äºæåºï¼
å¦æä¸éè¦æåºï¼æ们åºè¯¥æ¾ç¤ºå£°æï¼ä¿®æ¹SQLå¦ä¸æ示ï¼
select mo.product_id , count(*) as num , sum(mo.total_price) from my_order mo where mo.product_id between 1000 and 2000 group by mo.product_id order by null
MySQLæ§è½çæ§ä¸æ¥è¦
MySQLæ§è½çæ§åæ¥è¦æ¯ä¿è¯æ°æ®åºè¿è¡ç¨³å®æ§åæ§è½ä¼åçå ³é®æ¥éª¤ãå®å¯ä»¥å¸®å©æ们å®æ¶çæ§æ°æ®åºçå项ææ ï¼å¹¶å¨å¼å¸¸æ åµåçæ¶åæ¶ååºè¦æ¥ï¼ä»¥ä¾¿åæ¶éåæªæ½è¿è¡æ éææ¥åæ§è½ä¼åã
以ä¸æ¯ä¸äºå¸¸ç¨çMySQLæ§è½çæ§ææ ãå·¥å ·å使ç¨è¯´æï¼
1. çæ§ææ ï¼
- æ¥è¯¢æ§è½ï¼å æ¬æ ¢æ¥è¯¢ãæ¥è¯¢ååºæ¶é´ãæ¥è¯¢ååéçã
- è¿æ¥å并åæ§è½ï¼å æ¬å¹¶åè¿æ¥æ°ã线ç¨æ± 使ç¨æ åµãè¿æ¥è¯·æ±çã
- ç¼åå½ä¸çï¼å æ¬æ¥è¯¢ç¼åãInnoDBç¼å²æ± çå½ä¸çã
- éçå¾ åæ»éæ åµï¼å æ¬éçå¾ æ¶é´ãæ»é次æ°çã
- ç£çIOæ§è½ï¼å æ¬è¯»åé度ãç£çå©ç¨çãIOçå¾ æ¶é´çã
- 主ä»å¤å¶ç¶æï¼å æ¬å»¶è¿æ¶é´ãåæ¥ç¶æçã
2. çæ§å·¥å ·ï¼
- MySQL Enterprise Monitorï¼å®æ¹æä¾çåä¸çæ§å·¥å ·ï¼æä¾å ¨é¢çæ§è½çæ§åè¦æ¥åè½ã
- Percona Monitoring and Management (PMM)ï¼ä¸ä¸ªå è´¹å¼æºçMySQLçæ§å管çå·¥å ·ï¼æä¾ä¸°å¯çæ§è½ææ åè¦æ¥åè½ã
- Nagiosï¼ä¸ä¸ªå¹¿æ³ä½¿ç¨çå¼æºçæ§å·¥å ·ï¼å¯ä»¥éè¿æ件æ©å±æ¥çæ§MySQLçå项ææ ã
- Zabbixï¼å¦ä¸ä¸ªå¼æºççæ§å·¥å ·ï¼æ¯æMySQLçæ§è½çæ§åè¦æ¥åè½ã
- Prometheusï¼ä¸ä¸ªå¼æºçç³»ç»çæ§åè¦æ¥å·¥å ·ï¼å¯ä»¥éè¿æ件æExporteræ¥çæ§MySQLçæ§è½ææ ã
3. 使ç¨è¯´æï¼
- é ç½®çæ§å·¥å ·ï¼æ ¹æ®æéççæ§å·¥å ·ï¼æç §å ¶ææ¡£è¿è¡å®è£ åé ç½®ï¼å æ¬æå®è¦çæ§çMySQLå®ä¾å设置è¦æ¥éå¼ã
- éæ©åéçææ ï¼æ ¹æ®å®é éæ±åå ³æ³¨ç¹ï¼éæ©éè¦çæ§çææ ï¼å¹¶è®¾ç½®éå½çè¦æ¥éå¼ã
- å®ææ¶éååææ°æ®ï¼çæ§å·¥å ·ä¼å®ææ¶éMySQLçæ§è½æ°æ®ï¼å°å ¶åå¨å¨æ°æ®åºä¸ãå¯ä»¥éè¿çæ§å·¥å ·ççé¢æAPIæ¥æ¥çååææ°æ®ã
- 设置è¦æ¥è§åï¼æ ¹æ®çæ§å·¥å ·çè§å设置åè½ï¼è®¾ç½®åéçè¦æ¥è§åï¼å½ææ è¶ è¿éå¼æ¶è§¦åè¦æ¥ã
- æ éææ¥åä¼åï¼å½æ¶å°è¦æ¥æ¶ï¼åæ¶è¿è¡æ éææ¥ï¼å®ä½é®é¢å¹¶éåç¸åºçä¼åæªæ½ï¼å¦è°æ´åæ°é ç½®ãä¼åæ¥è¯¢è¯å¥ãå¢å 硬件èµæºçã
- å®æè¯ä¼°åè°æ´ï¼å®æè¯ä¼°æ°æ®åºæ§è½ï¼å¹¶æ ¹æ®éæ±è¿è¡è°æ´ï¼å æ¬å¢å çæ§ææ ãè°æ´è¦æ¥éå¼ãä¼åçæ§å·¥å ·çé ç½®çã
éè¿ç»¼å使ç¨çæ§å·¥å ·ååéçææ ï¼å¯ä»¥å®æ¶çæ§MySQLçæ§è½å¹¶æååç°æ½å¨çé®é¢ãåæ¶éåæªæ½è¿è¡æ éææ¥åæ§è½ä¼åï¼å¯ä»¥ç¡®ä¿æ°æ®åºç稳å®æ§åé«æ§è½è¿è¡ã
MySQLè°ä¼å°ç»
MySQLè°ä¼æ¯ä¸ä¸ªæç»ä¼åçè¿ç¨ï¼å®æ¶åå°å¤ä¸ªæ¹é¢çå·¥ä½ï¼å æ¬åæ°é ç½®ãç´¢å¼ä¼åãæ¥è¯¢ä¼åå硬件æä½ç³»ç»ä¼åçã
å¨è¿è¡è°ä¼æ¶ï¼éè¦æ ¹æ®æå¡å¨çå®é é ç½®ååºç¨å±çéæ±ï¼ç»¼åèèåç§å ç´ ï¼å¹¶ç»åå®é åºæ¯è¿è¡ä¼åã
è°ä¼çæè·¯å¯ä»¥æ»ç»ä¸ºä»¥ä¸å ç¹ï¼
- çæ§åè¯ä¼°ï¼é¦å ï¼éè¦å¯¹MySQLè¿è¡çæ§åè¯ä¼°ï¼äºè§£æ°æ®åºçæ§è½ç¶é¢åç¶é¢åå ãéè¿çæ§å·¥å ·åæ§è½è¯ä¼°æ¥åï¼å¯ä»¥è¯å«åºæ ¢æ¥è¯¢ãé«è´è½½åèµæºç¶é¢çé®é¢ã
- åæ°é ç½®ä¼åï¼æ ¹æ®çæ§åè¯ä¼°çç»æï¼é对æ§å°è°æ´MySQLçåæ°é ç½®ãåçé ç½®ç¼å²åºå¤§å°ã并åè¿æ¥æ°åæ¥è¯¢ç¼åçåæ°ï¼ä»¥å åå©ç¨ç³»ç»èµæºå¹¶æé«æ§è½ã
- ç´¢å¼ä¼åï¼éè¿åææ¥è¯¢è¯å¥åæ°æ®è®¿é®æ¨¡å¼ï¼è®¾è®¡åä¼ååéçç´¢å¼ãé¿å å建è¿å¤çç´¢å¼ï¼åæ¶ç¡®ä¿å ³é®æ¥è¯¢è½å¤ä½¿ç¨ç´¢å¼å éæ¥è¯¢æä½ã
- æ¥è¯¢ä¼åï¼ä¼åæ¥è¯¢è¯å¥ï¼é¿å å ¨è¡¨æ«æåä¸å¿ è¦çè¿æ¥æä½ã使ç¨åéçæ¥è¯¢æ¡ä»¶ãJOINè¯å¥ååæ¥è¯¢çææ¯æ段ï¼æåæ¥è¯¢æ§è½ã
- 硬件åæä½ç³»ç»ä¼åï¼æ ¹æ®æå¡å¨çå®é é ç½®åæä½ç³»ç»ç¹ç¹ï¼è¿è¡ç¸åºç硬件åæä½ç³»ç»ä¼åãåçåé å ååç£ç空é´ï¼ä¼åæ件系ç»åç½ç»é ç½®ï¼ä»¥æé«æ°æ®åºçè¿è¡æçã
è°ä¼å·¥ä½éè¦ä¸æå¡å¨å®é é ç½®ååºç¨å±ç´§å¯ç»åãä¸åçåºç¨åºæ¯åä¸å¡éæ±å¯è½éè¦ä¸åçè°ä¼çç¥åéç¹å ³æ³¨çæ¹é¢ãå æ¤ï¼è°ä¼è¿ç¨ä¸éè¦ä¸åºç¨å¼å人åå¯ååä½ï¼äºè§£åºç¨çç¹ç¹åéæ±ï¼ä»¥ç¡®ä¿è°ä¼æ¹æ¡çæææ§åå¯è¡æ§ã
è°ä¼æ¯ä¸ä¸ªæç»çè¿ç¨ï¼éè¦å®æçæ§åè¯ä¼°ç³»ç»æ§è½ï¼å¹¶æ ¹æ®å®é æ åµè¿è¡ä¼ååè°æ´ãä¸æå°ä¼åMySQLæ°æ®åºï¼å¯ä»¥æåç³»ç»çæ§è½å稳å®æ§ï¼ä¸ºç¨æ·æä¾æ´å¥½çä½éªåååºé度ã