Mysql Оптимизировать запрос, в таблице 6млн записей

Проверенные VDS на SSD в Европе от $4 и России: Датацентр №1 от 150руб | Датацентр №2 от 199руб

Тема в разделе "PHP & Mysql & MariaDB & Percona", создана пользователем lolka02, 11 сен 2017.

  1. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    Помогите Оптимизировать запрос, в таблице 6млн записей и каждый день увеличивается на 20к, индексы добавлены, дальше не знаю в какую сторону смотреть. Вот запрос, иногда при нагрузке часто вот такой запрос выполняется по 30-100 секунд (это запрос для выполки похожих записей в каждой карточке товара)

    Код:
    SELECT distinct(product.id), product.id,
                   product.name,
                   product.descriptio
                   product.city_id,
                   product.price,
                   product.company_name,
                   product.timestamp_update,
                   product.company_id,
                   company.company_type,
                   company.image as company_image,
                   city.name as city_name
    FROM `product_to_tag` `v2t`
    JOIN `product` ON product.id = v2t.product_id
    JOIN `product_to_city` `vtc` ON product.id = vtc.product_id
    JOIN `city` `c` ON c.id = vtc.city_id
    LEFT JOIN `city` `city` ON city.id=product.city_id
    LEFT JOIN `company` ON company.id=product.company_id
    WHERE ((`product`.`publish` = 1)) AND (product.id !=  5016460) AND (c.id = 99 or c.parent_id = 99) AND ((`v2t`.`tag_id` IN (65, 181, 228, 1135)))
    ORDER BY `product`.`timestamp_update` DESC LIMIT 30;
     
  2. Yoskaldyr

    Yoskaldyr Новичок Партнер клуба

    Сообщения:
    395
    Ваш город:
    Varna, Bulgaria
    Adress:
    Varna, Bulgaria
    Country:
    Location on Map:
    @lolka02, Нужна схема таблицы, и общее количество записей в таблице. т.к. главное не просто добавить индексы а правильно добавить.
    Также зависит от того какая версия Mysql/Percona/Maria стоят. В зависимости от версии у них магут быть оптимизации подобных запросов (там где есть OR: c.id = 99 or c.parent_id = 99)
     
  3. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    версия 5.5, структуру позже могу скинуть
     
  4. Yoskaldyr

    Yoskaldyr Новичок Партнер клуба

    Сообщения:
    395
    Ваш город:
    Varna, Bulgaria
    Adress:
    Varna, Bulgaria
    Country:
    Location on Map:
    @lolka02, Желательно хотя бы 5.6 или еще новее. Начиная с этих версий улучшипась оптимизация объединения индексов (Index Merge Optimization). Иногда может помочь с подобными запросами (условие или в середине).
    Но самое важное это количество записей в таблице + схема таблицы
     
  5. antson

    antson на форуме с 2005 года Партнер клуба

    Сообщения:
    1.137
    Ваш город:
    Россия, Липецк
    Adress:
    Lipetsk, Russia
    Country:
    Location on Map:
    @lolka02, explain - чтобы не гадать почему долго
     
  6. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    как варинат можно ли заранее добавить в отдельную таблицу все похожие товары для каждого товара? этот запрос делает выборку похожих товаров...
     
  7. fixxxer

    fixxxer К.О.

    Сообщения:
    12.277
    Ваш город:
    Moscow, Russia
    Adress:
    Moscow, Russia
    Country:
    Location on Map:
    Это что-то очень странное.
     
    Yoskaldyr и WMix нравится это.
  8. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    таблица product
    [​IMG]
    индексы
    [​IMG]
    product_to_tag
    [​IMG]
    product_to_city
    [​IMG]
    company
    [​IMG]
     
  9. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    city
    [​IMG]

    explain
    [​IMG]

    product_id лишнее уберу его, или что еще? без дистинкта не получилось сделать запрос, иначе появлялись дубли
     
  10. WMix

    WMix герр M:)ller Партнер клуба

    Сообщения:
    5.774
    Ваш город:
    Berlin
    Adress:
    Berlin, Germany
    Country:
    Location on Map:
    фигаж се труд совершил!,
    а че, консолькой работать не умеешь?
    PHP:
    mysql -u root -p world
    mysql
    show tables;
    +-----------------+
    Tables_in_world |
    +-----------------+
    City            |
    Country         |
    CountryLanguage |
    cities          |
    person          |
    persons         |
    +-----------------+
    6 rows in set (0,00 sec)

    mysqlshow columns from City;
    +-------------+----------+------+-----+---------+----------------+
    Field       Type     Null Key | Default | Extra          |
    +-------------+----------+------+-----+---------+----------------+
    ID          int(11)  | NO   PRI NULL    auto_increment |
    Name        char(35) | NO   |     |         |                |
    CountryCode char(3)  | NO   MUL |         |                |
    District    char(20) | NO   |     |         |                |
    Population  int(11)  | NO   |     | 0       |                |
    +-------------+----------+------+-----+---------+----------------+
    5 rows in set (0,00 sec)

    mysqlshow index from City;
    +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    Table Non_unique Key_name    Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment |
    +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    City  |          PRIMARY     |            ID          A         |        4188 |     NULL NULL   |      | BTREE      |         |               |
    City  |          CountryCode |            CountryCode A         |         232 |     NULL NULL   |      | BTREE      |         |               |
    +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0,00 sec)

    mysqlexplain select Country.Codecount(distinct City.IDfrom Country left join City on City.CountryCode Country.Code
        
    ->  group by Country.Code;
    +----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
    id select_type table   partitions type  possible_keys key         key_len ref                rows filtered Extra       |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
    |  
    SIMPLE      Country NULL       index PRIMARY       PRIMARY     3       NULL               |  239 |   100.00 Using index |
    |  
    SIMPLE      City    NULL       ref   CountryCode   CountryCode 3       world.Country.Code |   18 |   100.00 Using index |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
    2 rows in set1 warning (0,00 sec)

    mysql>
     
    fixxxer и Adelf нравится это.
  11. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    ну кому как мне так удобнее
     
  12. antson

    antson на форуме с 2005 года Партнер клуба

    Сообщения:
    1.137
    Ваш город:
    Россия, Липецк
    Adress:
    Lipetsk, Russia
    Country:
    Location on Map:
    v2t как понимаю самая большая. Если есть возможность (на сервере достаточно оперативки) поиграйся буферами муськи для индексов . так чтобы пропало using temporary , using filesort

    как вариант протестируй связку из 2 запрсов
    1) выбирает только ид товаров
    если бяка пропадет из плана, то
    2) select * from products where id in (идишники из первого)
     
    Последнее редактирование: 12 сен 2017
  13. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    Можете рассказать как именно делать? в конфигах Mysql ?
    тут просто тогда сортировка не очень будет, например нам нужен только 15 товаров, придется выбирать тогда все товары и во втором запросе только сортировку и ограничение ставить, или же опять надо будет выбрать только опубликованные товары, а для этого придется делать джоин и опять придем к главному запросу
     
  14. antson

    antson на форуме с 2005 года Партнер клуба

    Сообщения:
    1.137
    Ваш город:
    Россия, Липецк
    Adress:
    Lipetsk, Russia
    Country:
    Location on Map:
    @lolka02, начнем с того где хоститься ?
    шаред - нельзя настраивать.
    vps,dedicated - смотрим сколько памяти.
    смотрим текущий my.cnf
    находим рекомендуемый под заданный объем озу и тип inodb или isam
     
  15. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    @antson, hetzner, dedicated. 64гб памяти. тип innodb.
    Только что заметил что у меня два одинаковых индекса для поля publish от этого тоже тормозит наверное?
     
  16. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    + вот из за этого условия AND (c.id = 99 or c.parent_id = 99) запрос выполняется дольше когда убираю разница 12-20 секунд получается
     
  17. Yoskaldyr

    Yoskaldyr Новичок Партнер клуба

    Сообщения:
    395
    Ваш город:
    Varna, Bulgaria
    Adress:
    Varna, Bulgaria
    Country:
    Location on Map:
    Во первых настройки базы - чтобы не вникать детально можно скриптами типа MySQLTuner или MySQL Tuning Primer Script.

    Как вариант может тупить из-за сортировки, для проверки выкинуть из запроса ORDER BY `product`.`timestamp_update` DESC и посмотреть что получится.
    Но все это предположения, без explain-а точно все-равно не сказать.

    P.S. Хотя мне кажется в данном случае вряд-ли какие советы помогут. Т.к. надо и сильно запрос переписать и настроить базу, а это скилл значительно выше скилла топикстартера, который довольно однозначный судя по запросу бессмысленному и беспощадному и его любви к phpmysqladmin-у.
     
  18. Yoskaldyr

    Yoskaldyr Новичок Партнер клуба

    Сообщения:
    395
    Ваш город:
    Varna, Bulgaria
    Adress:
    Varna, Bulgaria
    Country:
    Location on Map:
    Хотя может топик стартеру просто досталось такое тяжелое наследство, а не он сам это писал. Тогда можно только посочувствовать. Архитектура базы, таблиц и связей явно г-но.
     
  19. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    @Yoskaldyr, да проектировал базу не я, убрал order в результате в explain пропали using temporary , using filesort. Но мне без сортировки никак(( нужно именно чтобы последние товары были вначале
     
  20. lolka02

    lolka02 Новичок

    Сообщения:
    22
    Ваш город:
    Nizhnevartovsk, Ханты-Мансийский автономный округ, Россия
    Adress:
    Nizhnevartovsk, Russia
    Country:
    Location on Map:
    как решить проблему с order, можете подсказать?