mysql：连接表性能低下

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

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

  1. SELECT * 
  2.   FROM practice_invoice_detail PID 
  3.   LEFT 
  4.   JOIN ref_practice_invoice_payment RPIP 
  5.     ON RPIP.timestamp = PID.timestamp 
  6.    AND RPIP.practice_id = PID.practice_id
  7.    AND RPIP.refunded <> 1 
  8.    AND RPIP.other_bill = 0 
  9.   JOIN practice_invoice_header PIH 
  10.     ON PIH.timestamp = PID.timestamp 
  11.    AND PIH.practice_id = PID.practice_id 
  12.    AND PIH.is_active = 1
  13. WHERE PIH.source = 'E' 
  14.    AND PID.practice_id = 28618 
  15.    AND (
  16.          (RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200) 
  17.        OR (PIH.cal_id >= 201805130 AND PIH.cal_id <= 201805200 AND PIH.total_invoice = 0 AND PID.item_comission_type <> '%')
  18.            )

模式如下：

  1. CREATE TABLE `practice_invoice_header` (
  2.  `timestamp` bigint(20) NOT NULL,
  3.  `practice_id` int(11) NOT NULL,
  4.  `cal_id` int(11) NOT NULL,
  5.  `source` char(1) NOT NULL COMMENT 'E = ENCOUNTER; P = OTHER (PHARM / LAB)',
  6.  `total_invoice` float NOT NULL DEFAULT '0',
  7.  `total_procedure` float NOT NULL DEFAULT '0',
  8.  `total_pharmacy` float NOT NULL DEFAULT '0',
  9.  `extra_charge_ph` float NOT NULL DEFAULT '0',
  10.  `total_lab` float NOT NULL DEFAULT '0',
  11.  `total_voucher` float NOT NULL DEFAULT '0',
  12.  `total_base` float NOT NULL DEFAULT '0',
  13.  `procedure_base` float NOT NULL DEFAULT '0',
  14.  `pharmacy_base` float NOT NULL DEFAULT '0',
  15.  `lab_base` float NOT NULL DEFAULT '0',
  16.  `clinic_share` float NOT NULL DEFAULT '0',
  17.  `tax` float NOT NULL DEFAULT '0',
  18.  `other_bill` float NOT NULL DEFAULT '0',
  19.  `changed` float NOT NULL DEFAULT '0',
  20.  `paid` float NOT NULL DEFAULT '0',
  21.  `covered_amount` float NOT NULL DEFAULT '0',
  22.  `reff_id` bigint(20) NOT NULL,
  23.  `notes` varchar(300) DEFAULT NULL,
  24.  `custom_invnum` varchar(30) DEFAULT NULL,
  25.  `insurance_plan_id` varchar(20) DEFAULT NULL,
  26.  `outpx_id` bigint(20) DEFAULT NULL,
  27.  `is_active` int(11) NOT NULL DEFAULT '1',
  28.  `cancel_reason` varchar(200) DEFAULT NULL,
  29.  `pharm_read` int(11) NOT NULL DEFAULT '0',
  30.  `lab_read` int(11) NOT NULL DEFAULT '0',
  31.  `rad_read` int(11) NOT NULL DEFAULT '0',
  32.  `ph_checked_by` int(11) NOT NULL DEFAULT '0',
  33.  `ph_checked_time` bigint(20) DEFAULT NULL,
  34.  PRIMARY KEY (`timestamp`,`practice_id`),
  35.  KEY `source` (`source`),
  36.  KEY `reff_id` (`reff_id`),
  37.  KEY `practice_id` (`practice_id`),
  38.  KEY `timestamp` (`timestamp`),
  39.  KEY `is_active` (`is_active`),
  40.  KEY `custom_invnum` (`custom_invnum`),
  41.  KEY `insurance_plan_id` (`insurance_plan_id`),
  42.  KEY `practice_id_3` (`practice_id`,`reff_id`),
  43.  KEY `ph_check_status` (`ph_checked_by`),
  44.  KEY `cal_id` (`cal_id`),
  45.  KEY `outpx_id` (`outpx_id`),
  46.  KEY `practice_id_8` (`practice_id`,`source`,`reff_id`,`is_active`),
  47.  KEY `total_invoice` (`total_invoice`),
  48.  CONSTRAINT `practice_invoice_header_ibfk_1` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
  49. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  51. CREATE TABLE `practice_invoice_detail` (
  52.  `id` int(11) NOT NULL AUTO_INCREMENT,
  53.  `timestamp` bigint(20) NOT NULL,
  54.  `practice_id` int(11) NOT NULL,
  55.  `item_id` int(11) NOT NULL,
  56.  `item_sub_id` int(11) DEFAULT NULL,
  57.  `item_type` char(1) NOT NULL COMMENT 'D = DRUG; P = PROCEDURE; L = LAB',
  58.  `item_qty` float NOT NULL,
  59.  `item_price` float(22,2) NOT NULL,
  60.  `discount` float NOT NULL DEFAULT '0',
  61.  `item_comission` float NOT NULL DEFAULT '0',
  62.  `item_comission_type` char(1) NOT NULL DEFAULT '%',
  63.  `doctor_id` int(11) NOT NULL DEFAULT '0',
  64.  `item_comission_2` float NOT NULL DEFAULT '0',
  65.  `item_comission_2_type` char(1) NOT NULL DEFAULT '%',
  66.  `doctor_id_2` int(11) NOT NULL DEFAULT '0',
  67.  `item_base_price` float(22,2) NOT NULL DEFAULT '0.00',
  68.  `extra_base` float(22,2) NOT NULL DEFAULT '0.00',
  69.  `clinic_share` float NOT NULL DEFAULT '0',
  70.  `extra_charge` float NOT NULL DEFAULT '0',
  71.  `referred_by` varchar(30) DEFAULT NULL,
  72.  `referred_type` char(1) DEFAULT NULL,
  73.  `referred_comission` float NOT NULL DEFAULT '0',
  74.  `pm_id` int(11) NOT NULL DEFAULT '0',
  75.  PRIMARY KEY (`id`),
  76.  KEY `item_type` (`item_type`),
  77.  KEY `timestamp` (`timestamp`,`practice_id`),
  78.  KEY `practice_id` (`practice_id`),
  79.  KEY `item_id_2` (`item_id`,`item_sub_id`,`item_type`),
  80.  KEY `pm_id` (`pm_id`),
  81.  KEY `timestamp_2` (`timestamp`,`practice_id`,`item_id`,`item_sub_id`,`item_type`),
  82.  KEY `practice_id_2` (`practice_id`,`referred_by`,`referred_type`),
  83.  KEY `practice_id_3` (`practice_id`,`item_type`),
  84.  KEY `the_id` (`id`,`practice_id`) USING BTREE,
  85.  KEY `item_comission_type` (`item_comission_type`),
  86.  KEY `item_comission` (`item_comission`),
  87.  KEY `doctor_id` (`doctor_id`),
  88.  KEY `item_comission_2` (`item_comission_2`),
  89.  KEY `item_comission_2_type` (`item_comission_2_type`),
  90.  KEY `doctor_id_2` (`doctor_id_2`),
  91.  KEY `group_id` (`id`,`timestamp`,`practice_id`) USING BTREE,
  92.  KEY `timestamp_3` (`timestamp`,`practice_id`,`item_type`,`item_comission`,`item_comission_type`,`doctor_id`,`item_id`,`item_sub_id`,`id`) USING BTREE,
  93.  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,
  94.  CONSTRAINT `practice_invoice_detail_ibfk_1` FOREIGN KEY (`timestamp`) REFERENCES `practice_invoice_header` (`timestamp`) ON DELETE CASCADE,
  95.  CONSTRAINT `practice_invoice_detail_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
  96. ) ENGINE=InnoDB AUTO_INCREMENT=968763 DEFAULT CHARSET=latin1
  98. CREATE TABLE `ref_practice_invoice_payment` (
  99.  `id` int(11) NOT NULL AUTO_INCREMENT,
  100.  `timestamp` bigint(20) NOT NULL,
  101.  `practice_id` int(11) NOT NULL,
  102.  `payment_method` int(11) NOT NULL,
  103.  `pay` float NOT NULL,
  104.  `changed` float NOT NULL DEFAULT '0',
  105.  `extra_amount` float NOT NULL DEFAULT '0',
  106.  `pay_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  107.  `pay_cal_id` int(11) NOT NULL DEFAULT '0',
  108.  `user_id` int(11) NOT NULL,
  109.  `user_type` int(11) NOT NULL,
  110.  `refunded` int(11) NOT NULL DEFAULT '0',
  111.  `payment_note` varchar(200) DEFAULT NULL,
  112.  `extra` varchar(15) DEFAULT NULL,
  113.  `other_bill` int(11) NOT NULL DEFAULT '0',
  114.  PRIMARY KEY (`id`),
  115.  KEY `timestamp` (`timestamp`),
  116.  KEY `practice_id` (`practice_id`),
  117.  KEY `payment_method` (`payment_method`),
  118.  KEY `user_id` (`user_id`),
  119.  KEY `user_type` (`user_type`),
  120.  KEY `timestamp_2` (`timestamp`,`practice_id`),
  121.  KEY `refunded` (`refunded`),
  122.  KEY `pay` (`pay`),
  123.  KEY `extra_amount` (`extra_amount`),
  124.  KEY `extra` (`extra`),
  125.  KEY `pay_date` (`pay_date`),
  126.  KEY `pay_cal_id` (`pay_cal_id`),
  127.  KEY `other_bill` (`other_bill`),
  128.  KEY `timestamp_3` (`timestamp`,`practice_id`,`refunded`,`other_bill`,`pay_cal_id`) USING BTREE,
  129.  CONSTRAINT `ref_practice_invoice_payment_ibfk_1` FOREIGN KEY (`timestamp`) REFERENCES `practice_invoice_header` (`timestamp`) ON DELETE CASCADE,
  130.  CONSTRAINT `ref_practice_invoice_payment_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
  131. ) ENGINE=InnoDB AUTO_INCREMENT=255913 DEFAULT CHARSET=latin1

下面是对上述问题的解释

  1. 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   
  2. 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       
  3. 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秒）

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

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

  1. 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 
  2. 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       
  3. 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#

部分帮助：

  1. PIH:  INDEX(timestamp, source, is_active, total_invoice, cal_id)
  2. PID:  INDEX(practice_id, item commission_type)
  3. RPIP: INDEX(practice_id, timestamp, other_bill, pay_cal_id)
  4. ``` `(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 <> '%'
)

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

2q5ifsrm2#

上面的查询可以写成：

  1. SELECT * FROM 
  2. (SELECT * FROM practice_invoice_detail WHERE practice_id = 28618 ) as PID 
  4. LEFT JOIN 
  5. (SELECT * FROM ref_practice_invoice_payment WHERE practice_id = 28618 AND refunded <> 1 AND other_bill = 0 ) as RPIP 
  6. ON RPIP.timestamp = PID.timestamp 
  8. JOIN 
  9. (SELECT * FROM practice_invoice_header WHERE practice_id = 28618 AND is_active = 1 AND source = 'E' ) as PIH 
  10. ON PIH.timestamp = PID.timestamp 
  12. WHERE 
  13. ( (RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200) 
  14. 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