Сучасна диджитал-освіта для дітей — безоплатне заняття в GoITeens ×
Mazda CX 5
×

Оптимизация SQL запросов

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті

Здравствуйте,
подскажите, пожалуйста, по двум запросам, можно ли что-то сделать

1. SELECT * FROM table WHERE field LIKE ’%smth%’
Здесь использование % в начале отключает индексы. Этот запрос работает в функции на ajax типа «выпадающий список» по набранным символам. Может кто-то знает, как такие функции можно сделать по другому, без LIKE?

2. SELECT * FROM table WHERE field != 1000 ORDER BY id LIMIT 50
В этом случае выбирает (всмысле сканирует) все записи, их там миллионы, логичней было бы проверить только 50, отсортированных по индексу

База данных: MYSQL
Спасибо

👍ПодобаєтьсяСподобалось0
До обраногоВ обраному0
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Може я помиляюся, але для таких питань є stackoverflow.

Коментар порушує правила спільноти і видалений модераторами.

По 1 вопросу, только FullTextSearch решит вопрос (самодельный вариант сплита на слова и ведения меппинга слов к фразам не рассматриваем :))
По 2. Как я понял есть Clustered PK по ID.
Далее варианты
2.1. По Field нет индекса. Имеем 100 % скан по таблице без вариантов
2.2 По Field есть индекс.
Имеем следующие варианты.
2.2.1 Статистика распределения говорит что в атрибуте Field значений = 1000 более 90%.
тогда на плане будем видеть Index Seek + KeyLookUP
2.2.2 Статистика распределения говорит что в атрибуте Field значений = 1000 менее 90%.
имеем тот же скан как и в п 2.1.

Исходя из такого положения можно говорить что почти всегда будет clustered index scan. Или попросту говоря ПРЕДИКАТ У ВАС ТАКОЙ ;)

PS. примечание к п 2.2.х граница смены оптимизатором стратегии доступа к данным скан/сик примерно находится на уровне 10% от выбираемых записей , и зависит от ширины строки, и веса IO, наличия актуальной статистики и т.д

єто мускуль, он не умеет использовать несколько индексов по одной таблице.
обычно он должен сканить по primary сразу в правильной сортировке, проверяя «ручками» field («Using where» в explain)

єто мускуль, он не умеет использовать несколько индексов по одной таблице.

O_O

єто мускуль, он не умеет использовать несколько индексов по одной таблице.
чё?
If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer will attempt to use the Index Merge optimization (see Section 8.3.1.4, “Index Merge Optimization”), or attempt to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.

dev.mysql.com/...ql-indexes.html

ну вот как-то так получается на практике, что если он таки решает смержить индексы получается проседание по скорости на порядки.

это уже совсем другой вопрос. Но уметь — умеет.

умеет, но неэффективно, так наверное было бы правильнее сказать.

опять лучи субстанции ?
для остальных ссылко
jorgenloland.blogspot.com/...n-mysql-56.html

опять лучи субстанции ?
Очередное выведение псевдоексперта на чистую воду
для остальных ссылко
jorgenloland.blogspot.com/...n-mysql-56.html
То что оптимизатор запросов лажает иногда не значит что «если он таки решает смержить индексы получается проседание по скорости на порядки.» Если все оттюнено как раз скорость вырастает на порядки.
Если все оттюнено как раз скорость вырастает на порядки.
сразу видно формошлепа.

для остальных просто дополнение — когда вместо использования составного индекса начинается merge — проседание на порядки
даже на синтетическом примере получается на порядок, притом в реальной жизни еще будет filesort
www.mysqlperformanceblog.com/...vs-index-merge

сразу видно формошлепа.
Сразу видно звездуна, выше ни о каком составном индексе речи не шло.
А еще что бы доказать твою неформошлеперность приведика пожалуйста пруф на возможность создания составного индекса по числовому и строковому полю с возможностью полнотекстового поиска.

Видел что MySQL. Где я указал что нужно использовать два индекса одновременно ?
Если .Вы про

Index Seek + KeyLookUP
то это твивиальная операция, решаемая через Nested Loop Join и судя по спецификации dev.mysql.com/...loop-joins.html он поддерживается.

основной поинт — решение какой индекс выбрать. Делается это на основонании предпологаемого кол-ва страниц необходымых вычитать (IO) и затрат CPU.
А исходя из предиката != мы всегда имеем скан. И что самое важное — это правильный выбор.

Покрайней мере в MS SQL Это так. Не думаю что MySQL настолько туп.
Если внес сумятицу в Ваши головы — сорри....

Коментар порушує правила спільноти і видалений модераторами.

full text search в первом случае уже посоветовали как я понимаю, во втором случае не != провоцирует фул скан ли, что то мне не нравится этот запрос....

Мускул уже научился оптимизировать такие выборки с лимитом, не будет там фулскана, если по «field» и «id» построен индекс.

Хорошие новости, раньше это утомляло. Надо поковырять свежие релизы мускуля

Я вам больше скажу — иннодб уже умеет полнотекстовый поиск «из коробки»!
-1 каверзный вопрос на собеседованиях :)

я немного перефокусирован на java последние годы, похоже уже стоит актуализировать знания по mySQL )))

Возможно не стоит стараться, mysql full text search еще тот тормоз.

Вообще, да, по полнотекстовому поиску всё так же рулит сфинкс с люценами, то я так, для справки про иннодб написал :)

Мускул уже научился оптимизировать такие выборки с лимитом, не будет там фулскана, если по «field» и «id» построен индекс.
не будет(не должно быть) фулскана даже если по field нет индекса, обычно он выбирает по индексу по id и затем накладывает «ручное» where

он НЕ может выбрать вначале, используя индекс по ID, 50 записей, а потом среди этих них проверять условие.
поэтому как ни крути, и как это не называй, но по сути будет фулскан.

он НЕ может выбрать вначале, используя индекс по ID, 50 записей, а потом среди этих них проверять условие.
А БД обычно и не выбирает 50 записей, там обычно создается поток отсортированных записей и в процессе выполнения происходит чтение из потока, и заканчивается по удовлетворению усовия LIMIT, задолго до полного фул скана. Это в теории конечно, на практике хз как там мскл в данном случае глючит.

мускуль подхватывает индекс для сортировки только в специальных случаях. если не получается — он честно выберет все записи удовлетворяющие условию, отсортирует их ручками и вернет 50

Это спекуляции на фразе «только в специальных случаях», у меня все подхватывается на ура и работает как я описал без фулсканов

счастливый.
сколько говоришь у тебя сотен миллионов строк в наибольших таблицах ?
и запросов в секунду поди в 5 знаках ?

Где то так, только в некоторых местах на порядок больше.

вот не верю,
назови основную(ые) проблему(ы) дефолтного конфига мускуля на многоголовых серверах ?

Я админством мскл не занимаюсь, есть специальные люди для этого, я запросики пишу, логику шардинга и решардинга имплементирую, на разный nosql выношу проблемные участки, джава код и инфраструктуру оптимизирую что бы все рабоптало и укладывалось в 50ms, это то что касается инфраструктуры, но есть еще и другие задачи.

окей. хотя меня админы и пинули от безысходности.
а просто «в слепую» ответить , на основе опыта-интуиции ?

Без понятия, я апологет постгреса, там все работает из коробки, мскл использую на работе по принуждению.
Без понятия, я апологет постгреса, там все работает из коробки, мскл использую на работе по принуждению. Наверное куда то нужно в какие то пулы каких то потоков добавить.

ок. вопрос по постге и наоборот. тут уж я «в слепую» , где затыкается постгря ?

Постгрес хороший кусок кода, и на моих пэт задачах затыкается крайне редко. Раньше были проблемы с вакуумом, но сейчас все как то саморазруливается.

т.е. я могу считать что то, что когда ты
логику шардинга и решардинга имплементирую, на разный nosql выношу проблемные участки
это просто блажь от скуки, типа некуда деть неуемную энергию ?

Нет, это не блаж, а насущные проблемы, но я не считаю их затыком БД.

ну вот мне и хотелось услышать, почему возникли эти данные насущные проблемы
реально я хотел услышать про contention

Шардинг или вынос в nosql мы делаем потому что/когда база начинает упираться в проц и/или память и поэтому получаются провалы в производительности. Я не сильно в курсах что ты имеешь в виду под «contention».

Я не сильно в курсах что ты имеешь в виду под «contention».
вобщем когда в многоядерном сервере несколько «ядер» пытаются изменить одну «страницу» памяти , защищенную локом-мутексом, в результате они выполняются последовательно (сериализируются) снижая общую производительность
визуально выглядит какбудто ядра не нагружены полностью, но при этом context-switches (vmstat cs) зашкаливает
шардинг (даже внутри одной базы-сервера) позволяет распределить нагрузку, соответственно contention уменьшается , общая производительность возрастает «в разы»
я не скажу за постгрю, но в мускуле можно память разбить на несколько независимых блоков (как и пространство адаптивных хешей) и получить лучшую масштабируемость (такой себе низкоуровневый шардинг, на уровне страниц базы)

а подобрать innodb_thread_concurrency не может несколько сгладить проблему? меньеш потоков- меньше свитчей.

оно решает проблему кардинально.
вот у тебя 16 головый ксеон, пошли проблемы — ты ставишь concurrency 8, все, проблема ушла, но половина голов стоит(ну там одно еще коннекты и ядро обслуживает)
неаккуратно получается , зачем тогда было покупать 16 головый сервер

там есть другая «крайность» — много длинных транзакций , тут да, но опять же много проще переключится на гуглевский патч, который вместо зацикленных локов (rw lock )использует time based scheduling , т.е. локов нет, есть квантование времени. но это эффективно если больше 150 активных транзакций в мгновение

А где и как ты такое прочитал/узнал/выяснил? Как ты знаешь что проблема именно в сериализации изменений страниц памяти и что это корелируется с переключением контекста, что именно за шардинг ты имеешь в виду, какими опциями ты разбиваешь память?

та лан, ничего умного — vmstat+top ,понять что проц не нагружен полностью, а cs зашкалило, потом нагуглить че там есть по настройкам, потом подобрать нужные параметры (самое длинное, ибо адаптивные заморочки мускуля, чуть ли не неделю надо ждать до прогрева, а пацаны из перконы\гугля не особо спешили с доками)

ясно, конкретики как обычно услышать неудастся.

зачем конкретика ? куда копать — я рассказал, конкретные параметры зависят от задачи. не. я могу конечно написать что
innodb_adaptive_hash_index_partitions 16
innodb_buffer_pool_instances 12
thread_concurrency 24
query_cache_type OFF
и ?
я могу поспорить что на другой задаче оно не пойдет как надо

Ну да, у меня тоже сложилось впечатление что ты вычитал рецептик в интернете на каком то бложике, он случайно заработал(а возможно и нет), а все теории про шардинг страниц памяти с целью уменьшения контеншна — теории буйного воображения. Забавляет что ты это преподносишь как must have знание для разработчика нагруженных систем. Интересно было бы взглянуть как ты собеседования проводишь.

увы. такое не гуглицца и лучше бы никто не знал, у меня тогда рынок больше :)

а собеседования со мной простые — практические задачи не имеющие точного решения, тот кто находит хотя бы одно удачное решение — проходит, потом — ревью кода, там уже по разному получается

thread_concurrency 24
innodb_thread_concurrency

ага thread_concurrency крутить не поможет

в том что по дефолту он их не использует... Самое смешное что я как то видел дефолтный конфиг на продакшене, под хорошей нагрузкой. Было смешно когда именно фикс этого параметра сделал серверу хорошо

ну я вообще про общий вариант, там одна проблема и несколько точек где ее нужно решать
я вот предполагаю что вы имели в виду query cache , а я всетаки больше про innodb_buffer_pool_instances и innodb_adaptive_hash_index_partitions

ну я все же не настоящий dba все же. Было довольно давно это, я вообще почему то думал что я крутил thread_concurrency, полез проверил — точно не оно :)

и thread_concurrency тоже крутят, кстати, только она сразу срезает производительность, поэтому разве что как временный фикс.
я тоже не дба, но получилось что мне проще понять что этот мускуль хочет

ну и реально я хотел услышать про contention только, без ключей

т.е. если повезет, просканируем небольшую кучу данных, а если совсем не повезет, то 100% отсканируем.
я думаю, что на практике просто поставить LIMIT недостаточно.
а в теории фулскан есть фулскан, т.е. если теоретически оценивать временную сложность данного решения, то сложность = фулскану. А то, что иногда может повезти и не придется просматривать все-все записи, то это опять же не меняет сути решения.

т.е. LIMIT не решает поставленной задачи, и нужно что-то другое придумать, чтобы этот запрос более-менее нормально работал.

на практике такие запросы редко возникают, т.е. для конечного юзера они не настолько критичны и обычно условие идет сложное, что позволяет отсеять количество просматриваемых записей на раннем этапе, типа такого:
select .. from ..
where field1 IN (v1,v2,v3) AND field2!=1000
ORDER .. LIMIT

— с другой стороны, если будет просто WHERE field!=1000, то тут есть два варианта.

когда field2 — хранит ограниченный набор, т.е. некие константы
тогда field!=1000 переписывается как field IN (ограниченный набор).

когда field2 может хранить любое число, т.е. мощность множества почти бесконечна.
Но такой запрос нужно просто избегать делать и переделать логику, чтобы такого не было.
Представить себе запрос, когда пользователю нужно выбрать field!=1000.
Например, если field — это цена, то искать товары, у которых цена != 1000 как-то странно.
Обычно ищут по диапазону, WHERE field BETWEEN 500 AND 1000.
Если же field = бренд или категория товара, то это вполне жизненный запрос, когда хотят найти все товары, кроме заданного бренда. Но в этом случае поиск будет по ограниченному набору значений. И в UI это будет выглядеть как набор отмеченных брендов, т.е. поиск с позитивным условием (field IN (..)).

Общий вывод — не пишите такие запросы.
Если они у вас возникли, сначала подумайте, может с логикой/требованиями к приложению, что-то не так и задачу можно решить по-другому.

А то, что иногда может повезти и не придется просматривать все-все записи, то это опять же не меняет сути решения.
Я согласен с тем что ты здесь написал, я просто уточнил картину по механике выполнения запроса.

да, в случае если все field равны 1000, а мы ищем field != 1000 это будет фулскан.
но реально сканирование остановится как только найдется 50 нужных записей, что намного легче

Версия мускула какая?

«mysql —version» поможет узнать.

Очень древняя версия, мускул сильно поумнел с 5.0. Рекомендую обновиться до актуальной, решите по крайней мере вторую проблему, и лишитесь подобных в будущем.

поумнел разве что в InnoDB. MyISAM остался таким же.

Поумнел оптимизатор запросов. А это касается всех подсистем хранения :)

Выигрыш от поумнения — максимум милисекунды.

Впрочем у ТС как оказалось проблема не с БД совершенно.

Выигрыш от поумнения — максимум милисекунды

Пешите исчо :)

зачем ? Вы же писатель на ДОУ, я так — мимо проходил.

Как зачем? Мы же должны знать, что оптимизировать оптимизатор запросов — это глупости и вообще неэффективно :)

1.
LIKE ’%smth%’
=> LIKE ’smth%’
Индекс будет работать, и возможно даже результаты работы «подсказки» устроят
2.
field != 1000
Нету индекса на field ===> от full table scan никуда не деться (в независимости от primary(id))

1. В этом случае может найти только результаты, которые начинаются с smth, мне же нужен полнотекстовый поиск. На поле field стоит индекс FULLTEXT. Я не знаю толком, как работает этот индекс, но вижу что таблица с 4000 записей (каждая около 20-30 символов) доставляет проблемы.
2. Индекс есть, более того mysql его выбирает, как основной. Это я ниже написал в результатах команды EXPLAIN. Там ниже дали пример с вложенным запросом, я попробую его

FULLTEXT
index используется не с LIKE, а с MATCH — AGAINST конструкцией.
В примере ниже (при решении проблемы медлительности LIMIT) упущен важный момент — фильтрация по field.

4000 * 30 ~ 120 000 символов. В случае «тяжелых» кодировок типа юникода это примерно 1 Мб. Не хотите сделать кэширование таблицы в памяти клиента или хотя бы самовосстанавливаемую копию таблицы с ENGINE=MEMORY ?

У меня 1251 general ci
И я конфигом базы ограничиваю выборку только первых 20 символов в каждом поле для оператора LIKE.
То что вы написали дальше, я не понял. Если можно поподробнее или просто ссылку какую-то.
Сегодня я добавил оперативки, перенастроил конфиг my.ini и оптимизировал запросы, все что поддавались оптимизации. Еще установил mysqltuner.pl, очень классная штука, сразу говорит какие параметры в базе плохие и что подправить / увеличить.

На данный момент основная проблема осталась с этими запросами с LIKE. Попробовал сделать промежуточную таблицу, куда пишу 200 самых популярных записей, но это на удивление не помогло. Все равно тысячи записей в mysql_slow.log.

А вторая описанная проблема в топике судя по всему решилась вложенным запросом

Что такое Сфинкс я не знаю, если ничего не поможет, буду разбираться как его устанавливать и настраивать

Под кэшированием на стороне клиента я подразумевал изменение клиентского кода таким образом, чтобы перед отображением контрола, для которого нужно автодополнение, выполнялось select field from table без условий и результат записывался в некоторую переменную Х на клиенте (Javascript-массив?). Тогда автодополнение можно переписать для использования в качестве источника данных не прямой запрос в базу, а выборку из переменной Х. В случае особенно веб-приложения будет некоторая задержка первой загрузки страницы, но не нужно будет вообще выполнять AJAX-запросов.

Вторая часть ответа как раз подразумевала то, что вы описываете как промежуточную таблицу — только создавать ее через CREATE TABLE xxx (...) ENGINE=MEMORY; Для такой таблицы на диск сохраняется только структура, а все данные хранятся в оперативной памяти сервера БД и теряются при перезапуске сервера. Для вашей ситуации вполне может помочь. Подробнее: dev.mysql.com/...age-engine.html

Что такое Сфинкс я не знаю, если ничего не поможет, буду разбираться как его устанавливать и настраивать
Там нефиг чего настраивать и для поиска будет самое оно. Для подробностей напиши мне в скайп завтра.
В случае «тяжелых» кодировок типа юникода это примерно

360 KB.

1. SELECT * FROM table WHERE field LIKE ’%smth%’
Здесь использование % в начале отключает индексы. Этот запрос работает в функции на ajax типа «выпадающий список» по набранным символам. Может кто-то знает, как такие функции можно сделать по другому, без LIKE?
самый правильный путь ставить полнотекстовый поисковый сервер — типа sphinx — так будет быстрее.
если нет возможности, то можно использовать full-text индексы из mysql (работают только с myisam и медленнее сфинкс) , либо строить свой псевдо-индекс ручками ...
1. SELECT * FROM table WHERE field LIKE ’%smth%’
Здесь использование % в начале отключает индексы. Этот запрос работает в функции на ajax типа «выпадающий список» по набранным символам. Может кто-то знает, как такие функции можно сделать по другому, без LIKE?

Зависит от базы, в постгресе например есть для этого www.postgresql.org/...tic/pgtrgm.html

2. SELECT * FROM table WHERE field != 1000 ORDER BY id LIMIT 50
В этом случае выбирает все записи, их там миллионы, а нужно только 50.

На ID есть индекс? Покажи план выполнения запроса? С чего ты взял что выбираются все записи?

1. У меня mysql
2. Делаю так
EXPLAIN SELECT * FROM comments WHERE news_id != 1549 ORDER BY id LIMIT 50
получаю
id *** select_type *** table *** type *** possible_keys *** key *** key_len *** ref *** rows *** Extra

1*** SIMPLE *** comments *** range *** news_id,comments *** news_id*** 4 *** NULL *** 263359 *** Using where; Using filesort
Не знаю правда как копировать из phpmyadmin, чтобы красиво было.
То есть выбираются 263 тыс записей (то есть общее число записей из comments у которых news_id = 1549)
На id стоит PRIMARY индекс

можно use index использовать, вроде некрасиво, а что делать
либо двух проходный вариант.
сначала берем 150-200 записей по id , потом уже отфильтровываем news_id

Делаю так
EXPLAIN SELECT * FROM comments ORDER BY id DESC LIMIT 200
Результат пишет, что используется PRIMARY индекс по полю id и просканировано 398492 записей, то все записи в таблице comments. Я не знаю насколько это верно, поидее правильнее было бы просто взять первые 200 записей, раз использован индекс. При нагрузке в 4-5 тыс юзеров онлайн этот запрос выростает до 8-10 секунд.

гм. чет тут нетак.
id какого типа ?
analyze table ?
версия сервера слишком древняя ?
опять же что с рам и все такое ? может сервак черпает диск ?
percona ?

ID — int 11, auto increment, primary index
analyze table что должно показать? Пишет Ok
Я не думаю, что это может от версии сервера зависеть, в прошлом году обновлял
Оперативки 4 гигабайта, ее врядли хватает при максимальных нагрузках, но даже если там и используется своп, то как это может влиять на работу базы данных? Всмылсе не на скорость работы, а на механизм.
percona — это что-то для проверки медленных запросов?

analyze table что должно показать? Пишет Ok
а теперь еще раз запустить explain
просто оптимизатор мускуля статистический и иногда лажает, особенно если статистика неправильная (старая или взята на малом диапазоне)
Я не думаю, что это может от версии сервера зависеть, в прошлом году обновлял
ну не знаю, они чуть ли не каждую неделю апдейты выпускают
Оперативки 4 гигабайта, ее врядли хватает при максимальных нагрузках, но даже если там и используется своп, то как это может влиять на работу базы данных? Всмылсе не на скорость работы, а на механизм.
ну там многие механизмы учитывают наличие свободной памяти, явно или неявно. вообще в идеале база должна почти вся лежать в памяти (активная часть), если хоть немного не помещается — начинается диск, который на порядки медленнее чем рам
percona — это, что-то для проверки медленных запросов?
это форк мускуля под хайлоад. там много вкусностей, например в логах можно получить сколько страниц данных «прошерстил» запрос , что более точно для оценки запросов
многие механизмы учитывают наличие свободной памяти, явно или неявно. вообще в идеале база должна почти вся лежать в памяти (активная часть), если хоть немного не помещается — начинается диск, который на порядки медленнее чем рам
Вполне возможно, но я уже задолбался настраивать конфиг mysql. Это явно не для обычных людей сделано. Не знаете ли ссылку, где можно вписать параметры моего сервера и чтобы выдало оптимальный конфиг mysql?

оптимальный конфиг только руками
ну можно попробовать чет типа mysqltuner.pl

SELECT * FROM ( SELECT * FROM comments ORDER BY id WHERE id > (398492 — 200) ) t1 ORDER BY t1.id DESC;

:/

Спасибо, интересный вариант. Попробую его сегодня на нагрузке. По крайней мере EXPLAIN говорит, что все хорошо

1. Если твое smth это токенизируемое слово, то можно прикрутить движек полнотекстового поиска, в mysql есть уже такое, оно медленно работало, но лучше чем full scan dev.mysql.com/...ext-search.html

2. Ты же писал что записей миллионы? т.е. может базе и нужно вытащить 200 тыс что бы найти 50 соответствующих твоему критерию?

2. Да не может такого быть... тащить 200 тыс запросов для выборки 50-ти при сортировке всего по одному полю, которое к тому же primary index

Если у вас есть база под рукой, можете выполнить такой запрос c EXPLAIN к какой-нибудь таблице с большим числом записей? Важно, что будет в поле rows, после выполнения запроса, то есть сколько полей база просканировала.
SELECT * FROM table WHERE field != 1000 ORDER BY id LIMIT 50
Просто интересно, чтобы понять, только ли у меня идет скан всей таблицы из-за каких-то неправильных настроек.

У тебя есть еще фильтр по полю, но вообще да, думаю выглядит странно.

Просто интересно, чтобы понять, только ли у меня идет скан всей таблицы из-за каких-то неправильных настроек.
все правильно, индексация баз по сути делается с помощью kd-деревьев, если есть сравнение >,< или == , то при траверсе дерева отсекаются ветви не удовлетворяющие условию поиска. Для сбалансированного дерева компексити будет O(log n), в случае условия != отсечение выполнить не возможно, потому получается компексити O(n) (то есть все записи в таблице)

Підписатись на коментарі