Меню

Mysql как устанавливать счетчик

Mysql как устанавливать счетчик

Я буду время от времени писать интересные технические заметки, мало ли разработчики тоже читают наш блог. ? Итак, сегодня попытаемся решить очень простую задачку – организовать счетчики в MySQL. Для чего это нужно? Примеров использования очень много: нужно для каждой страницы сайта хранить количество обращений, при обработке большого объема данных нужно посчитать частоты встречаемости элементов, нужно контролировать количество запросов для каждого пользователя в системе… В общем, задача частенько встречается.

Используем “ненадежные” хранилища

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

  • Храните все счетчики в памяти и периодически сбрасывайте контрольные значения на диск в инкрементальные файлы с указанием временной метки. Такой способ позволяет потерять только часть данных от последнего сохранения.
  • Используйте Redis с его возможностью ведения счетчиков. Работает очень быстро и достаточно надежно. Но, если у вас еще не используется Redis, то добавлять его только для счетчиков достаточно спорно. И вторая проблема заключается в транзакционности – счетчик нужно обновлять при успешном прохождении транзакции (в идеале в рамках транзакции), а иначе иногда при падениях или остановках системы счетчик может рассинхронизироваться с реалиями.
  • Используйте ZooKeeper с его распределенными счетчиками. Он предлагает функциональности больше чем вам нужно, но в некоторых случаях работает очень даже неплохо. Снова таки, вводить ZooKeeper только для счетчиков я не рекомендую. И при большой нагрузке он может стать узким местом, так как работает в один поток.

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

Тупое решение в лоб

Вам все таки нужны точные счетчики, привязанные к бизнес-транзакции и вы решили реализовать их на уровне БД. От этой точки и до конца статьи все примеры будут приводиться для MySQL. Решение напрашивается, потому что БД у вас уже есть и почему бы не использовать ее и для этой цели. Первым решением, которое приходит в голову, будет создание отдельной таблички:

Ну и чтобы обновить счетчик, нужно вызвать банальный код:

У этого решения есть несколько “сюрпризов”. Первый из них очень простой и понятен каждому, кто знает как работают базы данных. В случае обновления счетчика из нескольких потоков все они будут заблокированы и выполнятся в порядке очереди. Поэтому производительность будет не совсем радостной. Но есть и еще один интересный “сюрприз” – наличие deadlock-ов в банальном коде. Они могут проявляться сразу в нескольких случаях:

  • Вы в одной транзакции обновляете сразу несколько счетчиков. Параллельно подобных транзакций может выполняться несколько. Если вы не контролируете порядок resource_id, то в силу специфики захвата lock-ов по индексу (в примере он PK, но в любом случае он понятное дело у вас должен быть, чтобы поиск счетчика по resource_id работал быстро) вы получите deadlock.
  • Еще один сценарий deadlock-а появится, если вы будете производить действия с самой таблицей ресурса в той же транзакции для другой записи (например, дочерней или родительской) а вместо простого индекса будете использовать FK на таблицу ресурса.

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

Только вставки, ничего кроме вставок

И тут вам приходит в голову оптимизация. Вы вспоминаете, что вставки практически не блокируют друг друга, в отличие от обновлений данных. Поэтому вы решаете убрать PK с resource_id и вместо обновлений счетчика добавлять новые данные. Для подсчета же общего значения счетчика вы будете просто использовать следующий запрос:

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

Подсчитываем промежуточные итоги

Чтобы избавиться от “мусорных” исторических данных существует множество однотипных простых техник. Нужно время от времени агрегировать данные и удалять или “виртуально удалять” старые. Давайте рассмотрим немного детальнее. Вы запускаете job в отдельном потоке либо на уровне БД либо на уровне вашего приложения. Этот job бежит по таблице счетчиков, подсчитывает сумму по каждому ресурсу и вместо набора записей оставляет ровно одну со значением суммы. Как это реализовать технически:

  • Блокировать доступ к определенному ресурсу на время проведения операции. Проблема заключается в том, что другие потоки продолжают вставлять значения. А это значит, что без блокировки вы удалите старые записи, среди которых могут быть и новые. Тем самым рискуете поломать счетчик. Блокировку можно делать как на уровне БД так и в коде. Способ не очень хороший, потому что блокировки всегда замедляют работу.
  • Второй способ заключается в добавлении новой колонки с временем добавления записи (тип TIMESTAMP). Теперь вы можете безопасно посчитать сумму за 5 минут в прошлое и удалить записи, которые уже не нужны в той же транзакции. Сумма добавляется как новая запись за текущее время. На самом деле такой способ тоже чреват дополнительными блокировками при удалении и deadlock-ами при параллельной вставке и удалении по индексу, который у вас есть на resource_id.
  • Третий способ заключается в том, чтобы использовать отдельный поток для чистки, а агрегированные записи помечать специальным маркером. В этом случае правильное значение счетчика будет равно сумме всех записей, начиная с последнего агрегированного значения. Исторические данных могут удаляться по одной или блоками в любое время безо всякого риска.
Читайте также:  Часы со встроенным счетчиком гейгера

Третий способ, пожалуй, является самым быстрым и безопасным из перечисленных в плане блокировок. Подобное решение является “виртуальным удалением” и часто используется для подсчета баланса за определенные периоды. Агрегированные записи могут как удаляться так и оставаться в системе для построения временных графиков. Подсчет значения счетчика более-менее фиксирован по времени и зависит от периода агрегации.

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

Чистим за собой быстро

Чтобы избежать удаления данных, нужно немного напрячься и вспомнить об операции TRUNCATE TABLE. Она очищает данные очень быстро. Но не все так просто, придется немного изменить алгоритм обновления значений счетчиков.

Для этого нам понадобятся вместо одной сразу 2 или 3 таблички одинаковой структуры: resource_counter, resource_counter_shadow, resource_counter_total (эта табличка является необязательной). Каждая из них будет поддерживать только вставки. Ваше приложение пишет все изменения значения счетчика в виде дополнительных записей в таблицу resource_counter и только в нее. Параллельно работает отдельный поток, который раз в определенное время производит замену таблиц:

Запрос является атомарным и по сути меняет местами таблицы resource_counter и resource_counter_shadow. Получается, что с таблицей resource_counter_shadow никто не работает и можно быстро сделать агрегацию данных в ней. Полученные результаты можно добавить в таблицу resource_counter запросом:

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

Есть еще более симпатичная версия этого же запроса:

Ну и конечно же, после использования таблицы resource_counter_shadow она очищается. За исключением необязательной таблицы, данный подход требует минимальное количество блокировок, но повышает количество “переливаний данных”.

Применяем мульти-счетчик

В какой-то момент времени вы задумаетесь, а не слишком ли все стало сложно и обратитесь к изначальной проблеме. Она заключалась в том, что запись в таблице счетчика блокируется при обновлении. Но в то же время, формирование отдельных записей для каждого обновления приводит к росту их количества и необходимости чистить данные. Тогда надо использовать золотую середину. Для этого в таблицу счетчика добавляется новая колонка counter_index, которая включается в состав PK:

Теперь наша цель распределить обновление по нескольким записям таблицы, сделав возможность блокировки менее вероятной. От качества распределения будет зависеть количество блокировок по одному счетчику. Для простенькой версии можно использовать следующий запрос:

Для получения значения счетчика по определенному ресурсу по-прежнему нужно будет использовать сумму, но по фиксированному количеству строк (максимум 10 в нашем примере):

Работает быстро как на обновление так и на получение значения. Балансировать можно количеством счетчиков на один ресурс.

Как видите, решений такой простенькой задачи достаточно много, если знать предметную область и тщательно тестировать ваши решения. Надеюсь, это сэкономит кому-то время. ?

P.S. Все запросы писались напрямую в текстовом редакторе, поэтому мелкие ошибки и опечатки просьба указывать в комментариях, но в нежной форме. ?

Не хочешь пропускать ничего интересного? Подпишись на ленту RSS или следи за нами в Twitter!

Источник

Настройка часового пояса MySQL

Хранение даты в MySQL с учетом часового пояса — непростая тема. Однако с ней обязательно нужно разобраться, чтобы сайты и приложения работали правильно.

Время и дату в MySQL можно указывать в TIMESTAMP и DATETIME.

  • DATETIME — здесь время не зависит от часового пояса и выводится в том виде, в котором было изначально задано. Изменение времени в MySQL не позволяет изменить значение в DATETIME. Его удобно использовать для хранения дат.
  • TIMESTAMP — абсолютное значение времени с начала эпохи Unix (с 00:00 1 января 1970 года). При выводе этого значения учитывается часовой пояс, который может быть указан для системы, MySQL в целом или конкретной сессии. Его удобно использовать для хранения времени публикаций на сайте, совершения заказов и других событий, которые происходят в режиме реал ьного времени.

Для определения времени с учетом часового пояса MySQL можно отредактировать конфигурационный файл или использовать переменные. Мы рассмотрим оба способа.

Как установить время MySQL в конфигурационном файле

Самая большая проблема — найти конфигурационный файл my.cnf. Он может храниться как минимум в 5 местах:

Если вы нашли конфигурационный файл, то дальше уже сложностей не будет. Чтобы установить часовой пояс, добавьте в секцию [mysqld] следующую строку:

В этом случае мы прибавляем 2 часа относительно UTC — всемирного координированного времени. Поддерживаются также другие значения:

  • ‘SYSTEM’ — часовой пояс сервера совпадает с системным часовым поясом;
  • именованный часовой пояс — например, ‘Europe/Moscow’.

Чтобы применить конфигурацию, перезапустите MySQL.

Как установить часовой пояс MySQL через переменные

Чтобы изменить часовой через переменные в консоли, нужны права суперпользователя. Авторизуйтесь под root и укажите пароль. Затем посмотрите текущие дату и время. Сделать это можно командой

Читайте также:  Мосэнерго вакансия контролер счетчиков

Если хотите вывести только время (без даты), выполните в консоли

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

В качестве примера мы поставили время, которое смещено относительно UTC на 2 часа. Однако у такого подхода есть большой недостаток. Если в стране принято переводить часы на летнее/зимнее время, то запрос обрабатывается неверно. В зависимости от текущего периода можно получить время на час больше или меньше. Чтобы устранить эту особенность, задайте временную зону в таком формате:

Однако и у такого подхода есть свои подводные камни. Например, в стране могут измениться правила перехода на зимнее и летнее время. В России за последнее десятилетие это случилось два раза: сначала перешли на летнее время, отказавшись от зимнего, а затем сделали наоборот — отказались от летнего, оставив только зимнее. Поэтому нужно следить за актуальностью данных и вносить изменения в часовые пояса MySQL вручную.

Чтобы изменения сохранялись после перезагрузки и применялись ко всему серверу баз данных, используйте глобальную переменную:

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

Можно проверить, требуется ли задавать время для конкретной сессии, выполнив команду

Если время сессии совпадает с системным, то задавать его отдельно не требуется.

Для корректной работы веб-приложения время PHP и MySQL должны совпадать. Если вы корректируете время в PHP, то нужно изменить часовой пояс MySQL, и наоборот.

Источник

Как изменить счетчик автоматического увеличения в MySQL на php?

Как изменить счетчик автоматического увеличения в MySQL

ALTER TABLE table_name AUTO_INCREMENT = value;

PS Но вообще неплохо бы подробнее описать, что именно вы хотите получить в итоге. Возможно, то, что вы хотите сделать, возможно сделать проще через phpMyAdmin — сомневаюсь, что это будет не разовая задача.

Руслан Федосеев, потому что он сбивается при удалении строк
например удалил 4 строки и добавил четыре и вот что получается

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

Очень много раз говорилось — не надо пытаться заполнять пустоты в autoincrement

ЗЫ. У меня вот вообще он только четные значения принимает в одной базе.. И только ооочень редко нечетные. Знаете почему? Потому что база реплицирована в режиме master-master и потенциально добавление записей может происходить на любом сервере.

подсказать что? есть такое понятие — нормальные формы базы данных, ссылочная целостность и т.д.
для реализации этого используются триггера, внешние ключи и т.д. При удалении записи из основной таблицы триггером удаляются все подчинненые данные и т.д.

Проектируйте базу правильно.
А модифицировать структуру баз на ходу — это ненормально

Источник



Как сбросить AUTO_INCREMENT в MySQL/MariaDB

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

Но у AUTO_INCREMENT есть одно интересное свойство — при удалении строк значение счётчика AUTO_INCREMENT не уменьшается. То есть, например, в таблицу было добавлено 10 записей, после этого все записи были удалены, а затем стали добавлять новые записи — в результате у первой новой записи значение столбца с AUTO_INCREMENT будет 11, у второй будет 12 и так далее.

Отсюда иногда возникает задача — сбросить значение AUTO_INCREMENT или установить AUTO_INCREMENT на единицу.

Сброс AUTO_INCREMENT для InnoDB баз данных

Если таблица не пустая, то для таких таблиц с движком хранения InnoDB невозможно установить AUTO_INCREMENT равное или меньшее значению в последней записи. Это вполне логично, поскольку в противном случае это привело бы к дублированию значения, которое должно быть уникальным для каждой строки.

Как сбросить AUTO_INCREMENT в phpMyAdmin

В веб интерфейсе phpMyAdmin вы можете просмотреть текущее значение AUTO_INCREMENT и изменить его. Для этого выберите таблицу, для которой вы хотите поменять AUTO_INCREMENT, затем перейдите в Операции → Параметры таблицы → AUTO_INCREMENT:

Установите желаемое значение — помните об ограничении для не пустых таблиц InnoDB.

Очистка таблицы и сброс AUTO_INCREMENT

Как уже было сказано, если удалить записи обычными средствами, то это удаление не сбрасывает значение AUTO_INCREMENT даже если удалены абсолютно все строки.

Выполнить удаление всех записей вместе со сбросом счётчика AUTO_INCREMENT можно командой:

Изменение/сброс счётчика AUTO_INCREMENT без очистки таблицы

Другим вариантом является следующий SQL запрос:

Обратите внимание, что AUTO_INCREMENT можно установить на любое значение — но помните об ограничении для непустых таблиц InnoDB.

И ещё очень важное замечание об ALTER TABLE. Дело в том, что этот запрос приводит к перестроению всей таблицы. То есть MySQL создаст новую таблицу с той же структурой и добавит новое значение auto_increment и скопирует все записи с оригинальной таблицы, удалит оригинальную таблицу и переименует новую. Если таблица содержит много записей, то этот процесс может затянуться очень надолго.

Читайте также:  Причины неисправности электронных счетчиков электроэнергии

Если нужно увеличить значение счётчика AUTO_INCREMENT, то проще вставить ненужную строку (а затем удалить, если нужно). Это займёт долю секунды, в то время как ALTER TABLE может потребовать дни для больших таблиц.

Допустим, я у меня есть таблица со столбцом auto_increment ID и другими столбцами col1, col2…:

Как уменьшить значение AUTO_INCREMENT для InnoDB

Предположим, в таблице имеется 10 записей, при этом у последней записи столбец AUTO_INCREMENT равен 20. Как изменить значение этого столбца ID, чтобы следующая запись имела номер 11?

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

Итак, первый вариант — убрать свойство AUTO_INCREMENT у столбца ID, выполнить запрос для переназначения ID, и затем вернуть AUTO_INCREMENT:

Не нужно знать текущее максимальное значение. Этим способом счётчик автоматического прибавления будет сброшен и запущен автоматически с максимального существующего значения.

Но помните о медленной работе ALTER TABLE для больших таблиц.

Ещё один вариант — полностью удалить столбец id, а затем его вернуть с предыдущими настройками. Все поля внутри таблицы заполняются новыми значениями счётчика.

Помните о том, что:

  • все поля внутри таблицы будут посчитаны заново и у них будут другие id
  • описанный метод полностью поломает foreign keys

Источник

Счётчик

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

Автоинкрементный счетчик
В общем такая вот штука: в sql server 2008 не могу понять как добавить запись в которой.

Триггер-счетчик
Есть таблица Участники и отдельная таблица Кол-во участников с int полем amount, которое выступает.

Модифицировать целочисленное поле в счетчик
Вообщем, есть интовое поле и нужно модифицировать его в интовый счетчик create database test .

так в свойствах столбца установлно а вот в если в определении таблицы щулкую на поле то там нет! а там мне как свойства подправить!?

Добавлено через 1 минуту

Листинг 5.3. Синтаксис оператора SET IDENTITY_INSERT
SET IDENTITY_INSERT [[ .] .]
< ON | OFF >;

IDENTITY ( , )
Для подобного автоинкрементного столбца нельзя задавать значение по умолчанию
(предложение DEFAULT). Столбец с такой характеристикой должен иметь тип данных
TINYINT, SMALLINT, INT, BIGINT, DECIMAL или NUMERIC. Если ему задается тип данных
DECIMAL или NUMERIC, то количество дробных знаков должно быть указано нулевым.
Столбец типа IDENTITY может быть в таблице только один.

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

Счетчик в таблице неверно нумерует
Создал таблицу в MS sql server. столбец с ID нумерует без остановки, на пример идет нумерация.

Установить поле счетчик id (identity) на начальное значение .
Есть поле id decimal(18,0) identity(1,1) NOT NULL. После вставки записей счетчик увеличивается.

Завершить хранимую процедуру выдав ошибку не выполняя ROLLBACK/COMMIT (чтобы счетчик не изменялся)
Прошу помощи в следующем — есть процедура, которая выполняет INSERT. Но перед этим необходимо.

Источник

Как изменить счетчик автоматического приращения в MySQL?

У меня есть поле ID, которое является моим первичным ключом и является просто полем int.

У меня меньше!—3—>300 строки, но теперь каждый раз, когда кто-то подписывается, что ID auto inc вводится очень высоко, как 11800089, 11800090, etc. есть ли способ заставить это вернуться, чтобы он мог следовать порядку (310,311,312).

6 ответов

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

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

Если вы используете MySQL или какой-либо другой SQL server, вам нужно:

  1. резервное копирование базы данных
  2. отбросьте столбец id
  3. экспорт данных
  4. TRUNCATE или «пустой» стол
  5. пересоздать

предполагая, что вы используете mysql, потому что вы используете PHP. Вы можете сбросить auto_increment с помощью оператора

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

счетчик автоматического приращения для таблицы может быть (re)установлен двумя способами:

выполнив запрос, как и другие, уже объясненные:

используя верстак или другой визуальный инструмент проектирования баз данных. Я собираюсь показать на верстаке, как это делается, но это не должно сильно отличаться и в другом инструменте. Щелкните правой кнопкой мыши по нужной таблице и выберите Alter table в контекстном меню. На дне можно увидеть доступные варианты изменения таблицы. Выберите Options и вы получите эту форму:

затем просто установите желаемое значение в поле Auto increment как показано на рисунке. Это в основном выполнит запрос, показанный в первом варианте.

Я считаю, что mysql делает выбор max на id и ставит следующий. Попробуйте обновить идентификаторы таблицы до нужной последовательности. Проблема у вас будет, если они связаны, вы должны поставить Каскад на обновление на fk. Вопрос, который приходит мне на ум:

700 что-то меньше, чем 11800090 у вас есть и рядом с 300, где id>0;

Я считаю, что жалобы MySQL, если вы не ставите где

Я играл вокруг аналогичной проблемы и нашел это решение:

Я надеюсь, что это поможет кому-то в подобной ситуации!

Источник