高性能MySQL学习笔记一：MySQL架构

MySQL逻辑架构

MySQL最与众不同的特性是：它的存储引擎架构，这种架构的设计将查询处理以及其他系统任务和数据的存储/提取相分离。这种处理和存储相分离的设计可以在使用时根据性能 特性，以及其他需求来选择数据存储的方式。

并发控制

• 读写锁：共享锁和排他锁。读锁是共享的，互不干扰，写锁是排他的，一个写锁会阻塞其他的写锁和读锁。在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL会通过锁定防止其他用户读取同一数据。写锁比读锁的优先级要高。

• 锁粒度：只锁定需要修改的部分，而不是所有的资源。每种MySQL的存储引擎多有自己的锁策略和锁粒度。用户可以根据需要自行选择。常见的锁策略：表锁和行级锁 一个用户在对表进行写操作 前需要先获得写锁，表锁会阻塞其他用户对该表的所有读写操作 。读锁不相互阻塞。诸如ALTER TABLE 之类的语句会使用表锁，而忽略存储引擎的锁机制。行级锁 可以最大程度的支持并发处理， InnoDB 引擎支持行级锁。

• MVCC(多版本并发控制) mvcc没有一个实现标准，可以认为MVCC是一个行级锁的变种，很多情况下避免了加锁操作，开销更低。MVCC的实现是通过保存数据在某一个时间点的快照来实现的。每个事务本身看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能不一致。典型的有乐观并发控制和悲观并发控制。InnoDB 的实现方式：每行记录有两个隐藏的列：行的创建时间和行的过期时间 （表示这一列是什么时候被删除的），存储的是系统版本号。没开始一个事务，系统版本号都会递增。事务开始时刻的系统版本号会作为事务的版本号，用来与每行记录的版本号作比较。SELECT 只有符合这两个条件的行，才会作为查询结果返回：行的创建版本号 <= 事务的版本号 and ( 行的删除版本号未定义 or 行的删除版本号 > 事务版本号 )；INSERT 插入新的一行，并将当前版本号存入 行的创建时间 这一列作为行的版本号；DELETE 在这一行的 过期时间 这一列存入当前版本号作为删除标识；UPDATE 插入新的一行记录，旧行标记为删除（DELETE语句）。InnoDB的MVCC只在REPEATABLE READ 和READ COMMIT两个级别下有效。优势和不足： 这种设计使得读数据操作很简单，性能很好，并且可以保证只会读到符合标准的行。但是每行都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

事务

• 隔离性
• 原子性
• 一致性
• 持久性

MySQL默认采用自动提交模式。设置是否自动提交（1为自动提交，0禁用，禁用后，使用COMMIT或ROLLBACK结束一个事务并开始另一个事务）：

SET AUTOCOMMIT = 1;

事务日志：可以帮助提高事物执行的效率。
在同一个事务中，使用多种存储引擎是不可靠的，如果一个事务中混合使用了事务型的表和非事务型的表，发生了撤销操作，可能会导致数据库处于不一致的状态。所以，为每张表选择合适的引擎非常重要。

隔离级别

• 未提交读
• 提交读
• 可重复读
• 可串行化

设置隔离级别：

SET SESSION(只在当前会话生效) TRANSACTION ISOLTION LEVEL READ COMMIT（隔离级别）;

死锁

• 不可抢占条件
• 循环等待条件
• 互斥条件
• 请求和保持条件

为了解决死锁，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB能检测到死锁的循环依赖，并立即返回一个错误。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事物进行回滚。

存储引擎

查看表的相关信息：

SHOW TABLE STATUS LIKE '表名' \G(输出格式控制);

InnoDB

他是MySQL的默认引擎，它被用来处理大量的短期事务。优先考虑InnoDB 引擎。它实现了四个标准的隔离级别，默认级别是可重复读，采用MVCC支持高并发，并且通过间隙锁 的策略防止幻读的出现。
它的表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能。不过他的二级索引必须包含主键列，所以主键应当尽可能小。
它的内部做了很多优化，包括从磁盘读取数据时采用可预测性预读，自动在内存中创建hash所有以加速读操作的自适应哈希索引，以及能够加速chary操作的插入缓冲区等。

MyISAM

MyISAM是MySQL 5.1之前的默认引擎，它有大量的特性，支持全文索引，压缩，空间函数， 地理空间搜索等。但是它不支持事务和行级锁，而且有一个缺陷：崩溃后无法安全恢复。MyISAM引擎设计简单，数据以紧密格式存储，在某些场景下性能很好。对于只读的数据 或者表比较小 可以忍受修复操作，可以使用这个引擎。
它会对整张表加锁，支持并发插入（读取表的时候，向表中写入记录）。支持压缩操作，压缩之后不能在进行修改操作，可以极大地减少磁盘空间占用，减少磁盘I/O，提高查询性能。

选择合适的索引

大多数情况下选择InnoDB，除非需要用到某些InnoDb不具备的特性。在选择引擎的时候考虑这几个因素：事务 备份 崩溃恢复 特有的特性等。

转换表的引擎操作

• AKTER TABLE 表名 ENGINE = InnoDB;
• 导入与导出（略）
• CREATE和SELECT操作 （略）