MySQL中的using where using index详解|极客笔记

MySQL中的using where using index详解

介绍

在MySQL中，查询语句的效率对于数据库的性能至关重要。优化查询语句是提高数据库性能的重要手段之一。在优化查询语句时，了解MySQL的执行计划对于理解查询语句的性能和效率有很大帮助。

其中，using where和using index是MySQL执行计划中的两个重要关键词。本文将详细解释这两个关键词的含义以及在查询优化过程中的作用。

using where

首先，我们来看一下using where的含义。在MySQL的执行计划中，using where表示查询中存在一个过滤条件，MySQL需要使用这个过滤条件来筛选数据。这个过滤条件可以是一个WHERE子句、JOIN条件或者子查询。

以下是一个示例查询语句：

SELECT * FROM employees WHERE age > 30;

在执行这个查询语句时，MySQL会在执行计划中显示using where。这是因为查询语句中存在一个过滤条件age > 30，MySQL需要使用这个过滤条件来筛选满足条件的数据。

using index

接下来，我们来看一下using index的含义。在MySQL的执行计划中，using index表示查询中使用了一个索引来加速查询。当MySQL决定使用一个索引时，它不仅仅为了找到满足查询条件的数据，还可以避免访问表中的所有数据。

以下是一个示例查询语句：

SELECT * FROM employees WHERE age > 30;

假设employees表有一个对age字段的索引，那么在执行这个查询语句时，MySQL会在执行计划中显示using index。这是因为MySQL决定使用age字段的索引来加速查询，避免访问表中所有的数据。

组合使用

在一些情况下，MySQL在执行计划中可能会同时显示using where和using index。这表示查询语句中既存在过滤条件，又使用了一个索引来加速查询。

以下是一个示例查询语句：

SELECT * FROM employees WHERE age > 30 ORDER BY last_name;

假设employees表有一个对age字段的索引，以及一个对last_name字段的索引，那么在执行这个查询语句时，MySQL会在执行计划中显示using where using index。

示例代码

为了更好地理解using where using index的作用，我们来看一个示例代码。以下是一个employees表的定义：

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT,
  salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, age, salary)
VALUES ('John', 'Doe', 25, 5000), ('Jane', 'Smith', 35, 8000), ('Mike', 'Johnson', 40, 10000);

现在，我们进行一次查询：

EXPLAIN SELECT * FROM employees WHERE age > 30 ORDER BY last_name;

执行以上查询，并查看执行计划：

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+

从上述执行计划中可以看出，MySQL没有选择使用索引来加速查询，并且还进行了全表扫描。

为了让MySQL使用索引，我们需要创建一个对age字段的索引：

ALTER TABLE employees ADD INDEX age_index (age);

然后，我们再次执行查询，并查看执行计划：

+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | range | age_index     | age_index  | 5       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+

从新的执行计划中可以看到，MySQL选择使用了索引来加速查询，同时也利用了过滤条件age > 30。

总结

在MySQL中，using where和using index是执行计划中的两个关键词，用于表示查询语句中的过滤条件和使用了索引来加速查询。using where表示查询中存在一个过滤条件，using index表示查询中使用了一个索引。在查询优化过程中，了解这两个关键词的含义对于优化查询语句的性能和效率非常有帮助。