MySQL `FORCE INDEX`使用场景？

Question

几乎所有我读到的地方都不建议使用FORCE INDEX，我非常理解并知道为什么——MySQL比（普通）开发人员更有可能知道选择哪些索引。

然而，最近我发现了一种情况，在这种情况下，FORCE INDEX将我的执行时间提高了数百倍：

• 在4个表上进行JOIN
• 第一个表约有500,000条记录
• 2个INNER JOIN的表中有超过1百万条记录
• 第一个表有一个名为published_date的字段，以YMD格式存储为varchar（无法更改为datetime）
• 需要对最多5,000条记录的published_date范围进行筛选
• 此查询需要在第一个表上使用其他与published_date不同的字段进行GROUP BY和ORDER BY子句

尽管我已经以多种方式重新编写了查询，但我无法获得小于130秒的执行时间（最高超过700）。在使用published_date的FORCE INDEX后，执行时间降至5秒以下。

让我花了几天时间才想起臭名昭著的FORCE INDEX选项。 问题：

• 你发现在哪些其他用例中使用FORCE INDEX可以节省时间？
• 在考虑使用FORCE INDEX时，您是否有一些最佳实践？

编辑-观察： 我使用此处的问题创建了这篇博客文章。您提供的所有答案也将出现在那里-带有信用和所有您想要的内容。

编辑2：

我应用了您在评论中收到的建议（ANALYZE TABLE和OPTIMIZE TABLE），下面是应用于查询的EXPLAIN输出-不幸的是，索引选择并没有改善：

1. 在别名为a的表上没有使用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

2. 使用别名为a的表和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

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. 在没有 FORCE INDEX 的情况下，执行 OPTIMIZE TABLE:

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

- Tudor Constantin

---

你是否在那些你需要使用 FORCE INDEX 的表上运行了 ANALYZE TABLE 命令？ - Romain

我的经验也告诉我，指示规划器使用特定索引的情况非常罕见，大多数时候它会由于受损或有问题的索引而选择不当，但是这可以通过执行ANALYZE命令来修复。 - jishi

@Romain - 没有运行 analyze table...好主意。 - Tudor Constantin

@TudorConstantin 你可以尝试分析它们，然后将新的“原始”查询计划与“强制”计划进行比较...也许在此之后它们会是相同的。 - Romain

强制使用特定索引的问题在于，即使今天能够获得更好的性能，也很难预测表统计信息修改后的后果，尤其是对于复杂查询。查询优化器几乎总是选择最佳执行计划，并且还可以适应变化。除了使用ANALYZE更新表统计信息外，如果您正在进行大量修改（大量DELETE / INSERT），还可以使用OPTIMIZE http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html，它还会对索引页面进行排序。 - georgepsarakis

Answer 1

我注意到FORCE INDEX在多个连接和子查询中对VARCHAR字段非主键的FK和引用值同时存在，且同时在日期字段上具有where条件时非常有帮助。

例如：

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';

在IT技术中，MySQL会始终使用主键（PKs）和外键（FKs），但是如果您希望优先使用payments表上的payment_date索引，因为这是最大的一个，请使用FORCE INDEX(payment_date)来执行与payments表的联接，这将对性能有很大帮助。

这是我们工作中使用的第三方计费数据库的示例。我们曾经遇到过大量的优化问题，而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;

- Martin Taleski

---

为什么MySQL会始终在表customers上使用主键？这是否取决于连接中表的顺序？ - dma_k

我已经编辑了这篇文章...我遇到的更大问题是没有在付款表上使用日期索引。然而，我认为表的顺序确实会影响使用索引的顺序。 - Martin Taleski

你说了一大堆，但是却没有在任何地方提供关于FORCED_INDEX解决方案的参考。如果你建议我们在第一个查询中使用它，为什么不列出一个已经实现了FORCED_INDEX的第二个查询呢？感谢你的尝试帮助。 - JasonMichael

Answer 2

我注意到通过您的“EXPLAIN”计划，表序列已经改变，前两个表被颠倒了，这可能是您的性能提升的原因，除了使用日期索引。

您是否考虑过在查询中使用“STRAIGHT_JOIN”来强制表的顺序？

我曾经在一个大型数据库架构上工作过，在那里，最佳的连接配置一直使用“STRAIGHT_JOIN”，而且性能比“INNER JOIN”等价物提高了100倍。

不幸的是，我不再有机会获取一些示例“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获得更好的查询性能，后者本质上只是颠倒了表的顺序。

回到原始查询，去除强制索引，并用STRAIGHT_JOIN替换INNER JOIN，看看解释计划给出的结果。

您还可以在a表上创建一个复合索引，使用pub_date和serial，我认为这将进一步改善查询。

- Dave Rix