Несколько заметок о MySQL

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

    Буду использовать дефолтные настройки MySQL. Некоторые заметки связаны с PHP, поэтому для примеров буду использовать расширение mysqli.

    Для запуска sql-запросов из статьи можно инициализировать таблицы так:

    CREATE TABLE IF NOT EXISTS `user_myisam` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `login` varchar(8) NOT NULL,
      `money` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `login` (`login`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
    
    INSERT INTO `user_myisam` (`id`, `login`, `money`) VALUES
    (1, 'ivanov', 100),
    (2, 'petrov', 200),
    (3, 'sidorov', 300);
    
    CREATE TABLE IF NOT EXISTS `user_innodb` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `login` varchar(8) NOT NULL,
      `money` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `login` (`login`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
    
    INSERT INTO `user_innodb` (`id`, `login`, `money`) VALUES
    (1, 'ivanov', 100),
    (2, 'petrov', 200),
    (3, 'sidorov', 300);
    

    Проверить текущие автоинкременты можно так:

    SHOW TABLE STATUS;

    У обоих таблиц они равны 4. При этом имеется такое подключение к БД:

    $mysqli = new mysqli($host, $user, $password, $database);
    

    Вставка по уникальному ключу и автоинкремент


    Если в таблице есть уникальный ключ, то для вставки и обновления есть три способа переложить проверку уникальности на MySQL: INSERT IGNORE, INSERT… ON DUPLICATE KEY UPDATE, REPLACE. Каждый тип запросов по разному ведет себя с автоинкрементом на разных типах таблиц:

    INSERT IGNORE INTO `user_innodb` SET `login` = "ivanov", `money` = 1000; 
    

    Автоинкремент стал 5, хотя вставки не было. А что будет с MyISAM:

    INSERT IGNORE INTO `user_myisam` SET `login` = "ivanov", `money` = 1000; 
    

    Там автоинкремент остался 4. Аналогичная ситуация будет с ON DUPLICATE KEY UPDATE:

    INSERT INTO `user_innodb` SET `login` = "ivanov", `money` = 1000 ON DUPLICATE KEY UPDATE `money` = 1000; 
    INSERT INTO `user_myisam` SET `login` = "ivanov", `money` = 1000 ON DUPLICATE KEY UPDATE `money` = 1000; 
    

    В user_innodb автоинкремент стал 6, а у user_myisam остался 4.

    REPLACE работает иначе: в случае нахождения совпадений в уникальном ключе, он удалит старую запись и добавт новую.

    REPLACE `user_innodb` SET `login` = "petrov", `money` = 2000;

    Увеличит автоинкремент до 7, теперь у Петрова id = 6.

    У MyISAM аналогично:

    REPLACE `user_myisam` SET `login` = "petrov", `money` = 2000; 

    Автоинкремент стал 5, а Петров получил id = 4.

    Итак, REPLACE работает на обоих движках таблиц одинаково, а INSERT IGNORE и ON DUPLICATE KEY UPDATE изменяют автоинкремент на InnoDB.

    Про автоинкремент и про нюансы с InnoDB.

    Получение id изменяемой записи после обновления


    После вставки/обновления, с использованием INSERT… ON DUPLICATE KEY UPDATE, $mysqli->insert_id содержит id только если произошло добавление записи. Если нужно вытащить id изменяемой записи независимо от того была вставка или редактирование, можно сделать так:

    $mysqli->query('INSERT INTO `user_innodb` SET `login` = "walker" ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `money` = 10;'); 
    echo $mysqli->insert_id; 
    
    $mysqli->query('INSERT INTO `user_innodb` SET `login` = "walker" ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `money` = 20;'); 
    echo $mysqli->insert_id;
    

    Выведет 7 и 7, первый раз запись была добавлена под id = 7, второй раз изменена.

    С INSERT IGNORE такой трюк не выйдет. Код ниже выведет 9 и 0

    $mysqli->query('INSERT IGNORE `user_innodb` SET `id` = LAST_INSERT_ID(`id`), `login` = "smith", `money` = 3000'); 
    echo $mysqli->insert_id;
    
    $mysqli->query('INSERT IGNORE `user_innodb` SET `id` = LAST_INSERT_ID(`id`),`login` = "smith", `money` = 5000'); 
    echo $mysqli->insert_id;
    

    Реализация SEQUENCE


    У MySQL, в отличие от других СУРБД, нет такой штуки как SEQUENCE. Есть автоинкремент, но он не позвоялет решить все задачи, с которыми может помочь SEQUENCE. Например, шардинг.

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

    Решить эту задачу можно так:

    CREATE TABLE IF NOT EXISTS `sequence` (
      `user_id` int(11) NOT NULL DEFAULT '0',
      `post_id` int(11) NOT NULL DEFAULT '0',
      `some_other_id` int(11) NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `sequence` (`user_id`, `post_id`, `some_other_id`) VALUES (0, 0, 0);
    

    В одной таблице получается сразу несколько последовательностей, в этом примере три.

    Далее можно получать следующий ID из нужной последовательности с помощью функции
    last_insert_id():

    UPDATE `sequence` SET `some_other_id` = last_insert_id(`some_other_id` + 1);
    SELECT last_insert_id();
    

    Для автоинкремента есть возможность указать шаг приращения опцией конфигурации auto_increment_increment. В этом примере такую функцию можно реализовать примерно так:
    UPDATE `sequence` SET `some_other_id` = last_insert_id(`some_other_id` + N);
    SELECT last_insert_id();
    

    Немного о беззнаковых целых


    С аккуратностью используйте беззнаковые целые как типы полей MySQL, если обращаетесь к БД из PHP.

    История моего «озарения» по этому поводу. Для поля id всегда использовал беззнаковый целый тип, все равно классический id не бывает отрицательным. Однажды, генерируя модель с помощью Gii (скаффолдинг Yii), я обратил внимание, на то что правила валидации в модели для моих id и других беззнаковых целых полей генерируются как для строк. “WTF?” — подумал я и полез в код фреймворка, где обнаружил, что при разборе типов полей есть такой “хардкод” проверки на наличие unsigned:

    if(stripos($dbType,'int')!==false && stripos($dbType,'unsigned int')===false)
    

    Я посчитал это ошибкой, обрадовался, что сейчас у меня есть шанс внести свою лепту в исправление багов Yii. Но радость быстро сменилась мыслью “это ж-ж-ж-ж, неспроста”.

    Действительно, в PHP нет беззнаковых целых, а в общем случае целые в PHP 32-х разрядные (под 32-bit Linux и под Windows). Если целочисленное значение превышает PHP_INT_MAX, то оно приводится к float, и тут самое место для возникновения магии со странными багами. Так что господин Qiang Xue все правильно сделал.
    Метки:
    Поделиться публикацией
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 29
    • +1
      Далее можно получать следующий ID из нужной последовательности с помощью функции
      last_insert_id():

      UPDATE `sequence` SET `some_other_id` = last_insert_id(`some_other_id` + 1);
      SELECT last_insert_id();
      Во-первых, функция last_insert_id тут не нужна, можно и сразу с полем работать. В ряде случаев это выйдет даже экономичнее.

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

        Да, в моем примере блокировка записи будет. Но можно делать не несколько столбцов одной записи, а несколько записей одной таблицы или даже несколько таблиц. Суть была именно в получении гарантированной последовательности без повторений.
        • 0
          А что вы имеете ввиду под «сразу с полем работать»?
          Например, так:
          UPDATE `sequence` SET `some_other_id` = `some_other_id`+1;
          SELECT `some_other_id` FROM `sequence`;
          
          Или так:
          UPDATE `sequence` SET `some_other_id` = `some_other_id`+1;
          INSERT INTO `some_other_table` (id, textfield) SELECT `some_other_id`, 'какой-то текст' FROM `sequence`;
          
          Второй случай экономичнее, т.к. требует на один запрос меньше.
          • +1
            Я правильно понимаю, что вы тут неявно предполагаете, что запросы в ваших примерах должны идти в одной транзакции с блокировкой на запись?

            Иначе между UPDATE `sequence` и SELECT FROM `sequence` может произойти что угодно. Аналогично и со вторым вариантом.

            Я предлагал решение без явного старта транзакции.
            • 0
              Я правильно понимаю, что вы тут неявно предполагаете, что запросы в ваших примерах должны идти в одной транзакции с блокировкой на запись?
              Хм, да, действительно, предполагал одну транзакцию.
          • –1
            Да, в моем примере блокировка записи будет.
            Тут я, кстати, не совсем прав. Если включен автокоммит (а это по умолчанию) и если дело не происходит в рамках явно начатой транзакции, то блокировка будет довольно короткая, только на время самого UPDATE-а. Т.е. скорее всего, она будет некритична.
        • +4
          По-моему никто уже в здравом уме не использует Myisam. Это просто ненадежное хранилище данных (без записи в лог). При внезапном выключении электричества в момент записи данных всё бъется, причем не определить, что именно побилось, а что нет.
          • 0
            Задачи бывают разные. И если вас опасность потери данных беспокоит меньше скорости записи то myisam отлично подходит для этой работы.
            • 0
              Запись в таблицу myisam однопоточная. Потому что приходится лочить всю таблицу перед изменением.
              Так что innodb гораздо быстрее на запись, если сразу несколько потоков обновляют/вставляют данные.

              MyIsam не нужна вообще. Никому.
              • 0
                Фу, как голословно. Нужна мне. В моем проекте разница при использовании MyISAM более чем в 2 раза превышает скорость работы с InnoDB.
                • +1
                  Если у вас insert тормозит, то не пробовали ли вы отключать вызов fsync() на каждую вставку?
                  • 0
                    Не представляю, как это может быть. Вы что-то не то делаете с innodb
                    • НЛО прилетело и опубликовало эту надпись здесь
                      • 0
                        В проекте необходимо высчитывать тысячи различных COUNT(*). На очень многих InnoDB проигрывает в скорости :(
                        P.S. Заранее хранить их значения в отдельной таблице не представляется возможным
                        • 0
                          обычно count(*) значит статистику, а для этого используют немного другие инструменты
                          • 0
                            1. Не статистику
                            2. Другие инструменты тут не причем. Речь о MyISAM и InnoDB. И «знатоки» со знанием дела, явно, за всех решив, сказали, что MyISAM не нужен никому.
                            • 0
                              он и правда никому не нужен)
                              • 0
                                но вот прямо интересно. что за проект такой? ОО
                            • НЛО прилетело и опубликовало эту надпись здесь
                              • 0
                                count(*) на MyIsam выигрывает только в случае count по всей таблице, без каких-либо where. Но count по всей таблице делается относительно редко.
                                Если прям нужен общетабличный count, то можно использовать innodb, но кешировать где-нибудь или еще лучше обновлять по триггеру или по крону время от времени.
                    • 0
                      может стоит использовать уникальный ID для всей базы данных?
                      select UUID_SHORT()
                      • 0
                        я его вообще в приложении генерирую и горя не знаю)
                      • +2
                        Вредные советы без понимания мат.части.
                        Автоинкремент стал 5, хотя вставки не было. А что будет с MyISAM:

                        На самом деле вставка была, потому что запись сначала удаляется, а потом делается insert с новыми значениями. Чтобы это понимать нужно было прочитать документацию:
                        REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Syntax”.
                        Источник.
                        и
                        You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded.
                        Источник.

                        И если вы указываете primary key с AUTO_INCREMENT зачем при вставке данных в таблицу явно указывать поле id?

                        Вообще уровень новичка чувствуется по тому, что автор следит за изменением поля AUTO_INCREMENT, созданного для того, чтобы не следить за ним вообще.
                        • НЛО прилетело и опубликовало эту надпись здесь
                          • –1
                            А я не понимаю как такая статья могла набрать 30 плюсов… Я вот в даже в кошмарном сне не могу представить в каких случаях может понадобится такая структура таблицы «sequence», что нет ни одного обязательного параметра и таблицу можно заполнять нулями…
                            хотя следить за ним иногда бывает нужно, например, после вставки записи и сразу за этим следующим добавлением данных в связанную таблицу уже с foreign key.

                            Для этих целей можно написать триггер.
                            • 0
                              На какую таблицу? А вообще, для размазывания логики в том числе на СУБД нужны, как правило, веские основания, а не просто единичный кейс.
                              • 0
                                Не знаю, насколько страшные вам снятся сны. Однако, разрабочики многих других СУРБД сделали реализацию SEQUENCE. В MySQL этого дела нет, но решение по эмуляции, которое я предложил тут, есть в документации (см. в конце описания функции LAST_INSERT_ID()).
                            • 0
                              Ок, каким образом вы получите в приложении id, если не «следить» за ним? Например, если у вас, в приложении одна логика для вставки и изменения с одним запросом INSERT… ON DUPLICATE KEY UPDATE?

                              PS. С REPLACE понятное дело, неудачный оборот «вставки не было», вставка конечно была после удаления. Просто имелось ввиду, что не было вставки новых данных в таблицу.
                            • 0
                              Оставлять дефолтные настройки на MySQL это очень неправильно. Как минимум нужно выставлять свой fsync и innodb_buffer_pool_size. Так как значения по-умолчанию убивают производительность очень сильно.

                              Поищите на англ. статьи вроде «MySQL performance tuning» особо порекомендую статьи от Percona.

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