MySQL 5.7 NOT EXISTS用法介紹

feelpurple發表於2016-08-20
MySql
在MySQL中沒有MINUS語句，要想實現MINUS語句，可以使用NOT EXISTS語句。

檢視測試表中的資料
mysql> select * from person;
+----------------+-----------------+-----------------+
| MSISDN         | IMEI            | IMSI            |
+----------------+-----------------+-----------------+
| +3301000000000 | 129980000000000 | 208011000000000 |
| +3301000000001 | 129980000000100 | 208011000000001 |
| +3301000000002 | 129980000000200 | 208011000000002 |
| +3301000000003 | 129980000000300 | 208011000000003 |
| +3301000000004 | 129980000000400 | 208011000000004 |
| +3301000000005 | 129980000000500 | 208011000000005 |
| +3301000000006 | 129980000000600 | 208011000000006 |
| +3301000000007 | 129980000000700 | 208011000000007 |
+----------------+-----------------+-----------------+
8 rows in set (0.00 sec)
mysql> select * from card;
+-----------------+----------------+-----------------+
| IMSI            | MSISDN         | IMEI            |
+-----------------+----------------+-----------------+
| 208011000000000 | +3301000000000 | 129980000000000 |
| 208011000000001 | +3301000000001 | 129980000000100 |
| 208011000000002 | +3301000000002 | 129980000000200 |
| 208011000000003 | +3301000000003 | 129980000000300 |
| 208011000000004 | +3301000000004 | 129980000000400 |
| 208011000000005 | +3301000000005 | 129980000000500 |
| 208011000000006 | +3301000000006 | 129980000000600 |
| 208011000000007 | +3301000000007 | 129980000000700 |
| 208011000000008 | +3301000000008 | 129980000000800 |
| 208011000000009 | +3301000000009 | 129980000000900 |
+-----------------+----------------+-----------------+
10 rows in set (0.00 sec)

查詢出在card中存在，但是在person中不存在的某一列MSISDN。

mysql> select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
+-----------------+----------------+-----------------+
| IMSI            | MSISDN         | IMEI            |
+-----------------+----------------+-----------------+
| 208011000000008 | +3301000000008 | 129980000000800 |
| 208011000000009 | +3301000000009 | 129980000000900 |
+-----------------+----------------+-----------------+
2 rows in set (0.00 sec)

檢視執行計劃
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

透過執行計劃，card表使用了全表掃描，person表使用了主鍵索引
mysql> show keys from card;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| card  |          0 | PRIMARY              |            1 | IMSI        | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
mysql> show keys from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person |          0 | PRIMARY  |            1 | MSISDN      | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

由於兩張表的主鍵不是相同列，建立索引進行測試，發現card表始終無法使用索引掃描
mysql> create index idx_card_msisdn on card(msisdn);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> create index idx_card_msisdn_imsi on card(msisdn,imsi);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> create index idx_card_imsi_msisdn on card(imsi,msisdn);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2123740/，如需轉載，請註明出處，否則將追究法律責任。

相關文章