《高性能MySQL》笔记一

在大规模水平集群的架构设计中，开源的MySQL受到的关注度越来越高。到2012年整个淘宝网的核心交易系统已经全部运行在基于PC服务器的MySQL数据库集群中。

第1章 MySQL架构与历史

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

1.1 MySQL逻辑架构

第一层 客户端

第二层 服务器层   包含大多数MySQL核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数。所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等；

第三层 存储引擎   存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信。（InnoDB会解析外键定义，这是一个例外）

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程。服务器会缓存线程，因此不需要为每个新建的连接创建或者销毁线程（MySQL5.5支持线程池插件）。

1.1.2 优化与执行

MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。

优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。因为优化器会请求存储引擎提供容量或者某个具体操作的开销信息，以及表数据的统计信息等。

对于SELECT语句，在解析查询前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。（详见第7章）

1.2 并发控制

两个层面：服务器层、存储引擎层

1.2.1 读写锁

读锁，也叫共享锁(shared lock)，不阻塞读锁，但是阻塞写锁；

写锁，也叫排他锁(exclusive lock)，会阻塞其他的写锁和读锁；

1.2.2 锁粒度

由于加锁也要消耗资源，所以理想方式是精确的锁定。所谓锁策略，就是在锁的开销和数据的安全性之间寻求平衡。MySQL支持多个存储引擎的架构，每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

两种重要的锁策略：表锁（table lock）和行级锁（row lock）

服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

InnoDB和XtraDB，以及其它一些存储引擎中实现了行级锁（行级锁只在存储引擎层实现，服务器层没有实现）。/

1.3 事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。

START TRANSACTION

...

COMMIT;

事务的特性ACID（atomicity，consistency，isolation，durability）

1.3.1 隔离级别

ANSI SQL隔离级别

未提交读，事务可以读取未提交的数据，被称为脏读。

提交读，大多数数据库系统的默认隔离级别。有时也叫做不可重复读，因为两次执行相同的查询，可能会得到不一样的结果。

可重复读，理论上无法解决幻读（Phantom Read）。所谓幻读，指某个事务在读取某个范围的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。

                   InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC)解决了幻读，可重复读是MysSQL默认隔离级别。

可串行化，强制事务串行执行。

1.3.2 死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求对方占用的资源，从而导致恶性循环的现象。越复杂的系统，比如InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。InnoDB目前处理死锁的方式是，将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回滚算法）。

锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因：有些是因为真正的数据冲突，这些情况通常很难避免，有些则完全是由于存储引擎的实现方式导致的。

1.3.3 事务日志

1.3.4 MySQL中的事务

MySQL提供了两种支持事务的存储引擎：InnoDB和NDB Cluster。另外还有一些第三方的存储引擎也支持事务，比较知名的包括XtraDB和PBXT。

InnnoDB采用的是两阶段锁定协议。

MySQL也支持LOCK TABLES 和UNLOCK TABLES,这是在服务器层实现的。

1.4 多版本并发控制（MVCC）

可以认为MVCC是行级锁的一个变种，它在大多数情况下避免了加锁，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC是通过保存数据在某个时间点的快照来实现的，典型的有乐观并发控制和悲观并发控制。

MVCC只在REPEATABLE和READ COMMITTED两个隔离级别下工作。

1.5 MySQL的存储引擎

InnoDB基于聚簇索引建立。

转换表的引擎有三种方式：

1）ALTER TABLE

ALTER TABLE mytable ENGINE = InnoDB;

2) 导出与导入

3）创建与查询（CREATE和SELECT)

第2章 MySQL基准测试

这章的内容可能有些陌生，平时开发也不怎么用到。那么重不重要呢？书中第一行就告诉我们，”基准测试（benchmark）是MySQL新手和专家都需要掌握的一项基本技能“。显然，这玩意儿很重要，即使我们不是dba而只是开发。那么先牢记下这个单词，benchmark，benchmark，benchmark......

什么是基准测试？一言以蔽之：针对系统设计的一种压力测试。

通常的目标是为了掌握系统的行为。但也有其他原因，如重现某个系统状态。或者是做新硬件的可靠性测试。

2.1 Why we need benchmark

为什么基准测试很重要？因为基准测试是唯一方便有效的、可以学习系统在给定的工作负载下会发生什么的方法。

基准测试可以完成以下工作：

1、验证基于系统的假设

2、重现系统中某些异常行为

3、测试当前系统运行情况

4、模拟比当前系统更高的负载

5、规划未来业务增长

6、测试应用适应可变环境的能力

7、测试不同的硬件、软件和操作系统配置（很多是dba需要关注的，对于开发，我们需要关注mysql版本、使用不同存储引擎的影响）

8、证明新采购的设备是否配置正确。

基准测试不是真实压力测试，其施加给系统的压力相对真实压力通常较为简单。

基准测试要尽量简单直接，结果之间容易相互比较，成本低且易于执行。

2.2 strategy of benchmark

两种策略:

1、集成式（full-stack）针对整个系统

2、组件式（single-component）单独测试MySQL

2.2.1 测试何种指标

有时候需要用不同的方法测试不同的指标。比如，针对延迟（latency）和吞吐量（throughput）就需要采用不同的测试方法。以下是常见的指标：

1、吞吐量

吞吐量指的是单位时间内的事务处理数。这一直是经典的数据库应用测试指标。一些标准的基准测试被广泛地引用，如TPC-C。这类基准测试主要针对在线事务处理（OLTP）地吞吐量，非常适用于多用户地交互式应用。常用地测试单位是每秒事务数（TPS），有时也采用每分钟事务数（TPM）。

2、响应时间或者延迟

3、并发性

并发性基准测试关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数。当并发性增加时，需要测量吞吐量是否下降，响应时间是否变长，如果是这样，应用可能就无法处理峰值压力。

可以使用sysbench测量数据库的并发性，在测试期间记录MySQL数据库的Threads_running状态值。

4、可扩展性

指的是，给系统增加一倍资源（比如两倍CPU数），就可以获得两倍吞吐量。大多数系统无法做到如此理想的线性扩展。

2.3 基准测试方法

先看下如何避免一些常见的错误，这些错误可能导致测试结果无用或者不精确：

• 使用真实数据的子集而不是全集
• 使用错误的数据分布
• 使用不真实的分布参数
• 在多用户场景中，只做了单用户测试
• 在单服务器上测试分布式应用
• 与真实用户行为不匹配
• 没有检查错误
• 忽略了系统预热
• 使用默认的服务器配置
• 测试时间太短

2.3.1 设计和规划基准测试

2.3.2 基准测试应该运行多长时间

2.3.3 获取系统性能和状态

2.3.4 获得准确的测试结果

2.3.5 运行基准测试并分析结果

2.3.6 绘图的重要性

2.4 基准测试工具

没有必要开发自己的基准测试工具，除非现有的工具确实无法满足需求

2.4.1 集成式测试工具

ab 单个URL

http_load  可以多个URL

JMeter 比ab和http_load复杂的多。它可以通过控制预热时间等参数，更加灵活地模拟真实用户的访问。JMeter拥有绘图接口，还可以对测试进行记录，然后离线重演测试结果。

2.4.2 单组件式测试工具

mysqlslap

MySQL Benchmark Suit（sql-bench)

Super Smack

Database Test Suite

Percona's TPCC-MySQL Tool

sysbench 多线程系统压测工具，支持lua脚本。

 

附：MYSQL的BENCHMARK()函数

2.5 基准测试案例

2.5.1 http_load

2.5.2 MySQL Benchmark Suit

2.5.3 sysbench（强烈推荐）

sysbench可以执行多种类型的基准测试，它不仅设计用来测试数据库的性能，也可以测试运行数据库的服务器的性能。

sysbench的CPU基准测试

sysbench的文件I/O基准测试

sysbench的OLTP基准测试

2.5.4 数据库测试套件中的dbt2 TPC-C测试

2.5.5 Percona的TPCC-MySQL测试工具

2.6 总结

每个MySQL的使用者都应该了解一些基准测试的知识。基准测试不仅仅是用来解决业务问题的一种实践行动，也是一种很好的学习方法。学习如何将问题分解成可以通过基准测试来获得答案的方法，就和在数学课上从文学题目中推导出方程式一样。首先正确地描述问题，之后选择合适的基准测试来回答问题，设置基准测试的持续时间和参数，运行测试，收集数据，分析结果数据，这一系列的训练可以帮助你成为更好的MySQL用户。

 

第3章 服务器性能剖析（profiling）

3.1 性能优化简介

数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

性能剖析一般有两个步骤：测量任务所花费的时间；然后对结果进行统计和排序，将重要的任务排到前面。

在对系统进行性能剖析前，必须先要能够进行测量，这需要系统可测量化的支持。MySQL从5.5版本开始提供Performance Schema，默认关闭，到5.6版本默认打开。Performance Schema的存储引擎为专门设计的PERFORMANCE_SCHEMA引擎，普通的用户表无法设置使用这种引擎。

3.2 对应用程序进行性能剖析

推荐工具 New Relic、xhprof、Ifp或者MySQL的企业监视器 Enterprise Monitor

3.3 剖析MySQL查询

3.3.1 剖析服务器负载

使用慢查询日志（长期开启需要考虑磁盘负载，可部署日志轮转工具），建议用pt-query-digest生成剖析报告。主要关注剖析报告中的V/M列和执行计划列。V/M列提供了方差均值比的详细数据，方差均值比就是常说的离差参数。离差指数高的查询对应的执行时间的变化较大，而这类查询通常都值得去优化。

如果因为某些原因如权限不足等，无法在服务器上记录查询。有两种替代技术：

• 通过--processlist不断查看SHOW FULL PROCESSLIST的输出。
• 抓取TCP网络包，通过tcpdump将网络包保存到磁盘，然后用pt-query-digest解析并分析查询。

3.3.2 剖析单条查询

使用SHOW PROFILE

使用SHOW STATUS

3.3.3 使用性能剖析

3.4 诊断间歇性问题

3.4.1 单条查询问题还是服务器问题

3.4.2 捕获诊断数据

3.4.3 一个诊断案例

3.5 其他剖析工具

3.5.1 使用USER_STATISTICS表

3.5.2 使用strace