php - MySQL从5.0升级到5.5后的查询时间

Question

我刚刚将 MySQL 版本从 5.0 更新到 5.5，但我的一些查询出现了问题。它不仅需要很长时间来执行，而且似乎正在检查每一行。我需要一些帮助来清理这个查询，因为我不是最擅长使用 MySQL 的。我可能可以向几个表添加几个索引，但我希望这个查询得到更好的优化。查询如下

# Query_time: 112.139527  Lock_time: 0.000366 Rows_sent: 24  Rows_examined: 133007191
SET timestamp=1395362602;
SELECT 
      item_id, parent_item_id, refer_item, item_name, doctype, fullpath, pos,     sale_end_date, pimage_list_src, pimage_list_width, pimage_list_height, 
      pimage_cart_src, pimage_cart_width, pimage_cart_height, sku, smalldescr, prefix, prefix_type, use_prefix, pref_name, price 
    FROM
    (
        SELECT 
          DISTINCT t.`item_id`, t.`parent_item_id`, t.item_name, t.refer_item, t.doctype, t.fullpath, t.pos, d.pimage_list_src, 
          d.pimage_list_width, d.pimage_list_height, d.pimage_cart_src, d.pimage_cart_width, d.pimage_cart_height, 
          d.sku, d.smalldescr, d.prefix, d.prefix_type, d.use_prefix, pref.pref_name,   d.sale_end_date,
          IF(d.sale_end_date>now(), (SELECT pg.`price_sale` 
                                      FROM `price_grid` as pg 
                                      WHERE pg.`item_id`=t.`item_id` and pg.`price_sale`<>0 
                                      ORDER BY pg.`price_sale` ASC LIMIT 1),

                                      (SELECT pg.`price` 
                                      FROM `price_grid` as pg 
                                      WHERE pg.`item_id`=t.`item_id` 
                                      ORDER BY pg.`price` ASC LIMIT 1)) as price 
        FROM `treeman` as t
        INNER JOIN `dt_product` as d ON d.`item_id`=t.`item_id`
        LEFT JOIN `prefixes` as pref ON pref.`pref_id`=d.`prefix`
        WHERE t.parent_item_id IN (SELECT t2.item_id FROM treeman as t2 WHERE t2.parent_item_id=97 AND t2.item_type=0) AND t.active=1 
          OR t.parent_item_id=97 AND t.item_type=1 AND t.active=1

        UNION

        SELECT 
          DISTINCT t.`item_id`, t.`parent_item_id`, t.item_name, t.refer_item, t.doctype, t.fullpath, t.pos,  
          d.pimage_list_src, d.pimage_list_width, d.pimage_list_height, d.pimage_cart_src, d.pimage_cart_width, d.pimage_cart_height, 
          d.sku, d.smalldescr, d.prefix, d.prefix_type, d.use_prefix, pref.pref_name,  d.sale_end_date,
          IF(d.sale_end_date>now(), (SELECT pg.`price_sale` 
                                      FROM `price_grid` as pg 
                                      WHERE pg.`item_id`=t.`item_id` and pg.`price_sale`<>0 
                                      ORDER BY pg.`price_sale` ASC LIMIT 1),

                                      (SELECT pg.`price` 
                                      FROM `price_grid` as pg 
                                      WHERE pg.`item_id`=t.`item_id` 
                                      ORDER BY pg.`price` ASC LIMIT 1)) as price  
        FROM `treeman` as t_ref
        INNER JOIN `treeman` as t ON t_ref.`refer_item`=t.`item_id`
        INNER JOIN `dt_product` as d ON d.`item_id`=t.`item_id`
        LEFT JOIN `prefixes` as pref ON pref.`pref_id`=d.`prefix`
        WHERE t_ref.parent_item_id = 97 and t.active=1 and t.item_type=1
    ) u
    ORDER BY  price ASC   LIMIT 0, 24;

Answer 1

您应该运行 EXPLAIN 函数并确定它是否使用了错误的索引字段。

我发现同样的事情发生在我身上，我确定了更好的索引字段并添加了

USE INDEX (index_list)

告诉 MySQL 使用与自动选择的索引不同的索引