Mysql
MySQL 5.5.54 和 mariadb 5.5.56 查詢執行計劃的區別
我正在
SELECT2 台主機上執行“舊數據庫”執行mysql 5.5.54和“新數據庫”執行mariadb 5.5.56。執行時和EXPLAIN顯示都存在差異，mariadb 明顯較慢。“新數據庫”上的數據是“舊數據庫”的最新轉儲解釋“舊數據庫”：
explain SELECT b.tariff as tariff, b.benefitType as benefitType, b.name as name, b.code as code, SUM(b.effQuantPckType) as `count`, SUM(effCalcAmtVat) as `amount`, SUM(effAmt) as `amountAL`, SUM(effAmt2) as `amountTL` FROM benefitCombined AS b LEFT OUTER JOIN invoice AS inv ON inv.id = b.invoice WHERE (invoicePart != 'aaaaaaaaaaaaaaaaaaaa' OR invoice != 'aaaaaaaaaaaaaaaaaaaa') AND executingDoctor = 'dqwjvgbskiaaaacsonmj' AND b.begin >= '2018-06-16 00:00:00' AND b.begin <= '2018-07-16 23:59:57' AND 1=1 GROUP BY benefitType, code; +----+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 287344 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | invoiceBase | eq_ref | id | id | 20 | b.invoice | 1 | Using index | | 2 | DERIVED | benefitBase | ALL | NULL | NULL | NULL | NULL | 285328 | Using where | | 3 | UNION | benefitTarmed | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+ 5 rows in set (3.34 sec)在“舊數據庫”上顯示創建：
show create table invoice \G; *************************** 1. row *************************** View: invoice Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`c0515_dwh`@`%` SQL SECURITY DEFINER VIEW `invoice` AS ( select `invoiceBase`.`id` AS `id`,`invoiceBase`.`created` AS `created`, `invoiceBase`.`begin` AS `begin`,`invoiceBase`.`end` AS `end`, `invoiceBase`.`formatType` AS `formatType`,`invoiceBase`.`invUID` AS `invUID`, `invoiceBase`.`traStat` AS `traStat`,`invoiceBase`.`invStat` AS `invStat`, `invoiceBase`.`patient` AS `patient`,`invoiceBase`.`provider` AS `provider`, `invoiceBase`.`biller` AS `biller`,`invoiceBase`.`debitor` AS `debitor`, `invoiceBase`.`name` AS `name`,`invoiceBase`.`amtDue` AS `amtDue`, `invoiceBase`.`law` AS `law`,`invoiceBase`.`reason` AS `reason`, `invoiceBase`.`amtTotal` AS `amtTotal`,`invoiceBase`.`amtPrepaid` AS `amtPrepaid`, `invoiceBase`.`amtPaid` AS `amtPaid`,`invoiceBase`.`amtPaidDirect` AS `amtPaidDirect`, `invoiceBase`.`amtFee` AS `amtFee`,`invoiceBase`.`splitFac` AS `splitFac`, `invoiceBase`.`invoiceType` AS `invoiceType`,`invoiceBase`.`invoiceOptimal` AS `invoiceOptimal`, `invoiceBase`.`patientCopy` AS `patientCopy`,`invoiceBase`.`diagnoses` AS `diagnoses`, `invoiceBase`.`customerNumber` AS `customerNumber`,`invoiceBase`.`traStatDate` AS `traStatDate`, `invoiceBase`.`invStatDate` AS `invStatDate`,`invoiceBase`.`oldInvoiceNr` AS `oldInvoiceNr`, `invoiceBase`.`newInvoiceNr` AS `newInvoiceNr`,`invoiceBase`.`mandator` AS `mandator`, `invoiceBase`.`account` AS `account`,`invoiceBase`.`cancReqFlag` AS `cancReqFlag`, `invoiceBase`.`cancReqDate` AS `cancReqDate`,`invoiceBase`.`claimNr` AS `claimNr`, `invoiceBase`.`claimReqFlag` AS `claimReqFlag`,`invoiceBase`.`claimReqDate` AS `claimReqDate`, `invoiceBase`.`depReqFlag` AS `depReqFlag`,`invoiceBase`.`depReqDate` AS `depReqDate`, `invoiceBase`.`canton` AS `canton`,`invoiceBase`.`amtObligations` AS `amtObligations`, `invoiceBase`.`amtTax` AS `amtTax`,`invoiceBase`.`remarks` AS `remarks`, `invoiceBase`.`cancRemarks` AS `cancRemarks`,`invoiceBase`.`claimRemarks` AS `claimRemarks`, `invoiceBase`.`depRemarks` AS `depRemarks`,`invoiceBase`.`p02InvNr` AS `p02InvNr`, `invoiceBase`.`p02InvDate` AS `p02InvDate`,`invoiceBase`.`invoiceDate` AS `invoiceDate`, `invoiceBase`.`debMngmt` AS `debMngmt`, (round(((`invoiceBase`.`amtTotal` * `invoiceBase`.`splitFac`) * 20), 0) / 20) AS `calcAmtTotal`,round((`invoiceBase`.`amtPrepaid` * `invoiceBase`.`splitFac`), 2) AS `calcAmtPrepaid`,round((`invoiceBase`.`amtTax` * `invoiceBase`.`splitFac`), 2) AS `calcAmtTax`,round((`invoiceBase`.`amtObligations` * `invoiceBase`.`splitFac`), 2 ) AS `calcAmtObligations`, 2) + `invoiceBase`.`amtPaid`) + `invoiceBase`.`amtPaidDirect`) AS `calcAmtPaid`, character_set_client: latin1 collation_connection: latin1_swedish_ci…
show create table benefitCombined \G; *************************** 1. row *************************** View: benefitCombined Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`c0515_dwh`@`%` SQL SECURITY DEFINER VIEW `benefitCombined` AS ( select `benefitBase`.`id` AS `id`,`benefitBase`.`additionType` AS `additionType`, `benefitBase`.`end` AS `end`,`benefitBase`.`eanExecuting` AS `eanExecuting`, `benefitBase`.`eanResponsible` AS `eanResponsible`,`benefitBase`.`unitFac` AS `unitFac`, `benefitBase`.`extFac` AS `extFac`,`benefitBase`.`amountQty1` AS `amountQty1`, `benefitBase`.`orderNumber` AS `orderNumber`,`benefitBase`.`srvAtts` AS `srvAtts`, `benefitBase`.`tariff` AS `tariff`,`benefitBase`.`benefitType` AS `benefitType`, `benefitBase`.`code` AS `code`,`benefitBase`.`executingDoctor` AS `executingDoctor`, `benefitBase`.`responsibleDoctor` AS `responsibleDoctor`, `benefitBase`.`obligation` AS `obligation`,`benefitBase`.`refCode` AS `refCode`, `benefitBase`.`quantity` AS `quantity`,`benefitBase`.`effQuant` AS `effQuant`, `benefitBase`.`begin` AS `begin`,`benefitBase`.`unit` AS `unit`, `benefitBase`.`scaleFac` AS `scaleFac`,`benefitBase`.`amount` AS `amount`, `benefitBase`.`effAmt` AS `effAmt`,`benefitBase`.`vatRate` AS `vatRate`, `benefitBase`.`negObligation` AS `negObligation`,`benefitBase`.`sessionNr` AS `sessionNr`, `benefitBase`.`pckMult` AS `pckMult`,`benefitBase`.`pckType` AS `pckType`, `benefitBase`.`pckUnits` AS `pckUnits`,`benefitBase`.`srvMin` AS `srvMin`, `benefitBase`.`name` AS `name`,`benefitBase`.`invoicePart` AS `invoicePart`, `benefitBase`.`created` AS `created`,`benefitBase`.`session` AS `session`, `benefitBase`.`patient` AS `patient`,`benefitBase`.`medicalCase` AS `medicalCase`, `benefitBase`.`invoice` AS `invoice`,`benefitBase`.`owner` AS `owner`, 0 AS `unitFac2`,0 AS `extFac2`,0 AS `amount2Qty1`,NULL AS `bodyLoc`, 0 AS `unit2`,0 AS `scaleFac2`,0 AS `amount2`,0 AS `effAmt2`, `benefitBase`.`amount` AS `calcAmtVat`,`benefitBase`.`effAmt` AS `effCalcAmtVat`, (`benefitBase`.`amount` / (1 + (`benefitBase`.`vatRate` / 100))) AS `calcAmtWoVat`, (`benefitBase`.`effAmt` / (1 + (`benefitBase`.`vatRate` / 100))) AS `effCalcAmtWoVat`, NULL AS `calcMinTot`,NULL AS `effCalcMinTot`, ifnull(round((`benefitBase`.`effAmt` / nullif(`benefitBase`.`amountQty1`, 0)),3),0 ) AS `effQuantPckType` from `benefitBase` where (((`benefitBase`.`invoicePart` <> 'aaaaaaaaaaaaaaaaaaaa') and (`benefitBase`.`invoicePart` is not null)) or ((`benefitBase`.`invoice` <> 'aaaaaaaaaaaaaaaaaaaa') and (`benefitBase`.`invoice` is not null)) ) ) UNION all ( SELECT `benefitTarmed`.`id` AS `id`,`benefitTarmed`.`additionType` AS `additionType`, `benefitTarmed`.`end` AS `end`,`benefitTarmed`.`eanExecuting` AS `eanExecuting`, `benefitTarmed`.`eanResponsible` AS `eanResponsible`, `benefitTarmed`.`unitFac` AS `unitFac`,`benefitTarmed`.`extFac` AS `extFac`, `benefitTarmed`.`amountQty1` AS `amountQty1`,`benefitTarmed`.`orderNumber` AS `orderNumber`, `benefitTarmed`.`srvAtts` AS `srvAtts`,`benefitTarmed`.`tariff` AS `tariff`, `benefitTarmed`.`benefitType` AS `benefitType`,`benefitTarmed`.`code` AS `code`, `benefitTarmed`.`executingDoctor` AS `executingDoctor`, `benefitTarmed`.`responsibleDoctor` AS `responsibleDoctor`, `benefitTarmed`.`obligation` AS `obligation`,`benefitTarmed`.`refCode` AS `refCode`, `benefitTarmed`.`quantity` AS `quantity`,`benefitTarmed`.`effQuant` AS `effQuant`, `benefitTarmed`.`begin` AS `begin`,`benefitTarmed`.`unit` AS `unit`, `benefitTarmed`.`scaleFac` AS `scaleFac`,`benefitTarmed`.`amount` AS `amount`, `benefitTarmed`.`effAmt` AS `effAmt`,`benefitTarmed`.`vatRate` AS `vatRate`, `benefitTarmed`.`negObligation` AS `negObligation`,`benefitTarmed`.`sessionNr` AS `sessionNr`, `benefitTarmed`.`pckMult` AS `pckMult`,`benefitTarmed`.`pckType` AS `pckType`, `benefitTarmed`.`pckUnits` AS `pckUnits`,`benefitTarmed`.`srvMin` AS `srvMin`, `benefitTarmed`.`name` AS `name`,`benefitTarmed`.`invoicePart` AS `invoicePart`, `benefitTarmed`.`created` AS `created`,`benefitTarmed`.`session` AS `session`, `benefitTarmed`.`patient` AS `patient`,`benefitTarmed`.`medicalCase` AS `medicalCase`, `benefitTarmed`.`invoice` AS `invoice`,`benefitTarmed`.`owner` AS `owner`, `benefitTarmed`.`unitFac2` AS `unitFac2`,`benefitTarmed`.`extFac2` AS `extFac2`, `benefitTarmed`.`amount2Qty1` AS `amount2Qty1`,`benefitTarmed`.`bodyLoc` AS `bodyLoc`, `benefitTarmed`.`unit2` AS `unit2`,`benefitTarmed`.`scaleFac2` AS `scaleFac2`, `benefitTarmed`.`amount2` AS `amount2`,`benefitTarmed`.`effAmt2` AS `effAmt2`, (`benefitTarmed`.`amount` + `benefitTarmed`.`amount2`) AS `calcAmtVat`, (`benefitTarmed`.`effAmt` + `benefitTarmed`.`effAmt2`) AS `effCalcAmtVat`, ((`benefitTarmed`.`amount` + `benefitTarmed`.`amount2`) / (1 + (`benefitTarmed`.`vatRate` / 100))) AS `calcAmtWoVat`, ((`benefitTarmed`.`effAmt` + `benefitTarmed`.`effAmt2`) / (1 + (`benefitTarmed`.`vatRate` / 100))) AS `effCalcAmtWoVat`, (`benefitTarmed`.`srvMin` * `benefitTarmed`.`quantity`) AS `calcMinTot`, (`benefitTarmed`.`srvMin` * `benefitTarmed`.`effQuant`) AS `effCalcMinTot`,ifnull(round(((`benefitTarmed`.`effAmt` + `benefitTarmed`.`effAmt2`) / nullif((`benefitTarmed`.`amountQty1` + `benefitTarmed`.`amount2Qty1`),0)),3),0) AS `effQuantPckType` from `benefitTarmed` where (((`benefitTarmed`.`invoicePart` <> 'aaaaaaaaaaaaaaaaaaaa') and (`benefitTarmed`.`invoicePart` is not null) ) or ((`benefitTarmed`.`invoice` <> 'aaaaaaaaaaaaaaaaaaaa') and (`benefitTarmed`.`invoice` is not null))) ) character_set_client: latin1 collation_connection: latin1_swedish_ci 1 row in set (0.00 sec)在“舊數據庫”上顯示索引：
show index from benefitBase from c0515_dwh; +-------------+------------+-------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------------+------------+-------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | benefitBase | 0 | id | 1 | id | A | 275913 | NULL | NULL | | BTREE | | | | benefitBase | 1 | benefitBase_created_index | 1 | created | A | 275913 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_tariff_index | 1 | tariff | A | 13 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_code_index | 1 | code | A | 15 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_executingDoctor_index | 1 | executingDoctor | A | 249 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_responsibleDoctor_index | 1 | responsibleDoctor | A | 17 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_obligation_index | 1 | obligation | A | 13 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_begin_index | 1 | begin | A | 39416 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_negObligation_index | 1 | negObligation | A | 13 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_name_index | 1 | name | A | 17 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_owner_index | 1 | owner | A | 226 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_patient_index | 1 | patient | A | 30657 | NULL | NULL | YES | BTREE | | | | benefitBase | 1 | benefitBase_session_index | 1 | session | A | 275913 | NULL | NULL | YES | BTREE | | | +-------------+------------+-------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ show index from benefitTarmed from c0515_dwh; +---------------+------------+---------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+---------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | benefitTarmed | 0 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | benefitTarmed | 1 | benefitTarmed_created_index | 1 | created | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_tariff_index | 1 | tariff | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_code_index | 1 | code | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_executingDoctor_index | 1 | executingDoctor | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_responsibleDoctor_index | 1 | responsibleDoctor | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_obligation_index | 1 | obligation | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_begin_index | 1 | begin | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_negObligation_index | 1 | negObligation | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_name_index | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_patient_index | 1 | patient | A | 0 | NULL | NULL | YES | BTREE | | | | benefitTarmed | 1 | benefitTarmed_owner_index | 1 | owner | A | 0 | NULL | NULL | YES | BTREE | | | +---------------+------------+---------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ show index from invoiceBase from c0515_dwh; +-------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | invoiceBase | 0 | id | 1 | id | A | 40574 | NULL | NULL | | BTREE | | | | invoiceBase | 1 | invoiceBase_created_index | 1 | created | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_begin_index | 1 | begin | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_end_index | 1 | end | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_traStat_index | 1 | traStat | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_invStat_index | 1 | invStat | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_patient_index | 1 | patient | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_provider_index | 1 | provider | A | 34 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_biller_index | 1 | biller | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_name_index | 1 | name | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_law_index | 1 | law | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_reason_index | 1 | reason | A | 25 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_amtPrepaid_index | 1 | amtPrepaid | A | 73 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_amtPaid_index | 1 | amtPaid | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_amtPaidDirect_index | 1 | amtPaidDirect | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_amtFee_index | 1 | amtFee | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_invoiceType_index | 1 | invoiceType | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_invoiceOptimal_index | 1 | invoiceOptimal | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_patientCopy_index | 1 | patientCopy | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_diagnoses_index | 1 | diagnoses | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_customerNumber_index | 1 | customerNumber | A | 25 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_traStatDate_index | 1 | traStatDate | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_invStatDate_index | 1 | invStatDate | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_oldInvoiceNr_index | 1 | oldInvoiceNr | A | 19 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_cancReqFlag_index | 1 | cancReqFlag | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_cancReqDate_index | 1 | cancReqDate | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_claimNr_index | 1 | claimNr | A | 19 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_claimReqFlag_index | 1 | claimReqFlag | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_claimReqDate_index | 1 | claimReqDate | A | 19 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_depReqFlag_index | 1 | depReqFlag | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_depReqDate_index | 1 | depReqDate | A | 1 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_cancRemarks_index | 1 | cancRemarks | A | 2386 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_depRemarks_index | 1 | depRemarks | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_p02InvNr_index | 1 | p02InvNr | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_p02InvDate_index | 1 | p02InvDate | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_newInvoiceNr_index | 1 | newInvoiceNr | A | 4508 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_mandator_index | 1 | mandator | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_account_index | 1 | account | A | 3 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_invoiceDate_index | 1 | invoiceDate | A | 40574 | NULL | NULL | YES | BTREE | | | | invoiceBase | 1 | invoiceBase_debMngmt_index | 1 | debMngmt | A | 3 | NULL | NULL | YES | BTREE | | | +-------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+解釋“新數據庫”
explain SELECT b.tariff as tariff, b.benefitType as benefitType, b.name as name, b.code as code, SUM(b.effQuantPckType) as `count`, SUM(effCalcAmtVat) as `amount`, SUM(effAmt) as `amountAL`, SUM(effAmt2) as `amountTL` FROM benefitCombined AS b LEFT OUTER JOIN invoice AS inv ON inv.id = b.invoice WHERE (invoicePart != 'aaaaaaaaaaaaaaaaaaaa' OR invoice != 'aaaaaaaaaaaaaaaaaaaa') AND executingDoctor = 'dqwjvgbskiaaaacsonmj' AND b.begin >= '2018-06-16 00:00:00' AND b.begin <= '2018-07-16 23:59:57' AND 1=1 GROUP BY benefitType, code; +------+--------------+---------------+------+---------------+------+---------+------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+---------------+------+---------------+------+---------+------+--------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 281550 | Using where; Using temporary; Using filesort | | 2 | DERIVED | benefitBase | ALL | NULL | NULL | NULL | NULL | 281549 | Using where | | 3 | UNION | benefitTarmed | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+---------------+------+---------------+------+---------+------+--------+----------------------------------------------+ 7 rows in set (5.53 sec)在“新數據庫”上顯示創建：
與“舊數據庫”相同。
我已經對“新數據庫”上的所有表/數據庫進行了“分析表”，並嘗試設置相同的優化器開關，但執行計劃似乎沒有改變。mariadb.com 提到“出於所有實際目的，MariaDB 是替代相同 MySQL 版本的二進制下降……”。我的目標是使查詢在“新數據庫”上以相同的速度（至少）執行。我正在尋找對上述計劃差異的解釋。
編輯：我添加了索引/視圖創建資訊，但已達到字元限制。索引僅顯示基數的差異，例如：
benefitBase_owner_index (old db: 233, new db: 17) invoiceBase_newInvoiceNr_index (old db: 4647, new db: 1)
在 mariadb 配置文件中設置了 optimizer_switch （在這種情況下，’table_elimination=on’ 是執行計劃差異的罪魁禍首）之後，執行計劃發生了變化，現在類似於從 ‘old db’ 看到的那個：
+------+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 306922 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | invoiceBase | eq_ref | id | id | 20 | b.invoice | 1 | Using where; Using index | | 2 | DERIVED | benefitBase | ALL | NULL | NULL | NULL | NULL | 306921 | Using where | | 3 | UNION | benefitTarmed | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+我預計執行時間會發生變化，但在速度相當快（與 CPU 相關）的主機上，查詢仍然會以大約 6 秒的速度執行。由於 I/O 速度或延遲可能存在差異，我已將 tmpdir（用於寫入磁碟綁定的臨時表）安裝為 ramdisk，但執行速度沒有顯著變化。