时间戳

首页 > 解决方案 > 无法提高查询的查询性能

mysql - 无法提高查询的查询性能

问题描述

我有以下 2 个表： 

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我似乎无法降低这个数字。
我可以创建一个索引来提高此查询性能吗？

标签： mysql

解决方案

移入ValidForBilling前。MerchantCreationTime_ accID_type_merchCreatTime_vfb您需要=TRUE在索引中使用范围之前进行 ref 查找。

对于表 2，似乎已经是一个 table1ID 索引，并且附加 CalculatedAmount 将能够在结果中使用：

CREATE INDEX tbl1IDCalcAmount (table1ID,CalculatedAmount) ON table2

推荐阅读