php - 在php View 中组合MYSQL查询

Question

我的任务是制作一个网络应用程序来替换由于用户数量不断增加而不断锁定的 Excel 预订表。预订数据库(大约有一百个可能的 Assets )看起来像这样:

+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| ID                 | int(11)       | NO   | PRI | 0       |       |
| bookedDate         | date          | YES  |     | NULL    |       |
| bookedBy           | varchar(256)  | YES  |     | NULL    |       |
| startDate          | date          | YES  |     | NULL    |       |
| endDate            | date          | YES  |     | NULL    |       |
| Equipment1         | varchar(512)  | YES  |     | NULL    |       |
| Equipment2         | varchar(512)  | YES  |     | NULL    |       |
| ...                |               |      |     |         |       |
| Equipment15        | varchar(512)  | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+

因此，用户在 1d-1Y 期间最多可以预订 15 项 Assets 。

在我的 php 页面上，显示 View (仅显示预订的 Assets )如下所示:

        <<< << < Prev week  Bookings for week 49 - 2014 Next week > >> >>>
      Monday     Tuesday    Wednesday   Thursday  Friday     Saturday    Sunday
     01/12/2014 02/12/2014 03/12/2014 04/12/2014 05/12/2014 06/12/2014 07/12/2014
---------------------------------------------------------------------------------
Eq1  | fred                      tom      tom     tom                    harry
Eq4  | tom        tom                                        frank
Eq66 |                                                       tom          tom
...
Eq832|                          harry

这一切都有效，但页面非常慢。 20-30 秒即可生成包含约 50 个 Assets 的页面选择显示哪些 Assets 的代码(大约需要 3 秒)是:

function read_Equipment($sDate,$eDate){
    $query = mysql_query("SELECT Equipment1 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment2 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment3 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment4 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment5 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment6 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment7 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment8 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment9 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment10 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment11 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment12 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment13 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment14 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' union 
        SELECT Equipment15 FROM oldDB WHERE startDate<'$sDate' AND endDate>'$eDate' 
        order by Equipment1")or die();
    while($ad= mysql_fetch_array($query)){
        if($ad['Equipment1'] != "")
        {
            $rdata[$ad['Equipment1']] = $ad['Equipment1'];
        }
    }
    return($rdata);
}

获取每天的代码(大约需要 120 毫秒，总共大约 20 秒)如下所示

function read_booking($equipment,$sDate,$eDate){
    $query = mysql_query("SELECT * FROM oldDB WHERE startDate<'$sDate' AND endDate>='$eDate' AND (
        Equipment1 = '$equipment' OR 
        Equipment2 = '$equipment' OR 
        Equipment3 = '$equipment' OR 
        Equipment4 = '$equipment' OR 
        Equipment5 = '$equipment' OR 
        Equipment6 = '$equipment' OR 
        Equipment7 = '$equipment' OR 
        Equipment8 = '$equipment' OR 
        Equipment9 = '$equipment' OR 
        Equipment10 = '$equipment' OR 
        Equipment11 = '$equipment' OR 
        Equipment12 = '$equipment' OR 
        Equipment13 = '$equipment' OR 
        Equipment14 = '$equipment' OR 
        Equipment15 = '$equipment'
        )")or die();
    $rdata = "";
    while($ad = mysql_fetch_array($query)){
        if($ad != "")
        {
            $rdata += $ad['ID']." - ";
        }
    }
    return($rdata);
}

将它们放在一起的代码如下所示

//$time_i = time at start of the week
$tepp = read_Equipment($sDate,$eDate);
foreach($tepp as $time){
    echo '<tr><th class="booking_time_th">' . $time . '</th>';
    $i = 0;
    while($i < 7){
        $i++;
        $msc=microtime(true);
        $strtmp = read_booking($time,date('Y-m-d', $time_i+($i)*24*3600),date('Y-m-d', $time_i+($i-1)*24*3600));
        echo '<td>'.(microtime(true)-$msc).'s<div class="booking_time_div">
        <div class="booking_time_cell_div" id="div:' . $strtmp . '" onclick="void(0)">' . $strtmp. '</div></div></td>';
    }
    echo '</tr>';
}
echo '</table>';

微时间只是用来跟踪进度，不会对总长度产生太大影响。

所以我的问题是如何更好地组合 sql 查询，以便不存在数百个小查询(0.1 毫秒时数百个，总共 20-30 秒)？我应该更好地重新排列数据库吗？

Answer 1

我个人会给每个 Assets 一个“id”并将其表标记为 Assets 。然后我将创建一个设备表，其中的第一列是“asset_id”。 “asset_id”将是 Assets 表中“id”列的外键。这会创建从特定 Assets 到设备表中任意数量的行的链接，从而有效消除当前 15 件设备的限制。这是一个EAV表结构。

话又说回来，我会使用 Laravel 4 中的 Eloquent 或 CodeIgniter 的 ActiveRecord :https://ellislab.com/codeigniter/user-guide/database/active_record.html

考虑使用框架构建您的系统。

编辑:索引还将加快您的搜索速度。我将对开始日期、结束日期以及搜索中使用的任何字段(当前是所有设备字段)建立索引。但是，请注意，虽然这会加快 SELECT 查询的速度，但会稍微减慢对此表的 INSERT 查询的速度。另外，如果您确实切换到像我上面描述的那样的 EAV 结构，您可以索引更少的列来获得相同的、更快的结果。