svitlo1
Постоялец
- Регистрация
- 17 Фев 2009
- Сообщения
- 559
- Реакции
- 118
- Автор темы
- #1
Оптимизация базы данных интернет-магазина — это весьма непростой вопрос, порой требующий отдельных исследований.
И самое неприятное в этом процессе заключается в том, что сделать хоть какую-то оптимизацию может только тот, кто знает sql-запросы и разбирается в базах данных (БД).
Вот как раз для того, чтобы клиентам не приходилось заниматься тем, что им не обязательно знать, и создан IMDBOptimizer (OC 3).
Настоятельно рекомендую ознакомиться с обзором Для просмотра ссылки Войди
Демо модуля
Для просмотра ссылки Войди
Оптимизация в 1 щелчок
Модуль позволяет в 1 щелчок произвести оптимизацию: создать индексы, оптимизировать таблицы (sql запрос optimize table), а также включить SQL-кэш, если подобное требуется.
Особенность. Если выбрать в поле «Включить кэш?» значение «Отключено», то модуль не отключит кэш, если тот был включён. Это специально сделано для того, чтобы случайно не отключить кэш, когда вы периодически проверяете нужно ли создать индексы и оптимизировать таблицы.
Небольшой нюанс для редких случаев. Учитывайте, что, например, если индексы создаются слишком долго (более детально в «Что делать если индексы создаются слишком долго?»), то придётся создавать их через вкладку, а не через оптимизацию в 1 щелчок. Это чисто техническое ограничение.
Кэширование SQL-запросов
OpenCart, как и любая CMS, осуществляет немалое количество sql-запросов к БД, часть из которых являются однотипными (то есть для разных пользователей будет один и тот же результат).
И если товаров много, то sql-запросы легко могут стать основной причиной тормозов интернет-магазина (если у вас 5000+ товаров, то об этом вы, вероятно, хорошо знаете).
Однако, этого можно избежать за счет кэширования sql-запросов модулем IMDBOptimizer.
Возможности:
1. Гибридная система кэширования SQL-запросов (БД + файлы), позволяющая увеличить скорость генерации HTML-страницы (тестировалось на стандартном OpenCart с 5500 товаров — прирост производительности от 30% до 70-80%) и частично сбалансировать нагрузку между диском и БД.
2. Поддерживается фильтр «по словам» для исключения SQL-запросов из процесса кэширования (регистронезависимо).
3. Поддерживается фильтр «по URL» для исключения отдельных страниц из процесса кэширования SQL-запросов (регистронезависимо).
4. Так как кэшируются только SQL-запросы, то такой модуль можно успешно применять совместно с другими модулями кэширования (например, v2pagecache). Однако, совместимость лучше проверять на тестовом сервере.
5. Установили модуль? Ничего не нужно настраивать для кэширования. SQL-запросы автоматически начинают кэшироваться (с учетом фильтров), без необходимости что-то еще настраивать.
6. Еще одной отличительной особенностью кэширования именно SQL-запросов является то, что если один и тот же запрос используется при генерации разных веб-страниц или же просто выполняется повторно, то используется всего один кэш. Простой пример, открыли один и тот же товар из разных категорий — опции будут закэшированы всего 1 раз.
7. Можно применять как с созданием индексов, так и без.
8. При установке, модуль сразу создает типовую настройку, нужно лишь включить кэш.
9. Легко включается и легко отключается.
Ограничения и нюансы:
1. Так как это модуль кэширования уровня БД, то необходимо учитывать, что такие возможности, как отображение реального остатка товара или текущей цены в карточке, не поддерживаются (данные же кэшированы).
2. Кэшируются только SQL-запросы, начинающиеся с select.
3. Заменяется ядровой файл registry.php
4. Кэширование применяется только к клиентской части, в админской части все запросы выполняются как обычно.
5. Учитывайте, что кэширование это дополнительная нагрузка. Например, при первом открытии страницы товара, она может дольше загружаться (создается кэш).
Кэширование SQL-запросов — включение и очистка
Как включить кэширование:
1. Перейдите во вкладку «Кэш SQL-запросов».
2. Укажите, что кэширование «Включено» и сохраните настройки.
3. Кэш включен
Для отключения нужно сделать все то же самое, только в шаге 2 установить «Отключено»
Как очистить кэш:
1. Перейдите во вкладку «Кэш SQL-запросов»
2. Нажмите кнопку «Удалить кэш»
3. Дождитесь соответствующего сообщения
Кэширование SQL-запросов — фильтры
Фильтр запросов «по словам»:
В данном фильтре построчно указываются фразы, которых не должно быть в запросе (приводятся к нижнему регистру). По умолчанию, указан список таблиц/префиксов для стандартной конфигурации.
При фильтрации, символ «#» заменятся на префикс базы данных, что позволяет создавать собственные конфигурации, которые легко переносить в другие интернет-магазины.
Важно! Пробелы так же учитываются - сделано для того, чтобы можно было исключать отдельные таблицы. Например, строка «#order» без пробела после order исключает все таблицы, связанные с заказом. А с пробелом после исключает только oc_order (рекомендуется так же дублировать правило и указывать апостроф после order, так как SQL-запросы формируются по-разному).
Пустые строки игнорируются. Так же игнорируются пробелы вначале и в конце SQL-запроса.
Фильтр запросов «по URL»:
В данном фильтре построчно указываются комбинации «[тип поиска]: [часть URL адреса]» (или же просто «[часть URL адреса]») для отключения кэширования при генерации конкретных страниц (таких как корзина или же оформление заказа). Все комбинации приводятся к нижнему регистру (регистронезависимо).
1. [часть URL адреса] – это какая-то часть URL адреса, например, «#/cart/» или «=checkout/».
2. [тип поиска] – имеет три значения: «l» (сравнивать часть адреса сначала URL; учитываются параметры запроса), «i» (искать часть адреса внутри URL; учитываются параметры запроса), «r» (искать часть адреса справа; параметры запроса не учитываются).
При фильтрации, символ «#» заменятся на домен сайта, что позволяет создавать собственные конфигурации, которые легко переносить в другие интернет-магазины.
По умолчанию, указан список фильтров для стандартных настроек, а так же корзины с модулем Simple.
Так же учитывайте, что префиксы «Для просмотра ссылки Войди
Примеры:
r: #/cart
Подходит «site.ru/cart», «site.ru/cart?asd=1». Но, не подходит «site,ru/cartini»
i: =checkout/
Подходит «site.ru/index.php?route=checkout/checkout&1=2». Но, не подходит «site.ru/index.php?route=check/some».
Кэширование SQL-запросов — дополнительные настройки
Максимальный размер вставки в БД. Данный параметр указывает свыше какого размера данных, информацию необходимо кэшировать в файл. Максимальное значение 65000. Рекомендуется использовать в диапазоне 20000 — 30000.
Время жизни кэша (сек). Здесь указывается время в секундах, в течение которое будет актуален созданный кэш.
Минимальное время для кэша (мс). Здесь задается время в мс, определяющее минимальное время выполнения sql-запроса, выше которого запрос кэшируется. Например, если стоит 20 мс, а запрос выполнился за 10 мс, то он не кэшируется. Если же запрос выполнился за 21 мс, то он кэшируется.
Таблицы sql-кэширования: тип MyISAM и InnoDB
Существует возможность выбирать какой тип таблицы будет использоваться для хранения sql-кэша в БД: тип MyISAM или InnoDB.
Учтите, что чисто физически это две разные таблицы в БД и что кнопка удаления кэша очищает обе таблицы.
Если вы не знаете что это и зачем нужно, то используйте тип MyISAM (по умолчанию в модуле используется).
Одновременно может быть задействована только одна из двух таблиц. Учтите, что переключение таблиц необходимо выполнять при выключенном и очищенном sql-кэше, так как потенциально могут возникать ошибки. Какая бы таблица не использовалась, файловый кэш остается одним и тем же.
Для тех, кому нужна только базовая оптимизация
Если вы не планируете сами вносить дополнительные индексы (или же оставите это тем, кто в этом разбирается), то, как уже говорил, модуль содержит настройки по умолчанию для базовой оптимизации БД.
Важно! Учтите, что операция весьма длительная и что требуется минимальная нагрузка в базе данных (то есть, желательно, чтобы пользователей сайта вообще не было или их было немного). В крайнем случае, вы можете последовательно и отдельно создавать индексы для каждой из таблиц, а не все скопом.
1. Сделайте бэкап всей базы данных (а лучше сайта в целом)! Это важно!
2. Откройте модуль
3. Выберите все таблицы (можно щелкнуть по ссылке «Выделить всё»)
4. Чуть ниже, щелкните по кнопке «Генерировать!»
5. Откиньтесь на спинку кресла и наблюдайте как модуль создает индексы
Несколько щелчков мыши и у вас проведена базовая оптимизация базы данных!
Профилактика — создание индексов
Для профилактики, необходимо запускать генерацию после установки любых других модулей, которые создают свои таблицы (не всегда в них имеются все необходимые индексы).
Индексы создаются самые простые без ограничений, так что в таблицах модулей не будет нарушена какая-либо логика (только если модуль не будет в последствии добавлять индексы без проверок, но обычно это редкость).
Профилактика — оптимизация таблиц
Базы данных это сложные механизмы, где часть вещей остается на ответственность пользователей. Одной из них является снижение производительности, в связи с частым изменением и модификацией набора записей таблиц.
Проще говоря, товаров, их атрибутов и прочего. Что особенно актуально для магазинов с частым импортом прайсов или для магазинов, использующих парсеры.
Поэтому для повышения производительности БД рекомендуется периодически выполнять оптимизацию таблиц. В среднем, для OpenCart это, примерно, раз в неделю.
Чтобы провести оптимизацию, достаточно лишь открыть вкладку «Сервис», выбрать таблицы и нажать кнопку «Оптимизировать!».
Учтите, что данная операция может занимать время и блокировать доступ к данным во время своего выполнения. Поэтому лучше всего выполнять ее, когда пользователей либо нет на сайте, либо их минимальное количество.
Починка таблиц
Как же неприятно открыть страницу товара и увидеть вместо карточки сообщение вида «PHP … Table is currupted … try to repair it...».
Редко, но такое бывает, что таблицы в БД MySql повреждаются. К счастью, чаще всего для их починки достаточно лишь запустить специальный запрос. Однако, чтобы это сделать необходимо зайти в хостинг, открыть панель phpMyAdmin, найти в интернете как составлять запрос и запустить его (а до этого всего, еще понять что делать с этой ошибкой). Для обычных пользователей или тех, кто только начинает, это весьма непростая задача.
Модуль же позволяет существенно упростить этот процесс. Нужно лишь указать поврежденную таблицу и нажать кнопку «Починить!» во вкладке «Сервис».
Учтите, что ошибки при подсчете не всегда означают ошибки восстановления. Так, например, таблица oc_cart попросту не поддерживает данный вид команды.
Блок с именами
Для настройки автоматического поиска и создания одиночных индексов, рядом с блоком таблиц есть три поля, где можно указывать конкретные имена полей, их начало или окончание (через запятую).
Стоит понимать, что они действуют по правилу ИЛИ. То есть если поле таблицы указано среди конкретных полей ИЛИ начинается с одного из указанных префиксов ИЛИ заканчивается на одном из указанных окончаний, то для поля в текущей таблице будет создан индекс.
Например:
Конкретные поля:
product_id, language_id
Префиксы:
stat, col
Окончания:
_id
При таких настройках, индекс для поля «product_option_value_id» будет создан (если его нет в таблице), так как поле заканчивается на «_id»
Карта индексов
В данном блоке можно составлять конкретные индексы, которые необходимо создать. Важно, что таблица каждого правила должна быть выбрана в списке таблиц. Иначе индекс не будет проверен или создан.
Правила составляются по следующему принципу:
Имя таблицы - Поле (, Поле)
Где имя таблицы может быть как указано с общим префиксом БД, так и вместо префикса можно использовать символ #, который будет автоматически заменен на префикс. Чтобы создать индексы из нескольких полей, их необходимо перечислить через запятую.
Например:
#product_description - language_id, product_id
В данном случае будет создан индекс (language_id, product_id) для таблицы oc_product_description (если такового индекса не было в таблице).
Удаление индексов
В закладке «Индексы» есть возможность удалять из таблиц индексы, созданные модулем. А именно те, которые имеют префикс «imdbo_».
Сделано это для того, чтобы исходные индексы или созданные вручную индексы не были случайным образом удалены из базы данных.
Подход к генерации имен индексов
Чтобы упростить использование созданных индексов для других авторов и создателей сайтов, в модуле был введен специальный алгоритм генерации имен создаваемых индексов.
Так что если вы будете планировать использовать данные индексы при построении своих sql-запросов (например, с возможностью повторного внедрения в другие интернет-магазины), то сделать это будет весьма просто.
Сам алгоритм подбора
Шаг 1. Составляется имя «imdbo_» + «[поле 1]» + «_[поле 2]» + … + «_[поле N]». Если у таблицы индекса с таким именем нет и его длина не превышает 64 символа (требование БД), то индексу присваивается это название. В противном случае, алгоритм переходит к следующему шагу.
Например, для индекса (product_id) будет использовано имя «imdbo_product_id», а для индекса (product_id, order_id) будет использовано имя «imdbo_product_id_order_id». И так далее.
Шаг 2. Составляется имя «imdbo_» + «[поле 1 — первые буквы слов в столбце]» + «_[поле 2 — первые буквы слов в столбце]» + ... Если индекса с таким именем нет и его длина не превышает 64 символа (требование БД), то индексу присваивается это название. В противном случае, переход к следующему шагу.
Например, для индекса (order_id, customer_id, store_id, payment_zone_id, currency_id, marketing_id), чья длина больше 64 символов в шаге 1, будет использовано имя «imdbo_oi_ci_si_pzi_ci_mi».
Шаг 3. Практически нереальная ситуация, но сделана для унификации. Составленное имя из шага 2 обрезается до 61 символа (если требуется) и к нему прибавляется приставка «_[номер]», где номер от 1 до 99.
Например, «imdbo_oi_ci_si_pzi_ci_mi_1», …, «imdbo_oi_ci_si_pzi_ci_mi_25», «imdbo_oi_ci_si_pzi_ci_mi_99».
Если же и это недостижимо, то выполняется следующий шаг.
Шаг 4. Аналогично шагу 3, практически нереальная ситуация, но сделана для 100% унификации. Имя стоится как «imdbo_» + «[время UTC]» + «_[номер]». Например, «imdbo_1508711578_3».
А теперь, по-простому. При базовой оптимизации будут созданы индексы только шага 1. Шаг 2 это уже настройки из карты индексов (если вручную были указаны сложные составные индексы). Шаг 3 встретится очень редко, но если и встретится такое, то имена с постфиксами будут аналогичными от интернет-магазина к интернет-магазину. Шаг 4 сделан просто для безопасности, но в реальности невозможен (если, конечно, кто-то специально вручную не создал более 100 одинаковых индексов для таблицы).
Ограничения типов полей создаваемых индексов
Модуль проверяет типы полей перед созданием индексов. И если какое-то поле не подходит по типу, то такой индекс не будет создан.
Отключить проверку можно в файле /system/IMDBOptimizer/IMDBOConfig.php, указав в параметре IMDBO_COLUMNS_CHECK_DATA_TYPE значение 0. Для включения значение должно быть 1.
Если вы отключаете проверку типов полей, то в таком случае вы действуете на свой страх и риск.
Список поддерживаемых типов:
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, CHAR, VARCHAR, DATE, DATETIME, TIMESTAMP, TIME, YEAR, BIT, BOOLEAN, DECIMAL, FLOAT, DOUBLE, REAL
Учтите, что для типов VARCHAR и CHAR так же проверяется максимальный размер колонки в 255 символов (в плане ограничения для индексов).
Доступные действия:
1. EnableSQLCache — включает SQL кэширование.
2. ClearSQLCache — очищает SQL кэш.
3. DisableSQLCache — отключает SQL кэширование.
4. EnableSQLLog — включает лог медленных запросов.
5. ClearSQLLog — очищает лог медленных запросов.
6. DisableSQLLog — отключает лог медленных запросов.
Примечание: Название действий регистронезависимы.
Как настроить генерацию по Cron:
1. Откройте модуль.
2. Перейдите во вкладку «Настройки».
3. Укажите секретный ключ в соответствующем поле (желательно цифро-буквенную комбинацию минимум из 20-30 символов). Тут важно понимать, что ключ не может быть пустым. В таком случае действия попросту не будут выполняться.
4. Сохраните настройки. Если вы использовали сохранение с перезагрукой, то снова откройте вкладку «Настройки».
5. Укажите список необходимых действий через запятую. Так же можете воспользоваться кнопками, расположенными прямо под полем со списком действий. Учитывайте, что действия выполняются в порядке их следования.
6. Сохраните ссылку и укажите в cron вызов через wget.
7. Повторяйте пункт 5-6 для всех нужных вам наборов действий.
Более подробно о том, как корректно вызывать cron написано у каждого провайдера (иногда бывают отличительные особенности), но обычно это выглядит так:
wget -q -O- [ссылка] > /dev/null 2>&1
где [ссылка] – это как раз та ссылка, которая генерируется во вкладке «Настройки».
Помните, что в один момент времени может быть запущен всего один список действий (иначе это может приводить к проблемам). Поэтому, настраиваете задания в cron с запасом по времени.
Секретный ключ можно менять сколько угодно раз, но важно помнить, что хранится только актуальный ключ. Поэтому если вы изменили секретный ключ, то ранее сохраненные ссылки (или указанные в cron) не будут приводить к запуску действий.
Помните, что скрипт выполняется при помощи обычного запроса wget (т. е. аналогично тому, как открыть страницу в браузере). Так что, если при выполнении действий скрипту не хватает времени, то необходимо увеличить время выполнения клиентского запроса в php. Например, если у вас SQL кэш занимает десятки Гигабайт, то очистка кэша может выполняться не очень быстро.
Особенности и ограничения
- Важно учитывать, что генерация индексов происходит для каждой таблицы отдельно. То есть для каждой таблицы отдельно посылается AJAX-запрос. Это связано с тем, что для больших таблиц создание одного индекса может быть весьма длительной операцией. Поэтому, если вы запустили генерацию, то просто дождитесь, когда рядом с кнопками появится сообщение, что генерация завершена.
- Создаются только обычные индексы.
- Удаляются только индексы, созданные модулем. А именно те, которые имеют префикс «imdbo_». Сделано для того, чтобы не сломать исходные настройки базы данных и вручную созданные индексы.
- Если не существует указанных таблиц или полей, то указанные данные будут просто игнорироваться.
- В БД проверяются только те таблицы, которые начинаются с префикса таблиц опенкарта (Сделано для тех, кто использует в одной БД несколько сайтов)
- Пользователь должен иметь полные права для получения доступа к БД (или достаточные для получения метаданных и создания индексов)
- Оптимизация производится для БД MySQL
- Имена создаваемых индексов имеют технические названия (техническое ограничение автоматизации)
- Если существует идентичный по полям индекс, то ничего не будет происходить.
- Заменяется ядровой файл registry.php
- Ссылку для cron (та что в настройках динамически создается) необходимо дополнительно проверять (могут быть некорректности в части до «index.php»; например, если вы скрыли админку в другой каталог; к сожалению, ядро опенкарта в этом смысле не очень удобное).
- Требуется, чтобы в настройках сайта было выставлено mbstring.func_overload 0. Если вы не знаете как это сделать , то уточните в вашем хостинге. В большинстве случаев по умолчанию это значение 0 (вам, как клиенту, ничего не нужно делать), но в некоторых хостингах выставляют значение 2. Это актуально в тех случаях, если у вас возникли проблемы с ключами и активацией модуля.
- Требуется boostrap и jquery
Нюансы:
1. При переходе с версии 1.3.0 (и более ранних) в 1.4.1 настоятельно рекомендуется почистить SQL-кэш модуля ДО установки версии 1.4.1.
2. Как и перед установкой любых модулей, настоятельно советуется делать полный бэкап сайта.
3. Если вы переустанавливаете модуль, то настоятельно рекомендуется удалить из фильтра sql-кэша строку «#review», так как один из тяжелых запросов категорий использует эту таблицу. Если вы установили модуль в первый раз, то этого фильтра нет в настройках по умолчанию.
Если у вас много товаров (где-то от 50-100к+) и так же большое количество стандартных скидок и акций опенкарта, то так же может быть полезным удалить фильтры «#length_class» и «#weight_class», но обязательно протестируйте этот момент, так как эффект может быть уникальным для каждого интернет-магазина (зависит от скорости выполнения sql-запросов для получения дополнительной информации о каждом товаре в категориях и отдельных карточках).
Для просмотра скрытого содержимого вы должны войти или зарегистрироваться.