Как уменьшить размер базы данных

     Примеры приведены для версии юми 2.8.6.1 от 2013-го года. Работающая чистилка для свежих версий выложена внизу статьи

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

    Наиболее вероятная причина такого роста.

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

    Я использую для этого Sypex. На сайте у них есть бесплатная версия, которой вполне достаточно для резервирования и восстановления базы данных umi.cms.

    Шаг 1. Чистим логи.

    Первым шагом я смотрю размер, занимаемый в базе различными логами и статистикой. Найти таблицы для статистики очень легко, они все начинаются в названии на cms_stat_*.

    Если они непомерно раздуты, очищаю их.

    Для очистки использую SQLyog. Или можно phpmyadmin взять, он на всех хостингах фактически есть.

    Шаг 2. Для интернет-магазинов чистим заказы.

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

    Тем паче, чаще всего на странице товара размещена ссылка или кнопка для покупки вида: /emarket/basket/put/element/{page/@id}/. По этой ссылке ходят роботы поисковиков, и я подоздреваю, что при этом в базе добавляется заказик с этим товаром.

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

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

    В моей практике пападались пара не очень крупных интернет-магазинов, база которых достигла размера более 500 мб буквально за пару лет работы. Причём именно за счёт неиспользованных заказов в базе. После очистки размер базы сводится к обычным 50-90 мБ.

    Обычно в процессе очистки удаляются 10-ки тысяч записей, что в любом случает положительно сказывается на быстродействии всего сайта в целом. Так как уменьшаются не только таблицы с собственно данными, но и сопутсвующие индексные таблицы. Результат виден на примере по размеру таблицы cms3_object_content до и после очистки (данную базу стараюсь чистить раз в 3-4 месяцев):

     

     

    Пример кода первого этапа очистки в базе данных о ненужных покупателей в брошенных заказах:

    // Сначала ищем и сохраняем в массиве все id покупателей, которые нельзя удалять.
    $objectTypes = umiObjectTypesCollection::getInstance();
    $ObjectTypeId = $objectTypes->getBaseType("emarket", "order");
    
    $sel_order = new umiSelection;
    $sel_order->addObjectType($ObjectTypeId);
    $sel_order->addPropertyFilterIsNotNull($num_order);
    $result_order = umiSelectionsParser::runSelection($sel_order); //Массив id объектов
    $arr_need_users = array();
    			
    foreach($result_order as $Id) {
    	  $object = $objects->getObject($Id);
    	  $need_user_id = $object->getValue("customer_id");
    	  $arr_need_users[] = $need_user_id;
    }
    
    // затем тупо делаем выборку из базы всех покупателей и удаляем только тех, 
    // которых нет в ранее сохранённом массиве нужных покупателей.
    
    $cnt = 1000;
    
    $sel_guest = new selector('objects');
    $sel_guest->types('object-type')->name('emarket', 'customer');        
    $sel_guest->limit(0, $cnt); 
    sel_guest->order('id')->desc();
    $result_guest = $sel_guest->result();
    
    $deleted=0;
    foreach($result_guest as $item){
        $id_customer = $item->id;
        if(!in_array($id_customer, $arr_need_users)) {
            $objects->delObject($id_customer);
            //$sql_del = "DELETE FROM `cms3_object_content` WHERE `obj_id` = {$id_customer}";
            //l_mysql_query($sql_del);    
            $deleted++;
        }
    }
    
    

    Затем точно также удаляем ненужные заказы:

    // Нужные заказы легко найти по наличию у них заполненного поля "Номер заказа"
    $sel_order_need = new selector('objects');
    $sel_order_need->types('object-type')->name('emarket', 'order');   
    $sel_order_need->where('number')->isNull(false);
    $result_order_need = $sel_order_need->result();
    
    // Находим все заказы и удаляем только те, которых нет в массиве $result_order_need
    $cnt = 1000;
    $sel_order = new selector('objects');
    $sel_order->types('object-type')->name('emarket', 'order');   
    $sel_order->limit(0, $cnt); 
    $sel_order->order('id')->desc();
    $result_order = $sel_order->result();
    
    $deleted=0;
    foreach($result_order as $order){
        $id_order = $order->id;
        if(!in_array($id_order, $arr_non_orders)) {
            $objects->delObject($id_order);
            //$sql_del = "DELETE FROM `cms3_object_content` WHERE `obj_id` = {$id_customer}";
            //l_mysql_query($sql_del);    
            $deleted++;
        }
    }
    

    Ну и последним этапам находим и удаляем товарные позиции, которые хранились в удалённых ранее заказах:

    // Вначале выбираем все нужные заказы, берём товары в них и сохраняем в единый  массив.
    $sel_order_need = new selector('objects');
    $sel_order_need->types('object-type')->name('emarket', 'order');   
    $sel_order_need->where('number')->isNull(false);
    $result_order_need = $sel_order_need->result();
    
    $arr_need_order_items = array();
    foreach($result_order_need as $order){
    	$order = umiObjectsCollection::getInstance()->getObject($order->id);
    	if($order instanceof umiObject) {
    		$orderItems = $order->getValue('order_items');
    	}
    	foreach($orderItems as $OrderItemId) {
    		$arr_need_order_items[] = $OrderItemId;
    	}
    }
    
    // Затем находим все товарные позиции и удаляем только те, которых нет в массиве $arr_need_order_items
    $cnt = 1000;
    	   
    $sel_order_item = new selector('objects');
    $sel_order_item->types('object-type')->name('emarket', 'order_item');   
    $sel_order_item->limit(0, $cnt); 
    $sel_order_item->order('id')->desc();
    $result_order_items = $sel_order_item->result();
    
    $deleted=0;
    foreach($result_order_items as $order_item){
    	$id_order_item = $order_item->id;
    	if(!in_array($id_order_item, $arr_need_order_items)) {
    		$objects->delObject($id_order_item);
    		//$sql_del = "DELETE FROM `cms3_object_content` WHERE `obj_id` = {$id_order_item}";
    		//l_mysql_query($sql_del);    
    		$deleted++;
        }
    }
    

    Всё, база очищена

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

    $sql_optimize = "OPTIMIZE TABLE `cms3_object_content`, `cms3_objects`";
    l_mysql_query($sql_optimize);  
    

    Шаг 3. Скачать чистилку.

    Рабочий вариант можно скачать на github.com

    Обновления:

    Выложил чистилку для свежих юми, начиная с версии 14 от 26.06.2016.

    После 24.06.2016 пошли версии с доработанной системой учёта просроченных объектов, можно использовать как текущую чистилку так и предыдущие варианты. Этот быстрее работает.


    Комментарии: