LINUX.ORG.RU

Оптимизация запроса

 


0

1

Пишу запрос, постепенно его усложняя. На каждом шаге время выполнения увеличивается. Рост ожидаем, проблема в том, что он очень высок - на порядок. То есть, на втором шаге - 0.3с, на третьем - уже 3с (по данным профайлера).

Помогите понять, почему так быстро растет время запроса.

По существу. Есть две связанных таблицы, в первой - список уникальных наборов данных, во второй - сами данные в виде ключ->значение, каждая пара привязана к набору:

CREATE TABLE dataset
  set_id INT PRIMARY KEY,
  label TEXT;

CREATE TABLE keyvalue
  id INT PRIMARY KEY,
  set_id INT,
  key varchar(10),
  val int;

CREATE INDEX index_k_v ON keyval (set_id, key, val);

Запрос:

SELECT t.set_id as id, t1.val as key1, t2.val as key2, t3.val as key3  FROM dataset t
  JOIN keyvalue t1 ON t1.key='key1' and t.set_id=t1.set_id
  JOIN keyvalue t2 ON t2.key='key2' and t.set_id=t2.set_id -- 0.3s
  JOIN keyvalue t3 ON t3.key='key3' and t.set_id=t3.set_id -- 3.0s

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

Профайлер говорил, что время занимало sending data (98%), поэтому, чтобы снять вопрос объема данных, в тестовой схеме поле val имеет тип INT, хотя картина осталась прежней. Замеры времени приведены для этой схемы.

А что explain говорит? Наверняка же где-то не по индексам ищет.

Shtsh ★★★★ ()

Из-за условий ='key' в данном случае таблицы перемножаются. То, что всего на порядок дольше - так это у тебя записей мало :).

vahtu ()
Ответ на: комментарий от Shtsh

Говорит - везде ref. Точнее смогу сказать завтра.

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

codenewb ()
Ответ на: комментарий от codenewb

А так:

SELECT set_id, group_concat(val) 
from keyvalue
where `key` in ('key1', 'key2')
group by set_id
vahtu ()
Ответ на: комментарий от vahtu

По идее, должны перемножаться результаты выборок по условию ON, нет?

Но, похоже, ты прав, и этого не происходит. Вопрос - почему?

codenewb ()
Ответ на: комментарий от vahtu

group_concat - это не то, что нужно. Но, спасибо за подсказку, для теста подойдет.

codenewb ()
Ответ на: комментарий от Shtsh

explain говорит

+----+-------------+-------+--------+---------------+-----------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key       | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+-----------+---------+---------------+------+-------------+
|  1 | SIMPLE      | t2    | ref    | index_k_v     | index_k_v | 303     | const         |  357 | Using where |
|  1 | SIMPLE      | t3    | ref    | index_k_v     | index_k_v | 303     | const         |  357 | Using where |
|  1 | SIMPLE      | t1    | ref    | index_k_v     | index_k_v | 303     | const         |  837 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY   | 4       | tdb.p1.set_id |    1 | Using where |
+----+-------------+-------+--------+---------------+-----------+---------+---------------+------+-------------+

codenewb ()

Вопрос все еще открыт. Прошу высказываться.

codenewb ()
Ответ на: комментарий от codenewb

Ну и чем тебя не устраивает group_concat? Мой запрос должен возвращать точно такой же результат,как и твой, за исключением названий полей (забыл поставить as ) и того, что все val'ы соединены в строку.

При этом при росте кол-ва ключей (я так понял ты их добавлял) время будет расти линейно.

Вырезать из строки можно субстрингом.

group_concat работает гораздо быстрее, да

Нет. Выборка по множеству 'key' работает быстрее, чем объединение.

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

vahtu ()
Ответ на: комментарий от vahtu

Нет. Выборка по множеству 'key' работает быстрее

Да, разумеется, речь не о самой group_concat(), а о запросе с ней.

Ну и чем тебя не устраивает group_concat? Мой запрос должен возвращать точно такой же результат

Во-первых, не точно такой же, в set_id попадают записи, представленные не полным набором key, а совпадающие с хотя бы одним значением из множества.

Во-вторых, в реальной БД тип поля val может быть, например, TEXT, а длина строки, возвращаемой group_concat() ограничена. Также, не исключены проблемы с выбором разделителя.

При этом при росте кол-ва ключей (я так понял ты их добавлял) время будет расти линейно.

Ты правильно понял, добавлял. Если бы время росло линейно, не было бы вопроса, а оно растет экспоненциально с каждым join-ом

Похоже, я что-то не понимаю в работе JOIN

codenewb ()
Ответ на: комментарий от codenewb

Во-первых, не точно такой же, в set_id попадают записи, представленные не полным набором key, а совпадающие с хотя бы одним значением из множества.

Может их отсеять?

длина строки, возвращаемой group_concat() ограничена

Ну да. И это ограничение можно поменять в group_concat_max_len.

Сказал бы хоть зачем тебе это нужно.

vahtu ()
Ответ на: комментарий от codenewb

Дык вот! EXPLAIN убелительно показывает, откуда такое замедление. t3.rows=357! В 357 раз больше записей просматривать собирается.

Для таблиц t1,t2,t3 key_len=303. Это при индексе:


CREATE TABLE keyvalue
  id INT PRIMARY KEY,
  set_id INT,
  key varchar(10),
  val int;

CREATE INDEX index_k_v ON keyval (set_id, key, val);

set_id - INT - 8 байт key - 10 * sizeof(char), пусть utf-8, 10*3=30 val - INT - 8 байт

добавим еще байт на NULLы. Никак 303 байта не набирается...

gods-little-toy ★★★ ()
Ответ на: комментарий от codenewb

А как это в таблице t2 у тебя ref(const) используется?

Индекс, как сказали- keyval (set_id, key, val). Но ведь во WHERE нету никаких условий типа set_id=const, ни явных, ни неявно-выводимых.

что-не сходится. Ты уверен, что запостил тот EXPLAIN, и те определения таблицы?

gods-little-toy ★★★ ()
Ответ на: комментарий от gods-little-toy

t3.rows=357! В 357 раз больше записей просматривать собирается.

Я нуб, и 2/3 вывода EXPLAIN не понимаю, но это разве не кол-во записей, участвующих в выборке?

Но ведь во WHERE нету никаких условий типа set_id=const, ни явных, ни неявно-выводимых.

Может это?

...FROM dataset t ... and t.set_id=t2.set_id

CREATE TABLE dataset
  set_id INT PRIMARY KEY,

и t.rows=1

Ты уверен, что запостил тот EXPLAIN, и те определения таблицы?

Я уже ни в чем не уверен. Индекс мог остаться после смены типа поля val с varchar(100) на int? (после этого не было никаких insert-update-delete)

codenewb ()
Ответ на: комментарий от vahtu

И это ограничение можно поменять в group_concat_max_len.

А можно mongodb заюзать, только нельзя.

Понимаешь, если б было все так просто, я бы вообще не парился с join-ами, и хранил все это добро в блобе в xml, json или еще каком из 100500 форматов.

codenewb ()
Ответ на: комментарий от gods-little-toy

t3.rows=357!

Кажется понял, 1*857*357*357, так? Пояснил бы кто, как правильно explain читать.

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

codenewb ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.