MySQL存储引擎

MySQL存储引擎

1、存储引擎概述
作为可插拔式的组件提供,MySQL服务软件自带的功能程序，处理表的处理器,不同的存储引擎有不同的功能和数据存储方式
MySQL 5.0/5.1—>MyISAM
MySQL 5.5/5.6—>InnoDB
MySQL服务的功能组件：

	1 管理工具 安装数据库服务软件后提供的命令
	2 连接池   验证用户连接和系统硬件资源
	3 sql接口  负责把命令传递给mysqld进程处理
	4 分析器  检查命令的语法格式 和 用户的访问权限
	5 优化器  对命令做优化再执行
	6 查询缓存 占用系统的物理内存空间，存储曾经查找过的数据
		   默认情况MySQL服务的查询缓存没有开启
	7 存储引擎 支持多种存储引擎，有各自的存储方式和功能
	8 文件系统 (硬盘)

2、存储引擎的选择

1）myisam特点：表级锁
支持表级锁
不支持事务、事务回滚、外键
每表3个文件：表名 .frm（表结构） 表名.MYl（索引信息）表名.MYD（表数据）
2）innodb特点：行级锁、事务回滚
支持行级锁定
支持事务、事务回滚、外键
每个表2个文件：表名.frm 表名.ibd
事务日志文件 记录对innodb存储引擎的表执行的sql命令

3）专业术语：

锁粒度：加锁的范围
行级锁: 仅仅对被访问的行分别加锁，其他行不加锁
表级锁: 只要是对表访问，无论访问的是表的1行还是多行，都会给整张表加锁。
锁类型：
读锁（共享锁）：对表做select访问，MySQL服务会给表加读锁，加读锁后，只允许再连接执行查询访问。
写锁（排它锁或互斥锁）：对表做insert delete update 访问,mysql服务会给表加写锁。加了写锁后，再连接的连接读写访问都不可以。
加锁的目的：解决并发访问，写冲突问题

mysql> show status like "table_lock_waited"; 查看锁状态
事务：连接服务器后执行各自sql，操作断开连接。这个过程被称为事务。；
事务回滚：把错误的操作恢复到操作错误之前的状态
事务日志：iblogfile0  ib_logfile1  ibdatal
建表时如何确定表使用哪种存储引擎比较合适？
查询数据select操作多的表适合使用myisam存储引擎，节省内存资源，一次性锁表。
存储数据insert/delete/update操作多、带外键的表适合使用innodb存储引擎，好处并发访问量大

3、修改存储引擎

1）列出可用的存储引擎类型
> show engines；
2）建表时指定表使用的存储引擎engine=memory;
> create database gamedb;
> create table gamedb.a(id int);
> create table gamedb.b(id int) engine=memory;
> create table gamedb.c(id int) engine=myisam;
> system ls /var/lib/mysql/gamedb/
a.frm  a.ibd  b.frm  c.frm  c.MYD  c.MYI  db.opt    
存储区别  frm表结构 ibd表数据
3）修改表存储引擎 alter ...engine=myisam;
> alter table gamedb.b engine=myisam;   修改engine
4）修改默认引擎default-storage-engine="myisam"
~]# vim /etc/my.cnf
[mysqld]
default-storage-engine="myisam"
> create table gamedb.d(id  int);
> show create table gamedb.d \G
ENGINE=MyISAM DEFAULT 

4、innodb事务回滚举例 set autocommit=off;

tty1 
> set autocommit=off; 				#关掉自动提交，默认自动
> show variables like "autocommit";
| autocommit    | OFF   |
tty2
> show variables like "autocommit";
| autocommit    | ON    |
tty1
> create database db5;
> create table db5.a(id int)engine=innodb;
> desc db5.a;
tty2
> desc db5.a;
tty1
> insert into db5.a values(1);
> select  * from db5.a;
tty2
> select  * from db5.a;  		#tty1不commit，tty2看不到数据
tty1
> commit ；
tty2
> select  * from db5.a;  看到数据了
tty1
> delete from  db5.a;
> select  * from db5.a;
tty2
> select  * from db5.a;  看到数据了
tty1 
> commit; 提交
> select  * from db5.a;
tty2
> select  * from db5.a;  看不到数据了
tty1
> insert into db5.a values(88);
> commit;
> select  * from db5.a;
tty2
> select  * from db5.a;
tty1
> insert into db5.a values(99);
> update db5.a set id=1010;
> delete from db5.a;
tty2
> select  * from db5.a;  依然看到的是88 的记录
tty1		
> rollback;		#使用回滚，数据回到上次commit后
> select  * from db5.a;然看到的是88 的记录
tty2
> select  * from db5.a;  依然看到的是88 的记录