Индексы MySQL - primary, составные и покрывающие. Или как сделать, что бы сайт не тормозил.

Когда я только начинал кодить я вообще не знал, что такое индексы в таблицах и писал первые свои говносайты вообще без них 🙂 Но те времена давно канули в лета. Потом я очень долго кроме PRIMARY id иных индексов и не юзал ни когда. Причем в таблицах у меня обычно было дофига колонок и многие TEXT. Но и этого тогда хватало для моих проектов. Но вот связался я с обработкой больших и очень больших объемом данных все мои промахи тут же вылезли в виде жутких тормозов. Пришлось учить матчасть как говорится. Наконец мои глаза добрались до мануалов про все виды индексов. Поделюсь с вами вкратце как это все работает.

И так id в каждой таблице обязателен. Сейчас если вы даже не прописываете ни одну таблицу как primary, то MySQL все равно сам один столбец добавляет первичным. Да и такой индекс самый быстрый. Он быстрее остальных потому что в нем записывается сразу положение на место диска, где начинается следующий элемент. Без создания дополнительной таблицы. Поэтому primary в таблице может быть только один. И по этой же причине первичный индекс всегда уникален. В обычных же индексах записывается положение на диске, где записано положение места начала данных следующего индекса. То есть поиск по primary раза в два быстрее чем просто по index.

Теперь почему таблицы нужно максимально разбивать на более мелкие. Дело в том, что когда в большой по количеству столбцов таблице мы делаем какой то из них индексным, то MySQL все равно делает отдельную таблицу под эти данные, хоть и не отображает нам это. Она делает этот столбец primary, а id связывающим элементом. То есть по любому получается дублирование данных. Поэтому лучше сразу разбить таблицы на более мелкие и вынести индексные столбцы из основной таблицы в primary более мелких.

Теперь что такое составной индекс. Индекс может состоять из двух и более столбцов. Допустим если индекс состоит из id и time, то создастся индекс в котором будет записано idtime слитно. И тут важна последовательность. Если в запросе у нас select *from table where id=1 and time =2 and day=6, то составной индекс должен формироваться именно в том порядке как в where - id и второй time. Тут фишка в том, что по составному индексу идет поиск сначала по первой части индекса и затем из выборки совпадающей по первой части идет выборка по второму ключу. Если их поменять местами, то будет поиск по первому ключу, который находится во второй части составного индекса, а это считай что придется пробежаться по всем элементам без перепрыгивания.

Ну и на последок это покрывающий индекс. Я не даром в прошлом примере прикрутил еще и day=6, что бы он не был покрывающим. Покрывающий индекс это составной индекс в котором есть все элементы из запроса wherе. То есть для запроса select *from table where id=1 and time =2 and day=6 покрывающим индексом будет idtimeday, то есть составной индекс содержащий id, time и day. Причем как уже понимаем именно в этой последовательности.

Прелесть покрывающих индексов в том, что все данные для поиска сосредоточены в одном ключе и MySQL не нужно подгружать в память несколько таблиц и делать выборки из них. Это быстрее. А еще быстрее изначально сделать таблицу с составным индексом под запрос и сделать весь этот составной индекс primary - то есть самым быстрым. Единственное такому индексу нельзя сделать auto_increment, но если это не проблема, то это пожалуй идеальное решение по структуре таблицы.

Если честно после того, как я получил эти знания структура и количество таблиц в моих проектах сильно изменилась. Но и производительность тоже. Сейчас с удивлением наблюдаю как сайт отдает роботам Яндекса по 10-15 страниц в секунду собирая данные из десятка таблиц в каждой из которых под миллион записей. По мне так это сайт летает.

Если из моей писанины вы ни чего не поняли, а оптимизировать таблицы все равно надо, то загуглите про составные индексы у других авторов. Есть в сети более подробные и наверное более понятные мануалы. Успехов!

One Comment

Add a Comment

Ваш e-mail не будет опубликован.