PHP+MYSQL 多表查询并显示结果集，如何优化？

CR2208 2019-01-06 03:51:43

想通过多表查询来显示一张完整的条码对应产品表（如图8），使用多表关联查询，总是显示查询超时或是过了好久才显示出来，而且在进行表单过滤查询时，十分不方便。大家有什么好的建议吗？是需要优化数据库呢？还是在查询语句上进行优化或改变？还是显示语句上改变呢？有时也在思考，是否应该不要多表查询，就三表查询，另外的元素（如规格，颜色，类型，单位等信息）再查询出来存放数组，显示时再对应从ID解析出文字呢？大侠请支招，谢谢！表

* 表ean-存放条码对应的SKU编号
* 表product-存在产品信息
* 表sku-存放SKU对应的产品信息
* 表specification-存放规格信息
* 表color-存放颜色信息
* 表type-存放产品类别信息
* 表units-存放单位信息

多表查询



//从数据库里提取数据获取结果集

$_result = _query("SELECT

                            `fcoa_ean`.ean_ean,

                            `fcoa_ean`.ean_sku,

                            `fcoa_product`.product_kis,

                            `fcoa_product`.product_number,

                            `fcoa_product`.product_item,

                            `fcoa_type`.type_name,

                            `fcoa_product`.product_name,

                            `fcoa_color`.color_name,

                            `fcoa_specification`.spec_name,

                            `fcoa_sku`.sku_date

                    FROM    fcoa_ean

                    LEFT JOIN  `fcoa_sku` ON  `fcoa_ean`.ean_sku =  `fcoa_sku`.sku_sku

                    LEFT JOIN  `fcoa_product` ON  `fcoa_sku`.sku_product_id =  `fcoa_product`.product_id

                    LEFT JOIN  `fcoa_color` ON  `fcoa_sku`.sku_color =  `fcoa_color`.color_id

                    LEFT JOIN  `fcoa_specification` ON  `fcoa_sku`.sku_spec =  `fcoa_specification`.spec_id

                    LEFT JOIN  `fcoa_type` ON  `fcoa_product`.product_type =  `fcoa_type`.type_id

                    WHERE `fcoa_ean`.ean_sku IS NOT NULL $_html_where

                    ORDER BY `fcoa_ean`.ean_id 

                    LIMIT $_pagenum,$_pagesize

    ");

显示结果



<div id="table_a">

    <h3>条码信息</h3>

    <table cellspacing="1">

    <tr>

        <td colspan="11" class="select1">

            <form method="post" action="?action=select_on">

            <input type="hidden" name="One_Select" value="1"></input>

            <ul>

                <li>类别：<?php echo $_html['type_html']?>　　</li>

                <li>查询：<?php echo $_html['select_html']?></li>

                <li><?php echo $_html['select_text']?>　　</li>

                <li><input type="submit" class="submit" value="搜索" /></li>

            </ul>

            </form>

        </td>

    </tr>

    <tr><th>序号</th><th>条码</th><th>SKU编号 </th><th>金蝶代码</th><th>产品货号</th><th>产品型号</th><th>类别</th><th>品名</th><th>颜色</th><th>规格</th><th>建立日期</th></tr>

    <?php 

    $_html = array();

    $_i = 1;    

    while(!!$_rows = _fetch_array_list($_result)) {

        $_html[ean_ean] = $_rows[`fcoa_ean`.ean_ean];

        $_html[ean_sku] = $_rows[`fcoa_ean`.ean_sku];

        $_html[product_kis] = $_rows[`fcoa_product`.product_kis];

        $_html[product_number] = $_rows[`fcoa_product`.product_number];

        $_html[product_item] = $_rows[`fcoa_product`.product_item];        

        $_html[type_name] = $_rows[`fcoa_type`.type_name];

        $_html[product_name] = $_rows[`fcoa_product`.product_name];

        if ($_rows[`fcoa_color`.color_name] == '无') {

            $_html[color_name] = '';

        } else {

            $_html[color_name] = $_rows[`fcoa_color`.color_name];

        };

        $_html[spec_name] = $_rows[`fcoa_specification`.spec_name];        

        $_html[sku_date] = substr($_rows[`fcoa_sku`.sku_date],0,16);

        $_html = _html($_html);

        $color_z = '';

        $zhiz = $_i%2;

        if ($zhiz == '1'){

            $color_z ='class="rn1"'; 

        } else {

            $color_z ='class="rn2"';

        }

        ?>

        <tr <?php echo $color_z;?>>

        <td><?php echo $_i?></td>

        <td><?php echo $_html[ean_ean]?></td>

        <td><?php echo $_html[ean_sku]?></td>

        <td><?php echo $_html[product_kis]?></td>

        <td><?php echo $_html[product_number]?></td>

        <td><?php echo $_html[product_item]?></td>

        <td><?php echo $_html[type_name]?></td>

        <td><?php echo $_html[product_name]?></td>

        <td><?php echo $_html[color_name]?></td>

        <td><?php echo $_html[spec_name]?></td>

        <td><?php echo $_html[sku_date]?></td>

        </tr>

    <?php

        $_i++;

        }

        _free_result($_result);

    ?>

    </table> 

    <?php

        //调取分页模块 

        _paging_url(2);

    ?>

</div>