|
|
1、获取服务器使用信息
SHOW STATUS
语法规则- show [session | global] status like 'Connections'
参数值参数说明Connections连接MySQL服务器的次数UptimeMySQL服务器启动后连续工作的时间Slow_queries慢查询的次数Com_insert插入数据的次数(只统计insert语句的次数，一次插入多条算一次)Com_delete删除数据的次数Com_update修改数据的次数Com_select查询数据的次数Innodb_rows_read查询数据时返回的数据行数Innodb_rows_inserted插入数据时返回的记录数Innodb_rows_updated更新数据时返回的记录数Innodb_rows_delete删除数据时返回的记录数例如查看MySLQ服务器启动后连续工作的时间- mysql> show session status like 'connections';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Connections | 10 |
- +---------------+-------+
- 1 row in set (0.00 sec)
- mysql> show global status like 'connections';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Connections | 10 |
- +---------------+-------+
- 1 row in set (0.01 sec)
- mysql> show status like 'connections';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Connections | 10 |
- +---------------+-------+
- 1 row in set (0.00 sec)
EXPLAIN
语法格式- explain select id,name,age from Student \G;
- mysql> explain select name from Student \G;
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: Student
- partitions: NULL
- type: ALL
- possible_keys: NULL
- key: NULL
- key_len: NULL
- ref: NULL
- rows: 1000
- filtered: 100.00
- Extra: NULL
- 1 row in set, 1 warning (0.00 sec)
（2）select_type：SQL语句的查询类型，简单查询语句/复杂查询语句。
类型说明SIMPLE简单查询PRIMARY主键查询或者包含子查询时最外层的查询语句UNION连接查询，表示连接查询的第二个语句或者更后面DEPENDENT UNION与UNION差不多，但是这里取决于最外层的查询语句UNION RESULT连接查询的结果信息SUBQUERY子查询中的第一个查询语句DEPENDENT SUBQUERY取决于外层的查询语句DERIVEDFROM子句中的子查询MATERIALIZED表示实例化子查询UNCACHEABLE SUBQUERY不缓存子查询的结果数据，重新计算外部查询的每一行数据UNCACHEABLE UNION不缓存连接查询的结果数据，每次执行连接查询时都会重新计算数据结果（3）table：当前查询的数据表
（4）partitions：如果这个是分区表、表示查询结果匹配的分区。负责返回null。
（5）type：当前SQL语句使用的关联类型或者访问类型。最优到差依次为
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All
（6）possible_keys：执行查询语句时可能使用到的索引。但在实际查询中未必会使用到。为null时表示没有可使用的索引。
（7）key：执行查询语句时实际会使用到的索引，没有使用则为null。
（8）key_len：实际使用到索引按照字节计算的长度值。
（9）ref：数据表中的哪个列或者常量用来与key列中的索引做比较来检索数据。
（10）rows：查询数据的行数。
（11）filtered：查询结果符合查询条件的百分比。
（12）ectra：执行查询语句时额外的详细信息。
3、深入分析
SHOW PROFILE语句解析
查看MySQL是否支持PROFILE- mysql> select @@have_profiling;
- +------------------+
- | @@have_profiling |
- +------------------+
- | YES |
- +------------------+
- 1 row in set, 1 warning (0.00 sec)
- mysql> select @@profiling;
- +-------------+
- | @@profiling |
- +-------------+
- | 0 |
- +-------------+
- 1 row in set, 1 warning (0.00 sec)
- mysql> set session profiling=1;
- Query OK, 0 rows affected, 1 warning (0.01 sec)
- mysql> select @@profiling;
- +-------------+
- | @@profiling |
- +-------------+
- | 1 |
- +-------------+
- 1 row in set, 1 warning (0.00 sec)
- mysql> show create table Student \G;
- *************************** 1. row ***************************
- Table: Student
- Create Table: CREATE TABLE `Student` (
- `name` varchar(255) DEFAULT NULL,
- `age` int DEFAULT NULL,
- `gender` varchar(255) DEFAULT NULL,
- `school` varchar(255) DEFAULT NULL,
- `id` int NOT NULL AUTO_INCREMENT,
- `phone` varchar(255) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
- 1 row in set (0.00 sec)
- mysql> select count(*) from Student;
- +----------+
- | count(*) |
- +----------+
- | 1000 |
- +----------+
- 1 row in set (0.00 sec)
- mysql> show profiles;
- +----------+------------+--------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+--------------------------------+
- | 1 | 0.00342200 | select @@profiling |
- | 2 | 0.00027400 | select countct(*) from Student |
- | 3 | 0.00028900 | select countct(*) from Student |
- | 4 | 0.00436800 | select count(*) from Student |
- | 5 | 0.00045100 | show create tables Student |
- | 6 | 0.00571900 | show create table Student |
- | 7 | 0.00136000 | show create table Student |
- | 8 | 0.00401200 | select count(*) from Student |
- | 9 | 0.00037000 | show profiles
- ； |
- +----------+------------+--------------------------------+
- 9 rows in set, 1 warning (0.00 sec)
- mysql> show profile for query 8;
- +--------------------------------+----------+
- | Status | Duration |
- +--------------------------------+----------+
- | starting | 0.001623 |
- | Executing hook on transaction | 0.000015 |
- | starting | 0.000029 |
- | checking permissions | 0.000017 |
- | Opening tables | 0.000119 |
- | init | 0.000013 |
- | System lock | 0.000025 |
- | optimizing | 0.000014 |
- | statistics | 0.000060 |
- | preparing | 0.000051 |
- | executing | 0.001900 |
- | end | 0.000008 |
- | query end | 0.000007 |
- | waiting for handler commit | 0.000030 |
- | closing tables | 0.000019 |
- | freeing items | 0.000062 |
- | cleaning up | 0.000020 |
- +--------------------------------+----------+
- 17 rows in set, 1 warning (0.00 sec)
- mysql> show profile block io for query 8;
- +--------------------------------+----------+--------------+---------------+
- | Status | Duration | Block_ops_in | Block_ops_out |
- +--------------------------------+----------+--------------+---------------+
- | starting | 0.001623 | 0 | 0 |
- | Executing hook on transaction | 0.000015 | 0 | 0 |
- | starting | 0.000029 | 0 | 0 |
- | checking permissions | 0.000017 | 0 | 0 |
- | Opening tables | 0.000119 | 0 | 0 |
- | init | 0.000013 | 0 | 0 |
- | System lock | 0.000025 | 0 | 0 |
- | optimizing | 0.000014 | 0 | 0 |
- | statistics | 0.000060 | 0 | 0 |
- | preparing | 0.000051 | 0 | 0 |
- | executing | 0.001900 | 0 | 0 |
- | end | 0.000008 | 0 | 0 |
- | query end | 0.000007 | 0 | 0 |
- | waiting for handler commit | 0.000030 | 0 | 0 |
- | closing tables | 0.000019 | 0 | 0 |
- | freeing items | 0.000062 | 0 | 0 |
- | cleaning up | 0.000020 | 0 | 0 |
- +--------------------------------+----------+--------------+---------------+
- 17 rows in set, 1 warning (0.00 sec)
来源:https://www.cnblogs.com/changwan/p/18198053
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！ |
|
