原文地址：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 等于多少就要重新计算了，与之类似的问题还有 当索引列上 使用了 函数、运算、表达式、隐式转换 等操作都可能导致这个查询列使用不上索引而变成全表扫描。
第三点 提供了多种查看执行计划信息的选项，以提供执行计划信息不同的详细程度，大家可以根据自己需要来选择使用哪个。