一个SQL问题

下面连个sql,本质是一样的, in里面 第一个套了一层, 第二个没有套
第一个比第二个要快一倍(多次执行也是如此)搞不懂,求解
select real_name,credits from users_slave_09 where user_id in (select * from (select * from tmp_card where user_id=1009236) a);

select real_name,credits from users_slave_09 where user_id in (select * from tmp_card where user_id=1009236);

root@localhost 18:37: [dbe8je6i4c3gjd50]> select real_name,credits from users_slave_09 where user_id in (select * from (select * from tmp_card where user_id=1009236) a);
+-----------+---------+
| real_name | credits |
+-----------+---------+
| | |
+-----------+---------+
1 row in set (0.67 sec)

root@localhost 18:37: [dbe8je6i4c3gjd50]> select real_name,credits from users_slave_09 where user_id in (select * from tmp_card where user_id=1009236);
+-----------+---------+
| real_name | credits |
+-----------+---------+
| | |
+-----------+---------+
1 row in set (1.48 sec)



root@localhost 18:57: [dbe8je6i4c3gjd50]> select version();
+------------+
| version() |
+------------+
| 5.5.56-log |
+------------+
1 row in set (0.00 sec)

root@localhost 18:57: [dbe8je6i4c3gjd50]> show global variables like '%optimizer_switch%';
+------------------+-------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on |
+------------------+-------------------------------------------------------------------------------------------------------------------------+



root@localhost 18:57: [dbe8je6i4c3gjd50]> show create table tmp_card\G
*************************** 1. row ***************************
Table: tmp_card
Create Table: CREATE TABLE `tmp_card` (
`user_id` bigint(20) DEFAULT NULL,
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost 18:58: [dbe8je6i4c3gjd50]> show create table users_slave_09\G
*************************** 1. row ***************************
Table: users_slave_09
Create Table: CREATE TABLE `users_slave_09` (
`user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
`user_name` varchar(250) NOT NULL COMMENT '用户名',
`nick_name` varchar(255) DEFAULT NULL COMMENT '昵称',
`expand_user_name` varchar(200) NOT NULL COMMENT '第三方平台用户名',
`password` varchar(100) NOT NULL COMMENT '密码',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`login_ip` bigint(20) NOT NULL DEFAULT '-1' COMMENT '登录IP',
`login_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登录时间',
`reg_ip` bigint(20) NOT NULL DEFAULT '-1' COMMENT '注册IP',
`reg_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册时间',
`union_id` varchar(50) DEFAULT NULL COMMENT '联盟ID',
`child_union_id` varchar(1000) DEFAULT NULL COMMENT '联盟子ID',
`user_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '用户状态 0 正常 1 禁止登录',
`real_name` varchar(30) DEFAULT NULL COMMENT '真实姓名',
`credits` varchar(30) DEFAULT NULL COMMENT '身份证号',
`creditstype` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '证件类型 0身份证',
`question` int(11) unsigned DEFAULT '0' COMMENT '安全问题',
`answer` varchar(50) DEFAULT NULL COMMENT '安全答案',
`total_score` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '总积分',
`score` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '剩余积分',
`vouch_count_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '充值次数',
`vouch_amount_total` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '充值总金额',
`vouch_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '充值余额',
`lcoins_total` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '充值总平台币',
`lcoins` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '剩余平台币',
`lcoins_less` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '预减平台币',
`user_level` tinyint(1) NOT NULL DEFAULT '1' COMMENT '用户等级',
`newmessages` int(5) unsigned NOT NULL DEFAULT '0' COMMENT '未读站内消息数',
`password_safe_level` int(4) unsigned NOT NULL DEFAULT '1000' COMMENT '密码保护安全等级 1000常规 1001邮件已绑定 1010手机已绑定 1100密保卡已绑定',
`isogfs` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否启用防沉迷 0默认 1启用',
`password_safe_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '密码安全级别 0 极低 1低 2中 3高 默认为0',
`lockstatus` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '消费状态 0正常 1 禁止消费',
`mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
`vipno` int(11) NOT NULL DEFAULT '0' COMMENT 'vip号码',
`sex` enum('F','M') DEFAULT NULL COMMENT '性别',
`birthday` date DEFAULT NULL COMMENT '生日',
`province` varchar(20) DEFAULT NULL COMMENT '省',
`city` varchar(20) DEFAULT NULL COMMENT '市',
`telephone` varchar(30) DEFAULT NULL COMMENT '电话',
`adresss` varchar(50) DEFAULT NULL COMMENT '地址',
`zipcode` varchar(10) DEFAULT NULL COMMENT '邮编',
`qq` varchar(20) DEFAULT NULL COMMENT 'QQ',
`msn` varchar(50) DEFAULT NULL COMMENT 'MSN',
`headimg` varchar(100) DEFAULT NULL COMMENT '用户头像地址',
`is_check_email` tinyint(1) NOT NULL DEFAULT '0' COMMENT '邮箱是否已验证',
`is_subscribe` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否订阅',
`field_expand_int_01` int(10) NOT NULL DEFAULT '0' COMMENT '扩展预留字段',
`field_expand_int_02` int(10) NOT NULL DEFAULT '0' COMMENT '扩展预留字段',
`field_expand_tinyint_01` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1为老用户',
`field_expand_tinyint_02` tinyint(1) NOT NULL DEFAULT '0' COMMENT '扩展预留字段',
`field_expand_varchar_01` varchar(200) DEFAULT NULL COMMENT '扩展预留字段',
`field_expand_varchar_02` varchar(200) DEFAULT NULL COMMENT '扩展预留字段',
`field_expand_varchar_03` varchar(200) DEFAULT NULL COMMENT '京东用户id',
`field_expand_varchar_04` varchar(300) DEFAULT NULL,
`domain` varchar(20) NOT NULL DEFAULT '' COMMENT '二级域名',
`last_name` varchar(250) DEFAULT NULL COMMENT '姓',
`game_code` varchar(50) NOT NULL DEFAULT '' COMMENT '游戏编码',
`is_check_mobile` tinyint(1) NOT NULL DEFAULT '0' COMMENT '手机是否已验证',
`ifa` varchar(50) NOT NULL DEFAULT '' COMMENT 'ifa',
`mac` varchar(20) NOT NULL DEFAULT '' COMMENT 'mac',
`udid` varchar(50) NOT NULL DEFAULT '' COMMENT 'udid',
`app_bg` varchar(100) NOT NULL DEFAULT '' COMMENT 'APP+背景图片',
`user_desc` varchar(100) NOT NULL DEFAULT '' COMMENT '一句话简单介绍自己',
`userVersion` int(11) DEFAULT '1',
`code` varchar(50) DEFAULT NULL COMMENT '推广码',
`bandtime` int(11) NOT NULL DEFAULT '0' COMMENT '绑定手机的时间',
`ismobile` int(11) NOT NULL DEFAULT '0' COMMENT '是否为手机注册,0否',
`twoConfirm` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否开启二次验证',
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `reg_time` (`reg_time`),
KEY `email` (`email`),
KEY `login_time` (`login_time`),
KEY `mac` (`mac`),
KEY `udid` (`udid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户详细信息表'
1 row in set (0.00 sec)





root@localhost 18:51: [dbe8je6i4c3gjd50]> explain extended select real_name,credits from users_slave_09 where user_id in (select * from (select * from tmp_card where user_id=1009236) a);
+----+--------------------+----------------+--------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+----------------+--------+---------------+---------+---------+------+--------+----------+--------------------------+
| 1 | PRIMARY | users_slave_09 | ALL | NULL | NULL | NULL | NULL | 885118 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 3 | DERIVED | tmp_card | ref | user_id | user_id | 9 | | 1 | 100.00 | Using where; Using index |
+----+--------------------+----------------+--------+---------------+---------+---------+------+--------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

root@localhost 18:52: [dbe8je6i4c3gjd50]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `dbe8je6i4c3gjd50`.`users_slave_09`.`real_name` AS `real_name`,`dbe8je6i4c3gjd50`.`users_slave_09`.`credits` AS `credits` from `dbe8je6i4c3gjd50`.`users_slave_09` where <in_optimizer>(`dbe8je6i4c3gjd50`.`users_slave_09`.`user_id`,<exists>(select 1 from dual where (<cache>(`dbe8je6i4c3gjd50`.`users_slave_09`.`user_id`) = '1009236'))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost 18:52: [dbe8je6i4c3gjd50]> explain extended select real_name,credits from users_slave_09 where user_id in (select * from tmp_card where user_id=1009236);
+----+--------------------+----------------+------+---------------+---------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+----------------+------+---------------+---------+---------+-------+--------+----------+--------------------------+
| 1 | PRIMARY | users_slave_09 | ALL | NULL | NULL | NULL | NULL | 885118 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | tmp_card | ref | user_id | user_id | 9 | const | 1 | 100.00 | Using where; Using index |
+----+--------------------+----------------+------+---------------+---------+---------+-------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

root@localhost 18:52: [dbe8je6i4c3gjd50]> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `dbe8je6i4c3gjd50`.`users_slave_09`.`real_name` AS `real_name`,`dbe8je6i4c3gjd50`.`users_slave_09`.`credits` AS `credits` from `dbe8je6i4c3gjd50`.`users_slave_09` where <in_optimizer>(`dbe8je6i4c3gjd50`.`users_slave_09`.`user_id`,<exists>(select 1 from `dbe8je6i4c3gjd50`.`tmp_card` where ((`dbe8je6i4c3gjd50`.`tmp_card`.`user_id` = 1009236) and (<cache>(`dbe8je6i4c3gjd50`.`users_slave_09`.`user_id`) = 1009236)))) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



root@localhost 18:40: [dbe8je6i4c3gjd50]> show profiles
-> ;
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------+
| 1 | 52.17114525 | select real_name,credits from users_slave_09 where user_id in (select * from (select * from tmp_card where user_id=1009236) a) |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost 18:40: [dbe8je6i4c3gjd50]> show profile cpu,block io for query 1;
+--------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+----------+------------+--------------+---------------+
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 8 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000036 | 0.001000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000035 | 0.000000 | 0.001000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.001000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000036 | 0.000000 | 0.001000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000112 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000042 | 0.000999 | 0.000000 | 0 | 0 |
| freeing items | 0.000061 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000133 | 0.000000 | 0.000000 | 0 | 16 |
| cleaning up | 0.000046 | 0.000000 | 0.000999 | 0 | 0 |
+--------------------+----------+----------+------------+--------------+---------------+
100 rows in set (0.00 sec)




root@localhost 18:44: [dbe8je6i4c3gjd50]> show profiles;
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------+
| 1 | 52.17114525 | select real_name,credits from users_slave_09 where user_id in (select * from (select * from tmp_card where user_id=1009236) a) |
| 2 | 52.02670925 | select real_name,credits from users_slave_09 where user_id in (select * from (select * from tmp_card where user_id=1009236) a) |
| 3 | 55.58310100 | select real_name,credits from users_slave_09 where user_id in (select * from tmp_card where user_id=1009236) |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

root@localhost 18:44: [dbe8je6i4c3gjd50]> show profile cpu,block io for query 3;
+--------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+----------+------------+--------------+---------------+
| Sending data | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000036 | 0.000000 | 0.001000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000036 | 0.000000 | 0.001000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000036 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000035 | 0.000000 | 0.001000 | 0 | 0 |
| Sending data | 0.000036 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000040 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000034 | 0.001000 | 0.000000 | 0 | 0 |
| query end | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000036 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000057 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000089 | 0.000000 | 0.000000 | 0 | 8 |
| cleaning up | 0.000045 | 0.000000 | 0.001000 | 0 | 0 |
+--------------------+----------+----------+------------+--------------+---------------+
100 rows in set (0.00 sec)
已邀请:

Fan - 菜的抠脚

赞同来自:

看不清可以看附件

qidan3500

赞同来自:

首先你这个版本是mysql5.5 如果是最新的mysql5.7版本 结果会一样的
现在分析为什么快 从执行计划中可以分析 快的那个版本 把结果缓存在内存了
而慢的那个没有
可以设置下
set session tmp_table_size=1;
set session max_heap_table_size=1;
这两个值之后 运行下那个快的版本 速度会比之前变慢
 

要回复问题请先登录注册