为什么select语句这么慢？

frebpwbc  于 2021-06-21  发布在  Mysql
关注(0)|答案(4)|浏览(445)

这个select语句运行得非常慢。完成执行需要10秒以上。可能会更长，但我不知道，因为mysql的连接超时了。那是另一个问题。
代码如下：

SELECT 
    f.id, f.name, GROUP_CONCAT(DISTINCT (c.firstname)) children
FROM
    families f,
    children c,
    transactions t
WHERE
    f.companyid = 1170 AND f.id = t.familyid
        AND f.id = c.familyid
        AND t.transactiontype = 'P'
        AND t.taxdeductible = 'Y'
        AND YEAR(t.date) = 2017
        AND status = 'A'
        OR f.id = 9779432
GROUP BY f.id
ORDER BY name;

我有families.companyid、children.familyid、transactions.transactiontype、transactions.taxcreible和transactions.date的索引。
有没有什么理由让它不顾我的索引而进行全表扫描？或者这个查询运行缓慢还有其他原因吗？
编辑：根据以下注解填写部分空白：
children表在73000行中有17mb的数据。
families表在56000行中有6mb的数据
transactions表在980000行中有83mb的数据。
子表

CREATE TABLE `children` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `familyid` int(10) unsigned DEFAULT '0',
  `companyid` int(11) DEFAULT '0',
  `picture` varchar(250) DEFAULT NULL,
  `stockpicture` varchar(1) DEFAULT 'N',
  `firstname` varchar(250) DEFAULT NULL,
  `lastname` varchar(250) DEFAULT NULL,
  `nickname` varbinary(250) DEFAULT NULL,
  `birthdate` date NOT NULL DEFAULT '0000-00-00',
  `usecustomfee` varchar(1) NOT NULL DEFAULT 'N',
  `usecustomproviderfee` varchar(1) NOT NULL DEFAULT 'N',
  `customfee` decimal(10,2) DEFAULT '0.00',
  `customfeetypecode` varchar(45) DEFAULT 'MONTH',
  `customproviderfee` decimal(10,2) DEFAULT '0.00',
  `customproviderfeetypecode` varchar(45) DEFAULT 'MONTH',
  `usecustomchargeitem` varchar(1) DEFAULT 'N',
  `customchargeitem` int(11) DEFAULT '0',
  `dailyrate` decimal(10,2) DEFAULT '55.00',
  `startdate` date DEFAULT NULL,
  `enddate` date DEFAULT NULL,
  `subsidynotrequired` char(1) NOT NULL DEFAULT 'Y',
  `subsidychildid` varchar(250) DEFAULT NULL,
  `subsidyapplicantid` varchar(250) DEFAULT NULL,
  `subsidynote` text,
  `waitingsince` date DEFAULT NULL,
  `waitingroom` int(11) DEFAULT NULL,
  `waitingtype` varchar(1) DEFAULT 'F',
  `preferredstart` date DEFAULT NULL,
  `registrationdate` date DEFAULT NULL,
  `groupid` int(11) NOT NULL DEFAULT '0',
  `providerisparent` varchar(1) NOT NULL DEFAULT 'N',
  `attendingschool` char(1) NOT NULL DEFAULT 'N',
  `schoolname` varchar(250) DEFAULT NULL,
  `liveswithmother` char(1) NOT NULL DEFAULT 'Y',
  `liveswithfather` char(1) NOT NULL DEFAULT 'Y',
  `liveswithother` char(1) NOT NULL DEFAULT 'N',
  `otherguardian` varchar(250) DEFAULT NULL,
  `sex` char(1) NOT NULL DEFAULT 'M',
  `note` text,
  `archived` char(1) NOT NULL DEFAULT 'N',
  `priorityid` int(11) DEFAULT '0',
  `onlineregistration` varchar(1) NOT NULL DEFAULT 'N',
  `onlineregistrationaccept` varchar(1) NOT NULL DEFAULT 'N',
  `registrationconfirmed` varchar(1) NOT NULL DEFAULT 'N',
  `registrationconfirmeddate` datetime DEFAULT NULL,
  `createddate` datetime DEFAULT NULL,
  `modifieddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fullpart` varchar(1) DEFAULT 'F',
  `parttimedays` int(11) DEFAULT '10',
  `parttimedaystype` varchar(45) DEFAULT 'D',
  `parttimedaystypecode` varchar(45) DEFAULT 'MONTH',
  `program` varchar(45) DEFAULT 'daycare',
  `registrationnote` varchar(2000) DEFAULT NULL,
  `registrationnoteread` varchar(1) DEFAULT 'N',
  `registrationsubsidy` varchar(45) DEFAULT 'noplan',
  `registrationsubsidydate` datetime DEFAULT NULL,
  `registrationsubsidyamount` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `Familyid` (`familyid`),
  KEY `companyid` (`companyid`),
  KEY `startdate` (`startdate`),
  KEY `enddate` (`enddate`),
  KEY `roomid` (`groupid`),
  KEY `providerisparent` (`providerisparent`)
) ENGINE=InnoDB AUTO_INCREMENT=93685 DEFAULT CHARSET=latin1;

“族”表

CREATE TABLE `families` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `accountnumber` varchar(100) DEFAULT NULL,
  `name` varchar(245) NOT NULL COMMENT 'The account name will typically be the name of the parent responsible for payment',
  `motherid` int(10) unsigned NOT NULL,
  `fatherid` int(10) unsigned NOT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `notes` varchar(2000) DEFAULT NULL,
  `companyid` int(10) unsigned NOT NULL,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  `financialaidrequired` char(1) NOT NULL DEFAULT 'N',
  `intakesurveyid` int(10) unsigned DEFAULT NULL,
  `referralid` int(10) unsigned NOT NULL DEFAULT '0',
  `registrationemailrequired` varchar(1) DEFAULT 'N',
  `registrationemailsent` varchar(1) DEFAULT 'N',
  `registrationemaildate` date DEFAULT NULL,
  `registrationemailaddressfound` varchar(1) DEFAULT NULL,
  `waitinglistemailrequired` varchar(1) DEFAULT 'N',
  `waitinglistemailsent` varchar(1) DEFAULT 'N',
  `waitinglistemaildate` date DEFAULT NULL,
  `waitinglistemailaddressfound` varchar(1) DEFAULT NULL,
  `activationemailrequired` varchar(1) DEFAULT 'N',
  `activationemailsent` varchar(1) DEFAULT 'N',
  `activationemaildate` date DEFAULT NULL,
  `activationemailaddressfound` varchar(1) DEFAULT NULL,
  `createddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `companyid` (`companyid`),
  KEY `intakesurveyid` (`intakesurveyid`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=9803007 DEFAULT CHARSET=latin1;

事务处理表

CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `familyid` int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  `transactiontype` varchar(1) NOT NULL DEFAULT 'C' COMMENT '''C'' = Charge, ''P'' = Payment',
  `paymenttype` varchar(3) DEFAULT NULL COMMENT '''DBT'' = Debit, ''CSH'' = Cash, ''CRE'' = Credit Card, ''CHQ'' = Cheque, ''MNY'' = Money Order,''EFT'' = Electronic Funds Transfer',
  `comment` varchar(500) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `reference` varchar(45) DEFAULT NULL,
  `chargeitem` int(10) unsigned DEFAULT '0',
  `taxdeductible` varchar(1) NOT NULL DEFAULT 'Y',
  `payer` varchar(1) DEFAULT 'M',
  `createddate` datetime DEFAULT NULL,
  `modifieddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `Familyid` (`familyid`),
  KEY `Transaction Type` (`transactiontype`),
  KEY `Tax Deductible` (`taxdeductible`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1013472 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
mysqlquery-performance

4条答案

按热度按时间
nfs0ujit

nfs0ujit1#

最好使用21世纪的连接语法。

SELECT f.id, f.name, GROUP_CONCAT(DISTINCT (c.firstname)) children
  FROM families f
  JOIN children c ON f.id = c.familyid
  JOIN transactions t ON f.id = t.familyid
 WHERE f.companyid = 1170 
   AND t.transactiontype = 'P'
   AND t.taxdeductible = 'Y'
   AND YEAR(t.date) = 2017
   AND status = 'A'
    OR f.id = 9779432
 GROUP BY f.id
 ORDER BY name;

改变 AND YEAR(t.date) = 2017 至 AND t.date >='2017-01-01 AND t.date < '2018-01-01' . 为什么？这个 YEAR() 过滤子句的形式是不可更改的。
从你的问题看不出哪个表包含 status 列，它对性能非常重要。如果是的话 t.status ，然后尝试在上创建复合索引

transaction(status, transactiontype, taxdeductible, date, familyid)

然后在上尝试复合索引

transaction(familyid, status, transactiontype, taxdeductible, date)

其中一个应该很有帮助。为什么？在满足您对 transaction 表中，mysql可以随机访问索引到第一个符合条件的记录：与所有 = 标准和具有最低值 date . 然后它可以按顺序扫描索引，直到找到最后一个符合条件的日期。
使用性能最佳的索引。
如果 status 列不在 transaction 把它从索引中去掉。

赞(0）分享  回复(0）举报  2021-06-21
mbjcgjjk

mbjcgjjk2#

假设您的意思是（mysql将这样解释它）：

(this AND that ...) OR (f.id=...)

让我们使用 UNION 而不是 OR . ( OR 优化效果不佳。）
让我们也使用“标准” JOIN...ON 而不是“commajoin”。
我们不要在函数中隐藏列( YEAR ); 它禁止使用索引。
你已经因为没有说出哪个表包含 status . 我看到哈蒙不小心弄丢了这个事实 status 在 f . 我会这么想。 DISTINCT 不是函数，所以我去掉了后面的paren。
我来挑 UNION DISTINCT （较慢，但匹配 OR 的语义）而不是 UNION ALL （更快，但可能会复制一行）。
我要走了 children 到外面去 SELECT 避免一些潜在的打嗝。
什么时候 GROUP BY 以及 ORDER BY 匹配，查询可以运行得更快。所以，假设 id 以及 name 在逻辑上是联系在一起的，我想这会给你相同的分组和排序：

GROUP BY name, id
ORDER BY name, id

把我所有的建议放在一起：

SELECT  x.id, x.name,
        GROUP_CONCAT(DISTINCT c.firstname) children
    FROM (
           ( SELECT  f.id, f.name,
                FROM  families f
                JOIN  transactions t  ON f.id = t.familyid
                WHERE  f.companyid = 1170
                  AND  t.transactiontype = 'P'
                  AND  t.taxdeductible = 'Y'
                  AND  t.date >= '2017-01-01'
                  AND  t.date <  '2017-01-01' + INTERVAL 1 YEAR
                  AND  f.status = 'A'
           )
           UNION DISTINCT
           ( SELECT   f.id, f.name
                FROM  families f
                WHERE  f.id = 9779432
           ) 
         ) AS x
    JOIN  children c  ON x.id = c.familyid
    GROUP BY  x.name, x.id
    ORDER BY  x.name, x.id

您将需要这些索引。列顺序通常很重要。

f:  I assume it has PRIMARY KEY(id)
f:  (companyid, status)   -- in either order
t:  (familyid, transactiontype, taxdeductible, date)
t:  (transactiontype, taxdeductible, date, familyid)
c:  (familyid, firstname)

注意事项：
我给了两个索引 t --两者都提供，从而让优化器决定是否从 f 或者 t .
一些指数是“覆盖”的，因此提供了额外的推动。
重新制定之后， DISTINCT 在 GROUP_CONCAT 可能没有必要。
多个单列索引通常不如“复合”（多列）索引有利。

赞(0）分享  回复(0）举报  2021-06-21
6tqwzwtp

6tqwzwtp3#

尝试

EXPLAIN
SELECT 
    f.id, f.name, GROUP_CONCAT(DISTINCT (c.firstname)) children
FROM
    families f,
    children c,
    transactions t
WHERE
    f.companyid = 1170 AND f.id = t.familyid
        AND f.id = c.familyid
        AND t.transactiontype = 'P'
        AND t.taxdeductible = 'Y'
        AND YEAR(t.date) = 2017
        AND f.status = 'A'
        OR f.id = 9779432
GROUP BY f.id
ORDER BY name;

以确保加载了正确的索引
你说你“有索引”，但你只能使用一个索引每个查询，使 1 所需查询的索引。
我也建议不要使用倍数 from 但是使用 JOIN 语句，而不是能够针对连接的表索引和索引

赞(0）分享  回复(0）举报  2021-06-21
djmepvbi

djmepvbi4#

请提供表格架构。我们需要检查一下你们有什么索引。
同时你可以试着 JOIN 表格和删除 ORDER BY . 从我看来你只有一个 f.id = 9779432 ，那么为什么要订购相同的值呢？
检查您的 OR 条件，我把它变成了对我有意义的东西。你最初的陈述很宽泛或者意味着你需要什么 YEAR(t.date) OR f.id = 9779432 这对你有意义吗？

SELECT 
    f.id, f.name, GROUP_CONCAT(DISTINCT (c.firstname)) children
FROM
    families f
INNER JOIN children c
ON f.id = c.familyid
INNER JOIN transactions t
ON f.id = t.familyid
   AND t.transactiontype = 'P'
   AND t.taxdeductible = 'Y'
   AND YEAR(t.date) = 2017
WHERE
    (f.companyid = 1170 OR f.id = 9779432)
    AND f.status = 'A'

GROUP BY f.id;
赞(0）分享  回复(0）举报  2021-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com