MySQL `FORCE INDEX` 用例？

Question

几乎在我读到的所有地方都非常不鼓励使用 FORCE INDEX，我完全理解并知道为什么 - MySQL 比(普通)开发人员更清楚要选择哪些索引的可能性非常大。

然而，最近我发现了一个案例，FORCE INDEX 将我的执行时间提高了数百倍:

• JOIN 4 个表
• 第一个表有大约 500 000 条记录
• INNER JOINed 表中有 2 条记录超过 100 万条
• 第一个表有一个名为 published_date 的字段，以 YMD 格式存储为 varchar(不能更改为 datetime)
• 需要在 published_date 上最多包含 5 000 条记录的范围
• 此查询需要在第一个表的不同字段上的其他一些 GROUP BY 和 ORDER BY 子句而不是 published_date

尽管我以多种方式重写了查询，但我无法获得小于 130 秒的执行时间(最高超过 700 秒)。将 FORCE INDEX 与 published_date 一起使用后，执行时间降至 5 秒以下。

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

问题:

• 您发现 FORCE INDEX 拯救了您的其他用例有哪些？
• 您在考虑使用 FORCE INDEX 时是否有一些最佳做法？

编辑 - 观察:我创建了 this blog post这里也有问题。您提供的所有答案也会出现在那里 - 包括学分和您想要的所有东西。

编辑 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. 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

Answer 1

我注意到，当您对 VARCHAR 字段有多个连接和子查询时，FK 和引用的值都不是主键，同时在 DATE 字段上有 where 子句时，FORCE INDEX 会有所帮助。

类似:

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 将始终使用 PKs 和 FKs，您会首先使用支付表上的 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;