php - 问题 : Writing a MySQL parser to split JOIN's and run them as individual queries (denormalizing the query dynamically)

Question

我正在尝试找出一个脚本来获取 MySQL 查询并将其转换为单独的查询，即动态地对查询进行反规范化。

作为测试，我构建了一个包含 4 个表的简单文章系统:

• 文章
  • 文章编号
  • article_format_id
  • 文章标题
  • 文章正文
  • 文章日期
• 文章类别
  • 文章编号
  • 类别编号
• 类别
  • 类别编号
  • 类别标题
• 格式
  • 格式编号
  • 格式标题

一篇文章可以属于多个类别，但只能有一种格式。我觉得这是现实生活中的一个很好的例子。

在列出所有文章的类别页面上(也包含 format_title)这可以通过以下查询轻松实现:

SELECT articles.*, formats.format_title 
FROM articles 
INNER JOIN formats ON articles.article_format_id = formats.format_id 
INNER JOIN article_categories ON articles.article_id = article_categories.article_id 
WHERE article_categories.category_id = 2 
ORDER BY articles.article_date DESC

但是，我尝试构建的脚本会收到此查询、解析它并单独运行查询。

因此在此类别页面示例中，脚本将有效地运行此(动态计算):

// Select article_categories
$sql = "SELECT * FROM article_categories WHERE category_id = 2";
$query = mysql_query($sql);
while ($row_article_categories = mysql_fetch_array($query, MYSQL_ASSOC)) {

    // Select articles
    $sql2 = "SELECT * FROM articles WHERE article_id = " . $row_article_categories['article_id'];
    $query2 = mysql_query($sql2);
    while ($row_articles = mysql_fetch_array($query2, MYSQL_ASSOC)) {

        // Select formats
        $sql3 = "SELECT * FROM formats WHERE format_id = " . $row_articles['article_format_id'];
        $query3 = mysql_query($sql3);
        $row_formats = mysql_fetch_array($query3, MYSQL_ASSOC);

        // Merge articles and formats
        $row_articles = array_merge($row_articles, $row_formats);

        // Add to array
        $out[] = $row_articles;
    }
}

// Sort articles by date
foreach ($out as $key => $row) {
    $arr[$key] = $row['article_date'];
}

array_multisort($arr, SORT_DESC, $out);

// Output articles - this would not be part of the script obviously it should just return the $out array
foreach ($out as $row) {
    echo '<p><a href="article.php?id='.$row['article_id'].'">'.$row['article_title'].'</a> <i>('.$row['format_title'].')</i><br />'.$row['article_body'].'<br /><span class="date">'.date("F jS Y", strtotime($row['article_date'])).'</span></p>';
}

这方面的挑战是以正确的顺序计算出正确的查询，因为您可以在查询中以任何顺序放置 SELECT 和 JOIN 的列名(这正是 MySQL 和其他 SQL 数据库翻译得很好的地方)并计算出PHP 中的信息逻辑。

我目前正在使用 SQL_Parser 解析查询这在将查询拆分为多维数组方面效果很好，但解决上面提到的问题很头疼。

如有任何帮助或建议，我们将不胜感激。

Answer 1

据我所知，您正试图在您无法修改的第 3 方论坛应用程序(也许是混淆代码？)和 MySQL 之间放置一个层。该层将拦截查询，将它们重新编写为可单独执行，并生成 PHP 代码以针对数据库执行它们并返回聚合结果。 这是一个非常糟糕的主意。

您暗示不可能添加代码并同时建议生成要添加的代码，这似乎很奇怪。希望你不打算使用类似 funcall 的东西注入(inject)代码。 这是一个非常糟糕的主意。

其他人要求避免您最初的方法并专注于数据库是非常合理的建议。我会把我的声音加入到希望不断增长的合唱中。

我们将假设一些约束条件:

• 您运行的是 MySQL 5.0 或更高版本。
• 查询不能改变。
• 无法更改数据库表。
• 您已经拥有 appropriate indexes为麻烦的查询所引用的表准备就绪。
• 您已经三重检查访问您的数据库的慢速查询(并运行 EXPLAIN)并尝试设置索引以帮助它们更快地运行。
• 内部联接给您的 MySQL 安装带来的负载是 Not Acceptable 。

三种可能的解决方案:

1. 您可以通过将运行数据库的硬件升级为具有更多内核、更多(尽可能多)RAM 和更快磁盘的设备来投资当前数据库，从而轻松解决此问题。如果你有钱Fusion-io's products强烈推荐这种事情。这可能是我将提供的三个选项中较简单的一个
2. 设置第二个主 MySQL 数据库并将其与第一个配对。确保您有能力强制 AUTO_INCREMENT id 交替(一个数据库使用偶数 id，另一个数据库使用奇数)。这不会永远扩展，但它确实为硬件和机架空间的价格提供了一些喘息的空间。再次，加强硬件。您可能已经这样做了，但如果没有，也值得考虑一下。
3. 使用像 dbShards 这样的东西.您仍然需要为此投入更多硬件，但您可以获得额外的好处，即能够扩展到两台机器之外，并且随着时间的推移您可以购买成本更低的硬件。