Mysql存储引擎的选择
Mysql存储引擎概述
mysql的存储引擎是插件式的,用户可以根据需求选择如何存储和索引数据是否使用事务等.
Mysql支持多种存储引擎,用户可以选择不同的引擎来提高应用的效率,灵活的存储方案,存储的可定制型很强.
这里只要介绍常用的MyISAM和innoDB引擎.
当然还有其他很多引擎比如MEMORY/MERGE/EXAMPLE等等.
Mysql的默认引擎:
- Mysql5.5之前用的是MyISAM
- Mysql5.5以后用的是InnoDB
查看数据库支持的引擎:
mysql> show engines \G
*************************** 3. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 9. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO 9 rows in set (0.00 sec)
创建表时可以通过ENGINE关键字来设置表的引擎
CREATE TABLE ai( i BIGINT(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY(i)) ENGINE=MyISAM
修改ai表的引擎为InnoDB
mysql> alter table ai engine = innodb;
查看表的创建信息:可以看到引擎已经被修改成InnoDB
mysql> show create table ai \G *************************** 1. row *************************** Table: ai Create Table: CREATE TABLE `ai` ( `i` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
MyISAM
MyISAM基本介绍
MyISAM不支持事务,也不支持外键,但是他的优势是访问的速度快,对事务的完整性没有要求或者以select 和insert为主的基本上都可以选择该引擎来创建表.
每个MyISAM在磁盘上存储成3个文件,他的文件名都和表名相同,但扩展名分别是:
- .frm(存储表定义)
- .MYD(MYDdata,存储数据)
- .MYI(MYIindex,存储索引)
MyISAM的3钟存储格式
MyISAM的表支持3中不同的存储格式
- 静态表(固定长度)
- 动态表
- 压缩版
静态表
静态表是默认的存储格式.静态表中的字段都是不可变长字段,这样每个记录都是固定长度的.
这种存储方式的优点是
- 存储非常迅速
- 容易缓存
- 出现故障容易恢复
缺点:
- 占用的空间通常比较多
静态表在存储的时候会按照列的宽度定义补足空格,但是在访问的时候这些空格在返回给应用之前已经去掉.
注意
如果保存的内容后面有空格,那么在返回结果的时候也会被去掉,因为静态表是默认的存储格式
例子:
CREATE TABLE sam (name char(10)) ENGINE=myisam INSERT into sam VALUES('abcd'),('ab cd'),(' abcd'),('abcd ') mysql> select name,length(name) from sam; +--------+--------------+ | name | length(name) | +--------+--------------+ | abcd | 4 | | ab cd | 6 | | abcd | 6 | | abcd | 4 | +--------+--------------+ 4 rows in set (0.06 sec)
动态表
动态表包含变长字段,记录不是固定长度的,这样的有点是占用空间相对较少频繁的更新和删除记录会产生碎片,并且在出现故障时,恢复相对比较难
压缩表
压缩表由myisampack创建,占据非常小的磁盘空间,因为每个记录是被单独压缩的,所有只有非常小的访问开支.
InnoDB
InnoDB介绍
innoDB存储引擎提供了具有提交,回滚和崩溃恢复能力的事务安全.但是对比MyIsam的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间来保存数据和索引
自动增长列
InnoDB表的自动增长列可以手工插入,但是插入的值如果是0或者空,则实际插入的讲师自动增长后的值
mysql> CREATE TABLE auto_demo( -> i int not null auto_increment, -> name VARCHAR(10),PRIMARY KEY(i) -> ); mysql> INSERT into auto_demo VALUES(1,'1'),(0,'1'),(null,'1'); mysql> select * from auto_demo; +---+------+ | i | name | +---+------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+------+
对于InnoDB表,自动增长列必须是索引.
外键约束
Mysql支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引.
--- 每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任 ----主表 CREATE TABLE ClassCharger( id TINYINT PRIMARY KEY auto_increment, name VARCHAR (20), age INT , is_marriged boolean -- show create table ClassCharger: tinyint(1) ); INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0), ("丹丹",14,0), ("歪歪",22,0), ("姗姗",20,0), ("小雨",21,0); ----子表 CREATE TABLE Student( id INT PRIMARY KEY auto_increment, name VARCHAR (20), charger_id TINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致 -- [ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ) ENGINE=INNODB; INSERT INTO Student(name,charger_id) VALUES ("alvin1",2), ("alvin2",4), ("alvin3",1), ("alvin4",3), ("alvin5",1), ("alvin6",3), ("alvin7",2); DELETE FROM ClassCharger WHERE name="冰冰"; INSERT student (name,charger_id) VALUES ("yuan",1); -- 删除居然成功,可是 alvin3显示还是有班主任id=1的冰冰的; -----------增加外键和删除外键--------- ALTER TABLE student ADD CONSTRAINT abc FOREIGN KEY(charger_id) REFERENCES classcharger(id) on DELETE RESTRICT ON UPDATE CASCADE; ALTER TABLE student DROP FOREIGN KEY abc;
介绍在创建索引时,可以指定在删除更新父表时,对子表进行的相应操作的几种方式
- RESTRICT和NO ACTION
- RESTRICT和NO ACTION是相同的,是指限制在子表有关联记录得到情况下父表不能更新
- CASCADE
- 父表在更新或者删除时,更新或者删除子表对应记录
- SET NULL
- 表示父表在更新或者删除的时候,子表对应的字段被设置成 SET NULL.
在创建表的时候外键指定是
ON DELETE RESTRICT ON UPDATE CASCADE
表在删除的时候有RESTRICT 的性质,在更新的时候有CASCADE的性质.
存储方式
InnoDB的存储表和索引有两种方式
- 使用共享表空间存储
- 这种方式创建的表的表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中.
- 使用多表空间存储
- 这种方式创建的表的表结构保存在.frm文件中,但是每个表的数据和索引都单独保存在.ibd中,如果是分区表,则每个分区对应单独的.ibd文件,文件名是"表名+分区名"
如何选择合适的引擎
- MyIsam
- 如果应用是以度操作和插入操作为主的,只有很少的更新和删除操作,并且对事务的完整性,并发性要求不高的,那么选择MyIsam
- Innodb
- 用于事务处理应用程序,支持外键,如果对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了查询以外,包含很多的更新.删除操作,那么Innodb存储引擎是比较合适的选择
当然还有Innodb支持行锁和表锁,
myisam支持表锁不再介绍.
转载需要标明出处!
