Комментарии 14
Спасибо за статью, узнал несколько новых для себя вещей. Поставил плюсик.
Впечатления, однако, смешанные.
С одной стороны, хорошо что она есть, но с другой — видно, что написана по разнарядке для продвижения блога компании. Хотелось бы большей вовлечённости. Поменьше источников и побольше реальных юзкейсов из собственного опыта.
Не секрет, что explain используется в первую очередь для оптимизации запросов. А этой теме в статье посвящено до обидного мало. Тому, кто уже разбирается, она даст несколько новых подсказок, но тому, кто захочет с ее помощью научиться оптимизировать запросы, будет очень непросто вычленить ключевые моменты.
Желательно структурировать информацию, выделяя более значимую. К примеру, действительно важные значения столбца Extra стоит дать подробнее, а всякие диковины типа impossible having, которые только на бумаге и встречаются, я бы убрал под спойлер.
Чтобы не быть голословным, несколько вещей из собственного опыта
- надо обращать внимание на размер key length. Если он большой, то стоит подумать над уменьшением. У нас в практике был случай, когда требовалась уникальность для поля, содержащего довольно большой объем данных. Убрав с него индекс и добавив рядом поле, содержащее md5() от этих данных, получили ощутимый прирост производительности.
- перемножать значения rows надо не "если не лень", а в обязательном порядке. Ну или точнее — следить чтобы там в идеале были единички во всех дополнительных таблицах, поскольку перемножение строк и является основной причиной медленных запросов
- не нужно переживать из-за Filesort, если значение Rows небольшое и эти действия производятся на финальной стадии — БД вполне может просто отсортировать уже полученный результат прямо в памяти, это никак не повлияет на скорость
- если запрос очень большой, то я всегда "упрощаю" его, выкидывая из него различные элементы, следя за тем, чтобы ключевые проблемы в EXPLAIN оставались теми же — помогает увидеть самое основное и не отвлекаться на не имеющие значения детали, а так же сформулировать конкретный вопрос для google/stackoverflow
Эх, ну как тут удержаться от сентенции "Хабр уже не торт"? Статью про "предателя" три дня обсуждают взахлеб, а в статью по тематике, для которой изначально создавался сайт, зашло полтора инвалида.
Но вы все равно пишите. Когда пишешь статью, мысли у самого в голову укладываются лучше, это уже и сам по себе плюс. Ну статья в любом случае не пропадет и будет интересна пусть не тем кто читает хабр за утренним кофе, но тем кто гуглит информацию в интернете.
А вот как решить задачу оптимизации, например, если нужно делать запросы к FULLTEXT индексу в постоянно нарастающей по объему таблице (и отсечь данные нельзя ни под дате, ни как-либо еще — всегда нужно делать запрос ко всем текстовым данным в таблице, которые каждый день прирастают по сотне тысяч строк текста).
Любой такой запрос подбрасывает расход CPU в 100% пока запрос не получит результат.
Вот это задача номер раз!
Я не встречал на продакшене использования фуллтекст индексов. Полнотекстовый поиск всегда выносится в Сфинкс/Эластик.
А не встречали, почему?
Вот хабр сейчас использует для поиска похожих статей метки, был-ли раньше полнотекстовый поиск для этого на хабре? (по идее был). Поиск в поле поиска на хабре — что использует? (сфинкс, или другой поиск, который как-то индексирует тексты и затем использует что? — правильно всё те же полнотекстовые индексы).
И это пример с низкой скоростью добавления данных.
А взять риа например — новости каждые 20-30 секунд в БД, и поиск похожих новостей перед добавлением, за год например — как ищутся?
Стоп. Тут какая-то куча-мала из вопросов.
Во-первых, с переходом на отдельный поисковый сервис нагрузка на процессор сервера базы данных нас перестаёт интересовать от слова "совсем". Ну то есть понятно, что если у нас ВДС за три копейки на двух виртуальных ядрах, которые обслуживают и веб и БД и всех демонов впридачу — тогда нагрузка на поиск будет волновать.
Но если у нас система с "приростом по сотне тысяч строк в день", то логично разнести её на разные физические серверы — веб на одном, БД на втором, поиск — на третьем. И пусть себе грузит свой процессор сколько влезет.
Метки вообще никакого отношения к полнотекстовому поиску не имеют, это простой поиск по обычному индексу:
SELECT article_id FROM articles_tags at, tags t
WHERE at.tag_id=t.id AND t.name IN (...)
GROUP BY article_id
ORDER BY count(*) DESCчтобы отсортировать по релевантности
Поиск "похожих новостей" и полнотекстовый поиск — это две большие разницы. Я думаю, для поиска "похожих" надо сначала научиться выделять ключевые слова в тексте. А дальше уже чисто техническая задача.
Всё на одном сервере, и внешний поисковый двиг только добавит нагрузку.
Поиск по меткам — как пример, что сейчас есть по меткам, а дальше предположение, что был полнотекст на хабре вначале пути.
«Поиск „похожих новостей“ и полнотекстовый поиск — это две большие разницы» нет, никакие метки вам не дадут гарантии похожести, полнотекст лидирует, либо человек должен проверять глазами выборку после меток.
Полнотекст обязателен для вывода похожих новостей в релевантном поиске (как у гулга например).
Но, сотрудник тех части google — Ubl, в недавней беседе упомянул структуру их индексов поиска (у них обычный индекс текста по словам, такой же в принципе, как создает любая БД), и сказал, что помимо самих индексов у них есть индексы — индексов, и указатели индексов — индексов.
Вот это и интересно. Это решает, как я понимаю, проблему с нагрузкой при полнотекстовом поиске, но как это реализовано у них, пока представить не могу в полной мере.
Как-то немного разнобой получается. По запросам у вас РИА и Гуголь, а по возможностям — один арендованный сервак. Ну в общем, я наверное ничего не смогу посоветовать для вашего случая.
Нет, чем больше и круче компания, тем серьезнее и интереснее стараются подойти к оптимизации.
Мои вот движки держат легко 120-200 тыс посетителей в стуки даже на одно-двух процессорных вдс за доллар, при этом, обновление данных каждые 20-40 секунд.
И именно потому, что я очень жестко подхожу к борьбе за каждый байт и процессорную операцию при разработке, это уже как азарт просто, само-собой разумеющийся.
Сейчас вот как раз озадачаен решением выше-озвученной задачи для вдс на 2-3 CPU ядра средней линейки.
— выделение ключевых слов/тэгов, вынесение в отдельную таблицу и создание индексов по ним
— хранить тексты в файлах и юзать linux-grep (не знаю насколько быстрее будет)
Т.е. полнотекстовый поиск по большому объему текста в ограниченных условиях CPU врядли решаем.
Если придумаете интересное решение, будем ждать статья на хабре))
Вот сейчас откатываю разные размеры партиций и отдельных таблиц, а не партиций и вместо одного — 5-20 запросов, если процент выигрыша будет существенный, это будет очередной костыль, пока не будет найдено протестировано более быстрое и незатратное решение.
Пробую и файловые операции использовать на регэкспах, вместо полнотекста в БД, посмотрим, понюхаем.
Читаем EXPLAIN на максималках