第一章 mysql架构和历史
1.1 mysql逻辑架构
客户端
连接/线程处理
查询缓存——解析器
优化器
存储引擎
1.1.1 连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只有在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或销毁线程。
当客户端（应用）连接到mysql服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。
1.1.2 优化与执行
优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。
对于select查询语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的过程，而是直接返回查询缓存中的结果集。
1.2 并发控制
1.2.1 读写锁
共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。
1.2.2锁粒度
一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。
问题是加锁也需要消耗资源。锁的各种操作，包括获得所、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统话费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会因此受到影响。
所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。
mysql提供了多种选择，每种mysql存储引擎都可以实现自己的锁策略和锁粒度。
在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去对另外一些应用场景的良好支持。
好在mysql支持多个存储引起的架构，所以不需要单一的通用解决方案。下面将介绍两种最重要的锁策略。
表锁（table lock）
表锁是mysql中最基本的锁策略，并且是开销最小的策略。
尽管存储引擎可以管理自己的锁，mysql本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如alter table之类的语句使用表锁，而忽略存储引擎的锁机制。
行级锁（row lock）
行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而mysql服务器层没有实现。服务层完全不了解存储引擎中的锁实现，所有 存储引擎都以自己的方式显现了锁机制。
1.3 事务
事务特性：
原子性——隔离性——持久性——一致性
1.3.4 mysql 中的事务
mysql提供了两种事务型的存储引擎，InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括XtraDB和PBXT。
自动提交：mysql默认采用自动提交模式，如果不是显示地开始一个事务，则每个查询都被当做一个事务执行提交操作。
show variables like 'AUTOCOMMIT’；
set autocommit=1
1或者on表示启用，0或者off表示禁用
InnoDB采用的是两阶段锁定协议，在事务执行过程中，随时都可执行锁定，锁只有在执行commit或rollback时才会释放，并且所有的锁是在同一时刻被释放。
经常可以发现，应用已经将表从myISAM转换到InnoDB，但还是显示地使用lock tables语句。这不但没有必要，还会严重影响性能，实际上innoDB的行级锁工作得更好。
建议不要显式地执行lock tables。
1.4 多版本并发控制
mysql的大多数事务型存储引擎实现的都不是简单的行级锁。基于提示并发性能的考虑，它们一般都同时实现了多版并发控制MVCC。不经是mysql，包括oracle、PostgreSQL等其他数据库系统也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC没有一个统一的实现标准。
不同存储引擎的MVCC实现不同，典型的有乐观并发控制和悲观并发控制。
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号system version number。每开始一个新事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
select
（1）InnoDB只查找版本早于当前事务版本的数据行。
（2）行的删除版本要么未定义，要么大于当前事务版本号。
只要符合上述两个条件的记录，才能返回作为查询结果
insert
（1）InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
delete
（1）InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
update
（1）InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识、
保存这两个额外的系统版本号，使大多数读操作可以不用加锁，这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。
MVCC只有在repeatable read和read committed两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容，因为read uncommitted总是读取最新的数据行，而不是符合当前事务版本的数据行。而serializable则会对所有读取的行都加锁。
1.5 Mysql的存储引擎
在文件系统中，mysql将每个数据库schema保存为数据目录下的一个子目录；创建表时，mysql会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。
show table status like 'user' \G
输出的结果表名，这是一个MyISAM表。
name：表名
engine：表的存储引擎类型
row_format：行的格式，对于myisam表，可选的值为dynamic、fixed或者compressed。dynamic的行长度是可变的，一般包含可变长度的字段，如varchar或blob。fixed的行长度是固定的，只包含固定长度的列，如char和integer。compressed的行则只在压缩表中存在。
rows：表中的行数，对于myisam和其他一些存储引擎，该值精确，但对于InnoDB，该值是估计值。
avg_row_length：平均每行包含的字节数
data_length：表数据的大小（以字节为单位）
max_data_length：表数据的最大容量，该值和存储引擎有关
index_length：索引的大小（以字节为单位）
data_free：对于myisam表，表示已分配但目前没有使用的空间，这部分空阿金包括了之前删除的行，以及后续可以被insert利用到的空间。
auto_increment：下一个auto_increment的值
create_time：表的创建时间
update_time：表数据的最后修改时间
check_time：使用check table命令或者mysiamchk工作最后一次检查表的空间
collation：表的默认字符集和字符列排序规则
checksum：如果启用，保存的是整个表的实时校验
create_options：创建表时指定的其他选项
comment：该列包含了一些其他的额外信息，对于myisam表，保存的是表在创建时带的注释。对于InnoDB表，则保存的是InnoDB表空间的剩余空间信息。如果是一个视图，则该列包含”VIEW“的文本字样。
1.5.1 InnoDB存储引擎
InnoDB是mysql的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
InnoDB的历史
InnoDB概览
InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。
InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是repeatable read（可重复读），并且通过间隙锁策略防止幻读的出现。
InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。不过它的二级索引中必须包含主键列，所以主键列很大的话，其他的所有索引都会很大。
1.5.2 MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。对于只读的数据，或者表比较小、可以忍受修复操作，则依然可以继续使用myisam，但请不要默认使用myisam，而是应当默认使用InnoDB。
存储
MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。
MyISAM表可以包含动态或者静态（长度固定）行。Mysql会根据表的定义来决定采用何种行格式。MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。
MyISAM特性
加锁和并发：对整张表加锁，而不是针对行。读取时共享锁，写入时排它锁，也可以并发插入。
修复：
索引特性：即使是Blob和text等长字段，也可以基于其前500个字符创建索引
查询：
MyISAM压缩表：
如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。
可以使用myisampack对MyISAM表进行压缩（也叫打包pack）。压缩表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间的占用，因此也可以减少磁盘I/O，从而提高查询性能。压缩表也支持索引，但索引也是只读的。
MyISAM性能
MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。
MyISAM最典型的性能问题还是表锁的问题，如果你发现所有的查询都长期处于“Locked”状态，那么毫无疑问表锁就是罪魁祸首。
1.5.3 MySQL内建的其他存储引擎
Archive引擎：只支持insert和select操作
Archive引擎支持行级锁和专用的缓冲区，所以可以实现高并发的插入。Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。
Blackhole引擎
没有实现任何的存储机制，它会丢失所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用，但这种应用方式有很多问题，不推荐。
CSV引擎
CSV引擎可以将普通的CSV文件（逗号分隔值的文件）作为mysql的表来处理，但这种表不支持索引。CSV引擎可以作为一种数据交换的机制，非常有用。
Federated引擎
是访问其他mysql服务器的一个代理，它会创建一个到远程mysql服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。
Memory引擎
如果需要快速的访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用memory表是非常有用的。Memory表至少比MyISAM表要快一个数量级，因为所有的数据都都保存在内存中，不需要进行磁盘I/O。Memory表的结构在重启以后还会保留，但数据会丢失。
Memory表在很多场景可以发挥好的作用：
用于查找或者映射表；
用于缓存周期性聚合函数的结果；
用于保存数据分析中产生的中间数据。
Memory表支持Hash索引，因此查找操作非常快。Memory表是表级锁，因此并发写入的性能较低。它不支持BlOB和TXXT类型的列，并且每行的长度是固定，所以即使指定了varchar列，实际存储也会转换成char，这可能导致部分内存的浪费。
如果mysql在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是memory表。如果中间结果太大超出了memory表的限制，或者包含blob或text字段，则临时表会转换成MyISAM表。
Merge引擎
Merge引擎是MyISAM引擎的一个变种。merge表是由多个myisam表合并而来的虚拟表。如果将mysql用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃。
NDB集群引擎
Mysql服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为Mysql集群（Mysql Cluster）。
1.5.4 第三方存储引擎
OLTP类引擎
Percona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本，已经包含在Percona Server和MariaDB中，它的改进点主要集中在性能、可测量性和操作灵活性方面，可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件，并支持InnoDB的所有查询。
PBXT引擎，都支持ACID事务和MVCC，是一款社区支持的存储引擎，Maria包含了该引擎。
TokuDB引擎使用了一种新的叫做分形树的索引数据结构。该结构是缓存无关的，因此即使其大小超过内存性能也不会下降，也就没有内存生命周期和碎片的问题。TokuDB是一种大数据存储引擎，因为其拥有很高的压缩比，可以在很大的数据量上创建大量索引。
RethinkDB最初是为固态存储而设计的，比较特别的地方在于采用了一种只能追加的写时复制B树作为索引的数据结构。
面向列的存储引擎
myslq默认是面向行的，每一行的数据是一起存储的，服务器的查询是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高。如果不需要正行的数据，面向列的方式可以传输更少的数据。如果每一列都单独存储，那么压缩的效率也会更高。
Infobright是最有名的面向列的存储引擎。
另外一个面向列的存储引擎是Calpont公司的infiniDB。
社区存储引擎
Aria——Groonga——OQgraph——Q4M——SphinxSE——Spider——VPForMySQL
1.5.5 选择合适的引擎
大部分情况下，InnoDB都是正确的选择。
除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。存储引擎层和服务器层的交互已经比较复杂，更不用说混合多个存储引擎了。至少，混合存储对一致性备份和服务器参数配置都带来的一些困难。
如果应用需要不同的存储引擎，请先考虑一下几个因素：
事务：
备份：
崩溃恢复：
特有的特性：
总结一些场景如下：
日志型应用——MyISAM或者Archive存储引擎——开销低，插入速度非常快
只读或者大部分情况下只读的表——MyISAM
订单处理——InnoDB——支持事务
电子公告牌和主题讨论论坛——
CD-ROM应用——MyISAM或MyISAM压缩表
大数据量——InnoDB（3-5TB之间）——10T以上，Infobright
1.5.6 转换表的引擎
有很多种方法可以将表的存储引擎转换成另外一种引擎，每种方法都有其优点和缺点。
方法一：
alter table mytable engine=InnoDB;
上述语法可以适用任何存储引擎，但是有一个问题，需要执行很长的时间。myslq会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁。所以在繁忙的表上执行此操作要特别小心。一个替代方案是，手工进行表的复制。
如果转换表的存储引擎，将会失去和原引擎相关的所有特性。
方法二：
导入与导出
为了更好的控制转换的过程，可以使用mysqldump工具将数据导出到文件，然后修改文件中create table语句的存储引擎选项，注意同时修改表名。同名，会drop table的，注意。
方法三
创建和查询（create和select）
第三种转换的技术综合了第一种方法的高效和第二种方法的安全。不需要导出整张表的数据，而是先创建一个新的存储引擎的表，然后利用insert 。。。select语法来导数据
create table mmmm like nnnn;
alter table mmmm engine=InnoDB;
insert into mmmm select from nnnn;
数据量不大的话，这样做工作得很好，如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操作，以避免大事务产生过多的undo。
start transaction;
insert into mmmm select *from nnnn where id between x and y;
commit;
如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。
pt-online-schema-change的工具，基于facebook的在线schema变更技术，可以比较简单、方便地执行上述过程，避免手工操作可能导致的失误和繁琐。
1.6 Mysql时间线（Timeline）
版本3.23（2001）
被认为mysql真正诞生的时刻，其开始获得广泛使用。
版本4.0（2003）
支持新的语法，重写的复制，InnoDB称为标准配备，引入查询缓存
版本4.1（2005）
引入了更多新的语法，开始支持utf8字符集
版本5.0（2006）
出现企业特性，视图、触发器、存储过程
版本5.1（2008）
Sun收购mysql AB后发布的版本，引入了分区、基于行的复制
版本5.5（2010）
这是oracle收购sun以后发布的版本，主要改善集中在性能、扩展性、复制、分区、对微软系统的支持，以及其他方面。InnoDB称为默认的存储引擎。更多的一些遗留特性和不建议使用的特性被移除。
版本5.6（还未发布）
版本6.0（已经取消）
现在版本更多的取决于业务需求而不是技术需求，理想情况下当然是版本越新越好，也可以选择等到第一个bug
修复版本以后再采用新的大版本。
1.7 MySQL的开发模式
MySQL依然遵循GPL开源协议，全部的源代码（除了一些商业版本的插件）都会开放给社区。
GA（Generally Available）的意思是通常可用的版本，这种版本也意味着达到了满足生产环境中使用的质量标准
MySQL拥有分层的架构，上层是服务器层的服务和查询执行引擎，下层则是存储引擎。虽然有很多不同作用的插件API，但存储引擎API还是最重要的。
