mysql中explain的用法是什么

Introduction

MySQL is a popular open-source database management system that allows users to process and manage large amounts of data. One of the features that make MySQL a powerful tool is its ability to optimize queries using the query optimizer. The query optimizer is a software module that automatically selects the most efficient execution plan for a given query.

One of the ways to understand how the MySQL query optimizer works is by using the EXPLAIN command. EXPLAIN is a command that allows you to see the execution plan that MySQL uses for a specific query. It shows how the query uses indexes, how it retrieves data, and how it joins tables, among other things. In this article, we will look at how to use the EXPLAIN command and how it can help you optimize your MySQL queries.

Setting up the environment

Before we dive into the details on how to use the EXPLAIN command, let's first set up the environment. For this tutorial, we will be using a Windows 10 system, MySQL 8.0.22 version, and a Dell G3 computer. You should have MySQL already installed and running on your computer. If not, you can easily download and install it from the official MySQL website.

Syntax of the EXPLAIN command

The syntax of the EXPLAIN command is straightforward. It looks like this:

EXPLAIN SELECT columns FROM table WHERE conditions;

You simply add the EXPLAIN keyword before your SQL query. The result is a table that shows how MySQL processes your query.

Analyzing the EXPLAIN output

The EXPLAIN command produces a result set with ten columns. Let's go through each column and see what it represents:

1. id: The SELECT identifier. It shows the order in which MySQL executes the queries. If the id is the same, MySQL executes the queries from top to bottom. If it's a subquery, the id will increase, and the higher the id, the higher the priority.

2. select_type: It represents the type of the query. It can be SIMPLE, PRIMARY, UNION, DEPENDENTUNION, UNIONRESULT, SUBQUERY, DEPENDENTSUBQUERY, DERIVED, or UNCACHEABLESUBQUERY.

3. table: It shows the table that the query is looking at.

4. partitions: It displays the matching partitions.

5. type: It shows how MySQL accesses the table. It can be ALL, index, range, ref, eq_ref, const, system, or NULL. These types are arranged in increasing order of efficiency.

6. possible_keys: It displays the possible keys that MySQL could use. If NULL is shown, MySQL did not find a matching index.

7. key: It shows the actual key that MySQL uses.

8. key_len: It displays the length in bytes of the key.

9. ref: It shows how MySQL compares the values from the previous table to the current table.

10. rows: It represents the estimated number of rows that MySQL expects to examine.

Let's look at an example to see how to use the EXPLAIN command.

Example

Suppose we want to find all employees named "Jefabc" from our "emp" table. We can use the following query:

SELECT * FROM emp WHERE name='Jefabc';

Let's run EXPLAIN on this query and see what we get:

EXPLAIN SELECT * FROM emp WHERE name='Jefabc';

Here's the output that we will see:

+----+-----------+-------+------------+-------+---------------+------+---------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
+----+-----------+-------+------------+-------+---------------+------+---------+------+-------------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 33 | const| 1 |
+----+-----------+-------+------------+-------+---------------+------+---------+------+-------------+

This result set shows us that MySQL used a const access type, which means it was able to find the data it needed using a unique index. In this case, the unique index is the primary key of the "emp" table. MySQL estimated that it will need to examine only one row to find the data it needs.

Conclusion

The EXPLAIN command is an essential tool for MySQL developers who want to optimize their queries. It provides valuable insights into how MySQL retrieves and processes data. By understanding the output of the EXPLAIN command and how MySQL query optimization works, developers can write more efficient and effective queries.

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。