mysql - 具体查询优化

Question

我不太了解如何优化数据库查询以获得更好的速度和性能，我有这个 SQL，据我所知，这个查询非常消耗性能:

SELECT *, c.coefficient FROM tickets t INNER JOIN draws d 
  ON(FIELD(t.b1,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b2,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b3,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b4,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b5,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b6,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
INNER JOIN coefficients c ON c.number = GREATEST(
FIELD(t.b1,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b2,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b3,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b4,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b5,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b6,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)
) 
WHERE...

任何人都可以给我优化此查询的提示

Answer 1

试试这个，看看是否更快。尝试减少FIELD()函数调用的工作量

SELECT td.*,c.coefficient
FROM
    (SELECT *,
    FIELD(t.b1,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match1,
    FIELD(t.b2,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match2,
    FIELD(t.b3,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match3,
    FIELD(t.b4,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match4,
    FIELD(t.b5,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match5,
    FIELD(t.b6,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match6
    FROM tickets t
    CROSS JOIN draws d
    HAVING match1>0 AND match2>0 AND match3>0 AND match4>0 AND match5>0 AND match6>0
    )td
INNER JOIN coefficients c ON c.number =
    GREATEST(match1,match2,match3,match4,match5,match6)
WHERE ...

注意 tickets t 和 draws d 现在已合并以创建 td，因此在您的 WHERE 中您可能必须引用 td 而不是 t 和 d。