Вхід і реєстрація
Розробка · 9 січня 2013, 0:12 3582
Чорний Список Юджина

Оптимизация 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
Спасибо

Теми: 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
Petro Sidlovskyy Software Engineer в DevRecords 11.01.2013 11:18

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

Відповісти
Підтримати
Kostiantyn Reu Sr. Principal Data Engineer в GoodRx 10.01.2013 20:36

По 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, наличия актуальной статистики и т.д

Відповісти
Підтримати
proger 11.01.2013 00:21

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

Відповісти
Підтримати
Kostiantyn Reu
двадцатитрёхлетний сениор 11.01.2013 00:59
єто мускуль, он не умеет использовать несколько индексов по одной таблице.

O_O

Відповісти
Підтримати
proger
Станіслав Малкін DevOps в Flix 11.01.2013 05:30
єто мускуль, он не умеет использовать несколько индексов по одной таблице.
чё?
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

Відповісти
Підтримати
proger
proger 11.01.2013 06:40

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

Відповісти
Підтримати
Станіслав Малкін
Станіслав Малкін DevOps в Flix 11.01.2013 06:41

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

Відповісти
Підтримати
proger
proger 11.01.2013 06:54

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

Відповісти
Підтримати
Станіслав Малкін
proger 11.01.2013 07:17

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

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

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

Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 10:23
сразу видно формошлепа.
Сразу видно звездуна, выше ни о каком составном индексе речи не шло.
А еще что бы доказать твою неформошлеперность приведика пожалуйста пруф на возможность создания составного индекса по числовому и строковому полю с возможностью полнотекстового поиска.
Відповісти
Підтримати
proger
Kostiantyn Reu Sr. Principal Data Engineer в GoodRx 11.01.2013 18:31

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

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

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

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

Відповісти
Підтримати
proger
Нестор Иванович 09.01.2013 20:52

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

Відповісти
Підтримати
двадцатитрёхлетний сениор 10.01.2013 00:11

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

Відповісти
Підтримати
Нестор Иванович
Нестор Иванович 10.01.2013 00:24

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

Відповісти
Підтримати
двадцатитрёхлетний сениор
двадцатитрёхлетний сениор 10.01.2013 00:32

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

Відповісти
Підтримати
Нестор Иванович
Нестор Иванович 10.01.2013 00:50

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

Відповісти
Підтримати
двадцатитрёхлетний сениор
reality_hacker 10.01.2013 01:00

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

Відповісти
Підтримати
Нестор Иванович
двадцатитрёхлетний сениор 10.01.2013 11:04

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

Відповісти
Підтримати
Нестор Иванович
proger 10.01.2013 02:01
Мускул уже научился оптимизировать такие выборки с лимитом, не будет там фулскана, если по «field» и «id» построен индекс.
не будет(не должно быть) фулскана даже если по field нет индекса, обычно он выбирает по индексу по id и затем накладывает «ручное» where
Відповісти
Підтримати
двадцатитрёхлетний сениор
Max I CEO в FriendlyData 11.01.2013 11:00

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

Відповісти
Підтримати
proger
reality_hacker 11.01.2013 11:03
он НЕ может выбрать вначале, используя индекс по ID, 50 записей, а потом среди этих них проверять условие.
А БД обычно и не выбирает 50 записей, там обычно создается поток отсортированных записей и в процессе выполнения происходит чтение из потока, и заканчивается по удовлетворению усовия LIMIT, задолго до полного фул скана. Это в теории конечно, на практике хз как там мскл в данном случае глючит.
Відповісти
Підтримати
Max I
proger 11.01.2013 11:26

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

Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 20:57

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

Відповісти
Підтримати
proger
proger 11.01.2013 21:05

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

Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 21:09

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

Відповісти
Підтримати
proger
proger 11.01.2013 21:35

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

Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 21:38

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

Відповісти
Підтримати
proger
proger 11.01.2013 22:08

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

Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 22:14

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

Відповісти
Підтримати
proger
proger 11.01.2013 22:43

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

Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 23:35

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

Відповісти
Підтримати
proger
proger 11.01.2013 23:40
т.е. я могу считать что то, что когда ты
логику шардинга и решардинга имплементирую, на разный nosql выношу проблемные участки
это просто блажь от скуки, типа некуда деть неуемную энергию ?
Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 23:48

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

Відповісти
Підтримати
proger
proger 11.01.2013 23:51
ну вот мне и хотелось услышать, почему возникли эти данные насущные проблемы
реально я хотел услышать про contention
Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 23:56

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

Відповісти
Підтримати
proger
proger 12.01.2013 00:24
Я не сильно в курсах что ты имеешь в виду под «contention».
вобщем когда в многоядерном сервере несколько «ядер» пытаются изменить одну «страницу» памяти , защищенную локом-мутексом, в результате они выполняются последовательно (сериализируются) снижая общую производительность
визуально выглядит какбудто ядра не нагружены полностью, но при этом context-switches (vmstat cs) зашкаливает
шардинг (даже внутри одной базы-сервера) позволяет распределить нагрузку, соответственно contention уменьшается , общая производительность возрастает «в разы»
я не скажу за постгрю, но в мускуле можно память разбить на несколько независимых блоков (как и пространство адаптивных хешей) и получить лучшую масштабируемость (такой себе низкоуровневый шардинг, на уровне страниц базы)
Відповісти
Підтримати
reality_hacker
Нестор Иванович 12.01.2013 00:28

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

Відповісти
Підтримати
proger
proger 12.01.2013 01:06

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

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

Відповісти
Підтримати
Нестор Иванович
reality_hacker 12.01.2013 00:48

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

Відповісти
Підтримати
proger
proger 12.01.2013 01:24

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

Відповісти
Підтримати
reality_hacker
reality_hacker 12.01.2013 01:26

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

Відповісти
Підтримати
proger
proger 12.01.2013 01:39

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

Відповісти
Підтримати
reality_hacker
reality_hacker 12.01.2013 01:43

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

Відповісти
Підтримати
proger
proger 12.01.2013 02:00

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

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

Відповісти
Підтримати
reality_hacker
proger 14.01.2013 14:16
thread_concurrency 24
innodb_thread_concurrency
Відповісти
Підтримати
proger
Нестор Иванович 15.01.2013 03:22

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

Відповісти
Підтримати
proger
Нестор Иванович 11.01.2013 21:51

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

Відповісти
Підтримати
proger
proger 11.01.2013 23:46

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

Відповісти
Підтримати
Нестор Иванович
Нестор Иванович 11.01.2013 23:58

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

Відповісти
Підтримати
proger
proger 12.01.2013 00:09

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

Відповісти
Підтримати
Нестор Иванович
proger 11.01.2013 23:49

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

Відповісти
Підтримати
Нестор Иванович
Max I CEO в FriendlyData 11.01.2013 12:31

т.е. если повезет, просканируем небольшую кучу данных, а если совсем не повезет, то 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 (..)).

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

Відповісти
Підтримати
reality_hacker
reality_hacker 11.01.2013 21:00
А то, что иногда может повезти и не придется просматривать все-все записи, то это опять же не меняет сути решения.
Я согласен с тем что ты здесь написал, я просто уточнил картину по механике выполнения запроса.
Відповісти
Підтримати
Max I
proger 11.01.2013 11:17

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

Відповісти
Підтримати
Max I
двадцатитрёхлетний сениор 09.01.2013 19:04

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

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

Відповісти
Підтримати
двадцатитрёхлетний сениор 10.01.2013 11:06

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

Відповісти
Підтримати
Чорний Список Юджина
Станіслав Малкін DevOps в Flix 11.01.2013 05:31

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

Відповісти
Підтримати
двадцатитрёхлетний сениор
двадцатитрёхлетний сениор 11.01.2013 12:51

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

Відповісти
Підтримати
Станіслав Малкін
Станіслав Малкін DevOps в Flix 12.01.2013 03:29

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

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

Відповісти
Підтримати
двадцатитрёхлетний сениор
двадцатитрёхлетний сениор 12.01.2013 12:45
Выигрыш от поумнения — максимум милисекунды

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

Відповісти
Підтримати
Станіслав Малкін
Станіслав Малкін DevOps в Flix 12.01.2013 15:16

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

Відповісти
Підтримати
двадцатитрёхлетний сениор
двадцатитрёхлетний сениор 12.01.2013 17:40

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

Відповісти
Підтримати
Станіслав Малкін
Iurii Potehin at KoloGlobal в Kolo Global 09.01.2013 15:42
1.
LIKE ’%smth%’
=> LIKE ’smth%’
Индекс будет работать, и возможно даже результаты работы «подсказки» устроят
2.
field != 1000
Нету индекса на field ===> от full table scan никуда не деться (в независимости от primary(id))
Відповісти
Підтримати
Чорний Список Юджина 09.01.2013 15:54

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

Відповісти
Підтримати
Iurii Potehin
Iurii Potehin at KoloGlobal в Kolo Global 09.01.2013 17:14
FULLTEXT
index используется не с LIKE, а с MATCH — AGAINST конструкцией.
В примере ниже (при решении проблемы медлительности LIMIT) упущен важный момент — фильтрация по field.
Відповісти
Підтримати
Чорний Список Юджина
Sergey Kudriavtsev Lead developer and technical co-founder в YOUR 10.01.2013 15:46

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

Відповісти
Підтримати
Чорний Список Юджина
Чорний Список Юджина 10.01.2013 15:55

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

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

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

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

Відповісти
Підтримати
Sergey Kudriavtsev
Sergey Kudriavtsev Lead developer and technical co-founder в YOUR 10.01.2013 16:12

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

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

Відповісти
Підтримати
Чорний Список Юджина
Станіслав Малкін DevOps в Flix 11.01.2013 05:33
Что такое Сфинкс я не знаю, если ничего не поможет, буду разбираться как его устанавливать и настраивать
Там нефиг чего настраивать и для поиска будет самое оно. Для подробностей напиши мне в скайп завтра.
Відповісти
Підтримати
Чорний Список Юджина
двадцатитрёхлетний сениор 10.01.2013 15:55
В случае «тяжелых» кодировок типа юникода это примерно

360 KB.

Відповісти
Підтримати
Sergey Kudriavtsev
Sergey Kudriavtsev Lead developer and technical co-founder в YOUR 10.01.2013 16:02

Да, вы правы. Тем более.

Відповісти
Підтримати
двадцатитрёхлетний сениор
proger 09.01.2013 00:44
1. SELECT * FROM table WHERE field LIKE ’%smth%’
Здесь использование % в начале отключает индексы. Этот запрос работает в функции на ajax типа «выпадающий список» по набранным символам. Может кто-то знает, как такие функции можно сделать по другому, без LIKE?
самый правильный путь ставить полнотекстовый поисковый сервер — типа sphinx — так будет быстрее.
если нет возможности, то можно использовать full-text индексы из mysql (работают только с myisam и медленнее сфинкс) , либо строить свой псевдо-индекс ручками ...
Відповісти
Підтримати
reality_hacker 09.01.2013 00:16
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 есть индекс? Покажи план выполнения запроса? С чего ты взял что выбираются все записи?

Відповісти
Підтримати
Чорний Список Юджина 09.01.2013 00:32

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 индекс

Відповісти
Підтримати
reality_hacker
proger 09.01.2013 00:39

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

Відповісти
Підтримати
Чорний Список Юджина
Чорний Список Юджина 09.01.2013 00:46

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

Відповісти
Підтримати
proger
proger 09.01.2013 01:01

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

Відповісти
Підтримати
Чорний Список Юджина
Чорний Список Юджина 09.01.2013 01:30

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

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

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

Відповісти
Підтримати
Чорний Список Юджина
anonymous 09.01.2013 14:12

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

:/

Відповісти
Підтримати
Чорний Список Юджина
Чорний Список Юджина 09.01.2013 16:02

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

Відповісти
Підтримати
anonymous
reality_hacker 09.01.2013 01:05

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

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

Відповісти
Підтримати
Чорний Список Юджина
Чорний Список Юджина 09.01.2013 01:17

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

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

Відповісти
Підтримати
reality_hacker
reality_hacker 09.01.2013 01:19

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

Відповісти
Підтримати
Чорний Список Юджина
Володимир Дубовий Dev in Looksery 09.01.2013 16:16
Просто интересно, чтобы понять, только ли у меня идет скан всей таблицы из-за каких-то неправильных настроек.
все правильно, индексация баз по сути делается с помощью kd-деревьев, если есть сравнение >,< или == , то при траверсе дерева отсекаются ветви не удовлетворяющие условию поиска. Для сбалансированного дерева компексити будет O(log n), в случае условия != отсечение выполнить не возможно, потому получается компексити O(n) (то есть все записи в таблице)
Відповісти
Підтримати
Чорний Список Юджина

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

Не підписуватись
Скористайтесь акаунтом
×
з умовами використання сайту і політикою конфіденційності.