Особенность фильтрации для Bitrix ORM

Как то раз заглянул в slow-query.log на проекте и обнаружил там повторяющиеся, двадцатисекундные запросы. Как оказалось причина их оказалась в том, что разработчик не знал/подумал глянуть как запрос строится ORM-кой битрикса. Локально, пока он писал и тестировал все работало замечательно. Но вот прошло около 6 гигабайт данных в таблице - и на первый взгляд простой запрос начал “зависать” на ~10 секунд 🙁.

Предыстория

Понадобилось найти заказ по значению свойства. Был написан следующий код:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
\Bitrix\Sale\Order::getList([
    'select' => ['ID', 'PROPERTY.VALUE'],
    'filter' => [
        'PROPERTY.CODE' => 'SOME_ORDER_PROPERTY_CODE',
        'PROPERTY.VALUE' => 'my value',
    ],
    'runtime' => [
        new \Bitrix\Main\Entity\ReferenceField(
            'PROPERTY',
            '\Bitrix\sale\Internals\OrderPropsValueTable',
            ["=this.ID" => "ref.ORDER_ID"],
            ["join_type" => "left"]
        ),
    ]
]);

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

1
2
3
4
5
6
7
+--------------------------+-----------+
| Table                    | Size (MB) |
+--------------------------+-----------+
| b_sale_order_props_value |      1339 |
| b_sale_order             |       102 |
+--------------------------+-----------+
2 rows in set (0.01 sec)

Смотрим SQL

Но вот результат выполнения запроса около 15 (пятнадцати!) секунд. Тут явно что-то не то происходит. Кажется стоило бы глянуть “под капот” ORM и оценить запрос. Печатаем запрос:

1
2
3
4
5
6
7
8
SELECT `sale_internals_order`.`ID`             AS `ID`,
       `sale_internals_order_property`.`VALUE` AS `SALE_INTERNALS_ORDER_PROPERTY_VALUE`,
       `sale_internals_order_property`.`ID`    AS `UALIAS_0`
FROM `b_sale_order` `sale_internals_order`
         LEFT JOIN `b_sale_order_props_value` `sale_internals_order_property`
            ON `sale_internals_order`.`ID` = `sale_internals_order_property`.`ORDER_ID`
WHERE UPPER(`sale_internals_order_property`.`CODE`) like upper('SOME_ORDER_PROPERTY_CODE')
  AND UPPER(`sale_internals_order_property`.`VALUE`) like upper('my value')

А вот и виновник: UPPER. Мы выкидываем возможность глянуть в индекс, что подтверждает EXPLAIN:

1
2
3
4
5
6
7
+----+-------------+-------------------------------+------------+-------+----------------------+----------------------+---------+---------------------------------+--------+----------+-------------+
| id | select_type | table                         | partitions | type  | possible_keys        | key                  | key_len | ref                             | rows   | filtered | Extra       |
+----+-------------+-------------------------------+------------+-------+----------------------+----------------------+---------+---------------------------------+--------+----------+-------------+
|  1 | SIMPLE      | sale_internals_order          | NULL       | index | PRIMARY              | IXS_ORDER_UPDATED_1C | 3       | NULL                            | 130584 |   100.00 | Using index |
|  1 | SIMPLE      | sale_internals_order_property | NULL       | ref   | IX_SOPV_ORD_PROP_UNI | IX_SOPV_ORD_PROP_UNI | 4       | lazurit.sale_internals_order.ID |     35 |   100.00 | Using where |
+----+-------------+-------------------------------+------------+-------+----------------------+----------------------+---------+---------------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

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

Отступление “Зачем”

Кажется есть понимание ЗАЧЕМ так сделано: так как CMS - универсальная штука, ставящаяся на разного рода хостинги, то нет уверенности, что на очередном хостинге collations могут оказаться настроено с учетом регистра и этого не поменяют, или забудут, или оставят “так”, или еще какая-то причина, неподвластная разработчикам CMS. Но тогда уже разработчик должен “следить” за собой, и применять правильное сравнение, если он уверен в значениях для поиска. Встроенный модуль проверки сайта (проверки настроек хостинга), указываем, что нужно использовать регистронезависимое сравнение:

1
2
3
4
5
6
7
if (defined('BX_UTF') && BX_UTF === true)
{
    if ($character_set_connection != 'utf8')
        $strError = GetMessage("SC_CONNECTION_CHARSET_WRONG", array('#VAL#' => 'utf8', '#VAL1#' => $character_set_connection));
    elseif ($collation_connection != 'utf8_unicode_ci')
        $strError = GetMessage("SC_CONNECTION_COLLATION_WRONG_UTF", array('#VAL#' => $collation_connection));
}

Исправляемся

Попробуем приме выше переписать на вариант побыстрее (добавить строгое сравнение в фильтр):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
\Bitrix\Sale\Order::getList([
    'select' => ['ID', 'PROPERTY.VALUE'],
    'filter' => [
        '=PROPERTY.CODE' => 'SOME_ORDER_PROPERTY_CODE',
        '=PROPERTY.VALUE' => 'my value',
    ],
    'runtime' => [
        new \Bitrix\Main\Entity\ReferenceField(
            'PROPERTY',
            '\Bitrix\sale\Internals\OrderPropsValueTable',
            ["=this.ID" => "ref.ORDER_ID"],
            ["join_type" => "left"]
        ),
    ]
]);

Для проверки своей теории глянем получившийся SQL:

1
2
3
4
5
6
7
8
SELECT `sale_internals_order`.`ID`             AS `ID`,
       `sale_internals_order_property`.`VALUE` AS `SALE_INTERNALS_ORDER_PROPERTY_VALUE`,
       `sale_internals_order_property`.`ID`    AS `UALIAS_0`
FROM `b_sale_order` `sale_internals_order`
         LEFT JOIN `b_sale_order_props_value` `sale_internals_order_property`
            ON `sale_internals_order`.`ID` = `sale_internals_order_property`.`ORDER_ID`
WHERE `sale_internals_order_property`.`CODE` = 'SOME_ORDER_PROPERTY_CODE'
  AND `sale_internals_order_property`.`VALUE` = 'my value';

Снова проверим EXPLAIN:

1
2
3
4
5
6
7
+----+-------------+-------------------------------+------------+--------+----------------------------------------------------+-------------------------------+---------+------------------------------------------------+------+----------+-------------+
| id | select_type | table                         | partitions | type   | possible_keys                                      | key                           | key_len | ref                                            | rows | filtered | Extra       |
+----+-------------+-------------------------------+------------+--------+----------------------------------------------------+-------------------------------+---------+------------------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | sale_internals_order_property | NULL       | ref    | ix_perf_b_sale_order_props__1,IX_SOPV_ORD_PROP_UNI | ix_perf_b_sale_order_props__1 | 153     | const                                          |    1 |    10.00 | Using where |
|  1 | SIMPLE      | sale_internals_order          | NULL       | eq_ref | PRIMARY                                            | PRIMARY                       | 4       | lazurit.sale_internals_order_property.ORDER_ID |    1 |   100.00 | Using index |
+----+-------------+-------------------------------+------------+--------+----------------------------------------------------+-------------------------------+---------+------------------------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

А вот и что нам нужно: константный выбор из таблицы свойств, и лишь для выбранных данных присоединение таблицы с заказами. Время выполнения упало, барабанная дробь 🥁, до ~0.06 секунд.

Дополнение

Такая же история работает с фильтрацией вхождения в массив вариантов:

1
2
3
$filter = [
    '=PROPERTY.CODE' => ['first_code', 'second_code', 'third_code'],
];

Такое условие раскладывается на более грустный запрос:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT
    `sale_internals_order`.`ID` AS `ID`,
    `sale_internals_order_property`.`VALUE` AS `SALE_INTERNALS_ORDER_PROPERTY_VALUE`,
    `sale_internals_order_property`.`ID` AS `UALIAS_0`
FROM
    `b_sale_order` `sale_internals_order`
        LEFT JOIN `b_sale_order_props_value` `sale_internals_order_property` ON
            `sale_internals_order`.`ID` = `sale_internals_order_property`.`ORDER_ID`
WHERE
    (UPPER(`sale_internals_order_property`.`CODE`) like upper('first_code')
        OR UPPER(`sale_internals_order_property`.`CODE`) like upper('second_code')
        OR UPPER(`sale_internals_order_property`.`CODE`) like upper('third_code'))
  AND UPPER(`sale_internals_order_property`.`VALUE`) like upper('my value')

Хотя при использовании строгого сравнения:

1
2
3
$filter = [
    '=PROPERTY.CODE' => ['first_code', 'second_code', 'third_code'],
];

Мы получим запрос через IN (что вроде бы быстрее чем множественный OR):

1
2
3
4
5
6
7
8
SELECT 
    `sale_internals_order`.`ID` AS `ID`,
    `sale_internals_order_property`.`VALUE` AS `SALE_INTERNALS_ORDER_PROPERTY_VALUE`,
    `sale_internals_order_property`.`ID` AS `UALIAS_0`
FROM `b_sale_order` `sale_internals_order` 
LEFT JOIN `b_sale_order_props_value` `sale_internals_order_property` ON `sale_internals_order`.`ID` = `sale_internals_order_property`.`ORDER_ID`
WHERE `sale_internals_order_property`.`CODE` in ('first_code', 'second_code', 'third_code')
AND `sale_internals_order_property`.`VALUE` = 'my value'

Предыстория#

Смотрим SQL#

Отступление “Зачем”#

Исправляемся#

Дополнение#

Предыстория

Смотрим SQL

Отступление “Зачем”

Исправляемся

Дополнение