У меня есть многораздельная таблица в MySQL, которая выглядит следующим образом:
CREATE TABLE `table1` (
`id` bigint(19) NOT NULL AUTO_INCREMENT,
`field1` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`field2_id` int(11) NOT NULL,
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`,`created_at`),
KEY `index1` (`field2_id`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=603221206 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY RANGE (to_days(created_at))
(PARTITION p_0 VALUES LESS THAN (730485) ENGINE = InnoDB,
..... lots more partitions .....
PARTITION p_20130117 VALUES LESS THAN (735250) ENGINE = InnoDB) */;
И это типичный запрос SELECT к таблице:
SELECT field1 from TABLE1 where field2_id = 12345 and id > 13314313;
Выполнение объяснения, MySQL иногда решает использовать PRIMARY вместо index1. Это кажется довольно последовательным, когда вы делаете первое объяснение. Однако после нескольких повторных объяснений MySQL наконец решает использовать индекс. Проблема в том, что в этой таблице миллионы строк, а операции вставки и выделения ударяются о нее порядка нескольких раз в секунду. Выбор неправильного индекса приводил к тому, что эти запросы SELECT занимали до ~ 40 секунд, а не до секунды. На самом деле не могу запланировать время простоя, поэтому я не могу запустить оптимизацию для таблицы (из-за размера это, вероятно, займет много времени), и я не уверен, что это в любом случае поможет.
Я исправил это, форсируя индекс, поэтому он выглядит следующим образом:
SELECT field1 from TABLE1 FORCE INDEX (index1) WHERE field2_id = 12345 and id > 13314313;
Мы запускаем это на MySQL 5.1.63, от которого мы не можем отойти в данный момент.
Мой вопрос: почему MySQL выбирает неправильный индекс? И можно ли что-то сделать, чтобы это исправить, кроме форсирования индекса по всем запросам? Разбивает ли раздел на части движок InnoDB? Я много работал с MySQL и никогда раньше не видел такого поведения. Запрос настолько прост, насколько это возможно, и индекс также идеально подходит. У нас много запросов, которые предполагают, что слой БД будет делать правильные вещи, и я не хочу проходить через все из них, заставляя использовать правильный индекс.
Обновление 1:
Это типичное объяснение без предложения FORCE INDEX. После того, как это вставлено, столбец возможных ключей показывает только принудительный индекс.
id select_type table type possible_keys key key_len ref rows
1 SIMPLE table1 range PRIMARY,index1 index1 12 NULL 207