Marco's Java 之【MySQL数据库结构浅析】

MySQL数据库结构

MySQL数据库通常包含了三层结构：
第一层主要负责的是服务于C/S程序(Navicat端或者是JDBC客户端Clients) 或者是连接处理，身份验证，安全性认证等等。需要注意的是在连接处理这一层，因为如果多个用户同时访问数据，那么每个连接必然都是并行的关系，在连接进程模块中主要的就是处理这一个个进程，且每一个用户的进程之间是没有关系的，否则可能会导致用户的数据紊乱。
第二层是MySQL的核心部分，称之为SQL Layer，在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括==权限判断， 命令的解释与分发，查询优化， 查询内容的缓存，日志记录模块的处理以及所有内置的函数(如日期,时间,数学运算,加密)==等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器以及视图等。
第三层主要是包含了MySQL的引擎部分(StorEngine Layer),他是有多个引擎组成的，在MySQL 5.0以前，默认的引擎为MyIASM，而由于时代的变迁和需求的变更，在5.0往后默认的引擎都为InnoDB。由于MySQL属于开源产品，还支持用户自定义引擎。

---

MySQL数据库引擎

ISAM

ISAM是一个定义明确且历经时间考验的数据表格管理方法(索引序列访问法)，它
在设计之时就考虑到=数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作
的速度很快，而且不占用大量的内存和存储资源。

ISAM的不足之处:

• 不支持事务处理
• 不能够容错

如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正
在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特
性，MYSQL能够支持这样的备份应用程序。此引擎主要用于优化查询。
注意：
1.主要用于查询，且要经常备份
2.不支持事务，涉及到事务的业务，不应该选择使用ISAM

MyISAM

MyISAM是MySQL的ISAM扩展格式（MySQL5.5之前版本的缺省数据库引擎）数据库引擎。
除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表
格锁定的机制(表锁)，来优化多个并发的读写操作(读数据用共享锁，写数据用排它锁(类似于synchronized同步锁，一次只能有一个线程访问数据))，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间
(DML操作会产生大量磁盘碎片)。
MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。
MYISAM的核心功能就是快速读取操作，因此大多数虚拟主机提供商和INTERNET平台提供商
只允许使用MYISAM格式。MyISAM同ISAM一样都有一个重要缺陷就是不能在表损坏后恢复数据。

MyISAM引擎使用注意：
1.必须经常使用Optimize Table命令清理空间；
2.必须经常备份所有实时数据。工具有用来修复数据库文件的MyISAMCHK工具和
用来恢复浪费空间的 MyISAMPACK工具。
3.不支持事务。数据越多，写操作(DML操作)效率越低。因为要维护数据和索引信
息。（索引列越多，相对效率越低。）
使用该数据库引擎会在安装目录下的data目录生成三个文件：
.frm:表结构信息
.MYD:数据文件
.MYI:表的索引信息

InnoDB

InnoDB包括了对 事务处理 和 外键 的支持
事务处理：InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）的存储引擎。

外键 InnoDB也支持FOREIGN KEY强制（支持外键的级联更新）。在SQL查询中，你可以自由地
将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓
存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间
可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个
表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作
系统上。

使用该数据库引擎会在安装目录下的data目录生成三个文件：
.frm:表结构信息
.idb:数据文件包含索引内容

除了上面的数据引擎外，MySQL还有诸如以下引擎
Memory： 将数据存储在内存中的存储引擎，Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQLCrash或者主机Crash(崩溃)之后，Memory的表就只剩下一个结构了。Memory存储引擎实现页级锁定，但目前可以使用Redis等替代它
***NDBCluster：***主要用于MySQLCluster分布式集群环境
Merge： 也称为MRG_MyISAM引擎，相较于MyISAM，它通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。因此要创建MERGE表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致
ARCHIVE： 主要用于通过较小的存储空间来存放过期的很少访问的历史数据，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。类似于飞机，或者公交上的黑盒子，里面的数据可能会存放很长一段时间的记录，但是不支持外界修改，外界仅能查询。
BLACKHOLE： 不管我们写入任何信息，都是有去无回。这样的好处就是避免外面人员去查看数据内容。
CSV： 主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件

常用存储引擎对比

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	表锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入速度	高	低	高	高	高
支持外键		支持

InnoDB与MyISAM区别：
1.InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语句都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语句放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败(navicat的设计–选项中换引擎)；
3. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
4. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高
5. InnoDB是聚集索引，使用 B-Tree 作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构）。MyISAM是非聚集索引，也是使用B-Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
6. MyISAM需要定期的管理、备份文件，手动去清理文件碎片，且系统崩溃后恢复起来很困难

补充：行锁和表锁

表锁： 不会出现死锁，发生锁冲突几率高，并发低。

行锁： 会出现死锁，发生锁冲突几率低，并发高。
行锁又分为共享锁和排它锁
共享锁（读锁）：当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。【select * from u_user lock in share mode】
排它锁（写锁）：当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁，insert ，delete ， update在事务中都会自动默认加上排它锁。【select * from u_user for update】

注意：
1.行锁必须有索引才能实现，否则会自动锁全表
2.两个事务不能锁同一个索引

总结

由于InnoDB的高性能以及对事务的支持，支吃外键等独有有的功能，使得InnoDB成为目前MySQL数据库中主要使用到的引擎，但是这不代表着其他的引擎没有作用，因此，在一个项目开发中，对数据库引擎的选择也至关重要。
1.例如项目涉及到电商，银行项目等等对数据要进行严控必须使用到事务特性的情况下，我们必然要选择InnoDB，
但是如果我们只涉及到查询，很少或者不怎么涉及到数据的修改和频繁的读写，那么此时使用MyISAM的效率更佳。
2.由于MyISAM本身的特性，在系统崩溃后数据难以恢复，因此也要考虑到这一点在项目中是否能被接受