mysql - 高效地从 MYSQL 检索数据以进行批处理？

Question

我有一种情况，我在主表中有数据，而在一堆关联表中有附加数据。给定主表中的 ID 列表，我需要检索批处理的所有数据。这个批处理过程量很大，所以我需要非常有效地实施它。

我已经使用子查询group_concat和一些映射代码实现了这一点，但我对此不是很有信心。它看起来很高效但很难维护（特别是因为我的实际实现要丑得多，有更多的表和列）。这个实现是个好主意吗？我可以采取其他一些mysql查询/加入方法来改进它吗？

数据的结构与此类似，但实际上我有更多的表和列：

Objects
ID | column1 | column2 | column3
----------
1      a          b         c 
2      d          e         f


Attributes1
ID | ObjectsId| column1 | column2 | column3
--------------------
1         1       g        k           l
2         1       m        n           o
3         2       p        q           r
4         2       s        t           u


Attributes2
ID | ObjectsId| column1 | column2 | column3
--------------------
1         1         w        v         x
2         1         y        z         aa
3         2         bb       cc        dd
4         2         ee       ff        gg

这大概是我的应用程序代码生成的对象的样子。

[
   {
   id = 1,
   column1 = a,
   Column2 = b,
   Column3 = c,
   Attributes1 = [
          {
          Id = 1, 
          ObjectsId = 1,
          Column1 = g,
          Column2 = k,
          Column3 = l
          },
          {
          Id = 2, 
          ObjectsId = 1,
          Column1 = m,
          Column2 = n,
          Column3 = o
          }]
     Attributes2 = [
         {
          Id = 1, 
          ObjectsId = 1,
          Column1 = w,
          Column2 = v,
          Column3 = x
          },
          {
          Id = 2, 
          ObjectsId = 1,
          Column1 = y,
          Column2 = z,
          Column3 = aa
         }]
   },
   {
   id = 2,
   column1 = d,
   Column2 = e,
   Column3 = f,
   Attributes1 = [
          {
          Id = 3, 
          ObjectsId = 2,
          Column1 = p,
          Column2 = q,
          Column3 = r
          },
          {
          Id = 4, 
          ObjectsId = 2,
          Column1 = s,
          Column2 = t,
          Column3 = u
          }]
     Attributes2 = [
         {
          Id = 3, 
          ObjectsId = 2,
          Column1 = bb,
          Column2 = cc,
          Column3 = dd
          },
          {
          Id = 4, 
          ObjectsId = 2,
          Column1 = ee,
          Column2 = ff,
          Column3 = gg
         }]
    }
]

这是我的实现：

private function get_all($object_ids)
{
    $a1 = " SELECT group_concat(a1.ID, '|', a1.ObjectsId, '|', a1.column1, '|', a1.column2, '|', a1.column3) 
              FROM   Attributes1 a1 
              WHERE  a1.objectsId = o.id";

    $a2 = " SELECT group_concat(a2.ID, '|', a2.ObjectsId, '|', a2.column1, '|', a2.column2, '|', a2.column3) 
              FROM   Attributes2 a2
              WHERE  a2.objectsId = o.id";

    $o = " SELECT ID, column1, column2, column3,
                     (%s) as attributes1_result,
                     (%s) as attributes2_result
              FROM   Objects o 
              WHERE  o.id IN (?)";

    $query   = sprintf($o, [$a1, $a2]);
    $results = $this->query($query, $object_ids);

    foreach ($results as &$result) {
        $a1Results = explode(',', $result['attributes1_result']);
        foreach ($a1Results as $a1Result) {
            $columns                = explode('|', $a1Result);
            $resultRow['id']        = $columns[0];
            $resultRow['ObjectsId'] = $columns[1];
            $resultRow['column1']   = $columns[2];
            $resultRow['column2']   = $columns[3];
            $resultRow['column3']   = $columns[4];

            $result['Attributes1'][] = $resultRow;
        }
        unset($result['attributes1_result']);

        $a2Results = explode(',', $result['attributes2_result']);
        foreach ($a2Results as $a2Result) {
            $columns                = explode('|', $a2Result);
            $resultRow['id']        = $columns[0];
            $resultRow['ObjectsId'] = $columns[1];
            $resultRow['column1']   = $columns[2];
            $resultRow['column2']   = $columns[3];
            $resultRow['column3']   = $columns[4];

            $result['Attributes2'][] = $resultRow;
        }
        unset($result['attributes2_result']);
    }

    return $results;
}

标签： mysql