Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 107
Дополнение:
Зачастую при добавление в таблицу, имеющей UNIQUE индекс или PRIMARY KEY, новой строки, очень полезным бывает синтаксис
INSERT IGNORE. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке,
то есть сама вставка не будет произведена, при этом не будет прекращенно выполнение.
Это очень выручает при оптимизации приложения, когда мы вместо двух запросов:
1- проверить наличие строки в таблице по ключу (SELECT)
2- вставить строку в случае отсутствия дублирования ключа (INSERT)
будем использовать только один запорс INSERT IGNORE.
Зачастую при добавление в таблицу, имеющей UNIQUE индекс или PRIMARY KEY, новой строки, очень полезным бывает синтаксис
INSERT IGNORE. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке,
то есть сама вставка не будет произведена, при этом не будет прекращенно выполнение.
Это очень выручает при оптимизации приложения, когда мы вместо двух запросов:
1- проверить наличие строки в таблице по ключу (SELECT)
2- вставить строку в случае отсутствия дублирования ключа (INSERT)
будем использовать только один запорс INSERT IGNORE.
На мой взгляд INSERT IGNORE, также, как REPLACE и ON DUPLICATE KEY UPDATE - порой очень опасные конструкции, т.е. их если применять, то в очень узких местах, когда действительно, лишний запрос недопустим, и полностью осознавая, что делаешь.
За свою практику встречал случаи, когда люди этими конструкциями "затыкали" ошибки в запросах, что приводило потом к очень долгой и проблематичной отладке (т.к. заткнули симптомы, а причина осталась и потом вылезает в самых неожиданных местах).
За свою практику встречал случаи, когда люди этими конструкциями "затыкали" ошибки в запросах, что приводило потом к очень долгой и проблематичной отладке (т.к. заткнули симптомы, а причина осталась и потом вылезает в самых неожиданных местах).
как можно REPLACE'ом и ON DUPLICATE KEY UPDATE "затыкать" ошибки?
Ну к примеру мы считаем статистику, статистика вставляется с ключём "дата", "элемент", статистика за определённый период может пересчитываться (с целью корректировки, скажем, или просто что-то падало и надо восстановить). Соответственно идёт дубликат по первичному ключу, самое простое - REPLACE и не париться. Однако, если в обновлённой статистике элементов стало меньше - то в том, что посчитается будет смесь правильных данных и того, что было. Выход - сначала удалить период за дату, потом вставить - будут чистые данные. Казалось бы, дурацкая ошибка, но я с таким сталкивался.
«Нет смысла индексировать поле, в котором два-три возможных значения. Пользы от такого индекса будет мало.» — как раз-таки эти поля и надо индексировать в первую очередь!
Попробуйсте создайте таблицу на 500 000 записей с колонкой, где будет около 5 разных значений не весь сет и потом сделайте выборку БЕЗ и С индексом. Разница будет очень ощутима.
Попробуйсте создайте таблицу на 500 000 записей с колонкой, где будет около 5 разных значений не весь сет и потом сделайте выборку БЕЗ и С индексом. Разница будет очень ощутима.
да изменения в скорости будут, но фулл скан по 100000 записей это в любом случае - не лучший вариант.
при select * с индексом скорость будет значительно меньше чем при запросе без индекса.
Согласен. Проверено! Особенно они (эти индексы) отлично "отрезают" лишнее при JOIN. (Кстати mysql сам прекрасно знает в этих случаях какой инекс выбрать в зависимости от кол-ва записей) А в составном индексе вообще...
Просто почаще надо пользоваться Explain :) и анализировать в каждом конкретном случае., особенно на больших базах.
Просто почаще надо пользоваться Explain :) и анализировать в каждом конкретном случае., особенно на больших базах.
Попробовал на 1 000 000 записей. Разницы вообще нет.
По возможности используйте INSERT DELAYED
он работает только на MyISAM таблицах :(
использование InnoDB, кстати, даёт ощутимый прирост скорости на выборках
использование InnoDB, кстати, даёт ощутимый прирост скорости на выборках
> использование InnoDB, кстати, даёт ощутимый прирост скорости на выборках
ох и спорное это высказывание. Вот здесь например есть тесты. Заметьте, что при нагрузках < 10000 запросов\сек разницы между InnoDB и MyISAM практически нет.
ох и спорное это высказывание. Вот здесь например есть тесты. Заметьте, что при нагрузках < 10000 запросов\сек разницы между InnoDB и MyISAM практически нет.
при работе с большими объёмами данных, Limit спасает только частично. Если Вы укажите limit 10000, 20, то mysql будет бежать с самого начала таблицы, пока не насчитает 10000, и только тогда считает 20 записей. В простейшем случае этого можно избежать, запомнив, какой id мы выводили последним, и в условие добавить id>последний_ид limit 20. Если требуется сортировка по другим полям, то нужно создать "правильный" индекс по этим полям и в условиях явно указать, что больше чего, что меньше чего. Правда этим способом будет сложнее вывести "пагинатор" "1 2 3 4 5" и тд. Но учитывая то, что пользователи редко ходят дальше нескольких страниц, то вполне будет достаточно "следующая последняя".
по-моему :
4. Использование SQL там где можно вполне обойтись ORM. Т.е. в большинстве приложений на ruby, python, и php. Переложить составление sql-запросов на компьютер, а он уже будет паковать выборки в более крупные\оптимизированные
4. Использование SQL там где можно вполне обойтись ORM. Т.е. в большинстве приложений на ruby, python, и php. Переложить составление sql-запросов на компьютер, а он уже будет паковать выборки в более крупные\оптимизированные
здесь речь идёт об оптимизации работы с MySQL, а ORM годится для составления только простых запросов типа select * from ... (максимум, JOIN сделать) и никакой оптимизации таким способом Вы не добьётесь. Вы где-нибудь видели ORM, который использует INSERT IGNORE или ON DUPLICATE KEY UPDATE?
иначе бы ORM просто не было. курите http://www.continuousthinking.com/are/activerecord-extensions-0-0-5 к примеру.
с INSERT IGNORE и ON DUPLICATE KEY UPDATE был не удачный пример. То, что Вы напишете с помощью ORM и то, что напишете сами, будет одним и тем же, только на разных языках. Я своим комментарием хотел сказать лишь то, что использование ORM не прибавит никакой оптимальности Вашим запросам. ORM не сможет создать нужные индексы или выбрать правильный для использования, а если и преобразует "*" во все поля из таблицы, то перед этим выполнит "desc tablename".
Ну desc tablename она не обязана каждый раз выполнять, но в целом согласен - ORM обчно не може ничего оптимизировать. Хотя бы потому, что не располагает сведениями о частоте тех или иных запросов.
как так не располагает? почему не располагает? ваши запросы идут через ORM, верно? данные о ваших запросах есть, верно?.. что же мешает мапперу хранить результаты последних \ частых выборок в кеше и возвращать этот кеш при повторяющихся запросах? так поступает например тот же ActiveRecord.
я отказываюсь понимать этих людей.
я отказываюсь понимать этих людей.
Во-первых, этот кэш нужен только тогда, когда Вы получили некоторые данные, а потом забыли о том, что они у Вас уже есть и пытаетесь получить второй раз. Во-вторых, этот кэш действителен только на время выполнения текущего процесса и если Вам нужно где-то что-то сохранить, то для этого выгоднее использовать что-то вроде memcached, но это уже совсем другая история.
1. При чем здесь кеш? Мы говорим об оптимизации запросов, а не об ускорении их исполнения.
2. Прозрачный кеш для сложных выборок - зло, т.к. чем сложнее выборка, тем сложнее отследить момент, когда он перестает быть валидным.
3. Кешировать просто последние выборки плохо, т.к. его полезность на более-менее сложном сайт,
с большим количеством разных запросов стремительно идет к нулю. Логичнее использовать отношение популярность/вес для определения того, что надо кешировать.
Зря, пытаться понять человека иногда очень даже полезно ;)
2. Прозрачный кеш для сложных выборок - зло, т.к. чем сложнее выборка, тем сложнее отследить момент, когда он перестает быть валидным.
3. Кешировать просто последние выборки плохо, т.к. его полезность на более-менее сложном сайт,
с большим количеством разных запросов стремительно идет к нулю. Логичнее использовать отношение популярность/вес для определения того, что надо кешировать.
я отказываюсь понимать этих людей
Зря, пытаться понять человека иногда очень даже полезно ;)
>Логичнее использовать отношение популярность/вес для определения того, что надо кешировать.
согласен, логичнее. вот и напишите плагин\переопределите класс для ORM. в изначальном посте я говорил о вариантах, где писать лапшу SQL не обязательно, т.к. запросы довольно несложные и в оптимизации человеческим существом не нуждаются
согласен, логичнее. вот и напишите плагин\переопределите класс для ORM. в изначальном посте я говорил о вариантах, где писать лапшу SQL не обязательно, т.к. запросы довольно несложные и в оптимизации человеческим существом не нуждаются
Я так и делаю. Привет от Limb::ActiveRecord
Только это не снимает проблем многопоточности ;)
Только это не снимает проблем многопоточности ;)
простейший пример - вы делаете несколько выборок в своей функции ( с циклом пример не буду приводить, это уже слишком очевидно ). ORM пихает эти выборки в очередь, и делает из них один большой запрос, рассовывая затем результат по местам, тем самым уменьшая нагрузку на БД. Более практичный пример - eager loading:
"
Eager loading is a way to find objects of a certain class and a number of named associations along with it in a single SQL call. This is one of the easiest ways of to prevent the dreaded 1+N problem in which fetching 100 posts that each need to display their author triggers 101 database queries. Through the use of eager loading, the 101 queries can be reduced to 1."
( http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html )
"
Eager loading is a way to find objects of a certain class and a number of named associations along with it in a single SQL call. This is one of the easiest ways of to prevent the dreaded 1+N problem in which fetching 100 posts that each need to display their author triggers 101 database queries. Through the use of eager loading, the 101 queries can be reduced to 1."
( http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html )
И что здесь оптимизирует ORM, если мне вручную нужно указывать :include => :somthing?
Вам вручную надо указать, "что вам нужно", а как - в данном случае решит ORM.
К сожалению она ничего не решит, а просто засунет в запрос JOIN. Оптимизации - 0. Только что писать иногда быстрее.
Вы сейчас о каком-то конкретном ORM'е говорите? Я говорил о потенциальной возможности eager fetcher'а
Я работал с ActiveRecord.
Допустим я указываю, как Вы говорите "что мне нужно", а какие есть варианты у ORM, "как" мне это дать? Как он может оптимизировать этот момент?
Допустим я указываю, как Вы говорите "что мне нужно", а какие есть варианты у ORM, "как" мне это дать? Как он может оптимизировать этот момент?
ActiveRecord это паттерн. У него есть куча реализаций на разных языках.
Не делать одинаковых запросов. Например, через использование UoW.
Допустим я указываю, как Вы говорите "что мне нужно", а какие есть варианты у ORM, "как" мне это дать? Как он может оптимизировать этот момент?
Не делать одинаковых запросов. Например, через использование UoW.
Извиняюсь, говорил об ActiveRecord в Rails.
По поводу не делать одинаковых запросов - Вы про кэш? Если да, то выше Вы же сами написали отличный комментарий по этому поводу. Здесь больше всего подойдёт пункт 1 из него.
Если речь о чем-то другом (к сожалению на UoW гугл выдаёт University of Wollongong и ничего по теме), то извиняйте.
По поводу не делать одинаковых запросов - Вы про кэш? Если да, то выше Вы же сами написали отличный комментарий по этому поводу. Здесь больше всего подойдёт пункт 1 из него.
Если речь о чем-то другом (к сожалению на UoW гугл выдаёт University of Wollongong и ничего по теме), то извиняйте.
Извиняюсь, непонятно написал.
Выше говорилось о прозрачном кеше, значения которого будут отдаваться пользователям ORM. Я же имел ввиду внутренний "кеш" (на самом деле это не совсем кеш, т.к. работает не только с получением данных) ORM'а, который будет использоваться, например, для того чтобы не делать два update'а для одной записи.
UnitOfWork(UoW)
Выше говорилось о прозрачном кеше, значения которого будут отдаваться пользователям ORM. Я же имел ввиду внутренний "кеш" (на самом деле это не совсем кеш, т.к. работает не только с получением данных) ORM'а, который будет использоваться, например, для того чтобы не делать два update'а для одной записи.
UnitOfWork(UoW)
Если делать серьезный проект, не с таким подходом, как авто.ру и его 80 серверов, то ++каждый++ новый запрос, отличный от выбора по первичному ключу, требует персонального ++человеческого++ рассмотрения.
Зачем им 80 серверов??? о_О
"то ++каждый++ новый запрос" хех, я бы посмотрел как это будет происходить. Вы понимаете не реально это, для проекта такого масштаба.
1.вопрос такой - что лучше нанять 2-5 программистов + в случае чего сервера докупать
2.либо нанять свору жутко умных программистов, которым нужно платить кучу денег.
Любой бизнесмен выберет более дешёвый и стабильный пункт 1.
а оптимизировать sql и всё остальное можно вечно.
1.вопрос такой - что лучше нанять 2-5 программистов + в случае чего сервера докупать
2.либо нанять свору жутко умных программистов, которым нужно платить кучу денег.
Любой бизнесмен выберет более дешёвый и стабильный пункт 1.
а оптимизировать sql и всё остальное можно вечно.
Потерять данные, вверенные неопрятным неучам, распределённые в рамках неоправданно разросшейся системы — бесценно.
Важно сделать первый шаг по неоправданной докупке серверов, далее процесс не будет знать предела.
Свора жутких программистов это неуместное понятие в данном случае, весь вопрос в подходе, который должен быть определён изначально.
Я на сам прошёл через то, что от лишних серверов можно было отказаться столько раз, что в это трудно поверить. Каждый раз казалось, что другого пути нет. Единственное, с чем приходится бороться в таких случаях — с ленью своей и ленью окружающих.
Важно сделать первый шаг по неоправданной докупке серверов, далее процесс не будет знать предела.
Свора жутких программистов это неуместное понятие в данном случае, весь вопрос в подходе, который должен быть определён изначально.
Я на сам прошёл через то, что от лишних серверов можно было отказаться столько раз, что в это трудно поверить. Каждый раз казалось, что другого пути нет. Единственное, с чем приходится бороться в таких случаях — с ленью своей и ленью окружающих.
Не могли бы Вы написать ещё и примеры использования данных методов? Всегда страдал нехваткой информации в документации. Либо она слишком разбросана, либо методы описаны неявно.
Советы полезные, но хотелось бы плюс к этому увидеть их правильное применение. Возможно, с описанием как делают часто (неправильно) и как нужно делать (правильно).
Спасибо!
Советы полезные, но хотелось бы плюс к этому увидеть их правильное применение. Возможно, с описанием как делают часто (неправильно) и как нужно делать (правильно).
Спасибо!
отписал пониже
За SQL_CALC_FOUND_ROWS и SELECT FOUND_ROWS() спасибо.
Можно я Вас огорчу? Вот тут говорят, что SQL_CALC_FOUND_ROWS работает медленее, чем COUNT(*)
Про enum — реальный прогон. Посмотрите план запроса при склеивании таблиц по enum и по int колонке, попробуйте добавить значение к enum-колонке в таблице и посмотрите, сколько полей обновится.
По-хорошему, enum лучше не пользоваться вообще, лушче пользоваться int.
По-хорошему, enum лучше не пользоваться вообще, лушче пользоваться int.
В том примере (по ссылке в посте) сравнивалось использование varchar( например, 'html', 'plaintext'), enum('html', 'plaintext') и INT, где инт - идентификатор в таблице, где лежат 'html' и 'plaintext'. Первые два случая оказались равны по скорости (примерно), а второй медленнее из-за того, что приходилось делать join. Так что в контексте данного примера - все верно.
С другой стороны, если ситуация позволяет, то лучше действительно использовать int, так как это уменьшает размер таблицы, индекса (если он используется), соответственно повышается скорость. Только в этом случае придётся помнить, что 1, например, - это html, а 2 - это plaintext. И именно это "хардкодить", что не очень правильно, так как люди, читающие Ваш код, ничего не поймут.
Я лично использую INT, но все подробно документирую:)
С другой стороны, если ситуация позволяет, то лучше действительно использовать int, так как это уменьшает размер таблицы, индекса (если он используется), соответственно повышается скорость. Только в этом случае придётся помнить, что 1, например, - это html, а 2 - это plaintext. И именно это "хардкодить", что не очень правильно, так как люди, читающие Ваш код, ничего не поймут.
Я лично использую INT, но все подробно документирую:)
я тебя полностью в это поддерживаю, я так всегда и делаю
enum по сути это и есть int. просто к каждому из int'ов привязано еще и строковое значение, которое хранится в метаданных таблицы.
Сегодня столкнулись с одной очень интересной штуковиной.
Итак есть таблица у которой есть индексы UNIQUE KEY `unique_key` (field1, field2) и KEY `key` (field2).
Так вот в запросе для которого оптимальным ключом был "key", EXPLAIN писал что он и используется, а на самом деле использовался "unique_key" (это было видно и количества "rows"). Вот такая багафича-).
Итак есть таблица у которой есть индексы UNIQUE KEY `unique_key` (field1, field2) и KEY `key` (field2).
Так вот в запросе для которого оптимальным ключом был "key", EXPLAIN писал что он и используется, а на самом деле использовался "unique_key" (это было видно и количества "rows"). Вот такая багафича-).
Да, MySQL к сожалению часто не правильно выбирает нужный индекс, Postres в этом плане получше.
ПС. Если Вы все ещё в поисках решения, то воспользуйтесь FORCE INDEX:)
ПС. Если Вы все ещё в поисках решения, то воспользуйтесь FORCE INDEX:)
Да, нашли это решение. Но воспользовались тем, что просто убрали UNIQUE, не особо он там к месту был-)
Не знаю про Postres, знаю то, что оптимизатор MySQL решает использовать или нет индекс по довольно большому количеству параметров. И чаще всего бывает прав. FORCE INDEX нужно использовать с большой осторожностью. Сегодня один индекс оптимальный, через месяц количество и типы данных в таблице могут измениться и оптимальным будет другой индекс.
1. Это понятно. Я бы удивился, если бы он решал это только по паре параметров.
2. Чаще всего, но не всегда
3. если "сегодня один индекс оптимальный, через месяц другой", то я воспользуюсь "сегодня" explain, посмотрю, и если mysql не использует мой индекс и при использовании force index запрос работает быстрее, то оставлю запрос с force index. "через месяц" при изменении структуры таблицы я снова проделаю туже процедуру.
2. Чаще всего, но не всегда
3. если "сегодня один индекс оптимальный, через месяц другой", то я воспользуюсь "сегодня" explain, посмотрю, и если mysql не использует мой индекс и при использовании force index запрос работает быстрее, то оставлю запрос с force index. "через месяц" при изменении структуры таблицы я снова проделаю туже процедуру.
Если таблица имеет многостолбцовый индекс, то любой крайний слева префикс этого индекса может использоваться оптимизатором для нахождения строк. Например, если имеется индекс по трем столбцам (col1,col2,col3), то существует потенциальная возможность индексированного поиска по (col1), (col1,col2) и (col1,col2,col3).
так что ваш индекс unique_key в данном случае невозможно в принципе
так что ваш индекс unique_key в данном случае невозможно в принципе
Поддерживаю прозьбу MOVe
Тоже хотелось бы живых примеров
не все пункты понятны токо по словам
Тоже хотелось бы живых примеров
не все пункты понятны токо по словам
напишите, пожалуйста, что конкретно не понятно. Постараюсь расписать.
Использование индексов:
> Один из основных параметров, характеризующий индекс - селективность(selectivity) ...
>При использовании составных индексов порядок полей в индексе ...
> Не забывайте про покрывающие(covering) индексы...
> Для индексов по строкам ...
SQL запросы:
>Избегайте запросов в цикле ...
>При постраничном выборе для получения общего количества записей используйте ...
>Используйте INSERT ... ON DUPLICATE KEY UPDATE ...
Вот если можно примерчики хоть к чемуто превести, буду рад и благодарин :)
> Один из основных параметров, характеризующий индекс - селективность(selectivity) ...
>При использовании составных индексов порядок полей в индексе ...
> Не забывайте про покрывающие(covering) индексы...
> Для индексов по строкам ...
SQL запросы:
>Избегайте запросов в цикле ...
>При постраничном выборе для получения общего количества записей используйте ...
>Используйте INSERT ... ON DUPLICATE KEY UPDATE ...
Вот если можно примерчики хоть к чемуто превести, буду рад и благодарин :)
Например, если у нас есть таблица пользователей, содержащая поля Id,Имя, ЛОгин, пароль, status - где статус, это флаг заблокирован пользователь или нет, в принципе он используется в каждом запросе. То вместо одиночного индекса на ID можно сделать индекс и на статус - при этом для подсчета количества заблокированных пользователей, MySQL не будет просматривать всю таблицу, а возьмет значения из составного ключа (Id, Status)
1. Selectivity - количество разных элементов в индексе. Представьте - у вас есть запрос SELECT * FROM table WHERE field = 5. По полю field есть индекс. Чем больше РАЗНЫХ значений в индексе, тем больше будет от него пользы - тем меньше строк останется в выборке после использования индекса. Так вот при выборе индексов нужно страться выбирать индексы с бОльшей выборностью.
2. Есть таблица с полями field1, field2, field3 и есть составной индекс (field1, filed2, field3). Так вот для запроса select * from table where field1=a and field2=b индекс будет использоваться, а для запроса select * from table where field1=a and field3=b использоваться не будет. Подробно об этом расписано в мануале - советую почитать.
3. в статье есть пример :)
4. есть поле name varchar(50). Часто имеет смыл делать индекс alter table table name add index(name(10)). т.е. индексировать не все поле, а только первые скажем 10 букв.
5. запросы в цикле - это зло. часто люди выбирают скажем какие-нибудь основные данные(скажем статистику) и потом в цикле для каждой из строк делают запрос на выборку дополнительных данных из других таблиц.
6. Нужно делать постраничный вывод. Часто делают так: select * from table where ... limit 100, 10, а потом, чтобы узнать общее количество делают select count(*) from table where ... ТАк вот это все можно сделать так: select sql_calc_found_rows * from table where ... limit 100, 10. и потом для получения общего количества сделать SELECT FOUND_ROWS();
7. Часто встает задача - если в таблице есть запись, то проапдейтить поле, а если его нет, то вставить новую запись. Подобную задачу часто решают "в лоб": row = select * from table where ...; if(row) update ... else insert ...; Но решить можно(и нужно по-другому): добавить, если его нет, уникальный ключ определяющий поле и потом сделать inset into table set field1=a, field2=b on duplicate key update set field2=c.
2. Есть таблица с полями field1, field2, field3 и есть составной индекс (field1, filed2, field3). Так вот для запроса select * from table where field1=a and field2=b индекс будет использоваться, а для запроса select * from table where field1=a and field3=b использоваться не будет. Подробно об этом расписано в мануале - советую почитать.
3. в статье есть пример :)
4. есть поле name varchar(50). Часто имеет смыл делать индекс alter table table name add index(name(10)). т.е. индексировать не все поле, а только первые скажем 10 букв.
5. запросы в цикле - это зло. часто люди выбирают скажем какие-нибудь основные данные(скажем статистику) и потом в цикле для каждой из строк делают запрос на выборку дополнительных данных из других таблиц.
6. Нужно делать постраничный вывод. Часто делают так: select * from table where ... limit 100, 10, а потом, чтобы узнать общее количество делают select count(*) from table where ... ТАк вот это все можно сделать так: select sql_calc_found_rows * from table where ... limit 100, 10. и потом для получения общего количества сделать SELECT FOUND_ROWS();
7. Часто встает задача - если в таблице есть запись, то проапдейтить поле, а если его нет, то вставить новую запись. Подобную задачу часто решают "в лоб": row = select * from table where ...; if(row) update ... else insert ...; Но решить можно(и нужно по-другому): добавить, если его нет, уникальный ключ определяющий поле и потом сделать inset into table set field1=a, field2=b on duplicate key update set field2=c.
супер!!! большое человеческое СПС :)
оказывается половина знал из этого не знал что так называетя
оказывается половина знал из этого не знал что так называетя
Спасибо! Конечно, попрактиковаться ещё нужно будет в реальных условиях, но Вы мне ответили на те вопросы, ответы на которые я долго не мог найти.
7. а если нужно узнать существует ли значение в таблице, и если да, то получить его id (auto_increment), и если нет, то insert.
insert into table set field1=a, field2=b on duplicate key select id from table where field1=a
if (!id) id = mysql_insert_id
?
insert into table set field1=a, field2=b on duplicate key select id from table where field1=a
if (!id) id = mysql_insert_id
?
Может быть полезно:
MyProfi - выводит статистику по наиболее частовыполняемым запросам
http://myprofi.sourceforge.net/
Начиная с версии 5.0.37 в MySQL есть встроенный профайлер:
http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
Документация по опции сервера, которая включает вывод информации о самых долгих запросах:
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
MyProfi - выводит статистику по наиболее частовыполняемым запросам
http://myprofi.sourceforge.net/
Начиная с версии 5.0.37 в MySQL есть встроенный профайлер:
http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
Документация по опции сервера, которая включает вывод информации о самых долгих запросах:
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
Почему то еще обошли стороной такую тему как VIEW.
Представления могут быть очень полезны для оптимизации сложных часто повторяющихся запросов.
Если у вас есть запрос с 3-мя и более JOIN и вы его используете часто, то вместо денормализации таблиц лучше использовать VIEW по этим JOIN'ам и производить выборку из этого VIEW.
Зачастую прирост скорости получается очень значительным.
Представления могут быть очень полезны для оптимизации сложных часто повторяющихся запросов.
Если у вас есть запрос с 3-мя и более JOIN и вы его используете часто, то вместо денормализации таблиц лучше использовать VIEW по этим JOIN'ам и производить выборку из этого VIEW.
Зачастую прирост скорости получается очень значительным.
Народ, а кто подскажет есть ли какие курсы по оптимизации mysql в мск или другие близкие курсы по оптимизации?
Лучший и единственно возможный у нас курс — пойти в серъёзную контору с опытными людьми и самому создать там посещаемый проект.
Я про такие курсы не знаю. Думаю, для начала будет достаточно чтения литературы(ее написано немало. На русский, правда, ничего не переводят) ну и опыт конечно...
В апреле на РИТ может приехать Зайцев и Ко - возможно, будет и его очередной семинар.
Позволю себе запиарить онлайн-курсы по оптимизации MySQL, которые мы проводим: habrahabr.ru/blogs/i_am_advertising/46372/
Я бы добавил в статью информацию о том, что в таблице по возможности следует избегать типов данных переменной длины - например, если таблица содержит много полей, то введение одного поля типа VarChar меняет ее тип. Суть в том что запись поля для таблицы с фиксированным типом данных (не содержащих VarChar и т.д.) найти намного быстрее -прибавлением к физическому адресу начала данных места, занимаемому пропущенными строками). То есть если в таблице много данных, а какое-то поле - строка с более-менее маленькими границами (напр пароль 6-10 символов), не стоит делать его VarChar, лучше пожертвовать местом на диске.
И еще, разбиение таблиц с длинной строкой на две, как правило, замедляет скорость. Оно оправдано обычно лишь для того, чтобы разделить одну таблицу на две - одна из которых будет содержать все поля фиксированной длины, а вторая - переменной.
Ну и совет - при большом количестве удаляемых значений, надо запускать периодически служебные команды оптимизации
И еще, разбиение таблиц с длинной строкой на две, как правило, замедляет скорость. Оно оправдано обычно лишь для того, чтобы разделить одну таблицу на две - одна из которых будет содержать все поля фиксированной длины, а вторая - переменной.
Ну и совет - при большом количестве удаляемых значений, надо запускать периодически служебные команды оптимизации
До некоторых пор, я тоже был такого мнения про fixed rows. Однако на практике оказывается все совсем по-другому. Очень мало можно придумать случаев, когда преимущество fixed rows действительно будет использоваться. Дело в том, что поля в таблице могут идти(и чаще всего идут не последовательно). Кому интересно почитайте тему. Там как раз боролись с этими fixed rows.
Интересно что не затронута оптимизация поиска по текстовым полям, хотя в мускуле это реально очень слабое место ;-)
насчет типов данных int-smallint - не согласен, на быстродействие это практически не влияет - только на размер бд.
а вот другая очень хорошая оптимизация не была озвучена - испльзовать char() вместо varchar. поля char хранятся в основной таблице, тогда как - varchar в отдельном сегменте.
а вот другая очень хорошая оптимизация не была озвучена - испльзовать char() вместо varchar. поля char хранятся в основной таблице, тогда как - varchar в отдельном сегменте.
> на быстродействие это практически не влияет - только на размер бд.
размер таблицы в любом случае сказывается на скорости выборок. по поводу char и varchar не согласен. вот тут уже высказывался по этому поводу.
размер таблицы в любом случае сказывается на скорости выборок. по поводу char и varchar не согласен. вот тут уже высказывался по этому поводу.
На счёт сегментов могли бы рассказать подробнее? Я знаю лишь что если все поля в таблице FIXED - желательно выставить полям в типе VARCHAR тип CHAR.
> Я знаю лишь что если все поля в таблице FIXED - желательно выставить полям в типе VARCHAR тип CHAR.
Это не так. Если в таблице есть поля VARCHAR, то тип у нее будет DYNAMIC
Это не так. Если в таблице есть поля VARCHAR, то тип у нее будет DYNAMIC
Я это понимаю =) Я хотел сказать что если все поля фиксированные, а одно поле (или несколько) - VARCHAR , то ему просто необходимо поставить тип CHAR что таблица была не динамичной, а фиксированной.
как раз думал проверить это :) скоро отпишу о результатах.
написал статейку про "VARCHAR просто необходимо поставить тип CHAR"
Спасибо.
Надеюсь, у вас ещё будут посты с конкретным применением SQL (для мюскла) в каких-нибудь сложных или спорных ситуациях (с удовольствием почитал бы).
Надеюсь, у вас ещё будут посты с конкретным применением SQL (для мюскла) в каких-нибудь сложных или спорных ситуациях (с удовольствием почитал бы).
Думаю самый важная тема это производительность mysql, когда понятия нормализации/денормализации и все нормы "морали" (если так можно выразится) уходят из области видимости.
Было бы интересно почитать, про конректные примеры-ситации, когда можно увеличить скорость, жертвуя всем на свете :)
пример: из-за того, что при select, lookup данных для полей не входящих в индекс делается очень медленно при больших объёмах данных, то иногда приходится засовывать нужны поля прямо в индекс, для которых этот индекс и не нужен вовсе, зато скорость существенно увеличивается.
Было бы интересно почитать, про конректные примеры-ситации, когда можно увеличить скорость, жертвуя всем на свете :)
пример: из-за того, что при select, lookup данных для полей не входящих в индекс делается очень медленно при больших объёмах данных, то иногда приходится засовывать нужны поля прямо в индекс, для которых этот индекс и не нужен вовсе, зато скорость существенно увеличивается.
Небольшая статья на тему SQL_CALC_FOUND_ROWS в MySQL: http://valera.ws/2007.08.29~sql_calc_found_rows/
> При использовании составных индексов порядок полей в индексе имеет определяющее значение.
Порядок имеет значение, если запрос типа WHERE a=10 AND b>20
В таком случае при использовании индекса KEY(a,b), он будет использован полностью, а при наличии индекса KEY(b,a) будет использована только первая часть индекса, так как операция сравнения прекращает дальнейший поиск по индексу.
В случае равнозначного запроса WHERE a=10 AND b=15 порядок полей в индексе не имеет значения - аргументы здесь http://sqlinfo.ru/forum/viewtopic.php?id=151#p1520
Добавлю, что MyISAM позволяет создавать отдельные именованные кэши индексов, для кэширования индексов избранных таблиц http://webew.ru/articles/184.webew
Порядок имеет значение, если запрос типа WHERE a=10 AND b>20
В таком случае при использовании индекса KEY(a,b), он будет использован полностью, а при наличии индекса KEY(b,a) будет использована только первая часть индекса, так как операция сравнения прекращает дальнейший поиск по индексу.
В случае равнозначного запроса WHERE a=10 AND b=15 порядок полей в индексе не имеет значения - аргументы здесь http://sqlinfo.ru/forum/viewtopic.php?id=151#p1520
Добавлю, что MyISAM позволяет создавать отдельные именованные кэши индексов, для кэширования индексов избранных таблиц http://webew.ru/articles/184.webew
На тему CALC_FOUND_ROWS
Его стоит использовать осторожно. На практике для запросов, где выборки быстрые из-за индексов (даже если с 5-6 JOIN) CALC_FOUND_ROWS тормозит выполнение запроса по одной простейшей причине - ему нужно выбрать ВСЕ записи (а не LIMIT 20, 20) что-бы знать сколько их всего - это значит что у вас делается запрос без WHERE, записывается кол-во строк и потом фильтруется через WHERE. Так что используйте SELECT COUNT(id) FROM
SQL_CALC_FOUND_ROWS нужно использовать для совсем тяжелых запросов, где происходит сканирование таблицы в любом случае - вот там он идиально работает и не даёт никаког overhead.
Все выше сказанное проверено опытным путём на довольно большом проекте, в котором мы в итоге вырезали с порядка 150 запросов SQL_CALC_FOUND_ROWS оставив его только в небольшой части самых сложных и тяжеловестных запросах. Серверу полегчало раз так в 5-6. Кто не верит - проверьте сами.
Его стоит использовать осторожно. На практике для запросов, где выборки быстрые из-за индексов (даже если с 5-6 JOIN) CALC_FOUND_ROWS тормозит выполнение запроса по одной простейшей причине - ему нужно выбрать ВСЕ записи (а не LIMIT 20, 20) что-бы знать сколько их всего - это значит что у вас делается запрос без WHERE, записывается кол-во строк и потом фильтруется через WHERE. Так что используйте SELECT COUNT(id) FROM
SQL_CALC_FOUND_ROWS нужно использовать для совсем тяжелых запросов, где происходит сканирование таблицы в любом случае - вот там он идиально работает и не даёт никаког overhead.
Все выше сказанное проверено опытным путём на довольно большом проекте, в котором мы в итоге вырезали с порядка 150 запросов SQL_CALC_FOUND_ROWS оставив его только в небольшой части самых сложных и тяжеловестных запросах. Серверу полегчало раз так в 5-6. Кто не верит - проверьте сами.
скажите, а вот при выборке из большой таблице с лимитом скажем limit 3000000, 100
когда уже в конце берется начинает оч долго выбираться… скажите плз что делать? выбирается по полю с индексом
когда уже в конце берется начинает оч долго выбираться… скажите плз что делать? выбирается по полю с индексом
была статья на эту тему: habrahabr.ru/blogs/mysql/44608/
Делаю интернет — аукцион. Есть таблица лотов и таблица ставок.
Для лучшего понимания вот структуры таблиц:
TABLE items
id|name|created|user_id|startptice
TABLE bids
id|item_id|price|user_id
Текущая цена лота — максимальная ставка на этот лот. Для вывода всех лотов использую запрос:
select items.*,max(bids.price),count(bids.id) from items left join bids on bids.item_id = items.id group by items.id
такой запрос получает все лоты + текущую цену(максимальная ставка) + количество ставок
У меня вопрос такой: есть ли смысл сделать сохранение таких рассчетов в таблицу? То есть не каждый раз обсчитывать цену и количество, а просто если добавляем ставку, то изменяем цену и количество ставок в таблице items.
Такой вариант будет проще на выборе данных, но хуже при вставке и апдейте.
Как лучше поступить?
Для лучшего понимания вот структуры таблиц:
TABLE items
id|name|created|user_id|startptice
TABLE bids
id|item_id|price|user_id
Текущая цена лота — максимальная ставка на этот лот. Для вывода всех лотов использую запрос:
select items.*,max(bids.price),count(bids.id) from items left join bids on bids.item_id = items.id group by items.id
такой запрос получает все лоты + текущую цену(максимальная ставка) + количество ставок
У меня вопрос такой: есть ли смысл сделать сохранение таких рассчетов в таблицу? То есть не каждый раз обсчитывать цену и количество, а просто если добавляем ставку, то изменяем цену и количество ставок в таблице items.
Такой вариант будет проще на выборе данных, но хуже при вставке и апдейте.
Как лучше поступить?
меня сейчас волнует процедура update в mysql
у меня довольно часто обновляется поле с датой, по этому полю есть индекс
иногда этот запрос может выполняться 2 сек, но в основном довольно быстро
меня интреерсует как можно было бы избежать таких длинных запросов, это вероятно связано с пересобиранием индекса, лучше наверно аккамулировать даты update table… where id in (1,2,3..10)?
а и еще я думал что если в большинстве ячеек этого поля будет null — индекс будет быстрее пересобираться, но я вроде ошибся. Может еще есть какие-то варианты?
у меня довольно часто обновляется поле с датой, по этому полю есть индекс
иногда этот запрос может выполняться 2 сек, но в основном довольно быстро
меня интреерсует как можно было бы избежать таких длинных запросов, это вероятно связано с пересобиранием индекса, лучше наверно аккамулировать даты update table… where id in (1,2,3..10)?
а и еще я думал что если в большинстве ячеек этого поля будет null — индекс будет быстрее пересобираться, но я вроде ошибся. Может еще есть какие-то варианты?
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Оптимизация работы с MySQL