Оптимизация запросов MySQL по иерархическим данным

Ответ 1

Не тратя время на тестирование, вы предоставили неполный пример? вам следует попробовать переупорядочить объединенные таблицы. Объяснение вывода предоставляет некоторую информацию, допустим, упорядочение по key_len должно быть эвристически быстрым. Я считаю, что первая таблица, которую нужно отфильтровать, должна быть указана как последняя, ​​если оптимизатор не сможет понять это.

Итак, скажем, что 'c, v, k, u' порядок является лучшим.

SELECT DISTINCT
  `v`.`key`,
  `u`.`val`
FROM
  `VertexDictionary`  AS `u`
  JOIN `SpecialKeys`       AS `k` ON (`k`.`x`, `k`.`key`) = (`u`.`x`, `u`.`key`)
  JOIN `VertexDictionary`  AS `v`
  JOIN `ConnectedVertices` AS `c` ON (`u`.`x`, `u`.`y`  ) = (`c`.`tail_x`, `c`.`tail_y`)
           AND (`v`.`x`, `v`.`y`  ) = (`c`.`head_x`, `c`.`head_y`)
WHERE
  `v`.`x` = X
;

'rows' предложит 'c/u, k, v' порядок, но это зависит от данных:

SELECT DISTINCT
  `v`.`key`,
  `u`.`val`
FROM
  `VertexDictionary`  AS `u`
  JOIN `VertexDictionary`  AS `v`
  JOIN `SpecialKeys`       AS `k` ON (`k`.`x`, `k`.`key`) = (`u`.`x`, `u`.`key`)
  JOIN `ConnectedVertices` AS `c` ON (`u`.`x`, `u`.`y`  ) = (`c`.`tail_x`, `c`.`tail_y`)
                                 AND (`v`.`x`, `v`.`y`  ) = (`c`.`head_x`, `c`.`head_y`)
 WHERE
  `v`.`x` = X
;

Надеюсь, что это поможет.

UPDATE (избегая соединения varchar):

SELECT DISTINCT
  `v`.`key`,
  `u`.`val`
FROM
       `ConnectedVertices` AS `c`
  JOIN `VertexDictionary`  AS `u` ON (`u`.`x`, `u`.`y`  ) = (`c`.`tail_x`, `c`.`tail_y`)
  JOIN `VertexDictionary`  AS `v` ON (`v`.`x`, `v`.`y`  ) = (`c`.`head_x`, `c`.`head_y`)
WHERE
  (`u`.`x`, `u`.`key`) IN (SELECT `k`.`x`, `k`.`key` FROM `SpecialKeys` AS `k`)
AND
  `v`.`x` = X
;

Ответ 2

Другие могут не согласиться, но я имел и регулярно предлагаю STRAIGHT_JOIN для запросов... Как только вы ЗНАЕТ данные и отношения. Поскольку это предложение WHERE относится к псевдониму таблицы "V", а значение "x", вы хорошо относитесь к индексу. Переместите ЭТО в переднее положение, затем присоединитесь к нему.

SELECT STRAIGHT_JOIN DISTINCT
      v.`key`,
      u.`val`
   FROM
      VertexDictionary AS v 

         JOIN ConnectedVertices AS c
            ON v.x = c.head_x
            AND v.y = c.head_y

            JOIN VertexDictionary AS u 
               ON c.tail_x = u.x 
               AND c.tail_y = u.y

               JOIN SpecialKeys AS k
                  ON u.x = k.x
                  AND u.key = k.key
   WHERE
      v.x = {some value}      

Любопытно узнать, как эта перестройка работает для вас

Ответ 3

Попробуйте выполнить поэтапный запрос; или, по крайней мере, дать нам еще несколько моментов, чтобы определить, где узкие места. Некоторые комбинации следующих запросов должны дать вам разумную производительность, если это возможно без изменения схемы или набора данных.

Каково количество строк и времени выполнения для следующих запросов для получения списка подходящих вершин хвоста (т.е. у которых есть SpecialKey)

SELECT -- DISTINCT
    vd.x as tail_x, vd.y as tail_y, vd.val
FROM
    VertexDictionary vd
WHERE
    EXISTS (
        SELECT
            1
        FROM
            SpecialKeys sk
        WHERE
            vd.x = sk.x
        AND
            vd.key = sk.key
    )

или

SELECT -- DISTINCT
    vd.x as tail_x, vd.y as tail_y, vd.val
FROM
    VertexDictionary vd
JOIN
    SpecialKeys sk
ON
    vd.x = sk.x
AND
    vd.key = sk.key

или

SELECT -- DISTINCT
    vd.x as tail_x, vd.y as tail_y, vd.val
FROM
    VertexDictionary vd
WHERE
(vd.x, vd.key) IN (SELECT x, key FROM SpecialKeys)
-- also could try vd.key IN (SELECT sk.key FROM SpecialKeys sk WHERE sk.x = vd.x)

Я надеюсь, что один из них вернет либо небольшой набор результатов, либо, по крайней мере, быстро приведет к результатам. если низкая мощность и большие результаты применяются различно.

выберите лучший из предыдущих двух запросов и добавьте к следующему шагу: объединение этих подходящих "хвостов" в "подходящие головы"

SELECT -- DISTINCT
    cv.head_y as y,
    tv.val
FROM
(
    -- ADD SUB QUERY HERE also try nesting the subquery like: (select tail_x, tail_y, val from ([SUBQUERY]) as sq)

) as tv -- tail verticies
JOIN
    ConnectedVerticies cv
ON
    cv.tail_x = tv.tail_x
AND
    cv.tail_y = tv.tail_y
WHERE
    cv.head_x = X -- lets reduce the result set here.

Опять же, я надеюсь, что один из них вернется либо к небольшому результирующему набору, либо, по крайней мере, быстро приведет к результатам. если низкая мощность и большие результаты применяются различно.

Если он упадет в этот момент, ну нет никакой надежды на то, что он быстрее начнет применять последний этап, и лучше попробовать другой подход.

Поскольку head x известен из более раннего запроса, теперь нам просто нужно присоединиться к head_y и X, чтобы получить v.key

SELECT DISTINCT
    inner_query.val,
    head.key
FROM
(
 -- previous nested subquery behemoth here, again, try a few things that might work.

) as inner_query
JOIN
    VertexDictionary as head
ON
    head.x = X
AND
    head.y = inner_query.y

Другой подход - получить список head.key, tail_x и tail_y из

SELECT -- DISTINCT
    cv.tail_x as x,
    cv.tail_y as y,
    vd.key
FROM
    VertexDictionary vd
JOIN
    ConnectedVerticies cv
ON
    cv.head_x = vd.x
AND
    cv.head_y = vd.y
WHERE
    vd.head_x = X

Как долго это выполняется для выполнения, с четкими и без? сколько результатов (w и w/o разных)?

Если он быстрый и/или небольшой, попробуйте использовать его в качестве подзапроса и присоединитесь к другому потенциальному подзапросу для SpecialKeys и VertexDictionary, если это небольшое (т.е. одно из первых трех запросов, если они хорошо работают).

Ответ 4

Я подозреваю, что ваша проблема - это все с синтаксисом

(k. x, k. key) = (u. x, u. key)

Вы можете переписать как?

k.x = y.x и k.key = u.key

Когда у вас есть расчет в левой части предложения, dbms не может оптимизировать. Установив сравнение как прямое сравнение, вы можете улучшить свою производительность.

например.

year (my_date) = '2012'

медленнее, чем

'2012' = year (my_date)

Я не уверен, что mysql рассматривает сравнение как сравнение столбцов или как расчет.

Попробуйте изменить свой запрос, чтобы выполнить сравнение значений столбцов.

Вторая оптимизация

Кроме того, вы перекрестите 4 таблицы. Умножение не является аддитивным - оно экспоненциально. Вы уверены, что это то, что вы намерены? Вам может быть лучше, если вы начнете с самого маленького набора результатов, а затем присоедините только этот результат к следующему набору.

select a.c1
from (
select t1.c1
from t1
join t2 on t1.c1 = t2.c1
) a
join t3 on t3.c1 = a.c1

и т.д...

третья оптимизация

Если опция 2 помогает, вы можете создать индексированные представления и работать с ними вместо непосредственно из таблиц.

четвертая оптимизация

не использовать mysql. если у вас нет команды dbas, постоянно контролирующей производительность и настройки, вы столкнетесь с плохими временами с mysql. mysql отлично и быстро с простыми вещами, но начинает сосать очень плохо, если вы делаете что-то умеренно сложное. 4 года назад я перешел из mysql в sql server express и мои 10-минутные запросы заняли менее 2 секунд с теми же таблицами, индексами и запросами...

если вы хотите открыть исходный код, postgres намного умнее, чем mysql, а

Создайте представление, включающее первые 3 таблицы, индексированные в полях v.key, u.val. Затем запустите запрос с 4-й таблицы и представления. Перед запуском убедитесь, что индексы построены на представлении.

Ответ 5

DISTINCT часто бывает плохим другом. Попробуйте заменить его на GROUP BY. Вот так:

SELECT sub.key, sub.val
FROM (
    SELECT 
      v.key,
      u.val
    FROM
      ConnectedVertices AS c
      JOIN VertexDictionary  AS u ON (u.x, u.y  ) = (c.tail_x, c.tail_y)
      JOIN VertexDictionary  AS v ON (v.x, v.y  ) = (c.head_x, c.head_y)
      JOIN SpecialKeys       AS k ON (k.x, k.key) = (u.x, u.key)
    WHERE (v.x = @X)
) AS sub
GROUP BY sub.key, sub.val

UPDATE:

Затем попробуйте выполнить следующий запрос, который заставляет использовать индексы:

SELECT DISTINCT
  v.key,
  u.val
FROM
  ConnectedVertices AS c USE INDEX (fx,rx)
  JOIN VertexDictionary  AS u USE INDEX (primary) ON (u.x, u.y  ) = (c.tail_x, c.tail_y) 
  JOIN VertexDictionary  AS v USE INDEX (primary) ON (v.x, v.y  ) = (c.head_x, c.head_y)
  JOIN SpecialKeys       AS k USE INDEX (primary) ON (k.x, k.key) = (u.x, u.key)
WHERE (v.x = @X)

Если это все еще не лучше, попробуйте следующее:

SELECT DISTINCT
  v.key,
  u.val
FROM
       ConnectedVertices AS c
  JOIN VertexDictionary  AS u ON (u.x=c.tail_x) AND (u.y=c.tail_y)
  JOIN VertexDictionary  AS v ON ([email protected]) AND (v.y=c.head_y)
  JOIN SpecialKeys       AS k ON (k.x=u.x) AND (k.key=u.key)
WHERE
  v.x = @X

Ответ 6

Я не думаю, что принудительное использование специфических индексов - хорошее мнение. оптимизатор Mysql часто имеет хорошие оценки.

У вас есть индекс на v. x?