我在这个特定的查询中遇到了问题,我尝试在receiving_table中使用索引,但它仍然使用filesort,Mysql,数据库一般不是我的强项,我仍在学习基础知识,我只有8000条记录,查询7秒很慢,我附上了我的查询和数据库,对不起,英语也不是我的强项, 提前致谢
1 PRIMARY re index PRIMARY,imei_index,imeibatchclientyear,batches batches 20 8326 Using index; Using temporary; Using filesort
1 PRIMARY b ref PRIMARY,batch PRIMARY 26 tracking_system_1_schema.re.clientcode,tracking_system_1_schema.re.batchnum 1 Using where
1 PRIMARY hh eq_ref PRIMARY PRIMARY 24 tracking_system_1_schema.re.imei 1 Using where; Using index
1 PRIMARY rm eq_ref PRIMARY PRIMARY 24 tracking_system_1_schema.re.imei 1 Using where
1 PRIMARY si ref PRIMARY,imei,date date 22 func,tracking_system_1_schema.re.imei 1 Using where; Using index
1 PRIMARY h ref imei_index imei_index 30 tracking_system_1_schema.b.clientcode,tracking_system_1_schema.re.batchnum,tracking_system_1_schema.re.year 136 Using where
2 DEPENDENT SUBQUERY ssi ref PRIMARY,imei imei 17 tracking_system_1_schema.re.imei 2 Using index
EXPLAIN
SELECT
b.clientcode AS 'CUSTOMER',
b.batchnum AS 'BATCH NUMBER',
b.year,
b.datereceived AS 'DATE RECEIVED',
b.quantity AS 'BATCH QTY' ,
COUNT(DISTINCT re.imei ) + COUNT(DISTINCT IFNULL(h.imei, NULL) ) AS 'RECEIVE ITEMS',
COUNT(DISTINCT IFNULL(h.imei , NULL)) AS 'SHIPPED ITEMS',
COUNT(DISTINCT re.imei ) - COUNT(DISTINCT IF(si.processcode = 51, si.imei , NULL)) - COUNT(DISTINCT IF((hh.imei = si.imei OR rm.imei= si.imei) AND si.processcode != 51 , si.imei , NULL)) AS 'WIP',
COUNT(DISTINCT IF(si.processcode = 51, si.imei , NULL)) - COUNT(DISTINCT IF((hh.imei = si.imei OR rm.imei= si.imei) AND si.processcode = 51 , si.imei , NULL)) AS 'FGS',
COUNT(DISTINCT hh.imei) + COUNT(DISTINCT rm.imei) AS 'HOLD ITEMS'
FROM
tracking_system_1_schema.scanin_process_table si
INNER JOIN
tracking_system_1_schema.receiving_table re
ON
re.imei = si.imei
LEFT OUTER JOIN
tracking_system_1_schema.hold_table hh
ON
hh.imei = re.imei
LEFT OUTER JOIN
tracking_system_1_schema.rma_status_Table rm
ON
re.imei = rm.imei
AND
rm.status = 2
INNER JOIN
tracking_system_1_schema.batch_table b
ON
b.batchnum = re.batchnum
AND
b.clientcode = re.clientcode
AND
b.year = re.year
LEFT OUTER JOIN
item_history_1_schema.item_history_table h
ON
b.batchnum = h.batchnum
AND
b.clientcode = h.clientcode
AND
b.year = h.year
WHERE
si.dateandtime =
(
SELECT
MAX(ssi.dateandtime)
FROM
tracking_system_1_schema.scanin_process_table ssi
WHERE
ssi.imei = re.imei
)
AND
(si.dateandtime <= NOW()
OR
h.departuredate <= NOW())
GROUP BY
re.clientcode,
re.batchnum ,
re.year
;
delimiter $$
CREATE TABLE `receiving_table` (
`imei` varchar(15) NOT NULL,
`modelname` varchar(20) NOT NULL,
`batchnum` int(10) NOT NULL,
`clientcode` varchar(10) NOT NULL,
`dateandtime` datetime NOT NULL,
`status` int(11) NOT NULL DEFAULT '0',
`workerid` varchar(6) NOT NULL,
`reusecount` bigint(20) NOT NULL,
`gradebatch` varchar(30) NOT NULL,
`serialnum` varchar(15) NOT NULL,
`modifydate` datetime DEFAULT NULL,
`simcarrier` varchar(20) DEFAULT NULL,
`ismanual` int(1) DEFAULT '0',
`modelnumber` varchar(15) DEFAULT NULL,
`fccid` varchar(15) DEFAULT NULL,
`rmastatus` int(11) DEFAULT '0',
`simtraystatus` varchar(15) DEFAULT 'null',
`batchtype` int(2) DEFAULT '0',
`withlcmmarking` bit(1) NOT NULL DEFAULT b'0',
`year` int(10) NOT NULL DEFAULT '2015',
PRIMARY KEY (`imei`),
UNIQUE KEY `imei_index` (`imei`) USING BTREE,
KEY `imeibatchclientyear` (`imei`,`clientcode`,`batchnum`,`year`),
KEY `batches` (`batchnum`,`clientcode`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
首先,"文件排序"只是一个问题。 所以我不会专注于它。 文件排序本质上是 GROUP BY 所必需的。 这并不一定意味着它接触的磁盘。
AND (si.dateandtime <= NOW() OR h.departuredate <= NOW())
将来有条目吗? 如果没有,你就不能摆脱这个吗?
如果确实需要它,请尝试将该语句转换为 UNION DISTINCT。 这将为优化程序提供使用包含其中一列的索引的机会。
COUNT(DISTINCT)s需要在联盟之外。 也就是说,UNION 需要位于子查询中。
这是一个"相关子查询":( SELECT MAX(...) ... )
它有一个覆盖索引(好),但我们不知道它被调用的频率 - 可能超过8K次。 这可能是你 7 秒的大部分时间。 尝试将其变成JOIN ( SELECT imei, MAX(dateandtime) FROM scanin_process_table ) foo ON ...
,外加合适的行李。
请注意,创建该 JOIN 将导致它成为要处理的第一个表。 所以,你需要INDEX(imei, dateandtime)
. (我在这里对细节很模糊。
的一个普遍问题是,JOIN 扩展了正在考虑的"行"的数量;然后是 GROUP BY 合约。 请注意,您必须在 COUNT 中执行 DISTINCT 操作以避免不必要的重复。 要了解这有多糟糕,请SELECT COUNT(*) FROM ( <your SELECT, but without the GROUP BY> ) x
; 我猜可能超过一百万。 这也可能是导致 7 秒的主要原因。
因此,一般的解决方案是明智地将查询拆分为具有子查询的查询。 唉,我看不出容易分裂。 下面是部分解决方案:将b
拆分为外部查询。 请注意,b
字段并没有真正在任何地方使用。 (h
可以从re
获得,而不是通过b
。 通过拉出b.clientcode等,tmp表不必拖着它们。 (因此,百万行 tmp 表将更小,因此更快。 但速度不会快很多。
您可以尝试通过简单地删除对 b 的所有引用来拆分 b。 然后看看它运行的速度有多快。 然后(如果它明显更快),因此使用它:
SELECT b.clientcode ...
FROM ( <all the rest> ) x
JOIN tracking_system_1_schema.batch_table b ON b.year = x.year AND ...;
确保 b 有INDEX(batchnum, clientcode, year)
-- 以任何顺序。