【MySQL】【持续更新】《高性能MySQL》 学习笔记
第一章：MySQL架构和历史
1.处理和存储分离的设计
MySQL最重要，最与众不同的特性是它的可插拔式存储引擎架构（将查询处理，系统任务，数据的存储，提取相分离）。
2.架构：
| 层级 | 作用 | 备注 |
|---|---|---|
| 连接层 | 连接处理，授权认证等 | RDMS共有设计 |
| 服务层 | 查询解析，缓存，分析优化，内置函数，跨引擎功能 | MySQL核心服务与功能，服务层未设计外键 |
| 引擎层 | 负责数据存储与提取 | 底层函数，不会解析SQL，不同引擎直接不在此层级互相通信，仅响应来自上层的请求。处理外键 |
3.优化与执行
服务层解析查询，创建解析树，然后进行重写查询，决定表的读取顺序，选择合适的索引等。在此步，用户可以通过hint关键字或者force index影响优化器的决策，也可以通过explain命令查看优化器如何优化决策。
优化器不关心表使用什么引擎，但存储引擎对优化查询是有影响的。
4.锁
读锁是共享的，相互不阻塞。
写锁是排他的，相互阻塞，在给定的时间内，同一行数据，只能有一个用户正在进行写入。
大多数时候，MySQL锁的内部管理都是透明的。
在给定的资源上，锁定的数据量越少，则系统的并发读越高。
加锁需要消耗资源，如果系统花费大量时间来管理锁，而不是存取数据，则系统的性能可能会因此收到影响。
一般会在锁的开销和数据的安全性之间寻求平衡，即在表上施加行级锁。
每种MySQL引擎都可以实现自己的锁倾向和锁粒度。
写锁比读锁有更高的优先级，并发时，写锁请求可能会被插入到队列的最前端，但读锁最多只能排在其他读锁的最前端。
服务层会对涉及到整个表的内容的更改使用表级锁，引擎层的锁机制被忽略。
行级锁只在存储引擎层实现。
InnoDB采用两段锁定协议，在事务执行过程中，随时都可以执行锁定，但只有在整个事务提交或者回滚时才会同一时间释放该事务占有的所有锁。‘隐式锁’
服务层可以使用LOCK TABLE或者UNLOCK TABLE，但可能会和事务产生相互影响产生不可预料的后果，尽量不要在业务进行时中使用。
5.事务
事务就是一组原子性的SQL查询，一组独立的工作单元。事务内的语句要么全部执行完，要么全部失败。
一个完备的数据库系统需要满足ACID特征：
Atomictiy 原子性：一个事务视为不可分割的最小单元。
Consistency 一致性: 数据库总是从一个一致性的状态转换到另一个一致性的状态。
Isolation 隔离性: 一个事务所做的修改在最终提交之前，对其事务是不可见的。 Durability 持久性：一旦提交，永久保存，不受系统崩溃影响。
一个实现了ACID特性的数据库，需要更强的硬件。
即使存储引擎不支持事务，但还可以通过lock table来提供部分ACID特性。
非事务型的表只能自动提交。
事务型的表在执行DDL操作或者lock table时会强行commit当前的活动事务
隔离级别（ANSI）
READ UNCOMMITED(未提交读)：事务中未提交的修改也会被其他事务读到，’脏读‘，性能不会好太多。
READ COMMITED(提交读)：事务中提交后的修改会被其他事务读到,但会造成不可重复读。MSSQL,ORACLE
READ REPEATABLE(可重复读):事务中多次读取同一条数据值相同，但新插入的不算，即主键范围读可能不一致，'幻读',MySQL默认级别。
SEARIALIZE(串行)：取消并行，完全串行，悲观锁。
可以通过set [session] transaction isolation level [RU|RC|RR|SX]设置隔离级别，全局性设置在下一个事务开始时生效，会话级设置只对当前事务有效
| 隔离级别 | 脏读可能性 | 不可重复读 | 幻读可能性 | 加锁读 |
|---|---|---|---|---|
| RU | Y | Y | Y | N |
| RC | N | Y | Y | N |
| RR | N | N | Y | N |
| SX | N | N | N | Y |
死锁
两个或以上的事务争用同一组资源，并请求锁定对方占用的资源。
处理办法：完备的RDMS包含了死锁检测与死锁超时机制。
InnoDB:将持有最少行级X锁的事务进行回滚。
死锁在事务型RDMS中是无法避免的，死锁发生后，只有部分或者完全回滚其中一个事务才能打破窘境。
事务日志（redo）
可以提高事务的效率，存储引擎在修改表的数据时只需要修改其内存拷贝，再批量地把修改的行为记录到硬盘上的事务日志文件中。
事务日志采用了正向追加的方式，保证了写入时的 顺序IO。批量记录修改的行为时，事务日志持久后，可以在后台将内存中的数据慢慢刷回磁盘
6.多版本并发控制（MVCC）
MVCC是行级锁的一个变种，在很多情况下避免了加锁的操作，且只在RR和RC隔离级别下工作。
MVCC是通过保存数据行在某个时间点的快照来实现的。
根据事务开始时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
InnoDB的MVCC，是通过在每个记录（包括已经存入undo中的记录）后面保存两个隐藏的版本号来实现的。即：该数据创建时的系统版本号和该数据失效时的系统版本号。
其他事务：
SELECT时：只查找比当前事务创建更早的行（极端情况下，在此事务中对此行进行了修改或创建，即读取本事务中被修改后此行的数据）。
INSERT时：为新插入的此行保存本事务版本的版本号作为此行的创建版本号。同时若在此事务中不在对此行进行修改，则行过期版本号留空
DELETE时：将本事务的版本号赋给被删除行的失效版本号
UPDATE时：将本事务的版本号赋给被修改行的失效版本号，同时插入被修改后的数据，同时把本事务的版本号付给新插入的修改后的数据的创建版本号。
保留这两个额外版本号，可以使大多数读操作都不用加S锁，但这些多余的行会占用undo空间。建议将undo从共享表空间中独立出来，并减小事务长度。
7.存储引擎
不同引擎保存数据和索引的方式是不同的，但表的定义是在服务层统一处理的。
通过show table status显示表的相关信息
| 属性 | 属性值 | 说明 |
|---|---|---|
| Name: | user | 表名 |
| Engine: | InnoDB | 存储引擎 |
| Row_format: | Dynamic | 行格式，变长行 |
| Rows | 3 | 对应InnoDB，估计的行数值 |
| Avg_row_length | 5461 | 平均每行字节数 |
| Data_length | 16384 | 表数据字节数 |
| Max_data_length | 0 | 表最大字节数（InnoDB无限制） |
| Index_length | 0 | 非主键索引占用字节数 |
| Data_free | 4194304 | 已分配但未使用的字节数 |
| Auto_increment | NULL | 下个自增值的起始点 |
| Create_time | 2018-01-24 20:02:01 | 创建时间 |
| Update_time | NULL | 最后一次的更新时间 |
| Check_time | NULL | CHECK TABLE命令使用的时间 |
| Collation | utf8_bin | 该表默认字符集与排序规则 |
| Checksum | NULL | （若启用校验）校验和 |
| Create_options | stats_persistent=0 | 建表时的其他非默认选项 |
| Comment | Users and privileges | 表的备注 |
InnoDB引擎被设计用来处理大量短期事务（大部分情况下正常提交），正常情况下默认使用InnoDB引擎，MySQL8.0版本，将mysql库中的表也从MyISAM更换为InnoDB引擎。
InnoDB引擎一直朝着可测量性，可扩展性，可配置化，性能，更多新特性的方向演进。
InnoDB引擎概览
InnoDB的数据存储在表空间中，推荐使用独立表空间，即将各个表的数据和索引放在单独的文件中。
InnoDB使用MVCC来支持高并发，并且实现了四个标准的隔离级别。默认隔离级别为可重复读RR，并且通过间隙锁的机制解决了范围读可能因为新插入值导致出现幻读的问题。
间隙锁通过在本来锁定查询设计的行同事，还会对索引中的间隙进行锁定，防止插入新的行。
InnoDB表是基于聚簇索引建立的，即索引组织表。这种设计，使得对主键的进行的查询效率很高。其二级索引，即非聚集索引（普通索引，非主键索引）是通过链接的方式指向其对应的主键位置，即二级索引中包含了主键列。这就会产生一个主键列长度很大，其他索引的大小也会同样很大的问题。这个特性要求我们主键列的最大长度尽量减小。
InnoDB引擎表的文件存储格式是独立的，某种程度上可以跨平台使用。
InnoDB的显著优化特点：从磁盘读取数据时的可预测性读，引入了能够在内存中创建Hash索引来加速读操作的自适应哈希索引（adaptive hash index），能够加速插入操作的插入缓冲区(insert buffer，对非主键非唯一性索引进行缓存，每10s合并到非主键索引中)。
InnoDB引擎的表支持热物理备份（非逻辑备份成sql文件）即XtraBackup或者官方的Enterprise Backup
有趣的CSV引擎
可以将Excel文件另存为CSV格式，放入MySQL数据目录下，即可在MySQL中打开使用。
慢慢弃用的引擎：
Federated引擎，本来设计用于建立Oracle，SQL server到MySQL的数据联系纽带，后被用于创建跨实例连接（类似于SQL server中的同义词或者链接服务器），但经常带来问题，默认禁用，MariaDB提供了一个改进版的FederatedX。
Memory引擎，数据只存在内存中，重启后表结构留存，但数据会全部丢失。支持HASH索引，表级锁，并发低，行长度固定。
第三方常用存储引擎：
XtraDB引擎，Percona公司基于InnoDB引擎的改进版本
TokuDB引擎，基于分形树索引的引擎，具有较高的压缩比，可以在很大的数据量上创建索引。
Infobright引擎，列组织的引擎，面向大数据设计。
更改表的存储引擎：alter table t1 engine = InnoDB; 执行过程中会创建相同表结构不同引擎的一张新表，然后按行将数据从原表读入到新表中，会进行锁表，消耗大量系统IO，尽量不要在业务高峰期进行，或者使用pt-online-schema-change的工具进行在线修改。
本文转自 angry_frog 51CTO博客，原文链接:http://blog.51cto.com/l0vesql/2063394