MySQL查询（28K行）重载服务器

Question

We are running OpenCart store and now we have to export order data for 6 months (around 17K orders).

Opencart has a builtin solution, when you select the orders you need and export, but it works fine with less than 500 orders.

I've decided to make a standalone script based on the query it uses for its original export.

I'm not publishing the export part, since my dedicated server and the store freezes when I run the following query and consequent loop for mysql_fetch_assoc.

   $query  = "SELECT o.*, op.name, op.model,op.order_product_id, op.product_id, op.quantity, op.price, op.total AS ptotal, op.tax,
  (SELECT ot.value FROM order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total') AS sub_total,
  (SELECT ot.value FROM order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'credit') AS store_credit,
  (SELECT `name` FROM order_history oh INNER JOIN `order_status` os on oh.order_status_id=os.order_status_id WHERE oh.order_id = o.order_id and os.language_id='2'
   ORDER BY order_history_id DESC LIMIT 1) as order_status 
   FROM `order` o inner join `order_product` op on o.order_id=op.order_id  WHERE o.date_added BETWEEN '2014-01-01 00:00:00' AND '2014-06-30 03:59:59'";

             $sql = mysql_query($query);

             do {

             echo $o['model']."<br/>"; // here we will have a part using PEAR Excel basically

             } while ($o=mysql_fetch_assoc($sql));
             $endtime = microtime();

             echo $endtime-$starttime;

Indexes are set. Additional index was set on oh.order_id. No luck. The same request for 1 particular order id runs in 0.003ms.

My process list shows "Sending data" for the above shown query and "Waiting for table level lock" for anothers.

Could you kindly assist?

Answer 1

I think you could optimize your SQL a bit to prevent locks. The following might help, but recognize that I don't have your schema or data to test this on, so you may need to tweak.

SELECT o.*, op.name, op.model,op.order_product_id, op.product_id,
       op.quantity, op.price, op.total AS ptotal, op.tax,
       ot.sub_total, ot.store_credit, os.os_name 
FROM `order` o inner join `order_product` op on o.order_id=op.order_id
INNER JOIN (SELECT t.order_id,
    MAX(case when t.code = 'sub_total' t.value else 0 end case) as sub_total, 
    MAX(case when t.code = 'credit' t.value else 0 end case) as store_credit 
  FROM order_total t GROUP BY t.order_id) ot ON ot.order_id = o.order_id
INNER JOIN (SELECT * FROM (SELECT oh.order_id, `name` AS os_name
  FROM order_history oh 
     INNER JOIN `order_status` s on oh.order_status_id=s.order_status_id
     WHERE s.language_id='2' ORDER BY order_history_id DESC) t1 
   GROUP BY t1.order_id) as os ON os.order_id = o.order_id
WHERE o.date_added BETWEEN '2014-01-01 00:00:00' AND '2014-06-30 03:59:59'

I would also probably do a create temp table temp_order_history from select ... and then select * from temp_order_history Also, you may need to do outer join instead of inner join if either of those sub selects have missing data. In those cases, you'd just get NULLs for those columns.