Innodb 文件结构分析 (三) row format 以及字符集

从两个参数入手:

mysql> show variables like '%row_format%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like '%file_format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+
3 rows in set (0.00 sec)

mysql> 

这里是5.7 的版本,在 8 的版本里 innodb_file_format 已经废弃,这样举例是把所有的格式都做下总结;首先看文件的格式,在mysql前后出现过两种文件格式:

antelope: 是早期的Mysql文件格式,它支持两种方式的行记录格式compact 和redundant ;

barracude:是后期新的文件格式,它支持两种行记录格式分别是compress和dynamic;其中compress 参数只能用于在创建表时;而不能指定为系统的行格式,因为系统表空间并不支持压缩存储;

从数量上说总共有4种 row 格式,实际是dynamic和compact的区别非常小,所以也可以看做是三种格式,首先来看看 redundant格式:

  • Redundant

row1

如图所示一个完整的行记录,主要包含如下部分:

offset array:此部分为变长,其作用是指出从 origin 处开始的,列的偏移量;每个列的偏移量由1byte或者2byte表示;此数组列表中的每个元素的具体长度由 6-byte bit field 中的1byte_offs_flag 值决定;

6-byte bit field,又称为REC_N_OLD_EXTRA_BYTES,是固定长度 6byte,其主要记录列的相关信息,每个bit位的具体意思如下:

row2

hidden field 实际由三个部分组成:primary key/DB_ROW_ID,如果是后者为定长 6bytes;DB_TRX_ID 事务id,定长6byte,DB_ROLL_PTR 回滚段地址,定长7bytes;

其余为具体的表定的其它列,这里要说明的是如果表没有加主键,那么系统会自动生成 6byte的 DB_ROW_ID作为主键,那么Column1 就是行的第一列,如果表在创建时添加了主键,那么此处Column1就是行记录的第二列;

  • compact/dynamic

compact 和dynamic 格式的行记录基本是相同的,只是在不同的数据类型存储上,dynamic有更优秀之处,其主要组成部分为:

row4

offset array:此处为变长部分,记录具体列的长度,数值的每个元素长度为1byte或者2byte,具体是由列的类型定义长度与实际存储长度决定的,详细可以参考:http://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

Null bit field:每个bit代表一个null 列,所以null 列的个数决定了此部分的长度;null 列不占用空间;

5byte bit field:也就是REC_N_NEW_EXTRA_BYTES 

row5

其余部分与前者同;

dynamic 与compact的两种格式的区别在于:blob 的存储上,dynamic 只存储前20byte 在row 中,而compact 存储前768 byte; 这样dynamic格式更灵活;对于多列也有很多好处;

字符集实际就是 字符能够被计算机软硬件识别的组织方式;不同的字符集的组织形式是不一样的,比如ASCII,是每个字符一个byte,用其中的7bit进行表示,以0–127 代表一个字符,而Unicode 是用整数,两byte –16bit代表一个字符的,所以了解字符集对于数据的解析是非常有好处的;由于不同的字符集导致字符占用的实际存储长度也不同,所以在进行数据的解析,迁移等等操作时,需要更好的考虑字符集带来的影响;

Innodb 文件结构分析 (二) – 数据存储类型

在Mysql当中,数据类型可以分为三大类:数值类型、日期和时间类型、字符类型;每个类型又可以详细划分为数个详细的子类,

需要注意的是有些类型是底层的数据类型的别名,举个例子:

mysql> create table dtype (id bool);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table dtype \G
*************************** 1. row ***************************
       Table: dtype
Create Table: CREATE TABLE `dtype` (
  `id` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 

从这里可以看到底层的存储仍然是 tinyint 的;下面详细看看各个具体的分类与存储方式:

数值

dt1

在数值类型当中需要注意的是float 类型,其长度是4byte 或者8byte,如果是Float(x)x>24则为8byte否则为4byte,如果是float(x,y) 则为4byte;这里把bit放入到数值类型当中,是因为bit的底层存储是二进制的,就是0和1组成的数字,所以我放到这里,是自己的一个习惯性做法不通用;知道了每个数值类型的长度那么在进行数据文件的的行解析时,就能判断每个类型的长度,这样就比较容易直接转换数据;那么数值在底层是如果转换存储的呢? 以tiny int 为例子,其长度是1byte,而1byte 是8bit,bit从存储是二进制的,也就说1一个byte 可以存储的最大的值为 1111 11111,转换为10进制就是 255 ,也就是0–255 ,如果按照这种计算方式,存储的全部为正数这种情况是mysql unsigned方式,如果有负数呢,如果是有正负数的情况,此时会使用 第一个 bit 作为正负的标志位,正数为1,负数为0,所以此时有7个bit位是作为数值的存储的,也就是 +-127;

数值的存储方式大多如此,那么数值类型当中有特殊情况的是:decimal类型,在之前的版本中decimal实际是以字符串的形式进行存储的在大当前版本中是binary方式,同时需要注意如何计算decimal的长度;decimal 的整数部分每9个数字占用4个字节,小数部分也是如此,不足9个数字的按照下面的方式进行计算:

dt2

因为decimal 是由定义长度决定存储长度的,所以在文件解析时需要特别对待;其它的类型基本都是固定长度;

日期时间:

dt4

日期与时间类似是固定长度的,但是存储长度还是有一定的变化的,如上图图所示:

字符类型包括:

dt5

对于字符类型来说需要,明确binary strings 和character strings 的区别;

在myql当中还包含其他的类别的数据类型,这里不一一介绍,主要介绍的是常用的类型;

从上面的分析可以看出,知道数据类型的定义后,就可以清楚的判断出row 在存储时,每个列的长度,这样对于数据的解析有很好的帮助,如果不知道类的类型,那么有可能只能去猜测,这样就会存在各种误差;

Mysql 中Dtrace的应用

Dtrace是一个非常好的动态跟踪工具,并且在Mysql当中就是支持Dtrace的;尤其是performance_schema中信息的采集;有关Dtrace的基本知识,语法等可以参考dtrace的官方文档:http://dtrace.org/guide/chp-user.html 初次使用dtrace可能会感觉无从下手,这里分享下自己经验;

注:测试环境为:Mysql 8,MAC os 10.10.5 ;

在mac 中有一个命令 dtruss ,实际它的最终来源是dtrace,需要高权限才能执行;所以对dtrace 熟悉后可以直接使用dtrace;

[HuSi@HuoSi]mysql8 $ sudo dtrace -l -m mysqld 
sudo: cannot get working directory
Password:
   ID   PROVIDER            MODULE                          FUNCTION NAME
dtrace: failed to match :mysqld::: No probe matches description

这里想看看有关Mysql的模块信息,但是你会发现此时什么都没有,下面可以启动mysql 后再检查:

[HuSi@HuoSi]mysql8 $ pgrep -x mysqld 
[HuSi@HuoSi]mysql8 $ ./mysqlstart start
Starting MySQL
 SUCCESS! 
[HuSi@HuoSi]mysql8 $ pgrep -x mysqld
16279
[close_connection(THD*, unsigned int, bool, bool)] connection-done
 5458 mysql16279            mysqld _Z22thd_prepare_connectionP3THD [ha_myisammrg::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)] index-read-row-done
 5479 mysql16279            mysqld _ZN9ha_myisam15index_next_sameEPhPKhj 

----省略N行-----

[Sql_cmd_delete_multi::execute(THD*)] multi-delete-start
 9772 mysql16279            mysqld _ZN14Sql_cmd_update26execute_multi_table_updateEP3THD [Sql_cmd_update::execute_multi_table_update(THD*)] multi-update-done
 9773 mysql16279            mysqld _ZN14Sql_cmd_update26execute_multi_table_updateEP3THD [execute_sqlcom_select(THD*, TABLE_LIST*)] select-start
 9949 mysql16279            mysqld _ZN14Sql_cmd_update23try_single_table_updateEP3THDPb [Sql_cmd_update::try_single_table_update(THD*, bool*)] update-done
 9950 mysql16279            mysqld _ZN7handler13ha_update_rowEPKhPh [handler::ha_update_row(unsigned char const*, unsigned char*)] update-row-done
 9951 mysql16279            mysqld _ZN7handler13ha_update_rowEPKhPh [handler::ha_update_row(unsigned char const*, unsigned char*)] update-row-start
 9952 mysql16279            mysqld _ZN14Sql_cmd_update23try_single_table_updateEP3THDPb [Sql_cmd_update::try_single_table_update(THD*, bool*)] update-start

当Mysql启动后可看到dtrace 可以跟踪的具体函数;实际上在Mysql的官方文档里面归类介绍了可以进行Dtrace跟踪的函数;目前5.7 的文档中是有的,但是8 的文档中还没加入Dtrace的有关内容,但是不影响具体的功能;官方介绍的比较笼统,如果是初学者可能会感到困惑,那怎么具体使用Dtrace呢?下面来看看;比如我想查看跟 parse 有关的函数:

[HuSi@HuoSi]mysql8 $ sudo dtrace -l -m mysqld  | grep parse 
 9784 mysql16279            mysqld _Z11mysql_parseP3THDP12Parser_state [mysql_parse(THD*, Parser_state*)] query-exec-done
 9788 mysql16279            mysqld _Z11mysql_parseP3THDP12Parser_state [mysql_parse(THD*, Parser_state*)] query-exec-start
 9791 mysql16279            mysqld _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx [parse_sql(THD*, Parser_state*, Object_creation_ctx*)] query-parse-done
 9792 mysql16279            mysqld _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx [parse_sql(THD*, Parser_state*, Object_creation_ctx*)] query-parse-start

这里选取一条来说明下:9792 mysql16279 mysqld _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx [parse_sql(THD*, Parser_state*, Object_creation_ctx*)] query-parse-start

需要关注的是:parse_sql(THD*, Parser_state*, Object_creation_ctx*),parse_sql 是函数名称,括号里是相关的参数,比如像看看这个函数具体的做什么:

[HuSi@HuoSi]mysql8 $ sudo dtrace -n 'mysql*:::query-parse-start {printf ("%Y   %s\n ",walltimestamp,copyinstr(arg0));}'
dtrace: description 'mysql*:::query-parse-start ' matched 1 probe

此时数据库没有执行任何的解析所以是没有结果的,下面执行几个简单的sql:

[HuSi@HuoSi]mysql8 $ bin/mysql -uroot -proot 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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> select * from test;
+------+---------------------------------------+
| id   | name                                  |
+------+---------------------------------------+
|    1 | jjjjjj                                |
|   12 | jjjj                                  |
|   19 | uuuuuu                                |
|   19 | uuuuuu-------                         |
..................
.....................
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | rectest1                              |
|   21 | rectest1                              |
|   21 | rectest1                              |
|   21 | rectest1                              |
+------+---------------------------------------+
42 rows in set (0.00 sec)

mysql> 

此时dtrace 显示的数据为: 

[HuSi@HuoSi]mysql8 $ sudo dtrace -n 'mysql*:::query-parse-start {printf ("%Y   %s\n ",walltimestamp,copyinstr(arg0));}'
dtrace: description 'mysql*:::query-parse-start ' matched 1 probe
CPU     ID                    FUNCTION:NAME
  0   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   SELECT DATABASE()
 
  2   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show databases
 
  1   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show databases
 
  1   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show tables
 
  0   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show tables
 
  0   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:45   select * from test
 

在使用Dtrace 时需要注意的一个问题是:copyinstr(arg0) 这里,这个argxxx 这个好理解就是第几个参数,第一个参数是arg0,然后依次的即可,但是如果使用copyinstr,可能会显示乱码等问题,这里可以参考官方文档的第33章节有比较详细的描述;有了上面的例子,其它的mysql的相关函数,基本就这么来就可以,这样就可以能看到你想要的内容了;

关于Dtrace 确实很有用,但是这个不是什么独孤九剑,懂一定有好处,不懂也无害,毕竟使用的地方较少,不能当做一个炫耀的资本,感觉有点类似 oracle bbed似得,只不过 dtrace 使用的范围更广;与源码的结合可以更快的更清晰的掌握相关内容;配合使用Dtrace后,也可以减少学习的枯燥程度;

那么大家可以思考如下的场景:

Mysql监控,常用监控项 OS 层面设置就可以了,如果是db层面的,实际是查询db中的各种信息得来的,如直接使用Dtrace呢,比如想做审计,审计所有解析的 sql,以及解析时间,执行时间等等,用Dtrace 写个脚本然后直接发送给监控系统;还有DB 竞争分析等等;

最后推荐一篇文章:

http://dtrace.org/blogs/brendan/2011/06/23/mysql-performance-schema-and-dtrace/