mysql使用desc(describle)和explain查看执行计划--笔记
大家查看mysql执行计划时用的最多的是explain，其实还可以等效使用desc、describle查看执行计划，desc和explain命令还可以有别的作用如查看表列属性等功能。
1、查看表结构和相关信息
dba_mon@tdb_hlf>show create table tbl_read\G
*************************** 1. row ***************************
Table: tbl_read
Create Table: CREATE TABLE `tbl_read` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
dba_mon@tdb_hlf>select * from tbl_read;
+----+----------+
| id | name |
+----+----------+
| 1 | hlf |
| 3 | NULL |
| 4 | zhangsan |
| 5 | lisi |
| 6 | mazi |
+----+----------+
5 rows in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>show index from tbl_read;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_read | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>show columns from tbl_read;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>show table status like 'tbl_read'\G
*************************** 1. row ***************************
Name: tbl_read
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 7
Create_time: 2020-06-29 19:21:08
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
2、使用desc(describle)查看执行计划
dba_mon@tdb_hlf>help desc;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
EXPLAIN
GROUP_CONCAT
SELECT
dba_mon@tdb_hlf>desc tbl_read;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>desc tbl_read name;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>desc tbl_read id;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>desc select * from tbl_read order by rand();
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tbl_read | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>desc extended select * from tbl_read order by rand();
+----+-------------+----------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | tbl_read | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.01 sec)
dba_mon@tdb_hlf>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `tdb_hlf`.`tbl_read`.`id` AS `id`,`tdb_hlf`.`tbl_read`.`name` AS `name` from `tdb_hlf`.`tbl_read` order by rand() |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>desc extended select * from tbl_read order by name;
+----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | tbl_read | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>desc extended select * from tbl_read order by id;
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_read | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>desc partitions select * from tbl_read order by rand();
+----+-------------+----------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tbl_read | NULL | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.01 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>desc partitions select * from tbl_read order by name;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | tbl_read | NULL | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>desc partitions select * from tbl_read order by id;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | tbl_read | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>describe tbl_read;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>describe extended select * from tbl_read order by id;
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_read | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
dba_mon@tdb_hlf>
3、使用explain查看执行计划
dba_mon@tdb_hlf>help explain;
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
explainable_stmt
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).
URL: http://dev.mysql.com/doc/refman/5.6/en/explain.html
dba_mon@tdb_hlf>explain tbl_read;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>explain tbl_read id;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>explain tbl_read name;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>explain select * from tbl_read order by rand();
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tbl_read | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>explain extended select * from tbl_read order by rand();
+----+-------------+----------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | tbl_read | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
dba_mon@tdb_hlf>
dba_mon@tdb_hlf>explain partitions select * from tbl_read order by rand();
+----+-------------+----------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tbl_read | NULL | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.01 sec)
dba_mon@tdb_hlf>
