如何优化MySQL视图 [英] How to optimize MySQL Views

问题描述

我有一些使用视图的查询，这些查询的运行速度比给定所有相关表的索引要慢得多(无论如何也不会那么大).

I have some querys using views, and these run a lot slower than I would expect them to given all relevant tables are indexed (and not that large anyway).

我希望我能解释一下:

我的主要查询看起来像这样(大大简化了)

My main Query looks like this (grossly simplified)

select [stuff] from orders as ord 
left join calc_order_status as ors on (ors.order_id = ord.id)

calc_order_status是一个视图，其定义如下:

calc_order_status is a view, defined thusly:

create view calc_order_status as
select ord.id AS order_id,
(sum(itm.items * itm.item_price) + ord.delivery_cost) AS total_total
from orders ord 
left join order_items itm on itm.order_id = ord.id
group by ord.id

订单(ord)包含订单，order_items包含与每个订单相关联的单个项目及其价格.

Orders (ord) contain orders, order_items contain the individual items associated with each order and their prices.

所有表都已正确索引，但是事情运行缓慢，当我做一个解释时，我得到了

All tables are properly indexed, BUT the thing runs slowly and when I do a EXPLAIN I get

  # id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
  1 1 PRIMARY ord ALL customer_id NULL NULL NULL 1002 Using temporary; Using filesort 
  2 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1002   
  3 1 PRIMARY cus eq_ref PRIMARY PRIMARY 4 db135147_2.ord.customer_id 1 Using where 
  4 2 DERIVED ord ALL NULL NULL NULL NULL 1002 Using temporary; Using filesort 
  5 2 DERIVED itm ref order_id order_id 4 db135147_2.ord.id 2   

我的猜测是，"derived2"是指视图.单个项目(itm)似乎可以正常工作，并按订单_ id索引.问题似乎出在第4行上，这表明系统未在订单表(ord)中使用键.但是在MAIN查询中，订单ID已经定义: 左连接calc_order_status作为ors(or.order _ id = ord.id) 和ord.id(在主查询中和视图中)均指向主键.

My guess is, "derived2" refers to the view. The individual items (itm) seem to work fine, indexed by order _ id. The problem seems to be Line # 4, which indicates that the system doesn't use a key for the orders table (ord). But in the MAIN query, the order id is already defined: left join calc_order_status as ors on (ors.order _ id = ord.id) and ord.id (both in the main query and within the view) refer to the primary key.

我在某处阅读过，MySQL的简单性不能很好地优化视图，即使在可用的情况下，在某些情况下也可能无法利用键.这似乎是其中一种情况.

I have read somewhere than MySQL simpliy does not optimize views that well and might not utilize keys under some conditions even when available. This seems to be one of those cases.

任何建议，我将不胜感激.有没有一种方法可以迫使MySQL实现它比您想象的要简单得多，只需要使用主键就可以了"?还是观点完全是错误的方法?

I would appreciate any suggestions. Is there a way to force MySQL to realize "it's all simpler than you think, just use the primary key and you'll be fine"? Or are views the wrong way to go about this at all?

推荐答案

如果有可能删除这些联接，则将其删除.用子查询替换它们可以大大提高速度.

If it is at all possible to remove those joins remove them. Replacing them with subquerys will speed it up a lot.

您还可以尝试运行类似这样的命令，以查看它是否完全有速度差异.

you could also try running something like this to see if it has any speed difference at all.

select [stuff] from orders as ord 
left join (
  create view calc_order_status as
  select ord.id AS order_id,
  (sum(itm.items * itm.item_price) + ord.delivery_cost) AS total_total
  from orders ord 
  left join order_items itm on itm.order_id = ord.id
  group by ord.id
) as ors on (ors.order_id = ord.id) 

这篇关于如何优化MySQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！