html текст
All interests
  • All interests
  • Design
  • Food
  • Gadgets
  • Humor
  • News
  • Photo
  • Travel
  • Video
Click to see the next recommended page
Like it
Don't like
Add to Favorites

Оптимизация 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, которые вы хотели бы прочитать.

Читать дальше
Twitter
Одноклассники
Мой Мир

материал с habrahabr.ru

1

      Add

      You can create thematic collections and keep, for instance, all recipes in one place so you will never lose them.

      No images found
      Previous Next 0 / 0
      500
      • Advertisement
      • Animals
      • Architecture
      • Art
      • Auto
      • Aviation
      • Books
      • Cartoons
      • Celebrities
      • Children
      • Culture
      • Design
      • Economics
      • Education
      • Entertainment
      • Fashion
      • Fitness
      • Food
      • Gadgets
      • Games
      • Health
      • History
      • Hobby
      • Humor
      • Interior
      • Moto
      • Movies
      • Music
      • Nature
      • News
      • Photo
      • Pictures
      • Politics
      • Psychology
      • Science
      • Society
      • Sport
      • Technology
      • Travel
      • Video
      • Weapons
      • Web
      • Work
        Submit
        Valid formats are JPG, PNG, GIF.
        Not more than 5 Мb, please.
        30
        surfingbird.ru/site/
        RSS format guidelines
        500
        • Advertisement
        • Animals
        • Architecture
        • Art
        • Auto
        • Aviation
        • Books
        • Cartoons
        • Celebrities
        • Children
        • Culture
        • Design
        • Economics
        • Education
        • Entertainment
        • Fashion
        • Fitness
        • Food
        • Gadgets
        • Games
        • Health
        • History
        • Hobby
        • Humor
        • Interior
        • Moto
        • Movies
        • Music
        • Nature
        • News
        • Photo
        • Pictures
        • Politics
        • Psychology
        • Science
        • Society
        • Sport
        • Technology
        • Travel
        • Video
        • Weapons
        • Web
        • Work

          Submit

          Thank you! Wait for moderation.

          Тебе это не нравится?

          You can block the domain, tag, user or channel, and we'll stop recommend it to you. You can always unblock them in your settings.

          • pashkinz92
          • домен habrahabr.ru

          Get a link

          Спасибо, твоя жалоба принята.

          Log on to Surfingbird

          Recover
          Sign up

          or

          Welcome to Surfingbird.com!

          You'll find thousands of interesting pages, photos, and videos inside.
          Join!

          • Personal
            recommendations

          • Stash
            interesting and useful stuff

          • Anywhere,
            anytime

          Do we already know you? Login or restore the password.

          Close

          Add to collection

             

            Facebook

            Ваш профиль на рассмотрении, обновите страницу через несколько секунд

            Facebook

            К сожалению, вы не попадаете под условия акции