我有以下数据:

customer
+---------------------------------------------------------------------------------------------+
| id    | email             | firstname     | lastname      | no_pub    | customer_address    |
|---------------------------------------------------------------------------------------------|
| 1     | martin@domain.com | Martin        | Scorcese      | 0         | 4                   |
| 2     | robert@domain.com | Robert        | De Niro       | 0         | 7                   |
| 3     | bruce@domain;com  | Bruce         | Willis        | 0         | 10                  |
+---------------------------------------------------------------------------------------------+

address
+------------------------------------------+
| id    | city             | zipcode       |
|------------------------------------------|
| 4     | Paris            | 75001         |
| 7     | Marseille        | 13000         |
| 10    | Bordeaux         | 33000         |
+------------------------------------------+

sf_geo_cities
+------------------------------------------+
| id    | region_id    | zipcode           |
|------------------------------------------|
| 1     | 1            | 75001             |
| 2     | 2            | 13000             |
| 2     | 3            | 33000             |
+------------------------------------------+

sf_geo_regions
+------------------------------------------+
| id    | name             | zipcode       |
|------------------------------------------|
| 1     | Ile-de-France    | 75001         |
| 2     | Cote d'Azur      | 13000         |
| 2     | Gironde          | 33000         |
+------------------------------------------+

这是一个示例，这些表中显然有更多数据(客户 300k+，地址 400k+) 最后，我想获得以下数据:

+---------------------------------------------------------------------------------------------+
| id    | email             | firstname     | lastname      | city          | region          |
|---------------------------------------------------------------------------------------------|
| 1     | martin@domain.com | Martin        | Scorcese      | Paris         | Ile-de-France   |
| 2     | robert@domain.com | Robert        | De Niro       | Marseille     | Cote d'Azur     |
| 3     | bruce@domain;com  | Bruce         | Willis        | NULL          | NULL            |
+---------------------------------------------------------------------------------------------+

我尝试了以下 SQL 查询:

SELECT c.id, c.email, c.firstname, c.lastname, gc.name, gr.name
FROM customer c
LEFT JOIN address ad ON ad.id = c.customer_address
JOIN sf_geo_cities gc ON gc.zipcode = ad.zipcode
JOIN sf_geo_regions gr ON gr.id = gc.region_id
WHERE no_pub = 0

但这需要很长时间，我必须关闭 mysql 服务。联接乘以行可能存在问题。

是否有一个简单的查询来获取我期望的数据？

更新#1: 这是在下面的评论中询问的解释:

id  select_type     table   type    possible_keys                                           key         key_len     ref                         rows    Extra   
1   SIMPLE          c       ref     PRIMARY,UNIQ_81398E097D3656A4,UNIQ_81398E09E7927C7...   no_pub      1           const                       136220  Using where
1   SIMPLE          a       eq_ref  PRIMARY                                                 PRIMARY     8           evotest.c.account           1       Using index
1   SIMPLE          ad      eq_ref  PRIMARY                                                 PRIMARY     8           evotest.c.customer_address  1       NULL
1   SIMPLE          gc      ALL     zipcode                                                 NULL        NULL        NULL                        38194   Range checked for each record (index map: 0x8)
1   SIMPLE          gr      eq_ref  PRIMARY                                                 PRIMARY     4           evotest.gc.region_id        1       NULL

更新 #2: 数据库示例 这是我的数据库，样本数据最少。

--
-- Structure de la table `account`
--

CREATE TABLE IF NOT EXISTS `account` (
`id` bigint(20) unsigned NOT NULL,
  `identifier` varchar(255) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=315688 ;

--
-- Structure de la table `customer`
--

CREATE TABLE IF NOT EXISTS `customer` (
`id` bigint(20) unsigned NOT NULL,
  `account` bigint(20) unsigned NOT NULL,
  `customer_address` bigint(20) unsigned DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `lastname` varchar(255) COLLATE utf8_bin NOT NULL,
  `firstname` varchar(255) COLLATE utf8_bin NOT NULL,
  `no_pub` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=315224 ;

--
-- Structure de la table `address`
--

CREATE TABLE IF NOT EXISTS `address` (
`id` bigint(20) unsigned NOT NULL,
  `city` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `street` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `complement` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `zipcode` varchar(16) COLLATE utf8_bin DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `cedex` tinyint(1) NOT NULL DEFAULT '0',
  `abroad` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=442743 ;

--
-- Structure de la table `sf_geo_cities`
--

CREATE TABLE IF NOT EXISTS `sf_geo_cities` (
`id` int(11) NOT NULL,
  `region_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `zipcode` varchar(5) NOT NULL,
  `insee_code` int(11) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=38106 ;

CREATE TABLE IF NOT EXISTS `sf_geo_regions` (
`id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;

CREATE TABLE IF NOT EXISTS `sf_geo_countries` (
`id` int(11) NOT NULL,
  `code` int(11) NOT NULL,
  `alpha2` varchar(2) NOT NULL,
  `alpha3` varchar(3) NOT NULL,
  `name_en` varchar(45) NOT NULL,
  `name_fr` varchar(45) NOT NULL,
  `is_default` tinyint(1) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=242 ;

CREATE TABLE IF NOT EXISTS `sf_user_data` (
`id` int(11) NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `main_activity_type_id` int(11) DEFAULT NULL,
  `main_activity_id` int(11) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=19001 ;


CREATE TABLE IF NOT EXISTS `sf_activity_types` (
`id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `identifier` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Ne doit pas être modifié, il s agit de la clé dans le tableau de constantes $constants stockant les taux dans l entité Calculator'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `sf_activities` (
`id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `asks_for_custom` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;

--
-- Index pour les tables exportées
--

ALTER TABLE `account`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `identifier_UNIQUE` (`identifier`);

ALTER TABLE `customer`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `UNIQ_81398E097D3656A4` (`account`), ADD UNIQUE KEY `UNIQ_81398E09E7927C74` (`email`), ADD UNIQUE KEY `UNIQ_81398E091193CB3F` (`customer_address`), ADD KEY `no_pub` (`no_pub`);

ALTER TABLE `address`
 ADD PRIMARY KEY (`id`), ADD KEY `IDX_D4E6F81F92F3E70` (`country_id`), ADD KEY `zipcode` (`zipcode`); 

ALTER TABLE `sf_geo_cities`
 ADD PRIMARY KEY (`id`), ADD KEY `IDX_B56556A198260155` (`region_id`), ADD KEY `zipcode` (`zipcode`);

ALTER TABLE `sf_geo_regions`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `sf_geo_countries`
 ADD PRIMARY KEY (`id`), ADD KEY `IDX_F86325E277153098` (`code`), ADD KEY `IDX_F86325E2B762D672` (`alpha2`), ADD KEY `IDX_F86325E2C065E6E4` (`alpha3`);

ALTER TABLE `sf_user_data`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `UNIQ_E904BFD1A76ED395` (`user_id`), ADD KEY `IDX_E904BFD12E864BE8` (`main_activity_type_id`), ADD KEY `IDX_E904BFD15543A800` (`main_activity_id`);

ALTER TABLE `sf_activity_types`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `sf_activities`
 ADD PRIMARY KEY (`id`); 

--
-- Contraintes pour les tables exportées
--

ALTER TABLE `customer`
ADD CONSTRAINT `FK_81398E091193CB3F` FOREIGN KEY (`customer_address`) REFERENCES `address` (`id`),
ADD CONSTRAINT `FK_81398E097D3656A4` FOREIGN KEY (`account`) REFERENCES `account` (`id`);

ALTER TABLE `address`
ADD CONSTRAINT `FK_D4E6F81F92F3E70` FOREIGN KEY (`country_id`) REFERENCES `sf_geo_countries` (`id`);

ALTER TABLE `sf_geo_cities`
ADD CONSTRAINT `FK_B56556A198260155` FOREIGN KEY (`region_id`) REFERENCES `sf_geo_regions` (`id`);

ALTER TABLE `sf_user_data`
ADD CONSTRAINT `FK_E904BFD12E864BE8` FOREIGN KEY (`main_activity_type_id`) REFERENCES `sf_activity_types` (`id`),
ADD CONSTRAINT `FK_E904BFD15543A800` FOREIGN KEY (`main_activity_id`) REFERENCES `sf_activities` (`id`),
ADD CONSTRAINT `FK_E904BFD1A76ED395` FOREIGN KEY (`user_id`) REFERENCES `account` (`id`);


INSERT INTO `account` (`id`, `identifier`) VALUES ('1', 'martin@domain.com'), ('2', 'robert@domain.com'), ('3', 'bruce@domain.com');

INSERT INTO `sf_geo_countries` (`id`, `code`, `alpha2`, `alpha3`, `name_en`, `name_fr`, `is_default`) VALUES ('1', '1', 'FR', 'FRA', 'France', 'France', '1');

INSERT INTO `address` (`id`, `city`, `street`, `complement`, `zipcode`, `country_id`, `cedex`, `abroad`) VALUES ('1', 'Paris', '1 rue de Paris', NULL, '75001', '1', '0', '0'), ('2', 'Marseille', '1 rue de Marseille', NULL, '13000', '1', '0', '0');

INSERT INTO `customer` (`id`, `account`, `customer_address`, `email`, `lastname`, `firstname`, `no_pub`) VALUES ('1', '1', '1', 'martin@domain.com', 'Scorcese', 'Martin', '0'), ('2', '2', '2', 'robert@domain.com', 'De Niro', 'Robert', '0'), ('3', '3', NULL, 'bruce@domain.com', 'Willis', 'Bruce', '0');

INSERT INTO `sf_activities` (`id`, `name`, `asks_for_custom`) VALUES ('1', 'Activity #1', '0'), ('2', 'Activity #2', '0');

INSERT INTO `sf_activity_types` (`id`, `name`, `identifier`) VALUES ('1', 'Activity Type #1', 'activity-type-1'), ('2', 'Activity Type #2', 'activity-type-2');

INSERT INTO `sf_geo_regions` (`id`, `name`) VALUES ('1', 'Ile-de-France'), ('2', 'Cote d''Azur');

INSERT INTO `sf_geo_cities` (`id`, `region_id`, `name`, `slug`, `zipcode`, `insee_code`, `latitude`, `longitude`) VALUES ('1', '1', 'Paris', 'paris', '75001', '1', '0', '0'), ('2', '2', 'Marseille', 'marseille', '13000', '2', '0', '0');

INSERT INTO `sf_user_data` (`id`, `user_id`, `main_activity_type_id`, `main_activity_id`) VALUES ('1', '1', '1', '1'), ('2', '3', '2', '2');

使用最少的数据，我可以运行以下查询，返回我想要的一切。但是这个查询在我拥有 300k+ 客户和 400k+ 地址的真实数据库中运行了很长时间

SELECT c.id, c.email, c.firstname, c.lastname, acttypes.name AS activity_type, act.name AS activity, gc.name AS city, gr.name AS region
FROM customer c
JOIN account a ON a.id = c.account
LEFT JOIN sf_user_data ud ON ud.user_id = a.id
LEFT JOIN sf_activity_types acttypes ON acttypes.id = ud.main_activity_type_id
LEFT JOIN sf_activities act ON act.id = ud.main_activity_id
LEFT JOIN address ad ON ad.id = c.customer_address
LEFT JOIN sf_geo_cities gc ON gc.zipcode = ad.zipcode
LEFT JOIN sf_geo_regions gr ON gr.id = gc.region_id
WHERE no_pub = 0

更新#3:跟踪加入问题

这个查询运行得很快:

SELECT 
    c.id 
    ,c.email 
    ,c.firstname 
    ,c.lastname 
    ,acttypes.name AS activity_type 
    ,act.name AS activity 
    ,ad.zipcode AS address_zipcode
--    ,gc.name AS city 
--    ,gr.name AS region    
FROM  
    customer c 
JOIN account a ON a.id = c.account
    LEFT JOIN sf_user_data ud ON ud.user_id = a.id
    LEFT JOIN sf_activity_types acttypes ON acttypes.id = ud.main_activity_type_id
    LEFT JOIN sf_activities act ON act.id = ud.main_activity_id
    LEFT JOIN address ad ON ad.id = c.customer_address
--    LEFT JOIN sf_geo_cities gc ON gc.zipcode = ad.zipcode
--    LEFT JOIN sf_geo_regions gr ON gr.id = gc.region_id
WHERE 
    no_pub = 0 

这个查询运行得也很快:

SELECT 
    c.id 
    ,c.email 
    ,c.firstname 
    ,c.lastname 
--    ,acttypes.name AS activity_type 
--    ,act.name AS activity 
    ,ad.zipcode AS address_zipcode
    ,gc.name AS city 
--    ,gr.name AS region    
FROM  
    customer c 
JOIN account a ON a.id = c.account
--    LEFT JOIN sf_user_data ud ON ud.user_id = a.id
--    LEFT JOIN sf_activity_types acttypes ON acttypes.id = ud.main_activity_type_id
--    LEFT JOIN sf_activities act ON act.id = ud.main_activity_id
    LEFT JOIN address ad ON ad.id = c.customer_address
    LEFT JOIN sf_geo_cities gc ON gc.zipcode = ad.zipcode
--    LEFT JOIN sf_geo_regions gr ON gr.id = gc.region_id
WHERE 
    no_pub = 0 

这个查询永远运行:

SELECT 
    c.id 
    ,c.email 
    ,c.firstname 
    ,c.lastname 
    ,acttypes.name AS activity_type 
    ,act.name AS activity 
    ,ad.zipcode AS address_zipcode
    ,gc.name AS city 
--    ,gr.name AS region    
FROM  
    customer c 
JOIN account a ON a.id = c.account
    LEFT JOIN sf_user_data ud ON ud.user_id = a.id
    LEFT JOIN sf_activity_types acttypes ON acttypes.id = ud.main_activity_type_id
    LEFT JOIN sf_activities act ON act.id = ud.main_activity_id
    LEFT JOIN address ad ON ad.id = c.customer_address
    LEFT JOIN sf_geo_cities gc ON gc.zipcode = ad.zipcode
--    LEFT JOIN sf_geo_regions gr ON gr.id = gc.region_id
WHERE 
    no_pub = 0

事件上的联接没问题，地址上的联接没问题，但是尝试将所有这些联接一起使用是行不通的。

最佳答案

正如我在评论中提到的，非常重要的一点是返回了多少条记录。一个语句中的记录太多可能会导致问题，如果您对结果进行分页，一次选择 n 条记录，可能会更好。

要对查询进行故障排除，请逐个连接地构建它，以尝试确定性能何时跌落悬崖。开始于:

SELECT 
    c.id 
    ,c.email 
    ,c.firstname 
    ,c.lastname 
--    ,acttypes.name AS activity_type 
--    ,act.name AS activity 
--    ,gc.name AS city 
--    ,gr.name AS region    
FROM  
    customer c 
--    JOIN account a ON a.id = c.account
--    LEFT JOIN sf_user_data ud ON ud.user_id = a.id
--    LEFT JOIN sf_activity_types acttypes ON acttypes.id = ud.main_activity_type_id
--    LEFT JOIN sf_activities act ON act.id = ud.main_activity_id
--    LEFT JOIN address ad ON ad.id = c.customer_address
--    LEFT JOIN sf_geo_cities gc ON gc.zipcode = ad.zipcode
--    LEFT JOIN sf_geo_regions gr ON gr.id = gc.region_id
WHERE 
    no_pub = 0

然后一次取消对一个联接的注释，直到您的查询执行得非常慢。分析该查询并查看是否可以使用索引对其进行改进。然后继续取消对联接的注释，直到您获得完整的查询。

关于mysql - 无法使连接正常工作，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31673960/