高性能MySQL记录-架构

逻辑框架

MySQL最重要、最与众不同的是它的存储引擎架构，将查询处理以及其他系统任务和数据的存储/提取相分离。

结构图

最上层 连接管理与安全性
1:每个客户端连接都会在服务器进程中拥有一个线程
2:客户端连接需要进行认证

优化与执行
1:MySQL会解析查询，并创建内部数据结构（解析树），然后进行优化
2:优化器不分存储引擎，但存储引擎对优化查询有影响
3:select语句，在解析查询之前，服务器会先检查查询缓存，如果有直接返回缓存结果

并发控制
1:查询需要在同一时刻修改数据，容易产生并发问题
2:会使用锁来防止数据损坏，锁住了必需等待锁释放才能使用

简单的锁类型介绍

读写锁
1:防止读取数据同时有人修改数据，导致数据混乱
读锁(共享锁)::多个客户可以同时读取一个资源，互不干扰
写锁(排他锁):阻塞其他的读写锁

锁粒度
1:为了提高资源共享性，让锁对象更有选择性，只锁需要资源，将锁粒度固定在某个级别

锁策略
1:锁会消耗资源，不锁又容易出现并发控制等安全性问题，所以为了平衡，定义了一个锁策略

---

表锁***
1:基本的锁策略，并且是开销最小的策略
2:锁定整张表，用户需要写操作时先获取写锁，只有没有写锁，其他读操作用户才能获取读锁
3:写锁优先级大于读锁，所以困难被先执行(插队)

行阶锁
1:可以最大程度支持并发处理，同时带来最大锁开销，排他锁
2:只在存储引擎层实现，而MySQL服务器层没有实现

事务
1:事务就是一组原子性的SQL语句，或者说一个独立的工作单元。(个人理解:就是一次输入的语句)
2:事务内的语句，要么全部成功，要么全部失败

ACID
1:一个运行良好的事务处理系统，必须经过ACID的标准测试
**原子性:**事务执行要么全部成功，要么全部失败，不可能只执行其中一部分，这就是事务的原子性
**一致性:**数据库总是从一个一致性到另外的一个一致性(个人理解:状态改变)
**隔离性:**一个事务所做的修改在最终提交以前，对其他事务是不可见的(个人理解:执行完之前维持原状)
**持久性:.**事务提交，则其所做的修改就会永久保存到数据库
2:功能意味着消耗资源，MySQL可以根据用户需求，选择合适的存储引擎

隔离级别

**READ UNCOMMITTED(未提交读)😗*事务修改即使没提交也是可读的

**READ COMMITTED(提交读)😗*大部分数据库(MySQL不是)的默认隔离级别，也叫不可重复读
加粗样式
**REPEATABLE READ (可重复读)😗*解决了脏读(读取未提交事务)，保证了同一个事务多次读取同样记录的结果一致，但没有解决幻读(读取某个范围内记录时，另一个事务又在该范围插入新数据),是MySQL的默认隔离级别

**SERIALIZABLE(可串行化)😗*是最高的隔离级别，避免了幻读，在读取每一行数据上都加锁，所以可能导致大量的超时和锁争问题，只有在非常需要确保数据一致性而且可以接受没有并发情况下，才考虑

死锁
1:指两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
2:检测死锁，并返回一个错误
3:排他锁回滚

事务日志
1:存储引擎在修改表的数据时只需要修改其内存拷贝，然后把修改行为记录到硬盘的事务日志(.个人理解:拷贝地址，事务日志记录修改行为)
2:提高事务效率
3:.采用追加的方式，写日志是磁盘顺序IO
4:内存被修改的数据后台可以慢慢刷回磁盘
5:以上称为预写式日志，修改数据需要写两次磁盘
6:如果日志修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此刻系统崩溃，存储引擎重启能够自动修复这部分修改数据

MySQL中的事务
1:.MySQL提供哦了两种类型的存储引擎:InnoDB和NDB Cluster

自动提交(AUTOCOMMIT)
1:默认是自动提交模式(如果不是自动提交要输入COMMIT提交或者ROLLBACK回滚才能结束)
2:可以设置AUTOCOMMIT 1或ON表示启用，0或OFF表示禁用
3:自动修改设置对非事务型的表(MyISAM)没有影响，相当于一直处于自动提交
4:MySQL可以设置 set transactton isolation level来设置隔离级别

事务中混合使用存储引擎
1:MySQL服务器不管理事务，事务是由下层的存储引擎实现的，所以一个事务不能多种存储引擎
2:如果事务在混合事务和非事务型的表上一般是没问题，但如果该事务要回滚则变更无法撤销，所以要为每张表选取合适的存储

隐式和显式锁定
**隐式锁定:**在事务执行过程都可以锁定 锁只有在COMMIT提交或者ROLLBACK回滚才能被释放，并且同一时间释放
**显示锁定:**特定语句锁定
1:支持lock tables和unlock tables 在服务器层实现的，和存在引擎无关
2:建议除了事务禁用自动提交可以使用lock tables之外，其他其他时候都不要显式执行lock tables，不论什么引擎

多版本并发控制MVC C

1:.很多情况避免了加锁操作，大都实现读操作，写操作也只锁定必要行
2:保存数据在某个时间点的快照实现 每个事务对同一张表，同一时刻看到的数据可能是不一样的
3:InnoDB 的MVC C是通过在每行记录后面两个隐藏的列来实现的 一个保存行创建时间，一个保存行过期时间

MySQL的存储引擎
1:MySQL会在数据库子目录下创建一个和表同名的frm文件保存表的定义
2:show table statues(5.0后information_schema)显示表的相关信息
InnoDB
1:数据存储在表空间
2:采用MVC C来支持高并发，默认隔离级别是可重复读，并且通过间隙锁(锁定查询涉及行和索引间隙锁定)防止幻读
3:基于聚簇索引建立**
MyISAM
1:.提供大陆特性
2:5.1.之前默认版本
3:不支持事务和行级锁，崩溃后无法安全恢复 尽量InnoDB
存储
1:MyISAM会将表存储在两个文件 数据文件(. MYD)和索引文件(.MYI)为扩展名
特性
加锁与并发：MyISAM对整张表加锁，而不是行
修复：对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，不等于事务恢复以及崩溃恢复
索引特性：对于前MyISAM表，即使是BL OB和text 等长字段，也可以基于其前500个字符创建索引
延迟更新索引键：创建MyISAM表，如果指定delay_key_write选项，在每次修改执行完成时，会写到内存中的键缓冲区，只有清理键缓冲区或者关闭表的时候才会将对应索引块写入到磁盘
MyISAM压缩表
1:不在更改可以压缩打包
MyISAM性能
1:表锁问题

MySQL的其他存储引擎
Archiver引擎
1:只支持insert和select操作，并且会缓存所有的写利用skin 对插入行进行压缩，所以比MyISAM表的磁盘IO更少但，但是每次select查询都需要全盘扫描，所以适合日志和数据采集类，及需要更快速的insert操作的场合
2:支持行级锁和专用的缓存区，所以可以实现高并发插入，查询开始到返回表存在的所有行数之前，会阻止其他select执行。它不是一个事务型引擎，而是一个针对高速插入和压缩做了优化的简单引擎
blackhole引擎
1:没有实现任何的存储机制，会丢弃所有插入的数据，不做保存，但服务器会记录black holde表的日志，可以用于复制数据到备库，或者只是简单地记录到日志
2:一些特殊的复制架构和日志审核时发挥作用，但问题多不推荐
CSV引擎
1:可以将普通的CSV文件(逗号分隔值的文件)作为MySQL表来处理，这种表不支持索引
2:可以在数据库运行时拷入拷出文件
3:可以将excel等电子表格中的数据存储到CSV文件，然后负责到MySQL数据目录下就可以打开了，如果写到CSV引擎表，其他外部程序也能立刻从表的数据文件读取CSV格式数据
4:可以作为一种数据交换的机制，十分有用
federated引擎
1:是访问其他MySQL服务器代理，先创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发生需要数据
2:经常出问题，默认禁用
3:federatedX进阶版
memory引擎
1:快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用memory表(heap表)非常有用
2:至少比MyISAM快一个数量级，数据都保存在内存，不需要进行次品IO，memory表的结构重启以后还会不留，但数据会丢失
3:支持hash索引，查找操作非常快，memory表是表级锁，并发写入性能低，不支持BL OB和text类型列，每行长度固定，可能导致部分内容浪费
4:如果MySQL在执行查询过程需要使用临时表保存中间结果，内部使用就是memory表，如果太大或者BL OB、text会变成MyISAM(.临时表指使用create temporary table语句创建的表，可以使用任何存储引擎,临时表只在单个连接中可见，当连接断开时，临时表也会消失)
作用场景:
1:查找隐射表
2:缓存周期性聚合数据的结果
3:保存数据分析产生的中间数据
merge引擎
1:merge表由多个MyISAM表合并，如果将MySQL用于日志或者数据仓库应用，该引擎可用。
2:但是引入分区功能后，改引擎已经被放弃
NDB集群引擎
1:作为SQL和NDB原生协议的接口。

第三方存储引擎
1:OLTP类引擎
2:面向列存储引擎
3:社区存储引擎

选择合适的引擎
1:除非需要用到InnoDB不具备的特性，并且没有其他办法可以代替，否则优先InnoDB
2:除非万不得已否则不混引擎
选择考虑的因素:
事务:需要事务InnoDB或者xtradb. 不需要事务，且主要select和insert操作MyISAM，一般日志型应用比较符合这一特性
备份:.如果定期关闭服务器备份，不考虑。反之如果需要在线备份，选择InnoDB
崩溃恢复:MyISAM损坏概率大，修复慢即使不需要事务也InnoDB
持有的特性:依据特性需求选择

日志型应用
1:网站访问信息记录表，插入速度要快，选择MyISAM或者Archiver存储引擎比较合适，开销低插入快
2:.需要对记录日志做分析报表
可以MySQL复制数据到备库做CPU查询，主库只插入
可以名字加上信息可以在没有插入操作的历史表上直接查询，而不干扰插入

大部分只读情况
1:如果不介意MyISAM崩溃恢复选他(使用MyISAM然后断电让它崩溃，练练手) 还是建议InnoDB
订单问题
1:InnoDB最佳选择
电子公告和主题讨论论坛
1:大部分数据库效率都不高，因为他们大多倾向于在一次请求中执行尽可能多的查询语句。
2:对核心表读写压力大，为了读取一致性，锁重要
CD-Rom
1:如果要发布一个基于CD-ROM或者DVD-ROM并且使用MySQL数据文件的应用，可以考虑MyISAM和MyISAM压缩表，这样的表之间可以隔离并且可以在不同介质上相互拷贝
2:压缩表比较节约可见，但只读
大量数据
1:InnoDB在3～5TB，如果增长到10TB考虑建立数据仓库，info bright是MySQL数据仓库最成功的解决方案，有些不使用，却可能适合tokudb

转换表引擎
LATER TABLE: mysql>alter table mytable engine=innnodb;.
1:执行时间长会将数据从原表复制到一张新表，复制期间可能消耗系统所有IO，同时原表上会加锁
2:如果转换存储引擎，将会失去和原引擎相关的所有特性
导出与导入
1:使用MySQL dump(默认会在create table前面加drop table要注意)工具将数据导出文件，然后修改文件中create table语句存储引擎，注意修改表名，同一个数据库不能存在相同表名，即使存储引擎不同
创建与查询
1:create table Innodb_table like myisam_ table;
2: alter table innodb_ table engine=innnodb;
3: insert into innnodb_table select *from myisam_table;
数据量不大，数据量大分组传输，percona Toolkit提供一个pt-online-schem-change工具可以代替手工

总结
1：MySQL拥有分层架构，如果能理解MySQL在存储引擎和服务层之间处理查询如何通过API来回交互，就能抓住MySQL核心基础架构的精髓