vs


Вопросы:
1) Если два эти запроса выполняются не для получения данных, а для подсчета выбранных строк, то что именно мы теряем, если средний размер ряда 20 Кб, а количество выборки 12 тыс. строк?
2) Памяти больше сжирается? Корректно ли утверждать, что первый запрос требует на 240 Мб больше, чем второй? Чую, что, скорее всего нет, но утверждать что-либо у меня нет оснований.
3) Есть ли у первого запроса из-за звездочки потеря в производительности и если да, то о каких % потерь в скорости идет речь: 5%, 20%, 50%, в 2, 5, 10 раз медленнее?
4) Какие вообще сжираются ресурсы сервером при выполнении select'а до получения самих данных?

vkle1) Если два эти запроса выполняются не для получения данных, а для подсчета выбранных строк, то что именно мы теряем

Вероятно, мозг. Потому как для подсчёта количества строк рекомендуют использовать функцию count(), специально для этого предназначенную. СУБД по барабану, как будут использованы отданные им записи.

Эта тема не про решение какой-то конкретной задачи из ежедневной практики на получение количества записей.
В практике все задачи уже решены.
Этот пример искусственно создан для иллюстрации.
Вопрос стоит в понимании принципов работы со звездочкой * по сравнению с фиксированным списком полей.

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

Я понял, что сформулировал задачу неудачно.
Делаю второй подход к формулированию.

Пусть есть таблица.


и для учебных целей мы заполняем её 1 млн. записей, где в каждое поле tit пишем 32 Кб текста.
Затем выполняем два запроса на клиенте, но данные НЕ фетчим данные.



Насколько я понимаю, первый запрос грузит сервер существенно больше, чем второй.
Вот я и хочу узнать ПРИНЦИПЫ что там примерно происходит, чтобы руководствуясь этими принципами можно было бы принимать более мудрые решения, когда звездочка погоды не делает, а когда замена звездочки на id может привести к сильной экономии.
Например, у меня есть внутренне ощущение, что в данном примере объем записанного текста, хоть 1кб, хоть 32 кб, на запрос со звездочкой не влияют, но я не могу это объяснить.

Блин, вижу, что моя вторая попытка сформулировать задачу тоже провалилась... (((

MasterZivподумай лучше сам, что СУБД должна сделать, чтобы выполнить оба запроса.

Вопрос: Есть где-нибудь в интернете какая-нибудь статья или ещё лучше картинка (инфографика) где не профессорским, а более-менее простым языком перечисляются последовательности (примерный стратегический алгоритм) действий, которые происходят внутри сервера при выполнении обычного селекта? Или все, что там происходит настолько сложно, что больше похоже на русскую рулетку?

MasterZivа невозможно изолировать выполнение запроса от стадии передачи данных клиенту.
Вопрос: Неужели не существует никакой экономии от того, что клиент не стал фетчить данные с сервера, а только выполнив select * запрос, просто стал выполнять другие запросы? Неужели с точки зрения сервера не существует разницы, если select * выбрал 10 тыс. строк, но клиент сфетчил первые 50, а остальные 9950 фетчить не стал, а просто стал выполнять какие-то другие запросы? Я в этом смысле имел ввиду изоляцию стадии выборки и стадии транспорта...

вадяну а если запрос сделан и закрыт - то что не забрано с сервера, серверу пофиг, и он этот объём держит в кэше минимум времени...

вот уже ближе к тому, что я пытаюсь понять
пара вопросов

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

2) что именно складывается в кеш?




Допустим в первом селекте в кеш копируется вся таблица за исключением полей, которые хранятся по ссылкам типа varchar, тогда как char всегда хранится в таблице.

Допустим тогда во втором селекте в кеш копируется только таблица из id, то есть список интов.

Но неужели в третьем селекте в кеш копируется только список айдишников-интов? Если нет, то тогда список из интов и колонки а? А если так и у нас в каждой записи в колонке а хранится по 32 кб данных, то все эти 32 Кб кидаются в кеш? или может быть все таки в кеш кидается ссылка (то есть инт), а 32 кб достаются только в момент фетчинга записи??

вадяну вот я боюсь предлагать тебе ссылку, вдруг опять будешь ругаться....
http://habrahabr.ru/post/41166/

статью прочитал
по моей теме со звездочкой она ничего не проясняет, к сожалению...

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

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

неприятная новость в статье следующая: перед выполнением запроса сервер не знает о размере получившейся выборки... то есть не совсем понятно что происходит для запросов типа select * from t where id = 100500; Видимо как раз для таких случаев вы используете SQL_SMALL_RESULT типа как тут 18351614

итого:
1) из статьи я так и не понял в чем разница при работе с кешем для select * vs select id
2) не понял для select id from t where a like '%1%' && b like '%2%' в кеш достаются все поля таблицы, только id поля или id, a, b поля

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

netwindУ select * конечно же меньше преимуществ в разрезе кеширования, потому что, в общем случае, данных в нем больше и они памяти больше занимают.

Насколько корректно просто сложить размеры полей:
все инты, варчары и тексты по 8 байт
+ все чаты по 1 байту на длину чара
и умножить на количество записей в таблице?

пример




Вопрос: Если в таблице 8 тыс. записей, то сколько кеша запросов схавает запрос select * from t where id = 5;
Ответ: 8 + 6 + 8 + 8 + 1 * 8000 = 31 * 8000 = 248 Кб

Так верно или ошибочно?

автор2) не понял для select id from t where a like '%1%' && b like '%2%' в кеш достаются все поля таблицы, только id поля или id, a, b поля
Достается и хранится весь результат как он есть. То есть, поле id.[/quot]

Блин, опять запутался: в кеше хранятся данные, по которым производится выборка a like '' && b like '' или в кеше все-таки хранятся результаты выборки и если у нас where id = 5, в то кеше будет хранится вся таблица в которой искалось id = 5 или в кеше будет храниться только одна эта запись?

Ну и собственно мы опять вернулись в исходную точку:
когда поступает select * from t where id = 5, то сервер ведь не на винчестере ищет данные, а он сначала их с винчестера читает в память и уже в памяти их "перебирает" для выполнения запроса? и в вашей статье даже есть модификатор SQL_NO_CACHE, который принудительно заставляет эту переборку делать на диске, если изначально известно, что размер выборки огромен.

Так вот если производство выборки на диске задается директивой НО КЕШ, значит обычный ход обработки запроса - это КЕШ
или кеш в котором производится выборка данных и кеш запросов - это два разных типа кеша??
если да, то меня интересует не кеш, в котором хранятся уже выполненные результаты запросов, а кеш, в котором хранятся данные, из которых производится выборка
а ещё точнее меня интересует влияние на этот кеш звездочки в селекте

вадяinnodb_buffer_pool_size = 512M
первый обращение запроса загружает таблицу в эту память, повторный - уже ищет в ней

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

ну и как я уже приводил есть 3 варианта как мне видится:



если по первому селекту понятно, что грузится вся таблица, то по второму и третьему - вопрос: вся или во втором случае только список (id), а втретьем только список (id, a)??

Причина этого вопроса в том, что если и во втором и третьем случае в этот пул будет грузиться ВСЯ таблица, тогда select * вообще ни в чем не проигрывает селектам с перечисленными полями!!!

Alex_Ustinovну, так сказать, просто проверьте, что происходит на самом деле
создайте таблицу, как в вашем примере
до после запросов 2 и 3

смотрите
Show variables like %cache%;

оцените "дельту" Qcache_free_memory
почувствуйте разницу......

Ну и какой мне смысл чувствовать разницу?
Разница - это все рано что температуру мерить у пациента.
Ну понятно, что у одного температура 37 у другого 39 и что дальше?
То, что вы мне предлагаете - это не метод исследования, а игра в поле чудес.

netwindLumix, вас остается только исходники читать оправлять. Все ж прозрачно и понятно.

Нее, сорсы читать не буду.
Игра не стоит свеч.

netwindНу попробуйте вместо Хабра купить на торентах книжку High Performance MySQL

Только что по вашей рекомендации прочитал эту книжку
На торренты даже идти не пришлось, лежит в инете открытой ссылкой
http://moemesto.ru/kristy/file/9626805/OReIlly.High.Performance.MySQL.pdf

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

стр. 81. Избегайте NULL как только возможно
стр. 85. Char всегда обрезает пробелы с обоих концов строки
стр. 87. Text и Blob требуют от 1 до 4 байт для хранения каждой колонки
стр. 87. Никогда не сортируйте по полю с типом Text или Blob
стр. 124. Индексы используются как для поиска, так и для сортировки
стр. 153. Относитесь с подозрением ко всем select *
стр. 154. но select * имеет смысл, если смотреть с точки зрения кодинга по обслуживанию софта
стр. 157. мускул тянет 50К запросов / сек локально и 2К запросов / сек по гигабитной сетке

стр. 159. декомпозиция джоинов на отдельные селекты с клиента дает существенную экономию в хайлоаде

стр. 169. в мускуле in() умный, то есть в других базах in() является аналогом OR и поэтому алгоритмическая сложность линейная, то у мускульного in() за счет сортировки алогоритмическая сложность логарифмическая

стр. 178. Вот в этом параграфе Query Execution Engine должна была располагаться инфа, которую я ищу, но там лишь вода... (((

стр. 299. Для InnoDB опасно хранить данные, если ряд превышает 8 Кб, потому что будет создаваться дополнительная 16КБ пага и она все время будет подтягиваться в пул, как только двигателю понадобится эта запись

стр. 300. На чтение колонок влияет не select *, а where, то есть условия выборки и если запись понадобится, то в пул будут считываться все паги этой записи

стр. 300. Если в записи слишком много больших колонок, то лучше объединить их в одну, иначе на каждую колонку будет создаваться отдельная innodb пага по 16 кб

стр. 313. Если InnoDB потребуется запись, то она будет читать с диска в пул все 16 кб, вне зависимости от select *, причем даже если вес записи 6 интов, то есть 24 байта, все равно будет читать 16 кб

стр. 577. Buffer pool hit rate - соотношение - какой % данных достается из пула, а не из диска

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

но применительно к обсуждаемой теме, если пага записи уже в пуле, то нет никакой разницы select id или select * - все равно в паге хранится вся запись

*

просмотрел доку, тоже нет ответа

https://dev.mysql.com/doc/refman/5.5/en/estimating-performance.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html
https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

*

Таким образом из книжки понял следующее: единственный перегрев от select * может быть в хранении лишних данных в кеше запросов, тогда как кеш данных (пул) все равно хранит данными пагами по 16 кб и на его нагрузку влияет не select *, а сколько записей потребуется перебрать, чтобы выполнить запрос

сам же кеш запросов полезен только при наличии большого количества однотипных запросов

кеш данных (пул) убивают запросы, выполнение которых требует перебора сразу большого количества данных, то есть запросы без условий и лимитов, а select * тут вообще ни при чем


Alex_Ustinovвы постоянно уводите обсуждение в сторону
этак мне не так а это не эдак.

мой интерес в этой теме довольно узок - понять принципы как влияет список полей в селекте по сравнению с select *

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

я считаю, что измерение результатов запроса никоим образом не прольет свет на понимание принципов влияния списка полей в селекте по сравнению с select *.

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

Alex_UstinovПосмотрел на топики с вашим участием, вы оказывается спец в С/С++.
Исходники на офф.сайте лежат в свободном доступе.

Я не буду копаться в сорсах ради получения ответа на этот вопрос.

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

netwindТак что для innodb select id, где id есть primary key, всегда существенно выгоднее select * .

не пойму эту фразу, потому исходя из философии хранения innodb select * уже всегда содержит в себе primary key...

Исходя из книги, которую я прочитал по вашей рекомендации я понял, что вне зависимости от перечисления полей, если движку для обработки запроса понадобится 1000 каких-то записей, то он потянет в пул все страницы этих записей ВНЕ зависимости от select * это или select id

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

значит, если в условиях селектов производится выборка только по полям, которые не хранятся за пределами таблицы, то никакого ущерба select * по сравнению с select id не наносит и единственный минус, который в этом может быть - это нагрузка на кеш запросов, а минус этот состоит в том, что в нем поместится меньше запросов, то есть при бОльших объемах данных, будут чаще удаляться более ранние запросы

netwindУпускаете как именно происходит считывание страниц. Желательно таки дочитать книжку, чтобы понимать как это работает все вместе. Как минимум первых 5 глав.

ну вот собственно вот так и разводят всех новичков на форумах...
и меня тоже много лет так разводили в начале моего профессионального пути...

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

ведь это так приятно с умным сказать: ИДИ ЧИТАЙ!!
http://moemesto.ru/kristy/file/9626805/OReIlly.High.Performance.MySQL.pdf

netwindОчевидно, для запросов типа select id_primary ..., select key_value ... where key_value= достаточно считать только некоторые страницы содержащие этот индекс, а primary key или значения столбцов из индекса там уже будут в этих же страницах.
А в случае с select * нужно считать по ссылке дополнительные страницы содержащие остальные поля записи.

а ведь никто из "советчиков" так и не смог сказать, что

select * from t where a like 'some'
проигрывает
select id from t where a like 'some'
в случае, если по полю a объявлен ключ (задан индекс),
но становится таким же неэффективным как
select * , если в запросе появляется поле, которого нет в индексе,
например
select id, b from t where a like 'some'
или select id from t where a like 'some' && b like 'some'