Использованы случаи использования MySQL `FORCE INDEX`?
Почти везде я читаю, что использование FORCE INDEX сильно обескуражено, и я прекрасно понимаю и знаю почему - есть огромные шансы, что MySQL лучше знает, какие индексы выбрать, чем (средний) разработчик.
Однако недавно я нашел случай, когда FORCE INDEX улучшил время выполнения в диапазоне 100 раз:
-
JOIN на 4 таблицах
- первая таблица содержит около 500 000 записей
- 2 таблицы
INNER JOIN ed имеют более 1 milion records
- В первой таблице есть поле под названием
published_date, которое хранится как varchar в формате YMD (не может быть изменено на datetime)
- нужен диапазон на
published_date не более 5 000 записей
- для этого запроса были необходимы другие
GROUP BY и ORDER BY предложения по первой таблице в разных полях, чем published_date
Хотя я переписал запрос во многих отношениях, мне не удалось получить время выполнения меньше 130 секунд (с наивысшим значением более 700). После использования FORCE INDEX с published_date время выполнения опустилось ниже 5 секунд.
Мне потребовалось несколько дней, чтобы вспомнить о печально известной опции FORCE INDEX.
Вопросы:
- Какие другие варианты использования вы нашли, где
FORCE INDEX сохранили вас?
- Есть ли у вас несколько лучших практик, когда вы рассматриваете использование
FORCE INDEX?
Изменить - Обобщения:
Я создал этот пост в блоге с вопросом здесь. Весь ответ, который вы предоставите, также появится там - с кредитами и всем необходимым.
Изменить 2
Я применил предложения, которые я получил в ваших комментариях (ANALYZE TABLE и OPTIMIZE TABLE), ниже приведен результат EXPLAIN, примененный к запросу - к сожалению, выбор индекса не лучше:
1. без FORCE INDEX на таблице с псевдонимом a:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE am2 range PRIMARY,idx_meta_article idx_meta_article 4 NULL 275228 Using where; Using index; Using temporary; Using f...
1 SIMPLE a eq_ref PRIMARY,serial_issue_date_productid,pub_date,idx_d... PRIMARY 4 mydb_toto.am2.ArticleID 1 Using where
1 SIMPLE ai ref PRIMARY,idx_iso_article PRIMARY 4 mydb_toto.a.serial 11523 Using where; Using index
1 SIMPLE m range PRIMARY,meta_articles_type meta_articles_type 4 NULL 96 Using where
1 SIMPLE am eq_ref PRIMARY,idx_meta_article PRIMARY 8 mydb_toto.a.serial,mydb_toto.m.meta_id 1 Using where; Using index
2. с FORCE INDEX на таблице с псевдонимом a:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a range pub_date pub_date 11 NULL 17679 Using where; Using temporary; Using filesort
1 SIMPLE am2 ref PRIMARY,idx_meta_article PRIMARY 4 mydb_toto.a.serial 21930 Using where; Using index
1 SIMPLE ai ref PRIMARY,idx_iso_article PRIMARY 4 mydb_toto.a.serial 11523 Using where; Using index
1 SIMPLE m range PRIMARY,meta_articles_type meta_articles_type 4 NULL 96 Using where
1 SIMPLE am eq_ref PRIMARY,idx_meta_article PRIMARY 8 mydb_toto.am2.ArticleID,mydb_toto.m.meta_id 1 Using where; Using index
3. после ANALYZE TABLE, без FORCE INDEX:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE am2 range PRIMARY,idx_meta_article idx_meta_article 4 NULL 275228 Using where; Using index; Using temporary; Using f...
1 SIMPLE a eq_ref PRIMARY,serial_issue_date_productid,pub_date,idx_d... PRIMARY 4 mydb_toto.am2.ArticleID 1 Using where
1 SIMPLE ai ref PRIMARY,idx_iso_article PRIMARY 4 mydb_toto.a.serial 11523 Using where; Using index
1 SIMPLE m range PRIMARY,meta_articles_type meta_articles_type 4 NULL 96 Using where
1 SIMPLE am eq_ref PRIMARY,idx_meta_article PRIMARY 8 mydb_toto.a.serial,mydb_toto.m.meta_id 1 Using where; Using index
4. после OPTIMIZE TABLE, без FORCE INDEX:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE am2 range PRIMARY,idx_meta_article idx_meta_article 4 NULL 275228 Using where; Using index; Using temporary; Using f...
1 SIMPLE a eq_ref PRIMARY,serial_issue_date_productid,pub_date,idx_d... PRIMARY 4 mydb_toto.am2.ArticleID 1 Using where
1 SIMPLE ai ref PRIMARY,idx_iso_article PRIMARY 4 mydb_toto.a.serial 11523 Using where; Using index
1 SIMPLE m range PRIMARY,meta_articles_type meta_articles_type 4 NULL 96 Using where
1 SIMPLE am eq_ref PRIMARY,idx_meta_article PRIMARY 8 mydb_toto.a.serial,mydb_toto.m.meta_id 1 Using where; Using index
5. после OPTIMIZE TABLE и ANALYZE TABLE, с FORCE INDEX:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a range pub_date pub_date 11 NULL 17679 Using where; Using temporary; Using filesort
1 SIMPLE am2 ref PRIMARY,idx_meta_article PRIMARY 4 mydb_toto.a.serial 21930 Using where; Using index
1 SIMPLE ai ref PRIMARY,idx_iso_article PRIMARY 4 mydb_toto.a.serial 11523 Using where; Using index
1 SIMPLE m range PRIMARY,meta_articles_type meta_articles_type 4 NULL 96 Using where
1 SIMPLE am eq_ref PRIMARY,idx_meta_article PRIMARY 8 mydb_toto.am2.ArticleID,mydb_toto.m.meta_id 1 Using where; Using index
Ответы
Ответ 1
Я заметил, что FORCE INDEX помогает, когда у вас есть несколько объединений и подзапросов в полях VARCHAR, где FK и ссылочное значение не являются первичным ключом, и в то же время имеют предложение where в поле DATE.
Что-то вроде:
SELECT NAME, a.reference_no, i.value, p.value FROM customers AS c
INNER JOIN accounts AS a ON c.id = a.customer_id
INNER JOIN invoices AS i ON i.reference_no = a.reference_no
INNER JOIN payments AS p ON p.invoice_no = i.invoice_no
WHERE payments.date >= '2011-09-01' AND DATE < '2011-10-01';
mysql всегда будет использовать PK и FK, где вы бы сначала использовали индекс payment_date в таблице платежей, поскольку он является самым большим. Таким образом, FORCE INDEX(payment_date) в соединении платежных таблиц поможет много.
Это пример из базы данных биллинга сторонних разработчиков, которую мы используем на работе. У нас были огромные проблемы с оптимизацией, и FORCE INDEX выполнял эту работу большую часть времени. Обычно мы находили медленные запросы с mysqladmin, тестировали их с помощью FORCE INDEX и отправляли их поставщикам, чтобы переписать их в исходном коде приложения.
Вот четыре таблицы, чтобы лучше понять пример:
CREATE TABLE `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE `accounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`reference_no` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `reference_no_uniq` (`reference_no`),
KEY `FK_accounts` (`customer_id`),
CONSTRAINT `FK_accounts` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
CREATE TABLE `invoices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`reference_no` varchar(10) NOT NULL,
`invoice_no` varchar(10) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `invoice_no_uniq` (`invoice_no`),
KEY `FK_invoices` (`reference_no`),
CONSTRAINT `FK_invoices` FOREIGN KEY (`reference_no`) REFERENCES `accounts` (`reference_no`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
CREATE TABLE `payments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`invoice_no` varchar(10) NOT NULL,
`value` int(11) NOT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_payments` (`invoice_no`),
KEY `payment_date` (`date`),
CONSTRAINT `FK_payments` FOREIGN KEY (`invoice_no`) REFERENCES `invoices` (`invoice_no`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
Ответ 2
Я заметил в ваших планах EXPLAIN, что последовательность таблиц изменилась, причем первые две таблицы были отменены, что вполне может быть там, где ваши улучшения производительности исходят, помимо использования индекса даты.
Изучали ли вы использование STRAIGHT_JOIN в запросах, чтобы заставить последовательность таблиц?
Я работал над большой схемой базы данных, где оптимальная конфигурация соединения использовала STRAIGHT_JOIN полностью через запрос, а производительность была в 100 раз улучшена по сравнению с эквивалентами INNER JOIN.
К сожалению, у меня нет доступа к системе, чтобы получить некоторые примеры EXPLAIN планов, но оптимальная последовательность таблиц прошла примерно так:
Table 1 10 rows 1 analysed
Table 2 500 rows 50 analysed
Table 3 1,000,000 rows 300,000 analysed
Table 4 500,000,000 rows 4,000,000 analysed
Использование STRAIGHT_JOIN для сохранения этой последовательности привело к тому, что производительность запроса намного превосходила эквивалент INNER JOIN, который по существу просто менял последовательность таблиц.
Вернитесь к исходному запросу, удалите индекс силы и замените INNER JOIN на STRAIGHT_JOIN и посмотрите, что дает вам план объяснения.
Вы также можете создать составной индекс в таблице a, используя pub_date и serial, что, я думаю, еще больше улучшит запрос.
