时间戳

首页 > 解决方案 > 优化 DISTINCT 。在 mysql 从一个大的 Mysql 表中

mysql - 优化 DISTINCT 。在 mysql 从一个大的 Mysql 表中

问题描述

我的查询有问题，超过 1 分钟，我认为问题来自我的不同

我的索引：table1 (project_name) table2 (importer_id,sipplier_id) table3 (name)

我的表 2 有超过 200 万行

这是我的查询

SELECT DISTINCT table1.id         AS project_id,
                table1.name       AS project_name,
                table3.id         AS supplier_id,
                table4.report_id as report_id,
                table3.name       AS supplier_name
FROM  table1
         LEFT JOIN  table2 ON table2.project_id = table1.id
         LEFT JOIN  table3 ON table3.id = table2.supplier_id
         LEFT JOIN  table4 ON table4.project_id = table1.id
WHERE table1.id IN
      ('2067', '2068', '2069', '2070', '2072', '2073', '2074', '2083', '2084', '2085', '2086', '2087', '2088', '2089',
       '2090', '2091', '2092', '2093', '2094', '2095', '2096', '2097', '2098', '2100', '2101', '2102', '2103', '2104',
       '2105', '2106', '2107', '2108', '2109', '2110', '2111', '2112', '2113', '2114', '2115', '2116', '2117', '2118',
       '2119', '2120', '2121', '2122', '2123', '2124', '2125', '2126', '2127')
  AND (table2.importer_id IN ('215') OR table2.supplier_id IN ('215'))

解释

为所有表创建表语句（表1表2表3表4）

CREATE TABLE `table1` (
                                  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                                  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
                                  `client_id` int(11) unsigned NOT NULL,

                                  PRIMARY KEY (`id`),
                                  KEY `FK_wsxix_projects` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2085 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE `table2` (
                          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

                          `buyer_id` int(11) unsigned DEFAULT NULL,
                          `client_id` int(11) unsigned DEFAULT NULL,
                          `merchandiser_user_id` int(11) unsigned DEFAULT NULL,
                          `factory_id` int(11) unsigned DEFAULT NULL,
                          `dc_id` int(11) unsigned DEFAULT NULL,
                          `parent_id` int(10) unsigned DEFAULT NULL,
                          `supplier_id` int(11) unsigned NOT NULL,
                          `importer_id` int(11) unsigned NOT NULL,
                          `project_id` int(11) unsigned NOT NULL,
                          `tier_two_id` int(11) unsigned DEFAULT NULL,
                          `store_id` int(11) unsigned DEFAULT NULL,
                          PRIMARY KEY (`id`),
                          KEY `buyer_id` (`buyer_id`),
                          KEY `client_id` (`client_id`),
                          KEY `merchandiser_user_id` (`merchandiser_user_id`),
                          KEY `FK_po_factory_companies_id` (`factory_id`),
                          KEY `FK_po_dc_companies_id` (`dc_id`),
                          KEY `index_po_parentid` (`parent_id`),
                          KEY `index_purchaseorders_cie` (`supplier_id`),
                          KEY `index_poimporter_cie` (`importer_id`),
                          KEY `index_purchaseorders_project` (`project_id`),
                          KEY `facory_importer_id` (`factory_id`,`importer_id`),
                          KEY `posu` (`project_id`,`supplier_id`),
                          KEY `store_id` (`store_id`),
                          KEY `tier_two_id` (`tier_two_id`)

) ENGINE=InnoDB AUTO_INCREMENT=34959 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;






CREATE TABLE `table3` (
                                   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                                   `parent_company_id` int(11) DEFAULT NULL,
                                   `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                   `type_id` int(11) unsigned DEFAULT NULL,
                                   `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

                                   `use_majora_majorb` tinyint(1) NOT NULL DEFAULT '0',
                                   `aql_presets_id` int(11) unsigned DEFAULT NULL,
                                   `fabx_presets_id` int(11) unsigned DEFAULT NULL,
                                   `group_id` int(11) unsigned DEFAULT NULL,
                                   `contact_number` varchar(55) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                   `unit_number` varchar(55) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                   `use_factory_location` tinyint(1) unsigned DEFAULT NULL,
                                   `restrict_users_access_to_sub_companies` tinyint(1) unsigned DEFAULT '0' COMMENT 'Using for share function so far',
                                   PRIMARY KEY (`id`),
                                   KEY `index_wsxix_companies_aql_presets_id` (`aql_presets_id`),
                                   KEY `index_company_type` (`type_id`),
                                   KEY `index_companies_group_id` (`group_id`),
                                   KEY `fk_wsxix_companies_fabx_presets_idx` (`fabx_presets_id`)

) ENGINE=InnoDB AUTO_INCREMENT=992 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE `table4` (
                                         `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                                         `report_id` int(11) NOT NULL,
                                         `project_id` int(11) unsigned NOT NULL,
                                         PRIMARY KEY (`id`),
                                         KEY `index_project_reports_projects` (`project_id`),
                                         KEY `index_project_reports_reports` (`report_id`),
                                         CONSTRAINT `fk_project_reports_projects` FOREIGN KEY (`project_id`) REFERENCES `wsxix_projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                                         CONSTRAINT `fk_project_reports_reports` FOREIGN KEY (`report_id`) REFERENCES `wsxix_report_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

标签： mysqlquery-optimization

解决方案

好吧，这是我的第一次尝试。OR请记住，考虑到所有的andIN语句，这是一个相当难以优化的查询。

尝试以下重写的查询。它使用半连接来避免对 的需要DISTINCT，这可能会带来不错的性能提升。

SELECT
table1.id         AS project_id,
table1.name       AS project_name,
table3.id         AS supplier_id,
table4.report_id  AS report_id,
table3.name       AS supplier_name
FROM table1
LEFT OUTER JOIN table3
ON EXISTS (
  SELECT 1 FROM table2
  WHERE table2.project_id = table1.id
  AND table2.supplier_id = table3.id
  AND (table2.importer_id IN ('215') OR table2.supplier_id IN ('215'))
)
LEFT OUTER JOIN table4
ON table4.project_id = table1.id
WHERE table1.id IN
  ('2067', '2068', '2069', '2070', '2072', '2073', '2074', '2083', '2084', '2085', '2086', '2087', '2088', '2089',
   '2090', '2091', '2092', '2093', '2094', '2095', '2096', '2097', '2098', '2100', '2101', '2102', '2103', '2104',
   '2105', '2106', '2107', '2108', '2109', '2110', '2111', '2112', '2113', '2114', '2115', '2116', '2117', '2118',
   '2119', '2120', '2121', '2122', '2123', '2124', '2125', '2126', '2127')

然后，添加以下索引，这也应该有助于提高性能：

ALTER TABLE table4
ADD INDEX sowr1 (project_id,report_id)

ALTER TABLE table2
ADD INDEX sowr2 (project_id,supplier_id,importer_id)

至少运行两次查询，并忽略第一次运行的时间，这样可以避免 InnoDB 缓冲区缓存给出不准确的速度结果。

让我知道它的运行速度。然后，如果它仍然需要更多改进，请为此查询提供 EXPLAIN 计划，因为如果没有您手头的数据，很难确切地看到这些更改将如何工作。

推荐阅读