文章/答案/技术大牛

发布

社区首页 >问答首页 >MySQL:简单的模式、加入视图和对无关属性进行排序会导致无法忍受的性能下降。

问MySQL:简单的模式、加入视图和对无关属性进行排序会导致无法忍受的性能下降。
EN

Stack Overflow用户

提问于 2011-08-06 08:21:59

回答 2查看 766关注 0票数 0

我正在创建一个数据库模型，供不同数量的应用程序和不同类型的数据库服务器使用(尽管我现在主要在MySQL和SQLite上进行测试)。这是一个非常简单的模型，它基本上由一个中央匹配表和许多属性表组成，这些表以match_id作为主键和另一个字段(属性值本身)。换句话说，每个匹配都有每种类型的属性中的一种，并且每个属性都存储在一个独立的表中。在对这些属性(FROM matches LEFT JOIN attributes_i_want on primary index)进行排序和筛选时，在经历了一些相当糟糕的性能之后，我决定尝试改进它。为此，我在每个属性值列上添加了一个索引。为了方便查询，排序和过滤性能有了很大提高。

这个简单的模式基本上是应用程序的一个需求，因此它能够自动发现和使用属性。因此，为了创建实际上基于其他结果的更复杂的属性，我决定使用视图将一个或多个不一定与类似属性的模式匹配的表转换为属性模式。我将这些元属性称为(它们也不是直接可编辑的)。然而，对于应用程序来说，这一切都是透明的，因此当它想要的时候，它也很高兴地加入到视图中。问题是：会扼杀性能。当视图在没有对任何属性进行排序的情况下加入时，性能仍然是可以接受的，但是将视图的检索与排序结合起来是不可接受的慢(按1s的顺序)。即使在阅读了大量关于索引的教程和关于堆栈溢出的一些问题之后，我似乎也无能为力。

_Prerequisites用于解决方案:在某种程度上，num_duplicates必须以表或视图的形式存在，其中列为match_id和num_duplicates，以使其看起来像一个属性。我无法改变发现和使用属性的方式。因此，如果我想看到num_duplicates出现在应用程序中，它必须是某种视图或物化表，从而生成一个num_duplicates table._

模式的相关部分

主表：

CREATE TABLE `matches` (
  `match_id` int(11) NOT NULL,
  `source_name` text,
  `target_name` text,
  `transformation` text,
  PRIMARY KEY (`match_id`)
) ENGINE=InnoDB;

普通属性示例(索引)：

CREATE TABLE `error` (
  `match_id` int(11) NOT NULL,
  `error` double DEFAULT NULL,
  PRIMARY KEY (`match_id`),
  KEY `error_index` (`error`)
) ENGINE=InnoDB;

(所有正常属性，如error，基本相同)

元属性/视图：

CREATE VIEW num_duplicates 
AS SELECT duplicate AS match_id, COUNT(duplicate) AS num_duplicates 
    FROM duplicate 
    GROUP BY duplicate

(这是我现在使用的唯一元属性)

对属性值列进行索引的简单查询(通过索引改进部分)

SELECT matches.match_id, source_name, target_name, transformation FROM matches
    INNER JOIN error ON matches.match_id = error.match_id
ORDER BY error.error

(由于错误索引，此查询的性能有了很大提高)(此查询的运行时约为0.0001秒)

稍微复杂一些的查询及其运行时，包括元属性(仍然不好的部分)

SELECT 
    matches.match_id, source_name, target_name, transformation, STATUS , volume, error, COMMENT , num_duplicates
FROM matches
    INNER JOIN STATUS ON matches.match_id = status.match_id
    INNER JOIN error ON matches.match_id = error.match_id
    LEFT JOIN num_duplicates ON matches.match_id = num_duplicates.match_id
    INNER JOIN volume ON matches.match_id = volume.match_id
    INNER JOIN COMMENT ON matches.match_id = comment.match_id

(运行时:0.0263秒)<-仍可接受

SELECT matches.match_id, source_name, target_name, transformation, STATUS , volume, error, COMMENT , num_duplicates
FROM matches
    INNER JOIN STATUS ON matches.match_id = status.match_id
    INNER JOIN error ON matches.match_id = error.match_id
    LEFT JOIN num_duplicates ON matches.match_id = num_duplicates.match_id
    INNER JOIN volume ON matches.match_id = volume.match_id
    INNER JOIN COMMENT ON matches.match_id = comment.match_id
ORDER BY error.error
LIMIT 20, 20

(运行时: 0.8866秒)我想它必须扫描整个桌子，所以这个限制并不重要)

解释最后一个查询

当然，在来到这里之前，我试着自己解决这个问题，但我必须承认，我并不擅长这些事情，而且还没有找到一种方法来消除这个令人不快的性能杀手。我知道这很可能是使用文件，但我不知道如何摆脱它。

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY error   index   PRIMARY,match_id    error_index 9   NULL    53909   Using index; Using temporary; Using filesort
1   PRIMARY COMMENT eq_ref  PRIMARY PRIMARY 4   tangbig4.error.match_id 1    
1   PRIMARY STATUS  eq_ref  PRIMARY PRIMARY 4   tangbig4.COMMENT.match_id   1   Using where
1   PRIMARY matches eq_ref  PRIMARY PRIMARY 4   tangbig4.COMMENT.match_id   1   Using where
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2    
1   PRIMARY volume  eq_ref  PRIMARY PRIMARY 4   tangbig4.matches.match_id   1   Using where
2   DERIVED duplicate   index   NULL    duplicate_index 5   NULL    49222   Using index

顺便说一句，没有排序的查询仍然可以接受地运行，是这样解释的：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY COMMENT ALL PRIMARY NULL    NULL    NULL    49610    
1   PRIMARY error   eq_ref  PRIMARY,match_id    PRIMARY 4       tangbig4.COMMENT.match_id   1    
1   PRIMARY matches eq_ref  PRIMARY PRIMARY 4   tangbig4.COMMENT.match_id   1    
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2    
1   PRIMARY STATUS  eq_ref  PRIMARY PRIMARY 4   tangbig4.COMMENT.match_id   1    
1   PRIMARY volume  eq_ref  PRIMARY PRIMARY 4   tangbig4.matches.match_id   1       Using where
2   DERIVED duplicate   index   NULL    duplicate_index 5   NULL    49222   Using index

问题

因此，我的问题是，对数据库/MySQL了解更多的人是否能够找到一种方法来提高我上一次查询的性能。

对于物化视图，我考虑了很多，但它们在MySQL中并不是本地支持的，而且由于我打算尽可能广泛地使用SQL服务器，这可能不是什么好主意。我希望对查询或视图的更改可能会有所帮助，或者可能增加一个索引。

编辑：我对查询有一些随机的想法：

非常快:连接所有表(不包括视图)，排序
可接受:连接所有表，包括视图，no排序
DOG :连接所有表，包括视图、排序

但是：视图对排序没有任何影响，它的属性或其组成表中的属性都没有用于排序。为什么包括那类对性能有那么大的影响？有什么办法可以说服数据库先排序然后加入视图吗？或者我能让它相信视图对于排序并不重要吗？

EDIT2：按照@ace的建议创建视图，然后加入，似乎没有帮助：

DROP VIEW IF EXISTS `matches_joined`;
CREATE VIEW `matches_joined` AS (
  SELECT matches.match_id, source_name, target_name, transformation, STATUS , volume, error, COMMENT
  FROM matches
  INNER JOIN STATUS ON matches.match_id = status.match_id
  INNER JOIN error ON matches.match_id = error.match_id
  INNER JOIN volume ON matches.match_id = volume.match_id
  INNER JOIN COMMENT ON matches.match_id = comment.match_id
  ORDER BY error.error
);

其次是：

SELECT matches_joined.*, num_duplicates
FROM matches_joined
LEFT JOIN num_duplicates ON matches_joined.match_id = num_duplicates.match_id

然而，对视图使用限制确实产生了影响：

DROP VIEW IF EXISTS `matches_joined`;
CREATE VIEW `matches_joined` AS (
  SELECT matches.match_id, source_name, target_name, transformation, STATUS , volume, error, COMMENT
  FROM matches
  INNER JOIN STATUS ON matches.match_id = status.match_id
  INNER JOIN error ON matches.match_id = error.match_id
  INNER JOIN volume ON matches.match_id = volume.match_id
  INNER JOIN COMMENT ON matches.match_id = comment.match_id
  ORDER BY error.error
  LIMIT 0, 20
);

之后，查询以可接受的速度运行。这已经是个不错的结果了。但是，我觉得我是在跳圈来强迫数据库做我想做的事情，而且时间的减少可能只是因为它现在只需要对20行进行排序。如果我有更多的排呢？还有其他方法可以迫使数据库看到加入num_duplicates视图对排序的影响最小吗？我是否可以更改使视图稍微有一点的查询？

mysql

database

performance

view

回答 2

Stack Overflow用户

回答已采纳

发布于 2011-08-06 08:57:28

如果你还没有试过的话，有些东西是可以测试的。通过排序为所有联接创建一个视图。

DROP VIEW IF EXISTS `matches_joined`;
CREATE VIEW `matches_joined` AS (
  SELECT matches.match_id, source_name, target_name, transformation, STATUS , volume, error, COMMENT
  FROM matches
  INNER JOIN STATUS ON matches.match_id = status.match_id
  INNER JOIN error ON matches.match_id = error.match_id
  INNER JOIN volume ON matches.match_id = volume.match_id
  INNER JOIN COMMENT ON matches.match_id = comment.match_id
  ORDER BY error.error
);

然后用num_duplicates加入他们

SELECT matches_joined.*, num_duplicates
FROM matches_joined
LEFT JOIN num_duplicates ON matches_joined.match_id = num_duplicates.match_id

我假设，正如这里中所指出的，该查询将使用视图matches_joined中的order by子句。

一些可能有助于优化的信息。

MySQL :：MySQL 5.0参考手册:：7.3.1.11通过优化订购

票数 1

Stack Overflow用户

发布于 2011-08-23 09:03:05

@ace提出的“视图”建议或多或少地解决了这个问题，但其他几种类型的查询仍然存在性能问题(特别是大偏移量)。最后，通过简单地强制迟行查找，对这个表单的所有查询都有了很大的改进。请注意，通常声称这只对MySQL是必要的，因为MySQL总是执行早期行查找，并且像PostgreSQL这样的其他数据库不会受到此问题的影响。但是，我的应用程序的广泛基准已经指出，PostgreSQL也从这种方法中获得了很大的好处。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/6965635

复制

相似问题

问MySQL:简单的模式、加入视图和对无关属性进行排序会导致无法忍受的性能下降。
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:简单的模式、加入视图和对无关属性进行排序会导致无法忍受的性能下降。EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:简单的模式、加入视图和对无关属性进行排序会导致无法忍受的性能下降。
EN