Mysql Помогите новичку разобраться в индексах.

DevConf 2017 - 17 июня, для тех кто не хочет быть планктоном | VDS на SSD - скида 25% | 16 июня YiiConf - впервые в России

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

  1. Cardee

    Cardee Новичок

    Сообщения:
    7
    Adress:
    Saint Petersburg, Russia
    Country:
    Location on Map:
    Если две таблицы:
    Код:
    mysql> SELECT * FROM `pages` WHERE `url` = "/url";
    +--------+----------+----------+
    | id     | url      | remove   |
    +--------+----------+----------+
    |      1 |     /url |        0 |
    +--------+----------+----------+
    |      2 |     /url |        1 |
    +--------+----------+----------+
    |      3 |     /url |        2 |
    +--------+----------+----------+
    С индексами:
    • PRIMARY на id;
    • уникальный индекс url на url и remove
    • индекс для частого запроса url_id_remove на все три столбца

    Код:
    mysql> SELECT * FROM `template` WHERE `id` IN (1, 2, 3);
    +--------+--------+
    | id     | tpl    |
    +--------+--------+
    |      1 |      1 |
    +--------+--------+
    |      2 |      1 |
    +--------+--------+
    |      3 |      1 |
    +--------+--------+
    С индексом PRIMARY на id и связью `template`.`id` = `pages`.`id`.

    Частый запрос, в котором суть проблемы, для получение `pages`.`id` с минимальным значением `pages`.`remove`, у которого есть в наличие запись в `template`:
    Код:
    mysql> SELECT `pages`.`id` FROM `pages`, `template` WHERE `pages`.`url` = "/url" AND `template`.`id` = `pages`.`id` ORDER BY `pages`.`remove` ASC LIMIT 1;
    В принципе всё работает быстро, но
    Код:
    mysql> EXPLAIN SELECT `pages`.`id` FROM `pages`, `template` WHERE `pages`.`url` = "/url" AND `template`.`id` = `pages`.`id` ORDER BY `pages`.`remove` ASC LIMIT 1;
    +----+-------------+----------+--------+---------------------------+---------+---------+-------------+------+--------------------------+
    | id | select_type | table    | type   | possible_keys             | key     | key_len | ref         | rows | Extra                    |
    +----+-------------+----------+--------+---------------------------+---------+---------+-------------+------+--------------------------+
    |  1 | SIMPLE      | pages    | ref    | PRIMARY,url,url_id_remove | url     | 767     | const       |    1 | Using where; Using index |
    |  1 | SIMPLE      | template | eq_ref | PRIMARY                   | PRIMARY | 3       | db.pages.id |    1 | Using index              |
    +----+-------------+----------+--------+---------------------------+---------+---------+-------------+------+--------------------------+
    Объясните, пожалуйста, почему MySQL использует индекс url, а не url_id_remove? Почему при наличие индексов на все поля, участвующие в запросе, мне предлагается использовать индексы?
     
    Фанат нравится это.
  2. Фанат

    Фанат oncle terrible Команда форума

    Сообщения:
    39.561
    Ваш город:
    Moscow, Russia
    Adress:
    Moscow, Russia
    Country:
    Location on Map:
    Сначала объясни, почему ты считаешь что БД должна использовать "на три столбца" и как она это будет делать?
     
  3. Фанат

    Фанат oncle terrible Команда форума

    Сообщения:
    39.561
    Ваш город:
    Moscow, Russia
    Adress:
    Moscow, Russia
    Country:
    Location on Map:
    Кстати, обрати внимание на длину ключа. И сколько он в итоге занимает места в памяти.
    И поменяй кодировку таблицы на latin1
     
  4. Andreika

    Andreika "PHP for nubies" reader

    Сообщения:
    945
    Ваш город:
    японские рестораны на Арбате
    Adress:
    Moscow, Russia
    Country:
    Location on Map:
    Вероятно где-то тут ответ
     
  5. Cardee

    Cardee Новичок

    Сообщения:
    7
    Adress:
    Saint Petersburg, Russia
    Country:
    Location on Map:
    @Фанат, я только начинаю разбираться в этом.
    Я думаю, что она определяет все используемые значения (это `id`, `url`, `remove`), определяет более подходящий ключ (url_id_remove) и использует его для поиска и сортировки.
    Но похоже я много не знаю.
    Спасибо за советы.

    @Andreika, обязательно сегодня же посмотрю, чтобы не задавать больше глупых вопросов.
     
  6. Фанат

    Фанат oncle terrible Команда форума

    Сообщения:
    39.561
    Ваш город:
    Moscow, Russia
    Adress:
    Moscow, Russia
    Country:
    Location on Map:
    При чем здесь "она"? Почему ты считаешь что url_id_remove более подходящий?
     
  7. Фанат

    Фанат oncle terrible Команда форума

    Сообщения:
    39.561
    Ваш город:
    Moscow, Russia
    Adress:
    Moscow, Russia
    Country:
    Location on Map:
    А, понял теперь.
    Виновата эта дебильная фраза, которую пишут составители пособий "25 идиотских фактов про пхп".

    Разумеется, это абсолютно бессмысленная рекомендация. Индекс нужен по тем полям, которые влияют на выборку. А у тебя на нее влияют только url и remove.
    поэтому и используется именно этот индекс.

    А вот если поставить между url и remove абсолютно бессмысленный id, то оптимизатор может и не сообразить, что этот элемент индекса надо просто пропустить. И в любом случае между нормальным и кривым индексом выберет первый.
     
    Последнее редактирование: 17 фев 2017
  8. JOSS-PHP

    JOSS-PHP Новичок

    Сообщения:
    110
    Ваш город:
    Belarus, Minsk
    Adress:
    Minsk, Belarus
    Country:
    Location on Map:
    сначала задумайся почему поле из одной таблицы должно быть в индексе другой? я про `template`.`id`
     
  9. Cardee

    Cardee Новичок

    Сообщения:
    7
    Adress:
    Saint Petersburg, Russia
    Country:
    Location on Map:
    @Фанат, @JOSS-PHP, спасибо за советы. Посмотрел посмотрел видео, которое скинул Andreika и понял в чем моя ошибка.
    Еще раз всем спасибо.