MySQL 查询优化 - 不使用主键？

Question

我有一个使用三个表的查询。这个查询是一个更大的查询的一部分，这个查询由于这个看似简单的查询导致对 Patients 表的全表扫描而降低了性能。

查询的目的是能够查看患者列表，其中包含医生姓名、治疗和给定日期的收费金额。

我已经在 Transactions 中为 PatientID 创建了索引，在 Patients 中为 DoctorID 创建了索引，但是 MySQL 坚持对患者进行全表扫描。

患者表(13,000 行)

CREATE TABLE `Patients` (
  `ID` int(10) NOT NULL,
  `DoctorID` int(10) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `DoctorID_Index` (`DoctorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

医生表(42行)

CREATE TABLE `Doctors` (
  `ID` int(10) NOT NULL,
  `DoctorName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

交易表(~500,000 行)

CREATE TABLE `Transactions` (
  `Description` text,
  `TransactionDate` datetime DEFAULT NULL,
  `Amount` decimal(19,4) DEFAULT NULL,
  `PatientID` int(10) DEFAULT NULL,
  `ID` int(10) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `PatientID_Index` (`PatientID`),
  KEY `TransactionDate_Index` (`TransactionDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

此查询执行一天大约需要 1.5 秒。这里会发生什么？它没有使用患者的主键索引？如何进一步优化此查询？

EXPLAIN SELECT P.ID, D.DoctorName, T.Description, T.Amount
FROM
    `Doctors` AS D
    INNER JOIN
        `Patients` AS P
        ON
            D.ID = P.DoctorID
    INNER JOIN
        `Transactions` AS T
        ON
            P.ID = T.PatientID
WHERE Date(T.TransactionDate) IN ('2017-03-30')

[
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "P",
        "partitions" : null,
        "type" : "ALL",
        "possible_keys" : "PRIMARY",
        "key" : null,
        "key_len" : null,
        "ref" : null,
        "rows" : 13748,
        "filtered" : 100.00,
        "Extra" : "Using where"
    },
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "D",
        "partitions" : null,
        "type" : "eq_ref",
        "possible_keys" : "PRIMARY",
        "key" : "PRIMARY",
        "key_len" : "4",
        "ref" : "P.DoctorID",
        "rows" : 1,
        "filtered" : 100.00,
        "Extra" : null
    },
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "T",
        "partitions" : null,
        "type" : "ref",
        "possible_keys" : "PatientID_Index",
        "key" : "PatientID_Index",
        "key_len" : "5",
        "ref" : "P.ID",
        "rows" : 34,
        "filtered" : 100.00,
        "Extra" : "Using where"
    }
]

Answer 1

我将从这样做开始:

<强>1。创建外键。

您需要医生与患者之间以及患者与交易之间的 FK。 MySQL(与其他数据库不同)会自动创建必要的索引并加快查询速度。

alter table `Patients` add (
  constraint fk_patient_doctor foreign key (`DoctorId`) 
    references `Doctors` (`ID`)
);

alter table `Transactions` add (
  constraint fk_tx_patient foreign key (`PatientID`) 
    references `Patients` (`ID`)
);

<强>2。根据日期创建交易索引。

create index ix_tx_date on `Transactions` (`TransactionDate`);

这将加快按日期搜索的速度，希望使用范围扫描而不是全表扫描。

<强>3。修复您的查询。

正如@UUeerdo 所说，而不是:

Date(T.TransactionDate) IN ('2017-03-30')

尝试:

T.TransactionDate BETWEEN '2017-03-30 00:00:00' AND '2017-03-30 23:59:59'

<强>4。更新 MySQL 统计数据。

analyze table `Transactions`;
analyze table `Patients`;
analyze table `Orders`;