MySQL5.7新特性之explain UPDATE/DELETE/INSERT

 在MySQL5.5版本中，explain查看执行计划，只能支持select语句，但是在MySQL5.6/5.7版本中，可以支持DML语句，即UPDATE、DELETE、INSERT。
 建立测试表accessLog和accessLog_bak进行测试：

点击(此处)折叠或打开

1. mysql> explain select * from accessLog where id=16649850;
   
2. +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
   
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
   
4. +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
   
5. | 1 | SIMPLE | accessLog | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
   
6. +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
   
7. 1 row in set, 1 warning (0.00 sec)
   
8. 
   
9. mysql> explain delete from accessLog where id=16649850;
   
10. +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
    
11. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    
12. +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
    
13. | 1 | DELETE | accessLog | NULL | range | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using where |
    
14. +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
    
15. 1 row in set (0.00 sec)
    
16. 
    
17. mysql> explain update accessLog_bak set id=111 where id=16649867;
    
18. +----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
19. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    
20. +----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
21. | 1 | UPDATE | accessLog_bak | NULL | ALL | NULL | NULL | NULL | NULL | 582486 | 100.00 | Using where |
    
22. +----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
23. 1 row in set (0.02 sec)
    
24. 
    
25. mysql> explain insert into accessLog select * from accessLog_bak where id=111;
    
26. +----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
27. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    
28. +----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
29. | 1 | INSERT | accessLog | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
    
30. | 1 | SIMPLE | accessLog_bak | NULL | ALL | NULL | NULL | NULL | NULL | 582486 | 10.00 | Using where |
    
31. +----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
32. 2 rows in set (0.02 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30135314/viewspace-2144572/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30135314/viewspace-2144572/