CREATE TABLE `table1` (
`id` mediumint(10) unsigned NOT NULL auto_increment,
`resumeid` varchar(10) NOT NULL,
`flag` varchar(10) NOT NULL default 'U',
PRIMARY KEY (`id`)
);INSERT INTO `table1` (`id`, `resumeid`, `flag`) VALUES
(1, '1', 'U'),
(2, '2', 'D'),
(3, '3', 'U'),
(4, '2', 'D');CREATE TABLE `table2` (
`id` mediumint(10) unsigned NOT NULL auto_increment,
`resumeid` varchar(10) NOT NULL,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
);INSERT INTO `table2` (`id`, `resumeid`, `name`) VALUES
(1, '1', 'tom'),
(2, '2', 'jim'),
(3, '3', 'lily'),
(4, '4', 'tony'),
(5, '6', 'yy'),
(6, '5', 'tt');说明:
2表id都是自动增长, resumeid在table2中惟一,在table1中可以重复,2表通过resumeid关联找出存在于table2中 但是不存在于table1中的name数据
sql语句1:
SELECT table2.resumeid,table2.name
FROM table2
WHERE NOT
EXISTS (
SELECT DISTINCT (
table1.resumeid
)
FROM table1
WHERE table1.resumeid = table2.resumeid
)
sql语句2:
SELECT table2.resumeid, table2.name
FROM table2
LEFT JOIN table1
USING ( resumeid )
WHERE table1.resumeid IS NULL问题:
当2表数据量比较大时,比如:5万左右,发现2 sql查询非常缓慢,(当然实际表字段比这2演示表字段多),
请问各位 ,有没有好的解决方法呢??
FROM table2
LEFT JOIN table1
on table1.id=table2.resumeid
WHERE table1.resumeid IS NULL在resumeid上建立索引,是唯一还是有重复
or
alter table test add UNIQUE dd1 (resumeid)
在table1.resumeid 上加上索引。然后可以用sql语句1: -- 删除distinct
SELECT table2.resumeid,table2.name
FROM table2
WHERE NOT EXISTS (
SELECT table1.resumeid
FROM table1
WHERE table1.resumeid = table2.resumeid
)或者
sql语句2:
SELECT table2.resumeid, table2.name
FROM table2 LEFT JOIN table1 USING ( resumeid )
WHERE table1.resumeid IS NULL
2表id都是自动增长, resumeid在table2中惟一,在table1中可以重复,2表通过resumeid关联
这样id在查询中没什么作用。 在你两表的关联字段上加上索引即可。唯一索引的效率会比普通索引效率高。你的两种SQL语句效率理论上应该差不多。具体你可以测试一下。
alter table t1 add INDEX dd (resumeid)
2.从估计来分析,不相等的属于少数。
SELECT table2.resumeid,table2.name
FROM table2,table1
WHERE table1.resumeid <> table2.resumeid
2.从估计来分析,不相等的属于少数。
SELECT table2.resumeid,table2.name
FROM table2,table1
WHERE table1.resumeid <> table2.resumeid
[code=BatchFile]mysql> explain SELECT table2.resumeid,table2.name
-> FROM table2
-> WHERE NOT EXISTS (
-> SELECT table1.resumeid
-> FROM table1
-> WHERE table1.resumeid = table2.resumeid limit 1
-> );
+----+--------------------+--------+------+---------------------+---------------------+---------+----------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------+------+---------------------+---------------------+---------+----------------------+-------+-------------+
| 1 | PRIMARY | table2 | ALL | NULL | NULL | NULL | NULL | 50000 | Using where |
| 2 | DEPENDENT SUBQUERY | table1 | ref | idx_table1_resumeid | idx_table1_resumeid | 12 | test.table2.resumeid | 1 | Using index |
+----+--------------------+--------+------+---------------------+---------------------+---------+----------------------+-------+-------------+
2 rows in set (0.00 sec)mysql>
mysql> explain SELECT table2.resumeid, table2.name
-> FROM table2 LEFT JOIN table1 USING ( resumeid )
-> WHERE table1.resumeid IS NULL;
+----+-------------+--------+------+---------------------+---------------------+---------+----------------------+-------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------+---------------------+---------+----------------------+-------+--------------------------------------+
| 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 50000 | |
| 1 | SIMPLE | table1 | ref | idx_table1_resumeid | idx_table1_resumeid | 12 | test.table2.resumeid | 1 | Using where; Using index; Not exists |
+----+-------------+--------+------+---------------------+---------------------+---------+----------------------+-------+--------------------------------------+2 rows in set (0.00 sec)mysql>[/code]
特别感谢ACMAIN_CHM,这么晚了还帮我测试了下
用了你们的方法确实查询快了很多
至于说ACMAIN_CHM说的table2中resumeid 不需要加索引,就我提问来说 看测试结果 是不需要 但是我实际应用还有其他表关联 所以还是要加的
呵呵 再次谢谢ACMAIN_CHM