MySQL: индекс не используется в одиночных запросах или запросах объединения

ProgramBox

MySQL: индекс не используется в одиночных запросах или запросах объединения

Post author:admin
Запись опубликована:21 мая, 2023
Post category:Вопросы по программированию

#mysql #sql #join #database-design #indexing

#mysql — сервер #sql #Присоединиться #проектирование базы данных #индексирование #mysql #база данных-дизайн

Вопрос:

Я использую MySQL версии 5.6.19, и у меня возникла эта проблема. Я не знаю, есть ли какая-то очень базовая тема, которую я забыл, или это действительно что-то странное.

Ну, у меня есть клиенты, коды (связанные с клиентами) и продукты. Продукты напрямую связаны с клиентом или кодом.

Пример сценария таков:

     DROP TABLE IF EXISTS `CUSTOMERS`;
CREATE TABLE `CUSTOMERS` (
  `REQ_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` VARCHAR(15) COLLATE LATIN1_SPANISH_CI NOT NULL ,
  `SEGMENT_ID` INT(10) UNSIGNED NOT NULL,
  `STATUS` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`REQ_ID`),
  KEY `IDX_CUSTOMER_ID` (`CUSTOMER_ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1 COLLATE=LATIN1_SPANISH_CI;

INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (1,'CA.5310',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (2,'KH.5880',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (3,'QQ.4639',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (4,'TH.7692',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (5,'YU.6972',1,1);
INSERT INTO `CUSTOMERS` (REQ_ID,CUSTOMER_ID,SEGMENT_ID,STATUS) VALUES (6,'ZA.7926',1,1);

COMMIT;

DROP TABLE IF EXISTS `CUSTOMER_CODES`;
CREATE TABLE `CUSTOMER_CODES` (
  `REQ_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CODE_ID` VARCHAR(20) COLLATE LATIN1_SPANISH_CI NOT NULL,
  `CUSTOMER_ID` VARCHAR(15) COLLATE LATIN1_SPANISH_CI NOT NULL,
  PRIMARY KEY (`REQ_ID`),
  KEY `IDX_CODE_ID` (`CODE_ID`),
  KEY `IDX_CUSTOMER_ID` (`CUSTOMER_ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1 COLLATE=LATIN1_SPANISH_CI;

INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (1,'AW-5332-R-918806','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (2,'CA-7600-F-419496','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (3,'DJ-6557-X-562485','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (4,'DL-6266-L-449657','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (5,'DS-9853-K-509896','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (6,'EF-6540-Z-075718','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (7,'EY-4689-L-798950','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (8,'HU-9798-W-580898','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (9,'HW-2352-Q-563124','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (10,'IJ-0691-P-347580','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (11,'IY-1366-C-744557','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (12,'JX-2997-A-619073','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (13,'KW-8547-V-632604','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (14,'LD-3972-X-404768','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (15,'OH-8414-Q-681690','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (16,'QB-3065-E-961964','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (17,'RS-8531-A-605359','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (18,'RT-0093-P-934263','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (19,'SR-9752-I-936573','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (20,'TT-1675-V-069568','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (21,'UX-9967-P-917243','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (22,'VB-1578-E-917704','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (23,'XT-9346-I-369510','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (24,'YD-5031-S-685069','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (25,'ZU-5811-D-519507','CA.5310');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (26,'AI-8578-P-297337','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (27,'BV-8935-R-335510','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (28,'CE-5544-B-140242','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (29,'DU-4606-X-292810','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (30,'HU-9125-V-564299','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (31,'JM-2356-C-286826','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (32,'KP-2970-D-251577','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (33,'OV-8244-W-588479','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (34,'RL-4228-G-538911','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (35,'VO-3663-O-065655','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (36,'YJ-8463-N-388866','KH.5880');
INSERT INTO `CUSTOMER_CODES` (REQ_ID,CODE_ID,CUSTOMER_ID) VALUES (37,'ZV-5622-T-443263','KH.5880');
COMMIT;

DROP TABLE IF EXISTS `CODE_OR_CUSTOMER_PRODUCTS`;
CREATE TABLE `CODE_OR_CUSTOMER_PRODUCTS` (
  `REL_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `CODE_OR_CUSTOMER_ID` VARCHAR(20) COLLATE LATIN1_SPANISH_CI NOT NULL,
  `PRODUCT_ID` VARCHAR(55) COLLATE LATIN1_SPANISH_CI NOT NULL,
  `START_DATE` DATETIME NOT NULL,
  `END_DATE` DATETIME DEFAULT NULL,
  PRIMARY KEY (`REL_ID`),
  KEY `IDX_ENTIDAD` (`CODE_OR_CUSTOMER_ID`),
  KEY `IDX_ENTIDAD_FECHAS` (`CODE_OR_CUSTOMER_ID`,`START_DATE`,`END_DATE`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1 COLLATE=LATIN1_SPANISH_CI;

INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (1,'AW-5332-R-918806','DISC_01','2013-12-15 00:00:00',NULL);
INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (2,'AW-5332-R-918806','DISC_02','2014-01-13 00:00:00','2014-01-29 00:00:00');
INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (3,'AW-5332-R-918806','DISC_02','2014-02-01 00:00:00',NULL);
INSERT INTO `CODE_OR_CUSTOMER_PRODUCTS` (REL_ID,CODE_OR_CUSTOMER_ID,PRODUCT_ID,START_DATE,END_DATE) VALUES (4,'CA.5310','DISC_03','2013-01-01 00:00:00',NULL);

COMMIT;

Я пробую этот подход, потому что я не хочу использовать отношение customer-code к таблице CODE_OR_CUSTOMERS_PRODUCTS.

Когда я запускаю EXPLAIN для этого запроса :

 EXPLAIN SELECT 
CODE_OR_CUSTOMER_ID, 
PRODUCT_ID
FROM CODE_OR_CUSTOMER_PRODUCTS
where 
    ( 
    CODE_OR_CUSTOMER_ID = 'CA.5310'
    or
    CODE_OR_CUSTOMER_ID IN (
                SELECT CODE_ID
                FROM CUSTOMERS, CUSTOMER_CODES
                where CUSTOMERS.CUSTOMER_ID = CUSTOMER_CODES.CUSTOMER_ID
                and CUSTOMERS.CUSTOMER_ID = 'CA.5310'
            )
    )
    and START_DATE <= '2014-01-31' and (END_DATE IS NULL OR '2014-01-01' <= END_DATE)

У меня есть доступ к полной таблице CODE_OR_CUSTOMERS_PRODUCTS:

 *'1', 'PRIMARY', 'CODE_OR_CUSTOMER_PRODUCTS', '**ALL**', 'IDX_ENTIDAD,IDX_ENTIDAD_FECHAS', NULL, NULL, NULL, '4', 'Using where'*

Я думаю, это из-за предложения IN, но я не хочу запрашивать много раз (возможно, сотни) один CUSTOMER_ID и связанный с ним CODE_ID.

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

Большое спасибо

Ответ №1:

Предложение IN можно исключить с помощью JOINs. Но также должна быть возможность удалить ИЛИ, объединив 2 более простых запроса:-

 SELECT CODE_OR_CUSTOMER_ID, 
    PRODUCT_ID
FROM CODE_OR_CUSTOMER_PRODUCTS
WHERE CODE_OR_CUSTOMER_ID = 'CA.5310'
AND START_DATE <= '2014-01-31' AND (END_DATE IS NULL OR '2014-01-01' <= END_DATE)
UNION
SELECT CODE_OR_CUSTOMER_ID, 
    PRODUCT_ID
FROM CODE_OR_CUSTOMER_PRODUCTS
INNER JOIN CUSTOMER_CODES ON CUSTOMER_CODES.CODE_ID = CODE_OR_CUSTOMER_PRODUCTS.CODE_OR_CUSTOMER_ID
INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMER_ID = CUSTOMER_CODES.CUSTOMER_ID AND CUSTOMERS.CUSTOMER_ID = 'CA.5310'
WHERE  START_DATE <= '2014-01-31' AND (END_DATE IS NULL OR '2014-01-01' <= END_DATE)

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

1. спасибо за ваш ответ, потому что я сильно застрял. Второй запрос ОБЪЕДИНЕНИЯ по-прежнему обращается к CODE_OR_CUSTOMER_PRODUCTS ВСЕМИ . Я не знаю, что происходит, но я повторил тестовый пример с тестовыми таблицами (от 2 до 6 строк), и вывод EXPLAIN ссылается на индексы, итак, это полный доступ из-за данных, или потому, что я чего-то не хватает?

2. Если имеется только небольшое количество строк или индекс не сильно сужает количество строк, MySQL проигнорирует это. Для тестовых данных в вашем примере вторая часть запроса использует 3/4 строк из CODE_OR_CUSTOMER_PRODUCTS (на основе диапазона дат). Очень грубо это выглядит так, что если использование индекса не сузит количество возвращаемых строк до менее 1/3, MySQL проигнорирует индекс. Обратите внимание, что если вместо этого выполняется проверка CODE_OR_CUSTOMER_ID, то это также использует 3/4 строк

3. спасибо за ваше предложение. Ну, полный доступ возможен благодаря данным. Я вставил много данных для других customer_id и code_id, поэтому значение CA.5310 содержит только несколько связанных данных. Затем объясненный запрос показывает, что доступ осуществляется по диапазону, лучше, чем все.

Ответ №2:

Причина, по которой индекс в CODE_OR_CUSTOMER_PRODUCTS не используется, заключается в том, что у вас есть PRODUCT_ID в списке выбора, и он не содержится ни в одном индексе. Поэтому, как только соответствующая строка найдена, должен быть выполнен другой логический ввод-вывод для получения значения этого поля. Поскольку строки маленькие и их очень мало, оптимизатор просто прочитал всю таблицу одним махом и сохранил некоторый ввод-вывод. Если ваша фактическая таблица содержит много строк и / или строки содержат много полей, которые вы опустили для простоты, то IDX_ENTIDAD_FECHAS индекс может использоваться для поиска с дополнительной логической прошивкой, выполняемой по мере необходимости (номинально очень небольшой процент времени; то есть несколько строк проходят через фильтр).

Вы могли бы добавить PRODUCT_ID к индексу и, таким образом, сделать его полным покрывающим индексом. Тогда индекс будет использоваться, я гарантирую это.

Если позволите, это очень неудобный дизайн. Но это не ваша проблема, поэтому я придержу свои комментарии по этому поводу. Однако при работе с проектами, имеющими столбцы Startxxx / Endxxx, обычно выполняется поиск по дате, которая находится между датами начала / окончания: search_date >= START and search_date < END . Если это так в данном случае, то там, где у вас есть START_DATE <= '2014-01-31' и '2014-01-01' <= END_DATE , вы на самом деле имеете в виду START_DATE >= '2014-01-01' и '2014-01-31' > END_DATE . В противном случае ваше предложение WHERE не имеет смысла.