使用2个连接和group by子句优化mysql查询

概述我有一个需要10-20秒的查询,但我确信它可以优化,我只是不够好做到这一点.我想要一些帮助和解释,以便我可以将它应用于类似的查询.这是我的查询：SELECT `store_formats`.`Store Nbr`, `store_formats`.`Store Name`, `store_formats`.`F

我有一个需要10-20秒的查询,但我确信它可以优化,我只是不够好做到这一点.我想要一些帮助和解释,以便我可以将它应用于类似的查询.
这是我的查询：

SELECT        `store_formats`.`Store Nbr`,`store_formats`.`Store name`,`store_formats`.`Format name`,`eds_sales`.`Date`,sum(`eds_sales`.`EPOS Sales`) AS Sales,sum(`eds_sales`.`EPOS Quantity`) AS Quantity         FROM         `eds_sales`         INNER JOIN `item_codes` ON `eds_sales`.`Prime Item Nbr` = `item_codes`.`Customer Item`         INNER JOIN `store_formats` ON `eds_sales`.`Store Nbr` = `store_formats`.`Store Nbr`         WHERE         `eds_sales`.`Store Nbr` IN ($storenbr) AND         `eds_sales`.`Date`  BETWEEN '$startdate' AND '$enddate' AND         `eds_sales`.`ClIEnt` = '$customer' AND         `eds_sales`.`Retailer` IN ($retailer) AND         `store_formats`.`Format name` IN ($storeformat) AND         `item_codes`.`Item Number` IN ($products)         GROUP BY         `store_formats`.`Store name`,`store_formats`.`Store Nbr`,`eds_sales`.`Date`

这是解释输出：

正如您将在那里看到的那样,我尝试并创建了几个索引,其中涉及的列没有太大成功.主要的延迟是由我认为复制到临时表引起的.

这些是涉及的表格：

store_formats：

CREATE table `store_formats` (`ID` int(12) NOT NulL,`Store Nbr` smallint(5) UNSIGNED DEFAulT NulL,`Store name` varchar(27) DEFAulT NulL,`City` varchar(19) DEFAulT NulL,`Post Code` varchar(9) DEFAulT NulL,`Region #` int(2) DEFAulT NulL,`Region name` varchar(10) DEFAulT NulL,`distr #` int(3) DEFAulT NulL,`dist name` varchar(26) DEFAulT NulL,`Square Footage` varchar(7) DEFAulT NulL,`Format` int(1) DEFAulT NulL,`Format name` varchar(23) DEFAulT NulL,`Store Type` varchar(20) DEFAulT NulL,`TV Region` varchar(12) DEFAulT NulL,`Pharmacy` varchar(3) DEFAulT NulL,`Optician` varchar(3) DEFAulT NulL,`Home ShopPing` varchar(3) DEFAulT NulL,`Retailer` varchar(15) DEFAulT NulL) ENGINE=InnoDB DEFAulT CHARSET=utf8;ALTER table `store_formats`ADD PRIMARY KEY (`ID`),ADD UNIQUE KEY `uniqness` (`Store Nbr`,`Store name`,`Format`),ADD KEY `Store Nbr_2` (`Store Nbr`,`Format name`,`Store name`);

eds_sales：

CREATE table `eds_sales` (`ID` int(12) UNSIGNED NOT NulL,`Prime Item Nbr` mediumint(7) NOT NulL,`Prime Item Desc` varchar(255) NOT NulL,`Prime Size Desc` varchar(255) NOT NulL,`VarIEty` varchar(255) NOT NulL,`WHPK Qty` int(5) NOT NulL,`SUPPK Qty` int(5) NOT NulL,`Depot Nbr` int(5) NOT NulL,`Depot name` varchar(50) NOT NulL,`Store Nbr` smallint(5) UNSIGNED NOT NulL,`Store name` varchar(255) NOT NulL,`EPOS Quantity` smallint(3) NOT NulL,`EPOS Sales` decimal(13,2) NOT NulL,`Date` date NOT NulL,`ClIEnt` varchar(10) NOT NulL,`Retailer` varchar(50) NOT NulL) ENGINE=InnoDB DEFAulT CHARSET=latin1;ALTER table `eds_sales`ADD UNIQUE KEY `uniqness` (`Prime Item Nbr`,`Prime Item Desc`,`Prime Size Desc`,`VarIEty`,`WHPK Qty`,`SUPPK Qty`,`Depot Nbr`,`Depot name`,`Store Nbr`,`Date`,`ClIEnt`) USING BTREE,ADD KEY `Store Nbr` (`Store Nbr`),ADD KEY `Prime Item Nbr_2` (`Prime Item Nbr`,`Date`),ADD KEY `ID` (`ID`) USING BTREE,ADD KEY `Store Nbr_2` (`Prime Item Nbr`,`ClIEnt`,`Retailer`) USING BTREE,ADD KEY `ClIEnt` (`ClIEnt`,ADD KEY `Date` (`Date`,`Retailer`);

item_codes：

CREATE table `item_codes` (`ID` int(12) NOT NulL,`Item Number` varchar(30) CHaraCTER SET latin1 NOT NulL,`Customer Item` mediumint(7) NOT NulL,`Description` varchar(255) CHaraCTER SET latin1 NOT NulL,`Status` varchar(15) CHaraCTER SET latin1 NOT NulL,`Customer` varchar(30) CHaraCTER SET latin1 NOT NulL,`Sort name` varchar(255) CHaraCTER SET latin1 NOT NulL,`EquIDataCustomer` varchar(30) CHaraCTER SET latin1 NOT NulL) ENGINE=InnoDB DEFAulT CHARSET=utf8;ALTER table `item_codes`ADD PRIMARY KEY (`ID`),ADD UNIQUE KEY `uniq` (`Item Number`,`Customer Item`,`Customer`,`EquIDataCustomer`),ADD KEY `Item Number_2` (`Item Number`,`Sort name`,ADD KEY `Customer Item` (`Customer Item`,`Item Number`,ADD KEY `Customer Item_2` (`Customer Item`,`EquIDataCustomer`);

所以我的问题：
正如您所看到的,我正在加入3个表,我正在按商店格式查找销售日期.我一直在尝试不同类型的连接,或者例如,不是将销售加入item_codes和store_formats,而是将store_formats连接到其他连接,但结果相同.我也使用IN传递一些变量数组,因为它们是由应用程序中的选择框提供的.

>加入这些表格的最佳方式
>建议每张桌子的最佳指数
>为什么我要获得临时表？是因为小组的？有解决方法吗？
>如果需要临时表,还是要加速这个创建吗？ (我已经拥有8个磁盘的raID中的数据文件夹,但仍然很慢.
>当然,欢迎任何建议的替代方案

更新：使用评论中的一些建议更新了我的表格

更新：修改我的my.cnf以提高性能(我的RAM为8GB,2核,/ data / tmp在8驱动器raID上,与数据相同)

tmpdir          = /dev/shm/:/data/tmp:/tmplc-messages-dir = /usr/share/MysqLskip-external-lockingexpire_logs_days        = 10max_binlog_size   = 100Minnodb_buffer_pool_size = 6Ginnodb_buffer_pool_instances = 6query_cache_type=1

最佳答案(评论太多了;请原谅我使用答案.)

当你有INDEX(a)和INDEX(a,b)时,前者是多余的,应该被删除.我看到了大约5个这样的案例.

每个store_nbr只有一个store_name？如果是这样,将store_name放在多个表中是多余的.我不知道store_formats的意图,但我想这是一个容纳store_name的表.请注意,两个store_name列的数据类型和store_nbr列的大小不一致！

似乎每个商店都应该有一个唯一的编号,如果是这样,那么ADD UNIQUE KEY uniqness(Store Nbr,Store name)应该可以变成PRIMARY KEY(store_nbr). (对不起,我不打算在列名中添加空格.)

使用日期启动索引很少有用,因此请删除KEY Date_2(日期,客户端).取而代之的是,添加INDEX(ClIEnt,store_nbr,Date);这应该对查询的速度有直接影响.您可能会看到EXPLAIN SELECT …更改.

int(4) – 也许你的意思是SMALliNT UNSIGNED？

在UNIQUE(或PRIMARY)键中使用Date通常是“错误的”.什么是“客户”在同一天购买了两件相同的东西？

完成这些更改后,我们再谈一谈.

为了查看的一致性,请提供SHOW CREATE table.

避免这种结构：

FROM ( SELECT ... )JOIN ( SELECT ... ) ON ...

这是低效的,因为子查询都没有使JOIN高效的索引. 总结

以上是内存溢出为你收集整理的使用2个连接和group by子句优化mysql查询全部内容，希望文章能够帮你解决使用2个连接和group by子句优化mysql查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。