innodb基础知识

一 两种表空间

1.共享表空间

• 共享表空间必须存在,文件名为ibdata1
• innodb_data_home_dir：设置共享表空间的主目录，默认为datadir指定的值
• innodb_data_file_path定义路径,初始化大小(建议至少1G以上),自动扩展策略
• 例如:innodb_data_file_path = ibdata1:200M:autoextend,当200M填满后,每次增长8MB
• 主要存储对象

       data dictionary

       double write buffer

       insert buffer

       rollback segments

       undo space

       foreign key constraint system tables

       user data,if innodb_file_per_table=0

    因此,在初始化ibdata1时，最好设置大一些，这样就可以避免在高并发情景下导致ibdata1急剧增大，大大影响性能。

    当然在5.7中undo space可以独立出来,不用担心过多的undo space把共享表空间撑大,而无法自动缩减.

 

2.独立表空间

• 设置innodb_file_per_tables = 1,这样每个表都有各自的xx.ibd文件
• Rollback segments,double write ,insert buffer 等仍然存储在共享表空间里
• 主要存储聚集索引B+ 树以及其他的普通索引数据

    注:5.6版本后可以在线修改选项,新建的表都会使用独立表空间,之前使用共享表空间的表,通过 alter table x engine=innodb;可以变成独立表空间.

 

二 表空间比较

1.共享表空间的优势

• 删除大表或者删除大量数据时的开销更小,drop table/truncate table
• 可以使用裸设备(性能可能有所提升)

2.独立表空间的优势

• 表空间更方便回收 
• 透明表空间文件迁移

 

三 独立表空间碎片整理

1.检查碎片

公式:(行数*行平均长度) 计算表应该占用磁盘空间大小 比较 独立表空间.ibd文件大小,如果相差很大,则碎片高需要整理碎片

show table status from school where name='t_gap' \G;
或者
root@localhost:mysql.sock  00:39:08 [school]>show table status like 't_gap' \G;
*************************** 1. row ***************************
           Name: t_gap
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
 Avg_row_length: 4096  行平均长度
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0     大于0表示有碎片
 Auto_increment: NULL
    Create_time: 2016-06-15 00:40:50
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.30 sec)

例如:行平均长度50bytes,5000w * 50bytes=2.38G ,如果独立表空间是5G,碎片还是挺厉害的几乎达到一半,需要整理碎片

还可以用SQL语句来计算表大小

select table_name
,concat(round(data_length/1024/1024),'MB') as data_length
,concat(round(index_length/1024/1024),'MB') as index_length
,concat(round(sum(data_length+index_length)/1024/1024),'MB') as totdb_size 
,table_rows
from information_schema.tables 
where table_schema not in ('information_schema', 'mysql')  
group by table_name;

2.消除碎片

alter table xx engine = innodb;

或者 optimize talbe xx;

最好在业务低谷的时候整理碎片,并且使用pt-osc工具操作.

 

四 表空间回收

  1.独立表空间：alter table xx engine = innodb;
  2.共享表空间：重新导出、导入  

五 存储结构

1. page 页

   最小I/O单位,默认16KB,5.6起可自定义page size

   实际可用16338字节(去掉38bytes FIL header 和 8bytes trailer)

innodb_page_size 

• 5.7 之前innodb_page_size 可定义为 4KB(主要为了适应SSD设备,对齐),8KB,不能调大
• 5.7 以上可以定义为 32KB,64KB
• 全局选项,无法动态调整
• 每个page最少存储两行记录,因为B+tree结构,是双向链表,必须存储至少2条记录,才能前后相互链接.

page页类型

• 数据页(B-Tree node)
• undo页(undo log page)
• 系统页(system page)
• 事务数据页(transaction system page)
• 插入缓冲位图页(insert buffer bitmap page)
• 插入缓冲空闲列表页(insert buffer free list page)
• 未压缩的二进制大对象页(uncompressed blob page)
• 压缩的二进制大对象页(compressed blob page)

2. extent 区

• 空间管理单位
• 每个extent总是1MB,由64个page组成
• 如果page size 是8KB的话,则由128个page组成.

3. segment 段

• 对象单位,例如:rollback seg,undo seg,data seg,index seg等
• 每个seg由N个extent以及32个零散的page组成
• seg最小以extent为单位扩展

4. tablespace 表空间

• 表存储对象
• 每个tablespace都至少包含2个seg(叶子和非叶子 page file seg)

六 行格式

1.row-format

•  Redundant，最早的行格式
•  Compact，5.0.3以后的默认行格式
•  Dynamic，将长字段完全off-page存储
•  Compressed，将data、index pages进行压缩，但buffer pool中的page则不压缩。压缩比约1/2，但tps能下降到原来的1/10 , 性能影响巨大，不建议用

 5.6 FILE_FORMAT : Antelope,  ROW_FORMAT:Compact 

5.7 FILE_FORMAT : Barracuda, ROW_FORMAT:Dynamic
root@localhost:mysql57.sock  06:55:06 [testdb]>SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like '%g_gap%';
+----------+--------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME         | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------+------+--------+-------+-------------+------------+---------------+------------+
|       40 | testdb/g_gap |   33 |      5 |    24 | Barracuda   | Dynamic    |             0 | Single     |
+----------+--------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.13 sec)

5.7查看表.ibd 的space id

root@localhost:mysql57.sock 23:08:51 [(none)]>SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESpaces WHERE NAME like '%g_gap%';
+-------+--------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+--------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| 24 | testdb/g_gap | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
+-------+--------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.00 sec)

  5.7 前 创建如下表,进行监控

  innodb_tablespace_monitor
  innodb_table_monitor 

 2.Row,行记录 

• 每个row中至少存储几个基本信息
• DB_ROW_ID  ,6 字节,指向对应行记录,每次写新数据该ID自增
• DB_TRX_ID   , 6字节,每个事务的唯一标识符
• DB_ROLL_PTR,7字节,指向undoe log的回滚指针
• 用于实现MVCC

 3.Dynamic vs Compact 差异 

•  Dynamic:存放溢出字段的前20字节 (指针)
•  Compact存放溢出字段的前768字节
•  Dynamic vs Compact 最主要的差异在于：overflow page处理机制不同 

4.什么叫行溢出(overflow)

  行记录长度大约超过page一半时,依次将最长的列拆分到多个page存储,直到不再超过page的一半为止,溢出的列放在一个page不够的话,还会继续放在新的page中.

  Compact格式下,溢出的列只存储768字节

  Dynamic格式下,溢出的列只存储前20个字节(指针)

  Select * 会同时读取这些溢出的列,因此代价很高

总结:

1.compact格式消耗的磁盘空间和备份耗时最小，redundant相比之下略大一些。建议采用默认的compact格式，适用于绝大多数场景.
2.dynamic及compressed格式下，大字段数据存储在off-page中,如果不需要读取大字段时效率较高，否则效率很差.

  因此count(*)之类的操作相对快，但进行备份需要全表扫描读取时,其代价反而更高,适用于有很多大字段但无需经常被更新且备份的表. 

 

七 行版本控制(待确认)

1.更新：无论是聚集索引，还是二级索引，只要其键值更新，就会产生新版本.将老版本数据deleted mark设置为1,同时插入新版本.

2.对于聚集索引，如果更新操作没有更新primary key，那么更新不会产生新版本，而是在原有版本上进行更新，老版本进入undo表空间，通过记录上的undo指针进行回滚.
3.对于二级索引，如果更新操作没有更新其键值，那么二级索引记录保持不变。

4.聚簇索引设置记录deleted bit时，会同时更新DATA_TRX_ID列。老版本DATA_TRX_ID进入undo表空间；二级索引设置deleted bit时，不写入undo。