MySQL`FORCE INDEX`用例?

本文介绍了MySQL`FORCE INDEX`用例?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我强烈反对在几乎所有我读过的FORCE INDEX的用法，我完全理解并知道原因-与(普通)开发人员相比，MySQL有更多的机会更好地了解选择哪种索引.

Almost everywhere I read the use of FORCE INDEX is highly discouraged and I perfectly understand and know why - there are immensely huge chances that MySQL knows better what indexes to choose than the (average) developer.

但是，最近我发现FORCE INDEX将执行时间缩短了数百倍的情况:

However, recently I have found a case where FORCE INDEX improved my execution times in the range of hundred of times:

JOIN在4张桌子上
第一个表有大约50万条记录

INNER JOIN

2条记录超过100万条
第一个表有一个名为published_date的字段，以YMD格式存储为varchar(无法更改为datetime)
published_date最多需要5000条记录
第一个表上的其他GROUP BY和ORDER BY子句，这些子句与published_date不在同一字段上

JOIN on 4 tables
first table has about 500 000 records
2 of the INNER JOINed table have more than 1 milion records
first table has a field called published_date, stored as varchar in YMD format (could not changed to datetime)
needed a range on published_date of at most 5 000 records
some other GROUP BY and ORDER BY clauses on the first table on different fields than published_date were needed for this query

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

Although I've rewritten the query in many ways, I was not able to get execution times smaller than 130 seconds (with highest being over 700 ). After using FORCE INDEX with published_date, the execution time dropped below 5 seconds.

我花了几天的时间来记住臭名昭著的FORCE INDEX选项.

It took me a few days to remember about the infamous FORCE INDEX option.

问题:

您发现FORCE INDEX救了您的其他用例是什么?
考虑使用FORCE INDEX时，您是否有一些最佳做法?

What are other use cases you found where FORCE INDEX saved you?
Do you have some best practices when you consider using FORCE INDEX?

编辑-观察结果:我创建了此博客文章，问题在这里还.您提供的所有答案也将出现在此处-包括点数和您想要的所有内容.

Edit - Obsevations:I created this blog post with the question here also. All the answer you provide will appear there too - whith credits and all the stuff you want.

编辑2

我应用了我在评论中收到的建议(ANALYZE TABLE和OPTIMIZE TABLE)，以下是在查询中应用的EXPLAIN的输出-不幸的是，索引选择一点也不好:

I applied the suggestions I received in your comments (ANALYZE TABLE and OPTIMIZE TABLE), below is the output of EXPLAIN applied on query - unfortunately the index selection is not at all better:

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`:

2. with `FORCE INDEX` on table with alias `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`:

3. after `ANALYZE TABLE`, without `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`:

4. after `OPTIMIZE TABLE`, without `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`:

5. after `OPTIMIZE TABLE` and `ANALYZE TABLE`, with `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

推荐答案

我注意到，当VARCHAR字段上有多个联接和子查询(其中FK和引用值都不是主键)时，FORCE INDEX会有所帮助，同时在DATE字段上具有where子句.

I have noticed that FORCE INDEX helps when you have multiple joins and sub-queries on VARCHAR fields where both the FK and the referenced value are not the primary key, while at the same time having where clause on a DATE field.

类似的东西:

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)会很有帮助.

mysql will always use the PKs and FKs, where you would it to use the payment_date index on the payments table first as it is the largest one. So a FORCE INDEX(payment_date) on the payments table join would help a lot.

这是我们在工作中使用的第三方计费数据库中的示例.我们在优化方面遇到了很多问题，FORCE INDEX大部分时间都在工作.通常，我们使用mysqladmin找到速度较慢的请求，并使用FORCE INDEX对它们进行测试，然后将其发送给供应商以在应用程序的源代码中重写它们.

This an example from the third-party billing database that we use at work. We had huge issues with optimisation, and FORCE INDEX did the job most of the times. Usually we found the slow quires with mysqladmin, tested them with FORCE INDEX and send them to the vendors to rewrite them in the source code of the app.

以下是四个可以更好地理解示例的表格:

Here are the four tables to get a better grip on the example:

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;

这篇关于MySQL`FORCE INDEX`用例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

records