求PHP高效显示表中记录（万条以上）

Question

我用以下方法，不到1万条记录却用了近3分钟才显示出来！效率太低！求高手指教！谢谢！！
<?php
  $sql="SELECT Premises_ID,RoomName,BedName,Sex,Department,Lived,Available,Editor,EditTime,IP FROM lr_accommodation WHERE Premises_ID=".$P_ID." ORDER BY RoomName";//已对RoomName建立了索引
                                    $result=mysqli_query($db->conn,$sql);
                                    if(empty($result)){
                                        _location("还没有入住表信息，请添加入住表！","add-live.php?pid=".$P_ID);
                                    }//_location是自义函数
                                    while ($_info = mysqli_fetch_assoc($result)){ //返回查询结果到数组
                                        $In_ID=$_info['In_ID'];
                                        $P_ID=$_info['Premises_ID'];
                                        $_BuildingName=$_info['RoomName'];
                                        if($_BuildingName=='洗衣房'){
                                            $_BuildingName=$db->getBuildingName(($_info['Building_ID']));
                                        }else{
                                            $_BuildingName=substr($_BuildingName,0,2)."栋";
                                        }//getBuildingName是自定义数数，取楼栋名
                                        $_Department=$_info['Department'];
                                        if(empty($_Department)){
                                            $_Department='不限';
                                        }
                                        $_Lived=$_info['Lived'];
                                        if(empty($_Lived)){
                                            $_Lived='否';
                                        }else{
                                            $_Lived='是';
                                        }
                                        $_Available =$_info['Available'];
                                        if($_Available){
                                            $_Available='是';
                                        }else{
                                            $_Available='否';
                                        }
                                        ?>
                                        <tr>
                                            <td><?php echo $_BuildingName;?></td>
                                            <td><?php echo $_info['RoomName'];?></td>
                                            <td><?php echo $_info['BedName'];?></td>
                                            <td><?php echo $_info['Sex'];?></td>
                                            <td><?php echo $_Department;?></td>
                                            <td><?php echo $_Lived;?></td>
                                            <td><?php echo $_Available;?></td>
                                            <td>
                                                <div class='controls center' >
                                                    <a href = "live-edit.php?lid=<?php echo $In_ID?>&&pid=<?php echo $P_ID;?>" title = " 编辑床位信息 " class="tip" ><span class="icon12 icomoon-icon-pencil" ></span ></a >
                                                    <a href = "#" title = "删除床位" class="tip Del-Confirm<?php echo $In_ID?>"><span class="icon12 icomoon-icon-remove"></span ></a >
                                                </div>
                                            </td >
                                        </tr >
<?php } ?>

Answer 1

分页显示，根据页数每次只显示N条记录，下一页再显示后面的N条记录加两个参数page,pagesize$page = isset($_REQUEST['page'])? $_REQUEST['page'] : 1; // 当前页
$pagesize = 20; // 每页记录数$offset = ($page-1)*$page;// 查询增加limit参数做分页
  $sql="SELECT Premises_ID,RoomName,BedName,Sex,Department,Lived,Available,Editor,EditTime,IP FROM lr_accommodation WHERE Premises_ID=".$P_ID." ORDER BY RoomName  limit ".$offset.",".$pagesize;

Answer 2

一次显示太多的记录？没必要，也不应该
赋值应在 SQL 指令中进行，而不是取出后再去处理

Answer 3

一次太多记录，内存是有限的，怎么能保证不超出了。如果你只使用一次，可以调大可用内存<?php
ini_set('memory_limit', '256m');
?>

Answer 4

getBuildingName是不是做数据库查询的？
如果getBuildingName是做单条记录查询的，你相当于总共做了记录数+1次查询。如果BuildingName记录不是很多，一次性读进内存或用任何cache方式（本地文件也可以）；如果记录数很多，看看join的效率后再决定。P.S. 非要逻辑和现实粘连在一起的，建议用for...endfor和if...endif这种。