分类：MySQL

InnoDB INFORMATION_SCHEMA缓冲池表
InnoDB INFORMATION_SCHEMA缓冲池表提供了缓冲池状态信息和InnoDB缓冲池中页的元数据。这些表是在MySQL 5.6.2中引入的，后来移植到MySQL 5.5 (MySQL 5.5.28)和MySQL 5.1 (MySQL 5.1.66)。

InnoDB INFORMATION_SCHEMA缓冲池表包括下面这些表:

mysql> show tables from information_schema like 'INNODB_BUFFER%';
+-----------------------------------------------+
| Tables_in_information_schema (INNODB_BUFFER%) |
+-----------------------------------------------+
| INNODB_BUFFER_PAGE_LRU                        |
| INNODB_BUFFER_PAGE                            |
| INNODB_BUFFER_POOL_STATS                      |
+-----------------------------------------------+
3 rows in set (0.00 sec)

.INNODB_BUFFER_PAGE:保存InnoDB缓冲池中每一页的信息。

.INNODB_BUFFER_PAGE_LRU:保存了关于InnoDB缓冲池中页的信息，特别是它们在LRU列表中的排序方式，该列表决定了当缓冲池满时从缓冲池中清除哪些页。INNODB_BUFFER_PAGE_LRU表和INNODB_BUFFER_PAGE表有相同的列，不同的是INNODB_BUFFER_PAGE_LRU表有一个LRU_POSITION列而不是BLOCK_ID列。

.INNODB_BUFFER_POOL_STATS:缓冲池状态信息。大部分相同的信息由SHOW ENGINE INNODB STATUS输出提供，或者可以通过使用INNODB缓冲池服务器状态变量获得。

警示:
查询INNODB_BUFFER_PAGE表或INNODB_BUFFER_PAGE_LRU表会引入显著的性能开销。不要在生产系统上查询这些表，除非您意识到您的查询可能产生的性能影响，并确定它是可接受的。为了避免影响性能，在测试实例上重现您想要调查的问题，并在测试实例上运行您的查询。

查询INNODB_BUFFER_PAGE表中的系统数据
该查询通过排除TABLE_NAME值为NULL或包含表名中有斜杠/或句点.的表名表示用户自定义表，提供了包含系统数据的页面的大致数量。

mysql> select count(*) from information_schema.innodb_buffer_page
    -> where table_name is null or (instr(table_name, '/') = 0 and instr(table_name, '.') = 0);
+----------+
| count(*) |
+----------+
|   451428 |
+----------+
1 row in set (1.80 sec)

该查询返回包含系统数据的大约页数、缓冲池页数和包含系统数据的大约百分比的页数。

mysql> select
    -> (select count(*) from information_schema.innodb_buffer_page
    -> where table_name is null or (instr(table_name, '/') = 0 and instr(table_name, '.') = 0)
    -> ) as system_pages,
    -> (
    -> select count(*)
    -> from information_schema.innodb_buffer_page
    -> ) as total_pages,
    -> (
    -> select round((system_pages/total_pages) * 100)
    -> ) as system_page_percentage;
+--------------+-------------+------------------------+
| system_pages | total_pages | system_page_percentage |
+--------------+-------------+------------------------+
|       451428 |      524224 |                     86 |
+--------------+-------------+------------------------+
1 row in set (3.35 sec)

通过查询PAGE_TYPE的值，可以确定缓冲池中系统数据的类型。例如，下面的查询返回包含系统数据的页的10个不同的PAGE_TYPE值:

mysql> select distinct page_type from information_schema.innodb_buffer_page
    -> where table_name is null or (instr(table_name, '/') = 0 and instr(table_name, '.') = 0);
+-------------------+
| page_type         |
+-------------------+
| SYSTEM            |
| INODE             |
| IBUF_INDEX        |
| INDEX             |
| IBUF_BITMAP       |
| TRX_SYSTEM        |
| FILE_SPACE_HEADER |
| UNKNOWN           |
| UNDO_LOG          |
| EXTENT_DESCRIPTOR |
+-------------------+
10 rows in set (2.02 sec)

查询INNODB_BUFFER_PAGE表中的用户数据
这个查询通过统计TABLE_NAME值不为NULL且不像’%INNODB_SYS_TABLES%’的页面，提供了包含用户数据的页面的近似计数。

mysql> select count(*) from information_schema.innodb_buffer_page
    -> where table_name is not null and table_name not like '%innodb_sys_tables%';
+----------+
| count(*) |
+----------+
|    91067 |
+----------+
1 row in set (1.67 sec)

该查询返回包含用户数据的页面的大致数量、缓冲池页面的总数以及包含用户数据的页面的大致百分比。

mysql> select
    -> (select count(*) from information_schema.innodb_buffer_page
    -> where table_name is not null and (instr(table_name, '/') > 0 or instr(table_name, '.') > 0)
    -> ) as user_pages,
    -> (
    -> select count(*)
    -> from information_schema.innodb_buffer_page
    -> ) as total_pages,
    -> (
    -> select round((user_pages/total_pages) * 100)
    -> ) as user_page_percentage;
+------------+-------------+----------------------+
| user_pages | total_pages | user_page_percentage |
+------------+-------------+----------------------+
|      72796 |      524224 |                   14 |
+------------+-------------+----------------------+
1 row in set (3.31 sec)

这个查询将使用缓冲池中的页的用户定义的表标识出来:

mysql> select distinct table_name from information_schema.innodb_buffer_page
    -> where table_name is not null and (instr(table_name, '/') > 0 or instr(table_name, '.') > 0)
    -> and table_name not like '`mysql`.`innodb_%';
+--------------------------------------------------------+
| table_name                                             |
+--------------------------------------------------------+
| `undo`.`opening_lines`                                 |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_3` |
| `mysql`.`help_category`                                |
| `mysql`.`help_topic`                                   |
| `undo`.`big_table`                                     |
| `undo`.`key_block_size_4`                              |
| `sys`.`sys_config`                                     |
| `test`.`FTS_000000000000020c_DELETED_CACHE`            |
| `undo`.`FTS_00000000000001d9_BEING_DELETED`            |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_4` |
| `mysql`.`help_relation`                                |
| `undo`.`t8`                                            |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_1` |
| `mysql`.`slave_relay_log_info`                         |
| `undo`.`FTS_00000000000001d9_BEING_DELETED_CACHE`      |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_5` |
| `mysql`.`help_keyword`                                 |
| `undo`.`employees`                                     |
| `undo`.`t13`                                           |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_2` |
| `mysql`.`time_zone_name`                               |
| `mysql`.`slave_master_info`                            |
| `test`.`t7`                                            |
| `test`.`user`                                          |
| `undo`.`FTS_00000000000001d9_CONFIG`                   |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_6` |
| `undo`.`t9`                                            |
| `undo`.`t14`                                           |
| `test`.`articles`                                      |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_3` |
| `mysql`.`time_zone`                                    |
| `mysql`.`slave_worker_info`                            |
| `test`.`t8`                                            |
| `undo`.`FTS_00000000000001d9_DELETED`                  |
| `undo`.`t1`                                            |
| `undo`.`t10`                                           |
| `test`.`t1`                                            |
| `test`.`FTS_000000000000020c_BEING_DELETED`            |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_4` |
| `mysql`.`plugin`                                       |
| `mysql`.`gtid_executed`                                |
| `mysql`.`time_zone_transition`                         |
| `undo`.`FTS_00000000000001d9_DELETED_CACHE`            |
| `undo`.`t2`                                            |
| `undo`.`t11`                                           |
| `test`.`parent`                                        |
| `test`.`FTS_000000000000020c_BEING_DELETED_CACHE`      |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_5` |
| `mysql`.`server_cost`                                  |
| `mysql`.`time_zone_transition_type`                    |
| `mysql`.`servers`                                      |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_1` |
| `undo`.`t7`                                            |
| `test`.`child`                                         |
| `test`.`FTS_000000000000020c_CONFIG`                   |
| `test`.`FTS_000000000000020c_0000000000000310_INDEX_6` |
| `mysql`.`engine_cost`                                  |
| `mysql`.`time_zone_leap_second`                        |
| `undo`.`FTS_00000000000001d9_00000000000002da_INDEX_2` |
| `undo`.`t12`                                           |
| `test`.`abc`                                           |
| `test`.`FTS_000000000000020c_DELETED`                  |
+--------------------------------------------------------+
62 rows in set (1.81 sec)

查询INNODB_BUFFER_PAGE表中的索引数据
有关索引页的信息，请使用索引的名称查询INDEX_NAME列。例如，下面的查询返回在employees.salaries表上定义的emp_no索引的页数和总数据大小：

mysql> select index_name, count(*) as pages,
    -> round(sum(if(compressed_size = 0, @@global.innodb_page_size, compressed_size))/1024/1024)
    -> as 'total data (mb)'
    -> from information_schema.innodb_buffer_page
    -> where index_name='emp_no' and table_name = '`employees`.`salaries`';
+------------+-------+-----------------+
| index_name | pages | total data (mb) |
+------------+-------+-----------------+
| emp_no     |    44 |               1 |
+------------+-------+-----------------+
1 row in set (1.75 sec)

该查询返回在employees.salaries表上定义的所有索引的页数和总数据大小:

mysql> select index_name, count(*) as pages,
    -> round(sum(if(compressed_size = 0, @@global.innodb_page_size, compressed_size))/1024/1024)
    -> as 'total data (mb)'
    -> from information_schema.innodb_buffer_page
    -> where table_name = '`employees`.`salaries`'
    -> group by index_name;
+------------+-------+-----------------+
| index_name | pages | total data (mb) |
+------------+-------+-----------------+
| emp_no     |    44 |               1 |
| idx_salary |    65 |               1 |
| PRIMARY    |  6086 |              95 |
+------------+-------+-----------------+
3 rows in set (1.73 sec)

查询INNODB_BUFFER_PAGE_LRU表中的LRU_POSITION数据
INNODB_BUFFER_PAGE_LRU表保存了InnoDB缓冲池中页的信息，特别是当缓冲池满时，它们是如何排序的，这决定了哪些页需要从缓冲池中移除。该表的定义与INNODB_BUFFER_PAGE表相同，不同之处在于该表有一个LRU_POSITION列而不是BLOCK_ID列。

该查询计算LRU链表中特定位置上employees.employees表所使用的页数:

mysql> select count(lru_position) from information_schema.innodb_buffer_page_lru
    -> where table_name='`employees`.`employees`' and lru_position < 6570;
+---------------------+
| count(lru_position) |
+---------------------+
|                  13 |
+---------------------+
1 row in set (0.58 sec)

查询INNODB_BUFFER_POOL_STATS表
INNODB_BUFFER_POOL_STATS表提供了类似于SHOW ENGINE INNODB STATUS和INNODB缓冲池状态变量的信息。

mysql> select * from information_schema.innodb_buffer_pool_stats \G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46471
                  DATABASE_PAGES: 17936
              OLD_DATABASE_PAGES: 6640
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4480
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 16823
            PAGES_NOT_MADE_YOUNG: 19769
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 857
            NUMBER_PAGES_CREATED: 17087
            NUMBER_PAGES_WRITTEN: 24505
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3448729
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 2. row ***************************
                         POOL_ID: 1
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 47000
                  DATABASE_PAGES: 17515
              OLD_DATABASE_PAGES: 6485
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4051
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 864
            NUMBER_PAGES_CREATED: 16651
            NUMBER_PAGES_WRITTEN: 23771
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 5410983
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 832
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 3. row ***************************
                         POOL_ID: 2
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46659
                  DATABASE_PAGES: 17733
              OLD_DATABASE_PAGES: 6565
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4544
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 755
            NUMBER_PAGES_CREATED: 16978
            NUMBER_PAGES_WRITTEN: 23908
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3462987
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 640
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 4. row ***************************
                         POOL_ID: 3
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46644
                  DATABASE_PAGES: 17751
              OLD_DATABASE_PAGES: 6572
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4528
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 2251
            PAGES_NOT_MADE_YOUNG: 17746
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 802
            NUMBER_PAGES_CREATED: 16961
            NUMBER_PAGES_WRITTEN: 23848
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 4188354
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 5. row ***************************
                         POOL_ID: 4
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46660
                  DATABASE_PAGES: 17729
              OLD_DATABASE_PAGES: 6564
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4550
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 792
            NUMBER_PAGES_CREATED: 16937
            NUMBER_PAGES_WRITTEN: 24222
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 4314265
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 6. row ***************************
                         POOL_ID: 5
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46748
                  DATABASE_PAGES: 17642
              OLD_DATABASE_PAGES: 6532
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4548
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 790
            NUMBER_PAGES_CREATED: 16852
            NUMBER_PAGES_WRITTEN: 23741
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3449085
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 7. row ***************************
                         POOL_ID: 6
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46821
                  DATABASE_PAGES: 17571
              OLD_DATABASE_PAGES: 6506
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4544
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 727
            NUMBER_PAGES_CREATED: 16844
            NUMBER_PAGES_WRITTEN: 24005
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3852166
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 704
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
*************************** 8. row ***************************
                         POOL_ID: 7
                       POOL_SIZE: 65528
                    FREE_BUFFERS: 46651
                  DATABASE_PAGES: 17749
              OLD_DATABASE_PAGES: 6571
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 4512
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 804
            NUMBER_PAGES_CREATED: 16945
            NUMBER_PAGES_WRITTEN: 23958
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 3606901
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 768
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
8 rows in set (0.00 sec)

为了比较，SHOW ENGINE INNODB STATUS输出和INNODB缓冲池状态变量输出如下所示，基于相同的数据集。

mysql> show engine innodb status \G
......

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8795455488
Dictionary memory allocated 519969
Buffer pool size   524224
Free buffers       373654
Database pages     141626
Old database pages 52435
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19074, not young 37515
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6391, created 135255, written 191958
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 141626, unzip_LRU len: 35757
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65528
Free buffers       46471
Database pages     17936
Old database pages 6640
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16823, not young 19769
0.00 youngs/s, 0.00 non-youngs/s
Pages read 857, created 17087, written 24505
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17936, unzip_LRU len: 4480
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65528
Free buffers       47000
Database pages     17515
Old database pages 6485
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 864, created 16651, written 23771
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17515, unzip_LRU len: 4051
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   65528
Free buffers       46659
Database pages     17733
Old database pages 6565
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 755, created 16978, written 23908
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17733, unzip_LRU len: 4544
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   65528
Free buffers       46644
Database pages     17751
Old database pages 6572
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2251, not young 17746
0.00 youngs/s, 0.00 non-youngs/s
Pages read 802, created 16961, written 23848
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17751, unzip_LRU len: 4528
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   65528
Free buffers       46660
Database pages     17729
Old database pages 6564
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 792, created 16937, written 24222
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17729, unzip_LRU len: 4550
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   65528
Free buffers       46748
Database pages     17642
Old database pages 6532
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 790, created 16852, written 23741
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17642, unzip_LRU len: 4548
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   65528
Free buffers       46821
Database pages     17571
Old database pages 6506
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 727, created 16844, written 24005
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17571, unzip_LRU len: 4544
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   65528
Free buffers       46651
Database pages     17749
Old database pages 6571
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 804, created 16945, written 23958
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17749, unzip_LRU len: 4512
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
......


mysql> show status like 'Innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 230510  9:02:54 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 141626                                           |
| Innodb_buffer_pool_bytes_data         | 2466910208                                       |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 154983                                           |
| Innodb_buffer_pool_pages_free         | 373654                                           |
| Innodb_buffer_pool_pages_misc         | 8944                                             |
| Innodb_buffer_pool_pages_total        | 524224                                           |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 6016                                             |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 31733470                                         |
| Innodb_buffer_pool_reads              | 376                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 15535407                                         |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)

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)

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个小数空格。

使用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中的不同。

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)。

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树节点的数据太满，而包含的行太少，从而使整个索引的效率低于行更短或列值存储在页外的情况。

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增加和潜在的写放大。

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。

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)

InnoDB的AUTO_INCREMENT处理
InnoDB提供了一种可配置的锁机制，可以显著提高将行添加到具有AUTO_INCREMENT列的表中的SQL语句的可伸缩性和性能。要在InnoDB表中使用AUTO_INCREMENT机制，必须将一个AUTO_INCREMENT列定义为索引的一部分，这样就可以在表上执行相当于索引SELECT MAX(ai_col)的查找以获得最大列值。通常，这是通过使某一列成为某些表索引的第一列来实现的。

本节描述AUTO_INCREMENT锁模式的行为，不同AUTO_INCREMENT锁模式设置的使用影响，以及InnoDB如何初始化AUTO_INCREMENT计数器。
.InnoDB AUTO_INCREMENT锁模式
.InnoDB AUTO_INCREMENT锁模式使用影响
.InnoDB AUTO_INCREMENT计数器初始化

InnoDB AUTO_INCREMENT锁模式
用于生成自动增量值的AUTO_INCREMENT锁模式的行为，以及每种锁模式如何影响复制。自动增量锁模式在启动时使用innodb_autoinc_lock_mode配置参数配置。

以下术语用于描述innodb_autoinc_lock_mode设置:
.“INSERT-like”语句

所有在表中生成新行的语句，包括INSERT, INSERT… SELECT,REPLACE,REPLACE… SELECT和LOAD DATA。包括“简单插入”、“批量插入”和“混合模式”插入。

.“简单插入”
可以提前确定要插入的行数的语句(在初始处理语句时)。这包括没有嵌套子查询的单行和多行INSERT和REPLACE语句，但不包括INSERT … ON DUPLICATE KEY UPDATE。

.“混合模式插入”
这些是“简单插入”语句，它们为一些(但不是全部)新行指定自动增量值。下面是一个例子，其中c1是表t1的AUTO_INCREMENT列:

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种“混合模式插入”是INSERT…ON DUPLICATE KEY UPDATE，在最坏的情况下，它实际上是INSERT，然后是UPDATE，其中AUTO_INCREMENT列的分配值可能在更新阶段使用，也可能不使用。

innodb_autoinc_lock_mode配置参数有三种可能的设置。“传统”、“连续”、“交错”锁模式的取值分别为0、1、2。
.innodb_autoinc_lock_mode = 0(“传统”锁模式)

传统的锁模式提供了与MySQL 5.1中引入innodb_autoinc_lock_mode配置参数之前相同的行为。传统的锁模式选项是为了向后兼容、性能测试和解决“混合模式插入”的问题而提供的，因为可能存在语义上的差异。

在这种锁模式下，所有“INSERT-like”语句都获得一个特殊的表级AUTO-INC锁，用于向具有AUTO_INCREMENT列的表中插入数据。该锁通常保持到语句的末尾(而不是事务的末尾)，以确保对给定的INSERT语句序列按可预测和可重复的顺序分配自动递增值，并确保任何给定语句分配的自动递增值是连续的。

在基于语句的复制的情况下，这意味着当从服务器上复制SQL语句时，自动递增列的值与主服务器上相同。多个INSERT语句的执行结果是确定的，从服务器复制的数据与主服务器相同。如果多个INSERT语句生成的自动递增值是交错的，则两个并发INSERT语句的结果将是不确定的，并且不能使用基于语句的复制可靠地将其传播到从服务器。

为了更清楚地说明这一点，考虑一个使用这个表的例子:

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

假设有两个事务正在运行，每个事务将行插入到表中AUTO_INCREMENT列。一个事务正在使用INSERT…SELECT语句，该语句插入1000行，另一个是使用一个简单的INSERT语句插入一行:

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB无法预先告诉Tx1中的INSERT语句从SELECT中检索了多少行，并且它会在语句执行过程中每次分配一个自动递增值。使用一直保持到语句末尾的表级锁，一次只能执行一条引用table t1的INSERT语句，并且不同语句生成的自动递增编号不会交叉。由Tx1 INSERT…SELECT语句生成的自动递增值是连续的，而Tx2中INSERT语句使用的(单个)自动递增值比Tx1中使用的所有自动递增值是小或还是大，这取决于首先执行哪条语句。

只要SQL语句以二进制日志中相同的顺序执行(在使用基于语句的复制时，或在恢复场景中)，结果就与Tx1和Tx2第一次运行时相同。因此，表级别的锁一直保持到语句结束，使得使用自动递增的INSERT语句可以安全地与基于语句的复制一起使用。然而，当多个事务同时执行insert语句时，这些表级锁限制了并发性和可伸缩性。

在前面的示例中，如果没有表级锁，那么Tx2中用于插入的自动递增列的值完全取决于语句执行的时间。如果Tx2的插入是在Tx1的插入运行时执行的(而不是在它开始之前或完成之后)，那么两个INSERT语句分配的特定的自动递增值是不确定的，并且可能在不同的运行中有所不同。

在连续锁模式下，对于预先知道行数的“simple insert”语句InnoDB可以避免使用表级的AUTO-INC锁来处理，并保持执行的确定性和基于语句的复制的安全性。

如果你不使用二进制日志来重放SQL语句作为恢复或复制的一部分，那么交错锁模式可以用来消除所有表级的AUTO-INC锁，以获得更大的并发性和性能，但代价是允许语句分配的自动递增编号之间有间隙，并且可能通过交错并发执行语句分配编号。

.innodb_autoinc_lock_mode = 1 (“连续”锁模式)

这是默认的锁定模式。在这种模式下，“批量插入”使用特殊的AUTO-INC表级锁，并一直保持到语句结束。这适用于所有的INSERT…SELECT，REPLACE … SELECT和LOAD DATA语句。一次只能执行一个持有AUTO-INC锁的语句。如果批量插入操作的源表与目标表不同，则在源表中选择的第一行获得共享锁之后，在目标表上获得AUTO-INC锁。如果批量插入操作的源和目标是同一个表，那么在对所有选定行使用共享锁之后，会使用AUTO-INC锁。

“简单插入”(需要插入的行数是预先知道的)通过在互斥量(轻量级锁)的控制下获取所需的自动递增值的数量来避免表级的AUTO-INC锁，该互斥量只在分配过程中保持，直到语句完成。除非AUTO-INC锁被另一个事务持有，否则不会使用表级的AUTO-INC锁。如果另一个事务持有AUTO-INC锁，则“简单插入”会等待AUTO-INC锁，就像“批量插入”一样。

这种锁模式确保，如果INSERT语句的行数事先不知道(并且随着语句的执行分配了自动递增号)，任何“类INSERT”语句分配的所有自动递增值都是连续的，并且基于语句的复制操作是安全的。

简而言之，这种锁模式显著提高了可伸缩性，同时可以安全地与基于语句的复制一起使用。此外，与“传统”锁模式一样，任何给定语句分配的自动递增数字都是连续的。与“传统”模式相比，任何使用自动递增的语句在语义上都没有变化，但有一个重要的例外。

例外情况是“混合模式插入”，在这种情况下，用户在多行“简单插入”中为某些(但不是全部)行显式地提供AUTO_INCREMENT列的值。对于这样的插入，InnoDB分配的自动增量值比要插入的行数要多。然而，所有自动赋值的值都是连续生成的(因而高于)最近执行的前一个语句自动递增生成的值。“多余”的数字会丢失。

.innodb_autoinc_lock_mode = 2(“交错”锁模式)

在这种锁模式下，没有“INSERT-like”语句使用表级AUTO-INC锁，并且多个语句可以同时执行。这是最快和最具可伸缩性的锁模式，但是当重放来自二进制日志中SQL语句执行基于语句的复制或恢复场景时，它并不安全。

在这种锁模式下，自动增量值保证在所有并发执行的“INSERT-like”语句中是唯一且单调递增的。但是，因为多个语句可以同时生成数字(也就是说，数字的分配是跨语句交叉的)，任何给定语句插入的行生成的值都可能不是连续的。

如果唯一执行的语句是“简单插入”，其中要插入的行数是提前知道的，那么除了“混合模式插入”之外，为单个语句生成的行数没有间隔。然而，当执行“批量插入”时，任何给定语句分配的自动增量值可能存在间隙。

InnoDB AUTO_INCREMENT锁模式使用影响
.对复制使用自动增量

如果使用基于语句的复制，请将innodb_autoinc_lock_mode设置为0或1，并在主服务器和从服务器上使用相同的值。如果使用innodb_autoinc_lock_mode = 2 (” interleaved “)，或者配置主、从不使用相同的锁模式，则不能确保从上的自动增量值与主上的相同。

如果您使用的是基于行或混合格式的复制，那么所有的自动增量锁模式都是安全的，因为基于行的复制对SQL语句的执行顺序不敏感(对于基于语句的复制来说不安全的任何语句混合格式使用基于行的复制来进行处理)。

.“丢失”自动增量值和序列间隙

在所有锁模式(0、1和2)中，如果生成自动递增值的事务回滚，则这些自动递增值将“丢失”。一旦为自动递增的列生成了值，无论“INSERT-like”语句是否完成，以及包含它的事务是否回滚，它都不能回滚。这些丢失的值不会被重用。因此，存储在表的AUTO_INCREMENT列中的值可能会有差距。

.为AUTO_INCREMENT列指定NULL或0
在所有的锁模式下(0、1和2)，如果用户为INSERT语句中的AUTO_INCREMENT列指定了NULL或0，InnoDB将该行视为未指定值，并为其生成一个新值。

.给AUTO_INCREMENT列赋一个负值
在所有锁模式(0、1和2)中，如果给AUTO_INCREMENT列赋一个负值，则不会定义自动递增机制的行为。

.如果AUTO_INCREMENT值大于指定整数类型的最大整数
在所有锁模式(0、1和2)中，如果值大于可以存储在指定整数类型中的最大整数，则不定义自动递增机制的行为。

.“bulk inserts”的自动增量值存在间隔
当innodb_autoinc_lock_mode设置为0(“传统”)或1(“连续”)时，任何给定语句生成的自动增量值都是连续的，没有间隔，因为表级AUTOINC锁一直保持到语句结束，并且一次只能执行一个这样的语句。

当innodb_autoinc_lock_mode设置为2(“交错”)时，“bulk inserts”生成的自动增量值可能会有间隙，但仅当有并发执行“类似插入”语句时才会如此。

对于锁模式1或2，因为对于批量插入，可能不知道每个语句所需的自动增量值的确切数量，并且可能会高估因此连续语句之间可能会出现间隙。

.“混合模式插入”分配的自动增量值

考虑一个“混合模式插入”，其中一个“简单插入”指定了一些结果行(但不是全部)的自动增量值。这样的语句在锁模式0、1和2中的行为不同。例如，假设c1是表t1的AUTO_INCREMENT列，并且最近自动生成的序列号是100。

mysql> CREATE TABLE t1
    -> (
    ->   c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   c2 CHAR(1)
    -> ) ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(100,'x');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 | x    |
+-----+------+
1 row in set (0.00 sec)

现在，考虑下面的“混合模式插入”语句:

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
| 100 | x    |
+-----+------+
5 rows in set (0.00 sec)

下一个可用的自动递增值是103，因为自动递增值是每次分配一个，而不是在语句执行开始时一次性分配的。无论是否有并发执行的“INSERT-like”语句(任何类型)，该结果都为true。

当innodb_autoinc_lock_mode设置为1(“连续”)时，这四个新行也是:

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
| 100 | x    |
+-----+------+
5 rows in set (0.01 sec)

然而，在本例中，下一个可用的自动增量值是105，而不是103，因为在处理语句时分配了四个自动增量值，但只使用了两个。无论是否并发执行“INSERT-like”语句(任何类型)，这个结果都为真。

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

innodb_autoinc_lock_mode设置为模式2(“交错”)，这四个新行是:

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

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
| 100 | x    |
+-----+------+
5 rows in set (0.00 sec)

x和y的值是唯一的，且大于之前生成的任何行。但是，x和y的具体值取决于并发执行语句生成的自动增量值的数量。

最后，考虑以下语句，当最近生成的序列号为4时发出:

mysql> truncate table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(4,'x');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+----+------+
| c1 | c2   |
+----+------+
|  4 | x    |
+----+------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` char(1) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

对于任何innodb_autoinc_lock_mode设置，该语句都会生成一个ERROR 1062 (23000): Duplicate entry ‘5’ for key ‘PRIMARY’，因为5被分配给行(NULL， ‘b’)，并且行(5，’c’)的插入失败。

.修改INSERT语句序列中间的AUTO_INCREMENT列值

在所有锁模式(0、1和2)中，在INSERT语句序列的中间修改AUTO_INCREMENT列值可能会导致“重复条目”错误。例如，如果您执行更新操作，将AUTO_INCREMENT列的值更改为大于当前最大自动递增值的值，则后续的插入操作如果没有指定未使用的自动递增值，可能会遇到“重复条目”错误。下面的例子演示了这种行为。

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1
    -> (c1 int not null auto_increment,
    -> primary key(c1)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(0), (0), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+
3 rows in set (0.00 sec)

mysql> INSERT INTO t1 VALUES(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

< ?pre>
InnoDB AUTO_INCREMENT计数器初始化

如果你为InnoDB表指定了一个AUTO_INCREMENT列，那么InnoDB数据字典中的表句柄中就会包含一个特殊的计数器，叫做自动递增计数器(auto-increment counter)，用于为这一列赋值。这个计数器只存储在主内存中，而不是磁盘中。

为了在服务器重启后初始化一个自动递增计数器，InnoDB在第一次向包含AUTO_INCREMENT列的表中插入数据时，执行等价于下面的语句。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB递增语句检索到的值，并将其分配给列和表的autoincrement计数器。缺省情况下，加1。这个默认值可以通过auto_increment_increment配置设置覆盖。

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

如果表为空，InnoDB使用值1。这个默认值可以被auto_increment_offset配置设置覆盖。

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

如果SHOW TABLE STATUS语句在自动递增计数器初始化之前检查了表，InnoDB会初始化该值，但不会递增。该值被存储起来，供后续插入使用。此初始化使用表上的普通排它锁定读取，锁持续到事务结束。InnoDB为新创建的表初始化自增计数器的过程与此相同。

在自动递增计数器被初始化之后，如果你没有显式地为AUTO_INCREMENT列指定一个值，InnoDB会递增计数器并将新值赋值给列。如果插入显式指定列值的行，且该值大于当前计数器值，则计数器设置为指定的列值。

只要服务器在运行，InnoDB就会使用内存中的自动递增计数器。当服务器停止并重新启动时，InnoDB会在第一次插入表时重新初始化每个表的计数器，如前所述。

服务器重启也会取消CREATE table和ALTER table语句中的AUTO_INCREMENT = N 表选项的影响，你可以在InnoDB表中使用它来设置初始的计数器值或改变当前的计数器值。