首页 文章

使用索引表Mysql进行慢查询

提问于
浏览 次
0

我一直在努力提高我的一个数据库（MySQL）查询的性能 . 事情就是我做的，我的表现仍然很慢 . 在我的更改之前，Query运行在9.54s，并且在一些索引更改之后，性能提高到5.67s .

这是我的查询：

Query_time：5.343565 Lock_time：0.000302 Rows_sent：100005 Rows_examined：200017

码：

SET timestamp=1455032448;
SELECT 
     id, 
     description,
     unit_price, 
     (SELECT coalesce(sum(quantity),0) from si_invoice_items where product_id = si_products.id) as qty_out ,
     (SELECT coalesce(sum(quantity),0) from si_inventory where product_id = si_products.id) as qty_in ,
     (SELECT coalesce(reorder_level,0)) as reorder_level ,
     (SELECT qty_in - qty_out ) as quantity,
     (SELECT (CASE  WHEN enabled = 0 THEN 'Disabled' ELSE 'Enabled' END )) AS enabled
FROM 
    si_products  
WHERE 
    visible = 1
    AND domain_id = '1'
ORDER BY 
    description asc;

这是受影响的所有表的索引信息：

Table Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type   
si_products 0 PRIMARY 1 id A 100005 NULL NULL  BTREE   
si_products 0 PRIMARY 2 domain_id A 100005 NULL NULL  BTREE   
si_products 1 unit_price 1 unit_price A 10000 NULL NULL YESBTREE   
si_products 1 description 1 id A 100005 NULL NULL  BTREE   
si_products 1 description 2 description A 100005 15 NULL  BTREE 

Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment   
si_inventory 0 PRIMARY 1 domain_id A NULL NULL NULL  BTREE   
si_inventory 0 PRIMARY 2 id A 0 NULL NULL  BTREE   
si_inventory 1 product_id 1 product_id A NULL NULL NULL  BTREE   
si_inventory 1 quantity 1 quantity A NULL NULL NULL  BTREE   

Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment   
si_invoice_items 0 PRIMARY 1 id A NULL NULL NULL  BTREE   
si_invoice_items 0 PRIMARY 2 invoice_id A 7 NULL NULL  BTREE   
si_invoice_items 1 unit_price 1 unit_price A NULL NULL NULL YES BTREE   
si_invoice_items 1 quantity 1 quantity A NULL NULL NULL  BTREE   
si_invoice_items 1 product_id 1 product_id A NULL NULL NULL YES

任何建议将不胜感激 .

随着我的最后一次改变有所改善

Query_time：3.723339 Lock_time：0.000254 Rows_sent：100005 Rows_examined：200024 Rows_affected：0

SET timestamp=1455037952;
SELECT  
     A.id, 
     A.description,
     A.unit_price, 
     (SELECT coalesce(sum(B.quantity),0) from si_invoice_items B JOIN si_products A ON B.product_id = A.id) as qty_out ,
     (SELECT coalesce(sum(C.quantity),0) from si_inventory C JOIN si_products A ON C.product_id = A.id) as qty_in ,
     (SELECT coalesce(A.reorder_level,0)) as reorder_level ,
     (SELECT qty_in - qty_out ) as quantity,
     (CASE  WHEN A.enabled = 0 THEN 'Disabled' ELSE 'Enabled' END ) AS enabled
FROM 
    si_products A
WHERE 
    A.visible = 1
    AND A.domain_id = '1'

ORDER BY 
    description asc;

谢谢Ollie，我已经尝试用您的建议测试Query，但显然没有改善响应时间 .

这是您的方法的结果：

# Query_time: 4.041339  Lock_time: 0.000245  Rows_sent: 100005  Rows_examined: 200029
# Rows_affected: 0
SET timestamp=1455045101;
SELECT p.id, p.description, p.unit_price,
    COALESCE(invoice.quantity,0) as qty_out,
    COALESCE(inventory.quantity,0)as  qty_in,
    coalesce(p.reorder_level,0) as reorder_level ,
    (select qty_in - qty_out ) as quantity

  FROM si_products p
  LEFT JOIN (
            SELECT SUM(quantity) quantity, product_id
                 FROM si_invoice_items 
                GROUP BY product_id
    ) invoice ON p.id = invoice.product_id
  LEFT JOIN (
            SELECT SUM(quantity) quantity, product_id
                 FROM si_inventory 
                GROUP BY product_id
    ) inventory ON p.id = inventory.product_id
 WHERE p.visible = 1
   AND p.domain_id = '1'

 ORDER BY            
         description asc;
mysql database query-performance

3 回答

  • 0

    由于您提供了多少（ Rows_sent: 100005 ），查询不能非常快 .

    这些索引中的任何一个都可能有助于一些：

    INDEX(visible, domain_id, description)
INDEX(domain_id, visible, description)

    前两个字段可能有助于过滤;第三个应该防止 ORDER BY 需要"filesort" .

    当表达式足够时，不要使用子查询 . 例如：

    (SELECT (CASE  WHEN enabled = 0 THEN 'Disabled' ELSE 'Enabled' END )) AS enabled

    IF(enabled, 'Enabled', 'Disabled') AS enabled

    如需进一步讨论，请提供 SHOW CREATE TABLE 和 EXPLAIN SELECT... .

    回复于
  • 0

    您可以重构此查询以消除相关子查询（SELECT子句中的嵌套SELECT） . 如果使用JOINed summary子查询替换这些相关子查询，则性能可能会提高 .

    看起来您需要 si_invoice_items 和 si_inventory 表的摘要 .

    您可以使用以下子查询获取这些摘要：

    SELECT SUM(quantity) quantity, product_id
                 FROM si_invoice_items 
                GROUP BY product_id

    和

    SELECT SUM(quantity) quantity, product_id
                 FROM si_inventory 
                GROUP BY product_id

    然后，您可以将这两个子查询视为虚拟表，并将它们连接到 si_products 表 . 请注意，您需要使用LEFT JOIN，因为您的某些 si_products 行可能在其他表中没有匹配的行 .

    像这样 .

    SELECT p.id, p.description, p.unit_price,
       COALESCE(invoice.quantity,0) qty_out,
       COALESCE(inventory.quantity,0) qty_in,
       etc, etc
  FROM si_products p
  LEFT JOIN (
               SELECT SUM(quantity) quantity, product_id
                 FROM si_invoice_items 
                GROUP BY product_id
       ) invoice ON p.id = invoice.product_id
  LEFT JOIN (
               SELECT SUM(quantity) quantity, product_id
                 FROM si_inventory 
                GROUP BY product_id
       ) inventory ON p.id = inventory.product_id
 WHERE p.visible = 1
   AND p.domain_id = '1'
 ORDER BY p.description asc;

    这将只运行两个表上的摘要，然后加入它们 . 所以它应该运行得更快 .

    Edit 您正在使用此查询获取超过100K行 . 这是一个非常大的结果集，无论如何都需要一段时间 .

    通过在清单和发票表的 (product_id, quahtity) 列上定义复合索引，可以使摘要（GROUP BY）子查询更快 .

    通过在 (visible, domain_id) 列上定义复合索引，可以更快地从product表中选择记录 .

    但是你的应用程序仍然需要研磨十万行 .

    回复于
  • 1

    我找不到比此查询更好的结果：

    Query_time：3.174778 Lock_time：0.000250 Rows_sent：100005 Rows_examined：200024

    Rows_affected：0

    SET timestamp=1455157468;
SELECT  
     A.id, 
     A.description,
     A.unit_price, 
     (SELECT coalesce(sum(B.quantity),0) from si_invoice_items B JOIN si_products A ON B.product_id = A.id) as qty_out ,
     (SELECT coalesce(sum(C.quantity),0) from si_inventory C JOIN si_products A ON C.product_id = A.id) as qty_in ,
     (SELECT coalesce(A.reorder_level,0)) as reorder_level ,
     (SELECT qty_in - qty_out ) as quantity,
     (CASE  WHEN A.enabled = 0 THEN 'Disabled' ELSE 'Enabled' END ) AS enabled
 FROM 
     si_products A
 WHERE 
     A.visible = 1
     AND A.domain_id = '1'

 ORDER BY 
     description asc;
    回复于

相关问题