mysql学习之-存储引擎

Mysql存储引擎

一、mysql存储引擎概述

　　1、mysql5.0支持的存储引擎包括MyISAM、InnoDB、DBD、MEMORY、MERGE、EXAMPLE、NDB Cluser、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和DBD提供事务安全表，其他都是非安全事务。

　　Mysql5.5之前的默认存储引擎是MyISAM，mysql5.5之后默认是存储引擎是InnoDB。

　　查询当前数据库支持的存储引擎，

方法一：show ENGINES;

方法二：SHOW VARIABLES LIKE 'have%';

其中，如果value显示为“DISABLED”的记录表示支持存储引擎，但是数据库启动时候被禁用。

2、创建表如果不使用默认存储引擎，可以使用engine关键字设置存储引擎，如：

CREATE TABLE ai(
  i BIGINT(20) NOT NULL auto_increment,
  PRIMARY key (i)
) ENGINE=myISAM DEFAULT charset=gbk;

　　修改存储引擎：

　　　　ALTER TABLE ai ENGINE=INNODB;

　　查看表使用的存储引擎：

　　　　SHOW CREATE TABLE ai;

二、各种存储引擎的特性

几个常用存储引擎的特点

下面我们重点介绍几种常用的存储引擎并对比各个存储引擎之间的区别和推荐使用方式。

 三、常见存储引擎说明

1、mylSAM

　　使用范围：对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用此引擎。

　　缺点：MyISAM不支持事务、也不支持外键；

　　优点：访问速度快。

　　注意：mylSAM类型的表可能会损坏，损坏后的表可能不能被访问，并给出提示：修复、访问后返回错误的结果。

　　检查方式：CHECK TABLE ai;

　　修复损坏：repair table ai;

（1）3种不同的存储格式：

　　1）静态表(固定长度)：默认存储格式，非变长字段(固定长度)，不足的会以空格补足；但访问时会自动去掉空格。

　　　　优点：存储非常快，容易缓存，出现故障容易恢复。缺点：占用的空间通常比动态表多。注意：插入数据只有前面空格，后面没有空格，才会出现前面空格保留。

　　2）动态表(变长字段)：优点：占用空间相对较少。缺点：频繁地更新和删除记录会产生碎片，出现故障不易恢复。

　　　　定期执行optimize table，或者myisamchk-r命令来改善性能。

　　3）压缩表：占据非常小的磁盘空间，每条记录都会被单独压缩，只有非常小的访问开支。

2、InnoDB

　　特性：具有提交、回滚和崩溃恢复能力的事务安全，但是写的处理效率差，并且会占用更多的磁盘空间保留数据和索引。

　　（1）自动增长列

　　　　手动插入自增长列插入值为空、0，而实际插入的将是自动增长后的值。强制设置自增长列初始值：alter table *** auto_increment=n，默认值从1开始，但是强制的默认值存储在内存中。重启后，会丢失，需要重新设置。

　　查询当前线程最后插入记录（自增长）使用值：select last_insert_id();如果一次插入多条记录，返回的自增长值是第一条记录。

　　注意：自增长列必须是索引，必须是组合索引的第一列。但是对于mylSAM存储引擎，自增长列可以是组合索引的其他列，并且自增长列值按照组合索引的前面几列进行排序后增加。

例如：

CREATE TABLE autoincre_demo(
 d1 SMALLINT NOT NULL auto_increment,
 d2 SMALLINT NOT null,
 NAME VARCHAR(10),
 INDEX(d2,d1)
)ENGINE=myisam;

INSERT INTO autoincre_demo(d2,NAME) VALUES
(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4'),
(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4')；

　　SELECT * FROM autoincre_demo;

　　说明：myisam存储引擎的表autoincre_demo,自增长列d1作为组合索引的第二列，对该表插入的一些记录后，可以发现自增长列按照组合索引的第一列d2进行排序后增长的。

（2）外键约束

　　只有InnoDB支持外键。

　　注意：要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

例子：country父表：

CREATE TABLE country(
  country_id SMALLINT UNSIGNED NOT NULL auto_increment,
  country VARCHAR(50) NOT null,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (country_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

City子表：

CREATE TABLE city(
  city_id SMALLINT UNSIGNED NOT NULL auto_increment,
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT UNSIGNED NOT null,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (city_id),
  KEY idx_fk_country_id(country_id),
  CONSTRAINT fk_city_country FOREIGN KEY (country_id)
  REFERENCES country(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT charset=utf8;

　　子表city的外键指定：ON DELETE RESTRICT ON UPDATE CASCADE，表示在主表删除记录时，如果子表有对应的记录，则不许删除；主表更新记录时，如果子表有对应记录。则子表对应更新。

　　在创建索引时，可以指定在删除、更新父表时，对子表进行相应操作，包括restrict、cascade、set null、no action。

　　其中，restrict和no action相同，是指限制在子表有关联记录的情况下父表不能更新；cascade表示父表在更新或删除时，更新或者删除子表记录；set null则表示父表在更新或者删除时候，子表对应字段被set null。注意：后两种情况要谨慎，可能会导致数据丢失。

　　关闭外键约束：set foreign_key_checks=0;

（3）存储方式

　　其他存储引擎可以自行查询。

四：如何合适选择存储引擎

　　分享常用存储引擎适应的环境，下面只是建议，仅供参考：

　　（1）MyISAM：以读、插入操作为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不高，这种情况下适合该种存储引擎。经常使用在web、数据仓库和其他应用环境下。

　　（2）InnoDB：对事务完整性有比较高的要求，在并发条件下要求数据的一致性，数据除了插入、查询之外，还有很多更新、删除操作，此种情况下适合该种存储引擎。除了有效降低由于删除和更新导致的锁定，还确保事务的完整提交、回滚，还支持外键。

　　（3）MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问；通常用于更新不太频繁的小表，用以快速得到访问；数据库异常终止后，表中的数据是可以恢复的。缺点：1）对表大小有限制，太大的表无法缓存在内存中。

　　（4）Merge:可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，有效改善merge表的访问效率。如：数据仓储等VLDB环境。