一、MySQL存储引擎概述
MySQL与多数数据库不同的是包含存储引擎这一特性,用户可以根据应用的需要选择合适的存储引擎来使存储和索引数据,以及是否使用事务等。MySQL5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。其中InnoDB和BDB提供事务安全表,其他存储引擎都是非事务安全表。创建新表时在不指定存储引擎的情况下,系统会使用默认存储引擎,MySQL5.5之前默认的是MyISAM,5.5后改为InnoDB。如果要修改默认存储引擎,可以在参数文件中设置,Linux系统为/etc/my.cnf,Windows为MySQLServer5.7/my-default.ini,在[mysqld]后面增加default-storage-engine=INNODB即可。
二、MySQL存储引擎常规操作
1、查询当前数据库支持的存储引擎的两种方法:
a. mysql> SHOW ENGINES \G
b. mysql> SHOW VARIABLES LIKE 'have%';
2、将已存在的表修改成其他的存储引擎,如将表ai的存储引擎从MyISAM改为innodb:
a. mysql> alter table ai engine = innodb;
三、各存储引擎相关特性(常用)
1、MyISAM
MyISAM不支持事务和外键,访问速度快。对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用该引擎创建表。每个MyISAM会在磁盘上存储成3各文件,文件名均与表名相同,但扩展名分别是:
.frm(存储表定义)
.MYD(MYData,存储数据);
.MYI(MYIndex,存储索引);
且MyISAM的表还支持3种不同的存储格式,分别是:
静态(固定长度)表:默认的存储格式,非变长字段,每个记录都是固定长度,存储非常迅速,出现故障易恢复但占用空间多。静态表数据在存储时会按照列的宽度定义补足空格,但在应用访问时因在返回给用用之前已去掉,所以并不会得到这些空格。如果需要保存的内容后面本就带有空格,那么在返回的结果时也会被去掉。
动态表:变长字段,记录不是固定长度,占用空间少,但频繁地更新和删除记录会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能,且出现故障不易恢复。
压缩表:由myisampack工具创建,占据磁盘空间非常小——因每个记录是被单独压缩地,所以只有非常小地开支。
MyISAM的自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引的前面激烈进行排序后递增。例如创建一个新的MyISAM类型的表autoincre_demo,自动增长列d1作为组合索引第二列,插入记录后发现自动增长列是按组合索引的第一列d2进行排序后递增。
2、InnoDB
支持事务:具有提交、回滚和崩溃恢复能力的事务安全。相比MyISAM,写的处理效率较差,且会占用更多磁盘空间以保留数据和索引。InnoDB表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值。可通过“ALTER TABLE 表名 AUTO_INCREMENT=n;”语句强制设置自动增长列的初始值,默认从1开始,但是强制的默认值是保留在内存中的,如果使用前重启数据库则该值会丢失,需要在数据库重启后重新设置。对于InnoDB,自动增长列必须是索引。如果是组合索引也必须是其第一列。MySQL中只有InnoDB支持外键,且在创建外键时,父表必须有对应的索引,子表在创建外键时也会自动创建对应的索引。
在创建索引时,可以指定在删除、更新父表时,对子表进行相应操作,包括:
RESTRICT、NO ACTION:两者均指限制在子表有关联记录的情况下父表不能更新。
CASCADE:父表在更新或者删除时,更新或者删除子表对应记录。
SET NULL:表示父表在更新或者删除的时候,子表对应的字段被SET NULL。
当某个表被其他表创建了外键参照,那么该表的对应索引或者主键禁止被删除。
在导入多个表的数据时,如果需要忽略表之前的导入顺序,可以暂时关闭外键的检查;同样,在执行LOAD DATA和ALTER TABLE操作的时候,可以暂时关闭外键约束来加快处理的速度,关闭命令是
“SET FOREIGN_KEY_CHECKS = 0;" 开启的命令值为1。
可通过SHOW CREATE TABLE 和 SHOW TABLE STATUS命令显示表外键信息。
InnoDB存储表和索引包含共享表空间存储和多表空间存储两种方式。
3、MEMORY
MEMORY存储引擎使用存在与内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件,格式是.frm。MEMORY类型的表访问非常地快,因为它地数据存放在内存中且默认使用HASH索引,但一旦服务关闭,表中的数据便会丢失。下面为创建一个MEMORY表,并从city表获得记录。
给MEMORY表创建索引时,可以指定使用HASH索引和BTREE索引:
可通过SHOW INDEX FROM tab_memory;查看索引
当不再需要MEMORY表的内容要释放被使用的内存时,可执行DELETE FROM或TRUNCATE TABLE或整个删除表(DROP TABLE)。
4、MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有数据,对MERGE表可以进行查询、更新、删除操作,但实际是对内部的MyISAM表进行的。对于MERGE类型表的插入操作,是通过INSERT_METHO子句定义插入的表,可以有3个不同的值,使用FIRST或LAST值使得插入操作被相应地作用在第一或最后一个表上,不定义这个子句或定义为NO,表示不能对这个MERGE表执行插入操作。可以对MERGE表进行DROP操作,这个操作只是删除MERGE定义,对内部地表没有任何影响。下面是MERGE表的示例:
创建payment_2006、payment_2007和payment_all,其中payment_all是前两个表的MERGE表:
分别向payment_2006和payment_2007表插入测试数据,并分别查询3个表中的记录:
可以发现payment_all表的数据是payment_2006和payment_2007表的记录合并后的结果集。下面向MERGE表中插入一条记录,由于MERGE表的定义是INSERT_METHOD=LAST,就会向最后一个表(payment_2007)中插入记录。
5、TokuDB
属于第三方存储引擎 ,高写性能高压缩率,支持事务处理的MySQL和MariaDB的存储引擎,支持大多数在线DDL操作。
TokuDB主要特性:
使用Fractal树索引保证高效的插入性能;
优秀的压缩特性,比InnoDB高近10倍;
Hot Schema Changes特性支持在线创建索引和添加、删除属性列等DDL操作。
使用Bulk Loader达到快速加载大量数据;
提供了主从延迟消除技术:
支持ACID和MVCC。
适用场景:
日志数据:因为日志通常插入频繁且存储量大。
历史数据:通常不会再有写操作,可以利用TokuDB的高压缩性进行存储。
在线DDL较频繁的场景,使用TokuDB可以大大增加系统的可用性。
四、选择合适的存储引擎
MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择该存储引擎是非常合适的。MyISAM是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。
InnoDB:用于事务处理应用程序,支持外键。如果应用程序对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询外,还包括很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致地锁定,还可以确保事务地完整提交(Commit)和回滚(Rollback),对于类似计费系统或者财务系统等对数据准确性要求较高地系统,InnoDB都是合适地选择。
MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据的环境下,可提供极快的访问。MEMORY的缺陷是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速访问结果。
MERGE:用于将一系列等同地MyISAM表以逻辑方式组合在一起,并作为一个对象引用它们,MERGE表地优点在于可以突破对单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率,这对于诸如数据仓库等VLDB环境十分适合。
————————————————
版权声明:本文为CSDN博主「王哲晓」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qushaming/article/details/82773783
一、MySQL存储引擎概述
MySQL与多数数据库不同的是包含存储引擎这一特性,用户可以根据应用的需要选择合适的存储引擎来使存储和索引数据,以及是否使用事务等。MySQL5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。其中InnoDB和BDB提供事务安全表,其他存储引擎都是非事务安全表。创建新表时在不指定存储引擎的情况下,系统会使用默认存储引擎,MySQL5.5之前默认的是MyISAM,5.5后改为InnoDB。如果要修改默认存储引擎,可以在参数文件中设置,Linux系统为/etc/my.cnf,Windows为MySQLServer5.7/my-default.ini,在[mysqld]后面增加default-storage-engine=INNODB即可。
二、MySQL存储引擎常规操作
1、查询当前数据库支持的存储引擎的两种方法:
a. mysql> SHOW ENGINES \G
b. mysql> SHOW VARIABLES LIKE 'have%';
2、将已存在的表修改成其他的存储引擎,如将表ai的存储引擎从MyISAM改为innodb:
a. mysql> alter table ai engine = innodb;
三、各存储引擎相关特性(常用)
1、MyISAM
MyISAM不支持事务和外键,访问速度快。对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用该引擎创建表。每个MyISAM会在磁盘上存储成3各文件,文件名均与表名相同,但扩展名分别是:
.frm(存储表定义)
.MYD(MYData,存储数据);
.MYI(MYIndex,存储索引);
且MyISAM的表还支持3种不同的存储格式,分别是:
静态(固定长度)表:默认的存储格式,非变长字段,每个记录都是固定长度,存储非常迅速,出现故障易恢复但占用空间多。静态表数据在存储时会按照列的宽度定义补足空格,但在应用访问时因在返回给用用之前已去掉,所以并不会得到这些空格。如果需要保存的内容后面本就带有空格,那么在返回的结果时也会被去掉。动态表:变长字段,记录不是固定长度,占用空间少,但频繁地更新和删除记录会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能,且出现故障不易恢复。压缩表:由myisampack工具创建,占据磁盘空间非常小——因每个记录是被单独压缩地,所以只有非常小地开支。MyISAM的自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引的前面激烈进行排序后递增。例如创建一个新的MyISAM类型的表autoincre_demo,自动增长列d1作为组合索引第二列,插入记录后发现自动增长列是按组合索引的第一列d2进行排序后递增。
2、InnoDB
支持事务:具有提交、回滚和崩溃恢复能力的事务安全。相比MyISAM,写的处理效率较差,且会占用更多磁盘空间以保留数据和索引。InnoDB表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值。可通过“ALTER TABLE 表名 AUTO_INCREMENT=n;”语句强制设置自动增长列的初始值,默认从1开始,但是强制的默认值是保留在内存中的,如果使用前重启数据库则该值会丢失,需要在数据库重启后重新设置。对于InnoDB,自动增长列必须是索引。如果是组合索引也必须是其第一列。MySQL中只有InnoDB支持外键,且在创建外键时,父表必须有对应的索引,子表在创建外键时也会自动创建对应的索引。
在创建索引时,可以指定在删除、更新父表时,对子表进行相应操作,包括:
RESTRICT、NO ACTION:两者均指限制在子表有关联记录的情况下父表不能更新。
CASCADE:父表在更新或者删除时,更新或者删除子表对应记录。
SET NULL:表示父表在更新或者删除的时候,子表对应的字段被SET NULL。
当某个表被其他表创建了外键参照,那么该表的对应索引或者主键禁止被删除。
在导入多个表的数据时,如果需要忽略表之前的导入顺序,可以暂时关闭外键的检查;同样,在执行LOAD DATA和ALTER TABLE操作的时候,可以暂时关闭外键约束来加快处理的速度,关闭命令是
“SET FOREIGN_KEY_CHECKS = 0;" 开启的命令值为1。
可通过SHOW CREATE TABLE 和 SHOW TABLE STATUS命令显示表外键信息。
InnoDB存储表和索引包含共享表空间存储和多表空间存储两种方式。
3、MEMORY
MEMORY存储引擎使用存在与内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件,格式是.frm。MEMORY类型的表访问非常地快,因为它地数据存放在内存中且默认使用HASH索引,但一旦服务关闭,表中的数据便会丢失。下面为创建一个MEMORY表,并从city表获得记录。
给MEMORY表创建索引时,可以指定使用HASH索引和BTREE索引:
可通过SHOW INDEX FROM tab_memory;查看索引
当不再需要MEMORY表的内容要释放被使用的内存时,可执行DELETE FROM或TRUNCATE TABLE或整个删除表(DROP TABLE)。
4、MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有数据,对MERGE表可以进行查询、更新、删除操作,但实际是对内部的MyISAM表进行的。对于MERGE类型表的插入操作,是通过INSERT_METHO子句定义插入的表,可以有3个不同的值,使用FIRST或LAST值使得插入操作被相应地作用在第一或最后一个表上,不定义这个子句或定义为NO,表示不能对这个MERGE表执行插入操作。可以对MERGE表进行DROP操作,这个操作只是删除MERGE定义,对内部地表没有任何影响。下面是MERGE表的示例:
创建payment_2006、payment_2007和payment_all,其中payment_all是前两个表的MERGE表:
分别向payment_2006和payment_2007表插入测试数据,并分别查询3个表中的记录:
可以发现payment_all表的数据是payment_2006和payment_2007表的记录合并后的结果集。下面向MERGE表中插入一条记录,由于MERGE表的定义是INSERT_METHOD=LAST,就会向最后一个表(payment_2007)中插入记录。
5、TokuDB
属于第三方存储引擎 ,高写性能高压缩率,支持事务处理的MySQL和MariaDB的存储引擎,支持大多数在线DDL操作。
TokuDB主要特性:
使用Fractal树索引保证高效的插入性能;
优秀的压缩特性,比InnoDB高近10倍;
Hot Schema Changes特性支持在线创建索引和添加、删除属性列等DDL操作。
使用Bulk Loader达到快速加载大量数据;
提供了主从延迟消除技术:
支持ACID和MVCC。
适用场景:
日志数据:因为日志通常插入频繁且存储量大。
历史数据:通常不会再有写操作,可以利用TokuDB的高压缩性进行存储。
在线DDL较频繁的场景,使用TokuDB可以大大增加系统的可用性。
四、选择合适的存储引擎
MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择该存储引擎是非常合适的。MyISAM是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。
InnoDB:用于事务处理应用程序,支持外键。如果应用程序对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询外,还包括很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致地锁定,还可以确保事务地完整提交(Commit)和回滚(Rollback),对于类似计费系统或者财务系统等对数据准确性要求较高地系统,InnoDB都是合适地选择。
MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据的环境下,可提供极快的访问。MEMORY的缺陷是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速访问结果。
MERGE:用于将一系列等同地MyISAM表以逻辑方式组合在一起,并作为一个对象引用它们,MERGE表地优点在于可以突破对单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率,这对于诸如数据仓库等VLDB环境十分适合。————————————————版权声明:本文为CSDN博主「王哲晓」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/qushaming/article/details/82773783