数据库优化的三个例子

在维护旧数据库的时候经常碰到非常的查询，多数都是两方面的原因。
1）没有加索引
2）查询语句导致索引用不上
3）过多的连接数据库

成都创新互联公司2013年开创至今，先为灵璧等服务建站，灵璧等地企业，进行企业商务咨询服务。为灵璧企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

例子1：

在一个大型的计算中原来每天要花费半小时才能完成，对计算的过程进行仔细的分析，发现下面的语句花费了很长时间

select sum(order_qty - delivery_qty - reduce_confirm_qty - lost_qty ) qty from circle_ordering where sku = '" . $sku . "' AND submit_status = 5 AND order_type = 'AIR'

通过explain 这条语句，仔细分析数据库才知道并没有相关的索引作用在这条查询语句上，这样导致了这条sql是全表查询。于是对这三列（sku, submit_status, order_type）新建索引. 重新执行后，整个程序只用了10份钟就完成了。

例子2:

select a.ebay_id, b.ebay_id as ebay_subid, from_unixtime(a.ebay_paidtime) as ebay_paidtime,
                                      a.ebay_account, a.ebay_countryname, c.store_name as warehouse, a.ebay_carrier,
                                      b.sku, b.ebay_amount, a.ebay_currency, b.ebay_itemprice,
                                      b.shipingfee,  ((b.ebay_itemprice*b.ebay_amount)+b.shipingfee) as total_amount, ebay_postcode,
                                      b.item_promotion_discount_amount, b.ship_promotion_discount_amount

                                    from ebay_order a left join ebay_orderdetail b on(a.ebay_ordersn=b.ebay_ordersn) 
                                                      left join ebay_store c on (a.ebay_warehouse = c.id)
                                    where a.ebay_combine !=1 and (a.resend_org_ebay_id=0 or a.resend_org_ebay_id is null) and 
                                          b.ebay_amount >0 and a.ebay_warehouse !='' and a.ebay_user='manwei' 

                                            and                                             

                                          (

                                            a.ebay_paidtime between UNIX_TIMESTAMP('".$astart."') and UNIX_TIMESTAMP('".$aend."') 

                                            or
                                          (a.ebay_paidtime not between UNIX_TIMESTAMP('".$astart_p."') and UNIX_TIMESTAMP('".$aend_p."')  and 
                                           a.shippedtime between UNIX_TIMESTAMP('".$astart_p."') and UNIX_TIMESTAMP('".$aend_p."')) ";

                                        if($last_ebay_id!='') $data .= " or a.ebay_id >='".$last_ebay_id."'";

                                        $data .=  ") order by a.ebay_id, b.ebay_id ";

注意这个复杂的查询语句的条件

第一个条件
(a.ebay_paidtime between UNIX_TIMESTAMP('".$astart."') and UNIX_TIMESTAMP('".$aend."')
由于在ebay_paidtime字段有索引，如果只有这个条件，查询速度很快，查询一次不到一秒。但是因为后面还有两个条件使用了 or, 这样导致会导致了对ebay_order进行了全表查询，而这个表有3百多万条数据，所以查询非常慢。
(有这个说法 ：验证在两个相同字段之间使用or不会导致全表扫描，只有出现不同字段自建使用or时会导致全表扫描。但我没有验证过。)

根据业务需求我们把三个用or 连接的查询条件拆出来，分别进行查询，最后用union语句连起来。这样查询的效率得到了大大的提高。修改后的查询如下

$data1 ="select " . $fields_list . "
                                    from ebay_order a left join ebay_orderdetail b on(a.ebay_ordersn=b.ebay_ordersn) 
                                                      left join ebay_store c on (a.ebay_warehouse = c.id)
                                    where a.ebay_combine !=1 and (a.resend_org_ebay_id=0 or a.resend_org_ebay_id is null) and 
                                          b.ebay_amount >0 and a.ebay_warehouse !='' and a.ebay_user='manwei' 
                                          and  a.ebay_paidtime between UNIX_TIMESTAMP('".$astart."') and UNIX_TIMESTAMP('".$aend."')";

  $data2 = "select " . $fields_list . "
                                    from ebay_order a left join ebay_orderdetail b on(a.ebay_ordersn=b.ebay_ordersn) 
                                                      left join ebay_store c on (a.ebay_warehouse = c.id)
                                    where a.ebay_combine !=1 and (a.resend_org_ebay_id=0 or a.resend_org_ebay_id is null) and 
                                          b.ebay_amount >0 and a.ebay_warehouse !='' and a.ebay_user='manwei' 
                                          and (
                                                a.shippedtime between UNIX_TIMESTAMP('".$astart_p."') and UNIX_TIMESTAMP('".$aend_p."') and
                                                a.ebay_paidtime not between UNIX_TIMESTAMP('".$astart."') and UNIX_TIMESTAMP('".$aend."') 
                                           )";

    if($last_ebay_id!='') {
             $data3 = "select " . $fields_list . "
                                    from ebay_order a left join ebay_orderdetail b on(a.ebay_ordersn=b.ebay_ordersn) 
                                                      left join ebay_store c on (a.ebay_warehouse = c.id)
                                    where a.ebay_combine !=1 and (a.resend_org_ebay_id=0 or a.resend_org_ebay_id is null) and 
                                          b.ebay_amount >0 and a.ebay_warehouse !='' and a.ebay_user='manwei' 
                                          and a.ebay_id >='" .$last_ebay_id ."'";
   }

    $data = "(" . $data1 . ")";
  if($data2 != "") $data = $data . " union (". $data2 . ")";
  if($data3 != "") $data = $data . " union (". $data3 . ")";

小插曲，当我们分析data2的时候，无论如何给shippedtime加索引，只要查询shippedtime都是全表查询。仔细分析才知道原来在数据库设计的时候，这个shippedtime的字段是varchar, 程序把时间戳保存成这种类型，自然没有办法使用适合我们需要的索引，解决的方法是通过alter语句先把shippedtime改成int 类型，再增加一个索引到这个字段。这样这个查询慢的问题就彻底得到解决了。

例子3：

$data = $isfesdb->query($data);
$quan = $isfesdb->num_rows($data);

for($i=0;$i<$quan;$i++){
{
            ...
            $vv             = "select goods_name, goods_weight from ebay_goods where goods_sn='".$sku[$i]."' limit 1";
            $vv             = $isfesdb->execute($vv);
            $vv             = $isfesdb->getResultArray($vv);

            if(count($vv)==0){                      

                            ...
                            $sku[$i]   = str_replace('-FBA-FR','',$sku[$i]);                                    
                            ...

                        }

            ...
}

从代码上看，这个只是很简单的查询，ebay_goods也有索引，应该很快就能查询到结果。但实际上整个流程跑下来很慢。仔细分析原因是因为$quan的数字太大，导致了for循环超过了10000次，这样导致了$vv这个查询进行了10000次。所以单独查一条没有性能问题，但是如果多次重复这样的查询就会引起性能问题。

解决的方法就是在for循环的前面先查询ebay_goods全表，把这个表记录到一个数组，然后在for循环里使用素组的数据。因为ebay_goods这个数组只有几千条记录，这个方法是可行的。
修改程序变成：

$vv = $isfesdb->query("select goods_sn, goods_name, goods_weight from ebay_goods");
$vv_quan = $isfesdb->num_rows($vv);
$vv_result = $isfesdb->getResultArray($vv);

for($i=0; $i<$vv_quan; $i++) {
        $goods_array[$vv_result[$i]['goods_sn']] = array($vv_result[$i]['goods_name'], $vv_result[$i]['goods_weight']); 
        }

for($i=0;$i<$quan;$i++)
{
...

     if(!array_key_exists($sku[$i], $goods_array)){

             ...
             $sku[$i]   = str_replace('-FBA-FR','',$sku[$i]);                                   
             ...

     }

 ...
 }

我们采用数组的方法后，查询也比旧方法效率提高好几倍。这是因为现在我们的服务器配置的内存是足够大的，PHP的运行也是足够快的。瓶颈就在于php在等待MySQL的查询结果。所以我们先用一次查询把数据库结果组成了数组。