Пользователь
0,0
рейтинг
9 октября 2008 в 23:37

Разработка → Оптимизация MySQL запросов

В повседневной работе приходится сталкиваться с довольно однотипными ошибками при написании запросов.

В этой статье хотелось бы привести примеры того, как НЕ надо писать запросы.
  • Выборка всех полей
    SELECT * FROM table

    При написании запросов не используйте выборку всех полей — "*". Перечислите только те поля, которые вам действительно нужны. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вам на самом деле необходимы все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочки невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, со временем количество столбцов в вашей таблице может изменяться, и если сегодня это пять INT столбцов, то через месяц могут добавиться TEXT и BLOB поля, которые будут замедлять выборку.

  • Запросы в цикле.
    Нужно четко представлять себе, что SQL — язык, оперирующий множествами. Порой программистам, привыкшим думать терминами процедурных языков, трудно перестроить мышление на язык множеств. Это можно сделать довольно просто, взяв на вооружение простое правило — «никогда не выполнять запросы в цикле». Примеры того, как это можно сделать:

    1. Выборки
    $news_ids = get_list('SELECT news_id FROM today_news ');
    while($news_id = get_next($news_ids))
    $news[] = get_row('SELECT title, body FROM news WHERE news_id = '. $news_id);

    Правило очень простое — чем меньше запросов, тем лучше (хотя из этого, как и из любого правила, есть исключения). Не забывайте про конструкцию IN(). Приведенный код можно написать одним запросом:
    SELECT title, body FROM today_news INNER JOIN news USING(news_id)

    2. Вставки
    $log = parse_log();
    while($record = next($log))
    query('INSERT INTO logs SET value = '. $log['value']);

    Гораздо более эффективно склеить и выполнить один запрос:
    INSERT INTO logs (value) VALUES (...), (...)

    3. Обновления
    Иногда бывает нужно обновить несколько строк в одной таблице. Если обновляемое значение одинаковое, то все просто:
    UPDATE news SET title='test' WHERE id IN (1, 2, 3).

    Если изменяемое значение для каждой записи разное, то это можно сделать таким запросом:
    UPDATE news SET
    title = CASE
    WHEN news_id = 1 THEN 'aa'
    WHEN news_id = 2 THEN 'bb' END
    WHERE news_id IN (1, 2)

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

  • Выполнение операций над проиндексированными полями
    SELECT user_id FROM users WHERE blogs_count * 2 = $value

    В таком запросе индекс использоваться не будет, даже если столбец blogs_count проиндексирован. Для того, чтобы индекс использовался, над проиндексированным полем в запросе не должно выполняться преобразований. Для подобных запросов выносите функции преобразования в другую часть:
    SELECT user_id FROM users WHERE blogs_count = $value / 2;

    Аналогичный пример:
    SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) — TO_DAYS(registered) <= 10;

    не будет использовать индекс по полю registered, тогда как
    SELECT user_id FROM users WHERE registered >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
    будет.

  • Выборка строк только для подсчета их количества
    $result = mysql_query(«SELECT * FROM table», $link);
    $num_rows = mysql_num_rows($result);
    Если вам нужно выбрать количество строк, удовлетворяющих определенному условию, используйте запрос SELECT COUNT(*) FROM table, а не выбирайте все строки лишь для того, чтобы подсчитать их количество.

  • Выборка лишних строк
    $result = mysql_query(«SELECT * FROM table1», $link);
    while($row = mysql_fetch_assoc( $result) && $i < 20) {

    }
    Если вам нужны только n строк выборки, используйте LIMIT, вместо того, чтобы отбрасывать лишние строки в приложении.

  • Использование ORDER BY RAND()
    SELECT * FROM table ORDER BY RAND() LIMIT 1;

    Если в таблице больше, чем 4-5 тысяч строк, то ORDER BY RAND() будет работать очень медленно. Гораздо более эффективно будет выполнить два запроса:

    Если в таблице auto_increment'ный первичный ключ и нет пропусков:
    $rnd = rand(1, query('SELECT MAX(id) FROM table'));
    $row = query('SELECT * FROM table WHERE id = '.$rnd);

    либо:
    $cnt = query('SELECT COUNT(*) FROM table');
    $row = query('SELECT * FROM table LIMIT '.$cnt.', 1');
    что, однако, так же может быть медленным при очень большом количестве строк в таблице.

  • Использование большого количества JOIN'ов
    SELECT
        v.video_id
        a.name,
        g.genre
    FROM
        videos AS v
    LEFT JOIN
        link_actors_videos AS la ON la.video_id = v.video_id
    LEFT JOIN
        actors AS a ON a.actor_id = la.actor_id
    LEFT JOIN
        link_genre_video AS lg ON lg.video_id = v.video_id
    LEFT JOIN
        genres AS g ON g.genre_id = lg.genre_id

    Нужно помнить, что при связи таблиц один-ко многим количество строк в выборке будет расти при каждом очередном JOIN'е. Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.

  • Использование LIMIT
    SELECT… FROM table LIMIT $start, $per_page

    Многие думают, что подобный запрос вернет $per_page записей (обычно 10-20) и поэтому сработает быстро. Он и сработает быстро для нескольких первых страниц. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет. Многие просто ограничивают количество доступных страниц разумным числом. Также можно ускорить подобные запросы использованием покрывающих индексов или сторонних решений (например sphinx).

  • Неиспользование ON DUPLICATE KEY UPDATE
    $row = query('SELECT * FROM table WHERE id=1');

    if($row)
        query('UPDATE table SET column = column + 1 WHERE id=1')
    else
        query('INSERT INTO table SET column = 1, id=1');

    Подобную конструкцию можно заменить одним запросом, при условии наличия первичного или уникального ключа по полю id:
    INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

Читайте оригинал статьи на MySQL Consulting.

P.S. пишите в личку темы статей по MySQL, которые вы хотели бы прочитать.

Евгений @tuta_larson
карма
52,3
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (142)

  • +4
    при написании запросов не используйте выборку всех полей — "*".
    об этом много споров…
    некоторые говорят что иногда, при малом количестве полей в таблице, select * быстрее…
    • +6
      Думаю, что про то, что перечесление полей или звездочка работают быстрее друг друга говорить не стоит. Отличия если и есть, то они будут минимальными. А вот то, что вы получите замедление при выборке ненужных данных — это факт.
      • +2
        Вообще, переписывать все поля занятие довольно унылое, особенно если их больше 50. Хорошо бы придумать что-то, чтобы об этом не думать. Например автоматизировать, используя один из объектых SQL мапперов или вынести BLOB и TEXT в отдельные таблицы и подключать только при необходимости.

        Но перечисление полей — это непозволительная растрата времени программиста :)
        • +2
          Вы не правы потому, что если так не делать — позже при изменениях в структуре базы данных/таблиц программист затратит _гораздо_ больше времени на то, сначала понять, что проблема в неправильном запросе, а затем еще на то чтобы переписать его правильно.

          Не следует путать понятия «лень» и «бессмысленная трата времени». В данном совете трата времени весьма осмысленна и полезна.
          • +1
            Вы немного не поняли о чем я. Я там наже ещё написал.

            Да и вообще, когда у вас будет 180 таблиц и 700 видов запросов, звездочки — это последнее о чем вы будете думать… :)
          • 0
            а можно, пожалуйста, пример порчи запроса при использовании *?
        • 0
          50 и более столбцов в таблице

          я, конечно, не исключаю, что где то это необходимо, но в общем случае это повод для изменения архитектуры БД.

          P.S.
          Раскрывать звездочки налету такая же сомнительная идея…
          Это надо делать раз — при написании запроса, я это так и делаю :) (Delphi+FIBPlus)
          • +1
            я, конечно, не исключаю, что где то это необходимо, но в общем случае это повод для изменения архитектуры БД.


            C чего бы? Взять хотя бы человека — сколько у него может быть «свойств» = колонок в таблице? имя, фамилия, адрес, почтовый индекс, номер ИНН, телефон, icq, skype… дальше продолжать?
            • –1
              Вот это тот самый показательный пример.
              Адрес — это отдельная сущность. Есть адрес регистрации, а есть адрес проживания.
              Будем сразу делать нормальную структуру или дальше лепить поля street1 b street2?
              • +2
                Ну как вам сказать. Если в моем проекте адрес выступает исключительно в привязке к человеку, а не как самостоятельная единица — то он — свойство, а не сущность. Конечно — можно во все проекты вгружать КЛАДР и еще устраивать проверки на валидность + делать объединения (не нужные совершенно).

                Я никогда не считал, что разделение на сущности должно быть бездумным. «Имя» — это тоже отдельная сущность в каком то смысле, да и icq тоже — где грань? Можно все их разбить посущностно и вместо одной таблицы с 50 полями, получить 50 таблиц с кучей JOIN-ов.
            • –1
              Предлагаю вам решение))

              CREATE TABLE userinfo (
              id INTEGER AUTO_INCREMENT,
              user_id INTEGER, /* наверно надо сделать ключом, поправьте меня*/
              field_type INTEGER, /* тип поля, 1=имя, 2=фамилия, и т. д. */
              field_value TEXT
              );

              Как насчет такого?
              • 0
                гемор с разворачиванием в строку, например, надо получить «Иванов И. И.»
                • 0
                  Ну это видимо в шаблонизаторе:
                  {$person.name} {$person.surname|first_letter} и так далее
              • +1
                Можно. Мне не нравится, потому что работать будет медленнее, чем 1 таблица :)

                а вообще рассматривать общие подходы без привязки к конкретным данным и задачам — квинтэссенция бреда, имхо :)

                Я просто считаю утверждение, что если полей в таблице 50 — то нужно перепроектировать БД, не видя данных и не зная задач — неверным (очень мягко говоря)
              • 0
                Торомозить на выборках будет жутко. А для веб-приложений это критично (mysql в основном же в веб-приложениях используется).
                Как учили нас в университетах процессу «нормализации» БД, так сейчас приходится выдумывать способы «денормализации», бо очень важной становится скорость выборок.
          • 0
            Ну, там где это повод для изменения архитектуры к чему мы прийдем? К 20 полям вместо 50 и к 30 именам таблиц в джоинах вместо 3-х? :) Да и поля из подключаемх таблиц перечислять всеравно придется… итого. вместо 50 получаем получим все 80…

            Понятно, что я утрирую, но я и начинал не о том, что записывать поля не нужно, а о том, что в некоторых разросшихся проектах, писать запросы вручную целиком нужно только в очень редких случаях.

            А в остальных случях рациональнее использовать автоматические рендеры (их много разных), и править только их критичные куски. Это экономит _очень_ много времени, в том числе и при изменениях в структуре, о которой говорил предыдущий оратор.
        • 0
          изза таких как ты админы мучаются с внредрением новых аппаратных средств, а канторы тратят лишние десятки тысяч долларов. Ты столько за год не зарабатываешь. Подумай что дороже. 5 минут твоего драгоценного времени или новый сервак под БД?
        • 0
          Выносить блобы в отдельные таблицы нужно по другим причинам… хотя и по этой тоже. Это слишком разнородные данные, обрабатываемые по-разному.

          Касательно * — а работайте через phpMyAdmin, он лимит ставит :) В коде же работающей программы такое допустимо только для небольших справочников.
  • 0
    В основном все простое, но некоторые вещи для себя переосмыслил.
    Подозреваю что в "$cnt = query('SELECT COUNT(*) FROM table');" вкралась ошибка, в таком варианте всегда будет выводиться одна и та же запись.
    И вопрос на туже тему, «Если в таблице auto_increment'ный первичный ключ и нет пропусков» — вы же понимаете что это единичные случаи. Всегда есть пропуски. Что делать? Имеет ли смысл держать отдельную таблицу с `id` только для выборки случайного значения по ней? Есть другие варианты?
    • +1
      imho, так будет лучше:
      Если в таблице auto_increment'ный первичный ключ и ВОЗМОЖНЫ пропуски:
      $rnd = rand(1, query('SELECT MAX(id) FROM table'));
      $row = query('SELECT * FROM table WHERE id >= '.$rnd.' LIMIT 1');

      у меня есть свой вопрос: а как эфективно выбрать случайные M из N записей? например 10 случайных статей из 10 000? я не знаю как это сделать за меньше чем M+1 запросов :(
      • 0
        Сгенерируйте M случайных id в пределах N. Если есть пропуски, то чуть больше: K=M*2 (больше или меньше, если пропусков много). Тогда запрос будет один:
        select * from table where id in (1, 2, 3,… K) limit M;
        • 0
          это подходит, если меня устроит негарантированные M записей.
          т. к. IN () ищет точное совпадение, реально на выходе я могу получить пустое множество
          • 0
            Тут как раз наиболее оптимальный наверно мой способ :) Держать таблицу чисто под `id` для выборки. rand ее провернет быстрее чем «рабочую», а по полученному результату мы гарантированно выберем то что нам нужно. Конечно главное нигде не забывать соблюдать целостность связи между таблицами
            • 0
              *Это получается 2 запроса…
              Хотя тут еще надо гонять, чтобы найти ту «точку» где такой способ будет быстрее, чем банальный «order by rand» по основной таблице
            • 0
              Непроверено, но нутром чую, что ORDER BY rand() будет одинаково медленным что на полной таблице, что на таблице_из_id. В обоих случаях «на лету» будет создан новый индекс по номерам записей. Только добавится дополнительная работа по зеркалированию этих id.
              Или вы имели в виду что-то более хитрое? Я не понял.
              • 0
                Выбрать индексы в массив, а потом рандомно из массива таскать.
                Да, два запроса((
                Хотя…
  • +3
    SELECT user_id FROM users WHERE MD5(pass) = 'pwd';
    
    SELECT user_id FROM user WHERE pass = MD5('pwd');

    Вообще говоря — это не одно и тоже.

    Я уж не говорю, что первый запрос в принципе смысла не имеет. Если пароли в базе хэшированные, то что такое MD5(pass)? А если в открытом виде хранятся, то зачем вообще MD5() делать если можно явно
    SELECT user_id FROM users WHERE pass = 'pwd'.
    

    Удивился, что узнал для себя новое — CASE WHEN… THEN в UPDATE, пойду почитаю, что это такое :)
    • 0
      Да, пример совсем не кассу получился. Спасибо, исправил
      • 0
        Исправленные тоже неравнозначны :)
        • 0
          время-то не раннее :)
          • +6
            вы, наверно, имели ввиду

            SELECT user_id FROM users WHERE blogs_count * 2 = $value

            SELECT user_id FROM user WHERE blogs_count = $value / 2;
            • +1
              отлично, очень наглядно
            • 0
              FROM users
              и во вотором случае!
    • +1
      Ну первый вариант можно использовать, если мы хотим хранить в базе пароли в чистом виде, НО не хотим слать запросы с паролями, а шлем сразу MD5 хеш… Смысл я себе конечно очень смутно представляю.
      Второй вариант, да, совершенно противоположный, именно он имеет смысл на практике, его всегда и используют
      • 0
        даже в этом, смутно представляемом случае вместо 'pwd' должно быть что то типа 'md5_pwd', показывающее, что в запрос пришел хэш, а не сам пароль (ибо во втором случае это так, а обозначение одинаковое). В общем это стилистические мелочи.
  • +1
    Сначала советуете не использовать SELECT *, потом в последнем примере сами же и приводите ) Непорядок!
    • +1
      Ну на то они и примеры. А вы придираетесь :)
      «SELECT * FROM table WHERE» — безусловный стандарт для легкого понимая смысла в примерах
      • 0
        опередил :)
    • 0
      это для краткости изложения :)
  • 0
    Кстати, могу поделиться наблюдением: большое количество INNER JOIN-ов, в отличие от LEFT-ов, запрос сильно не замедляет. Кто-нибудь мог бы объяснить, почему?
    • 0
      INNER JOIN обычно возвращает куда меньший набор строк.
      • +1
        я ба сказал объединяет.
      • 0
        Ну так не на порядок же. Ровно на столько, сколько строк не удовлетворяют условию JOIN-а, а таковых может и вовсе не быть.
        • 0
          Без конкретных условий тут не о чем дискутировать. Inner join объединяет только строки с совпадающим ключем в обеих таблицах. Left join всегда объединяет левую таблицу со всей правой целиком. При некоторых раскладах разница в производительности может быть гораздо больше чем на порядок, но при некоторых ее может и не быть вовсе.
          • 0
            «Inner join объединяет только строки с совпадающим ключем в обеих таблицах.»
            при чём тут ключ — непонятно. inner join объединяет таблицы по условию. в качестве условия там может быть хоть RAND() > .5
            именно поэтому нельзя сказать, что «INNER JOIN обычно возвращает куда меньший набор строк.».
            • +1
              Эээ… ну тут негласно имелись ввиду нормальные запросы.
    • +1
      Сам буквально вчера столкнулся!

      LEFT JOIN жестко задает порядок выполнения запроса: пробегаемся по всем записям первой таблицы, и для каждой ищем во второй соответствующие им. При этом количество записей может оказаться большим, и в EXPLAIN можно увидеть Using temporary или filesort (а это в данном случе плохо)

      INNER JOIN оставляет MySQL возможность выбора порядка объединения по своим критериям, и она может выбрать более правильный порядок.

      Если вы используете LEFT JOIN, вы должны вручную расставить таблицы в оптимальном порядке.

      Это легко проверяется созданием 2 таблиц, например «Компании» и «Вакансии», каждая вакансия принадлежит какой-то компании. После чего делаем EXPLAIN SELECT companies LEFT JOIN vacancies WHERE… и EXPLAIN SELECT vacancies LEFT JOIN companies и сравниваем.
      • 0
        Ну тут дело ещё в том, что LEFT JOIN асимметричен, и порядок таблиц задаётся логикой запроса. Если говорить о вашем примере, то мне может быть далеко не безразлично, увижу ли я компании без вакансий или вакансии без компаний :)
        • 0
          Согласен. Правда, вакансий без компаний нет по условию)) А вот компании есть.
    • 0
      Автор просто переаутал inner join и outer join
  • +3
    Многие советы очевидны. Я бы назвал статью не «Оптимизация запросов», а «Какие ошибки не нужно допускать».
    Но все равно спасибо. Статья будет полезна новичкам.
    • 0
      антипаттерны
  • +4
    Порой программистам, привыкшим думать терминами функциональных языков

    Возможно вы имели ввиду процедурных языков? Функциональными обычно называют языки по типу Lisp, Haskell, OCaml
    • +1
      да, вы правы
  • –1
    Статья улыбнула. БОльшая часть пунктов из серии «программист Боб Кривые Руки».
  • НЛО прилетело и опубликовало эту надпись здесь
    • –1
      > а ваши примеры — на смех
      рад, что развесилил вас.

      > не бывает таблиц без удаленных строк
      Очень даже бывают. Если удалений не много, то строки можно не удалять, а помечать как удаленные.
      • +1
        Согласитесь, если строка помечена, как удалённая, в выборку она тоже попасть не должна.
        • 0
          WHERE status='used'
          • 0
            $rnd = rand(1, query('SELECT MAX(id) FROM table'));
            Чем это поможет?
            Нам нужен случайный неудалённый ID'шник.
    • 0
      Ну или возьмите например хабр. Блоги удалять нельзя. Их можно только делать закрытыми. Получаем таблицу без дыр с id — первичным ключом.
      • НЛО прилетело и опубликовало эту надпись здесь
      • 0
        В теории все может быть, но на практите таких таблиц практически не бывает.
        Даже если в процессе работы программы удаления нет, то они могут появится через ручное изменение. Например, я всегда после тестирования очищаю таблицы, но не обнуляю auto_increment. И любая такая дырка, влечет за собой неверную работу обсуждаемого запроса.
        • 0
          Нууу. Если вам _действительно_ нужно, чтобы id были монотонно возрастающими, то можно позаботиться о том, чтобы руками никто из таблицы не удалял и делать truncate table вместо delete from table
          • +2
            Разумеется! А если вдруг что-то нужно срочно удалить, то пересчитать ключи всех последующих записей и апдейтить auto_increment.
            Это настолько надуманно, искуственно и чревато огромными проблемами в дальнейшем, что такой способ в жизни работать не будет.
            Вот приведите пример из своей жизни, где у вас была подобная таблица?
    • +2
      (цитата)
      — select * from table where id in (select id from table order by rand()) limit 1,
      вот это выполнится перфектно при условии что ид = первичный ключ, поспорите? ;)
      (/цитаты)

      Поспорю.

      order by rand приведет к тому, что оптимизатор даже не сообразит, какой индекс можно использовать. То есть он выберет filesort, temporary file для вычисления списка id-ов в случайном порядке в сабселекте…

      далее сервер соберет резултсет из всех записей (поскольку все id есть в разультатах сабселекта) и затем первая строка из него (limit 1) будет возвращена пользователю.

      Не слишком похоже на оптимальный план.

    • 0
      Офигеть! Спасибо за реализацию случайной выборки, долго искал как это сделать.
      • НЛО прилетело и опубликовало эту надпись здесь
        • 0
          абсолютно точно. Уровень хабра медленно но неуклонно сползает.
    • 0
      Если не надо очень часто делать случайную сортировку, то я делал так:

      1. Вводил колонку `randomindex` для хранения случайного числа и индекс по ней

      2. Периодически (в фоновом процессе или по TTL) выполнял запрос
      UPDATE `%TABLENAME%` SET `randomindex`=RAND();

      3. Выполнял выборку с сортировкой по колонке `randomindex`
      SELECT `%COLUMNNAME%` FROM `%TABLENAME%` ORDER BY `randomindex`

      Второй запрос в этом примере работает очень быстро
  • 0
    Выполнение операций над проиндексированными полями
    SELECT user_id FROM users WHERE blogs_count * 2 = $value

    В таком запросе индекс использоваться не будет, даже если столбец blogs_count проиндексирован. Для того, чтобы индекс использовался, над проиндексированным полем в запросе не должно выполняться преобразований. Для подобных запросов выносите функции преобразования в другую часть:
    SELECT user_id FROM user WHERE blogs_count = $value * 2;


    Может, так правильнее?

    SELECT user_id FROM user WHERE blogs_count = $value / 2;
  • 0
    Поменяйте SELECT user_id FROM user WHERE blogs_count = $value * 2;
    на SELECT user_id FROM user WHERE blogs_count = $value / 2;
  • –3
    "# Использование LIMIT
    SELECT… FROM table LIMIT $start, $per_page"

    В таких случаях выбирать стоит по auto_increment id — WHERE `id` > 1 000 000 AND `id` < 1 000 020

    • НЛО прилетело и опубликовало эту надпись здесь
      • 0
        Конечно же такая выборка по индексному полю, к примеру, INT(8) будет быстрее лимитов с шести- и семи- значными цифрами.

        К сожалению, цифр я никаких предоставить не могу — сейчас валяюсь с температурой 39 уже несколько дней и к компу подхожу изредка. Чесслово, нет никакого желания экспериментировать.

        Если кто-либо проведёт испытания — буду премного благодарен.

        ps. задолбали эти ограничения на комментирование!
        • –1
          «INT(8) будет быстрее лимитов с шести- и семи- значными цифрами.»
          число в скобках обозначает совсем не разрядность целого.
          • 0
            а я где-либо утверждал это?!
            • 0
              а, немного не так понял твою фразу :-)
        • 0
          для любителей докапываться по мелочам: вверху у меня опечатка.

          «шести- и семи- значными цифрами.»

          конечно же, «числами».

          удивительно, как на это не обратил внимание сей комментатор: habrahabr.ru/blogs/mysql/41968/#comment_1031978
      • НЛО прилетело и опубликовало эту надпись здесь
        • 0
          forums.mysql.com/read.php?115,42553,42553 много интересного «на тему»
    • +1
      А если id начинается с числа 1000000?
      • 0
        Не понял вопроса.

        Какую роль играет с какого числа начинается содержимое полей?
        Мы же просто делаем выборку где id приблизительно попадает в нужный нам диапазон с использованием индекса.
        • 0
          Такую, что в этом случае ваш запрос вернет не те строки — он вернет первые 20 (18 если быть точным, с первой по девятнадцатую), а не с 1000000 по 10000020, как было бы, если бы мы написали LIMIT 1000000,1000020. Да и то, только в случае, если между ними нет пропусков
          • НЛО прилетело и опубликовало эту надпись здесь
          • 0
            про пропуски ниже.

            про < >= habrahabr.ru/blogs/mysql/41968/#comment_1031996 второй абзац.

            зачем оставлять подобные комменты я не въезжаю.

            или это наша национальная черта проявляется «в чужом глазу соринку....»?!

            • 0
              Ничего не понял. Если вы болеете и плохо соображаете — болейте, но не пишите ерунды.
              Нельзя заменить один запрос на другой, так как вы написали. Только в очень ограниченном количестве случаев — если id без пропусков, если не наложено никакое условие WHERE. Так как вы взяли конкретные цифры из конкретного примера, то ко всему прочему ваш совет вообще неверен, потому что вы предлагаете вместо получения 20 записей со смещением в миллион вытащить записи у которых id лежит в некотором диапазоне (т. е. предлагаете полагаться на содержимое полей). Это принципиально разные вопросы в общем случае.

              При чем тут национальность — не въехал совсем, выздоравливайте
    • 0
      // почему коммент отправился-то? я ж не дописал. случайно.

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

      так сделано, например, на xap.ru. в результате на одной и той же странице постоянно разные проиндексированные урлы.
      • 0
        Ага, сорри не дочитал.
        Но все равно не вариант ИМХО
      • 0
        Зачем такие сложности? :) SELECT * FROM table WHERE id>100000 LIMIT 20 :) Сначала условием id>100000 отбрасываем ненужные начальные записи, потом LIMIT'ом отбрасываем ненужные конечные записи :)
    • 0
      Вариант не катит, т. к. опять же удаленные строки в таблице
      если там всего 1 запись 1 000 001?
    • 0
      Ну ясное дело что по первичному ключу лучше. Только
      1) нет гарантии что вы выберите именно 19 значений, как Вы предолагаете
      2) Нет гарантии, что в записи где $start = 1000 000, и первичный ключ тоже = 1 000 000

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

      select что_надо from таблица where keyfield>сохраненныйпервичныйключ limit 20;

      • +1
        А если у меня сортировка может меняться. Вот в списке товаров интернет-магазина я могу поставить сортировку по цене или по названию. И какием запросом мне выводить товары для третьей страницы?
        • 0
          значит web страница должна хранить в скрытых полях значения и цены и названия для первой (на странице) и последней (на странице) записи…

          Переключили сортировку, значит прсто от другого ключа танцуем…
          • 0
            а кто вам сказал, что цена это ключ? И что она уникально? Может у меня на 5 страниц товаров с одинаковой ценой? Да еще и выводятся не все, а с определенным условием — только указанного раздела например.
            Приведите мне пример из вашей практики, где ваше решение сработало бы.
            • 0
              А какая разница, есть ключ по цене или нет? Если есть, то будет использоваться, нет — будет медленнее работать.

              А насчет 3 страниц с ценой — если поле не уникально, используйте

              where цена >= граничное_значение_цены and pkey >граничное_значение_pkey order by цена, pkey

              Мне кажется это очевидно…

              • 0
                >and pkey >граничное_значение_pkey
                а теперь подумайте, почему это не будет работать
                • 0
                  Да, очевидно не будет работать. Как -то в жизни всегда хватало ключей с нормльной селективностью. А не было — создавал искусственные.
                  • 0
                    Забавно. Вы отвечаете на конкретно поставленный вопрос, потом признаете, что ваш ответ не будет работать и еще пытаетесь что-то доказать.
                    Напоминаю вопрос. Он вполне жизненный, я с такой ситуацией постоянно сталкиваюсь

                    А если у меня сортировка может меняться. Вот в списке товаров интернет-магазина я могу поставить сортировку по цене или по названию. И какием запросом мне выводить товары для третьей страницы?
                    • 0
                      И что Вам забавно? то что я признал свою неправоту? Тут так не принято?
                      Ну чтож… попытался упростить ответ, получилось плохо… Если Вам так нужно, могу привести решение без упрощения.

                      Итак.
                      У нас есть таблица, скажем prices,
                      create table goods (ix int auto_increment primary key, cost integer);
                      И есть составной индекс
                      create index ix1 (cost,ix)

                      У нас есть страница, в которой в hidden полях есть pkey и cost последней записи и этой страницы. Скажем они будут в переменных $ix и $cost когда пользователь тыркнет кнопку next.

                      При отрисовке страницы первая запись будет
                      handler goods open;
                      handler goods read ix1 = ($cost,$ix);

                      следующие N записей (длина страницы N+1)
                      handler goods read ix1 next limit $N;
                      handler goods close;
                      У последней записи естественно ix и cost сохраняем в hidden полях.

                      Интересно, а Вас то какое решение?

  • +1
    «query('INSERT INTO table SET column = 1, id=1');»
    но при этом строкой ниже:
    «при условии наличия первичного или уникального ключа по полю id:»

    какой смысл указывать явно id, если он PK?

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

    в целом — не понравилась слишком категоричная манера изложения «если так — то делайте так». ведь люди прочитают и будут бездумно следовать советам…
  • +3
    UPDATE news SET
    title = CASE
    WHEN news_id = 1 THEN 'aa'
    WHEN news_id = 2 THEN 'bb' END
    WHERE news_id IN (1, 2)

    порадовало. возьму на заметку такой способ. Спасибо
    • +1
      еще вариант, в InnoDB:
      START TRANSACTION
      BEGIN
      … тут в цикле куча запросов update
      COMMIT

      в MyISAM, наверное, вместо транзакции можно использовать LOCK TABLE.

      работает тоже в разы быстрее, чем просто куча update. С предложенным выше вариантом не сравнивал.
    • 0
      еще напишите для него обертку, типа function bulk_update(), чтобы не склеивать каждый раз строчку
  • –11
    Эта статья полезная, конечно, но какую версию mysql она описывает? четвёртую, наверно. В пятой версии mysql и php практически все приведённые примеры использовать вредно и опасно, например
    row = query('SELECT * FROM table WHERE id = '.$rnd);
    подвержен sql injections. Правильным будет запрос примерно такой:
    row = query('SELECT * FROM table WHERE id = ?', [$rnd]);

    и ещё более правильный — использовать
    $sth = prepare('SELECT * FROM table WHERE id = ?'); и далее
    $sth->execute($rnd);

    Это защищает от ошибок и работает быстрее.
    • +4
      А причем здесь версия MySQL? Или вы думаете, что инъекции для 4-ки не подходят для 5-ки?
    • +3
      С чего вы решили, что статья «Оптимизация MySQL запросов» должна рассматривать вопросы безопасности? Котлеты отдельно, мухи отдельно
      • +2
        Вот, кстати, зря. Уж где-где, а в примерах, по-моему, нужно всегда писать безопасно. На то они и примеры.
  • 0
    В форме с '?' некоторые запросы выполняются в разы быстрее. Это не оптимизация?

    Почему именно 5 — «Before MySQL 5.1.17, prepared statements do not use the query cache.» Неизвестно что лучше было — компилировать и placeholderы или по старинке.
    • 0
      если запрос на insert/delete/update то query cache в любом случае по барабану. Так что выигрыш от prepared statements может быть заметным, начиная, если мне не изменяет склероз, с 4.1.

      другое дело что некоторые клиенсткие бибки работают (как это будет по русски) коряво с prepared statements. Можно на засаду нарваться.
      • 0
        prepared statements как раз в 4.1 и появились в полном объёме. Если пользоваться PDO — то никаких засад не будет. Perl (DBI) имеет предупреждение о засаде и её причинах и правилах обхода. Но работать надо именно с ними, иначе тратится время на компиляцию и оптимизацию. У меня опыт работы с mysql маленький, но в postgres и oracle изменения скорости просто огромные, и я не вижу почему бы того же не ожидать от mysql. (кстати, используя prepared statements с DBASE IV получаем десятикратный выигрыш). Анализ запросов форумов и CMS'ок показывает, что используются очень простые запросы, но каждый раз уникальные с точки зрения mysql.
  • 0
    Несколько сумбурно все.
    Вообще правило написание нормальных запросов одно — смотрите explain.
    • 0
      explain… А заодно slow query log. И еще show status. А так же show variables. И если Вы понимаете, что там написано, во всех этих местах, то скорее всего сможете найти быстрый способ выборки данных
  • +1
    спасибо, очень полезно, особенно ON DUPLICATE KEY всегда с этим мучался и думал как лучше сделать.

    А что на счёт вставки нескольких строк одним запросом, как узнать все insert_id?
    • НЛО прилетело и опубликовало эту надпись здесь
      • +1
        До того, как придет запрос select max(id) может успеть произойти вставка из другого потока, так что это не решение.

        наверно в этом случае (если нужны все id) — только циклом перебирать. Я правда не знаю зачем оно надо :)
        • НЛО прилетело и опубликовало эту надпись здесь
    • +1
      для myisam — поскольку операции атомарны, диапазон будет в пределах
      LAST_INSERT_ID()… LAST_INSERT_ID()+ROW_COUNT()

  • 0
    SELECT COUNT(field) FROM table
    разве не будет быстрее чем
    SELECT COUNT(*) FROM table
    при условии, что field — уникальный ключ таблицы?
    • 0
      в общем случае — нет
    • 0
      зависит от типа таблицы. Для myisam однозначно не будет.
    • 0
      SELECT COUNT(*) FROM table (без WHERE если)
      берется из заголовка таблицы даже без ее просмотра.
      • 0
        в innodb не берется.
  • +1
    Дополнение(процитирую себя):
    «Зачастую при добавление в таблицу, имеющей UNIQUE индекс или PRIMARY KEY, новой строки, очень полезным бывает синтаксис
    INSERT IGNORE. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке,
    то есть сама вставка не будет произведена, при этом не будет прекращенно выполнение.
    Это очень выручает при оптимизации приложения, когда мы вместо двух запросов:
    1- проверить наличие строки в таблице по ключу (SELECT)
    2- вставить строку в случае отсутствия дублирования ключа (INSERT)
    будем использовать только один запорс INSERT IGNORE.»

    2 %tuta_larson%:
    Юджин, опять забыл про это написать=)
    • 0
      Для меня полезными оказались:

      1. Аналогичный пример:
      SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) — TO_DAYS(registered) <= 10;

      не будет использовать индекс по полю registered, тогда как
      SELECT user_id FROM users WHERE registered >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
      будет.


      и исправьте плиз в предыдущем примере на «blogs_count = $value / 2;»

      2. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет.

      Согласен с habrahabr.ru/blogs/mysql/41968/#comment_1031853, что статью надо назвать не «Оптимизация запросов» (таких тысячи), а как-то по-оригинальнее, типа «Плохие запросы и как их сделать хорошими».

      Успехов.
      • +1
        Переделка плохих запросов в хорошие это и есть оптимизация, только одним словом ;-)
        • 0
          Так мы всю жизнь занимаемся оптимизацией: делаем мир лучше :-p

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

          Если я потом захочу вернутсья сюда и введу в гугле «Оптимизация запросов», то вряд ли я дойду до этой статьи. А вот если я введу «Плохие запросы и как их сделать хорошими», то статья вероятно будет в первых строчках.
    • 0
      (сорри, ваш коммент шёл при просмотре страницы последним и я нажал не туда)
  • +2
    Наконец-то хорошая техническая статья на хабре!
    Половину знал, половина — новое. Зачитался мануалами =)
    Спасибо!
  • НЛО прилетело и опубликовало эту надпись здесь
    • 0
      не MySQL — тормоз, а руки у людей бывают кривые… так наверно правильнее будет…
      • –1
        Ага. вот например запрос из старого wp для выборки постов с указаными тэгами. На дедике отрабатывал за две-три минуты :)

        SELECT DISTINCT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.object_id) AS counter
        FROM wp_posts AS p
        INNER JOIN wp_term_relationships AS tr ON (p.ID = tr.object_id)
        INNER JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
        WHERE tt.taxonomy = 'post_tag'
        AND (tt.term_id IN («54», «42», «30», «19»))
        AND p.ID <> 4818
        AND p.post_status = 'publish'
        AND p.post_date < '2008-07-15 12:40:08'
        AND p.post_type IN ('page', 'post')
        GROUP BY tr.object_id
        ORDER BY counter DESC, p.post_title DESC
        LIMIT 0, 5
    • 0
      Обычно так пишут люди не знающие предмета обсуждения.
  • +3
    Насчет «Неиспользование ON DUPLICATE KEY UPDATE», есть же в mysql REPLACE.
    Цитата: «Оператор REPLACE работает точно так же, как INSERT, за исключением того, что если старая запись в данной таблице имеет то же значение индекса UNIQUE или PRIMARY KEY, что и новая, то старая запись перед занесением новой будет удалена. „
    • 0
      Ну это не прямые аналоги, в зависимости от ситуации можно пользоваться тем и другим. Но спасибо за подсказку на возможность.
  • 0
    1. Лучше переименовать заголовок в anySQL

    2. По INNER JOIN table USING уточняйте обязательно версию СУБД и ее марку, ибо на старых это может на канать (желательно вообще все СУБД перечислить, где это будет работать или конкретный диалект ANSI SQL`я дать)

    3. CASE WHEN для UPDATE`а — крайне неудачный совет. Ибо UPDATE`ы случаются гораздо реже и логика просачивается в данные, что есть коряво.

    4. Аналог LIMIT`а в MSSQL`е: select top 10 Name, Surname from Person order by PersonID /* первые 10 */ или select * from (select top 10 Name, Surname from Person order by PersonID DESC) TMP order by PersonID /* последние 10 */

    /* Промежуточные сами додумайте :) Хотя наверное в 2008 это уже по-человечьи сделали */

    5. Также неудачный совет про «auto_increment'ный первичный ключ и нет пропусков» ибо это только в демоверсиях так :) Как говаривал Конфуций: «Всё течет, все удаляется»

    6. Тьфу, бла, это перевод… Афтар! Убей сибя ап стены, т. е. юзай топик-ссылки… :(
    • 0
      на первый пять предпочту не отвечать.
      > 6. Тьфу, бла, это перевод… Афтар! Убей сибя ап стены, т. е. юзай топик-ссылки… :(
      хотелось бы посмотреть на оригинал
  • 0
    USING( сработает только в случае одинаковых названий столбцов
    рекурсии и циклы и при запросах исключены, в этом я согласен
    INSERT INTO logs (value) VALUES (...), (...) не работает если много строчек!!! кто не скажет заветную цифру того сколько мускул может сожрать за раз буду благодарен — если она конечно же одинакова везде что мало вероятно).
    все равно статья здравая для новичков очень сгодится
    • 0
      Кажетсязависит от макс. размера буфер под запрос, который по умолчанию мегабайт.
    • 0
      INSERTI INTO… VALUES (..), (...) завист от параметра
      bulk_insert_buffer_size =

      Используется для INSERT INTO… SELECT FROM… и загрузки данных через LOAD DATA INFILE

  • 0
    Мда, опять разочаровался в качестве написанного. В комментарияю в б0льшей части правильно сказано — если так — делай вот так. Глупо это.
  • 0
    Подскажите плиз, оч нужно, можно ли оптимизировать след запрос:
    Таблица с 3,5 млн записей, идет выборка по дате, где дата больше такого-то числа, по этому полю есть индекс. скрипт запускается в кроне и перебирает все с limit 0, 100 и т.д.
    explain показывает что проходится по 2,7 млн строкам, можно ли оптимизировать как-то???

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