数据库存储引擎
1.什么是存储引擎
MySQL中的数据用各种不同的技术存储在文件（或者内存）中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的相关功能在MySQL中称为存储引擎（也称为表类型）。
MySql支持多个存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎：
-
ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。
-
MyISAM管理非事务表，是ISAM的扩展格式。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。它提供高速存储和检索，以及全文搜索能力，受到web开发的青睐。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎。
-
MEMORY存储引擎提供“内存中”表，被正式确定为HEAP引擎，也处理非事务表。HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MyISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。
-
InnoDB和BDB（BerkleyDB）存储引擎提供事务安全表。尽管要比ISAM和MyISAM引擎慢很多，但是InnoDB和BDB包括了对事务处理和外键的支持，这两点都是前两个引擎所没有的。
-
NDB Cluster是被MySQLCluster用来实现分割到多台计算机上的表的存储引擎，当前只被Linux， Solaris和Mac OS X支持。
2.如何更换引擎？
Globle：一种最简单的方法就是更改服务器配置，直接将其设置成你所需要的引擎。这个在win下通过更改服务器安装目录下的mysql.ini或my.ini中的default-storage-engine项即可，也可以通过运行MySQL ServerInstance Configuration Wizard做简单的设置。
PerTable:除了全局的方法外，还有一种更灵活的配置方法，那就是按表来设置引擎，这样我们就可以把那些需要用到事务处理的表设置成InnoDB，其他设置成MyISAM,将性能提升到极致，设置方法也比较简单：
创建一个新表时，可以通过在CREATE语句中ENGINE或TYPE选项来告诉MySQL要创建什么类型的表：
CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;
如果省略掉ENGINE或TYPE选项，默认的存储引擎被使用。当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储引擎替代MyISAM存储引擎作为默认。当不可用的类型被指定时，自动用InnoDB表来替代。
还可以把表从一个类型转到另一个类型，使用ALTERTABLE语句：
ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;
当不清楚当前数据库中各表的引擎时可以使用SHOW TABLE STATUS FROMDBname来查看。
3.如何选择存储引擎？
关于这个问题，我们需要考虑每个存储引擎提供了哪些不同的核心功能，一般把这些核心功能分为4类：支持的字段和数据类型、锁定类型、索引和事务处理。
- 支持的字段和数据类型
虽然所有这些引擎都支持通用的数据类型，例如整型、实型和字符型等，但是，并不是所有的引擎都支持其它的字段类型，特别是BLOG（二进制大对象）或者TEXT文本类型。 - 锁定类型
锁定机制主要是为了防止多个处理同时更新同一个数据。不同的存储引擎支持不同级别的锁定：表锁定、页锁定和行锁定。
表锁定：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。支持最多的就是表锁定，MyISAM和MEMORY支持这种锁定。MySQL的表级锁有两种模式：表共享读锁和表独占写锁。对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！
行锁定：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。InnoDB表进行行级锁定。
页锁定：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。BerkeleyDB引擎支持页锁定。 - 索引
建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术。有些存储引擎根本不支持索引。 - 事务处理
事务处理功能通过提供在向表中更新和插入信息期间的可靠性。
事务表和非事务表
MySQL事务表是MySQL数据库的重要概念，下面就为你详细介绍MySQL事务表和非事务表的区别，希望可以让您对MySQL事务表有更多的了解。
MySQL事务表支持将批处理当做一个完整的任务统一提交或回滚，即对包含在事务中的多条语句要么全执行，要么全部不执行。非事务表则不支持此种操作，批处理中的语句如果遇到错误，在错误前的语句执行成功，之后的则不执行。
比如常用操作中，你将A表中的100条数据导入B表，导入后删除A表中的那部分数据，但如果中途发生意外事务表
可保证操作正常，但非事务表可能会出现数据异常.
MySQL事务表有表锁与行锁
非事务表则只有表锁
存储引擎中支持自动增长列AUTO_INCREMENT。值不能为空，值必须唯一。规定自增列必须为主键。
一些语句
"CREATE DATABASE IF NOT EXISTS `$dbname` DEFAULT CHARACTER SET ".DBCHARSET
DROP DATABASE
DROP TABLE IF EXISTS `tp_company_staff`;
CREATE TABLE `tp_company_staff` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`companyid` int(10) NOT NULL,
`token` varchar(30) NOT NULL DEFAULT '',
`name` varchar(30) NOT NULL DEFAULT '',
`username` varchar(20) NOT NULL DEFAULT '',
`password` varchar(40) NOT NULL DEFAULT '',
`tel` varchar(11) NOT NULL DEFAULT '',
`time` int(10) NOT NULL,
PRIMARY KEY (`id`), //这么写可以设置多字段主键
KEY `companyid` (`companyid`),
KEY `token` (`token`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
总结约束条件：
单字段主键：直接在字段后面加上 PRIMARY KEY
多字段主键：属性定义完后统一加上 PRIMARY KEY (`id`,``,``),
设置表的外键：FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) ;如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P,...,) REFERENCES Persons(Id_P,...,)
非空约束：`companyid` int(10) NOT NULL,指插入字段的值不能为null
唯一性约束：UNIQUE
属性值自动增加：AUTO_INCREMENT
设置属性的默认值：DEFAULT ''
空值和null的区别：
空 (NULL) 值表示数值未知。空值不同于空白或零值。没有两个相等的空值。比较两个空值或将空值与任何其它数值相比均返回未知，这是因为每个空值均为未知。
在写入数据的时候，空字符串'' 也是一个确定的值，所以就算你定义了 NOT NULL 也可以被写入
相信很多用了mysql很久的人，对这两个字段属性的概念还不是很清楚，一般会有以下疑问：
1、我字段类型是not null，为什么我可以插入空值
2、为毛not null的效率比null高
3、判断字段不为空的时候，到底要 select * from table where column <> '' 还是要用 select * from table where column is not null 呢。
1、空值是不占用空间的
2、mysql中的NULL其实是占用空间的
NOT NULL 的字段是不能插入“NULL”的，只能插入“空值，NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。查看表结构：
DESCRIBE 表名;
SHOW CREATE TABLE 表名 \G
修改表名：ALTER TABLE tabname1 RENAME tabname2;
修改字段数据类型：ALTER TABLE tabname MODIFY name VCHAR(30);
修改字段名：ALTER TABLE tabname CHANGE col1 col2 newtype;
增加字段：ALTER TABLE table_name ADD column_name datatype (约束条件) [FIRST | AFTER col2];
删除字段：ALTER TABLE table_name DROP COLUMN column_name
修改字段的排列位置：ALTER TABLE table_name MODIFY column_name datatype FIRST | AFTER col2;
更改表的存储引擎：ALTER TABLE table_name ENGINE=...;
删除表的外键约束：ALTER TABLE table_name DROP FOREIGN KEY keyname;
删除表：DROP TABLE 表名称
删除被其它表关联的父表：先删除外键约束，再删除这张表
扫一扫
6940
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
