Проблемы с использованием составных индексов для условий по LEFT_MARGIN и RIGHT_MARGIN в запросе к таблице b_sale_location обнаружено, что составные индексы
IX_SALE_LOCATION_MARGINS (LEFT_MARGIN, RIGHT_MARGIN)
IX_SALE_LOCATION_MARGINS_REV (RIGHT_MARGIN, LEFT_MARGIN)
не обеспечивают оптимальную фильтрацию, так как условия запроса используют неравенства для обоих столбцов:
WHERELEFT_MARGIN <= 986790
AND RIGHT_MARGIN >= 989445
Из-за использования условий неравенства (<= и >=) оптимизатор MySQL не может полноценно воспользоваться данными составными индексами:
Для индекса IX_SALE_LOCATION_MARGINS используется только первая колонка (LEFT_MARGIN), поскольку условие – это не точное равенство, а диапазонное.
Аналогично, индекс IX_SALE_LOCATION_MARGINS_REV с первичным условием по RIGHT_MARGIN тоже не может быть использован эффективно.
Шаги для воспроизведения:
- CRE ATE TABLE `b_sale_location` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`SORT` int(11) NOT NULL DEFAULT 100,
`CODE` varchar(100) NOT NULL,
`LEFT_MARGIN` int(11) DEFAULT NULL,
`RIGHT_MARGIN` int(11) DEFAULT NULL,
`PARENT_ID` int(11) DEFAULT 0,
`DEPTH_LEVEL` int(11) DEFAULT 1,
`TYPE_ID` int(11) DEFAULT NULL,
`LATITUDE` decimal(8,6) DEFAULT NULL,
`LONGITUDE` decimal(9,6) DEFAULT NULL,
`COUNTRY_ID` int(11) DEFAULT NULL,
`REGION_ID` int(11) DEFAULT NULL,
`CITY_ID` int(11) DEFAULT NULL,
`LOC_DEFAULT` char(1) NOT NULL DEFAULT 'N',
PRIMARY KEY (`ID`),
UNIQUE KEY `IX_SALE_LOCATION_CODE` (`CODE`),
KEY `IX_SALE_LOCATION_MARGINS` (`LEFT_MARGIN`,`RIGHT_MARGIN`),
KEY `IX_SALE_LOCATION_MARGINS_REV` (`RIGHT_MARGIN`,`LEFT_MARGIN`),
KEY `IX_SALE_LOCATION_PARENT` (`PARENT_ID`),
KEY `IX_SALE_LOCATION_DL` (`DEPTH_LEVEL`),
KEY `IX_SALE_LOCATION_TYPE` (`TYPE_ID`),
KEY `IXS_LOCATION_SORT` (`SORT`),
KEY `IX_SALE_LOCATION_TYPE_MARGIN` (`TYPE_ID`,`LEFT_MARGIN`,`RIGHT_MARGIN`),
KEY `IXS_LOCATION_COUNTRY_ID` (`COUNTRY_ID`),
KEY `IXS_LOCATION_REGION_ID` (`REGION_ID`),
KEY `IXS_LOCATION_CITY_ID` (`CITY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1127119 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Выполнить запрос:
SELECT
sale_location_location_name.NAME AS CHAIN,
sale_location_location.ID AS UALIAS_0,
sale_location_location_name.ID AS UALIAS_1
FR OM b_sale_location sale_location_location
INNER JOIN b_sale_loc_name sale_location_location_name
ON sale_location_location.ID = sale_location_location_name.LOCATION_ID
WHERE UPPER(sale_location_location_name.LANGUAGE_ID) LIKE UPPER('ru')
AND sale_location_location.LEFT_MARGIN <= 986790
AND sale_location_location.RIGHT_MARGIN >= 989445
ORDER BY sale_location_location.LEFT_MARGIN ASC;Провести анализ плана выполнения запроса через EXPLAIN и обратить внимание на использование индексов.
Фактическое поведение:
Оптимизатор MySQL при анализе запроса использует только часть составного индекса – например, для IX_SALE_LOCATION_MARGINS используется только колонка LEFT_MARGIN. Это приводит к недостаточной селективности и замедлению выполнения запроса при большом объёме данных.
Ожидаемое поведение:
Оптимизатор MySQL мог бы использовать более релевантные индексы для условий с неравенствами по обоим столбцам.
- ALT ER TABLE b_sale_location
ADD KEY IXS_LOCATION_LEFT_MARGIN (LEFT_MARGIN),
ADD KEY IXS_LOCATION_RIGHT_MARGIN (RIGHT_MARGIN); Такой индекс позволит выполнять запросы, используя только данные индекса, что снизит число обращений к таблице.
Текущее использование составных индексов (IX_SALE_LOCATION_MARGINS и IX_SALE_LOCATION_MARGINS_REV) не позволяет оптимизатору полноценно применять их для диапазонных условий по обоим столбцам.
Индекс Merge может быть вариантом, но создание отдельных или покрывающего индекса должно дать более стабильный выигрыш в производительности.
