高性能mysql.1.mysql架构

第一章mysql架构

1.1mysql的逻辑架构

 

顶层非mysql独有，他们是许多基于网络的C/S工具或服务器都需要的服务，比如连接处理、授权认证、安全等。

第二层包括mysql的大多数核心内容，如查询解析、分析、优化、缓存及所有内建函数的代码。各种存储引擎提供的功能也集中在这层，如存储过程、触发器、视图等。

第三层包含了存储引擎，负责存储和提取所有存放在mysql中的数据。服务器通过存储引擎API与引擎进行通信。存储引擎不会进行sql解析（innodb会解析外键定义，因为mysql本身没有实现外键功能），也不会进行互相通信，只是简单响应服务器的要求。

1.1.1连接管理和安全性

每个连接都使用一个独立的线程，服务器会缓存线程。服务器认证方式基于用户名、主机名和口令，还可以用X.509证书。

1.1.2优化与执行

Mysql会解析查询，并创建一个解析树，然后对其进行各种优化。在解析前会询问查询缓存是否有该查询语句的结果，如果有则直接返回该结果，没有则进行解析。

优化器并不关心表使用了什么存储引擎，但不同的存储引擎对优化器有影响。优化器会向存储引擎请求性能与开销信息，以及表内数据的统计信息。

1.2并发控制

Mysql在两个层面实现并发控制：server level 和storage engine level

1.2.1读锁和写锁

也称共享锁和排他锁。大多数情况下锁都是以透明的方式实现。

1.2.2锁粒度

Mysql不同存储引擎可以支持不同的锁粒度。

表锁

表锁对整个表进行锁定，开销最小。在某些情况下表锁有良好的性能，如read local 表锁允许某些并发的写操作。写锁比读锁优先级高。

Mysql除了存储引擎外，本身也有表锁。如alter table时使用的就是自身的表锁。

行锁对行进行锁定，开销最大，支持最大的并发处理。支持的存储引擎有innodb，falcon。行锁在存储引擎层实现，而不是mysql本身。

1.3事务

事务遵循ACID规则，即atomicity，consistency，isolation，durability。

事务跟锁一样，会增加额外的开销。

1.3.1隔离级别

Read uncommitted

存在dirty read

Read committed

Mssql oracle默认级别。存在nonrepeatable read（两次读的内容不一样）。

Repeatable read

Mysql默认级别。存在phantom read（两次读的记录数量不一样）。Innodb和falcon通过MVCC解决了幻读。

Serialization

强制事务排序，从而解决幻读。但会产生大量超时和锁竞争。

1.3.2死锁

Innodb可以注意到循环相关性并快速返回错误，然后回滚开销最小（有最少排他行锁）的事务。其它存储引擎会有一个time out来解决。

1.3.3事务日志

在事务中，存储引擎先更新内存中的数据缓存cache，再将更新记录写入事务日志（redo），然后再合并写入磁盘。

1.3.4mysql中的事务

Mysqlab支持3个事务性存储引擎innodb、ndb cluster和falcon。第三方事务性存储引擎solidDB和PBXT。

Autocommit

Mysql默认为自动提交。

Show viariables like ‘autocommit’；

Set autocommit = 1或on（0或off）

该值的改变对于非事务存储引擎没有意义。

某些命令一旦执行将立即提交当前事务，如DDL命令，lock tables以及其它一些语句。

Set session或global transaction isolation level read committed 更改隔离级别

在事务中混合使用存储引擎

如何实现？？？？？？？？？？

隐式和显式锁

Innodb使用二相锁机制，它能在事务中随时请求锁，但是只有提交或回滚时才释放锁。并且同时释放全部锁。Innodb根据隔离级别自动加锁。

Innodb还支持显式锁，如

Select …lock in share mode

Select… for update

Mysql也在server层（不仅在存储引擎层）支持表锁。

建议不要使用表锁，除非你在事务中并且禁用了autocommit。Why？？

1.4Mvcc

MVCC非mysql特有。MVCC避免了很多情况下的加锁操作，大大降低了系统开销。

每种存储引擎实现MVCC方式是不同的。例如乐观并发控制、悲观并发控制。

InnodbMVCC的实现:

Innodb每行增加了两个隐含值，一个记录了该行创建时的事务版本号DB_TRX_ID1，一个记录了改行被修改或删除时的事务版本号DB_TRX_ID2。

当事务隔离级别为repeatabl read时的实际应用方式：

Select

Innodb检查每行数据，确保它们符合两个标准：

.行数据的DB_TRX_ID1早于或等于当前事务版本

.行数据的DB_TRX_ID2未定义或大于当前事务版本

Insert

Innodb为每个新增行记录当前事务版本号到DB_TRX_ID1

Delete

Innodb为每个删除行记录当前事务版本号到DB_TRX_ID2

Update

复制需要更新的行，在该行的拷贝上记录当前事务版本号到DB_TRX_ID2；更新需要更新行的数据，并修改DB_TRX_ID1为当前事务版本号。

优点：使大多数读操作都不必申请加锁，加快了读操作。

缺点：存储引擎必须为每行数据，存储更多的额外数据，做更多行的检查工作，以及处理一些额外的整理操作（housekeeping operations）。

MVCC只工作在repeatable read和read committed两个隔离级别。在read uncommitted下的查询只读取最新版本的数据行；serialization下会对每个返回行都进行加锁。

http://blog.csdn.net/chen77716/article/details/6742128

该文中认为INNODB中的MVCC为行记录添加了6字节的事务id和7字节的回滚指针。Ｚ这里用DB_TRX_ID2只是便于理解。

1.5mysql存储引擎

Mysql使用文件系统来存储数据库名和表定义，大小写敏感性依赖于具体的平台。Windows大小写不敏感，unix类平台上大小写敏感。.frm文件存储表定义。每种存储引擎对表数据和索引的存储方式有所不同，但表定义是由mysql服务器独立处理的。

查看表的基本信息

mysql> show table status like 'table1'\G

*************************** 1. row ***************************

Name: table1     

Engine: MyISAM

Version: 10

Row_format: Fixed    ### 行格式，myisam还有dynamic和compressed

Rows: 3         ###对于非事务表该行数精确，事务性表则只是个估算值

Avg_row_length: 7

Data_length: 21

Max_data_length: 1970324836974591   ###整个表可容纳的字节数

Index_length: 1024

Data_free: 0

Auto_increment: NULL      ###下个auto_increment值

Create_time: 2012-03-05 21:40:42

Update_time: 2012-03-21 20:32:51

Check_time: NULL   ###使用check table命令或myisqnchk工具检查表的最近时间

Collation: latin1_swedish_ci

Checksum: NULL   ###如开启，则实时计算整表内容的checksum

Create_options:     ##创建表时的其它选项

Comment: ##myisam表显示注释；innodb表显示剩余空间；视图，注释里包含view字样

1.5.1myisam

Myisam在旧版本中为mysql的默认存储引擎，在性能和可用性之间提供了一种平衡。

存储

Myisam将每个表存储成两个文件：数据文件（.MYD）和索引文件（.MYI）。myisam的格式是平台通用的。

Myisam会根据表定义自动选择行格式为动态行还是静态行。Myisam表可容纳的总行数，一半只受限于数据库服务器的可用磁盘空间大小，以及操作系统允许创建的最大文件大小。

在mysql5.0中，默认配置的含有可变长行定义的myisam表支持256  TB的数据处理，并使用6字节的指针记录数据（2的48次位就是256T）。旧版本默认只支持4字节记录指针和4GB.mysql最大支持8字节记录指针，16384PB。

max_rows，max_rows，Max_data_length具体含义和相互关系？？？？

Myisam特性

加锁和并发

Myisam只有表锁。在select的同时，可以对表进行插入。

自动修复

Mysql支持对myisam表的自动检查和自动修复。参见myisamI/O调优。

手动修复

可以使用check table table_namehe repair table table_name对表进行检查和修复。当服务器离线时也可以使用myisamchk进行检查修复表。

索引特性

用户可以基于BLOB或text类型列的前500个字符，创建相关索引。Myisam支持全文索引。

延迟更新索引delayed key writes

使用选项delay_key_write创建的myisam表，在查询结束后并不马上将索引的改变写入磁盘，而是在inmemry key buffer中缓存索引改变数据，该选项默认开启。在清理缓冲区，或者关闭表时才将索引块转储到磁盘，这样大大提高了表的处理性能。对于表内容频繁变更的表非常有用。但是在服务器或系统崩溃时，索引将会损坏，需要修复。如使用myisamchk在重启mysql前进行修复，也可以使用automatic recovery选项进行修复。Delay_ key_write可以是全局配置，也可以为个别表配置。

压缩的myisam表

压缩表被创建后不能被更改。由于占用磁盘空间很小，磁盘寻道时间将减少，使之获得更快的读性能。要修改压缩表必须先解压,修改，再压缩。

1.5.2myisam merge引擎

Merge引擎是myisam的变种。Merge table是指将几个相同的myisam表合并为一个virtual table。对于mysql日志记录或数据仓库应用特别有用。

1.5.3innodb引擎

Innodb是专为事务处理设计的存储引擎，特别是用于处理大量短期事务short-lived。短期一般能正常完成，不需要回滚的事务。

Innodb数据存储在共享表空间或独立表空间内。

Innodb使用MVCC获取高并发性能。在默认隔离级别repeatable read上，使用next-key locking防止幻读产生：不仅对查询中的行进行加锁，而且还对索引结构中的gaps进行加锁，以防止幻影phantom插入。

Innodb表基于聚簇索引cluster index建立（参见第三章）。Innodb的索引结构非常不同于其它大多数存储引擎，它提供了非常快速的主键查找性能。不过它的辅助索引secondary index也会包含主键列。所以主键定义的比较大将导致索引也很大，因此尽量把主键设计得小点。Innodb不会压缩索引。

直到本书写作时innodb还不能根据排序sorting创建索引，而myisam支持。因此，当innodb加载数据和创建索引时要比myisam慢很多，任何改变innodb表结构的操作，会导致整个表重建，包括重建所有索引。

外键约束

Innodb做了各种内部优化。如可预测性的预读predictive read-ahead，支持在磁盘中事先读取数据prefetching；自适应的hash index；插入缓冲区。。。两次写。。。

建议阅读官方文档中的the innodb transaction model and locking

http://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-model.html

1.5.4memory引擎

数据永不改变或者重启后不需要保留数据情况下可以使用内存表。由于数据全部保存在内存中，它的性能比myisam快一个数量级。一旦系统重启，除了表结构外，所有数据将丢失。

内存表的几个用途：

.用于lookup或mapping的表

.用于缓存periodically aggerageted data的结果

.用于保存数据分析中产生的中间结果

内存表支持hash索引，这使得其查询非常迅速。

内存表使用表锁，只支持较低的写并发，也不支持text或BLOB字段类型。它们只支持固定大小行，如将varchar类型实际存储为char，这可能会浪费不少内存。

Mysql在处理保存中间数据的临时表时，也会在内部使用内存表。如果中间数据太大，或含有TEXT或BLOB字段类型，mysql会将其转换成myisam表，存储在磁盘上。

注意不要与临时表搞混。临时表是单个连接中可见，连接断开后就消失的短暂的表。它用create temporary table创建，可以使用任何存储引擎。

1.5.5archive引擎

Archive只支持select和insert，并且不支持索引。它比myisam使用更少的磁盘I/O，因为它缓存了写数据，并且用zlib对每行在插入时进行了压缩。Archive每次select都会导致full table scan。因此适合做数据记录（logging）和数据采集的应用。或者在需要快速插入的主服务器上使用achive，而在从服务器上的对应表用其它的存储引擎来拥有索引以提供高性能的分析服务（select）。

Archive支持行锁和一种提供高并发插入的特殊的缓冲机制。

1.5.6csv引擎

Comma-separated values逗号分隔符文件

1.5.7federated引擎

目前不是很成熟

1.5.8blackhole引擎

略

1.5.9NDB cluster引擎

略

1.5.10falcon引擎

略

1.5.11soliddb引擎

略

1.5.12PBXT引擎

略

1.5.13maria存储引擎

Mysql设计者开发设计，目的是用来替代myisam。

1.4.14存储引擎的选择

可以为每个表选择合适的存储引擎，但是有时候如果可以为整个数据库只选择一种存储引擎会比较好。

考虑的因素

.事务

支持事务最好的是innodb。如果不需要事务处理，主要操作是一些select或insert操作，那么myisam更好。

.并发

如果只是需要并发的插入和读操作，那么myisam可以满足。如果需要混合性的并发操作，并且操作之间要互不干扰，那么需要支持行级锁的引擎。

.备份

各种引擎备份方式会不一样。如果同时使用了多种引擎，会使备份和服务器性能调优变得更加复杂。

.崩溃恢复

如果有大量数据，应该慎重考虑系统崩溃后需要多长时间恢复。通常myisam比innodb表更容易出错，恢复时间也更长。许多人就是基于这点，在不需要事务处理时也选择了innodb。

.特有特性

如聚集索引（innodb、soliddb），全文索引（myisam）。。。

 

All表示mysql3.23

1.4.15表转换

Alter table

Alter table table_name engine=innodb；

这种转换会消耗大量时间和系统I/O。为此需要执行一个旧表到新表的逐行复制，并且期间源表将被加读锁。如果做了引擎转换，原有引擎的特性将丢失。如将innodb转成myisam再转回innodb，将丢失最初定义的所有外键。

Dump和import

用mysqldump将表转储城一个文本文件，再编辑该文件，修改其中的create table语句（表名、引擎名），再导入。注意如果有drop database语句要删除。

Create和select

该方法在以上两种的速度和安全性之间做了个平衡。先创建一个表，再用insert……select语句来复制数据。

mysql> create table innodb_table like myisam_table;

mysql> alter table innodb_table engine=innodb;

mysql> insert into innodb_table select * from myisam_table;

如果数据量不大，这种方法效果不错。但是更高效的方法是增量地填充表，在填充每个增量数据块的时候都提交事务，这样就不会导致撤销日志（undo log）变得过于庞大。假定id是主键，可以重复运行下列查询（每次主键增大x和y的值），直至所有的数据都复制到新表。

mysql> start transaction;

mysql> insert into innodb_table select * from myisam_table where id between x and y;

mysql> commit;

数据复制完成后可以drop源表。如有必要要在转移时加锁源表，以防止复制时数据不一致。

Mysql版本表

Version3.23 （2001）

Myisam替代了isam。Innodb出现，但是需要自己编译安装。引入了全文索引和复制。

Version4.0（2003）

出现了新的语句，如union，多表的delete。复制重写为两个线程（以前为一个线程）。Innodb内嵌在了mysql中，并且支持了行锁、外键等。导入了查询缓存，而且以后都没什么改变。开始支持SSL连接。

Version4.1（2005）

导入了更多的语句，如子查询和insert on duplicate key update。增加UTF-8字符集。GROUP BY语句增加了ROLLUP，mysql.user表采用了更好的加密算法,innodb开始支持单独的表空间。

Version 5.0 (2006)

增加了Stored procedures、stored functions、Views、Cursors、Triggers、XA transactions的支持，增加了INFORMATION_SCHEMA系统数据库。去除了iSAM存储引擎。

Version 5.1 (2008)

Sun收购mysql ab后的第一个版本

增加了Event scheduler，Partitioning，Pluggable storage engine API ，Row-based replication、Global级别动态修改general query log和slow query log的支持。去除了federated 等存储引擎。Oracle 发布Innodb plugin。

Version 5.5 (2010)

Oracle收购sun后的第一个版本，主要着重在性能、扩展性、复制、分区和对windows平台的支持。增加了performance—schema系统数据库。参见mysql各版本的新特性整理by mysqlops.doc

Version 5.6 (Unreleased)

更大的改变