Пример пользы индексов и IMDBOptimizer

В рамках данной статьи я хотел бы привести и разобрать пример пользы от наличия индексов в базе данных OpenCart, которые создает IMDBOptimizer.

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

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

Второе, что стоит знать — мощный сервер стоит дорого и его поддерживать не просто. Плюс после определенных объемов, увеличение мощности сервера не сможет заменить оптимизацию.

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

Пример абстрактного варианта расписан в описании модуля IMDBOptimizer, но если вкратце то для таблицы размером в 1000 записей, поиск может осуществляться от 60 до ….. раз быстрее.

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

Возвращаясь к сути, в базе данных OpenCart отсутствуют индексы для весьма большого количества полей с идентификаторами (а так же некоторые, которые взяты из общих практик — найти их можно в интернете).

Чтобы не быть голословным, я рассмотрю один из примеров - отсутствие необходимых индексов у таблиц «product_option» и «product_option_value» (например, для идентификатора продукта). Для тех, кто не знает, в них хранятся опции продуктов.

Сейчас будет немного страшного кода, но не волнуйтесь я поясню его.

Если открыть модель клиентской части и посмотреть получение опций товара, то можно увидеть следующий код:

---

public function getProductOptions($product_id)
{

$product_option_data = array();
$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order");
foreach ($product_option_query->rows as $product_option) { $product_option_value_data = array();
$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = '" . (int)$product_id . "' AND pov.product_option_id = '" . (int)$product_option['product_option_id'] . "' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order");

foreach ($product_option_value_query->rows as $product_option_value) { …

.................... }

….................... }

return $product_option_data;
}

---

Для получения опций со значениями используются - 1 основной запрос для получения опций и для каждой опции осуществляется еще по 1 запросу для поиска значений.

 

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

---

SELECT * FROM oc_product_option po WHERE po.product_id = (int)$product_id

​ Это для получения опций

SELECT * FROM oc_product_option_value pov WHERE pov.product_id = (int)$product_id AND pov.product_option_id = '" . (int)$product_option['product_option_id']

​ Это для получения уже конкретных значений опций

---

 

Стоит отметить, что индексов по идентификаторам продуктов у таблиц нет. А так же у таблицы «oc_product_option_value» нет индекса и по «product_option_id».

Это означает, что каждый раз, когда получаются опции для конкретного продукта, происходит - 1 полное сканирование таблицы «oc_product_option» в поиске опций. А затем для каждой опции дополнительно происходит по еще 1-му полному сканированию таблицы «oc_product_option_value».

Чтобы не раскрывать чьи-то данные и чтобы каждый мог проверить у себя в phpMyAdmin. Попробуем сделать тестовые упрощенные таблицы, заполним их данными (где-то 2000 строк в основной таблице опций и 10000 строк для вторичной таблицы со значениями, что соответствует примерно магазину, где есть около 1000 товаров), а затем сделаем замеры количества сравнений при помощи встроенных команд mysql.

Время не сравнивается, так как таблицы очень маленькие (не содержат массы полей), созданы в отдельной базе данных (поэтому нет проблем с кэшированием в mysql), и плюс нет той нагрузки, которая есть в реальных магазинах, так как запросы выполнялись в свободном ненагруженном тестовом сервере. В общем идеальные условия «коня в вакууме», которые в интернет-магазинах никогда не бывают.

Вот скрипты создания упрощенных таблиц (только не запускайте их в БД, где находится ваш интернет-магазин, создайте для этого отдельную БД):

---

--

-- Структура таблицы `option`

--

CREATE TABLE IF NOT EXISTS `option` (

`product_id` int(11) NOT NULL,

`option_id` int(11) NOT NULL,

`somedata` int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--

-- Структура таблицы `option_value`

--

CREATE TABLE IF NOT EXISTS `option_value` (

`product_id` int(11) NOT NULL,

`option_id` int(11) NOT NULL,

`somedata` int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Итак, создали упрощенные варианты таблиц.

Теперь заполним их данными.

Пусть у нас есть около 1000 товаров, для которых случайно создаются опции.

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

-- Очистка таблицы

truncate table `option`;

insert into `option` (`product_id`, `option_id`, `somedata`)

values

(1,2,32),

(2,3,32),

(3,4,32),

(4,5,32),

(5,6,32),

(6,7,32),

(7,7,32),

(8,9,32),

(9,10,32),

(10,11,32)

;

insert into `option` (`product_id`, `option_id`, `somedata`)

select coeff * (o3.product_id * 10 * 10 + o2.product_id * 10 + o1.product_id),

coeff * (o3.option_id * 10 * 10 + o2.option_id * 10 + o1.option_id),

coeff * (o3.somedata * 10 * 10 + o2.somedata * 10 + o1.somedata)

from `option` as o1,

`option` as o2,

`option` as o3,

(

select 1 as coeff

union

select 2 as coeff

) coeff

;

---

-- Очистка таблицы

truncate table `option_value`;

insert into `option_value` (`product_id`, `option_id`, `somedata`)

select opt.product_id, opt.option_id, opt.somedata * coeff.coeff

from `option` as opt,

(

select 1 as coeff

union

select 2 as coeff

union

select 3 as coeff

union

select 4 as coeff

union

select 5 as coeff

) as coeff

;

Теперь, в основной таблице содержится 2010 записей, а в дополнительной содержится 10050 записей. Попробуем найти записи по определенным идентификаторам вручную.

Для опций запустим следующий запрос:

explain

select *

from `option`

where product_id = 2218

;

Вернет следующую информацию

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

Теперь, попробуем поискать значения опций.

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

explain

select *

from `option_value`

where product_id = 2218

;

Как видите, для того, чтобы найти все записи для одного продукта mysql снова пришлось проверить все записи таблицы.

А теперь, построим хотя бы просто индексы для колонок product_id и выполним те же самые запросы. И посмотрим разницу.

Создаем индексы:

ALTER TABLE `option` ADD INDEX ( `product_id` );

ALTER TABLE `option_value` ADD INDEX ( `product_id` );

Теперь, снова запускаем запросы.

Запрос для получения опций.

explain

select *

from `option`

where product_id = 2218

;

Как видите с индексом было проверено всего 2 записи. Это, конечно, безумно удачная ситуация (особенно учитывая, что тестовые данные забивались просто), однако, можете проверить сами и убедится в этом. Разница в скорости составила 1000 раз.

Теперь, попробуем посмотреть запрос для значений.

explain

select *

from `option_value`

where product_id = 2218

;

 

Для поиска значений опций потребовалось 15 сравнений против 10050 (в 670 раз быстрее).

И таких отсутствий индексов в БД от 201 до 211 индексов (в зависимости от версии). Конечно, не все из них важны именно для вашего интернет-магазина, однако, факт остается фактом.

Как видите, модуль IMDBOptimizer даже с учетом базовой оптимизации может помочь вашему интернет-магазину неплохо так снизить нагрузку в базу данных OpenCart.