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,''),(0,''),(null,''); 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支持表锁不再介绍.

转载需要标明出处!

上一篇:MySql中存储引擎MyISAM与InnoDB区别于选择


下一篇:【SqlServer】聚集索引与主键、非聚集索引