×

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

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

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

与Oracle一样,对MySQL的性能进行调整,同样也有一堆相关的工具。这里,我们将着重介绍如下内容:

explain;

show create table;

show indexes;

show table stauts;

show status;

show variables;

INFORMATION_SCHEMA。


1. explain


在Oracle中,我们可以有至少5种以上的方法来查看一条sql的执行计划。当然,有些是优化器估算生成而不一定是实际的执行计划。但是在MySQL中,我们查看一条sql的执行计划,最常用的,就是explain命令。

使用这条命令,我们可以深入了解MySQL中基于成本的优化器(没错,也是CBO),也可以获得很多可能被优化器考虑到的访问数据的策略,以及当运行sql时哪种策略预计会被优化器采用。

需要注意的是,生成的执行计划并不确定,也可能会根据很多因素而发生变化。


来看一个例子:

mysql> explain select host,user,password_last_changed

    -> from mysql.user

    -> where user like 'r%' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

able: user

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 2

filtered: 50.00

Extra: Using where


1 row in set, 1 warning (0.01 sec)


再执行如下查询:

mysql> explain select host,user,password_last_changed

    -> from mysql.user

    -> where host='localhost'

    -> and user like 'r%' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user

partitions: NULL

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 276

ref: NULL

rows: 1

filtered: 100.00

Extra: Using index condition


1 row in set, 1 warning (0.01 sec)

这里用到的mysql.user,是数据库的一个字典表,用来存储数据库用户的元数据信息。关于explain输出各列的具体含义,我们后面会详细展开。各位也可以自己先查阅相关资料或者是官方文档。这里先说几个点:

1,key:NULL 表示该sql没有使用索引。

2,rows,显示了处理的行数。有点类似于oracle中的cardinality。

3,possible_keys,被评估到的索引,也就是该sql可能会使用的索引。


若诸位有心去查,就可以知道,其实这个输出结果中,ref列和Extra列是最有说道的地方。ref列显示了访问数据的方式,Extra很多时候显示了使用的数据库的特性,比如说ICP啥的。


关于explain命令,所有的sql语句前面都可以直接加上这个关键词来查看其执行计划。对于update或delete操作,则需要把查询重写为select语句来确保有效使用索引。


该命令还有两个可选的关键字:partitions和extended。

前者在sql语句中包含分区表时很有用,从5.7.3开始,explain命令默认已经包含分区表的相关信息(如果使用了分区表的话)。并且该关键字在将来会被移除。其对应于输出中的partitions列。

后者用来获取与执行计划相关的一些额外的信息。同样也是从5.7.3开始,explain命令默认也启用了这一选项。其对应于输出中的filtered列。


最后一个需要注意的点是,我们上面两个查询的最后,都出现了warning:

mysql> show warnings \G

*************************** 1. row ***************************

  Level: Note

   Code: 1003

Message: /* select#1 */ select `mysql`.`user`.`Host` AS `host`,`mysql`.`user`.`User` AS `user`,`mysql`.`user`.`password_last_changed` AS `password_last_changed` from `mysql`.`user` where ((`mysql`.`user`.`Host` = 'localhost') and (`mysql`.`user`.`User` like 'r%'))

1 row in set (0.00 sec)

使用该命令,可以看到实际执行的sql是什么样子,也就是说,在实际执行的时候,MySQL可能会对我们提交的sql进行改写。


2. show create table


该命令用来得到表的DDL语句,类似于Oracle中的DBMS_METADATA.get_ddl函数。

可以使用该命令来获取表中的当前列和索引定义的全部细节。包括列名,数据类型,默认值,是否包含自增值,索引,以及字符集和使用的存储引擎等等。

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> create table t_test 

    -> (id bigint unsigned not null auto_increment,

    -> name varchar(30) not null default '',

    -> create_time date,

    -> primary key (id));

Query OK, 0 rows affected (0.04 sec)


mysql> show create table t_test \G

*************************** 1. row ***************************

       Table: t_test

Create Table: CREATE TABLE `t_test` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(30) NOT NULL DEFAULT '',

  `create_time` date DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

可见,与上面的创建语句相比,多了存储引擎Innodb,以及默认的字符集。


可以使用mysqldump命令得到某一schema或者数据库中所有表的定义:

[root@rhel6 Desktop]# mysqldump --databases test -d > test.sql;

[root@rhel6 Desktop]# more test.sql 

···输出省略。


可以使用mysqldump --help来获取该命令的所有选项。上面的例子中:

--datebases test  表示只输出test数据库中的对象。

-d  表示no-date,不导出数据,也就是只导出元数据。


其实,也可以使用INFORMATION_SCHEMA中的表来获取表结构,这些表包括:

TABLES

columns;

TABLE_CONSTRAINTS

KEY_COLUMN_USAGE

REFERENTIAL_CONSTRAINTS

PARTITIONS等等。

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号