Từ khóa EXPLAIN được sử dụng để lấy thông tin về cách cơ sở dữ liệu SQL của chúng tôi thực hiện các truy vấn trong MySQL. Nó đồng nghĩa với câu lệnh DESCRIBE. Trong thực tế, từ khóa MÔ TẢ cung cấp thông tin cấu trúc bảng, trong khi từ khóa GIẢI THÍCH cung cấp kế hoạch thực hiện truy vấn. Nó là một công cụ mạnh mẽ để hiểu và tối ưu hóa các truy vấn trong MySQL, nhưng các nhà phát triển rất hiếm khi sử dụng nó
Nó có thể hoạt động với các truy vấn INSERT, SELECT, DELETE, UPDATE và REPLACE. Từ MySQL 8. 0. 19 trở lên, nó cũng có thể hoạt động với các câu lệnh TABLE. Khi chúng tôi sử dụng từ khóa này trong các truy vấn, nó sẽ xử lý câu lệnh và cung cấp thông tin về từng bước trong kế hoạch thực hiện, chẳng hạn như cách nối các bảng, thứ tự của bảng, phân vùng ước tính, v.v. Nó trả về một hoặc nhiều hàng giải thích từng phần của kế hoạch thực hiện và thứ tự thực hiện
Hãy để chúng tôi hiểu nó với sự giúp đỡ của một ví dụ
Ví dụ
Giả sử chúng ta có một bảng tên là "student_info" và "orders" trong cơ sở dữ liệu mẫu chứa dữ liệu sau
Nếu chúng ta muốn hiển thị kế hoạch thực hiện của câu lệnh SELECT, chúng ta có thể sử dụng truy vấn như bên dưới
đầu ra
Truy vấn này tạo ra thông tin sau
Đôi khi chúng tôi không muốn quét toàn bộ bảng. Trong trường hợp đó, chúng ta cần sử dụng INDEX để xem thông tin dựa trên một điều kiện cụ thể. Thực hiện câu lệnh dưới đây để tạo chỉ mục trong bảng student_info
Nếu chỉ mục được tạo thành công, nó sẽ tạo ra đầu ra bên dưới
Bây giờ, hãy thực hiện truy vấn sau để tránh quét toàn bộ bảng trong cơ sở dữ liệu
Nó sẽ tạo ra đầu ra như hình ảnh bên dưới
đầu ra
Sau khi thực hiện ta sẽ được kết quả như hình bên dưới
Ở kết quả trên, chúng ta có thể thấy rằng select_type ĐƠN GIẢN cho cả hai bảng. Cả hai bảng đều tuân theo mối quan hệ một-nhiều. Khóa chính của bảng student_info được sử dụng làm khóa ngoại cho bảng đơn hàng. Do đó, giá trị có thể_keys cho hàng thứ hai là order_id. Giá trị được lọc ở hàng đầu tiên là 12. 50% cho bảng student_info vì 'Barack' là mục thứ tư của bảng này. Giá trị được lọc ở hàng thứ hai là 100% cho bảng đơn hàng. Đó là bởi vì tất cả các giá trị của bảng đơn đặt hàng phải được kiểm tra để lấy dữ liệu
GIẢI THÍCH Từ khóa trong Truy vấn CHỌN với Toán tử UNION ALL
UNION ALL là toán tử trả về tất cả các giá trị cột phù hợp với các giá trị trùng lặp từ các bảng có liên quan trong khi sử dụng truy vấn SELECT. Câu lệnh dưới đây hiển thị đầu ra EXPLAIN bằng cách sử dụng toán tử UNION ALL giữa bảng student_info và bảng đơn đặt hàng
đầu ra
Nó sẽ tạo ra kết quả bên dưới, nơi chúng ta có thể thấy rằng giá trị của select_type là UNION cho hàng thứ hai và giá trị của cột Extra là chỉ mục
MySQL GIẢI THÍCH Hạn chế từ khóa
Sau đây là những hạn chế phổ biến nhất của từ khóa EXPLAIN trong MySQL
- EXPLAIN không cung cấp bất kỳ thông tin nào về cách trình kích hoạt, chức năng được lưu trữ hoặc UDF sẽ ảnh hưởng đến truy vấn của chúng tôi
- Từ khóa GIẢI THÍCH không thể hoạt động đối với các thủ tục được lưu trữ
- Nó không cho bạn biết về tối ưu hóa mà MySQL thực hiện trong quá trình thực hiện truy vấn
- Nó tạo ra các số liệu thống kê ước tính có thể rất không chính xác
- Nó không tạo ra mọi thông tin liên quan đến kế hoạch thực hiện truy vấn
PHÂN TÍCH GIẢI THÍCH MySQL
Nó là một công cụ lập hồ sơ cho các truy vấn hiển thị thông tin về nơi MySQL dành thời gian để thực hiện truy vấn và tại sao. Nó sẽ lập kế hoạch cho truy vấn, thiết bị và thực hiện nó trong khi đếm các hàng và đo thời gian dành cho các điểm khác nhau trong kế hoạch thực hiện. Khi thực hiện xong, nó sẽ in kế hoạch và phép đo thay vì kết quả truy vấn
EXPLAIN 是什么chọn phiên bản[]:
4system > const > eq_ref > ref > range > index > ALL 复制代码
MySQL 提供了一个
system > const > eq_ref > ref > range > index > ALL
复制代码
5 命令, 它可以对 system > const > eq_ref > ref > range > index > ALL
复制代码
6 语句进行分析, 并输出 system > const > eq_ref > ref > range > index > ALL
复制代码
6 执行的详细信息, 以供开发人员针对性优化EXPLAIN 结果中的type字段例如分析一条 SELECT 语句
EXPLAIN SELECT * FROM `user` WHERE id = 1 复制代码
Tips:常见的扫描方式
- system:系统表,少量数据,往往不需要进行磁盘IO
- const:常量连接
- eq_ref:主键索引[primary key]或者非空唯一索引[unique not null]等值扫描
- ref:非主键非唯一索引等值扫描
- range:范围扫描
- index:索引树扫描
- ALL:全表扫描[full table scan]
system > const > eq_ref > ref > range > index > ALL
复制代码
1扫描方式由快到慢
system > const > eq_ref > ref > range > index > ALL
复制代码
system > const > eq_ref > ref > range > index > ALL
复制代码
1. hệ thống
上例中,从系统库mysql的系统标proxies_priv里查询数据,这里的数据在Mysql服务启动时候已经加载在内存中,不需要进行磁盘IO。
官方文档中的解释:
system > const > eq_ref > ref > range > index > ALL
复制代码
22. hăng sô
模拟数据create table user [
id int primary key,
name varchar[20]
]engine=innodb;
insert into user values[1,'ar414'];
insert into user values[2,'zhangsan'];
insert into user values[3,'lisi'];
insert into user values[4,'wangwu'];
复制代码
Explain分析结果上例中id是主键[primary key],连接部分是常量1,通过索引一次就能找到,速度非常快
场景:
- 命中
3或者system > const > eq_ref > ref > range > index > ALL 复制代码
4system > const > eq_ref > ref > range > index > ALL 复制代码
- 被连接的部分是一个
5system > const > eq_ref > ref > range > index > ALL 复制代码
3. eq_ref
模拟数据________số 8Explain分析结果上例中对于前表
system > const > eq_ref > ref > range > index > ALL
复制代码
6表中的每一行(row),对应后system > const > eq_ref > ref > range > index > ALL
复制代码
7表只有一行被扫描,这类扫描的速度也非常的快场景:
8[join]查询system > const > eq_ref > ref > range > index > ALL 复制代码
- 命中
9[primary key]或者system > const > eq_ref > ref > range > index > ALL 复制代码
0[unique not null]create table user [ id int primary key, name varchar[20] ]engine=innodb; insert into user values[1,'ar414']; insert into user values[2,'zhangsan']; insert into user values[3,'lisi']; insert into user values[4,'wangwu']; 复制代码
1连接create table user [ id int primary key, name varchar[20] ]engine=innodb; insert into user values[1,'ar414']; insert into user values[2,'zhangsan']; insert into user values[3,'lisi']; insert into user values[4,'wangwu']; 复制代码
4. giới thiệu
模拟数据同eq_ref模拟数据区别:user_balance表中的
2改为create table user [ id int primary key, name varchar[20] ]engine=innodb; insert into user values[1,'ar414']; insert into user values[2,'zhangsan']; insert into user values[3,'lisi']; insert into user values[4,'wangwu']; 复制代码
3create table user [ id int primary key, name varchar[20] ]engine=innodb; insert into user values[1,'ar414']; insert into user values[2,'zhangsan']; insert into user values[3,'lisi']; insert into user values[4,'wangwu']; 复制代码
system > const > eq_ref > ref > range > index > ALL
复制代码
7Explain分析结果联表查询
由于后表使用了
create table user [
id int primary key,
name varchar[20]
]engine=innodb;
insert into user values[1,'ar414'];
insert into user values[2,'zhangsan'];
insert into user values[3,'lisi'];
insert into user values[4,'wangwu'];
复制代码
4,对于前表system > const > eq_ref > ref > range > index > ALL
复制代码
6表的每一行[row],后表system > const > eq_ref > ref > range > index > ALL
复制代码
7表可能有多于一行的数据被扫描单表查询
当id改为普通非唯一索引后,常量的连接查询,也由const降级为了ref,因为非唯一索引所以有多于一行的数据被可能被扫描
create table user [
id int primary key,
name varchar[20]
]engine=innodb;
insert into user values[1,'ar414'];
insert into user values[2,'zhangsan'];
insert into user values[3,'lisi'];
insert into user values[4,'wangwu'];
复制代码
7每一次匹配可能有多行数据返回,虽然它比eq_ref要慢,但它仍然是一个很快的join类型场景:
- 联表查询
- 普通非唯一索引
5. phạm vi
模拟数据system > const > eq_ref > ref > range > index > ALL
复制代码
2Explain分析结果ở giữa
Trong
>,>=, eq_ref > ref > range > index > ALL 复制代码3Explain分析结果
如果id上不建索引,则全表扫描
总结
- type类型从快到慢:system>const>eq_ref>ref>range>index>ALL
- 作为一名合格的后端开发者应该熟悉掌握Explain
- 结合业务建立正确索引,而不是每个字段建立索引(滥用)
MySQL là gì và các loại của nó?
MySQL hỗ trợ rất nhiều loại dữ liệu tiêu chuẩn SQL trong các danh mục khác nhau. Nó sử dụng nhiều loại dữ liệu khác nhau có thể được chia thành các loại sau. số, ngày và giờ, kiểu chuỗi, kiểu không gian và kiểu dữ liệu JSON .loại chọn trong MySQL là gì?
Giá trị select_type cho các câu lệnh không phải SELECT hiển thị loại câu lệnh cho các bảng bị ảnh hưởng . Ví dụ: select_type là DELETE cho câu lệnh DELETE. bảng [tên JSON. table_name ] Tên của bảng mà hàng đầu ra đề cập đến.Làm cách nào để tìm kiểu dữ liệu trong MySQL?
Bạn có thể lấy kiểu dữ liệu cột của bảng MySQL với sự trợ giúp của “information_schema. cột". CHỌN DATA_TYPE từ INFORMATION_SCHEMA. COLUMNS trong đó table_schema = 'yourDatabaseName' và table_name = 'yourTableName' .Giải thích trong SQL là gì?
Trong SQL, từ khóa EXPLAIN cung cấp mô tả về cách các truy vấn SQL được cơ sở dữ liệu thực thi . Những mô tả này bao gồm nhật ký trình tối ưu hóa, cách các bảng được nối và theo thứ tự, v.v. Do đó, nó sẽ là một công cụ hữu ích trong việc tối ưu hóa truy vấn và biết từng bước thực hiện chi tiết của nó.