[译] MySQL查询调优：top 3 技巧

原文地址：https://blogs.oracle.com/mysql/post/mysql-query-optimization-top-3-tips
原文作者：David Stokes 

MySQL 查询优化一般是一个简单工程，但是去查找关于怎么优化查询的信息时，在很多网站都是像哈利·波特咒语一样被对待。不过这里就有一些你需要知道的简单技巧以使你的查询最优化。

技巧一 ：MySQL查询优化器在每次查询出现时都想对它进行优化

每次你的查询被MySQL服务接收时，查询优化器都是像第一次那样从未见过，当做全新的问题一样的对待，想要去优化这个查询，即使已经有10打完全一样的查询同时都正在运行了。像其他的数据库（比如Oracle）就允许查询执行计划被锁定，但是MySQL则每次都一样的对待。

这个问题没有很好的办法除非你使用优化器提示（hints)去强制降低影响。例如你从经验上知道连接时b表驱动a表性能比其他方式更好，那你可以放个优化器提示指令去跳过其他优化进程部分步骤。优化器提示只作用于一个查询或一个语句，因此是不会对其他查询性能产生影响的。

技巧二 ：计算顺序

运算的顺序是每个初学者都要学习的东西，因为这对于理解计算机运算操作是非常重要的。在处理优化查询时注意 MySQL 也有很多顺序依赖的问题。
让我们来假定在计算商品的成本和运算成本上你有一个函数索引。你的客户对销售的产品成本和产品的运输成本都非常敏感，为了提升性能，你创建了一个函数索引在计算 产品成本和运输成本上。
如下函数索引：

CREATE index cost_and_shipping ON products((cost + shipping));

在如下样例查询上使用EXPLAIN 可以看到查询计划显示新索引确实用到了去搜索成本小于5的产品。

EXPLAIN FORMAT=TREE SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE cost + shipping < 5\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((cost + shipping) < 5)  (cost=1.16 rows=2)
    -> Index range scan on PRODUCTS using cost_and_shipping  (cost=1.16 rows=2)
1 row in set (0.0008 sec)

从上面例子来看函数索引是正常工作的，但是你同事尝试使用这个神奇的索引时，他们说性能鲜有提升。最后他们共享了他们的查询和EXPLAIN 报告（显示这个神奇的索引没有被使用到），他们查询不得不执行一个更慢的全表扫描，这发生了什么？？

EXPLAIN FORMAT=TREE SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE shipping + cost < 5\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((products.shipping + products.cost) < 5)  (cost=0.65 rows=4)
    -> Table scan on PRODUCTS  (cost=0.65 rows=4)
1 row in set (0.0016 sec)

你注意到问题了吗?
如果没有，这就有点微妙了。这个索引是以 (cost + shipping)这个格式创建的，你的查询使用的也是(cost + shipping) 这个格式，但他们的查询使用的是(shipping + cost) 这个格式。在这个情况下，优化器是意识不到 (cost + shipping) 和 (shipping + cost)是运算等价的。这个是非常容易颠倒这两列顺序的，从而不知不觉地陷入到了性能困境。为了从函数索引中获得期望的性能，就必须使用正确的组件（比如列）顺序。

技巧三： 新的EXPLAIN 格式

这个EXPLAIN新的变体提供了惊人的新细节。EXPLAIN 命令被习惯用于看查询计划，系统将运行以获取数据的实际查询，以及关于查询如何运行的详细信息。传统的输出提供了一些非常好的细节。

EXPLAIN FORMAT=TRADITIONAL SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE cost + shipping  < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: PRODUCTS
   partitions: NULL
         type: range
possible_keys: cost_and_shipping
          key: cost_and_shipping
      key_len: 9
          ref: NULL
         rows: 2
     filtered: 100
        Extra: Using where
1 row in set, 1 warning (0.0008 sec)
Note (code 1003): /* select#1 */ select `demo`.`products`.`id` AS `id`,`demo`.`products`.`name` AS `name`,`demo`.`products`.`cost` AS `cost`,`demo`.`products`.`shipping` AS `shipping`,(`demo`.`products`.`cost` + `demo`.`products`.`shipping`) AS `(cost + shipping)` from `demo`.`products` where ((`cost` + `shipping`) < 5)

这里优化器再次意识到可以利用cost_and_shipping索引,优化器通过扫描这个索引将返回两行数据，这还有一些其他的我们现在可能不感兴趣的细节。
树格式对信息提供了一个不同的视觉。

EXPLAIN FORMAT=TREE SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE cost + shipping  < 5\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((cost + shipping) < 5)  (cost=1.16 rows=2)
    -> Index range scan on PRODUCTS using cost_and_shipping  (cost=1.16 rows=2)
1 row in set (0.0008 sec)

在树（TREE）格式中添加的内容使我们得到了一个更容易的阅读展示，我们还可以看到查询的成本。它还明确提示了我们where条件查询中使用了 filter。
对于优化器如何对待你的查询，如果你真的渴望细节，想要一个更细粒度的视角。那么，这里有个JSON格式的，你可以看看。

EXPLAIN FORMAT=JSON SELECT id, name, cost, shipping, (cost + shipping) FROM PRODUCTS 
WHERE cost + shipping  < 5\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.16"
    },
    "table": {
      "table_name": "PRODUCTS",
      "access_type": "range",
      "possible_keys": [
        "cost_and_shipping"
      ],
      "key": "cost_and_shipping",
      "used_key_parts": [
        "(`cost` + `shipping`)"
      ],
      "key_length": "9",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.96",
        "eval_cost": "0.20",
        "prefix_cost": "1.16",
        "data_read_per_join": "208"
      },
      "used_columns": [
        "id",
        "cost",
        "shipping",
        "name",
        "(`cost` + `shipping`)"
      ],
      "attached_condition": "((`cost` + `shipping`) < 5)"
    }
  }
}
1 row in set, 1 warning (0.0023 sec)
Note (code 1003): /* select#1 */ select `demo`.`products`.`id` AS `id`,`demo`.`products`.`name` AS `name`,`demo`.`products`.`cost` AS `cost`,`demo`.`products`.`shipping` AS `shipping`,(`demo`.`products`.`cost` + `demo`.`products`.`shipping`) AS `(cost + shipping)` from `demo`.`products` where ((`cost` + `shipping`) < 5)

现在我们有了阅读的成本，评估的成本等等更多的信息了。

译者备注：

第一点 讲到了MySQL和其他数据库（比如Oracle）一个非常重要的不同点：MySQL没有硬解析、软解析的概念，MySQL每执行一条SQL（哪怕完全一样的SQL多次执行）时都要进行语法检查、语义检查、查询转换、生成执行树（执行计划）、进行执行等完全的解析执行步奏，而不会像Oracle那样将生成的执行计划保存到SGA里，后面再执行时可以拿来直接使用，以避免生成执行计划这些非常耗费资源(CPU、时间）的步骤。从这点也可以理解为什么MySQL的开发规范里都说要避免复杂SQL，因为复杂SQL生成执行计划的过程都是很复杂、很耗时的，而MySQL每次执行都要经过这个耗时的过程，那么SQL的执行效率自然不会高，整个数据库的性能都可能受到影响。
第二点 讲到了优化器处理一些复杂点运算的注意事项，优化器整体是非常聪明的（大部分情况可以从众多执行路径中找到一个最优或较优的执行路径），但有时候你发现它又是非常笨的，比如文中举例的，创建了函数索引，查询时列的顺序换了下就不认识了，相当于记住了1 + 2 = 3,但你问他 2 + 1 等于多少就要重新计算了，与之类似的问题还有 当索引列上 使用了 函数、运算、表达式、隐式转换 等操作都可能导致这个查询列使用不上索引而变成全表扫描。
第三点 提供了多种查看执行计划信息的选项，以提供执行计划信息不同的详细程度，大家可以根据自己需要来选择使用哪个。