mysql：仍然使用filesort，使用temporary，即使正确添加了索引

我有这个问题，我想我已经索引他们正确。但仍然得到文件排序和临时索引。
查询如下：

SELECT * FROM
    (SELECT PIH.timestamp, PIH.practice_id, PIH.timestamp as invoice_num, PIH.custom_invnum,
CEIL(PIH.total_invoice + PIH.tax + PIH.other_bill)  as grand_total, PIH.total_invoice, PIH.extra_charge_ph as extra_charge,
PIH.tax, PIH.other_bill, PIH.changed, PIH.source,
PIH.notes, PIH.is_active, PIH.paid as pay,
PIH.covered_amount, IF(PIH.is_active = 1, IF(PIH.total_invoice = 0 OR PIH.total_invoice + PIH.tax + PIH.other_bill - PIH.covered_amount <= PIH.paid, 1, IF(PIH.paid = 0, 0, 2)), '')  as invoice_st,
RPP.patient_id, RPP.first_name as pfname, RPP.last_name as plname, RPP.dob as p_dob, RPP.gender as p_gender, RPP.reff_id as p_reff_id, RPP.mobile_number as p_mobile, IF(PIH.group_doctors IS NOT NULL, NULL, D.doc_title) as doc_title, IF(PIH.group_doctors IS NOT NULL,
PIH.group_doctors, D.first_name) as doc_fname, IF(PIH.group_doctors IS NOT NULL, PIH.group_doctors, D.last_name) as doc_lname, IF(PIH.group_doctors IS NOT NULL, NULL, D.spc_dsg) as spc_dsg, PA.username, TL.timestamp as checkout_time, IP.name as ip_name, PMM.timestamp as mcu_id
            FROM  practice_invoice_header PIH
            INNER JOIN  practice_invoice_detail PID  ON PID.timestamp = PIH.timestamp
              AND  PID.practice_id = PIH.practice_id
            INNER JOIN  practice_queue_list PQL  ON PQL.encounter_id = PID.encounter_id
              AND  PQL.practice_place_id = PIH.practice_id
            INNER JOIN  temp_search_view D  ON D.id = PQL.doctor_id
              AND  D.pp_id = PQL.practice_place_id
            INNER JOIN  practice_place PP  ON PP.id = PIH.practice_id
            INNER JOIN  ref_practice_patient RPP  ON RPP.patient_id = PIH.patient_id
              AND  RPP.practice_id = PP.parent_id
            LEFT JOIN  practice_mcu_module PMM  ON PMM.id = PID.mcu_module_id
              AND  PMM.practice_id = PID.practice_id
            LEFT JOIN  transaction_log TL  ON TL.reff_id = PIH.timestamp
              AND  TL.practice_id = PIH.practice_id
              AND  TL.activity = "CHK"
            LEFT JOIN  practice_admin PA  ON PA.id = TL.admin_id
            LEFT JOIN  insurance_plan IP  ON IP.id = PIH.insurance_plan_id
            WHERE  PIH.source <> 'P'
              AND  PIH.practice_id = 28699
              AND  PIH.is_active = 1
              AND  PQL.cal_id >= 201807010
              AND  PQL.cal_id <= 201807312
            GROUP BY  PIH.timestamp, PIH.practice_id 
    ) AS U  LIMIT 0,20

注意：我只显示了一些在这个查询中使用的主表和那些使用filesort/temporary进行排序的表，当然，如果我发布了所有内容，那么会有太多的信息。
查询是关于发票列表的，它有标题（practice\u invoice\u header）和详细信息（practice\u invoice\u detail）。此查询与practice\u place表连接

CREATE TABLE `practice_invoice_header` (
 `timestamp` bigint(20) NOT NULL,
 `practice_id` int(11) NOT NULL,
 `cal_id` int(11) NOT NULL,
 `patient_id` int(11) NOT NULL DEFAULT 0,
 `source` char(1) NOT NULL COMMENT 'E = ENCOUNTER; P = OTHER (PHARM / LAB)',
 `total_invoice` float(30,2) NOT NULL DEFAULT 0.00,
 `tax` float(30,2) NOT NULL DEFAULT 0.00,
 `other_bill` float(30,2) NOT NULL DEFAULT 0.00,
 `changed` float(30,2) NOT NULL DEFAULT 0.00,
 `paid` float(30,2) NOT NULL DEFAULT 0.00,
 `covered_amount` float(30,2) NOT NULL DEFAULT 0.00,
 `notes` varchar(300) DEFAULT NULL,
 `custom_invnum` varchar(30) DEFAULT NULL,
 `insurance_plan_id` varchar(20) DEFAULT NULL,
 `is_active` int(11) NOT NULL DEFAULT 1,
 `cancel_reason` varchar(200) DEFAULT NULL,
 PRIMARY KEY (`timestamp`,`practice_id`),
 KEY `custom_invnum` (`custom_invnum`),
 KEY `insurance_plan_id` (`insurance_plan_id`),
 KEY `practice_id_3` (`practice_id`,`xxx_reff_id`),
 KEY `ph_check_status` (`ph_checked_by`),
 KEY `cal_id` (`cal_id`),
 KEY `practice_id_5` (`practice_id`,`outpx_id`),
 KEY `practice_id_6` (`practice_id`,`cal_id`,`source`,`is_active`),
 KEY `total_invoice` (`total_invoice`),
 KEY `patient_id` (`patient_id`),
 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,
 `is_active` int(11) NOT NULL DEFAULT 1,
 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 `timestamp_2` (`timestamp`,`practice_id`,`item_id`,`item_sub_id`,`item_type`),
 KEY `practice_id_3` (`practice_id`,`item_type`),
 KEY `the_id` (`id`,`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,
 KEY `request_id` (`request_id`,`request_practice`),
 KEY `timestamp_5` (`timestamp`,`practice_id`,`is_active`),
 KEY `practice_id_6` (`practice_id`,`encounter_id`,`is_active`),
 KEY `practice_id_7` (`practice_id`,`item_type`,`encounter_id`,`is_active`),
 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=1447348 DEFAULT CHARSET=latin1

CREATE TABLE `ref_practice_patient` (
 `practice_id` int(11) NOT NULL,
 `patient_id` int(11) NOT NULL,
 `reff_id` varchar(35) DEFAULT NULL,
 `is_user` int(11) NOT NULL DEFAULT 0,
 `parent_user_id` int(11) NOT NULL DEFAULT 0
 PRIMARY KEY (`practice_id`,`patient_id`),
 KEY `patient_id` (`patient_id`),
 KEY `reff_id` (`reff_id`),
 KEY `practice_id` (`practice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `practice_place` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(75) NOT NULL,
 `statement` text DEFAULT NULL,
 `address` varchar(200) NOT NULL,
 `phone` varchar(15) NOT NULL,
 `wa_number` varchar(15) DEFAULT NULL,
 `fax` varchar(15) NOT NULL,
 `email` varchar(50) NOT NULL,
 `is_branch` int(11) NOT NULL,
 `parent_id` int(11) NOT NULL,
 `editted_by` int(11) DEFAULT NULL,
 `editted_date` bigint(20) DEFAULT NULL,
 `status` int(11) NOT NULL DEFAULT 1,
 PRIMARY KEY (`id`),
 KEY `parent_id` (`parent_id`),
 KEY `reff_id` (`reff_id`),
) ENGINE=InnoDB AUTO_INCREMENT=29058 DEFAULT CHARSET=latin1

下面是查询产生的解释结果，我突出显示了使用filsort的结果（第2个）

1 PRIMARY ALL NULL  NULL NULL NULL  14028
2 DERIVED PP  const PRIMARY,parent_id PRIMARY 4 const 1 Using temporary; Using filesort
2 DERIVED PIH ref   PRIMARY,practice_id_3,practice_id_5,practice_id_6,practice_id_8,pharm_read,lab_read,rad_read,patient_id
                   practice_id_5 4 const  7014 Using where 
2 DERIVED RPP eq_ref PRIMARY,patient_id,practice_id,practice_id_2,practice_id_3 
                   PRIMARY 8 const,k6064619_lokadok.PIH.patient_id  1
2 DERIVED PID ref   timestamp,practice_id,timestamp_2,practice_id_2,practice_id_3,timestamp_3,timestamp_4,practice_id_4,practice_id_5,timestamp_5,practice_id_6,practice_id_7
                   timestamp 12 k6064619_lokadok.PIH.timestamp,const  1 
2 DERIVED PMM eq_ref PRIMARY,id,practice_id
                   PRIMARY 4 k6064619_lokadok.PID.mcu_module_id  1 Using where 
2 DERIVED TL  ref   reff_id reff_id 12 k6064619_lokadok.PIH.timestamp,const  1 Using where 
2 DERIVED PA  eq_ref PRIMARY PRIMARY 4 k6064619_lokadok.TL.admin_id  1 Using where 
2 DERIVED IP  ref   PRIMARY,id PRIMARY 22 k6064619_lokadok.PIH.insurance_plan_id  1 Using where 
2 DERIVED PQL ref   PRIMARY,encounter_id,cal_id_2
                   encounter_id 5 k6064619_lokadok.PID.encounter_id  2 Using where; Using index
2 DERIVED D   ref   doc_id,pp_id,id_2,pp_doc doc_id 4 k6064619_lokadok.PQL.doctor_id  1 Using where

我相信我已经在practice\u place表中索引了parent\u id，在ref\u practice\u patient中，patient\u id和practice\u id是主要的。

为什么有外部查询？优化器可以随意洗牌内部查询的结果，从而保留 LIMIT 选择一个你不期望的订单。至少添加 ORDER BY ，最好也选择抛外侧。
主要指标
让我们分析一下设计索引的可能位置：

WHERE  PIH.source <> 'P'
          AND  PIH.practice_id = 28699
          AND  PIH.is_active = 1
          AND  PQL.cal_id >= 201807010
          AND  PQL.cal_id <= 201807312
        GROUP BY  PIH.timestamp, PIH.practice_id

由于所涉及的表是混合的，所以不可能有一个处理所有表的索引 WHERE .
因为测试不是全部 = ，不可能超越 WHERE 并包括 GROUP BY .
所以，我看到两个索引：

PIH:  INDEX(practice_id, is_active,   -- in either order
            source)
PQL:  INDEX(cal_id)

因为我们进不了监狱 GROUP BY ，优化器别无选择，只能基于 WHERE ，进行分组，然后 ORDER BY （如我所说，这是缺失的，但却是必要的）。
因此， GROUP BY 以及 ORDER BY 将需要一个或两个temp和filesort。不，你不能逃避它，至少不能不以某种方式改变查询(请注意，“文件排序”实际上可能是在ram中完成的。）
你额外的 SELECT 层可能正在添加一个额外的temp和filesort。 EXPLAIN 没有指出什么时候有两种。 EXPLAIN FORMAT=JSON 有那种细节。
其他问题。。。
有一个 timestamp 在一个 PRIMARY KEY 是有风险的，除非您确定两行可以使用相同的时间戳出现，或者pk中有另一列来确保唯一性。
不要使用 FLOAT 为了钱。它会产生额外的舍入错误，并且不能存储超过7个有效数字（一便士不到10万美元）。不要使用 float(30,2) ，更糟糕的是，因为您正在强制执行额外的舍入。使用 DECIMAL(30,2) ，但选择一些合理的，而不是30。它需要14个字节——主要是浪费空间。
只要你有 INDEX(a,b) ，你不需要 INDEX(a) ; 它是多余的，速度慢了（稍微慢一点） INSERTs .

LEFT JOIN  transaction_log TL
           ON  TL.reff_id = PIH.timestamp
          AND  TL.practice_id = PIH.practice_id
          AND  TL.activity = "CHK"

需要

INDEX(reff_id, practice_id, activity)  -- in any order

也

INNER JOIN  practice_invoice_detail PID  ON PID.timestamp = PIH.timestamp
          AND  PID.practice_id = PIH.practice_id

PIH:  INDEX(practice_id, timestamp)   -- not the opposite order
PIH:  INDEX(practice_id, is_active, timestamp)

        INNER JOIN  practice_queue_list PQL  ON PQL.encounter_id = PID.encounter_id
          AND  PQL.practice_place_id = PIH.practice_id

PQL:  INDEX(encounter_id, cal_id)
PQL:  INDEX(encounter_id, practice_place_id, cal_id)

一些讨论。。。
在一个 JOIN , EXPLAIN 显示表格的一个工作顺序；它没有给你任何线索，如果它以其他方式在表格中运行，情况会怎样。
我试图说明，如果先使用pql，或者先使用pih，可能需要什么索引——即使用 WHERE 那table的东西呢
我试图显示连接到另一个表的最佳索引。
可能优化器不会从中没有提到的任何表开始 WHERE 但这不是必然的。
我没有列出访问其他每个表的最佳索引。
更多讨论：http://mysql.rjweb.org/doc.php/index_cookbook_mysql

mysql：仍然使用filesort，使用temporary，即使正确添加了索引

1条答案

相关问题

热门标签

最新问答