这样的大批量数据查询怎么实现

数据库mysql，版本6.0

CREATE TABLE `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lists_id` int(11) DEFAULT '0' COMMENT '列表ID',
  `name` varchar(255) DEFAULT '' COMMENT '姓名',
  `mobile` varchar(50) DEFAULT '' COMMENT '手机',
  `phone` varchar(50) DEFAULT '' COMMENT '电话号码',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=50167 DEFAULT CHARSET=utf8 COMMENT='联系人';


INSERT INTO `contacts` VALUES ('1',  '1', '客户1', '13511111111', '80001111' );
INSERT INTO `contacts` VALUES ('2',  '2', '客户2', '13511111111', '80022222' );
INSERT INTO `contacts` VALUES ('3',  '1', '客户3', '13511111113', '80001113' );
INSERT INTO `contacts` VALUES ('4',  '1', '客户4', '13511111114', '80001114' );
INSERT INTO `contacts` VALUES ('5',  '2', '客户5', '13511111115', '80001113' );
............

要注意的是，表里同一个lists_id的数据会有几万条
1、现在要得到lists_id=1的数据，但如果其中的mobile值在lists_id=2的mobile中有相同的，则不能放入数据集中
即得到
id lists_id name  mobile          phone
3  1        客户3 13511111113    80011113
4  1        客户4 13511111114    80011114

2、再复杂点，同样得到lists_id=1的数据，但如果其中的mobile值在lists_id=2的mobile中有相同的，或者phone值在lists_id=2的phone中有相同的,不能放入数据集中,即得到
id lists_id name  mobile          phone
4  1        客户4 13511111114    80011114


3、还更复杂的是
CREATE TABLE `customers_group` (
  `group_id` int(11) DEFAULT NULL COMMENT '组ID',
  `customers_id` int(11) DEFAULT NULL COMMENT '客户ID，关联customers表id'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='联系人组关系';

INSERT INTO `customers_group` VALUES ('1', '1');
INSERT INTO `customers_group` VALUES ('1', '2');
INSERT INTO `customers_group` VALUES ('2', '2');

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) DEFAULT NULL COMMENT '联系人姓名',
  `company` varchar(100) DEFAULT NULL COMMENT '公司名',
  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='通讯录联系人';

INSERT INTO `customers` VALUES ('1', '张三', '公司1', '13511111114', '80001116');
INSERT INTO `customers` VALUES ('2', '立四', '公司2', '13511111117', '80001117' );

要注意的是，customers里属于同一个group的数据会有几万条
同样得到contacts表lists_id=1的数据，但除了做上述2的数据过滤外，还再考虑过滤customers中的数据。过滤掉customers_group中group_id为1关联customers的数据，过滤方法同。
过滤后，则一个数据都没有
id lists_id name  mobile          phone


谢谢

21 个解决方案

#1

1
SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts`  WHERE lists_id=2 AND a.mobile=mobile);
2
SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts`  WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone));

#2

3
简要说明一下结果是怎样得出的

#3

注意建立索引
lists_id、mobile、phone

#4

谢谢。
刚才忘了说了，其实 最终的目的是要 把符合条件的数据 导入到 另一个表userlist中
CREATE TABLE `userlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '' COMMENT '姓名',
  `mobile` varchar(50) DEFAULT '' COMMENT '手机',
  `phone` varchar(50) DEFAULT '' COMMENT '电话号码',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=50167 DEFAULT CHARSET=utf8 COMMENT='最终联系人';

#5

insert into `userlist`
select id,name,mobile,phone FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone));

#6

另外，3是这么得到的，数据经过2处理后，得到
id lists_id name mobile phone
4 1 客户4 13511111114 80011114

customers_group是客户和组的关系表，customers是客户信息表。
现在要过滤掉组为1的客户，按照下面数据，组为1的客户为customers中id为1和2的数据，而id为1的客户，mobile为 13511111114，所以要把contacts表中mobile为13511111114的数据过滤掉，这样就空数据集了。
INSERT INTO `customers_group` VALUES ('1', '1');
INSERT INTO `customers_group` VALUES ('1', '2');
INSERT INTO `customers_group` VALUES ('2', '2');

INSERT INTO `customers` VALUES ('1', '张三', '公司1', '13511111114', '80001116');
INSERT INTO `customers` VALUES ('2', '立四', '公司2', '13511111117', '80001117' );

#7

嗯，3的过滤同于2的过滤方法，在问题描述里忘写了 

#8

3
SELECT a.id,a.`name`,a.mobile,a.phone FROM `contacts` a 
LEFT JOIN
(SELECT * FROM `customers_group` a INNER JOIN `customers` b ON a.customers_id=b.id
WHERE a.customers_id=1) c
ON a.mobile=c.mobile
WHERE a.lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone)) AND  c.id IS NULL

#9

引用 3 楼 wwwwb 的回复:

注意建立索引
lists_id、mobile、phone

请问是对这3个字段分别建立所引；  还是3个放一起组合索引。

刚都试了下，
SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND a.mobile=mobile);可以很快得到结果

SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone));则mysql处于没响应状态

#10

1.
SELECT id,lists_id,name,mobile,phone FROM `contacts` where lists_id=1 and
 mobile not in(
select mobile from contacts where lists_id=2
)
;

2.
SELECT id,lists_id,name,mobile,phone FROM `contacts` where lists_id=1 and
 mobile not in(
select mobile from contacts where lists_id=2
)
and
phone not in(
select phone from contacts where lists_id=2
)
;

#11

ists_id=1 和  lists_id=2 的测试数据有4万多条

#12

建立 复合索引

#13

3
SELECT id,lists_id,name,mobile,phone FROM `contacts` where lists_id=1 and
 mobile not in(
select mobile from contacts where lists_id=2
)
and
phone not in(
select phone from contacts where lists_id=2
)
and
mobile not in (SELECT mobile FROM `customers` where id in (select customers_id from customers_group where group_id=1))
and
phone not in (SELECT phone FROM `customers` where id in (select customers_id from customers_group where group_id=1))
;

#14

引用 12 楼 wwwwa 的回复:

建立 复合索引

谢谢。（lists_id、mobile、phone）复合索引后

但
SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND a.mobile=mobile);可以很快得到结果

SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone));则mysql处于没响应状态

#15

引用

SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND a.mobile=mobile);可以很快得到结果

SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone));则mysql处于没响应状态

show index from `contacts`;
explain SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND a.mobile=mobile)

explain SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone))

贴出来看一下。

#16

（lists_id、mobile、phone）这个索引对 WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone)没有实际作用。需要看你的数据分布进行调整。

#17

谢谢。

show index from `contacts`;
得到
contacts 0 PRIMARY 1 id A 81920 BTREE

contacts 1 lmp 1 lists_id A 2 YES BTREE

contacts 1 lmp 2 mobile A 40960 YES BTREE

contacts 1 lmp 3 phone A 81920 YES BTREE

#18

创建两个索引。
(lists_id,mobile)
(lists_id,phone)

或者直接创建 两个索引。你的lists_id 只有两种值。
(mobile)
(phone)

#19

引用 18 楼 acmain_chm 的回复:

创建两个索引。
(lists_id,mobile)
(lists_id,phone)

或者直接创建 两个索引。你的lists_id 只有两种值。
(mobile)
(phone)

这样建立索引后，还是不行，
SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND a.mobile=mobile);可以很快得到结果

SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone));则mysql处于没响应状态

#20

引用

SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND (a.mobile=mobile OR a.phone=phone));则mysql处于没响应状态

改成如下。
SELECT * FROM `contacts` a WHERE lists_id=1 AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND a.mobile=mobile)
NOT EXISTS(
SELECT 1 FROM `contacts` WHERE lists_id=2 AND a.phone=phone)

#21

谢谢ACMAIN_CHM。 按照你20楼的写法，可以了。

不过还想问下，其实实际情况，lists_id不只两种值,也是上万甚至更多,而且选择数据很有可能从多个lists_id中选择，即可能出现：
SELECT * FROM `contacts` a WHERE (lists_id=1 or lists_id=3) AND NOT EXISTS(
SELECT 1 FROM `contacts` WHERE (lists_id=2 or lists_id=4) AND a.mobile=mobile) and
NOT EXISTS(
SELECT 1 FROM `contacts` WHERE (lists_id=2 or lists_id=4) AND a.phone=phone)

按照上面的写法，可不可以呢？