在维护旧数据库的时候经常碰到非常的查询, 多数都是两方面的原因
1)没有加索引
2)查询语句导致索引用不上
3)过多的连接数据库
例子 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 连接的查询条件拆出来, 分别进行查询, 最后用 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 的查询结果所以我们先用一次查询把数据库结果组成了数组
来源: http://www.bubuko.com/infodetail-2544901.html