MySQL查询优化5

Question

我正在用JAVA + MySQL (maria )开发一个会计软件。我用下面的查询计算了查询的数量，当我运行查询时，查询花费了16秒。查询时长正常吗？我的问题出错了吗？

SELECT products_id as ID,prod_name as 'Product Name',
       IFNULL((SELECT sum(piece)
               FROM `ktgcari_000_fatura_xref` 
               WHERE product_id = ktgcari_000_stok.products_id AND
                     (type = 1 or type = 4)
              ), 0) -
       IFNULL((SELECT sum(piece)
               FROM `ktgcari_000_fatura_xref` 
               WHERE product_id = ktgcari_000_stok.products_id AND 
                     (type = 2 or type = 5)
              ), 0) +
       IFNULL((SELECT sum(piece)
               FROM ktgcari_000_ssayim 
               WHERE urun_id = ktgcari_000_stok.products_id
               ), 0) as stock
FROM ktgcari_000_stok
LIMIT 0,1000

Stock=(入库发票总和+入库派单总和)-(出库发票总和+出库派单合计)+(盘点收据合计)

数据库信息:存量卡数: 39000张发票数: 545张发票内容表盘点: 1800张点票指数: 942张数据库大小:5MB

​

​

Answer 1

我会把这个查询写成：

SELECT s.products_id as ID, s.prod_name as `Product Name`,
       (COALESCE((SELECT SUM(CASE WHEN x.type IN (1, 4) THEN piece
                                  WHEN x.type IN (2, 5) THEN - piece
                             END)
                FROM `ktgcari_000_fatura_xref` x
                WHERE x.product_id = s.products_id AND
                      x.type IN (1, 2, 4, 5)
                  ), 0) +
        COALESCE((SELECT SUM(ss.piece)
                  FROM ktgcari_000_ssayim ss
                  WHERE ss.urun_id = s.products_id
                 )), 0
       ) as stock
FROM ktgcari_000_stok s
LIMIT 0, 1000

然后，出于性能考虑，您需要ktgcari_000_fatura_xref(product_id, type, piece)和ktgcari_000_ssayim(urun_id, piece)上的索引。

我还注意到您使用的是不带ORDER BY的LIMIT。您一定知道SQL结果集是无序的，除非它们具有显式的ORDER BY。

Answer 2

我编辑了sql查询，如下所示。查询时间为8秒。我如何才能缩短持续时间。

SELECT products_id as ID，prod_name，(SELECT IF(type=1或type=4，sum(urun_adet),0)-IF(type=2或type=5，sum(urun_adet),0) FROM -IF where sum(miktar) FROM ktgcari_000_ssayim where urun_id=ktgcari_000_stok.products_id),0)作为‘股票’FROM ktgcari_000_stok LIMIT 0,1000

​

​