Оптимизация работы с MySQL

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

    Проблемы при использовании MySQL можно разделить на следующие три группы(в порядке значимости):
    1. Неиспользование или неправильное использование индексов.
    2. Неправильная структура БД.
    3. Неправильные \ неоптимальные SQL запросы.

    Остановимся на каждой из этих групп подробнее.

    Использование индексов.
    Неиспользование или неправильное использование индексов — это то, что чаще всего замедляет запросы. Для тех, кто мало знаком с механизмом работы индексов или еще не читал об этом в мануале, очень советую почитать.
    Советы по использованию индексов:
    • Не нужно индексировать все подряд. Довольно часто, не понимая смысла, люди просто индексируют все поля таблицы. Индексы ускоряют выборки, но замедляют вставки и обновления строк, поэтому выбор каждого индекса должен быть осмыслен.
    • Один из основных параметров, характеризующий индекс — селективность(selectivity) — количество разных элементов в индексе. Нет смысла индексировать поле, в котором два-три возможных значения. Пользы от такого индекса будет мало.
    • Выбор индексов должен начинаться с анализа всех запросов к данной таблице. Очень часто после такого анализа вместо трех-четырех индексов можно сделать один составной.
    • При использовании составных индексов порядок полей в индексе имеет определяющее значение.
    • Не забывайте про покрывающие(covering) индексы. Если все данные в запросе могут быть получены из индекса, то MySQL не будет обращаться непосредственно к таблице. Подобные запросы будут выполняться очень быстро. Например для запроса SELECT name FROM user WHERE login=«test» при наличии индекса (login, name) обращения к таблице не потребуется. Порой имеет смысл добавить в составной индекс дополнительное поле, которое сделает индекс покрывающим и ускорит запросы.
    • Для индексов по строкам часто достаточно индексировать лишь часть строки. Это может значительно уменьшить размер индекса.
    • Если % стоит в начале LIKE(SELECT * FROM table WHERE field LIKE '%test') индексы использоваться не будут.
    • FULLTEXT индекс используется только с синтаксисом MATCH … AGAINST.

    Структура БД.
    Грамотно спроектированная БД — залог быстрой и эффективной работы с базой. С другой стороны, плохо продуманная БД — это всегда головная боль для разработчиков.
    Советы по проектированию БД:
    • Используйте минимально возможные типы данных. Чем больше тип данных, тем больше таблица, тем больше обращений к дискам нужно для получения данных. Используйте очень удобную процедуру: SELECT * FROM table_name PROCEDURE ANALYSE(); для определения минимально возможных типов данных.
    • На этапе проектирования соблюдайте нормальные формы. Часто программисты прибегают к денормализации уже на этом этапе. Однако в большинстве случаев в начале проекта далеко не очевидно чем это может вылиться. Денормализовать таблицу гораздо проще, чем страдать от неоптимально денормализованной. Да и JOIN порой работает быстрее, чем неверно денормализованные таблицы.
    • Не используйте NULL столбцы кроме случаев, когда они вам осознанно нужны.
    SQL запросы.
    • Избегайте запросов в цикле. SQL — язык множеств и к написанию запросов нужно подходить не языком функций, а языком множеств.
    • Избегайте * (звездочки) в запросах. Не поленитесь перечислить именно те поля, которые вы выбираете. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вы действительно выбираете все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочек невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, сегодня в вашей таблице пять INT столбцов, а через месяц добавилось еще одно TEXT и BLOB, а звездочка как была, так и осталась.
    • При постраничном выборе для получения общего количества записей используйте SQL_CALC_FOUND_ROWS и SELECT FOUND_ROWS(); При использовании SQL_CALC_FOUND_ROWS MySQL кеширует выбранное количество строк(до применения LIMIT) и при SELECT FOUND_ROWS() только отдает это закешированное значение без необходимости повторного выполнения запроса.
    • Не забывайте, что у INSERT есть синтаксис для множественной вставки. Один запрос будет выполняться на порядок быстрее, чем множество запросов в цикле.
    • Используйте LIMIT там, где вам не нужны все данные.
    • Используйте INSERT… ON DUPLICATE KEY UPDATE… вместо выборки и INSERT или UPDATE после нее, а также часто вместо REPLACE.
    • Не забывайте про замечательную функцию GROUP_CONCAT. Она может выручить при сложных запросах.


    Средняя зарплата в IT

    113 000 ₽/мес.
    Средняя зарплата по всем IT-специализациям на основании 5 637 анкет, за 2-ое пол. 2020 года Узнать свою зарплату
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 107

      +5
      Дополнение:
      Зачастую при добавление в таблицу, имеющей UNIQUE индекс или PRIMARY KEY, новой строки, очень полезным бывает синтаксис
      INSERT IGNORE. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке,
      то есть сама вставка не будет произведена, при этом не будет прекращенно выполнение.
      Это очень выручает при оптимизации приложения, когда мы вместо двух запросов:
      1- проверить наличие строки в таблице по ключу (SELECT)
      2- вставить строку в случае отсутствия дублирования ключа (INSERT)
      будем использовать только один запорс INSERT IGNORE.
        +1
        На мой взгляд INSERT IGNORE, также, как REPLACE и ON DUPLICATE KEY UPDATE - порой очень опасные конструкции, т.е. их если применять, то в очень узких местах, когда действительно, лишний запрос недопустим, и полностью осознавая, что делаешь.
        За свою практику встречал случаи, когда люди этими конструкциями "затыкали" ошибки в запросах, что приводило потом к очень долгой и проблематичной отладке (т.к. заткнули симптомы, а причина осталась и потом вылезает в самых неожиданных местах).
          0
          как можно REPLACE'ом и ON DUPLICATE KEY UPDATE "затыкать" ошибки?
            0
            Ну к примеру мы считаем статистику, статистика вставляется с ключём "дата", "элемент", статистика за определённый период может пересчитываться (с целью корректировки, скажем, или просто что-то падало и надо восстановить). Соответственно идёт дубликат по первичному ключу, самое простое - REPLACE и не париться. Однако, если в обновлённой статистике элементов стало меньше - то в том, что посчитается будет смесь правильных данных и того, что было. Выход - сначала удалить период за дату, потом вставить - будут чистые данные. Казалось бы, дурацкая ошибка, но я с таким сталкивался.
        +1
        «Нет смысла индексировать поле, в котором два-три возможных значения. Пользы от такого индекса будет мало.» — как раз-таки эти поля и надо индексировать в первую очередь!

        Попробуйсте создайте таблицу на 500 000 записей с колонкой, где будет около 5 разных значений не весь сет и потом сделайте выборку БЕЗ и С индексом. Разница будет очень ощутима.
          0
          да изменения в скорости будут, но фулл скан по 100000 записей это в любом случае - не лучший вариант.
            0
            при select * с индексом скорость будет значительно меньше чем при запросе без индекса.
              0
              Согласен. Проверено! Особенно они (эти индексы) отлично "отрезают" лишнее при JOIN. (Кстати mysql сам прекрасно знает в этих случаях какой инекс выбрать в зависимости от кол-ва записей) А в составном индексе вообще...
              Просто почаще надо пользоваться Explain :) и анализировать в каждом конкретном случае., особенно на больших базах.
                0
                Попробовал на 1 000 000 записей. Разницы вообще нет.
                0
                По возможности используйте INSERT DELAYED
                  0
                  он работает только на MyISAM таблицах :(
                  использование InnoDB, кстати, даёт ощутимый прирост скорости на выборках
                    0
                    > использование InnoDB, кстати, даёт ощутимый прирост скорости на выборках
                    ох и спорное это высказывание. Вот здесь например есть тесты. Заметьте, что при нагрузках < 10000 запросов\сек разницы между InnoDB и MyISAM практически нет.
                  +4
                  при работе с большими объёмами данных, Limit спасает только частично. Если Вы укажите limit 10000, 20, то mysql будет бежать с самого начала таблицы, пока не насчитает 10000, и только тогда считает 20 записей. В простейшем случае этого можно избежать, запомнив, какой id мы выводили последним, и в условие добавить id>последний_ид limit 20. Если требуется сортировка по другим полям, то нужно создать "правильный" индекс по этим полям и в условиях явно указать, что больше чего, что меньше чего. Правда этим способом будет сложнее вывести "пагинатор" "1 2 3 4 5" и тд. Но учитывая то, что пользователи редко ходят дальше нескольких страниц, то вполне будет достаточно "следующая последняя".
                    0
                    Полностью согласен. В статье только писалось о том, что не нужно выбирать записей больше, чем требуется.
                    0
                    по-моему :

                    4. Использование SQL там где можно вполне обойтись ORM. Т.е. в большинстве приложений на ruby, python, и php. Переложить составление sql-запросов на компьютер, а он уже будет паковать выборки в более крупные\оптимизированные
                      0
                      здесь речь идёт об оптимизации работы с MySQL, а ORM годится для составления только простых запросов типа select * from ... (максимум, JOIN сделать) и никакой оптимизации таким способом Вы не добьётесь. Вы где-нибудь видели ORM, который использует INSERT IGNORE или ON DUPLICATE KEY UPDATE?
                        +1
                        иначе бы ORM просто не было. курите http://www.continuousthinking.com/are/activerecord-extensions-0-0-5 к примеру.
                          +1
                          с INSERT IGNORE и ON DUPLICATE KEY UPDATE был не удачный пример. То, что Вы напишете с помощью ORM и то, что напишете сами, будет одним и тем же, только на разных языках. Я своим комментарием хотел сказать лишь то, что использование ORM не прибавит никакой оптимальности Вашим запросам. ORM не сможет создать нужные индексы или выбрать правильный для использования, а если и преобразует "*" во все поля из таблицы, то перед этим выполнит "desc tablename".
                            0
                            Ну desc tablename она не обязана каждый раз выполнять, но в целом согласен - ORM обчно не може ничего оптимизировать. Хотя бы потому, что не располагает сведениями о частоте тех или иных запросов.
                              0
                              как так не располагает? почему не располагает? ваши запросы идут через ORM, верно? данные о ваших запросах есть, верно?.. что же мешает мапперу хранить результаты последних \ частых выборок в кеше и возвращать этот кеш при повторяющихся запросах? так поступает например тот же ActiveRecord.

                              я отказываюсь понимать этих людей.
                                0
                                Во-первых, этот кэш нужен только тогда, когда Вы получили некоторые данные, а потом забыли о том, что они у Вас уже есть и пытаетесь получить второй раз. Во-вторых, этот кэш действителен только на время выполнения текущего процесса и если Вам нужно где-то что-то сохранить, то для этого выгоднее использовать что-то вроде memcached, но это уже совсем другая история.
                                  +1
                                  1. При чем здесь кеш? Мы говорим об оптимизации запросов, а не об ускорении их исполнения.

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

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

                                  я отказываюсь понимать этих людей

                                  Зря, пытаться понять человека иногда очень даже полезно ;)
                                    0
                                    >Логичнее использовать отношение популярность/вес для определения того, что надо кешировать.

                                    согласен, логичнее. вот и напишите плагин\переопределите класс для ORM. в изначальном посте я говорил о вариантах, где писать лапшу SQL не обязательно, т.к. запросы довольно несложные и в оптимизации человеческим существом не нуждаются
                                      0
                                      Я так и делаю. Привет от Limb::ActiveRecord
                                      Только это не снимает проблем многопоточности ;)
                                0
                                простейший пример - вы делаете несколько выборок в своей функции ( с циклом пример не буду приводить, это уже слишком очевидно ). 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 )
                                  0
                                  И что здесь оптимизирует ORM, если мне вручную нужно указывать :include => :somthing?
                                    0
                                    Вам вручную надо указать, "что вам нужно", а как - в данном случае решит ORM.
                                      0
                                      К сожалению она ничего не решит, а просто засунет в запрос JOIN. Оптимизации - 0. Только что писать иногда быстрее.
                                        0
                                        Вы сейчас о каком-то конкретном ORM'е говорите? Я говорил о потенциальной возможности eager fetcher'а
                                          0
                                          Я работал с ActiveRecord.
                                          Допустим я указываю, как Вы говорите "что мне нужно", а какие есть варианты у ORM, "как" мне это дать? Как он может оптимизировать этот момент?
                                            0
                                            ActiveRecord это паттерн. У него есть куча реализаций на разных языках.

                                            Допустим я указываю, как Вы говорите "что мне нужно", а какие есть варианты у ORM, "как" мне это дать? Как он может оптимизировать этот момент?

                                            Не делать одинаковых запросов. Например, через использование UoW.
                                              0
                                              Извиняюсь, говорил об ActiveRecord в Rails.
                                              По поводу не делать одинаковых запросов - Вы про кэш? Если да, то выше Вы же сами написали отличный комментарий по этому поводу. Здесь больше всего подойдёт пункт 1 из него.
                                              Если речь о чем-то другом (к сожалению на UoW гугл выдаёт University of Wollongong и ничего по теме), то извиняйте.
                                                0
                                                Извиняюсь, непонятно написал.

                                                Выше говорилось о прозрачном кеше, значения которого будут отдаваться пользователям ORM. Я же имел ввиду внутренний "кеш" (на самом деле это не совсем кеш, т.к. работает не только с получением данных) ORM'а, который будет использоваться, например, для того чтобы не делать два update'а для одной записи.

                                                UnitOfWork(UoW)
                            0
                            Если делать серьезный проект, не с таким подходом, как авто.ру и его 80 серверов, то ++каждый++ новый запрос, отличный от выбора по первичному ключу, требует персонального ++человеческого++ рассмотрения.
                              0
                              Зачем им 80 серверов??? о_О
                                0
                                Воздух гонять, думается
                                0
                                "то ++каждый++ новый запрос" хех, я бы посмотрел как это будет происходить. Вы понимаете не реально это, для проекта такого масштаба.

                                1.вопрос такой - что лучше нанять 2-5 программистов + в случае чего сервера докупать
                                2.либо нанять свору жутко умных программистов, которым нужно платить кучу денег.

                                Любой бизнесмен выберет более дешёвый и стабильный пункт 1.
                                а оптимизировать sql и всё остальное можно вечно.
                                  0
                                  Потерять данные, вверенные неопрятным неучам, распределённые в рамках неоправданно разросшейся системы — бесценно.

                                  Важно сделать первый шаг по неоправданной докупке серверов, далее процесс не будет знать предела.

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

                                  Я на сам прошёл через то, что от лишних серверов можно было отказаться столько раз, что в это трудно поверить. Каждый раз казалось, что другого пути нет. Единственное, с чем приходится бороться в таких случаях — с ленью своей и ленью окружающих.
                              0
                              Не могли бы Вы написать ещё и примеры использования данных методов? Всегда страдал нехваткой информации в документации. Либо она слишком разбросана, либо методы описаны неявно.
                              Советы полезные, но хотелось бы плюс к этому увидеть их правильное применение. Возможно, с описанием как делают часто (неправильно) и как нужно делать (правильно).
                              Спасибо!
                              +1
                              За SQL_CALC_FOUND_ROWS и SELECT FOUND_ROWS() спасибо.
                                0
                                Можно я Вас огорчу? Вот тут говорят, что SQL_CALC_FOUND_ROWS работает медленее, чем COUNT(*)
                                  0
                                  Заметьте, что в этом как раз используется покрывающий индекс, а на практике такое бывает далеко не всегда.
                                    –1
                                    ...в некоторых случаях.
                                  0
                                  Про enum — реальный прогон. Посмотрите план запроса при склеивании таблиц по enum и по int колонке, попробуйте добавить значение к enum-колонке в таблице и посмотрите, сколько полей обновится.

                                  По-хорошему, enum лучше не пользоваться вообще, лушче пользоваться int.
                                    0
                                    В том примере (по ссылке в посте) сравнивалось использование varchar( например, 'html', 'plaintext'), enum('html', 'plaintext') и INT, где инт - идентификатор в таблице, где лежат 'html' и 'plaintext'. Первые два случая оказались равны по скорости (примерно), а второй медленнее из-за того, что приходилось делать join. Так что в контексте данного примера - все верно.
                                    С другой стороны, если ситуация позволяет, то лучше действительно использовать int, так как это уменьшает размер таблицы, индекса (если он используется), соответственно повышается скорость. Только в этом случае придётся помнить, что 1, например, - это html, а 2 - это plaintext. И именно это "хардкодить", что не очень правильно, так как люди, читающие Ваш код, ничего не поймут.
                                    Я лично использую INT, но все подробно документирую:)
                                      0
                                      Всё решается просто с помощью define или const</strong
                                        0
                                        > так как это уменьшает размер таблицы
                                        вы уверены? Боюсь вас огорчить, но это не чаще всего не так. INT - 4 байта. ENUM с количиством элементов < 8 - 1 байт.
                                          0
                                          Спасибо за поправку. Вместо INT хотел сказать TINYINT. А про то, как ENUM хранится физически - не знал, спасибо. Принял к сведению.
                                        0
                                        я тебя полностью в это поддерживаю, я так всегда и делаю
                                          0
                                          enum по сути это и есть int. просто к каждому из int'ов привязано еще и строковое значение, которое хранится в метаданных таблицы.
                                            0
                                            по сути — да, а на практике при сложных склеиваниях по нескольким колонкам — нет
                                          0
                                          Сегодня столкнулись с одной очень интересной штуковиной.
                                          Итак есть таблица у которой есть индексы UNIQUE KEY `unique_key` (field1, field2) и KEY `key` (field2).
                                          Так вот в запросе для которого оптимальным ключом был "key", EXPLAIN писал что он и используется, а на самом деле использовался "unique_key" (это было видно и количества "rows"). Вот такая багафича-).
                                            0
                                            Да, MySQL к сожалению часто не правильно выбирает нужный индекс, Postres в этом плане получше.
                                            ПС. Если Вы все ещё в поисках решения, то воспользуйтесь FORCE INDEX:)
                                              0
                                              Да, нашли это решение. Но воспользовались тем, что просто убрали UNIQUE, не особо он там к месту был-)
                                                0
                                                Не знаю про Postres, знаю то, что оптимизатор MySQL решает использовать или нет индекс по довольно большому количеству параметров. И чаще всего бывает прав. FORCE INDEX нужно использовать с большой осторожностью. Сегодня один индекс оптимальный, через месяц количество и типы данных в таблице могут измениться и оптимальным будет другой индекс.
                                                  0
                                                  1. Это понятно. Я бы удивился, если бы он решал это только по паре параметров.
                                                  2. Чаще всего, но не всегда
                                                  3. если "сегодня один индекс оптимальный, через месяц другой", то я воспользуюсь "сегодня" explain, посмотрю, и если mysql не использует мой индекс и при использовании force index запрос работает быстрее, то оставлю запрос с force index. "через месяц" при изменении структуры таблицы я снова проделаю туже процедуру.
                                                0
                                                Если таблица имеет многостолбцовый индекс, то любой крайний слева префикс этого индекса может использоваться оптимизатором для нахождения строк. Например, если имеется индекс по трем столбцам (col1,col2,col3), то существует потенциальная возможность индексированного поиска по (col1), (col1,col2) и (col1,col2,col3).

                                                так что ваш индекс unique_key в данном случае невозможно в принципе
                                                  0
                                                  Вы не поняли суть поста. Я прекрасно знаю, что этот индекс тут не в тему! Проблема даже не в том что mysql не выбирал оптимальный индекс. ПРОБЛЕМА в том что Explain показывал не то что на самом деле. Т.е. explain говорил что использовался key, а в реальности использовался unique_key.
                                                    0
                                                    я хотел сказать что использование индекса unique_key в данном случае невозможно в принципе.
                                                0
                                                Поддерживаю прозьбу MOVe
                                                Тоже хотелось бы живых примеров
                                                не все пункты понятны токо по словам
                                                  0
                                                  напишите, пожалуйста, что конкретно не понятно. Постараюсь расписать.
                                                    0
                                                    Использование индексов:
                                                    > Один из основных параметров, характеризующий индекс - селективность(selectivity) ...
                                                    >При использовании составных индексов порядок полей в индексе ...
                                                    > Не забывайте про покрывающие(covering) индексы...
                                                    > Для индексов по строкам ...

                                                    SQL запросы:
                                                    >Избегайте запросов в цикле ...
                                                    >При постраничном выборе для получения общего количества записей используйте ...
                                                    >Используйте INSERT ... ON DUPLICATE KEY UPDATE ...

                                                    Вот если можно примерчики хоть к чемуто превести, буду рад и благодарин :)
                                                      0
                                                      Например, если у нас есть таблица пользователей, содержащая поля Id,Имя, ЛОгин, пароль, status - где статус, это флаг заблокирован пользователь или нет, в принципе он используется в каждом запросе. То вместо одиночного индекса на ID можно сделать индекс и на статус - при этом для подсчета количества заблокированных пользователей, MySQL не будет просматривать всю таблицу, а возьмет значения из составного ключа (Id, Status)
                                                        +1
                                                        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.
                                                          0
                                                          супер!!! большое человеческое СПС :)

                                                          оказывается половина знал из этого не знал что так называетя
                                                          • НЛО прилетело и опубликовало эту надпись здесь
                                                              0
                                                              Спасибо! Конечно, попрактиковаться ещё нужно будет в реальных условиях, но Вы мне ответили на те вопросы, ответы на которые я долго не мог найти.
                                                                0
                                                                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

                                                                ?
                                                                  0
                                                                  такое одним запросом не сделать.
                                                                    0
                                                                    спасибо, так и делаю двумя запросами.
                                                          0
                                                          Может быть полезно:

                                                          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
                                                            0
                                                            Почему то еще обошли стороной такую тему как VIEW.
                                                            Представления могут быть очень полезны для оптимизации сложных часто повторяющихся запросов.
                                                            Если у вас есть запрос с 3-мя и более JOIN и вы его используете часто, то вместо денормализации таблиц лучше использовать VIEW по этим JOIN'ам и производить выборку из этого VIEW.
                                                            Зачастую прирост скорости получается очень значительным.
                                                              0
                                                              Да, много каких тем не затронул. Здесь хотел лишь собрать вместе совсем не сложные советы. Если писать обо всем - это можно браться за книгу :)
                                                                0
                                                                А почему бы и нет? Ж)
                                                                  0
                                                                  пока еще нужен кликам :)
                                                              0
                                                              Народ, а кто подскажет есть ли какие курсы по оптимизации mysql в мск или другие близкие курсы по оптимизации?
                                                                +3
                                                                Лучший и единственно возможный у нас курс — пойти в серъёзную контору с опытными людьми и самому создать там посещаемый проект.
                                                                  0
                                                                  Я про такие курсы не знаю. Думаю, для начала будет достаточно чтения литературы(ее написано немало. На русский, правда, ничего не переводят) ну и опыт конечно...
                                                                    0
                                                                    В апреле на РИТ может приехать Зайцев и Ко - возможно, будет и его очередной семинар.
                                                                      0
                                                                      Позволю себе запиарить онлайн-курсы по оптимизации MySQL, которые мы проводим: habrahabr.ru/blogs/i_am_advertising/46372/
                                                                      0
                                                                      Я бы добавил в статью информацию о том, что в таблице по возможности следует избегать типов данных переменной длины - например, если таблица содержит много полей, то введение одного поля типа VarChar меняет ее тип. Суть в том что запись поля для таблицы с фиксированным типом данных (не содержащих VarChar и т.д.) найти намного быстрее -прибавлением к физическому адресу начала данных места, занимаемому пропущенными строками). То есть если в таблице много данных, а какое-то поле - строка с более-менее маленькими границами (напр пароль 6-10 символов), не стоит делать его VarChar, лучше пожертвовать местом на диске.

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

                                                                      Ну и совет - при большом количестве удаляемых значений, надо запускать периодически служебные команды оптимизации
                                                                        0
                                                                        До некоторых пор, я тоже был такого мнения про fixed rows. Однако на практике оказывается все совсем по-другому. Очень мало можно придумать случаев, когда преимущество fixed rows действительно будет использоваться. Дело в том, что поля в таблице могут идти(и чаще всего идут не последовательно). Кому интересно почитайте тему. Там как раз боролись с этими fixed rows.
                                                                          0
                                                                          Причем тут боролись с фиксед роус :D Вряд ли тот, у кого есть таблица в 4 млн записей, нуждается в ознакомительных советах. Каждый случай - частный
                                                                            0
                                                                            так я как раз и боролся :)
                                                                            0
                                                                            Верно... чем меньше таблица тем быстрее запросы... денормализируйте одну таблицу на 2 fixed и dynamic
                                                                          0
                                                                          Интересно что не затронута оптимизация поиска по текстовым полям, хотя в мускуле это реально очень слабое место ;-)
                                                                            0
                                                                            вы о fulltext индексах?
                                                                              0
                                                                              fulltext индексы в БД большого объема особо не спасают.
                                                                              Я имел ввиду расширения типа сфинкса(Sphinx).
                                                                                0
                                                                                ну это точно не тема подобной статьи
                                                                            0
                                                                            насчет типов данных int-smallint - не согласен, на быстродействие это практически не влияет - только на размер бд.
                                                                            а вот другая очень хорошая оптимизация не была озвучена - испльзовать char() вместо varchar. поля char хранятся в основной таблице, тогда как - varchar в отдельном сегменте.
                                                                              0
                                                                              > на быстродействие это практически не влияет - только на размер бд.
                                                                              размер таблицы в любом случае сказывается на скорости выборок. по поводу char и varchar не согласен. вот тут уже высказывался по этому поводу.
                                                                                0
                                                                                На счёт сегментов могли бы рассказать подробнее? Я знаю лишь что если все поля в таблице FIXED - желательно выставить полям в типе VARCHAR тип CHAR.
                                                                                  0
                                                                                  > Я знаю лишь что если все поля в таблице FIXED - желательно выставить полям в типе VARCHAR тип CHAR.
                                                                                  Это не так. Если в таблице есть поля VARCHAR, то тип у нее будет DYNAMIC
                                                                                    0
                                                                                    Я это понимаю =) Я хотел сказать что если все поля фиксированные, а одно поле (или несколько) - VARCHAR , то ему просто необходимо поставить тип CHAR что таблица была не динамичной, а фиксированной.
                                                                                      0
                                                                                      как раз думал проверить это :) скоро отпишу о результатах.
                                                                                        0
                                                                                        написал статейку про "VARCHAR просто необходимо поставить тип CHAR"
                                                                                0
                                                                                Спасибо.
                                                                                Надеюсь, у вас ещё будут посты с конкретным применением SQL (для мюскла) в каких-нибудь сложных или спорных ситуациях (с удовольствием почитал бы).
                                                                                  0
                                                                                  Думаю самый важная тема это производительность mysql, когда понятия нормализации/денормализации и все нормы "морали" (если так можно выразится) уходят из области видимости.
                                                                                  Было бы интересно почитать, про конректные примеры-ситации, когда можно увеличить скорость, жертвуя всем на свете :)

                                                                                  пример: из-за того, что при select, lookup данных для полей не входящих в индекс делается очень медленно при больших объёмах данных, то иногда приходится засовывать нужны поля прямо в индекс, для которых этот индекс и не нужен вовсе, зато скорость существенно увеличивается.
                                                                                    0
                                                                                    Небольшая статья на тему SQL_CALC_FOUND_ROWS в MySQL: http://valera.ws/2007.08.29~sql_calc_found_rows/
                                                                                      0
                                                                                      > При использовании составных индексов порядок полей в индексе имеет определяющее значение.

                                                                                      Порядок имеет значение, если запрос типа 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
                                                                                        +1
                                                                                        На тему 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. Кто не верит - проверьте сами.
                                                                                          0
                                                                                          да, все верно.
                                                                                          0
                                                                                          скажите, а вот при выборке из большой таблице с лимитом скажем limit 3000000, 100
                                                                                          когда уже в конце берется начинает оч долго выбираться… скажите плз что делать? выбирается по полю с индексом
                                                                                          0
                                                                                          Делаю интернет — аукцион. Есть таблица лотов и таблица ставок.
                                                                                          Для лучшего понимания вот структуры таблиц:
                                                                                          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.
                                                                                          Такой вариант будет проще на выборе данных, но хуже при вставке и апдейте.

                                                                                          Как лучше поступить?
                                                                                            0
                                                                                            меня сейчас волнует процедура update в mysql
                                                                                            у меня довольно часто обновляется поле с датой, по этому полю есть индекс
                                                                                            иногда этот запрос может выполняться 2 сек, но в основном довольно быстро
                                                                                            меня интреерсует как можно было бы избежать таких длинных запросов, это вероятно связано с пересобиранием индекса, лучше наверно аккамулировать даты update table… where id in (1,2,3..10)?
                                                                                            а и еще я думал что если в большинстве ячеек этого поля будет null — индекс будет быстрее пересобираться, но я вроде ошибся. Может еще есть какие-то варианты?

                                                                                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                                            Самое читаемое