wp_order_goods有200多万行的数据,查询特别慢,怎么优化好,贴上sql代码和业务逻辑代码,谢谢。
wp_order_goods有200多万行的数据,查询特别慢,怎么优化好,贴上sql代码和业务逻辑代码
--------------
SELECT `g`.`isgifts`,`og`.`giftssku`,`og`.`giftsname`,`og`.`costprice`,`og`.`amount_real`,`sp`.`v1`,`order`.`uid`,`order`.`openid`,`og`.`supplier_id`,`order`.`order_status`,`order`.`order_amount`,`order`.`second_paid_amount`,`og`.`wenqu`,og.brand_id as brandid,og.cate_id as cateid,`og`.`merchant_id`,`og`.`created`,`order`.`book_time`,`order`.`id`,`order`.`order_sn`,`og`.`sku`,`og`.`number`,`og`.`price`,`og`.`product_amount`,`order`.`consignee`,`order`.`cellphone`,`order`.`gtype`,`og`.`goods_name`,`og`.`sa_name`,`og`.`sv_name`,`og`.`arrival_time`,`og`.`token`,`og`.`og_status`,`order`.`payment_id` FROM `wp_order_goods` `og` INNER JOIN `wp_order` `order` ON `og`.`order_sn`=`order`.`order_sn` LEFT JOIN `wp_goods_spec` `sp` ON `og`.`gid`=`sp`.`gid` LEFT JOIN `wp_goods` `g` ON `og`.`gid`=`g`.`id` WHERE ( order.isdel=0 and og.sku NOT IN ('ZH15224538','ZH15220918') and order.jl_status=0 and og.og_status IN (300,400,500,600,1500,2200,2400) and order.created BETWEEN '1546272000' and '1546358400' )
------------
$orders = Db::table('wp_order_goods')
->alias('og') //订单表
->join('wp_order order', 'og.order_sn=order.order_sn')
->join('wp_goods_spec sp', 'og.gid=sp.gid', 'left')
->join('wp_goods g', 'og.gid=g.id', 'left')
->field('g.isgifts,og.giftssku,og.giftsname,og.costprice,og.amount_real,sp.v1,order.uid,order.openid,og.supplier_id,order.order_status,order.order_amount,order.second_paid_amount,og.wenqu,og.brand_id as brandid,og.cate_id as cateid,og.merchant_id,og.created,order.book_time,order.id,order.order_sn,og.sku,og.number,og.price,og.product_amount,order.consignee,order.cellphone,order.gtype,og.goods_name,og.sa_name,og.sv_name,og.arrival_time,og.token,og.og_status,order.payment_id')
->where($where)
->select();
------------
使用的是thinkphp5.013版本框架,谢谢!