如何优化 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 on (ors.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屋！