高性能MySQL第三版学习笔记（一）_高性能mysql学习笔记

只摘取对个人来说重要的部分，以及容易忘记的知识

优化与执行

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

事物的ACID原则

案例：

1. 检查支票账户的余额高千 200 美元。

2. 从支票账户余额中减去 200 美元。

3. 在储蓄账户余额中增加 200 美元。

sql语句：

1. START TRANSACTION;

2. SELECT balance FROM checking WHERE customer_id = 10233276;

3. UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;

4. UPDATE savings SET balance = balance+ 200.00 WHERE customer_id = 10233276;

5. COMMIT;

原子性(atomicity)：

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功， 要么全部失败回滚，对千一个事务来说， 不可能只执行其中的	一部分操作，这就是事务的原子性。
1

一致性(consistency)：

数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中，一致性确保了， 即使在执行第三、四条语句之间时系统崩溃， 支票账户中也	不会损 失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。
1

隔离性(isolation)：

通常来说， 一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中， 当执行完第三条语句、第四条语句还未开始时， 此时有另外一个账户汇总程序开始运行，则其看到的支票账户的余额井没有被减去200美元。后面我们讨论隔离级别(Isolation level)的时候，会发现为什么我们要说 “通常来说” 是不可见的。
1

持久性(durability)：

一但事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持久性是个有点模糊的概念， 因为实际上持久性也分很多不同的级别。有些 持久性策略能够提供非常强的安全保障， 而有些则未必。而且不可能有能做到100％的持久性保证的策略（如果数据库本身就能做到真正的持久 性，那么备份又怎么能增加持久性呢？）。
1

事物的隔离级别

较低级别 的隔离通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITTED（未提交读）：

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见 的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但却缺乏 其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。
1

READ COMMITTED（提交读）：

大多数数据库系统的默认隔离级别都是READCOMMITTED（但MySQL不是）。READCOMMITTED满足前面提到的隔离性的简单定义：一看见已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatableread)，因为两次执行同样的查询，可能会得到不一样的结果。
1

REPEATABLE READ（可重复读）：

REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一 一个幻读(Phantom Read)的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control) 解决了幻读的问题。
可重复读是MySQL的默认事务隔离级别。
1
2

SERIALIZABLE （可串行化）：

SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读 的问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致 大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需 要确保数据的一致性而且可以接受没有井发的情况下，才考虑采用该级别。
1

事务的状态：

• 脏读: 当一条线程中的事务读取到另一条线程事务未提交的 update、delete 数据；即一个事务能读取到另一个事务还未提交的数据；

• 不可重复读: 当一条线程中的事务读取到另一条线程中事务已提交的 update、delete 数据；即在一个事务内，多次读取同一数据

• 虚读(幻读): 当一个线程中的事务读取到另一个线程已提交的 insert 数据；即一个事务内，多次查询返回的结果集不一样；

死锁

死锁是指两个或者多个事务在同一资源上相互占用， 井请求锁定对方占用的资源， 从而导致恶性循环的现象。 当多个事务试图以不同的顺序锁定资源时， 就可能会产生死锁。多个事务同时锁定同一个资源时， 也会产生死锁。

为了解决这种问题， 数据库系统实现了各种死锁检测和死锁超时机制。 越复杂的系统，比如 InnoDB 存储引擎， 越能检测到死锁的循环依赖， 并立即返回一个错误。 这种解决方式很有效， 否则死锁会导致出现非常慢的查询。 还有一种解决方式， 就是当查询的时 间达到锁等待超时的设定后放弃锁请求， 这种方式通常来说不太好。 InnoDB 目前处理死锁的方法是， 将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回 滚算法）。

对于事物型的系统，这是无法避免的！！

MVCC（多版本并发控制）

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

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。 也就是说，不管需要执 行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

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

SELECE:

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

• InnoDB只查找版本早千当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

• 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

UPDATE：

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

MVCC只在 REPEATABLE READ 和 READ COMMITTED两个隔离级别下工作。其他两个隔离 级别都和MVCC不兼容注 4，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合 当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

三大范式

范式是数据库设计时遵循的一种规范，不同的规范要求遵循不同的范式。

第一范式(1NF)：

属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

第二范式(2NF)：

满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

表中主键为stu_id和kc_id组成的联合主键。满足1NF；非主键列score完全依赖于主键，stu_id和kc_id两个值才能决定score的值；而kc_name只依赖于kc_id，与stu_id没有依赖关系，它不完全依赖于主键，只依赖于主键的一部分，不符合2NF。

第三范式(3NF)：

满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

表中sex_desc依赖于sex_code，而sex_code依赖于id(主键)，从而推出sex_desc依赖于id(主键)；sex_desc不直接依赖于主键，而是通过依赖于非主键列而依赖于主键，属于传递依赖，不符合3NF。

范式的优点和缺点

优点：

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

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

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

4. 很少有多余的数据意味着检索列表数据时更少需要 DISTINCT 或者 GROUP BY语句。

缺点：

范式化设计的schema的缺点是通常需要关联。这不但代价昂贵，也可能使 些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引

反范式的优点和缺点

优点：

1. 因为所有数据都在一张表中，可以很好地避免关联

2. 如果不需要关联表，则对大部分查询最差的情况就是：即使表没有使用索引（是全表扫描）。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I0

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

数据类型优化

良好的逻辑设计和物理设计师高性能的基石，但所有的设计都应根据实际情况来，无脑的使用类型小，cup利用率小的设计，只会让你的设计变得局限

三点：

1、更小的通常更好：

一般情况下，应该尽量使用可以正确存储数据的最小数据类型，因为他们占用更少的磁盘、内存、CPU缓存，并且处理时需要的CPU周期也更少。（但是要确保没有低估需要存储的值的范围）

2、简单就好：

简单数据类型的操作通常需要更少的 CPU 周期。例如， 整型比字符操作代价更低， 因为字符集和校对规则（排序规则 ）使字符比较比整型比较更复杂。

3、尽量避免null：

通常情况下最好指定列为NOT NULL, 除非真的需要存储NULL 值。如果查询中包含可为NULL 的列， 对MySQL来说更难优化， 因为可为NULL 的列 使得索引、 索引统计和值比较都更复杂。 可为 N ULL的列会使用更多的存储空间， 在MySQL里也需要特殊处理。 当可为NULL的列被索引时， 每个索引记录需要一个额 外的字节， 在MyISAM 里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的列改为NOT NULL 带来的性能提升比较小， 所以（调优时）没有 必要首先在现有schema中查找井修改掉这种情况，除非确定这会导致问题。 但是， 如果计划在列上建索引， 就应该尽扯避免设计成可为 NULL 的列。

整数类型

整数类型包含：tinyint、smallint、mediumint、int、bigint。分别使用8，16, 24, 32, 64位存储空间。它们可以存储的值的范围从-2(n-1)到2(n-1)-1，其中n是存储空间的位数。

整数类型有可选的 UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍，
例如 TINYINT. UNSIGNED 可以存储的范围是 0 - 255, 而 TINYINT 的存储范围是-128~127。

有符号和无符号类型使用相同的存储空间，并具有相同的性能， 因此可以根据实际情况选择合适的类型。

MySQL 可以为整数类型指定宽度， 例如 INT(ll)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL 的 些交互工具（例如 MySQL 命令行客户端）用来显示字符的个数。 对于存储和计算来说， INT(l) 和 INT(20) 是相同的。

实数类型

实数是带有小数部分的数字。然而， 它们不只是为了存储小数部分，也可以使用DECIMAL 存储比BIGINT 还大的整数。

因为需要额外的空间和计算开销，所以应该尽扯只在对小数进行精确计算时才使用DECIMAL—例如存储财务数据。 但在数据最比较大的时候， 可以考虑使用BIGINT代替 DECIMAL, 将需要存储的货币单位根据小数的位数乘以 相应的倍数即可。这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

varchar：

1. VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。 它比定长类型 更节省空间，因为它仅使用必要的空间

2. VARCHAR需要使用1 或2个额外字节记录字符串的长度；（以255为界线）

3. VARCHAR节省了存储空间，所以对性能也有帮助。

4. 在5.0 或者更高版本，MySQL 在存储和检索时会保留末尾空格。但在4.1 或更老
   的版本，MySQL会剔除末尾空格。

char：

1. CHAR类型是定长的：MySQL总是根据定义的字符串 长度分配足够的空间

2. 存储CHAR值时，MySQL会删除所有的末尾空格

BINARY 和 VARBINARY：

1. 它们存储的是二进制字符串。

2. 当需要存储二进制数据， 井且希望 MySQL 使用字节码而不是字符进行比较时， 这些类型是非常有用的。 二进制比较的优势井不仅仅体现在大小写敏感上。 MySQL 比较BINARY字符串时， 每次按一个字节， 并且根据该字节的数值进行比较。 因此， 二进制比 较比字符比较简单很多， 所以也就更快

重点：

使用 VARCHAR(5) 和 VARCHAR(200) 存储’hello’的空间开销是一样的。 那么使用更 短的列有什么优势吗？
事实证明有很大的优势。 更长的列会消耗更多的内存， 因为 MySQL 通常会分配固定大小的内存块来保存内部值。 尤其是使用内存临时表进行排序或操作时会特别糟糕。 在利用磁盘临时表进行排序时也同样糟糕。
所以最好的策略是只分配真正需要的空间。

BLOB和TEXT：

1. BLOB 和TEXT 都是为存储很大的数据而设计的字符串数据类型， 分别采用二进制和字符方式存储。

2. 它们分别属千两组不同的数据类型家族：字符类型是TINYTEXT, SMAL LTEXT, TEXT, MEDIUMTEXT, tONGTEXT 1 对应的二进制类型是TINYBLOB, SMALLBLOB, BLOB,MEDIUMBLOB, LONGBLOB。BLOB 是SMALLBLOB 的同义词， TEXT 是SMALLTEXT 的同义词。

3. MySQL 把每个BLOB 和TEXT 值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB 和TEXT 值太大时，InnoDB会使用专门的“ 外部“存储区域来进行存储， 此时每个值在行内需要l ~4个字节存储一个指针，然后在外部存储区域存储实际的值

使用枚举 (ENUM) 代替字符串类型

有时候可以使用枚举列代替常用的字符串类型。 枚举列可以把一些不重复的字符串存储成一个预定义的集合。 MySQL在存储枚举时非常紧凑， 会根据列表值的数量压缩到 个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数， 的frm文件中保存 “数字—字符串” 映射关系的 “查找表”。（宛如一个map）

实际存储为整数， 而不是字符串

枚举字段是按照内部存储的整数而不是定义的字符串进行 排序的：

还有一个好处就是可以让表的大小缩小，因为字符串复用了嘛

日期和时间类型

Mysql能存储的最小时间粒度为秒，但是Mysql也可以使用微秒级的粒度进行临时运算

DateTime：

这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式YYYYMMDDHHMMSS的整数中，与时区无关。使用8 个字节的存储空间。

TIMESTAMP：

TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准 时间） 以来的 秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此 它的范围比DATETIME 小得多：只能表示从1970年到2038年。

默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间注见在插入一行记录时， MySQL默认也会更新第一个TIMESTAMP列的值（除非在UPDATE语句中明确指定了值）。你可以配置任何TIMESTAMP列的插入和更新行为

TIMESTAMP列默认为 NOT NULL, 这也和其他的数据类型不一样。

可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUB LE 存储秒之后的小数部分。

选择标识符（关联字段）

在可以满足值的范围的需求， 井且预留未来增长空间的前提下， 应该选择最小的数据类型。

整数类型：

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

字符串类型：

应该避免使用字符串类型作为标识列， 因为它们很消耗空间， 并且通常比数字类型慢。在我们的测试中， 我
们注意到最多有6倍的性能下降。

对于完全 ”随机” 的字符串也需要多加注意， 例如 MDS(）、 SHAl(）或者 UUID(）产生的字符串。 这些函数生成的新值会任意分布在很大的空间内， 这会导致 INSERT 以及一些SELECT语句变得很慢

• 因为插入值会随机地写到索引的不同位置， 所以使得 INSERT 语句更慢。 这会导致页分裂、 磁盘随机访问， 以及对于聚簇存储引擎产生聚簇索引碎片。

• SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。

• 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作 的访问局部性原理失效。 如果整个数据集都一样的 “热”，那么缓存任何一部分 特定数据到内存都没有好处；如果工作集比内存大， 缓存将会有很多刷新和不命中。

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然 而，有时也需要创建 一张完全独立的汇总表或缓存表（特别是为满足检索的需求时）。如果能容许少最的脏数据，这是非常好的方法，但是有时确实没有选择的余地（例如，需要避免复杂、 昂贵的实时更新操作）。

术语“缓存表”：来表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢） 数据的表（例如，逻辑上冗余的数据）

术语 “汇总表”：则保存的是使用GROUP BY语句聚合数据的表（例如，数据不是逻辑上冗余的）

栗子：

仍然以网站为例，假设需要计算之前24小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到，井且比实时维护计数器要高效得多。缺点是计数器并不是 100％精确。

如果必须获得过去 24 小时准确的消息发送数晕（没有遗漏），有另外一种选择。以每小 时汇总表为基础，把前23个完整的小时的统计表中的计数全部加起来，最后再加上开始阶段和结束阶段不完整的小时内的计数。

计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到井发问题。 计数器表在Web应用中很常见。 可以用这种表缓存一个用户的朋友数、 文件下载次数等。 创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。 使用独立的表可以帮助避免查询缓存失效；

问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁这会使得这些事务只能串行执行。 要获得更高的并发更新性能，也可以将计 数器保存在多行中，每次随机选择一行进行更新。

加快ALTER TABLE操作的速度

也就是说加快修改表结构的操作；

MySQL执行大部分修改表 结构操作的方法是用新的结构创建一个空表， 从旧表中查出所有数据插入新表， 然后删除旧表。 这样操作可能需要花费很长时间， 如果内存不足而表又很大， 而且还有很多索 引的情况下尤其如此。 许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚 至数天才能完成。

一般而言， 大部分ALTER TABLE操作将导致MySQL服务中断。对常见的场景， 能使用的技巧只有两种：

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

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

不是所有的ALTER TABLE操作都会引起表重建。MySQL可以跳过创建新表的步骤。 列的默认值实际上存在表的．frm文件中，所以可以直接修改这个文件而不需要改动表本身。 然而MySQL还没有采用这种优化的方法， 所有的MODIFY COLUMN操作都将导致表重建。

通过ALTER COLUMN操作来改变列的默认值：

基本的技术是为想要的表结构创建一个新的．frm文件， 然后用它替换掉已经存在的那张表的.frm文件， 像下面这样：

1. 创建一张有相同结构的空表，井进行所需要的修改（例如增加ENUM 常址）

2. 执行 FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，井且禁止任何表被打开。

3. 交换.frm文件．

4. 执行 UNLOCK TABLES 来释放第 2 步的读锁。