Innodb存储引擎表类型

在innodb存储引擎表中，每张表都有个主键，如果在创建表时没有显示地定义主键，则innodb存储引擎会按如下方式选择或创建主键

1.首先表中是否有非空的唯一索引（uni not null）如果有则该列即为主键.

2.不符合上述条件，innodb存储引擎自动创建一个6个字节大小的指针.

Innodb存储引擎的逻辑存储结构和oracle大致相同，所有数据都被逻辑地存放在一个空间中，我们称之为表空间tablespace.表空间又由段segment,区extent,页page组成.

段的介绍

表空间是由各个段组成,常见的段有数据段,索引段,回滚段.innodb存储引擎表是索引组织的，因此数据即索引，索引即数据.

那么数据段即为B+树的叶节点（leaf node segment），索引段即为B+树的非页节点(non-leaf node segment)

Innodb存储引擎对于段的管理是由引擎本身完成，简化了DBA的管理

区的介绍

区是由64个连续的页组成，每个页大小为16kb，即每个区大小为1MB.对于大数据段，innodb存储引擎最多每次可以申请4个区.以此来保证数据的顺序性能。

页的介绍

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

常见的页类型有:

1.数据页（B-tree node）

2.Undo页（undo log page）

3.系统页（system page）

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

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

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

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

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

页头 （页相关的指针以及checksum） 页body （真正的数据） 页尾（存放相关checksum的值）

行的介绍

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

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

4中格式

Compact Row Format

• 默认格式，大多数情况下使用。
• 适用于大多数应用，能够高效地存储和读取数据。
• 采用 不压缩 的方式存储数据，但通过对 NULL 值、短文本列进行优化，能达到合理的存储效率。
• 写入性能较高，因为没有额外的压缩和解压缩开销。

Dynamic Row Format

• 适用于存储 变长列（如 VARCHAR、TEXT 等） 时，能够更有效地使用存储空间。
• 当列数据较大时，它将不再存储数据在行中，而是将数据存储在一个外部页面（off-page）中，只在行中存储数据的指针，这样可以节省存储空间。
• 提供更高的 灵活性，特别是在数据量较大的列（例如 TEXT 类型）上。
• 空间效率高于 Compact 格式，尤其是对于变长数据。

TPS 每秒钟 事务的处理 QPS 每秒种查询的处理

1.datetimp 时间格式

2.char varchar 一个是定长 一个可变长度 （不能同时存在）

3.查看库中表的相关信息

select * from TABLES\G;

触发收集统计信息的方式（确保在执行sql查询时能走最优的执行计划）

1.访问information_schema.table 会收集

2.数据库重启

3.show table status

*************************** 540. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: performance_schema
     TABLE_NAME: innodb_redo_log_files
     TABLE_TYPE: BASE TABLE
         ENGINE: PERFORMANCE_SCHEMA
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2025-03-10 11:12:18
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb3_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
540 rows in set (0.04 sec)

如何统计表的大小

SELECT 
    table_schema AS `Database Name`, 
    table_name AS `Table Name`, 
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS `Data Size (MB)`,
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS `Index Size (MB)`,
    ROUND((SUM(data_length + index_length)) / 1024 / 1024, 2) AS `Total Size (MB)`
FROM 
    information_schema.tables
GROUP BY 
    table_schema, table_name
ORDER BY 
    `Total Size (MB)` DESC;

库的大小

SELECT 
    table_schema AS `Database Name`, 
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS `Data Size (MB)`,
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS `Index Size (MB)`,
    ROUND((SUM(data_length + index_length)) / 1024 / 1024, 2) AS `Total Size (MB)`
FROM 
    information_schema.tables
GROUP BY 
    table_schema
ORDER BY 
    `Total Size (MB)` DESC;

问题： 对于线上的表通过show table status 显示出来的行的信息 与select count(*) from table_name 不一样 这种问题是由于统计信息没有更新会影像表最优的执行计划

表碎片空间产生的原因

1.随机的方式插入新数据，可能导致辅助索引产生大量的碎片，意思是索引page和索引顺序不接近，

2.删除旧数据方式也会产生

• 行删除后：删除的行会标记为空闲，但并不会立即回收占用的空间，导致数据页内有空闲区域未被回收，形成碎片。
• 表空间管理：对于 InnoDB 表，每个表的数据都会存储在多个数据页中（默认情况下，每个数据页大小为 16KB）。删除的行将会“释放”数据页中的空间，但这个空间在没有新的数据行填充时，不会被回收。

更新操作

更新操作也是导致碎片的一个常见原因，尤其是对数据行大小有影响的更新（如更新 VARCHAR 字段的长度，或更新表中大字段的内容）。

• 行扩展或缩小：在 MySQL 中，更新操作可能导致行的大小发生变化。如果一行的长度变大，InnoDB 会将其移动到新的位置，并在旧的页中留下空闲空间；如果行变小，原来占用的空间就变成了未使用的碎片，直到有其他行填充这些空间。
• 数据行迁移：当更新导致数据行的大小改变时（例如修改了一个 VARCHAR 字段的长度），InnoDB 可能会将数据行移到另一个数据页上，而原数据页留下未使用的空间。

插入和删除的频繁交替

• 空闲页：大量删除数据可能会留下大量的空闲页，而这些页在没有其他插入操作时无法被有效利用。
• 未填满的数据页：如果删除的数据量大于插入的数据量，表中可能会有很多没有完全填满的数据页，从而产生碎片。

表的结构修改（如增加/删除列）

重建索引：添加、删除或修改列会导致索引的重建，可能会导致新的数据页分配，旧数据页的空间未被完全清理，形成碎片。

如何计算表中的碎片空间

SHOW TABLE STATUS LIKE 'your_table_name';

Data_free

如何清理

1.修改表的存储引擎 （会对表进行锁，数据从新整理且消耗cpu） 一般晚上进行操作修改 通过crontab定时任务进行处理 优点 数据的连续性比较好 全表扫描比较好

2.删除和重建表的方式

3 对于共享表空间使用导入导出的方式进行优化

2.查看mysql正在运行的方式 show fulll processlists； show engine innodb status;