某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > [MySQL Reference Manual]14 InnoDB存款和储蓄引擎

[MySQL Reference Manual]14 InnoDB存款和储蓄引擎

时间:2020-02-07 19:56

14 InnoDB存储引擎

14 InnoDB存储引擎... 1

14.1 InnoDB说明... 5

14.1.1 InnoDB作为默认存储引擎... 5

14.1.1.1 存储引擎的趋势... 5

14.1.1.2 InnoDB变成默认存储引擎之后... 5

14.1.1.3 InnoDB表好处... 6

14.1.1.4 InnoDB表最佳实践... 6

14.1.1.5 InnoDB表提升... 6

14.1.1.6 InnoDB作为默认存储引擎测试... 6

14.1.1.7 验证InnoDB是默认存储引擎... 7

14.1.2 检查InnoDB可用... 7

14.1.3 关闭InnoDB. 7

14.2 InnoDB概念和体系结构... 8

14.2.1 Mysql和ACID模型... 8

14.2.2 InnoDB事务模式和锁... 9

14.2.3 InnoDB锁定模式... 9

14.2.3.1 意向锁... 9

14.2.3.2 死锁例子... 10

14.2.4 一致性无锁读... 10

14.2.5 锁读... 11

14.2.6 InnoDB Record,Gap,Next-key锁... 11

14.2.6.1 Record Lock. 11

14.2.6.2 Next-Key Lock. 11

14.2.6.3 Gap Lock. 11

14.2.7 使用Next-key锁定避免幻影... 11

14.2.8 不同语句的锁定设置... 12

14.2.9 事务提交和回滚的影响... 13

14.2.10 死锁发现和回滚... 13

14.2.11 如何处理死锁... 13

14.2.12 InnoDB多版本... 14

14.2.12.1 多版本内部细节... 14

14.2.12.2 Rollback Segment管理指导... 14

14.2.13 InnoDB表和索引结构... 14

14.2.13.1 .frm文件在innodb表的角色... 14

14.2.13.2 聚集和secondary索引... 15

14.2.13.3 FULLTEXT索引... 15

14.2.13.4 索引的物理结构... 15

14.2.13.5 Insert Buffering. 15

14.2.13.6 自适应hash索引... 16

14.2.13.7 物理行结构... 16

14.3 InnoDB配置... 16

14.3.1 配置InnoDB为只读... 17

14.3.1.1 如何启动... 17

14.3.1.2 使用场景... 17

14.3.1.3 如何工作... 17

14.4 InnoDB管理... 18

14.5 InnoDB表空间管理... 18

14.5.2 InnoDB File-Per-Table模式... 18

14.5.3 启动禁用File-Per-Table模式... 19

14.5.4 指定TableSpace的路径... 19

14.5.5 把表空间复制到另外一个服务上... 20

14.5.6 把undo log移出System表空间... 21

14.5.7 修改InnoDB日志文件,增长InnoDB表空间... 21

14.5.8 使用原生磁盘分区作为系统表空间... 22

14.6 InnoDB表管理... 23

14.6.1 创建InnoDB表... 23

14.6.2 把InnoDB表移动到另一个机器... 24

14.6.2.1 跨平台复制移动使用小写... 24

14.6.2.2 传输表空间... 24

14.6.2.3 MySQL企业备份... 24

14.6.2.4 复制数据文件... 24

14.6.2.5 ibd文件可移植性... 24

14.6.3 使用事务批量DML操作... 25

14.6.4 把MyISAM转化为InnoDB. 25

14.6.4.1 减少MyISAM内存使用,增加InnoDB内存使用... 25

14.6.4.2 查看太长或者太短的事务... 25

14.6.4.3 不要太担心死锁... 26

14.6.4.4 计划存储分布... 26

14.6.4.5 转化一个现有的表... 26

14.6.4.6 克隆表结构... 26

14.6.4.7 转化已存在数据... 26

14.6.4.8 存储需求... 27

14.6.4.9 仔细选择Primary Key. 27

14.6.4.10 应用程序性能考虑... 27

14.6.4.11 InnoDB表和文件的关联... 27

14.6.5 InnoDB自增处理... 27

14.6.5.1 传统InnoDB自增长锁... 27

14.6.5.2 配置InnoDB自增锁... 28

14.6.6 InnoDB和外键约束... 30

14.6.7 InnoDB表的限制... 30

14.6.7.1 最大和最小... 30

14.6.7.2 索引类型... 31

14.6.7.3 InnoDB表的限制... 31

14.6.7.4 锁定和事务... 32

14.7 InnoDB压缩表... 32

14.7.1 表压缩概述... 32

14.7.2 启动压缩表... 32

14.7.3 InnoDB压缩表调优... 33

14.3.7.1 什么时候使用压缩... 33

14.3.7.2 数据特性和压缩... 34

14.3.7.3 数据库压缩VS应用程序压缩... 34

14.3.7.4 工作负荷特点和压缩... 34

14.3.7.5 配置特点和压缩... 34

14.3.7.6 选择压缩page大小... 35

14.7.4 监视压缩... 35

14.7.5 InnoDB如何压缩... 35

14.7.5.1 压缩算法... 35

14.7.5.2 InnoDB存储引擎和压缩... 35

14.7.5.3 B树页压缩... 36

14.7.5.4 压缩BLOB,VARCHAR,TEXT列... 36

14.7.5.5 压缩和innodb buffer pool36

14.7.5.6 压缩和InnoDB Redo Log文件... 37

14.7.6 OLTP负荷的压缩... 37

14.7.7 压缩语法警告和错误... 37

14.8 InnoDB文件格式管理... 39

14.8.1 启动文件格式... 39

14.8.2 验证文件兼容模式... 39

14.8.2.1 InnoDB打开时的兼容性检查... 40

14.8.2.2 表打开时的兼容性检查... 40

14.8.3 识别使用的文件格式... 41

14.8.4 文件格式降级... 41

14.9 InnoDB 行存储和行格式... 41

14.9.1 InnoDB行存储概述... 41

14.9.2 位表指定行格式... 42

14.9.3 DYNAMIC和COMPRESSED行格式... 42

14.9.4 Compact和REDUNDANT行格式... 42

14.10 InnoDB磁盘I/O和文件空间管理... 42

14.10.1 InnoDB磁盘I/O.. 42

14.10.2 文件空间管理... 43

14.10.3 InnoDB 检查点... 43

14.10.4 表整理... 43

14.10.5 使用TRUNCATE TABLE清理表空间... 44

14.11 InnoDB和Online DDL. 44

14.11.1 Online DDL概述... 44

14.11.2 Online DDL的性能和并发考虑... 45

14.11.3 Online DDL的SQL语法... 46

14.11.4 组合和独立DDL语句... 46

14.11.5 Online DDL的例子... 47

14.11.6 Online DDL的细节... 47

14.11.7 Online DDl Crash如何恢复... 48

14.11.8 分区表上的Online DDL. 48

14.11.9 Online DDL限制... 48

14.12 innodb启动选项和系统变量... 49

14.13 InnoDB性能... 49

14.13.1 InnoDB buffer pool配置... 49

14.13.1.1 配置InnoDB Buffer Pool预读(Read-Ahead)49

14.13.1.2 配置InnoDB刷新比率... 49

14.13.1.3 Buffer Pool处理scan. 50

14.13.1.4 使用多buffer pool实例... 51

14.13.1.5 快速重启后加载buffer pool51

14.13.1.6 调整InnoDB buffer pool刷新... 52

14.13.2 InnoDB信号量和Read/Write锁实现... 52

14.13.3 配置InnoDB的内存分配... 53

14.13.4 配置innodb Change Buffer53

14.13.5 配置InnoDB的线程并发... 54

14.13.6 配置innodb I/O后台线程数量... 54

14.13.7 分组提交... 54

14.13.8 配置InnoDB主线程I/O率... 55

14.13.9 在InnoDB自旋循环中使用PAUSE指令... 55

14.13.10 设置自旋锁轮询... 55

14.13.11 InnoDB使用MySQL性能框架整合... 55

14.13.12 使用多RollBack Segment获得更好的扩展性... 56

14.13.13 配置InnoDB清理调度... 56

14.13.14 优化InnoDB只读事务... 56

14.13.15 使用CRC32 Checksum算法,快速checksum.. 56

14.13.16 undo log保存在独立包空间... 57

14.13.17 优化统计信息... 57

14.13.17.1 配置持久优化统计信息参数... 57

14.13.17.2 配置非持久性统计信息值... 58

14.13.18 评估ANALYZE TABLE复杂性... 59

14.14 InnoDB INFORMATION_SCHEMA表... 59

14.15 InnoDB监视器... 59

14.15.1 InnoDB监视器类型... 59

14.15.2 启动innodb监视器... 60

14.15.3 InnoDB标准监控输出和锁监控输出... 61

14.15.4 InnoDB表空间监视器输出... 62

14.15.5 表监视器... 63

14.16 InnoDB备份和恢复... 63

14.16.1 innodb恢复进程... 63

14.17 InnoDB和MySQL复制... 63

14.18 InnoDB集成memcached.. 64

14.19 InnoDB Troubleshooting. 64

14.19.1 troubleshooting InnoDB I/O问题... 64

14.19.2 启动损坏的数据库... 65

14.19.3 排查InnoDB数据字典操作... 65

14.19.4 InnoDB错误处理... 67

14.19.5 InnoDB错误代码

14.19.6 系统错误代码

15. 其他存储引擎

  1. 其他存储引擎... 1

15.1 设置存储引擎... 3

15.2 MyISAM存储引擎... 4

15.2.1 MyISAM启动选项... 5

15.2.2 Key的空间要求... 5

15.2.3 MyISAM表存储格式... 5

15.2.3.1 静态表特性... 5

15.2.3.2 动态表特性... 6

15.2.3.3 压缩表特性... 6

15.2.4 MyISAM表问题... 7

15.2.4.1 MyISAM表损坏... 7

15.2.4.2 表没有被正确关闭... 7

15.3 MEMORY存储引擎... 8

15.3.1 性能特点... 9

15.3.2 MEMORY表的物理特性... 9

15.2.3 MEMORY的DDL操作... 9

15.2.4 索引... 9

15.2.5 用户创建和临时表... 10

15.2.6 导入数据... 10

15.2.7 MEMORY表和复制... 10

15.2.8 管理内存使用... 10

15.3 CSV存储引擎... 11

15.3.1 CSV表的检查和修复... 11

15.3.2 CSV的限制... 11

15.4 ARCHIVE存储引擎... 12

15.5 BLACKHOLE 存储引擎... 13

15.7 MERGE存储引擎... 14

15.7.1 MERGE表的优缺点... 16

15.7.2 MERGE表问题... 16

15.8 FEDERATED存储引擎... 17

15.8.1 FEDERATED存储引擎概述... 17

15.8.2 如何创建FEDERATED表... 18

15.8.2.1 使用CONNECTION创建FEDERATED.. 18

15.8.2.2 创建FEDERATED表使用CREATE SERVER. 19

15.8.3 FEDERATED存储引擎注意点... 20

15.8.4 FEDERATED存储引擎资源... 21

15.9 EXAMPLE存储引擎... 21

15.10 其他存储引擎... 21

15.11 MySQL存储引擎概述... 21

15.11.1 插件是存储引擎结构... 21

 

存储引擎是MySQL组件,用来控制不同表类型的SQL操作。InnoDB是默认而且是最常用的存储引擎,Oracle推荐使用,除非是特别的使用场景。

MySQL Server使用可插入的存储引擎结构可以让正在运行MySQL的存储引擎load,unload。

可以使用SHOW ENGINES语句来决定引擎是不是可以使用。Support字段说明引擎是不是被支持。

MySQL 5.6支持的存储引擎

InnoDB:MySQL 5.6默认的存储引擎。InnoDB是事务安全的存储引擎,有commit,rollback,crash-recovery功能来保护用户数据。InnoDB行级锁和Oracle样式的一致性无锁读,增加多用户并发和性能。InnoDB以聚集索引方式存储用户数据,对主键查询,可以减少IO。InnoDB也支持外键约束。

MyISAM:这些表footprint比较小,表级锁定限制了读写性能,一般用于只读或者读多写少的应用。

Memory:所有的数据都存放在RAM内,能够快速的访问数据。这个引擎就是以前的HEAP引擎。

CSV:这个表实际上是文本文件使用了逗号分隔值。CSV表一般用于导入导出,数据线存放在InnoDB,在需要导入导出的时候转为CSV表。

Archive:这个是紧密的非索引表,用来存储和获取大量数据。

Blackhole:这个引擎其实不保存数据,和linux的/dev/null一样。查询结果都是为空,这些表可以复制,DML语句会传到Slave,但是master不保存数据。

Merge:可以让DBA和开发逻辑的把一些MyISAM表当成一个对象。

Federated:连接到独立的MySQL服务,在这个逻辑数据库上为很多歌物理服务创建对象。

Example:这个是MySQL的例子,如何开始编写新的存储引擎。。

 

以下是不同存储引擎的基本特点:

 

Feature

MyISAM

Memory

InnoDB

Archive

NDB

Storage limits

256TB

RAM

64TB

None

384EB

Transactions

No

No

Yes

No

Yes

Locking granularity

Table

Table

Row

Row

Row

MVCC

No

No

Yes

No

No

Geospatial data type support

Yes

No

Yes

Yes

Yes

Geospatial indexing support

Yes

No

Yes[a]

No

No

B-tree indexes

Yes

Yes

Yes

No

No

T-tree indexes

No

No

No

No

Yes

Hash indexes

No

Yes

No[b]

No

Yes

Full-text search indexes

Yes

No

Yes[c]

No

No

Clustered indexes

No

No

Yes

No

No

Data caches

No

N/A

Yes

No

Yes

Index caches

Yes

N/A

Yes

No

Yes

Compressed data

Yes[d]

No

Yes[e]

Yes

No

Encrypted data[f]

Yes

Yes

Yes

Yes

Yes

Cluster database support

No

No

No

No

Yes

Replication support[g]

Yes

Yes

Yes

Yes

Yes

Foreign key support

No

No

Yes

No

No

Backup / point-in-time recovery[h]

Yes

Yes

Yes

Yes

Yes

Query cache support

Yes

Yes

Yes

Yes

Yes

Update statistics for data dictionary

Yes

Yes

Yes

Yes

Yes

[a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher.

[b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

[c] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

[d] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

[e] Compressed InnoDB tables require the InnoDB Barracuda file format.

[f] Implemented in the server (via encryption functions), rather than in the storage engine.

[g] Implemented in the server, rather than in the storage engine.

[h] Implemented in the server, rather than in the storage engine.

14.1 InnoDB说明

Innodb是通用的存储引擎,平衡高可用和高性能。从Mysql 5.5开始作为Mysql的默认存储引擎。

 

InnoDB重要特点

InnoDB重点:

1.DML操作遵循ACID,支持事务提交,回滚和灾难恢复来保护数据。

2.支持行锁,oracle类型的读一致性。增加多用户并发。

3.InnoDB保存在磁盘中,通过primary key来优化查询。

4.为了维护数据完整性,InnoDB也支持外键约束。

5.InnoDB可以和mysql的其他存储引擎混合。

6.InnoDB设计,当处理大数据时,最大化性能。

 

InnoDB存储引擎特性:

InnoDB自己维护了buffer pool来缓存数据和索引。默认启动innodb_file_per_table,这样InnoDB表的索引和数据都被存放在独立的一个文件中。如果disable,被保存在系统表空间中。

15.1 设置存储引擎

当你创建新表,你会指定存储引擎,在ENGINE选项上。

-- ENGINE=INNODB not needed unless you have set a different
-- default storage engine.
CREATE TABLE t1 (i INT) ENGINE = INNODB;
-- Simple table definitions can be switched from one to another.
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

当你忽略ENGINE选项,就是用默认存储引擎。目前默认存储引擎是InnoDB。你可以 指定默认存储引擎通过—default-storage-engine启动参数,或者配置文件中指定default-storage-engine。也可以在runtime设置使用set命令设置变量:

SET default_storage_engine=NDBCLUSTER;

临时表的存储引擎,通过create temporary table来创建,默认存储引擎通过default_tmp_storage_engine在启动前或者runtime设置。

可以使用alter table语句来转化表的存储引擎

ALTER TABLE t ENGINE = InnoDB;

如果指定的存储引擎没有被编译或者已经编译了但是没有激活,MySQL会使用默认存储引擎来代替。为了防止被魔火,可以启动NO_ENGINE_SUBSTITUTUIN SQL模式。如果指定的引擎不可用,会产生一个错误,而不是一个警告,表不会被创建。

对于新表,frm文件用来保存表和列的定义。表的索引和数据可能被存放在其他一个或者多个文件中,根据存储引擎决定。

14.1.1 InnoDB作为默认存储引擎

15.2 MyISAM存储引擎

MyISAM的基本特性:

Storage limits

256TB

Transactions

No

Locking granularity

Table

MVCC

No

Geospatial data type support

Yes

Geospatial indexing support

Yes

B-tree indexes

Yes

T-tree indexes

No

Hash indexes

No

Full-text search indexes

Yes

Clustered indexes

No

Data caches

No

Index caches

Yes

Compressed data

Yes[a]

Encrypted data[b]

Yes

Cluster database support

No

Replication support[c]

Yes

Foreign key support

No

Backup / point-in-time recovery[d]

Yes

Query cache support

Yes

Update statistics for data dictionary

Yes

每个MyISAM表被存储在3个文件。文件以表名开头,并且有一个扩展名。Frm文件保存了表格式。Myd文件包村了数据文件,myi保存了索引文件。

可以通过mysqlcheck客户端,或者myisamchk工具,来检查和修复myisam表。也可以使用myisampack来压缩myisam表。

MyISAM主要由以下几个特点:

1.所有数据低字节先存。

2.所有数值类型高字节先存。

3.可以支持large files,最大63bit文件长度。

4.MyISAM最大行数2^32^2

5.每个MyISAM表64个索引,每个列最多16个索引

6.最大key为1000个字节。

7.当行被顺序插入,比如使用了auto_increment列,提高空间使用率。

8.支持一个表一个auto_increment列。

9.动态行大小,减少碎片。

10.MyISAM支持并发插入,如果表没有空闲的blocks你可以插入一个新行的同事,其他表读取表。

11.可以把数据文件目录和索引文件目录放在不同的目录上,在createtable的时候指定data directory和indexdirectory。

12.BLOB,TEXT可以被索引。

13.NULL值可以在所有列中

14.每个字符列可以有不同的字符集

15.每个MyISAM的索引文件有个标记表示表是否给关闭。

16.myisamchk标记呗检查过的表,如果使用了update-state选项。如果使用—fast选项不会被标记

17.myisamchk –analyze保存了部分可以的统计信息

18.myisampack可以压缩blob和varchar列

其他支持的特性:

1.支持varchar类型

2.varchar列可能固定或者动态行大小

3.varchar,char的长度最多为64KB。

4.任意长度的unique约束

14.1.1.1 存储引擎的趋势

在MySQL 5.6之后版本:

1.InnoDB支持全文索引。

2.InnoDB在执行只读,或者读多写少比较出色。

3.在只读媒体上可以使用InnoDB表。

15.2.1 MyISAM启动选项

具体看:

14.1.1.2 InnoDB变成默认存储引擎之后

从MySQL 5.5.5之后默认存储引擎变成了InnoDB,可以再create table语句中使用engine=myisam来制定表的存储引擎。Mysql和informateion_schema任然在使用myisam。对于grant表,不能转化为InnoDB。

15.2.2 Key的空间要求

MyISAM表使用B树索引。你初略的计算index文件的大小,(key_length+4)*0.67,覆盖所有key。不压缩的key评估的大小。

String索引空间可以被压缩。如果索引的第一个部分是string,也会使用前缀压缩。索引压缩会让索引文件变小。

在MyISAM表你可以前缀压缩个数通过PACK_KEYS=1来指定,在创建表的时候。个数代表钱几个字节。

14.1.1.3 InnoDB表好处

1.如果服务crash,在服务启动后不需要做其他事情。InnoDB会自动recovery数据库。

2.InnoDB buffer pool缓存表和索引,把数据和索引放在内存中可以提高处理速度。

3.放在不同表上的数据可以使用外键来关联。

4.若数据在磁盘或者内存中损坏,checksume机制会警告你。

5.当每个表上都设计了合适的索引,设计的列都会被优化。

6.insert,update,delete会被change buffer机制优化。

7.性能不会因为giant表的long running query而限制。

15.2.3 MyISAM表存储格式

14.1.1.4 InnoDB表最佳实践

1.指定primary key为最常用的列,若没有可以用自增。

2.为了更好的join性能,外键定义在join字段上,并且保持字段数据类型一致。

3.关闭自动提交,一次性提交可以提高性能。

4.可以使用start transaction commit包裹DML语句组成一个事务。

5.停用lock table语句,InnoDB可以控制多个会话在一个表上读写。

6.启动innodb_file_per_table选项

7.InnoDB压缩特性,row_format=compressed

8.—sql_mode-no_engine_substitution防止创建和指定engine不同的表。

15.2.3.1 静态表特性

静态表也就是固定行长度的表。没有可变长字段。每个行使用固定长度字节。

静态格式是最简单,醉汉全的模式。也是最快的,因为可以快速的定位到某一行。通过行号乘以行的长度。

如果电脑crash但是mysql还在运行。Myisamchk可以简单的发现那个是行的开始和结束,所以通常可以全部回收上除非被部分写入。

静态表的一些特性:

1.char和varchar使用空白填充。Binary和varbinary使用0x00填充。

2.快速

3.cache简单

4.crash重构很简单,因为行位置是固定的。

5.不需要重组除非删除了大量的行需要归还空间给OS。

6.空间比动态表消耗的多。

14.1.1.5 InnoDB表提升

1.可以压缩索引和表。

2.可以创建,删除索引对性能和可用性影响变小。

3.truncate table很快,并且把空间还给OS,而不是保留在系统表空间中。

4.使用DYNAMIC行格式来保存blob和text效率更高。

5.通过information_schema,查看存储引擎内部情况。

6.performance_schema查看存储引擎性能。

7.一些性能提升。

15.2.3.2 动态表特性

动态表是表中包含了可变长的列,或者表使用ROW_FORMATE_DYNAMIC创建。

动态格式比静态的有一点复杂,每个行的头都包含了行的长度。行也可以被碎片化。因为没有连续的空间存放。

你可以使用OPTIMIZE_TABLE或者myisamchk–r来整理表的碎片。如果是固定长度的列也包含了可变长的列。那么如果把变长列移动到其他地方那么固定长度的就不需要整理了。

动态格式有以下特性:

1.所有的字符创都是动态的,除非长度超过4

2.每个行前面都有一个bitmap,用来表示那些列时空字符串或者是0。但是不包含为null的值。如果字符串列的长度是0,或者数值型值是0,空间被删除只是在bitmap上面标记。

3.空间比静态的少。

4.每个行只用要求的空间。如果行变大,会分为很多碎片,比如,你更行了一行行变长了,但是变得碎片。

5.和静态表不同,crash之后要重组,因为行是碎片的,可能会丢失。

6.行长度计算公式如下:

3

  • (number of columns + 7) / 8
  • (number of char columns)
  • (packed size of numericcolumns)
  • (length of strings)
  • (number of NULL columns + 7) / 8

可以使用myisamchk –ed查看连接碎片的连接,这些连接可以通过optimizetable或者myisamchk –r移除。

14.1.1.6 InnoDB作为默认存储引擎测试

从之前版本升级MySQL 5.5之后,还是要检查InnoDB是不是能够正常在被程序使用。使用参数—default-storage-engine=Innodb设置默认存储引擎。设置之后只会影响后来新建的表。测试应用程序可以正常运行,确定读写正常,如果有依赖myisam特性可能会失败。如果想要知道老表在innodb运行的情况,可以使用alter table table_name ENGINE=InnoDB。

或者

CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;

 

15.2.3.3 压缩表特性

标错存储米欧式是只读的,使用myisampack工具生成。亚水表可以使用myisampack解压。

压缩表有以下特性:

1.压缩表空间占用少。

2.每行独立的被压缩,行头被压缩成1到3个字节,根据表中的最大行来决定。每个列压缩也不同。压缩类型:

       a.后缀压缩

       b.前缀压缩

       c.数值列使用bit来代替0

d.如果int型,使用最小的长度来存储值。比如bigint的列,如果值在-128到127 可以使用smallint。

e.如果表只有很少的值,表类型就会被转为ENUM。

f.列可以使用很多上面组合的压缩类型。

3.可以是静态表或者动态表。

14.1.1.7 验证InnoDB是默认存储引擎

检查InnoDB状态:

1.使用SHOW ENGINE查看所有MySQL所有的引擎,查看DEFAULT列。

2.如果没有InnoDB说明编译的时候,没有加InnoDB。

3.如果InnoDB存在但是不可用,回到配置文件或者配置,去掉skip-innodb选项。

15.2.4 MyISAM表问题

14.1.2 检查InnoDB可用

使用SHOW ENGINE查看InnoDB是否存在来决定InnoDB是否可用。

15.2.4.1 MyISAM表损坏

出现以下情况MyISAM表就有可能被损坏:

1.mysqld在写入时被kill

2.异常的电脑关闭。

3.硬件错误。

3.使用外部程序比如myisamchk来修改表,但是服务也同时在修改。

4.软件bug,可能是mysql的或者是MyISAM的

通常表损坏的症状:

1.可能会在select数据的时候出现以下错误。

Incorrect key file for table: '...'. Tryto repair it

2.查询没有完成,返回了一个未完成错误。

你可以通过CHECK TABLE检查MyISAM的表,并且听过REPAIR TABLE修复损坏的MyISAM表。当服务没有启动可以通过myisamchk来修复和检查损坏表。

如果表损坏很频繁,你可以尝试确定为什么会出现。最重要的损坏是服务crash。你可以通过error log的restarted mysqld信息来验证,是否频繁crash。如果不是crash那么就有可能是bug。

14.1.3 关闭InnoDB

如果你不要使用InnoDB:

1.使用—InnoDB=OFF或者—skip-innodb选项禁用innodb存储引擎

2.因为innodb是默认的存储引擎,所以启动报错,需要设置—default-storage-engine和—default-tmp-storage-engine,

3.为了让服务在查询information_schema innodb相关的表是不会奔溃,还需要配置一下参数

loose-innodb-trx=0

loose-innodb-locks=0

loose-innodb-lock-waits=0

loose-innodb-cmp=0

loose-innodb-cmp-per-index=0

loose-innodb-cmp-per-index-reset=0

loose-innodb-cmp-reset=0

loose-innodb-cmpmem=0

loose-innodb-cmpmem-reset=0

loose-innodb-buffer-page=0

loose-innodb-buffer-page-lru=0

loose-innodb-buffer-pool-stats=0

loose-innodb-metrics=0

loose-innodb-ft-default-stopword=0

loose-innodb-ft-inserted=0

loose-innodb-ft-deleted=0

loose-innodb-ft-being-deleted=0

loose-innodb-ft-config=0

loose-innodb-ft-index-cache=0

loose-innodb-ft-index-table=0

loose-innodb-sys-tables=0

loose-innodb-sys-tablestats=0

loose-innodb-sys-indexes=0

loose-innodb-sys-columns=0

loose-innodb-sys-fields=0

loose-innodb-sys-foreign=0

loose-innodb-sys-foreign-cols=0

 

15.2.4.2 表没有被正确关闭

每个MyISAM表包含索引文件,上面有个计数器用来检查表是否被正确关闭。如果在checktable或者myisamchk的时候有以下信息:

clients are using or haven't closed thetable properly

这个信息表示表已经损坏,至少要检查该表。

计数器工作如下:

1.第一次表被MySQL更新,计数器自增1

2.在被更新的时候,计数器不变化。

3.当表的最后一个实例被关闭,计数器自减。

4.当你修复表或者检查表并且发现是没问题的,计数器会被重置为0.

5.为了避免问题,和其他操作交互的时候会检查表,如果计数器是0就不会被减。

也就是说,计数器只有在以下条件会变不正确:

1.MyISAM表被复制没有使用LOCK TABLES并且FLUSH TABLES.

2.MySQL在update和最后关闭之前crash。

3.表使用myisamchk –recover或者myisam –update-state和mysqld同时运行。

4.多个mysqld服务使用了这个表并且一个服务商在执行REPAIRTABLE或者CHECK TABLE,另外一份服务在运行。CHECKTABLE是安全的,你可以冲其他服务上得到警告。但是REPAIR TABLE应该避免因为当一个服务覆盖了数据文件,另外一个服务是不知道的。

一般不使用多个服务共享数据文件。

14.2 InnoDB概念和体系结构

15.3 MEMORY存储引擎

MEMORY存储引擎创建特别目的的表,表内容是存放在内存中的。用户只能用来做临时的工作区或者只读cache。MEMORY存储引擎特性。

Storage limits

RAM

Transactions

No

Locking granularity

Table

MVCC

No

Geospatial data type support

No

Geospatial indexing support

No

B-tree indexes

Yes

T-tree indexes

No

Hash indexes

Yes

Full-text search indexes

No

Clustered indexes

No

Data caches

N/A

Index caches

N/A

Compressed data

No

Encrypted data[a]

Yes

Cluster database support

No

Replication support[b]

Yes

Foreign key support

No

Backup / point-in-time recovery[c]

Yes

Query cache support

Yes

Update statistics for data dictionary

Yes

[a] Implemented in the server (via encryption functions), rather than in the storage engine.

[b] Implemented in the server, rather than in the storage engine.

[c] Implemented in the server, rather than in the storage engine.

 

什么时候使用MEMORY或者MySQLCluster。开发想要部署使用了MEMORY的应用程序,高可用或者频繁更新数据要考虑MySQLCluster是不是最好的选择。通常使用MEMORY涉及到以下特性:

1.操作相关的短暂的,非重要数据的管理或者cache。当MySQL服务夯住或者重启,MEMORY表的数据就会丢失。

2.内存存储访问快速,并且延迟低。数据可以填充到内存不会导致操作系统swap到虚拟内存。

3.只读或者读多的数据访问模式。

MySQL Cluster提供和MEMORY相似的功能提高性能,提供额外的特性MEMORY不支持的:

1.行级锁并且多线程操作,client减低争用。

2.可扩展性,甚至语句和写入混合。

3.数据持久性的后台操作

4.shared-nothing的结构,多host操作不会有单点错误。

5.自动数据分布,应用程序不需要参与用户sharding或者分区解决方案。

6.支持可变长字段,MEMORY不支持。

14.2.1 Mysql和ACID模型

ACID模式是数据库设计的标准的集合。

A: atomicity原子性

C:consistency 一致性

I:isolation 隔离性

D:durability 持久性

 

原子性

原子性主要由InnoDB事务来保持

一致性

一致性方面 ACID主要设计InnoDB内部处理来保护数据。

1.InnoDB双写 buffer

2.InnoDB灾难恢复

隔离性

ACID的隔离性的模型,主要涉及InnoDB事务在指定隔离级别上的执行。

1.自动提交设置

2.set isolation level隔离级别

3.低级InnoDB锁,可以通过INFORMATION_SCHEMA查看。

持久性

ACID的持久性,涉及mysql和指定的硬件的交互,

1.innodb 双写

2.innodb_flush_log_at_trx_commit配置参数

3.sync_binlog 配置参数

4.innodb_file_per_table配置参数

5.存储设备的,write buffer

6.存储设备battery-backed cache

7.运行MySQL的操作系统,特别是支持fsync系统调用的

8.UPS支持的环境

9.备份策略

10.硬件设备和网络连接

15.3.1 性能特点

MEMORY性能受限于单线程执行结果和更新的时候表锁。当实例负荷增加,语句混合了写入会限制可扩展性。

尽管MEMORY表,可能没有InnoDB速度快,对于繁忙的系统。特殊情况下,更新的表级锁可能会影响其他线程对MEMORY表的操作。

根据查询的不同,你可以创建索引,比如默认的hash数据结构,或者b树结构。

14.2.2 InnoDB事务模式和锁

在InnoDB事务模式,目标是把高性能的多版本的数据和2阶段锁定结合。InnoDB锁定在行级别并且查询默认以nolocking一致性执行。和Oracle一样。Lock信息在InnoDB是空间高效实用的。锁不需要创建。也不会照成内存压力。

 

InnoDB所有用户行为都在事务内。若打开了自动提交,每个SQL都是一个事务。默认会话都是自动提交的,若出错会根据错误回滚。自动提交的会话,可以通过start transaction 或者begin语句,以commit或者rollback结束,启动一个事务。通过set autocommit=0取消自动提交。

 

默认InnoDB的隔离级别是REPEATABLE READ,InnoDB提供了4个隔离级别,READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ和SERIALIZABLE。

 

用户可以通过SET TRANSACTION语句设置隔离级别,使用—transaction-isolation选项来设置默认隔离级别。

 

对于行级锁,InnoDB通常使用next-key锁定。也就是说除了索引记录,InnoDB还可以lock页内空隙,不让其他会话插入数据。Next-key lock是锁定了index record和它之前的gap。

 

15.3.2 MEMORY表的物理特性

MEMORY存储引擎都有个相关的磁盘文件,用来保存表定义。表名.frm。

MEMORY表有以下几个特性:

1.MEMORY表使用小的block,表使用动态hash存储,没有一处或者额外的key空间。删除行把空间放到一个链表。当你插入的时候会重新使用。MEMORY表在插入和删除混合没有什么问题。

2.内存表固定长度。可变长度类型比如varchar也以固定长度保存。

3.内存表不能包含BLOB,TEXT裂隙

4.MEMORY支持AUTO_INCREMENT列

5.没有零食内存表可以被所有客户端共享,可其他临时表一样。

14.2.3 InnoDB锁定模式

InnoDB实现标准的行锁有2中强度类型,S锁和X锁。粒度类型有3类,record锁,gap锁,next-key锁。

15.2.3 MEMORY的DDL操作

创建MEMORY表只要指定ENGINE=MEMORY。表被存放在内存,默认使用hash所有,hash索引可以让单个值更快的被查找,可以创建零食表。当服务关闭,所有MEMORY中的数据就会丢失。表会一直存在,因为表定义文件是存放在磁盘的。

MEMORY表的由系统变量,max_heap_table_size决定,默认是16MB,强制不同的大小,可以修改这个变量。这个值可以在CREATETABLE,ALTER TABLE,TRUNCATE TABLE上被定义。服务重启也可以设置MEMORY表的最大大小到max_heap_table_size。

14.2.3.1 意向锁

InnoDB可以支持多粒度锁定允许,就引入了意向锁。

IS:事务T意向在表上使用S锁。

IX:事务T以上在表上使用X锁。

意向锁协议:

1.为了获取在行上获取S锁,就必须在表上获取IS锁。

2.为了获取行上的X锁,必须在表上限获取IX锁。

Lock兼容表

 

X

IX

S

IS

X

Conflict

Conflict

Conflict

Conflict

IX

Conflict

Compatible

Conflict

Compatible

S

Conflict

Conflict

Compatible

Compatible

IS

Conflict

Compatible

Compatible

Compatible

15.2.4 索引

MEMORY存储引擎支持hash和b树索引。你可以通过using子句来设置:

CREATE TABLE lookup
    (id INT, INDEX USING HASH (id))
    ENGINE = MEMORY;
CREATE TABLE lookup
    (id INT, INDEX USING BTREE (id))
    ENGINE = MEMORY;

MEMORY表最多可以有64个索引,每个索引16个列。最多key长度为3072个字节。

如果MEMORY表hash索引的列包含在很多索引中,yodate表影响字段的值会影响性能。性能影响和被几个索引引用有关。你可以使用b树索引来避免问题。

MEMORY表可以是非唯一的key。

索引列的值可以是null

14.2.3.2 死锁例子

15.2.5 用户创建和临时表

MEMORY表内容存储在内存中,这个是MEMORY表共享内部临时表的目的。有2种表类型不同,MEMORY表的目的不是为了存数据方便,但是内部临时表却是:

1.内部临时表变的太大,服务自动会转化为磁盘表。

2.用户创建的MEMORY表不会存到磁盘。

14.2.4 一致性无锁读

一致性读的意思是,InnoDB使用多版本来生产查询结果的快照。查询只能看到事务启动时,已经提交完的事务修改。有个异常就是查询可以看到事务内前面语句的修改内容。这个异常会导致一个情况,在其他事务刚好也更新了同样的表,这种情况下,你看到的表状态重来就没出现在数据库过。

 

如果隔离级别是所有的一致性读,从第一个读开始。

使用READ COMMIT隔离级别,每个事务中的一致性读来至于自己刷新的快照。

不管是READ COMMIT,REPEATABLE READ都默认使用一致性读。一致性读不会锁定表访问,所以其他会话可以同时更新这些表。

 

假设你默认是REPEATABLE READ隔离级别。当查询数据是会给一个时间点,当行被其他事务删除,在查询不会发现行被删除。

Note

      快照在事务内有效。尽管查不到数据,但是update,delete还是可以修改这些数据库的。

 

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'; -- Returns 0: no rows match.

DELETE FROM t1 WHERE c1 = 'xyz'; -- Deletes several rows recently committed by other transaction.

 

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc'; -- Returns 0: no rows match.

UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc'; -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba'; -- Returns 10: this txn can now see the rows it just updated.

 

如果要推进快照点,可以执行事务提交,再执行另一个select或者START TRANSACTION WITH CONSISTENT SNAPSHOT。

 

如果想要看最新的数据库状态,可以使用read committed,或者锁定读。

SELECT * FROM t LOCK IN SHARE MODE;

使用READ COMMITTED隔离级别,事务里面的每次一致性读都是独自己刷新的快照。使用LOCK IN SHARE MODE,发生读锁。

一致性读不能覆盖DDL语句,insert into select ,update…(select),create table select都不能指定for update或者 lock in shared mode。

15.2.6 导入数据

为了MEMORY表,当服务启动你可以使用—init-file。你也可以使用INSERT INTO … SELECT或者LOADDATA INFILE到文件冲持久性表里面导入数据。

14.2.5 锁读

在事务里select不会被保护,其他事务可以同时修改查询的值。Innodb提供额外的锁读,提供安全性:

1.Select … lock in share mode设置

2.select … for update

使用以上标记的锁,到事务完成时才能释放。

15.2.7 MEMORY表和复制

当服务的MEMORY表在shutdown的时候就丢失。如果服务是master,slave不会在意这些表是不是变空了,所以可以从slave中看到以前的数据。为了同步master和slave,当master使用memory表,先使用delete语句删除所有记录,这样slave也会删除所有记录。Slave在master重启和第一次使用表之间还是会有过期数据。因此为了避免可以在master上使用—init-file选项。

14.2.6 InnoDB Record,Gap,Next-key锁

InnoDB有几类锁:record lock,Gap lock,Next-key lock

15.2.8 管理内存使用

服务需要有足够的内存来维护所有MEMORY表。

内存并不会因为删除个别的行而使用减少,内存只有在整个表删除的时候才会释放。删除行的内存会被新增的行重用。为了缩放所有的内存你可以执行delete或者truncate table来删除所有的行。或者直接drop table。为了释放删除行的内存,可以通过alter table engine=memory来重建表。

每行的内存使用公式如下:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)

+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) *2)

+ ALIGN(length_of_row+1, sizeof(char*))

ALIGN()表示向上取证,因为sizeof(char*) 在32位是4个字节,在64位是8个字节。

Max_heap_table_size表示最大的MEMORY表的大小。在创建表的时候。可以设置session的值,如,以下脚本创建了2个MEMEORY表,一个是1MB一个是2MB。

mysql> SET max_heap_table_size = 1024*1024;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;

Query OK, 0 rows affected (0.01 sec)

mysql> SET max_heap_table_size = 1024*1024*2;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;

Query OK, 0 rows affected (0.00 sec)

如果服务重启后,2个表都是从全局的max_heap_table_size里面获取最大值。

你也可以在创建MEMORY的表上设置max_rows。

14.2.6.1 Record Lock

Record Lock锁定Index记录,不管有没有定义索引。

15.4 CSV存储引擎

14.2.6.2 Next-Key Lock

默认,InnoDB使用REPEATABLE READ隔离级别并且innodb_locks_unsafe_for_binlog不可用。Next-key lock查询可以防止幻读出现。

 

Next-key是index-row和gap的组合。Next-key锁定record和,record之前的gap。若会话共享,独占锁定了R,那么另外一个就不能再R之前插入数据。

15.4.1 CSV表的检查和修复

CSV存储引擎支持CHECK,REPAIR语句来检查和修复损坏的CSV表。

当运行CHECK语句,CSV文件会检查字段的分隔符是否正确。发现一个不可用的行就会报错。检查表如下:

mysql> check table csvtest;

+--------------+-------+----------+----------+

|Table        | Op    |Msg_type | Msg_text |

+--------------+-------+----------+----------+

| test.csvtest | check |status   | OK       |

+--------------+-------+----------+----------+

1 row in set (0.00 sec)

如果检查错误,表会被标记为crashed。一旦表被标记为损坏,当你check或者执行select 的时候会自动修复。相关的错误状态和形状带会在运行check的时候被显示:

mysql> check table csvtest;

+--------------+-------+----------+----------------------------+

|Table        | Op    |Msg_type |Msg_text                  |

+--------------+-------+----------+----------------------------+

| test.csvtest | check | warning  |Table is marked as crashed |

| test.csvtest | check |status   |OK                        |

+--------------+-------+----------+----------------------------+

2 rows in set (0.08 sec)

为了修复表可以运行REPAIR,这个副本尽量复制所有存在的数据,并且替换现在的CSV文件。损坏的记录就会被丢失。

14.2.6.3 Gap Lock

Gap lock不会使用在使用唯一索引查询上。

有一种gap lock类型被称为插入意向gap lock,由Insert操作设置。这个锁表示尝试插入。多事务插入到同一个index gap就会被堵塞。

 

使用read commit或者 innodb_locks_unsafe_for_binlog来取消gap lock。

15.4.2 CSV的限制

CSV存储引擎不能定义索引。

分区也不支持。

使用CSV创建的表必须要有notnull属性。如果用了之前的MySQL创建的nullable列可以继续使用。

14.2.7 使用Next-key锁定避免幻影

幻影问题,事务内相同查询在不同时间运行,返回的数据不一样。

如:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

如果不加gap锁,就可以插入101,那么再次使用查询,就会出现幻影行。

为了避免幻影问题,使用next-key lock锁定index record和gap。

15.5 ARCHIVE存储引擎

ARCHIVE存储引擎是特殊目的的,用来保存大量的非索引数据。

ARCHIVE特性如下:

Storage limits

None

Transactions

No

Locking granularity

Row

MVCC

No

Geospatial data type support

Yes

Geospatial indexing support

No

B-tree indexes

No

T-tree indexes

No

Hash indexes

No

Full-text search indexes

No

Clustered indexes

No

Data caches

No

Index caches

No

Compressed data

Yes

Encrypted data[a]

Yes

Cluster database support

No

Replication support[b]

Yes

Foreign key support

No

Backup / point-in-time recovery[c]

Yes

Query cache support

Yes

Update statistics for data dictionary

Yes

[a] Implemented in the server (via encryption functions), rather than in the storage engine.

[b] Implemented in the server, rather than in the storage engine.

[c] Implemented in the server, rather than in the storage engine.

ARCHIVE存储引擎包含MySQL binary发布中。如果是源代码安装,为了启动ARCHIVE存储引擎,可以使用CMake–DWITH_ARCHIVE_STORAGE_ENGINE选项。

可以在storage/archive查看ARCHIVE源代码。

当创建一个ARCHIVE表,服务创建一个表的定义在数据库目录。文件名为表名.frm。存储引擎创建其他文件,所有的都是以表名开头。数据文件的扩展名是ARZ,ARN文件会在优化操作的时候会出现。

ARCHIVE存储引擎,包括insert,select,但是没有delete,replace,update操作。使用order by排序,BLOB字段和其他所有基础类型但是不能排序spatial数据类型。

ARCHIVE存储引擎自持AUTO_INCREMENT类型属性。自增类型可以是唯一或者不唯一的索引。

试图在其他字段上创建创建索引都会报错。ARCHIVE存储引擎也支持对自增字段在创建表的时候设置初始化值。或者reset已经有的自增字段。

ARCHIVE不支持插入到自增字段小于当前字段最大值。如果尝试,就报键重复错误。

如果不需要BLOB字段在读取的时候会被忽略掉。

存储:行被压缩插入,ARCHIVE存储引擎使用zlib无损压缩方式。你可以使用OPTIMIZE TABLE来分析白哦并且打包到更小的格式。存储引擎也支持CHECK TABLE 。有一些被使用的类型:

1.INSERT语句只是把行存到压缩buffer中,buffer根据需要被刷新。Insert到buffer被锁保护。Select会强制buffer刷新。

2.bulk insert只有在完成后才可见,除非其他插入同时发生,可以部分可见。Select不会导致bulk insert刷新,除非正常的插入也同时发生。

获取:为了获取,行被解压。没有行cache。Select操作在压缩表上扫描:当select发生,查找有多上行会被读取。Select执行读一致性操作。大量在插入的时候有select语句会恶化,除非只有bulk或者延迟插入被使用。为了归档更好的压缩,你可以使用OPTIMIZETABLE或者REPAIR TABLE。Show tablestatus可以查看archive表的行数。

14.2.8 不同语句的锁定设置

锁读,update,delete,当扫描是,会对所有的扫描到的index record上锁。并不会理睬是不是在where条件之外。InnoDB只知道要扫描的Index Range。

InnoDB指定lock类型:

1.select from 一致性读不加锁,除非在SERIALIZABLE隔离级别

2.select from lock in share mode 共享锁读,next-key lock。

3.select from for update 排他锁读

4.update where排他的next-key lock

5.delte from where排他next-key lock

6.insert在插入的行上设置排他锁,是record锁,不是next-key锁。

 

如果发生重复键错误,那么index record会设置共享锁,当多个事务同时获得共享锁,就有可能会出现死锁。

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations in order:

Session 1:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

当第一个在会话1执行,那么会话2和会话3会报主键冲突,会共享锁。当会话1释放。会话2,会话3同时去获取排他锁。就会导致2,3死锁。

 

7.Insert on duplicate key upate,和insert不同,当出现重复键错误,或使用x锁,不是s锁。

8.replace当在没有冲突的情况下,和insert一样。如果有冲突获取next-key x锁,替换行。

39insert into t select * from s where,会在T中设置record x锁。若隔离级别是read committed或者设置了innodb_locks_unsafe_for_binlog,并且不是SERIALIZABLE否则innodb设置共享next-key锁在S表上。

 

Create table select,insert select都会申请共享next-key锁。如果表在结构replace into t selct from s where 或者update t where col in(select from s)innodb都会在s上设置共享next-key锁。

10.在初始化auto_increament时,会在相关的索引上加上x锁。在访问自增counter时,使用auto-inc锁,语句执行完就释放。

11.如果表上定义了外键,任何写入操作都需要去检查行,需要设置共享行锁查看。

12.lock tables设置表锁

Innodb自动死锁诊断,不会发现设计到table locks的死锁。因为table locks是mysql层的不知道innodb的行级别锁。

15.6 BLACKHOLE 存储引擎

BLACKHOLE存储引擎就是black hole,接受数据,但是不保存。获取总是为空。

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;

Query OK, 0 rows affected (0.03 sec)

 

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0 Warnings: 0

 

mysql> SELECT * FROM test;

Empty set (0.00 sec)

如果要使用BLACKHOLE存储引擎,在编译源代码的时候,调用CMAKE–DWITH_BLACKHOLE_STORAGE_ENGINE。参数。

为了检查BLACKHOLE的源代码,可以在sql目录先查看发布的源代码。

当你创建BLACKHOLE表,服务创建一个表类型文件。表名.frm没有其他的文件。

BLACKHOLE存储引擎支持所有类型的索引。你可以在表定义的时候包含索引定义。

你可以使用show engine查看BLACKHOLE存储引擎是否可用。

插入到BLACKHOLE表,不保存任何数据,但是binlog会生成,SQL语句会被发送到slave上。

假设你的程序需要slave段的过滤,如果传输所有binlog数据传输量太大。这个时候可以使用假的master host,默认存储引擎是BLACKHOLE,描述如下:

必赢棋牌官网 1

Master写入bin log。假的mysqld进程就是slave,使用根据replicae-do-*,replicate-ignore-*规则写入一个新的过滤的binary log。被过滤的binary log提供给slave。

假进程并不提供任何数据存储,所以进程负荷几乎没有。

BLACKHOLE的INSERT触发器是一个例外,因为BLACKHOLE不保存任何数据,UPDATE和DELETE触发器没有被激活:FOR EACH ROW定义不会起作用因为没有数据。

其他可能使用BLACKHOLE的场景:

1.验证dump文件格式。

2.测量binary log 的负荷,通过使用BLACKHOLE不启动binary log的进行比较。

3.BLACKHOLE本质是no-op存储引擎,所以可以用来查找和存储引擎无关的性能问题。

BLACKHOLE存储引擎是事务的,提交事务被雪茹到binary log并且rollback并不会写入。

Blackhole存储引擎和自增字段

Blackhole是no-op引擎。任何表上的操作都没有响应。引擎并不会自动增加自增字段的值,也不会保留自增字段的状态。

考虑一下复制场景:

1.在master上,有一个blackhole表有一个自增字段并且是主键。

2.slave上有个一样表名的myisam表

3.插入操作执行没有显示的分配自增值。

在这个场景下复制就会报错,重复键错误。

基于语句的复制,自增值在context事件都是一样的。复制会因此报错。基于行的复制,每次插入的值都是一样的。也会报重复键错误。

列过滤

当使用基于行的binlog,slave少了最后一列是被支持的。具体可以看:

Section 17.4.1.10, “Replication with Differing TableDefinitions on Master and Slave”.

过滤工作在slave段,列在过滤之前就被复制到slave。至少有2种情况需要过滤:

1.数据机密,所以slave不能访问。

2.master有很多slave,在发送前过滤可以减少网络负荷。

Master列过滤可以使用BLACKHOLE存储引擎。和master表的过滤类似,使用BLACKHOLE引擎和—replicate-do-table或者—replicate-ignore-table。

Master的部署:

CREATE TABLE t1 (public_col_1, ..., public_col_N,

                secret_col_1, ..., secret_col_M) ENGINE=MyISAM;

信任slave部署:

CREATE TABLE t1 (public_col_1, ...,public_col_N) ENGINE=BLACKHOLE;

不信任slave部署:

CREATE TABLE t1 (public_col_1, ...,public_col_N) ENGINE=MyISAM;

14.2.9 事务提交和回滚的影响

默认MySQL启动会话,以自动提交方式提交事务。如果语句返回错误,会根据错误处理commit和rollback。

如果会话取消了自动提交,没有显示提交,最终会回滚。一些语句会影响事务提交,在commit执行的话,会导致影响事务提交。

15.7 MERGE存储引擎

MERGE存储引擎也就是MRG_MyISAM存储引擎,可以把一组一致的MyISAM表当成一个表。一致的意思是表结构和索引信息都一样。如果列顺序不同也不行。替代MERGE的方法是使用分区表,把单个表的不同分区存放在不同的文件上。分区可以让一些操作更加高效,不会受MyISAM存储引擎限制。

当你创建了MERGE表,MySQL创建2个文件在磁盘上。.frm和.mrg文件。

你可以使用select,delete,update,insert在MERGE表中。你必须有在MyISAM表上的select,delete,update权限。

使用DROP TABLE,只会删除MERGE设置,并不会影响底层表。

为了创建MERGE你必须指定,UNION操作说明那些MyISAM表被使用。你可以使用INSERT_METHOD选项来控制如何插入到MERGE表。使用FIRST或者LAST来决定插入到第一个表还是最后一个表。如果没有指定INSERT_METHOD或者指定了NO,插入MERGE不被允许操作,会报错。

创建MERGE的例子:

mysql> CREATE TABLE t1 (

   ->    a INT NOTNULL AUTO_INCREMENT PRIMARY KEY,

   ->    messageCHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE t2 (

   ->    a INT NOTNULL AUTO_INCREMENT PRIMARY KEY,

   ->    messageCHAR(20)) ENGINE=MyISAM;

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (

   ->    a INT NOTNULL AUTO_INCREMENT,

   ->    messageCHAR(20), INDEX(a))

   ->    ENGINE=MERGEUNION=(t1,t2) INSERT_METHOD=LAST;

在底层MyISAM表上,指定了主键,但是没有再MERGE表上,因为MERGE不能强制底层表的唯一性。

创建之后,你可以使用查询MERGE表:

重新映射MERGE表的底层表:

1.删除重新创建。

2.使用alter table tbl_name union=(…)来修改。也可以使用alter table tbl_name union()来删除所有底层表。这样表就是空的,插入就会失败,因为没有底层表。

低沉表定义和索引必须和MERGE一直。当表被打开的时候进行一致性检查。如果任意表一致性检查失败,触发打开表的操作就会失败。也就是说当MERGE被访问的时候修改MERGE内表的定义就会导致访问失败。一致性检查会应用到所有表:

1.底层表和MERGE表必须列个数一样

2.底层表和MERGE表列顺序必须一样。

3.列类型必须一致。

4.类长度必须一致

5.列可以为null

6.底层表索引个数至少和MERGE一样多。底层表可以更多但是不能少于MERGE表。

每个索引必须满足一下条件:

1.索引类型必须一样。

2.index part必须一样。

3.对于每个索引,index part一样,类型一样,语言一样,检查indexpart是否可以为null。

如果MERGE不能被打开或者使用因为底层表的问题,CHECKTABLE检查什么表的问题。

14.2.10 死锁发现和回滚

InnoDB自动诊断发现事务死锁回滚事务或者打破事务回滚。

如果配置innodb_table_locks=1默认并且autocommit=0 InnoDB会认识table locks否则不认识。

在InnoDB中一个事务rollback,所有的锁都会被释放。然而对于单个语句因为出错rollback,可能锁会被保留,因为InnoDB不知道锁是属于哪个语句的。

如果select调用了用户函数在事务中,语句出错,语句被回滚。

15.7.1 MERGE表的优缺点

MERGE表优点:

1.简单管理日志表,比如你可以把不同的月份的数据放在不同的表上,使用myisampacl压缩。然后创建MERGE来使用它们。

2.获取更快的速度。你可以根据一些关键点拆分大的只读表,然后分开的多个表中,并且位于不同的磁盘。MERGE表结构可以比使用单个大表速度更快。

3.执行查询更加高效。如果你准确的知道你要获取什么,你可以在底层表执行查询,然后多其他表使用merge。在一组表上可以有很多个merge。

4.修复更加有效。修复小表比修复单个达标速度更快。

5.及时的把表映射到一个。MERGE表不需要维护所有因为索引属于个别的表。MERGE可以快速的创建或者重新映射。

6.如果有一组表,你先要创建一个大表,现在可以使用MERGE表来代替。

7.可以超过系统限制的文件大小,因为MERGE由多个表组成。

8.你可以创建一个别名,通过映射到MERGE表

MERGE缺点:

1.只能使用MyISAM作为底层表。

2.一些MyISAM表的 特性不可用。比如不能创建全文索引。

3.如果MERGE表不是临时的,所有低沉MyISAM表必须是非临时的。如果MERGE表是临时的,底层表可以是临时的也可以不是临时的。

4.MERGE表比MyISAM的文件描述多。如果10个客户端使用MERGE表映射了10个MyISAM表。服务使用(10*10)+10个文件描述。

5.索引读取很慢。当你读取索引,MERGE存储引擎需要在所有底层表上执行一个读来检查那个最匹配给定的index值。为了读取下一个值,MERGE存储引擎需要查询read buffer来查找下一个值。MERGE索引在eq_ref上很慢,但是在ref上并不慢。

14.2.11 如何处理死锁

死锁是事务数据库的典型问题,除非经常发生,不然不是问题。在写程序的时候要进行重试。

InnoDB使用自动行锁。在单行写入也可能发生死锁,因为不是真的原子性。他们自动在index record上设置锁。

可以使用如下方法来减少死锁:

1.                  任何时候,可以使用show engine innodb status来确认最近死锁。

2.                  使用innodb_print_all_deadlocks,把错误信息print到error log上。

3.                  发生错误的时候要重试

4.                  完成修改后,立马提交

5.                  如果使用锁读,使用比较低的隔离级别,比如read committed

6.                  当修改不同的表,或者不同的行集,使用一致的顺序。

7.                  增加合适的索引。

8.                  减少锁,如果可以使用老的快照,就不要使用锁读。

9.                  如果还是没有缓减,使用串行事务,表级锁。

10.              另外一个方式是创建一个辅助的信号量表。事务update先要访问信号量表。

15.7.2 MERGE表问题

1.直到MySQL 5.1.23 为止都可以使用非临时的MyISAM创建临时MERGE表。

2.如果使用ALTER TABLE来修改MERGE到另外一个存储引擎,底层表消失,底层表的行被复制到alter table表上,使用了指定存储引擎。

3.INSERT_METHOD表选项表示那个MyISAM表用于MERGE的insert into。如果底层表使用了自增字段,insertinto MERGE表不会有反应

4.MERGE表不能维护唯一约束。

5.因为MERGE存储引擎不能强制唯一性约束,REPLIACE就不可能和预期的一样运行。主要有2个方面:

       a.REPLACE只有在底层表的写入上才能发现唯一性冲突。

       b.如果REPLACE发现唯一性冲突,只能改变写入的底层表。

6.MERGE表不支持分区。

7.你不能使用ANALYZE TABLE,REPAIRTABLE,OPTIMIZE TABLE,ALTER TABLE,DROP TABLE,DELETE没有where子句的。

8.DROP table在MERGE 表上在windows上不起作用因为MERGE表被映射在底层表上。Windows不允许打开的文件被删除。所以你先要flush 所有merge表。

9.当文芳表的时候会检查MERGE和MyISAM表的定义。

10.MERGE表的索引顺序要和底层表一样。

11.如果发生错误比如Error 1017不能查看文件。通常表示底层表没有使用MyISAM存储引擎。

12.MERGE表最大的行数是2^64,就算映射的表在多也无法比这个行数多。

13.底层MyISAM表和MERGE表不同就会报错。

14.当LOCK tables的时候,不能修改unionlist。

15.不能通过create select创建MERGE表。

16.在一些情况下不同的PACK_KEYS表选项会导致一些想不到的问题。

14.2.12 InnoDB多版本

InnoDB是多版本存储引擎,保存了老的修改的行。支持并发和回滚。这些数据被保存在rollback segment。InnoDB使用在rollback segment的信息来执行undo。

15.8 FEDERATED存储引擎

FEDERATED存储引擎可以让你访问远程数据不需要通过复制或者集群技术。查询本地FEDERATED表自动从远程获取数据。本地不保存数据。

为了有FEDERATED存储引擎,可以在编译的时候CMake–DWITH_FEDERATED_STORAGE_ENGINE选项。

FEDERATED存储引擎默认不启动,可以通过-federated选项来启动。存储引擎源代码在storage/federated目录下。

14.2.12.1 多版本内部细节

内部,InnoDB为每个行存储增加了3个列。6个字节的DB_TRX_ID表示最后写入的事务ID,7字节DB_ROLL_PTR roll指针,roll指针指向rollback segment中undo log record。6字节的DB_ROW_ID,包含rowid,rowid是单调递增的。

 

undo log在rollback segment被分为insert,和update undo log。insert undo log只在回滚时需要commit才能释放。update undo logs也可以用在一致性读上,只有在事务没有使用的时候才会消失。

15.8.1 FEDERATED存储引擎概述

当你创建表使用了标准的存储引擎(如,MyISAM,CSV,Innodb)表由表定义和相关数据组成。当你创建FEDERATED表,表定义是一样的,但是数据存储是在远程的。

FEDERATED表由以下2个要素组成:

1.远程服务的数据库表,包含了表定义,和相关数据。

2.本地服务的表,表定义和远程服务的要一样。表定义保存在frm文件,但是没有数据文件,只是有一个连接字符串指向远程表。

当在FEDERATED表执行查询和语句,草错可以正常执行,insert,update,delete都会被发送到远程去执行,或者从远程返回行。

FEDERATED结构如下:

必赢棋牌官网 2

当客户端执行SQL语句引用了FEDERATED表,客户端和服务间的信息流如下:

1.存储引擎查看,FEDERATED表相关的每个列,构建合适的SQL发送到远程表。

2.语句通过MySQL Client API发送到远程

3.远程服务处理语句并且本地服务获取语句处理的结果。

4.如果语句生成的结果,每个列都被转化为FEDERATED要的内部存储过程格式。并且结果发送到语句发生的最初地方。

本地服务和远程服务通过MySQL Client API交互使用mysql_real_query()发送语句,mysql_store_result()来获取结果,使用mysql_fetch_row()一次获取一行。

14.2.12.2 Rollback Segment管理指导

InnoDB不能从update undo log中放数据,回滚段会变得很大。undo log记录在回滚段是一般比insert和update的行小。

在InnoDB多版本结构,一个行不会在删除后,马上物理上删除。只有当取消update undo log记录后才会在物理上删除。删除操作被称为清空,通常清空的顺序和sql顺序一样。

如果insert和delete行。

如果你插入和删除行在小批处理。清理现场会滞后会越来越大导致变慢。这时,可以使用innodb_max_purge_lag参数来调整清理操作。

15.8.2 如何创建FEDERATED表

你可以使用以下步骤创建FEDERATED表:

1.在远程服务商创建表,如果表已经存在,使用show createtable获取语句。

2.在本地创建表定义,添加链接信息到远程。

创建本地表关联到远程,有2个可用的选项。要不创建一个本地表并且制定连接字符串,或者使用已经存在的连接,通过createserver创建的连接。

14.2.13 InnoDB表和索引结构

15.8.2.1 使用CONNECTION创建FEDERATED

使用第一种方法创建,你必须指定CONNECTION选项,例如:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

CONNECTION字符串包含了连接到远程服务的信息包括存储数据的物理表。连接支付穿指定了服务名,登录凭据,端口,数据库/表名。格式如下:

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

schema:表示可以识别的协议,mysql是唯一支持的协议。

User_name:连接的用户名,用户名必须在remote上被创建,并且有正确的权限。

Password:可选用户名对应的密码

Host_name:远程服务的主机名或者ip地址

Port_num:可选,远程服务的端口

Db_name:数据库名

Tbl_name:远程的表名。

如:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

14.2.13.1 .frm文件在innodb表的角色

MySQL以.frm文件方式,把数据目录保存在数据库目录中。InnoDB也会把表的元数据信息存放在表空间中。当MySQL drop表或者数据库,会删除一个或者多个.frm文件。但也不能通过移动.frm文件来移动表。

15.8.2.2 创建FEDERATED表使用CREATESERVER

如果你创建一批FEDERATED表,你可以先使用createserver创建,如:

CREATE SERVER server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

如:

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');

创建FEDERATED表的使用,如下:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';

这个CONNECTION中包含了fedlink就是之前创建的server。

Description

CONNECTION string

CREATE SERVER option

mysql.servers column

Connection scheme

scheme

wrapper_name

Wrapper

Remote user

user_name

USER

Username

Remote password

password

PASSWORD

Password

Remote host

host_name

HOST

Host

Remote port

port_num

PORT

Port

Remote database

db_name

DATABASE

Db

14.2.13.2 聚集和secondary索引

每个InnoDB表有一个特别的聚集索引,聚集索引和primary key同义。

1.                  当你定义了primary key,InnoDB会作为聚集索引。

2.                  如果没有定义primary key,MySQL会使用unique,并且非null的列作为聚集索引

3.                  如果表没有primary key和unique索引,InnoDB自动生成一个隐藏聚集索引聚合列和rowid。

15.8.3 FEDERATED存储引擎注意点

在使用FEDERATED的时候要注意:

1.FEDERATED表可能被复制到其他slave,但是你必须保证slave服务的可以使用用户密码访问远程服务,根据connection的定义。

后面是FEDERATED表支持的或不支持的特性:

1.远程服务必须是MySQL

2.远程表在访问前必须存在

3.很有可能一个FEDERATED表指向到另外一个FEDERATED表,但是注意不能创建出一个循环。

4.通常意义上FEDERATED表不支持索引,因为访问表数据是在远程被处理的,司机远程表是使用索引的。也就是说一个查询不能使用任何索引并且要表扫描,服务从远程获取所有记录并且过滤。不管使用了任何where,limit子句,这些子句都是在本地被使用的。

如果使用索引失败,或导致性能问题和网络负荷问题。另外会导致内存短缺。

5.注意当创建FEDERATED表因为和其他表一样的索引定义可能不被支持。比如在varchar,text,blob上创建前缀索引就会失败。

如:

CREATE TABLE `T1`(`A`VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED
  CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';

6.内部实现, select,insert,update,但是没有实现handle

7.FEDERATED存储引擎支持,select,insert,update,delete,truncate table和索引。但是不支持alter table或者任何其他DDL语句除了drop table。

8.FEDERATED使用insert … on duplicatekey update语句,但是如果出现重复键错误,语句错误。

9.FEDERATED表在bulk insert的时候比其他表类型要慢,因为每个查询行都被分为独立的insert,insert到FEDERATED。

10.不支持事务。

11.FEDERATED执行bulk-insert处理多行被批量发送到远程表,性能得到提高并且远程的执行性能提高。如果远程是事务的,可以让远程引擎在错误发生的时候回滚:

       a.行的大小不能超过服务器间的包大小,如果超过,就会被分为多个包。并且回滚就会出错。
       b.bulk insert不支持insert… on duplicate key update。

12.FEDERATED无法知道远程表是不是被修改。因为文件必须和数据文件一样运行不能被其他服务写入。如果任何修改远程数据库本地表的一致性就可能会被破坏。

13.当使用CONNECTION连接的时候,不能在密码中使用@。但是在create server中可以使用。

14.insert_id和timestamp不能传播到data provider上。

15.任何drop table语句在FEDERATED表上只会删除本地表,不会删除远程表。

16.FEDERATED表不能使用query cache。

17.FEDERATED不支持用户定义分区。

14.2.13.2.1 聚集索引加快查询速度

如果通过访问聚集索引访问一行很快是因为开销索引查询可以直接访问到有所有数据的page。如果表很大,通过聚集索引访问会减少I/O。

15.8.4 FEDERATED存储引擎资源

其他的资源: .

14.2.13.2.2 聚集索引和secondary关联

除了聚集索引外,其他都是secondary索引,InnoDB secondary 索引,都包含了primary key的列。如果primary key很长,secondary就需要更多的空间,所以建议primary key比较短。

15.9 EXAMPLE存储引擎

EXAMPLE存储引擎是为了给开发存储引擎的使用的例子,略。

14.2.13.3 FULLTEXT索引

特别的索引,FULLTEXT索引,帮助innodb处理关于基于文字的查询。全文索引可以使用create table,alter table,create index创建。 MATCH() ... AGAINST 语法查询。

15.10 其他存储引擎

具体看:

14.2.13.4 索引的物理结构

所有InnoDB所以是b树结构,索引的page为16KB,当新的记录插入,InnoDB试图保留1/16的空间用于未来的插入和修改。

如果index record是顺序插入的,page为15/16,若是随机插入的1/2~15/16。当page低于1/2,page会试图调整释放page。

 

Note

      page的大小可以通过参数innodb_page_size制定。

15.11 MySQL存储引擎概述

MySQL插件式存储引擎体系结构,可以让数据库根据不同的程序选择不同的存储引擎。MySQL服务体系结构隔离了应用程序和DBA对底层存储的实现细节,提供一致的简单的程序模型和API。因此尽管不同的存储引擎能力不同,这些不同对应用程序不可见。

插件式存储引擎结构提供了标准的服务管理和支持。存储引擎自己是数据库服务的组件实现了数据库底层的数据保存。

有效的和模块化的体系结构提供了大量的好处,可以为程序定制不同的存储。比如数据长裤,事务处理和高可用。

14.2.13.5 Insert Buffering

数据库应用程序总是以主键自增的循序插入新行,因为聚集索引和primary key顺序一样,插入table不需要做随机I/O。

secondary index是不唯一的,insert到secondary顺序是随机的。删除和update会影响数据页是随机的。

当索引被删改,innodb会检查page是否在buffer pool。如果在,会直接修改index page,如果不在,InnoDB会记录到insert buffer中。insert buffer会保持的很小,可以放到buffer pool,修改会快速修改。这个进程被称为change buffering。

定期,insert buffer会合并到secondary index上,通常吧多个修改合并到一个page,来减少I/O操作。

insert buffer在事务提交后,会合并到insert buffer。当很多索引要被delete和update,insert buffer合并会话很长时间。这个时候磁盘I/O会增长,会导致依赖于磁盘查询延迟。另外一个重要I/O操作时清理线程。

15.11.1 插件是存储引擎结构

MySQL服务使用插入是存储引擎结构体系可以让存储引擎加载和卸载。

加载存储引擎:

在存储引擎被使用前,存储引擎的共享library必须被加载到MySQL,使用INSTALL PLUGIN语句。比如:example存储引擎plugin称为example,shard library是ha_example。so.

mysql> INSTALL PLUGIN example SONAME'ha_example.so';

为了能够加载存储引擎,插件文件的路径必须在MySQL plugin目录,并且执行INSTALL PLUGIN用户要有对mysql.plugin表有插入权限。

Shared library目录可以查看plugin_dir。

卸载存储引擎

为了卸载存储引擎可以使用UNINSTALL PLUGIN语句:

mysql> UNINSTALL PLUGIN example;

如果被卸载的存储引擎已经有表存在,那么这些表就不能被访问了,但是依然存在在磁盘上。保证在卸载之前已经没有表使用这个存储过程了。

 

 

14.2.13.6 自适应hash索引

自适应hash索引让innodb很像内存数据库,适应负荷和内存。不会牺牲任何事物特性和可靠性,使用innodb_adaptive_hash_index来启动和禁用。

 

根据查询部分,mysql创建一个hash索引使用固定的索引键。固定键的长度可以是任意长度的。只有btree的一些值会出现在hash索引上。hash索引只会的page进行访问。

当表整个放入住内存,hash index可以加快访问速度。innodb有机制可以监控索引访问。如果InnoDB觉得查询可以从创建hash index后获得好处,就会自动创建。

 

在某些情况下,hash索引查找提高的速度远远大于额外的监控的和维护hash索引结构。有时候,read/write锁守护数据在搞负荷情况下,会变成争用焦点。

 

你可以使用show engine innodb status的semaphores段来监控hash index。若看到大量的RW-latch在btr0sea.c被创建,可能取消自适应hash索引更好。

14.2.13.7 物理行结构

物理行结构依赖于InnoDB表依赖于表创建时候的row format。可以使用show table status检查。

14.3 InnoDB配置

InnoDB表空间和日志文件概述

2个重要的磁盘管理的资源是,InnoDB的数据文件和日志文件。如果没有InnoDB选项。MySQL创建自动扩展的文件大于12MB,ibdata1和2个日志文件ib_logfile0,ib_logfile1。大小由innodb_log_file_size系统参数。

考虑存储设备

把log文件放到不同的磁盘可以提升性能。也可以使用原始的磁盘分区作为innodb数据文件,可能可以提升性能。

指定innodb表空间位置和大小

innodb_data_file_path选项指定了数据文件,使用分号隔开。

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

以下设置显示的创建最小系统表空间

[mysqld]

innodb_data_file_path=ibdata1:12M:autoextend

若指定了autoextend InnoDB会自动扩展。max指定文件最大限制

[mysqld]

innodb_data_file_path=ibdata1:12M:autoextend:max:500M

innodb_data_home_dir指定了表空间文件的路径

[mysqld]

innodb_data_home_dir = /ibdata

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

 

Note

      innodb不会创建目录所以要保证,目录存在。并且保证有权限。

innodb_data_home_dir默认值为mysql 数据目录。

也可以直接在innodb_data_file_path上指定文件路径。

创建InnoDB系统表空间

当创建系统表空间时,最好使用命令行启动。

C:> "C:Program FilesMySQLMySQL Server 5.6binmysqld" --console

14.3.1 配置InnoDB为只读

如果MySQL数据目录在只读媒体中,带—innodb-read-only来启动服务。就可以查询了。

14.3.1.1 如何启动

准备只读操作,首先要保证数据被刷新到数据文件中。然后取消change buffer innodb_change_buffering=0,使用slow shutdown。使用参数—innodb-read-only =1。

14.3.1.2 使用场景

1.有一部分数据在只读介质中

2.多个MySQL实例并发查询相同的数据目录

3.因为数据安全或者数据集成的需要设置为只读

14.3.1.3 如何工作

如果MySQL启动了—innodb-read_only选项,InnoDB会关闭一些特性和组件:

1.没有启动change buffer

2.启动阶段没有carsh recovery

3.因为只读不需要redo log,可以把redo log 设置到最小

4.素有后台IO除了IO都可以关闭

5.deadlock,monitor输出等等都不会被写入临时文件。Show engine innodb status不会输出任何信息。

6.如果Mysql以—innodb-read-only启动,如果数据目录放在读写上,还是可以运行DCL命令grant,revoke。

7.修改配置选项可以修改写入操作,当read-only时,没有影响。

8.MVCC强制隔离级别被关闭

9.undo log不能使用。

14.4 InnoDB管理

InnoDB管理设置到很多方面:

1.管理数据文件表示系统表空间,innodb表和其他相关的索引

2.管理redo log文件

3.尽量使用innodb

4.性能相关的通用管理

14.5 InnoDB表空间管理

启动前,先检查innodb数据文件存在,并且有足够的权限访问。第一次启动使用命令行比较好。因为会把信息都print到命令行上。

14.5.2 InnoDB File-Per-Table模式

File-Per-Table的好处:

1.当表删除或者truncate,空间可以被OS回收。

2.Truncate table在单个.ibd文件上执行更快。

3.可以为每个表指定一个特定存储。优化IO,空间管理。CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory

4.运行OPTIMEIZE TABLE,压缩或者重建创建表空间。运行OPTIMIZE TABLE InnoDB会创建一个新的ibd文件。当完成时,老的表空间会被新的代替。

5.可以移动单个表,不需要移动整个数据库

6.可以把表复制到另外一个实例

7.innodb_file_per_table启动后才能使用Barracuda文件格式。

8.可以更有效的存储带BLOB,TEXT使用动态行模式的表。

9.使用innodb_file_per_table可以提高recovery的成功率,减少损坏错误发生恢复的时间。

10.可以快速的备份,恢复单个表。

  1. innodb_file_per_table 可以从备份中去除一个表

  2. innodb_file_per_table在备份和复制表时,容易观察每个表的状态。

13.可以通过文件系统直接观察表的大小。

14.当innodb_flish_method设置为O_DIRECT,通常linux文件系统不允许并发的写入同一个文件。使用innodb_file_per_table就会有性能提升。

15.不启用innodb_file_per_table,数据都会放在系统表空间中,最大64TB,如果使用innodb_file_per_table每个表可以64TB。

 

File-Per-Table一些缺点:

1.表空间中的空间只能被这个表使用

2.fsync操作必须在每个表上都运行一遍

3.mysqld必须保持一个打开的文件句柄,表太多会影响性能。

4.会消耗很多文件描述

  1. innodb_file_per_tablezhiyou 5.6.6或更高版本才能用,有向下兼容问题。

6.如果很多表都增长,会出现文件碎片问题。导致drop 表和表扫描性能下降。

7.当drop表的时候会扫描buffer pool,如果太大会比较耗时。

8.innodb_autoextend_increment指定当文件满了之后增长的空间。

 

14.5.3 启动禁用File-Per-Table模式

File-Per-Table启动禁用方式:

设置配置文件或者全局变量:innodb_file_per_table。

启动innodb_file_per_table,InnoDB会吧数据存放在ibd文件中,和MyISAM不通,MyISAM存放在tbl_name.myd,tbl_name.myi。

InnoDB的数据和索引都放在ibd文件中,frm文件依然还是会创建。如果关闭innodb_file_per_table那么InnoDB的表都会创建在系统表空间中。

通过以下方法可以移动innodb到自己的表空间:

-- Move table from system tablespace to its own tablespace.

SET GLOBAL innodb_file_per_table=1;

ALTER TABLE table_name ENGINE=InnoDB;

-- Move table from its own tablespace to system tablespace.

SET GLOBAL innodb_file_per_table=0;

ALTER TABLE table_name ENGINE=InnoDB;

14.5.4 指定TableSpace的路径

启动了innodb_file_per_table,可以再创建表的时候指定文件路径。具体看create table。

这个位置很重要,因为之后不能通过alter table修改。在数据目录上,MySQL在目标目录上创建一个以数据库命名的子目录,里面存放新的ibd文件。在DataDir上创建一个tbl_name.isl文件,包含了这个文件的路径。这个文件相当于symbolic link。

Note

      1.MySQL初始化的时候会把ibd文件保持打开装填,以防止设备被unmount。不要在mysql运行的时候unmount,不要在unmount的时候启动服务。

*       2.不要把Mysql表放在NFS mounted卷上。NFS使用消息来修改文件,如果消息不对,可能会影响数据一致性。*

*       3.如果使用LVM快照,file copy,或者基于文件的备份,先执行FLUSH TABLS FRO EXPORT.*

*       4.data directory子句可以使用symbolic link来代替。*

 

14.5.5 把表空间复制到另外一个服务上

表空间复制的限制和注意点

1.表空间复制过程只有当innodb_file_per_table启用的时候才能用。

2.只有读操作可以再该表上使用。

3.导入时,page size必须和被导入实例上的一致。

4.DISCARD TABLESPACE不支持分区表。

  1. DISCARD TABLESPACE有主外键约束的也不支持,在discard之前要先设置foreign_key_checks=0。

6.alter table import tablespace不会强制外键约束

7.alter table import tablespace 不需要cfg文件,但是当没有cfg文件的时候导入时不会检查元数据。

8.MySQL 5.6之后,如果2个服务都是GA状态,并且在一个系列里面,可以导入。否则不许先在没导入服务上创建文件。

9.在windows上innodb,表空间,表名都是小写,为了避免这个问题,要注意linux,unix上的大小写。

例子:移动表空间到另外一个服务上

1.            源服务器上创建创建表:

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

2.                  在目标服务器上创建表,如果不存在:

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

3.                  目标服务器上discard表

mysql> ALTER TABLE t DISCARD TABLESPACE;

4.                  源服务器上运行 FLUSH TABLES ... FOR EXPORT 限制表,并创建了cfg文件:

mysql> use test;

mysql> FLUSH TABLES t FOR EXPORT;

cfg文件被创建在数据目录上.

5.                  复制ibd和cfg文件:

shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test

6.                  Unlock 表释放flush table for export的锁:

mysql> use test;

mysql> UNLOCK TABLES;

7.                  导入到目标服务:

mysql> use test;

mysql> ALTER TABLE t IMPORT TABLESPACE;

 

表空间复制内部细节

当运行alter table discard tablespace,目标服务:

1.表会被X锁

2.表空间会被分离

当运行flush tables for export:

1.表被S锁

2.清理线程停止

3.脏数据被同步到磁盘

4.表元数据生产到cfg文件

当运行unlock tables

1.cfg被删除

2.s锁被释放,清理线程启动

当运行alter table import tablespace:

1.检查表空间page是否损坏

2.空间Id和LSN被更新

3.检查flag,LSN被更新

4.b树被更新

5.page设置为dirty,让数据库可以写入磁盘。

14.5.6 把undo log移出System表空间

你可以把undo log分离到独立的表空间文件。详细看14.13.16

14.5.7 修改InnoDB日志文件,增长InnoDB表空间

MySQL 5.6.8之后,修改innodb日志文件和大小:

1.没有错误下,关闭服务。

2.编辑my.cnf,设置innodb_log_file_size,innodb_log_files_in_group

3.启动服务。

当innodb 发现innodb_log_file_size和老的不一样。会删除redo log文件,创建新的文件。

 

MySQL 5.6.7和之前的版本,修改innodb文件个数和文件大小:

1.设置innodb_fast_shutdown 为1

2.关闭服务没有错误

3.复制老的日志文件到其他地方

4.在目录中删除日志文件

5.编辑配置文件

6.启动服务

 

增加InnoDB表空间

最简单的方法是通过自增长来实现,innodb_autoextend_incremnt参数。也可以通过增加新的文件方式来扩展空间:

1.关闭服务

2.若之前的文件定义了自增长,修改变为固定长度,设置大小。可以再参数innodb_data_file_path中显示指定。

3.在innodb_data_file_path后增加新的文件,可以设置为自增长。

4.启动服务。

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

一段时间之后,文件增长到了988M。

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

 

减少InnoDB表空间

减少innodb表空间过程:

1.使用mysqldump,导出所有innodb表包括在mysql数据库中的表。

2.停止服务

3.删除所有存在的ibd文件,ibdata,ib_log

4.删除frm文件

5.配置新的表空间

6.启动服务

7.导入文件。

14.5.8 使用原生磁盘分区作为系统表空间

你可以把系统表空间放到原生分区中。当使用原生磁盘分区,保证服务有读写分区的权限。

在Linux和unix系统分配原生磁盘分区

1.当你创建一个新的数据文件,指定在innodb_data_file_path选项设置newraw

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

2.重启服务,InnoDB注意到newraw关键字,并初始化这个行分区,不创建或者修改任何InnoDB表。否则下次重启InnoDB会重新初始化修改会丢失。

3.InnoDB初始化新分区之后,停止服务,把newraw修改为raw。

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

4.重启服务,InnoDB允许修改。

14.6 InnoDB表管理

14.6.1 创建InnoDB表

创建表

-- Default storage engine = InnoDB.

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a));

-- Backward-compatible with older MySQL.

CREATE TABLE t2 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

根据innodb_file_per_table设置,InnoDB创建每个表在系统表空间或者独立的表空间,使用show table status语句查看这些表的属性:

mysql> SHOW TABLE STATUS FROM test LIKE 't%' G;

*************************** 1. row ***************************

           Name: t1

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 0

 Avg_row_length: 0

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2015-03-16 16:26:52

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

使用新的行模式

SET GLOBAL innodb_file_per_table=1;

SET GLOBAL innodb_file_format=barracuda;

CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=DYNAMIC;

CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=COMPRESSED;

为primary key设置主键,列的选择:

1.被很多查询引用

2.左边无空白

3.不会有重复键

4.一旦插入后很难修改

14.6.2 把InnoDB表移动到另一个机器

14.6.2.1 跨平台复制移动使用小写

为了跨平台移动,创建的数据库和表使用小写。在配置文件中设置

[mysqld]

lower_case_table_names=1

14.6.2.2 传输表空间

使用flush tables for export准备innodb表复制到另外一个服务。当然InnoDB必须开启innodb_file_per_table

14.6.2.3 MySQL企业备份

14.6.2.4 复制数据文件

可以使用InnoDB数据库,简单的复制所有相关文件。像MyISAM数据文件,InnoDB数据和日志文件是binary,所有的平台都兼容。

14.6.2.5 ibd文件可移植性

当移动ibd文件,数据库目录名必须相同。如果你有干净的ibd文件备份,你可以恢复到新装MySQL:

1.复制ibd之后,不要drop或者truncate,因为这样会修改表空间中存在的表ID。

2.alter table删除元数据

ALTER TABLE tbl_name DISCARD TABLESPACE;

3.复制ibd文件到目标库中

4.使用alter table语句导入元数据

ALTER TABLE tbl_name IMPORT TABLESPACE;

干净的ibd文件备份要满足一下条件:

1.没有未提交的修改。

2.没有未合并的insert buffer

3.清理所有删除标记的index record

4.把所有修改也从buffer pool写入到文件中。

使用以下步骤创建干净的ibd备份。

1.停止所有活动并提交所有事务

2.等待直到show engine innodb status没有活动,主线程的状态为waiting for server activity。

另外一个方式是:

1.使用Mysql企业版备份备份mysql

2.启动mysqld服务,清理ibd文件。

 

导入导出

可以使用mysqldump导出表,然后在另一个表上导入。在导入时关闭自动提交可以提高性能。

14.6.3 使用事务批量DML操作

连接到MySQL默认是自动提交模式。多余多语句事务,可以set autocommit=0取消自动提交。使用start transaction开启,commit,rollback结束。

14.6.4 把MyISAM转化为InnoDB

14.6.4.1 减少MyISAM内存使用,增加InnoDB内存使用

从MyISAM中过渡,减少key_buffer_size配置释放内存。增加innodb_buffer_pool_size配置。

1.尽可能的多分配内存,最多80%的内存

2.当os总是出现内存短切,减少innodb_buffer_pool_size

3.若innodb_buffer_pool_size有好几个G可以使用innodb_buffer_pool_instance增加buffer pool,减少访问buffer pool的冲突。

4.在忙绿服务,把query cache关闭。

14.6.4.2 查看太长或者太短的事务

因为MyISAM不支持事务,所以不用在意自动提交。主要是用在innodb上的。当大量的写入的服务上,若事务太长会生成大量的负荷。因此小心避免事务运行时间太长:

1.如果你使用mysql交互式会话,完成的时候总是使用commit,rollback。及时关闭交互式会话。

2.保证任何错误,rollback不完整修改或者commit完整提交

3.ROLLBACK是比较昂贵的操作。当处理大量数据后,避免执行rollback

4.大量批量insert语句,同期的commit可以提高性能。

事务太长,会浪费内存和磁盘空间。事务太短,因为commit太频繁,也会浪费I/O。对于1.频繁使用InnoDB表,应该取消自动提交避免不必要的I/O。

2.自动提交适合与生产报表或者分析统计。

3.如果做了一列相关修改,最后要使用一个commit。

4.select语句也会打开一个事务。

14.6.4.3 不要太担心死锁

MySQL会很快发现死锁并且取消小的事务。应用应该处理错误并重启事务。如果死锁发生很频繁,就要检查应用程序代码,整理代码顺序,或者缩短事务。Innodb_print_all_deadlock会吧错误信息输出到error log中。

14.6.4.4 计划存储分布

为了获得做好的性能,需要调整几个和存储有关的参数。当你转化一个MyISAM表,数据大,访问频繁,并且重要的。可以考虑innodb_file_per_table,innodb_file_format和innodb_page_size配置,和create table中的row_format,key_block_size。最重要的参数是innodb_file_per_table。

14.6.4.5 转化一个现有的表

使用alter命令转换

ALTER TABLE table_name ENGINE=InnoDB;

14.6.4.6 克隆表结构

使用show create table table_name 获取表结构,然后修改engine子句。

14.6.4.7 转化已存在数据

把数据转移到空的innodb表,可以使用:

INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns.

可以再插入之后在创建索引,以前索引创建是很慢的,现在消耗已经降低。如果有唯一性约束,可以先关闭约束检查,提高插入性能

SET unique_checks=0;

... import operation ...

SET unique_checks=1;

对于达标,可以减少I/O因为innodb可以使用insert buffer来批量写入secondary index的修改。

对于大表也可以使用如下来控制insert

INSERT INTO newtable SELECT * FROM oldtable

   WHERE yourkey > something AND yourkey <= somethingelse;

14.6.4.8 存储需求

不管是转化MyISAM到InnoDB,都要保证有足够的空间存放新老2份数据。如果alter table出现空间不足,会回滚,并且化很长时间。Innodb使用insert buffer批量合并到index rolback没有这样的机制,会比insert多近30倍的时间。

14.6.4.9 仔细选择Primary Key

Primary key是总要的因素,会影响mysql查询性能和表索引空间使用。

创建primary key的指导:

1.为每个表定义主键

2.最好在创建表的时候就定义,而不是使用alter table

3.仔细的选择列和数据类型,最好使用数值列

4.如果没有合适的列,考虑自增列

5.如果不再到主键会不会被修改的情况下,自增列也是很好的选择。

根据表的预计大小,使用最小的类型,可以让表收窄减少空间,若有secondary索引,可以减少索引空间。

创建表,没有指定主键,mysql会自动创建一个6字节长度的主键,但是不能用于查询。

14.6.4.10 应用程序性能考虑

额外的可用性和扩展性,需要的空间比MyISAM多。可以窄设计来节省空间。

14.6.4.11 InnoDB表和文件的关联

InnoDB文件需要比MyISAM更加小心,有计划性:

1.不能删除innodb系统表空间

2.复制innodb到另外一个服务,需要使用flush table for export并且复制cfg和ibd文件。

14.6.5 InnoDB自增处理

InnoDB对自增列的插入做了优化。InnoDB表的自增必须是索引的一部分。

14.6.5.1 传统InnoDB自增长锁

若在InnoDB上使用了自增,InnoDB数据目录包含auto-increment计数器。用来分配自增值,计数器保存在内存中,不是在磁盘中。

InnoDB初始化自增计数器,服务启动后,第一个插入的语句会运行如下语句:

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB增加这个值,并分配给counter和这个insert。默认增加为1,可以通过auto_increment_increment来配置。

若表是空的,就使用1,可以用auto_increment_offset来设置便宜。

若在初始化钱使用了语句show table status,innodb初始化计数器,但不增加。初始化使用排它锁。

 

在初始化之后,如果不显示的指定值,自增计数器会分配一个值。如果指定的值大于计数器,计数器就会被更新。如果为自增列指定了null或者0,innodb会为自增分配一个新值。

 

访问计数器,会使用auto-inc锁,知道语句结束才释放。Auto-inc提高了并发性,但是长期持有会导致性能问题。如:

Insert into t select from t1;

 

服务重启后,第一次插入会初始化自增计数器,也会取消create table中auto_increment设置。

若事务rollback,计数器不会rollback。

14.6.5.2 配置InnoDB自增锁

InnoDB的自增使用特殊的锁AUTO-INC。语句结束就释放。对于基于语句的复制,意味着slave,master使用想用的语句,会导致insert语句不确定。

有表如下:

CREATE TABLE t1 (

  c1 INT(11) NOT NULL AUTO_INCREMENT,

  c2 VARCHAR(10) DEFAULT NULL,

  PRIMARY KEY (c1)

) ENGINE=InnoDB;

插入数据

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...

Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

 

InnoDB不知道有多少行要通过select插入。在语句执行是一个一个的分配。Tx1生产的自增时连续的,Tx2分配的自增,和Tx1比大小,由谁先运行决定。

只要执行顺序和binary log一样,结果是一样的。所以在插入的时候可以使用表锁,这样自增分配对复制来说是安全的,然而当有多个会话insert时表锁会影响并发性和可扩展性。

 

如果没有加表锁,那么tx1和tx2分配的自增将是不确定的。有类insert事先知道插入的行数,就可以避免表级auto-inc锁,任然可以保证给予语句复制的安全性。如果不需要使用binlog来恢复或者复制,可以取消表级auto-inc锁来提高性能。但是会出现自增值是交错的。

 

对于可以知道insert行数的InnoDB可以快速分配值,而不用加锁,前提是auto-inc没有分配。这些insert语句获取自增值是有mutex控制,分配过程完成就释放。

 

新的锁机制可以提高可扩展性,但是和之前比原理有些不同,可以通过参数innodb_autoinc_lock_mode配置.insert因为auto-inc锁定的问题被分为几类:

Insert-like:

所有在表上生成新行的语句,insert,insert select,replace,replace select,load data

Simple insert:

行插入可以预知行数。Insert on duplicate key update除外。

Bulk insert:

插入行数无法预知,insert select,replace select,load data。

Mixed-Mode Insert:

其实是simple insert 但是中间有些指定的自增列

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

 

有以下一些取值:

0:传统锁模式,所有的insert-like语句都获取auto-inc锁保持知道语句结束

1: 连续锁模式,bluk insert语句会使用auto-inc锁,simple-insert可以使用轻量的mutex。但是如果有语句使用了auto-inc就要锁释放。分配的自增值都是顺序的。对于mixed-mode insert因为用户指定,所以会分配比需要的更多的自增。

2:交错锁模式,多个语句可以同时执行,但是对于给予语句的复制是不安全的。

 

Innodb_autoinc_lock_mode对其他使用的影响:

在复制中自增:如果使用基于语句的复制,建议设置为1,0,保证自增值确定性。

丢失自增值和顺序空隙:在所有的模式下,如果事务回滚,都会导致自增值丢失。

Bulk insert自增值空隙:在1,0下自增值分配是顺序的。没有空隙,但是如果为2,bluk insert和insert like语句一起执行的时候就可能会出现空隙。

Mixed-mode insert自增值的分配:因为混合模式,有些会指定自增,有些不会。语句在不同锁定模式下,反应也不同:

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

Innodb_autoinc_lock_mode = 0

+-----+------+

| c1  | c2   |

+-----+------+

|   1 | a    |

| 101 | b    |

|   5 | c    |

| 102 | d    |

+-----+------+

下一个值是103

必赢棋牌官网,Innodb_autoinc_lock_mode = 1

+-----+------+

| c1  | c2   |

+-----+------+

|   1 | a    |

| 101 | b    |

|   5 | c    |

| 102 | d    |

+-----+------+

下一个值是105而不是103

Innodb_autoinc_lock_mode = 2

+-----+------+

| c1  | c2   |

+-----+------+

|   1 | a    |

|   x | b    |

|   5 | c    |

|   y | d    |

+-----+------+

X,y是唯一的大于之前生成的任何行。

如果下一个自增时4,那么上面insert语句就会报错,建冲突。因为5会分配给b,和c冲突。

14.6.6 InnoDB和外键约束

14.6.7 InnoDB表的限制

不要把MySQL数据库上的表转移到InnoDB上,不支持。一旦转化后,MySQL不能重启除非还原到以前的备份。不要把InnoDB表创建在NFS上。

14.6.7.1 最大和最小

1.一个表最多能够包含1017个列

2.最多能有64个secondary 索引

3.单列索引,key的长度最多只能767个字节,使用innodb_large_prefix可以突破767的限制最多为3072.如果再复制情况下,如果slave不能设置,master也不要设置

4.InnoDB最大key长度为3500,但是MySQL限制到3072字节

5.若page size奢侈为8KB或者4KB对应的index key也会按比例收缩:

       16KB->3072B,8KB->1536B,4KB->768B

6.最大行长度,也会根据page size变化而变化

       16KB->8000B,8KB->4000B,4KB->2000B

7.在老的操作系统上,文件做多为2GB。

8.innodb 日志文件最多为512GB。

9.表空间最小为10MB,最大为64TB,也是表做大的大小。

10.默认数据库page size为16KB。

14.6.7.2 索引类型

1.全文索引

2.InnoDB支持空间数据类型,但是不能创建索引

14.6.7.3 InnoDB表的限制

1.通过analyze table确定索引中数据密度。,analyze table随机采样并且更新表的评估值。

当启动innodb_stats_persistent启动,要在大量修改之后运行analyze table,因为启动后,统计信息不会被定期计算。

 

在生产统计信息是,可以通过innodb_stats_persistent_sample_page修改采样率,或者使用innodb_stats_transient_sample_pages。

 

若join优化器优化有问题,可以使用analyze table重新分析。也可以使用force index强制缩影使用。也可以通过变量max_seeks_for_key,设置最大seek,超过就变扫描。

 

2.若语句或者事务在一个表上运行,并且有一个analyze table运行,后面还跟了一个analyze table。那么第二个analyze table就会被堵塞。直到语句或者事务完成。因为analyze table 运行完成必须要标记当前加载的表定义过期。新的事务或者语句必须加载新的表定义。在当前事务没有完成前老的表定义不能被清理。

 

3.show table status不能提供准确的统计信息,只能看被表保留的物理空间。InnoDB内部不保存行数。准确的行数需要count,若不需要太准确可以使用show table status的结果。

 

4.在windows平台上,innodb数据库和表名内部都用小写保存

 

5.auto_increment列必须为索引的一部分

 

6.在auto_increment初始化的时候,innodb会在和自增列相关的索引尾上加一个排它锁。当访问auto-increment列时innodb使用特定的auto-inc锁,直到语句完成。

 

7.当重启mysql服务,innodb可能重用老的值,因为分配后没有被保存到表里面。

 

8.当auto_increment超出之后的insert会报错重复key错误。

 

9.delete from table_name 不会重新生成表,是删除所有行。

 

10.外键的级联操作,不会触发触发器

 

11.不能创建列和innodb内部列一样的名字。

14.6.7.4 锁定和事务

1.在MySQL层lock table会锁定MySQL层,也会请求InnoDB 表锁。如果innodb_table_locks=1,lock tables会申请mysql层的表锁和innodb的表锁。如果为0不需要申请innodb的表锁。在没有innodb表锁情况下,lock tables在其他事务在表上锁定了一些记录的时候,也可以成功锁定。

 

2.innodb锁在事务内有效,事务结束后释放。

 

3.不能再事务中锁定其他表,因为lock tables会隐式执行commit和unlock tables。

 

4.从1023并发事务修改,被升级到128*1023,同事写入undo record.

14.7 InnoDB压缩表

使用SQL语法和MySQL配置,可以创建压缩保存数据的表。压缩可以提高原设备的性能和可扩展性。压缩也表示内存和磁盘传输变小。占用的空间变小。

14.7.1 表压缩概述

InnoDB可以创建压缩表,row_format=compressed并制定较小的page size。Page越小请求的I/O越少,特别是SSD。

Page size可以通过key_block_size指定。不同的page size 表示表必须在自己的ibd文件里面,必须启动innodb_file_per_table。如果key_block_size越小,因为page越小,I/O效率提高。但是如果过分小,如果不能压缩的单个page中,就需要额外的操作去重组page。

 

在buffer pool压缩数据以小page保存。Page size依据key_block_size,对于提取和更新,MySQL会分配16KB的非压缩数据。然后在生产压缩页。可能需要加大buffer pool来保存压缩和非压缩页,尽管非压缩页会在内存不够时被牺牲。

 

14.7.2 启动压缩表

在创建压缩表前,先要启动innodb_file_per_table和innodb_file_format=Barracuda然后创建表或者修改,并指定row_format=compressed,key_block_size。

SET GLOBAL innodb_file_per_table=1;

SET GLOBAL innodb_file_format=Barracuda;

CREATE TABLE t1

 (c1 INT PRIMARY KEY)

 ROW_FORMAT=COMPRESSED

 KEY_BLOCK_SIZE=8;

1.若指定了row_format=commpressed可以忽略key_block_size,默认page是innodb_page_size的一半。

2.指定了key_block_size,可以忽略row_format=compressed,会自动启动压缩。

3.确定key_block_size的最好方法是,对同一个表按size不同分配创建压缩,并且比较负荷。

4.key_block_size被认为是提示,如果是0就表示innodb_page_size的一般,key_block_size<=innodb_page_size,若innodb_page_size>key_block_size就会被忽略,并设置为innodb_page_size的一半。如果innodb_strict_mode=on则会报错。

5.其他性能相关的看14.7.3 压缩表调优

 

MySQL key_block_size可以1KB,2KB,4KB,8KB,16KB,但是不会影响压缩本身的算法,只是决定每个chunk的大小。

 

设置Key_block_size等于InnoDB page size就表示可能没有太大的压缩比率。但是对于TEXT,BLOB,varchar还是有用的,可以减少overflow page。

 

表上的所有索引被压缩都使用相同的page size。由create table或者alter table指定。Create index中的row_format,key_block_size不是innodb表的属性,如果指定会被忽略。

 

压缩表的限制:

Innodb系统表空间不能使用表压缩。压缩只能应用在整个表和索引上。不能用在指定行集上。

14.7.3 InnoDB压缩表调优

压缩比率和数据有关,但是可以决定压缩表的性能影响:

1.什么表要压缩。

2.使用多大的page size

3.runtime情况下如何调整buffer pool大小。

4.若系统执行了DML操作在压缩表上,而数据是分布方式导致高昂的压缩操作失败,可能需要额外的高级配置选项。