Mysql explainとprofile
explain extended + show warnings
explain partitions
explain sql
リファレンス
id列
select_type simple:サブクエリとunion を含まない単純なクエリ primary:複雑なクエリーの最外層のselect subquery:selectに含まれるサブクエリ(from句ではない) derived:from句に含まれるサブクエリ.MySQLは結果を一時テーブルに格納します.派生テーブル(derivedの英語の意味) とも呼ばれます. union:unionの2番目とその後のselect union result:unionテンポラリテーブルから結果を取得するselect table
type NULL system const eq_ref ref ref_or_null index_merge range index ALL
possible_keys
key
key_len
ref
rows
extra:クエリーの追加情報の解析 distinct Using where Using index Using index condition Using filesort Using temporary Using join buffer (Block Nested Loop)
profile
explain extended: explain 。
show warnings , 。
filtered , ,rows * filtered/100 explain
( explain id id )。
mysql> show columns from admin;
+------------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| email | char(30) | NO | UNI | NULL | |
| pwd | char(60) | NO | | NULL | |
| token | char(60) | NO | | | |
| id_char | char(35) | NO | UNI | | |
| create_at | timestamp | NO | | CURRENT_TIMESTAMP | |
| updated_at | timestamp | YES | | NULL | |
+------------+-----------+------+-----+-------------------+----------------+
7 rows in set (0.01 sec)
mysql> explain extended select id,email from admin;
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | admin | index | NULL | admin_pk | 120 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `hyperf`.`admin`.`id` AS `id`,`hyperf`.`admin`.`email` AS `email` from `hyperf`.`admin` |
+-------+------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
explain partitions
explain partitions , , 。
explain sql
select explain ,
MySQL , , , SQL
( from , , )
リファレンス
id列
id select , select id,
id select
select_type
type
(the join type), ( )
system const eq_ref ref range index all
ref:reference(re,fen,ce)[ ]
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
mysql ,
explain select min(id) from table;
, ,
, , IO;
# mysql time_zone , , IO
explain select * from mysql.time_zone;
#pkId const, tmp system
#mysql
explain select * from ( select * from user where pkId=1 ) tmp;
mysql
1. (primary key) (unique)
2. (const) ,pkId = 1
tip.
(primary key) (unique not null)
(row),
1.join ;
2. (primary key) (unique not null) ;
3. ;
1.
2. (row),
3. , const ref,
ref, NULL 。
:id ,tenant_id 。or primary key, primary key(id) tenant_id
explain select * from role where id = 11011 or tenant_id = 8888;
sql between in, in, >, <
explain select COUNT(*) from user;
(full table scan)
possible_keys
。 , 。 WHERE
key
。 NULL, 。 ,MYSQL 。 , SELECT USE INDEX(indexname) IGNORE INDEX(indexname) MYSQL
key_len
。 ,
char(n):n
varchar(n):2 , utf-8, 3n + 2
tinyint:1
smallint:2
int:4
bigint:8
date:3
timestamp:4
datetime:8
NULL, 1 NULL
768 , ,mysql ,
。
ref
, ,
rows
MYSQL
extra:クエリーの追加情報の解析
mysql ,
SQL where , type , type all,
SQL , , ,
, , ,
, 。 SQL , ,
order by, filesort,
(temporary table) 。 SQL , 。
,group by order by , , ,
, type ALL,rows 4, 4*4 。
SQL , 。
, join, , 。
, , 。
profile
1. , .
show version(); show variables like '%version%'
2. profiling
show variables like '%profil%' ;
result:
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES | -- , profiling
| profiling | OFF | -- SQL
| profiling_history_size | 15 | -- profiling , 15, 0 100, 0 p
show profiles; , .
3.
help profile;
4. profile
set profiling=1; .
5. sql, profile
select * from test ;
show profiles;
result:
+----------+------------+--------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------+
| 28 | 0.00033575 | select * from test |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------+
sql ,
show profile [cpu,io][all] for query 28 ;
show profile for query 28 ;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 5.7E-5 |
| checking permissions | 7E-6 |
| Opening tables | 1.7E-5 |
| init | 2.3E-5 |
| System lock | 8E-6 |
| optimizing | 5E-6 |
| statistics | 1.1E-5 |
| preparing | 9E-6 |
| executing | 3E-6 |
| Sending data | 8.8E-5 |
| end | 5E-6 |
| query end | 6E-6 |
| closing tables | 5E-6 |
| freeing items | 7.8E-5 |
| cleaning up | 1.5E-5 |
+----------------------+----------+
6.
set profiling=off;