Форум пользователей MySQL

Отображение строк 0 - 24 (1400317 всего, Запрос занял 0.0468 сек.)
Тут, правда, есть странность. Не знаю, может это нормально. Визуально этот запрос занял около 10-15 сек., а phpmyadmin выдал результат 0,04.

Запрос:

SELECT
`t`.`tr_type_fullname` AS `tr_type`,
`m`.`tr_type_code` AS `tr_type_code`,
`m`.`send_date` AS `send_date`,
`m`.`arrival_date` AS `arrival_date`,
`e`.`product_fullname` AS `product_name`,
`c`.`country_fullname` AS `send_country`,
`ss`.`port_fullname` AS `send_foreign_port`,
`r`.`region_fullname` AS `send_region_rf`,
`ro`.`city_shortname` AS `send_city`,
`sr`.`port_fullname` AS `send_port_rf`,
`co`.`company_name` AS `sender`,
`co`.`company_phone` AS `sender_phone`,
`co`.`company_email` AS `sender_email`,
`co`.`company_website` AS `sender_website`,
`co`.`company_address` AS `sender_address`,
`co`.`company_CEO` AS `sender_CEO`,
`co`.`company_inn` AS `sender_inn`,
`c1`.`country_fullname` AS `target_country`,
`ss1`.`port_fullname` AS `target_foreign_port`,
`r1`.`region_fullname` AS `target_region_rf`,
`ro1`.`city_shortname` AS `target_city`,
`sr1`.`port_fullname` AS `target_port_rf`,
`co1`.`company_name` AS `reciever`,
`co1`.`company_phone` AS `reciever_phone`,
`co1`.`company_email` AS `reciever_email`,
`co1`.`company_website` AS `reciever_website`,
`co1`.`company_address` AS `reciever_address`,
`co1`.`company_CEO` AS `reciever_CEO`,
`co1`.`company_inn` AS `reciever_inn`,
`wt`.`transport_type_fullname` AS `transport_type`,
`wo`.`transport_company_name` AS `transport_company`,
`m`.`volume` AS `volume`,

FROM `main` AS `m`
LEFT JOIN `tr_types` AS `t` ON `t`.`tr_type_code` = `m`.`tr_type_code`
LEFT JOIN `products` AS `e` ON `e`.`product_code` = `m`.`product_code`
LEFT JOIN `countries` AS `c` ON `c`.`country_code` = `m`.`send_country_code`
LEFT JOIN `ports` AS `ss` ON `ss`.`port_code` = `m`.`send_foreign_port_code`
LEFT JOIN `regions` AS `r` ON `r`.`region_code` = `m`.`send_region_rf_code`
LEFT JOIN `ports` AS `sr` ON `sr`.`port_code` = `m`.`send_port_rf_code`
LEFT JOIN `cities` AS `ro` ON `ro`.`city_code` = `sr`.`city_code`
LEFT JOIN `companies` AS `co` ON `co`.`company_code` = `m`.`sender_code`
LEFT JOIN `countries` AS `c1` ON `c1`.`country_code` = `m`.`target_country_code`
LEFT JOIN `ports` AS `ss1` ON `ss1`.`port_code` = `m`.`target_foreign_port_code`
LEFT JOIN `regions` AS `r1` ON `r1`.`region_code` = `m`.`target_region_rf_code`
LEFT JOIN `ports` AS `sr1` ON `sr1`.`port_code` = `m`.`target_port_rf_code`
LEFT JOIN `cities` AS `ro1` ON `ro1`.`city_code` = `sr1`.`city_code`
LEFT JOIN `companies` AS `co1` ON `co1`.`company_code` = `m`.`reciever_code`
LEFT JOIN `transport_types` AS `wt` ON `wt`.`transport_type_code` = `m`.`transport_type_code`
LEFT JOIN `transport_companies` AS `wo` ON `wo`.`transport_company_code` = `m`.`transport_company_code`

WHERE (`m`.`send_date` BETWEEN "2012-01-01" AND "2012-12-31");


EXPLAIN:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    m    ALL    NULL    NULL    NULL    NULL    1389129    Using where
1    SIMPLE    t    ALL    PRIMARY    NULL    NULL    NULL    2    Using where; Using join buffer (Block Nested Loop)
1    SIMPLE    e    eq_ref    PRIMARY    PRIMARY    15    login_name.m.product_code    1    NULL
1    SIMPLE    c    eq_ref    PRIMARY    PRIMARY    4    login_name.m.send_country_code    1    NULL
1    SIMPLE    ss    eq_ref    PRIMARY    PRIMARY    15    login_name.m.send_foreign_port_code    1    NULL
1    SIMPLE    r    eq_ref    PRIMARY    PRIMARY    4    login_name.m.send_region_rf_code    1    NULL
1    SIMPLE    sr    eq_ref    PRIMARY    PRIMARY    15    login_name.m.send_port_rf_code    1    Using where
1    SIMPLE    ro    eq_ref    PRIMARY    PRIMARY    4    login_name.sr.city_code    1    NULL
1    SIMPLE    co    eq_ref    PRIMARY    PRIMARY    24    login_name.m.sender_code    1    NULL
1    SIMPLE    c1    eq_ref    PRIMARY    PRIMARY    4    login_name.m.target_country_code    1    NULL
1    SIMPLE    ss1    eq_ref    PRIMARY    PRIMARY    15    login_name.m.target_foreign_port_code    1    NULL
1    SIMPLE    r1    eq_ref    PRIMARY    PRIMARY    4    login_name.m.target_region_rf_code    1    NULL
1    SIMPLE    sr1    eq_ref    PRIMARY    PRIMARY    15    login_name.m.target_port_rf_code    1    Using where
1    SIMPLE    ro1    eq_ref    PRIMARY    PRIMARY    4    login_name.sr1.city_code    1    NULL
1    SIMPLE    co1    eq_ref    PRIMARY    PRIMARY    24    login_name.m.reciever_code    1    NULL
1    SIMPLE    wt    eq_ref    PRIMARY    PRIMARY    4    login_name.m.transport_type_code    1    NULL
1    SIMPLE    wo    eq_ref    PRIMARY    PRIMARY    4    login_name.m.transport_company_code    1    NULL

У Вас нет индексов на `m`.`send_date` и, вероятно, на `t`.`tr_type_code`

Попробуйте такой параметр: http://dev.mysql.com/doc/refman/5.7/en/ … uffer_size

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

generale, у меня в докладе быстрая загрузка достигалась за счет LOAD DATA INFILE.

Спасибо, Александр.
Я пытался тестировать этот способ. Действительно, вставка данных происходит на порядок быстрее, хуже с SELECT INTO OUTFILE. На локальной машине сброс данных объемом около 1ГБ происходит примерно за 40-50 сек., что практически нивелирует ускорение вставки.
Сегодня попробую протестировать на VPS. По идее, с SSD это должно быть быстрее.
Вообще, проблема в том, что я только полгода занимаюсь веб-программированием, и именно сейчас сказывается отсутствие опыта.
Я вижу это так, что есть:
1. Сам запрос. Нужно понять, оптимален ли он. Опять таки, этого не сделать в отрыве от параметров конфигурации. Все, что я нашел на эту тему, попытался настроить. Сейчас профилирую запрос (хотя это почти ничего не дает - там 99% времени sending data), и смотрю изменения в STATUS VARIABLES, может быть там найду решение.
2. Структура БД. Если не удастся оптимизировать запрос, придется, видимо, думать над изменением логики самого веб-приложения и алгоритмов его взаимодействия с БД, для того, чтобы изменить структуру. Не хотелось бы.
3. Железо. Опять-таки, нет опыта. Первый раз в жизни имею дело с VPS и вообще впервые вижу линукс))) Вот если я увеличу ресурсы, скажем до 16 или 32 ГБ RAM, и 4-8 ядер, что произойдет? Запрос, который выполняется 60-70 сек. будет выполняться быстрее, или нет? Все равно, для полноценного использования этих ресурсов нужно будет правильно настроить конфигурацию MySQL. Но, если это так, то возможно, при правильной настройке и не нужны такие ресурсы. Короче, возврат к п. 1 )))))
Сорри за сумбур, вопросов пока намного больше, чем ответов.

Но я все понять не могу, зачем одному человеку выбирать 1,5 млн записей за раз?

У пользователей могут быть очень разные потребности в анализе данных. Поэтому нет возможности заранее знать, какие поля им будут необходимы. Что же касается количества записей, то тут тоже также потребность в больших объемах вполне вероятна. То есть, запросы, типа "хочу все, за весь период" могут быть довольно часто, и эту возможность хочется предусмотреть. Конечно, можно попытаться над этим поработать, но это уже относится к п. 2 моего предыдущего поста (нужно будет менять логику работы веб-интерфейса). Если не получится сделать так, как задумано изначально, то, конечно, придется этим заняться.

Но, все-таки, не могли бы вы, на основании своего опыта, как бы "на глаз", дать оценку: те запросы, которые я приводил ранее, с их временем выполнения, они "быстрые", "медленные" или "нормальные"? Просто, чтобы мне понимать, получится ли их ускорить за счет каких-то настроек MySQL.

К примеру, меня смущают значения этих переменных, после выполнения запроса:
created_tmp_tables: 6
created_tmp_disk_tables: 2

Я попытался увеличить tmp_table_size и max_heap_table_size до 1,5 ГБ, но ничего не изменилось. Вообще, не уверен, в том, что в моем случае эти значения могут говорить о чем-нибудь.

ЗЫ.Если нет времени разбираться, для настройки MySQL обратитесь к Григорию и Павлу. +)) Это того стОит.

Пока не хочется сдаваться))) Хочу, все-таки, разобраться с этим, раз уж занялся, но если не получится, то, конечно, буду искать помощи экспертов!

Отредактированно generale (25.01.2016 14:16:37)

Всем огромное спасибо за помощь! Попробовал масштабировать VPS сервер до 4-х ядер и 32 ГБ ОЗУ, что практически ничего не дало (время вставки упало до 40 секунд). Придется идти по пути доработки логики веб-приложения и сильно усложнить php-скрипт, который получает данные из БД, но, с другой стороны, такой путь, видимо будет более правильным, поскольку будет задействовать значительно меньше серверных ресурсов.