《高性能MySQL》第一章：MySQL架构与历史

MySQL最与众不同的特性: 存储引擎架构，这种架构将查询处理及其他系统任务和数据的存储/提取相分离。

1. MySQL的逻辑架构:

第一层：连接处理，授权认证，安全 等等
第二层：查询解析、分析、优化、缓存 以及 所有的内置函数。包含跨存储引擎的功能：存储过程、触发器、视图等。
第三层：存储引擎。负责MySQL中数据的存储和提取。

1.1 连接管理与安全性：

每个客户端连接都会在服务器进程中拥有一个线程。服务器会负责缓存线程，因此不需要为每个新建的连接创建或者销毁线程。当客户端连接到MySQL服务器时，服务器会对它进行认证。认证基于用户名、原始主机信息和密码。客户端连接成功后，服务器还会验证客户端的操作权限。

1.2 优化与执行：

MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。
用户可以通过特殊的关键字提示优化器，影响它的决策过程；也可以请求优化器解释优化过程。
存储引擎对于优化查询是有影响的，所以，优化器可能请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息。
对于select语句，在解析查询前，服务器会先检查 查询缓存，如果在查询缓存中存在，则可以直接返回查询缓存中的结果集。

2. 并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。

2.1 读写锁

读锁（共享锁）：多个用户在同一时刻可以同时读取同一个资源，而互不干扰。
写锁（排他锁）：一个写锁会阻塞其他的写锁和读锁。

2.2 锁粒度

表锁(table lock)：MySQL中最基本的锁策略，开销最小的策略。
行级锁(row lock)：最大程度的支持并发处理，开销最大的所开销。

3. 事务

事务：一组原子性的SQL查询。事务内的语句，要么全部执行成功，要么全部执行失败。

事务SQL的样本：

START TRANSACTION；
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance +200.00 WHERE customer_id = 10233276;
COMMIT;

事务的四个标准特征（ACID）

• 原子性（atomicity）：一个事务必须被视为不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚。
• 一致性（consistency）：数据库总是保持一致性的状态
• 隔离性（isolation）：通常来说，一个事务在没有最终提交以前，对其它事务是不可见的。
• 持久性（durability）：一旦事务提交，则其所做的唏嘘该就会永久保存到数据库中。

3.1 隔离级别

• 未提交读（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其他事务也都是可见的。
• 提交读（READ COMMITTED）：大多数数据库的默认隔离级别是提交读（MySQL不是）。事务从开始直到提交之前，所作的修改对其他事务都是不可见的。也叫不可重复读。
• 可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样记录的结果是一致的。可重复读是MySQL的默认事务隔离级别
• 可串行化（SERIALIZABLE）：非常需要数据的一致性和接受没有并发的情况性，才考虑该级别。

对于隔离级别的举例解释

3.2 死锁

例如，下面两个事务有可能出现死锁：

事务一
START TRANSACTION;
UPDATE StockPrice SET close =45.50 WHERE date = '2002-05-01';
UPDATE StockPrice SET close=19.80 WHERE date='2002-05-02';
COMMIT;
事务二
START TRANSACTION;
UPDATE StockPrice SET high=20.12 WHERE date = '2002-05-02';
UPDATE StockPrice SET high=47.20 WHERE date = '2002-05-01';
COMMIT;

数据库实现了各种死锁检测和死锁超时机制。
InnoDB处理死锁的方式：将持有最少行级排他锁的事务进行回滚。

3.3 事务日志

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都讲修改的数据本身持久到磁盘。事务日志采用的是追加的方式。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动回复这部分修改的数据。

3.4 MySQL中的事务

MySQL提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster。第三方包括XtraDB和PBXT。

自动提交（autocommit）：MySQL默认采用自动提交模式。如果不是显式地开始一个事务。每个查询都被当做一个事务执行提交操作。若autocommit=0时，所有的查询都在一个事务中，知道显式地执行Commit或RollBack。而修改autocommit，对非事务型的表没有影响，它们总是相当于启用autocommit模式。

查看AUTOCOMMIT变量：SHOW VARIABLES LIKE 'AUTOCOMMIT';
设置：SET AUTOCOMMIT =1;  或 SET AUTOCOMMIT =0; 

存在一些命令，在执行之前会强制执行COMMIT提交当前的活动事务。例如，ALTER TABLE，LOCK TABLES等。

设置隔离级别：set  transaction isolation level READ COMMITTED;

在事务中混合使用存储引擎：是不可靠的。例如如果事务需要回滚时，非事务型的表的更改是无法撤销的。

隐式和显式锁定：
隐式锁定：
InnoDB采用两阶段锁定协议。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或RollBack的时候才会释放，并且所有的锁是在同一时刻被释放的。InnoDB会根据隔离级别在需要的时候自动加锁。

显式锁定：
通过特定的语句进行显式锁定。

InnoDB支持通过特定的语句进行显示锁定：
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
MySQL支持：
LOCK TABLES
UNLOCK TABLES

4. 多版本并发控制（MVCC）

MVCC是行级锁的一个变种，它在很多情况下避免了加锁操作，开销更低。
不同的存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。

InnoDB中的MVCC：
是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间），这里的时间并不是指的实际的时间值，而是系统版本号。
每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到每行记录的版本号进行比较。

在可重复读的隔离级别下的MVCC具体操作：

• select——InnoDB只查找创建版本早于当前事务版本的数据行；而且行的删除版本要么未定义，要么大于当前事务版本号。
• insert——InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
• delete——InnoDB为删除的每一行保存当前系统版本作为行删除标识。
• update——InnoDB为插入一行新记录，保存当前版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

5. MySQL的存储引擎

在文件系统中，MySQL将每个数据库(也称为schema)保存为数据目录下的一个子目录。创建表时，MySQL会在数据库目录下创建一个和表同名的.frm文件保存表的定义。
可以使用 SHOW TABLE STATUS 命令显示表的相关信息。

SHOW TABLE STATUS; 显示所有表
SHOW TABLE STATUS LIKE 'user' ;
SHOW TABLE STATUS LIKE 'user' \G; 加上\G表示按行显示

5.1 InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期事务。短期事务大部分情况是正常提交的，很少会被回滚。
InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。
InnoDB采用MVCC来支持高并发，并实现了四个标准的隔离级别。其默认级别是可重复读，并且通过间隙锁策略防止幻读。
InnoDB表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能。不过它的二级索引中必须包含主键列。
InnoDB内部做了很多优化，包括从磁盘中读取数据时采用的可预测性读，能加速插入操作的插入缓冲区。
InnoDB通过一些机制和工具支持真正的热备份。

5.2 MyISAM存储引擎

在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但是MyISAM不支持事务和行级锁，而且崩溃后无法安全恢复。对于只读的数据，或者表较小、可以忍受修复操作，依然可以使用MyISAM。

存储：MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。MyISAM表可以包含动态或者静态（长度固定）行。
在MySQL 5.0中，MyISAM表如果是变长行，则默认配置只能处理256TB的数据，因为指向数据记录的指针长度是6个字节。可以通过MAX_ROWS和AVG_ROW_LENGTH修改。

MyISAM特性：

• 加锁与并发：MyISAM对整张表加锁，而不是针对行。读取时会对所有需要的表加共享锁，写入时则对表加排他锁。
• 修复：CHECK TABLE mytable 检查表的错误；REPAIR TABLE mytable 进行修复。如果MySQL服务器已经关闭，可以通过myisamchk命令行工具进行检查和修复操作。
• 索引特性：即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。MyISAM也支持全文索引。
• 延迟更新索引键（DELAY_KEY_WRITE）：在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。

MyISAM压缩表：如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。
可以使用myisampack对MyISAM表进行压缩。压缩表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间占用。压缩表也支持索引。

5.3 MySQL内建的其他存储引擎

Archive引擎：
只支持INSERT和SELECT操作。适合日志和数据采集类应用。
Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩。但是每次SELECT查询都需要执行全表扫描。支持行级锁和专用的缓冲区，可以实现高并发的插入。

Blackhole引擎：
Blackhole引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。

CSV引擎：
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但是这种表不支持索引。CSV引擎可以作为数据交换的机制。

Federated引擎：
Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或发送需要的数据。

Memory引擎
如果需要快速地访问数据，并且不会修改这些数据，那么可以使用Memory表。Memory表中所有的数据都会保存在内存中。
用于查找表或映射表，例如邮编和州名映射的表；用于缓存周期性聚合数据的结果；用于保存数据分析中产生的中间数据。
Memory表支持Hash索引，因此查找操作非常快。

Merge引擎：
NDB集群引擎;

5.4 第三方存储引擎

OLTP类引擎：
联机事务处理OLTP（on-line transaction processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。
包括：XtraDB存储引擎, PBXT, TokuDB， RethinkDB

面向列的存储引擎：
Infobright ， InfiniDB ， ……

社区存储引擎：
Aria ， Groonga ，OQGraph ， ……

5.5 选择合适的存储引擎

要考虑的因素： 事务；备份；崩溃恢复；特有的特性；（一般用InnoDB）

• 日志型应用：一般用MyISAM或Archive存储引擎。
• 只读或大部分情况下只读的表：InnoDB ，其次MyISAM
• 订单处理：InnoDB
• 电子公告牌和主题讨论论坛：
• CD-ROM应用：MyISAM压缩表
• 大数据量：InnoDB数据量在3~5TB之间。10TB以上用Infobright或数据仓库

5.6 转换表的引擎

一、ALTER TABLE mytable ENGINE = InnoDB;
需要执行很长的时间。

二、导入和导出
使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名。
同时要注意DROP TABLE 语句。

三、创建和查询

CREATE TABLE innoDB_table LIKE myisam_table;
ALTER TABLE innoDB_table ENGINE = InnoDB;
INSERT INTO innoDB_table SELETC * FROM myisam_table;

如果数据量很大，则可以考虑做分批处理。

6 MySQL时间线

2001 - 版本3.23
2003 - 版本4.0
2005 - 版本4.1
2006 - 版本5.0
2008 - 版本5.1
2010 - 版本5.5