mysql（1）

MySQL的架构与历史

主要介绍mysql的服务器架构、各种存储引擎之间的主要区别，以及这些重要性

1.1 逻辑架构

第一层：是大多数基于网络的client/serve的工具或服务都有类似的架构（连接处理、授权认证、安全等等）

第二层：大多数的mysql的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、view等

第三层：包含了存储引擎。存储引擎包含了mysql中数据的存储和提取，每个存储引擎都有它的优势和劣势。serve通过api与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异。注意：存储引擎不会去解析sql，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求

1.1.1 连接管理与安全性

每个client都会在服务器进程中拥有一个线程，这个连接的查询只在这个单独的线程中执行，该线程只能轮流在某个cpu核心或cpu中运行。服务器会负责缓存线程，因此不需要为每一个新建立的连接创建或者销毁线程

认证方式：a 基于用户名、原始主机信息和密码 b 安全套接字（SSL）c X.509证书认证

一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限

1.1.2 优化与执行

mysql会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化（重写查询、决定表的读取顺序，以及选择合适的索引等）。（第一种）用户可以通过特殊的关键字hint优化器，影响他的决策过程。（第二种）也可以选择何使的索引explain优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于重构查询和schema、修改相关配置，使应用尽可能高效运行

优化器和存储引擎在这期间的活动关系

比如：

1 某些存储引擎的index会对一些特定的查询有影响的

2 对于select的查询过程 serve -> Query Cache（如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集）

1.2 并发控制

mysql的两个层面的并发控制：服务器层和存储引擎层

1.2.1 读写锁

shared lock/read lock 

exclusive lock/write lock

1.2.2 锁粒度

锁策略：在锁的开销和数据的安全性之间寻求平衡，这种平衡会影响到性能（一般都是在表上加 row level lock，并以各种复杂的方式实现）

在mysql中，不同的存储引擎都实现了自己的锁策略和锁粒度

两种重要的锁策略：

      a 表锁（开销最小；先获取写锁，会阻塞其他用户对该表的所有读写操作，在没有写锁时，其他用户才能获得读锁，读锁之间是不能相互阻塞的）

alter table语句会使用表索而忽略存储引擎的锁机制

      b 行级锁（开销最大（最大程度的支持并发处理））

行级锁只在存储引擎（InnoDB和XtraDB中）层实现，在mysql服务器层没有实现

1.3 事务

atomicity consistency isolation durability

 

1.3.2 死锁

指2个或多个事务在同一资源上相互占用，并请求对方占用的资源，从而导致恶性循环的现象

innoDB目前处理死锁的方法是：将持有最少行级排他锁的事务进行回滚

锁的行为顺序是和存储引擎相关的，以同样的顺序执行语句，有些存储引擎会死锁有些则不会

。。。

1.4 多版本并发控制(mvcc)

mvcc可总结为 是行级锁的一个变种，但是在很多情况下避免了加锁操作，因此开销更低。虽然实现机制不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

mvcc的实现是通过保存数据在某个时间点的快照来实现的（典型的有乐观锁和悲观锁）

InnoDB的mvcc简化实现：

id	...行数据	行的创建时间(隐藏列)	行的过期时间(隐藏列)
1	...
...	...

 

*注：隐藏列的时间都为 system version number，增加2个隐藏行是大多数读操作都可以不用加锁，但会增加额外的存储空间

每开始一个新的事务， system version number都会自动递增。事务开始时刻的 system version number作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ下mvcc的操作：

mvcc只在repeatable read 和read commit下工作其他隔离级别都不支持的

1.5 mysql的存储引擎

存储引擎	InnoDB	MyISAM
简单介绍	除非有特别的原因需要选用其他的存储引擎，否则优先考虑这个	支持全文索引、压缩、空间函数等，但不支持事务和行级锁且奔溃后无法恢复。对于只读或者表比较小可以忍受repair操作，依然可以使用
数据存储哪里	tablespace，表空间是由InnoDB管理的一个黑盒子，有一系列的文件组成	将表存储在2个文件中：data file和index file，分别以.myd和.myi为扩展名
	每个表的数据和索引放在单独的文件中
	可以使用裸设备作为tablespace的存储介质
	采用mvcc支持高并发，实现了4个隔离级别，默认为repeatable read，并且通过间隙锁策略防止幻读的出现	对整张表加锁，读取时对读到的所有表加共享锁，写入时对表加排他锁
index	基于聚簇索引的，对主键查询有很高的性能，但是二级索引必须包含主键列，所以主键很大的话，其他的所有索引都会很大（表中index较多时主键越小性能越好）
优化	从磁盘读数据时采用的可预测预读，能够在内存创建hash index一加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区
热备份	支持
推荐	行为很复杂，建议读取官方手册InnoDB事务模型和锁