ä½è éè¿ä¸ä¸ªæ ¢æ¥å¿é®é¢ï¼å¼åº MySQL åä¸è´æ§è¯»çæ¦å¿µåå®é åºç¨åºæ¯ã
ä½è ï¼é¾åæ°
ç±å¯ç DBA å¢éæåï¼ä¸»è¦è´è´£ MySQL ææ¯æ¯æï¼æ é¿ MySQLãPGãå½äº§æ°æ®åºã
æ¬ææ¥æºï¼ååæ稿
- ç±å¯çå¼æºç¤¾åºåºåï¼ååå 容æªç»ææä¸å¾éæ使ç¨ï¼è½¬è½½è¯·èç³»å°ç¼å¹¶æ³¨ææ¥æºã
èæ¯
æç³»ç»æ§è¡æ´æ°æä½åç°å¾æ ¢ï¼åç°æ大éæ ¢æ¥å¿ï¼å ¶ä¸ Lock time æ¶é´å æ¯å¾é«ï¼MySQL çæ¬ä¸º 5.7.25ï¼é离级å«ä¸º RRã
åæ
æ¥ç表ç»æ以å UPDATE è¯å¥çæ§è¡è®¡åï¼
mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2621401 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain update test set name ='test' where name='a';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | test | NULL | index | NULL | PRIMARY | 4 | NULL | 2355988 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
éè¿æ§è¡è®¡ååç°ï¼è¯¥ SQL æ¯èµ°ç主é®å ¨ç´¢å¼æ«æï¼å¹¶ä¸å¯¹äº name åæªå ç´¢å¼ï¼å½å¤ä¸ªäºå¡åæ¶æ§è¡æ¶ï¼å°±ä¼è§å¯å°æé»å¡åºç°ã
äºå¡ 1 | äºå¡ 2 |
mysql> begin; <br> Query OK, 0 rows affected (0.00 sec) <br> mysql> update test set name ='test' where name='a'; <br> Query OK, 262144 rows affected (4.67 sec) <br> Rows matched: 262144 Changed: 262144 Warnings: 0 | |
mysql> begin; <br> Query OK, 0 rows affected (0.00 sec) <br> mysql> update test set name ='test1' where name='b'; |
è¥ name åçéå¤å¼ä¸å¤ï¼é£ä¹å¯ä»¥å¯¹ name åæ·»å ç´¢å¼å³å¯è§£å³è¯¥é®é¢ãå 为 InnoDB çè¡éæºå¶æ¯åºäºç´¢å¼åæ¥å®ç°çï¼å¦æ UPDATE è¯å¥è½ä½¿ç¨å° name åçç´¢å¼ï¼é£ä¹å°±ä¸ä¼äº§çé»å¡ï¼å¯¼è´ä¸å¡å¡é¡¿ã
ä½è¥æ¯ name åçå¼çåºå度å¾ä½ï¼å°±ä¼å¯¼è´ SQL ä¸ä¼èµ° name åçç´¢å¼ï¼ç¤ºä¾å¦ä¸ï¼
å æ·»å ç´¢å¼
mysql> alter table test add index tt(name);
Query OK, 0 rows affected (2.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
ç¶åæ¥çæ§è¡è®¡åï¼åç°å¯è½ç¨å°çç´¢å¼æ ttï¼ä½æ¯å®é æ åµä¾ç¶èµ°ç主é®å ¨ç´¢å¼æ«æã
mysql> explain update test set name ='test' where name='a';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | test | NULL | index | tt | PRIMARY | 4 | NULL | 2355988 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
å 为 MySQL çä¼åå¨æ¯åºäºä»£ä»·æ¥è¯ä¼°çï¼æ们å¯ä»¥éè¿ optimizer trace æ¥è§å¯ã
mysql> show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.01 sec)
å¯ä»¥çå°å¼ä¸º enabled=offï¼è¡¨æè¿ä¸ªåè½é»è®¤æ¯å ³éçã
å¦ææ³æå¼è¿ä¸ªåè½ï¼å¿ 须⾸å æ enabled çå¼æ¹ä¸º onã
mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
ç¶åæ§è¡è¯¥ SQLï¼æ¥ç详ç»çä¿¡æ¯ï¼è¿éæ们主è¦å ³æ³¨çæ¯ PREPARE é¶æ®µçææ¬è®¡ç®ã
mysql> update test set name ='test' where name='a';
Query OK, 262144 rows affected (5.97 sec)
Rows matched: 262144 Changed: 262144 Warnings: 0
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
详ç»ç»æå¦ä¸ã
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: update test set name ='test' where name='a'
TRACE: {
"steps": [
{
"substitute_generated_columns": {
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`test`.`name` = 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal('a', `test`.`name`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal('a', `test`.`name`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal('a', `test`.`name`)"
}
]
}
},
{
"table": "`test`",
"range_analysis": {
"table_scan": {
"rows": 2355988,
"cost": 475206
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "tt",
"usable": true,
"key_parts": [
"name",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "no_join"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "tt",
"ranges": [
"0x0100610000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 <= name <= 0x0100610000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 553720,
"cost": 664465,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
å¯ä»¥åç°æ§è¡å ¨è¡¨æ«æçææ¬ä¸º 475206ï¼èµ°ç´¢å¼ tt çææ¬ä¸º 664465ï¼æ以 MySQL éæ©äºå ¨è¡¨æ«æã
é£ä¹å¦ææ¯è¿ç§æ åµæ¹æä¹å¤çå¢ï¼
å¦æ InnoDB é离级å«æ¯ RRï¼æ°æ®åºå±é¢æ²¡æ太好çæ¹å¼ï¼æ¨èåºç¨ç«¯è¿è¡æ¹é ã
å¦ææ°æ®åºé离级å«å¯ä»¥æ´æ¹ï¼é£ä¹å¯ä»¥æ¹ä¸º RC æ¥è§£å³é»å¡çé®é¢ãå 为 RC 模å¼ä¸æ¯æåä¸è´æ§è¯»ã
ä»ä¹æ¯åä¸è´æ§è¯»å¢ï¼
ç®åæ¥è¯´å°±æ¯å½è¦å¯¹è¡è¿è¡å éæ¶ï¼ä¼å¤ä¸æ¥å¤æ该è¡æ¯ä¸æ¯ççéè¦ä¸éãæ¯å¦å ¨è¡¨æ«ææ´æ°çæ¶åï¼æ们åªéè¦æ´æ° WHERE å¹é å°çè¡ï¼å¦ææ¯æ²¡æåä¸è´æ§è¯»å°±ä¼ææææ°æ®è¿è¡å éï¼ä½æ¯æäºåä¸è´æ§è¯»ï¼é£ä¹ä¼å¤ææ¯å¦æ»¡è¶³ WHERE æ¡ä»¶ï¼è¥ä¸æ»¡è¶³åä¸ä¼å éï¼æåéæ¾éï¼ã
é£ä¹å¯¹äºåºå度ä½çå段就å¯ä»¥ä½¿ç¨åä¸è´æ§è¯»ç¹æ§æ¥ä¼åï¼è¿æ ·æ´æ°ä¸åçå¼å°±ä¸ä¼äºç¸çå¾ ï¼å¯¼è´ä¸å¡å¡é¡¿ã
äºå¡ 1 | äºå¡ 2 |
mysql> begin; <br> Query OK, 0 rows affected (0.00 sec) <br> mysql> update test set name ='test' where name='a';<br>Query OK, 262144 rows affected (9.30 sec)<br>Rows matched: 262144 Changed: 262144 Warnings: 0 | |
mysql> begin;<br> Query OK, 0 rows affected (0.00 sec)<br>mysql> update test set name ='test1' where name='b';<br>Query OK, 262144 rows affected (8.46 sec)<br>Rows matched: 262144 Changed: 262144 Warnings: 0 |
ç»è®º
- è¡éæºå¶æ¯åºäºç´¢å¼åå®ç°çï¼è¥æ²¡æ使ç¨å°ç´¢å¼ï¼åä¼è¿è¡å ¨è¡¨æ«æã
- åä¸è´æ§è¯»æ¯åºäº RC é离级å«çä¼åï¼å¯ä»¥åå°éå²çªä»¥åéçå¾ ï¼æå并åã