MySQL InnoDB INFORMATION_SCHEMA全文索引表

InnoDB INFORMATION_SCHEMA全文索引表
随着MySQL 5.6.4对InnoDB表的全文索引支持的引入,以下表被添加到INFORMATION_SCHEMA数据库中:

mysql> show tables from information_schema like 'INNODB_FT%';
+-------------------------------------------+
| Tables_in_information_schema (INNODB_FT%) |
+-------------------------------------------+
| INNODB_FT_CONFIG                          |
| INNODB_FT_BEING_DELETED                   |
| INNODB_FT_DELETED                         |
| INNODB_FT_DEFAULT_STOPWORD                |
| INNODB_FT_INDEX_TABLE                     |
| INNODB_FT_INDEX_CACHE                     |
+-------------------------------------------+
6 rows in set (0.00 sec)

.INNODB_FT_CONFIG:显示关于InnoDB表的全文索引和相关处理的元数据。

.INNODB_FT_BEING_DELETED:提供INNODB_FT_DELETED表的快照,仅在使用optimizer table执行维护时使用。当OPTIMIZE TABLE运行时,INNODB_FT_BEING_DELETED表被清空,doc_id从INNODB_FT_DELETED表中移除。因为INNODB_FT_BEING_DELETED的内容的生命周期通常很短,所以该表在监控或调试方面的用途有限。

.INNODB_FT_DELETED:从InnoDB表的全文索引中删除的记录。为了避免在DML操作中对InnoDB全文索引进行昂贵的索引重组,新删除的单词的信息被单独存储,在执行文本搜索时从搜索结果中过滤,并且只有在运行OPTIMIZE TABLE时才从主搜索索引中删除。

.INNODB_FT_DEFAULT_STOPWORD:保存在创建全文索引时默认使用的停用词列表。

.INNODB_FT_INDEX_TABLE:包含用于处理全文索引的文本搜索的倒排索引的数据。

.INNODB_FT_INDEX_CACHE:包含新插入的行在全文索引中的标记信息。为了避免DML操作过程中昂贵的索引重组,新索引词的信息被单独存储,并仅在优化表运行时、服务器关闭时或缓存大小超过innodb_ft_cache_size或innodb_ft_total_cache_size定义的限制时才与主搜索索引结合。

注意:
除了INNODB_FT_DEFAULT_STOPWORD表,你必须将innodb_ft_aux_table配置变量设置为包含全文索引的表名(database_name/table_name)。否则,InnoDB的全文索引INFORMATION_SCHEMA表显示为空。

InnoDB全文索引INFORMATION_SCHEMA表
这个例子使用一个带全文索引的表来演示全文索引INFORMATION_SCHEMA表中包含的数据。
1.创建一个包含全文索引的表并插入一些数据:

mysql> create table articles (
    -> id int unsigned auto_increment not null primary key,
    -> title varchar(200),
    -> body text,
    -> fulltext (title,body)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into articles (title,body) values
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

2.设置innodb_ft_aux_table变量为FULLTEXT索引表的名称。如果没有设置这个变量,除了INNODB_FT_DEFAULT_STOPWORD表之外,InnoDB的FULLTEXT INFORMATION_SCHEMA表将显示为空。

mysql> set global innodb_ft_aux_table = 'test/articles';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%aux_table%';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| innodb_ft_aux_table | test/articles |
+---------------------+---------------+
1 row in set (0.01 sec)

3.查询INNODB_FT_INDEX_CACHE表,它显示了在全文索引中新插入的行信息。为了避免DML操作期间昂贵的索引重组,新插入行的数据将保留在全文索引缓存中,直到优化表运行(或直到服务器关闭或超过缓存限制)。

mysql> select * from information_schema.innodb_ft_index_cache limit 5;
+------------+--------------+-------------+-----------+--------+----------+
| WORD       | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| 1001       |            5 |           5 |         1 |      5 |        0 |
| after      |            3 |           3 |         1 |      3 |       22 |
| comparison |            6 |           6 |         1 |      6 |       44 |
| configured |            7 |           7 |         1 |      7 |       20 |
| database   |            2 |           6 |         2 |      2 |       31 |
+------------+--------------+-------------+-----------+--------+----------+
5 rows in set (0.00 sec)

4.启用innodb_optimize_fulltext_only,在包含全文索引的表上运行OPTIMIZE TABLE。此操作将全文索引缓存的内容刷新到主全文索引。innodb_optimize_fulltext_only改变了optimize_table语句在InnoDB表上的操作方式,并且是在对带有全文索引的InnoDB表进行维护操作时临时启用的。

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_optimize_fulltext_only';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_optimize_fulltext_only | ON    |
+-------------------------------+-------+
1 row in set (0.01 sec)


mysql> optimize table articles;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.articles | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.01 sec)

5.查询INNODB_FT_INDEX_TABLE表,查看主要全文索引中的数据信息,包括刚刚从全文索引缓存中刷新的数据信息。

mysql> select * from information_schema.innodb_ft_index_table limit 5;
+------------+--------------+-------------+-----------+--------+----------+
| WORD       | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| 1001       |            5 |           5 |         1 |      5 |        0 |
| after      |            3 |           3 |         1 |      3 |       22 |
| comparison |            6 |           6 |         1 |      6 |       44 |
| configured |            7 |           7 |         1 |      7 |       20 |
| database   |            2 |           6 |         2 |      2 |       31 |
+------------+--------------+-------------+-----------+--------+----------+
5 rows in set (0.00 sec)

INNODB_FT_INDEX_CACHE表现在是空的,因为优化表操作刷新了全文索引缓存。

mysql> select * from information_schema.innodb_ft_index_cache limit 5;
Empty set (0.00 sec)

6.从test/articles表中删除一些记录。

mysql> delete from test.articles where id < 4;
Query OK, 3 rows affected (0.01 sec)

7.查询INNODB_FT_DELETED表。该表记录从全文索引中删除的行。为了避免DML操作期间昂贵的索引重组,新删除记录的信息被单独存储,在进行文本搜索时从搜索结果中过滤,在运行OPTIMIZE TABLE时从主搜索索引中删除。

mysql> select * from information_schema.innodb_ft_deleted;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      4 |
+--------+
3 rows in set (0.01 sec)

8.运行OPTIMIZE TABLE删除删除的记录。

mysql> optimize table articles;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.articles | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.03 sec)

INNODB_FT_DELETED表现在应该是空的。

mysql> select * from information_schema.innodb_ft_deleted;
Empty set (0.00 sec)

9.查询INNODB_FT_CONFIG表。该表包含关于全文索引和相关处理的元数据:
.optimize_checkpoint_limit是optimize table运行结束的秒数。

.synced_doc_id是下一个要发布的DOC_ID。

.stopword_table_name是用户定义的停止词表的数据库/表名。如果没有用户定义的停用词表,该字段显示为空。

.use_stopword表示是否使用stopword表,stopword表在创建FULLTEXT索引时定义。

mysql> select * from information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 8     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.00 sec)

MySQL InnoDB INFORMATION_SCHEMA系统表

InnoDB INFORMATION_SCHEMA系统表
你可以使用InnoDB INFORMATION_SCHEMA系统表来提取由InnoDB管理的schema对象的元数据。这些信息来自InnoDB内部的系统表(也称为InnoDB数据字典),它不能像普通的InnoDB表那样直接查询。传统上,你可以使用InnoDB监视器技术来获取这类信息,设置InnoDB监视器并解析SHOW ENGINE InnoDB STATUS语句的输出。InnoDB INFORMATION_SCHEMA表接口允许你使用SQL查询这些数据。

除了INNODB_SYS_TABLESTATS没有对应的内部系统表外,InnoDB INFORMATION_SCHEMA系统表中的数据都是直接从InnoDB内部系统表中读取的,而不是从内存中缓存的元数据中读取。

InnoDB INFORMATION_SCHEMA系统表包括下面列出的表。INNODB_SYS_DATAFILES和INNODB_SYS_TABLESPACES是在MySQL 5.6.6中添加的,引入了对CREATE TABLE语句中的DATA DIRECTORY=’ DIRECTORY ‘子句的支持,它允许InnoDB file-per-table表空间(在MySQL数据目录之外的位置创建ibd文件。

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';
+--------------------------------------------+
| Tables_in_information_schema (INNODB_SYS%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_VIRTUAL                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_TABLES                          |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_TABLESTATS                      |
+--------------------------------------------+
10 rows in set (0.00 sec)

表名表明所提供的数据类型:
.INNODB_SYS_TABLES提供关于InnoDB表的元数据,等价于InnoDB数据字典中的SYS_TABLES表中的信息。

.INNODB_SYS_COLUMNS提供关于InnoDB表列的元数据,等价于InnoDB数据字典中的SYS_COLUMNS表中的信息。

.INNODB_SYS_INDEXES提供有关InnoDB索引的元数据,等价于InnoDB数据字典中的SYS_INDEXES表中的信息。

.INNODB_SYS_FIELDS提供了InnoDB索引中关键列(字段)的元数据,等价于InnoDB数据字典中的SYS_FIELDS表中的信息。

.INNODB_SYS_TABLESTATS提供了关于InnoDB表的底层状态信息的视图,这些状态信息来源于内存中的数据结构。没有对应的内部InnoDB系统表。

.INNODB_SYS_DATAFILES提供了InnoDB file-per-table件和一般表空间的数据文件路径信息,等价于InnoDB数据字典中的SYS_DATAFILES表中的信息。

.INNODB_SYS_TABLESPACES提供了关于InnoDB file-per-table和一般表空间的元数据,等价于InnoDB数据字典中的SYS_TABLESPACES表中的信息。

.INNODB_SYS_FOREIGN提供了定义在InnoDB表上的外键的元数据,等价于InnoDB数据字典中的SYS_FOREIGN表中的信息。

.INNODB_SYS_FOREIGN_COLS提供了定义在InnoDB表中的外键列的元数据,等价于InnoDB数据字典中的SYS_FOREIGN_COLS表中的信息。

InnoDB INFORMATION_SCHEMA系统表可以通过TABLE_ID、INDEX_ID和SPACE等字段连接在一起,让你可以轻松地检索到你想要研究或监控的对象的所有可用数据。

这个例子使用一个简单的表(t1)和一个索引(i1)来展示在InnoDB INFORMATION_SCHEMA系统表中发现的元数据的类型。
1.创建表t1:

mysql> use test;
Database changed
mysql> create table t1 (
    -> col1 int,
    -> col2 char(10),
    -> col3 varchar(10))
    -> engine = innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> create index i1 on t1(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.创建表t1后,查询innodb_sys_tables来定位test/t1的元数据:

mysql> select * from information_schema.innodb_sys_tables where name='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 509
         NAME: test/t1
         FLAG: 33
       N_COLS: 6
        SPACE: 508
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.01 sec)

表t1的TABLE_ID是509。FLAG字段提供了有关表格式和存储特性的位级别信息。有6列,其中3列是由InnoDB创建的隐藏列(DB_ROW_ID, DB_TRX_ID和DB_ROLL_PTR)。表空间的ID是508(值为0表示表驻留在system表空间中)。FILE_FORMAT是Barracuda格式,ROW_FORMAT是Dynamic格式。ZIP_PAGE_SIZE只适用于行格式压缩的表。

3.使用innodb_sys_tables中的table_id信息,查询innodb_sys_columns表中关于表的列的信息。

mysql> select * from information_schema.innodb_sys_columns where table_id = 509 \G
*************************** 1. row ***************************
TABLE_ID: 509
    NAME: col1
     POS: 0
   MTYPE: 6
  PRTYPE: 1027
     LEN: 4
*************************** 2. row ***************************
TABLE_ID: 509
    NAME: col2
     POS: 1
   MTYPE: 13
  PRTYPE: 2949374
     LEN: 40
*************************** 3. row ***************************
TABLE_ID: 509
    NAME: col3
     POS: 2
   MTYPE: 12
  PRTYPE: 2949135
     LEN: 40
3 rows in set (0.00 sec)

除了TABLE_ID和NAME列之外,INNODB_SYS_COLUMNS还提供了每列的序号位置(POS)(从0开始,顺序递增),列的MTYPE或“主类型”(6 = INT,13 = CHAR, 12 = VARCHAR), PRTYPE或“精确类型”(一个二进制值,用比特表示MySQL的数据类型,字符集代码和可空性),以及列的长度(LEN)。

4.再次使用innodb_sys_tables中的table_id信息,查询innodb_sys_indexes,获取与表t1相关的索引信息。

mysql> select * from information_schema.innodb_sys_indexes where table_id = 509 \G
*************************** 1. row ***************************
       INDEX_ID: 758
           NAME: GEN_CLUST_INDEX
       TABLE_ID: 509
           TYPE: 1
       N_FIELDS: 0
        PAGE_NO: 3
          SPACE: 508
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 759
           NAME: i1
       TABLE_ID: 509
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 508
MERGE_THRESHOLD: 50
2 rows in set (0.01 sec)

INNODB_SYS_INDEXES返回两个索引的数据第一个索引是GEN_CLUST_INDEX,如果表没有用户定义的聚集索引,它是由InnoDB创建的聚集索引。第二个索引(i1)是用户定义的辅助索引。

INDEX_ID是索引的标识符,在一个实例中的所有数据库中都是唯一的。TABLE_ID标识了索引关联的表。索引TYPE值指示索引类型(1 =集群索引,0 =辅助索引)。n_fields值是组成索引的字段数目。PAGE_NO是索引B-tree的根页号,SPACE是索引所在的表空间ID。非零值表示索引不在系统表空间中。MERGE_THRESHOLD定义索引页中数据量的百分比阈值。当删除一行或者更新操作缩短一行时,如果索引页中的数据量低于这个值(默认值为50%),InnoDB会尝试将索引页与相邻的索引页合并。

5.使用innodb_sys_indexes中的index_id信息,查询innodb_sys_fields中索引i1的字段信息。

mysql> select * from information_schema.innodb_sys_fields where index_id = 759 \G
*************************** 1. row ***************************
INDEX_ID: 759
    NAME: col1
     POS: 0
1 row in set (0.01 sec)

INNODB_SYS_FIELDS提供了索引字段的名称及其在索引中的序号位置。如果索引(i1)定义在多个字段上,INNODB_SYS_FIELDS将为每个被索引的字段提供元数据。

6.使用innodb_sys_tables中的space信息,查询innodb_sys_tables]表获得关于表的表空间信息。

mysql> select * from information_schema.innodb_sys_tablespaces where space = 508 \G
*************************** 1. row ***************************
         SPACE: 508
          NAME: test/t1
          FLAG: 33
   FILE_FORMAT: Barracuda
    ROW_FORMAT: Dynamic
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Single
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 114688
ALLOCATED_SIZE: 114688
1 row in set (0.05 sec)

除了表空间的SPACE ID和关联表的NAME之外,INNODB_SYS_TABLESPACES还提供了表空间的FLAG数据,这是有关表空间格式和存储特性的位级别信息。还提供了表空间FILE_FORMAT、ROW_FORMAT、PAGE_SIZE和其他几个表空间元数据项。

7.再次使用INNODB_SYS_TABLES中的SPACE信息,查询INNODB_SYS_DATAFILES表空间数据文件的位置。

mysql> select * from information_schema.innodb_sys_datafiles where space = 508 \G
*************************** 1. row ***************************
SPACE: 508
 PATH: ./test/t1.ibd
1 row in set (0.00 sec)

该数据文件位于MySQL的data目录下的test目录中。如果使用CREATE TABLE语句的data directory子句在MySQL数据目录之外的位置创建一个file-per-table表空间,那么表空间路径将是一个完全限定的目录路径。

8.最后一步,在表t1 (TABLE_ID = 509)中插入一行,然后查看INNODB_SYS_TABLESTATS表中的数据。MySQL优化器使用该表中的数据来计算查询InnoDB表时使用的索引。该信息来源于内存中的数据结构。没有对应的内部InnoDB系统表。

mysql> insert into t1 values(5, 'abc', 'def');
Query OK, 1 row affected (0.00 sec)

mysql> select * from information_schema.innodb_sys_tablestats where table_id = 509 \G
*************************** 1. row ***************************
         TABLE_ID: 509
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 1
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 1
 MODIFIED_COUNTER: 1
          AUTOINC: 0
        REF_COUNT: 2
1 row in set (0.00 sec)

STATS_INITIALIZED字段表示是否收集了表的统计信息。NUM_ROWS是表中当前估计的行数。cluster_index_size和OTHER_INDEX_SIZE字段分别报告磁盘上为表存储集群索引和辅助索引的页数。MODIFIED_COUNTER值显示了由DML操作和来自外键的级联操作修改的行数。AUTOINC值是任何基于自动递增操作的下一个数字。在表t1中没有定义autoincrement列,因此值为0。REF_COUNT值是一个计数器。当计数器变为0时,表示表元数据可以从表缓存中移除。

外键信息_schema系统表
INNODB_SYS_FOREIGN和INNODB_SYS_FOREIGN_COLS表提供了有关外键关系的数据。这个例子使用具有外键关系的父表和子表来展示在INNODB_SYS_FOREIGN和INNODB_SYS_FOREIGN_COLS表中找到的数据。

1.创建包含父表和子表的测试数据库:

mysql> create table parent (id int not null,
    -> primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> create table child (id int, parent_id int,
    -> index par_ind (parent_id),
    -> constraint fk1
    -> foreign key (parent_id) references parent(id)
    -> on delete cascade) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

2.在创建父表和子表之后,查询INNODB_SYS_FOREIGN并找到test/child和test/parent外键关系的外键数据:

mysql> select * from information_schema.innodb_sys_foreign \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

元数据包括外键ID (fk1),它是根据在子表上定义的约束命名的。FOR_NAME是定义外键的子表的名称。REF_NAME是父表(被引用的表)的名称。N_COLS是外键索引中的列数。TYPE是一个数值,表示提供有关外键列的附加信息的位标志。在本例中,TYPE值为1,表示为外键指定了ON DELETE CASCADE选项。有关类型值的更多信息,请参阅INNODB_SYS_FOREIGN表定义。

3.使用外键ID,查询INNODB_SYS_FOREIGN_COLS来查看这个外键对应的列的数据。

mysql> select * from information_schema.innodb_sys_foreign_cols where id = 'test/fk1' \G
*************************** 1. row ***************************
          ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0
1 row in set (0.00 sec)

FOR_COL_NAME是子表中外键列的名称,REF_COL_NAME是父表中被引用的列的名称。POS值是键字段在外键索引中的顺序位置,从0开始。

连接InnoDB INFORMATION_SCHEMA系统表
这个例子展示了连接三个InnoDB INFORMATION_SCHEMA系统表(INNODB_SYS_TABLES、INNODB_SYS_TABLESPACES和INNODB_SYS_TABLESTATS)来收集employees sample数据库中表的文件格式、行格式、页面大小和索引大小信息。

mysql> select a.name, a.file_format, a.row_format,
    -> @page_size :=
    -> if(a.row_format='compressed',
    -> b.zip_page_size, b.page_size)
    -> as page_size,
    -> round((@page_size * c.clust_index_size)
    -> /(1024*1024)) as pk_mb,
    -> round((@page_size * c.other_index_size)
    -> /(1024*1024)) as secidx_mb
    -> from information_schema.innodb_sys_tables a
    -> inner join information_schema.innodb_sys_tablespaces b on a.name = b.name
    -> inner join information_schema.innodb_sys_tablestats c on b.name = c.name
    -> where a.name like 'employees/%'
    -> order by a.name desc;
+------------------------+-------------+------------+-----------+-------+-----------+
| name                   | file_format | row_format | page_size | pk_mb | secidx_mb |
+------------------------+-------------+------------+-----------+-------+-----------+
| employees/titles       | Barracuda   | Dynamic    |     16384 |     0 |         0 |
| employees/t            | Barracuda   | Dynamic    |     16384 |     0 |         0 |
| employees/salaries     | Barracuda   | Dynamic    |     16384 |     0 |         0 |
| employees/employees    | Barracuda   | Dynamic    |     16384 |     0 |         0 |
| employees/dept_manager | Barracuda   | Dynamic    |     16384 |     0 |         0 |
| employees/dept_emp     | Barracuda   | Dynamic    |     16384 |     0 |         0 |
| employees/departments  | Barracuda   | Dynamic    |     16384 |     0 |         0 |
| employees/c            | Barracuda   | Dynamic    |     16384 |     0 |         0 |
+------------------------+-------------+------------+-----------+-------+-----------+
8 rows in set (0.05 sec)

IF()控制流函数用于处理压缩表。如果表被压缩,则使用ZIP_PAGE_SIZE而不是PAGE_SIZE计算索引大小。cluster_index_size和OTHER_INDEX_SIZE以字节为单位,它们除以1024*1024,就得到了以兆字节(MBs)为单位的索引长度。使用ROUND()函数将MB值四舍五入为0个小数空格。

Oracle 18c 使用影子表空间来管理丢失写保护

使用影子表空间来管理丢失写保护

当I/O子系统确认块写入已完成,但在持久存储中没有发生写入时,就会发生数据块丢失写入。影子丢失写保护可以防止丢失写。
.关于影子丢失写保护
当I/O子系统确认块写入的完成,即使没有发生写入,或者当块的前一个镜像覆盖了当前的镜像时,就会发生数据块丢失写. 影子丢失写保护可以防止表空间或单个数据文件的丢失写。

.为影子丢失写保护创建影子表空间
为了给影子丢失写保护创建一个影子表空间,在执行create big file tablespace语句时使用lost write protection子句。

.对数据库启用影子丢失写保护
为了对一个多租户容器数据库(CDB)或一个非CDB启用影子丢人写保护,可以执行带有enable lost write protection子句的alter database语句。为了对一个PDB启用影子丢失写失保护,可以执行带有enable lost write protection子句的alter pluggable database语句。

.对表空间和数据文件启用影子丢失写保护
可以对表空间和数据文件启用影子丢失写保护

.对数据库禁用影子丢失写保护
为了对CDB或非CDB数据库禁用影子丢失写保护,执行带有disable lost write protection子句的alter database语句。为了对一个pdb禁用丢失写保护,执行带有disable lost write protection子句的alter pluggable database语句。

.删除或暂停影子丢失写保护
可以对表空间或数据文件删除或暂停影子丢失写保护

.删除影子表空间
可以执行drop tablespace语句来删除影子表空间。如果使用带有including contents子句的drop tablespace语句,那么影子表空间连同它的内容会一起被删除。如果使用不带including contents子句的drop tablespace语句,那么在删除影子表空间之前,影子表空间的内容在存在其它影子表空间且有足够的可用空间时会被移动到其它的影子表空间中。

关于影子丢失写保护
当I/O子系统确认块写入的完成,即使没有发生写入,或者当块的前一个镜像覆盖了当前的镜像时,就会发生数据块丢失写操作。影子丢失写保护可以防止表空间或数据文件发生丢失写。

影子丢失写保护对于丢失写提供了一种快速检测和立即响应的方法。使用影子丢失写保护可以最小化数据丢失和请求修复数据库所需要的时间。

为了使用影子丢失写保护,你必须对数据库启用并创建一个或多个影子表空间。一个影子表空间是一种特殊目的的大文件表空间它只包含跟踪数据文件的系统改变号(SCNs)。可以通过使用带有lost write protection子句的create tablespace语句来创建一个影子表空间。

当一个被跟踪的数据块从磁盘被读取时,影子丢失写保护通过比较在影子表空间中数据块的SCN与将被读取的数据块最近写入的SCN来检测是否发生了丢失写。如果影子条目的SCN大于被读数据块的SCN,那么发生了丢失写。当检测到丢失写时会返回一个错误。

未检测到的丢失写可能会造成数据损坏因为不正确的数据可能被用于其它的DML事务。影子丢失写保护在使用之前检测到丢失写,以防止数据损坏。可以对特定的表空间和数据文件启用影子丢失写保护。因此,可以对你最重要的数据启用它。你不必使用它来跟踪你的所有数据。

此外,影子表空间也是灵活的。您可以将一个影子表空间替换为另一个影子表空间,以更改其配置或位置。

当影子丢失写保护被启用后,可以对正常的DML操作和SQL*Loader常规路径加载和直接路径加载启用。也可以对RMAN备份启用。RMAN备份检查正在读取的块是否丢失写,如果找到这样的块,会引发错误。

在对表空间或数据文件启用影子丢失写保护之后,如果你想停止收集新的丢失写保护信息并检测丢失写可以暂停它。当影子丢失写保护被暂停之后,跟踪数据会被保存在影子表空间中,并且可以重新启用影子丢失写保护。如果对数据文件或表空间删除影子丢失写保护,那么它的跟踪数据会被删除并且不能重新使用。

可以通过执行带有lost write protection子句的alter tablespace语句来对表空间启用影子丢失写保护,执行带有lost write protection子句的alter database data_file_name语句来对数据文件启用影子丢失写保护。当对表空间启用影子丢失写保护后,表空间的所有当前和以后的数据文件都会启用影子丢失写保护。

Oracle数据库会自动对一个特定的影子表空间指派一个跟踪数据文件。你不能指定那个影子表空间使用某个特定的数据文件。影子表空间中的空间量应该至少为启用影子丢失写保护的数据文件所使用的空间的2%。

注意:
如果增加跟踪数据文件的大小,那么影子丢失写保护将试图重置影子表空间中的跟踪数据。如果没有足够的空间来跟踪所有数据,那么影子丢失写保护会向日志文件插入一条告警信息并使用它所能使用的影子空间来跟踪数据。

数据库闪回会造成任何影子丢失写保护数据被删除。在闪回之后,影子丢失写保护在重新填充时跟踪数据,并在块更新时对影子跟踪数据进行更新。

影子丢失写保护与使用DB_LOST_WRITE_PROTECT初始化参数和备用数据库配置的丢失写保护无关。

为影子丢失写保护创建影子表空间
可以使用带有lost write protection子句的create bigfile tablespace语句来为影子丢失写保护创建一个影子表空间。

一个影子表空间可以被任何启用影子丢失写保护的表空间或数据文件所使用。影子表空间的空间量至少是那些启用影子丢失写保护的数据文件大小的2%.

为了创建影子表空间,数据库的兼容级别必须至少为18.0.0或更高版本。

例如创建一个名为shadow_lwp1的影子表空间供影子丢失写保护使用

SQL> CREATE BIGFILE TABLESPACE shadow_lwp1 DATAFILE 'shadow_lwp1.df' SIZE 10M LOST WRITE PROTECTION;

Tablespace created.

对数据库启用影子丢失写保护
为了对一个CDB或非CDB启用影子丢失写保护,执行带有enable lost write protection子句的alter database语句。为了对pdb启用影子丢失写保护执行带有enable lost write protection子句的alter pluggable database语句。

在对单个表空间和数据文件启用影子丢失写保护之前,你必须至少创建一个影子表空间,并且必须对包含它的数据库启用影子丢失写保护。在执行这些配置后,可以通过执行alter tablespace语句来对表空间启用影子丢失写保护,并且可以通过执行alter database语句来为数据文件启用影子丢失写保护。

注意:
对数据库启用影子丢失写保护,那么数据库的兼容级别至少为18.0.0或更高版本,并且至少存在一个影子表空间。

对CDB root启用或禁用影子丢失写保护不影响PDBs的影子丢失写保护。因此即使对CDB root禁用了影子丢失写保护也还是能对PDB启用影子丢失写保护。

当对数据库启用影子丢失写保护时,会自动指派一个影子表空间供其使用。

对数据库启用影子丢失写保护操作如下:
1.通过SQL*Plus,使用有要求的权限的用户进行登录:
.对于非CDB或CDB root使用有alter database系统权限的用户进行登录。

.对于应用程序root,PDB或应用程序PDB,使用有alter pluggable database系统权限的用户进行登录。

2.执行以下操作:
.对于非CDB或CDB root,执行带有enable lost write protection子句的alter database语句。

.对于应用程序root,PDB或应用程序PDB,执行带有enable lost write protection子句的alter pluggable database语句。

对非CDB或CDB root启用影子丢失写保护

SQL> alter database enable lost write protection;

Database altered.

对PDB启用影子丢失写保护

SQL> create bigfile tablespace shadow_pdb datafile 'shadow_pdb.dbf' size 10M lost write protection;

Tablespace created.

SQL> alter pluggable database enable lost write protection;

Pluggable database altered.

对表空间和数据文件启用影子丢失写保护
为了对表空间启用影子丢失写保护,执行带有enable lost write protection子句的alter tablespace语句。为了对数据文件启用影子丢失写保护执行带有enable lost write protection子句的alter database data_file_name语句。当对一个表空间启用了影子丢失写保护,那么就是对表空间的所有数据文件启用了影子丢失写保护,并且对于向表空间增加的任何数据文件也会启用影子丢失写保护。

注意:
.为了对一个表空间或数据文件启用影子丢失写保护那么数据库中必须至少存在一个影子表空间。
.当对一个表空间或数据文件启用影子丢失写保护,那么这个影子表空间会自动被分配给它。

为了对一个表空间或数据文件启用影子丢失写保护必须满足以下条件:
1.在SQL*Plus中,使用有以下权限的用户来连接数据库:
.如果计划对一个表空间启用影子丢失写保护,那么连接用户必须有alter tablespace权限。

.如果计划对非CDB或CDB root中的数据文件启用影子丢失写保护,那么连接用户必须有alter database权限。

.如果计划对一个应用程序root,PDB或应用程序PDB中的数据文件启用影子丢失写保护,那么连接用户必须有alter pluggable database权限。

2.执行以下操作来启用影子丢失写保护:
.为了对一个表空间启用影子丢失写保护,执行带有enable lost write protection子句的alter tablespace语句。

.为了对非CDB或CDB root中的数据文件启用影子丢失写保护,执行带有enable lost write protection子句的alter database data_file_name语句。

.为了对一个应用程序root,PDB或应用程序PDB中的数据文件启用影子丢失写保护,执行带有enable lost write protection子句的alter pluggable database datafile data_file_name语句。

创建一个test表空间并启用影子丢失写保护:

SQL> create tablespace test datafile 'test01.dbf' size 10M;

Tablespace created.


SQL> alter tablespace test enable lost write protection;

Tablespace altered.

对非CDB或CDB root中的数据文件启用影子丢失写保护

SQL> create tablespace test1 datafile 'test1_01.dbf' size 10M;

Tablespace created.

SQL> alter tablespace test1 add datafile 'test1_02.dbf' size 10M;

Tablespace altered.

SQL> alter database datafile 'test1_01.dbf' enable lost write protection;

Database altered.

对一个应用程序root,PDB或应用程序PDB中的数据文件启用影子丢失写保护

SQL> alter session set container=hypdb;

Session altered.

SQL> create tablespace cs datafile 'cs01.dbf' size 10M;

Tablespace created.

SQL> alter pluggable database datafile 'cs01.dbf' enable lost write protection;

Pluggable database altered.

对数据库禁用影子丢失写保护
为了对CDB或非CDB禁用影子丢失写保护,执行带有disable lost write protection子句的alter database语句。为了对PDB禁用影子丢失写保护,执行带有disable lost write protection子句的alter pluggable database语句。

当你对一个数据库禁用影子丢失写保护后,不是这个数据库中的表空间或数据文件可以被影子丢失写保护。

注意:
.禁用影子丢失写保护不会删除现有影子表空间中的数据,但表空间中的数据不再被更新或被检查。如果想删除影子表空间中的数据,那么可以使用带有including contents子句的drop tablespace语句。

.对CDB root启用或禁用影子丢失写保护不会影响PDBs的影子丢失写保护。

为了对一个数据库禁用影子丢失写保护必须满足以下条件:
1.在SQL*Plus中,使用有以下权限的用户来连接数据库:
.在非CDB或CDB root中,那么连接用户必须有alter tablespace权限。

.在应用程序root,PDB或应用程序PDB中,那么连接用户必须有alter pluggable database权限。

2.执行以下操作来启用影子丢失写保护:
.对非CDB或CDB root,执行带有disable lost write protection子句的alter database语句。

.对应用程序root,PDB或应用程序PDB,执行带有disable lost write protection子句的alter pluggable database语句。

对非CDB或CDB root禁用影子丢失写保护

SQL> alter session set container=cdb$root;

Session altered.


SQL> alter database disable lost write protection;

Database altered.

对PDB禁用影子丢失写保护

SQL> alter session set container=hypdb;

Session altered.

SQL> alter pluggable database disable lost write protection;

Pluggable database altered.

删除或暂停影子丢失写保护
可以对表空间或数据文件删了或暂停影子丢失写保护。

当表空间或数据文件不再需要影子丢失写保护,可以选对以下操作:
.可以删除影子丢失写保护。这个操作会从影子表空间中删除表空间或数据文件的跟踪信息。这个选项也会停止对表空间或数据文件收集新的写丢失信息并且对他们的新丢失写停止检查。

.也可以暂停影子丢失写保护。停止对表空间或数据文件收集新的写丢失信息并且对他们的新丢失写停止检查。然而旧的丢失写信息仍然保留在影子表空间中。如果对表空间或数据文件重新启用影子丢失写保护,那么它们可以使用旧的丢失写信息。

当对表空间删除或暂停影子丢失写保护后,会删除影子丢失写保护或对表空间中的所有数据文件暂停影子丢失写保护。

为了对一个表空间或数据文件删除或暂停影子丢失写保护必须满足以下条件:
1.在SQL*Plus中,使用有以下权限的用户来连接数据库:
.如果计划对一个表空间删了或禁用影子丢失写保护,那么连接用户必须有alter tablespace权限。

.如果计划对非CDB或CDB root中的数据文件删除或禁用影子丢失写保护,那么连接用户必须有alter database权限。

.如果计划对一个应用程序root,PDB或应用程序PDB中的数据文件删除或禁用影子丢失写保护,那么连接用户必须有alter pluggable database权限。

2.执行以下操作来启用影子丢失写保护:
.为了对一个表空间删除或禁用影子丢失写保护,执行带有enable lost write protection或suspend lost write protection子句的alter tablespace语句。

.为了对非CDB或CDB root中的数据文件删除或禁用影子丢失写保护,执行带有enable lost write protection或suspend lost write protection 子句的alter database data_file_name语句。

.为了对一个应用程序root,PDB或应用程序PDB中的数据文件删除或禁用影子丢失写保护,执行带有enable lost write protection或suspend lost write protection子句的alter pluggable database datafile data_file_name语句。

删了表空间test的影子丢失写保护

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter tablespace test remove lost write protection;

Tablespace altered.

暂停对数据文件test1_01.dbf的影子丢失写保护

SQL> alter database datafile 'test1_01.dbf' suspend lost write protection;

Database altered.

对PDB中的数据文件cs01.dbf暂停影子丢失写保护

SQL> alter session set container=hypdb;

Session altered.

SQL> alter pluggable database datafile 'cs01.dbf' suspend lost write protection;

Pluggable database altered.

删除影子表空间
可以执行drop tablespace语句来删除影子表空间。如果执行带有including contents子句的drop tablespace语句,那么在删除影子表空间的同时也会删除它的内容和数据文件。如果执行没带inclding contents子句的drop tablespace语句,那么在删除影子表空间之前,如果存在其它的影子表空间且有足够空间,它的内容会被移到另一个影子表空间中。

SQL> drop tablespace shadow_pdb including contents;

Tablespace dropped.

MySQL使用InnoDB事务和锁信息识别阻塞事务

使用InnoDB事务和锁信息识别阻塞事务
有时识别哪些事务阻塞了另一个事务是有帮助的。包含InnoDB事务和数据锁信息的表使您能够确定哪个事务正在等待另一个事务,以及正在请求哪些资源。

假设有三个会话同时运行。每个会话对应于一个MySQL线程,并依次执行一个事务。考虑当这些会话发出以下语句,但还没有提交事务时,系统的状态:

.会话a

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select a from abc for update;
select sleep(100);+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

mysql> select sleep(100);
+------------+
| sleep(100) |
+------------+
|          0 |
+------------+
1 row in set (1 min 40.00 sec)

.会话b

mysql> select a from abc for update;

.会话c

mysql> select a from abc for update;

在这种情况下,使用以下查询来查看哪些事务正在等待,哪些事务正在阻塞它们:

mysql> SELECT
    -> r.trx_id waiting_trx_id,
    -> r.trx_mysql_thread_id waiting_thread,
    -> r.trx_query waiting_query,
    -> b.trx_id blocking_trx_id,
    -> b.trx_mysql_thread_id blocking_thread,
    -> b.trx_query blocking_query
    -> FROM information_schema.innodb_lock_waits w
    -> INNER JOIN information_schema.innodb_trx b
    -> ON b.trx_id = w.blocking_trx_id
    -> INNER JOIN information_schema.innodb_trx r
    -> ON r.trx_id = w.requesting_trx_id;
+----------------+----------------+------------------------------+-----------------+-----------------+------------------------------+
| waiting_trx_id | waiting_thread | waiting_query                | blocking_trx_id | blocking_thread | blocking_query               |
+----------------+----------------+------------------------------+-----------------+-----------------+------------------------------+
| 5686612        |            174 | select a from abc for update | 5686611         |             173 | select a from abc for update |
| 5686612        |            174 | select a from abc for update | 5686610         |             172 | select sleep(100)            |
| 5686611        |            173 | select a from abc for update | 5686610         |             172 | select sleep(100)            |
+----------------+----------------+------------------------------+-----------------+-----------------+------------------------------+
3 rows in set, 1 warning (0.01 sec)

或者,更简单地,使用sys schema的innodb_lock_waits视图:

mysql> SELECT
    -> waiting_trx_id,
    -> waiting_pid,
    -> waiting_query,
    -> blocking_trx_id,
    -> blocking_pid,
    -> blocking_query
    -> FROM sys.innodb_lock_waits;
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
| waiting_trx_id | waiting_pid | waiting_query                | blocking_trx_id | blocking_pid | blocking_query               |
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
| 5686611        |         173 | select a from abc for update | 5686610         |          172 | select sleep(100)            |
| 5686612        |         174 | select a from abc for update | 5686611         |          173 | select a from abc for update |
| 5686612        |         174 | select a from abc for update | 5686610         |          172 | select sleep(100)            |
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
3 rows in set, 3 warnings (0.08 sec)

在上面的查询结果中,你可以通过“等待查询”或“阻塞查询”列来识别会话。如你所见:
.会话b(trx_id 568661,线程号173)和会话c(trx id 5686612,线程号174)都在等待会话a(trx id 5686610,线程号172

.会话c正等待会话b和会话a

你可以在表INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS中看到底层的数据。

下表是INFORMATION_SCHEMA.INNODB_TRX的一些示例内容。

mysql> select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_weight,trx_mysql_thread_id,trx_query from information_schema.innodb_trx;
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                                                                                                                              |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5686614         | RUNNING   | 2023-12-11 16:39:57 | NULL                  | NULL                |          2 |                 172 | select sleep(100)                                                                                                                                      |
| 5686612         | LOCK WAIT | 2023-12-11 16:07:07 | 5686612:423:3:2       | 2023-12-11 16:40:07 |          2 |                 174 | select a from abc for update                                                                                                                           |
| 5686611         | LOCK WAIT | 2023-12-11 16:07:04 | 5686611:423:3:2       | 2023-12-11 16:40:04 |          2 |                 173 | select a from abc for update                                                                                                                           |
| 421200136110592 | RUNNING   | 2023-12-11 16:07:52 | NULL                  | NULL                |          0 |                 176 | select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_weight,trx_mysql_thread_id,trx_query from information_schema.innodb_trx |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+--------------+------------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_data |
+-----------------+-------------+-----------+-----------+--------------+------------+-----------+
| 5686612:423:3:2 | 5686612     | X         | RECORD    | `test`.`abc` | PRIMARY    | 1         |
| 5686611:423:3:2 | 5686611     | X         | RECORD    | `test`.`abc` | PRIMARY    | 1         |
| 5686614:423:3:2 | 5686614     | X         | RECORD    | `test`.`abc` | PRIMARY    | 1         |
+-----------------+-------------+-----------+-----------+--------------+------------+-----------+
3 rows in set, 1 warning (0.00 sec)

mysql> select requesting_trx_id,requested_lock_id,blocking_trx_id,blocking_lock_id from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5686612           | 5686612:423:3:2   | 5686611         | 5686611:423:3:2  |
| 5686612           | 5686612:423:3:2   | 5686614         | 5686614:423:3:2  |
| 5686611           | 5686611:423:3:2   | 5686614         | 5686614:423:3:2  |
+-------------------+-------------------+-----------------+------------------+
3 rows in set, 1 warning (0.00 sec)

InnoDB锁和锁等待信息

当一个事务更新表中的一行,或者用SELECT FOR UPDATE锁住它时,InnoDB会为该行建立一个锁的列表或队列。类似地,InnoDB在表上维护了一个表级锁的列表。如果第二个事务想要更新一行数据或者在不兼容的模式下锁定一个已经被前一个事务锁定的表,InnoDB会向相应的队列添加一个锁请求。为了让事务获得锁,必须删除先前进入锁队列的所有不兼容的锁请求(当持有或请求这些锁的事务提交或回滚这些锁时)。

一个事务可以对不同的行或表有任意数量的锁请求。在任何给定的时间,一个事务可以请求一个由另一个事务持有的锁,在这种情况下,它被另一个事务阻塞。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务没有等待锁,则它处于运行状态。如果事务正在等待锁,则它处于锁等待状态。(INFORMATION_SCHEMA INNODB_TRX表表示事务状态值。)

INNODB_LOCKS表为每个锁等待事务保存了一个或多个行,表示任何阻止其进行的锁请求。该表还包含一行,描述给定行或表的待决锁队列中的每个锁。INNODB_LOCK_WAITS表显示了一个事务持有的哪些锁是其他事务请求的阻塞锁。

InnoDB事务与锁信息的持久化与一致性
由事务和锁表(INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS)暴露的数据代表了快速变化的数据。这与用户表不同,用户表中的数据只有在应用程序发起更新时才会发生变化。底层数据是内部系统管理的数据,可以非常快速地更改。

出于性能方面的原因,以及为了尽量减少事务和锁定表之间的误导性连接,每当发出对任何表的SELECT请求时,InnoDB都会将所需的事务和锁定信息收集到中间缓冲区中。仅当从上一次读取缓冲区以来已经超过0.1秒时,才会刷新该缓冲区。填充这三张表所需的数据以原子性和一致性的方式获取,并保存在这个全局内部缓冲区中,形成一个即时点的“快照”。如果在0.1秒内发生多次表访问(当MySQL处理这些表之间的联结时,几乎肯定会发生这种情况),则使用相同的快照来满足查询。

当您将这些表中的任何一张联结到一个查询中时,都会返回正确的结果,因为这三张表的数据来自同一个快照。因为对这些表的每次查询都不会刷新缓冲区,如果您在十分之一秒内对这些表发出单独的查询,那么每次查询的结果都是相同的。另一方面,如果两次对相同或不同表的单独查询间隔超过十分之一秒,可能会得到不同的结果,因为数据来自不同的快照。

因为在收集事务和锁定数据时,InnoDB必须暂时停止,对这些表过于频繁的查询可能会对其他用户的性能产生负面影响。

因为这些表包含敏感信息(至少INNODB_LOCKS.LOCK_DATA和INNODB_TRX.TRX_QUERY),出于安全考虑,只有具有进程权限的用户才能从中选择。

如前所述,填充事务和锁表(INNODB_TRX, INNODB_LOCKS和INNODB_LOCK_WAITS)的数据会被自动获取并保存到一个提供“时间点”快照的中间缓冲区中。当从同一个快照中查询时,所有三个表的数据是一致的。然而,底层数据变化如此之快,以至于其他类似的快速变化的数据可能并不同步。因此,在比较InnoDB事务和锁定表中的数据与PROCESSLIST表中的数据时,你应该小心。PROCESSLIST表中的数据与锁定和事务的数据并不来自同一个快照。即使你只执行了一个SELECT操作(例如,关联INNODB_TRX和PROCESSLIST),这些表的内容通常也不一致。INNODB_TRX可以引用PROCESSLIST中不存在的行,或者INNODB_TRX中显示的当前正在执行的事务的SQL查询。TRX_QUERY可能与PROCESSLIST.INFO中的不同。

MySQL InnoDB与联机DDL

InnoDB与联机DDL
InnoDB联机DDL特性允许就地表更改或并发DML,或两者兼而有之。这个特性的好处包括:
.改进了繁忙的生产环境中的响应性和可用性,在这些环境中,使表在几分钟或几小时内不可用是不现实的。

.使用LOCK子句在DDL操作期间调整性能和并发性之间的平衡的能力。
.LOCK=EXCLUSIVE完全阻塞对表的访问

.LOCK=SHARED允许查询,但不允许DML。

.LOCK=NONE允许对表进行完整的查询和DML访问。

.LOCK=DEFAULT或省略LOCK子句允许尽可能多的并发,具体取决于DDL操作的类型。

.避免了与复制表和重建二级索引相关的磁盘空间使用和I/O开销。

联机DDL概述
联机DDL特性增强了许多DDL操作,这些操作以前需要表复制或阻塞表上的DML操作,或者两者兼而有之。除了大多数ALTER TABLE分区子句之外,分区后的InnoDB表的在线DDL操作遵循与普通InnoDB表相同的规则。一些因素会影响在线DDL操作的性能、空间使用和语义。
.”In-Place?”列显示哪些操作允许使用ALGORITHM=INPLACE子句。

.”Rebuilds Table?”列显示重建表的操作。对于使用INPLACE算法的操作,将就地重建表。对于不支持INPLACE算法的操作,使用表复制方法重建表。

.”Permits Concurrent DML?”列显示哪些操作是完全在线执行的。您可以指定LOCK=NONE来断言在DDL操作期间允许并发DML。MySQL在可能的情况下自动允许并发DML。

在所有在线DDL操作期间都允许并发查询。可以指定LOCK=SHARED来断言在DDL操作期间允许并发查询。MySQL在可能的情况下自动允许并发查询。

.“Notes”列提供额外的信息,并解释与其他列的“Yes/No”值相关的异常和依赖关系。星号表示异常或依赖。

下面的小节提供了各种在线DDL操作的基本语法和用法说明。
添加或删除二级索引
.添加二级索引:

CREATE INDEX name ON table (col_list);

ALTER TABLE table ADD INDEX name (col_list);

.删除二级索引:

DROP INDEX name ON table;

ALTER TABLE table DROP INDEX name;

尽管在CREATE INDEX或DROP INDEX命令中不需要更改语法,但有一些因素会影响此操作的性能、空间使用和语义。

在InnoDB表上创建和删除二级索引会跳过表复制行为。

在创建或删除索引时,表仍然可用于读写操作。CREATE INDEX或DROP INDEX语句仅在所有访问表的事务完成后才完成,因此索引的初始状态反映了表的最新内容。以前,在创建或删除索引时修改表通常会导致死锁,从而取消表上的INSERT、UPDATE或DELETE语句。

在线DDL支持添加二级索引,这意味着通常可以加快创建和加载表及其关联索引的整体过程,方法是创建不带任何二级索引的表,然后在加载数据后添加二级索引。

修改列属性
.修改列的默认值:

ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal;
ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT;

列的默认值存储在表的.frm文件中,而不是InnoDB数据字典中。

.更改列的自动增量值:
ALTER TABLE table AUTO_INCREMENT=next_value;

特别是在使用复制或分片的分布式系统中,有时需要将表的自动递增计数器重置为特定值。插入到表中的下一行使用指定的值作为其自动递增列。您也可以在数据仓库环境中使用此技术,您可以定期清空所有表并重新加载它们,并且可以从1重新启动自动递增序列。

.重命名列
ALTER TABLE tbl CHANGE old_col_name new_col_name datatype;

当您保持相同的数据类型和[NOT] NULL属性时,仅更改列名,此操作始终可以在线执行。

还可以重命名属于外键约束的列。外键定义会自动更新,以使用新的列名。重命名参与外键的列仅适用于ALTER TABLE的in-place模式。如果使用了ALGORITHM=COPY子句,或者其他条件导致命令在后台使用了ALGORITHM=COPY,那么ALTER TABLE语句就会失败。

.使用in-place ALTER TABLE语句扩展VARCHAR的大小:
ALTER TABLE t13 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(50);

VARCHAR列所需的长度字节数必须保持不变。对于0到255的VARCHAR值,需要一个长度字节来编码该值。对于256字节或更多的VARCHAR值,需要两个长度字节。因此,in-place ALTER TABLE只支持将VARCHAR大小从0增加到255字节,或将VARCHAR大小从等于或大于256字节的值增加。In-place ALTER TABLE不支持将VARCHAR大小从小于256字节增加到等于或大于256字节。在这种情况下,所需长度字节的数量将从1变为2,只有表复制(ALGORITHM= copy)才支持这种情况。例如,试图使用in-place ALTER TABLE将VARCHAR列的大小从255更改为256将会返回错误:

mysql> ALTER TABLE t13 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> ALTER TABLE t13 ALGORITHM=COPY, CHANGE COLUMN c1 c1 VARCHAR(256);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

不支持使用in-place ALTER TABLE减少VARCHAR大小。减少VARCHAR的大小需要表的复制(ALGORITHM=COPY)。

mysql> ALTER TABLE t13 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(20);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> ALTER TABLE t13 ALGORITHM=COPY, CHANGE COLUMN c1 c1 VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加或删除外键
.添加或删除外键约束:

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) REFERENCES tbl2(col2) referential_actions;

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

删除外键可以在启用或禁用foreign_key_checks选项的情况下在线执行。在线创建外键需要禁用foreign_key_checks。

如果你不知道某张表上的外键约束的名称,执行下面的语句,并在每个外键的约束子句中找到约束名称:

SHOW CREATE TABLE table\G

或者,查询INFORMATION_SCHEMA.TABLE_CONSTRAINTS表,使用CONSTRAINT_NAME和CONSTRAINT_TYPE列来标识外键的名称。

你也可以在一条语句中删除外键及其关联索引:

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

如果外键已经存在于被修改的表中(也就是说,它是一个包含FOREIGN KEY …REFERENCE子句的子表),其他的限制适用于在线DDL操作,即使是那些不直接涉及外键列的操作:

.如果对父表的更改通过使用CASCADE或SET NULL参数的on UPDATE或on DELETE子句引起子表的相关更改,则子表上的ALTER TABLE可以等待另一个事务提交。

.同样,如果一张表是外键关系中的父表,即使它不包含任何外键子句,如果INSERT、UPDATE或DELETE语句导致子表中的ON UPDATE或ON DELETE操作,它也可以等待ALTER TABLE完成。

维护CREATE TABLE语句
当你的数据库模式随着新列、数据类型、约束、索引等的变化而变化时,请确保CREATE TABLE语句与最新的表定义保持一致。即使有了在线DDL的性能改进,在开始时创建稳定的数据库结构比创建模式的一部分然后发出ALTER TABLE语句更有效。

对有大量行记录表的二级索引这条准则除外。通常最有效的做法是:先指定除二级索引之外的所有详细信息,然后加载数据,再创建二级索引。如果您知道初始数据是干净的,并且在加载过程中不需要一致性检查,则可以对外键使用相同的技术(首先加载数据,然后设置外键)。

无论CREATE TABLE、CREATE INDEX、ALTER TABLE和类似的语句是如何将一个表组合在一起的,您都可以通过执行语句SHOW CREATE TABLE TABLE \G(为了整洁的格式化,需要大写\G)来捕获重建表所需的SQL。上述输出显示了numeric precision、NOT NULL和CHARACTER SET等子句,这些子句有时是在后台添加的,用户可能希望在新系统上克隆表或设置相同类型的外键列时省去这些子句。

联机DDL性能、并发性和空间需求
联机DDL提高了MySQL操作的几个方面,如性能、并发性、可用性和可伸缩性:
.由于表上的查询和DML操作可以在DDL进行时继续进行,因此访问表的应用程序响应更快。在整个MySQL服务器中减少锁和等待其他资源会带来更大的可伸缩性,即使对于不涉及修改表的操作也是如此。

.对于in-place操作,通过避免重新构建表所需的磁盘I/O和CPU周期,可以最小化数据库的总体负载,并在DDL操作期间保持良好的性能和高吞吐量。

.对于in-place操作,由于读入缓冲池的数据比复制所有数据要少,因此可以避免从内存中清除频繁访问的数据,这在以前可能会在DDL操作之后导致暂时的性能下降。

如果联机操作需要临时排序文件,InnoDB默认在临时文件目录下创建它们,而不是在包含原始表的目录下。如果这个目录不够大,无法容纳这样的文件,您可能需要将tmpdir系统变量设置为另一个目录。另外,您可以使用innodb_tmpdir配置选项为InnoDB在线ALTER TABLE操作定义一个单独的临时目录。

联机DDL的锁选项
当DDL操作改变InnoDB表时,表可能被锁住,也可能不被锁住,这取决于DDL操作的内部工作原理和ALTER table语句的LOCK子句。默认情况下,MySQL在DDL操作期间使用尽可能少的锁;您可以指定这个子句,使锁定比通常情况下更加严格(从而限制并发DML或DML和查询),或者确保某个操作允许某种预期程度的锁定。如果LOCK子句指定的锁级别不适用于特定类型的DDL操作,例如在创建或删除主键时指定LOCK=SHARED或LOCK=NONE,那么该子句就会像断言一样工作,导致语句失败并报错。下面列出了LOCK子句的不同可能性,从最宽松的到最严格的:

.对于LOCK=NONE的DDL操作,查询和并发DML都是允许的。如果不能使用所请求的锁类型执行DDL操作,则该子句会使ALTER TABLE失败,因此,如果保持表完全可用是至关重要的,则指定LOCK=NONE,如果不可能则可以取消DDL。例如,您可以在DDLs中对涉及客户注册或购买的表使用此子句,以避免错误地发出昂贵的ALTER TABLE语句,从而使这些表不可用。

.对于LOCK=SHARED的DDL操作,任何对表的写操作(即DML操作)都会被阻塞,但是可以读取表中的数据。如果请求的锁类型不能执行这种DDL操作,则该子句会使ALTER TABLE失败,因此,如果保持表对查询可用非常重要,则指定LOCK=SHARED,如果不可能,则可以取消DDL。例如,您可以在DDL中为数据仓库中的表使用这个子句,在DDL中可以延迟数据加载操作直到DDL完成,但是不能长时间延迟查询。

.对于使用LOCK=DEFAULT或省略LOCK子句的DDL操作,MySQL针对此类操作使用可用的最低级别的锁,允许并发查询、DML或两者同时使用。当您根据表的工作负载进行预先计划、预先测试的更改,并且知道不会导致任何可用性问题时,将使用此设置。

.对于LOCK=EXCLUSIVE的DDL操作,查询和DML操作都被阻塞。如果请求的锁类型不能执行这种DDL操作,这个子句会使ALTER TABLE失败,因此,如果主要关注的是在尽可能短的时间内完成DDL,则指定LOCK=EXCLUSIVE,并且可以使应用程序在尝试访问表时等待。如果服务器应该是空闲的,您也可以使用LOCK=EXCLUSIVE,以避免对表的意外访问。

在大多数情况下,表上的联机DDL操作等待正在访问表的当前执行的事务提交或回滚,因为在准备DDL语句时,它需要在短时间内独占访问表。同样,联机DDL操作在完成之前需要对表进行短时间的独占访问。因此,联机DDL语句也会等待DDL正在提交或回滚时启动的事务完成。因此,对于执行插入、更新、删除的长时间运行的事务或对表执行SELECT .. FOR UPDATE操作,联机DDL操作可能会超时等待对表的独占访问。

当表处于外键关系中,并且事务在外键关系中的另一个表上显式运行时,可能发生表上的在线DDL操作不等待当前执行的事务完成的情况。在这种情况下,事务在它正在更新的表上持有一个排他的元数据锁,但只持有另一张表上共享的InnoDB表锁(外键检查所需)。共享的InnoDB表锁允许在线DDL操作继续进行,当需要一个互斥的InnoDB表锁时会在提交阶段阻塞操作。当其他事务等待在线DDL操作提交时,此场景可能导致死锁。(参见Bug #48652和Bug #77390)

因为需要一些处理工作来记录并发DML操作所做的更改,然后在最后应用这些更改,所以在线DDL操作可能比阻止其他会话访问表的旧式机制花费更长的时间。原始性能的降低与使用表的应用程序更好的响应性是平衡的。在评估更改表结构的理想技术时,要根据诸如网页加载时间等因素,考虑最终用户对性能的感知。

当CREATE INDEX或ALTER TABLE语句执行完毕时,一个新创建的InnoDB二级索引只包含表中已提交的数据。它不包含任何未提交的值、值的旧版本或标记为删除但尚未从旧索引中删除的值。

In-PlaceDDL操作与Table-Copy DDL操作的性能
在线DDL操作的原始性能在很大程度上取决于该操作是in-place执行,还是需要复制和重建整个表。

in-place DDL带来的性能提升适用于二级索引上的操作,而不是主键索引。InnoDB表中的行存储在一个基于主键组织的聚集索引中,形成了一些数据库系统所称的“索引组织表”。因为表结构与主键紧密联系在一起,所以重新定义主键仍然需要复制数据。

当对主键的操作使用ALGORITHM=INPLACE时,即使数据仍然被复制,它也比使用ALGORITHM=COPY更有效,因为:
.ALGORITHM=INPLACE不需要undo日志记录或相关的重做日志记录。这些操作增加了使用ALGORITHM=COPY的DDL语句的开销。

.二级索引项是预先排序的,因此可以按顺序加载。

.没有使用更改缓冲区,因为没有随机访问插入到二级索引中。

为了判断在线DDL操作的相对性能,你可以使用当前版本和更早版本的MySQL在一个大的InnoDB表上运行这样的操作。还可以在最新的MySQL版本下运行所有性能测试,通过设置old_alter_table系统变量,模拟“before”结果的DDL行为。在会话中发出语句set old_alter_table=1,并测量DDL性能以记录“之前”的数字。然后设置old_alter_table=0以重新启用更新、更快的行为,并再次运行DDL操作以记录“之后”的数字。

要了解DDL操作是in-place进行更改还是执行表复制的基本概念,请查看命令完成后显示的“受影响的行”值。例如,在执行不同类型的DDL操作后,您可能会看到以下行:
.更改列的默认值(超快,完全不影响表数据):

Query OK, 0 rows affected (0.07 sec)

.添加索引(需要时间,但是0行受影响表明表没有被复制):

Query OK, 0 rows affected (21.42 sec)

.更改列的数据类型(需要花费大量时间,并且需要重建表中的所有行):

Query OK, 1671168 rows affected (1 min 35.54 sec)

更改列的数据类型需要重建表中的所有行,VARCHAR大小的更改除外,这可以使用在线ALTER table执行。

例如,在一个大表上运行DDL操作之前,你可能会检查操作的快慢,如下所示:
1.克隆表结构。

2.用少量数据填充克隆表。

3.在克隆表上运行DDL操作。

4.检查“受影响的行”值是否为零。非零值意味着操作需要重建整个表,这可能需要特殊的规划。例如,您可以在计划的停机期间执行DDL操作,或者每次在每个复制从服务器上执行一个。

为了更深入地了解MySQL处理过程中的归约,可以查看DDL操作前后与InnoDB相关的performance_schema和INFORMATION_SCHEMA表,查看物理读、写、内存分配等的数量。

联机DDL操作的空间要求
联机DDL操作对空间的要求如下:
.存放临时日志文件的空间
对于每个创建的索引或修改的表,都有一个这样的日志文件。此日志文件存储在DDL操作期间在表中插入、更新或删除的数据。临时日志文件在需要时通过innodb_sort_buffer_size的值进行扩展,直到达到innodb_online_alter_log_max_size指定的最大值。如果临时日志文件的大小超过上限,那么ALTER TABLE操作将失败,所有未提交的并发DML操作将回滚。因此,如果该选项的值较大,就允许在联机DDL操作期间发生更多的DML,
但也会延长DDL操作结束时表被锁定以应用日志中的数据的时间。

如果操作花费的时间太长,并发的DML修改表的时间太长,导致临时在线日志的大小超过innodb_online_alter_log_max_size配置选项的值,则在线DDL操作失败,并出现DB_ONLINE_LOG_TOO_BIG错误。

.临时排序文件的空间
重建表的在线DDL操作在创建索引时将临时排序文件写入MySQL临时目录(Unix上为$TMPDIR, Windows上为%TEMP%,或由–tmpdir配置变量指定的目录)。每个临时排序文件都足够大,可以容纳新二级索引定义的所有列,以及聚集索引的主键的列,一旦合并到最终表或索引中,这些列就会被删除。此类操作需要的临时空间相当于存储表中数据和索引数据所需要的空间大小。如果重建表的联机DDL操作使用了数据目录(datadir)所在的文件系统上的所有可用磁盘空间,则会导致错误。

从MySQL 5.7.11开始,你可以使用innodb_tmpdir配置选项为联机DDL操作定义一个单独的临时目录。innodb_tmpdir选项的引入是为了避免在联机ALTER TABLE操作重建表时创建大型临时排序文件时可能发生的临时目录溢出。

.中间表文件的空间
一些重建表的联机DDL操作在与原始表相同的目录中创建临时中间表文件,而不是在原地重建表。中间表文件可能需要与原始表大小相等的空间。

联机DDL SQL语法
通常,当对InnoDB表使用ALTER TABLE语句时,不需要做任何特殊的事情来启用联机DDL。一些变体需要使用配置或与ALTER TABLE子句的特定组合。

通过使用ALTER TABLE语句的LOCK和ALGORITHM子句,可以控制特定联机DDL操作的各个方面。这些子句位于语句的末尾,用逗号与表和列的规范分隔。LOCK子句用于微调对表的并发访问程度。ALGORITHM子句主要用于性能比较并作为在现有DDL代码遇到任何问题时对旧表复制行为的回退。例如:
.为了避免意外地使表不可读、不可写或不可读和不可写,可以在ALTER table语句中指定一个子句,例如LOCK=NONE(允许读写)或LOCK=SHARED(允许读)。如果请求的并发级别不可用,操作将立即停止。

.为了比较性能,除了设置old_alter_table配置选项外,还可以运行一个ALGORITHM=INPLACE语句和另一个算法ALGORITHM=COPY的语句。

.为了避免复制表的ALTER TABLE操作占用服务器资源,可以使用ALGORITHM=INPLACE子名。如果不能使用in-place机制,该语句将立即停止。

使用联机DDL简化DDL语句
在引入联机DDL之前,将许多DDL操作组合到单个ALTER TABLE语句中是一种常见的做法。因为每个ALTER TABLE语句都涉及复制和重建表,所以一次对同一个表进行多次更改会更加高效,因为这些更改可以通过对表的一次重建操作完成。缺点是涉及DDL操作的SQL代码很难在不同的脚本中维护和重用。如果每次的具体更改都不同,则可能需要为每个稍有不同的场景构建一个新的复杂的ALTER TABLE。

对于可以in-place完成的DDL操作,现在,您可以将它们分离为单独的ALTER TABLE语句,以便于编写脚本和维护,而不会牺牲效率。例如,你可以用这样一个复杂的语句:

ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),
CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;

将其分解为可以独立测试和执行的更简单的部分,例如:

ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;

你可能仍然要使用由多个部分组成的ALTER TABLE语句:
.必须按特定顺序执行的操作,例如创建索引,然后使用该索引的外键约束。

.所有操作都使用相同的特定LOCK子句,希望作为一个组成功或失败。

.不能in-place执行的操作,即仍然复制和重建表的操作。

.指定ALGORITHM=COPY或old_alter_table=1的操作,如果需要在特定场景中实现精确的向后兼容性,可以强制表复制行为。

联机DDL实现详细信息
InnoDB表的每个ALTER TABLE操作都受以下几个方面的控制:
.是否对表的物理表示有任何更改,或者是否纯粹是对元数据的更改,而无需触及表本身。

.表中的数据量是保持不变、增加还是减少

.表数据的更改是否涉及聚集索引、二级索引或两者。

.被修改的表与其他表之间是否存在外键关系。机制差异取决于foreign_key_checks配置选项是启用还是禁用。

.表是否被分区。ALTER TABLE的分区子句被转换为涉及一个或多个表的低级操作,这些操作遵循联机DDL的常规规则。

.是否必须复制表数据,表是否可以“in-place”重组,或者两者的组合。

.需要多大程度的锁定,取决于底层数据库操作的性质,或者取决于在ALTER TABLE语句中指定的LOCK子句。

联机DDL错误条件
以下是联机DDL操作可能失败的主要原因:
.如果LOCK子句指定了低级别锁(SHARED或NONE)将与特定类型的DDL操作不兼容。

.如果在等待获得表上的排他锁时发生超时,在DDL操作的初始和最后阶段可能需要短暂的超时。

.MySQL在创建索引时将临时排序文件写入磁盘,如果tmpdir或innodb_tmpdir文件系统耗尽了磁盘空间。

.如果ALTER TABLE花费了很长时间,并且并发的DML对表的修改太多,那么临时在线日志的大小超过了innodb_online_alter_log_max_size配置选项的值。这种情况会导致DB_ONLINE_LOG_TOO_BIG错误。

.如果并发DML对原表定义允许的表进行更改,但新表定义不允许。只有在最后,当MySQL试图应用并发DML语句中的所有更改时,操作才会失败。例如,用户可以在创建唯一索引时向列中插入重复的值,或者在创建主键索引时向列中插入NULL值。并发DML所做的更改优先,并且有效地回滚了ALTER TABLE操作。

尽管配置选项innodb_file_per_table对InnoDB表的表示有显著的影响,但是不管这个选项是启用还是禁用,不管表物理位置是在它自己的.ibd文件中还是在系统表空间中,所有的在线DDL操作都是一样的。

InnoDB有两种类型的索引:代表表中所有数据的聚集索引,以及可选的加速查询的二级索引。因为聚集索引包含其b树节点中的数据值,所以添加或删除聚集索引需要复制数据,并创建表的新副本。而二级索引只包含索引键和主键的值。可以在不复制聚集索引中的数据的情况下创建或删除这种类型的索引。因为每个二级索引都包含主键值的副本(用于在需要时访问聚集索引),所以当更改主键的定义时,也会重新创建所有二级索引。

删除二级索引很简单。只有内部的InnoDB系统表和MySQL数据字典表被更新,以反映索引不再存在的事实。InnoDB将用于索引的存储空间返回到包含它的表空间,以便新的索引或额外的表行可以使用该空间。

为了给已存在的表添加二级索引,InnoDB会扫描表,并使用内存缓冲区和临时文件,根据二级索引键列的值对行进行排序。B树是按键值顺序构建的,这比按随机顺序将行插入索引更高效。因为b树节点在填充时是分裂的,以这种方式构建索引会导致索引的填充因子更高,从而使后续访问更高效。

联机DDL和崩溃恢复
虽然在执行ALTER TABLE语句时服务器崩溃不会丢失数据,但是集群索引和二级索引的崩溃恢复过程是不同的。

如果服务器在创建InnoDB二索引时崩溃,在恢复时,MySQL删除所有部分创建的索引。您必须重新运行ALTER TABLE或CREATE INDEX语句。

当在创建InnoDB集群索引时发生崩溃时,恢复会更加复杂,因为表中的数据必须复制到一个全新的集群索引中。请记住,所有InnoDB表都存储为集群索引。

MySQL通过将原始InnoDB表中的现有数据复制到具有所需索引结构的临时表中来创建新的集群索引。一旦数据被完全复制到这个临时表中,原始表将被重命名为一个不同的临时表名。包含新聚集索引的临时表将使用原始表的名称重命名,原始表将从数据库中删除。

如果在创建新的聚集索引时发生系统崩溃,则不会丢失数据,但是必须使用该过程中存在的临时表来完成恢复过程。由于在此操作期间很少重新创建聚集索引或重新定义大型表上的主键,也很少遇到系统崩溃的情况,因此本手册不提供有关从此场景中恢复的信息。

分区表的联机DDL
大多数ALTER TABLE分区子句不会像常规的非分区InnoDB表那样使用相同的内部联机DDL API。因此,对ALTER TABLE分区子句的联机支持各不相同。

下表显示了每个ALTER TABLE分区语句的联机状态。不管使用的是哪种联机DDL API, MySQL都会尽可能地减少数据复制和锁定。
.”In-Place”列显示哪些操作允许使用ALGORITHM=INPLACE子句。

.”Permits Concurrent DML?”列显示哪些操作是完全联机执行的。您可以指定LOCK=NONE来断言在DDL操作期间允许并发DML。MySQL在可能的情况下自动允许并发DML。

对于支持ALGORITHM={COPY|INPLACE}的操作,可以指定LOCK=SHARED来断言DDL操作期间允许并发查询。MySQL在可能的情况下自动允许并发查询。

.“Notes”列提供了其他信息,并解释了与其他列的“Yes/No”值相关的异常和依赖关系。星号表示异常或依赖项。

使用ALGORITHM=COPY或只允许”ALGORITHM=DEFAULT, LOCK=DEFAULT”的ALTER TABLE分区选项,将使用COPY算法对表进行重新分区。换句话说,使用新的分区方案创建了一个新的分区表。新创建的表包含ALTER table语句应用的任何更改,表数据被复制到新的表结构中。

对分区表执行非分区联机ALTER TABLE操作遵循适用于常规表的相同规则。但是,ALTER TABLE在每个表分区上执行在线操作,由于在多个分区上执行操作,这会导致对系统资源的需求增加。

联机DDL限制
联机DDL操作存在以下限制:
.在TEMPORARY表上创建索引时复制表,而不是使用快速索引创建。这已经被报告为MySQL Bug #39833。

.当用户试图删除外键所需的索引时,InnoDB会处理错误。

.如果表上有ON…CASCADE或ON…SET NULLT约束,ALTER TABLE子句LOCK=NONE是不允许的。

.根据联机DDL操作的内部工作原理和ALTER TABLE语句的LOCK子句,联机DDL操作在DDL操作的初始阶段和最终阶段可能需要在短时间内对表进行排他访问。因此,如果有一个执行插入、更新、删除或对表执行SELECT … FOR UPDATE的长时间运行的事务并且如果在ALTER TABLE进行中启动了类似的长时间运行的事务,则联机DDL操作可能需要等待才能完成。

.当运行一个联机DDL操作时,运行ALTER TABLE语句的线程应用一个来自其他连接线程在同一个表上并发运行的DML操作的“在线日志”。当应用DML操作时,可能会遇到重复键条目错误(错误1062(23000):重复条目),即使重复条目只是临时的,并且会被“在线日志”中的稍后条目恢复。这类似于InnoDB中的外键约束检查,其中约束必须在事务期间保持。

.将InnoDB表的OPTIMIZE TABLE映射到ALTER TABLE以重建表并更新索引统计信息和释放聚集索引中的未使用空间。创建辅助索引的效率不高,因为键是按照它们在主键中出现的顺序插入的。通过添加在线DDL支持,可以重建常规的和分区的InnoDB表。

.在MySQL 5.6之前创建的InnoDB表,对于包括时间列(日期,日期时间或时间戳)的表不支持ALTER TABLE…ALGORITHM=INPLACE且不能使用ALTER TABLE…ALGORITHM=COPY进行重建。在这种情况下,ALTER TABLE…ALGORITHM=INPLACE操作返回如下错误:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

.这些限制通常适用于涉及表复制的大型表上的在线DDL操作:
.不存在暂停在线DDL操作或限制在线DDL操作的I/O或CPU使用的机制DDL操作。

.如果操作失败,回滚在线DDL操作的代价可能很高。

.长时间运行在线DDL操作可能导致复制滞后。在线DDL操作必须在主节点上运行完之后才能在从节点上运行。此外,在主服务器上并发处理的DML只有在从服务器上的DDL操作完成后才会在从服务器上处理(Bug #73196)。

MySQL InnoDB行存储和行格式

InnoDB行存储和行格式
本节讨论InnoDB的特性,如表压缩、可变长度列值的页外存储和大索引键前缀(innodb_large_prefix)是如何由InnoDB表的行格式控制的。它还讨论了选择正确行格式的注意事项,以及MySQL版本之间行格式的兼容性。

InnoDB行存储概述
行和关联列的存储影响查询和DML操作的性能。由于单个磁盘页面中容纳了更多的行,查询和索引查找可以更快地工作,InnoDB缓冲池中所需的缓存内存更少,为数字和短字符串列写更新值所需的I/O也更少。

每个InnoDB表中的数据被划分为多个页。组成每个表的页面排列在称为b-树索引的树状数据结构中。表数据和二级索引都使用这种类型的结构。表示整个表的b树索引称为聚集索引,它是根据主键列组织的。索引数据结构的节点包含该行中所有列的值(对于聚集索引)或索引列和主键列的值(对于辅助索引)。

变长列是此规则的例外。像BLOB和VARCHAR这样的列太长而不能放在b树页上,它们存储在单独分配的磁盘页上,称为溢出页。我们称这样的列为页外列。这些列的值存储在溢出页的单链表中,每个这样的列都有自己的一个或多个溢出页列表。在某些情况下,长列值的全部或前缀存储在b树中,以避免浪费存储空间并消除读取单独页面的需要。

下面的章节描述了如何配置InnoDB表的行格式来控制如何存储可变长度列的值。行格式配置还决定了表压缩特性和大索引键前缀特性(innodb_large_prefix)的可用性。

指定表的行格式
默认的行格式由innodb_default_row_format定义,默认值为DYNAMIC。当没有显式定义ROW_FORMAT表选项或指定ROW_FORMAT= default时,使用默认行格式。

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

表的行格式可以在CREATE table或ALTER table语句中使用ROW_FORMAT表选项显式定义。例如:

mysql> CREATE TABLE t9 (c1 INT) ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.03 sec)

显式定义的ROW_FORMAT设置覆盖隐式默认值。指定ROW_FORMAT=DEFAULT相当于使用隐式默认值。

innodb_default_row_format选项可以动态设置:

mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

有效的innodb_default_row_format选项包括DYNAMIC、COMPACT和REDUNDANT。COMPRESSED行格式不支持在系统表空间中使用,不能被定义为默认行格式。它只能在CREATE TABLE或ALTER TABLE语句中显式指定。尝试将innodb_default_row_format设置为COMPRESSED会返回一个错误:

mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
ERROR 1231 (42000): Variable 'innodb_default_row_format' can't be set to the value of 'COMPRESSED'

当没有明确指定ROW_FORMAT选项或使用ROW_FORMAT=DEFAULT时,新创建的表使用innodb_default_row_format定义的行格式。例如,下面的CREATE TABLE语句使用innodb_default_row_format定义的行格式。

mysql> CREATE TABLE t10 (c1 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t11 (c1 INT) ROW_FORMAT=DEFAULT;
Query OK, 0 rows affected (0.01 sec)


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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='undo/t10'\G
*************************** 1. row ***************************
     TABLE_ID: 498
         NAME: undo/t10
         FLAG: 33
       N_COLS: 4
        SPACE: 500
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='undo/t11'\G
*************************** 1. row ***************************
     TABLE_ID: 499
         NAME: undo/t11
         FLAG: 33
       N_COLS: 4
        SPACE: 501
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.00 sec)

当没有显式指定ROW_FORMAT选项或使用ROW_FORMAT=DEFAULT时,任何重建表的操作都会将表的行格式静默地更改为innodb_default_row_format定义的格式。

表重建操作包括使用ALGORITHM=COPY的ALTER TABLE操作和使用ALGORITHM= INPLACE的ALTER TABLE操作。OPTIMIZE TABLE也是一个表重建操作。

下面的示例演示了一个表重建操作,该操作静默地更改在没有显式定义行格式的情况下创建的表的行格式。

mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t12 (c1 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'undo/t12' \G
*************************** 1. row ***************************
     TABLE_ID: 500
         NAME: undo/t12
         FLAG: 33
       N_COLS: 4
        SPACE: 502
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.01 sec)

mysql> SET GLOBAL innodb_default_row_format=COMPACT;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t12 ADD COLUMN (c2 INT);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'undo/t12' \G
*************************** 1. row ***************************
     TABLE_ID: 501
         NAME: undo/t12
         FLAG: 1
       N_COLS: 5
        SPACE: 503
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.00 sec)

在将现有表的行格式从REDUNDANT或COMPACT更改为DYNAMIC之前,请考虑以下潜在问题。
.如果启用了innodb_large_prefix配置选项,则REDUNDANT和COMPACT行格式支持的最大索引键前缀长度为767字节,而DYNAMIC和COMPRESSED行格式支持的索引键前缀长度为3072字节。在复制环境中,如果innodb_default_row_format在主服务器上被设置为DYNAMIC,在从服务器上被设置为COMPACT,那么下面的DDL语句(没有明确定义行格式)在主服务器上成功,在从服务器上失败:

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));

.导入一个没有显式定义行格式的表,如果源服务器上的innodb_default_row_format设置与目标服务器上的设置不同,则会导致模式不匹配错误。

要查看表的行格式,可以发出SHOW TABLE STATUS语句或查询INFORMATION_SCHEMA.TABLES。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'undo/t12' \G
*************************** 1. row ***************************
     TABLE_ID: 501
         NAME: undo/t12
         FLAG: 1
       N_COLS: 5
        SPACE: 503
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: big_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3370592
 Avg_row_length: 205
    Data_length: 694140928
Max_data_length: 0
   Index_length: 0
      Data_free: 3145728
 Auto_increment: 3326977
    Create_time: 2023-07-06 18:05:18
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:

InnoDB表的行格式决定了它的物理行结构。

DYNAMIC和COMPRESSED行格式
当创建一个ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED的表时,InnoDB可以存储长可变长度的列值(对于VARCHAR, VARBINARY, BLOB和TEXT类型)完全溢出页,集群索引记录只包含一个指向溢出页的20字节指针。InnoDB也将长度大于或等于768字节的固定长度字段编码为可变长度字段。例如,如果字符集的最大字节长度大于3,那么CHAR(255)列可以超过768字节,就像utf8mb4一样。

是否有列存储在页外取决于页大小和行的总大小。当行太长时,InnoDB选择最长的列作为页外存储,直到聚集索引记录适合B-tree页。小于或等于40字节的TEXT和BLOB列始终内联存储。

如果适合的话,DYNAMIC行格式保持了在索引节点中存储整行的效率(就像COMPACT和REDUNDANT格式一样),但是DYNAMIC行格式避免了用长列的大量数据字节填充b树节点的问题。DYNAMIC格式基于这样的思想:如果长数据值的一部分存储在页外,那么将所有值存储在页外通常是最有效的。使用DYNAMIC格式,较短的列可能会保留在B-tree节点中,从而最大限度地减少任何给定行所需的溢出页数。

对于页外存储,COMPRESSED行格式使用了与DYNAMIC行格式类似的内部细节,同时对表和索引数据进行了额外的存储和性能考虑,并使用了更小的页面大小。对于COMPRESSED行格式,KEY_BLOCK_SIZE选项控制在聚集索引中存储多少列数据,以及在溢出页上放置多少列数据。

DYNAMIC和COMPRESSED行格式都支持最多3072字节的索引键前缀。该特性由innodb_large_prefix配置选项控制,默认情况下是启用的。

mysql> select @@innodb_large_prefix;
+-----------------------+
| @@innodb_large_prefix |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.01 sec)

使用COMPRESSED行格式的表可以在file-per-table表空间或一般表空间中创建。system表空间不支持COMPRESSED行格式。为了在file-per-table表空间中存储压缩表,innodb_file_per_table必须启用,innodb_file_format必须设置为Barracuda。innodb_file_per_table和innodb_file_format配置选项不适用于一般表空间。一般表空间支持所有的行格式,但需要注意的是,由于物理页大小不同,压缩表和未压缩表不能在同一通用表空间
中共存。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql>  show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.01 sec)

DYNAMIC表可以存储在file-per-table表空间、通用表空间和系统表空间中。要在系统表空间中存储DYNAMIC表,必须使用CREATE TABLE或ALTER TABLE语句并使用选项tablespace [=] innodb_system。innodb_file_per_table和innodb_file_format配置选项不适用于一般表空间,也不适用于使用TABLESPACE [=] innodb_system表选项在system表空间中存储DYNAMIC表。

可以通过禁用innodb_file_per_table并使用常规的CREATE TABLE或ALTER TABLE语句向系统表空间添加一个DYNAMIC表。innodb_file_format设置将被忽略。DYNAMIC表总是使用Barracuda文件格式。

DYNAMIC和COMPRESSED行格式是COMPACT行格式的变体,因此处理CHAR存储的方式与COMPACT行格式相同。

COMPACT and REDUNDANT行格式
早期版本的InnoDB使用一种未命名的文件格式(现在称为Antelope)来处理数据库文件。对于这种文件格式,表定义为ROW_FORMAT=COMPACT或ROW_FORMAT=REDUNDANT。使用这些行格式,InnoDB将可变长度列(VARCHAR, VARBINARY, BLOB和TEXT类型)的前768字节存储在b树节点的索引记录中,其余的存储在溢出页上。InnoDB还将长度大于或等于768字节的固定长度字段编码为可变长度字段,可以存储在页外。例如,如果字符集的最大字节长度大于3,那么CHAR(255)列可以超过768字节,就像utf8mb4一样。

对于Antelope文件格式,如果列的值为768字节或更少,则不需要溢出页,并且可能会节省一些I/O,因为该值位于b树节点中。这对于相对较短的blob很有效,但可能导致B树节点填充数据而不是键值,从而降低了它们的效率。具有许多BLOB列的表可能会导致B树节点的数据太满,而包含的行太少,从而使整个索引的效率低于行更短或列值存储在页外的情况。

MySQL InnoDB文件格式管理

InnoDB文件格式管理
随着InnoDB的发展,数据文件格式有时需要与以前版本的InnoDB不兼容,以支持新特性。为了在升级和降级的情况下管理兼容性,以及运行不同版本MySQL的系统,InnoDB使用命名文件格式。InnoDB目前支持两种命名文件格式,Antelope和Barracuda。

.Antelope是原始的InnoDB文件格式,以前没有一个名字。它支持紧凑和冗余的InnoDB表行格式。

.Barracuda是最新的文件格式。它支持所有InnoDB行格式,包括较新的compressed和dynamic行格式。与compressed和dynamic行格式相关的特性包括压缩表、页外列的高效存储以及高达3072字节的索引键前缀(innodb_large_prefix)。

这将讨论为新的InnoDB表启用InnoDB文件格式,验证MySQL版本之间不同文件格式的兼容性,以及识别正在使用的文件格式。

InnoDB文件格式设置不适用于存储在一般表空间中的表。通用表空间提供对所有行格式和相关特性的支持。

以下文件格式配置参数有新的默认值:
.innodb_file_format默认值修改为Barracuda。之前的默认值是Antelope。

.innodb_large_prefix默认值修改为ON。之前的默认值是OFF。

以下文件格式配置参数已弃用,并可能在将来的版本中删除:
.innodb_file_format

.innodb_file_format_check

.innodb_file_format_max

.innodb_large_prefix

文件格式配置参数用于创建与MySQL 5.1中早期版本的InnoDB兼容的表。现在MySQL 5.1已经结束了它的产品生命周期,不再需要这些参数。

启用文件格式
innodb_file_format配置选项为file-per-table表空间启用一个InnoDB文件格式。

Barracuda是默认的innodb_file_format设置。在早期版本中,默认的文件格式是Antelope。

innodb_file_format配置选项已弃用,可能在将来的版本中被删除。

当你启动mysqld时,你可以在命令行中设置innodb_file_format的值,或者在选项文件中(Unix上是my.cnf, Windows上是my.ini)。你也可以使用SET GLOBAL语句动态地修改它。

SET GLOBAL innodb_file_format=Barracuda;

使用说明
.InnoDB文件格式设置不适用于存储在一般表空间中的表。通用表空间提供对所有行格式和相关特性的支持。

.当使用CREATE table或ALTER table的TABLESPACE [=] innodb_system 表选项在system表空间中存储动态表时,innodb_file_format设置不适用。

.在创建使用动态行格式的表时,innodb_file_format设置会被忽略。

验证文件格式兼容性
InnoDB包含了一些检查来防止可能发生的崩溃和数据损坏,如果你对运行在旧版本的MySQL服务器上的InnoDB数据文件使用新文件格式可能会发生。这些检查发生在服务器启动时,以及用户第一次访问表时。本节描述这些检查,如何控制它们,以及可能出现的错误和警告条件。

向后兼容性
你只需要在使用最新版本的InnoDB (InnoDB的MySQL 5.5或更高版本)和旧版本(MySQL 5.1或更早版本,内置InnoDB而不是InnoDB插件)时考虑向后的文件格式兼容性。为了尽量减少兼容性问题,你可以在所有MySQL 5.1和更早版本的数据库服务器上使用InnoDB插件进行标准化。

一般来说,新版本的InnoDB创建的表或索引在旧版本的InnoDB中无法安全地读取或写入,而不会有崩溃、挂起、错误结果或损坏的风险。InnoDB提供了一种机制来防范这些情况,并有助于保持数据库文件和InnoDB版本之间的兼容性。这种机制可以让你利用InnoDB版本的一些新特性(如性能改进和bug修复),同时通过防止意外使用创建向下不兼容的磁盘文件的新特性,仍然保留使用旧版本InnoDB数据库的选择。

如果某个版本的InnoDB支持一种特定的文件格式(不管这种格式是否是默认的),你就可以查询和更新任何需要这种格式或更早的格式的表。根据所启用的特定文件格式,只有使用新特性创建新表的操作受到限制。相反,如果表空间包含一个使用不支持的文件格式的表或索引,则根本无法访问它,即使是读访问。

将InnoDB表空间“降级”为早期的Antelope文件格式的唯一方法是将数据复制到使用早期格式的新表中。

确定一个已经存在的InnoDB表空间的文件格式最简单的方法是检查它所包含的表的属性,使用SHOW table STATUS命令或者查询表INFORMATION_SCHEMA.TABLES。如果表的Row_format被报告为’Compressed’或’Dynamic’,则包含该表的表空间支持Barracuda格式。

内部细节
每个InnoDB file-per-table表空间(由一个*.ibd文件)文件标记了文件格式标识符。system表空间(由ibdata文件表示)被标记为在一组InnoDB数据库文件中使用的“最高”文件格式,并且当文件被打开时检查这个标记。

创建一个压缩表,或者一个带有ROW_FORMAT=DYNAMIC的表,更新对应file-per-table .ibd文件的文件头和InnoDB数据字典中的表类型,其中包含Barracuda文件格式的标识符。从那以后,这个表就不能在不支持Barracuda文件格式的InnoDB版本中使用了。为了防止异常行为,InnoDB会在打开表时进行兼容性检查。(在很多情况下,ALTER TABLE语句会重新创建一个表,从而改变它的属性。)

一般的表空间,也用*.ibd文件表示,支持Antelope和Barracuda文件格式。

ib文件集的定义
为了避免混淆,为了讨论的目的,我们将术语“ib文件集”定义为InnoDB作为一个单元管理的操作系统文件集。ib文件集合包括以下文件:

.系统表空间(一个或多个ibdata文件),包含内部系统信息(包括内部目录和undo信息),可能包括用户数据和索引。

.零个或多个单表表空间(也称为“file per table”文件,命名为*.ibd文件)。

.InnoDB日志文件;通常有两个,ib_logfile0和ib_logfile1。用于崩溃恢复和备份。

“ib文件集”不包含包含InnoDB表元数据的对应的.frm文件。.frm文件由MySQL创建和管理,有时会与InnoDB中的内部元数据不同步。

多个表(甚至来自多个数据库)可以存储在一个“ib文件集”中。(在MySQL中,“数据库”是表的逻辑集合,其他系统称之为“模式”或“目录”。)

InnoDB启动时的兼容性检查
当InnoDB打开ib文件集时,为了防止可能的崩溃或数据损坏,它会检查是否完全支持ib文件集中正在使用的文件格式。如果系统在崩溃后重新启动,或者“快速关机”(即innodb_fast_shutdown大于零),磁盘上的数据结构(如redo或undo条目,或doublewrite页面)可能对当前软件来说是“太新的”格式。在恢复过程中,如果访问了这些数据结构,可能会对数据文件造成严重的破坏。文件格式的启动检查在任何恢复过程开始之前进行,从而防止新表的一致性问题或MySQL服务器的启动问题。

从版本InnoDB 1.0.1开始,system表空间记录了ib文件集中任何表空间中任何表使用的“最高”文件格式的标识符或标记。这个文件格式标签的检查由配置参数innodb_file_format_check控制,默认是开启的。

如果system表空间中的文件格式标签比当前执行的软件支持的最高版本更新或更高,并且innodb_file_format_check开启,则在服务器启动时会发出以下错误:

InnoDB: Error: the system tablespace is in a
file format that this version doesn't support

mysql> show variables like 'innodb_file_format_check';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_file_format_check | ON    |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.02 sec)

也可以设置“innodb_file_format”为文件格式名称。如果当前软件不支持指定的文件格式,这样做可以防止InnoDB启动。它还将“高水位标记”设置为指定的值。如果你手动“降级”ib文件集中的所有表,那么设置innodb_file_format_check的能力(在未来的版本中)是很有用的。如果你随后使用旧版本的InnoDB来访问ib文件集,那么你可以依赖启动时的文件格式检查。

在某些有限的情况下,您可能希望启动服务器并使用ib文件集,该文件集是您正在使用的软件不支持的新文件格式。如果你把配置参数innodb_file_format_check设置为OFF, InnoDB会打开数据库,但会在错误日志中显示如下警告信息:

InnoDB: Warning: the system tablespace is in a
file format that this version doesn't support

这是一个危险的设置,因为它允许恢复过程运行,如果之前的关闭是崩溃或“快速关闭”,可能会破坏数据库。

只有当你确定上一次关闭innodb_fast_shutdown=0时,才应该将innodb_file_format_check设置为OFF,这样基本上没有恢复过程发生。

参数innodb_file_format_check只影响打开数据库时发生的操作,不会影响后续操作。相反,参数innodb_file_format(启用特定的格式)只确定是否可以以启用的格式创建新表,对数据库是否可以打开没有影响。

文件格式标记是一个“高水位标记”,如果创建了一个“更高”格式的表,或者访问一个现有的表进行读或写(假设支持它的格式),那么在服务器启动后,它就会增加。如果你以高于运行软件支持的格式访问一个已经存在的表,系统表空间标签不会更新,但是表级别的兼容性检查会应用(并且会发出错误),如14.10.2.2节“打开表时兼容性检查”所述。每当高水位线更新时,innodb_file_format_check的值也会更新,因此执行SELECT @@innodb_file_format_check;显示当前打开的ib文件集中的表使用的、当前执行软件支持的最新文件格式的名称。

mysql> SELECT @@innodb_file_format_check;
+----------------------------+
| @@innodb_file_format_check |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

打开表时检查兼容性
当第一次访问表时,InnoDB(包括一些InnoDB 1.0之前的版本)会检查存储表的表空间的文件格式是否完全支持。这种检查可以防止使用“太新”数据结构的表出现崩溃或损坏。

使用某个版本支持的任何文件格式的所有表都可以读或写(假设用户有足够的权限)。设置系统配置参数innodb_file_format可以防止创建使用特定文件格式的新表,即使给定的版本支持这种文件格式。这样的设置可以用来保持向后兼容性,但它不会阻止访问任何使用支持的格式的表。

如果在创建表时使用了新的文件格式,那么5.0.21之前的MySQL版本不能可靠地使用新版本创建的数据库文件。为了防止各种错误或损坏,InnoDB在打开文件时(例如,第一次访问一张表时)会检查文件格式的兼容性。如果当前运行的InnoDB版本不支持InnoDB数据字典中表类型标识的文件格式,MySQL报告如下错误:

ERROR 1146 (42S02): Table 'test.t1' doesn't exist

InnoDB也会在错误日志中写入一条消息:

InnoDB: table test/t1: unknown table type 33

MySQL 4.1之前的InnoDB版本在数据库文件中不包含表格式标识符,MySQL 5.0.21之前的版本不包含表格式兼容性检查。因此,如果在5.0.21之前的InnoDB版本中使用了较新的文件格式的表,则无法确保操作是正确的。

InnoDB 1.0及更高版本的文件格式管理能力(表空间标记和运行时检查)允许InnoDB尽快验证运行版本的软件能够正确处理数据库中存在的表。

如果你允许InnoDB打开一个包含它不支持的文件格式的数据库(通过设置参数innodb_file_format_check为OFF),本节描述的表级检查仍然适用。

在使用InnoDB插件的MySQL 5.1之前的版本中强烈建议用户不要使用包含Barracuda文件格式表的数据库文件。可以使用Antelope格式重建这样的表。

识别正在使用的文件格式
如果您使用innodb_file_format配置选项启用不同的文件格式,则更改仅适用于新创建的表。此外,当您创建一个新表时,包含该表的表空间被标记为支持表特性所需的“最早”或“最简单”文件格式。例如,如果您启用了Barracuda文件格式,并创建了一个不使用Dynamic或Compressed行格式的新表,那么包含该表的新表空间将被标记为使用Antelope文件格式。

很容易识别给定表使用的文件格式。如果SHOW TABLE STATUS报告的行格式是Compact或Redundant,则表使用Antelope文件格式。如果SHOW TABLE STATUS报告的行格式是Compressed或Dynamic,则表使用Barracuda文件格式。


mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: big_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3370592
 Avg_row_length: 205
    Data_length: 694140928
Max_data_length: 0
   Index_length: 0
      Data_free: 3145728
 Auto_increment: 3326977
    Create_time: 2023-07-06 18:05:18
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:

您还可以使用InnoDB INFORMATION_SCHEMA表识别给定表或表空间使用的文件格式。例如:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='undo/big_table'\G
*************************** 1. row ***************************
     TABLE_ID: 488
         NAME: undo/big_table
         FLAG: 33
       N_COLS: 25
        SPACE: 487
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.00 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='undo/big_table'\G
*************************** 1. row ***************************
         SPACE: 487
          NAME: undo/big_table
          FLAG: 33
   FILE_FORMAT: Barracuda
    ROW_FORMAT: Dynamic
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Single
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 708837376
ALLOCATED_SIZE: 708841472
1 row in set (0.00 sec)

修改文件格式
每个InnoDB表空间文件(名称匹配*.ibd)都被标记为用于创建其表和索引的文件格式。修改文件格式的方法是重新创建表及其索引。重建表及其索引的最简单方法是在每个想要修改的表上使用以下命令:

ALTER TABLE t ROW_FORMAT=format_name;

如果您正在修改文件格式以降级到较旧的MySQL版本,则可能存在表存储格式的不兼容性,需要额外的步骤。

MySQL InnoDB页面压缩

InnoDB页面压缩
innoDB支持页面级别的表压缩,这些表位于file-per-table表空间中。这个特性被称为透明页面压缩。通过使用CREATE TABLE或ALTER TABLE指定compression属性,可以启用页面压缩。支持的压缩算法包括Zlib和LZ4。

支持平台
页面压缩需要稀疏文件和穿孔支持。在带有NTFS的Windows上,以及以下支持mysql的Linux平台子集上支持页面压缩,其中内核级别提供了hole punch支持:
. RHEL 7 and derived distributions that use kernel version 3.10.0-123 or higher
. OEL 5.10 (UEK2) kernel version 2.6.39 or higher
. OEL 6.5 (UEK3) kernel version 3.8.13 or higher
. OEL 7.0 kernel version 3.8.13 or higher
. SLE11 kernel version 3.0-x
. SLE12 kernel version 3.12-x
. OES11 kernel version 3.0-x
. Ubuntu 14.0.4 LTS kernel version 3.13 or higher
. Ubuntu 12.0.4 LTS kernel version 3.2 or higher
. Debian 7 kernel version 3.2 or higher

对于给定的Linux发行版,所有可用的文件系统可能都不支持打孔。

页面压缩工作原理
在写入一页时,使用指定的压缩算法对其进行压缩。压缩后的数据被写入磁盘,在磁盘上打孔机制从页的末尾释放空块。如果压缩失败,数据会原样写入。

Linux上的打孔尺寸
在Linux系统中,文件系统块长度是用于打孔的单位长度。因此,只有当页面数据可以压缩到小于或等于InnoDB页面大小减去文件系统块大小时,页面压缩才有效。例如,如果innodb_page_size=16K,文件系统块大小为4K,则页面数据必须压缩到小于或等于12K,才能穿孔。

Windows上的打孔尺寸
在Windows系统上,用于稀疏文件的底层基础设施是基于NTFS压缩。冲孔尺寸为NTFS压缩单元,是NTFS集群尺寸的16倍。集群大小及其压缩单位如下表所示:

Windows NTFS集群大小和压缩单元
集群大小 压缩单元
512 Bytes 8 KB
1 KB 16 KB
2 KB 32 KB
4 KB 64 KB

在Windows系统上,只有当页面数据可以压缩到小于或等于InnoDB页面大小减去压缩单位大小时,页面压缩才有效。

NTFS集群的默认大小是4K,其中压缩单位大小是64K。这意味着页面压缩对于开箱即用的Windows NTFS配置没有任何好处,因为innodb_page_size的最大值也是64K。

为了让页面压缩在Windows上工作,创建文件系统时必须使用小于4K的集群大小,并且innodb_page_size必须至少是压缩单元大小的两倍。例如,为了让页面压缩在Windows上工作,你可以构建一个512字节的集群文件系统(压缩单位是8KB),并将InnoDB初始化为16K或更高的值。

启用页面压缩
要启用页面压缩,请在CREATE TABLE语句中指定compression属性。例如:

mysql> CREATE TABLE t7 (c1 INT) COMPRESSION="zlib";
Query OK, 0 rows affected (0.03 sec)

您还可以在ALTER TABLE语句中启用页面压缩。但是,ALTER TABLE…COMPRESSION只更新表空间的COMPRESSION属性。在设置新的压缩算法之后,对表空间的写入将使用新的设置,但是要将新的压缩算法应用到现有页面,必须使用OPTIMIZE table重建表。
mysql>  CREATE TABLE t8 (c1 INT);
Query OK, 0 rows affected (0.01 sec)

mysql>  ALTER TABLE t8 COMPRESSION="zlib";
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> OPTIMIZE TABLE t8;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| undo.t8 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| undo.t8 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)

对于Innodb存储引擎不能支持optimize table,Myisam存储引擎支持optimize table。

禁用页面压缩
要禁用页面压缩,请使用ALTER TABLE设置compression =None。在设置COMPRESSION=None后,写入表空间不再使用页面压缩。要解压缩现有页面,必须在设置COMPRESSION=None后使用OPTIMIZE TABLE重新构建表。

mysql> ALTER TABLE t8 COMPRESSION="None";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> OPTIMIZE TABLE t8;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| undo.t8 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| undo.t8 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)

mysql> show create table t8;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t8    | CREATE TABLE `t8` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMPRESSION='None' |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

页面压缩元数据
页面压缩元数据可以在INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES表中找到。在下列列中:
.FS_BLOCK_SIZE:文件系统块大小,打孔时使用的单位大小。

.FILE_SIZE:文件的外观大小,表示未压缩时文件的最大大小。

.ALLOCATED_SIZE:文件的实际大小,即在磁盘上分配的空间量。

在类unix系统上,使用ls -l tablespace_name.ibd以字节为单位给出了明显的文件长度(相当于FILE_SIZE)。要查看磁盘上实际分配的空间大小(相当于ALLOCATED_SIZE),可以使用du –block-size=1 tablespace_name.ibd。–block-size=1选项以字节(而不是块)打印分配的空间,以便与ls -l的输出进行比较。

使用SHOW CREATE TABLE查看当前页面压缩设置(Zlib、Lz4或None)。一个表可能包含具有不同压缩设置的混合页面。

在下面的示例中,从INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES表中检索employees表的页面压缩元数据。

# Create the employees table with Zlib page compression
mysql> CREATE TABLE employees (
    -> emp_no INT NOT NULL,
    -> birth_date DATE NOT NULL,
    -> first_name VARCHAR(14) NOT NULL,
    -> last_name VARCHAR(16) NOT NULL,
    -> gender ENUM ('M','F') NOT NULL,
    -> hire_date DATE NOT NULL,
    -> PRIMARY KEY (emp_no)
    -> ) COMPRESSION="zlib";
Query OK, 0 rows affected (0.01 sec)

# Insert data
mysql> insert into employees values(1,'1985-02-28','yong','jing','M','2023-08-04');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


# Query page compression metadata in INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM
    -> INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='undo/employees'\G
*************************** 1. row ***************************
         SPACE: 498
          NAME: undo/employees
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 98304
ALLOCATED_SIZE: 61440
1 row in set (0.01 sec)

employees表的页面压缩元数据显示,表面文件大小为98304字节,而实际文件大小(使用页面压缩)为61440字节。文件系统块大小为4096字节,即穿孔时使用的块大小。

页面压缩限制和使用说明
.如果文件系统块大小(或Windows上的压缩单位大小)* 2>innodb_page_size禁用页面压缩。

.对于驻留在共享表空间中的表,包括system表空间、临时表空间和一般表空间,不支持页面压缩。

.undo日志表空间不支持页面压缩。

.重做日志页面不支持页面压缩。

.用于空间索引的R树页面不能被压缩。

.属于压缩表(ROW_FORMAT= compressed)的页面保持原样。

.在恢复过程中,更新后的页面将以未压缩的形式输出。

.在不支持所用压缩算法的服务器上加载页压缩表空间会导致I/O错误。

.在降级到不支持页面压缩的MySQL早期版本之前,请解压使用页面压缩特性的表。要解压一张表,运行ALTER table…compression=None和
optimize table。

.如果所使用的压缩算法在Linux和Windows服务器上可用,则可以在Linux和Windows服务器之间复制页压缩表空间。

.在将页面压缩的表空间文件从一台主机移动到另一台主机时保持页面压缩需要一个保留稀疏文件的实用程序。

.与其他平台相比,使用NVMFS的Fusion-io硬件可以实现更好的页面压缩,因为NVMFS旨在利用打孔功能。

.在InnoDB页面大小较大和文件系统块相对较小的情况下使用页面压缩特性可能会导致写入放大。例如,InnoDB页的最大大小为64KB,而文件系统块的大小为4KB,这可能会提高压缩性能,但也可能会增加对缓冲池的需求,从而导致I/O增加和潜在的写放大。

MySQL InnoDB表压缩

InnoDB表压缩
InnoDB表压缩,它支持在file_per_table表空间或普通表空间中的InnoDB表。使用ROW_FORMAT=COMPRESSED属性与CREATE Table或ALTER Table一起启用表压缩。

表压缩概述
InnoDB表压缩,它支持在file_per_table表空间或普通表空间中的InnoDB表。使用ROW_FORMAT=COMPRESSED属性与CREATE Table或ALTER Table一起启用表压缩。

表压缩概述
由于处理器和缓存内存的速度比磁盘存储设备提高得更快,因此许多工作负载都是磁盘绑定的。数据压缩可以实现更小的数据库大小、更少的I/O和更高的吞吐量,而增加CPU利用率的代价很小。压缩对于读密集型应用程序尤其有价值,因为系统有足够的RAM将经常使用的数据保存在内存中。

使用ROW_FORMAT=COMPRESSED创建的InnoDB表可以在磁盘上使用比配置的innodb_page_size值更小的页面大小。更小的页面需要更少的I/O来读写磁盘,这对于SSD设备来说特别有价值。

压缩后的页面大小通过CREATE TABLE或ALTER TABLE KEY_BLOCK_SIZE参数指定。不同的页面大小要求将表放在file-per-table表空间或通用表空间中,而不是放在系统表空间中,因为系统表空间不能存储压缩表。

无论KEY_BLOCK_SIZE值如何,压缩级别都是相同的。当您为KEY_BLOCK_SIZE指定较小的值时,您将获得越来越小的页面所带来的I/O好处。但是,如果指定的值太小,则在数据值无法压缩到足以容纳每个页中的多行时,会有重新组织页的额外开销。表的KEY_BLOCK_SIZE可以有多小是有严格限制的,这取决于每个索引的键列的长度。如果指定的值太小,CREATE TABLE或ALTER TABLE语句将失败。

在缓冲池中,压缩数据保存在小页面中,页面大小基于KEY_BLOCK_SIZE值。为了提取或更新列值,MySQL还使用未压缩的数据在缓冲池中创建一个未压缩的页面。在缓冲池中,对未压缩页面的任何更新也会被重新写回等效的压缩页面。您可能需要调整缓冲池的大小,以容纳压缩和未压缩页面的附加数据,尽管在需要空间时将未压缩页面从缓冲池中取出,然后在下一次访问时再次解压缩。

创建压缩表
压缩表可以在file-per-table表空间中创建,也可以在一般表空间中创建。InnoDB系统表空间不支持表压缩。系统表空间(空间0,.ibdata文件)可以包含用户创建的表,但它也包含内部系统数据,这些数据从未被压缩过。因此,压缩只适用于存储在file-per-table或一般表空间中的表(和索引)。

在file-per-table表空间中创建压缩表
要在file-per-table表空间中创建压缩表,innodb_file_per_table必须启用(MySQL 5.6.6默认设置),innodb_file_format必须设置为Barracuda。您可以在MySQL配置文件(my.cnf或my.ini)中设置这些参数,也可以使用set语句动态设置这些参数

在配置了innodb_file_per_table和innodb_file_format选项后,在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE子句,或两者都指定,以在file-per-table表空间中创建压缩表。

例如,你可以使用以下语句:

mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1
    -> (c1 INT PRIMARY KEY)
    -> ROW_FORMAT=COMPRESSED
    -> KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.01 sec)

在通用表空间中创建压缩表
要在通用表空间中创建压缩表,必须为通用表空间定义FILE_BLOCK_SIZE,该值在创建表空间时指定。FILE_BLOCK_SIZE值必须是相对于innodb_page_size值的有效压缩页大小,并且由CREATE table或ALTER table KEY_BLOCK_SIZE子句定义的压缩表的页大小必须等于FILE_BLOCK_SIZE/1024。例如,innodb_page_size=16384, FILE_BLOCK_SIZE=8192,则表的KEY_BLOCK_SIZE必须为8。

下面的示例演示创建一个通用表空间和添加一个压缩表。本例innodb_page_size的默认值为16K。FILE_BLOCK_SIZE为8192要求压缩表的KEY_BLOCK_SIZE为8。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.02 sec)

mysql> CREATE TABLESPACE ts3 ADD DATAFILE 'ts3.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.01 sec)

注意:
.如果指定ROW_FORMAT=COMPRESSED,则可以省略KEY_BLOCK_SIZE;KEY_BLOCK_SIZE默认为innodb_page_size值的一半。

.如果你指定了一个有效的KEY_BLOCK_SIZE值,你可以忽略ROW_FORMAT=COMPRESSED;自动启用压缩功能。

.要确定KEY_BLOCK_SIZE的最佳值,通常需要为该子句创建具有不同值的同一表的多个副本,然后测量生成的.ibd文件的大小,并查看每个文件在实际工作负载下的性能如何。对于一般的表空间,请记住,删除表并不会减少一般表空间.ibd文件的大小,也不会将磁盘空间返回给操作系统。

.KEY_BLOCK_SIZE值被视为提示;如果需要,InnoDB可以使用不同的大小。对于file-per-table表空间,KEY_BLOCK_SIZE只能小于或等于innodb_page_size值。如果指定的值大于innodb_page_size,则忽略指定的值,并发出警告,并将KEY_BLOCK_SIZE设置为innodb_page_size值的一半。如果innodb_strict_mode=ON,指定无效的KEY_BLOCK_SIZE值将返回错误。对于一般的表空间,有效的KEY_BLOCK_SIZE值取决于表空间的FILE_BLOCK_SIZE设置。

.32k和64k页面大小不支持压缩。

.InnoDB数据页的默认未压缩大小是16KB。根据选项值的组合,MySQL使用1KB、2KB、4KB、8KB或16KB的页大小来存放表空间数据文件(.ibd文件)。实际的压缩算法不受KEY_BLOCK_SIZE值的影响;该值决定每个压缩块的大小,进而影响每个压缩页中可以装入多少行。

.当在file-per-table表空间中创建压缩表时,将KEY_BLOCK_SIZE设置为InnoDB页面大小通常不会导致太多压缩。例如,设置KEY_BLOCK_SIZE=16通常不会导致太多的压缩,因为正常的InnoDB页面大小是16KB。此设置对于具有许多长BLOB、VARCHAR或TEXT列的表可能仍然有用,因为这些值通常可以很好地压缩,因此可能需要更少的溢出页。对于一般的表空间,KEY_BLOCK_SIZE值等于InnoDB页面大小是不允许的。

.表的所有索引(包括聚集索引)使用相同的页面大小进行压缩,这在CREATE table或ALTER table语句中指定。表属性如ROW_FORMAT和KEY_BLOCK_SIZE不是InnoDB表的CREATE INDEX语法的一部分,如果指定了,它们将被忽略(尽管,如果指定了,它们将出现在SHOW CREATE Table语句的输出中)。

压缩表的限制
.5.1之前的MySQL版本不能处理压缩表。

.压缩表不能存储在InnoDB系统表空间中。

.一般表空间可以包含多个表,但是压缩表和未压缩表不能在同一个表空间中共存。

.压缩适用于整个表及其所有关联索引,而不是单独的行,尽管子句名为ROW_FORMAT。

InnoDB表压缩调优
大多数情况下,InnoDB数据存储和压缩中描述的内部优化可以确保系统在压缩数据下运行良好。但是,由于压缩的效率取决于数据的性质,因此您可以做出影响压缩表性能的决定:
.要压缩哪些表。

.要使用的压缩页面大小。

.是否根据运行时性能特征(例如系统压缩和解压缩数据所花费的时间)调整缓冲池的大小。工作负载更像数据仓库(主要是查询)还是OLTP系统(查询和DML的混合)。

.如果系统在压缩表上执行DML操作,并且数据分布的方式在运行时导致代价高昂的压缩失败,那么您可能需要调整其他高级配置选项。

何时使用压缩
一般来说,压缩在包含合理数量的字符串列的表上效果最好,并且数据的读取次数远远多于写入次数。因为没有保证的方法来预测压缩是否有利于特定情况,所以总是使用在代表性配置上运行的特定工作负载和数据集进行测试。在决定压缩哪些表时,请考虑以下因素。

数据特性与压缩
在压缩数据文件时,决定压缩效率的一个关键因素是数据本身的性质。回想一下,压缩的工作原理是识别数据块中重复的字节串。完全随机的数据是最糟糕的情况。典型的数据通常有重复的值,因此压缩效果很好。无论是定义在CHAR、VARCHAR、TEXT还是BLOB列中的字符串,通常压缩效果都很好。另一方面,主要包含二进制数据(整数或浮点数)或之前压缩过的数据(例如JPEG或PNG图像)的表通常不能很好地压缩,或者显著压缩,或者根本压缩不了。

你可以选择是否为每个InnoDB表开启压缩。一张表及其所有索引使用相同的(压缩的)页面大小。可能是包含表中所有列数据的主键(聚集)索引比辅助索引压缩更有效。对于那些行很长的情况,使用压缩可能会导致长列值被存储在“页外”,那些溢出的页面可以很好地压缩。考虑到这些因素,对于许多应用程序,有些表的压缩比其他表的压缩更有效,并且您可能会发现只有在压缩了一部分表的情况下,您的工作负载才能达到最佳性能。

要确定是否要压缩一个特定的表,可以进行实验。通过使用对未压缩表的.ibd文件副本实现LZ77压缩的实用程序(如gzip或WinZip),您可以粗略估计数据压缩的效率。与基于文件的压缩工具相比,MySQL压缩表的压缩更少,因为MySQL根据页面大小(默认为16KB)分块压缩数据。除了用户数据之外,页面格式还包括一些未压缩的内部系统数据。基于文件的压缩工具可以检查更大的数据块,因此可能会在一个巨大的文件中发现更多重复的字符串比MySQL在单个页面中所能找到的多。

在特定表上测试压缩的另一种方法是将一些数据从未压缩的表复制到一个类似的、压缩的表(具有相同的索引)中,并查看生成的.ibd文件的大小。
例如:

mysql> use undo
Database changed
mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET GLOBAL autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
Query OK, 3249 rows affected, 1 warning (0.52 sec)
Records: 3249  Duplicates: 0  Warnings: 1

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 3249 rows affected (0.05 sec)
Records: 3249  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 6498 rows affected (0.10 sec)
Records: 6498  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 12996 rows affected (0.20 sec)
Records: 12996  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 25992 rows affected (0.51 sec)
Records: 25992  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 51984 rows affected (0.78 sec)
Records: 51984  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 103968 rows affected (1.40 sec)
Records: 103968  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 207936 rows affected (2.84 sec)
Records: 207936  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 415872 rows affected (5.72 sec)
Records: 415872  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 831744 rows affected (11.46 sec)
Records: 831744  Duplicates: 0  Warnings: 0

mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 1663488 rows affected (22.86 sec)
Records: 1663488  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;
Query OK, 0 rows affected (48.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE big_table\G
*************************** 1. row ***************************
       Table: big_table
Create Table: CREATE TABLE `big_table` (
  `TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
  `IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
  `COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `GENERATION_EXPRESSION` longtext CHARACTER SET utf8 NOT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3326977 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select count(id) from big_table;
+-----------+
| count(id) |
+-----------+
|   3326976 |
+-----------+
1 row in set (1.09 sec)


mysql> \! ls -l /mysqldata/mysql/undo/big_table.ibd
-rw-r-----. 1 mysql mysql 708837376 Jul  6 18:06 /mysqldata/mysql/undo/big_table.ibd


mysql> CREATE TABLE key_block_size_4 LIKE big_table;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO key_block_size_4 SELECT * FROM big_table;
Query OK, 3326976 rows affected (2 min 52.11 sec)
Records: 3326976  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> \! ls -l /mysqldata/mysql/undo/key_block_size_4.ibd
-rw-r-----. 1 mysql mysql 155189248 Jul  6 18:11 /mysqldata/mysql/undo/key_block_size_4.ibd

这个实验产生了以下数字,当然,根据您的表结构和数据,这些数字可能会有很大的不同:
-rw-r-----. 1 mysql mysql 708837376 Jul  6 18:06 /mysqldata/mysql/undo/big_table.ibd
-rw-r-----. 1 mysql mysql 155189248 Jul  6 18:11 /mysqldata/mysql/undo/key_block_size_4.ibd

要查看压缩对您的特定工作负载是否有效:
.对于简单的测试,使用一个没有其他压缩表的MySQL实例,并对INFORMATION_SCHEMA.INNODB_CMP表进行查询。

.对于涉及多个压缩表的工作负载的更复杂的测试,可以对INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表进行查询。因为收集INNODB_CMP_PER_INDEX表中的统计信息非常昂贵,所以你必须在查询这张表之前启用配置选项innodb_cmp_per_index_enabled,并且你可以将这样的测试限制在开发服务器或非关键的从服务器上。

.对正在测试的压缩表运行一些典型的SQL语句。

.通过查询INFORMATION_SCHEMA.INNODB_CMP或INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表,并比较COMPRESS_OPS和COMPRESS_OPS_OK,可以查看成功压缩操作与总体压缩操作的比率。

.如果成功完成的压缩操作的百分比很高,则该表可能是压缩的一个很好的候选者。

.如果压缩失败的比例很高,您可以调整innodb_compression_level、innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max选项,并尝试进一步的测试。

数据库压缩与应用程序压缩
决定压缩应用程序中的数据还是表中的数据;不要对同一数据同时使用两种压缩方式。当压缩应用程序中的数据并将结果存储在压缩表中时,几乎不可能节省额外的空间,而双重压缩只会浪费CPU周期。

在数据库中压缩
当启用时,MySQL表压缩是自动的,并且适用于所有列和索引值。这些列仍然可以使用LIKE之类的操作符进行测试,即使索引值被压缩了,排序操作仍然可以使用索引。因为索引通常占数据库总大小的很大一部分,所以压缩可以显著节省存储、I/O或处理器时间。压缩和解压缩操作发生在数据库服务器上,这可能是一个强大的系统,其大小可以处理预期的负载。

应用程序压缩
如果在应用程序中压缩文本等数据,在将其插入数据库之前,通过压缩某些列而不压缩其他列,可以为压缩效果不好的数据节省开销。这种方法使用CPU周期在客户机机器上而不是数据库服务器上进行压缩和解压缩,这可能适用于具有许多客户机的分布式应用程序,或者客户机机器有空闲CPU周期的情况。

混合方法
当然,也可以结合使用这两种方法。对于某些应用程序,可能适合使用一些压缩表和一些非压缩表。最好从外部压缩一些数据(并将其存储在未压缩的表中),并允许MySQL压缩应用程序中的其他表。与往常一样,预先设计和真实的测试对于做出正确的决定是有价值的。

工作负载特征和压缩
除了选择要压缩的表(和页面大小)之外,工作负载是性能的另一个关键决定因素。如果应用程序主要是读操作,而不是更新操作,那么在索引页耗尽MySQL为压缩数据维护的每页“修改日志”的空间后,需要重新组织和重新压缩的页面就会更少。如果更新主要是更改非索引的列,或者那些包含blob或大字符串的列,它们恰巧存储在“页外”,那么压缩的开销是可以接受的。如果对表的唯一更改是使用单调递增主键的插入,并且辅助索引很少,那么就不需要重新组织和重新压缩索引页。由于MySQL可以通过修改未压缩的数据“就地”删除压缩页上的行,因此表上的删除操作是相对高效的。

对于某些环境,加载数据所需的时间可能与运行时检索同样重要。特别是在数据仓库环境中,许多表可能是只读的或大多数情况下是只读的。在这些情况下,以增加加载时间为代价进行压缩可能是可接受的,也可能是不可接受的,除非由此减少磁盘读取或存储成本的节省非常显著。

基本上,当CPU时间可以用于压缩和解压缩数据时,压缩工作得最好。因此,如果您的工作负载是I/O绑定的,而不是cpu绑定的,您可能会发现压缩可以提高整体性能。当您使用不同的压缩配置测试应用程序性能时,请在与生产系统的计划配置类似的平台上进行测试。

配置特点及压缩
从磁盘读写数据库页面是系统性能最慢的方面。压缩试图通过使用CPU时间来压缩和解压缩数据来减少I/O,当I/O相对于处理器周期来说是一种稀缺资源时,这种方法最有效。

当运行在具有快速多核cpu的多用户环境中时,情况尤其如此。当压缩表的一页在内存中时,MySQL通常会使用额外的内存,通常是16KB,在缓冲池中用于页面的未压缩副本。自适应LRU算法试图平衡压缩页和未压缩页之间的内存使用,以考虑工作负载是以I/ o绑定方式还是cpu绑定方式运行。尽管如此,在使用压缩表时,为缓冲池提供更多内存的配置往往比内存高度受限的配置运行得更好。

选择压缩页面大小
压缩页大小的最佳设置取决于表及其索引包含的数据的类型和分布。压缩后的页面大小应该总是大于最大记录大小,否则操作可能会失败,正如b树页面压缩中提到的那样。

将压缩页的大小设置得太大会浪费一些空间,但并不需要经常压缩页。如果压缩页大小设置得太小,插入或更新可能需要耗时的重新压缩,并且b树节点可能需要更频繁地分割,导致数据文件更大和索引效率更低。

通常,您可以将压缩页面大小设置为8K或4K字节。考虑到InnoDB表的最大行大小大约是8K, KEY_BLOCK_SIZE=8通常是一个安全的选择。

在运行时监控InnoDB表压缩
应用程序的整体性能、CPU和I/O利用率以及磁盘文件的大小都可以很好地反映压缩对应用程序的有效性。基于“InnoDB表的压缩调优”中的性能调优建议,并展示如何发现在初始测试中可能不会出现的问题。

为了更深入地了解压缩表的性能考虑因素,你可以使用INFORMATION_SCHEMA方案中的表来监控运行时的压缩性能。这些表反映了内存的内部使用情况和总体的压缩率。

INNODB_CMP表报告了每个压缩页大小(KEY_BLOCK_SIZE)的压缩活动信息。这些表中的信息是系统范围的:它总结了数据库中所有压缩表的压缩统计信息。当没有其他压缩表被访问时,通过检查这些表,可以使用这些数据来帮助决定是否要压缩表。它在服务器上的开销相对较低,所以你可以定期在生产服务器上查询它,以检查压缩特性的整体效率。

INNODB_CMP_PER_INDEX表报告了关于单个表和索引的压缩活动的信息。这些信息更有针对性,对于评估压缩效率和诊断一次一个表或索引的性能问题更有用。(因为每个InnoDB表都表示为一个聚集索引,MySQL在这个上下文中没有对表和索引做很大的区分。)INNODB_CMP_PER_INDEX表确实涉及大量的开销,因此它更适合于开发服务器,在那里你可以单独比较不同工作负载、数据和压缩设置的影响。为了防止意外造成这种监控开销,用户必须在查询INNODB_CMP_PER_INDEX表之前启用innodb_cmp_per_index_enabled配置选项。

要考虑的主要统计数据是执行压缩和解压缩操作的数量和时间。由于MySQL在修改后会在b树节点满到无法包含压缩后的数据时拆分b树节点,因此需要将“成功”的压缩操作的数量与此类操作的总体数量进行比较。根据INNODB_CMP和INNODB_CMP_PER_INDEX表中的信息以及应用程序的整体性能和硬件资源利用率,您可以更改硬件配置,调整缓冲池的大小,选择不同的页面大小,或者选择不同的表集进行压缩。

如果压缩和解压缩所需的CPU时间非常多,那么在相同的数据、应用程序工作负载和压缩表集的情况下,改用更快的CPU或多核CPU可以帮助提高性能。增加缓冲池的大小也可能有助于提高性能,这样更多未压缩的页可以驻留在内存中,减少了对仅以压缩形式存在于内存中的页进行压缩的需求。

总体上大量的压缩操作(与应用程序中的插入、更新和删除操作的数量以及数据库的大小相比)可能表明某些压缩表的更新过于频繁,无法进行有效的压缩。如果是这样,请选择更大的页面大小,或者对要压缩的表更有选择性。

如果“成功”的压缩操作数量(COMPRESS_OPS_OK)占压缩操作总数(COMPRESS_OPS)的比例很高,那么系统可能性能良好。如果这个比率很低,那么MySQL会频繁地重组、重新压缩和分裂b树节点。在这种情况下,请避免压缩某些表,或者增大某些已压缩表的KEY_BLOCK_SIZE。如果你关闭了表的压缩,导致应用程序中的“压缩失败”数量超过了总数的1%或2%,那么你可以关闭表的压缩。(在数据加载等临时操作期间,这样的失败率是可以接受的)。

如何压缩InnoDB表
本节描述一些关于InnoDB表压缩的内部实现细节。这里提供的信息可能对性能调优有帮助,但对于压缩的基本使用来说没有必要知道。

压缩算法
有些操作系统在文件系统级别实现压缩。文件通常被划分为固定大小的块,然后被压缩为可变大小的块,这很容易导致碎片。每次数据块中的某些内容被修改时,整个数据块都会被重新压缩,然后再写入磁盘。这些特性使得这种压缩技术不适合在更新密集型数据库系统中使用。

MySQL在著名的zlib库的帮助下实现了压缩,该库实现了LZ77压缩算法。该压缩算法成熟、稳定,在CPU利用率和数据压缩方面都是有效的。该算法是“无损”的,因此原始未压缩的数据总是可以从压缩的形式中重构出来。LZ77压缩的工作原理是找出在要压缩的数据中重复出现的数据序列。数据中值的模式决定了它的压缩效果,但典型的用户数据通常会压缩50%或更多。

InnoDB只支持zlib库到1.2.3版本。

与应用程序执行的压缩或其他一些数据库管理系统的压缩特性不同,InnoDB压缩同时适用于用户数据和索引。在许多情况下,索引占数据库总大小的40-50%或更多,因此这种差异是显著的。当数据集的压缩工作正常时,InnoDB数据文件的大小(file-per-table表空间或一般的表空间.idb文件)是未压缩大小的25%到50%,甚至可能更小。根据工作负载的不同,这个较小的数据库可以反过来减少I/O,增加吞吐量,但在增加CPU利用率方面代价不大。您可以通过修改innodb_compression_level配置选项来调整压缩级别和CPU开销之间的平衡。

InnoDB数据存储和压缩
InnoDB表中的所有用户数据都存储在由b树索引(聚集索引)组成的页中。在其他一些数据库系统中,这种类型的索引被称为“索引组织表”。索引节点中的每一行包含(用户指定的或系统生成的)主键和表中所有其他列的值。

InnoDB表中的辅助索引也是b树,包含成对的值:索引键和指向聚集索引中的某一行的指针。指针实际上是表的主键的值,如果需要索引键和主键以外的列,则用于访问聚集索引。辅助索引记录必须始终能够放置在单个b树页上。

b树节点(集群索引和辅助索引)的压缩与用于存储长VARCHAR、BLOB或TEXT列的溢出页的压缩处理不同,以下各节将对此进行解释。

压缩b树页面
由于b树页经常更新,因此需要特殊处理。重要的是尽量减少b树节点拆分的次数,以及尽量减少解压缩和重新压缩它们的内容的需要。

MySQL使用的一种技术是在b树节点中以未压缩的形式维护一些系统信息,从而方便某些就地更新。例如,这允许在不进行任何压缩操作的情况下删除标记的行。

此外,当索引页发生变化时,MySQL试图避免不必要的解压缩和重压缩。在每个B-tree页面中,系统保存一个未压缩的“修改日志”来记录对页面所做的更改。小记录的更新和插入可以写入该修改日志,而不需要完全重建整个页面。

当修改日志的空间耗尽时,InnoDB解压缩页面,应用更改并重新压缩页面。如果重新压缩失败(这种情况称为压缩失败),则分割b树节点,并重复该过程,直到更新或插入成功。

为了避免在写密集的应用(比如OLTP应用)中频繁发生压缩失败,MySQL有时会在页面中预留一些空白空间(填充),这样修改日志就会很快填满,并且在有足够空间避免拆分页面时重新压缩页面。随着系统跟踪页面拆分的频率,每个页面中剩余的填充空间的大小也会变化。在一个繁忙的服务器上对压缩表进行频繁的写操作,你可以调整innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max配置选项来微调这种机制。

一般来说,MySQL要求InnoDB表中的每个B-tree页至少能容纳两条记录。对于压缩表,这个要求已经放宽了。b树节点的叶子页(无论是主键还是辅助索引)只需要容纳一条记录,但该记录必须以未压缩的形式容纳到每个页面的修改日志中。如果innodb_strict_mode是ON, MySQL会在CREATE TABLE或CREATE INDEX时检查最大行大小。如果行不合适,则会发出以下错误消息:error HY000: Too big row。

如果你在innodb_strict_mode关闭时创建表,并且后续的INSERT或UPDATE语句试图创建一个不适合压缩页面大小的索引项,操作将失败,ERROR 42000:Row size too large。(此错误消息没有指出哪个索引的记录太大,也没有提到该索引记录的长度或该特定索引页上的最大记录大小。)要解决这个问题,用ALTER table重建表并选择一个更大的压缩页大小(KEY_BLOCK_SIZE),缩短任何列前缀索引,或完全禁用压缩ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPACT。

Innodb_strict_mode不适用于普通表空间,普通表空间也支持压缩表。一般表空间的表空间管理规则是严格执行的,独立于innodb_strict_mode。

压缩BLOB、VARCHAR和TEXT列
在InnoDB表中,不属于主键的BLOB、VARCHAR和TEXT列可能会存储在单独分配的溢出页上。我们把这些列称为页外列。其值存储在溢出页的单链表上。

对于以ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED创建的表,BLOB、TEXT或VARCHAR列的值可以完全存储在页外,这取决于它们的长度和整行的长度。对于存储在页外的列,聚集索引记录只包含指向溢出页的20字节指针,每个列一个。是否有列存储在页外取决于页大小和行总大小。当一行太长,不能完全装入聚集索引页时,MySQL选择最长的列进行页外存储,直到该行装入聚集索引页为止。如上所述,如果压缩页中某一行不适合它自己,就会发生错误。

对于以ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED创建的表,小于或等于40字节的TEXT和BLOB列总是行内存储。

在旧版本的MySQL中创建的表使用羚羊文件格式,它只支持ROW_FORMAT= redundancy和ROW_FORMAT=COMPACT。在这些格式中,MySQL将BLOB、VARCHAR和TEXT列的前768字节与主键一起存储在聚集索引记录中。768字节的前缀后面是一个20字节的指针,指向包含该列剩余值的溢出页。

在表采用压缩格式时,写入溢出页的所有数据都“按原样”压缩;也就是说,MySQL对整个数据项应用zlib压缩算法。除了数据之外,压缩溢出页面还包含一个未压缩的头部和尾部,其中包含一个页面校验和和指向下一个溢出页面的链接。因此,如果数据是高度可压缩的,那么较长的BLOB、TEXT或VARCHAR列可以显著节省存储空间,文本数据通常就是这种情况。图像数据,如JPEG,通常已经被压缩,因此不能从存储在压缩表中获得太多好处;双重压缩可能会浪费CPU周期,但节省的空间很少或根本没有。

溢出页的大小与其他页相同。一个包含10列且存储在页外的行会占用10个溢出页,即使所有列的总长度只有8K字节。在未压缩的表中,10个未压缩的溢出页占用160K字节。在一个页面大小为8K的压缩表中,它们只占用80K字节。因此,对于with的表,使用压缩表格式通常更有效长列值。

对于file-per_table表空间,使用16K的压缩页大小可以减少BLOB、VARCHAR或TEXT列的存储和I/O成本,因为这些数据通常压缩得很好,因此可能需要更少的溢出页,即使b树节点本身占用与未压缩形式相同的页。一般的表空间不支持16K的压缩页大小(KEY_BLOCK_SIZE)。

压缩和InnoDB缓冲池
在一个压缩的InnoDB表中,每个压缩页(无论是1K、2K、4K还是8K)对应一个16K字节的未压缩页(如果设置了innodb_page_size,则更小)。为了访问页面中的数据,MySQL会从磁盘中读取压缩过的页面(如果它不在缓冲池中),然后将页面解压缩为原始形式。本节描述了InnoDB如何管理压缩表的页的缓冲池。

为了尽量减少I/O和减少解压缩页面的需要,缓冲池有时同时包含数据库页面的压缩形式和未压缩形式。为了给其他需要的数据库页面腾出空间,MySQL可以从缓冲池中移除未压缩的页面,而将压缩的页面留在内存中。或者,如果页有一段时间没有被访问,则可能将该页的压缩格式写入磁盘,以便为其他数据释放空间。因此,在任何给定的时间,缓冲池可能同时包含页的压缩形式和未压缩形式,或者只包含页的压缩形式,或者两者都不包含。

MySQL使用最近最少使用(least-recently-used, LRU)列表来跟踪哪些页应该保存在内存中,哪些页应该清除,这样热的(频繁访问的)数据就倾向于保存在内存中。当访问压缩表时,MySQL使用自适应LRU算法来实现内存中压缩页和未压缩页的适当平衡。该算法对系统是I/O密集型运行还是cpu密集型运行非常敏感。目标是避免在CPU繁忙时花费过多的处理时间来解压缩页,并避免在CPU有空闲周期可用于解压缩已压缩页(可能已经在内存中)时进行过多的I/O操作。当系统处于I/O绑定时,该算法倾向于移除一个页面的未压缩副本,而不是两个副本,以便为其他磁盘页面腾出更多空间成为内存驻留。当系统使用CPU 绑定时,MySQL倾向于同时移除压缩和未压缩的页面,这样就可以将更多的内存用于“热”页面,从而减少
只对压缩格式的数据进行解压的需求。

压缩和重做日志文件
在将压缩页面写入数据文件之前,MySQL将页面的一个副本写入重做日志(如果它从上次写入数据库以来已经被重新压缩过)。这样做是为了确保重做日志对于崩溃恢复是可用的,即使在zlib库升级的情况下,该更改也会引入与压缩数据的兼容性问题。因此,在使用压缩时,日志文件的大小可能会增加,或者需要更频繁的检查点。日志文件大小或检查点频率的增加量,取决于以需要重新组织和重新压缩的方式修改压缩页的次数。

压缩表需要Barracuda文件格式。要在一个以文件为表的表空间中创建一个压缩表,必须启用innodb_file_per_table,并且必须将innodb_file_format设置为Barracuda。在普通表空间中创建压缩表时,不依赖于innodb_file_format设置。

OLTP工作负载压缩
传统上,InnoDB压缩特性主要被推荐用于只读或以读为主的工作负载,例如在数据仓库配置中。SSD存储设备速度很快,但相对较小和昂贵,这使得压缩对于OLTP工作负载也很有吸引力:高流量、交互式的网站可以通过对频繁插入、更新和删除操作的应用程序使用压缩表来减少它们的存储需求和每秒I/O操作(IOPS)。

MySQL 5.6引入的配置选项允许你针对特定的MySQL实例调整压缩的工作方式,重点是写密集型操作的性能和可伸缩性:
.Innodb_compression_level允许你提高或降低压缩程度。值越高,存储设备上的数据就越多,但压缩时的CPU开销也越大。当存储空间不是很重要,或者你希望数据不是特别可压缩时,较小的值可以减少CPU开销。

.innodb_compression_failure_threshold_pct压缩表更新失败的截止点。当超过这个阈值时,MySQL开始在每个新的压缩页面中留下额外的空闲空间,动态调整空闲空间的数量,直到innodb_compression_pad_pct_max指定的页面大小的百分比

.innodb_compression_pad_pct_max允许您调整每个页面中保留的最大空间大小,以记录对压缩行的更改,而不需要再次压缩整个页面。该值越高,在不重新压缩页面的情况下可以记录的更改越多。MySQL为每个压缩表中的页面使用的空闲空间是可变的,只有当压缩操作在运行时“失败”时,需要执行昂贵的操作来分割压缩页面。

.Innodb_compression_pad_pct_max允许您调整每个页面中保留的最大空间大小,以记录对压缩行的更改,而不需要再次压缩整个页面。该值越高,在不重新压缩页面的情况下可以记录的更改越多。MySQL为每个压缩表中的页面使用的空闲空间是可变的,只有当压缩操作在运行时“失败”时,需要执行昂贵的操作来分割压缩页面。

.innodb_log_compressed_pages允许你禁止将重新压缩的页面的图像写入重做日志。当对压缩数据进行更改时,可能会发生重新压缩。默认启用此选项是为了防止在恢复过程中使用不同版本的zlib压缩算法时发生损坏。如果你确定zlib版本不会改变,禁用innodb_log_compressed_pages来减少修改压缩数据的工作负载的重做日志生成。

因为在处理压缩数据时,有时需要在内存中同时保存一个页面的压缩版本和未压缩版本,所以在使用oltp风格的工作负载时,要准备好增加innodb_buffer_pool_size配置选项的值。

SQL压缩语法警告和错误
在使用file-per-tabl表空间和通用表空间的表压缩特性时可能遇到的语法警告和错误。

针对每个表文件的表空间的SQL压缩语法警告和错误
当innodb_strict_mode被启用时(默认),在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE,如果innodb_file_per_table被禁用或者innodb_file_format被设置为Antelope而不是Barracuda会产生如下错误。

ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

如果当前配置不允许使用压缩表,则不会创建该表。

当innodb_strict_mode被禁用时,在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE,如果innodb_file_per_table被禁用会产生如下警告。

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4. |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=DYNAMIC. |
+---------+------+---------------------------------------------------------------+

如果innodb_file_format设置为Antelope而不是Barracuda,则会发出类似的警告。

这些消息只是警告,而不是错误,而且创建表时没有压缩,就像没有指定选项一样。

“非严格”行为允许您将mysqldump文件导入到不支持压缩表的数据库中,即使源数据库包含压缩表。在这种情况下,MySQL会在ROW_FORMAT=COMPACT中创建表,而不是阻止操作。

要将转储文件导入到一个新的数据库中,并重新创建原始数据库中的表,请确保服务器对innodb_file_format和innodb_file_per_table配置参数有适当的设置。

只有在ROW_FORMAT指定为COMPRESSED或省略时,属性KEY_BLOCK_SIZE才允许使用。使用任何其他ROW_FORMAT指定KEY_BLOCK_SIZE都会产生一个警告,用户可以通过SHOW WARNINGS查看。但是,该表没有被压缩。忽略指定的KEY_BLOCK_SIZE)。

Level   Code Message
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.

如果运行时启用了innodb_strict_mode, KEY_BLOCK_SIZE和任何ROW_FORMAT的组合都会产生一个错误,而不是一个警告,并且表不会被创建。

当innodb_strict_mode关闭时,MySQL创建或修改表,但忽略某些设置,如下所示。你可以在MySQL错误日志中看到警告消息。当innodb_strict_mode开启时,这些指定的选项组合将产生错误,表不会被创建或修改。要查看错误条件的完整描述,请执行SHOW ERRORS语句:示例:

mysql> show variables like 'innodb_strict_mode';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.02 sec)

mysql> CREATE TABLE x (id INT PRIMARY KEY, c INT)  ENGINE=INNODB KEY_BLOCK_SIZE=33333;
ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478)

mysql> SHOW ERRORS;
+-------+------+-------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------+
| Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333. |
| Error | 1005 | Can't create table 'test.x' (errno: 1478) |
+-------+------+-------------------------------------------+

当innodb_strict_mode开启时,MySQL会拒绝无效的ROW_FORMAT或KEY_BLOCK_SIZE参数并抛出错误。当innodb_strict_mode关闭时,MySQL会对忽略的无效参数发出警告而不是错误。innodb_strict_mode默认开启。

当innodb_strict_mode开启时,MySQL拒绝无效的ROW_FORMAT或KEY_BLOCK_SIZE参数。为了兼容MySQL的早期版本,默认不会启用严格模式。相反,MySQL会对被忽略的无效参数发出警告(而不是错误)。

使用SHOW TABLE STATUS是不可能看到选定的KEY_BLOCK_SIZE的。语句SHOW CREATE TABLE显示KEY_BLOCK_SIZE(即使在创建表时忽略了它)。MySQL无法显示表的实际压缩页大小。

通用表空间的SQL压缩语法警告和错误
.如果在创建表空间时没有为普通表空间定义FILE_BLOCK_SIZE,则该表空间不能包含压缩表。如果试图添加压缩表,则返回错误,如下面的例子所示:

mysql> CREATE TABLESPACE ts4 ADD DATAFILE 'ts4.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ERROR 1478 (HY000): InnoDB: Tablespace `ts4` cannot contain a COMPRESSED table

.试图将无效KEY_BLOCK_SIZE设置的表添加到普通表空间会返回错误,如下面的例子所示:

mysql> CREATE TABLESPACE `ts5` ADD DATAFILE 'ts5.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t5 (c1 INT PRIMARY KEY) TABLESPACE ts5 ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=4;
ERROR 1478 (HY000): InnoDB: Tablespace `ts5` uses block size 8192 and cannot contain a table with physical page size 4096

对于普通表空间,表的KEY_BLOCK_SIZE必须等于表空间的FILE_BLOCK_SIZE除以1024。例如,表空间的FILE_BLOCK_SIZE为8192,则表的KEY_BLOCK_SIZE必须为8。

.尝试将一个未压缩行格式的表添加到配置为存储压缩表的一般表空间会返回一个错误,如下面的例子所示:

mysql> CREATE TABLESPACE `ts6` ADD DATAFILE 'ts6.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t6 (c1 INT PRIMARY KEY) TABLESPACE ts6 ROW_FORMAT=COMPACT;
ERROR 1478 (HY000): InnoDB: Tablespace `ts6` uses block size 8192 and cannot contain a table with physical page size 16384

innodb_strict_mode不适用于一般表空间。一般表空间的表空间管理规则是严格执行的,独立于innodb_strict_mode。

MySQL InnoDB全文索引

InnoDB全文索引
FULLTEXT索引是在基于文本的列(CHAR、VARCHAR或TEXT列)上创建的,以帮助加快对这些列中包含的数据的查询和DML操作,从而省略定义为停止词的任何单词。FULLTEXT索引定义为CREATE TABLE语句的一部分,或者使用ALTER TABLE或CREATE index将其添加到现有表中。全文搜索使用MATCH()…对语法。

InnoDB全文索引设计
InnoDB FULLTEXT索引采用倒排索引设计。倒排索引存储一个单词列表,对于每个单词,存储该单词出现在其中的文档列表。为了支持邻近搜索,每个字的位置信息也以字节偏移量的形式存储。

InnoDB全文索引表
当创建一个InnoDB FULLTEXT索引时,会创建一组索引表,示例如下:

mysql> CREATE TABLE opening_lines (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200),
    -> FULLTEXT idx (opening_line)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'undo/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|      453 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_1 |   452 |
|      454 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_2 |   453 |
|      455 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_3 |   454 |
|      456 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_4 |   455 |
|      457 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_5 |   456 |
|      458 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_6 |   457 |
|      448 | undo/FTS_00000000000001bf_BEING_DELETED            |   447 |
|      449 | undo/FTS_00000000000001bf_BEING_DELETED_CACHE      |   448 |
|      450 | undo/FTS_00000000000001bf_CONFIG                   |   449 |
|      451 | undo/FTS_00000000000001bf_DELETED                  |   450 |
|      452 | undo/FTS_00000000000001bf_DELETED_CACHE            |   451 |
|      447 | undo/opening_lines                                 |   446 |
+----------+----------------------------------------------------+-------+
12 rows in set (0.00 sec)

前六个表表示倒排索引,称为辅助索引表。在对传入文档进行标记时,将单个单词(也称为“标记”)与位置信息和相关文档ID (DOC_ID)一起插入到索引表中。根据单词第一个字符的字符集排序权重,将单词完全排序并在六个索引表中进行分区。

倒排索引被划分为六个辅助索引表,以支持并行索引创建。默认情况下,两个线程对单词和相关数据进行标记、排序和插入索引表。线程的数量可以使用innodb_ft_sort_pll_degree选项进行配置。考虑在大型表上创建FULLTEXT索引时增加线程数。

辅助索引表名的前缀是FTS_,后缀是INDEX_*。每个索引表通过索引表名称中的十六进制值与被索引表相关联,该值与被索引表的table_id相匹配。例如,test/opening_lines表的table_id为447,其十六进制值为0x1bf。如前面的示例所示,“1bf”十六进制值出现在与test/opening_lines表相关联的索引表的名称中。

表示FULLTEXT索引的index_id的十六进制值也出现在辅助索引表名中。例如,在辅助表名undo/FTS_00000000000001bf_00000000000002bb_INDEX_1中,十六进制值2bb的十进制值为699。opening_lines表(idx)上定义的索引可以通过查询INFORMATION_SCHEMA来识别。INNODB_SYS_INDEXES表的值(699)。

mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id=699;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      699 | idx  |      447 |   446 |
+----------+------+----------+-------+
1 row in set (0.00 sec)

如果主表是在每个表文件的表空间中创建的,则索引表存储在它们自己的表空间中。

前面示例中显示的其他索引表被称为公共索引表,用于删除处理和存储FULLTEXT索引的内部状态。与为每个全文索引创建的倒排索引表不同,这组表对于在特定表上创建的所有全文索引都是通用的。

即使删除全文索引,也会保留常见的辅助表。当全文索引被删除时,为索引创建的FTS_DOC_ID列将被保留,因为删除FTS_DOC_ID列将需要重建表。管理FTS_DOC_ID列需要使用普通的辅助表。
.FTS_*_DELETED and FTS_*_DELETED_CACHE

包含已删除但其数据尚未从全文索引中删除的文档的文档id (DOC_ID)。FTS_*_DELETED_CACHE是FTS_*_DELETED表的内存版本。

.FTS_*_BEING_DELETED and FTS_*_BEING_DELETED_CACHE
包含要删除的文档的文档id (DOC_ID),这些文档的数据目前正在从全文索引中删除。FTS_*_BEING_DELETED_CACHE表是FTS_*_BEING_DELETED表的内存版本。

.FTS_*_CONFIG
存储关于FULLTEXT索引的内部状态的信息。最重要的是,它存储FTS_SYNCED_DOC_ID,它标识已解析并刷新到磁盘的文档。在崩溃恢复的情况下,FTS_SYNCED_DOC_ID值用于标识尚未刷新到磁盘的文档,以便可以重新解析文档并将其添加回FULLTEXT索引缓存。要查看该表中的数据,请查询INFORMATION_SCHEMA.INNODB_FT_CONFIG表。

InnoDB全文索引缓存
插入文档时,将对其进行标记,并将单个单词和相关数据插入到FULLTEXT索引中。这个过程,即使对于小文档,也可能导致对辅助索引表进行大量小的插入,从而使对这些表的并发访问成为争用点。为了避免这个问题,InnoDB使用FULLTEXT索引缓存来临时缓存索引表中最近插入的行。这个内存缓存结构保存插入,直到缓存满,然后将它们批量刷新到磁盘(到辅助索引表)。可以查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE表用于查看最近插入的行的标记化数据。

缓存和批处理刷新行为避免了对辅助索引表的频繁更新,这可能在繁忙的插入和更新期间导致并发访问问题。批处理技术还避免了对同一个单词的多次插入,并最大限度地减少了重复条目。不是逐个刷新每个单词,而是将相同单词的插入合并并作为单个条目刷新到磁盘,从而提高了插入效率,同时使辅助索引表尽可能小。

innodb_ft_cache_size变量用于配置全文索引缓存大小(以每个表为基础),它影响全文索引缓存刷新的频率。您还可以使用innodb_ft_total_cache_size选项为给定实例中的所有表定义全局全文索引缓存大小限制。

全文索引缓存存储与辅助索引表相同的信息。但是,全文索引缓存仅为最近插入的行缓存标记化的数据。查询时,已经刷新到磁盘(全文辅助表)的数据不会被带回到全文索引缓存中。直接查询辅助索引表中的数据,并且在返回之前,将辅助索引表中的结果与全文索引缓存中的结果合并。

InnoDB全文索引文档ID和FTS_DOC_ID列
InnoDB使用一个唯一的文档标识符,即文档ID (DOC_ID),将全文索引中的单词映射到该单词出现的文档记录。映射需要索引表上的FTS_DOC_ID列。如果没有定义FTS_DOC_ID列,InnoDB会在创建全文索引时自动添加一个隐藏的FTS_DOC_ID列。下面的示例演示了这种行为。

下面的表定义不包含FTS_DOC_ID列:

mysql> CREATE TABLE opening_lines (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

当使用create FULLTEXT index语法在表上创建全文索引时,会返回一个警告,报告InnoDB正在重建表以添加FTS_DOC_ID列。

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

当使用ALTER TABLE向没有FTS_DOC_ID列的表添加全文索引时,也会返回相同的警告。如果你在create TABLE时间创建一个全文索引,并且没有指定FTS_DOC_ID列,InnoDB会添加一个隐藏的FTS_DOC_ID列,没有警告。

在CREATE TABLE时定义一个FTS_DOC_ID列比在一个已经加载了数据的表上创建一个全文索引要便宜。如果在加载数据之前在表上定义了FTS_DOC_ID列,则不必重新构建表及其索引来添加新列。如果你不关心CREATE FULLTEXT INDEX的性能,可以省略FTS_DOC_ID列,让InnoDB为你创建它。InnoDB创建一个隐藏的FTS_DOC_ID列,并在FTS_DOC_ID列上创建一个唯一的索引(FTS_DOC_ID_INDEX)。如果你想创建自己的FTS_DOC_ID列,该列必须定义为BIGINT UNSIGNED NOT NULL,并命名为FTS_DOC_ID(全大写),如下所示:

FTS_DOC_ID列不需要定义为AUTO_INCREMENT列,但是AUTO_INCREMENT可以使加载数据更容易。

mysql> CREATE TABLE opening_lines (
    -> FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

如果选择自己定义FTS_DOC_ID列,则负责管理该列,以避免空值或重复值。FTS_DOC_ID的值不能重复使用,这意味着FTS_DOC_ID的值必须不断增加。

可选地,您可以在FTS_DOC_ID列上创建所需的惟一FTS_DOC_ID_INDEX(全部大写)。

mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果你没有创建FTS_DOC_ID_INDEX, InnoDB会自动创建它。

在MySQL 5.7.13之前,已使用的FTS_DOC_ID值与新的FTS_DOC_ID值之间允许的最大差值为10000。在MySQL 5.7.13及以后版本中,允许的间隙是65535。

为了避免重新构建表,在删除全文索引时保留FTS_DOC_ID列。

InnoDB全文索引删除处理
删除具有全文索引列的记录可能会导致辅助索引表中出现大量小的删除,从而使对这些表的并发访问成为争用点。为了避免这个问题,被删除文档的文档ID (DOC_ID)记录在一个特殊的FTS_*_DELETED表中,当一条记录从索引表中删除时,被索引的记录保留在全文索引中。在返回查询结果之前,使用FTS_*_DELETED表中的信息过滤已删除的Document id。这种设计的好处是,删除是快速和廉价的。缺点是在删除记录后索引的大小不会立即减小。要删除已删除记录的全文索引项,可以在已索引的表上运行OPTIMIZE TABLE命令(innodb_optimize_fulltext_only= on)重建全文索引。

InnoDB全文索引事务处理
InnoDB FULLTEXT索引由于其缓存和批处理行为而具有特殊的事务处理特性。具体来说,FULLTEXT索引上的更新和插入是在事务提交时处理的,这意味着FULLTEXT搜索只能看到提交的数据。下面的示例演示了这种行为。FULLTEXT搜索只在提交插入的行之后返回结果。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
    -> ('Call me Ishmael.','Herman Melville','Moby-Dick'),
    -> ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
    -> ('I am an invisible man.','Ralph Ellison','Invisible Man'),
    -> ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
    -> ('It was love at first sight.','Joseph Heller','Catch-22'),
    -> ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
    -> ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
    -> ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)