主页 / dba / 问题 / 53203
Accepted
Van
Asked: 2013-11-13 11:18:59 +0800 CST

MySQL 5.6.14 将不会在查询中使用正确的键/索引

  • 772

操作系统：ubuntu 12.04 amd64
数据库：MySQL 5.6.14-community amd64

我不明白为什么这个查询使用了错误的索引并对 ar_batch (ab) 表进行了全表扫描。也许有人能明白为什么！？

询问

SELECT
  DATE_FORMAT(ab.deposit_date, '%m/%d/%Y') AS paymentDate,
  FORMAT(pc.received_amt, 2) AS paymentAmount,
  pc.check_no AS paymentReference
FROM
  TRANSACTION ar 
JOIN payments py 
  ON (
    ar.transaction_id = py.ar_tranaction_id 
    AND py.ar_tranaction_id NOT LIKE '%AC'
  ) 
JOIN ar_batch ab
  ON (ab.batch_id = py.batch_id)
JOIN payment_checks pc 
  ON (py.payment_check_id = pc.id) 
WHERE ar.transaction_type = 'AR' 
AND ar.cust_no = 'SEACOR0003' 
ORDER BY py.id DESC 
LIMIT 1;

解释扩展

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"
"1" "SIMPLE"    "ab"    "index" "PRIMARY"   "Deposit_date_idx"  "4" \N  "34836" "100.00"    "Using index; Using temporary; Using filesort"
"1" "SIMPLE"    "py"    "ref"   "Batch_Id,Ar_Tranaction_Id,Payment_Check_Id"    "Batch_Id"  "5" "eci_lw_prod.ab.Batch_id"   "10"    "100.00"    "Using where"
"1" "SIMPLE"    "ar"    "eq_ref"    "PRIMARY,cust_no_idx,Trasns_type_idx"   "PRIMARY"   "4" "eci_lw_prod.py.Ar_Tranaction_Id"   "1" "100.00"    "Using where"
"1" "SIMPLE"    "pc"    "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "eci_lw_prod.py.Payment_Check_Id"   "1" "100.00"    \N

下面是创建表....

CREATE TABLE `ar_batch` (
`Batch_id` int(11) NOT NULL AUTO_INCREMENT,
`User_id` varchar(30) NOT NULL,
`Date` datetime NOT NULL,
`Deposit_date` date DEFAULT NULL,
`Total_Amount` decimal(10,2) DEFAULT NULL,
`Bank_Account` varchar(20) DEFAULT NULL,
`Notes` varchar(200) DEFAULT NULL,
`Status` varchar(15) NOT NULL,
`Reconciled` varchar(1) DEFAULT NULL,
`Applied_Amount` decimal(10,2) DEFAULT NULL,
`Balance_Amount` decimal(10,2) NOT NULL,
`On_Acct_Amount` decimal(10,2) DEFAULT NULL,
`Prepay_Amount` decimal(10,2) DEFAULT NULL,
`BankAccount_Desc` varchar(200) DEFAULT NULL,
`Gl_Account` varchar(50) DEFAULT NULL,
`batch_type` varchar(1) DEFAULT NULL,
`Adjust_Amount` decimal(10,2) DEFAULT '0.00',
`Using_By` int(11) DEFAULT NULL,
`direct_gl_account` tinyint(1) DEFAULT '0',
 PRIMARY KEY (`Batch_id`),
 KEY `Deposit_date_idx` (`Deposit_date`),
 KEY `status_idx` (`Status`),
 KEY `batch_type_idx` (`batch_type`),
 KEY `applied_amount_idx` (`Applied_Amount`)
) ENGINE=InnoDB AUTO_INCREMENT=44953 DEFAULT CHARSET=latin1;


CREATE TABLE `payments` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Batch_Id` int(11) DEFAULT NULL,
`Batch_Date` date DEFAULT NULL,
`Cust_No` varchar(10) DEFAULT NULL,
`Check_Date` date DEFAULT NULL,
`Payment_Amt` decimal(10,2) DEFAULT NULL,
`Payment_Type` varchar(10) DEFAULT NULL,
`Charge_Code` varchar(10) DEFAULT NULL,
`Ar_Tranaction_Id` varchar(15) DEFAULT NULL,
`invoice_no` varchar(100) DEFAULT NULL,
`Bill_Ladding_No` varchar(30) DEFAULT NULL,
`Adjustment_Amt` decimal(10,2) DEFAULT NULL,
`Check_no` varchar(30) DEFAULT NULL,
`adjustment_date` date DEFAULT NULL,
`userName` varchar(20) DEFAULT NULL,
`Payment_Check_Id` int(12) DEFAULT NULL,
`notes` varchar(200) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `Batch_Id` (`Batch_Id`),
KEY `Cust_No` (`Cust_No`),
KEY `Ar_Tranaction_Id` (`Ar_Tranaction_Id`),
KEY `Payment_Check_Id` (`Payment_Check_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4118743 DEFAULT CHARSET=latin1;

CREATE TABLE `transaction` (
 `Transaction_ID` int(10) NOT NULL AUTO_INCREMENT,
 `Bill_Ladding_No` varchar(42) DEFAULT NULL,
 `Charge_Code` varchar(20) DEFAULT NULL,
 `Transaction_date` date DEFAULT NULL,
 `posted_date` datetime DEFAULT NULL,
 `GL_account_number` varchar(20) DEFAULT NULL,
 `Transaction_amt` decimal(10,2) DEFAULT NULL,
 `Currency_code` varchar(100) DEFAULT NULL,
 `invoice_number` varchar(100) DEFAULT NULL,
 `Cheque_number` varchar(150) DEFAULT NULL,
 `Subledger_Source_code` varchar(20) DEFAULT NULL,
 `Journal_Entry_number` varchar(20) DEFAULT NULL,
 `Line_Item_number` varchar(20) DEFAULT NULL,
 `cust_name` varchar(60) DEFAULT NULL,
 `cust_no` varchar(30) DEFAULT NULL,
 `Transaction_type` varchar(10) DEFAULT NULL,
 `Balance` decimal(10,2) DEFAULT NULL,
 `BL_Terms` varchar(30) DEFAULT NULL,
 `Sub_House_BL` varchar(30) DEFAULT NULL,
 `Voyage_No` varchar(30) DEFAULT NULL,
 `Container_No` varchar(500) DEFAULT NULL,
 `Master_BL` varchar(30) DEFAULT NULL,
 `Vessel_No` varchar(30) DEFAULT NULL,
 `Status` varchar(20) DEFAULT NULL,
 `Due_Date` date DEFAULT NULL,
 `Bill_to` char(1) DEFAULT NULL,
 `Customer_Reference_No` varchar(500) DEFAULT NULL,
 `Originating_Terminal` varchar(50) DEFAULT NULL,
 `Destination` varchar(60) DEFAULT NULL,
 `Saling_date` date DEFAULT NULL,
 `Fwd_name` varchar(50) DEFAULT NULL,
 `Fwd_no` varchar(10) DEFAULT NULL,
 `Cons_name` varchar(100) DEFAULT NULL,
 `Cons_no` varchar(10) DEFAULT NULL,
 `Third_Pty_name` varchar(50) DEFAULT NULL,
 `Third_Pty_no` varchar(10) DEFAULT NULL,
 `Agent_name` varchar(50) DEFAULT NULL,
 `Agent_no` varchar(10) DEFAULT NULL,
 `Credit_Hold` varchar(20) DEFAULT NULL,
 `correction_flag` varchar(4) DEFAULT NULL,
 `Credit_Terms` int(4) DEFAULT NULL,
 `bank_no` varchar(10) DEFAULT NULL,
 `bank_name` varchar(50) DEFAULT NULL,
 `bank_account_no` varchar(30) DEFAULT NULL,
 `check_date` date DEFAULT NULL,
 `cleared` varchar(1) DEFAULT NULL,
 `cleared_date` date DEFAULT NULL,
 `reconciled` varchar(1) DEFAULT NULL,
 `reconciled_date` date DEFAULT NULL,
 `confirmation_number` varchar(30) DEFAULT NULL,
 `void` varchar(1) DEFAULT NULL,
 `void_date` date DEFAULT NULL,
 `reprint` varchar(1) DEFAULT NULL,
 `reprint_date` date DEFAULT NULL,
 `Balance_In_Process` decimal(10,2) DEFAULT NULL,
 `shipper_no` varchar(20) DEFAULT NULL,
 `shipper_name` varchar(50) DEFAULT NULL,
 `booking_no` varchar(30) DEFAULT NULL,
 `quotation_no` varchar(20) DEFAULT NULL,
 `Ap_Batch_Id` int(11) DEFAULT NULL,
 `Ar_Batch_Id` int(11) DEFAULT NULL,
 `pay_method` varchar(20) DEFAULT NULL,
 `drcpt` varchar(30) DEFAULT NULL,
 `Created_On` datetime DEFAULT NULL,
 `Created_By` int(6) DEFAULT NULL,
 `Updated_On` datetime DEFAULT NULL,
 `Updated_By` int(6) DEFAULT NULL,
 `Paid_By` int(6) DEFAULT NULL,
 `Approved_By` int(6) DEFAULT NULL,
 `Closed_Date` date DEFAULT NULL,
 `ach_batch_sequence` int(7) DEFAULT NULL,
 `manifest_flag` varchar(4) DEFAULT NULL,
 `correction_notice` varchar(10) DEFAULT NULL,
 `owner` int(6) DEFAULT NULL,
 `paid_on` datetime DEFAULT NULL,
 `emailed` tinyint(1) DEFAULT '0',
 `seal_no` varchar(1000) DEFAULT NULL,
 `eta` date DEFAULT NULL,
 `vessel_name` varchar(500) DEFAULT NULL,
 `steam_ship_line` varchar(35) DEFAULT NULL,
 `removed_from_hold` tinyint(1) NOT NULL DEFAULT '0',
 `search_invoice_number` varchar(100) DEFAULT NULL,
 `ap_invoice_id` int(11) DEFAULT NULL,
 `ap_invoice_status` varchar(30) DEFAULT NULL,
 `ap_invoice_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`Transaction_ID`),
KEY `cust_name_idx` (`cust_name`),
KEY `cust_no_idx` (`cust_no`),
KEY `Trasns_type_idx` (`Transaction_type`),
KEY `bill_ladding_idx` (`Bill_Ladding_No`),
KEY `Invoice_number` (`invoice_number`),
KEY `Status_IDX` (`Status`),
KEY `check_number_idx` (`Cheque_number`),
KEY `ap_batch_id_idx` (`Ap_Batch_Id`),
KEY `cleared_idx` (`cleared`),
KEY `reconciled_idx` (`reconciled`),
KEY `void_idx` (`void`),
KEY `void_date_idx` (`void_date`),
KEY `pay_method_idx` (`pay_method`),
KEY `drcpt_idx` (`drcpt`),
KEY `balance_idx` (`Balance`),
KEY `voyage_no_idx` (`Voyage_No`),
KEY `charge_code_idx` (`Charge_Code`),
KEY `container_no_idx` (`Container_No`),
KEY `transaction_amt_idx` (`Transaction_amt`),
KEY `balance_in_process_idx` (`Balance_In_Process`),
KEY `search_invoice_number_idx` (`search_invoice_number`),
KEY `ap_invoice_id_idx` (`ap_invoice_id`),
KEY `ap_invoice_status_idx` (`ap_invoice_status`),
KEY `created_by_idx` (`Created_By`)
) ENGINE=InnoDB AUTO_INCREMENT=1251542 DEFAULT CHARSET=latin1;

CREATE TABLE `payment_checks` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Batch_id` int(11) DEFAULT NULL,
 `Batch_date` date DEFAULT NULL,
 `Cust_id` varchar(10) DEFAULT NULL,
 `Check_no` varchar(20) DEFAULT NULL,
 `Check_date` date DEFAULT NULL,
 `Received_Amt` decimal(10,2) DEFAULT NULL,
 `applied_amount` double(10,2) DEFAULT NULL,
 `On_Acct_In` decimal(10,2) DEFAULT '0.00',
 `Pre_Pmt_In` decimal(10,2) DEFAULT '0.00',
 `Invoice_Out` decimal(10,2) DEFAULT '0.00',
 `On_Acct_Out` decimal(10,2) DEFAULT '0.00',
 `Pre_Pmt_Out` decimal(10,2) DEFAULT '0.00',
 `Charge_Code_Out` decimal(10,2) DEFAULT '0.00',
 `Invoice_In` decimal(10,2) DEFAULT '0.00',
 `Adjust_Amt` decimal(10,2) DEFAULT '0.00',
 PRIMARY KEY (`Id`),
 KEY `Batch_id` (`Batch_id`),
 KEY `Check_no` (`Check_no`),
 KEY `Cust_id` (`Cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=203272 DEFAULT CHARSET=latin1;
mysql-5.6

1 个回答

  1. Best Answer

    这部分看起来很奇怪，至少可以这样说：

    ON (
   ar.transaction_id = py.ar_tranaction_id 
   AND py.ar_tranaction_id NOT LIKE '%AC'
)

    您正在加入INT一个VARCHAR专栏，因此任何提高效率的希望都在那里失去了。更正一个（或两个）列，使其数据类型匹配。

    出于同样的原因，它py.ar_tranaction_id NOT LIKE '%AC'是多余的，因此可以将其删除，甚至在您不更改数据类型之前也可以删除。该列中的任何值都不能既LIKE '%AC'等于整数又等于整数。

    • 1

相关问题