来源:http://t.cn/RnU0h2o
MyISAM
: 不支持事务,表级锁。
InnoDB
: 支持事务,支持行级锁,事务ACID
。分三层:客户端->服务层->存储引擎
MySQL
是插件式的存储引擎,其中存储引擎分很多种。只要实现符合mysql存储引擎的接口,可以开发自己的存储引擎! MySQL5.5
及之后版本默认的存储引擎:InnoDB
。
show variables like 'innodb_file_per_table
如果innodb_file_per_table 为 ON 将建立独立的表空间,文件为tablename.ibd;
如果innodb_file_per_table 为 OFF 将数据存储到系统的共享表空间,文件为ibdataX(X为从1开始的整数);
.frm
:是服务器层面产生的文件,类似服务器层的数据字典,记录表结构。
1.1 系统表空间无法简单的收缩文件大小,造成空间浪费,并会产生大量的磁盘碎片。
1.2 独立表空间可以通过optimeze table
收缩系统文件,不需要重启服务器也不会影响对表的正常访问。
2.1 如果对多个表进行刷新时,实际上是顺序进行的,会产生IO瓶颈。
2.2 独立表空间可以同时向多个文件刷新数据。
强烈建立对Innodb 使用独立表空间,优化什么的更方便,可控。
1、使用mysqldump 导出所有数据库数据(存储过程、触发器、计划任务一起都要导出 )可以在从服务器上操作。
2、停止MYsql 服务器,修改参数(my.cnf加入innodb_file_per_table),并删除Inoodb相关文件(可以重建Data目录)。
3、重启MYSQL,并重建Innodb系统表空间。
4、 重新导入数据。
或者 Alter table
同样可以的转移,但是无法回收系统表空间中占用的空间。
Innodb
是一种事务性存储引擎。ACID
特性。Redo Log
和Undo Log
Redo Log:实现事务的持久性(已提交的事务)。 Undo Log:未提交的事务,独立于表空间,需要随机访问,可以存储在高性能io设备上。
Undo
日志记录某数据被修改前的值,可以用来在事务失败时进行rollback
;Redo
日志记录某数据块被修改后的值,可以用来恢复未写入data file
的已成功事务更新的数据。
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关
将table_name加表级锁命令:lock table table_name write
; 写锁会阻塞其它用户对该表的‘读写’操作,直到写锁被释放:unlock tables
;
(1)阻塞是由于资源不足引起的排队等待现象。 (2)死锁是由于两个对象在拥有一份资源的情况下申请另一份资源,而另一份资源恰好又是这两对象正持有的,导致两对象无法完成操作,且所持资源无法释放。
参考条件:
Innobd
免费在线备份)总结:Innodb大法好。 注意:尽量别使用混合存储引擎,比如回滚会出问题在线热备问题。
确定可以使用的内存上限。
内存的使用上限不能超过物理内存,否则容易造成内存溢出;(对于32位操作系统,MySQL只能试用3G以下的内存。)
确定MySQL的每个连接单独使用的内存。
sort_buffer_size #定义了每个线程排序缓存区的大小,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存);
join_buffer_size #定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲,导致一个查询产生了多个连接缓冲;
read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小,MySQL有查询需要时会为其分配内存,其必须是4k的倍数;
read_rnd_buffer_size #索引缓冲区大小,MySQL有查询需要时会为其分配内存,只会分配需要的大小。
注意:以上四个参数是为一个线程分配的,如果有100个连接,那么需要×100。
MySQL数据库实例: ①MySQL是单进程多线程(而oracle是多进程),也就是说
MySQL
实例在系统上表现就是一个服务进程,即进程; ②MySQL实例是线程和内存组成,实例才是真正用于操作数据库文件的; 一般情况下一个实例操作一个或多个数据库;集群情况下多个实例操作一个或多个数据库。
如何为缓存池分配内存:
Innodb_buffer_pool_size
,定义了Innodb所使用缓存池的大小,对其性能十分重要,必须足够大,但是过大时,使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中;
总内存-(每个线程所需要的内存*连接数)-系统保留内存
key_buffer_size
,定义了MyISAM所使用的缓存池的大小,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间;
select sum(index_length) from information_schema.talbes where engine='myisam'
注意:即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表。
max_connections
控制允许的最大连接数, 一般2000更大。
不要使用外键约束保证数据的完整性。
从上到下: