阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL内核InnoDB存储引擎详解

232次阅读
没有评论

共计 33665 个字符,预计需要花费 85 分钟才能阅读完成。

MySQL 从 5.5 版本开始将 InnoDB 作为默认存储引擎,该存储引擎是第一个完整支持事务 ACID 特性的存储引擎,且支持数据行锁,多版本并发控制(MVCC),外键,以及一致性非锁定读。 
作为默认存储引擎,也就意味着 默认创建的表都会使用此存储引擎 ,除非 
使用 ENGINE= 参数指定创建其他存储引擎的表。

InnoDB 的关键属性包括:

  1. ACID 事务特性支持,包括 commit,rollback 以及 crash 恢复的能力
  2. 行级别锁以及多版本并发控制 MVCC
  3. 利用主键的聚簇索引 (clustered index) 在底层存储数据,以提升对主键查询的 IO 性能
  4. 支持外键功能,管理数据的完整性

ACID模型是关系型数据库普遍支持的事务模型,用来保证数据的一致性,其中  
的 ACID 分别代表: 
A:atomicity 原子性:事务是一个不可再分割的工作单位,事务中的操作 要么都发生,要么都不发生 
C:consistency 一致性:事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性 
I:isolation 独立性:多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果 
D:durability 持续性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚

举例来说, 
比如银行的汇款 1000 元的操作,简单可以拆分成 A 账户的余额 -1000,B 账户的余额 +1000,还要分别在 A 和 B 的账户流水上记录余额变更日志,这四个操作必须放在一个事务中完成,否则丢失其中的任何一条记录对整个系统来说都是不完整的。 
对上述例子来说, 
原子性 体现在要么四条操作每个都成功,意味着汇款成功,要么其中某一个操作失败,则整个事务中的四条操作都回滚,汇款失败; 
一致性 表示当汇款结束时,A 账户和 B 账户里的余额变化和操作日志记录是可以对应起来的; 
独立性 表示当汇款操作过程中如果有 C 账户也在往 B 账户里汇款的话,两个事务相互不影响即 A ->B 有四个独立操作,C->B 有四个独立操作; 
持久性 表示当汇款成功时,A 和 B 的余额就变更了,不管是数据库重启还是什么原因,该数据已经写入到磁盘中作为永久存储,不会再变化,除非有新的事务  
其中事务的隔离性是通过 MySQL 锁机制实现  
原子性,一致性,持久性则通过 MySQL 的 redo 和 undo 日志记录来完成

InnoDB 多版本控制

为保证并发操作和回滚操作,InnoDB 会将修改前的数据存放在回滚段 (undo log) 中。

InnoDB 会在数据库的 每一行上额外增加三个字段以实现多版本控制  
第一个字段是 DB_TRX_ID 用来存放针对该行最后一次执行 insert、update 操作的事务 ID,而 delete 操作也会被认为是 update,只是会有额外的一位来代表事务为删除操作; 
第二个字段是 DB_ROLL_PTR 指针指向回滚段里对应的 undo 日志记录; 
第三个字段是 DB_ROW_ID 代表每一行的行 ID。

回滚段中的 undo 日志记录 只有在事务 commit 提交之后才会被丢弃,为避免回滚段越来越大,要注意及时执行 commit 命令

初始数据行的情况,六个字段的值分别是 1,2,3,4,5,6

MySQL 内核 InnoDB 存储引擎详解

数据在数据库存储,不只是只有数据存储还有其他辅助信息存储例如(隐含 id,事务 id,回滚指针)等等信息

事务 1 修改该数据行,将六个字段的值分别 *10,并生成回滚日志记录  
事务 2 读取该数据行

MySQL 内核 InnoDB 存储引擎详解

 

undo log 会保存修改前数据的哪一行的状态,执行 roallback 会找到回滚指针 进行回到以前的数据

事务 2 按照自己的事务 ID 和行数据中的事务 ID 做对比,并按照事务隔离级别选取事务 1 修改前的回滚段中的数据返回

模拟多版本控制

在两个数据库链接下实验多版本控制
链接 1:mysql> start transaction;
链接 2:mysql> start transaction;
链接 1:mysql> update score set score=88 where s>
链接 2:mysql> select * from score where s>
+——+———–+——-+
| sid | course_id | score |
+——+———–+——-+
| 1 | 1 | 90 |
| 1 | 2 | 90 |
| 1 | 3 | 90 |
| 1 | 4 | 90 |
链接 1:mysql>commit;
链接 2:mysql> select * from score where s>
+——+———–+——-+
| sid | course_id | score |
+——+———–+——-+
| 1 | 1 | 90 |
| 1 | 2 | 90 |
| 1 | 3 | 90 |
| 1 | 4 | 90 |
链接 2:mysql> commit;
链接 2:mysql> select * from score where s>
+——+———–+——-+
| sid | course_id | score |
+——+———–+——-+
| 1 | 1 | 88 |
| 1 | 2 | 88 |
| 1 | 3 | 88 |
| 1 | 4 | 88 |

InnoDB 体系结构

MySQL 内核 InnoDB 存储引擎详解

 MySQL 内核 InnoDB 存储引擎详解

特点: 
根据主键寻址速度很快  
主键值递增的 insert 插入效率较好  
主键值随机 insert 插入操作效率差

InnoDB 存储引擎体系架构

 

 MySQL 内核 InnoDB 存储引擎详解

缓存池: 
buffer pool 缓存池是 InnoDB 在内存中开辟的用来 缓存表数据和索引数据的区域,一般可以设置为 50%~80% 的物理内存大小,通过对经常访问的数据放置到内存当中来加快访问速度。

Buffer pool 以 page 页的格式 组成,页之间组成 list 列表,并通过 LRU 算法(最近最少使用算法)对长久不使用的页进行置换。

数据的读写需要经过缓存 (缓存在 buffer pool 即在内存中) 数据以整页(16K)位单位 读取到缓存中缓存中的数据以 LRU 策略换出(最少使用策略)IO 效率高,性能好

MySQL 内核 InnoDB 存储引擎详解

 MySQL 内核 InnoDB 存储引擎详解

Adaptive Hash Index(自适应哈希索引): 
Adaptive Hash index 属性使得 InnoDB 更像是内存数据库。该属性通过 innodb_adapitve_hash_index 开启,也可以通过—skip-innodb_adaptive_hash_index 参数关闭 
InnoDB 存储引擎会监控对表上索引的查找,如果观察到建立哈希索引可以带来速度的提升,则建立哈希索引,所以称之为自适应(adaptive)的。自适应哈希索引通过缓冲池的 B + 树构造而来,因此建立的速度很快。而且不需要将整个表都建哈希索引,InnoDB 存储引擎会自动根据访问的频率和模式 来为某些页建立哈希索引。

哈希(hash)是一种非常快的等值查找方法,在一般情况下这种查找的时间复杂度为 O(1), 即一般仅需要一次查找就能定位数据。 
而 B + 树的查找次数,取决于 B + 树的高度,在生产环境中,B+ 树的高度一般 3 - 4 层,故需要 3 - 4 次的查询。 
innodb 会监控对表上个索引页的查询。如果观察到建立哈希索引可以带来速度提升,则自动建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index,AHI)。 
AHI 有一个要求,就是对这个页的连续访问模式必须是一样的。 
例如对于(a,b)访问模式情况: 
where a = xxx 
where a = xxx and b = xxx

AHI 启动后,读写速度提高了 2 倍,辅助索引的连接操作性能可以提高 5 倍。 
AHI,是数据库自动优化的,尽量使用符合 AHI 条件的查询,以提高效率。

Redo log buffer 
Redo log buffer 是一块用来存放写入 redo log 文件内容的内存区域,内存的大小由 innodb_log_buffer_size 参数确定。该 buffer 的内容会定期刷新到磁盘的 redo log 文件中。 
参数 innodb_flush_log_at_trx_commit 决定了刷新到文件的方式,参数 innodb_flush_log_at_timeout 参数决定了刷新的频率

系统表空间

InnoDB 的系统表空间用来存放表和索引数据,同时也是 doublewriter 缓存,change 缓存和回滚日志的存储空间,系统表空间是被多个表共享的表空间。 
默认情况下,系统表空间只有一个系统数据文件,名为 ibdata1系统数据文件的位置和个数由参数 innodb_data_file_path 参数决定。

 MySQL 内核 InnoDB 存储引擎详解

Doublewrite 缓存

Doublewrite 缓存是位于系统表空间的存储区域,用来缓存 InnoDB 的数据页从 innodb buffer pool 中 flush 之后并写入到数据文件之前,所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃,Innodb 可以在 doublewrite 缓存中找到数据页的备份而用来执行 crash 恢复。 
数据页写入到 doublewrite 缓存的动作所需要的 IO 消耗要小于写入到数据文件的消耗,因为此写入操作会以一次大的连续块的方式写入。

在应用(apply)重做日志前,用户需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再进行重做,这就是 double write

doublewrite 组成: 
内存中的 doublewrite buffer, 大小 2M, 
物理磁盘上共享表空间中连续的 128 个页,即 2 个区(extend),大小同样为 2M。

 对缓冲池的脏页进行刷新时,不是直接写磁盘,而是会通过 memcpy()函数将脏页先复制到内存中的 doublewrite buffer,之后通过 doublewrite 再分两次,每次 1M 顺序地写入共享表空间的物理磁盘上,在这个过程中,因为 doublewrite 页是连续的,因此这个过程是顺序写的,开销并不是很大。在完成 doublewrite 页的写入后,再将 doublewrite buffer 中的页写入各个 表空间文件中,此时的写入则是离散的。如果操作系统在将页写入磁盘的过程中发生了崩溃,在恢复过程中,innodb 可以从共享表空间中的 doublewrite 中找到该页的一个副本,将其复制到表空间文件,再应用重做日志。

MySQL 内核 InnoDB 存储引擎详解

Undo 日志

Undo 日志是由一系列事务的 undo 日志记录组成,每一条 undo 日志记录包含了事务数据回滚的相关原始信息 ,所以 当其它的事务需要查看修改前的原始数据,则会从此 undo 日志记录中获取 Undo 日志存放在回滚段中的 undo 日志段中。默认情况下 回滚段是作为系统表空间的一部分,但也可以有自己独立的 undo 表空间,通过设置 
innodb_undo_tablespaces 和 innodb_undo_directory 两个参数。 
Innodb 支持最大 128 个回滚段,其中的 32 个用来服务临时表的相关事务操作,剩下的 96 个服务非临时表,每个回滚段可以同时支持 1023 个数据修改事务,也就是总共 96K 个数据修改事务。 
Innodb_undo_logs 参数用来设置回滚段的个数。

Undo Log 的原理很简单,为了满足事务的原子性,在操作任何数据之前,首先将数据备份到一个地方 
(这个存储数据备份的地方称为 Undo Log)。然后进行数据的修改。如果出现了错误或者用户执行了 
ROLLBACK 语句,系统可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态

File-per-table 表空间 
File-per-table 表空间意味着 innodb 的数据表不是共享一个系统表空间,而是每个表一个独立的表空间。可以通过设置 innodb_file_per_table 开启此属性。开启之后每个表数据和索引数据都会默认单独存放在数据文件夹下的.ibd 数据文件中。

mysql> show variables like ‘%per_table%’;
+———————–+——-+
| Variable_name        | Value |
+———————–+——-+
| innodb_file_per_table | ON    |
+———————–+——-+

temporary 表空间  
temporary 临时表空间用来存放临时表,默认情况下是在数据文件夹下的 ibtmp1 数据文件,此数据文件被设置为每次自动增长 12MB 大小,当然也可以设置 innodb_temp_data_file_path 来指定临时表空间文件的存放位置。 
临时表空间文件在正常的 shutdown 之后会自动清除 但在 crash 发生时不会清除,这就需要手动去删除表空间文件或重启服务器。

mysql> show variables like ‘%innodb_temp%’;
+—————————-+———————–+
| Variable_name | Value |
+—————————-+———————–+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |

如果发现临时表空间数据文件比较大,可以考虑重启 MySQL 来释放空间大小。

redo log

redo 日志是存在于磁盘上的文件,包括 ib_logfile0 和 ib_logfile1 两个文件,常用于在 crash 恢复发生时将还没来得及写入到数据文件中但已经完成提交的事务在数据库初始化时重新执行一遍  
InnoDB 对 redo log buffer 写入到 redo log 文件的方式提供了组提交(group commit) 的方式,意味着针对一次写磁盘操作可以包含多个事务数据,用此方法提高性能。 
为了 IO 效率,数据库修改的文件都在内存缓存中完成的 那么我们知道一旦断电,内存中的数据将消失,而数据库是如何保证数据的完整性?那就是数据持久化与事务日志

如果宕机了则:应用已经持久化好了的日志文件,读取日志文件中没有被持久化到数据文件里面的记录;将这些记录重新持久化到我们的数据文件中

MySQL 内核 InnoDB 存储引擎详解

innodb 日志持久化相关参数:

innodb_flush_log_at_trx_commit

0:每秒写入并持久化一次(不安全,性能高,无论 mysql 或服务器宕机,都会丢数据最多 1 秒的数据) 
1:每次 commit 都持久化(安全,性能低,IO 负担重) 
2:每次 commit 都写入内存的内存缓存,每秒再刷新到磁盘(安全,性能折中,mysql 宕机数据不会丢失,服务器宕机数据会丢失最多 1 秒的数据)

innodb_flush_log_at_timeout 参数决定最多丢失多少秒的数据,默认是 1 秒

InooDB 存储引擎配置

启动配置

InnoDB 合理的规划方法是在创建数据库实例之前就定义好数据文件,日志文件和数据页大小等相关属性

指定配置文件位置

MySQL 实例启动需要依赖 my.cnf 配置文件,而配置文件可以存在于多个操作系统目录下 my.cnf 文件的默认查找路径,从上到下找到的文件先读,但优先级逐级提升

MySQL 内核 InnoDB 存储引擎详解

系统表空间数据文件配置

可以通过 innodb_data_file_path 和 innodb_data_home_dir 来配置系统表空间数据文件 
Innodb_data_file_path 可以包含一个或多个数据文件,中间用;号分开 
innodb_data_file_path=datafile_spec1[;datafile_spec2]… 
datafile_spec1 = file_name:file_size[:autoextend[:max:max_file_size]]

其中 autoextend 和 max 选项只能用作最后的这个数据文件。Autoextend 默认情况下是一次增 
加 64MB,如果要修改此值可通过 innodb_autoextend_increment 参数。 Max 用来指定可扩展数据文件的最大容量用来避免数据文件大小超过可用磁盘空间大小

mysql> show variables like ‘%innodb_data%’;
+———————–+————————+
| Variable_name | Value |
+———————–+————————+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
+———————–+————————+
默认是 12M

举例如下:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
表示指定 ibdata1 和 ibdata2 两个数据文件,其中 ibdata1 文件为固定的 50M 大小,而 ibdata2 文件初始化为 50M 并可自动扩展容量
[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M
innodb_data_home_dir 参数用来显示指定数据文件的存储目录,默认是 MySQL 安装后的数据文件目录,举例如下:
[mysqld]
innodb_data_home_dir = /path/to/myibdata/
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
当然也可以在 innodb_data_file_path 中指定绝对路径的数据文件
[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/path/to/myibdata/ibdata1:50M;/path/to/myibdata/ibdata2:50M:autoextend

使用老版本 mysql 一定要注意 ibdata1 的大小,当 ibdata1 几十 G 或者几百 G,要对这个 ibdata1 压缩的时候该怎么办了,先把所有的数据给 mysqldump 出来,从新在弄一份数据库把数据库导进去 
5.7 和 5.6 是每张表都有一个表空间 表名.ibd,因为 5.6 有个参数开启了独立表空间

5.7 和 5.6 是每张表都有一个表空间 表名.ibd

日志文件配置

默认情况下 InnoDB 会在数据文件夹下创建两个 48M 的日志文件,分别是 ib_logfile0 和 ib_logfile1。 
Innodb_log_group_home_dir 参数用来定义 redo 日志的文件位置

mysql> show variables like ‘%innodb_log_file%’;
+—————————+———-+
| Variable_name | Value |
+—————————+———-+
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
+—————————+———-+
innodb_log_file_size 定义的 ib_logfile 的大小
innodb_log_files_in_group 定义的几个日志文件默认是两个 ib_logfile0 和 ib_logfile1

my.cnf

[mysqld]
innodb_log_group_home_dir = /dr3/iblogs
innodb_log_files_in_group 参数用来定义日志文件的个数,默认和推荐值都是 2
innodb_log_file_size 参数定义了每个日志文件的大小

日志文件越大意味着 buffer pool 进行文件间切换的操作越少,从而减少 IO,一般至少要保证在高峰期的 1 小时内的所有日志都能存放在一个日志文件里而不发生切换,如果一小时发生了切换这时候就要改变日志文件大小 当然文件大小也有最大限制,就是所有日志文件的总大小不能超过 512G

MySQL 内核 InnoDB 存储引擎详解

 时间间隔很大说明能支持数据库繁忙程度很小

Undo 表空间配置

默认情况下,undo 日志是存放在系统表空间里,但也可以选择在独立的一个或多个 undo 表空间中存放 undo 日志  
Innodb_undo_directory 参数决定了独立的 undo 表空间存放目录 
Innodb_undo_logs 参数决定了回滚段的个数,该变量可以动态调整 
Innodb_undo_tablespaces 参数决定了独立 undo 表空间的个数,比如设置为 16 时则会在 undo 表空间存放目录下创建 16 个 undo 文件,默 
认为 10M

mysql> show variables like ‘%innodb_undo%’;
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |

临时表空间配置

默认情况下,innodb 会创建一个自增长的 ibtmp1 文件在数据文件夹下作为临时表空间数据文件。 
Innodb_temp_data_file_path 参数可以指定文件路径,文件名和文件大小

mysql> show variables like ‘%innodb_temp%’;
+—————————-+———————–+
| Variable_name | Value |
+—————————-+———————–+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+—————————-+———————–+

数据页配置

Innodb_page_size 参数用来指定所有 innodb 表空间的数据页大小。默认是 16K 大小,也可以设置为 64K、32K、8K 和 4K。一般设置为存储磁盘的 block size 接近的大小.

内存相关配置

Innodb_buffer_pool_size 参数确定了缓存表数据和索引数据的内存区域大小,默认为 128M,推荐设置为系统内存的 50%~80%。 
在服务器有大量内存的情况下,也可以设置多个缓存以提高系统并发度。 
Innodb_buffer_pool_instances 参数就是用来做这个设置。 
Innodb_log_buffer_size 参数确定了 redo log 缓存的大小,默认值是 16M, 其大小取决于是否有某些大的事务会大量修改数据而导致在事务执行过程中就要写日志文件。

InnoDB 只读设置

InnoDB 可以通过—innodb-read-only 参数设置数据表只能读取 , 默认是 0

[mysqld]
innodb-read-only=1  #表示开启 innodb_read_only
 
mysql> show variables like ‘%read_only%’;
+———————–+——-+
| Variable_name        | Value |
+———————–+——-+
| innodb_read_only      | OFF  |
| read_only            | OFF  |
| super_read_only      | OFF  |
| transaction_read_only | OFF  |
| tx_read_only          | OFF  |
+———————–+——-+

innodb_read_only 开启 对所有的 innodb 的表都是只读权限。 
read_only 开启 只对普通用户的 innodb 的表 是只读权限 对管理员无效 如果要使其普通用户生效,加上 super 权限。 

mysql> show grants for yj@localhost;
+———————————————————————————————————————————————————————————————————————-+
| Grants for yj@localhost                                                                                                                                                                                              |
+———————————————————————————————————————————————————————————————————————-+
| GRANT SUPER ON *.* TO ‘yj’@’localhost’                                                                                                                                                                              |
| GRANT SELECT, INSERT, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `book`.* TO ‘yj’@’localhost’ |
+———————————————————————————————————————————————————————————————————————-+

InnoDB buffer pool 配置

InnoDB buffer pool 设置

Buffer pool 是内存中用来缓存数据和索引的存储区域,其是 MySQL 性能调优的重要一环 
理想情况下,设置的 size 越大,则缓存到内存的数据越多,InnoDB 就越像是内存数据库。

Buffer pool 的底层是一个列表,通过 LRU 算法进行数据页的换进换出操作。当空间原因导致新页的加入需要换出一页时,InnoDB 取出最近最少使用的页并将这个新的数据页加入到列表的中央。从方向上看,列表的头部是最常使用的数据页,而在尾部则是最少使用的数据页。 
Buffer pool 中 3 / 8 的部分是保存最少使用的数据页,而中央部分其实是经常使用和最少使用的结合点。当在最少使用中保存的数据页被访问时,则数据页就会被移动到列表的头部变成最常使用的。

配置大小

InnoDB buffer pool 的大小可以在启动时配置,也可以在启动之后配置。

增加和减少 buffer pool 的大小都是以大块的方式,块的大小由参数 innodb_buffer_pool_chunk_size 决定,默认为 128M。

Innodb_buffer_pool_size 的大小可以自行设定,但必须是  
innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances 的整数倍,如果不是,则 buffer pool 会被调整成大于设定值且最接近的一个值 
如下列

mysql> show variables like ‘%innodb_buffer_pool%’
    -> ;
+————————————-+—————-+
| Variable_name                      | Value          |
+————————————-+—————-+
| innodb_buffer_pool_chunk_size      | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON            |
| innodb_buffer_pool_dump_now        | OFF            |
| innodb_buffer_pool_dump_pct        | 25            |
| innodb_buffer_pool_filename        | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort      | OFF            |
| innodb_buffer_pool_load_at_startup  | ON            |
| innodb_buffer_pool_load_now        | OFF            |
| innodb_buffer_pool_size            | 134217728      |
+————————————-+—————-+
mysql> select 134217728/1024/1024
    -> ;
+———————+
| 134217728/1024/1024 |
+———————+
|        128.00000000 |
+———————+
1 row in set (0.00 sec)
# 动态修改一下 InnoDB_buffer_size 给加大一个 1 Bytes 看看是增加为 134217729 还是 134217728 的整数倍
mysql> set global  innodb_buffer_pool_size=134217729;
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 是变成了整数陪
mysql> show variables like ‘%innodb_buffer_pool%’
    -> ;
+————————————-+—————-+
| Variable_name                      | Value          |
+————————————-+—————-+
| innodb_buffer_pool_chunk_size      | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON            |
| innodb_buffer_pool_dump_now        | OFF            |
| innodb_buffer_pool_dump_pct        | 25            |
| innodb_buffer_pool_filename        | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort      | OFF            |
| innodb_buffer_pool_load_at_startup  | ON            |
| innodb_buffer_pool_load_now        | OFF            |
| innodb_buffer_pool_size            | 268435456      |
+————————————-+—————-+
10 rows in set (0.00 sec)
mysql> select 268435456/1024/1024
    -> ;
+———————+
| 268435456/1024/1024 |
+———————+
|        256.00000000 |
+———————+
1 row in set (0.00 sec)

Innodb_buffer_pool_chunk_size 可以自行设定,且增加和减少都要以 M 为单位,并只能在启动前修改,修改后的值 *innodb_buffer_pool_instances 不能大于 buffer pool 的大小,否则修改无效。

[mysqld]
innodb_buffer_pool_chunk_size=134217728

buffer pool 的大小可以动态修改,用 set 语句直接修改,当语句发起时,会一直等到当前所有的事务结束后才执行,而一旦执行则执行过程中的其他事务如果要访问 buffer pool 就会等待语句执行完毕。

# 动态修改
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

当执行 online 的调整大小时,可以通过 error log 或者 innodb_buffer_pool_resize_status 查看进度

mysql> SHOW STATUS WHERE Variable_name=’InnoDB_buffer_pool_resize_status’;
+———————————-+———————————-+
| Variable_name | Value |
+———————————-+———————————-+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+———————————-+———————————-+

配置多个 buffer pool 实例

当 buffer pool 的大小是 GB级别时 ,将一个 buffer poo 分割成几个独立的实例能降低多个线程同时读写缓存页的竞争性而 提高并发性  
通过 innodb_buffer_pool_instances 参数可以调整实例个数。如果有多个实例,则缓存的数据页会随机放置到任意的实例中,且每个实例都有独立的 buffer pool 所有的特性。 
Innodb_buffer_pool_instances 的默认值是 1,最大可以调整成 64。

mysql> SYSTEM cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
character-set-server=utf8
collation-server=utf8_unicode_ci
secure-file-priv=/tmp/
innodb_buffer_pool_instances=5
innodb_buffer_pool_size=1024M
# 这里我设置了实例为了 buffer_pool 为 1024M
mysql> show variables like ‘%innodb_buffer_pool%’
    -> ;
+————————————-+—————-+
| Variable_name                      | Value          |
+————————————-+—————-+
| innodb_buffer_pool_chunk_size      | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON            |
| innodb_buffer_pool_dump_now        | OFF            |
| innodb_buffer_pool_dump_pct        | 25            |
| innodb_buffer_pool_filename        | ib_buffer_pool |
| innodb_buffer_pool_instances        | 5              |
| innodb_buffer_pool_load_abort      | OFF            |
| innodb_buffer_pool_load_at_startup  | ON            |
| innodb_buffer_pool_load_now        | OFF            |
| innodb_buffer_pool_size            | 1342177280    |
+————————————-+—————-+
实际大小是 buffer_pool 的 1280M 实例为 5
可以 show engine innodb status\G; 看看几个 buffer 实例 会从 1280 平均分给 buffer pool 实例

Making the Buffer Pool Scan Resistant 
新读取的数据页被插入到 buffer pool 的 LRU 列表的中间位置,默认位置是从尾部开始算起的 3 / 8 的位置。当被放入 buffer pool 的页被第一次访问时就开始往列表的前方移动,而这样列表的后部就是不经常访问的页甚至是从不访问的页。 
通过参数 innodb_old_blocks_pct 可以控制列表中”old”数据页所占的百分比,默认是 37%,等同于 3 /8,取值范围是 5~95。

Innodb_old_blocks_time 参数默认是 1000 毫秒,指定了页面读取到 buffer pool 后但没有移动到经常被访问列表位置的时间窗口。

InnoDB buffer pool 预存取(read-ahead) 
Read ahead 是异步地预先获取多个数据页到 buffer pool 的 IO 操作,这些数据页都是假定会随后被用到的。InnoDB 通过两种 read-ahead 算法提高 IO 性能:

线性 read ahead:预测哪些页会被顺序访问。通过 innodb_read_ahead_threshold 参数调整顺序数据页的数量。当从一个区中顺序读取的页数量大于等于  
innodb_read_ahead_threshold 时,innodb 会触发异步 read ahead 操作将真个区都读到 buffer pool 中。该参数的默认值是 56,取值范围是 0~64。 
随机 read ahead通过已经在 buffer pool 中的数据页来预测哪些页会被随后访问到。如果 13 个连续的处于相同区的页存在于 buffer pool 中,则 InnoDB 会把同一个区的其它页都读取进来。通过设置 innodb_random_read_ahead=ON 来开启此方式。

通过执行 show engine innodb status 命令显示的三个参数判断 read-ahead 算法的有效性:

Innodb_buffer_pool_read_ahead
Innodb_buffer_pool_read_ahead_evicted
Innodb_buffer_pool_read_ahead_rnd

InnoDB buffer pool flushing 配置

Innodb 会在后台将 buffer pool 中的脏页 (已经修改但没有写到数据文件)flush 掉。当 buffer pool 中的脏页所占百分比达到 innodb_max_dirty_pages_pct_lvm 会触发 flush,当所占比例达到 innodb_max_dirty_pages_pct 时,则 innodb 会“强烈”的 flush。 
针对数据修改操作频繁的系统,flush 可能会严重滞后导致有大量的 buffer pool 内存占用,有一些参数专门针对修改繁忙的系统可以调整:

Innodb_adaptive_flushing_lwm:为防止 redo log 被填满,此参数设置一个阈值,如果 redo log 的容量超过此阈值,则执行 adaptive flush 操作。

Innodb_max_drity_pages_pct_lwm
Innodb_io_capacity_max
Innodb_flushing_avg_loops

重置 buffer pool 状态

InnoDB 可以通过配置 innodb_buffer_pool_dump_at_shutdown 参数来确保在 mysql 正常重启时部分经常使用的数据页能直接加载到 buffer pool 中,通过批量加载的方式,以节省重启 mysql 导致的 warmup 时间(原先在 buffer pool 中的数据页要从磁盘再次加载到内存中)。 

Buffer pool 的状态可以在任意时刻被保存,而重置状态也可以恢复任意保存的副本。 
在数据库运行期间动态配置 buffer pool 数据页保留占比的方式是:

SET GLOBAL innodb_buffer_pool_dump_pct=40;
而在配置文件中的配置方法为:
[mysqld]
innodb_buffer_pool_dump_pct=40
配置当服务器关闭时保存 buffer pool 的当前状态的方法是:
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
mysqld –innodb_buffer_pool_load_at_startup=ON;
默认情况下 innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 两个配置
是开启状态

在关闭 MySQL 时,会把内存中的热数据保存在磁盘里 ib_buffer_pool 文件中,位于数据目录下。

数据库运行期间保存和重新加载 buffer pool 的方法是:

SET GLOBAL innodb_buffer_pool_dump_now=ON;
SET GLOBAL innodb_buffer_pool_load_now=ON;

查看 buffer pool 保存和重新加载的进度的方法是:

mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool_dump_status’;
+——————————–+————————————————–+
| Variable_name | Value |
+——————————–+————————————————–+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 170519 0:16:28 |
+——————————–+————————————————–+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool_load_status’;
+——————————–+————————————————–+
| Variable_name | Value |
+——————————–+————————————————–+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170519 0:14:05 |
+——————————–+————————————————–+
1 row in set (0.00 sec)

监控 buffer pool 的状态情况

通过 show engine innodb status\G; 命令可以查看 buffer pool 的运行情况

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-08-15 22:55:43 0x7f5b69b4e700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
—————–
BACKGROUND THREAD
—————–
srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 18800 srv_idle
srv_master_thread log flush and writes: 18804
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 2
RW-shared spins 0, rounds 4, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
————
TRANSACTIONS
————
Trx id counter 35363587
Purge done for trx’s n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 421507019233104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
——–
FILE I/O
——–
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o’s:, sync i/o’s:
Pending flushes (fsync) log: 0; buffer pool: 0
480 OS file reads, 131 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 332099, node heap has 2 buffer(s)
Hash table size 332099, node heap has 0 buffer(s)
Hash table size 332099, node heap has 0 buffer(s)
Hash table size 332099, node heap has 0 buffer(s)
Hash table size 332099, node heap has 0 buffer(s)
Hash table size 332099, node heap has 0 buffer(s)
Hash table size 332099, node heap has 0 buffer(s)
Hash table size 332099, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

LOG

Log sequence number 3964869295
Log flushed up to  3964869295
Pages flushed up to 3964869295
Last checkpoint at  3964869286
0 pending log flushes, 0 pending chkp writes
10 log i/o’s done, 0.00 log i/o’s/second
———————-
BUFFER POOL AND MEMORY
———————-
Total large memory allocated 1374289920
Dictionary memory allocated 102398
Buffer pool size  81910
Free buffers      81395
Database pages    511
Old database pages 0
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 453, created 58, written 114
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: 511, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
———————-
INDIVIDUAL BUFFER POOL INFO
———————-
—BUFFER POOL 0
Buffer pool size  16382
Free buffers      16226
Database pages    155
Old database pages 0
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 155, created 0, written 2
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: 155, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
…..

InnoDB 其他配置

InnoDB change buffer 设置

change buffering 是 MySQL5.5 加入的新特性,change buffering 是 insert buffer 的加强,insert buffer 只针对 insert 有效,change buffering 对 insert、delete、update(delete+insert)、purge 都有效。当修改一个索引块 (secondary index) 时的数据时,索引块在 buffter pool 中不存在,修改信息就会被 cache 在 change buffer 中,当通过索引扫描把需要的索引块读取到 buffer pool 时,会和 change buffer 中修改信息合并,再择机写回 disk。

目的还是为了减少随机 IO 带来性能损耗

Change buffer 是作为 buffer pool 中的一部分存在。

Innodb_change_buffering 参数缓存所对应的操作:(update 会被认为是 delete+insert)

all: 默认值,缓存 insert, delete, purges 操作 
none: 不缓存 
inserts: 缓存 insert 操作 
deletes: 缓存 delete 操作 
changes: 缓存 insert 和 delete 操作 
purges: 缓存后台执行的物理删除操作 
innodb_change_buffer_max_size 参数配置 change buffer 在 buffer pool 中所占的最大百分比,默认是 25%,最大可以设置为 50%。当 MySQL 实例中有大量的修改操作时,要考虑增大 innodb_change_buffer_max_size。

InnoDB 线程并发度配置

InnoDB 利用操作系统的线程技术达到多线程实现。 
Innodb_thread_concurrency 参数限制同时执行的线程数。默认值是 0 代表没有限制。 
Innodb_thread_sleep_delay 参数确定

InnoDB 后台 IO 线程配置

通过配置 innodb_read_io_threads 和 innodb_write_io_threads 参数来指定后台读和写数据页的线程的个数,默认值是 4,容许的取值范围是 1 -64。

mysql> show engine innodb status\G
FILE I/O
——–
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
….

使用 Linux 异步 IO

InnoDB 在 Linux 平台使用异步 IO 子系统完成数据文件页的读写请求,可以通过 innodb_user_native_aio 参数控制,默认是开启状态,并且需要 libaio 系统库支持

InnoDB 主线程配置

InnoDB 的主线程在后台承担了诸多的任务,绝大多数是和 IO 操作相关的,比如将 buffer pool 中的修改后的数据刷新的磁盘文件中。 
Innodb_io_capacity 参数设置了 InnoDB 的整体 IO 能力。该参数应该被设置为等同于操作系统每秒的 IO 操作数量。该参数可以设置为 100 及以上的任意数值,默认值是 200。其中设置为 100 相当于 7200RPM 的磁盘性能。

InnoDB purge 配置

InnoDB 的 purge 操作是一类垃圾回收操作,是由一个或多个独立线程自动执行。通过  
innodb_purge_threads 参数设置 purge 线程的数量,如果 DML 操作比较复杂且涉及到多个表时,则可以考虑增加此值,最大可以设置为 32. 
事务被提交后,其所使用的 undolog 可能不再需要因此需要PurgeThread 来回收已经使用并分配的 undo 页

InnoDB 优化器统计信息配置

Innodb 表的优化器统计信息分为永久和非永久两种。 
永久的优化器统计信息即使是服务器重启的情况下也会存在,其用来选出更优的执行计划以便提供更好的查询性能. 
通过配置 innodb_stats_auto_recalc 参数来控制统计信息是否在表发生巨大变化(超过 10% 的行)之后是否自动更新,但由于自动更新统计信息本身是异步的,所以有时未必能马上更新,这是可以执行 analyze table 语句来同步更新统计信息。

Create table 和 alter table 语句中的 Stats_persistent, stats_auto_recalc, stats_sample_pages 子句可用来配置单个表的优化器统计信息规则

Stats_persistent 用来指定是否对此表开启永久统计资料,1 代表开启,0 代表不开启。当开启之后, 
可以执行 analyze table 命令来收集统计资料。 
Stats_auto_recalc 表示是否自动对表的永久统计资料进行重新计算,默认值和全局参数 innodb_stats_auto_recalc 一致。 1 代表当表中数据 10% 以上更新时重新计算,0 代表不自动更新,而是通过 analyze table 命令重新计算 
Stats_sample_pages 表示当计算索引列的统计资料是需要的索引页的样本数量

优化器永久统计资料数据在系统表 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表中存储,这两个表中有个字段 last_update 可以用来判断统计信息最后更改时间。这两个表的数据也可以被手工更改。当手工更改完数据之后,要执行 flush table 表名命令来重新 load 此表的统计资料。innodb_table_stats 表中每个目标表一行记录,而 innodb_index_stats 表中每个索引会有多条记录 
Innodb_table_stats 表结构

MySQL 内核 InnoDB 存储引擎详解

MySQL 内核 InnoDB 存储引擎详解

Innodb_index_stats 表结构:

MySQL 内核 InnoDB 存储引擎详解

Stat_name=n_diff_pfxNN 参数:当是 n_diff_pfx01 时 stat_value 列表示索引第一列上的区别值有几个,当是 n_diff_pfx02 时 stat_value 列表示索引第一、二列上的区别值有几个,以此类推。而 stat_description 列显示了对应的逗号可开的索引列值。 
默认情况下永久优化��统计信息的属性是开启的,innodb_stats_persistent=ON 
非永久优化器统计信息会在每次服务器重启或者其他一些操作时被清理。 
优化器统计信息会被存储在磁盘上,通过设置 innodb_stats_persistent=ON 参数(默认)。

MySQL 的查询优化器会基于评估好的统计资料选择合适的索引参与到执行计划中 而类似 analyze table 的语句会从索引中随机选取数据页参与到每个索引的基数评估中。而参数 innodb_stats_persistent_sample_pages 决定了参与评估的数据页的数量,默认值是 20。当语句执行的执行计划不是最优选择时,则考虑增加此参数,以便获得正确的统计资料。

当设置 innodb_stats_persistent=OFF 参数或者对单个表设置 stats_persistent= 0 时,对应的统计资料就仅存在于内存中而非磁盘上,当服务器重启之后统计资料丢失。当然此类统计资料也可以周期性的更新。

比如执行 analyze table 语句手动刷新统计资料,或者在 innodb_stats_on_metadata 选项打开之后执行 show table status/show index 或查询 information_schema.tables/statistics 表时非永久统计资料会自动更新,当 InnoDB 检测到 1 /16 的表数据被修改时也会更新。

索引页之间合并阈值

通过配置 merge_threshold 来确保当索引页的数据由于删除操作或者修改操作低于阈值,InnoDB 会将此索引页和邻近的索引页合并。默认值是 50,取值范围是 1 到 50。

Merge_threshold 参数可以定义在表上,也可以定义在一个独立的索引上。

CREATE TABLE t1 (
id INT,
KEY id_index (id)
) COMMENT=’MERGE_THRESHOLD=45′;
CREATE TABLE t1 (
id INT,
KEY id_index (id)
);
ALTER TABLE t1 COMMENT=’MERGE_THRESHOLD=40′;
CREATE TABLE t1 (
id INT,
KEY id_index (id) COMMENT ‘MERGE_THRESHOLD=40’
);
CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT ‘MERGE_THRESHOLD=40’;

评估 merge_threshold 参数合理的方法是查看 innodb_metrics 表里的相关参数,确保发生了 较少的索引页合并且合并请求和成功合并的数量相当

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS
WHERE NAME like ‘%index_page_merge%’;
+—————————–+—————————————-+
| NAME | COMMENT |
+—————————–+—————————————-+
| index_page_merge_attempts | Number of index page merge attempts |
| index_page_merge_successful | Number of successful index page merges |
+—————————–+—————————————-+

重置 InnoDB 系统表空间

最简单的增加系统表空间的办法就是在初始化阶段配置数据文件的自增长, 
通过配置最后一个文件的 autoextend 属性,当数据文件空间不足时默认自动增长 64M 大小。 
也可以通过修改 innodb_autoextend_increment 参数修改自动增长的大小。

也可以通过增加另一个数据文件方法扩展表空间,步骤如下:

关闭 MySQL
检查配置的最后一个数据文件是否是 autoextend,如果是则根据当前数据文件的大小去掉自动扩展属性,改成当前大小
mysql> SHOW VARIABLEs LIKE  ‘%innodb_data_file%’;
+———————–+————————+
| Variable_name        | Value                  |
+———————–+————————+
| innodb_data_file_path | ibdata1:12M:autoextend |
+———————–+————————+
1 row in set (0.01 sec)
在 my.cnf 配置文件的 innodb_data_file_path 参数里增加一个新的数据文件,选择是否自动扩展启动 MySQL
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:10M:autoextend
#### 如果有设置改成,没有设置就添加
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

减小系统表空间大小的方法如下: 
Mysqldump 出所有的 InnoDB 表,包括 mysql 系统数据库下的五个表

mysql> select table_name from information_schema.tables where table_schema=’mysql’ and
engine=’InnoDB’;
+———————-+
| table_name |
+———————-+
| innodb_index_stats |
| innodb_table_stats |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
+———————-+
关闭 MySQL
删除所有 InnoDB 的数据文件和日志文件,包括 *.ibd 和 ib_log 文件,还有在 MySQL 库文件夹下的 *.ibd 文件
删除所有.frm 的 InnoDB 表文件
在配置文件里配置新的表空间文件
启动 MySQL
导入备份出的 dump 文件

重置 InnoDB redo log 文件大小

关闭 MySQL
通过 innodb_log_file_size 更改文件大小,通过 innodb_log_files_in_group 更改文件数量
my.cnf
innodb_log_file_size=30M
innodb_log_files_in_group=3
启动 MySQL,看看 iblogfile 的数量和大小

配置单表数据文件表空间

InnoDB 的单表数据文件表空间代表每个 InnoDB 表的数据和索引数据都存放在单独的.ibd 数据文件中 每个.ibd 数据文件代表独立的表空间。 
此属性通过 innodb_file_per_table 配置  
此配置的主要优势: 
当删除表或者 truncate 表的时候,意味着对磁盘空间可以回收。而共享表空间时删除一个表时空间不会释放而只是文件里有空闲空间 
Truncate table 命令要比共享表空间快

通过定义 create table …data directory= 绝对路径,可以将特定的表放在特定的磁盘或者存储空间  
可以将单独的表物理拷贝到另外的 MySQL 实例中  
此配置的劣势: 
每个表都有未使用的空间,意味着磁盘空间有些浪费

启动单独表空间的方式如下:

[mysqld]
innodb_file_per_table=1
当设置 innodb_file_per_table=0, 所有创建的新表都会放置到共享表空间 ibdata1 里,除非在 create table 命令里显示的使用 tablespace 选项。

将已经存在于共享表空间的表修改为独立表空间的方法:

SET GLOBAL innodb_file_per_table=1;
ALTER TABLE table_name ENGINE=InnoDB;

通过命令 create table … data directory= 绝对路径可以将单表数据文件创建在另外的目录里。 
在指定的绝对路径下,会创建数据库名相同的文件夹,里面含有此表的.ibd 文件,同时在 MySQL 的默认数据文件下的数据库名文件夹下会创建 table_name.isl 文件包含了此表的路径,相当于 link 文件。

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = ‘/alternative/directory’;
Query OK, 0 rows affected (0.03 sec)
# MySQL creates a .ibd file for the new table in a subdirectory that corresponding
# to the database name
db_user@Ubuntu:~/alternative/directory/test# ls
t1.ibd
# MySQL creates a .isl file containing the path name for the table in a directory
# beneath the MySQL data directory
db_user@ubuntu:~/mysql/data/test$ ls
db.opt t1.frm t1.isl

单表迁移  
不管是出于备份复制还是什么原因要将单表复制到另外的数据库实例下,可以使用传输表空间的方法  
当数据库文件特别大 100G 的时候,导入导出特别慢,可以用单表的方式实现迁移

在原实例下创建表
mysql> use test;
mysql> CREATE TABLE t(c1 INT)engine=InnoDB;
在目标实例下创建表
mysql> use test;
mysql> CREATE TABLE t(c1 INT)engine=InnoDB;
在目标实例下将表的表空间属性去除
mysql> ALTER TABLE t DISCARD TABLESPACE;
此命令对有外键的表不支持,必须首先执行 foreign_key_checks=0
在原实例下表加锁仅允许读操作,并生成.cfg 元文件, 防止写入数据
mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;
将.ibd 和.cfg 文件拷贝到目标实例的指定目录下
shell> scp /path/to/datadir/test/t.{ibd,cfg} destinationserver:/path/to/datadir/test
原实例下释放锁
mysql> use test;
mysql> UNLOCK TABLES;
目标实例下执行导入表空间操作
mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;

设置 Undo log 独立表空间

默认情况下 undo log 是存储在系统表空间 ibdata1 里,我们也可以将其存放在一个或多个独立表空间下。

Innodb_undo_tablespaces 参数定义了有多少个 undo 表空间,此参数只能在建立 MySQL 实例时被配置

innodb_undo_directory 参数定义了 undo 表空间的存放路径 
innodb_undo_logs 参数定义了回滚段的数量

mysql> show variables like ‘%innodb_undo%’;
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |

Innodb_undo_log_truncate 参数决定是否开启 undo 表空间清空

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

当设置了此参数为 ON 后,则代表 undo 文件大小超过 innodb_max_undo_log_size(默认值是 128M)的都标记为清空

mysql> SELECT @@innodb_max_undo_log_size;
+—————————-+
| @@innodb_max_undo_log_size |
+—————————-+
| 1073741824 |
mysql> SET GLOBAL innodb_max_undo_log_size=2147483648;
Query OK, 0 rows affected (0.00 sec)

当标记为清空后,回滚段标记为非激活状态表示不接收新的事务,而已存在的事务会等到完成;然后通过 purge 操作将回滚段空间释放;当 undo 表空间的所有回滚段都释放后,表空间就会清空成初始 10M 大小;然后回滚段重新变成激活状态以接收新的事务

InnoDB 普通表空间

什么情况下需要普通表空间  
一般是当你有一些表访问比较频繁,而且你的物理磁盘性能不太一样,快的磁盘空间比较小的时候,可以考虑把这几个表通过表空间放到快的盘上。

通过 create tablespace 命令可以创建一个共享 InnoDB 表空间,和系统表空间一样,多个表可以在此表空间上存储数据,此表空间的数据文件可以放置在任意的文件夹下。

CREATE TABLESPACE tablespace_name
ADD DATAFILE ‘file_name’
[FILE_BLOCK_SIZE = value]
[ENGINE [=] engine_name]
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE ‘ts1.ibd’ Engine=InnoDB; ## 创建在
MySQL 数据目录下
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE ‘/my/tablespace/directory/ts1.ibd’
Engine=InnoDB;
当创建完表空间之后,就可以通过 create table …tablespace 或者 alter table …
tablespace 命令将表增加到此表空间上
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;
mysql> ALTER TABLE t2 TABLESPACE ts1;

通过 alter table 命令可以将 InnoDB 表在系统表空间、独立表空间和普通表空间之间转化:

ALTER TABLE tbl_name TABLESPACE [=] tablespace_name ## 从系统表空间或者独立表空
间上转移到普通表空间
ALTER TABLE tbl_name … TABLESPACE [=] innodb_system ## 从普通表空间或者独立表空间上转移到系统表空间
ALTER TABLE tbl_name … TABLESPACE [=] innodb_file_per_table ## 从系统表空间或者普通表空间转移到独立表空间

Alter table … tablespace 语句的执行都会导致此表会重建,即使表空间的属性和之前是  
一样的。

mysql> create tablespace ts1 add datafile
‘/usr/local/mysql/data/ts1.ibd’;
Query OK, 0 rows affected (0.02 sec)
mysql> use course;
# 创建了一个普通表空间
mysql> create table students4(id int,name varchar(10)) tablespace ts1;
Query OK, 0 rows affected (0.00 sec)
# 更改为独立表空间
mysql> alter table students4 tablespace=innodb_file_per_table;
root@localhost:/usr/local/mysql/data/course# ls students4.*
students4.frm students4.ibd

当删除一个普通表空间时,首先需要保证此表空间上的所有表都被删除,否则会报错。删除表空间是用 drop tablespace 语句来执行。Drop database 的动作会删除所有的表,但创建的 tablespace 不会被自动删除,必须通过 drop tablespace 显示执行。

普通表空间不支持临时表,而且也不支持 alter table … discard tablespace 和 alter table …import tablespace 命令。

mysql> drop tablespace ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1
mysql> drop table temp123;
Query OK, 0 rows affected (0.00 sec)
mysql> drop tablespace ts1;
Query OK, 0 rows affected (0.01 sec)

创建 InnoDB 表

通过 create table 语句创建 InnoDB 表,因为默认存储引擎就是 InnoDB,所以不需要在创建表的语句最后指定 engine=innodb。

InnoDB 的表数据和索引数据默认是存储在系统表空间中,但可以通过开启 innodb_file_per_table 选项将表数据和索引数据存放在独立表空间中。当表创建完之后,会在表所在的数据库文件夹里创建.frm 文件用来存储表的结构,系统表空间对应的.ibdata 文件存储数据文件,而当开启独立表空间时,则会在表所在的数据库文件夹里创建.ibd 用来存储表数据和索引数据。

MySQL 内核 InnoDB 存储引擎详解

 通过 show table status 语句可以查看 InnoDB 的表属性

mysql> show table status like ‘user_stock’\G;
*************************** 1. row ***************************
          Name: user_stock
        Engine: InnoDB
        Version: 10
    Row_format: Dynamic
          Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
  Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-08-15 23:58:20
    Update_time: NULL
    Check_time: NULL
      Collation: utf8_general_ci
      Checksum: NULL

修改表的存储引擎

通过 alter table 语句修改已有表的存储引擎 
ALTER TABLE table_name ENGINE=InnoDB;

自增长字段设置

当对 InnoDB 表设置了自增长字段之后,表会在内存中保存一个自增长计数器。 
默认情况下自增长字段的初始值是 1,但也可以通过配置 auto_increment_offset 参数将所有的自增长字段初始值设置为另外的值,而当表中插入数值时,InnoDB 会求出当前表中的该列的最大值,然后在此基础上加 1 作为插入的数据。默认是以 + 1 为增长的进度,但也可以通过 auto_increment_increment 配置所有自增长字段的自定义增长进度。

InnoDB 表主要的限制

InnoDB 表目前只支持最多 1017 个列  
InnoDB 表目前支持最大 64 个二级索引 
多列索引目前支持最大 16 个列

如果表中不存在 text 或者 blob 类型字段时,行数据整体的最大长度是 65535 个字节

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000), c VARCHAR(10000), d
VARCHAR(10000), e VARCHAR(10000),f VARCHAR(10000), g VARCHAR(10000))
ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for theused table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计33665字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中