优化程序提示，可加快MySQL查询的执行速度_程序员人生_博客_测评&博客

MySQL使用基于成本的优化器来确定执行查询的最佳方法。它通常做得很好，但并非总是如此。有不同的原因。在某些情况下，它可能没有足够的关于数据的信息，并且以非最佳方式计划查询。

优化器基于统计信息和每次操作的固定成本制定决策，但是它不了解硬件的差异。例如，根据所使用的存储类型，磁盘访问可能会有不同的成本-SSD驱动器的访问时间将比主轴更快，并且可以在给定时间内执行更多操作。

但是，可能会影响查询的执行方式，这是当今博客的主题。

（请注意，在MySQL 5.7中，为了改善这一点已经做了很多工作-用户可以修改不同类型操作的成本。）

这是“成为MySQL DBA”博客系列的第14部分。我们在DBA系列中的先前文章包括使用EXPLAIN改进SQL查询，数据库索引，深入研究pt-query-digest，使用pt-query-digest分析SQL工作负载，查询调优过程，配置调优，使用MySQL复制进行实时迁移，数据库升级，复制拓扑更改，架构更改，高可用性，备份和还原，监视和趋势。

指数统计

我们可以影响查询执行方式的方法之一是使用索引提示。优化器会根据查询的最佳索引来做出决策，这取决于InnoDB引擎为其提供的索引统计信息。首先让我们看看InnoDB统计如何工作以及如何更改它。

历史上不时重新计算索引统计信息。当有人明确执行ANALYZE TABLE或在第一次打开表时发生了这种情况。但是在执行SHOW TABLE STATUS，SHOW TABLES或SHOW INDEX时也会发生。除此之外，当修改表中的1/16行或20亿行时，表统计信息也会更新。这带来了一些不稳定。为了计算统计信息，InnoDB对8个索引页（是，八个！）进行查询。这是用于计算100G索引的统计数据的128k数据。从某种意义上讲，这很有意义-您进行的索引查询越多，更新索引统计信息所花费的时间就越长，为此需要更多的I / O-这不是您希望看到的。另一方面，很明显，这么小的样本可能会在最终结果中引入较大的差异。但是，InnoDB统计信息的变化会影响查询执行计划。通过更改可以更改此设置innodb_stats_sample_pages可以使统计信息更加稳定和“更接近于现实”，但这是以增加I / O为代价的。

从MySQL 5.6.6开始，InnoDB统计信息可以保持（这是默认设置）。不会为每个SHOW TABLE STATUS和类似命令重新计算统计信息。当在表上运行显式ANALYZE TABLE或表中超过10％的行被修改时，它们将更新。可以使用innodb_stats_auto_recalc变量来修改此阈值。

启用持久性统计信息后，InnoDB会对20个索引页执行查找以计算它们。它比我们以前拥有的要多。但是，统计数据的计算不是那么频繁，而且在收集它们时附加I / O的影响并不高。查询计划也应该更稳定，因为基础统计信息比以前更稳定。同样，可以通过更改innodb_stats_persistent_sample_pages变量来更改此默认设置。

可以通过禁用innodb_stats_persistent来禁用持久性统计信息并恢复为旧行为-在某些极端情况下，这可能是最佳选择。

索引提示

众所周知，索引统计仅是一种估计。查询执行计划可能不是最佳的，或者（甚至更糟）是在多个版本之间摇摆的情况。这很严重，因为它会导致性能不稳定。

幸运的是，当我们发现查询执行计划不适合我们的查询时，它使我们能够修改查询执行计划。让我们看看我们如何做到这一点。

在此示例中，我们将使用“ sakila”模式。假定以下查询执行计划：

1个

mysql> EXPLAIN SELECT inventory_id FROM rental WHERE rental_date > '2005-05-24 00:00:00' AND rental_date < '2005-05-28 00:00:00' AND customer_id IN (234, 123, 412, 23)G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: rental

type: range

possible_keys: rental_date,idx_fk_customer_id

key: rental_date

key_len: 5

ref: NULL

rows: 483

Extra: Using where; Using index

1 row in set (0.00 sec)

在这里，我们要查找某些客户在某个时间段内完成的租金的ventory_id。关于索引，优化器有两个选项-它可以使用rental_date键或idx_fk_customer_id。它选择了rental_date对其执行范围扫描。它很可能是由于它在此特定情况下还涵盖了索引这一事实而产生的。

假设我们实际上并不需要做更多的I / O操作，我们想在'customer_id'列上使用索引来执行索引查找。请记住，此更改还将更改I / O操作的访问模式-我们将执行随机读取，而不是索引扫描+覆盖索引（这是顺序访问）。此类更改可能不适用于主轴。不过，SSD不会注意到它。通过执行以下查询之一，可以在优化器上强制执行选择：

1个

18岁

mysql> EXPLAIN SELECT inventory_id FROM rental FORCE INDEX(idx_fk_customer_id) WHERE rental_date > '2005-05-24 00:00:00' AND rental_date < '2005-05-28 00:00:00' AND customer_id IN (234, 123, 412, 23)G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: rental

type: range

possible_keys: idx_fk_customer_id

key: idx_fk_customer_id

key_len: 2

ref: NULL

rows: 101

Extra: Using index condition; Using where

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT inventory_id FROM rental USE INDEX(idx_fk_customer_id) WHERE rental_date > '2005-05-24 00:00:00' AND rental_date < '2005-05-28 00:00:00' AND customer_id IN (234, 123, 412, 23)G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: rental

type: range

possible_keys: idx_fk_customer_id

key: idx_fk_customer_id

key_len: 2

ref: NULL

rows: 101

Extra: Using index condition; Using where

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT inventory_id FROM rental IGNORE INDEX(rental_date) WHERE rental_date > '2005-05-24 00:00:00' AND rental_date < '2005-05-28 00:00:00' AND customer_id IN (234, 123, 412, 23)G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: rental

type: range

possible_keys: idx_fk_customer_id

key: idx_fk_customer_id

key_len: 2

ref: NULL

rows: 101

Extra: Using index condition; Using where

1 row in set (0.00 sec)

我们要做的是使用“ USE INDEX”，“ FORCE INDEX”和“ IGNORE INDEX”提示。

“ USE INDEX”告诉优化器它应该使用列出的索引之一。在我们的例子中，我们只列出了一个，因此使用了它。

“ FORCE INDEX”与“ USE INDEX”基本上具有相同的行为，除了“ USE INDEX”以外，优化器可能仍选择对我们的查询使用全表扫描。使用“ FORCE INDEX”，全表扫描被标记为极其昂贵的操作，因此，只要任何列出的索引可用于我们的特定查询，优化器就不会使用它。

“ IGNORE INDEX”告诉优化器我们不需要哪些索引。在我们的例子中，我们将“ rental_date”列为我们要避免的索引。因此，它决定在查询执行计划中选择另一个选项。

在另一个示例中，我们将再次使用'sakila'模式，但还要进行另一处更改：

1个	`ALTER` `TABLE` `actor` `ADD` `KEY` `idx_actor_first_name (first_name);`

让我们假设以下查询：

1个

18岁

mysql> EXPLAIN SELECT title, description FROM film AS f JOIN film_actor AS fa ON f.film_id = fa.film_id JOIN actor AS a ON fa.actor_id = a.actor_id WHERE a.last_name = 'MIRANDA' AND f.release_year = 2006 ORDER BY a.first_nameG

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: a

type: ref

possible_keys: PRIMARY,idx_actor_last_name

key: idx_actor_last_name

key_len: 137

ref: const

rows: 1

Extra: Using index condition; Using where; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: fa

type: ref

possible_keys: PRIMARY,idx_fk_film_id

key: PRIMARY

key_len: 2

ref: sakila.a.actor_id

rows: 1

Extra: Using index

*************************** 3. row ***************************

id: 1

select_type: SIMPLE

table: f

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 2

ref: sakila.fa.film_id

rows: 1

Extra: Using where

3 rows in set (0.00 sec)

现在假设我们要使用索引对结果集进行排序，而不是根据优化程序的决定，对“ actor”表中“ last_name”列的查找进行索引。让我们暂不讨论这种更改在这里是否有意义（因为它没有用）。

我们可以做的是确保未将“ idx_actor_last_name”索引用于JOIN，并且将对ORDER BY使用“ idx_actor_first_name”索引。我们可以这样做：

1个

18岁

mysql> EXPLAIN SELECT title, description FROM film AS f JOIN film_actor AS fa ON f.film_id = fa.film_id JOIN actor AS a IGNORE INDEX FOR JOIN (idx_actor_last_name) FORCE INDEX FOR ORDER BY(idx_actor_first_name) ON fa.actor_id = a.actor_id WHERE a.last_name LIKE 'B%' AND f.release_year = 2006 ORDER BY a.first_nameG

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: a

type: index

possible_keys: PRIMARY

key: idx_actor_first_name

key_len: 137

ref: NULL

rows: 200

Extra: Using where

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: fa

type: ref

possible_keys: PRIMARY,idx_fk_film_id

key: PRIMARY

key_len: 2

ref: sakila.a.actor_id

rows: 1

Extra: Using index

*************************** 3. row ***************************

id: 1

select_type: SIMPLE

table: f

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 2

ref: sakila.fa.film_id

rows: 1

Extra: Using where

3 rows in set (0.00 sec)

请注意提示的位置-它们位于相关表及其别名之后：

1个	`JOIN` `actor` `AS` `a` `IGNORE` `INDEX` `FOR` `JOIN` `(idx_actor_last_name)` `FORCE` `INDEX` `FOR` `ORDER` `BY(idx_actor_first_name)`

因此，优化器可以将提示链接到正确的表。
除了…FOR JOIN和…FOR ORDER BY之外，还有一个提示：…FOR GROUP BY，用于使用索引来聚合数据。

当您使用USE INDEX，FORCE INDEX或IGNORE INDEX时，等同于合并所有前面提到的提示。例如：

强制索引（idx_myindex）：

联接的强制索引（idx_myindex）排序依据的强制
索引（idx_myindex）分组依据的强制
索引（idx_myindex）

加入订单修改

当您使用JOIN执行任何查询时，MySQL优化器必须确定这些表的连接顺序。您可能对它带来的顺序不满意。

让我们看一下这个查询。

1个

18岁

mysql> EXPLAIN SELECT actor_id, title FROM film_actor AS fa JOIN film AS f ON fa.film_id = f.film_id ORDER BY fa.actor_idG

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: f

type: index

possible_keys: PRIMARY

key: idx_title

key_len: 767

ref: NULL

rows: 1000

Extra: Using index; Using temporary; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: fa

type: ref

possible_keys: idx_fk_film_id

key: idx_fk_film_id

key_len: 2

ref: sakila.f.film_id

rows: 2

Extra: Using index

2 rows in set (0.00 sec)

请注意，在查询中，我们正在使用“ film_actor.actor_id”列对数据进行排序。在当前查询执行计划中，优化器从“ film”表开始，然后使用idx_fk_film_id索引将“ film_actor”连接到该表。必须使用文件排序算法和临时表来执行排序，因为当前的连接顺序使得无法使用任何索引进行排序。

假设由于某种原因（也许临时表影响太大），我们宁愿避免文件排序并使用索引执行ORDER BY。有可能使用我们可以使用的索引：

1个	`PRIMARY` `KEY` (`actor_id`,`film_id`),

有一种简单的更改方法-我们可以使用STRAIGHT_JOIN提示。它可以以两种方式使用。如果将其用作：

1个	`SELECT` `STRAIGHT_JOIN ...`

这将意味着表应该按照查询中出现的确切顺序进行连接。因此，对于以下类型的查询：

1个	`SELECT` `STRAIGHT_JOIN *` `FROM` `tab1` `JOIN` `tab2` `ON` `tab1.a = tab2.a` `JOIN` `tab3` `ON` `tab2.b = tab3.b;`

我们可以确定表将按以下顺序连接：

tab1，tab2，tab3

还可以通过以下方式在查询中使用STRAIGHT_JOIN代替JOIN：

1个	`SELECT` `*` `FROM` `tab1` `JOIN` `tab2` `ON` `tab1.a = tab2.a STRAIGHT_JOIN tab3` `ON` `tab2.b = tab3.b;`

这迫使tab3完全按此顺序连接到tab2。优化器具有以下组合可供选择：

tab1，tab2，tab3
tab2，tab3，tab1

请注意，我们在这里仅谈论JOIN。这是因为使用LEFT或RIGHT JOIN已经确定了如何联接表-STRAIGHT_JOIN在这里不会产生任何作用。

好的，让我们回到查询中。我们将按照“ film_actor”，“ film”的顺序强制加入-与查询中出现的表完全相同。

1个

18岁

mysql> EXPLAIN SELECT STRAIGHT_JOIN actor_id, title FROM film_actor AS fa JOIN film AS f ON fa.film_id = f.film_id ORDER BY fa.actor_idG

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: fa

type: index

possible_keys: idx_fk_film_id

key: PRIMARY

key_len: 4

ref: NULL

rows: 5462

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: f

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 2

ref: sakila.fa.film_id

rows: 1

Extra: NULL

2 rows in set (0.00 sec)

不出所料，“ film_actor”被用作联接中的第一个表-这使优化器可以使用主键对结果集进行排序。我们设法消除了文件排序和临时表。实际上，新查询要比原始查询快一点。以下是Mark Leith创建的“ sys”模式中的数据示例。

1个

mysql> select query, max_latency, avg_latency, rows_examined_avg from statement_analysis WHERE db='sakila' AND last_seen > (NOW() - INTERVAL 10 MINUTE) AND query like 'SELECT%'G

*************************** 1. row ***************************

query: SELECT `actor_id` , `title` FR ... d` ORDER BY `fa` . `actor_id`

max_latency: 22.10 ms

avg_latency: 16.25 ms

rows_examined_avg: 17386

*************************** 2. row ***************************

query: SELECT STRAIGHT_JOIN `actor_id ... d` ORDER BY `fa` . `actor_id`

max_latency: 16.75 ms

avg_latency: 15.10 ms

rows_examined_avg: 10924

2 rows in set (0.00 sec)

如您所见，我们扫描的行更少，延迟也似乎更好。

可切换的优化

随着时间的流逝，越来越多的优化被添加到MySQL优化器中。其中一些可以由用户控制-当我们发现它们不适合我们的查询组合时，可以将其关闭。我们将不涉及每个优化的细节，我们将专注于我们可以控制的内容和方法。

您可以通过运行以下命令列出当前会话的整套可切换优化及其状态：

1个

mysql> SELECT @@optimizer_switchG

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

1 row in set (0.00 sec)

该列表取决于所使用的MySQL版本，以上内容取自MySQL 5.6。MariaDB在这一点上也不同于Oracle MySQL。

可以在全局级别禁用每个优化。例如，要禁用index_merge优化，我们可以运行：

1个	`mysql>` `SET` `GLOBAL` `optimizer_switch="index_merge=off";`

要在会话级别进行更改，我们可以运行：

1个	`mysql>` `SET` `SESSION optimizer_switch="index_merge=off";`

让我们看看它是如何工作的。我们对'sakila'模式执行了以下查询。它产生一个涉及索引合并优化的查询执行计划，确切地说是联合索引合并。

1个

mysql> EXPLAIN SELECT * FROM film_actor WHERE actor_id = 4 OR film_id = 7G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: film_actor

type: index_merge

possible_keys: PRIMARY,idx_fk_film_id

key: PRIMARY,idx_fk_film_id

key_len: 2,2

ref: NULL

rows: 27

Extra: Using union(PRIMARY,idx_fk_film_id); Using where

1 row in set (0.00 sec)

这意味着MySQL将使用两个索引（PRIMARY和idx_fk_film_id）来执行查找。假设我们不希望在此使用这种特定的优化，因为我们知道有更好的执行计划。

我们可以在全局级别上禁用此索引合并，但如果有其他查询可以从中受益，则可能不是最好的主意。我们也可以在会话级别上执行此操作，仅针对此特定查询。

1个

mysql> SET SESSION optimizer_switch="index_merge=off"; EXPLAIN SELECT * FROM film_actor WHERE actor_id = 4 OR film_id = 7G SET SESSION optimizer_switch="index_merge=on";

Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: film_actor

type: ALL

possible_keys: PRIMARY,idx_fk_film_id

key: NULL

key_len: NULL

ref: NULL

rows: 5462

Extra: Using where

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

更改优化程序标志通常是最后一道防线-在大多数情况下，迫使优化程序使用特定索引（或阻止其使用）足以改变查询执行计划。知道该选项仍然很高兴，因为它有时会有用。

我们在这里结束有关性能的章节-我们涵盖了从数据库配置到查询调整过程，工作负载分析，索引和EXPLAIN的基础知识。我们计划在将来涵盖更多内容，但从DBA系列的下一篇博客文章开始，我们将继续进行故障排除-您如何找出数据库的问题所在？为什么无法正常启动？如果遇到暂时的性能问题该怎么办？如果您正在运行集群，为什么节点不加入集群。