Пользователь
0,1
рейтинг
8 сентября 2008 в 19:53

Разработка → MySQL Performance real life Tips and Tricks. To be continued.

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

В прошлой статье habrahabr.ru/blogs/mysql/38907 рассматривались вопросы оптимизации LIMIT, GROUP BY, COUNT.

В данной статье я немного вернусь к вышеописанному и опишу пару примеров, с которыми столкнулся на проекте недавно, после этого приведу еще пару небольших примеров относительно того что такое хорошо и что такое плохо в MySQL.

Возвращаясь к теме больших LIMIT в запросах. Например есть у нас такой запрос, который вытягивает из базы картинки и имена пользователей, которые эти картинки выложили, притом вытягиваем мы эту информация для картинок с ID > 2500 и интересуют нас результаты с 5000 по 5100. (В данном примере стоит обратить внимание на саму конструкцию запроса).
Итак, что должен сделать наш запрос в этом случае:
1. Из множества страниц `tx_localrep_images` as tli
2. Из них выбираем те, которые удовлетворяют условию tli.uid > 2500
3. Дальше, т.к. мы имеем LEFT JOIN, то кол-во результатов (записей) в результирующей выборке не зависит от склейки, поэтому MySQL вполне мог бы сделать LIMIT на той выборке которую мы имеем к этому моменту (полученную после шага 2) и только после этого начинать склейку с таблицей `fe_users`. НО MySQL этого НЕ делает!
А делает следующее: вначале делает склейку, а уже потом отсекает первые «ненужные» 5000 записей!!!

SELECT
  SQL_NO_CACHE tli.`uid`, tli.`caption`, fe.`username`
FROM
  `tx_localrep_images` as tli
LEFT JOIN
  `fe_users` as fe ON `tli`. cruser_id = fe.uid
WHERE
  tli.uid > 2500
LIMIT
  5000, 100;

* This source code was highlighted with Source Code Highlighter.


Это совсем не то поведение, которое мы ожидали, но не беда! Существует вполне простое решение данной проблемы с помощью DERIVED TABLE. Вот такой запрос выполняется гораздо быстрее (разность в скорости выполнения прямо пропорциональна кол-ву записей в LIMIT)

SELECT
  SQL_NO_CACHE tli.`uid`, tli.`caption`, fe.`username`
FROM
  (
  SELECT
   tli.`uid`, tli.`caption`, tli.`cruser_id`
  FROM 
   `tx_localrep_images` as tli
  WHERE
   tli.uid > 2500
  LIMIT
   5000, 100
  ) as tli
LEFT JOIN
  `fe_users` as fe ON `tli`. cruser_id = fe.uid;

* This source code was highlighted with Source Code Highlighter.


После этого, я решил немного поэкспериментировать как работает MySQL оптимизатор с запросами вида SELECT(*)

Соответственно есть такой стандартный запрос.

SELECT
   SQL_NO_CACHE count(*)
FROM
   `tx_localrep_images` as tli
WHERE
   tli.uid > 500;

* This source code was highlighted with Source Code Highlighter.


Иесть такой, менее стандартный. Прошу уважаемое сообщество не писать о том, что LEFT JOIN в данном запросе не имеет смысла, т.к. не влияет на число записей в результирующей выборке. Это и так понятно всем… всем кроме MySQL оптимизатора :-), который исправно выполнит склейку, а уж потом будет вычислять число записей в ней.

SELECT
   SQL_NO_CACHE count(*)
FROM
   `tx_localrep_images` as tli
LEFT JOIN
   `fe_users` as fe ON `tli`. cruser_id = fe.uid
WHERE
   tli.uid > 500;

* This source code was highlighted with Source Code Highlighter.


Поэтому данный запрос на сравнительно небольшом наборе данных (50 000 строк) выполняется в 20 раз медленней.

COUNT(*) vs COUNT(column_name)

Замечал не раз, что многие считают, что COUNT(*) это алиас COUNT(column_name). Это совершенно не так.

Во-первых, эти запросы могут возвращать разные результаты. Такое может проявляться когда столбец column_name может содержать NULL значения. Т.е. конструкция COUNT(column_name) вернет кол-во записей с column_name IS NOT NULL.

Во-вторых, эти запросы выполняются с разной скоростью. Например, такой запрос

SELECT
  SQL_NO_CACHE count(tli.`type`)
FROM
  `tx_localrep_images` as tli
WHERE
  tli.uid > 500;

* This source code was highlighted with Source Code Highlighter.


может выполнять гораздо дольше

SELECT
  SQL_NO_CACHE count(*)
FROM
  `tx_localrep_images` as tli
WHERE
  tli.uid > 500;


* This source code was highlighted with Source Code Highlighter.


т.к. запрос, использующий COUNT(*), при наличии индекса по полю tli.uid будет использывать покрывающий индекс и, соответственно, выполнится очень быстро. Первый же запрос будет искать COUNT методом ROW SCAN, о чем и говорит «Extra: USING WHERE» в EXPLAIN этого запроса.

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

alter table `tx_localrep_images` add key cover_key(uid, type);
* This source code was highlighted with Source Code Highlighter.


Но ИМХО, чем вводить дополнительные индексы, для одного единственного запроса, который их использует, и которые занимают лишнее место на диске и что самое ощутимое в кеше, а также тормозят наши изменения данных (UPDATE, INSERT, DELETE), то лучше уж использовать COUNT(*).

Поля, объявленные как DEFAULT NULL

Также если я оговорился и привел в пример сказав, что столбец может быть объявлен как DEFAULT NULL.
То сразу скажу, что такие столбцы лучше не объявлять вообще, если вы изначально не планируете хранить NULL — значения. Существует очень много задач, где нет необходимости в хранении NULL значений, однако очень многие создают таблицы содержащие NULL значения. Храниение НУЛЛ значений усложняет работу внутреннего механизма MySQL, а именно работа с индексами по этим полям и ведение статистики индексов, а также сравнение значений по таким полям.

When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM. Even when you do need to store a «no value» fact in a table, you might not need to use NULL. Consider using zero, a special value, or an empty string instead. The performance improvement from changing NULL columns to NOT NULL is usually small, so don't make finding and changing them on an existing schema a priority unless you know they are causing problems. However, if you're planning to index columns, avoid making them nullable if possible. © — High Performance MySQL, Second Edition

Длинна поля INT(length)

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

Как известно лучше подбирать типы данных для столбцов минимально достаточные для хранения всего диапазона значений в нем. Т.е. иногда люди используют INT там где спокойно можно использовать TINY INT и т.д.

В MySQL при создании таблиц можно задавать длину полей, что-то вроде `column` int(10) UNSIGNED NOT NULL
Так вот, я обнаружил создание таблиц, в которых значения длинны полей везде были разные от 1 до 20. Как оказалось некоторые думают, что MySQL будет выделять такое кол-во памяти для хранения поля, чтобы вместить его максимальное значение. Может быть плохо объяснил. Покажу на примере. Возьмем такой объявление:
`column` int(10) UNSIGNED NOT NULL
так вот, некоторые думают, что MySQL сделает следующее
1. создаст число из 10-ти 9-к (максимальное число), т.е. 999999999
2. найдет степень 2-ки минимально достаточную (т.е. минимальную из больших) для хранения этого числа, т.е в нашем случае
2^30 = 1073741824
3. данное значение округляется до байт в большую сторону, т.е. до 32 = 4 байта

Вобщем весь алгоритм описанный выше ничего общего к реальной жизни НЕ имеет!
На самом деле для MySQL под поле INT(1) выделится столько же памяти для хранения сколько и под INT(20).
Т.к. данное значение используется для вывода значений, т.е. размер зарезервированного места. Например когда мы выполняем запросы через command-line interface и.т.д.

Хранение IP-адресов в БД.

Кстати, я уже отошел от темы статьи, поэтому приведу последний trick, который на самом деле многие используют, кроме новичков, но именно для них я его и напишу.
Очень часто вижу, что люди хранят в базе IP-адрес используя тип данных VARCHAR(15), это очень неэкономно, притом достаточно медленно работает в поисках по range
Для хранения IP адресов в MySQL существует 2-фии.
Первая — INET_ATON
Позволяет преобразовывать строку, состоящую из 4 чисел, разделенных точками в значение типа INT UNSIGNED.
Делается это по такому алгоритму.

SELECT INET_ATON('X.Y.Z.J');

X*256^3+ Y*256^2+ Z*256^1+ J*256^0

Функция INET_NTOA выполняет обратное преобразование (из числа, к привычному виду IP-адреса разделенному точками).

Соответственно все что нам нужно — это создать в таблице поле UNSIGNED INT, в котором будет хранится преобразованное ф-ией INET_ATON число.

Соответственно если мы хотим, скажем, выполнить поиск и достать все IP адреса из какой-то подсети, скажем 255.255.0.0

То можно выполнить такой запрос

SELECT
  ip
FROM
  `ips`
WHERE
  `ips`.ip > INET_ATON('255.255.0.0')

* This source code was highlighted with Source Code Highlighter.

Что при наличии индекса по полю ip бужет достаточно быстро.

И последнее всегда думайте какой оптимальный тип данный можно выбрать для хранения нужного вам поля!
Например, для хранения маски подсети некоторые выберут VARCHAR(15), некоторые — INT и применят ф-ии INET_ATON, INET_NTOA. Но правильным вариантов в данном случае является выбор поля TINY INT для хранения количества единиц.

На этом пожалуй закончу. Устал уже писать.
ЗЫ. Оставьте мнение в комментах интересно ли вам или нет. Если нет — так и пишите: «Пощади нас, золотая антилопа! Хватит!»

ЗЗЫ. Спасибо хабра-сообществу за советы по компоновке статей и подаче материала. В особенности %hlomzik% и %maxshopen%
Игорь Миняйло @maghamed
карма
237,4
рейтинг 0,1
Пользователь
Реклама помогает поддерживать и развивать наши сервисы

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

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

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

  • +6
    а почему «AS' в запросах маленькими буквами пишете? ^_~
    • +4
      Честно говоря, AS по разному пишу иногда большими, иногда маленькими. Это не та конструкция, на которую много внимания уделяешь. Не понимаю, за что Вас заминусовали.
      • +6
        > Не понимаю, за что Вас заминусовали.
        Тут так положено)
        • +2
          Ну, что Вы так! С тролями нужно бороться! Я сейчас Вам немного компенсирую.
      • 0
        'as' можно и вообще писать. Вы конечно это знаете, но может кто не знал :)
        • 0
          упс, я хотел сказать

          as' можно и вообще _не_ писать. Вы конечно это знаете, но может кто не знал :)

          извините за опечатку
  • 0
    отличная статья, спасибо!
    • 0
      Как Вы так быстро умудряетесь их прочитывать. Я пол дня пишу, а Вы — за 7-10 минут прочитываете и уже комментите :-)
      • 0
        знакомо. сам же писатель :) да писать намного дольше чем читать. честно, я все прочел полностью :)
      • НЛО прилетело и опубликовало эту надпись здесь
      • +6
        «
        — Фрай! Тут всего две страницы текста!
        — Я писал это 2 часа, думал и читать будут два часа…
        » (с) Футурама.

        Спасибо за статью. Оч интересно и полезно… в мемориз однозначно :)
  • 0
    Хотим продолжения :)

  • +4
    Материал очень интересный, но изложение страдает. Иногда не очень понятно, где хорошо, где плохо. Иногда не очень понятно, о какой проблеме идет речь.

    Я бы постарался разбить текст на подразделы с кратким описанием проблемы, ее неправильным решением и правильным. Чтобы было мини how-to.

    А вообще хорошо бы это в отдельный блог вынести «Оптимизация MySQL», например
  • 0
    Спасибо, освежили память. :)
    Заблуждение по поводу int(2) — встречается повсеместно, это точно…
  • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    Весьма полезная информация. Хоть и не считаю себя новичком, функцию INET_ATON/INET_NTOA, признаюсь, подзабыл. Практически открыл ее для себя заново :-)
    Maghamed, продолжайте писать в том же духе. Уверен, есть еще много моментов и тонкостей в mysql, которые было бы не плохо использовать в повседневной работе, но по тем или иным причинам, достаточно большое количество разработчиков о них или не знают, или уже подзабыли.
  • –3
    Все хорошо, особенно про антилопу. Спасибо.
  • +2
    Долго пытался въехать в то, что вы написали про NTOA/ATON

    То ли я не понял, толи вы имели ввиду следующее (если это так — то поправьте статью, а то очень не понятно):

    Оптимально хранить ip-адреса не как строки (varchar), а как числа. Для этого существует две функции mySQL — INET_ATON и INET_NTOA, первая преобразует 4 байтную последовательность ip-адреса в число, вторая преобразует обратно. Таким образом:

    1. Столбец, в котором будут хранится ip-адреса объявляется как `ip` INT UNSIGNED NOT NULL

    2. При вставке:
    INSERT INTO `ips`
    SET ip = INET_ATON('213.169.23.35')

    3. При выборке (например > 255.255.0.0)

    SELECT
    INET_NTOA(ip)
    FROM
    `ips`
    WHERE
    `ips`.ip > INET_ATON('255.255.0.0')

    Само собой по ip делаем индекс

    P.S. Кстати с помощью этих функций можно хранить не только ip-адреса, но и любые аналогичные последовательности, например rgb-цвета :)
    • 0
      Да, Вы все поняли правильно, относительно Вашего постскриптума
      >P.S. Кстати с помощью этих функций можно хранить не только ip-адреса, но и любые >аналогичные последовательности, например rgb-цвета :)

      Думаю, это не лучший выбор, использование ф-ии INET_ATON для хранения RGB последовательности, т.к. данная ф-ия расчитана на хранение 4х групп чисел, разделенных точкой, т.е вам прийдется сохранять избыточную информацию, скажем, белый цвет
      #FFFFFF (255.255.255), вам прийдется хранить как 255.255.255.0

      Но если Вам понравился данный метод, то Вам ничто не мешает написать собственную ф-ию, которая расчитана непосредственно на RGB последовательность, скажем, нечто вроде

      CREATE FUNCTION RGB_FUNC(IN rgb_string VARCHAR(9))
        RETURNS INT(10)
      BEGIN
        DECLARE red INT;
        DECLARE green INT;
        DECLARE blue INT;
        DECLARE result INT;

        SET red = CAST(SUBSTR(rgb_string,1,3) AS INT);
        SET green = CAST(SUBSTR(rgb_string,4,3) AS INT);
        SET blue = CAST(SUBSTR(rgb_string,7,3) AS INT);

        SET result = red*256*256 + green*256 + blue;   
        RETURN(result);
      END;
      * This source code was highlighted with Source Code Highlighter.


      Если с синтаксисом ничего не напутал, то как-то так
      • +1
        Ну тогда уж скорее как 0.255.255.255, т.к.

        INET_ATON('0.255.255.255') -> 16777215
        INET_NTOA(16777215) -> 0.255.255.255

        :)

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

        • 0
          А можно и не отрезать. Многий комерческий софт типа ChartDirector использует 4-х байтные «цвета», в которых первый байт — alpha
        • 0
          Да, Вы правы, что 0.255.255.255. Я это и имел в виду. 2 часа ночи просто давали о себе знать :-) Ну и во всем остальном Вы правы.
          • 0
            все таки кусок про ip-адреса поправьте в статье. Поверьте, понять о чем вы написали в текущем виде сможет только тот, кто это и так уже знает :)

            а это — `ips`.ip > 256*256*256*255 + 256*256*255* — мало того, что неточно(не до конца) написано, так еще и не понятно, зачем тут ручная калькуляция, если сами же про функции рассказываете
            • +1
              Поправил, надеюсь так лучше.
  • 0
    поправьте заголовок — to be continueD
  • 0
    Mysql не до конца оптимизирует джойны, так как при некоторых вариантах склеек может получиться больше чем одна запись.
    Так же не стоит забывать, что можно сделать сортировку по одной из приджойненых таблиц… И мускуль вынужден сначала все склеить, потом отсортировать, и только потом понять, которая из записей пятитысячная.
  • 0
    Было бы здорово если бы автор приводил конкретные цифры (время выполнение того или иного запроса на определенном объеме данных). Но и без этого было интересно почитать ваш труд. Спасибо.
  • 0
    `ips`.ip > 256*256*256*255 + 256*256*255* < — вы забыли дописать последний множитель
    • 0
      ой, ступил ) сказываются 3 часа ночи
  • +1
    Знаете, человек бесконечно долго может смотреть на то как работают другие люди, как льется вода, на огонь, как тебе выдают зарплату.

    После написания пары топиков на хабр, я отметил для себя еще один пункт в этом списке.
    Я могу очень долго смотреть как растет моя карма :-))
    • 0
      Смотреть-то хорошо =) Некоторое вещи действительно интересны.
      Только вы бы статьи свои правили, приводили их к нормальному стилю.
      Пример — кусок про ip-адреса. «соответственно если мы хотим, скажем, выполнить поиск...» якобы продолжает предыдущую мысль, на самом деле начиная новую, и то как-то непонятно, с маленькой буквы, как будто у вас что-то удалилось…
      Ну и такая рваность и разрозненность кругом.
      • 0
        Да, я понимаю, что мысли скачут, т.к. статьи пишутся в течение дня на работе в перерывах между таковой и некоторые вещи, например про INT(1) буквально на ходу видишь в коде своих коллег, поэтому и дописываешь их на ходу в статью. Т.е. что за целый день набросал, то в конце и опубликовал.
        Я с удовольствием поправлю статью, чтобы она стала более читабельна и в следующей статье буду обращать внимание на отзывы по юзабилити в читании. Но пока я понял, что лучше разбивать статьи на независимые разделы. А пока такие жалобы/пожелания малоконструктивны. Пожалуйста, говорите конкретней как разбить, или что именно не понятно — я объясню, или плохо описано — я перепишу.
        • 0
          Ну в принципе тут не так много-то и надо для отличных заметок! )
          Добавить подзаголовки для разбиения.
          И строить логику «повествования», мысли организовывать, оформлять их в абзацы с более-менее законченным смыслом, без разрывов посреди предложения.
          Сложно это выразить, посмотрите хорошие статьи хотя бы прямо здесь, на хабре.
        • 0
          COUNT(*) vs COUNT(column_name)
          Замечал не раз, что многие считают, что COUNT(*) это алиас COUNT(column_name). Это совершенно не так.

          Во-первых, эти запросы могут возвращать разные результаты. Такое может проявляться когда столбец column_name может содержать NULL значения. Т.е. конструкция COUNT(column_name) вернет кол-во записей с column_name IS NOT NULL.

          Во-вторых, эти запросы выполняются с разной скоростью. Например, такой запрос

              SELECT
                SQL_NO_CACHE count(tli.`type`)
              FROM
                `tx_localrep_images` as tli
              WHERE
                tli.uid > 500;
          

          может выполняться гораздо дольше, чем

              SELECT
                SQL_NO_CACHE count(*)
              FROM
                `tx_localrep_images` as tli
              WHERE
                tli.uid > 500;
          

          т.к. запрос, использующий COUNT(*), при наличии индекса по полю tli.uid будет использывать покрывающий индекс и, соответственно, выполнится очень быстро. Первый же запрос будет искать COUNT методом ROW SCAN, о чем и говорит «Extra: USING WHERE» в EXPLAIN этого запроса.

          На самом деле можно сделать так, чтобы и первый запрос использовал индекс, добавив к данной таблице покрывающий индекс cover_key(uid, type). Но, ИМХО, лучше использовать COUNT(*), чем вводить дополнительный индекс для одного единственного запроса.
          • 0
            Я подправил в соответствии с Вашими замечаниями. Надеюсь, что так более удобочитаемо. Хотя некоторые обороты решил оставить. Нужно же иметь какой-то авторский стиль изложения, пусть его и называют рваным и разросненным :-)
            • 0
              Это был просто пример, возможно и неудачный к тому же.
              Статья осталась в том же обрывочном формате — прописная буква не сделает слово началом предложения, если оно таковым не является…
              • 0
                Знаете, я не учусь на журфаке и редколлегии у меня тут нет. Я — программист. Пишу — как могу и о чем интересно. Притом сижу сейчас на работе и стараюсь работать, так что у меня нет времени в разгар рабочего дня переписывать написанное. Я уже написал для тех кому непонятно, что они могут написать в комментах, что именно им непонятно, или где можно получить более подробную инфу или примеры, и когда я освобожусь — обязательно объясню, напишу.

                А если вы ищете красивые формы, то читайте Пушкина, Достоевского, Толстого…
                • 0
                  эх… я не хотел вас никак оскорбить.
                  Мне очень интересны и новы оказались некоторые сведения, вам спасибо!
                  Принуждать вас к написанию литературной статьи я не мог и не собирался. Лишь указал на недостатки, может вы в будущем прислушаетесь. Ведь всё-таки это статья для подачи какой-то информации, а не отрывки из беседы с другом-программистом.
                  Не принимайте так близко :)
                  • 0
                    Прислушаюсь, постараюсь прислушаться :-) а там посмотрим :-)
  • –5
    Если говорить про дурость оптимизатора MySQL, то не лишне сказать о том, что его надо вообще лишать какой-либо свободы действий. Хороший критический запрос это запрос с использованием директивы force index().

    То же самое можно сказать о неравенствах в условиях: часто их можно и нужно избежать: select id from posts where score = > 0;select id from posts where score_positive = 1;
    • –5
      Здесь, я с Вами категорически не согласен. Т.к. при заполнении данными может значительно изменятся cardinality индексов, и тот индекс, который оптимален на данный момент, может стать абсолютно неоптимален в будущем. И Вам прийдется потом долго искать причину тормозов. Поэтому force index также как и straight join следует избегать если в этом действительно нет необходимости.

      Я хотел уделить этому немного места в моей следующей статье.
      • 0
        Не знаю, что для вас «абсолютно неоптимален», а в моём представлении нет ничего хуже, чем файлсорт на несколько десятков тысяч записей, так что ваше абсолютно вовсе даже и не абсолютно.
        • 0
          Ха, как мы с Вами дружно друг-друга минуснули :-)

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

          Но не стоит так плохо говорить о MySQL оптимизаторе, хотя может показаться, что я и ругаю его в своих статьях, но как правило он работает вполне адекватно при выборе индексов для запросов.

          Просто следует проектировать БД так и создавать такие индексы, чтобы MySQL корректно выбирал, то что нужно.
          Также нужно держать индексы и данные в актуальном состоянии, для этого периодически (по крону) следует выполнять ANALYZE TABLE и OPTIMIZE TABLE.

          Но ANALYZE TABLE следует применять с умом на продакшене, т.к во время выполнения, а оно зависит от размера индексов ставится READ LOCK для MyISAM и WRITE LOCK для InnoDB

          Т.к. решение оптимизатора каокй индекс выбрать для выполнения запроса, зависит от статистики индексов, и хорошо если она актуальна.
          • 0
            Я имел дело с посещаемым проектом, где момент нестабильности наступал хаотично: мог работать день, а мог сходить с ума раз в 15 минут. И чаще всего проблема наступала в понедельник, так как это был новостной ресурс (до 300 обращений в секунду).

            Да, optimize table решал проблему, но это грубый костыль, так как для обеспечения нормальной работы проекта, его нужно было запускать каждую 1 минуту (?).

            Использовались вполне себе нормальные индексы на 3-4 поля. Выход был только один: расставить везде force index(). Есть некоторые предположения на этот счёт, например, что причина может быть в несимметричности бинарных деревьев итп.
            • 0
              Иногда, а точней зачастую если мы работаем с большими объемами данных. То следует выполнять дополнительную нормализацию, или как это еще называют вертикальный шардинг. Т.е. это неправильно плохо иметь большую таблицу, которая хранит миллионы записей и при этом имеет 10-ки полей (столбцов), притом переменной длинны, может быть Вы там еще и само тело статей хранили на Вашем новостном ресурсе, типа TEXT? Конечно, такие запросы будут работать крайне медленно на выборку.
              В таких ситуациях следует отделять такие поля как тело статьи и прочую информацию (типы переменной длинны) в другие таблицы, а в первой стараться оставлять как можно меньше полей типа фиксированной длинны (например ID-ики и связи с другими таблицами), и только в нужные моменты подключать большие поля.
              При этом используя технологию DERIVED TABLE в которой отсекаем с помощью WHERE условий множество ненужных записей и склеиваем только, скажем, 10 нужных.

              Нужно всегда думать на стадии проектирования какие основные запросы будут приходить на какие таблицы, и в соответствии с этим формировать архитектуру.

              Притом не стоит забывать о кешировании результатов для того чтобы разгружать сервер БД
              • 0
                Там было не очень много записей (несколько десятков тысяч).

                Вообще, мне кажется, что если переломный момент не наступил, и проще сделать force index, чем заниматься нормализацией, вводя значительные доработки в код, то лучше сделать первое.

                То есть нормализация с теоритической точки зрения это, конечно, круто, но ввести её там где раньше её не было не всегда просто.
                • 0
                  Вот, мы постепенно приходим к тому, что Вы сами признаете, что Архитектура Вашей БД была далеко не оптимальной. А о архитектуре БД нужно думать в момент её проектирования, расчета кол-во запросов на единицу времени, нагруженность определенных таблиц и т.д.

                  А если уж наступил переломный момент, то нужно делать рефакторинг, а не добавлять какие-то костыли, что делаете Вы в описанном выше случае.

                  Я честно-говоря не встречал случаев где бы force index был панацеей. Но это далеко не лучший способ решения проблемы.

                  Просто эту статью прочитает, я надеюсь, не один человек, и не все люди понимаю как работает MySQL оптимизатор, как профайлить запросы (кстати, об этом будет моя следующая статья:-) ). А Вы так категорично пишете, что нельзя доверять оптимизатору и нужно самому указывать индекся для использования.
                  Вы представляете себе если Вам в руки попадет проект в котором все запросы используют force index и straight join?!!!

                  А то что у Вас были проблемы с архитектурой с этого и нужно начинать, я утверждаю, и врядли меня кто-то переубедит, что если подобрана правильная архитектура БД и грамотно расставлены индексы, то такой проблемы не возникнет!
                  • 0
                    Далеко не все запросы с force index(), только несколько критических.
                  • 0
                    force index — зло :)

                    Оптимизатор MySQL лучше знает.
                    Бывают момент, когда вам кажется что force index здесь самый оптимальный, а потом долго ищете почему сервер тормозит и из-за чего… как раз может сильно положить БД из-за вашего force index.
                    Оптимизатор в этом отношении более гибкий, он не даст полностью «упасть».
      • 0
        Если быть более точным, то имеется в виду, следить за селективностью и эффективностью приходится самому. Ежу ясно, что когда меняется селективность, надо менять и индексы (если есть такая возможность).

        Есть некоторый абстрактный момент, связанный с нагрузкой, и отдельно взятыми индексами, когда оптимизатор может отказаться пользоваться ключём с нужным полем для сортировки, отдавая предпочтение меньшему перебору, но с использованием временных таблиц, что в случае крупного ресурса приводит к полному коллапсу, когда быстрее перезапустить сервер, чем дождаться, пока всё само «рассосётся».
        • 0
          Не стоит забывать, что если запрос будет работать (обращаться) к данным, которые занимают более 30% от общего количества (цифра 30% приблизительна и колеблется от версии к версии), то MySQL выберет ROW SCAN, потому как посчитает (и вполне правомерно), что данный метод быстрей а не будет использовать индекс.
          • 0
            в интернет-проектах как правило выбирается 10 из 1 000 000.
  • 0
    а что за SQL_NO_CACHE интересно, что дает?
    • 0
      Как раз что НЕ дает — не дает MySQL кешировать результат запроса и возвращать его из Кэша. Т.е. если мы хотим потестировать производительность запроса, то без SQL_NO_CACHE — никуда :-)
      • 0
        Ясно. А вот по поводу первого примера, я попробовал сравнить с DERIVED TABLE и без с обычным inner join, вроде как быстрее почему-то во втором случае…
        • 0
          Тут нужно смотреть ваши данны в таблицах и execution plan самого запроса, то в каком порядке берутся для джоина таблицы оптимизатором.

          Понимаете, в первом случае если использует INNER JOIN, то DERIVED TABLE c вложенным WHERE неуместен. Т.к. если у нас LEFT JOIN, то мы точно знаем, что выбрали 100 записей из первой таблице и к ним присоединяем записи из другой таблице, если соответсвия нет. то заполняем записи отсутствующие в правой таблице NULL-value.

          если же мы делаем INNER JOIN и в DERIVED TABLE делаем LIMIT и вытаскиваем 100 записей а потом начинаем делать INNER JOIN, то тут необходимо строгое соответствие по условию склейки, в моем случае ON `tli`. cruser_id = fe.uid;
          Т.е. если в таблице пользователей не найдется айдишника соответствующего cruser_id, то данная запись не попадет в результирующую выборку.

          И соответственно, не факт, что мы достанем из базы 100 записей, как планировали изначально, а не 90
  • 0
    отличная статья, спасибо!

    исправьте опечаточку
    «также тормозят наши изменения дЫнных (UPDATE, INSERT, DELETE)»
    и недопечаточку
    «так вот, некоторые думаю, что MySQL сделает следующее „
    • 0
      Поправил, спасибо
  • 0
    Вообще, было бы интересно почитать ваше мнение на тему правильного подхода к выбору индексов для сложных запросов, когда джоинятся не две, а 3-4 и более таблиц и анализе плана выполнения таких запросов, т.е. как правильно понять что имеет ввиду explain :)
    • 0
      Да, я как раз хотел следующую статью посвятить теме профайлинга запросов в MySQL. И может быть пару слов скажу о индексах там… Хотя все сразу скажут, что статья получилась рваная, не целостная… и т.д… :-)
      • 0
        главное пишите на конкретных примерах, где по миллиону записей в одной табличке, и чтобы каждый мог проверить приведите пример sql запросика (или хранимки), который позволит нагенерить эти данные, будэ кому захочется проверить =)
        С нетерпением ждём )))
  • 0
    хорошая статья, но я так и не понял, как ускорить limit, если выборка идет с сортировкой/условием, когда записи выбираются не подряд… типа… WHERE public=1 ORDER BY timest desc LIMIT 5000, 100… для себя решил хранением последнего и первого timestamp'a, и преобразованием в
    WHERE public=1 and timest
    • 0
      WHERE public=1 and timestamp
      • 0
        хабр тупо хавает знаки больше-меньше, видимо думает, что это я HTML пытаюсь писать :( короче,
        timestamp меньше нашего сохраненного limit 100
  • 0
    Всегда боролся с NULL манией. Спасибо, статья очень интересная. Про count(*) никогда не заморачивался. Еще добавил бы про грамотное использование назначения индексов, которые могут ускорить работу запроса просто в разы.

    Вобщем, с удовольствием почитаю еще посты в данной тематике!

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