×

首页 > [讲师] 史跃东 > [原创专区] Effective MySQL之sql优化——基本的分析命令03

Effective MySQL之sql优化——基本的分析命令03

讲师:史跃东 类型:原创专区 时间:2018年11月15日 浏览次数:1643 点赞:0

5. show status


show [ global | session] status命令可以用来查看MySQL服务器的当前内部状态信息。这些信息非常重要,可以从全局的角度来帮助用户确定MySQL服务器负载的各种指标。

在任何特定的会话中,上述信息可以帮助用户从大量的系统变量中找到有用的信息,从而进一步判断目标sql语句可能对服务器产生的影响——例如内部临时表的使用、索引的范围扫描以及处理的行数等等。

默认情况下show status等价于show session status。

mysql> show session status like 'Created_tmp%tables';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0     |

| Created_tmp_tables      | 0     |

+-------------------------+-------+

2 rows in set (0.01 sec)

mysql> alter table t_test add d timestamp;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0


mysql> show session status like 'Created_tmp%tables';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0     |

| Created_tmp_tables      | 3     |

+-------------------------+-------+

2 rows in set (0.00 sec)


注:show stauts一共可以输出300+的系统指标。若想了解关于这些输出项的全部信息,请查阅:

http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

上面的例子,则显示了当我们要给t_test表添加一列的时候,服务器实际创建了3个内部临时表来完成这一操作。


也可以通过查询INFORMATION_SCHEMA中的SESSION_STATUS或者GLOBAL_STATUS来得到同样的信息。


来看下面的例子:

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Database changed

mysql> flush status;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from t_test;

+----+------+-------------+---------------------+

| id | name | create_time | d                   |

+----+------+-------------+---------------------+

|  1 | 1    | 2016-11-10  | 2016-11-10 14:08:40 |

+----+------+-------------+---------------------+

1 row in set (0.00 sec)


mysql> show session status like 'handler_read%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Handler_read_first    | 1     |

| Handler_read_key      | 0     |

| Handler_read_last     | 0     |

| Handler_read_next     | 0     |

| Handler_read_prev     | 0     |

| Handler_read_rnd      | 0     |

| Handler_read_rnd_next | 1     |

+-----------------------+-------+

7 rows in set (0.00 sec)


mysql> flush status;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from t where id=10001;

+-------+-------+

| id    | name  |

+-------+-------+

| 10001 | 10001 |

+-------+-------+

1 row in set (0.01 sec)


mysql> show session status like 'handler_read%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Handler_read_first    | 0     |

| Handler_read_key      | 1     |

| Handler_read_last     | 0     |

| Handler_read_next     | 1     |

| Handler_read_prev     | 0     |

| Handler_read_rnd      | 0     |

| Handler_read_rnd_next | 0     |

+-----------------------+-------+

7 rows in set (0.00 sec)


Handler_read_key为1,表明使用了1个索引,Handler_read_next 为1,表明使用这个索引来读取了1行数据。


  6. show variables 


show [ global | session ] variables可以用来查看当前的系统参数。其中有些会影响到sql的执行方式。例如tmp_table_size限制了内部创建临时表的最大内存使用量。

mysql> show variables like 'tmp_table%';

+----------------+----------+

| Variable_name  | Value    |

+----------------+----------+

| tmp_table_size | 16777216 |

+----------------+----------+

1 row in set (0.00 sec)


当然,也可以查询INFORMATION_SCHEMA中的GLOBAL_VARIABLES和SESSION_VARIABLES来获得参数的相关信息。在比较系统级别和会话级别参数设置是否不同时,这两个字典表就很有用处。


7. INFORMATION_SCHEMA


实际上,在5.7.12中,初始化安装完成后,一共会有四个数据库:


mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |


+--------------------+

4 rows in set (0.00 sec)


关于information_schema ,官方文档的解释如下:


INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not performINSERTUPDATE, or DELETE operations on them.

例如:

mysql> SELECT table_name, table_type, engine

    -> FROM information_schema.tables

    -> where table_schema='test'

    -> order by table_name;

+------------+------------+--------+

| table_name | table_type | engine |

+------------+------------+--------+

| t          | BASE TABLE | InnoDB |

| t_test     | BASE TABLE | MyISAM |

+------------+------------+--------+

2 rows in set (0.00 sec)

footer底部

PMI, PMP, Project Management Professional, CAPM, PgMP, PfMP, PMI-ACP, PMI-RMP, PMI-SP, PMI-PBA and PMBOK are registered marks of the Project Management Institute, Inc.
Copyright © 2006-2018 东方瑞通(北京)咨询服务有限公司版权所有
京ICP备 13009094号   京公网安备 11010802014211号