Как почистить базу данных sql
Действия при переполнении базы данных
Содержание
Введение
В данной статье описываются действия при переполнении базы данных.
В случае переполнения базы данных у вас могут проявляться следующие симптомы: не отображается статистика АТС, система не запускается, зависание линий и др.
ВНИМАНИЕ: Если сделать «Профилактика БД и удаление данных 2 дня«, то база может «упасть» и восстанавливать ее можно только из бэкапа. Происходит это из за того, что запрос может выполняться долгое время и если его прервать, то восстановить БД уже будет сложно. Дело в том, что при выполнении данной процедуры создается некоторый кэш внутри бд. Если база переполнена — он не создастся.
Для использования инструкции вам потребуется установленная SQL Server Management Studio.
Создание бэкапа
Анализ размера таблиц БД
Определение таблиц, которые занимают больше всего места в вашей базе данных. Воспользуйтесь хранимой процедурой A_DB_HelpTables. Выполните в SSMS следующий запрос:
Удаление данных
Используя инструкцию Delete
Для очищения таблиц воспользуйтесь следующим запросом:
Описание запроса: Запрос удаляет записи из таблицы [oktell].[dbo].[A_Stat_Connections_1x1] по неделям, начиная с самой последней (первое условие в запросе). При этом запрос, в любом случае, оставит записи за последний месяц (второе условие). После выполнения запроса производится очистка таблицы от временных записей (логи, транзакции) командой SHRINK.
Нажмите F5 для выполнения запроса. В случае удачного выполнения из таблицы удалятся записи за неделю. Нажмите F5 еще несколько раз, пока не увидите в сообщениях, что обработано 0 строк.
Для проверки правильности выполнения предлагается использоваться следующий запрос:
На рисунке показан пример выполнения данного запроса. Запрос выполнялся 06.12.13 и как видно в результате выполнения очистки таблицы, остались все записи не позднее, чем 30 дней от текущего дня.
Используя инструкцию Truncate table
TRUNCATE TABLE (Transact-SQL) удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.
Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.
ВАЖНО! Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.
Более подробную информацию можно найти по ссылке TRUNCATE TABLE (Transact-SQL)
Если выполняются следующие условия:
Для проверки выполним код
В результате выполнения видно, что размер БД Oktell составляет 119.50 MB (столбец db_size), БД не переполнена.
, то можно выполнить следующие инструкции:
1)Создадим копию очищаемой таблицы в не переполненной базе данных, при этом записывая в нее данные которые нам требуется сохранить. Для этого выполним следующий код:
2)Очистим исходную таблицу от данных используя инструкцию TRUNCATE TABLE
3)Копируем данные из вспомогательной таблицы обратно в исходную таблицу
4)Очищение таблицы завершено, после этого можно удалить вспомогательную таблицу
Генеральная уборка продолжается: как почистить базу MySQL
Дата публикации: 2016-07-20
От автора: вы думаете, что везде убрали? Дом вычистили, машину помыли, детей до блеска «выдраили». А на своем сервере СУБД забыли прибраться! Не умеете? Это не страшно! Сейчас я научу вас, как почистить базу MySQL.
Зачем убирать?
Чтобы не было мусора и пыли. В случае с базами данных имеется в виду «цифровой» мусор. Если ваш ресурс работает на основе CMS, то даже в этом случае в таблицах остаются различные «ошметки» после работы плагинов, системы движка. Ну, может и вы где-то тоже строку «забыли». Постепенно вся эта «пыль» скапливается в огромные пласты, которые затем негативно влияют на быстродействие всего ресурса. А это негативно отражается на юзабилити сайта. В общем, чистить все равно надо и придется.
Кроме этого «выросшая» как на дрожжах база может занимать много виртуального дискового пространства, в результате чего хостер начнет нервничать и заваливать вас сообщении о превышении лимита. Если ничего не предпримите (не перейдете на дорогостоящий тариф, в котором предоставляются большие «площади» под сайт), то он просто «выдернет из розетки» ваш ресурс. Поэтому лучше научиться, как почистить базу данных MySQL, чем тратиться на новый тарифный план или переезд на «новые рельсы».
Также иногда требуется удалить только содержимое таблицы, и сберечь ее структуру. Все это мы рассмотрим в сегодняшнем материале, посвященном правильной уборке на СУБД. Стартуем!
Очищаем таблицу
Часто требуется не удалять таблицу, а лишь очистить ее от всех строк, но при этом сохранить всю ее структуру: типы данных столбцов, индексы, первичные ключи и другие ее параметры. Конечно, можно всегда удалить таблицу целиком с помощью команды DROP TABLTE, но затем придется создавать ее и прописывать все атрибуты заново. А это не совсем удобно, поэтому в MySQL для удаления строк таблицы есть специальная команда. Ее синтаксис:
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Очистка и оптимизация базы данных
Если вы давно не чистили базу данных, то в ней накопилось много неиспользуемых записей, которые увеличивают ее размер и замедляют сайт.
В этой статье вы узнаете, как очистить и оптимизировать базу данных с помощью SQL запросов в приложении phpMyAdmin на хостинге, и как очистить и оптимизировать базу данных с помощью плагинов.
Перед началом работы сделайте бэкап. Если что-то пойдет не так, вы сможете восстановить базу данных из бэкапа.
Удалите неиспользуемые плагины. Удалите все плагины, которые вы не используете. Остатки от удаленных плагинов — один из источников мусора.
Как делать SQL запросы к базе данных
Для администрирования БД обычно используется приложение phpMyAdmin, которое находится в хостинг панели. Найдите «База данных», «Database», «MySQL» или что-нибудь подобное. Откройте нужную базу данных.
Если вы забыли или не знаете название, логин или пароль к своей базе данных, то их можно узнать в файле wp-config.php, который находится в корневой папке сайта. Записи о базе данных находятся в самом начале файла.
Если у вас русский язык:
Если у вас английский язык:
Запросы создаются на вкладке SQL. Документация.
Делайте запросы здесь. Когда запрос готов, нажмите Вперед.
Если вы получили ответ, что 0 таблиц было изменено, то это значит, текущее состояние параметра соответствует запросу.
ЗАПРОСЫ
Удаление данных от удаленных плагинов и данные постов
После удаления ненужных плагинов в таблице wp_postmeta могут остаться записи от них. В этой же таблице находятся мета данные постов.
Создайте запрос для удаления неиспользуемых записей плагинов и постов, которые остались после удаления плагинов или постов.
DELETE FROM wp_postmeta WHERE meta_key = ‘ваш-мета-ключ‘;
Замените ваш-мета-ключ на нужное значение.
DELETE FROM wp_#_postmeta WHERE meta_key = ‘ваш-мета-ключ‘;
Измените # на ID сайта и ваш-мета-ключ на нужное значение.
Удаление спам комментариев
Удалить весь спам из бд можно этим запросом:
DELETE FROM wp_comments WHERE comment_approved = ‘spam‘;
DELETE FROM wp_#_comments WHERE comment_approved = ‘spam‘;
Измените # на ID сайта.
Удаление комментариев, ожидающих проверки
Если у вас много спама, ожидающего проверки, вы можете удалить его этим запросом. Перед удалением проверьте, чтобы не удалились нужные комментарии.
DELETE FROM wp_comments WHERE comment_approved = ‘0‘;
DELETE FROM wp_#_comments WHERE comment_approved = ‘0‘;
Измените # на ID сайта.
Удаление неиспользуемых тегов
Если у вас есть тег, который не связаны ни с одной статьей, удалите его этим запросом:
DELETE FROM wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = ‘post_tag’ AND wtt.count = 0;
DELETE FROM wp_#_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = ‘post_tag’ AND wtt.count = 0;
Измените # на ID сайта.
Удаление Trackback и Pingback
Если вы выключили эти функции и хотите удалить их записи из базы данных, используйте эти запросы:
Trackback
DELETE FROM wp_comments WHERE comment_type = ‘trackback‘;
DELETE FROM wp_#_comments WHERE comment_type = ‘trackback‘;
Измените # на ID сайта.
Pingback
DELETE FROM wp_comments WHERE comment_type = ‘pingback‘;
DELETE FROM wp_#_comments WHERE comment_type = ‘pingback‘;
Измените # на ID сайта.
Выключить эти функции в WordPress можно в Настройках — Обсуждения.
Удаление ревизий постов
Сохраненные версии постов хранятся в базе данных. Если у вас большой сайт, большое количество ревизий сильно увеличивает ее размер. Чтобы удалить их все, используйте этот запрос:
Замените # на ID сайта.
Удаление шорткодов плагинов и тем
Если вы пользовались каким-то плагином, который вставляет шорткоды, или вы сменили тему, в которой были встроены шорткоды, то теперь шорткоды появятся на сайте в виде текста. Чтобы удалить все записи о шорткодах из базы данных, сделайте этот запрос:
UPDATE wp_post SET post_content = replace(post_content, ‘[ваш-шорткод]‘, »);
UPDATE wp_#_post SET post_content = replace(post_content, ‘[ваш-шорткод]‘, »);
Измените # на ID сайта.
Удаление постов старше Х дней
Если вы хотите удалить посты старше Х дней, используйте этот запрос:
DELETE FROM ‘wp_posts’
WHERE ‘post_type’ = ‘post’
AND DATEDIFF(NOW(),’post_date’) > X-дней
Замените X-дней на нужное число дней.
DELETE FROM ‘wp_#_posts’
WHERE ‘post_type’ = ‘post’
AND DATEDIFF(NOW(),’post_date’) > X-дней
Измените # и X-дней.
Удаление других комментариев
SELECT FROM wp_commentsmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
);
Если вы хотите очистить таблицу wp_commentsmeta на другом сайте в сети, используйте этот запрос:
SELECT FROM wp_#_commentsmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_#_comments
);
Замените # на ID сайта.
Оптимизация базы данных
Вы можете оптимизировать таблицы базы данных без использования плагинов.
В phpMyAdmin выберите нужную базу данных, спуститесь вниз, нажмите Отметить все и выберите Оптимизировать таблицу в выпадающем меню. Нажимать «Вперед» не нужно, оптимизация начнется автоматически.
Если ваша базы данных не помещается на одной странице, то вам нужно оптимизировать таблицы на каждой странице. Можно оптимизировать все таблицы, или только самые большие по размеру. Размер таблицы указывается в столбце Размер.
Пустой результат означает, что ничего не было оптимизировано, потому что таблицы уже оптимизированы плагином, который установлен на сайте.
База данных свежей установки Вордпресс 4.9.8 для сравнения
Плагины WordPress для оптимизации базы данных
4 лучших плагина оптимизации базы данных в порядке увеличения функционала.
Сделайте бэкап базы данных перед использованием этих плагинов. Некоторые из плагинов удаляют посты, имеющие статус Черновик. Убедитесь, что все Черновики опубликованы.
WP Sweep
Current Version: 1.1.5
Last Updated: 21.07.2021
WP Sweep (бесплатный) очищает базу данных от неиспользуемых и служебных данных и оптимизирует базу данных. Плюс плагина в том, что для оптимизации он использует стандартные функции удаления Вордпресс. Все просто и безопасно. Минус в том, что в нем нет расписания для автоматического сервиса.
WP Optimize
Current Version: 3.2.1
Last Updated: 30.11.2021
WP Optimize (бесплатный) — делает то же самое, но в нем есть автоматическое расписание очистки и оптимизации БД. Для оптимизации базы данных он использует прямые запросы к базе данных и оставляет после себя некоторые записи. Настроек минимум, подходит для новичков.
У плагина более 1 миллиона установок, регулярно обновляется, написан группой известных разработчиков.
В одной из последних версий в плагине появилось кеширование на уровне функционала плагина WP Super Cache и сжатие изображений.
Advanced Database Cleaner
Current Version: 3.0.3
Last Updated: 03.08.2021
Advanced Database Cleaner (бесплатный) аналогичен предыдущему, но есть мощный инструмент редактирования базы данных вручную, редактирования cron-расписания и редактирования таблицы options базы данных. Плюс в том, что есть все, что нужно, некоторый минус в том, что для оптимизации используются прямые запросы к базе данных.
Этот плагин, так же как и предыдущие, пропускает некоторые записи (например, от удаленных плагинов), которые можно очистить только вручную. Для того, чтобы очистить вручную, нужно знать, чему соответствует каждая запись в базе данных.
Advanced Database Cleaner Pro
Advanced Database Cleaner Pro (платный) имеет весь функционал бесплатного плагина и показывает, чему соответствует каждая запись в базе данных.
Описание некоторых терминов, которые используются БД.
Orphan post meta. Когда вы создаете новый пост, у него появляются мета данные. Это Автор поста, Категория поста и дата публикации или изменения. В некоторых случаях эти мета данные остаются в базе данных неудаленными и не относящимися ни к одному посту. Такие данные называются orphan post meta, их надо удалять.
Orphan comment meta. То же самое, что post meta, — комментарии, которые не относятся ни к одному посту. Нужно удалять.
Orphan relationships. Таблица wp_term_relationships содержит записи о связях между постами, которые связывали удаленные посты. Если вы удаляете контент на сайте, то связи между удаленными страницами остаются в этой таблице. Со временем эти записи наполняют таблицу и занимают место в базе данных. Удаляйте.
Dashboard transient feed. Это врéменные данные, которые хранятся в базе данных и имеют срок существования, после чего удаляются.
Заключение
После оптимизаци базы данных сайт должен начать работать немного быстрее. Бэкап будет работать быстрее. На больших сайтах с высокой посещаемостью после очистки и оптимизации базы данных сайт будет работать заметно быстрее.
Если вы будете использовать один из плагинов для автоматической очистки базы данных, не оставляйте неопубликованный контент на сайте. Многие плагины во время автоматической очистки удаляют неопубликованный контент.
Восстановление и оптимизацию базы данных можно настроить по расписанию. В зависимости от посещаемости и контента выберите 1 раз в месяц для восстановления и 1 раз в неделю для оптимизации.
Читайте также:
Надеюсь, статья была полезна. Оставляйте комментарии.
Как очищать данные при помощи SQL
За время работы автору довелось использовать многие инструменты анализа, включая Excel, R и Python. Попробовав PostgreSQL и TimescaleDB, автор поняла, насколько простыми могут быть задачи очистки. Делимся подробностями сравнения PostgreSQL и Python из блога TimescaleDB, пока у нас начинается курс по аналитике данных.
Процесс анализа вкратце
Раньше столбцы и значения данных приходилось редактировать вручную. Приходилось извлекать «сырые» данные из CSV-файла или базы данных, а затем изменять их в Python-скрипте.
Приходилось ждать, пока машина настроит и очистит данные. А чтобы поделиться очищенными данными, нужно было запускать скрипт или передавать его другим людям. Но теперь благодаря PostgreSQL я один раз пишу запрос очистки на SQL прямо в базе данных и сохраняю результаты в таблице.
О наборе данных
Бо́льшую часть работы по очистке я проводила после анализа. Но иногда полезно очистить данные, оценить их и снова очистить. Именно с таким случаем мы и будем работать. В одном из наборов данных Kaggle содержатся показания потребления энергии одной из квартир в Сан-Хосе, штат Калифорния. Данные обновляются каждые 15 минут и следуют приблизительно такой схеме:
Вот что приходит в голову первым в смысле очистки:
Тариф — текстовый тип, а это вызовет проблемы.
Столбцы времени и даты разделены, что может вызвать проблемы при создании графиков или моделей на основе показателей времени.
Может понадобиться отфильтровать данные по временным параметрам, например по дню недели или конкретным праздникам (оба параметра влияют на потребление энергии).
К процессу очистки в PostgreSQL можно подойти по-разному: можно создать таблицу, а затем изменить её при очистке, создать несколько таблиц при добавлении или изменении данных или работать с представлениями. В зависимости от размера данных эти подходы могут иметь смысл, но вычисления будут выполнятся по-разному.
Часто при работе с большим объёмом данных изменение таблицы в PostgreSQL может оказаться дорогим. Я покажу, как с помощью представлений и дополнительных таблиц создать чистые данные.
Проблемы структуры
Разделённые столбцы даты и времени надо преобразовать в метку времени, а столбец тарифов— в тип float4. Подробности ниже.
Гипертаблицы TimescaleDB, и почему важна метка времени
В основе эффективности запроса данных временного ряда и управления этими данными лежат гипертаблицы TimescaleDB. Они разделяются по столбцу времени, который вы укажете при создании таблицы.
Данные разделяются по метке времени на «куски», так что каждая строка таблицы принадлежит какому-то куску исходя из диапазона. Позже эти куски используются в запросах строк, чтобы запросы и манипулирование данными по времени были эффективнее. Ниже вы видите разницу между обычной таблицей и гипертаблицей:
Изменение структуры даты и времени
Для разделения гипертаблицы можно использовать столбец даты, но тогда будет ограничен контроль над данными по времени. Один столбец с меткой времени даёт больше гибкости и экономит пространство эффективнее, чем отдельные столбцы с датой и временем.
Структура таблицы должна быть такой, чтобы из столбцов date и start_time можно было получать полезное значение метки времени: end_time не даёт столько информации. Иными словами, надо объединить эти два столбца в один с метками времени.
Изменение типов данных столбцов
Благодаря функции TO_NUMBER() в PostgreSQL это просто.
Преобразование ограничим значениями не больше 99,99, поскольку в столбце тарифов нет значений больше 0,65. А что если надо преобразовать столбец с большими числовыми значениями? Тогда добавляем G для запятых.
Представления PostgreSQL
Представление — это объект PostgreSQL, который позволяет определять запрос и вызывать его по имени представления, как если бы это была таблица БД. Сгенерируем данные и создадим представление:
Важно: данные внутри представлений PostgreSQL должны пересчитываться при каждом запросе. Вот почему надо вставлять данные представления в гипертаблицу, как только они подготовлены.
Cоздание или генерирование необходимых данных
Столбец примечаний ( notes ) в этом наборе пуст. Чтобы проверить это, просто включаем оператор WHERE и указываем, где notes не равны пустой строке.
Столбец примечаний пуст, поэтому заменим его различными наборами дополнительной информации, чтобы использовать эту информацию при моделировании.
Добавим столбец дня недели при помощи EXTRACT() — функции даты/времени PostgreSQL, которая позволяет извлекать из даты и времени различные элементы. У наших колонок в PostgreSQL есть обозначение дня недели DOW (day-of-week): 0 — это воскресенье, а 6 — суббота.
Можно добавить столбец, где указывается, приходится ли день на выходные или будни. Для этого воспользуемся оператором CASE:
Обратите внимание: в Python выходные представлены числами 5 и 6, а в PostgreSQL — числами 0 и 6.
Пока сохраним эту расширенную таблицу в другом представлении, чтобы воспользоваться представлением позже.
Вы спросите: «Зачем создавать столбцы логических значений?». Для фильтрации. В PostgreSQL благодаря логическим столбцам очень легко фильтровать данные. Например, если нужно показать данные только за выходные и праздничные дни, добавим WHERE вместе с указанными столбцами.
Добавление данных в гипертаблицу
Подготовив новые столбцы и организовав таблицу, создадим новую гипертаблицу и вставим очищенные данные.
В случае работы с постоянно поступающими данными можно создать скрипт, который при импорте данных автоматически вносит эти изменения.
Переименование значений
Ещё один ценный метод очистки данных — переименование элементов или повторное отображение категориальных значений.
Его важность подчёркивается популярностью вопроса об анализе данных Python на StackOverflow: «Как во фрейме Pandas поменять одно значение индекса». PostgreSQL и TimescaleDB используют структуры реляционных таблиц, поэтому переименовывать уникальные значения просто.
Внимание: код ниже сделает понедельник равным 7, потому что в функции DOW (day-of-week) Python значение понедельника 0, а воскресенья — 6. Но так и обновляется одно значение в столбце. При этом обновлять значения, скорее всего, не придётся, а эквивалент на Python показан просто для справки.
Аналогичные функции отображения есть в Python.
SQL здесь быстрее и элегантнее. На Python переименование столбцов может стать большой проблемой.
Заполнение недостающих данных
Другая проблема в процессе очистки данных — это их отсутствие. В нашем наборе нет явно отсутствующих точек данных, но наверняка найдутся недостающие данные по часам, возникающие из-за отключения электроэнергии или других обстоятельств. Здесь и пригодятся функции заполнения TimescaleDB.
Недостающие данные часто оказывают большое и негативное влияние на точность или надёжность модели. Иногда проблема решается заполнением недостающих данных данными, которые получены обоснованными оценками. Чтобы получить такие данные, TimescaleDB предоставляет встроенные функции.
Например, при моделировании потребления энергии в отдельные дни недели по некоторым дням из-за отключения электроснабжения или проблем с датчиком данных нет. Данные можно удалить или заполнить недостающие значения обоснованными оценками.
Чтобы добавить эти недостающие значения, используем гиперфункции TimescaleDB; interpolate() — ещё одна гиперфункция TimescaleDB. Она создаёт точки данных, которые следуют линейной аппроксимации с учётом точек данных до и после отсутствующего диапазона.
Продолжить изучение SQL и Python вы сможете на наших курсах:
Другие профессии и курсы
Data Science и Machine Learning