MySQL查询优化的三个技巧

MySQLSE

发布于 2021-12-27 08:40:56

4940

发布于 2021-12-27 08:40:56

文章被收录于专栏：MySQL解决方案工程师

作者：David Stokes

译者：徐轶韬

MySQL 查询优化在通常情况下是非常简单的工程。但是，当读者在网站上寻找如何优化查询的信息时，会发现一些深奥难懂的信息，就像一些哈利波特式的咒语。因此，在这里介绍一些简单的技巧，读者可以利用这些技巧获得更好的查询。

一 - MySQL 查询优化器在每次查询出现时执行优化

每当服务器看到用户的查询时，查询优化器都会将其视为第一次看到这个新查询！并且即使同时运行大量完全相同的查询，优化器也想对其进行优化！其他数据库（如 Oracle）允许锁定查询计划，但 MySQL 每次都会进行完整的优化处理。

解决这个问题时，用户可以使用优化器提示来强制减少这种情况。例如，如果用户从经验中知道将表 b 连接到表 a 比其他方式更好，则可以放置一个带有优化器提示的指令来跳过优化过程的那部分。优化器提示基于每个查询或每个语句工作，因此不会影响另一个查询的性能。

二 - 顺序

操作的顺序是每个初学者都会学习的东西，因为了解计算机如何评估操作很重要。MySQL 在改进查询时还需要注意许多依赖顺序的问题。

假设用户有关于商品成本和运输成本的函数索引。您的客户对您销售的产品和这些产品的运输成本非常敏感。为了提高性能，您创建了产品成本和运输成本总和的函数索引。

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

使用 EXPLAIN 检查示例查询，查询计划显示查询确实使用了新索引。

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获取数据的实际查询，以及关于查询如何运行的详细信息。传统的输出提供了一些非常好的细节。

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 索引。优化器将在扫描该索引后返回两行数据。除此之外，还有一些此时用户可能不感兴趣细节信息。

TREE 格式提供了信息的不同内容。

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 子句的过滤器。

但如果您渴望获得更为详细的信息并希望更详细地了解优化器如何处理您的查询，该怎么办？还可以使用 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)

现在我们可以获得读取成本、评估等更多信息。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-12-24，如有侵权请联系 cloudcommunity@tencent.com 删除

对象存储