无法提高查询的查询性能

rjee0c15  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(334)

我有以下两张表:

CREATE TABLE table1 (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  AccountID INT NOT NULL,
  Type VARCHAR(50) NOT NULL,
  ValidForBilling BOOLEAN NULL DEFAULT false,  
  MerchantCreationTime TIMESTAMP NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY (OrderID, Type)
);

使用索引: INDEX accID_type_merchCreatTime_vfb (AccountID, Type, MerchantCreationTime, ValidForBilling); ```
CREATE TABLE table2 (
OrderID INT NOT NULL,
AccountID INT NOT NULL,
LineType VARCHAR(256) NOT NULL,
CreationDate TIMESTAMP NOT NULL,
CalculatedAmount NUMERIC(4,4) NULL,
table1ID INT(11) NOT NULL
);

我正在运行以下查询:

SELECT COALESCE(SUM(CalculatedAmount), 0.0) AS CalculatedAmount
FROM table2
INNER JOIN table1 ON table1.ID = table2.table1ID
WHERE table1.ValidForBilling is TRUE
AND table1.AccountID = 388
AND table1.Type = 'TPG_DISCOUNT'
AND table1.MerchantCreationTime >= '2018-11-01T05:00:00'
AND table1.MerchantCreationTime < '2018-12-01T05:00:00';

大约需要2分钟才能完成。
是的 `EXPLAIN` 为了尝试提高查询性能,得到以下输出:

+----+-------------+------------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+----------------------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+----------------------+-------+----------+--------------------------+
| 1 | SIMPLE | table1 | NULL | range | PRIMARY,i_fo_merchant_time_account,FO_AccountID_MerchantCreationTime,FO_AccountID_ExecutionTime,FO_AccountID_Type_ExecutionTime,FO_AccountID_Type_MerchantCreationTime,accID_type_merchCreatTime_vfb | accID_type_merchCreatTime_vfb | 61 | NULL | 71276 | 100.00 | Using where; Using index |
| 1 | SIMPLE | table2 | NULL | eq_ref | table1ID,i_oc_fo_id | table1ID | 4 | finance.table1.ID | 1 | 100.00 | NULL |
+----+-------------+------------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+----------------------+-------+----------+--------------------------+

我看到我扫描了71276行 `table1` 我似乎无法降低这个数字。
我可以创建索引来提高查询性能吗?
cwxwcias

cwxwcias1#

移动 ValidForBilling 之前 MerchantCreationTimeaccID_type_merchCreatTime_vfb . 你需要做参考查找 =TRUE 在索引中使用范围之前。
对于表2,似乎已经是table1id索引,并且可以在结果中附加calculatedamount:

CREATE INDEX tbl1IDCalcAmount (table1ID,CalculatedAmount) ON table2

相关问题