MySQL高性能——第一章

第一章 MySQL架构和历史

MySQL单库经受住最高达6.5万的QPS，32个节点的核心集群的总QPS稳定在86万

Percona Toolkit（perl写）

http://planet.mysql.com优秀的技术博客

 

1.1逻辑和架构：

1.1.1连接管理以及安全性验证

连接管理:是重新生成连接还是从连接池中取出连接复用

安全性验证：连接权限（用户名和密码）以及访问权限（DB和表的查询权限）

1.1.2执行和优化：

先进行解析，然后再优化执行

对于SELECT类型的查询，先判断cache中是否有结果

1.2并发控制：

1.2.1锁

读写锁（共享锁和排他锁）

读锁：多个用户可以同时操作

写锁：完全排他的，同时只有一个用户可以操作

1.2.2锁粒度

锁粒度的控制，提高共享资源的并发性

锁策略就是锁的开销和数据安全性之间找到平衡

表锁最基本的锁策略，开销最小。MySQL自身的一些操作会使用到表锁，例如alter table,忽略具体的存储引擎

行锁带来最大的并发度，也带来最大的开销。且只在存储引擎阶层实现。

1.3事务

1.3.1ACID

1.3.2事务隔离级别

Read uncommit（未提交读）

一个事务还没有提交，其他事务可以看到该事务更新的数据，存在脏读

Read commit:

一个事务在其他事务提交之后就可以读到它的更新内容。这个存在不可重读，对于这个事务来说两次读取的内容会不一致。

Repeatable Read :

一个事务在其他事务提交之后读到的资源前后两次是一致的。可能存在幻读。

一个事务锁住一个范围的资源，另一个在事务操作该范围的数据，在该事务提交的时候可能会发现这些数据已经存在了。

Innodb和XtraDB通过MVCC来解决这个问题，范围锁。

 

Seriazable Read:强制将事务串行，避免幻读

1.3.2死锁

死锁是指两个或者多个事务在同一个资源上相互占用，并请求锁定对方占有的锁。

Innodb逐行扫描并持有锁

死锁有两个原因：资源冲突和存储引擎自身的实现方式。

1.3.3事务日志

事务日志为提高事务效率，属于内存型以及追加型

操作的时候，日志先行，先写日志，后写数据，需要两次磁盘操作。

在日志持久化之后，内存中修改的数据才刷回磁盘中；如果日志已经持久化到磁盘中，系统出现崩溃，数据的恢复依赖持久化的日志。

1.3.4MySQL中的事务

对于事务类的引擎，设置autocommit值会影响操作的提交方式，设置为1时默认提交；设置为0时候，多个操作为一个事务，在遇到commit或者rollback的时候，该事务才算结束。对于非事务类的引擎，设置该变量无影响。

MySQL存在一些操作默认将事务提交，如Alter table、Lock tables等

MySQL可以设置事务隔离级别，mysql>set session transaction isolation XXX

在事务中混合存储引擎，对于事务回滚的操作，非事务的存储引擎操作不会撤销，会导致数据存在不一性。

隐式和显式锁定：

Innodb采用的是两阶段锁定协议。在事务执行的过程中，随时都可以执行锁定，锁只有在commit或Rollback的时候才释放，并且所有的锁是在同一时刻被释放。这个描述的锁定为隐式。

显式锁定：

Select...Lock In share mode

Select …for Update

MySQL也支持Lock Tables和Unlock tables，这些是服务器层的锁定，和具体引擎无关。

1.4多版本并发控制（MVCC）

MVCC是行锁的一种变种，在很多情况下避免加锁操作，降低开销，例如非阻塞的读操作，以及写操作只锁定必要的行。

MVCC的实现是通过保存数据在某个时间点的快照来实现。

Innodb之MVCC

（1）解释：

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

在REPEATABLE READ隔离级别下，MVCC是如何操作的？

SELECT

Innodb只查找符合下面两个条件的记录：

a、Innodb只查找版本早于当前事务版本的数据行；

确保这些数据已经在事务开始之前已经存在或者是是当前事务自身插入或者修改产生的

b、行的删除版本要么未定义，要么大于当前事务的版本号

确保可以读到这些删除的数据，或者在事务开始之前未删除的

INSERT

Innodb为新插入的每一行保存当前系统版本号作为行版本号

DELETE

Innodb为删除的每一行保存当前系统版本号为行删除标识

UPDATE

Innodb为插入一行新纪录，保存当前系统版本号作为行版本号；同时保存当前版本号到原来删除行的版本号。

保存每行额外系统版本号

优点：使大多数的读操作不需要锁行

缺点：需要额外的开销以及更多的检查工作

MVCC只在REPEATABLE 和READ READ COMMITED隔离级别下才适应

READ Uncommited读取最新行数据，Serizable会读所有读的行都加锁。

1.5MySQL存储引擎

 

1.5.1表概述

存在一个以表名开头的.frm文件

show  table status like '%XX_table%'，输入结果如下：

 

每列表示的含义如下

Name 表名

Engine 引擎的类型

Row_format 行的格式，对于MyISAM表，可选的值为Dynamic、Fixed或者compressed

Rows表中的行数，MyISAM为准确的数据，Innodb为预估的数据

Avg_row_length 平均每行包含的字节数

Data_length为表数据的大小（以字节为单位）

Max_data_length 表数据的最大容量，该值和存储引起有关

Index_length 索引的大小（以字节为单位）

Data_free 对于Myisam表，表示已分配但目前没有使用的空间。这部分空间包括之前删除的行以及后续可以被Insert利用到的空间

Auto_increment 下一个AUTO_INCREMENT的值

Create_time 表的创建时间

Update_time 表数据的最后修改时间

Check_time 使用CHECK TABLE 命令或者yisamcheck

Collation 表的默认字符集和字符集排序规则

Create_options创建表时指定的其他选项（包括哪些呢）

Comment 该列包含一些其他的额外信息。对于MyIsam表，为表的注释信息；对于Innodb表，保存Innodb的剩余空间信息。如果是一个视图，该列包含“View”的文本 

1.5.1 Innodb存储引擎概述

表空间：Innodb的数据存储在表空间中

Innodb采用MVCC来支持高并发,其默认级别为可重复读,并通过间隙锁(next-key locking)策略防止幻读的出现.间隙锁使得Innodb不仅锁定查询设计的行,还会对索引中的间隙进行锁定,以防止幻影行的插入.

Innodb表是基于聚簇索引建立的.聚簇索引对主键查询有很高的性能不过二级索引(secondary index,非主键索引)中必须包含主键列,所以如果主键列很大的话,其他的所有索引都会很大.因此若表上的索引较多的话,主键应当尽可能的小。Innodb的存储格式是平台独立的。

 Innodb内部优化：从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index）以及能够加速插入操作的插入缓冲

 建议详细阅读手册 Innodb事务模型和锁一节

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

 作为事务型的存储引擎，Innodb支持真正的热备份XtraBackup

 1.5.2 MyISAM存储引擎

在MySQL5.1以及之前的版本，MyISAM是默认的存储引擎。MyISAM特性：全文索引、压缩、空间函数（GIS），但MyISAM不支持事务和行级锁，故崩溃后无法安全恢复，因此在实际线上默认是Innodb引擎。

 存储

MyISAM表有两个文件：数据文件和索引文件，分别.MYD 和.MYI为扩展名。MyISAM表可以包含动态或者静态（长度固定）行。MySQL会根据表定义采用何种行格式。MyISAM表存储表的行记录数。

 在MySQL5.0中，MyISAM表如果是变长行，则默认配置只能处理256TB的数据，因此指向数据记录的指针长度是6个字节（256TB行记录，256*1024G*1024M*1024K*1024行，=281474976710656/4294967296（2的32次方）=65536/65536（2的16次方），故48个bit对应6个字节）。

要改变MyISAM表指针的长度，可以通过修改表的MAX_ROWS和AVG_ROW_LENGTH选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引。

 MyISAM特性

加锁与并发

MyISAM对整长表加锁，而不是针对行。读加共享锁；写加排他锁。在表有读取查询的时候，可以往表中插入新的记录（并发插入）

 修复

对于MyISAM的修复，可以通过CHECK TABLE mytable检查表的错误，如果有错误可以通过执行REPAIRE TABLE mytable进行修复。如MySQL服务器已经关闭，可以通过myisamchk命令行工具进行检查和修复操作。

 索引特性

前缀索引，对于BLOB和TEXT等长字段（基于其前500个字符创建）

支持全文索引

延迟更新索引见

创建MyISAM表的时候，如果指定DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘中。可以提升写入的性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

延迟更新索引键的特性，可以在全局设置，也可以为单个表设置。

 MyISAM压缩表

表创建后导入数据，不再修改，这样的表适合采用MyISAM压缩表。

 可以使用myisampack对MyISAM表进行压缩。压缩表时不能进行修改（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表减少空间占用，减少磁盘IO，提升查询性能。压缩表也支持索引，但索引也是只读的。

压缩时表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表。

 MyISAM性能

性能扩展限制：索引缓冲区的Mutex锁，MariaDB基于段的索引键缓冲区机制来避免该问题。

最典型的性能问题是表锁问题。

 1.5.3MySQL内建的其他存储引擎

Archive引擎

只支持INSERT和SELECT操作，在MySQL5.1之前也不支持索引。Archive引擎会缓存所有的写并利用zlib对插入的进行压缩，所以比MyISAM表的磁盘ＩＯ少。但是每次select查询需要全表扫描。所以更适合日志和数据采集类应用，这类应用做数据分析时往往需要扫描全表。或者一个需要快速INSERT操作的场合下也可以使用。

 支持行级锁和专用的缓冲区，所以可以实现高并发插入。

在一个查询会阻塞其他的查询。批量插入在完成之前对读操作是不可见的。

不是事务型的引擎

Blackhole引擎

没有实现任何的存储机制，丢失所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，用于复制数据到备库，用于特殊的复制架构和日志审核时。

运维实践中碰到很多问题，因此并不推荐。

CSV引擎

将普通的CSV文件（逗号分割值的文件）作为MySQL表来处理，这种表不支持索引。

CSV可以作为一种数据交换的机制。

Federated引擎

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

目的是跨服务器，但也经常带来问题，默认是禁用的。

Memory引擎

适用场景：

需要快速访问数据，并且这些数据部不会被修改，重启后丢失也没有关系，推荐使用。

对于重启丢失问题，可以通过从库数据的固化解决。

 1、用于查找或者映射表

2、用于缓存周期性聚合数据的结果

3、用于保存数据分析中产生的中间数据

 所有的数据都保存在内存中，不需要进行磁盘IO，Memory表的结构在重启以后还会保留，但数据会丢失。

 支持Hash索引，是表级锁，并发写入性能低。不支持BLOB和TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR列也会转换成CHAR，可能导致部分内存的浪费（其中一些限制在Percona版本已经解决）

 MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表，如果中间结果超过Memory表的限制或者含有BLOB或TEXT字段，则将临时表转换成MyISAM表

 Merge引擎

Merge引擎室MyISAM引擎的一个变种，由多个MyISAM《

表合并而来的虚拟表。一般用于日志或者数据仓库类应用。引入分区功能后，该引擎被废弃。

NDB集群引擎

MySQL服务器、NDB集群存储引擎以及分布式、share-nothing的、容灾、高可用的NDB数据库的组合，被称为MySQL集群（MySQL Cluster）

 1.5.4第三方存储引擎

OLTP类引擎

XtraDB存储引擎，基于Innodb引擎的一个改进版，在Percona Server和MariaDB中使用，主要改进点在于性能、可测量性和操作灵活性方面。可以作为Innodb的一个完全替代产品，可以兼容地读写Innodb的数据文件，并支持Innodb的所有查询

 PBXT引擎

和innodb类似，支持ACID事务和MVCC，支持引擎级别的复制、外键约束，对SSD提供适当的支持。对较大的值类型如BLOB也做了优化。PBXT是社区支持的存储引擎，MariaDB包含该引擎。

 TokuDB引擎

使用分形树的索引数据结构，该结构与缓存无关的，不会随着数据量超过内存而产生性能下降。TokuDB是一种大数据存储引擎，拥有很高的压缩比，可以在很大的数据量上创建大量索引。

目前还在研发中，在并发性方面有很多限制。

 还有RethinkDB存储引擎和Falcon存储引擎。

 面向列的存储引擎

Infobright引擎

在非常大的数据量（数10TB）时，该引擎工作良好。Infobright是为数据分析和数据仓库应用设计的。数据高度压缩，按照块进行排序，每个块都对应有一组元数据。在处理查询时，访问元数据可决定跳过该块，甚至可能只需要元数据即可满足需求。该引擎不支持索引。Infobright需要对MySQL服务器做定制，需要修改以适应面向列存储的需要，否则需要在服务器层进行转换按行处理，这个过程很慢。

 还有InfiniDB（可以在一组机器集群间做分布式查询），LucidDB和MonetDB

社区存储引擎

Aria引擎（解决崩溃安全恢复问题的MyISAM，以及缓存数据——MyISAM只能缓存索引）

SphinxSE该引擎为Sphinx全文索引搜索服务器提供SQL接口

1.5.5 选择合适的引擎

大部分情况下，Innodb都是正确的选择，所以MySQL5.5版本时终于将Innodb作为默认的存储引擎。 

选择场景：

1、除非需要某些Innodb不具备的特性，并且没有其他办法可以代替，否则都应该优先选择Innodb引擎；

2、如果用到全文索引，建议优先考虑Innodb加上Sphinx的组合，而不是使用支持全文索引的MyISAM；

3、如果不需要用到Innodb的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。例如如果不在乎可扩展以及并发性，也不在乎崩溃后的数据丢失问题，却对Innodb的空间占用过多比较敏感，可以选择MyISAM。

尽量避免混合使用多种存储引擎。

选择引擎考虑的因素点：

1、事务

  需要事务支持的话，选择Innodb或者XtraDB；不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。一般日志型的应用比较符合这一特性。

2、备份

需要在线热备份，选择Innodb就是基本要求。

3、崩溃恢复

数据量比较大的时候，需要考虑系统崩溃后如何快速地恢复的问题。相对而言，MyISAM崩溃后发生损坏的概率比Innodb要高很多，且恢复速度也要慢。考虑该因素，也是选择Innodb引擎的原因之一。

4、特有的特性：

有些应用可能依赖一些存储引擎所独有的特性或者优化，比如依赖聚簇索引的优化。例如MySQL中也只有MyISAM支持地理空间搜索。

相关场景总结

日志型应用

记录日志，且对插入速度有很高的要求，选择MyISAM或者Archive存储引擎比较合适。如果需要对记录的日志做分析报表，生成报表的查询SQL会导致插入效率明显降低，如果解决？

解决方案：

1、使用主从架构，主库提供写服务，从库提供读服务，可以避免生成报表的SQL对写入的干扰；

2、使用分表的方式：日志记录表的名字按照包含年和月的信息，在历史表上查询，在当前表上执行插入操作，也可以避免干扰。

第一种方案在实际生成环境中，会导致同步延迟，但可能不影响报表的生成效果（查历史数据）。

只读或者大部分情况下只读的表

有些表的数据如用于编制类目或者分列清单，是典型的读多写少的业务。如果不介意MyISAM的崩溃恢复问题，选择MyISAM最合适。

不要轻易相信"MyISAM比Innodb快"之类的经验之谈，实际生产环境中，Innodb的速度更快，尤其是使用到聚簇索引或者需要访问的数据都可以放入内存的应用。

因此建议采用Innodb。

订单处理

支持事务就是必要选项，保证数据的完整性和一致性。另外一个重要的考虑点事存储引擎对外键的支持情况。Innodb是最佳选择。

电子公告牌和主题讨论论坛

多数应用只设计几张表来保存所有的数据，所以核心表的读写压力非常大。为保证这些核心表的数据一致性，锁成为资源争用的主要因素。

设计缺陷导致性能问题。

CD-ROM应用

考虑使用MyISAM表或者MyISAM压缩表，这样表之间可以隔离并且可以在不同介质上相互拷贝。MyISAM压缩表比未压缩的表节约很多空间，但压缩表是只读的。在实际应用中，需要考虑该问题。

大数据量

什么样的数据量算大？线上Innodb数据库单机的数据量在3~5TB

，关键在于选择硬件，做好物理设计，并为服务器IO瓶颈做好规划。

如果数据量绩效增长到10TB以上的级别，可能需要建立数据仓库。Infobright是MySQL数据仓库最成功的解决方案。但有一些大数据库不适合Infobright，却可能适合TokuDB。

 

1.5.6 转换表的引擎

三种方案

Alter table

优点：最简单，适用任何存储引擎

缺点：需要执行时间较长。原因在于MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的ＩＯ能力，同时原表上会加上读锁。（如果是对主键操作的话，会加写锁）

如果转换表的存储引擎，将会失去和原引擎相关的所有特性。

Innodb->MyISAM->Innodb,原Innodb表上所有的外键将丢失。

导出与导入

使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项,注意同时修改表名。mysqldump默认会自动在CREATE TABLE语句前加上DROP TABLE 语句,不注意这一点可能会导致数据丢。

优点：说安全，没发现。

缺点：需要手动编辑mysqldump文件，要保持新表和原表一致性也需要对原表加锁。

创建与查询（CREATE 和SELECT）

第三种转换的技术综合第一种方法和高效和第二种的安全。

不需要导出整个表的数据，而是先创建一个新的存储引擎的表，然后利用INSERT...SELECT语法来导数据。

上图只适用于数据量不大的情况下 

对于数据量很大，需要分批处理，针对每一段数据执行事务提交操作，以避免大事务产生过多的undo。

方法如下：

假设有主键字段id，重复运行以下语句（最小值x和最大值y运行相应的替换）将数据导入到新表：

同样在执行的过程中对原表加锁，以确保新表和原表的数据一致。

Percona Toolkit 提供一个pt-online-schema-change的工具。（要研究该工具的思想，而并不仅仅是用法，并关注新版本的变化）

1.6 MySQL时间线（版本发展）

版本3.23 （2001）

引入MyISAM，实现SQL查询，Innodb引擎已经可以使用但需要手工编译。还引入全文索引和复制。 

版本4.0（2003）

支持新的语法，比如UNION和多表DELETE。重写复制，在备库使用两个线程来实现复制。Innodb成为标准配备，包括全部特性：行级锁、外键等。版本4.0中还引入了查询缓存，同时还支持通过SSL进行连接。 

版本4.1（2005）

引入新语法，比如子查询和INSERT ON DUPLICATE KEY UPDATE。支持UTF-8字符集，支持新的二进制协议和prepared语句。 

版本5.0（2006）

一些“企业级”特性：视图、触发器、存储过程和存储函数。老的ISAM引擎的代码被彻底移除，引入新的Federated等引擎。 

版本5.1（2008）

引入分区、基于行的复制以及plugin API（包括可插拔存储引擎的API）。移除了BerkeyDB 引擎，Federated等也被放弃。Oracle收购的Innodb发布了Innodb plugin。 

版本5.5（2010）

这是Oracle收购sun以后发布的首个版本。版本5.5的主要改善集中在性能、扩展性、复制、分区、对微软Windows系统的支持，以及一些其他方面。

Innodb成为默认的存储引擎。增加了PERFORMANCE_SCHEMA库，包含了一些可测量的性能指标的增强。增加了复制、认证和审计API。半同步复制插件进入实用阶段。Innodb在架构方面也做了较大的改进，比如多个子缓冲实例（buffer pool） 

版本5.6

一些改变：对查询优化器进行大规模的改进，更多的插件API（比如全文索引）、复制的改进以及PERFORMANCE_SCHEMA库增加更多的性能指标。Innodb也有大量的改进工作。

MySQL5.5主要着重在基础部分的改进和加强，引入了部分新特性。而MySQL5.6则在MySQL5.5的基础上提升服务器的开发和性能。 

版本6.0（已经取消）

很多特性在版本5.5和5.6中体现。 

各版本间的性能测试：

 

 

测试说明：

机器：Cisco UCS C250 两颗6核CPU，每个核支持两个线程，内存384GB，测试的数据集市2.5GB，MySQL的buffer pool设置为4GB 

测试工具：

SysBench的read-only只读测试进行压测，采集Innodb存储引擎 

测试结论:

（1）在线程数据低于16个测试结果显示，高版本反而比低版本要差一些；在继续加大线程数据之后，5.5和5.6的性能提升显著。

原因在于新版本的SQL语法更复杂，针对复杂查询增加了很多特性和改进，对于简单查询可能带来了更多的开销。旧版本的代码简单，对于简单的查询反而会更有利。 

（2）在不同线程数据下，5.1的性能低于5.0的，但是加上Innodb plugin之后的扩展性更好。 

版本的选择更多地取决于业务需求而不是技术需求。 

1.7MySQL的开发模式

 MySQL依然遵循GPL开源协议，全部源代码（除了一些商业版本的插件）都会开放给社区。

 将更多的特性做成插件的开发模式

1.8总结

 分层架构：上层是服务器的服务和查询执行引擎，下层则是存储引擎。两层之间的交互式核心，是MySQL基础架构的精髓。