跳到主要内容

使用索引优化 MySQL 查询

第一部分：优化 MySQL 查询和索引​

索引在数据库中至关重要，可以快速找到所需的数据。没有适当的索引，检索数据可能会花费很长时间，尤其是随着数据量的增长。你需要逐页查找，这会导致更高的成本和更多资源的使用。
为了解决这个问题，分析数据库的使用情况并了解最常运行的查询，可以帮助识别并使用索引优化 MySQL 查询。查询执行计划等工具也能提供帮助。基于这些分析创建合适的索引，可以减少查询响应时间，提升数据库性能。

  • 使用索引优化 MySQL 查询
  • MySQL 查询优化技术
  • 总结

使用索引优化 MySQL 查询​

考虑以下示例：

CREATE TABLE employee (
 employee_number char(10) NOT NULL,
 firstname varchar(40),
 surname varchar(40),
 address text,
 tel_no varchar(25),
 salary int(11),
 overtime_rate int(10) NOT NULL
);

要查找员工 Fred Jone 的工资（员工编号 101832），你运行： SELECT salary FROM employee WHERE employee_number = '101832';
MySQL 不知道该记录在哪里。它甚至不知道如果找到一个匹配项，就不会有另一个匹配项，因此必须扫描整个表，可能有数千条记录，才能找到 Fred 的详细信息。
索引是一个单独的文件，已排序且只包含你感兴趣的字段。如果你在 employee_number 上创建索引，MySQL 可以非常快速地找到对应记录（索引的工作方式与书籍中的索引非常相似。想象一下翻阅一本技术书籍（或者更常见的是一堆杂乱的笔记！）寻找“优化 MySQL”这个主题。索引为你节省了大量时间！）
在我们调整上述表结构之前，让我告诉你一个对任何认真优化查询的人来说非常重要的小秘密：
EXPLAIN 是 MySQL 中的关键字，可以帮助你找出问题所在。
EXPLAIN 会显示（解释）你的查询是如何被执行的。将它放在 SELECT 语句前面，可以查看索引是否被正确使用，以及执行了哪种类型的连接。
例如：
EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number = '10875';

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

这些字段都代表什么？

  • table 显示输出涉及的表（当查询中连接多个表时有用）
  • type 是一个重要字段——它告诉我们使用了哪种连接类型。按优劣排序，类型依次为：system、const、eq_ref、ref、range、index、all
  • possible_keys 显示该表可能使用的索引
  • key 实际使用的索引
  • key_len 使用的索引长度，越短越好
  • ref 告诉我们使用的是哪一列或常量
  • rows MySQL 估计需要检查的行数
  • extra 额外信息——这里不希望看到的是 "using temporary" 和 "using filesort"

看起来我们的查询非常糟糕，是最差的情况！没有可用的索引，所以 MySQL 必须扫描所有记录（本例中只有 2 条，但想象一个有数百万条记录的大表）。
现在，我们添加之前提到的索引。
如果重新运行 EXPLAIN，结果是：

+----------+-------+---------------+---------+---------+-------+------+-------+
| table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----------+-------+---------------+---------+---------+-------+------+-------+
| employee | const | PRIMARY       | PRIMARY | 10      | const | 1    |       |
+----------+-------+---------------+---------+---------+-------+------+-------+

上述查询表现良好（几乎达到了“无法更好”的级别）。连接类型（在这个简单查询中不是真正的连接）是 "const"，意味着表中只有一条匹配行。使用了主键来查找这条特定记录，MySQL 估计需要检查的行数是 1。所有这些意味着 MySQL 在你阅读这段解释的时间内，几乎可以执行成千上万次该查询。

MySQL 查询优化技术​

  • 优化 MySQL 索引用于查询计算
  • 优化 MySQL 查询以实现最大速度

总结​

需要记住的是，不同的索引服务于不同的目的。根据具体使用场景选择正确的索引类型至关重要。过度索引也会降低性能，因为每次数据变更时都需要更新所有索引。
通过索引优化 MySQL 查询，或修复定义不当或缺失的索引，可以显著提升数据库性能，使应用程序更快、更响应。这是数据库管理和优化中的关键环节，正确执行时能显著改善数据检索时间和整体系统效率。