ä¸ãåè¨
ä¸å¨èå¨çä¸ä¸ªå¥½æå让æåºä¸ç¯æä½ è¯»æ SQL æ§è¡è®¡åï¼åæå¦ä¸ä½è¯»è åé¦çé¢è¯é¢å¦ä½ææ¥æ ¢ SQL ç强ç¸å ³ï¼ç´¢æ§å åºä¸ç¯ä¸æ读æ MySQL Explain æ§è¡è®¡åãExplain æ§è¡è®¡åä½ ä¸å®å¾ä¼çï¼ä¸ç¶ä½ ç®åä¸å°±å«å»åä»ä¹ä½ ä¼ SQL è°ä¼ï¼ä¸ç¶é¢è¯å®ä¼è§å¾ï¼Explain æ§è¡è®¡åä½ é½ä¸ä¼çï¼é£ä½ è¿ SQL è°å¥ä¼åï¼SQL è°ä½ å§ï¼ï¼ï¼å¼ä¸ªå°ç©ç¬ï¼ç©ç¬å½ç©ç¬ï¼éè¦æ¯ççéè¦ï¼ï¼ï¼
äºãExplain æ§è¡è®¡åæ¯ä»ä¹ï¼
ä»ä¹æ¯æ§è¡è®¡åï¼ç®èè¨ä¹ï¼å°±æ¯ SQL å¨æ°æ®åºä¸æ§è¡æ¶ç表ç°æ åµï¼é常ç¨äº SQL æ§è½åæãä¼ååå éåæçåºæ¯ï¼æ§è¡è¿ç¨ä¼å¨ MySQL æ¥è¯¢è¿ç¨ä¸ç±è§£æå¨ï¼é¢å¤çå¨åæ¥è¯¢ä¼åå¨å ±åçæãå¨ MySQL ä¸ä½¿ç¨ explain å ³é®åæ¥æ¥çã
2.1 æ§è¡è®¡åæä»ä¹ç¨ï¼
å®å¯ä»¥ç¨æ¥åæ SQL è¯å¥å表ç»æçæ§è½ç¶é¢
- å ³èæ¥è¯¢çæ§è¡é¡ºåº
- æ¥è¯¢æä½çæä½ç±»å
- åªäºç´¢å¼å¯ä»¥è¢«å½ä¸
- åªäºç´¢å¼å®é 被å½ä¸
- æ¯å¼ 表æå¤å°è®°å½åä¸æ¥è¯¢
- ...
2.2 MySQL æ§è¡è¿ç¨
å¦ä¸å¾æ示ï¼MySQL æ°æ®åºç± Server å±å Engine å±ç»æï¼
- Server å±æ SQL åæå¨ãSQLä¼åå¨ãSQL æ§è¡å¨ï¼ç¨äºè´è´£ SQL è¯å¥çå ·ä½æ§è¡è¿ç¨ï¼
- Engine å±è´è´£åå¨å ·ä½çæ°æ®ï¼å¦æ常使ç¨ç MyISAMãInnoDB åå¨å¼æï¼è¿æç¨äºå¨å åä¸åå¨ä¸´æ¶ç»æéç TempTable å¼æã
SQL ä¼åå¨ä¼åæææå¯è½çæ§è¡è®¡åï¼éæ©ææ¬æä½çæ§è¡ï¼è¿ç§ä¼åå¨ç§°ä¹ä¸ºï¼CBOï¼Cost-based Optimizerï¼åºäºææ¬çä¼åå¨ï¼ã
èå¨ MySQL ä¸ï¼ä¸æ¡ SQL ç计ç®ææ¬è®¡ç®å¦ä¸æ示ï¼
Cost = Server Cost + Engine Cost= CPU Cost + IO Cost
å ¶ä¸ï¼CPU Cost 表示计ç®çå¼éï¼æ¯å¦ç´¢å¼é®å¼çæ¯è¾ãè®°å½å¼çæ¯è¾ãç»æéçæåºçè¿äºæä½é½å¨ Server å±å®æï¼
IO Cost 表示å¼æå± IO çå¼éï¼MySQL 8.0 å¯ä»¥éè¿åºåä¸å¼ 表çæ°æ®æ¯å¦å¨å åä¸ï¼åå«è®¡ç®è¯»åå å IO å¼é以å读åç£ç IO çå¼éã
æ°æ®åº mysql ä¸ç表 server_costãengine_cost åè®°å½äºå¯¹äºåç§ææ¬ç计ç®ï¼å¦ï¼
表 server_cost è®°å½äº Server å±ä¼åå¨åç§æä½çææ¬ï¼è¿éé¢å æ¬äºææ CPU Costï¼å ¶å ·ä½å«ä¹å¦ä¸ï¼
- disk_temptable_create_costï¼å建ç£ç临æ¶è¡¨çææ¬ï¼é»è®¤ä¸º 20ã
- disk_temptable_row_costï¼ç£ç临æ¶è¡¨ä¸æ¯æ¡è®°å½çææ¬ï¼é»è®¤ä¸º 0.5ã
- key_compare_costï¼ç´¢å¼é®å¼æ¯è¾çææ¬ï¼é»è®¤ä¸º 0.05ï¼ææ¬æå°ã
- memory_temptable_create_costï¼å建å å临æ¶è¡¨çææ¬ï¼é»è®¤ä¸º 1ã
- memory_temptable_row_costï¼å å临æ¶è¡¨ä¸æ¯æ¡è®°å½çææ¬ï¼é»è®¤ä¸º 0.1ã
- row_evaluate_costï¼è®°å½é´çæ¯è¾ææ¬ï¼é»è®¤ä¸º 0.1ã
å¯ä»¥çå°ï¼ MySQL ä¼åå¨è®¤ä¸ºå¦æä¸æ¡ SQL éè¦å建åºäºç£çç临æ¶è¡¨ï¼åè¿æ¶çææ¬æ¯æ大çï¼å ¶ææ¬æ¯åºäºå å临æ¶è¡¨ç 20 åãèç´¢å¼é®å¼çæ¯è¾ãè®°å½ä¹é´çæ¯è¾ï¼å ¶å®å¼éæ¯é常ä½çï¼ä½å¦æè¦æ¯è¾çè®°å½æ°é常å¤ï¼åææ¬ä¼åå¾é常大ã
è表 engine_cost è®°å½äºåå¨å¼æå±åç§æä½çææ¬ï¼è¿éå å«äºææç IO Costï¼å ·ä½å«ä¹å¦ä¸ï¼
- io_block_read_costï¼ä»ç£ç读åä¸ä¸ªé¡µçææ¬ï¼é»è®¤å¼ä¸º 1ã
- memory_block_read_costï¼ä»å å读åä¸ä¸ªé¡µçææ¬ï¼é»è®¤å¼ä¸º 0.25ã
ä¹å°±æ¯è¯´ï¼ MySQL ä¼åå¨è®¤ä¸ºä»ç£ç读åçå¼éæ¯å åå¼éç 4 åã
ä¸ãExplain æ§è¡è®¡å详解
æ们å æ¥åå¤ä»¥ä¸ SQL èæ¬ï¼
CREATE TABLE `user` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (20) DEFAULT NULL COMMENT "ç¨æ·å",
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "ç¨æ·è¡¨";
CREATE TABLE `user_robot_relate` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`user_id` INT (11) NOT NULL COMMENT "ç¨æ·id",
`robot_id` INT (11) NOT NULL COMMENT "æºå¨äººid",
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_robot_id` (`robot_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "ç¨æ·ä¸æºå¨äººè¡¨";
CREATE TABLE `robot` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR ( 20 ) DEFAULT NULL COMMENT "æºå¨äººå",
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "æºå¨äººè¡¨";
INSERT INTO user VALUES (1, 'riemann');
INSERT INTO user VALUES (2, 'andy');
INSERT INTO user_robot_relate VALUES (1, 1, 1);
INSERT INTO user_robot_relate VALUES (2, 1, 2);
INSERT INTO user_robot_relate VALUES (3, 2, 3);
INSERT INTO robot VALUES (1, 'å°ç½é²¸');
INSERT INTO robot VALUES (2, 'æ«å°æº');
INSERT INTO robot VALUES (3, 'æ«æä¸ä½æº');
æ们å建ä¸å¼ 表 userãuser_robot_relateãrobotï¼è¡¨ä¹é´çå ³ç³» user.id = user_robot_relate.user_id AND user_robot_relate.robot_id = robot.idã
å æ¥çä¸æç MySQL çæ¬ï¼æ¯ 5.7.37 çã
æ¥çæ们çä¸ä¸æ§è¡è®¡åæåªäºå段ï¼å ç个æ´ä½çï¼è®©å¤§å®¶æ个大æ¦ç认è¯åï¼æ们åéä¸å»è¯¦è§£åæã
explain æ§è¡åè¾åºçç»æéå å« 12 åï¼åå«æ¯ idãselect_typeãtableãpartitionsãtypeãpossible_keysãkeyãkey_lenãrefãrowsãfiltered å Extraï¼ä¸é¢å¯¹è¿äºå段è¿è¡è§£éã
- idï¼Query Optimizer æéå®çæ§è¡è®¡åä¸æ¥è¯¢çåºåå·
- select_typeï¼æ¾ç¤ºæ¬è¡æ¯ç®åæå¤æ selectãå¦ææ¥è¯¢æä»»ä½å¤æçåæ¥è¯¢ï¼åæå¤å±æ 记为PRIMARYãDERIVED.ãUNIONãUNION RESUIT çã
- tableï¼æ¾ç¤ºè¿ä¸æ¥æ访é®çæ°æ®åºä¸ç表çå称
- partitionsï¼æ¥è¯¢æ¶å¹é å°çååºä¿¡æ¯ï¼å¯¹äºéååºè¡¨å¼ä¸º NULLï¼å½æ¥è¯¢çæ¯ååºè¡¨æ¶ï¼partitions æ¾ç¤ºååºè¡¨å½ä¸çååºæ åµã
- typeï¼æ°æ®è®¿é®ã读åæä½ç±»åï¼ALLãindexãrangeãrefãeq_refãconstãsystemï¼ç
- possible_keysï¼è¯¥æ¥è¯¢å¯ä»¥å©ç¨çç´¢å¼ï¼å¦æ没æä»»ä½ç´¢å¼å¯ä»¥ä½¿ç¨ï¼å°±ä¼æ¾ç¤ºæ nullï¼è¿ä¸é¡¹å 容对äºä¼åæ¶åç´¢å¼çè°æ´é常éè¦ã
- keyï¼MySQL Query Optimizer ä» possible_keys ä¸æéæ©ä½¿ç¨çç´¢å¼
- key_lenï¼è¢«éä¸ä½¿ç¨ç´¢å¼çç´¢å¼é®é¿åº¦
- refï¼ååºæ¯éè¿å¸¸éï¼constï¼ï¼è¿æ¯æ个表çæ个å段ï¼å¦ææ¯ joinï¼æ¥è¿æ»¤ï¼éè¿ keyï¼çã
- rowsï¼MySQL Query Optimizer éè¿ç³»ç»æ¶éå°çç»è®¡ä¿¡æ¯ä¼°ç®åºæ¥çç»æéè®°å½æ¡æ°
- filteredï¼è¡¨ç¤ºåå¨å¼æè¿åçæ°æ®å¨ç»è¿è¿æ»¤åï¼å©ä¸æ»¡è¶³æ¡ä»¶çè®°å½æ°éçæ¯ä¾ã
- Extraï¼æ¥è¯¢ä¸æ¯ä¸æ¥å®ç°çé¢å¤ç»èä¿¡æ¯ï¼å¦ Using filesortãindex çã
3.1 id
çå°ä¸æ¡è®°å½ç id é½ç¸åï¼å¯ä»¥ç解æè¿ä¸ä¸ªè¡¨ä¸ºä¸ç»ï¼å ·æåæ ·çä¼å 级ï¼æ§è¡é¡ºåºç±ä¸èä¸ï¼å ·ä½é¡ºåºç±ä¼åå¨å³å®ã
3.1.1 id ç¸å
mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = 'æ«å°æº'));
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | ur | NULL | ALL | idx_user_id,idx_robot_id | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 | 50.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | r | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
3.1.2 id ä¸å
å¦ææ们ç SQL ä¸åå¨åæ¥è¯¢ï¼é£ä¹ id çåºå·ä¼éå¢ï¼id å¼è¶å¤§ä¼å 级è¶é«ï¼è¶å 被æ§è¡ãå½ä¸ä¸ªè¡¨ä¾æ¬¡åµå¥ï¼åç°æéå±çåæ¥è¯¢ id æ大ï¼æå æ§è¡ã
mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = 'æ«å°æº'));
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | ur | NULL | ref | idx_robot_id | idx_robot_id | 4 | const | 1 | 100.00 | Using where |
| 3 | SUBQUERY | r | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
3.1.3 以ä¸ä¸¤ç§åæ¶åå¨
å°ä¸è¾¹ç SQL ç¨å¾®ä¿®æ¹ä¸ä¸ï¼å¢å ä¸ä¸ªåæ¥è¯¢ï¼åç° id ç以ä¸ä¸¤ç§åæ¶åå¨ãç¸å id åå为ä¸ç»ï¼è¿æ ·å°±æä¸ä¸ªç»ï¼åç»çä»ä¸å¾ä¸é¡ºåºæ§è¡ï¼ä¸åç» id å¼è¶å¤§ï¼ä¼å 级è¶é«ï¼è¶å æ§è¡ã
mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = 'æ«å°æº')) AND u.id IN (SELECT u.id FROM user u WHERE u.name = 'riemann');
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | ur | NULL | ref | idx_robot_id | idx_robot_id | 4 | const | 1 | 100.00 | Using where |
| 3 | SUBQUERY | r | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
3.2 select_type
select_typeï¼è¡¨ç¤º select æ¥è¯¢çç±»åï¼ä¸»è¦æ¯ç¨äºåºååç§å¤æçæ¥è¯¢ï¼ä¾å¦ï¼æ®éæ¥è¯¢ãèåæ¥è¯¢ãåæ¥è¯¢çã
- SIMPLEï¼è¡¨ç¤ºæç®åç select æ¥è¯¢è¯å¥ï¼ä¹å°±æ¯å¨æ¥è¯¢ä¸ä¸å å«åæ¥è¯¢æè union 交并差éçæä½ã
- PRIMARYï¼å½æ¥è¯¢è¯å¥ä¸å å«ä»»ä½å¤æçåé¨åï¼æå¤å±æ¥è¯¢å被æ 记为 PRIMARYã
- SUBQUERYï¼å½ select æ where å表ä¸å å«äºåæ¥è¯¢ï¼è¯¥åæ¥è¯¢è¢«æ 记为 SUBQUERYã
- DERIVEDï¼è¡¨ç¤ºå å«å¨ from åå¥ä¸çåæ¥è¯¢ç selectï¼å¨æ们ç from å表ä¸å å«çåæ¥è¯¢ä¼è¢«æ 记为 derivedã
- UNIONï¼å¦æ union åè¾¹ååºç°ç select è¯å¥ï¼åä¼è¢«æ 记为 unionï¼è¥ union å å«å¨ from åå¥çåæ¥è¯¢ä¸ï¼å¤å± select å°è¢«æ 记为 derivedã
- UNION RESULTï¼ä»£è¡¨ä» union ç临æ¶è¡¨ä¸è¯»åæ°æ®ï¼è table åç <union1,4> 表示ç¨ç¬¬ä¸ä¸ªå第å个 select çç»æè¿è¡ union æä½ã
mysql> EXPLAIN SELECT t.user_id, (SELECT u.id FROM user u) o FROM (SELECT ur.user_id, ur.robot_id FROM user_robot_relate ur WHERE ur.id = 2) t UNION (SELECT r.id, r.name FROM robot r);
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | ur | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | u | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
| 4 | UNION | r | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3.3 table
æ¥è¯¢ç表åï¼å¹¶ä¸ä¸å®æ¯çå®åå¨ç表ï¼æå«åæ¾ç¤ºå«åï¼ä¹å¯è½ä¸ºä¸´æ¶è¡¨ï¼ä¾å¦ä¸è¾¹ç DERIVEDã <union1,4> çã
3.4 partitions
æ¥è¯¢æ¶å¹é å°çååºä¿¡æ¯ï¼å¯¹äºéååºè¡¨å¼ä¸º NULLï¼å½æ¥è¯¢çæ¯ååºè¡¨æ¶ï¼partitions æ¾ç¤ºååºè¡¨å½ä¸çååºæ åµã
3.5 type
typeï¼æ¥è¯¢ä½¿ç¨äºä½ç§ç±»åï¼å®å¨ SQLä¼åä¸æ¯ä¸ä¸ªé常éè¦çææ ï¼ä»¥ä¸æ§è½ä»å¥½å°åä¾æ¬¡æ¯ï¼system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- systemï¼å½è¡¨ä» æä¸è¡è®°å½æ¶ï¼ç³»ç»è¡¨ï¼ï¼æ°æ®éå¾å°ï¼å¾å¾ä¸éè¦è¿è¡ç£ç IOï¼é度é常快ã
- constï¼è¡¨ç¤ºæ¥è¯¢æ¶å½ä¸ primary key 主é®æè unique å¯ä¸ç´¢å¼ï¼æè 被è¿æ¥çé¨åæ¯ä¸ä¸ªå¸¸éï¼constï¼å¼ãè¿ç±»æ«ææçæé«ï¼è¿åæ°æ®éå°ï¼é度é常快ã
mysql> EXPLAIN SELECT * FROM robot WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | robot | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
- eq_refï¼æ¥è¯¢æ¶å½ä¸ä¸»é® primary key æè unique key ç´¢å¼ï¼ type å°±æ¯ eq_refã
mysql> EXPLAIN SELECT u.name FROM user u, user_robot_relate ur WHERE u.id = ur.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 1 | SIMPLE | ur | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.u.id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
- refï¼åºå«äº eq_refï¼ref 表示使ç¨éå¯ä¸æ§ç´¢å¼ï¼ä¼æ¾å°å¾å¤ä¸ªç¬¦åæ¡ä»¶çè¡ã
mysql> EXPLAIN SELECT id FROM user_robot_relate WHERE user_id = 2;
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | user_robot_relate | NULL | ref | idx_user_id | idx_user_id | 4 | const | 1 | 100.00 | Using index |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
- ref_or_nullï¼è¿ç§è¿æ¥ç±»åç±»ä¼¼äº refï¼åºå«å¨äº MySQL ä¼é¢å¤æç´¢å å« NULL å¼çè¡ã
-- 为äºæ¨¡æè¿ä¸ªåºæ¯ï¼æåæ°å¢äºä¸ä¸ª user_test 表ã
mysql> CREATE TABLE `user_test` (
-> `id` INT (11) NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR (20) DEFAULT NULL COMMENT "ç¨æ·å",
-> PRIMARY KEY (`id`),
-> KEY `idx_name` (`name`)
-> ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "ç¨æ·æµè¯è¡¨";
Query OK, 0 rows affected (0.01 sec)
mysql> EXPLAIN SELECT id FROM user_test WHERE name = 'riemann' OR name IS NULL;
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | user_test | NULL | ref_or_null | idx_name | idx_name | 83 | const | 2 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
- index_mergeï¼ä½¿ç¨äºç´¢å¼å并ä¼åæ¹æ³ï¼æ¥è¯¢ä½¿ç¨äºä¸¤ä¸ªä»¥ä¸çç´¢å¼ã
-- ä¸è¾¹ç¤ºä¾ä¸åæ¶ä½¿ç¨å°ä¸»é® id åå段 user_id çç´¢å¼ã
mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id > 1 AND user_id = 2;
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| 1 | SIMPLE | user_robot_relate | NULL | index_merge | PRIMARY,idx_user_id | idx_user_id,PRIMARY | 8,4 | NULL | 1 | 100.00 | Using intersect(idx_user_id,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
- unique_subqueryï¼æ¿æ¢ä¸é¢ç IN åæ¥è¯¢ï¼åæ¥è¯¢è¿åä¸éå¤çéåã
value IN (SELECT primary_key FROM single_table WHERE some_expr)
- index_subqueryï¼åºå«äº unique_subqueryï¼ç¨äºéå¯ä¸ç´¢å¼ï¼å¯ä»¥è¿åéå¤å¼ã
value IN (SELECT key_column FROM single_table WHERE some_expr)
- rangeï¼ä½¿ç¨ç´¢å¼éæ©è¡ï¼ä» æ£ç´¢ç»å®èå´å çè¡ãç®åç¹è¯´å°±æ¯é对ä¸ä¸ªæç´¢å¼çå段ï¼ç»å®èå´æ£ç´¢æ°æ®ãå¨ where è¯å¥ä¸ä½¿ç¨ bettween...andã<ã>ã<=ãin çæ¡ä»¶æ¥è¯¢ type é½æ¯ rangeã
-- user_robot_relate è¡¨ä¸ id 为å¯ä¸ä¸»é®ï¼name æ®éå段æªå»ºç´¢å¼ã
mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id BETWEEN 2 AND 3;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user_robot_relate | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------
ä»ç»æä¸çå°åªæ对设置äºç´¢å¼çå段ï¼åèå´æ£ç´¢ type ææ¯ rangeã
mysql> EXPLAIN SELECT * FROM user WHERE name BETWEEN 2 AND 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
- indexï¼Index ä¸ ALL å ¶å®é½æ¯è¯»å ¨è¡¨ï¼åºå«å¨äº index æ¯éåç´¢å¼æ 读åï¼è ALL æ¯ä»ç¡¬çä¸è¯»åã
-- id 为主é®ï¼ä¸å¸¦ where æ¡ä»¶å
¨è¡¨æ¥è¯¢ï¼type ç»æ为 indexã
mysql> EXPLAIN SELECT id FROM robot;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | robot | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
- ALLï¼å°éåå ¨è¡¨ä»¥æ¾å°å¹é çè¡ï¼æ§è½æå·®ã
mysql> EXPLAIN SELECT * FROM robot;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | robot | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
3.6 possible_keys
possible_keysï¼è¡¨ç¤ºå¨ MySQL ä¸éè¿åªäºç´¢å¼ï¼è½è®©æ们å¨è¡¨ä¸æ¾å°æ³è¦çè®°å½ï¼ä¸æ¦æ¥è¯¢æ¶åå°çæ个å段ä¸åå¨ç´¢å¼ï¼åç´¢å¼å°è¢«ååºï¼ä½è¿ä¸ªç´¢å¼å¹¶ä¸å®ä¸ä¼æ¯æç»æ¥è¯¢æ°æ®æ¶æ被ç¨å°çç´¢å¼ãå ·ä½è¯·åèä¸è¾¹çä¾åã
3.7 key
keyï¼åºå«äº possible_keysï¼key æ¯æ¥è¯¢ä¸å®é 使ç¨å°çç´¢å¼ï¼è¥æ²¡æ使ç¨ç´¢å¼ï¼æ¾ç¤ºä¸º NULLãå ·ä½è¯·åèä¸è¾¹çä¾åã
å½ type 为 index_merge æ¶ï¼å¯è½ä¼æ¾ç¤ºå¤ä¸ªç´¢å¼ã
3.8 key_len
key_lenï¼è¡¨ç¤ºæ¥è¯¢ç¨å°çç´¢å¼é¿åº¦ï¼åèæ°ï¼ï¼ååä¸é¿åº¦è¶çè¶å¥½ ã
- ååç´¢å¼ï¼é£ä¹éè¦å°æ´ä¸ªç´¢å¼é¿åº¦ç®è¿å»ï¼
- å¤åç´¢å¼ï¼ä¸æ¯ææåé½è½ç¨å°ï¼éè¦è®¡ç®æ¥è¯¢ä¸å®é ç¨å°çåã
注ï¼key_len åªè®¡ç® where æ¡ä»¶ä¸ç¨å°çç´¢å¼é¿åº¦ï¼èæåºååç»å³ä¾¿æ¯ç¨å°äºç´¢å¼ï¼ä¹ä¸ä¼è®¡ç®å° key_len ä¸ã
3.9 ref
ååºæ¯éè¿å¸¸éï¼constï¼ï¼è¿æ¯æ个表çæ个å段ï¼å¦ææ¯ joinï¼æ¥è¿æ»¤ï¼éè¿ keyï¼çã
3.10 rows
rowsï¼ä»¥è¡¨çç»è®¡ä¿¡æ¯åç´¢å¼ä½¿ç¨æ åµï¼ä¼°ç®è¦æ¾å°æ们æéçè®°å½ï¼éè¦è¯»åçè¡æ°ã
è¿æ¯è¯ä¼° SQL æ§è½çä¸ä¸ªæ¯è¾éè¦çæ°æ®ï¼MySQL éè¦æ«æçè¡æ°ï¼å¾ç´è§çæ¾ç¤º SQL æ§è½ç好åï¼ä¸è¬æ åµä¸ rows å¼è¶å°è¶å¥½ã
3.11 filtered
filtered è¿ä¸ªæ¯ä¸ä¸ªç¾åæ¯çå¼ï¼è¡¨é符åæ¡ä»¶çè®°å½æ°çç¾åæ¯ãç®åç¹è¯´ï¼è¿ä¸ªå段表示åå¨å¼æè¿åçæ°æ®å¨ç»è¿è¿æ»¤åï¼å©ä¸æ»¡è¶³æ¡ä»¶çè®°å½æ°éçæ¯ä¾ã
å¨ MySQL.5.7 çæ¬ä»¥åæ³è¦æ¾ç¤º filtered éè¦ä½¿ç¨ explain extended å½ä»¤ãMySQL.5.7 åï¼é»è®¤ explain ç´æ¥æ¾ç¤º partitions å filtered çä¿¡æ¯ã
3.12 Extra
Extra ï¼ä¸éåå¨å ¶ä»åä¸æ¾ç¤ºçä¿¡æ¯ï¼Explain ä¸çå¾å¤é¢å¤çä¿¡æ¯ä¼å¨ Extra å段æ¾ç¤ºã
3.12.1 Using index
Using indexï¼æ们å¨ç¸åºç select æä½ä¸ä½¿ç¨äºè¦çç´¢å¼ï¼éä¿ä¸ç¹è®²å°±æ¯æ¥è¯¢çå被索å¼è¦çï¼ä½¿ç¨å°è¦çç´¢å¼æ¥è¯¢é度ä¼é常快ï¼SQL ä¼åä¸çæ³çç¶æã
ä»ä¹åæ¯è¦çç´¢å¼?
ä¸æ¡ SQL åªéè¦éè¿ç´¢å¼å°±å¯ä»¥è¿åï¼æ们æéè¦æ¥è¯¢çæ°æ®ï¼ä¸ä¸ªæå 个å段ï¼ï¼èä¸å¿ éè¿äºçº§ç´¢å¼ï¼æ¥å°ä¸»é®ä¹ååéè¿ä¸»é®æ¥è¯¢æ´è¡æ°æ®ï¼SELECT * ï¼ã
id 为 user 表ç主é®
mysql> EXPLAIN SELECT id FROM user;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
注æï¼æ³è¦ä½¿ç¨å°è¦çç´¢å¼ï¼æä»¬å¨ select æ¶åªååºéè¦çå段ï¼ä¸å¯ SELECT *ï¼èä¸è¯¥å段建äºç´¢å¼ã
mysql> EXPLAIN SELECT * FROM user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
3.12.2 Using where
Using whereï¼æ¥è¯¢æ¶æªæ¾å°å¯ç¨çç´¢å¼ï¼è¿èéè¿ where æ¡ä»¶è¿æ»¤è·åæéæ°æ®ï¼ä½è¦æ³¨æçæ¯å¹¶ä¸æ¯ææ带 where è¯å¥çæ¥è¯¢é½ä¼æ¾ç¤º Using whereã
ä¸è¾¹ç¤ºä¾ name 并æªç¨å°ç´¢å¼ï¼type 为 ALLï¼å³ MySQL éè¿å ¨è¡¨æ«æååæ where æ¡ä»¶çéæ°æ®ã
mysql> EXPLAIN SELECT name FROM user WHERE name = 'riemann';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
3.12.3 Using temporary
Using temporaryï¼è¡¨ç¤ºæ¥è¯¢åç»æéè¦ä½¿ç¨ä¸´æ¶è¡¨æ¥åå¨ï¼ä¸è¬å¨æåºæè åç»æ¥è¯¢æ¶ç¨å°ã
mysql> EXPLAIN SELECT name FROM user WHERE id IN (1, 2) GROUP BY name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
3.12.4 Using filesort
Using filesortï¼è¡¨ç¤ºæ æ³å©ç¨ç´¢å¼å®æçæåºæä½ï¼ä¹å°±æ¯ ORDER BY çå段没æç´¢å¼ï¼é常è¿æ ·ç SQL é½æ¯éè¦ä¼åçã
mysql> EXPLAIN SELECT id FROM user ORDER BY name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
å¦æ ORDER BY å段æç´¢å¼å°±ä¼ç¨å°è¦çç´¢å¼ï¼ç¸æ¯æ§è¡é度快å¾å¤ã
mysql> EXPLAIN SELECT id FROM user ORDER BY id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
3.12.5 Using join buffer
Using join bufferï¼å¨æ们è表æ¥è¯¢çæ¶åï¼å¦æ表çè¿æ¥æ¡ä»¶æ²¡æç¨å°ç´¢å¼ï¼éè¦æä¸ä¸ªè¿æ¥ç¼å²åºæ¥åå¨ä¸é´ç»æã
mysql> EXPLAIN SELECT u.name FROM user u, user_test t WHERE u.name = t.name;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t | NULL | index | idx_name | idx_name | 83 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | u | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
3.12.6 Impossible where
Impossible whereï¼è¡¨ç¤ºå¨æ们ç¨ä¸å¤ªæ£ç¡®ç where è¯å¥ï¼å¯¼è´æ²¡æ符åæ¡ä»¶çè¡ã
mysql> EXPLAIN SELECT name FROM user WHERE 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
3.12.7 No tables used
No tables usedï¼æ们çæ¥è¯¢è¯å¥ä¸æ²¡æ FROM åå¥ï¼æè æ FROM DUAL åå¥ã
mysql> EXPLAIN SELECT now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+