здравствуйте,
Прошу помощи. Вроде бы не первый раз с БД работаю, но как-то давно не сталкивался с классикой и застрял.
Мускул 5.1.57 если что, таблицы myisam (нужен фултекст, так что без вариантов, наверное)

Классическая казалось бы задача

Таблица categories_rel - связь между категориями и товарами. около 12млн записей.
уникальных catid около 60 тысяч
catid, goodid
оба поля unsigned int (11), висит индекс на одном поле, на другом поле и даже висел на обоих

goods - товары. около 1млн записей
goodid - unsigned int (11), первичный ключ, т.е. понятно что с индексом
orderid - unsigned int (11), висит индекс, для сортировки вывода
datagood text - висит фултекст, собственно данные по товару
и еще несколько цифровых полей

Стандартная задача - выбрать все товары из одной категории, отсортировать по orderid Полагал могу рассчитывать на время порядка 0.01 или лучше. Фигу.
Вариант 1


Выполняется за 0.3 секунды. Всего 60к+ записей выдает
По профилированию 0.22 из них это Copying to tmp table. И0.02 этоSorting result
По эксплеину
1 SIMPLE b ref catid,goodid catid 4 const 93574 Using where; Using temporary; Using filesort
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 x_base.b.goodid 1
Если выбирать только goods.id (но datagood тоже нужен) получается 0.2 секунды, в принципе это быстрее в итоге (довыбрать еще 1 запросом остатки данных), но недостаточно.

Вариант 1.1.
То же что вариант 1, но перевернутый - беру таблицу связей к ней джоиню таблицу товаров и сортирую. Получается на 10% дольше, все остальные симптомы те же. По правильному вроде джоинить к той таблице по которой идет сортировка, так что.

Вариант 1.2.
То же что вариант 1, но без сортировки. С практической точки зрения это абсолютно не юзабельно, но для данных и анализа пусть будет.
Выполняется за 0.1 секунды. Из них все 0.1 секунды это Sending data.
По эксплеину
SIMPLE b ref catid,goodid catid 4 const 93574 Using where
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 x_base.b.goodid 1

Вариант 1.3.
То же что вариант 1, но без лимита. Результат такой же.

Вариант 2


Выполняется 4 секунды с лимитом.
По профилированию все 4 секунды занимает preparing.
По эксплеину
1 PRIMARY goods ALL NULL NULL NULL NULL 980000 Using where; Using filesort
2 DEPENDENT SUBQUERY categories_rel index_subquery catid,goodid goodid 4 func 13 Using where
Любопытно что ВТОРОЙ запрос такой же тут же посланный выполняется за 0.0001 секунды
Тут это важно я считаю потому, что preparing занимает 4 секунды, таким образом если бы мускул каждый раз препарингом не занимался - все было бы кучеряво.
Без лимита выполняется 0.004 секунды!!!
С лимитом но без сортировки выполняется 0.27 секунды.


Что я еще попробовал.
А) Поскольку это еще и php, попробовал prepared statements, но по факту ПЛАН запроса компилируется каждый раз. Поэтому надежда составить типа шаблон для варианта 2 и потом подставлять в него разные ИД не прокатил.
Б) Поскольку от размера таблиц тоже иногда что-то зависит - попробовал вынести из goods самое жирное поле datagood, но на скорости это не сказалось от слова вообще никак


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

miksoftsgalaМускул 5.1.57 если что, таблицы myisam (нужен фултекст, так что без вариантов, наверное)В версиях 5.7.* и фултекст в InnoDB добавили, и убрали баг с тормозами конструкции IN (SELECT ...)К сожалению максимум на что могу апгрейднуться это 5.6:(

miksoftЕсли нет возможности обновить MySQL, то переписывайте запрос, избавляйтесь от IN (SELECT ...).Вот вопрос как переписать.
Посмотрите пожалуйста мой "вариант 1" , он без "селект ин", в принципе я с него и начал, но я категорически не понимаю почему он так много времени занимает - 0.3с.
Джоин по индексам должен был бы быть быстрым, а сортировка опять же идет по индексированному полю первой таблицы джоина - по манам как я понял индекс зацеплятся должен и мне почему-то кажется что раньше так не тормозило.
Что может быть не так?
Или в принципе это неверная идея запроса?

вадяавторК сожалению максимум на что могу апгрейднуться это 5.6:(
странное ограничениеконтрольная панель выше 5.6 не поддерживает, плюс некоторая универсальность должна быть.
в общем апгрейд не вариант.

поэтому или надо разбираться почему тормозит 1 вариант или придумывать 3-тий

Любопытно, поставил 5.6.
На 5.1 работал запрос


отрабатывал за 4с
на 5.6 такой запрос возвращает ошибку (1054 - Неизвестный столбец 'SQL_NO_CACHE' в 'field list')
ну а


отрабатывает за 0.3 секунды, что ЗНАЧИТЕЛЬНО лучше чем 4 секунды на 5.1.
По профайлингу получается 0.2 секунды из 0.3 это "sending data".
explain тоже другой
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL Using temporary; Using filesort
1 SIMPLE goods eq_ref PRIMARY PRIMARY 4 <subquery2>.qid 1 NULL
2 MATERIALIZED categories_rel ref catid,goodid catid 4 const 45180 NULL


Это значит в 5.6 этот баг уже поправили?

К сожалению 0.3 секунды не вариант:( Нужно на порядок меньше.

miksoftsgalaна 5.6 такой запрос возвращает ошибку (1054 - Неизвестный столбец 'SQL_NO_CACHE' в 'field list')Хинт в подзапросе и не нужен. Странно, что оно в 5.1 работало.
sgalaЭто значит в 5.6 этот баг уже поправили?Да, точно, в 5.6 поправили.кстати фултекст тоже в 5.6 появился походу.
Попробовал пока походу innoDB вместо myisam - запрос с джоинами остался тем же по времени, запрос с вложенным селектом стал дольше - вместо 0.3 стал минимум 0.6. Печаль, иннодб в сад в данном случае.

И все же проблема остается . 0.3 секунды как по мне так это очень много. Даже в 5.6 где баг с вложенным селектом исправили - получается в лучшем случае 0.3... Да и джоины из 1 варианта те же 0.3 секунды давали.

Если выбирать только ID товаров (для последующей выборки уже по ИД), то запрос сокращается до 0.2 секунды что уже лучше (на выборку товаров по ИД уходит 0.01 секунда потом), но во-первых это все равно много. А во вторых это какой-то бред что возврат данных из мускула с последующим запросом в него же занимает меньше времени чем если делать все внутри сразу.

Неужели нет какого-то 3 варианта? Или более шустрой реализации первых двух?
Я же вижу на нормальных сайтах у конкурентов выборка по категориям ну никак 0.3 секунды не занимает, учитывая то что там весь сайт грузится за 0.05, а тут жесть какая-то с тормозами. Я правильно все делаю? Или все же есть более другой способ?

miksoftПредлагаю эксперимент:
1) Создайте два индекса (goodid,orderid) и (orderid,goodid)
2) Выполните ANALYZE TABLE для goods и categories_rel
3) Попробуйте такой запрос:
1) При "составном" индексе запрос ускоряется на 10% по сравнению с обычным индексом, ну т.е. 0.28 вместо 0.3. При этом нет разницы в какую сторону строить.
2) Уже все ужато до минимума, по аналайзу и подправлял поля.
3) Да, пробовал, это как раз тот что я описывал как
sgalaЕсли выбирать только ID товаров (для последующей выборки уже по ИД), то запрос сокращается до 0.2 секунды что уже лучше (на выборку товаров по ИД уходит 0.01 секунда потом), но во-первых это все равно много. А во вторых это какой-то бред что возврат данных из мускула с последующим запросом в него же занимает меньше времени чем если делать все внутри сразу.

еще вопрос . напомните пожалуйста. есть что-то в mysql для углубленного анализа выполнения запроса?
я имею ввиду что бы понять влезла ли КОНКРЕТНАЯ выборка во временную таблицу в памяти или свалилась на диск и так далее? т.е. полная последовательность того чего мускул делает с запросом и как? не просто эксплеин.

miksoftsgalaна нормальных сайтах у конкурентов выборка по категориям ну никак 0.3 секунды не занимает,Мы же не знаем какая там структура БД.
Возможно, они используют денормализацию или еще что-то.я не против изменить структуру:) просто непонятно как.
в категории может быть до 300к товаров, так что совать их в текстовое поле к категории это жестоко и явно бессмысленно.
а других идей пока нет, many2many вроде же классика.
да и сам факт меня вымораживает долгой работы простого запроса - не должно быть так.

miksoftsgala,

Еще хорошо посмотреть бы, что у вас с размером всяких буферов.
В т.ч. c tmp_table_size и max_heap_table_size.16мб и то и другое.

miksoftsgalaя не против изменить структуру:) просто непонятно как.Например, можно создать копию поля orderid в таблице categories_rel. И для сортировки использовать именно его.Тут нюанс, полей для сортировки не одно, а около 4 штук, к текущим 12млн записей добавить еще 48 мегабайт - боюсь что таблица будет читаться еще дольше, а уж как там индексы будут ворочаться - страшно подумать.
Т.е. как я понимаю основной тормоз сейчас в том, что мускулу из 12млн записей нужно выдернуть нужные что бы составить таблицу для сортировки (раз уж он индексы использовать по какой-то загадочной причине не хочет, хотя должен бы судя по мануалу), а они отнюдь не подряд лежат. Если же таблица раздуется еще на 48мегабайт, то это "Не подряд лежат" увеличится еще в разы.
Но попробовать пожалуй стоит, спасибо.

miksoftsgalaв категории может быть до 300к товаровИ их все кто-то будет постранично листать по 50 позиций???Дело в том, что даже лимит 0,50 ситуацию не улучшает. Т.е. разница между лимит 0,50 и лимит 40000,50 около 10% по скорости. Если бы затык был именно в последних страницах я бы что-то подумал на эту тему.

miksoftsgala еще вопрос . напомните пожалуйста. есть что-то в mysql для углубленного анализа выполнения запроса?
я имею ввиду что бы понять влезла ли КОНКРЕТНАЯ выборка во временную таблицу в памяти или свалилась на диск и так далее? т.е. полная последовательность того чего мускул делает с запросом и как? не просто эксплеин.Ну есть еще профилирование, но там тоже не очень-то подробная информация.
http://dev.mysql.com/doc/refman/5.7/en/show-profile.html
http://www.sql.ru/forum/698050/faq-mysql-profiler-knutom-ili-pryanikom

И есть Performance Schema
(не пользовался, подробностей не знаю).Ага, попробую, спасибо.

miksoftsgalaпропущено...
16мб и то и другое.Я бы предложил попробовать их резко увеличить, например, до 1 ГБ. Если, конечно, оперативка позволяет. Но тут такой масштаб, что оперативку экономить не приходится.Не помогло.

miksoftsgalaбоюсь что таблица будет читаться еще дольшеТут надо наращивать innodb_buffer_pool_size (если таблицы InnoDB), чтобы и таблицы, и все индексы полностью в нем размещались.
Если таблицы в MyISAM, то наращивать надо key_buffer_size, но он помещает в себя только индексы.
Тогда данные будут в оперативной памяти и физический ввод-вывод не понадобится.myisam, да
key buffer size 1024М

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


отрабатывает те самые блин самые толстые 0.22 секунды. При чем в эксплеине файл-сорт опять.
Не поверив своим глазам пошел проверять есть ли индекс - есть курилка.
Да как так-то? Я какую-то базовую вещь забыл?
Почему индекс при сортировке не используется даже тупо напрямую?

miksoftsgalaПри чем в эксплеине файл-сорт опять.А покажите целиком.

ANALYZE TABLE точно делали?я кажется нашел причину в мане, что логично, но что делать с этим в большой перспективе пока неясно, вот тут мне нужна помощь


будет за 0.22 с файлсортом, т.к. индекс для ордеринга включается только если (фанфары) выбираются записи из первой трети.
а поскольку там 60к записей с лишним, то 40к заплевывает в оставшиеся 70% и пролетайло получается
для


индекс работает и срабатывает за 0.05 вместо 0.22
и при


индекс тоже работает и тоже 0.05 вместо 0.22 получается

НО!
я уже перепробовал с десяток разных вариантов, но как это вкрячить в запрос


категорически не понимаю:( что бы именно для ордера и все такое.
Есть какие-то идеи?


тем временем замечу любопытную вещь которую выяснил из профайлинга. если взять первое сообщение, то походу дела мускул "вариант 1.1" и "вариант 2" приводит в результате оптимизации к "вариант 1" и его уже исполняет (эксплеины, профайлинги - все совпадает с этой мыслью).

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

индекс не включается

miksoftsgalaбудет за 0.22 с файлсортом, т.к. индекс для ордеринга включается только если (фанфары) выбираются записи из первой трети.
а поскольку там 60к записей с лишним, то 40к заплевывает в оставшиеся 70% и пролетайло получается
дляа можно ссылочку на такое?В старом мануале (по 5.4.3 это было прямо в разделе про индексы, но я уже потерял ту ссылку) :)
В свежем мануале http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html говорится что строгого правила про 30% уже нет,
At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.
но по факту у меня на практике получилось как раз 30% граница

miksoftА когда выбирается 2/3 записей логично, что индекс не поможет.Побуду занудой. Как раз помог. С индексом (форсе индекс) выбирается за 0.05, без индекса за 0.22.
Вот теперь мне бы этот индекс к "большому" запросу прикрутить как-то.

miksoftsgalaя уже перепробовал с десяток разных вариантов, но как это вкрячить в запрос



категорически не понимаю:( что бы именно для ордера и все такое.
Есть какие-то идеи? Тут получается, что отбор идет по одной таблице, а сортировка по другой. Имхо, хорошего варианта не получится. Поэтому и приходится придумывать обходные варианты. Например, все тот же перенос поля для сортировки в таблицу categories_rel.
mysql-ный мануал говорит что должно работать если колонка "ордер бай" из первой константной таблицы, а у меня запрос именно такой. Т.е. первая таблица как раз с ордербаем и константная дальше ехать некуда.
http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
The query joins many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.

miksoftsgalaТ.е. первая таблица как раз с ордербаем и константная дальше ехать некуда.Константная-то как раз вторая, судя по where b .catid=982734да, тупанул. спасибо.
Буду думать что с этим делать.

[quot MasterZiv]sgala.
ты не умеешь писать запросы.
left join categories_rel b on a.goodid=b.goodid where b.catid=982734
left join тут не нужен нигде, ты фильтруешь по этой таблице b.То что я делаю как-то неправильно это я понимаю, иначе не были бы такие результаты.
Не могли бы вы подсказать как в моей ситуации сделать правильно?
Если думаете предложить вариант с вложенным запросом - обратите внимание на то, что один из таких вариантов уже был приведен, обсужден и отвергнут.

miksoft , попробовал сделать таблицу чисто для сортировки "goodid, categoryid, orderid". В принципе стало получше, правда не знаю к чему это приведет когда добавлю еще полей.
Что бы стало совсем хорошо попробовал сделать из нее memory table и вот тут был удивлен (обе таблицы myisam, индексы btree, smallint, mediumint, mediumint).
Во-первых размеры. memory таблица с нужными ключами улетает за 1500мб, в то время как myisam занимает культурные 244мб.
Мало того что "размер строки" (без индексов) в memory 21 байт против 9 байт в myisam (откуда 21-то? на 3 поля по 4 байта = 12 должно быть вроде). Так еще и ключи занимают почти в 4 раза больше места.

Во-вторых разница в эксплеинах. Таблицы полностью одинаковые, на всякий случай сделал optimize, analyze и даже ребутнул сервер.
Запрос SELECT * FROM `goodsORD` WHERE catid=85798 order by orderid limit 40000,50
Но memory эксплеин выглядит как
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE goodsORD ref orderid orderid 2 const 733 Using where
А myisam-овский
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE goodsORD ref orderid orderid 2 const 64139 Using where
Т.е. вот тут я вообще не понял. Какого черта (при чем эксплеины запускал несколько раз, ситуация аналогичная, хотя цифры могут плавать) в myisam-овской таблице при всех прочих равных - ему нужно перебрать в 100 раз больше строк ?! Т.е. по сути все, а memory довольствуется 733 ?

MasterZivsgala,
я же написал, тебе не нужен left join.так что нужно-то?

tanglirsgala,

[inner] join

ну и в самомделе, зачем вам лимит 40000?с inner join та же самая ерунда. т.е. даже эксплеин один в один.

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

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

но запросы вида goodid in (50 значений) работают ОЧЕНЬ долго запускаясь первый раз, хотя казалось бы куда проще-то запрос.
вплоть до 5 секунд, хотя со второго раза стабильно укладываются в 0.05 секунды, а то и 0.005 секунды даже. если чутка подождать, то время увеличивается до 0.5 секунды.
при чем эксплеин показывает index: range. никакого файлсорта и прочей ерунды
отчего такая ерунда?

key_buffer_size выставил в 1024м - казалось бы индексы всяко должны быть в памяти (всего их 700мб) но не фига не помогает.

miksoftsgalakey_buffer_size выставил в 1024м - казалось бы индексы всяко должны быть в памяти (всего их 700мб) но не фига не помогает.Откуда им быть в памяти, если их до этого никто не читал?
Сам по себе кэш индексов не заполняется, а только по мере использования этих индексов.
э.
вы хотите сказать, что файл индексов (при достаточности памяти) попадет в память ПОЛНОСТЬЮ тогда и ТОЛЬКО тогда когда все индексы хоть раз прочитаны?
я почему-то считал что индексный файл грузится в память если не при старте мускула, то как минимум после первого обращения.

если я правильно понял что вы хотели сказать, то есть ли какой-то вариант "прогрева" файла индексов?
что бы он таки оказался полностью в памяти и уже не мучал мозг?