MySQL/Table types

　 每张表在硬盘或内存的物理存储，需要由存储引擎（Storage Engine， SE）完成。 存储引擎对数据库是插件（plugin），可以安装/卸载。SE影响了表的有效性能、稳定性、锁类型、查询cache的使用、需要的磁盘空间等等。

表类型（Table Type）已经是个过时概念，现在使用引擎（ENGINE）或存储引擎（STORAGE ENGINE）代替。

存储引擎

MyISAM 与 InnoDB

MyISAM是表级上锁，InnoDB提供行级上锁。

除了外键，InnoDB支持事务。

MySQL在Linux上的默认表类型是MyISAM, 在Windows上默认表类型是InnoDB.

实现细节对比

InnoDB存储引擎默认每个页的大小为16KB。在某个页内插入新行时，为了减少数据的移动，通常是插入到当前行的后面或者是已删除行留下来的空间，所以在某一个页内的数据并不是完全有序的，但是为了为了数据访问顺序性，在每个记录中都有一个指向下一条记录的指针，以此构成了一条单向有序链表。新建的表，由于数据还比较少，一个页就能容下，所以只有一个根结点（page1），主键和数据也都是保存在根结点。随着行数的增加，Page1放不下了，这时候就需要进行页分裂，产生一个新的Page，流程为：

1. 产生新的Page2，然后将Page1的内容复制到Page2。
2. 产生新的Page3，新行数据放入Page3。这不符合B+树的节点分裂原则，是InnoDB的优化。这里有个隐含条件，那就是主键自增！如果一个表没有主键，那默认会找建了唯一索引的列rowid，如果也没有，则会生成一个隐形的字段作为主键！
3. 原来的Page1依然作为根结点，但是变成了一个不存放数据只存放索引的页，并且有两个子结点Page2、Page3。根结点是会预读到内存中的，所以根结点的物理地址固定会比较好

如果表频繁插入和删除，会导致数据页产生碎片，页的空间利用率低，还会导致树变的“虚高”，降低查询效率。这可以通过索引重建来消除碎片提高查询效率。

InnoDB数据页结构：

• Page Directory由多个slot有序组成，是一个稀疏索引结构。每个slot代表多个记录，最少属于4条记录，最多属于8条记录。当寻找一条数据的时候可以先在槽中通过二分法查找到一个大致的位置。
• 数据行形成一个单向链表。表头、表尾为两个虚记录。每个slot指向的数据行与前一个slot指向的数据行之间有4-8个数据行，形成一个逻辑块。块内是顺序查找。

InnoDB的主键形成聚集索引；非主键形成了非聚集索引，需要“回表”来查出具体的数据行。“回表”是指其他索引通过主键查询表的某一行。

MyISAM主键索引的存储结构：

• 主键索引树的叶子结点的数据区域没有存放实际的数据，存放的是数据记录的地址。
• 数据的存储不是按主键顺序存放的，按写入的顺序存放。

MyISAM

• 优点
  • 全文搜索
  • Geometric数据类型
  • 读速度更快
  • 数值是大尾序存储，利于索引压缩
  • 内部支持每张表一个AUTO_INCREMENT列。

• 缺点
  • 表级锁
  • 无外键
  • 掉电后恢复更慢

InnoDB

• 优点
  • 事务安全(ACID兼容)存储引擎具有commit, rollback, 崩溃恢复能力
  • XA事务
  • 外键
  • 行级枷锁
  • 在主存维持自己的缓存池用于数据与索引的缓冲
  • 当主键物理排序或者hash索引时，查询速度更快
  • 表的大小不限，即使操作系统限制文件最大2GB.
  • 掉电后更快与可靠恢复

• 缺点
  • 更多存储空间
  • ACID保证要求在事务提交时完全同步到硬盘。对于速度非常关键应用，也可以关闭这条。
  • 数据版本与事务，增加了表管理开销
  • 管理大量行锁要求很高内存
  • 对已有的表，增加索引很慢

Merge Table

同义词为Merge, MRG_MYISAM

• 一张MERGE table是相同的MyISAM表的集合。相同是指所有表有相同的列与索引信息。

CREATE TABLE mumbai (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE delhi  (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE total  (first_name VARCHAR(30), amount INT(10)) TYPE=MERGE UNION=(mumbai,delhi)

Merges可以用于克服MySQL或操作系统的文件尺寸限制。也曾用作代替view。MySQL 5.0开始支持view，因而Merge tables过时了

MEMORY / HEAP

MySQL 4.1之前叫做HEAP。此后叫做MEMORY

3.23引入。

BDB

Berkeley DB（BDB）在5.1版之后由于用户太少而被去除。

BLACKHOLE

Discards all data stored in it but does still write to the binary log, so it is useful in replication scale-out or secure binlog-do filtering situations where slaves aren't trustworthy and for benchmarking the higher layers of the server.

其他

• CSV:
• EXAMPLE (a stub for developers)
• ISAM (3.23版本之前使用，从5.1删除)

存储引擎的元数据

通过SQL可以得到你的server使用的存储引擎的元数据

MySQL 5.0开始可以用下述语句：

SHOW STORAGE ENGINES

STORAGE这个词可以省略。返回结果包含的列：

• Engine - 名字.
• Support - 是否支持，取值可以为：
  • 'DEFAULT' - 默认引擎
  • 'YES' - 支持;
  • 'DISABLED' - 已经编译,但MySQL启动时禁用该引擎，如启动参数选项：--skip-engine-name
  • 'NO' - 不支持
• Comment - 描述
• Transactions - 引擎是否支持SQL transactions
• XA - 引擎是否支持XA transactions
• Savepoints - 引擎是否支持savepoints与rollbacks

INFORMATION_SCHEMA `ENGINES` table

`ENGINES`是INFORMATION_SCHEMA数据库中的虚拟的表. MySQL 5.1.5增加。

HELP 语句

HELP 'myisam'

客户端可以输入:

help myisam \g

改变存储引擎

SQL

创建表时，使用 ENGINE 子句指定存储引擎：

CREATE TABLE ... ENGINE=InnoDB

否则，storage_engine变量的值被使用。你可以改变该变量：

SET storage_engine=InnoDB

或者修改MySQL服务器配置文件my.cnf中的default-storage-engine

修改已经存在的表的存储引擎:

ALTER TABLE `stats` ENGINE=MyISAM

mysql_convert_table_format

MySQL提供了工具mysql_convert_table_format, 用Perl写成. 把表从一种存储引擎转换为另外的存储引擎。

语法:

mysql_convert_table_format [options] database

选项：

--help

--version

--host=host MySQL正在运行的host. 缺省: localhost.

--port=port TCP端口.

--user=user

--password=password

--type=storage_engine 转换后的存储引擎。

--force 错误发生时不停止执行

--verbose

例如:

mysql_convert_table_format --host=localhost --user=root --password=xyz970 --force --type=InnoDB test