第3章
CHAPTER 3


MySQL元数据相关查询












3.1show关键字

在本书的第1章中介绍了show databases语句，show databases可以读取MySQL服务器当前包含的所有数据库。例如可以使用use语句选择一个数据库，然后使用show tables语句查看当前数据库包含的所有表。

3.1.1show关键字查看某实例库中含有的表

show关键字主要用于查询MySQL服务器相关的信息，SQL语句如下： 




图31查看实例库中表的结果集




//3.1.1 show关键字查看某实例库中含有的表.sql



show databases;#展示所有的实例库

use learnSQL; #进入learnSQL实例库

show tables; #展示learnSQL实例库中所有的表


运行后，结果如图31所示。

show关键字可以查询许多关于数据库、表、线程、状态等的实用信息，若要对MySQL有一个整体的了解，则掌握show命令是必不可少的。

展开来讲show命令可以查询许多MySQL服务器的信息，示例如下： 


//3.1.1 show关键字查看某实例库中含有的表.sql



show databases;-- 显示所有数据库

show tables; -- 显示数据表

show columns from table_name; -- 显示数据表的属性

show index from table_name; -- 显示数据表的索引 

show triggers; -- 显示触发器

show status; -- 显示服务器状态

show variables; -- 显示服务器配置变量 






show processlist; -- 显示服务器当前运行的线程

show grants; -- 显示授权

show errors; -- 显示最近的错误消息


3.1.2show关键字查看表结构

使用show关键字可以直接展示某张表的相关列信息，SQL语句如下： 


show columns from student;

#上下等价

desc student;


运行后，结果如图32所示。




图32表结构结果集


3.1.3show关键字查看binlog日志

binlog日志是MySQL中保留增、删、改内容的日志，也是MySQL主从同步最重要的日志，show关键字可直接查看binlog日志目录，SQL语句如下： 


show binary logs


运行后，结果如图33所示。




图33binlog日志状态结果集


3.1.4show关键字查看相关创建语句信息

在使用MySQL创建相关实例库、表、视图、存储过程、函数等内容之后，皆可使用show语句查询其初始创建语句，以方便用户进行导入、导出等相关操作。

1. 查看实例库的创建语句


show create database learnSQL2;


运行后，结果如图34所示。




图34查看实例库创建语句的结果集


2. 查看表的创建语句


show create table student;


运行后，结果如图35所示。




图35查看表创建语句的结果集


如图35所示的内容在查看时会有所困难，所以可以在SQL语句中增加＼G进行分行，SQL语句如下： 


show create table student＼G;


运行后，结果如图36所示。



图36查看表的创建语句分行的结果集

3.1.5show关键字查看MySQL支持哪些引擎

show关键字可以展示当前MySQL版本支持哪些引擎，SQL语句如下： 


show engines;

#上下等价

select * from information_schema.engines;


运行后，结果如图37所示。




图37查看表的创建语句分行的结果集

3.2数据库的系统变量元数据与set关键字

用户可以使用set关键字将用户自定义的变量存储至MySQL中。

3.2.1set关键字用于用户自定义变量

set关键字的语法如下： 


SET @var_name = expr ［, @var_name = expr］ …


@var_name处可以输入用户自定义的变量名称，其中可包括字母、数字及字符组成。

“=”赋予符号可以更改为“∶=”，两种是等价的。“∶=”赋予符号是为了有别于where子句中的等号，所以在set关键字用于用户自定义变量时更推荐使用此赋予符号。

在赋予符号之后的expr表达式内可以使用数字、字符串或表达式。

在MySQL的SQL语句中，通常以“;”作为一句话的结尾。用户自定义变量之后，set关键字需要结尾，后续select语句需要再次结尾，SQL语句如下： 


//3.2.1 set关键字用于用户自定义变量.sql



set @v1 = '41';

set @v2 = '41'+6;

set @v3 = @v2-@v1;

select @v1, @v2, @v3;

#上下等价

set @v4 := '41';

set @v5 := '41'+6;

set @v6 := @v2-@v1;

select @v4, @v5, @v6;


分别执行上述4句话，运行后的效果如图38所示。

此处展示的效果类似于其他语言中的数据变量的定义与调用，在MySQL的查询中也经常可以用到，set关键字的表达式的写法如下： 


set @v1 = (select sal from emp limit 1);

select @v1;


在set关键字的表达式中只能返回1行1列，并用括号进行包围表达式才能经过校验。运行后，效果如图39所示。




图38set自定义变量效果




图39set自定义变量效果




set关键字的表达式写法若含有多列，则报错如下： 


Operand should contain 1 column(s)


set关键字的表达式写法若含有多行，则报错如下： 


Subquery returns more than 1 row


3.2.2set关键字用于环境变量

MySQL中含有许多系统变量(也可称为环境变量)，系统变量是MySQL运行所需的重要元数据。

部分MySQL的系统变量由MySQL的配置文件(my.ini文件或者my.cnf文件)进行配置和管理，部分MySQL的系统变量存储在MySQL缓存中。

当需要修改由配置文件配置的系统变量时，需要重启MySQL服务才能执行成功。若在配置文件中不含有任何系统变量的设置，则MySQL在启动时将以默认的方式对其进行设置。

show关键字可以展示当前MySQL全部的系统变量，SQL语句如下： 


show variables;


服务器维护着两种系统变量，即全局变量(Global Variables)和会话变量(Session Variables)。全局变量影响 MySQL 服务的整体运行方式，而会话变量仅影响当前具体客户端连接的操作。

每个客户端成功连接服务器后都会产生与之对应的会话(Session)。会话期间MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量，会话变量的初始值是全局变量值的复制。

show关键字可以展示当前MySQL全局的系统变量，SQL语句如下： 


show global variables;


show关键字可以展示MySQL当前会话的系统变量。当前会话的系统变量指本次打开的MySQL会话中的系统变量，若关闭本次会话后重新打开，则一些之前被设置为当前会话级的系统变量会变回全局的系统变量，SQL语句如下： 


show session variables;


在MySQL中通常使用两种方式修改MySQL相关的系统变量： 

(1) 修改MySQL的配置文件(my.ini文件或者my.cnf文件)。

(2) 通过set关键字进行设置。


通过set关键字修改全局变量的SQL语句如下： 


set global innodb_file_per_table=on;

#上下等价

set @@global.innodb_file_per_table=ON;


通过set关键字修改会话变量的SQL语句如下： 


set @@session.pseudo_thread_id=5;

#上下等价

set session pseudo_thread_id=5;


在MySQL中通常以“@”符号作为用户自行设置的变量，以“@@”符号作为系统变量。在用户的set关键字的语句没有指定是全局变量还是会话变量的情况下，默认将设置为会话变量，SQL语句如下： 


set @@sort_buffer_size = 50000;


无论是MySQL的会话变量还是MySQL的全局变量，重启MySQL之后都会恢复为默认配置。

在MySQL的配置文件(my.ini文件或者my.cnf文件)的［mysqld］配置内编写MySQL的系统变量可以保证MySQL重启之后，该系统变量仍然有效。MySQL配置文件的示例如下： 


//3.2.2 set关键字用于环境变量.sql



［mysqld］

port=3306

basedir=/home/zhangfangxing/mysql/base

datadir=/home/zhangfangxing/mysql/data

max_connections=100

query_cache_size=0

table_cache=256

tmp_table_size=35M

thread_cache_size=8

key_buffer_size=55M






read_rnd_buffer_size=256K

sort_buffer_size=256K


MySQL配置文件的释义如表31所示。


表31配置文件变量释义


变 量 名 称释义


portMySQL监听的端口号
basedirMySQL安装路径
datadirMySQL数据的存储位置
max_connections允许同时访问 MySQL 服务器的最大连接数，其中一个连接是保留的，留给管理员专用
query_cache_size查询时的缓存大小，缓存中可以存储以前通过 SELECT 语句查询过的信息，再次查询时就可以直接从缓存中取出信息，可以改善查询效率
table_open_cache所有进程打开表的总数
tmp_table_size内存中每个临时表允许的最大大小
thread_cache_size缓存的最大线程数
key_buffer_size关键词的缓存大小
read_rnd_buffer_size将排序后的数据存入该缓存的大小
sort_buffer_size用于排序的缓存大小


以上数据只是MySQL变量中的一小部分，皆可通过MySQL的show关键字进行查询，SQL语句如下： 


//3.2.2 set关键字用于环境变量.sql



show variables where variable_name = 'port';

show variables where variable_name = 'basedir';

show variables where variable_name = 'datadir';

show variables where variable_name = 'max_connections';

show variables where variable_name = 'tmp_table_size';

show variables where variable_name = 'thread_cache_size';

show variables where variable_name = 'key_buffer_size';

show variables where variable_name = 'sort_buffer_size';


3.2.3sql_mode变量

MySQL中的sql_mode为SQL语句校验变量，也是MySQL 8.0中最需要重视的变量之一，该变量可为空值，默认值如下： 


//3.2.3 sql_mode变量.sql



show variables where variable_name = 'sql_mode';

--返回

--only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_engine_substitution



在sql_mode的设置下，MySQL允许执行部分非法语句。

为了保证SQL语句的正确性，生产数据库与测试数据库的sql_mode值必须保证相同，否则会出现一条SQL语句在测试环境下可正常执行，但在生产情况下却没法正常执行的现象。

only_full_group_by： 在该校验模式下将影响SQL语句中group by子句的编写方式。若在select中的列没有在group by的子句中出现，则该SQL是不合法的。此校验强行要求列在group by子句中标识出来。此校验是MySQL 8.0.0默认自带的校验，由于此校验是MySQL后期追加的校验，因此很多低版本MySQL的SQL语句无法跟高版本兼容，建议数据库部署时删掉此校验。

no_auto_value_on_zero： 在该校验模式下将影响自增长列的插入。默认设置下插入0或null代表生成下一个自增长值。设置该值后可插入值为0，但是会导致数据混乱的问题，建议数据库部署时删掉此校验。

strict_trans_tables： 在该校验模式下，若一个值不能插入一个事务表中，则中断当前的操作，对非事务表不做限制。

no_zero_in_date： 在该校验模式下，不允许日期和月份为0。

no_zero_date： 在该校验模式下，插入零日期会抛出错误而不是警告。

error_for_division_by_zero： 在该校验模式下，在insert或update过程中，若数据被删除，则产生错误而非警告。

no_auto_create_user： 在该校验模式下，禁止使用MySQL的grant命令创建密码为空的用户。

no_engine_substitution： 在该校验模式下，若需要的存储引擎被禁用或未编译，则会抛出错误。当不设置此值时用默认的存储引擎替代并抛出一个异常。

pipes_as_concat： 在该校验模式下，将“||”符号视为字符串的连接操作符而非或运算符，和字符串的拼接函数concat()相类似。此校验模式主要为了兼容Oracle的SQL语句。

ansi_quotes： 在该校验模式下，不能用双引号来引用字符串，双引号将被解释为识别符。

3.2.4根据用户自定义变量增加列的行号

在日常编写SQL的过程中，若在MySQL 5版本上期望在结果集中增加列的行号，则需要使用用户自定义变量。后文会提到在MySQL 8.0版本之后可直接使用窗口函数row_number()解决此问题。

查询公司表，SQL语句如下： 


select e.ename,e.job from emp e;


运行后，结果集如图310所示。

使用set关键字设置用户自定义变量增加结果集行号，SQL语句如下： 


#将用户自定义变量rownum设置为0

set @rownum := 0;

#编写SQL语句增加行号查询

select @rownum:=@rownum+1,e.ename,e.job from emp e;


运行后，结果集如图311所示。




图310EMP表的结果集




图311EMP表增加序号的结果集




关于此语句需要注意以下内容： 

(1) 因为以上SQL含有两个分号(“;”符号)，所以其为两句SQL语句，第一句设置了rownum变量，第二句根据rownum变量进行查询。


图312不重置变量的效果




(2) 因为SQL的select本身含有指针的特性，即新的一行被扫描之后，SQL相当于重新查看一次该行的所有列，所以可以在新一行的列中让rownum重新赋予新的值，新的值为rownum+1。本质上rownum只是利用了SQL语言的指针特性，与循环类似。

(3) 用户变量不会在SQL语句结束后自行重置，若再次运行此SQL语句且不重置用户变量，则SQL执行结果如图312所示。

若希望在将两条语句整合成一条语句时仅使用一句SQL在emp表增加序号，则可以不使用set关键字而使用用户变量，SQL语句如下： 


//3.2.4 根据用户自定义变量增加列的行号.sql



select

(@myrow := @myrow + 1) AS line,

e.ename,

e.job

from

( select ename, job from emp ) e,






( select @myrow := 0 ) r;



-- 以上SQL在FROM处隐性地将用户变量设置为0，所以可以在列处直接调用

-- FROM关键字只执行一次，但是SELECT关键字将执行N次


运行后，结果集如图313所示。




图313仅使用一句SQL在emp表增加序号的结果集


3.3表的元数据

计算机编程语言中常常含有通用的概念，例如在Java中有Class类用于获取对类的定义，以及有Method类用于获取函数的定义。

Java程序员可以通过Class类的getClass()函数获取类的反射，类的反射中会包含类名、引用包名等相关定义类信息，这些信息可被看作Java的元数据。

MySQL元数据是关于数据库本身的数据，用于描述数据库的结构和属性。

3.3.1表的元数据查询

前文提到过MySQL依靠information实例库存储MySQL有关库和表的信息，例如information_schema.tables表存储针对表的定义，包括表的名称、表的总行数、表的创建时间、表的最后修改时间等相关内容。

依靠information_schema.table_constraints表存储针对表的约束进行查询。

后续在学习MySQL调优的过程中要学会详细查看information_schema实例库存储的各个表，information_schema实例库中部分涉及MySQL调优的表如表32所示。


表32体验特性结果集



information_schema中部分涉及调优的表主要存储内容



files存储表空间数据的文件
innodb_buffer_pageInnoDB缓冲池中的页面
innodb_buffer_page_lruInnoDB缓冲池中页面的LRU排序
innodb_buffer_pool_statsInnoDB缓冲池统计
innodb_cmp压缩InnoDB表相关的操作的状态
innodb_cmp_per_index压缩InnoDB表和索引相关的操作状态
innodb_cmp_per_index_reset压缩InnoDB表和索引相关的操作状态
innodb_cmp_reset压缩InnoDB表相关的操作的状态
innodb_cmpmemInnoDB缓冲池中压缩页面的状态
innodb_cmpmem_resetInnoDB缓冲池中压缩页面的状态
innodb_ft_configInnoDB表全文本索引和相关处理的元数据
innodb_metricsInnoDB性能信息
innodb_trx活跃的InnoDB事务信息
innodb_temp_table_info关于活动用户创建的InnoDB临时表的信息
innodb_sys_indexesInnoDB索引元数据
optimizer_trace优化器跟踪活动生成的信息
partitions表格分区信息
processlist有关当前执行线程的信息
statistics表索引统计


在实际工作中若只是想知道简单的信息，则只需使用show关键字，毕竟show关键字运行后所查询的仍然是information_schema实例库中的众多表，其中包括了information_schema.tables和information_schema.engines等。

关键字的出现其实并不意外，例如show类关键字帮助简化查询元数据、新增数据库时使用的create关键字、删除数据库时使用的drop关键字，本质上都是在操纵information_schema实例库下的各个表。

因为元数据表互相之间的关系过于复杂，无法达到让人在其中快速地做出对表的DLL操作，所以出现了众多简化操作的关键字，用于辅助日常工作。

不过一旦涉及了数据库性能的优化，仍然需要数据库管理员对MySQL基础的各个表有足够的了解，尤其是MySQL的information_schema实例库中的InnoDB引擎系列表、information_schema实例库中的线程池系列表、information_schema实例库中的连接控制表等重要性能指标表，能够快速地定位数据查询缓慢、数据库内存占用过高、大数据量无法导出或迁移等问题。

MySQL查看表的元数据的SQL语句如下： 


//3.3.1 表的元数据查询.sql



select 

table_name as 表名称,

table_rows as 表总行数,

create_time as 表创建时间,

update_time as 表最后修改时间,






table_collation as 表排序规则,

engine as 表引擎

from 

information_schema.tables 

where 

table_schema = 'learnSQL2';


运行后，结果集如图314所示。




图314查看表元数据的结果集


在information_schema实例库中的tables表中，除了图314中展示的数据之外，存储着有关于用户自行创建的表的其他元数据内容，tables表的字段释义如表33所示。


表33information_schema.tables表的字段释义


列值释义


table_catalog数据表登记目录
table_schema数据表所属的数据库名
table_name表名称
table_type表类型［system view|base table］
engine表引擎［MyISAM|CSV|InnoDB］
version版本
row_format行格式［DEFAULT | FIXED | DYNAMIC | COMPRESSED | REDUNDANT | COMPACT］
table_rows表总行数
avg_row_length平均长度
data_length数据长度
max_data_length最大数据长度
index_length索引长度
data_free空间碎片
auto_increment做自增主键的自动增量当前值
create_time表创建时间
update_time表最后修改时间
check_time表的检查时间
table_collation表排序规则
checksum校验和
create_options创建选项
table_comment表的注释、备注


base table为基础表，是用户在create table时所创建的实体表，称为base表，也可称为基表。

system view为用户在create view时所创建的视图。


注意： view视图不仅在此处有存储，后文会继续提到在information_schmea.view表中也有相应存储。



3.3.2表信息中的row_format字段

MySQL中row_format(行格式)用于设置表的行存储格式。存储格式决定了其物理存储方式。物理存储方式会影响DML(数据库操纵)与DQL(数据库查询)的性能。

1. 行格式简述

InnoDB引擎支持4种行格式，分别为redundant(冗余型行格式)、compact(紧凑型行格式)、dynamic(动态型格式)和compressed(压缩型行格式)，其4种行格式简明释义如表34所示。


表34InnoDB中4种行格式的简明释义


行格式是否紧凑存储是否可变
长度存储大索引前
缀支持是否支持压缩支持的表空间




redundant否否否否系统、通用、独立表空间
compact是否否否系统、通用、独立表空间
dynamic是是是否系统、通用、独立表空间
compressed是是是是通用、独立表空间


redundant行格式兼容MySQL旧版的特性，与redundant行格式相比，compact行格式减少了20％的存储空间，但代价是增加了某些操作时的CPU占用率。若服务器的压力来自缓存命中率或者磁盘IO限制，则compact的行格式读取速度会更快。若查询压力来自CPU，则使用compact的行格式时速度会更慢。

dynamic行格式提供了与compact行格式相同的存储特性，但为大型的可变长列(例如varchar、varbinary、blob和text等类型)增强了存储性能，并支持大型的索引键前缀。dynamic行格式最多支持3072字节的索引前缀。

所谓大型索引键前缀指的是给text文本等大型字符串设置的索引，该索引所需存储空间同样较大。

compressed行格式提供了与dynamic行格式相同的存储特性和功能，但增加了对表和对索引数据的压缩支持。

目前MySQL 8.0推荐使用dynamic动态行格式和compressed压缩行格式。

在表设计上，若一张表里面不存在varchar、varbinary、blob和text等相关变形字段，则该表被称为静态表，每条记录所占用的字节一样。静态表的优点是读取快，缺点是浪费额外一部分空间。

若一张表里面存在varchar、varbinary、blob和text等相关变形字段，则该表被称为动态表，该表的row_format是dynamic，其每条记录所占用的字节均是动态的。

因为动态表的优点是节省空间，所以搜索查询量大的表一般以空间来换取时间，即被设计成静态表。缺点是增加读取的时间开销。

2. 查询默认行格式

MySQL 8.0默认行格式为dynamic，可通过show关键字进行查看，SQL语句如下： 



图315查看MySQL 8.0的默认行

格式结果集




show variables like 'innodb_default_row_format';

#上下等价

select @@innodb_default_row_format;


运行后，结果集如图315所示。

3. 修改默认行格式

修改默认行格式，SQL语句如下： 


set global innodb_default_row_format=DYNAMIC;


运行后，结果如下： 


Query OK, 0 rows affected (0.01 sec)


4. 修改默认行格式的注意事项

因为compressed行格式所支持的表空间不同，所以不能将compressed设置为默认行格式，只能在create table和alter table语句中出现。

若强行将compressed行格式设置为MySQL默认变量，则报错如下： 


ERROR 1231 (42000): Variable 'innodb_default_row_format' can't be set to the value of 'COMPRESSED'


行格式在互相转换时可能会出现更改类型的问题，例如当将fixed格式转换为dynamic格式时可能会将字段的char类型转换成varchar类型。

当将dynamic行格式转换成fixed行格式时可能将字段的varchar类型转换成char类型。

建议在初始化表设置行格式之后，尽量不要对其进行修改，强行修改可能对已存储的数据造成不可逆的损害。

5. 创建表时设置行格式

创建表时，同样可以单独给某张表设置行存储格式，SQL语句如下： 


create table test_row_format(

T1 varchar(255),

T2 INT

)row_format=COMPRESSED;


运行后，结果如下： 


Query OK, 0 rows affected (0.01 sec)


创建表后，可查看当前表的存储格式，可参考3.2.1节，也可使用如下SQL语句： 


show table status where name = 'test_row_format';


6. 修改表的行格式

使用alter table语句创建新列的SQL，SQL语句如下： 


alter table test_row_format row_format=dynamic;


运行后，结果如下： 


Query OK, 0 rows affected (0.02 sec)

Records: 0Duplicates: 0Warnings: 0


7. 修改表追加字段时设置行格式

使用alter table语句创建新列，SQL语句如下： 


alter table test_row_format add column(T3 int),row_format=DYNAMIC;


运行后，结果如下： 


Query OK, 0 rows affected (0.02 sec)

Records: 0Duplicates: 0Warnings: 0


8. 其他相关注意事项

redundant行格式和compact行格式支持最大767字节的最大索引前缀长度，而dynamic行格式和compressed行格式支持最大3072字节的最大索引前缀长度。简而言之,dynamic行格式和compressed行格式对大字段的索引支持力度要大于redundant行格式和compact行格式。

compressed行格式的表空间支持力度不如其他3种，若一定要修改行格式，则需先确定好所有已存在数据的表空间、索引最大长度等方面内容皆兼容两种行格式，在此前提下才能进行转换。

在MySQL集群复制的环境下，若InnoDB的row_format变量在主服务器上被设置为dynamic行格式，并且在从服务器上被设置为compact行格式，则某些DDL(数据库定义)语句在主服务器上成功，但在从服务器上可能失败。

因为MySQL集群复制的环境必须保证，所以主服务器节点master和从服务器节点slave的row_format要保持一致。

除了DDL语句之外，导入不同的数据、不同的数据结构都有异常的风险。届时各节点数据结构不统一、数据不统一将极难对数据库进行处理和维护，所以初始化时一定要检查是否一致。

3.3.3表信息中的data_free字段

data_free字段释义： 每当MySQL数据库使用delete语句删除一行内容时，在MySQL底层中该段空间就会被留空，而在一段时间内的大量删除操作会使留空的空间变得比存储列表内容所使用的空间更大。

若进行新的插入操作，则MySQL将尝试利用留空的区域，但仍然无法将其彻底占用，所以此刻可使用optimize关键字或alter table关键字对其进行优化。

3.3.4MySQL各表占用磁盘空间计算方式

MySQL占用磁盘空间为information_schema.tables表中Data_length 数据长度+ Index_length 索引长度，具体公式如下： 


(Data_length + Index_length ) /1024 /1024 = 磁盘占用空间(MB)


在实际工作中查询MySQL某库实际占用空间的SQL语句如下： 


//代码位置： 全书代码/3.3.4 MySQL各表占用磁盘空间计算方式.sql



select 

table_name,

table_rows,

data_length + index_length as length,

concat(round((data_length+index_length)/1024/1024,2),'mb') as data 

from 

information_schema.tables 

where 

table_schema='esif'

order by 

length desc


上述SQL的具体函数后文均会有逐步解答，在此只展示一下语句。为了体现出效果，笔者在自身公司测试环境下查询公司的esif实例库，运行后，结果集如图316所示。




图316查看MySQL各表占用磁盘空间的结果集


以MySQL各表占用磁盘空间计算方式进行演化，自然可以继续写出“查看MySQL各库占用磁盘空间计算方式”“MySQL单个库占用磁盘空间计算方式”“MySQL所有库占用磁盘空间计算方式”等相关内容。


注意： 不要直接修改information_schema.tables表。



3.3.5利用optimize关键字优化空间碎片

optimize关键字使用的前提是需要开启数据库安全模式及old_alter_table系统变量。old_alter_table参数代表服务器不会使用处理alter table操作的优化方法。

optimize关键字和alter table操作的本质都是MySQL数据库会先锁定某张表，然后对该表进行复制，再迅速将之前的表删除，对第2张表进行改名。

简而言之，程序员可以通过alter table的操作优化方法复制基表，复制出来新的基表自然不含空间碎片。

optimize关键字的原理与alter table的原理类似，在官网的表述中若optimize table table.name命令执行失败，则可尝试执行alter table tests engine='innodb';命令。

查询系统变量中的old_alter_table参数，SQL语句如下： 


show variables like '%old_alter_table%'


运行后，结果集如图317所示。

因为MySQL系统变量中的old_alter_table并未开启，所以可以使用MySQL中的set关键字设置数据库的临时变量，开启old_alter_table参数的SQL语句如下： 


set @@old_alter_table = ON;


运行后，结果如下： 


Query OK, 0 rows affected (0.01 sec)


再次查询old_alter_table参数，结果集如图318所示。




图317查看old_alter_table参数是否开启的结果集




图318再次查看old_alter_table参数是否开启的结果集




查看tt表的相关信息语句，SQL语句如下： 


//3.3.5 利用optimize关键字优化空间碎片.sql



select

table_name as 表名称,






table_rows as 表总行数,

create_time as 表创建时间,

update_time as 表最后修改时间,

table_collation as 表排序规则,

engine as 表引擎,

data_free as 空间碎片

from 

information_schema.tables 

where 

table_schema = 'learnSQL2' 

and 

table_name = 'tt';


运行后，结果集如图319所示。




图319insert之后的tt表相关数据的结果集


可以看到单纯使用insert关键字，无论新增了多少数据都不会产生空间碎片，在insert大量数据之后可直接进行删除。删除tt表的SQL语句如下： 


delete from tt;


运行后，结果如下： 


Query OK, 1 row affected (0.37 sec)


为了方便测试，虽然tt数据只插入了1条数据，但是用的是longtext数据类型，该条数据中的内容特别多，删除tt表全部的新增数据之后，再次执行查看tt表的相关信息的SQL语句，结果集如图320所示。




图320删除之后的tt表相关数据的结果集


此时可观察到tt表中含有大量的空间碎片。使用optimize关键字对表空间碎片进行优化，SQL语句如下： 


optimize table tt;


运行后，结果集如图321所示。



图321运行optimize关键字的结果集


此处出现了提示Table does not support optimize, doing recreate + analyze instead，提醒用户该表不支持优化，而应执行重新创建+分析。该提示属于提示/警告的类型，不属于报错，再次执行查看tt表的相关信息的SQL语句，运行后结果集如图322所示。




图322优化之后tt表相关数据的结果集



可查看最终tt表的空间碎片归零了。整体运行过程如图323所示。




图323整体运行过程


若该数据库为MySQL导入的data文件夹或者导入的数据库文件，则可能会导致optimize关键字执行时失败，该空间碎片可能永久无法删除或每次只能删除额外的一部分，例如空间碎片50000+时只能删除其中10000+的碎片。

在进行实际操作时，可先查看MySQL的data文件夹大小，删除后可发现该文件夹明显缩小了。

3.3.6查看表中的约束

MySQL查看表的相关约束的SQL语句如下： 


//3.3.6 查看表中的约束.sql



select

tc.constraint_catalog as 约束所属的目录,






tc.table_schema as 约束所属的数据库名称,

tc.table_name as 表的名称,

constraint_type as 约束类型

from

information_schema.table_constraints tc

where

tc.constraint_schema = 'learnSQL2';


运行后，结果集如图324所示。




图324查看表的相关约束结果集


MySQL在information_schema.table_constraints表中存储了表的相关约束信息，此信息可通过show关键字便捷地进行查找。

例如使用如下show关键字的SQL语句可以查询dept表中的约束，SQL语句如下： 


show index from dept;


运行后，结果集如图325所示。




图325查看dept表中约束的结果集


show 关键字在展示不清时，可以使用＼G语句进行输出，SQL语句如下： 


show index from dept ＼G;


运行后，结果集如图326所示。



图326分行输出的结果集


针对show index字段进行释义如表35所示。


表35show index的字段释义


列值释义


table表名称
non_unique若索引不能包含重复项，则为0，若可以包含重复项，则为1
key_name索引的名称。若索引是主键，则名称始终为primary
seq_in_index索引中的列序号，以1开头
column_name列的名称
collation如何在索引中对列进行排序。可以有值a(升序)或null(未排序)
cardinality估计索引中唯一值的数量，但是该值不一定准确
sub_part索引前缀。若列仅部分索引，则索引字符的数量； 

若整个列已索引，则为null
packed指示按键的包装方式。若不是，则为null
null若列可能含有null值，则输出yes，若不含null值，则输出''
index_type使用的索引方法(btree、fulltext、hash、rtree)
comment有关其自身列中未描述的索引的信息，例如若索引被禁用，则返回disabled
index_comment创建索引时，使用comment属性为索引提供的任何注释



3.4列的元数据

MySQL查看列的元数据，SQL语句如下： 


//3.4 列的元数据.sql



select

c.table_name as 所属表名称,

c.column_name as 列的名称,

c.column_default as 列的默认值,

c.is_nullable as 是否可空,

c.data_type as 数据类型,

c.column_type as 列的数据类型,

c.column_key as 索引类型,

c.column_comment as 列的注释

from

information_schema.columns c

where

c.table_name = 'dept';#此例子查询的dept表中的列数据


运行后，结果如图327所示。




图327查看列元数据的结果集


在MySQL中含有普通索引index、默认约束default、唯一约束unique、检查约束check、非空约束not null、主键约束primary key、外键约束foreign key。

在column_key索引类型字段中，若column_key为空，则该列要么没有索引，要么仅作为多列非唯一索引中的辅助列进行索引。

若column_key是pri，则该列是primary key或多列primary key中的列之一。

若column_key是uni，则该列是unique索引的第1列。unique索引允许多个null值，但可以通过检查null列来判断该列是否允许null。

若column_key是mul，则该列是非唯一索引的第1列，其中允许在列中多次出现给定值。

若多个column_key值适用于表的给定列，则column_key将按pri、uni、mul的顺序显示具有最高优先级的值。

若unique索引不能包含null值，并且表中没有primary key，则可以将其显示为pri。

若几列形成复合unique索引，则unique索引可能会显示为mul。

3.5用户权限的元数据

MySQL依靠mysql.user表查询用户权限的元数据。因为mysql.user表中含有的列过多，所以本节分批对其进行讲解。

除了mysql.user表存储着用户的相关权限信息之外，procs_priv表存储着存储过程和存储函数的操作权限，db表存储着实例库的操作权限，mysql.tables_priv表存储着对表操作的权限，mysql.columns_priv表存储着对列操作的权限。

3.5.1查询当前MySQL中含有哪些用户

通过mysql.user表可查询当前MySQL中含有哪些用户，SQL语句如下： 


//3.5.1 查询当前MySQL中含有哪些用户.sql



select

u.host as 地址,

u.user as 用户名,

u.plugin as 密码加密方式,

u.authentication_string as 被加密后的密码

from

mysql.user u;


运行后，结果集如图328所示。




图328当前MySQL用户的结果集



host地址指该用户被授权访问的地址。此处的localhost代表只有localhost地址的人才能登录该账户。若此处被写成192.168.1.1，则代表只有IP地址为192.168.1.1的人才能登录该账户。若此处被写成0.0.0.0，则代表任意IP地址的人都可以登录该账户。

MySQL数据库在创建时默认含有4个用户，分别是mysql.infoschema、mysql.session、mysql.sys、root。root为最高权限的管理员，其余3种用户是MySQL防止元数据被轻易篡改而默认创建的用户。

mysql.session用户用于内部访问服务器，由于该用户已被锁定客户端，所以无法连接，通过mysql.session用户可以查看MySQL数据库中正在运行的所有进程。

在实际工作中有将root账户删除的做法，因为root账户权限过大，所以被入侵服务器时首先会通过root账户进行扫描。解决方案为将root账户删除或重命名，并建立一个特权账户进行管理，删除root账户是一种常见的数据库安全运维方式。

MySQL版本不同，密码的加密方式也不同。MySQL 8.0 调整了账号认证方式，把 caching_sha2_password 插件认证方式作为默认首选，这就导致很多需要使用密码登录的客户端登录MySQL 8.0时发生错误，此时，需要将caching_sha2_password 加密插件改成mysql_native_password加密插件这样客户就可正常使用了，SQL语句如下： 


ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root123';



有关于user的plugin加密插件不用过多关注，通常只有MySQL第1次登录时，或者第1次创建账号时需要注意一下。

在连接错误或者登录错误时都会含有加密插件错误的提示，根据提示可自行查询或更改。

3.5.2用户的操作权限

通过mysql.user表可查询当前MySQL中用户含有哪些权限，SQL语句如下： 


select * from mysql.user;


查看用户权限中列的释义表如表36所示。


表36查看用户权限列的释义表


列值字段类型是否为空默认值说明




select_privenum('N','Y')NON是否可通过SELECT命令查询数据
insert_privenum('N','Y')NON是否可通过INSERT命令插入数据
update_privenum('N','Y')NON是否可通过UPDATE命令修改现有数据
delete_privenum('N','Y')NON是否可通过DELETE命令删除现有数据
create_privenum('N','Y')NON是否可创建新的数据库和表
drop_privenum('N','Y')NON是否可删除现有数据和表
grant_privenum('N','Y')NON是否可将自己的权限再授予其他用户
references_privenum('N','Y')NON是否可创建外键约束
index_privenum('N','Y')NON是否可对索引进行增、删、改操作
alter_privenum('N','Y')NON是否可重命名和修改表结构
execute_privenum('N','Y')NON是否可执行存储过程
repl_client_privenum('N','Y')NON是否可确定复制从服务器和主服务器的位置
create_view_privenum('N','Y')NON是否可创建视图
show_view_privenum('N','Y')NON是否可查看视图
event_privenum('N','Y')NON是否可创建、修改和删除事件
trigger_privenum('N','Y')NON是否可创建和删除触发器
…………………………

MySQL 8.0的user表共51个参数，涉及账户的权限、安全、资源控制等内容


3.5.3表的操作权限

在mysql.tables_priv表中存储着对表操作的权限，查询mysql.tables_priv表的语句如下： 


select * from mysql.tables_priv;


运行后，结果集如图329所示。



图329表权限的查询结果


mysql.tables_priv表的字段说明如表37所示。


表37mysql.tables_priv表的字段说明


列值释义


host主机名
db数据库实例
user账户
table_name表名
grantor哪个账户授予的权限
timestamp授权时间
table_privtable_name库中的哪张表
column_privtable_priv表的所有列被赋予了哪些权限


3.5.4列的操作权限

在mysql.columns_priv表中存储着对列操作的权限。查询mysql.columns_priv表的语句如下： 


select * from mysql.columns_priv;


mysql.columns_priv表的字段说明如表38所示。


表38mysql.columns_priv表的字段说明


列值释义


host主机名
db数据库实例
user账户
table_name表名
column_name列名
timestamp授权时间
column_privtable_priv表的column_name列被赋予了哪些权限