009、行格式、约束

tps：每秒钟对事务的处理

qps：每秒钟对数据库的请求数

查看表的信息：

mysql> use test;
Database changed

mysql> show table status like 'su' \G;
*************************** 1. row ***************************
           Name: su
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 50256
 Avg_row_length: 52
    Data_length: 2637824
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 50001
    Create_time: 2021-04-04 19:49:23
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

通过查看表的信息可以计算碎片大小：Data_length+Index_length-Rows*Avg_row_length

还可以看出行的格式：Row_format: Compact

默认行格式：Compact

MySQL的行格式

Innodb存储引擎是面向行的,也就是说数据的存放按行进行存放，每个页存放的行记录也是有定义的，最多可存放7992行。面向行（row-oriented）的数据库。

也有面向列的数据库，mysql infobright存储引擎就是按列来存放数据的，这对于数据仓库下的分析类sql语句的执行以及数据压缩很有好处。

INNODB支持的几种行格式：

• REDUNDANT
• COMPACT
• COMPRESS
• DYNAMIC

Mysql 记录是以行的形式存储，意味着页中保存着表中的一行行的数据。

Mysql5.1时，innodb存储引擎提供了compact和redundant两种格式来存放行记录数据。Redundant是为兼容之前版本保留的，是比较老的数据格式。

Version 5.6 默认使用 Compact，Version 5.7 默认使用Dynamic。Compact和Dynamic应用较广泛；

查看表的行格式：

mysql> show table status like '%t3%' \G;
*************************** 1. row ***************************
           Name: t3
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 4
    Create_time: 2021-03-31 17:01:10
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

1、REDUNDANT行格式特点：

• 每个页包含6字节ROWID，用于指向下一个连续的页，并可用于实现行锁。
• 聚集索引中包含所有字段，并且包含额外的6字节TRX_ID，和7字节ROLL_PTR(回滚标识)。
• 如果没有主键，则使用隐含的6字节ROWID作为主键。
• 行记录中存储指向全部字段的指针，长度小于128字节时，该指针占用1字节，否则需要2字节。
• 类似CHAR的定长类型字段，也采用固定宽度存储，并且不删除末尾的空格(占无用空间) *
• 类似VARCHAR变长类型字段中NULL不会占用实际存储空间，而CHAR定长类型字段中，NULL则占用相应的字节数。

2、COMPACT行格式特点：

• 每个行记录都有5字节长度域，用于指向下一个连续的页，并可用于实现行锁。
• 有个变长域，存储页中有多少可能为NULL的字段数，长度为CELING(N/8)字节，同时也需要用1-2字节存储变长字段所需长度。
• 所有非NULL的变长字段，用1-2字节存储其长度信息。
• 行记录头部域之后，紧跟着就是所有非NULL的字段。
• UTF8字符集的CHAR(N)字段，优先删除末尾空格，尝试用N字节存储，并且再预留N个字节用于后续数据更新，避免产生碎片。

COMPACT相比REDUNDANT约可节省20%左右，COMPRESS相比COMPACT约可节省50%左右，但会导致CPU消耗增加，TPS可能只有原来的10%，一般不使用COMPRESS格式。

行溢出数据

Innodb存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外，即为行溢出数据。

Compressed与Dynamic行记录格式：

DYNAMIC(动态存储)、COMPRESS格式中，长字段只存储20字节，其余全部off-page(页迁移，实际长度40字节以内的，则不会发生off-page，哪怕是text/blob)，所以不建议使用这两个格式。发生off-page（很影响数据库性能）时，每个字段占用一个page。

Compressed行记录格式的特点：存储在其中的行数据会以zlib的算法进行压缩，因此对于blob,text,varchar这类大长度类型的数据能进行非常有效的存储。

Innodb_File_format（innodb文件格式）:

1.antelope

2.Barracuda

• Antelope下支持compact和redundant两种行记录格式
• Barracuda下支持compressed和dynamic两种行记录格式

查看文件格式：

mysql> show variables like 'innodb_file_format';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.00 sec)
--mysql 5.7默认使用Barracuda和DYNAMIC格式

不管是行格式还是文件格式，不用太过关注。重点是让每个字段设计的尽可能高效、紧凑，不要有太多空间浪费就好了。

MySQL数据库的页

同大多数数据库一样，innodb有页page的概念,页是innodb磁盘管理的最小单位。

常见的页类型有：

1.数据页（B-tree node）

2.Undo页（undo log page）

3.系统页（system page）

4.事务数据页（transaction system page）

5.插入缓冲位图页（insert buffer bitmap）

6.插入缓冲空闲列表页（insert buffer free list）

7.未压缩的二进制大对象页

8.压缩的二进制大对象页

MySQL数据库的页由页头、页身、页尾组成。页在磁盘中叫page，在内存中叫buffer。

页头包含：指针的信息，事务的信息，锁的信息，checksum值(确保页的值是否完整)。

页尾：也包含checksum值，页头页尾checksum值一致，才能确保page是完整的。

页身：存储行记录，一个页身最多存储7992行。

知识点：

B+树索引本身并不能找到具体的一条记录，B+树索引能找到的只是该记录所在的页，数据库把页载入内存，然后通过page directory 再进行二叉查找，只不过二叉查找的时间复杂度很低，同时内存中的查找很快，因此通常我们忽略了这部分查找所用的时间。

约束的介绍

数据库完整性：

关系型数据库系统和文件系统的一个不同点是：关系数据库本身能保证存储数据的完整性，不需要应用程序控制，而文件系统一般需要在程序段进行控制。所有的关系型数据库都提供约束机制，约束提供了一条强大而简易的途径来保证数据库中的数据完整性。

数据完整性有三种形式：

1.实体完整性

保证表中有一个主键，在innodb存储引擎表中,我们可以通过定义primary key或者unique key 约束来保证实体的完整性。

主键的三个属性：非空、唯一、自增

2.域完整性

保证数据的值满足特定的条件，在innodb存储引擎表中，域完整性可以通过外键，还可以用default约束。

3.参照完整性：

保证两张表之间的关系，定义外键来强制参照完整性。

对于innodb存储引擎而言，提供了5种约束：

1. Primary key
2. Unique key
3. Foreign key
4. Default
5. Not null

注意：MySQL数据库不建议使用外键。

实验

1、建表

mysql> use test;
Database changed
mysql> create table t(
    ->   a int(11) not null auto_increment,
    ->   b varchar(20) default null,
    ->   primary key(a),
    ->   key b (b)  --定义b列为索引
    -> ) engine=innodb auto_increment=300 default charset=utf8; --自增值从300开始，字符集设置为UTF8
Query OK, 0 rows affected (0.05 sec)

2、插入数据

mysql> insert into t(b) values('aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(b) values('bb');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(b) values('cc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+-----+------+
| a   | b    |
+-----+------+
| 300 | aa   |
| 301 | bb   |
| 302 | cc   |
+-----+------+
3 rows in set (0.00 sec)

3、删除重新插入

mysql> delete from t;
Query OK, 3 rows affected (0.01 sec)

mysql> insert into t(b) values('aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(b) values('bb');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(b) values('cc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+-----+------+
| a   | b    |
+-----+------+
| 303 | aa   |
| 304 | bb   |
| 305 | cc   |
+-----+------+
3 rows in set (0.00 sec)
--观察a列的值

4、查看建表语句，注意自增开始的值

mysql> show create table t \G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=306 DEFAULT CHARSET=utf8  --自增开始的值发生了变化
1 row in set (0.00 sec)

5、使用truncate命令清空表数据，再观察建表语句

mysql> truncate table t;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t \G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  --自增开始的值被清空了
1 row in set (0.00 sec)

6、插入数据

mysql> insert into t(b) values('aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(b) values('bb');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(b) values('cc');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 | aa   |
| 2 | bb   |
| 3 | cc   |
+---+------+
3 rows in set (0.00 sec)
--可以观察到自增值从1开始了

以上实验得出结论：

1、truncate会清除自增字段的初始值，使自增字段从1开始自增，delete不会。

2、truncate会回收表空间

3、truncate速度要快于delete

在生产环境中建议使用delete删除表数据，不会清除自增值。

修改表的自增初始值：

mysql> alter table t auto_increment=500;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t \G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

查看自增长度设置：

mysql> show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |  --自增步长
| auto_increment_offset    | 1     |  --自增初始值
+--------------------------+-------+
2 rows in set (0.00 sec)

约束的创建和查找

对于约束的建立，可以在表建立时就进行定义，也可以在之后使用alter table 命令来进行创建。对于unique key的约束，我们还可以通过create unique index 来进行建立。对于主键约束而言，其默认约束名为primary key，而对于unique key 约束而言，默认约束名和列名一样.

查看约束：information_schema下的表table_constraints

mysql> use information_schema;
Database changed
mysql> desc table_constraints;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from table_constraints where TABLE_SCHEMA='test';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | PRIMARY         | test         | su         | PRIMARY KEY     |
| def                | test              | PRIMARY         | test         | t          | PRIMARY KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)

约束和索引的区别？？？

约束更像是个逻辑概念，用来保证数据的完整性，而索引是一个数据结构，有逻辑上的概念，在数据库中更是一个物理存储方式。

来自为知笔记(Wiz)

附件列表