《高性能 MySql》 第一章 Mysql架构（笔记）

 

1.MySql的逻辑架构

最顶层的各种服务并非Mysql独有。他们是许多基于网络的客户端服务器工具或服务器都需要的服务，比如连接处理，授权认证，安全等。

第二层值得关注。它包括了MySql的大多数核心内容，比如查询解析，分析，优化，缓存以及所有内建函数（如时间处理，数学，加密等）的代码。各种存储引擎提供的功能也集中在这层，如存储过程，触发器，视图等。

第三层包含了存储引擎，存储引擎负责存储和提权所有存放在MySql中的数据。存储引擎不会进行SQL解析，也不会互相通信，他们只是简单的响应服务器的请求。

 
其中应用层为所有RDBMS用户提供用户接口，逻辑层包括了所有核心功能的实现，物理层则负责将数据存储在硬件设备上。
图中右侧更为具体的描述了逻辑层的组成，查询处理子系统、事务管理子系统、恢复管理子系统和存储管理子系统共同组成了MySQL的逻辑层。相信Storage Engine的位置是在Storage Management处，既Storage Engine属于Storage Management子系统的一部分

 

为了让思路更清晰一些，下面给出一幅比较全面的体系结构图（或更确切的说是流程图，只是忽略了反馈）


 

2.MySql的并发控制

每种MySQL存储引擎都可以实现独有的锁策略（Lock Policy）或锁粒度（Lock Granularitey）。在存储引擎设计中，锁管理（Lock Management）事个非常重要的议题。将锁粒度调整到某一水平，就能为某种应用提供更佳的性能，由于MySQL提供了多种引擎，所以不需要一个通用的解决方案，下面介绍两种重要的锁策略：

    表锁（Table Lock）

    MySQL支持大多数基本的锁策略，其中开销最小的所策略是表锁，他将整个表加锁，当一个用户对表进行写操作时，用户可以获得一个写锁，写锁禁止其他的用户读写操作。写锁比读锁的优先级更高，即使有读操作用户已排在队列中，一个被申请的写锁仍可以排在所队列的前列。

    行级锁（Row Lock）

    行级锁可以支撑最大的并发处理，同时也带来最大的锁开销。行级锁由存储引擎实现，而不是由MySQL服务器实现。服务器完全不了解存储引擎里的锁实现方式

2.MySql的存储引擎

 

2.1获取表的存储引擎：

 

•     show table status like ‘%user%’ \G

 

这个\G可以让数据格式按照（key-value模式）格式。

2.2  MyISAM

 

•   MYISAM对整张表进行加锁，而不是行。用户在运行select查询时，可在一张表内插入新行（也称之为‘并发插入’）
•   修复方法：

 

check table user;

repair table user;

 

•  索引的时候对于text和bolb只支持前500个字符。
•     支持Delay key write，即主键变动不会马上写入磁盘以提升性能，因此在重启MyISAM引擎后有必要check myisam
•    使用myisampack工具可将MyISAM数据库进行压缩，压缩后具有更小的存放空间和性能，但是只读的。
•     MyISAM支持Merge Engine，将其他MyISAM表虚拟的合并到一个表中。

 

2.3  InnoDB

InnoDB主要用于“事务型”处理
InnoDB支持外键。
InnoDB支持“Raw Disk分区”直接作为数据的存储空间
使用MVCC进一步提升锁的并发性。
默认REPEATABLE READ事务隔离级别。
它使用Cluster Index，查询主键的速度非常快
非主键的索引一定会包含主键索引，因此要注意主键的大小。
InnoDB创建、修改索引的时间比MyISAM消耗的要大。
InnoDB当初是基于“低磁盘空间、单CPU、有限内存”的条件构建的，现在面临规模上拓展的难题。

2.4  Memory Engine
比MyISAM快一个数量级左右，但是重启后会丢失。
主要用于Cache，Map，中间处理结果等类似操作
表级锁定
它不同于临时表

2.5  Archive Engine
不支持索引，只支持select和insert
自动用zlib压缩，低I/O消耗
主要用途：Replication（MySQL复制集群）中的Master，因为slave可以用任意其他形式，复制的时候非常有优势。

2.6  CSV Engine
支持将csv文件格式（以；分隔，好多通讯录导出的那个玩意儿）作为数据库。。
一般用于MySQL和其他支持csv格式程序的数据交换。

2.7  The Federated Engine
目前仍在实验中，一般用于Hack的方式解决Rplication中遇到的问题。

2.8  The Blackhole Engine（黑洞引擎）
丢弃所有的insert操作不存储，但是仍记录下日志。。这个很搞笑
据说也是Rplication和审计日志的时候用到

2.9  NDB Engine
这个是索爱开发的（没错，就是那个索爱手机）
为高速（实时系统），冗余和负载均衡而设计。
我理解这是个分布式数据库引擎：
每个Node上存放若干Segment的数据（可冗余，多份copy），对上面是透明的。
据说这个引擎非常NB，但是不是一般应用可以用的，光搞清楚配置就可以写好几本书……我觉得Apache下的按个Ca什么应该更适用于实际一点。

2.10  The Falcon Engine
Jim Starkey(MVCC的发明者)开发的引擎，2006年刚引入MySQL，非常年轻。主要是事务操作全部在内存中执行因此commit和rollback比较快。

2.11  The solidDB Engine
SolidDB公司开发的引擎，很多特性与InnoDB类似。

 

4、如何选择合适的MySQL Engine

以下依据仅是一些参考因素：

事务：业务是否需要支持事务，如果需要InnoDB是最好的选择，否则MyISAM

并发：如果只是少量的insert和select，MyISAM是最好的选择。

备份：如果业务不允许离线备份（挺掉MySQL直接拷贝），请参考11章。

故障恢复：InnoDB的故障恢复速度远快于MyISAM

cluster index：InnoDB或solidDB

全文索引：MyISAM

5、从业务场景出发选择Engine

Logging：高insert速度，MyISAM、Archive。如果需要进行大量在线分析，可用Rplication，在slave上read操作。或者对Table切割（Merge Table），缩小出冲突区域然后再进行分析。

只读或读操作占多：MyISAM，但是崩溃后恢复起来会很慢。

处理订单：一般需要事务支持，InnoDB是首选

股票数据处理：如果数据已经收集完毕，MyISAM。如果需要并发地读和写，行级锁定的引擎是好的选择。

BBS：每个Request都需要好多个query（select，insert、update等）来支持。特别需要注意一些特殊SQL语句：

mysql> SELECT COUNT(*) FROM table;

不是每个Engine都能快速的完成这个查询，MyISAM可以，但是其他很多都不行。对于每种引擎，都有一些这样的“语句速度Bug”。

基于CD－ROM或DVD的程序：使用MyISAM或者压缩的MyISAM，特别是后者，因为占用空间小，而且CD／DVD本来就是只读的。

6、改变表的引擎

方法1：

mysql >> alter table mytable engine = InnoDB;

警告：MySQL默认采用row-by-row的拷贝，这可能会导致严重的I/O性能下降。

方法2：

也可以通过mysqldump导入，然后mysq再导入，只需修改create中的引擎即可。

方法3：

mysql >> create table test_innodb like test; //只复制结构

mysql >> alter test_innodb engine = innodb; //修改引擎

mysql >> insert into test_innodb select * from test; //快速的插入

但是万一有失败的话会非常麻烦，因此不如采用事务的方式：

mysql >> START TRANSACTION;
mysql >> INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
mysql >> COMMIT;

如果修改过程中不希望有新数据捣乱，可以直接Lock Table.