MySQL数据库

优化查询的技巧

1. 使用 BENCHMARK 比较函数性能

BENCHMARK 函数可以用于比较 MySQL 函数或操作符的性能。例如：

SELECT BENCHMARK(100000000, CONCAT('a', 'b'));

示例输出：

+---------------------------------------+
| BENCHMARK(100000000, CONCAT('a','b')) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (21.30 sec)

注意：

• BENCHMARK 无法用于比较查询语句。例如：

  SELECT BENCHMARK(100, SELECT `id` FROM `lines`);
  

  会导致语法错误。

• 运行时间少于 5-10 秒的基准测试通常无意义，因为 MySQL 解析查询和系统负载可能导致误差。

2. 使用 EXPLAIN 分析查询

EXPLAIN 是优化查询性能的核心工具。它会提供有关查询的执行计划，包括表的连接方式和顺序等信息。

使用 EXPLAIN 的重要性：

• 理解查询的执行方式。
• 确定需要优化的部分（如添加索引）。

3. 示例：逐步优化查询

场景：对两个没有索引的表进行连接查询。

查询语句：

EXPLAIN SELECT * FROM a LEFT JOIN b USING (i) WHERE a.i < 2;

输出：

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

分析：

• a 表：全表扫描（ALL），没有使用索引。
• b 表：全表扫描（ALL），没有使用索引。

4. 添加索引逐步优化查询

步骤 1：为 b 表添加索引

ALTER TABLE b ADD KEY(i);

再次运行查询：

EXPLAIN SELECT * FROM a LEFT JOIN b USING (i) WHERE a.i < 2;

输出：

+----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref      | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL     |    4 | Using where |
|  1 | SIMPLE      | b     | ref  | i             | i    | 5       | test.a.i |    2 |             |
+----+-------------+-------+------+---------------+------+---------+----------+------+-------------+

改进：

• 对 b 表的访问由全表扫描（ALL）变为引用（ref）。
• 只需扫描 2 行而非 3 行。

步骤 2：为 a 表添加索引

ALTER TABLE a ADD KEY(i);

再次运行查询：

EXPLAIN SELECT * FROM a LEFT JOIN b USING (i) WHERE a.i < 2;

输出：

+----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref      | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
|  1 | SIMPLE      | a     | range | i             | i    | 5       | NULL     |    1 | Using where |
|  1 | SIMPLE      | b     | ref   | i             | i    | 5       | test.a.i |    2 |             |
+----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+

改进：

• 对 a 表的访问由全表扫描（ALL）变为范围扫描（range）。
• 查询效率显著提高。

总结

1. 使用 BENCHMARK 比较函数性能，但不要用于短时查询的基准测试。
2. 使用 EXPLAIN 分析查询计划并确定优化点。
3. 通过添加索引，逐步减少全表扫描，提高查询性能。
4. 查询优化的核心：减少扫描行数，使用适当的索引和连接方式。