Оптимизация запросов с использованием индекса

Я все еще путаю с этим. Это из набора сетевых проблем.

Предположим, у меня есть таблицы USER, CHECKIN и PLACE.

Пользователь (uid, uname, ucity), uid является первичным ключом.

Место (pid, pname, pxcoord, pycoord, pcity), PID является первичным ключом.

CHECKIN (uid, pid, cdate, ctime), (uid, cdate, ctime) является первичным ключом.

Запрос

select c.uid, c.pid c.cdate
from user u natural join checkin c natural join place p 
where ucity='NewYork' and pcity='Chicago'
  1. У меня может быть какое-то недоразумение с определением. Но только разрешено создавать до двух индексных структур, как мы должны выбрать между упорядоченным индексом и индексом дерева B+?

  2. Для таблицы check in, есть ли два индекса (uid, pid), которые мы должны создать? Если это так, будет ли какая-то разница, какой из них должен быть первичным или вторичным индексом?

Я скопировал оригинальные вопросы здесь

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

1 ответ

  1. «Никто» не использует NATURAL JOIN. Пожалуйста, измените JOIN ... ON ...синтаксис так, чтобы Вы были явными о том, как связаны таблицы. Кроме того, пожалуйста, предоставьте SHOW CREATE TABLEдля каждого стола.

    «Только 2 индексных структуры»? Где ты это взял? InnoDB ‘требует’ один PRIMARY KEYи до 64 ‘вторичных ключей. Сохраняется PRIMARY KEYс данными и упорядочивает данные таким образом. Вторичные ключи имеют первичный ключ в них, поэтому они выполняют второй поиск, чтобы получить данные. Как первичный, так и вторичный ключи находятся в структуре BTree.

    APRIMARY KEY, определением MySQL, UNIQUEи индексом. Таким образом, Дайте таблице «естественную»PRIMARY KEY, основанную на любом столбце (или комбинации столбцов), уникальном в таблице. Или создайте суррогатный AUTO_INCREMENTключ. Затем сделайте любые другие ключи, которые вам могут понадобиться для поиска и / или JOINing.

    Да, индексы ускоряют выполнение запросов. Когда вы получаете некоторые таблицы с миллионами строк, мы можем обсудить тонкие вопросы за этим.

    В SELECTслучае, когда не используются никакие индексы, будет сканироваться вся таблица («table scan»). Это «медленно», особенно если стол «большой». Но это нормально.

    Поиск одной строки («точечный запрос») выполняется быстро, если он может использовать индекс. Это быстрее , если он может использоватьPRIMARY KEY, но мы говорим о 1 миллисекунды против 2 миллисекунд для типичных ситуаций. Даже для таблицы строки миллиарда, мы говорим о 10мс против 20мс. С другой стороны, сканирование таблицы из миллиарда строк может занять несколько часов. Это крайний пример того, почему индексы важны.

    В вашем запросе вы бы хотели

    INDEX(ucity)
    

    Если вы предоставите EXPLAIN SELECT ...запрос и таблицы, как они стоят, а затем добавить этот индекс и запустить EXPLAINснова, вы увидите разницу. Мы можем обсудить, как все меняется к лучшему и как исчезло сканирование таблицы.

    Подробнее о написании оптимальных индексов см. В моей поваренной книге.

    Еще один совет: не разделяйте DATETIMEполя на два отдельных поля. Гораздо проще разделить aDATETIME, когда это необходимо, чем объединить два поля.