Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 93
Могу добавить что от использования GROUP BY в mysql можно и нужно отказываться.
Так же надо строить систему так, чтобы БД надо было просматривать ровно столько рядов, сколько она будет возвращать при запросе. Это можно сделать всегда, кроме поиска по LIKE с начальным %.
Ну и так же заметил несколько неточностей, например фраза «Индексы лежат в памяти, в данные на диск мы не лезем» — неправда, ибо индексы также лежат на диске. То есть я понимаю, что хотел сказать автор. Он прав, но лучше не смущать новичков неточностями :)
Так же надо строить систему так, чтобы БД надо было просматривать ровно столько рядов, сколько она будет возвращать при запросе. Это можно сделать всегда, кроме поиска по LIKE с начальным %.
Ну и так же заметил несколько неточностей, например фраза «Индексы лежат в памяти, в данные на диск мы не лезем» — неправда, ибо индексы также лежат на диске. То есть я понимаю, что хотел сказать автор. Он прав, но лучше не смущать новичков неточностями :)
По поводу запроса LIKE с начальным %. Есть случаи, когда мы, например, записывает в БД почтовые ящики клиентов. И тогда вместо запроса
WHERE mail LIKE '%@gmail.com'
чтобы достать всех пользователей с указанным доменным именем.
Проще вставлять записи в реверс-порядке и делать индекс по этому полю по первым, скажем, 8 байтам.
Соответственно доменные имена будут вначале, а запрос примет вид
WHERE mail LIKE 'moc.liamg@%'
WHERE mail LIKE '%@gmail.com'
чтобы достать всех пользователей с указанным доменным именем.
Проще вставлять записи в реверс-порядке и делать индекс по этому полю по первым, скажем, 8 байтам.
Соответственно доменные имена будут вначале, а запрос примет вид
WHERE mail LIKE 'moc.liamg@%'
Проще разбивать ящик на два поля — домен и юзернейм, и индексировать по домену.
Еще шустрее будет работать данный запрос, если сделать отдельную таблицу для доменов, а в таблице пользователей E-mail хранить в виде двух полей mail_username и mail_domain_id.
Тогда вместо WHERE mail LIKE '%@gmail.com', WHERE mail LIKE 'moc.liamg@%' или WHERE mail_domain = 'gmail.com' будет очень быстрый поиск по целочисленному индексируемому полю, типа WHERE mail_domain_id = 23 (конечно, придется получить этот mail_domain_id из таблицы доменов, но это будет разовый очень быстрый поиск по уникальному ключу-доменному имени).
Конечно, строить такую сложную структуру имеет смысл только в том случае, если пользователей — миллионы. Но если это так, то данная схема оптимальна IMHO.
Тогда вместо WHERE mail LIKE '%@gmail.com', WHERE mail LIKE 'moc.liamg@%' или WHERE mail_domain = 'gmail.com' будет очень быстрый поиск по целочисленному индексируемому полю, типа WHERE mail_domain_id = 23 (конечно, придется получить этот mail_domain_id из таблицы доменов, но это будет разовый очень быстрый поиск по уникальному ключу-доменному имени).
Конечно, строить такую сложную структуру имеет смысл только в том случае, если пользователей — миллионы. Но если это так, то данная схема оптимальна IMHO.
Я на самом деле говорил про то, что это иногда нельзя сделать. В некоторых конкретных ситуациях действительно можно сделать хорошо.
Кстати в данном случае можно не делать отдельного поля и его индексировать, а просто создать индекс по тому же полю, только DESC.
Кстати в данном случае можно не делать отдельного поля и его индексировать, а просто создать индекс по тому же полю, только DESC.
А еще быстрее и универсальнее хранить все мыла в отдельных строчках в текстовом виде в памяти, затем искать там вхождение подстроки функцией наподобие strpos(), а затем найденную строку подставлять в запрос where mail='asd@gmail.com' )
strpos работает напорядок быстрее чем like '%qwe%'
strpos работает напорядок быстрее чем like '%qwe%'
Это где вы такое взяли, прочли?
Если по полям считать — забудьте про индексы!
Если по полям считать — забудьте про индексы!
да не, считать по полям не надо. как то у меня была такая задача сделать быстрый SELECT WHERE row like '%str%', решил так — сделал раздел на memoryfs, столбец row выкинул в файл примерно такой:
— qqwwee
aassddffggh
hsfgfgfgfg
…
drtgfdgdfg
— теперь like '%ddf%' можно сделать так:
while not eof
{
readln();
if (strpos($line, $ddf)) then $result[]=$line;
}
затем заменить like на WHERE row='$result[1]' OR row='$result[2]'. Тут понятно что на row висит normal индекс.
— qqwwee
aassddffggh
hsfgfgfgfg
…
drtgfdgdfg
— теперь like '%ddf%' можно сделать так:
while not eof
{
readln();
if (strpos($line, $ddf)) then $result[]=$line;
}
затем заменить like на WHERE row='$result[1]' OR row='$result[2]'. Тут понятно что на row висит normal индекс.
Вы совершенно правы и насчет group by тоже.
Спокойно можно сделать архитектуру не используя group by.
А фраза трактовалась скорее всего так: если первый запрос воспользовался индексами n..., то они теперь в кеше :)
Кстати действительно, если идут много запросов друг за другом, лучше всего оптимизировать так, чтобы использовались одни и те же индексы.
P.S. Не по теме…
Да, реально вас в минус запустили, хоть в комментах одни плюсы…
Тролли вам войну обьявили…
Спокойно можно сделать архитектуру не используя group by.
А фраза трактовалась скорее всего так: если первый запрос воспользовался индексами n..., то они теперь в кеше :)
Кстати действительно, если идут много запросов друг за другом, лучше всего оптимизировать так, чтобы использовались одни и те же индексы.
P.S. Не по теме…
Да, реально вас в минус запустили, хоть в комментах одни плюсы…
Тролли вам войну обьявили…
Спасибо, познавательно ;)
Надеюсь вы продолжите цикл статей этой тематики. Ждем продолжений.
Отличная статья! Респект и уважуха.
Проверил тему с SQL_CALC_FOUND_ROWS 2х милионной таблице.
Разница между одним и 2мя запросами действительно есть, но не очень большая.
про покрывающие индексы — оч круто. Не знал.
Сортировка по null — тоже красиво.
Про юнионы — как-то не очень.
Мысль про избыточные таблица интересна.
В общем ещё раз спасибо :)
Проверил тему с SQL_CALC_FOUND_ROWS 2х милионной таблице.
Разница между одним и 2мя запросами действительно есть, но не очень большая.
про покрывающие индексы — оч круто. Не знал.
Сортировка по null — тоже красиво.
Про юнионы — как-то не очень.
Мысль про избыточные таблица интересна.
В общем ещё раз спасибо :)
У нас сделано примерно также: есть таблицы с чистыми данными, периодически вызываются хранимки, которые их агрегируют по разному, считают, и уже подсчитанные репорты кладутся в таблицы daily_stats_by_* и другие.
Т.к. в основном все упирается в различные условия для GROUP BY есть идея такого варианта с денормализацией. Например, есть в таблице с сырыми данными поле date. Добавляем еще несколько полей, которые будут содержать значения от различных функций для группировки: hour, week, month и т.д. Создаем для каждого из таких полей индексы, можно покрывающие. И после этого, мы можем делать не GROUP BY MONTH(date), а GROUP BY month.
Но я еще не тестировал, может и фигня получится.
PS: Кстати, судя по документации, в PostgreSQL можно создавать индексы не только на поля, но и на значения выражений. Здесь можно и без доп. полей обойтись, просто сделав соответствующие индексы.
Т.к. в основном все упирается в различные условия для GROUP BY есть идея такого варианта с денормализацией. Например, есть в таблице с сырыми данными поле date. Добавляем еще несколько полей, которые будут содержать значения от различных функций для группировки: hour, week, month и т.д. Создаем для каждого из таких полей индексы, можно покрывающие. И после этого, мы можем делать не GROUP BY MONTH(date), а GROUP BY month.
Но я еще не тестировал, может и фигня получится.
PS: Кстати, судя по документации, в PostgreSQL можно создавать индексы не только на поля, но и на значения выражений. Здесь можно и без доп. полей обойтись, просто сделав соответствующие индексы.
Сейчас быстро нахожу и исправляю грамматические и пунктуационные ошибки у себя в статье. Просто написал, и самому стыдно :-( Спасибо, людям, которые мне в этом периодически помогают в скайпе и аське :-)
любопытно, сейчас пишу большой проект.
новички часто попадаются скорее на том, что часто повторяют запросы, иногда даже (омг!) в цикле.
новички часто попадаются скорее на том, что часто повторяют запросы, иногда даже (омг!) в цикле.
хорошее начало — имху стоит продолжать столь интересную и нужную тему.
отличная статья, спасибо. Последнюю часть правда я так не понял… получается 5 таблиц в которых надо добавлять данные и прибивать старые данные каждый час + избыточность… не проще было мемкешить данные по более простым выборкам?
нам надо найти сколкьо было заходов на каждую статью автора как я понимаю, а не сразу по всем.
select article_id from articleы where author_id = 1 (эту часть можно держать в мемкеше уже)
foreach (......) {
select count(*) from article_hits where article_id =… (а в таблице индекс по полю article_id)
}
по идее это должно работть очень быстро как только индекс попадет в кеш.
ась?
нам надо найти сколкьо было заходов на каждую статью автора как я понимаю, а не сразу по всем.
select article_id from articleы where author_id = 1 (эту часть можно держать в мемкеше уже)
foreach (......) {
select count(*) from article_hits where article_id =… (а в таблице индекс по полю article_id)
}
по идее это должно работть очень быстро как только индекс попадет в кеш.
ась?
то что в foreach соотв тоже можно засунуть в мемкеш… хотя получается избыточное дергание базы конечно…
Да, первая часть, как Вы написали действительно кешируется в мемкеш
select article_id from articleы where author_id = 1 (эту часть можно держать в мемкеше уже)
Но статистику можно смотреть не только по каждому автору но и по всем новостям вцелом, т.е. каждый автор может смотреть как общую статистику, так и свою лично.
select article_id from articleы where author_id = 1 (эту часть можно держать в мемкеше уже)
Но статистику можно смотреть не только по каждому автору но и по всем новостям вцелом, т.е. каждый автор может смотреть как общую статистику, так и свою лично.
Может стоит вам как специалисту написать пару статей «best practice» MySQL, как нужно делать правильно и не делать неправильно )
Честно-говоря, я не считаю большим специалистом, и считаю, что есть много людей, которые разбираются лучше меня — Питер Зайцев, например :-)
Ну судя по статье у вас есть некоторый опыт, чтобы советовать как нужно делать то или иное для увеличения производительности.
А почему он не Пётр? :)
Есть ли кто-то, разбирающийся в теме лучше чем вы — это другой вопрос.
Я же вижу, что вы разбираетесь гораздо лучше, чем я :) Поэтому ваш опыт мне интересен…
Я же вижу, что вы разбираетесь гораздо лучше, чем я :) Поэтому ваш опыт мне интересен…
Вот такие статьи и должны быть на хабре, вместо кучи новостей обнообразных и часто фанатичных. Плюсанул везде где смог =)
Вы уверены, насчет crc32?
запрос select * from `tags`group by crc32(tag_text) order by null;
не будет использовать индекс по tag_text (правда я не понял что вы имели ввиду — что в этом поле — text или например varchar(1000)?)
В любом случае правильнее сделать еще одну колонку с crc32, заполнять ее при вставке и группировать по ней, а не выполнять это при SELECT. (разумеется если приоритет у выборок, а не вставок)
запрос select * from `tags`group by crc32(tag_text) order by null;
не будет использовать индекс по tag_text (правда я не понял что вы имели ввиду — что в этом поле — text или например varchar(1000)?)
В любом случае правильнее сделать еще одну колонку с crc32, заполнять ее при вставке и группировать по ней, а не выполнять это при SELECT. (разумеется если приоритет у выборок, а не вставок)
>В любом случае правильнее сделать еще одну колонку с crc32, заполнять ее при вставке и группировать по >ней, а не выполнять это при SELECT. (разумеется если приоритет у выборок, а не вставок)
Да, иногда это удобно, особенно вместо того чтобы создавать составной индекс по нескольким полям (который будет достаточно большой), можно создать по одному полю, например используя ф-ию MD5 (128 бит, запись состоит из 32 символов) или если мы хотим подстраховаться больше то лучше использовать Sha1 (160 бит, 40 символов) нежели CRC32 (32 бита, беззнаковое число)
И соответственно запросы примут вид
так просто большая вероятность того, что значения CRC32 совпадут, особенно если в таблице много записей.
Ну и соответственно индекс поставить не на все поле, а на какую-то его часть.
>правда я не понял что вы имели ввиду — что в этом поле — text или например varchar(1000)?)
например URI (varchar(100))
>Вы уверены, насчет crc32?
Да :-)
Да, иногда это удобно, особенно вместо того чтобы создавать составной индекс по нескольким полям (который будет достаточно большой), можно создать по одному полю, например используя ф-ию MD5 (128 бит, запись состоит из 32 символов) или если мы хотим подстраховаться больше то лучше использовать Sha1 (160 бит, 40 символов) нежели CRC32 (32 бита, беззнаковое число)
И соответственно запросы примут вид
SELECT
*
FROM
tbl_name
WHERE
hash_col=MD5(CONCAT(col1, col2))
AND col1='constant'
AND col2='constant'; * This source code was highlighted with Source Code Highlighter.
так просто большая вероятность того, что значения CRC32 совпадут, особенно если в таблице много записей.
Ну и соответственно индекс поставить не на все поле, а на какую-то его часть.
>правда я не понял что вы имели ввиду — что в этом поле — text или например varchar(1000)?)
например URI (varchar(100))
>Вы уверены, насчет crc32?
Да :-)
Проверю, так как теоретически должно быть наоборот :)
Вы на каких объемах/типах данных сей эффект наблюдали в реальности? и на каком железе (CPU, Memory)
у меня есть пару баз по 3-4 гига text-полей, там такого не наблюдалось, но может чего не учел…
Вы на каких объемах/типах данных сей эффект наблюдали в реальности? и на каком железе (CPU, Memory)
у меня есть пару баз по 3-4 гига text-полей, там такого не наблюдалось, но может чего не учел…
Наблюдал его на объемах данных порядка 2 млн. записей. Тип — варчар(255). Выделенный сервер БД, 16 ГБ оперативной памяти. Про процессор врать не буду — не помню.
Это решение у нас тогда не прижилось т.к. в некоторых значения был замечен очень высокий уровень коллизий. После исследования было обнаружено, что GROUP BY берет 2^32 как максимальное число для группировки. Не помню какая тогда стояла версия мускула на продакшене, т.к. было это пол года назад. 5.0*. Но потом оказалось, что если приводить тип к BINARY, то и это можно обойти
как-то так
mysql> SELECT tag, COUNT(*) AS count, crc32(tag), BINARY crc32(tag)
-> FROM tags
-> GROUP BY BINARY crc32(tag)
-> ORDER BY count DESC
-> LIMIT 10
->;
+————+——-+————+——————-+
| tag | count | crc32(tag) | BINARY crc32(tag) |
+————+——-+————+——————-+
| spanish | 4576 | 874050868 | 874050868 |
| vocab | 4103 | 1178479308 | 1178479308 |
| vocabulary | 2786 | 2147483647 | 2425997691 |
| french | 2247 | 2147483647 | 2943733342 |
| english | 2087 | 746783232 | 746783232 |
| science | 1957 | 1729573288 | 1729573288 |
| latin | 1411 | 1421320458 | 1421320458 |
| chapter | 1274 | 2147483647 | 4186027310 |
| history | 1171 | 666529867 | 666529867 |
| words | 939 | 1904025228 | 1904025228 |
+————+——-+————+——————-+
но было уже поздно, и мы воспользовались другим решением.
кстати, заодно и ссылку нашел, которая косвенно подтверждает мои слова.
www.mysqlperformanceblog.com/2008/03/07/speeding-up-group-by-if-you-want-aproximate-results/#comment-272067
Это решение у нас тогда не прижилось т.к. в некоторых значения был замечен очень высокий уровень коллизий. После исследования было обнаружено, что GROUP BY берет 2^32 как максимальное число для группировки. Не помню какая тогда стояла версия мускула на продакшене, т.к. было это пол года назад. 5.0*. Но потом оказалось, что если приводить тип к BINARY, то и это можно обойти
как-то так
mysql> SELECT tag, COUNT(*) AS count, crc32(tag), BINARY crc32(tag)
-> FROM tags
-> GROUP BY BINARY crc32(tag)
-> ORDER BY count DESC
-> LIMIT 10
->;
+————+——-+————+——————-+
| tag | count | crc32(tag) | BINARY crc32(tag) |
+————+——-+————+——————-+
| spanish | 4576 | 874050868 | 874050868 |
| vocab | 4103 | 1178479308 | 1178479308 |
| vocabulary | 2786 | 2147483647 | 2425997691 |
| french | 2247 | 2147483647 | 2943733342 |
| english | 2087 | 746783232 | 746783232 |
| science | 1957 | 1729573288 | 1729573288 |
| latin | 1411 | 1421320458 | 1421320458 |
| chapter | 1274 | 2147483647 | 4186027310 |
| history | 1171 | 666529867 | 666529867 |
| words | 939 | 1904025228 | 1904025228 |
+————+——-+————+——————-+
но было уже поздно, и мы воспользовались другим решением.
кстати, заодно и ссылку нашел, которая косвенно подтверждает мои слова.
www.mysqlperformanceblog.com/2008/03/07/speeding-up-group-by-if-you-want-aproximate-results/#comment-272067
а разве varchar(1000) допустимое значение? я всю жизнь думал, что только varchar(255)…
а кто нить пробовал компилировать mysql с использованием pgcc? Действительно ли производительность повышается?
хотите поэксперементировать — попробуйте Bazaar
dev.mysql.com/tech-resources/articles/getting-started-with-bazaar-for-mysql.html
А потом не забудьте написать об этом статью :-)
Но зачастую все такие эксперименты остаются экспериментами и на продакшн сервер их не поставишь :-(
dev.mysql.com/tech-resources/articles/getting-started-with-bazaar-for-mysql.html
А потом не забудьте написать об этом статью :-)
Но зачастую все такие эксперименты остаются экспериментами и на продакшн сервер их не поставишь :-(
Интересно а как быть с ORM системами?
Они точно не в состоянии так тщательно и специфично оптимизировать запросы. С другой стороны я не представляю как работать со сложной системой без ORM, а только запросами… Неужели для систем с такими нагрузками обязательно приходится изобретать свои «велосипеды», отказываясь к примеру от Hibernate?
Автор, Вы используете ORM? И что вы о них думаете?
Они точно не в состоянии так тщательно и специфично оптимизировать запросы. С другой стороны я не представляю как работать со сложной системой без ORM, а только запросами… Неужели для систем с такими нагрузками обязательно приходится изобретать свои «велосипеды», отказываясь к примеру от Hibernate?
Автор, Вы используете ORM? И что вы о них думаете?
в active record можно реализовать кое что из описанного в статье. на счет всего не уверен, в частности насчет union. Но вот order by null точно =)
Использование Hibernate не исключает возможности использовать «tips and tricks» описанные в данной статье.
Просто когда пишешь на HQL надо хорошо понимать в какой SQL это превратится.
Соответственно, и архитектуру БД и маппинг тоже надо делать с пониманием.
Просто когда пишешь на HQL надо хорошо понимать в какой SQL это превратится.
Соответственно, и архитектуру БД и маппинг тоже надо делать с пониманием.
Наконец вернулся домой. И могу Вам ответить. На самом деле я работаю PHP программистом, а не Java. И использую другие ORM нежели Hibernate. Я использовал Propel, Doctrine и т.д.
Не в этом дело. Просто не всегда следует использовать продукт как есть as is. Yahoo, например, использует symfony PHP framework, который юзает ORM Propel (кстати, мне нравится как Symfony так и Propel :-)) для своей answers.yahoo.com/ со 135 миллионами пользователей habrahabr.ru/blogs/symfony/25040/. Но они переписывали какие-то части под себя, оптимизировали. На самом деле ОРМ нужен не во всех проэктах, например, в небольших точно не нужен, лишние трудности. А большой проект, который будет писаться и поддерживаться несколько лет разными программистами, возможно в разных странах… Тут гораздо удобней поддерживать код, написанный с помошью ОРМ.
Не в этом дело. Просто не всегда следует использовать продукт как есть as is. Yahoo, например, использует symfony PHP framework, который юзает ORM Propel (кстати, мне нравится как Symfony так и Propel :-)) для своей answers.yahoo.com/ со 135 миллионами пользователей habrahabr.ru/blogs/symfony/25040/. Но они переписывали какие-то части под себя, оптимизировали. На самом деле ОРМ нужен не во всех проэктах, например, в небольших точно не нужен, лишние трудности. А большой проект, который будет писаться и поддерживаться несколько лет разными программистами, возможно в разных странах… Тут гораздо удобней поддерживать код, написанный с помошью ОРМ.
а может стоит сделать поля час, день, месяц, год?
тогда выборка будет происходить быстрее чем с полями времени.
тогда выборка будет происходить быстрее чем с полями времени.
Вопрос с постраничной навигацией с учётом современной не любви к лишним GET-запросам можно решить так:
Где X — количество результатов на странице, Y — номер текущей страницы. Так мы обойдёмся без предзапросов, GET-запросов и прочих усложнений. Если не трудно, добавьте в текст. Заранее спасибо.
SELECT * FROM `table` WHERE id>X*Y-1 LIMIT by X;
Где X — количество результатов на странице, Y — номер текущей страницы. Так мы обойдёмся без предзапросов, GET-запросов и прочих усложнений. Если не трудно, добавьте в текст. Заранее спасибо.
Да, добавил, конечно не сложно, спасибо
наверно, все-таки имелось в виду
PS плюс такая штука не правильно будет работать, если записи в таблице могут удаляться.
WHERE id>X*(Y-1)PS плюс такая штука не правильно будет работать, если записи в таблице могут удаляться.
Такой финт не будет работать, если есть дополнительные фильтры на таблицу, тогда id не будет порядковым номером. А дополнительные условия-фильтры на таблицу есть в большинстве случаев.
Извините, все кому не получается сразу ответить. Я когда домой прийду обязательно напишу свое мнение по поводу ORM и больших проектов. По поводу Ваших предложений относительно возможных решений, поставленной задачи.
Просто последний рабочий день перед отпуском, а работы выше крыши :-(
И надо все успеть именно сегодня…
Просто последний рабочий день перед отпуском, а работы выше крыши :-(
И надо все успеть именно сегодня…
Категорически полезно. Страницу в закладки
Самое интересное было бы послушать специалистов по oracle или PostgreSQL с их примерами решения этой же проблемы.
Не проверял на практике, но мне кажется что предложенное решение для постраничного вывода
SELECT *
FROM `table`
WHERE id>X*Y-1
LIMIT by X;
уместно лишь при отсутствии пропусков в значениях поля id, что редко имеет место быть на практике
и условие вроде бы должно быть id > X*(Y-1) и указать что тип у id должен быть unsigned
SELECT *
FROM `table`
WHERE id>X*Y-1
LIMIT by X;
уместно лишь при отсутствии пропусков в значениях поля id, что редко имеет место быть на практике
и условие вроде бы должно быть id > X*(Y-1) и указать что тип у id должен быть unsigned
а я бы еще про ms sql послушал :)
Отличный пост, спасибо!
Автор поправьте!
«причем возможность шардинга поддерживается в версии 5.1»
это не совсем правда. Partitioning да, это так сказать вырожденный случай шардинга. С помощью первого можно раскидать по физическим дискам в пределах одной машины, а шардинг обычно предполагает, что данные попилены по разным машинкам. Не зря ведь придумал именно этот термин!
«причем возможность шардинга поддерживается в версии 5.1»
это не совсем правда. Partitioning да, это так сказать вырожденный случай шардинга. С помощью первого можно раскидать по физическим дискам в пределах одной машины, а шардинг обычно предполагает, что данные попилены по разным машинкам. Не зря ведь придумал именно этот термин!
Если приходилось самому реализовывать «group by», то утверждение, что добавление «order by null» к запросу с group by для увеличение скорости выглядит странным, т.к. быстрей групировать по построенному индексу, читай — отсортированному списку. На всякий случай спросил друга, который пишет ядро MySQL и он прокомментировал:
— Ха! Он же всегда сортирует! А константное выражение в order вообще игнорируется. Единственное может есть баг, но это поправимо ;-) Но то, что будет работать быстре одно значно — нет.
— Ха! Он же всегда сортирует! А константное выражение в order вообще игнорируется. Единственное может есть баг, но это поправимо ;-) Но то, что будет работать быстре одно значно — нет.
Да, в мускуле порой творят странные вещи, порой даже такие, о которых не знают и его «авторы». Но я показал вам то, что я вижу когда делаю EXPLAIN запроса. И этот эксплейн показывает мне, что с добавлением «order by null» — Using filesort ушел. Честно говоря в этом случае я больше верю своим глазам. Когда прийду домой поищу в интернете есть ли ссылки в Интернете на что-то подобное.
Есть записьв мануале
— … By default, MySQL sorts all GROUP BY col1, col2,… queries as if you specified ORDER BY col1, col2,… in the query as well. If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL…
dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
—
— … By default, MySQL sorts all GROUP BY col1, col2,… queries as if you specified ORDER BY col1, col2,… in the query as well. If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL…
dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
—
Благодарю Вас, Вы подтвердили мои слова, так сказать, документально, плюс Вас за это :-)
М-да, за такие хаки
Спасибо за ссыку. М-да, за такие хаки и умолчания разработчикам по рукам нужно дать! Почему нужно делать то, что пользователь не заказывал?! Завтра же пожалуюсь ;-)
Вообще, это довольно распространенная проблема а-ля Microsoft: Мы знаем лучше, что Вам нужно. Когда перед началом использования приложения приходится сначала все выключить, что не будешь использовать, вместо того, чтобы включить/добавить, если тебе когда-нибудь понадобится.
Вообще, это довольно распространенная проблема а-ля Microsoft: Мы знаем лучше, что Вам нужно. Когда перед началом использования приложения приходится сначала все выключить, что не будешь использовать, вместо того, чтобы включить/добавить, если тебе когда-нибудь понадобится.
Пожалуйста.
А сейчас похоже это модно, так строить логику приложений. Недавно приобрёл ноут, так там категорически не очевидно как отключить макафи. При этом програмка упорно лезет в интернет как только обнаруживает соединение, начинает что-то выкачивать и вообще не предлагает никаких вариантов натему отмены её действий, только убийство процесса помогает.
«Жираф большой — ему видней.» (с) =)
А сейчас похоже это модно, так строить логику приложений. Недавно приобрёл ноут, так там категорически не очевидно как отключить макафи. При этом програмка упорно лезет в интернет как только обнаруживает соединение, начинает что-то выкачивать и вообще не предлагает никаких вариантов натему отмены её действий, только убийство процесса помогает.
«Жираф большой — ему видней.» (с) =)
Сортировка в GROUP BY нужна для того, чтобы использовался бинарный поиск в уже выбранных в процессе группировки данных, вместо их полного перебора. То есть, на больших объемах данных эта сортировка позволяет значительно ускорять группировку.
Конечно, если в результате группировки возвращается малое количество записей, то в сортировке смысла мало. Но в любом случае, использовать хак ORDER BY NULL я бы не стал, если, конечно, он значительно не ускоряет выборку (а в этом я очень сомневаюсь что ускоряет, нужно проверять отдельно на каждом наборе данных).
Конечно, если в результате группировки возвращается малое количество записей, то в сортировке смысла мало. Но в любом случае, использовать хак ORDER BY NULL я бы не стал, если, конечно, он значительно не ускоряет выборку (а в этом я очень сомневаюсь что ускоряет, нужно проверять отдельно на каждом наборе данных).
По поводу последнего запроса. Ускорить его можно, если первичный ключ в таблице поменять с:
Использует новый первичный ключ, сканирует около 230732 строк — 0.6 секунд
Использует индекс по дате — 1.7 секунд
Но если периоды, по которым нужно собирать данные фиксированы, то агрегированные таблицы — то, что нужно.
PRIMARY KEY (`daily_entry_stats_entry_id`, `daily_entry_stats_date`)на
PRIMARY KEY (`daily_entry_stats_date`, `daily_entry_stats_entry_id`)В этом случае действительно будет использовано преимущество кластерного ключа, так как вся таблица будет упорядочена в первую очередь по дате и запрос будет сканировать индекс в одном «направлении». Я протестировал на таблице с 500k записей, если все нужные страницы находятся в памяти, разница в скорости не очень заметна, но если большинство страниц должно быть считано с диска, то запрос использующий новый первичный ключ отрабатывает примерно в 2.5 раза быстрее использующего просто индекс по дате. У меня получились следующие цифры:
Использует новый первичный ключ, сканирует около 230732 строк — 0.6 секунд
Использует индекс по дате — 1.7 секунд
Но если периоды, по которым нужно собирать данные фиксированы, то агрегированные таблицы — то, что нужно.
Кстати насчет paging-a и LIMIT 10000000,10
Совет, при большой нагрузке не заморачивайтесь с «полным» выводом кол-вом страниц…
посмотрите на всего нагруженные сайты — все делают paging максимум на 10 страниц (оконным методом paging-a), для чего — как раз для того чтобы боты не положили БД и не кто не нагружал БД.
А если хотите старые страницы — идите, извините в… поиск ;)
Это касается для тех сайтов у кого кол-во топиков превышает 100`000.
Совет, при большой нагрузке не заморачивайтесь с «полным» выводом кол-вом страниц…
посмотрите на всего нагруженные сайты — все делают paging максимум на 10 страниц (оконным методом paging-a), для чего — как раз для того чтобы боты не положили БД и не кто не нагружал БД.
А если хотите старые страницы — идите, извините в… поиск ;)
Это касается для тех сайтов у кого кол-во топиков превышает 100`000.
По поводу того, что многие сайты показывают в пэйджинге не много страниц, чтобы боты особо не лазили.
Я с Вами согласен
>Совет, при большой нагрузке не заморачивайтесь с «полным» выводом кол-вом страниц…
на самом деле, это вообще не проблема. Т.е. для мускула выполнить запрос count(*) особенно для таблиц MyISAM, HEAP и без условия WHERE
т.е. запрос
SELECT count(*) FROM `topic` — выполнится молниеносно.
#
mysql> SELECT count(*) FROM `topic`;
#
+----------+
#
| count(*) |
#
+----------+
#
| 8343532 |
#
+----------+
#
1 row IN SET (0.00 sec)
Все потому, что данные энжины не поддерживают транзакций, и имеют table level для блокирования. И такие данные как колличество записей в таблице хранится в структуре-метаинформаци, которая всегда в кеше и возвращается оттуда очень быстро.
В InnoDB такой запрос выполняется мендленей, т.к. нужно выполнить роускан или индексскан, это происходит от того, что в InnoDB row level locking
запросы на count(*) c присутствием WHERE выполняются приблизительно одинаково в зависимости от данных, кол-ва, и условия
Я с Вами согласен
>Совет, при большой нагрузке не заморачивайтесь с «полным» выводом кол-вом страниц…
на самом деле, это вообще не проблема. Т.е. для мускула выполнить запрос count(*) особенно для таблиц MyISAM, HEAP и без условия WHERE
т.е. запрос
SELECT count(*) FROM `topic` — выполнится молниеносно.
#
mysql> SELECT count(*) FROM `topic`;
#
+----------+
#
| count(*) |
#
+----------+
#
| 8343532 |
#
+----------+
#
1 row IN SET (0.00 sec)
Все потому, что данные энжины не поддерживают транзакций, и имеют table level для блокирования. И такие данные как колличество записей в таблице хранится в структуре-метаинформаци, которая всегда в кеше и возвращается оттуда очень быстро.
В InnoDB такой запрос выполняется мендленей, т.к. нужно выполнить роускан или индексскан, это происходит от того, что в InnoDB row level locking
запросы на count(*) c присутствием WHERE выполняются приблизительно одинаково в зависимости от данных, кол-ва, и условия
Простой способ немного ускорить выполнение запросов с большими лимитами:
Сначала найдем общее количество результатов:
SELECT COUNT(*) FROM item WHERE [тут какие-то наши условия]
Допустим, получилось 1015.
Теперь, вместо:
SELECT item_id FROM item WHERE… ORDER BY item_id LIMIT 1000, 15
можно написать:
SELECT item_id FROM item WHERE… ORDER BY item_id DESC LIMIT 0, 15
И потом уже выбрать данные:
SELECT… FROM item WHERE item_id IN ([массив из предыдущего запроса])
Запросы из первой половины будут обычные:
SELECT item_id FROM item WHERE… ORDER BY item_id LIMIT 200, 15
Самые медленные запросы получатся посередине, зато чем ближе к концу — тем быстрее.
Сначала найдем общее количество результатов:
SELECT COUNT(*) FROM item WHERE [тут какие-то наши условия]
Допустим, получилось 1015.
Теперь, вместо:
SELECT item_id FROM item WHERE… ORDER BY item_id LIMIT 1000, 15
можно написать:
SELECT item_id FROM item WHERE… ORDER BY item_id DESC LIMIT 0, 15
И потом уже выбрать данные:
SELECT… FROM item WHERE item_id IN ([массив из предыдущего запроса])
Запросы из первой половины будут обычные:
SELECT item_id FROM item WHERE… ORDER BY item_id LIMIT 200, 15
Самые медленные запросы получатся посередине, зато чем ближе к концу — тем быстрее.
> Также некоторые думают, что конструкция SQL_NO_CACHE SQL_CALC_FOUND_ROWS быстрей 2х запросов первый c LIMIT, а второй select count(*). Здесь вы прочтете разоблачение этой легенды…
Заметьте, что в тех примерах используется покрывающий индекс, а на практике такое бывает далеко не всегда и SQL_CALC_FOUND_ROWS бывает очень даже полезен. Он будет работать быстрее в 2 раза, чем вариант с COUNT(*) в запросах, в которых используется full table scan или группировки. В остальных случаях он тоже может быть полезен — тестируйте!
Заметьте, что в тех примерах используется покрывающий индекс, а на практике такое бывает далеко не всегда и SQL_CALC_FOUND_ROWS бывает очень даже полезен. Он будет работать быстрее в 2 раза, чем вариант с COUNT(*) в запросах, в которых используется full table scan или группировки. В остальных случаях он тоже может быть полезен — тестируйте!
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
MySQL Performance real life Tips and Tricks