高性能MYSQL部分摘要

高性能MYSQL读书笔记

第1章 MySQL架构与历史

1.1链接管理

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查 询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者 CPU中运行。

1.2并发控制

1.2.1读写锁

在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决 问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁 （exclusive lock），也叫读锁（read lock）和写锁（write lock）。读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的 写锁和读锁

1.2.2锁粒度

理想的方式是，只对会修改的数据片进行精确的锁定

加锁也需要消耗资源。锁的各种操作，包括获得锁、检查锁 是否已经解除、释放锁等，都会增加系统的开销

表锁是MySQL中最基本的锁策略，并且是开销最小的策略。

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

写锁也比读锁有更高的优先 级，因此一个写锁请求可能会被插入到读锁队列的前面

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

1.3事务

原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务 中的所有操作要么全部提交成功，要么全部失败回滚

一致性（consistency）：数据库总是从一个一致性的状态转换到另外一个一致性的状态

隔离性（isolation）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。

1.3.1隔离级别（可重复读是MySQL的默认事务隔离级别）

READ UNCOMMITTED 级别，事务中的修改，即使没有提交， 对其他事务也都是可见的

事务可以读取未提交的数据，这也被称为脏读（Dirty Read）

READ COMMITTED 也叫做不可重复读，满足前面提到的隔离性的简单定 义：一个事务开始时，只能“看见”已经提交的事务所做的修改

不可重复读两次执行同样的查询，可能会得到不 一样的结果

**REPEATABLE READ（可重复读）可重复读隔离级别还是无法解决另外一个幻读（Phantom Read）**的问题

幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围 的记录时，会产生幻行（Phantom Row）

InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题

**SERIALIZABLE（可串行化）**会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

1.3.2死锁

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

事务1START TRANSACTION;
	UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 '; 
	UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 ; 
	COMMIT; 
事务2	START TRANSACTION; 
		UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3; 
		UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4; 
	COMMIT;
两个事务都等待对方释放锁， 同时又持有对方需要的锁

InnoDB目前处理死 锁的方法是，将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回滚算法）。

1.3.3事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬 盘上的事务日志中

1.3.4mysql中的事务

在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT 或者ROLLBACK 的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定

InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范 (3) ：

SELECT ... LOCK IN SHARE MODE 

SELECT ... FOR UPDATE 

1.4 多版本并发控制

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

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

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或 删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number）

优点：保存这两个额外系统版本号，使大多数读操作都可以不用加锁

不足之处：是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作

MVCC只在REPEATABLE READ 和READ COMMITTED 两个隔离级别下工作，SERIALIZABLE 则会对所有读取的行都加锁

1.5　MySQL的存储引擎

1.5.5　选择合适的引擎

事务：InnoDB（或者XtraDB）

备份：如果需要在线热备份，那么选择InnoDB就是基本的要求

崩溃恢复：MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢

1.5.6　转换表的引擎三种方法

1. ALTER TABLE

ALTER TABLE mytable ENGINE=InnoDB;如果转换表的存储引擎，将会失去和原引擎相关的所有特性

​ 2.导出与导入

mysqldump 工具将数据导出到文件，然后修改文件中CREATE TABLE 语句的存储引擎选项，注意同时修改表名同时要注意mysqldump默认会自动在CREATE TABLE 语句前加上DROP TABLE 语句

​ 3.创建与查询（CREATE和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;

第2章 MySQL基准测试

2.1　为什么需要基准测试

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

2.2　基准测试的策略

集成式（full-stack）：针对整个系统的整体测试

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

2.2.1　测试何种指标

吞吐量 ：吞吐量指的是单位时间内的事务处理数要针对在线事务处理 （OLTP）的吞吐量，常用的测试单位是每秒事务数（TPS）

响应时间或者延迟 ：这个指标用于测试任务所需的整体时间

并发性 ：

可扩展性：

2.3　基准测试方法

2.4　基准测试工具

集成式测试工具

ab是一个Apache HTTP服务器基准测试工具 http_load JMeter

单组件式测试工具

mysqlslap测试时可以执行并发连接数，并指定SQL语句

MySQL Benchmark Suite（sql-bench） 主要用于测试服务器执行查询的速度。结果会显示哪种类型的操作在服务器上执行得更快

第4章　Schema与数据类型优化

4.1　选择优化的数据类型

更小的通常更好

简单就好

尽量避免NULL ：可为NULL 的列会使用更多的存储空间，在MySQL里也需要特殊处理

4.1.1　整数类型

TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT 。分别使用8，16，24，32，64位存储空间

整数类型有可选的UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍

4.1.2　实数类型

FLOAT 和DOUBLE 类型支持使用标准的浮点运算进行近似计算，浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间

DECIMAL类型用于存储精确的小数

4.1.3　字符串类型

VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间，VARCHAR 需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节

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

CHAR 类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间，采用空格进行填充

CHAR 适合存储很短的字符串，或者所有值都接近同一个长度。例如CHAR 非常适合存储密码的MD5 值，因为这是一个定长的值。对于经常变更的数据，CHAR 也比VARCHAR 更好，因为定长的CHAR 类型不容易产生碎片

使用VARCHAR（5） 和VARCHAR（200） 存储’hello’ 的空间开销是一样的。那么使用更短的列有什么优势吗？

更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值

4.1.4　日期和时间类型

DATETIME精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中， 与时区无关。使用8个字节的存储空间

TIMESTAMP只使用4个字节的存储空间

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME 空间效率更高

4.1.5　位数据类型

BIT可以使用BIT 列在一列中存储一个或多个true/false值

SET 如果需要保存很多true/false值，可以考虑合并这些列到一个SET 数据类型

4.1.6　选择标识符（identifier）

整数类型：整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT 。

4.1.7 特殊类型数据

某些类型的数据并不直接与内置类型一致。

低于秒级精度的时间戳就是一个例子

另一个例子是一个IPv4地址。人们经常使用VARCHAR（15） 列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数 点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无 符号整数存储IP地址。MySQL提供INET_ATON() 和INET_NTOA() 函数在这两种表示方法之间转换

4.2　MySQL schema设计中的陷阱

太多的列现客户使用了非常宽的表（数千个字段），然而只有一小部分列会实际用到，这时转换的代价就非常高。如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同

太多的关联一个粗略的经验法则，如果希望查询执行得快速且并发性好， 单个查询最好在12个表以内做关联

全能的枚举注意防止过度使用枚举（ENUM ）。下面是我们见过的一个例子：

CREATE TABLE … (

country enum(’’,‘0’,‘1’,‘2’,…,‘31’)

4.3　范式和反范式

在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方

4.3.1　范式的优点和缺点

优点

经常会被建议对schema进行范式化设计，尤其是写密集的场景

1.范式化的更新操作通常比反范式化要快。

2.当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。

3.范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

4.很少有多余的数据意味着检索列表数据时更少需要DISTINCT或 者GROUP BY 语句。还是前面的例子：在非范式化的结构中必须使 用DISTINCT 或者GROUP BY 才能获得一份唯一的部门列表，但是如 果部门（DEPARTMENT ）是一张单独的表，则只需要简单的查询这张表就行了

缺点

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联

4.3.2 反范式的优点和缺点

优点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联

单独的表也能使用更有效的索引策略

4.5　加快ALTER TABLE操作的速度

MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表

修改表结构：

一种是先在一台不提供服务的机器上执行ALTER TABLE 操作，然后和提供服务的主库进行切换；

另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表；

第5章　创建高性能的索引

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构

5.1　索引基础

B-Tree索引 ：B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据

哈希索引哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效

5.2 索引的优点

\1. 索引大大减少了服务器需要扫描的数据量。

\2. 索引可以帮助服务器避免排序和临时表。

\3. 索引可以将随机I/O变为顺序I/O。

5.3　高性能的索引策略

第10章　复制

10.1　复制概述

MySQL支持两种复制方式：基于行的复制和基于语句的复制

两种方式都是通过在主库上记录二进制日志 、在备库重放日志的方式来实现异步的数据复制。这意味着，在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟

10.1.1 复制解决的问题

数据分布

负载均衡：DNS轮询（将一个机器名指向多个IP地址）LVS

备份

高可用性和故障切换

10.1.2　复制如何工作

\1. 在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）。

\2. 备库将主库上的日志复制到自己的中继日志（Relay Log）中。

\3. 备库读取中继日志中的事件，将其重放到备库数据之上。

10.2　配置复制

\1. 在每台 (3) 服务器上创建复制账号。

\2. 配置主库和备库。

\3. 通知备库连接到主库并从主库复制数据

10.2.1　创建复制账号

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON .

-> TO repl@‘192.168.0.%’ IDENTIFIED BY ‘p4ssword’,;

10.2.2　配置主库和备库

主库

log_bin = mysql-bin

server_id = 10

备库

log_bin = mysql-bin

server_id = 2 （必要）

relay_log = /var/lib/mysql/mysql-relay-bin| Chapter 10:Chapter 10: Replication Replicationlog_slave_updates = 1

read_only = 1

10.2.3　启动复制

CHANGE MASTER TO

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

mysql> SHOW PROCESSLIST\G

10.2.4　从另一个服务器开始复制

需要有三个条件来让主库和备库保持同步：

在某个时间点的主库的数据快照。

主库当前的二进制日志文件，和获得数据快照时在该二进制日志文件中的偏移量，我们把这两个值称为日志文件坐标（logfilecoordinates）。通过这两个值可以确定二进制日志的位置。可以通过SHOW MASTER STATUS命令来获取这些值。

从快照时间到现在的二进制日志。

使用冷备份

使用热备份

使用mysqldump

使用快照或备份

使用Percona Xtrabackup

R TO

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

mysql> SHOW PROCESSLIST\G

10.2.4　从另一个服务器开始复制

需要有三个条件来让主库和备库保持同步：

在某个时间点的主库的数据快照。

主库当前的二进制日志文件，和获得数据快照时在该二进制日志文件中的偏移量，我们把这两个值称为日志文件坐标（logfilecoordinates）。通过这两个值可以确定二进制日志的位置。可以通过SHOW MASTER STATUS命令来获取这些值。

从快照时间到现在的二进制日志。

使用冷备份

使用热备份

使用mysqldump

使用快照或备份

使用Percona Xtrabackup

使用另外的备库