CC00147.bigdatajava——|Java&MySQL.高级.V19|——|MySQL.v20|EXPLAN_ID字段介绍|

yanqi_vip

已于 2022-04-09 22:24:43 修改

阅读量56

点赞数

分类专栏： bigdatav005——mysql 文章标签： mysql java 数据库 sql javascript

于 2022-04-06 16:01:00 首次发布

不予转载

本文链接：https://blog.csdn.net/yanqi_vip/article/details/124031217

版权

bigdatav005——mysql 专栏收录该内容

163 篇文章 0 订阅

订阅专栏

一、EXPLAIN字段介绍

### --- 数据准备

~~~     # 创建数据库
CREATE DATABASE test_explain CHARACTER SET 'utf8';

~~~     # 创建表

CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );

~~~     # 每张表插入3条数据

INSERT INTO L1(title) VALUES('yanqi01');
INSERT INTO L2(title) VALUES('yanqi02');
INSERT INTO L3(title) VALUES('yanqi03');
INSERT INTO L4(title) VALUES('yanqi04');

### --- ID介绍
~~~     select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序id相同，执行顺序由上至下

EXPLAIN SELECT * FROM    L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;

mysql> EXPLAIN SELECT * FROM    L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | L1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | L2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_explain.L1.id |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | L3    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_explain.L1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+

~~~     # id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

EXPLAIN SELECT * FROM L2 WHERE id = (
    SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title =
'yanqi03'));

mysql> EXPLAIN SELECT * FROM L2 WHERE id = (
    ->     SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title =
    -> 'yanqi03'));
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | L2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | L1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
|  3 | SUBQUERY    | L3    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+