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

fcipmucu 于 2021-06-25 发布在 Mysql

关注(0)|答案(3)|浏览(311)

我有一个使用三个表的查询。这个查询是一个更大的查询的一部分，由于这个看似简单的查询对patients表进行了完整的表扫描，这个查询正在扼杀性能。
该查询的目的是能够查看一个患者列表，其中包含医生姓名、治疗方法和某一天的收费金额。
我已经在事务中创建了patientid索引，在病人中创建了doctorid索引，但是mysql坚持对病人进行全表扫描。
患者表（13000行）

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

事务表（约500000行）

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"
    }
]

sql mysql query-optimization

来源：https://stackoverflow.com/questions/49883213/mysql-query-optimization-not-using-primary-key

3条答案

按热度按时间

nbewdwxp1#

对条件中使用的字段值使用任何函数都会极大地破坏性能（特别是因为它会导致这些字段上的任何索引都不可用）。
而不是 Date(T.TransactionDate) IN ('2017-03-30') 尝试 T.TransactionDate BETWEEN '2017-03-30 00:00:00' AND '2017-03-30 23:59:59' 另外，由于您正在筛选 T.TransactionDate 和加入 T.PatientID ，在两个上都有一个复合索引比在每个上都有单独的索引更有帮助。

赞(0）回复(0）举报 2021-06-26

zfycwa2u2#

我首先要做的是：
1创建外键。
你需要医生和病人之间，病人和交易之间的fks。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`);

这将加速按日期搜索，希望使用范围扫描而不是全表扫描。
三。修正你的问题。
正如@uuerdo所说，而不是：

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`;

赞(0）回复(0）举报 2021-06-26

vddsk6oq3#

最小的改变是避免在函数中隐藏列( DATE ). 我更喜欢这种模式：

WHERE T.TransactionDate >= '2017-03-30'
  AND T.TransactionDate  < '2017-03-30' + INTERVAL 1 DAY

这将让优化器从t开始，在这里可以更快地完成过滤。
你已经有了必要的索引。
使索引 TransactionDate 复合材料也无济于事。
fks不添加任何性能。 ANALYZE innodb表几乎不需要。
现在我希望 EXPLAIN 按以下顺序显示表格：t，p，d。t将使用“transactiondate”上的索引；其他人将使用他们的 PRIMARY KEY . 不应进行全表扫描。

赞(0）回复(0）举报 2021-06-25

我来回答

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

3条答案

相关问题

热门标签

最新问答