时间戳

首页 > 解决方案 > 我想优化 mysql 查询

mysql - 我想优化 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_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_liability_deduction 表

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_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;

psac_cash_deposited_pa​​yment 表 

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;

标签： mysqlquery-optimization

解决方案

  • 你明白你正在看整整8天吗？如果您只想要一周，请更改<=为<.
  • 将所有表切换到 InnoDB。
  • 每个表都有一个PRIMARY KEY- 一个“自然”PK（由一个或多个列组成，它们共同唯一地定义每一行），或者一个AUTO_INCREMENT.
  • dti需要INDEX(status, toll_date)
  • 不要使用(m,n)on FLOAT，它会导致额外的舍入。
  • 不要FLOAT用来赚钱，它会导致四舍五入。
  • FLOAT（带或不带(m,n)）包含不超过 7 个有效数字。
  • 考虑DECIMAL(11,2)代替float(11,2).
  • latin1在一张桌子和另一张桌子上使用时要小心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

考虑将qtyandwt作为另一个表中的两列。

MajorFee一家公司在一天内可能有两个不同的值吗？那和其他事情说GROUP BY是不正确的。

推荐阅读