MySQL查询优化5

Question

我正在开发一个使用JAVA + MySQL(maria db)的会计软件。我用下面的查询计算了查询量，当我运行查询时，查询花费了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

库存=(进发票金额+进发货金额)-(出发票金额+出发货金额)+(盘点收货金额)

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

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) 上建立索引。

我还注意到您使用的是 LIMIT 而没有 ORDER BY。您确实意识到 SQL 结果集是无序的，除非它们有显式的ORDER BY。