MySQL 加入直接表或表的子字段

Question

第一个查询:

SELECT  
    u.id,
    u.first_name,
    u.last_name,
    u.tazkera_id,
    cu.relation_type,
    CASE csp.`shift`
        WHEN '1' THEN 'Morning'
        WHEN '2' THEN 'Afternoon'
        else 'Neither'
    END AS shift
FROM `course_user` as cu
LEFT JOIN `courses` as c ON c.id = cu.`course_id`
LEFT JOIN (
    select users.id,
           users.first_name,
           users.last_name,
           users.tazkera_id
           FROM `users`
) as u ON u.id = cu.`user_id`

left JOIN `course_schedule_prefs`   as csp ON csp.`user_id` = cu.`user_id`

Where cu.relation_type = 1 group by cu.`user_id`;

第二个查询:

SELECT  
    u.id,
    u.first_name,
    u.last_name,
    u.tazkera_id,
    cu.relation_type,
    CASE csp.`shift`
        WHEN '1' THEN 'Morning'
        WHEN '2' THEN 'Afternoon'
        else 'Neither'
    END AS shift
FROM `course_user` as cu
LEFT JOIN `courses` as c ON c.id = cu.`course_id`
LEFT JOIN users as u ON u.id = cu.`user_id`
left JOIN `course_schedule_prefs`   as csp ON csp.`user_id` = cu.`user_id`
Where cu.relation_type = 1 group by cu.`user_id`;

表格:

用户:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_password_reset` int(11) NOT NULL DEFAULT '0',
  `login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `login_date` datetime DEFAULT NULL,
  `last_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_login_date` datetime DEFAULT NULL,
  `is_email_address_verified` int(11) NOT NULL DEFAULT '0',
  `failed_login_attempts` int(11) DEFAULT NULL,
  `last_failed_login_date` datetime DEFAULT NULL,
  `tazkera_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `province_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `district_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `village` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `home_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `promote_unique_user_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `forgot_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_confirm_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `district_other` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `father_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `training_provider_id` int(10) unsigned DEFAULT NULL,
  `is_profile_completed` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT '1',
  `picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `small_picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_poll` bigint(20) DEFAULT NULL,
  `last_password_reset` date DEFAULT NULL,
  `presence_status` int(11) DEFAULT NULL,
  `auto_presence` int(11) NOT NULL DEFAULT '0',
  `last_activity` timestamp NULL DEFAULT NULL,
  `trash` tinyint(4) NOT NULL DEFAULT '0',
  `login_approval` tinyint(4) NOT NULL DEFAULT '0',
  `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
  `cover` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `small_cover` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `forum_reputation` int(11) NOT NULL DEFAULT '0',
  `forum_suspension_date` date DEFAULT NULL,
  `forum_status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_training_provider_id_foreign` (`training_provider_id`),
  CONSTRAINT `users_training_provider_id_foreign` FOREIGN KEY (`training_provider_id`) REFERENCES `training_providers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1000038 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

类(class)用户:

CREATE TABLE `course_user` (
  `course_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `relation_type` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  KEY `course_user_course_id_foreign` (`course_id`),
  KEY `course_user_user_id_foreign` (`user_id`),
  CONSTRAINT `course_user_course_id_foreign` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `course_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

类(class)；

CREATE TABLE `courses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `curriculum_id` int(10) unsigned DEFAULT NULL,
  `training_center_id` int(10) unsigned DEFAULT NULL,
  `training_coordinator_id` int(10) unsigned DEFAULT NULL,
  `focal_point_id` int(10) unsigned DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `shift` int(11) DEFAULT NULL,
  `stage_id` int(10) unsigned DEFAULT NULL,
  `stream_id` int(10) unsigned DEFAULT NULL,
  `unit_id` int(10) unsigned DEFAULT NULL,
  `master_trainer_id` int(10) unsigned DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `pre_test_generated` int(11) NOT NULL DEFAULT '0',
  `post_test_generated` int(11) NOT NULL DEFAULT '0',
  `conduct_days` int(11) DEFAULT NULL,
  `no` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `evaluation_available` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `courses_curriculum_id_foreign` (`curriculum_id`),
  KEY `courses_training_coordinator_id_foreign` (`training_coordinator_id`),
  KEY `courses_focal_point_id_foreign` (`focal_point_id`),
  KEY `courses_stage_id_foreign` (`stage_id`),
  KEY `courses_stream_id_foreign` (`stream_id`),
  KEY `courses_unit_id_foreign` (`unit_id`),
  KEY `courses_master_trainer_id_foreign` (`master_trainer_id`),
  KEY `courses_training_center_id_foreign` (`training_center_id`),
  CONSTRAINT `courses_curriculum_id_foreign` FOREIGN KEY (`curriculum_id`) REFERENCES `curriculums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `courses_focal_point_id_foreign` FOREIGN KEY (`focal_point_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `courses_master_trainer_id_foreign` FOREIGN KEY (`master_trainer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `courses_stage_id_foreign` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `courses_stream_id_foreign` FOREIGN KEY (`stream_id`) REFERENCES `streams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `courses_training_center_id_foreign` FOREIGN KEY (`training_center_id`) REFERENCES `training_centers` (`id`),
  CONSTRAINT `courses_training_coordinator_id_foreign` FOREIGN KEY (`training_coordinator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `courses_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

course_schedule_prefs;

CREATE TABLE `course_schedule_prefs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cunduct_days` int(11) DEFAULT NULL,
  `training_centers` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `shift` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `course_schedule_prefs_user_id_foreign` (`user_id`),
  CONSTRAINT `course_schedule_prefs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

我的问题是，就性能和准确性而言，这个查询中的哪一个是好的。为什么 ？

第一个查询:我直接join表；选择表格的所有列。

第二个查询:我在连接中使用了子查询；只选择我需要的那些元素。

注意:我的主要问题是关于连接中的 users 表

Answer 1

这里有太多的可能性。没有简单、“直接”的答案。

使用 LEFT JOIN(而不是 JOIN)，优化器无法从子查询开始，而它通常喜欢这样做。

如果有 WHERE 子句并且它对过滤行很有用，优化器会从过滤次数最多的表开始。您的 WHERE 子句闻起来像一个标志，通常不是很有用。

如果子查询有 GROUP BY 或 LIMIT，那么它可能会显着减少要处理的行数。 (不是你的情况。)这可能使子查询方法更好。

如果 cu 有 INDEX(relation_type, user_id)，优化器可能决定处理所有的WHERE 和 GROUP BY 导致最佳选择，因此从 cu 作为“第一个”表开始。

在 旧 版本的 MySQL 中，JOIN ( SELECT ... ) 中的子查询无法获取索引，从而导致该 tmp 表的表扫描效率低下.在 新 版本中(我认为从 5.6 开始)，优化器采取额外的步骤(这需要一些成本)来生成最佳索引(在本例中为 (id))在创建 tmp 表之后和 JOINing 它之前。

更多

• 还需要 2 个表定义。
• 你有我建议的索引吗？
• InnoDB 确实需要一个 PRIMARY KEY - 请参阅 many:many有关 course_user 的建议。