mysql：连接表性能低下

8qgya5xd  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(369)

我有这3个表，但是当我加入ref\u practice\u invoice\u payment时，查询需要更长的时间（大约5秒++）。
查询如下：

SELECT * 
  FROM practice_invoice_detail PID 
  LEFT 
  JOIN ref_practice_invoice_payment RPIP 
    ON RPIP.timestamp = PID.timestamp 
   AND RPIP.practice_id = PID.practice_id
   AND RPIP.refunded <> 1 
   AND RPIP.other_bill = 0 
  JOIN practice_invoice_header PIH 
    ON PIH.timestamp = PID.timestamp 
   AND PIH.practice_id = PID.practice_id 
   AND PIH.is_active = 1
WHERE PIH.source = 'E' 
   AND PID.practice_id = 28618 
   AND (
         (RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200) 
       OR (PIH.cal_id >= 201805130 AND PIH.cal_id <= 201805200 AND PIH.total_invoice = 0 AND PID.item_comission_type <> '%')
           )

模式如下：

CREATE TABLE `practice_invoice_header` (
 `timestamp` bigint(20) NOT NULL,
 `practice_id` int(11) NOT NULL,
 `cal_id` int(11) NOT NULL,
 `source` char(1) NOT NULL COMMENT 'E = ENCOUNTER; P = OTHER (PHARM / LAB)',
 `total_invoice` float NOT NULL DEFAULT '0',
 `total_procedure` float NOT NULL DEFAULT '0',
 `total_pharmacy` float NOT NULL DEFAULT '0',
 `extra_charge_ph` float NOT NULL DEFAULT '0',
 `total_lab` float NOT NULL DEFAULT '0',
 `total_voucher` float NOT NULL DEFAULT '0',
 `total_base` float NOT NULL DEFAULT '0',
 `procedure_base` float NOT NULL DEFAULT '0',
 `pharmacy_base` float NOT NULL DEFAULT '0',
 `lab_base` float NOT NULL DEFAULT '0',
 `clinic_share` float NOT NULL DEFAULT '0',
 `tax` float NOT NULL DEFAULT '0',
 `other_bill` float NOT NULL DEFAULT '0',
 `changed` float NOT NULL DEFAULT '0',
 `paid` float NOT NULL DEFAULT '0',
 `covered_amount` float NOT NULL DEFAULT '0',
 `reff_id` bigint(20) NOT NULL,
 `notes` varchar(300) DEFAULT NULL,
 `custom_invnum` varchar(30) DEFAULT NULL,
 `insurance_plan_id` varchar(20) DEFAULT NULL,
 `outpx_id` bigint(20) DEFAULT NULL,
 `is_active` int(11) NOT NULL DEFAULT '1',
 `cancel_reason` varchar(200) DEFAULT NULL,
 `pharm_read` int(11) NOT NULL DEFAULT '0',
 `lab_read` int(11) NOT NULL DEFAULT '0',
 `rad_read` int(11) NOT NULL DEFAULT '0',
 `ph_checked_by` int(11) NOT NULL DEFAULT '0',
 `ph_checked_time` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`timestamp`,`practice_id`),
 KEY `source` (`source`),
 KEY `reff_id` (`reff_id`),
 KEY `practice_id` (`practice_id`),
 KEY `timestamp` (`timestamp`),
 KEY `is_active` (`is_active`),
 KEY `custom_invnum` (`custom_invnum`),
 KEY `insurance_plan_id` (`insurance_plan_id`),
 KEY `practice_id_3` (`practice_id`,`reff_id`),
 KEY `ph_check_status` (`ph_checked_by`),
 KEY `cal_id` (`cal_id`),
 KEY `outpx_id` (`outpx_id`),
 KEY `practice_id_8` (`practice_id`,`source`,`reff_id`,`is_active`),
 KEY `total_invoice` (`total_invoice`),
 CONSTRAINT `practice_invoice_header_ibfk_1` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `practice_invoice_detail` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `timestamp` bigint(20) NOT NULL,
 `practice_id` int(11) NOT NULL,
 `item_id` int(11) NOT NULL,
 `item_sub_id` int(11) DEFAULT NULL,
 `item_type` char(1) NOT NULL COMMENT 'D = DRUG; P = PROCEDURE; L = LAB',
 `item_qty` float NOT NULL,
 `item_price` float(22,2) NOT NULL,
 `discount` float NOT NULL DEFAULT '0',
 `item_comission` float NOT NULL DEFAULT '0',
 `item_comission_type` char(1) NOT NULL DEFAULT '%',
 `doctor_id` int(11) NOT NULL DEFAULT '0',
 `item_comission_2` float NOT NULL DEFAULT '0',
 `item_comission_2_type` char(1) NOT NULL DEFAULT '%',
 `doctor_id_2` int(11) NOT NULL DEFAULT '0',
 `item_base_price` float(22,2) NOT NULL DEFAULT '0.00',
 `extra_base` float(22,2) NOT NULL DEFAULT '0.00',
 `clinic_share` float NOT NULL DEFAULT '0',
 `extra_charge` float NOT NULL DEFAULT '0',
 `referred_by` varchar(30) DEFAULT NULL,
 `referred_type` char(1) DEFAULT NULL,
 `referred_comission` float NOT NULL DEFAULT '0',
 `pm_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `item_type` (`item_type`),
 KEY `timestamp` (`timestamp`,`practice_id`),
 KEY `practice_id` (`practice_id`),
 KEY `item_id_2` (`item_id`,`item_sub_id`,`item_type`),
 KEY `pm_id` (`pm_id`),
 KEY `timestamp_2` (`timestamp`,`practice_id`,`item_id`,`item_sub_id`,`item_type`),
 KEY `practice_id_2` (`practice_id`,`referred_by`,`referred_type`),
 KEY `practice_id_3` (`practice_id`,`item_type`),
 KEY `the_id` (`id`,`practice_id`) USING BTREE,
 KEY `item_comission_type` (`item_comission_type`),
 KEY `item_comission` (`item_comission`),
 KEY `doctor_id` (`doctor_id`),
 KEY `item_comission_2` (`item_comission_2`),
 KEY `item_comission_2_type` (`item_comission_2_type`),
 KEY `doctor_id_2` (`doctor_id_2`),
 KEY `group_id` (`id`,`timestamp`,`practice_id`) USING BTREE,
 KEY `timestamp_3` (`timestamp`,`practice_id`,`item_type`,`item_comission`,`item_comission_type`,`doctor_id`,`item_id`,`item_sub_id`,`id`) USING BTREE,
 KEY `timestamp_4` (`timestamp`,`practice_id`,`item_id`,`item_sub_id`,`item_type`,`item_comission_2`,`item_comission_2_type`,`doctor_id_2`,`id`) USING BTREE,
 CONSTRAINT `practice_invoice_detail_ibfk_1` FOREIGN KEY (`timestamp`) REFERENCES `practice_invoice_header` (`timestamp`) ON DELETE CASCADE,
 CONSTRAINT `practice_invoice_detail_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=968763 DEFAULT CHARSET=latin1

CREATE TABLE `ref_practice_invoice_payment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `timestamp` bigint(20) NOT NULL,
 `practice_id` int(11) NOT NULL,
 `payment_method` int(11) NOT NULL,
 `pay` float NOT NULL,
 `changed` float NOT NULL DEFAULT '0',
 `extra_amount` float NOT NULL DEFAULT '0',
 `pay_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `pay_cal_id` int(11) NOT NULL DEFAULT '0',
 `user_id` int(11) NOT NULL,
 `user_type` int(11) NOT NULL,
 `refunded` int(11) NOT NULL DEFAULT '0',
 `payment_note` varchar(200) DEFAULT NULL,
 `extra` varchar(15) DEFAULT NULL,
 `other_bill` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `timestamp` (`timestamp`),
 KEY `practice_id` (`practice_id`),
 KEY `payment_method` (`payment_method`),
 KEY `user_id` (`user_id`),
 KEY `user_type` (`user_type`),
 KEY `timestamp_2` (`timestamp`,`practice_id`),
 KEY `refunded` (`refunded`),
 KEY `pay` (`pay`),
 KEY `extra_amount` (`extra_amount`),
 KEY `extra` (`extra`),
 KEY `pay_date` (`pay_date`),
 KEY `pay_cal_id` (`pay_cal_id`),
 KEY `other_bill` (`other_bill`),
 KEY `timestamp_3` (`timestamp`,`practice_id`,`refunded`,`other_bill`,`pay_cal_id`) USING BTREE,
 CONSTRAINT `ref_practice_invoice_payment_ibfk_1` FOREIGN KEY (`timestamp`) REFERENCES `practice_invoice_header` (`timestamp`) ON DELETE CASCADE,
 CONSTRAINT `ref_practice_invoice_payment_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=255913 DEFAULT CHARSET=latin1

下面是对上述问题的解释

1   SIMPLE  PIH ref PRIMARY,source,practice_id,timestamp,is_active,practice_id_3,cal_id,practice_id_5,practice_id_6,practice_id_2,practice_id_4,practice_id_7,practice_id_8,total_invoice   practice_id_6   4   const   40228   Using index condition   
1   SIMPLE  PID ref timestamp,practice_id,timestamp_2,practice_id_2,practice_id_3,item_comission_type,timestamp_3,timestamp_4   timestamp   12  k6064619_lokadok.PIH.timestamp,const    1       
1   SIMPLE  RPIP    ref timestamp,practice_id,timestamp_2,refunded,other_bill,timestamp_3   timestamp   8   k6064619_lokadok.PIH.timestamp  1   Using where

连接查询很简单，但是查询花费的时间有点太长。不过，我想数据虽然很多，但还是不错的。约200000行
进一步发现：奇怪的是，如果我删除这部分，查询速度很快（低于1秒）

(RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200)

@电子琴
以下是对建议查询的解释：

1   SIMPLE  practice_invoice_header index_merge PRIMARY,source,practice_id,timestamp,is_active,practice_id_3,cal_id,practice_id_5,practice_id_6,practice_id_2,practice_id_4,practice_id_7,practice_id_8,total_invoice,practice_id_9 practice_id_9,practice_id   9,4 NULL    7422    Using intersect(practice_id_9,practice_id); Using where 
1   SIMPLE  practice_invoice_detail ref timestamp,practice_id,timestamp_2,practice_id_2,practice_id_3,item_comission_type,timestamp_3,timestamp_4   timestamp   12  k6064619_lokadok.practice_invoice_header.timestamp,const    1       
1   SIMPLE  ref_practice_invoice_payment    ref timestamp,practice_id,timestamp_2,refunded,other_bill,timestamp_3,practice_id_2 timestamp   8   k6064619_lokadok.practice_invoice_header.timestamp  1   Using where
mysqlJoinperformanceexplain

2条答案

按热度按时间
ac1kyiln

ac1kyiln1#

部分帮助：

PIH:  INDEX(timestamp, source, is_active, total_invoice, cal_id)
PID:  INDEX(practice_id, item commission_type)
RPIP: INDEX(practice_id, timestamp, other_bill, pay_cal_id)
``` `(RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200)` --当然，这可能很快。但就其本身而言，它使用的是一个索引 `pay_cal_id` . 当放入真正的查询时，在 `OR` ，索引无效。所以，让我们把 `OR` 变成一个 `UNION` :

( SELECT ...
FROM ...
WHERE PIH.source = 'E'
AND PID.practice_id = 28618
AND RPIP.pay_cal_id >= 201805130
AND RPIP.pay_cal_id < 201805200
) UNION
( SELECT ...
FROM ...
WHERE PIH.source = 'E'
AND PID.practice_id = 28618
AND PIH.cal_id >= 201805130
AND PIH.cal_id < 201805200
AND PIH.total_invoice = 0
AND PID.item_comission_type <> '%'
)

什么时候 `EXPLAIN` 说“指数合并”，它几乎总是可以通过制作一个综合指数加以改进。
赞(0）分享  回复(0）举报  2021-06-20
2q5ifsrm

2q5ifsrm2#

上面的查询可以写成：

SELECT * FROM 
(SELECT * FROM practice_invoice_detail WHERE practice_id = 28618 ) as PID 

LEFT JOIN 
(SELECT * FROM ref_practice_invoice_payment WHERE practice_id = 28618 AND refunded <> 1 AND other_bill = 0 ) as RPIP 
ON RPIP.timestamp = PID.timestamp 

JOIN 
(SELECT * FROM practice_invoice_header WHERE practice_id = 28618 AND is_active = 1 AND source = 'E' ) as PIH 
ON PIH.timestamp = PID.timestamp 

WHERE 
( (RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200) 
OR (PIH.cal_id >= 201805130 AND PIH.cal_id <= 201805200 AND PIH.total_invoice = 0 AND PID.item_comission_type <> '%') ) ;

请尝试添加索引（practice\u id，returned，other\u bill）和（practice\u id，is\u active，source），并在节目后解释结果。

赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com