一：innodb_file_format参数

在 innodb 1.0.6版本之前，innodb文件格式innodb_file_format只有 Antelope(Antelope 文件格式支持Redundant，Compact两种格式来存放行记录，Redundant是为了兼容之前版本而保留的。在mysql 5.1版本中，默认设置为Compact，用户可以通过 show table status like 'table_name'来查看表使用的行格式row_format)

从innodb 1.0.6开始引入了新的文件格式 Barracuda 在原来的基础上(Antelope)新增了Dynamic和Compressed两种行格式。

二：innodb_file_format如何使用

  一般， innodb_file_format 在配置文件中指定；row_format则在创建数据表时指定：

CREATE TABLE test2 (column1 INT PRIMARY KEY) 
ENGINE=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4; 

三：innodb_file_format = Barracuda

在指定innodb_file_format = Barracuda时，建议配合如下参数一起使用

innodb_file_format_max = Barracuda
innodb_strict_mode = 1
innodb_file_per_table = 1

因为system tablespace使用的是Antelope文件格式，如果不指定innodb_file_per_table = 1(5.6.6是默认开启的)，那么innodb表被存储在system tablespace,这时在建表的时候无法使用Dynamic和Compressed两种行格式(如果设置了innodb_strict_mode = 1，那么建表的时候指定Dynamic和Compressed会直接报错。如果没有指定innodb_strict_mode = 1，建表的时候指定Dynamic和Compressed会被忽略，默认设置为Compact)

关于innodb_strict_mode参数介绍：

innodb_strict_mode

Command-Line Format –innodb_strict_mode=#
System Variable Name innodb_strict_mode
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values Type boolean
Default OFF
When innodb_strict_mode is ON, InnoDB returns errors rather than warnings for certain conditions. The default value is OFF.

Strict mode helps guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL statements. When innodb_strict_mode is ON, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior). This is analogous to sql_mode in MySQL, which controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values.

The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX statements.innodb_strict_mode also enables a record size check, so that an INSERT or UPDATE never fails due to the record being too large for the selected page size.

Oracle recommends enabling innodb_strict_mode when using ROW_FORMAT and KEY_BLOCK_SIZE clauses on CREATE TABLE, ALTER TABLE, andCREATE INDEX statements. When innodb_strict_mode is OFF, InnoDB ignores conflicting clauses and creates the table or index, with only a warning in the message log. The resulting table might have different behavior than you intended, such as having no compression when you tried to create a compressed table. When innodb_strict_mode is ON, such problems generate an immediate error and the table or index is not created, avoiding a troubleshooting session later.

You can turn innodb_strict_mode ON or OFF on the command line when you start mysqld, or in the configuration file my.cnf or my.ini. You can also enable or disable innodb_strict_mode at runtime with the statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is either ONor OFF. Changing the GLOBAL setting requires the SUPER privilege and affects the operation of all clients that subsequently connect. Any client can change theSESSION setting for innodb_strict_mode, and the setting affects only that client.

关于innodb_file_per_table参数介绍：

 innodb_file_per_table

Command-Line Format –innodb_file_per_table
System Variable Name innodb_file_per_table
Variable Scope Global
Dynamic Variable Yes
Permitted Values (<= 5.6.5) Type boolean Default OFF Permitted Values (>= 5.6.6) Type boolean
Default ON
When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table in a separate.ibd file, rather than in the system tablespace. The storage for these InnoDB tables is reclaimed when the tables are dropped or truncated. This setting enables several other InnoDB features, such as table compression. See Section 14.4.4, “InnoDB File-Per-Table Tablespaces” for details about such features as well as advantages and disadvantages of using file-per-table tablespaces.

Be aware that enabling innodb_file_per_table also means that an ALTER TABLE operation will move InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY).

When innodb_file_per_table is disabled, InnoDB stores the data for all tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of filesystem operations for operations such as DROP TABLE or TRUNCATE TABLE. It is most appropriate for a server environment where entire storage devices are devoted to MySQL data. Because the system tablespace never shrinks, and is shared across all databases in an instance, avoid loading huge amounts of temporary data on a space-constrained system when innodb_file_per_table=OFF. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.

By default, innodb_file_per_table is enabled as of MySQL 5.6.6, disabled before that. Consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. This will prevent ALTER TABLE from moving InnoDB tables from the system tablespace to individual .ibd files.

innodb_file_per_table is dynamic and can be set ON or OFF using SET GLOBAL. You can also set this parameter in the MySQL configuration file (my.cnfor my.ini) but this requires shutting down and restarting the server.

Dynamically changing the value of this parameter requires the SUPER privilege and immediately affects the operation of all connections.

关于各个参数的详细介绍，请参见官方文档

https://www.cnblogs.com/zishengY/p/6892345.html

注意: FOR UPDATE 只能用在事务区块(BEGIN/COMMIT)中才有效。

有时候我们会看到一些select语句后面紧跟一句for update，表示手动加锁的意思，这里我们就介绍一下对for update的理解。相对另一种手动加锁方法lock in share mode 的区别见：https://blog.csdn.net/liangzhonglin/article/details/65438777。

for update：IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作，除了select …lock in share mode和select … for update这种显示加锁的查询操作。

lock in share mode：是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

for update是一个意向排它锁，也就是说对于select … for update 这条语句所在的事务中可以进行任何操作(锁定的是记录，这里指主键id=1的这条记录)，但其它事务中只能读取(对这条id=1的记录），不能进行update更新操作。

一般用在并发场景下，如双11的时候商品数量的更新，如果不添加for update的话，则会出现商品数量被多减的bug。

为了更加方便理解，我们举例说明(事务隔离级别为 RR，这里表tb的id为主键)

事务A:

start transaction;
select * from tb where id=1 for update;
update tb set product_num=product_num-1 where id=1;

此时另一个事务B执行同样的程序语句:

start transaction;
// 下面此时会被阻塞，直到事务A提交或者回滚
select * from tb where id=1 for update; 
update tb set product_num=product_num-1 where id=1;

对于其它主键值非1的不存在这种情况，只要两个事务操作的不是同一条记录就可以执行成功。

推荐阅读：https://blog.csdn.net/u011957758/article/details/75212222

建议先阅读这篇文章：http://hidba.org/?p=404

下面我们只对其中提到的做一个验证。

(1).索引字段的附加信息：可以分为变长和定长数据类型讨论，当索引字段为定长数据类型，比如char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节；对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；

(备注：当字段定义为非空的时候，是否为空的标记将不占用字节)

(2).同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节， utf8mb4 编码则是4个字节;

每种MySQL数据类型的定义参考：https://blog.haohtml.com/archives/15222

下面我们以定长数据类型准，变长数据类型请自行测试。

一、数据索引类型允许为null的情况：

表结构：

CREATE TABLE `tb` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sid` smallint(5) DEFAULT NULL,
`gid` smallint(5) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_common` (`sid`,`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

执行分析语句：

mysql> EXPLAIN select * from tb where sid=1 and gid=5;
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | tb    | NULL       | ref  | idx_common    | idx_common | 6       | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

发现用到了复合索引idx_common,这时复合索引的两个字段全部用到了，而由于 smallint 数据类型占用字节为两个字节, 属于定长类型，且允许为null,所以key_len长度计算公式为 (2 + 1) + (2 + 1) = 6
下面我们将两个字段全部禁止null看一下计算值

二、数据索引类型不允许为null的情况
表结构

CREATE TABLE `tb` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sid` smallint(5) NOT NULL,
`gid` smallint(5) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_common` (`sid`,`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

mysql> EXPLAIN select * from tb where sid=1 and gid=5;
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | tb    | NULL       | ref  | idx_common    | idx_common | 4       | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+

可以看到key_len的长度为4,即2 + 2 = 4

这里同样是复合索引中的字段全部用到，我们可以先测试一下用到一个字段的情况，依据左前缀索引原则

mysql> EXPLAIN select * from tb where sid=1;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb    | NULL       | ref  | idx_common    | idx_common | 2       | const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

发现key_len的值为2，就是说明只用到了一个复合索引字段，这里指的是sid字段。

说明：一般情况下如果key的值越大越好，说明了充分利用到了我们创建的索引。

对于char或者varchar类型则可能自行测试！

推荐阅读：http://imysql.com/2017/08/08/quick-deep-into-mysql-index.shtml