当前位置：Gxlcms > 数据库问题 > MySQL通过Explain查看select语句的执行计划结果触发写操作

MySQL通过Explain查看select语句的执行计划结果触发写操作

时间：2021-07-01 10:21:17 帮助过：13人阅读

table if not exists person( id int not null auto_increment primary key, name varchar(16) ); create table if not exists person_opration_log( id int not null auto_increment primary key, pid int not null, access_datetime datetime ); delimiter // create function fun_person_log(pid int) returns int begin insert into person_opration_log(pid,access_datetime) values(pid,now()); return pid; end // delimiter ;

　　正常的数据访问SQL如下，但是它并不写日志

mysql> select
    ->     id,
    ->     name 
    -> from person 
    -> where id = 1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 项羽   |
+----+--------+
1 row in set (0.00 sec)

　　如果我们要写日志可以分两步走，先访问再计一笔日志

mysql> select
    ->     id,
    ->     name 
    -> from person 
    -> where id = 1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 项羽   |
+----+--------+
1 row in set (0.00 sec)

mysql> 
mysql> select fun_person_log(1);
+-------------------+
| fun_person_log(1) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.05 sec)

mysql> 
mysql> select * from person_opration_log ;
+----+-----+---------------------+
| id | pid | access_datetime     |
+----+-----+---------------------+
|  1 |   1 | 2018-10-06 17:12:31 |
+----+-----+---------------------+
1 row in set (0.00 sec)

【牛人想出的新点子把两步合成一步】

　　牛人的新点子

mysql> select 
    ->     fun_person_log(100) as id , 
    ->     name
    -> from person
    -> where id = (select fun_person_log(100));
Empty set (0.04 sec)

mysql> 
mysql> select * from person_opration_log;
+----+-----+---------------------+
| id | pid | access_datetime     |
+----+-----+---------------------+
|  1 |   1 | 2018-10-06 17:12:31 |
|  2 | 100 | 2018-10-06 17:15:29 |
+----+-----+--------------------

　　牛人的新点子刚好入坑，我们可以explain一下

mysql> explain select 
    ->     fun_person_log(250) as id , 
    ->     name
    -> from person
    -> where id = (select fun_person_log(250));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  2 | SUBQUERY    | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.08 sec)

mysql> 
mysql> select * from person_opration_log;
+----+-----+---------------------+
| id | pid | access_datetime     |
+----+-----+---------------------+
|  1 |   1 | 2018-10-06 17:12:31 |
|  2 | 100 | 2018-10-06 17:15:29 |
|  3 | 250 | 2018-10-06 17:17:23 |
+----+-----+---------------------+
3 rows in set (0.00 sec)

　　看吧！ explain引发了写入操作！

【参考连接】

Derived Tables

【学习交流】

-----------------------------http://www.sqlpy.com-------------------------------------------------

技术分享图片

-----------------------------http://www.sqlpy.com-------------------------------------------------

MySQL通过Explain查看select语句的执行计划结果触发写操作

标签：有一个计划 where varchar end har 交流 type 结果

MySQL通过Explain查看select语句的执行计划结果触发写操作

人气教程排行