涉及的命令

  • 查看表的默认引擎: show variables like 'table_type'; 这个旧版本的用法,新的版本用法如下:
mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)
  • 查看支持的引擎: show engines\G

各引擎特点

MyISAM

MyISAM不支持事务、也不支持外键,其优势是访问的速度快,对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表.

在旧版本的MySQL上, 它是默认的引擎. 但新版本的则以InnoDB默认引擎.

每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但扩展名分别是

  • .frm(存储表定义)
  • .MYD(MYData,存储数据)
  • .MYI(MYIndex,存储索引)

要指定索引文件和数据文件的路径,需要在创建表的时候通过 DATA DIRECTORY 和INDEX DIRECTORY语句指定,也就是说不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径,并且具有访问权限

MyISAM的支持3种不同的存储格式, 分别是:

  • 静态(固定长度)表(默认,注意, 这种会删除最的一空格字符的.)

  • 动态表(频繁更新或删除时, 定期执行 optimize table 或 myisamcheck -r 命令来改善性能.)

  • 压缩表(由myisampack工具创建)

InnoDB

具有事务功能. 在新版本的MySQL里, 它已经成为默认引擎了. * 支持外键约束. 在创建外键时, 要求父表必须要有对应的索引, 子表在创建外键时, 也会自动创建索引. 外键动作:

RESTRICTCASCADESET NULLNO ACTIOIN

  • 其中RESTRICT和NO ACTION相同,是指限制在子表有关联记录的情况下父表不能更新;

  • CASCADE表示父表在更新或者删除时,更新或者删除子表对应记录;

  • SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被 SET NULL

存储方式

  • 共享表空间:表定义保存在.frm文件中. 数据和索引保存在innodb_data_home_dirinnodb_data_file_path定义的表空间中.可以是多个文件.

  • 多表空间: 表定义保存在.frm文件中. 但每个表的数据和索引单独保存在.ibd中.如果是分区表, 则每个分区表对应单独的.ibd文件, 文件名为表名+分区名.使用多表空间的存储方式, 需要设置参数innodb_file_per_table, 并且重启后才可以生效.

注意: 即便在多表空间的存储方式下, 共享表空间仍然是必须的, InnoDB把内部数据词典和在线重做日志放在这个文件中.

MERGE

MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有数据,对MERGE类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行的。对于MERGE类型表的插入操作,是通过INSERT_METHOD子句定义插入的表,可以有3个不同的值,使用FIRST或LAST值使得插入操作被相应地作用在第一或最后一个表上,不定义这个子句或者定义为NO,表示不能对这个MERGE表执行插入操作。

数据类型处理

  • BLOB能用来保存二进制数据,比如照片;而TEXT只能保存字符数据,

  • BLOB和TEXT值会引起一些性能问题,特别是在执行了大量的删除操作时. 建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理,避免因为“空洞”导致性能问题

  • 在不必要的时候避免检索大型的BLOB或TEXT值

  • 把BLOB或TEXT列分离到单独的表中

  • 定点数不同于浮点数,定点数实际上是以字符串形式存放的,所以定点数可以更精确地保存数据

  • 浮点数存在误差问题; 对货币等对精度敏感的数据,应该用定点数表示或存储