高性能mysql读书笔记（一）

第1章 MySQL架构与历史

MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构将查询处理与数据的存储/提取相分离，使得可以在使用时根据不同的需求来选择数据存储的方式。

MySQL逻辑架构

服务器通过API与存储引擎进行通信，这些API屏蔽了不同存储引擎之间的差异。存储引擎API包含几十个底层函数，不同存储引擎不会去解析SQL，而只是简单地响应上层服务器的请求。

连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个线程中执行，该线程只能轮流在某个CPU核心中运行。MySQL基于线程池来管理线程（创建、缓存、销毁）。
当客户端连接到MySQL服务器时，服务器会基于用户名、主机信息、密码等进行认证（SSL）。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

优化与执行

MySQL会解析查询并创建查询解析树，然后对其进行各种优化，如重写查询、决定表的读取顺序、选择合适的索引等。用户可以通过使用关键字提示优化器，从而影响它的决策过程。explain解释优化过程各个因素
优化器不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的：优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如索引
对于SELECT语句，在解析查询之前，服务器会先检查查询缓存，如果找到对应的查询，就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

并发控制

读写锁

处理并发读或者写时可以使用共享锁和排他锁，也叫读锁和写锁

锁粒度

加锁本身也需要消耗资源，锁策略就是在锁的开销和安全性之间寻求平衡。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

表锁：

开销最小的策略

会锁定整张表，在对表进行写操作之前，需要先获得写锁，获得写锁后将会阻塞其他用户对该表的读写操作。只有没有写锁时，其他用户才能获取读锁，读锁之间是不相互阻塞的。写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。

虽然不同的存储引擎都有自己的锁实现，MySQL自身仍然会在服务器层使用表锁并忽略存储引擎的锁机制，例如当执行ALTER TABLE时，服务器会使用表锁。

行级锁：

可以最大程度程度地支持并发处理（同时带来了最大的锁开销）

行级锁只在存储引擎层实现，MySQL服务器层没有实现。

事务

ACID：atomicity(原子性)、consistency(一致性)、isolation(隔离性)、durability(持久性)。

事务的ACID特性可以确保银行不会丢你的钱

隔离级别

隔离级别：SQL标准定义了4种隔离级别，每一种级别都规定了在一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低的隔离级别通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITED 事务中的修改，即使没有提交，对其他事务也是可见的。（因此会产生脏读）
READ COMMITTED 一个事务只能看见已经提交的事务所做的修改。
REPEATABLE READ 这是MySQL默认的事务隔离级别，保证在同一个事务中多次读取同样记录的结果是一样的。理论上该级别无法避免幻读的问题，InnoDB通过多版本并发控制解决了幻读的问题。
SERIALIZABLE 强制事务串行执行，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用问题。

可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别： SET SESSION TRANSACTION LEVEL READ COMMITTED;

死锁

两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环

当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一资源也会产生死锁。

为了解决问题，数据库系统实现了各种死锁检测和死锁超时机制

对于事务型的系统，死锁发生后，只有部分或者完全回滚其中一个事务，才能打破死锁。InnoDB目前处理死锁的方式是：在检测到死锁循环依赖后，将持有最少行级排它锁的事务进行回滚。

处理死锁，只要重新执行死锁回滚的事务即可。

事务日志

事务日志：事务日志可以帮助提高事务的效率，存储引擎在修改表的数据时只需要修改表数据的内存拷贝，同时把该修改行为持久化到硬盘中的事务日志中，相比于将修改的数据本身持久化到磁盘，事务日志采用的是追加的方式，因此是在磁盘上的一小块区域内顺序地写入，而不是随机的I/O操作。事务日志持久化后，内存中被修改的数据在后台可以慢慢刷回磁盘，如果在数据没有写回磁盘时系统崩溃了，存储引擎在重启时能够自动恢复这部分数据。目前大多数存储引擎都是这样实现的。

我们称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

MySQL中的事务

自动提交

MySQL默认采用自动提交模式，如果不是显式地开始一个事务，则每个查询都会被当做一个事务执行提交操作。可以在当前连接中设置AUTOCOMMIT变量来禁用自动提交模式(禁用后，需要显式地执行COMMIT提交或者ROLLBACK回滚)。

对于非事务型的表，修改AUTOCOMMIT不会有任何影响，这类表相当于一直处于AUTOCOMMIT启用的状态。此外，有一些命令，例如ALTER TABLE，在执行之前会强制执行COMMIT提交当前的活动事务。

事务中混合使用存储引擎

如果在事务中混合使用了事务型和非事务型的表，当事务需要回滚时，非事务型表上的变更将无法撤销，这将导致数据库处于不一致的状态。在非事务型表上执行事务相关操作时，MySQL通常并不会报错，只会给出一些警告。

隐式和显式锁定

InnoDB采用两阶段提交协议

显式锁定：

InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范，如： SELECT … LOCK IN SHARE MODE SELECT … FOR UPDATE

MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层实现的，但它们不能代替事务处理，如果应用到事务，还是应该选择事务型存储引擎。（建议：除了在事务中禁用了AUTOCOMMIT时可以使用LOCK TABLE之外，其他任何时候都不要显式地执行LOCK TABLES，不管使用的是什么存储引擎，因为LOCK TABLE和事务之间相互影响时，问题会变得非常复杂）

多版本并发控制

MYSQL的大多数事务型存储引擎实现都不是简单的行级锁。基于并发性能的考虑，一般都同时实现了多版本并发控制

可以认为MVCC是行级锁的一个变种，但是它在很多情况下都避免了加锁操作，因此开销更低（更高并发）。

其实现原理是通过保存数据在某个时间点的快照来实现的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的，这两个列一个保存行的创建时间，一个保存行的过期时间（或删除时间），当然并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的版本号会作为事务的版本号用来和查询到的每行记录的版本号进行比较。 MVCC只在REPEATABLE和READ COMMITED两个隔离级别下工作，其他两个隔离级别和MVCC不兼容。

MySQL的存储引擎

InnoDB

InnoDB是MySQL默认的事务型引擎。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，有一系列的数据文件组成。InnoDB采用MVCC来支持高并发，默认隔离级别是可重复读，并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，但是二级索引（非主键索引）中必须包含主键列。

MyISAM

MyISAM提供了全文索引、压缩、空间函数等，但是不支持事务和行级锁，且崩溃后数据无法恢复。最典型的问题还是锁表问题。

MyISAM会将表数据存储在两个文件中：数据文件和索引文件。会将数据写到内存中，然后定期将数据刷到磁盘上。

MyISAM对整张表加锁，读取时对表加共享锁，写入时对表加排他锁。支持并发插入（读取的同时，进行写入）。

压缩表：表创建并导入数据后不再进行修改，可以使用压缩表。极大的减少磁盘空间占用，也就减少了磁盘I/O，从而提升查询性能。

CSV

可以作为数据交换机制

Memory

如果需要快速访问数据，且数据不会被修改，重启后数据丢失也没关系（因为是存储在内存中的），则可以使用。会比MyISAM快至少一个数据量。重启后数据会丢失，但是表结构会保留。可以用来缓存周期性的聚合数据或保存数据分析中产生的中间数据。

临时表只在单个连接中可见，连接断开时，临时表也将不存在。所以临时表和memory表是不一样的。

NDB集群引擎

MySQL集群

选择存储引擎时的考虑因素

事务、备份、崩溃恢复、特有的特性

MySQL拥有分层架构，上层是服务器的服务和查询执行引擎，下层则是存储引擎。在存储引擎和服务层之间处理查询是通过API来回交互。对于InnoDB来说，所有操作都是事务。

第2章 MySQL基准测试

基准测试是针对系统设计的一种压力测试，通常的目标是为了掌握系统的行为。

基准测试的策略

基准测试主要两种策略：集成式（针对整个系统的整体测试）、单组件式（单独测试MySQL）

测试指标：

吞吐量：单位时间内的事务处理数；
响应时间（延迟）：完成测试任务所需的时间；（95%法则）
并发性：测试应用在不同并发下的性能；
可扩展性：线性扩展；

影响测试结果的因素

数据量
数据分布
系统预热

文档输出很重要

先收集所有的原始数据，然后再基于此做分析和过滤是一个好习惯

基准测试方法

避免常见错误设计和规划基准测试基准测试的运行时间设置获取系统的性能和状态收集分析绘图

基准测试工具

集成式测试工具

ab: Apache http服务器基准测试工具,测试服务器qps, 针对单个url进行尽可能快的压力测试
http_load: 对Web服务器,可以通过输入文件对多个url进行随机测试
JMeter: 测试Web应用和其他入FTP服务器或通过JDBC进行数据库查询测试

单组件式测试工具

mysqlslap: 模拟服务器负载并输出计时信息
sqlbench: 测试服务器执行查询的速度
super smack: mysql 和postgresql的基准测试工具
database test suite
sysbench: 多线程系统压测工具, 支持mysql,操作系统,和硬件测试

第3章服务器性能剖析

如何确定系统是否达到最佳的状态？

某条语句为什么执行不够快？

为什么会发生停顿、堆积或者卡死的某些间歇性故障？

性能：完成某件任务所需要的时间度量，性能即响应时间。性能剖析（profiling）：测量服务器的时间花费在哪里。性能剖析的步骤：测量任务所花费的时间，然后对结果进行统计和排序，将重要的任务排到前面。

剖析MySQL查询

SHOW PROFILE: 将profiling开启后,执行的sql的剖析信息会被记录到临时表, 通过 show profile from query (index)命令即可以获取到执行的过程信息
SHOW STATUS: 非剖析工具,主要用在对于执行完后观察某些计数器的值
使用慢查询日志

诊断间歇性问题

间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。

show global status: 通过某些计数器的”尖刺”或”凹陷”分析问题
show processlist: 观察是否有大量线程处于不正常的状态或其他不正常特征
使用innotop: 实时地展示服务器正在发生的事情，监控innodb,监控多个MySQL实例
使用慢查询日志

其他剖析工具

自带的一些INFORMATION_SCHEMA统计表
使用strace工具,pt-iopfofile工具生成I/O活动报告

第4章 Schema与数据类型优化

选择优化的数据类型

选择合适数据类型的三个原则

更小的通常更好 - 速度更快，占用更少
简单就好 - 简单数据类型占用更少的CPU周期，例如整型的比字符串操作代价更低
尽量避免NULL - 查询包含NULL的列，对Mysql来说更难优化，因为会使得索引，索引统计和值比较更为复杂

整数类型

整数的类型有：TINYINT 、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用8，16，24，32，64位存储空间

它们存储的值的范围：-2（N-1）到 2（N-1）- 1

其中N为存储空间的位数

Mysql可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了一些交互工

具用来显示字符的个数而已，对于存储和计算来说，INT(1) 和 INT(20) 是相同的

实数类型

在mysql的数据类型中浮点型分为两种，float()与double()类型，定点型为decimal()

数据类型(M,D) -》M：精度，数据的总长度； D：标度，小数点后的长度；

其区别在于：

当不指定精度时，Float、Double默认会保存实际精度，而Decimal默认是整数
当标度不够时，都会四舍五入，但Decimal会警告信息

字符串类型

VARCHAR 和 CHAR是最主要的字符串类型，CHAR自不必说，实际使用的情况较少，例如存储男/女，YES/NO等确定长度的字符串，但是这种固定的情况有时候用整型去存储效率更高，所以视情况而定吧

VARCHAR存储的是可变长字符串，它比定长类型更节省空间，因为它仅使用必要的空间，它需要用1个或者2个额外字节记录字符串长度

BLOB和TEXT类型：

BLOB：二进制存储，没有排序规则和字符集
TEXT：字符串存储，有排序规则和字符集

当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内都需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值

日期和时间类型

对于日期和时间类型，据我了解到身边的人大多都不会把时间直接存储到数据库中，同时《高性能Mysql》一书中也推荐另一种做法去存储时间，在这里推荐一下，即：

通过BIGINT类型存储毫秒/微秒级别的时间戳，再显示或者计算的时候都基于时间戳进行计算

选择标识符

选择标识列（identifier column）类型时，不仅要考虑存储类型还要考虑如何进行计算和比较，一旦选定了一种类型，还要确保所

有关联表中使用同样的类型，类型之间需要精确匹配（包括UNSIGNED这样的属性）

整数通常是ID列最好的选择。

使用MD5(),SHA1(),UUID()产生的字符串的值会随机分布在很大的空间中，导致INSERT和一些SELECT语句变得很慢：

插入值随机写到索引的不同位置，导致页分裂，磁盘随机访问等，详见第五章
逻辑上相邻的行会分布在磁盘和内存的不同地方
随机值使得缓存赖以工作的访问局部性原理失效。

存储UUID值应该移除“-”符号；最好使用UNHEX()函数将UUID值转换为16字节的数字，存储在BINARY(16)列中。检索时可以通过HEX()函数格式化成十六进制格式

特殊类型数据

例如：IPV4地址，人们经常使用 VARCHAR(15)列来存储IP地址。然而,它们实际上是32位无符号整数,不是字符串。用小数点将地址分

成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址，MYSQL提供INET_ATON()和 INET NTOA()函数在这

两种表示方法之间转换

MySQL schema设计中的陷阱

我们应该避免以下几种情况的出现：

太多的列存储引擎API需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个操作的代价非常高，而转换的代价依赖于列的数量。
太多的关联（上限61张表）单个查询做好12个表以内关联
全能的枚举，变相的枚举
随随便便的NULL

范式和反范式

反范式的标志：信息冗余

随着时代和机器的发展，我们会经常使用空间换时间的策略，因此基本淘汰了完全遵循范式的做法，但是在范式与反范式中间一定

要根据业务需求做好设计，减少不必要的空间浪费

缓存表和汇总表

缓存表：存储那些可以比较简单地从schema其他表获取数据的表（存在逻辑上冗余的数据）；汇总表：保存使用GROUP BY语句聚合数据的表；

利用Mysql做缓存的可能很少，用作汇总表的可能很多，提供原文中两个场景的较好的解决方案：

不严格技术或小范围计数

例如，如何更好的汇总一天中任务执行次数?

我们可以采用分割的思想，把一天划成小时，一天过去进行数据汇总时全部累加即可

计数表

例如，如果统计网站访问人数更合适?

我们当然可以用一条记录总人数，也可以用N天（条）数据记录总人数，然后累加，但是Mysql在执行时候有一定的延时，可能一秒

之内有好几十个人点击，那我们可以针对一条数据进行分割成1-100条数据，通过算法求余等等，让100条数据可以均匀的一起工作（避免锁竞争）

这样可以大幅度增加效率，最终再汇总即可

加快ALTER TABLE操作的速度

常规的方法是建另一张结构符合要求的表，并插入旧表的数据，然后切换主从，或者切换新旧表。修改表的.frm文件是很快的，因此可以为想要创建的表结构创建一个新的.frm文件，然后用它替换掉已经存在的表的.frm文件。（详略）

不知道其他公司对于底层数据库的字段是否会经常调整，反正我们公司每次需求都会涉及数据库字段的调整，每次都需要执行

ALTER操作，如何提高效率？

主从库切换，减少ALTER影响时间
“影子拷贝”，完全创建新表，然后通过重命名+删除的方式替换旧表（无数据的情况）

在有索引的情况下快速导入表数据

常规的方法：

先删除索引：如ALTER TABLE t.data DISABLE KEYS; ## 对唯一索引无效
导入数据
再创建索引：如ALTER TABLE t.data ENABLE KEYS;

hack方法是直接修改.MYD、.frm、.MYI等文件，详略。