我想优化mysql查询_大数据知识库

请大家帮我优化下面的查询

SELECT
        `dti`.`CompanyId`, 
        `dti`.`Samiti`, 
        `dti`.`toll_date`, 
        `dti`.`MajorFee`, 
        `dti`.`MinorFee`, 
        `dti`.`SawalFee`, 
        SUM(dti.Tmwt) as Tmwt, 
        SUM(dti.Localminor) as Localminor, 
        SUM(dti.Swt) as Swt, 
        SUM(dti.Twt) as Twt, 
        SUM(((dti.Tmwt * dti.MajorFee) + (dti.Localminor * dti.MinorFee) + (dti.Swt * dti.SawalFee))) as total_wages, 
        SUM((dti.Twt * dti.govt_charges)) as govt_deduction, 
        SUM((((dti.Tmwt * dti.MajorFee) + (dti.Localminor * dti.MinorFee) + (dti.Swt * dti.SawalFee)) - (dti.Twt * dti.govt_charges))) as net_amount, 

        (SELECT (SUM(ld.amount) + SUM(ld.advance_deduction)) 
          FROM psac_liability_deduction ld 
          WHERE ld.status = "Active" AND 
                ld.from_date >="2017-08-24" AND 
                ld.to_date <="2017-08-31" AND 
                ld.deducted_for = dti.CompanyId
        ) as group_liability_deduction, 

        (SELECT CONCAT(SUM(wi.GroupLiabilityDeduction), "|", SUM(wi.AdvanceWagesDeduction)) 
            FROM psac_wagesitem wi 
            WHERE   wi.status="Active" AND 
                    wi.from_date >= "2017-08-24" AND 
                    wi.to_date <= "2017-08-31" AND 
                    wi.MainGroup=dti.Samiti AND 
                    wi.FishermanId=dti.CompanyId
        ) as wages_deduction, 

        (SELECT CONCAT(SUM(cdp.product_liability), "|", SUM(cdp.wages_liability)) 
            FROM psac_cash_deposited_payment cdp 
            WHERE   cdp.status="Active" AND 
                    cdp.deposit_date >= "2017-08-24" AND 
                    cdp.deposit_date <= "2017-08-31" AND 
                    cdp.maingroup_id=dti.Samiti AND 
                    cdp.fisherman_id=dti.CompanyId
        ) as cash_deposited, 
        `fm`.`Name` as `fishername`, 
        `fm`.`Code` as `fishername_code`, 
        `fm`.`Bank`, 
        `fm`.`IfscCode`, 
        `fm`.`AccountNo`

    FROM `psac_dailytollinfo` `dti`
    LEFT JOIN `psac_fisherman` `fm` ON `fm`.`ID`=`dti`.`CompanyId`
    WHERE 
    `dti`.`status` = 'Active' AND
    `dti`.`toll_date` >= '2017-08-24' AND
    `dti`.`toll_date` <= '2017-08-31'
    GROUP BY `dti`.`toll_date`, `dti`.`CompanyId`
    ORDER BY `dti`.`toll_date` ASC

请帮助我优化此查询。如果我删除子查询，它将完美地工作，但与子查询它需要太多的时间。以下是表格结构
psac\ U dailytollinfo表

CREATE TABLE `psac_dailytollinfo` (
      `ID` int(11) NOT NULL,
      `toll_date` date NOT NULL,
      `Point` int(11) NOT NULL,
      `group_type` int(11) NOT NULL,
      `Samiti` int(11) NOT NULL,
      `DailytollId` int(11) NOT NULL,
      `CompanyId` int(11) NOT NULL,
      `Name` varchar(250) NOT NULL,
      `govt_charges` float(15,2) NOT NULL,
      `MajorFee` float(15,2) NOT NULL,
      `MinorFee` float(15,2) NOT NULL,
      `SawalFee` float(15,2) NOT NULL,
      `Cqty` varchar(150) NOT NULL,
      `Cwt` varchar(150) NOT NULL,
      `Rqty` varchar(150) NOT NULL,
      `Rwt` varchar(150) NOT NULL,
      `Mqty` varchar(150) NOT NULL,
      `Mwt` varchar(150) NOT NULL,
      `Kqty` varchar(150) NOT NULL,
      `Kwt` varchar(150) NOT NULL,
      `Aqty` varchar(150) NOT NULL,
      `Awt` varchar(150) NOT NULL,
      `Sqty` varchar(11) NOT NULL,
      `Swt` varchar(11) NOT NULL,
      `Lqty` varchar(150) NOT NULL,
      `Lwt` varchar(150) NOT NULL,
      `Localminor` varchar(150) NOT NULL,
      `Tmqty` varchar(150) NOT NULL,
      `Tmwt` varchar(150) NOT NULL,
      `Tqty` varchar(150) NOT NULL,
      `Twt` varchar(150) NOT NULL,
      `added_by` int(11) NOT NULL,
      `updated_by` int(11) NOT NULL,
      `added_date` datetime NOT NULL,
      `updated_date` datetime NOT NULL,
      `action_microtime` varchar(20) NOT NULL,
      `status` enum('Active','Inactive','Deleted') NOT NULL DEFAULT 'Active'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

psac负债扣除表

CREATE TABLE `psac_liability_deduction` (
  `ID` bigint(20) NOT NULL,
  `wages_id` int(11) NOT NULL,
  `wages_item_id` int(11) NOT NULL,
  `amount` float(15,2) NOT NULL,
  `advance_deduction` float(11,2) NOT NULL,
  `group_type_id` int(11) NOT NULL,
  `maingroup_id` int(11) NOT NULL,
  `deducted_by` int(11) NOT NULL,
  `deducted_for` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `status` enum('Active','Inactive','Deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
  `added_by` int(11) NOT NULL,
  `updated_by` int(11) NOT NULL,
  `added_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `action_microtime` varchar(50) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

psac\U wagesitem表

CREATE TABLE `psac_wagesitem` (
      `ID` int(11) NOT NULL,
      `wages_for` enum('Fisherman','Group') NOT NULL DEFAULT 'Fisherman',
      `wages_id` int(11) NOT NULL,
      `from_date` date NOT NULL,
      `to_date` date NOT NULL,
      `group_type_id` int(11) NOT NULL,
      `MainGroup` int(11) NOT NULL,
      `FishermanId` int(11) NOT NULL,
      `MajorFee` float(15,2) NOT NULL,
      `MinorFee` float(15,2) NOT NULL,
      `SawalFee` float(15,2) NOT NULL,
      `major_wt` float(15,2) NOT NULL,
      `minor_wt` float(15,2) NOT NULL,
      `sawal_wt` float(15,2) NOT NULL,
      `major_wage` float(15,2) NOT NULL,
      `minor_wage` float(15,2) NOT NULL,
      `sawal_wage` float(15,2) NOT NULL,
      `TotalWage` float(15,2) NOT NULL,
      `group_liability` float(15,2) NOT NULL,
      `advance_wages` float(15,2) NOT NULL,
      `GovDeduction` float(15,2) NOT NULL,
      `GroupLiabilityDeduction` float(15,2) NOT NULL,
      `AdvanceWagesDeduction` float(15,2) NOT NULL,
      `final_wages` float(15,2) NOT NULL,
      `added_by` int(11) NOT NULL,
      `updated_by` int(11) NOT NULL,
      `added_date` datetime NOT NULL,
      `updated_date` datetime NOT NULL,
      `action_microtime` varchar(20) NOT NULL,
      `status` enum('Active','Inactive','Deleted') NOT NULL DEFAULT 'Active',
      `editable` enum('Lock','Unlock') NOT NULL DEFAULT 'Unlock'
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

现金存款支付表

CREATE TABLE `psac_cash_deposited_payment` (
  `deposit_id` int(11) NOT NULL,
  `deposited_by` enum('Fisherman','Group') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Fisherman',
  `deposit_date` date NOT NULL,
  `group_type_id` int(11) NOT NULL,
  `maingroup_id` int(11) NOT NULL,
  `fisherman_id` int(11) NOT NULL,
  `product_liability` float(11,2) NOT NULL,
  `wages_liability` float(11,2) NOT NULL,
  `receipt_number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `remark` text COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('Active','Inactive','Deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
  `editable` enum('Lock','Unlock') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Unlock',
  `added_by` int(11) NOT NULL,
  `added_date` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_date` datetime NOT NULL,
  `action_microtime` varchar(20) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

你知道你要看整整8天吗？如果你只想要一个星期，就换衣服 <= 至 < .
将所有表切换到innodb。
有一个 PRIMARY KEY 对于每个表——要么是一个“自然”主键（由一个或多个列组成，这些列一起唯一地定义每一行），要么是一个 AUTO_INCREMENT . dti 需要 INDEX(status, toll_date) 不要使用 (m,n) 在 FLOAT ，它会导致额外的舍入。
不要使用 FLOAT 为了钱，它导致了一个舍近求远。 FLOAT （带或不带） (m,n) )包含的有效数字不超过7位。
考虑 DECIMAL(11,2) 而不是 float(11,2) .
小心使用 latin1 在一张table上 utf8 如果你需要的话 JOIN 在 VARCHAR ; 它必须具有相同的字符集和排序规则才能使用索引。
在可行的情况下，使 ORDER BY 与 GROUP BY .
这些可能有助于业绩的综合指数：

dti:  INDEX(status, toll_date)
ld:   INDEX(status, deducted_for, from_date)
ld:   INDEX(status, deducted_for, to_date)
wi:   INDEX(status, MainGroup, FishermanId, from_date)
wi:   INDEX(status, MainGroup, FishermanId, to_date)
cdp:  INDEX(status, maingroup_id, fisherman_id, deposit_date)

（日期必须是最后一天；其他列可以按任何顺序排列。）
如果在添加了这些索引之后，子查询仍然存在性能问题，那么让我们看看 EXPLAIN SELECT ... 所以我们可以再看看。
不要在列之间展开数组：

`Cqty` varchar(150) NOT NULL,
  `Cwt` varchar(150) NOT NULL,
  etc

考虑拥有 qty 以及 wt 作为另一个表中的两列。
有两个不同的值吗 MajorFee 在一天内为一家公司工作？还有其他一些东西说 GROUP BY 格式不正确。

我想优化mysql查询

1条答案

相关问题

热门标签

最新问答