我有这3个表,但是当我加入ref_practice_invoice_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
联接查询全部为SIMPLE,但是查询花费的时间太短。但是,我想数据很多,但仍然不错。约200.000行
进一步的发现:
奇怪的是,如果我删除了这部分,查询速度很快(不到1秒)
(RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200)
@Eperbab
以下是建议查询的说明:
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
最佳答案
部分帮助:
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
说“索引合并”时,几乎总是可以通过创建复合索引来对其进行改进。