MySQL - 获取连接表的最新值的最有效方法

Question

考虑以下查询:

SELECT 
  nodos.nombre,
  (SELECT super FROM atributo_16 WHERE nodoid=nodos.nodoid ORDER BY fecha DESC,created_at LIMIT 1) AS descuento_navision_super_cif,
  (SELECT regular FROM atributo_16 WHERE nodoid=nodos.nodoid ORDER BY fecha DESC,created_at LIMIT 1) AS descuento_navision_regular_cif,
  (SELECT diesel FROM atributo_16 WHERE nodoid=nodos.nodoid ORDER BY fecha DESC,created_at LIMIT 1) AS descuento_navision_diesel_cif
FROM 
  nodos 
WHERE 
  nodos.nodotipoid=8;

这工作正常，但速度很慢。在此示例中，查询重复 (3x) 到同一个表并使用相同的 WHERE。真正的查询有 20 个这样的子查询到不同的表。我想优化查询。

---

这是我使用派生表加快速度的尝试之一。创建一个 [fecha, created_at] 索引，它提高了速度，但查询不起作用，因为查询的 LIMIT 1 部分在 JOIN 之前应用，而且我似乎无法添加 nodoid WHERE 语句的一部分，这将解决问题。

SELECT 
  nodos.nombre,
  descuentos.super AS descuento_navision_super_cif,
  descuentos.regular AS descuento_navision_regular_cif,
  descuentos.diesel AS descuento_navision_diesel_cif
FROM 
  nodos 
  LEFT JOIN (SELECT nodoid, super, regular, diesel, ulsd 
    FROM atributo_16 ORDER BY fecha DESC,
    created_at LIMIT 1)descuentos ON descuentos.nodoid=nodos.nodoid
WHERE 
 nodos.nodotipoid=8

已更新这是第一个查询的 EXPLAIN 表。

id  select_type         table        type  possible_keys  key         key_len  ref                             rows  Extra                        
1   PRIMARY             nodos        ref   nodotipoid     nodotipoid  4        const                           226                                
4   DEPENDENT SUBQUERY  atributo_16  ref   nodoid         nodoid      4        nodos.nodoid                    376   Using where; Using filesort  
3   DEPENDENT SUBQUERY  atributo_16  ref   nodoid         nodoid      4        nodos.nodoid                    376   Using where; Using filesort  
2   DEPENDENT SUBQUERY  atributo_16  ref   nodoid         nodoid      4        nodos.nodoid                    376   Using where; Using filesort  

Answer 1

尝试在派生表中使用变量来获取最大每组记录:

SELECT 
  nodos.nombre,
  super AS descuento_navision_super_cif,
  regular AS descuento_navision_regular_cif,
  diesel AS descuento_navision_diesel_cif
FROM 
  nodos AS t1 
LEFT JOIN (
   SELECT super, regular, diesel, nodoid, 
          @rn := IF (@id = nodoid, @rn + 1,
                    IF (@id := nodoid, 1, 1)) AS rn
   FROM atributo_16
   CROSS JOIN (SELECT @rn := 0,  @id := -1) AS vars
   ORDER BY nodoid, fecha DESC,created_at
) AS t2 ON t1.nodoid = t2.nodoid AND t2.rn = 1
WHERE 
  nodos.nodotipoid=8;

我假设 nodoid<>-1 , 所以初始化 @id 是安全的使用这个值。