Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 65
а почему «AS' в запросах маленькими буквами пишете? ^_~
отличная статья, спасибо!
Как Вы так быстро умудряетесь их прочитывать. Я пол дня пишу, а Вы — за 7-10 минут прочитываете и уже комментите :-)
знакомо. сам же писатель :) да писать намного дольше чем читать. честно, я все прочел полностью :)
«
— Фрай! Тут всего две страницы текста!
— Я писал это 2 часа, думал и читать будут два часа…
» (с) Футурама.
Спасибо за статью. Оч интересно и полезно… в мемориз однозначно :)
— Фрай! Тут всего две страницы текста!
— Я писал это 2 часа, думал и читать будут два часа…
» (с) Футурама.
Спасибо за статью. Оч интересно и полезно… в мемориз однозначно :)
Хотим продолжения :)
Материал очень интересный, но изложение страдает. Иногда не очень понятно, где хорошо, где плохо. Иногда не очень понятно, о какой проблеме идет речь.
Я бы постарался разбить текст на подразделы с кратким описанием проблемы, ее неправильным решением и правильным. Чтобы было мини how-to.
А вообще хорошо бы это в отдельный блог вынести «Оптимизация MySQL», например
Я бы постарался разбить текст на подразделы с кратким описанием проблемы, ее неправильным решением и правильным. Чтобы было мини how-to.
А вообще хорошо бы это в отдельный блог вынести «Оптимизация MySQL», например
Спасибо, освежили память. :)
Заблуждение по поводу int(2) — встречается повсеместно, это точно…
Заблуждение по поводу int(2) — встречается повсеместно, это точно…
Весьма полезная информация. Хоть и не считаю себя новичком, функцию INET_ATON/INET_NTOA, признаюсь, подзабыл. Практически открыл ее для себя заново :-)
Maghamed, продолжайте писать в том же духе. Уверен, есть еще много моментов и тонкостей в mysql, которые было бы не плохо использовать в повседневной работе, но по тем или иным причинам, достаточно большое количество разработчиков о них или не знают, или уже подзабыли.
Maghamed, продолжайте писать в том же духе. Уверен, есть еще много моментов и тонкостей в mysql, которые было бы не плохо использовать в повседневной работе, но по тем или иным причинам, достаточно большое количество разработчиков о них или не знают, или уже подзабыли.
Все хорошо, особенно про антилопу. Спасибо.
Долго пытался въехать в то, что вы написали про NTOA/ATON
То ли я не понял, толи вы имели ввиду следующее (если это так — то поправьте статью, а то очень не понятно):
Оптимально хранить ip-адреса не как строки (varchar), а как числа. Для этого существует две функции mySQL — INET_ATON и INET_NTOA, первая преобразует 4 байтную последовательность ip-адреса в число, вторая преобразует обратно. Таким образом:
1. Столбец, в котором будут хранится ip-адреса объявляется как `ip` INT UNSIGNED NOT NULL
2. При вставке:
INSERT INTO `ips`
SET ip = INET_ATON('213.169.23.35')
3. При выборке (например > 255.255.0.0)
SELECT
INET_NTOA(ip)
FROM
`ips`
WHERE
`ips`.ip > INET_ATON('255.255.0.0')
Само собой по ip делаем индекс
P.S. Кстати с помощью этих функций можно хранить не только ip-адреса, но и любые аналогичные последовательности, например rgb-цвета :)
То ли я не понял, толи вы имели ввиду следующее (если это так — то поправьте статью, а то очень не понятно):
Оптимально хранить ip-адреса не как строки (varchar), а как числа. Для этого существует две функции mySQL — INET_ATON и INET_NTOA, первая преобразует 4 байтную последовательность ip-адреса в число, вторая преобразует обратно. Таким образом:
1. Столбец, в котором будут хранится ip-адреса объявляется как `ip` INT UNSIGNED NOT NULL
2. При вставке:
INSERT INTO `ips`
SET ip = INET_ATON('213.169.23.35')
3. При выборке (например > 255.255.0.0)
SELECT
INET_NTOA(ip)
FROM
`ips`
WHERE
`ips`.ip > INET_ATON('255.255.0.0')
Само собой по ip делаем индекс
P.S. Кстати с помощью этих функций можно хранить не только ip-адреса, но и любые аналогичные последовательности, например rgb-цвета :)
Да, Вы все поняли правильно, относительно Вашего постскриптума
>P.S. Кстати с помощью этих функций можно хранить не только ip-адреса, но и любые >аналогичные последовательности, например rgb-цвета :)
Думаю, это не лучший выбор, использование ф-ии INET_ATON для хранения RGB последовательности, т.к. данная ф-ия расчитана на хранение 4х групп чисел, разделенных точкой, т.е вам прийдется сохранять избыточную информацию, скажем, белый цвет
#FFFFFF (255.255.255), вам прийдется хранить как 255.255.255.0
Но если Вам понравился данный метод, то Вам ничто не мешает написать собственную ф-ию, которая расчитана непосредственно на RGB последовательность, скажем, нечто вроде
Если с синтаксисом ничего не напутал, то как-то так
>P.S. Кстати с помощью этих функций можно хранить не только ip-адреса, но и любые >аналогичные последовательности, например rgb-цвета :)
Думаю, это не лучший выбор, использование ф-ии INET_ATON для хранения RGB последовательности, т.к. данная ф-ия расчитана на хранение 4х групп чисел, разделенных точкой, т.е вам прийдется сохранять избыточную информацию, скажем, белый цвет
#FFFFFF (255.255.255), вам прийдется хранить как 255.255.255.0
Но если Вам понравился данный метод, то Вам ничто не мешает написать собственную ф-ию, которая расчитана непосредственно на RGB последовательность, скажем, нечто вроде
CREATE FUNCTION RGB_FUNC(IN rgb_string VARCHAR(9))
RETURNS INT(10)
BEGIN
DECLARE red INT;
DECLARE green INT;
DECLARE blue INT;
DECLARE result INT;
SET red = CAST(SUBSTR(rgb_string,1,3) AS INT);
SET green = CAST(SUBSTR(rgb_string,4,3) AS INT);
SET blue = CAST(SUBSTR(rgb_string,7,3) AS INT);
SET result = red*256*256 + green*256 + blue;
RETURN(result);
END;
* This source code was highlighted with Source Code Highlighter.
Если с синтаксисом ничего не напутал, то как-то так
Ну тогда уж скорее как 0.255.255.255, т.к.
INET_ATON('0.255.255.255') -> 16777215
INET_NTOA(16777215) -> 0.255.255.255
:)
В базе хранится именно число, а скриптами дорисовать/отрезать лидирующий ноль не проблема.
Я уж и не помню, если честно, где это применял, давно было
INET_ATON('0.255.255.255') -> 16777215
INET_NTOA(16777215) -> 0.255.255.255
:)
В базе хранится именно число, а скриптами дорисовать/отрезать лидирующий ноль не проблема.
Я уж и не помню, если честно, где это применял, давно было
А можно и не отрезать. Многий комерческий софт типа ChartDirector использует 4-х байтные «цвета», в которых первый байт — alpha
Да, Вы правы, что 0.255.255.255. Я это и имел в виду. 2 часа ночи просто давали о себе знать :-) Ну и во всем остальном Вы правы.
все таки кусок про ip-адреса поправьте в статье. Поверьте, понять о чем вы написали в текущем виде сможет только тот, кто это и так уже знает :)
а это — `ips`.ip > 256*256*256*255 + 256*256*255* — мало того, что неточно(не до конца) написано, так еще и не понятно, зачем тут ручная калькуляция, если сами же про функции рассказываете
а это — `ips`.ip > 256*256*256*255 + 256*256*255* — мало того, что неточно(не до конца) написано, так еще и не понятно, зачем тут ручная калькуляция, если сами же про функции рассказываете
поправьте заголовок — to be continueD
Mysql не до конца оптимизирует джойны, так как при некоторых вариантах склеек может получиться больше чем одна запись.
Так же не стоит забывать, что можно сделать сортировку по одной из приджойненых таблиц… И мускуль вынужден сначала все склеить, потом отсортировать, и только потом понять, которая из записей пятитысячная.
Так же не стоит забывать, что можно сделать сортировку по одной из приджойненых таблиц… И мускуль вынужден сначала все склеить, потом отсортировать, и только потом понять, которая из записей пятитысячная.
Было бы здорово если бы автор приводил конкретные цифры (время выполнение того или иного запроса на определенном объеме данных). Но и без этого было интересно почитать ваш труд. Спасибо.
`ips`.ip > 256*256*256*255 + 256*256*255* < — вы забыли дописать последний множитель
Знаете, человек бесконечно долго может смотреть на то как работают другие люди, как льется вода, на огонь, как тебе выдают зарплату.
После написания пары топиков на хабр, я отметил для себя еще один пункт в этом списке.
Я могу очень долго смотреть как растет моя карма :-))
После написания пары топиков на хабр, я отметил для себя еще один пункт в этом списке.
Я могу очень долго смотреть как растет моя карма :-))
Смотреть-то хорошо =) Некоторое вещи действительно интересны.
Только вы бы статьи свои правили, приводили их к нормальному стилю.
Пример — кусок про ip-адреса. «соответственно если мы хотим, скажем, выполнить поиск...» якобы продолжает предыдущую мысль, на самом деле начиная новую, и то как-то непонятно, с маленькой буквы, как будто у вас что-то удалилось…
Ну и такая рваность и разрозненность кругом.
Только вы бы статьи свои правили, приводили их к нормальному стилю.
Пример — кусок про ip-адреса. «соответственно если мы хотим, скажем, выполнить поиск...» якобы продолжает предыдущую мысль, на самом деле начиная новую, и то как-то непонятно, с маленькой буквы, как будто у вас что-то удалилось…
Ну и такая рваность и разрозненность кругом.
Да, я понимаю, что мысли скачут, т.к. статьи пишутся в течение дня на работе в перерывах между таковой и некоторые вещи, например про INT(1) буквально на ходу видишь в коде своих коллег, поэтому и дописываешь их на ходу в статью. Т.е. что за целый день набросал, то в конце и опубликовал.
Я с удовольствием поправлю статью, чтобы она стала более читабельна и в следующей статье буду обращать внимание на отзывы по юзабилити в читании. Но пока я понял, что лучше разбивать статьи на независимые разделы. А пока такие жалобы/пожелания малоконструктивны. Пожалуйста, говорите конкретней как разбить, или что именно не понятно — я объясню, или плохо описано — я перепишу.
Я с удовольствием поправлю статью, чтобы она стала более читабельна и в следующей статье буду обращать внимание на отзывы по юзабилити в читании. Но пока я понял, что лучше разбивать статьи на независимые разделы. А пока такие жалобы/пожелания малоконструктивны. Пожалуйста, говорите конкретней как разбить, или что именно не понятно — я объясню, или плохо описано — я перепишу.
Ну в принципе тут не так много-то и надо для отличных заметок! )
Добавить подзаголовки для разбиения.
И строить логику «повествования», мысли организовывать, оформлять их в абзацы с более-менее законченным смыслом, без разрывов посреди предложения.
Сложно это выразить, посмотрите хорошие статьи хотя бы прямо здесь, на хабре.
Добавить подзаголовки для разбиения.
И строить логику «повествования», мысли организовывать, оформлять их в абзацы с более-менее законченным смыслом, без разрывов посреди предложения.
Сложно это выразить, посмотрите хорошие статьи хотя бы прямо здесь, на хабре.
COUNT(*) vs COUNT(column_name)
Замечал не раз, что многие считают, что COUNT(*) это алиас COUNT(column_name). Это совершенно не так.
Во-первых, эти запросы могут возвращать разные результаты. Такое может проявляться когда столбец column_name может содержать NULL значения. Т.е. конструкция COUNT(column_name) вернет кол-во записей с column_name IS NOT NULL.
Во-вторых, эти запросы выполняются с разной скоростью. Например, такой запрос
может выполняться гораздо дольше, чем
т.к. запрос, использующий COUNT(*), при наличии индекса по полю tli.uid будет использывать покрывающий индекс и, соответственно, выполнится очень быстро. Первый же запрос будет искать COUNT методом ROW SCAN, о чем и говорит «Extra: USING WHERE» в EXPLAIN этого запроса.
На самом деле можно сделать так, чтобы и первый запрос использовал индекс, добавив к данной таблице покрывающий индекс cover_key(uid, type). Но, ИМХО, лучше использовать COUNT(*), чем вводить дополнительный индекс для одного единственного запроса.
Замечал не раз, что многие считают, что COUNT(*) это алиас COUNT(column_name). Это совершенно не так.
Во-первых, эти запросы могут возвращать разные результаты. Такое может проявляться когда столбец column_name может содержать NULL значения. Т.е. конструкция COUNT(column_name) вернет кол-во записей с column_name IS NOT NULL.
Во-вторых, эти запросы выполняются с разной скоростью. Например, такой запрос
SELECT
SQL_NO_CACHE count(tli.`type`)
FROM
`tx_localrep_images` as tli
WHERE
tli.uid > 500;
может выполняться гораздо дольше, чем
SELECT
SQL_NO_CACHE count(*)
FROM
`tx_localrep_images` as tli
WHERE
tli.uid > 500;
т.к. запрос, использующий COUNT(*), при наличии индекса по полю tli.uid будет использывать покрывающий индекс и, соответственно, выполнится очень быстро. Первый же запрос будет искать COUNT методом ROW SCAN, о чем и говорит «Extra: USING WHERE» в EXPLAIN этого запроса.
На самом деле можно сделать так, чтобы и первый запрос использовал индекс, добавив к данной таблице покрывающий индекс cover_key(uid, type). Но, ИМХО, лучше использовать COUNT(*), чем вводить дополнительный индекс для одного единственного запроса.
Я подправил в соответствии с Вашими замечаниями. Надеюсь, что так более удобочитаемо. Хотя некоторые обороты решил оставить. Нужно же иметь какой-то авторский стиль изложения, пусть его и называют рваным и разросненным :-)
Это был просто пример, возможно и неудачный к тому же.
Статья осталась в том же обрывочном формате — прописная буква не сделает слово началом предложения, если оно таковым не является…
Статья осталась в том же обрывочном формате — прописная буква не сделает слово началом предложения, если оно таковым не является…
Знаете, я не учусь на журфаке и редколлегии у меня тут нет. Я — программист. Пишу — как могу и о чем интересно. Притом сижу сейчас на работе и стараюсь работать, так что у меня нет времени в разгар рабочего дня переписывать написанное. Я уже написал для тех кому непонятно, что они могут написать в комментах, что именно им непонятно, или где можно получить более подробную инфу или примеры, и когда я освобожусь — обязательно объясню, напишу.
А если вы ищете красивые формы, то читайте Пушкина, Достоевского, Толстого…
А если вы ищете красивые формы, то читайте Пушкина, Достоевского, Толстого…
эх… я не хотел вас никак оскорбить.
Мне очень интересны и новы оказались некоторые сведения, вам спасибо!
Принуждать вас к написанию литературной статьи я не мог и не собирался. Лишь указал на недостатки, может вы в будущем прислушаетесь. Ведь всё-таки это статья для подачи какой-то информации, а не отрывки из беседы с другом-программистом.
Не принимайте так близко :)
Мне очень интересны и новы оказались некоторые сведения, вам спасибо!
Принуждать вас к написанию литературной статьи я не мог и не собирался. Лишь указал на недостатки, может вы в будущем прислушаетесь. Ведь всё-таки это статья для подачи какой-то информации, а не отрывки из беседы с другом-программистом.
Не принимайте так близко :)
Если говорить про дурость оптимизатора MySQL, то не лишне сказать о том, что его надо вообще лишать какой-либо свободы действий. Хороший критический запрос это запрос с использованием директивы force index().
То же самое можно сказать о неравенствах в условиях: часто их можно и нужно избежать: select id from posts where score = > 0; → select id from posts where score_positive = 1;
То же самое можно сказать о неравенствах в условиях: часто их можно и нужно избежать: select id from posts where score = > 0; → select id from posts where score_positive = 1;
Здесь, я с Вами категорически не согласен. Т.к. при заполнении данными может значительно изменятся cardinality индексов, и тот индекс, который оптимален на данный момент, может стать абсолютно неоптимален в будущем. И Вам прийдется потом долго искать причину тормозов. Поэтому force index также как и straight join следует избегать если в этом действительно нет необходимости.
Я хотел уделить этому немного места в моей следующей статье.
Я хотел уделить этому немного места в моей следующей статье.
Не знаю, что для вас «абсолютно неоптимален», а в моём представлении нет ничего хуже, чем файлсорт на несколько десятков тысяч записей, так что ваше абсолютно вовсе даже и не абсолютно.
Ха, как мы с Вами дружно друг-друга минуснули :-)
Да, я согласен, что «файлсорт на несколько десятков тысяч записей» это плохо. Я об этом и пишу во всех своих статья, и еще планирую писать дальше.
Но не стоит так плохо говорить о MySQL оптимизаторе, хотя может показаться, что я и ругаю его в своих статьях, но как правило он работает вполне адекватно при выборе индексов для запросов.
Просто следует проектировать БД так и создавать такие индексы, чтобы MySQL корректно выбирал, то что нужно.
Также нужно держать индексы и данные в актуальном состоянии, для этого периодически (по крону) следует выполнять ANALYZE TABLE и OPTIMIZE TABLE.
Но ANALYZE TABLE следует применять с умом на продакшене, т.к во время выполнения, а оно зависит от размера индексов ставится READ LOCK для MyISAM и WRITE LOCK для InnoDB
Т.к. решение оптимизатора каокй индекс выбрать для выполнения запроса, зависит от статистики индексов, и хорошо если она актуальна.
Да, я согласен, что «файлсорт на несколько десятков тысяч записей» это плохо. Я об этом и пишу во всех своих статья, и еще планирую писать дальше.
Но не стоит так плохо говорить о MySQL оптимизаторе, хотя может показаться, что я и ругаю его в своих статьях, но как правило он работает вполне адекватно при выборе индексов для запросов.
Просто следует проектировать БД так и создавать такие индексы, чтобы MySQL корректно выбирал, то что нужно.
Также нужно держать индексы и данные в актуальном состоянии, для этого периодически (по крону) следует выполнять ANALYZE TABLE и OPTIMIZE TABLE.
Но ANALYZE TABLE следует применять с умом на продакшене, т.к во время выполнения, а оно зависит от размера индексов ставится READ LOCK для MyISAM и WRITE LOCK для InnoDB
Т.к. решение оптимизатора каокй индекс выбрать для выполнения запроса, зависит от статистики индексов, и хорошо если она актуальна.
Я имел дело с посещаемым проектом, где момент нестабильности наступал хаотично: мог работать день, а мог сходить с ума раз в 15 минут. И чаще всего проблема наступала в понедельник, так как это был новостной ресурс (до 300 обращений в секунду).
Да, optimize table решал проблему, но это грубый костыль, так как для обеспечения нормальной работы проекта, его нужно было запускать каждую 1 минуту (?).
Использовались вполне себе нормальные индексы на 3-4 поля. Выход был только один: расставить везде force index(). Есть некоторые предположения на этот счёт, например, что причина может быть в несимметричности бинарных деревьев итп.
Да, optimize table решал проблему, но это грубый костыль, так как для обеспечения нормальной работы проекта, его нужно было запускать каждую 1 минуту (?).
Использовались вполне себе нормальные индексы на 3-4 поля. Выход был только один: расставить везде force index(). Есть некоторые предположения на этот счёт, например, что причина может быть в несимметричности бинарных деревьев итп.
Иногда, а точней зачастую если мы работаем с большими объемами данных. То следует выполнять дополнительную нормализацию, или как это еще называют вертикальный шардинг. Т.е. это неправильно плохо иметь большую таблицу, которая хранит миллионы записей и при этом имеет 10-ки полей (столбцов), притом переменной длинны, может быть Вы там еще и само тело статей хранили на Вашем новостном ресурсе, типа TEXT? Конечно, такие запросы будут работать крайне медленно на выборку.
В таких ситуациях следует отделять такие поля как тело статьи и прочую информацию (типы переменной длинны) в другие таблицы, а в первой стараться оставлять как можно меньше полей типа фиксированной длинны (например ID-ики и связи с другими таблицами), и только в нужные моменты подключать большие поля.
При этом используя технологию DERIVED TABLE в которой отсекаем с помощью WHERE условий множество ненужных записей и склеиваем только, скажем, 10 нужных.
Нужно всегда думать на стадии проектирования какие основные запросы будут приходить на какие таблицы, и в соответствии с этим формировать архитектуру.
Притом не стоит забывать о кешировании результатов для того чтобы разгружать сервер БД
В таких ситуациях следует отделять такие поля как тело статьи и прочую информацию (типы переменной длинны) в другие таблицы, а в первой стараться оставлять как можно меньше полей типа фиксированной длинны (например ID-ики и связи с другими таблицами), и только в нужные моменты подключать большие поля.
При этом используя технологию DERIVED TABLE в которой отсекаем с помощью WHERE условий множество ненужных записей и склеиваем только, скажем, 10 нужных.
Нужно всегда думать на стадии проектирования какие основные запросы будут приходить на какие таблицы, и в соответствии с этим формировать архитектуру.
Притом не стоит забывать о кешировании результатов для того чтобы разгружать сервер БД
Там было не очень много записей (несколько десятков тысяч).
Вообще, мне кажется, что если переломный момент не наступил, и проще сделать force index, чем заниматься нормализацией, вводя значительные доработки в код, то лучше сделать первое.
То есть нормализация с теоритической точки зрения это, конечно, круто, но ввести её там где раньше её не было не всегда просто.
Вообще, мне кажется, что если переломный момент не наступил, и проще сделать force index, чем заниматься нормализацией, вводя значительные доработки в код, то лучше сделать первое.
То есть нормализация с теоритической точки зрения это, конечно, круто, но ввести её там где раньше её не было не всегда просто.
Вот, мы постепенно приходим к тому, что Вы сами признаете, что Архитектура Вашей БД была далеко не оптимальной. А о архитектуре БД нужно думать в момент её проектирования, расчета кол-во запросов на единицу времени, нагруженность определенных таблиц и т.д.
А если уж наступил переломный момент, то нужно делать рефакторинг, а не добавлять какие-то костыли, что делаете Вы в описанном выше случае.
Я честно-говоря не встречал случаев где бы force index был панацеей. Но это далеко не лучший способ решения проблемы.
Просто эту статью прочитает, я надеюсь, не один человек, и не все люди понимаю как работает MySQL оптимизатор, как профайлить запросы (кстати, об этом будет моя следующая статья:-) ). А Вы так категорично пишете, что нельзя доверять оптимизатору и нужно самому указывать индекся для использования.
Вы представляете себе если Вам в руки попадет проект в котором все запросы используют force index и straight join?!!!
А то что у Вас были проблемы с архитектурой с этого и нужно начинать, я утверждаю, и врядли меня кто-то переубедит, что если подобрана правильная архитектура БД и грамотно расставлены индексы, то такой проблемы не возникнет!
А если уж наступил переломный момент, то нужно делать рефакторинг, а не добавлять какие-то костыли, что делаете Вы в описанном выше случае.
Я честно-говоря не встречал случаев где бы force index был панацеей. Но это далеко не лучший способ решения проблемы.
Просто эту статью прочитает, я надеюсь, не один человек, и не все люди понимаю как работает MySQL оптимизатор, как профайлить запросы (кстати, об этом будет моя следующая статья:-) ). А Вы так категорично пишете, что нельзя доверять оптимизатору и нужно самому указывать индекся для использования.
Вы представляете себе если Вам в руки попадет проект в котором все запросы используют force index и straight join?!!!
А то что у Вас были проблемы с архитектурой с этого и нужно начинать, я утверждаю, и врядли меня кто-то переубедит, что если подобрана правильная архитектура БД и грамотно расставлены индексы, то такой проблемы не возникнет!
Далеко не все запросы с force index(), только несколько критических.
force index — зло :)
Оптимизатор MySQL лучше знает.
Бывают момент, когда вам кажется что force index здесь самый оптимальный, а потом долго ищете почему сервер тормозит и из-за чего… как раз может сильно положить БД из-за вашего force index.
Оптимизатор в этом отношении более гибкий, он не даст полностью «упасть».
Оптимизатор MySQL лучше знает.
Бывают момент, когда вам кажется что force index здесь самый оптимальный, а потом долго ищете почему сервер тормозит и из-за чего… как раз может сильно положить БД из-за вашего force index.
Оптимизатор в этом отношении более гибкий, он не даст полностью «упасть».
Если быть более точным, то имеется в виду, следить за селективностью и эффективностью приходится самому. Ежу ясно, что когда меняется селективность, надо менять и индексы (если есть такая возможность).
Есть некоторый абстрактный момент, связанный с нагрузкой, и отдельно взятыми индексами, когда оптимизатор может отказаться пользоваться ключём с нужным полем для сортировки, отдавая предпочтение меньшему перебору, но с использованием временных таблиц, что в случае крупного ресурса приводит к полному коллапсу, когда быстрее перезапустить сервер, чем дождаться, пока всё само «рассосётся».
Есть некоторый абстрактный момент, связанный с нагрузкой, и отдельно взятыми индексами, когда оптимизатор может отказаться пользоваться ключём с нужным полем для сортировки, отдавая предпочтение меньшему перебору, но с использованием временных таблиц, что в случае крупного ресурса приводит к полному коллапсу, когда быстрее перезапустить сервер, чем дождаться, пока всё само «рассосётся».
Не стоит забывать, что если запрос будет работать (обращаться) к данным, которые занимают более 30% от общего количества (цифра 30% приблизительна и колеблется от версии к версии), то MySQL выберет ROW SCAN, потому как посчитает (и вполне правомерно), что данный метод быстрей а не будет использовать индекс.
а что за SQL_NO_CACHE интересно, что дает?
Как раз что НЕ дает — не дает MySQL кешировать результат запроса и возвращать его из Кэша. Т.е. если мы хотим потестировать производительность запроса, то без SQL_NO_CACHE — никуда :-)
Ясно. А вот по поводу первого примера, я попробовал сравнить с DERIVED TABLE и без с обычным inner join, вроде как быстрее почему-то во втором случае…
Тут нужно смотреть ваши данны в таблицах и execution plan самого запроса, то в каком порядке берутся для джоина таблицы оптимизатором.
Понимаете, в первом случае если использует INNER JOIN, то DERIVED TABLE c вложенным WHERE неуместен. Т.к. если у нас LEFT JOIN, то мы точно знаем, что выбрали 100 записей из первой таблице и к ним присоединяем записи из другой таблице, если соответсвия нет. то заполняем записи отсутствующие в правой таблице NULL-value.
если же мы делаем INNER JOIN и в DERIVED TABLE делаем LIMIT и вытаскиваем 100 записей а потом начинаем делать INNER JOIN, то тут необходимо строгое соответствие по условию склейки, в моем случае ON `tli`. cruser_id = fe.uid;
Т.е. если в таблице пользователей не найдется айдишника соответствующего cruser_id, то данная запись не попадет в результирующую выборку.
И соответственно, не факт, что мы достанем из базы 100 записей, как планировали изначально, а не 90
Понимаете, в первом случае если использует INNER JOIN, то DERIVED TABLE c вложенным WHERE неуместен. Т.к. если у нас LEFT JOIN, то мы точно знаем, что выбрали 100 записей из первой таблице и к ним присоединяем записи из другой таблице, если соответсвия нет. то заполняем записи отсутствующие в правой таблице NULL-value.
если же мы делаем INNER JOIN и в DERIVED TABLE делаем LIMIT и вытаскиваем 100 записей а потом начинаем делать INNER JOIN, то тут необходимо строгое соответствие по условию склейки, в моем случае ON `tli`. cruser_id = fe.uid;
Т.е. если в таблице пользователей не найдется айдишника соответствующего cruser_id, то данная запись не попадет в результирующую выборку.
И соответственно, не факт, что мы достанем из базы 100 записей, как планировали изначально, а не 90
отличная статья, спасибо!
исправьте опечаточку
«также тормозят наши изменения дЫнных (UPDATE, INSERT, DELETE)»
и недопечаточку
«так вот, некоторые думаю, что MySQL сделает следующее „
исправьте опечаточку
«также тормозят наши изменения дЫнных (UPDATE, INSERT, DELETE)»
и недопечаточку
«так вот, некоторые думаю, что MySQL сделает следующее „
Вообще, было бы интересно почитать ваше мнение на тему правильного подхода к выбору индексов для сложных запросов, когда джоинятся не две, а 3-4 и более таблиц и анализе плана выполнения таких запросов, т.е. как правильно понять что имеет ввиду explain :)
хорошая статья, но я так и не понял, как ускорить limit, если выборка идет с сортировкой/условием, когда записи выбираются не подряд… типа… WHERE public=1 ORDER BY timest desc LIMIT 5000, 100… для себя решил хранением последнего и первого timestamp'a, и преобразованием в
WHERE public=1 and timest
WHERE public=1 and timest
Всегда боролся с NULL манией. Спасибо, статья очень интересная. Про count(*) никогда не заморачивался. Еще добавил бы про грамотное использование назначения индексов, которые могут ускорить работу запроса просто в разы.
Вобщем, с удовольствием почитаю еще посты в данной тематике!
Вобщем, с удовольствием почитаю еще посты в данной тематике!
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
MySQL Performance real life Tips and Tricks. To be continued.