优化SQL查询 [英] Optimising SQL Queries

问题描述

我目前正在开发一个内容管理系统，我想听听您对以下内容的想法：

I'm developing a content management system at the moment, and I wanted to hear your thoughts on the following:

我有一个表， page 。让我们假设它看起来像这样

I have one table, page. Let's assume it looks like this

ID | Title | Content
1  | Test  | This is a test

除此之外，我有一个 page_option 表（所以我可以存储与页面相关的选项，但我不想有一个有限的选项列表 - 如果需要，模块可以添加自己的选项到页面）。

As well as this, I have a page_option table (so I can store options relating to the page, but I don't want to have a finite list of options - modules could add their own options to a page if required.)

page_option 表可能如下所示：

page_id | option_key  | option_value
1       | background  | red
1       | module1_key | chicken

现在要检索页面对象，我使用Active Record类编码为此问题）：

Now to retrieve a page object, I do the following using the Active Record class (this was pseudo coded for this question):

function get_by_id($page_id) {

 $this->db->where('id', $page_id);
 $page_object = $this->db->get('page');

 if($page_object->num_rows() > 0) {

   $page = $page_object->row();
   $this->db->where('page_id', $page_id);
   $options_object = $this->db->get('option');

   if($options_object->num_rows() > 0) {
     $page->options = $options_object->result();
   }

   return $page;

 }

 return $page_object->row();

}

我想知道的是有办法这在一个查询中，以便选项键成为我的选择中的虚拟列，所以我会得到：

What I want to know, is there a way to do this in one query, so that the option keys become virtual columns in my select, so I'd get:

ID | Title | Content        | background | module1_key
1  | Test  | This is a test | red        | chicken

在我的结果中，而不是对每一行执行单独的查询。如果有10,000？

In my results, rather than doing a seperate query for every row. What if there were 10,000? Etc.

提前非常感谢！

推荐答案

使用EAV （实体 - 属性值）模型您将永远不得不应付这些类型的问题。由于查询的复杂性，它们也不是高效的（在大多数情况下需要旋转）。

Using the EAV (Entity-Attribute-Value) model you will always have to cope with these kind of issues. They're also not ver efficient due to the complexity of the queries (pivoting is required in most of them).

SELECT page_id,
  MAX(CASE WHEN option_key = 'background' THEN option_value END) background,
  MAX(CASE WHEN option_key = 'module1_key' THEN option_value END) module1_key,
  MAX(CASE WHEN option_key = 'module2_key' THEN option_value END) module2_key
FROM page_option
GROUP BY page_id

例如，给定此表：

| PAGE_ID |  OPTION_KEY | OPTION_VALUE |
|---------|-------------|--------------|
|       1 |  background |          red |
|       1 | module1_key |      chicken |
|       2 | module1_key |         duck |
|       3 | module1_key |          cow |
|       4 |  background |         blue |
|       4 | module2_key |        alien |
|       4 | module1_key |      chicken |

您将输出以下内容：

| PAGE_ID | BACKGROUND | MODULE1_KEY | MODULE2_KEY |
|---------|------------|-------------|-------------|
|       1 |        red |     chicken |      (null) |
|       2 |     (null) |        duck |      (null) |
|       3 |     (null) |         cow |      (null) |
|       4 |       blue |     chicken |       alien |

Fiddle 此处。

然后只需加入页它:)我省略了这部分，以集中查询在分组本身。

Then just join with the page table and that's it :) I've omitted that part in order to focus the query in the grouping itself.

这篇关于优化SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！