mysql - MySQL View 的优化

Question

我想连接两个 MySQL 表并将其存储为一个 View ，这样我就可以在应用程序中处理这个 View ，而不是查询两个表。但是这个 View 恰好非常慢。

这是我的表格:

CREATE TABLE spectrumsets (
    setid INT(11) NOT NULL,
    timestampdt INT(11) NULL DEFAULT NULL,
    timestampd INT(10) UNSIGNED NOT NULL,
    timestampt INT(10) UNSIGNED NOT NULL,
    device INT(11) NOT NULL,
    methodname VARCHAR(50) NOT NULL,
    PRIMARY KEY (setid),
    UNIQUE INDEX setid_idx (setid),
    UNIQUE INDEX timestamp_device_idx (timestampd, timestampt, device),
    INDEX device_fk (device),
    INDEX timestampd_idx (timestampd),
    CONSTRAINT device_fk FOREIGN KEY (device)
        REFERENCES spectrumdevices (deviceid)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

CREATE TABLE spectrumdata (
    valueid INT(11) NOT NULL AUTO_INCREMENT,
    spectrumset INT(11) NOT NULL,
    wavelength DOUBLE NULL DEFAULT NULL,
    intensity DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (valueid),
    INDEX spectrumset_idx (spectrumset),
    CONSTRAINT spectrumset_fk FOREIGN KEY (spectrumset)
        REFERENCES spectrumsets (setid)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

这是我的观点:

SELECT spectrumsets.timestampd,spectrumsets.timestampt,spectrumsets.device,
    spectrumdata.wavelength,spectrumdata.intensity
FROM spectrumdata INNER JOIN spectrumsets ON spectrumdata.spectrumset=
    spectrumsets.setid
WHERE spectrumdata.wavelength>0 
ORDER BY spectrumsets.timestampd,spectrumsets.timestampt,spectrumsets.device,
    spectrumdata.wavelength

在我的机器上执行 select count(*) 需要 385.516 秒并生成 82923705 条记录，因此数据集相当大

我已经找到了这个link但仍然不完全明白哪里出了问题。

更新:

EXPLAIN 给出了这个结果:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","spectrumsets","index","PRIMARY,setid_idx","timestamp_device_idx","12",NULL,"327177","Using index; Using temporary; Using filesort"
"1","SIMPLE","spectrumdata","ref","spectrumset_idx","spectrumset_idx","4","primprod.spectrumsets.setid","130","Using where"

Answer 1

Explain 表明查询正在命中连接的索引(这很好)，但随后对查询的其余部分使用临时表和文件排序。

这有两个原因:

• where 子句没有命中索引
• order by 子句未命中索引

在评论中，您说删除 where 子句带来了很大的改进；这表明您需要光谱集、波长的复合索引，假设波长具有相当数量的可能值(如果它只有 10 个值，则索引可能不会执行任何操作)。

如果您将“order by”子句排除在您的 View 之外，它应该运行得更快 - 并且有一个很好的例子让排序顺序由提取数据的查询而不是 View 来确定。我猜想大多数查询都会对数据非常有选择性——仅限于几个时间戳；通过在 View 中嵌入 order by，您每次都要为排序付出代价。

如果您确实必须在 View 中有“order by”，请创建一个索引，其中包含按“order by”顺序排列的所有字段，并在前面连接。例如:

唯一索引 timestamp_device_idx (set_id, timestampd, timestampt, device),