MySQL查询优化的4种方法（非常详细）

查询是数据库中最频繁的操作，提高查询速度可以有效地提高 MySQL 数据库的性能。本节将为读者介绍优化查询的方法。

分析查询语句

通过对查询语句的分析，可以了解查询语句的执行情况，找出查询语句执行的瓶颈，从而优化查询语句。

MySQL 中提供了 EXPLAIN 语句和 DESCRIBE 语句，用来分析查询语句。

EXPLAIN 语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT select_options

使用 EXTENED 关键字，EXPLAIN 语句将产生附加信息。select_options 是 SELECT 语句的查询选项，包括 FROM WHERE 子句等。

执行该语句，可以分析 EXPLAIN 后面 SELECT 语句的执行情况，并且能够分析出所查询表的一些特征。

【实例】使用 EXPLAIN 语句来分析一个查询语句，可执行如下语句：

mysql> EXPLAIN SELECT * FROM fruits;
+----+------------+--------+------+-----------------+--------+---------+--------+-------+-------+
| id | select_type| table  | type | possible_keys   | key     | key_len | ref    | rows  | Extra |
+----+------------+--------+------+-----------------+--------+---------+--------+-------+-------+
|  1 | SIMPLE     | fruits | ALL  | NULL           | NULL   | NULL     | NULL   |   16  |       |
+----+------------+--------+------+-----------------+--------+---------+--------+-------+-------+
1 row in set (0.00 sec)

下面对查询结果进行解释：

• id：SELECT 识别符。这是 SELECT 的查询序列号。
• select_type：表示 SELECT 语句的类型。它可以是以下几种取值：
  • SIMPLE 表示简单查询，其中不包括连接查询和子查询；
  • PRIMARY 表示主查询，或者是最外层的查询语句；
  • UNION 表示连接查询的第2个或后面的查询语句；
• DEPENDENT UNION，连接查询中的第 2 个或后面的 SELECT 语句，取决于外面的查询；
• UNION RESULT，连接查询的结果；
• SUBQUERY，子查询中的第 1 个 SELECT 语句；
• DEPENDENT SUBQUERY，子查询中的第 1 个 SELECT，取决于外面的查询；
• DERIVED，导出表的 SELECT（FROM 子句的子查询）。
• table：表示查询的表。
• type：表示表的连接类型。

下面按照从最佳类型到最差类型的顺序给出各种连接类型。

1) system

该表是仅有一行的系统表。这是 const 连接类型的一个特例：

SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1AND primary_key_part2=2；

2) const

数据表最多只有一个匹配行，将在查询开始时被读取，并在余下的查询优化中作为常量对待。const 表查询速度很快，因为它们只读取一次。const 用于使用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分的场合。

在下面的查询中，tbl_name 可用于 const 表：

SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1AND primary_key_part2=2；

3) eq_ref

对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是 UNIQUE 或 PRIMARY KEY 时，即可使用这种类型。

eq_ref 可以用于使用“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。

在下面的例子中，MySQL 可以使用 eq_ref 连接来处理 ref_tables：

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

4) ref

对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于索引既不是 UNIQUE 也不是 PRIMARY KEY 的情况，或者查询中使用了索引列的左子集，即索引中左边的部分列组合。ref 可以用于使用 = 或 <=> 操作符带索引的列。

在下面的例子中，MySQL 可以使用 ref 连接来处理 ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

5) ref_or_null

该连接类型如同 ref，但是添加了 MySQL 可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该连接类型的优化。

在下面的例子中，MySQL 可以使用 ref_or_null 连接来处理 ref_tables：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

6) index_merge

该连接类型表示使用了索引合并优化方法。在这种情况下，key 列包含了使用的索引的清单，key_len 包含了使用索引的最长关键元素。

7) unique_subquery

该类型替换了下面形式的 IN 子查询的 ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery 是一个索引查找函数，可以完全替换子查询，效率更高。

8) index_subquery

该连接类型类似于 unique_subquery，可以替换 IN 子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

9) range

只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引。key_len 包含所使用索引的最长关键元素。

当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符用常量比较关键字列时，类型为 range。

下面介绍几种检索指定行情况：

SELECT * FROM tbl_name
WHERE key_column = 10;

SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);

10) index

该连接类型与 ALL 相同，除了只扫描索引树。它通常比 ALL 快，因为索引文件通常比数据文件小。

11) ALL

对于前面的表的任意行组合，进行完整的表扫描。如果表是第一个没标记 const 的表，这样不好，并且在其他情况下很差。通常可以增加更多的索引来避免使用 ALL 连接。

• possible_keys：指出 MySQL 能使用哪个索引在该表中找到行。如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 WHERE 子句看它是否引用某些列或适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能。
• key：表示查询实际使用到的索引，如果没有选择索引，该列的值是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。参见 SELECT 语法。
• key_len：表示 MySQL 选择的索引字段按字节计算的长度，如果键是 NULL，则长度为 NULL。注意，通过 key_len 值可以确定 MySQL 将实际使用一个多列索引中的几个字段。
• ref：表示使用哪个列或常数与索引一起来查询记录。
• rows：显示 MySQL 在表中进行查询时必须检查的行数。
• Extra：表示 MySQL 在处理查询时的详细信息。

DESCRIBE 语句的使用方法与 EXPLAIN 语句是一样的，并且分析结果也是一样的。DESCRIBE 语句的语法形式如下：

DESCRIBE SELECT select_options

DESCRIBE 可以缩写成 DESC。

索引对查询速度的影响

MySQL 中提高性能的一个有效方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且可加快查询的速度，因此，索引对查询的速度有着至关重要的影响。

使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度、提高数据库的性能。

如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。

【实例】下面是查询语句中不使用索引和使用索引的对比。首先，分析未使用索引时的查询情况，EXPLAIN 语句执行如下：

mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple';
+----+-------------+---------+------+---------------+--------+---------+------+------+--------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref  | rows | Extra        |
+----+-------------+---------+------+---------------+--------+---------+------+------+--------------+
|  1 | SIMPLE      | fruits  | ALL  | NULL          | NULL   | NULL    | NULL | 15   | Using where  |
+----+-------------+---------+------+---------------+--------+---------+------+------+--------------+
1 row in set (0.00 sec)

可以看到，rows 列的值是 15，说明“SELECT * FROM fruits WHERE f_name='apple';”这个查询语句扫描了表中的 15 条记录。

然后，在 fruits 表的 f_name 字段上加上索引。执行添加索引的语句及结果如下：

mysql> CREATE INDEX index_name ON fruits(f_name);
Query OK, 0 rows affected (0. 04 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在，再分析上面的查询语句。执行的 EXPLAIN 语句及结果如下：

mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple';
+----+-----------+------+----+-------------+----------+---------+-------+------+-------------+
| id |select_type|table |type|possible_keys|key       | key_len | ref   | rows | Extra       |
+----+-----------+------+----+-------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE    |fruits|ref |index_name   |index_name|255      |const  |1     | Using where |
+----+-----------+------+----+-------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

结果显示，rows 列的值为 1，表示这个查询语句只扫描了表中的一条记录，其查询速度自然比扫描 15 条记录快；而且 possible_keys 和 key 的值都是 index_name，说明查询时使用了 index_name 索引。

使用索引查询

索引可以提高查询的速度，但并不是使用带有索引的字段查询时索引都会起作用。

使用索引有几种特殊情况，在这些情况下使用带有索引的字段查询时，有可能索引并没有起到提高查询速度的作用。下面重点介绍这几种特殊情况。

1) 使用LIKE关键字的查询语句

在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。下面将举例说明。

【实例】查询语句中使用 LIKE 关键字，并且匹配的字符串中含有“%”字符，EXPLAIN 语句执行如下：

mysql> EXPLAIN SELECT * FROM fruits WHERE f_name like '%x';
+----+-----------+------+----+--------------+------+-------+----+-----+-------------+
| id |select_type|table |type|possible_keys | key  |key_len|ref | rows| Extra       |
+----+-----------+------+----+--------------+------+-------+----+-----+-------------+
|  1 | SIMPLE    |fruits|ALL |NULL          | NULL | NULL  |NULL| 16  | Using where |
+----+-----------+------+----+--------------+------+-------+----+-----+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name like 'x%';
+----+-------------+-------+------+--------------+----------+---------+-----+------+-------------+
| id | select_type | table | type | possible_keys| key      | key_len | ref | rows | Extra       |
+----+-------------+-------+------+--------------+----------+---------+-----+------+-------------+
|  1 | SIMPLE      | fruits| range| index_name   |index_name|150      |NULL | 4    | Using where |
+----+-------------+-------+------+--------------+----------+---------+-----+------+-------------+
1 row in set (0.00 sec)

已知 f_name 字段上有索引 index_name：

• 第 1 个查询语句执行后，rows 列的值为 16，表示这次查询过程中扫描了表中所有的 16 条记录；
• 第 2 个查询语句执行后，rows 列的值为 4，表示这次查询过程扫描了 4 条记录。第 1 个查询语句中的索引没有起作用，因为第 1 个查询语句中 LIKE 关键字后的字符串以“%”开头，而第 2 个查询语句使用了索引 index_name。

2) 使用多列索引的查询语句

MySQL 可以为多个字段创建索引。一个索引可以包括 16 个字段。对于多列索引，只有查询条件中使用了这些字段中的第 1 个字段时，索引才会被使用。

【实例】在表 fruits 中 f_id、f_price 字段上创建多列索引，验证多列索引的使用情况。

mysql> CREATE INDEX index_id_price ON fruits(f_id, f_price);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> EXPLAIN SELECT * FROM fruits WHERE f_id='l2';
+--+--------------+-------+------+---------------+-----+---------+------+------+------+
|id | select_type | table | type | possible_keys | key | key_len | ref  | rows | Extra|
+---+--------+--------+-------+------------------------+---------+----+-------+---+---+
| 1 | SIMPLE | fruits | const | PRIMARY,index_id_price | PRIMARY | 20 | const | 1 |   |
+---+--------+--------+-------+------------------------+---------+----+-------+---+---+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM fruits WHERE f_price=5.2;
+----+-------------+--------+------+---------------+------+---------+-----+------+------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref | rows | Extra      |
+----+-------------+--------+------+---------------+------+---------+-----+------+------------+
|  1 | SIMPLE      | fruits | ALL  | NULL         | NULL | NULL    | NULL|  16  | Using where|
+----+-------------+--------+------+---------------+------+---------+-----+------+------------+
1 row in set (0.00 sec)

从第 1 条语句查询结果可以看出，“f_id= 'l2'”的记录有 1 条。第 1 条语句共扫描了 1 条记录，并且使用了索引 index_id_price。

从第 2 条语句查询结果可以看出，rows 列的值是 16，说明查询语句共扫描了 16 条记录，并且 key 列值为 NULL，说明“SELECT * FROM fruits WHERE f_price=5.2;”语句并没有使用索引。因为 f_price 字段是多列索引的第 2 个字段，只有查询条件中使用了 f_id 字段，才会使 index_id_price 索引起作用。

3) 使用OR关键字的查询语句

查询语句的查询条件中只有 OR 关键字，且 OR 前后的两个条件中的列都是索引时，查询中才使用索引，否则查询将不使用索引。

【实例】查询语句使用 OR 关键字的情况：

mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or s_id=101 \G
*** 1. row ***
      id: 1
select_type: SIMPLE
   table: fruits
    type: ALL
possible_keys: index_name
     key: NULL
  key_len: NULL
     ref: NULL
    rows: 16
   Extra: Using where
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or f_id='l2' \G
*** 1. row ***
      id: 1
select_type: SIMPLE
   table: fruits
    type: index_merge
possible_keys: PRIMARY,index_name,index_id_price
     key: index_name,PRIMARY
  key_len: 510,20
     ref: NULL
    rows: 2
   Extra: Using union(index_name,PRIMARY); Using where
1 row in set (0.00 sec)

因为 s_id 字段上没有索引，所以第 1 条查询语句没有使用索引，总共查询了 16 条记录；第 2 条查询语句使用了 f_name 和 f_id 这两个索引，因为 id 字段和 name 字段上都有索引，所以查询的记录数为 2 条。

优化子查询

MySQL 从 4.1 版本开始支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的 SQL 操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。

因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在 MySQL 中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能会更好。连接之所以更有效率，是因为 MySQL 不需要在内存中创建临时表来完成查询工作。