Блог

Этот запрос:

 explain 
SELECT `Lineitem`.`id`, `Donation`.`id`, `Donation`.`order_line_id` 
  FROM `order_line` AS `Lineitem` 
       LEFT JOIN `donations` AS `Donation` 
       ON (`Donation`.`order_line_id` = `Lineitem`.`id`) 
 WHERE `Lineitem`.`session_id` = '1'
  

корректно использует индексы Donation.order_line_id и Lineitem.id , показанные в этом выводе EXPLAIN:

идентификатор select_type тип таблицы possible_keys ключ key_len дополнительные строки ссылки
1 ПРОСТОЙ элемент строки, ссылающийся на session_id session_id 97 const 1 С использованием where; С использованием index
1 ПРОСТОЕ пожертвование ref order_line_id order_line_id 4 Lineitem.id 2 Использование индекса

Однако этот запрос, который просто включает другое поле:

 explain 
SELECT `Lineitem`.`id`, `Donation`.`id`, `Donation`.`npo_id`, 
       `Donation`.`order_line_id` 
  FROM `order_line` AS `Lineitem`  
       LEFT JOIN `donations` AS `Donation` 
       ON (`Donation`.`order_line_id` = `Lineitem`.`id`) 
 WHERE `Lineitem`.`session_id` = '1'
  

Показывает, что Donation таблица не использует индекс:

идентификатор select_type тип таблицы possible_keys ключ key_len дополнительные строки ссылки
1 ПРОСТОЙ элемент строки, ссылающийся на session_id session_id 97 const 1 С использованием where; С использованием index
1 ПРОСТОЕ пожертвование ALL order_line_id NULL NULL NULL 3

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

По просьбе Джеймса Си, вот определения таблиц:

 CREATE TABLE `donations` (
`id` int(10) unsigned NOT NULL auto_increment,
`npo_id` int(10) unsigned NOT NULL,
`order_line_detail_id` int(10) unsigned NOT NULL default '0',
`order_line_id` int(10) unsigned NOT NULL default '0',
`created` datetime default NULL,
`modified` datetime default NULL,
PRIMARY KEY  (`id`),
KEY `npo_id` (`npo_id`),
KEY `order_line_id` (`order_line_id`),
KEY `order_line_detail_id` (`order_line_detail_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

CREATE TABLE `order_line` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`order_id` bigint(20) NOT NULL,
`npo_id` bigint(20) NOT NULL default '0',
`session_id` varchar(32) collate utf8_unicode_ci default NULL,
`created` datetime default NULL,
PRIMARY KEY  (`id`),
KEY `order_id` (`order_id`),
KEY `npo_id` (`npo_id`),
KEY `session_id` (`session_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8
  

Я также немного почитал о мощности, и похоже, что у Donations.npo_id и Donations.order_line_id мощность равна 2. Надеюсь, это подсказывает что-то полезное?

Я думаю, что USE INDEX мог бы решить проблему, но я использую ORM, который делает это немного сложнее, и я не понимаю, почему он не захватывает правильный индекс, когда JOIN конкретно называет индексированные поля ?!?

Спасибо за ваши умственные способности!

В конце первого объяснения есть «uses index». Это означает, что он смог найти строки и вернуть результат для запроса, просто просмотрев индекс и не прибегая к извлечению / анализу каких-либо данных строки.

Во втором запросе вы добавляете строку, которая, вероятно, не проиндексирована. Это означает, что MySQL должен просматривать данные таблицы. Я не уверен, почему оптимизатор решил выполнить сканирование таблицы, но я думаю, что, вероятно, если таблица довольно маленькая, ей проще просто прочитать все, чем пытаться выделить детали для отдельных строк.

редактировать: Я думаю, что добавление следующих индексов улучшит ситуацию еще больше и позволит всем объединениям использовать только индексы:

 ALTER TABLE order_line ADD INDEX(session_id, id);
ALTER TABLE donations ADD INDEX(order_line_id, npo_id, id)
  

Это позволит order_line находить строки с помощью session_id , а затем возвращать id , а также позволит donations присоединяться к order_line_id , а затем возвращать два других столбца.

Глядя на auto_increment значения, могу ли я предположить, что там не так много данных. Стоит отметить, что объем данных в таблицах будет влиять на план запроса, и рекомендуется поместить туда несколько образцов данных для проверки. Для получения более подробной информации ознакомьтесь с этим сообщением в блоге, которое я сделал некоторое время назад: http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality /