Logo

菜单

发布于 2026-01-05 4 阅读
0

优化您的 MySQL 查询

优化您的 MySQL 查询

如果你从事商业编程，那么你很可能已经接触过 SQL 数据库。由于 SQL 数据库易于存储和检索数据，并且有许多免费和开源的选项可供选择，因此它们已成为编程中不可或缺的一部分。然而，人们仍然难以找到正确优化查询的方法，所以让我们来解决这个问题。

虽然本文主要针对 MySQL，但其中大部分内容也适用于其他数据库。因此，即使您不使用 MySQL，也能从中学习到一些对您目前使用的 SQL 数据库有所帮助的新知识。

这里我们将使用MySQL 自带的员工数据库。这个仓库里有一个 docker-compose 配置，指导你如何启动和运行数据库，你可以跟着步骤运行命令。

请给我解释一下

要优化关系数据库中的查询，首先要学习的是使用 `getQuery()`EXPLAIN命令。EXPLAIN该命令会将你创建的查询传递给数据库引擎，检查执行该命令所需的资源。它不会实际执行查询，而是检查它认为需要访问多少行才能满足查询要求。

鉴于下表：

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);
Enter fullscreen mode Exit fullscreen mode

以及一个查找所有名为以下员工的查询Joe：

SELECT * FROM employees WHERE first_name = "Joe";
Enter fullscreen mode Exit fullscreen mode

EXPLAIN你之前运行了一个查询，所以EXPLAIN对之前的查询进行处理应该是：

EXPLAIN SELECT * FROM employees WHERE first_name = "Joe"\G
Enter fullscreen mode Exit fullscreen mode

这里\G是 MySQL 特有的，表示您希望数据按行打印。以下是打印结果：

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Joe"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299645
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

所以当我们询问数据库对这个查询的看法时，它的回答有点苛刻。它说需要查找 299645 行（这张表有 300024 条记录，几乎是每一行）才能找到没有名为“Employees”的员工Joe。这里没有可以使用任何键（这里的键指的是数据库索引），而且我们还使用了WHERE子句来过滤结果。

所以，我们知道需要根据名字查找用户，但EXPLAIN响应显示该特定列上没有索引，让我们添加一个：

CREATE INDEX employees_first_name_idx ON employees (first_name);
Enter fullscreen mode Exit fullscreen mode

现在EXPLAIN又可以运行了：

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Joe"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: employees_first_name_idx
          key: employees_first_name_idx
      key_len: 58
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

只检查了一行！

这是怎么发生的？

当你请求数据库在某一列上创建索引时，它会创建一个优化的结构，使你能够快速找到与特定值关联的所有行。你可以把它想象成将一个值（“Joe”）映射到你创建索引的列中所有具有该值的行。

索引是神奇的解决方案。我们应该为表中的每一列都创建索引，这样就万事大吉了，对吧？

“你应该为查询表中用到的所有列建立索引。”

这是对关系型数据库中索引工作原理的一个相当常见的误解。如果每个列都创建了索引，那么每个查询都会自动优化，因为数据库可以利用所有这些索引来查找行，但事实并非如此。

在某些情况下，MySQL 在查询数据时可以使用多个索引。如果我们分别在 `user`first_name和 `user`上创建索引，last_name并尝试查找同时具有这两个值的特定用户，则会得到以下结果：

mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Halloran' AND first_name = 'Aleksander'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: index_merge
possible_keys: employees_first_name_idx,employees_last_name_idx
          key: employees_last_name_idx,employees_first_name_idx
      key_len: 66,58
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using intersect(employees_last_name_idx,employees_first_name_idx); Using where
Enter fullscreen mode Exit fullscreen mode

所以，目前为止一切顺利，只检查了一行，但这个额外的字段有一个有趣的引用intersect(employees_last_name_idx,employees_first_name_idx)。MySQL 在这里发现了两个索引，并决定同时使用它们来进行查询。这仍然比没有索引要好，但我们现在需要访问两个不同的数据结构来查找值，而不是一个。

last_name如果我们现在分别对和进行索引first_name，则输出结果如下：

mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Halloran' AND first_name = 'Aleksander'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: employees_first_name_idx,employees_last_name_idx,employees_last_first_name_idx
          key: employees_last_first_name_idx
      key_len: 124
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

服务器只需在单个索引中查找与预期值匹配的行，而不是两个。索引中包含多行也有助于覆盖索引，我们稍后会讨论这一特性。

选择索引列

选择索引取决于你如何查询表以及查询中包含哪些字段。例如，在查找员工时，我们希望按姓名查找，因此我们在姓名和名字上都创建了索引。为多个列创建索引时，列的顺序很重要。

假设我们有一位名为 的员工Georgi Facello，我们的索引会将其引用在条目 下Facello-Georgi（索引为last_name, first_name），因此它只适用于查找last_name和first_name或last_name单独查找 的查询。仅查找 的查询first_name无法使用此索引，因为该索引是从左到右匹配的。为此，您需要一个以 开头的索引first_name。

对于需要根据某个字段查找某人的查询last_name ，这种方法也不适用first_name，因为你需要将这两个字段都放在所用索引的最左侧。在这种情况下，为这两个字段分别创建索引会很有帮助。MySQL 会使用两个索引执行联合查询。

如果要查询索引中的所有或大部分列，它们的顺序应该是什么？

应该优先处理值差异最大的列。您可以使用类似以下的查询快速计算列中值出现频率的平均值：

mysql> SELECT COUNT(DISTINCT emp_no)/COUNT(*) FROM employees;
+---------------------------------+
| COUNT(DISTINCT emp_no)/COUNT(*) |
+---------------------------------+
|             1.0000              |
+---------------------------------+
Enter fullscreen mode Exit fullscreen mode

主键就是一个完美的例子。每一行都有一个唯一的值，所以我们得到1。现在我们希望索引中使用的列的值尽可能接近1，让我们来看一下first_name：

mysql> SELECT COUNT(DISTINCT first_name)/COUNT(*) FROM employees;
+-------------------------------------+
| COUNT(DISTINCT first_name)/COUNT(*) |
+-------------------------------------+
|               0.0042 |
+-------------------------------------+
Enter fullscreen mode Exit fullscreen mode

然后查看last_name：

mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) FROM employees;
+------------------------------------+
| COUNT(DISTINCT last_name)/COUNT(*) |
+------------------------------------+
|               0.0055               |
+------------------------------------+
Enter fullscreen mode Exit fullscreen mode

因此，last_name它比其他列的过滤效果更好first_name，是索引左侧的最佳列。但是，这只是平均值，正如您可能在其他地方遇到的困难一样，平均值很容易掩盖异常值。这里的异常值会直接影响查询性能，因此我们还需要检查这些列中是否存在明显的异常值：

mysql> SELECT last_name, COUNT(*) AS total FROM employees GROUP BY last_name ORDER BY total DESC LIMIT 10;
+-----------+-------+
| last_name | total |
+-----------+-------+
| Baba      |  226  |
| Gelosh    |  223  |
| Coorg     |  223  |
| Sudbeck   |  222  |
| Farris    |  222  |
| Adachi    |  221  |
| Osgood    |  220  |
| Mandell   |  218  |
| Neiman    |  218  |
| Masada    |  218  |
+-----------+-------+
Enter fullscreen mode Exit fullscreen mode

所以这里没有异常值。数值看起来非常接近。我们来看first_name：

mysql> SELECT first_name, COUNT(*) AS total FROM employees GROUP BY first_name ORDER BY total DESC LIMIT 10;
+-------------+-------+
| first_name | total |
+-------------+-------+
| Shahab      |  295 |
| Tetsushi    |  291 |
| Elgin       |  279 |
| Anyuan      |  278 |
| Huican      |  276 |
| Make        |  275 |
| Sreekrishna |  272 |
| Panayotis   |  272 |
| Hatem       |  271 |
| Giri        |  270 |
+-------------+-------+
Enter fullscreen mode Exit fullscreen mode

还不错。数值之间相差不大。我们最终确定的索引顺序相当合理。

现在，创建索引时还需要考虑如何对结果进行排序？

就像数据库使用索引快速查找行一样，如果按索引顺序对相同的列进行排序，它也可以对结果进行排序。对于我们的last_name, first_name索引，这意味着要么全部排序，要么ORDER BY last_name全部排序ORDER BY last_name, first_name。如果对多个列进行排序，它们必须都按同一方向排序，即要么全部排序ASC，要么全部排序DESC。如果它们的顺序不一致，数据库就无法使用索引本身对结果进行排序，而必须使用临时表来加载结果并进行排序。

覆盖指数

创建包含多列的索引的另一个重要原因是 MySQL 提供的覆盖索引功能。当查询仅加载主键和索引中的列时，数据库甚至无需访问实际的表即可读取结果。它仅从索引中读取所有内容。如下所示：

mysql> explain select emp_no, last_name, first_name from employees where last_name = "Baba"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: employees_last_name_idx,employees_last_first_name_idx
          key: employees_last_first_name_idx
      key_len: 66
          ref: const
         rows: 226
     filtered: 100.00
        Extra: Using index
Enter fullscreen mode Exit fullscreen mode

这里的提示是Extra: Using index，这意味着所有数据都仅从索引中读取。由于索引已经包含了我们需要的所有信息（所有索引都包含表的主键），数据库会直接从索引加载所有数据并返回，而无需访问表本身。这对于查询来说是最佳情况，尤其是在索引可以完全加载到内存中的情况下。

创建多个索引

创建索引并非没有代价。虽然索引可以加快数据查找速度，但也会减慢对表的任何更改，因为写入带有索引的列会导致这些索引更新。因此，必须在尽可能提高查询速度和保证命令执行速度之间找到平衡INSERT/UPDATE/DELETE。

概括

因此，在进行优化时，请记住：

  • 用于EXPLAIN了解数据库认为您的查询将如何执行 -请在此处查看命令文档；
  • 创建索引，以涵盖您希望对查询执行的筛选和排序操作；
  • 创建索引时，评估列的最佳顺序；
  • 尽量从涵盖索引中加载尽可能多的信息；

优化 MySQL 数据库的最佳参考资料之一是《高性能 MySQL》（High Performance MySQL），该书已出到第四版，内容涵盖从数据库内部机制到如何设计数据库模式以最大程度地发挥 MySQL 性能的方方面面。如果您正在使用 MySQL 开发应用程序，那么您应该阅读这本书。如果您不使用 MySQL，很可能也有类似的书籍专门介绍 MySQL，您也应该花些时间阅读一下。

文章来源：https://dev.to/mauriciolinhares/optimizing-your-mysql-queries-437i
使用 Python 实现一切自动化...
使用 React.js 和 Nod...