本文基于MySQL 8.0。高性能MySQL自用笔记。

1. MySQL的架构和历史

1.1 MySQL逻辑架构

1-1:MySQL服务器逻辑架构图

第一层大部分基于网络的应用都有。包括了链接处理、授权认证、安全等。

第二层保存了MySQL的核心功能。查询解析、分析、优化、缓存以及所有的内置函数（日期、时间、数学、加密函数等），所有的跨储存引擎的功能：储存过程、触发器、视图等（因为不同储存引擎的实现不同）。

第三层是储存引擎。负责MySQL数据的存取。储存引擎会执行“开始一个事务”或“根据主键提取一行记录”的操作，但不会解析SQL（除了InnoDB，它会解析外键定义。因为MySQL的服务器并没有这个功能。）

1.1.1 链接管理与安全性

MySQL的每一个客户端链接都会有一个线程。创建和销毁线程都由服务器负责。在MySQL5.5以上的版本提供了线程池插件。

链接完成后，服务器会进行身份验证，同时检查该客户端是否有执行特定查询的权限。

1.1.2 优化与执行

MySQL会解析查询，并创造内部数据结构（解析树）同时进行优化。包括重写查询、决定表的读取顺序，选择索引等。我们能够通过关键字提示（hint）优化器，影响它的决策过程，也可以请求优化器解释（explain）优化过程的各个因素，让用户知道服务器是如何进行优化决策的。

优化器与储存引擎无关，但是储存引擎对优化查询有影响。因为优化器需要储存引擎提供容量或者某个具体操作的开销信息，以及表数据的统计信息等（如索引）。

对于SELECT语句，解析查询前，服务器会先检查查询缓存，如果找到了对应的查询，就不会再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

1.2 并发控制

本章只讨论并发读写。

1.2.1 读写锁

在处理并发读或者写时，可以通过实现一个由两个类型的锁组成的锁系统来解决问题。这两种锁为共享锁和排他锁。或者读锁和写锁。

读锁是共享的。多个线程可以同时读取一个资源。写锁是排他的，即写锁会阻塞其它的读锁和写锁。

1.2.2 锁粒度

最理想的情况是，在保证不会出现冲突的情况下，我们仅仅锁定需要修改的数据，以保证高并发。

加锁，解锁，检查锁状态都会增加系统开销。如果系统花费了大量的时间在锁上，系统的性能可能就会收到影响。

MySQL的每个储存引擎都实现了自己的锁策略和锁粒度。也就是说储存引擎决定了大部分锁管理的方式（比如MyISAM只有表锁，而InnoDB实现了表锁和行级锁。同时要注意服务器会因为一些特殊目的使用一些表锁，见下文表锁章节）。这里是MySQL最重要的两种锁策略。

表锁

MySQL最基本的锁策略，也是开销最小的策略（因为不像行级锁需要考虑哪些行受影响）。在特定的场景中，表锁也可以有良好的性能。例如READ LOCAL表锁支持某些类型的并发写操作。另外，写锁比读锁有更高的优先级。因此写锁请求可能会插入到读锁队列的前面。

虽然储存引擎可以管理自己的锁，但是MySQL还是会使用一些表锁来实现目的。比如服务器会对ALTER TABLE之类的语句使用表锁，而忽略储存引擎的锁机制。

行级锁

行级锁可以最大程度支持并发处理，同时也带来了最大的锁开销（需要确认哪些行需要被锁定）。在InnoDB和XtraDB以及其它的一些储存引擎中实现了行级锁。行级锁只在储存引擎层实现。

1.3 事务

事务是一组原子性的SQL查询，包含了多条SQL语句。对于一个事务来说，所有的请求要么全部执行成功，要么全部执行失败（任何一句语句失败，都会导致语句回退来满足“全部成功，全部失败”的条件）。

为什么我们需要事务？考虑一个银行的例子。一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）现在要从Jane的支票帐户转账200美元到她的储蓄账户。那么需要三个步骤：

检查支票的长湖的余额是否高于200美元。
从支票帐户余额中减去200美元。
在储蓄帐户余额中增加200美元。

上述步骤必须要么全部成功，要么全部失败。若第三步失败，那么我们必须回滚之前所有的操作。

我们可以用START TRANSACTION来开始一个事务，然后要么用COMMIT来提交事务修改的数据并且永久保存，要么用ROLLBACK回滚所有的修改。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;

如果我们第三步增加储蓄帐户余额失败，那么Jane就平白无故少了200美金，这是不能接受的，所以需要回滚。

事务处理系统，应该满足ACID特征：

原子性：一个事务应当是一个不可分割的最小工作单元，事务中的操作要么全部成功，要么全部失败。
一致性：数据总是从一个一致性的状态转换到另一个一致性的状态。即便系统崩溃，前文中提到的支票账户也不会损失200元。因为事务没有最终提交，所以事务中所做的修改也不会保存到数据库中。
隔离性：在事务最终提交前，该事务对其他事务是不可见的。注意，这里的隔离性不是完美的，在后面的隔离级别我们会提到。
持久性：一旦事务提交，所做的修改就会永久保存到数据库中。

1.3.1 隔离级别

在SQL标准中有四种隔离级别：

1. Read Uncommitted(未提交读)

在这个级别，即便事务中的修改没有提交，其他事务也能看到修改后的数据。事务能够读到未提交的数据，被称为(脏读)。理论上该隔离级别性能最好，但是考虑到脏读带来的问题，我们很少使用这种隔离级别。

实现：仅在更新数据时添加行级共享锁，直到事务结束。这样事务2就能读到事务1的修改，但是事务2无法修改事务1锁定的数据。

2. Read Committed(提交读)

大多数数据库的默认隔离级别是提交读（但MySQL不是）。提交读满足了隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的更改。也就是说，在提交之前，所做的修改对于其他事务都是不可见的。这个级别有时被称为不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。
实现：对于读取的数据添加行级共享锁，读取完成后立刻释放。更新数据行添加行级排他锁，直到事务结束才释放。

3. Repeatable Read（可重复读）

可重复度解决了脏读的问题，保证了在同一事物中多次读取同样的记录结果是一致的。但是没有办法解决幻读的问题。幻读指的是当某个事务在读取摸个范围内的记录时，另外一个事务又在该范围内插入新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。Innodb和XtraDB使用了多版本并发控制解决了幻读的问题。
实现：对于读取的数据添加行级共享锁，直到事务结束才释放。更新数据行添加行级排他锁，直到事务结束才释放。

4. Serializable（可串行化）

最高隔离级别，通过强制事务串行执行，避免了幻读的问题。但该隔离级别会导致大量的锁争用，并发也极差。
实现：事务在读取数据时，需要先添加表级共享锁。写数据时，需要添加表级排他锁。
| 隔离级别 | 脏读可能性 | 不可重复度可能性 | 幻读可能性 | 加锁读写 |
| :———: | :————: | :———————: | :————: | :———: |
| 未提交读 | Yes | Yes | Yes | No |
| 提交读 | No | Yes | Yes | No |
| 可重复读 | No | No | Yes | No |
| 可串行化 | No | No | No | Yes |

1.3.2 死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源。多个事务同时锁定同一个资源时，也会产生死锁。为了解决这个问题，数据库使用了死锁检测和死锁超时机制。如InnoDB系统能够检测到死锁的循环依赖，并立即返回一个错误。另外就是查询的时候，如果查询达到锁等待超时的设定后放弃锁请求。InnoDB目前解决死锁的办法是将拥有所少行级排它锁的事务进行回滚。锁的行为和顺序是和搜索引擎相关的。同样的顺序，有的搜索引擎会产生死锁，有些不会。所以死锁的产生有两种原因：有些是因为真的数据冲突，有的是因为搜索引擎的原因。

1.3.3 事务日志

类似oracle的重做日志文件。一般修改表的数据时，首先是修改内存中的表拷贝，然后是将修改记录记录硬盘上的事务日志中，而不用每次都将数据持久化到磁盘。因为事务日志采取的追加的方式，因此写日志的操作是磁盘上一小块区域的顺序IO。而不是随机IO.使用了事务日志后，内存的数据就可以慢慢刷入磁盘。因此修改数据需要写两次磁盘（写日志，写磁盘）。如果数据的修改已经记录到事务日志并持久化，但数据本身没有刷入磁盘，如果系统崩溃，储存引擎在重启时就能够自动回复这部分修改的数据。

1.3.4 MySQL中的事务

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

自动提交

MySQL默认使用自动提交模式。如果不显式地开启一个事务，那么每个查询都将被当作一个事务执行提交操作。通过修改AUTOCOMMIT变量来启用或者禁用自动提交模式。如果关闭autocommit,那么所有的查询都将在一个事务中直到你显示输入commit命令或者是rollback,该事务结束，同时开始一个新事务。

mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value | 
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set(0.0 sec)
mysql> SET AUTOCOMMIT = 1;

注意，一部分sql命令执行前会强制执行commit来提交当前的活动事务。比如alter table,lock tables。注意，对于没有事务管理的储存引擎，可以视作autocommit一直开启的状态。

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

因为事务是下层的储存引擎实现的，所以如果在一个事务中使用多种储存引擎就是不可靠的。如果我们需要回滚，那么非事务型的储存引擎就无法回滚。

隐式和显式锁定

InnoDB采用的是两阶段锁定协议。在事务的执行过程中，随时都可以执行锁定，锁只有在执行commit或者rollback的时候才会释放，并且所有的锁在同一时刻释放。前面的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。
虽然MySQL有lock tables语句，但是lock tables和事务同时使用会产生无法预料的后果，所以不推荐。

1.4 多版本并发控制

MVCC是行级锁的一个变种，在很多情况下避免了加锁操作，所以开销耕地。大部分实现了非阻塞的读操作，写操作也之锁定必要的行。

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

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列实现的。一个保存了行的创建版本，一个保存了行的过期版本（删除版本）。每次开始一个新的事务，系统版本号都会自动递增。

SELECT

InnoDB会根据以下两个条件检查每行记录：

InnoDB只查找版本早于当前事务版本的数据行，这样可以保证事务查询到的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
航的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。
只有两个条件都符合的行才会被返回。

INSERT

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

DELETE

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

UPDATE

InnoDB插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识。

注意，MVCC仅在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。

1.5 MySQL的储存引擎

1.5.1 InnoDB

支持事务，使用MVCC,使用间隙锁防止幻读，使用了聚簇索引，表的rows是估计值。
间隙锁：对一个范围的锁定，是前开后闭区间。比如表内有数据5,10。此时我们A事务第一次查询想看表内是否有7,结果是没有。此时B事务插入7。那么如果之后再查表内是否有7,那么就是没有了。所以为了解决这个问题，我们应当锁定(5,10]区间。这就是间隙锁。

1.5.2 MyISAM

不支持事务和行级锁。rows是准确值。开销低

1.5.3 Archive

只支持insert和select.插入时压缩数据，io更少。适合日志及高速插入。

2. 服务器性能剖析

2.1 剖析查询

2.1.1 记录慢查询

在my.ini开启慢查询日志：

[mysqlld]
//多久是慢查询，单位分钟，0代表记录所有查询
long_query_time=2

//5.0,5.1版本
log-slow-queries='mysql_slow_query.log'
//5.5以上
slow-query-log=On
slow_query_log_file='mysql_slow_query.log'

//记录没有使用索引的查询
log-query-not-using-indexes

对于获得的慢查询，通过explain查看运行的信息。慢查询对IO的开销可以忽略不计。如果权限不足的话，可以尝试percona toolkit的pt-query-digest或者抓取TCP包。

2.1.2剖析慢查询

使用show profile

1	mysql> SET profiling = 1;

通过show profiles能看到记录的查询细节

精细查看：

使用show status

show status可以查看操作的次数统计。注意一部分数据是会话级别的，一部分是全局级别的，需要在MySQL官方手册中查看具体的定义。

2.2诊断间歇性问题

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

我们需要观察，是服务器上所有的程序都突然变慢，又突然都变好，每一条查询也都变慢了，那么慢查询就不一定是原因。反之，如果服务器整体没有问题，只有某条查询偶尔变慢，那么就应该注意是否是这条查询的问题。

使用SHOW GLOBAL STATUS

通过较高频率执行命令可以通过显示的Threads_running, Threads_connected, questions,queries的“尖刺“或者”凹陷“来发现。

$ mysqladmin ext -i1 | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'
798 136 7
767 134 9
828 134 7
683 134 7
784 135 7
614 134 7
108 134 24
187 134 31
179 134 28
1179 134 7
1151 134 7
1240 135 7
1000 135 7

可以看到Thread connected没有变化，但是正在查询的线程数明显上升。这可能是因为：1. 服务器内部有瓶颈，导致新查询在开始执行前因为要获取老查询正在等待的锁而造成堆积。这样的锁通常也会对应用服务器造成后端压力，使得应用服务器出现排队问题。2. 突然遭到了大量查询请求的冲击。

使用show processlist

通过不断捕获show processlist的输出，观察是否存在大量线程处于不正常的状态或者不正常的特征。比如查询很少会长时间处于“statistics”状态。。另外，很少见到大量线程报告当前链接用户是“未经验证的用户”。我们可以使用

1	$ mysql -e 'SHOW PROCESSLIST\G' \| grep State: \| sort \|uniq -c \| sort -rn

来进行分析。

3.4.2 捕获诊断数据

诊断触发器

一个好的触发器应该避免误报和漏检。误报是指收集了很多数据但是都没有用，而漏检指出问题时没有采集到必要的数据。我们需要选择一个合适的数值，以便在开始是就捕获数据，更不能将阈值设定的太高。比如Threads_connected偶尔出现非常高的尖峰值，在几分钟从100冲向5000或更高。虽然将阈值设定为4900也能捕获到问题，但考虑到平时仅仅有10的并发，我们应该在150或200左右就开始捕获。

我们要收集什么样的数据？

系统的状态，cpu利用率，磁盘利用率，可用空间，ps输出采样，内存利用率，从mysql能够获取的信息如show status,show processlist和show innodb status。

解释结果数据

建议通过两个两个目的出发：1. 问题是否发生了？2. 是否有很明显的跳跃性的变化。为此有的时候应当抓取一些正常的数据作为对比。可能的问题有：性能低下的SQL查询，不适合的索引，设计糟糕的数据库逻辑架构等。通过抓取TCP流量或者show processlist输出，可以获得查询和事务出现的地方，从而知道用户对数据库进行了什么操作。通过服务器的内部行为则可以清楚服务器是否有bug,或者内部的性能和扩展性是否有问题。

3.4.3 一个案例

首先设置thread_connected的触发条件，正常情况下threads_connected的值不会超过15。，但在发生问题时可能飙升到几百。以下是样本的收集数据：

查询活动从1000到10000的qps,其中很多是垃圾命令，如ping服务器是否存活。其余的大部分是select,每秒300～2000次。有很少的update（大约每秒5次）
检查processlist.txt

$ grep State: processlist.txt | sort | uniq -c | sort -rn
161 State: Copying to tmp table
156 State: Sorting result
136 State: statistics
 50 State: Sending data
 24 State: NULL
 13 State:
  7 State: freeing item
  7 State: cleaning up
  1 State: storing result in query cache
  1 State: end

大部分查询都是索引扫描或者范围扫描，很少有全表扫描或表关联的情况。
每秒大约有20-100次排序，需要排序的行大约有1000-2000行。
每秒大约创建12-90个临时表，其中有3-5个是磁盘临时表。
没有表所或者查询缓存的问题。
在show innodb status中可以观察到主要的线程状态是”flushing buffer pool pages”，但只有很少的脏页需要刷新。Innodb_buffer_pool_pages_flushed也没有太大的变化，日志顺序好和最后检查点之间的差距也很少。InnoDB缓存池也没有用满。缓存池比数据集还要大很多。大多数线程在等待InnoDB队列：“12 queries inside InnoDB, 495 queries in queue”。
每秒不会一次iostat输出，持续30秒。从输出可以发现没有磁盘读，而写操作接近了“天花板”，所以IO平均等待时间和队列长度都非常高。

r/s	w/s	rsec/s	wsec/s	avgqu-sz	await	svctm	%util
1.00	500.00	8.00	86216.00	5.05	11.95	0.59	29.40
0.00	451.00	0.00	206248.00	123.25	238.00	1.90	85.90
0.00	565.00	0.00	269792.00	143.80	245.00	1.77	100.00
0.00	649.00	0.00	309248.00	143.01	231.30	1.54	100.10
0.00	589.00	0.00	281784.00	142.58	232.15	1.70	100.10
0.00	384.00	0.00	162008.00	71.80	238.39	1.73	66.60
0.00	14.00	0.00	400.00	0.01	0.93	0.36	0.5
0.00	13.00	0.00	248.00	0.01	0.92	0.23	0.3
0.00	14.00	0.00	400.00	0.01	0.93	0.36	0.3

vmstat的输出也验证了iostat的结果，并且cpu的大部分时间是空闲的，只是偶尔在写尖峰的时候有一些IO等待时间（最高约9%的cpu）。

在这里，值得注意的是是wsec/s中，服务器每秒写入了几百mb的数据到磁盘。但是在整体操作为select的情况下，为什么服务器一直在写入？注意到主要的线程状态是刷新页面。在这个版本的InnoDB中，存在这疯狂刷新的问题，因为InnoDB没有按时间均匀分布刷新请求，而是隔一段时间突然请求一次强制检查点导致大量刷新操作。但是我们发现Innodb_buffer_pool_pages_flushed并没有怎么增加，而且主要是select查询的话，不太可能有大量的写需求。我们注意到磁盘的IO已经饱和，为什么？要么是数据库导致了IO问题，要么不是。同时，iostat的结果表明了磁盘本身的相应是很快的，但是请求在块设备队列中等待了很长的时间才能够进入到磁盘设备。但注意，这也有可能是错误的信息。通过记录io来查看除了什么问题：

$ awk '/mysqld.*tmp/ {
        total+= $7;
      }
      /^Sun Mar 28/ && total {
          printf "%s %7.2f MB\n", $4, total/1024/1024;
          total = 0;
      }' lsof.txt
18:34:38 1655.21MB
18:34:42 1.88MB
18:34:48 1.88MB
18:34:53 1.88MB
18:34:58 1.88MB

我们能够发现在问题初始mysql写了1.5gb的数据到临时表。这和show processlist中大量的copying to tmp table相吻合。这表明了某些效率低下的查询风暴耗尽了磁盘资源，而一种可能性就是缓存失效。当memcached中所有缓存的条目同时失效，而同时又有很多应用要同时访问时，就会出现这种情况。要解决这个问题，一方面在应用层面解决缓存失效的问题；另一方面修改了查询，避免使用临时表。

4. schema与数据类型优化

4.1 选择数据类型的原则

更小的通常更好
简单的更好
尽量避免NULL

4.2 字符串

这些值的储存方式与储存引擎的实现密切相关。以下的描述基于InnoDB和MyISAM.

VARCHAR

varchar更节省空间，因为它仅使用必要的空间。除非该表使用ROW_FORMAT=FIXED创建。VARCHAR需要使用1到2个额外字节储存字符串的长度。如果最大长度小于等于255,那么就使用1个字节，否则就是2个字节。varchar解决了空间，也对性能有帮助。但是由于行是变长的，在update时有可能使行变得比原来更长。如果一个行占用的空间增长，同时页内没有更多的空间可以储存，这时不同的储存引擎的处理是不一样的。MyISAM会把行拆成不同的片段储存。InnoDB需要分裂页来使行可以放进页内。一般地说，推荐在这些情况使用varchar：

字符串列的最大长度比平均长度大很多
列的更新很少，所以碎片不是问题。
使用了像utf-8这样的字符集，每个字符都是用不同的字节数进行储存

在5.0或更高版本，mysql在储存和检索时会保留末尾空格，但是4.1或更老的版本，会剔除末尾空格。InnoDB还会把过长的varchar储存为BLOB。

CHAR

储存char时，mysql总是会删除所有的末尾空格。CHAR适合储存定长的字符串，比如密码的md5值。同时对于经常变更的数据，char也更好，因为不容易产生碎片。

VARCHAR(5)和VARCHAR(200)

当我们储存“hi“时，这两个数据项的开销是一样的，那么我们应该使用更短的列吗？事实是确实如此。更长的列会消耗更多的内存。mysql通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。使用磁盘临时表进行排序时也同样糟糕。

BLOB和TEXT类型

与其他类型不同，MySQL会把每个BLOB和TEXT当作独立的对象处理。当BLOB和TEXT太大时，InnoDB会使用专门的“外部“储存区域来进行储存，此时每个值在行内需要1-4个字节储存一个指针，然后在外部储存区域储存实际的值。mysql不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。因为memory引擎不支持BLOB和TEXT类型，所以如果查询使用了BLOB或TEXT的列，那么将不得不使用MyISAM磁盘临时表。所以最佳的解决方案是不使用BLOB和TEXT.另一个比较巧妙的解决方法是使用substring(column, length)将列值转换为字符串，这样就可以使用内存临时表了。但是要保证截取的字符串足够短，不然mysql会把内存临时表转换成磁盘临时表。

4.3 日期和时间类型

DATATIME

能保存从1001年到9999年，精度为秒。使用8个字节展示。

TIMESTAMP

能保存从1970年1月1日午夜以来的秒数值，只使用4个字节的储存空间，能保存从1970到2038的年份。提供了FROM_UNIXTIME()转换为日期UNIX_TIMESTAMP()从日期转换为时间戳。使用TIMESTAMP代表这个时间依赖系统的时区。同时，在插入时如果没有制定第一个timestamp的值，那么默认为当前时间。