mysql> show create table tab1\G
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
mysql> show create table tab2\G
*************************** 1. row ***************************
Table: tab2
Create Table: CREATE TABLE `tab2` (
`id` int(11) NOT NULL,
`tab1_id` int(11) DEFAULT NULL,
`code` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_tab1_id` (`tab1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from tab1;
+-----+-------+
| id| name|
+-----+-------+
| 1 | z |
|10 | a |
|32 | kope|
|99 | leo |
| 110 | dummy |
+-----+-------+
5 rows in set (0.03 sec)
mysql> select * from tab2;
+----+---------+------+
| id | tab1_id | code |
+----+---------+------+
|1 | 1 | 010|
|2 | 1 | 020|
|3 |10 | 0571 |
|4 |13 | 0572 |
|5 | 110 | 0576 |
+----+---------+------+
5 rows in set (0.00 sec)
mysql> select tab1.* from tab1 left join tab2 on tab1.id=tab2.tab1_id where tab2.tab1_id is null;
+----+------+
| id | name |
+----+------+
| 32 | kope |
| 99 | leo|
+----+------+
2 rows in set (0.00 sec)
mysql> select * from tab1 where not exists (select 1 from tab2 where tab2.tab1_id=tab1.id);
+----+------+
| id | name |
+----+------+
| 32 | kope |
| 99 | leo|
+----+------+
2 rows in set (0.01 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query
|
+----------+------------+-------------------------------------------------------------------------------------------+
|1 | 0.00083625 | select tab1.* from tab1 left join tab2 on tab1.id=tab2.tab1_id where tab2.tab1_id is null |
|2 | 0.01058275 | select * from tab1 where not exists (select 1 from tab2 where tab2.tab1_id=tab1.id) |
+----------+------------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status
| Duration |
+----------------------+----------+
| starting
| 0.000132 |
| checking permissions | 0.000035 |
| checking permissions | 0.000016 |
| Opening tables | 0.000066 |
| System lock
| 0.000027 |
| init
| 0.000050 |
| optimizing | 0.000028 |
| statistics | 0.000045 |
| preparing
| 0.000032 |
| executing
| 0.000011 |
| Sending data | 0.000193 |left join 只有一个
| end
| 0.000022 |
| query end
| 0.000020 |
| closing tables | 0.000087 |
| freeing items| 0.000040 |
| logging slow query | 0.000010 |
| cleaning up
| 0.000023 |
+----------------------+----------+
17 rows in set (0.00 sec)
mysql> show profile for query 2;
+----------------------+----------+
| Status
| Duration |
+----------------------+----------+
| starting
| 0.000176 |
| checking permissions | 0.000015 |
| checking permissions | 0.000015 |
| Opening tables | 0.009610 |
| System lock
| 0.000046 |
| init
| 0.000062 |
| optimizing | 0.000019 |
| statistics | 0.000028 |
| preparing
| 0.000023 |
| executing
| 0.000010 |
| Sending data | 0.000058 |
| optimizing | 0.000014 |
| statistics | 0.000028 |
| preparing
| 0.000017 |
| executing
| 0.000009 |
| Sending data | 0.000059 |
| executing
| 0.000014 |
| Sending data | 0.000022 |
| executing
| 0.000088 |
| Sending data | 0.000030 |
| executing
| 0.000009 |not exists 很多 是不是这个就是子查询效率差的地方？
| Sending data | 0.000022 |
| executing
| 0.000009 |
| Sending data | 0.000045 |
| end
| 0.000046 |
| query end
| 0.000016 |
| closing tables | 0.000022 |
| freeing items| 0.000052 |
| logging slow query | 0.000010 |
| cleaning up
| 0.000013 |
+----------------------+----------+
30 rows in set (0.00 sec)复制代码 |
