概述

MySQL的存储引擎采用了插件式的设计方案

查看可用搜索引擎

show engines;

常用搜索引擎

MEMORY存储引擎

特性

1. 数据存储在内存中
2. 在磁盘上只有一个.frm文件，用来存储表结构的定义
3. 数据在MySQL服务关闭或重启后，数据会丢失
4. 删除数据时的数据不会被释放内存,没有使用的内存会被新的记录使用,只有当整个表被删除或重建时才会回收相对内存,可以使用DROP,CREATE,ALTER回收内存
5. 可以使用的最大的内存空间由max_heap_table_size决定,默认为16MB,如果要创建内存上限不一样的表,可以在创建表之前设置max_heap_table_size(设置会话级的配置，不要设置全局的配置)再创建表,注意重启之后又会重新继承全局的max_heap_table_size设置

指定搜索引擎

create table m_test_min (`id` int) engine MEMORY;

CSV存储引擎

特性

1. 包含一个结构定义文件(.frm),一个的数据文件(.csv),一个源信息文件(.csm),用来保存表的状态及表中的数据量
2. csv可以直接编辑，如果表结构损坏，可以使用CHECK TABLE或REPAIR TABLE命令进行检查和修复

ARCHIVE存储引擎

特性

1. 可以对存储的数据进行压缩
2. 使用zlib算法压缩数据
3. 可以用OPTIMIZE TABLE分析表使其打包成更小的格式
4. 仅支持insert和select，不支持delete,repalce,update语句
5. 支持order by和blob列等常规操作
6. 支持行级锁,不支持索引
7. 插入效率高,保存数据占用的空间小
8. 包含一个结构文件(.frm),一个数据文件(.arz),执行优化操作时会出现一个临时文件(.arn)

BLACKHOLE存储引擎

特性

1. 不保存数据
2. 在磁盘上只有一个.frm文件，用来存储表结构的定义

MERGE引擎

特性

也被称为MGR_MyISAM存储引擎,实际上是将一组MyISAM表聚合在一起
被聚合在一起的表要求相同的列和索引信息(列的定义,列的顺序,索引都必须完全相同)

1. 可以将多种存储引擎为MyISAM的表结合到一起(效果相当于UNION语句)
2. 包含一个结构文件(.frm),一个描述数据来源的文件(.mgr)
3. 不存储实际的数据
4. 默认不能往MERGE引擎的表插入数据。需要通过(INSERT_METHOD)指定插入记录的表才能插入数据

实例

# 创建待创建的表1
create table merge_test_1 (`id` int) engine myisam;
# 创建待创建的表2
create table merge_test_2 (`id` int) engine myisam;
# 创建待存储引擎为merge表
create table merge_test (`id` int) engine merge union (merge_test_1,merge_test_2);
# 创建待存储引擎为merge表并指定插入表
create table merge_test (`id` int) engine=merge union=(merge_test_1,merge_test_2) insert_method=first;
# 修改插入数据的表
alter table merge_test insert_method last;

FEDERATED引擎

特性

1. 可以用于映射其他实例的表

2. 默认不支持,需要在配置文件中开启

   1. 在编译是指定DWITH_FEDERATED_STORAGE_ENGINE(可以用show engines;查看是否编译这个存储引擎)
   2. 在配置文件的[mysqld]中添加federated
   3. 重启mysql
3. 包含一个结构文件(.frm)，文件中还包含了远端链接信息的描述

4. 处理sql的过程和其他方式不一样

   1. 存储引擎检查FEDERATED表列以及关联的目标端的SQL语句
   2. 使用MySQL客户端API发送语句到远端
   3. 远端处理完语句,本地接收该语句生成的结果集
   4. 如果返回结果是一个结果集，每一列都会转换为FEDERATED引擎期望的内部引擎，返回正确的结果给客户端
5. 本地并不会保存数据，每次请求都需要从远端拉数据
6. 不支持索引
7. 无法感知远端表的结构变化
8. 批量插入相对于其他搜索引擎稍差,因为实际上还是会以逐条插入的防止执行
9. 支持select,insert,update,delete,不知道alter table以及其他DDL语句(除drop table外)

注意点

1. 配置了Replication的复制环境,需要注意从库是否有链接到远端数据库的权限
2. 当使用CONNECTION语句时,密码中不能包含有@字符,这个字符在连接时是关键字

配置

1. 直接指定connection

create table federated_target (
`id` int
) engine=federated connection="mysql://root:Zj123$%^@127.0.0.1/my_test/federated_source";
# connection格式
# scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

1. 使用 CREATE SERVER 创建 FEDERATED

直接指定connection有几个问题

1. 一旦链接信息发生变化，需要逐个更新表的结构
2. 密码中不能含有@符号
   使用 CREATE SERVER 可以避免这两个问题

# 创建server
create server federated_server
foreign data wrapper mysql
options (user 'root', password 'Zj123$%^', host '127.0.0.1', port 3306, database 'my_test');

# 创建表
create table federated_target_2 (
`id` int
) engine=federated connection="federated_server/federated_source";

MyISAM存储引擎

特性

1. 一个结构定义文件(.frm),一个数据文件(.MYD),一个索引文件(.MYI)

2. 默认存储在DB所在目录下,可自定义实际存储路径(如下),注意,就算定义了路径,DB目录下始终会有MYD和MYI文件(软链接)

   • DATA DIRECTORY [=] 'absolute path to directory'
   • INDEX DIRECTORY [=] 'absolute path to directory'
3. 单表最多支持2^64条记录
4. 单表最多创建64个索引
5. 复合索引最多包含16个列
6. 索引最大长度为1000B
7. 单表字符列最大长度为65532B(不止是MyISAM存储引擎，这个限制作用于整个MySQL数据库)
8. 锁粒度太粗(表级锁),在读写并重的场景时,整体响应速度不理想
9. 查询快,写入快

MyISAM引擎存储格式

主要分为三种

1. 定长(FIXED，也称静态)
2. 动态(DYNAMIC)
3. 压缩(COMPRESSED)

前两种不需要单独指定，会在创建对象时根据列的类型自动适配(表中没有BLOB或TEXT类型的列)

静态格式表

指表中不包含变长类型的列(varchar/varbinary/blob/text),表中的列均是固定的字节数。在MyISAM支持的三种格式中是最简单也是最安全的格式,也是最宽的查找数据的方式

创建静态表

# 创建表
create table myisam_test (
`id` int
) engine=myisam;
# 查看表信息，Row_format显示的就是表的类型
show table status like "myisam_test";

指定表格式为静态

只要表中没有BLOB,TEXT类型的列，就可以使用 row_format 强制指定表的格式为静态,这种情况下表,varchar变长类型会以定长处理,会增加表所占用的空间

# 创建表
create table myisam_test_2 (
`id` int,
`name` varchar(100)
) engine=myisam row_format=fixed;
# 查看表信息，Row_format显示的就是表的类型
show table status like "myisam_test";

特点

• 对于char,varchar类型会自动填充空格以达到指定的列长度,对于binary,varbinary二进制类型的列会附件0x00以达到指定长度
• 较快,易于存储
• 易于崩溃后重建,因为记录保存的位置是固定的
• 一版不需要重建,除非删除了大量记录,而后需要释放相应的磁盘空间
• 通常会比动态格式占据更多的磁盘空间

动态表

表中包含有变长字符类型的列(如varchar,varbinary,blob,text),或者在创建表时指定了row_format = dynamic
相比静态表,动态表的处理比较复杂,每行都需要有个行头来记录该行的长度,由于长度不定,在更新的操作后,可能会产生存储上的碎片(可以使用OPTIMIZE TABLE和myisamchk -r消除碎片)。所在在性能上回比静态表低，但是存储空间的消耗也会比静态表小.

特点

• 字符串长度小于4的列外,其他字符列的长度都是动态的
• 每行记录的行头会有一个bitmap,标识该行哪些列包含空字串(对于字符类型的列)或0(对于数值类型的列),注意不包含NULL指的列。如果字符类型的列截取掉空格后长度为0,或者数值类型的列的值为0,那么这类列的劣质只需要在bitmao中标注即可,并不需要向磁盘中列实际对应的位置写任何值,如果是非空的字符列的话,按照实际字符长度去保存
• 相对于静态表,相同列长定义的情况下,会节省一定空间
• 每行仅需要存储字符实际需要的空间,如果之后记录变大,则该条记录可能会分片保存,这也是碎片产生的主因。比如修改一条记录附加更多信息,则该条记录的长度必然会扩展,当原始的空间无法存储新增的数据时,只能将新增的数据保存在另外的位置,这即产生了碎片。更新一行记录产生碎片的同时会生成一个链接,一个新链接至少20个字符,以便能满足扩展要求。myisamchk -ei可以用来查询表的统计信息,myisamchk -ed命令可以查询表对象的链接数,同时也可以通过定期执行OPTIMIZE TABLE或者是myisamchk -r命令消除碎片和链接
• 相比静态类型表,遇到崩溃时恢复操作会更加复杂,这也是因为记录可能存在碎片,其中某些链接(碎片)有可能会丢失,一旦出现这种情况,那么基本这行记录的数据就丢失了

动态类型表的行长度计算公式

3 + (number of columns + 7) / 8 + (number of char columns) + (packed size of numeric columns) + (length of strings) + (number of NULL columns + 7)

也可用通过show table status like查看表对象的状态,其中的 Avg_row_length 列的值是大概估算二栋记录平均长度,不够精确,但是比较简单,可供参考.

压缩格式表

特点

• 只能用myisamchk命令创建,解压也是用myisamchk
• 只读
• 不能修改或者添加记录
• 基于静态或动态格式表
• 占用空间更小

InnoDB存储引擎

终于到InnoDB了...

特性

• 设计遵循ACID模型,支持实物,拥有从服务崩溃中恢复的能力,能最大限度的保护用户数据。

ACID
即事务的4个特性:原子性(Atomiocity),一致性(Consistency),隔离性(Isolation)和持久性(Durability),这4个特性合称ACID模型

• 支持行级锁(Row-level Locking),并且引入类似Oracle数据库的一致性读特性,以提升多用户并发是的读写性能
• InnoDB引擎表组织数据时按照主键(Primary Keys)聚簇,通过主键查找数据时极为优异
• 在维护数据完整性方面,InnoDB支持外键(foreign key)约束
• 对于服务器软,硬件问题导致的宕机,不管当时数据库在做什么,都不必担心,也不必进行任何特殊操作,MySQL服务在启动时能够自动进行故障恢复(原理通用Oracle数据库中的示例恢复),而且在MySQL5.6版本中,这个过程据说比之前的版本还要快
• InnoDB拥有自己的缓存池(对应innodb_buffer_pool_size系统变量,类似Oracle数据库中的SGA_TARGET),常用数据(含索引)都在缓存中
• 对于insert,update,delete操作,会被一种称为change buffering的机制自动优化。InnoDB不仅仅提供了一致性读,而且还能缓存变更的数据,以减少次磁盘I/O

InnoDB注意点

• 所有表都要创建主键,最好选择常作为查询条件的列,如果没有合适的列,那么就创建到auto_incremnet的列上。
• 如果数据是通过多表关联获取,那么使用join,为提高join性能,最好在join列上穿件索引,并且join条件的这些列最好使用相同的数据类型和定义
• 综合考虑磁盘的I/O能力,必要时可以禁用auto commit自动提交功能
• 相互关联的DML操作放到一个事务中处理
• 停止视同LOCK TABLE语句,InnoDB能够处理会话并发读写同一个表对象,如果是希望执行排他的记录更新,那可以尝试使用`SELECT ... FOR
  UPDATE`语句
• 启动innodb_file_per_table选项,使表中的数据和索引保存在单独的文件中,而不是保存到系统表空间
• 评估数据和读写行为是否适用新的压缩(compression)特性,如果可以,建议在执行CREATE TABLE时指定row_format=compressed选项,以提高性能
• 启动MySQL服务时附加--sql_mode=NO_ENGINE_SUBSTITUTION，以防止表被创建成其他存储引擎
• 在新版本中,删除或创建索引性能有所提升,对系统的冲击也有所降低
• 清空(truncate)表,现在非常快,并且释放的空间能够被操作系统重用
• 使用DYNAMIC格式保存大数据类型(如BLOB,TEXT)将更有效率
• 在INFORMATION_SCHEMA库中提供了若干新的表对象,可用于监控存储引擎的工作和负载情况
• 新增了PERFORMANCE_SCHEMA库,可以用来查看存储引擎的性能统计数据

InnoDB引擎配置

5.6之前InnoDB引擎的数据都会放在系统表空间对应的数据文件,所以需要做如下设置

1. 指定InnoDB引擎数据文件保存路径

通过innodb_data_file_path来设置，如下

innodb_data_file_path=datafile_spec1[;datafile_spec2]...
# datafile_spec1语法如下
file_name:file_size[autoextend[:max:max_file_size]]

• file_name : 文件大小
• file_size : 指定文件大小
• autoextend : 指定是否使用扩展
• :max:max_file_size : 指定该数据文件最大可占用空间,可选参数

例子如下

innodb_data_file_path=ibdata01.df:2048M:autoextend:max:100G

需要注意,虽说InnoDB不会限制数据文件的大小,但操作系统自身可能会对单个文件的大小做限制,由于达到了操作系统的最大值导致数据文件扩展报错,可能会导致MySQL数据库崩溃
如果有多个文件可以用;分隔,如下

innodb_data_file_path=ibdata01.df:2048M:autoextend:max:100G;ibdata02.df:2048M:autoextend:max:100G

默认InnoDB系统表空间对应的数据会放在innodb_data_home_dir目录下

2. 每个表的数据分开保存

5.6之前InnoDB引擎的数据都会放在系统表空间对应的数据文件,这样做有几个缺点

1. DBA管理成本增加

2. 系统空间的数据文件扩展后无法回收,即使表被删除或TRUNCATE,甚至该表空间已经没有数据了,已分配的空间也只能被InnoDB存储引擎使用,而不能被其他文件使用。如果需要回收这部分存储空间需要如下处理

   1. 导出数据
   2. 删除数据文件并重新配置innodb_data_file_path
   3. 重新启动MySQL服务
   4. 导入数据

为了避免以上的情况,可以将InnoDB定义为多重表空间(multiple tablespaces),这样设置后每个表都会拥有一个独享的.idb文件,这个文件就是独立的表空间,相当于MyISAM中的.MYI和.MYD
相比系统表空间,多重表空间有如下有点

• 各表对象的数据独立存储至不同的文件,可以更灵活的分散I/O,执行备份及恢复操作
• 能够支持compressed row format压缩存储数据
• 当执行TRUNCATE/DROP删除表对象时,空间可以即时释放回操作系统层。
• 空间自动扩展,无需额外配置

是否启用多重表空间由innodb_file_per_table来控制,如下

set global innodb_file_per_table=1;

对于数据原本是保存在系统空间的表,需要做如下操作

# 开启表空间
set global innodb_file_per_table=1;
# 修改表结构(alte table会触发表对象的重建,重建后的数据就会保存在独立的表空间)
alter table table_name engine=innodb;

注意

无论是否设置了innodb_file_per_table,系统表空间都是必须要有的,InnoDB自身需要使用系统表空间,存储内部数据字段及UNDO日志

3. 配置InnoDB日志文件

除了表空间,InnoDB还有自己专用的日志文件,即REDOLOG日志文件
默认InnoDB会创建两组大小均为5MB的日志文件,分别命名为ib_logfile0和ib_logfile1,日志保存在datadir变了指定的路径下

也可以使用如下的配置修改

• innodb_log_group_home_dir : 指定InnoDB的REDOLOG日志文件板寸路径,默认是在datadir变了指定的目录下
• innodb_log_file_size : 用于指定日志文件的大小,默认是5MB,每个日志文件最大不能超过512GB。本参数会影响检查点(checkpoint)的执行频率,已经故障的恢复时间,因此本参数值的设定也有些讲究。一般来说,日志文件设置得越大,检查点执行的频率就越低,从缓存池刷新数据到磁盘的次数就相对较少,因此能减少I/O操作,但是如果再这个期间出现故障,那么冲洗启动MySQL时,灾难恢复的时间也会越长。总和来看 : 要适量...
• innodb_log_files_in_group : 用于指定日志文件组的数量,默认(最少)是2个,最多不超过100个,也要适量...

注意这三个参数不支持动态修改(可以改,但改的步骤有讲究,操作不好的话MySQL就启动不了了)

如果需要动态调整需要依赖innodb_fast_shutdown参数,这参数默认为1,这个参数总共可以配置三个值,如下

• 0 : 等到会话关闭,所有事务结束,缓存区中的数据刷新到磁盘
• 1 : 关闭会话终止连接,将已提交的数据刷新至数据文件,未提交的数据则进行回滚,这种方式也被称为快速关闭
• 2 : 忽略当前的所有操作,直接关闭,下次启动时InnoDB需要执行故障恢复,重新读取日志文件中的数据,回滚未提交的事务等。

修改日志相关的配置

1. 修改innodb_fast_shutdown为1

set global innodb_fast_shutdown = 1;

1. 停止MySQL服务
2. 修改配置文件的日志相关的配置
3. 重新启动MySQL

4. 设置独立的UNDO表空间

什么是UNDO日志

对于实务操作来说,有提交(Commit)就必然会有回滚(Rollback),提交比较好理解,就是确定保存写入的数据,那么回滚就麻烦一些,因为他代表两步操作:首先撤销刚刚做的修改,而后将数据恢复至修改前的状态。那么,数据一经写入,怎么回复到修改前的状态呢?最简单的方式,但是就是在修改前先将旧数据保存下载,保存下来的这部分数据用专业术语形容,就是UNDO日志,存储在系统分配好的回滚段中。

在MySQL中回滚段默认是保存在系统表空间中,不过从MySQL5.6版本开始,InnoDB引擎中的UNDO日志也可以单独设置表空间,将InnoDB的UNDO日志从系统空间中移出,转移至一个独立的表空间中保存,于是就有了UNDO表空间,可以将UNDO表空间放置于SSD存储设置上已获得更好的性能

配置独立的UNDO表空间

想要使用独立的UNDO表空间,需要用到下面的配置项

• innodb_undo_directory : 用户指定保存UNDO日志的物理文件的位置
• innodb_undo_tablespaces : 用户指定UNDO表空间的数量,每个表空间都是独立的.idb文件,因此可以理解为UNDO数据文件的数量
• innodb_undo_logs : 用于指定UNDO表空间中回滚段的数量(老版本中名称为innodb_rollback_segments)

注意点

1. UNDO表空间配置只能建库前
2. UNDO表空间一旦创建就不能删除
3. 一旦使用了UNDO表空间,则无法降级到5.6之前的版本

使用事务

MySQL默认开启了自动提交,也就是每次执行的语句都会自动提交

1. 关闭自动提交

# 修改当前会话的配置
set autocommit = 0;
# 修改全局配置
set global init_connect = 'set autocommit = 0';
# 在配置文件中修改
[mysqld]
set global init_connect = 'set autocommit = 0';

2. 显式的声明事务

# 开始事务
start transaction;
# 执行sql
insert into `user` (`name`) value ('jin');
# 提交(commit)或者回滚(rollback)
commit;

逻辑存储结构

为了避免所有表和索引都被保存在系统表空间,导致严重的I/O争用,建议用innodb_file_per_table选项,这样创建的每个表都会独立存储(创建与表同名的.ibd文件)。这种方式创建的表会减少磁盘的碎片,并且当表被truncate时,也能将占用的空间释放回操作系统层,而不是像系统空间那样一旦占用就无法释放

InnoDB中的逻辑存储结构,从小到大分成了4种粒度

• 页(pages,也叫块)。页是InnoDB中的最小管理单位,同一个MySQL数据库,不管他分成多少个表空间,所有表空间都拥有相同的页大小。默认情况下,Page size的大小为16KB,不过可以在创建MySQL实例(初始化数据库)时通过innodb_page_size变了进行配置,可选值有4KB,8KB,16KB三种
• 扩展(Extents,也叫区)。每个扩展固定1MB大小,由64个16KB的页组成(大小为8KB时则由128个页组成,页大小为4KB时由256个页组成)
• 段(Segments),段本身有很多种,比如像数据段,索引段,还有前面提到过的回滚段,不过对于InnoDB来说,这里说的段实际上指的是独立表空间对应的数据文件
• 表空间(Tablespaces)。InnoDB逻辑存储单元中的最高粒度

Segment由无数个Extent组成,那么当表空间的空闲空间即将用尽,需要扩展时,对于需要扩展的表空间,InnoDB第一次时分配32个Pages,之后,每次扩展会分配一个完整的Extent给Segment,最大能够同时向Segment中增加4个Extent,以保证数据的连续性。
InnoDB比较特殊,他不是传统的堆组织表,而是个索引组织表,因此对于InnoDB来说数据就是索引,索引正式数据。InnoDB表的索引就需要两个段,其中一个用于B树的非页节点,另一个则用于B树的页节点。如果能保持叶节点存储在磁盘上的连续性,则能获得更好的IO性能,因为这些节点包含的正式表中实际的数据。

当通过show table status查看当前可用的空闲空间时,InnoDB会显示表空间中空闲的Extent。InnoDB会保留一些Extent,用于内部操作,这些Extent不会被包含在空闲空间中。

当从表中删除数据时,InnoDB会访问关联的B树索引。释放的空闲空间能否被其他用户使用,要看表空间中的Page或Extent是被什么操作释放的。删除一个或者表中所有记录能够释放给其他用户。不过要记住,删除的列只会被purge线程物理删除,而不是delete操作本身,purge会自动运行,当不再需要相关的Page构造回滚段或一致性读时就会将这部分被标记删除的数据物理移除,这种设定与InnoDB的多版本特性有关。

执行show engine innodb status可以查看InnoDB的基础信息,返回信息较长,不过这也说明其信息量很大,有效利用该语句输出的信息,能够加深我们队InnoDB引擎状态的了解

用户插入的记录与Page如果关联?

一条记录的长度(变长列除外,含varbinary,varchar,blob,text等类型),一般都会小于一个数据库页的一半,就是说,单条记录的最大长度不超过800B
如果一条记录的长度小于Page的一般,那么将会被完整的存储在一个Page内。当他增长到Page的一般时,变长列将选择在另外的Page保存,知道满足记录长度不超过Page一般的条件。对于选择Page外存储的列,InnoDB会在行头部前768个字段保存相关信息。每一个列都拥有自己的溢出页列表。这个768B前缀是一个20B长的值,保存着列实际的长度,以及指向其溢出的列表
表对象中的数据经过一段时间的增删改操作,极可能出现存储上的碎片,也就是说,数据再磁盘上的物理顺序并不相邻,各记录之间存在着或大或小的空闲空间。
对象存在着碎片的一个表象,就是他占用的空间比其应该占用的要多,可以简单估算下。假设一个表的平均记录长度是1KB,当前共有两万条记录,那么20MB应该就能存储该表的所有数据了,考虑到InnoDB数据和索引都是以B树的方式保存,其因子数从50%~100%,如果当前竟然占用了200MB空间,那显然就不正常了,就一定有碎片产生。
去除碎片可以通过重建表的方式,对于InnoDB来说,任意alter table操作都可能会导致表的重建,从而消除碎片。另外也可通过mysqldump先备份再导入的方式去除碎片。
清楚碎片不仅仅为了提高了资源利用率,更重要的是碎片过多可能会对性能造成不利影响,所以对于频繁增删改查的表空间,应该定时清理。

多版本机制

InnoDB能保存一定数量的记录修改的历史版本,以支持实物的特性,比如一致性和回滚。这部分信息保存在系统表空间中被称为回滚段(rollback segment)的数据结构中,与Oracle数据库的实现机制类似。InnoDB使用回滚段中的信息来执行实物的回滚,同时还可以用这些信息构造一致性读(Consisten Read)所需要的数据。
InnoDB引擎内部在保存数据到数据库时,每一行都有3个自动生成的内部列

• DB_TRX_IDG : 占用6B,用来标记事务的标识符,记录下最后一个事务的操作类型(更新或修改),注意,删除操作会被当成update处理,只是在其中设置特殊的位置指出实例操作为delete。
• DB_ROLL_PRT : 占用7B,被称为回滚标记,用来指定UNDO日志记录写到回滚段中的日志。如果记录被更新,那么UNDO日志会包含用于构造记录更新前状态的所有必要信息
• DB_ROW_ID : 占用6B,这个从本质上来说,就是记录行的行ID,如果是由InnoDB自动生成的聚簇索引(隠式主键),那么改索引中就会包含记录行的行ID值,否则,DB_ROW_ID不会出现在任何索引中。

回滚段中的UNDO日志分为insert UNDO日志和update UNDO日志两种:

• inserT UNDO : 仅在事务回滚时需要,事务提交后即可被废弃
• UPDATE UNDO : 用于构造一致性读,这部分数据只有当没有任何事务需要用到想过信息构造记录行的之前版本,已提供一致性读的快照才会被遗弃。

考虑到InnoDB的回滚段,一致性读等特性,建议事务尽早提交,不要长期持有,这使得InnoDB无法丢弃update UNDO日志的数据,可能会造成回滚段过大,占满整个系统表空间,从而拖垮整个InnoDB引擎的运行。
回滚段中的UNDO日志记录大小,一版情况下都小于实际插入或修改的记录大小,用户可用通过这个特点大致计算回滚段所需要的空间。在多版本模式下,记录被DELETE语句删除时不会立刻在物理上彻底删除。只有当InnoDB废弃了update UNDO日志后,才会从物理上一处关联的列和索引记录,这种一处操作被称为purge,他的执行速度很快。
如果以相同的频率插入和删除表中的列,purge线程可能会存在延迟的情况,这样表就会不断增大,因为那些被(标识)删除的列实际上仍然存在,此时磁盘操作可能会变慢,对于这种情况,考虑减少新增记录,并且为purge线程分配更多的资源。控制purge线程延迟可以通过系统变量innodb_max_purge_lag进行设置

联机修改表结构

对于MySQL数据库来说,即使是InnoDB引擎,在执行DDL时也令人颇感头疼。
在5.6之前,对InnoDB银引擎表执行DDL(加列,删列,建索引,修改列定义等均包含在内),MySQL的处理流程大致如下

1. 创建一个结构与源表对象完全相同的临时表(隠式操作,该对象对用户不可见),并将该表的结构修改为期望的结构
2. 锁定源表,只许查询,不许修改。
3. 将源表数据复制到新创建的临时表,类似insert into new_table select * from old_table;
4. 将表重命名,新创建的临时表名称修改为正式表名
5. 接触锁定,删除源表

可以看出每次DDL就相当于重建对象,如果是小表的话还好,但是表比较大的话,第3步会持续很长时间,期间不能做其他操作

对大表执行DDL的几种思路

1. 尽量不要搞太大的表,可以使用分表实现(这种方法会造成之后会维护多张表对象,维护成本增高)
2. 通过MySQL复制特性。先在slave端对大表做变更,而后执行主从切换,这种方式步骤的繁琐,容易造成复制环境中主从不同步
3. 在表对象设计时,预留出若干字段(无法预知后面有哪些可能会用到的字段)
4. 借用开源方案(Online Schema Change)

在5.6版本之后,MySQL官方终于对在线DDL修改InnoDB引擎表提供了(有限)支持。
为什么是有限支持呢?,因为当前还没有做到,对所有的DDL操作都支持练级执行(严格来说,即使是RDBMS市场中的领头羊Oracle也做不到),某些情况下还是需要复制整个表,或者DDL操作时,不允许DML同事修改表中记录等,同时某些场景下还有限制条件。官方提供了一份汇总表,罗列了不同操作对应的场景。

• 就地进行(In-Place) : 用于标识改操作是否允许附加ALGORITHM=INPLACE子句,如果允许的话则表示修改操作可以直接在该表对象上执行(也就是In-Place),最好是Yes
• 复制表(Copies Tables) : 用于标识该操作是否需要复制整个表,最好是No,多数情况跟In-Place的值相反
• 允许同时执行DML(Allows Concurrent DML) : 用于标识该操作是否允许联机进行,最好是Yes,可以通过指定LOCK=NONE来允许DDL操作进行过程中仍然接受DML,而且某些情况下MySQL也会自动允许某些粒度的DML/DDL同时执行,如果同时DML语句被允许,那么同时执行查询必然也是可以的
• 允许同事执行查询(Allows Concurrent Queries) : 用于标识当DDL执行时,是否允许同事查询对象中的数据,最好是YES。征程情况下,所有连接DDL都是允许同同时今夕查询的

建议把关注点放在"是否就地操作"和"是否复制表"两列上,这两列值的是与否,直接影响联机DDL的操作性能。一版来说,就地操作,而且不用复制表的操作性能是最好的;某些情况下尽管是就地操作,但仍然需要复制数据,不过即使是这种情况也会比表对象重建要高效一些。
这个表格还是比较清晰的,对于单项操作来说如果认真看还是能看得懂的,不过现实情况有时候要更复杂,比如我们有可能在增加列的同事删除索引,或者修改列定义的同时增加索引之类,就是说执行的DDL语句中符合了多项修改。在练级DDL前,不管复合多少项修改,其实对于MySQL的处理逻辑来说没有影响,反正表对象要重建,就按照新的定义创建对象并复制数据就好了。如今引入链接DDL,情况就会复杂一些,表7-3中的内容能够提供一些参考。如果不确定所做的操作,究竟会产生什么样的影响,那么可以换种思路,考虑将一条DDL拆分成多条DDL分别执行。

联机DDL相关语句的语法

对于InnoDB引擎表来说,执行ALTER TABLE时,并不需要特别指定是否使用联机DDL,MySQL会自动进行选择,是否进行就地(In-Place)执行,是否允许并行DML等。不过用户也可以通过ALTER TABLE语句中的LOCK和ALGORITHM两个子句,来明确控制联机DDL时的操作行为。其中,LOCK子句对于表并行度控制的微调比较有效,而ALGORITHM子句则对于操作时的性能和操作策略有较大影响。

LOCK语句用于控制表变更期间读写并发粒度,总共有4个选项

• DEFAULT : 默认处理策略,等同于不指定LOCK子句
• NONE : 不使用锁定策略,这种情况下其他会话既能读也能写
• SHARED : 采取共享锁定策略,这种情况下其他会话可读但不可写
• EXCLUSIVE : 采取排他锁定,这种情况下其他会话既不能读也不能写

ALGORITHM子句有3个选项值

• DEFAULT : 由MySQL按照默认方式处理,相当于不指定ALGORITHM子句,如果指定了ALGORITHM子句为DEFAULT,则跟不指定没有区别,一是因为该子句默认值就是DEFAULT,另外即使指定了也还是默认处理逻辑
• INPALCE : 如果支持直接对当前表对象做修改,则直接就地修改,对于联机DDL语句,最好是能够INPLACE,但是前提是操作确实支持INPLACE,如果对于不支持INPLACE的操作或引擎指定ALGORITHM值为INPLACE,则语句执行就会报错
• COPY : 不管是否支持就地修改,都采取将表对象中数据新复制一份的方式修改,这是在联机DDL被引入前的操作方式。毫无疑问,这种方式成本高,代价发,执行时间长,应尽可能避免此类情况发生,不过某些情况下,可能必须采取COPY方式,比如说,重定义主键的情况等

如果希望并发粒度最高,那么就要指定LOCK=NONE(可读可写),若希望操作的成本最低,则最好指定ALGORITHM=INPLACE(可读可写),若希望操作的成本低,则最好指定ALGORITHM=INPLACE(直接对对象进行操作,涉及读写的数据量最小)。不过不是说指定了值,实际执行就一定是做对应的操作,需要考虑实际的情况,比如说即使明确的指定了LOCK=NONE,但是实际执行的是创建或删除主键的操作,那怎么可能不加锁呢

InnoDB表对偶像的限制条件

1. 最大和最小

• 单表最多不超过1020列,在5.6.9版本之前最多不超过100列,最多能创建64个辅助索引
• 默认情况下,单列索引(含前缀索引)的键长度不超过767B。注意这个还跟字符集有关系。举例来说,在TEXT/VARCHAR类型列上创建前缀索引,假设当前是utf8字符集,因为每个字符占用3个字节,那么对于长度超过255个字符的列就可能会遭遇这种限制。不过,当启用了innodb_large_prefix配置选项时,最大长度就能提高到3072B。如果创建的前缀索引长度超出了最大值会怎么样呢?也得看情况,对于非唯一索引,会自动缩减到最大长度,而对于唯一索引则会抛出错误。
• 尽管InnoDB引擎支持列长度超过65535B,不过创建表时,仍然不能创建包含VARBINARY/VARCHAR列的符合长度超过65535B,例如,下面创建时会报错
• 一些老的文件系统,最大支持的文件大小不能超过2GB,对于这种系统上运行的InnoDB,创建数据文件时需要注意了,可以通过创建多个数据文件的方式来突破这一限制,如果使用独立表空间,那么需要控制表对象中的记录量了
• InnoDB日志文件最大不超过512GB
• InnoDB系统表空间最少需要10MB空间,最大则能够支持到64TB(for billion database pages),这同时也表示单个表最大不能超过64TB
• 默认的数据库中数据页的大小为16K,可以在创建MySQL实例时通过innodb_page_size选项指定为16K/8K/4K三种。目前暂不支持增加数据页的大小,因为当前InnoDB没有处理16K大小page size的函数,如果指定了超过这个值的page size,启动InnoDB时有可能会报错。另外,在同一个MySQL实例中的数据文件和日志文件的数据页大小必须相同。

1. 制约因素

• ANALYZE TABLE语句输出索引信息(显示在SHOW INDEX输出中的Cardinality),通过随机访问每个索引树,并更新相应的索引统计信息。由于这只是个预估值,因此重复执行ANALYZE TABLE语句可能生成不同的数值,这种差异使ANALYZE TABLE语句在InnoDB引擎表上执行更快(相比其他引擎如MyISAM),但不能做到100%准确,因为并非所有的列都被统计。如果由于ANALYZE TABLE生成的统计信息不正确,导致执行计划并不理想(这种情况完全有可能出现),那么对于用户来说,恐怕就得通过FORCE INDEX强制指定索引。
• SHOW TABLE STATUS语句不能列出InnoDB引擎表的实际统计数据(除了物理大小),记录行数,平均纪录长度等信息都仅是预估值
• InnoDB引擎并不会讲表的记录量保存在内部的某处(对于事务引擎这也确实比较难实现),因此执行SELECT COUNT(*) FROM tbl时,InnoDB必须检索全表(也可能是该表主键),这可能造成效率和性能上的问题,如果改表不是经常查询,那么应用查询缓存技术会比较有效,如果是经常查询总记录数,MySQL建议对于InnoDB引擎表的这类需求,考虑通过创建中间表专门记录表记录行数的方式来处理。当然如果只是想要得到一个大概的数据量,那么SHOW TABLE STATUS中显示的信息就可以了
• 在windows系统上,InnoDB使用小写名称保存数据库和表名。因此对于Windows/UNIX平台迁移的数据库,建议创建对象时都是用小写规则
• 对于auto_increment列,建议创建单列索引,如果是复合索引,那么最好定义为第一列
• 当初始化表上之前指定了auto_increment列,InnoDB会加载一个独占锁在auto_increment列的索引的最大值上。在访问自增长计数时,InnoDB使用特殊的表锁AUTO-INC,该锁只作用于当前的SQL语句,而非整个事务,其他会话或事务仍在AUTO-INC表锁持有时执行插入
• 当重启MySQL服务时,InnoDB可能会重用之前的auto_increment列生成但未保存的值(即由之前的事务生成的值,不过rollback了)
• 当auto_increment整数列超出范围时,insert操作会返回复制键错误的消息,这是MySQL层的行为。不过一般不用在意这个问题,MySQL支持多种整型,其中BIGINT类型有64位长度,支持的范围从--9223372036854775808到9223372036854775807,计算每秒插入一百万条记录,BIGINT至少也能坚持100年
• DELETE FROM tbl并非重建表,而是逐条删除记录,因此,清空表中记录还是首推TRUNCATE TABLE tbl
• 目前级联的外键行为并不会触发triggers,如果应用有触发器,那么需要考虑关联数据的更新
• 在创建表时,注意列名不能与InnoDB内部列相同(如DB_ROW_ID,DB_TRX_ID,DB_ROLL_PTR,DB_MIX_ID),否则MySQL会抛出1005错误或error-1错误。

1. 锁和事务

• 当系统变量innodb_table_lock=1时(默认就是1),LOCK TABLES会在表上持有两个锁,一个是MySQL层持有的锁,另外InnoDB层也需要持有一个锁。在MySQL 4.1.2版本之前并不会持有InnoDB层锁。如果要恢复旧版本时的特性,可以考虑将innodb_table_locks参数值设置为0。不过注意如果没有持有InnoDB层的锁,LOCK TABLES语句由可能会在仍有其他事务锁定表中记录时依然返回锁定成功的信息。在5.6版本中,设置innodb_table_locks=0对于LOCK TABLES ... WRITE语句无效，不过对于隐式的LOCK TABLES ... WRITE (比如triggers) 或LOCK TABLES ... READ语句仍然有效
• InnoDB引擎在事务中持有的锁会在事务提交或回滚时释放,因此当autocommit=1时执行LOCK TABLES并没有意义,因为持有的锁会马上释放
• 事务过程中不能显示的去锁定其他表,因为LOCK TABLES会隐式执行COMMIT和UNLOCK TABLES。
• 在5.5之前的版本中,InnoDB引擎并发数据修改事务的总数量,不能超过1023,从5.5开始并发事务数可以达到128*1023个