高性能Mysql读书笔记

第一章 MySQL架构与历史

1.1 MySQL逻辑架构

1. 最上层的服务（连接/线程处理）并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
2. 第二层结构是MySQL（查询缓存、解析器及优化器）比较有意思的部分。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、试图等。
3. 第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。和GUN/Linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL（InnoDB是一个例外，它会解析外键定义，因为MySQL服务器本身没有实现给功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层服务器的请求。

1.1.1 连接管理与安全性

1. 每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程（MySQL5.5或者更新的版本提供了一个API，支持线程池（Thread-Pooling）插件，可以使用池中少量的线程来服务大量的连接 ）。
2. 当客户端（应用）连接到MySQL服务器时，服务器需要对其经行认证。认证基于用户名、原始主机信息和密码。如果使用了安全套接字（SSL）的方式连接，还可以使用X.509证书认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有某个特定查询的权限（例如：是否允许客户端对world数据库的Country表执行SELECT语句）。

1.1.2 优化与执行

1. MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响他的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。
2. 优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如，某些存储引擎的某种索引，可能对一些特定的查询有优化。
3. 对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

1.2 并发控制

1. 一味的靠阻塞来控制并发会导致效率低下

1.2.1 读写锁

1. 在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁(shared lock)和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。 
2. 读锁是共享的，或者说是互相不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的统一资源。
3. 在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL会通过锁定防止其它用户读取统一数据。大多数时候，MySQL锁的内部管理是透明的。

1.2.2 锁粒度

1. 一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要互相之间不发生冲突即可。
2. 问题是加锁也需要消耗资源。锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会因此受到影响。
3. 所谓的锁策略，就是在锁的开销和安全性之间寻求平衡，这种平衡当然会影响到性能。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁(row-level lock)，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好地性能。
4. MySQL提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去另外一些应用场景的良好支持。好在MySQL支持多个存储引擎的架构，所有不需要单一的通用解决方案。下面将介绍两种最重要的锁策略。

表锁(table lock)

1. 表锁是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表。一个用户在对表进行写操作(插入、删除、更新等)前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其它读取的用户才能获得读锁，读锁之间是不互相阻塞的。
2. 在特定的场景里，表锁也可能有良好的性能。例如，READ LOCAL表锁支持某些类型的并发写操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面)。
3. 尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁(row lock)

1. 行级锁可以最大程度的支持并发处理(同时也带来了最大的锁开销)。众所周知，在InnoDB和XtraDb，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。

1.3 事务

1. 事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询地全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其它原因无法执行，那么所有地语句都不会执行。也就是说，事务内地语句，要么全部执行成功，要么全部执行失败。
2. ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)。一个运行良好地事务处理系统，必须具备这些标准特征。

原子性(atomicity)

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

一致性(consistency)

1. 数据库总是从一个一致性的状态转换到另外一个一致性的状态。

隔离性(isolation)

1. 通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

持久性(durability)

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

1.3.1 隔离级别

1. 隔离性其实比想象的要复杂。在SQL标准中定义了四种隔离级别，每种级别都规定了一个事务中所作的修改，哪些在事务内和事务间是可见的，那些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITTED(未提交读)

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

READ COMMITED(提交读)

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

REPEATABLE READ(可重复读)

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

SERIALIZABLE(可串行化)

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

1.3.2 死锁

1. 死锁是指两个或者多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。当多个事务同时锁定一个资源时，也会产生死锁。
2. 为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。

1.3.3 事务日志

1. 事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改器内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志(Write-Ahead Logging)，修改数据需要写两次磁盘。
2. 如果数据地修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改地数据。具体地恢复方式则视存储引擎而定。

1.3.4 MySQL中的事务

1. MySQL提供了两种事务性的存储引擎：InnoDB和NDB Cluster。另外还有一些第三方存储也支持事务。

自动提交(AUTOCOMMIT)

1. MySQL默认采用自动提交(AUTOCOMMIT)模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式
	1. SET AUTOCOMMIT = 1;
2. 1或ON表示启用，0或者OFF表示禁用。
3. 另外还有一些命令，再执行之前会强制执行COMMIT提交当前地活动事务。典型地例子，在数据定义语言(DDL)中，如果是会导致大量数据改变的操作，比如ALTER TABLE，就是如此。另外还有LOCK TABLES等其他语句也会导致同样地结果。
4. MySQL可以通过执行SET TRANSACTION ISOATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始地时候生效。可以在配置文件中设置整个数据库地隔离级别，也可以只改变当前会话地隔离级别：
	1. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED 
5. MySQL能够识别所有的4个ANSI隔离级别，InnoDB引擎也支持所有的隔离级别。

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

1. MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
2. 如果在事务中混合使用了事务型和非事务型的表(例如InnoDB和MyISAM表)，在正常提交的情况下不会有什么问题。
3. 但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

隐式和显式锁定

1. InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，所只有在执行COMMIT或ROLLBACK的时候才会释放，并且所有的锁都是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。
2. 另外，InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范。
	1. SELECT ... LOCK IN SHARE MODE
	2. SELECT ... FOR UPDATE
3. MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层实现的，和存储引擎无关。它们有自己的用途，但并不能替代事务处理。如果应用需要用到事务，还是应该选择事务型存储引擎。
4. 经常可以发现，应用已经将表从MyISAM转换到InnoDB,但还是显式地使用LOCK TABLES语句。这不但没有必要，还会严重影响性能，实际上InnoDB的行级锁工作得更好。

####建议：除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES外，其他任何时候都不要显示地执行LOCK TABLES，不管使用地是什么存储引擎。

1.4 多版本并发控制

1. MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都同时实现了**多版本并发控制(MVCC)。**不仅是MySQL，包括Oracle、PostgreSQL等其它数据库系统也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC没有一个统一的实现标准。
2. 可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
3. **MVCC的实现，**是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
4. 前面说到不同存储引擎的MVCC实现是不同的，典型的有乐观(optimistic)并发控制和悲观(pessimistic)并发控制。下面通过InnoDB的简化版行为来说明MVCC是如何工作的。
	1. InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值，而是系统版本号(system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事物的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在[REPEATABLE READ]隔离级别下，MVCC具体是如何操作的。
		1. SELECT
			1. InnoDB会根据以下两个条件检查每行记录
				1. InnoDB只查找版本早于当前当前事务版本的数据行(也就是，行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是事务开始前已经存在的，要么是事物自身插入或是修改过的。
				2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
				3. 只有符合上述两个条件的记录，才能返回作为查询结果。
		2. INSERT
			1. InnoDB为新插入的每一行保存当前系统版本号作为版本号。
		3. DELETE
			1. InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
		4. UPDATE
			1. InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
	2. 保存这两个额外系统版本号，是大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。
	3. MVCC只在REPREATABLE READ和READ COMMITED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

1.5 MySQL的存储引擎

1. 在文件系统中，MySQL将每个数据库(也可以称之为schema)保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。因为MySQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在Windows中，大小写是不敏感的；而在类UNIX中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。
2. 可以使用SHOW TABLE STATUS命令显示表的相关信息。下面简单介绍每一行的含义：
	1. Name:表名
	2. Engine:表的存储引擎类型。
	3. Row_format:行的格式。
	4. Rows:表中的行数。对于大多数存储引擎，该值是精确的，但对于InnoDB，该值是估计值。
	5. Avg_row_length:平均每行包含的字节数。
	6. Data_length:表数据的大小(以字节为单位)。
	7. Max_data_length:表数据的最大容量，该值和存储引擎有关。
	8. Index_length:索引的大小(以字节为单位)。
	9. Data_free:对于MyISAM表，表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行，以及后续可以被INSERT利用到的空间
	10. Auto_increment:下一个AUTO_INCREMENT的值。
	11. Create_time:表的创建时间。
	12. Update_time:表数据的最后修改时间。
	13. Check_time:使用CHECK TABLE命令或者myisamchk工具最后一次检查表的时间。
	14. Collation:表的默认字符集和字符列排序规则。
	15. Checksum:如果启用，保存的是整个表的实施校验和。
	16. Create_options:创建表时指定的其他选项。
	17. Comment:该列包含了一些其他的额外信息。
		1. 对于MyISAM表，保存的是表在创建时带的注释。
		2. 对于InnoDB表，则保存的是InnoDB表空间的剩余空间信息。
		3. 如果是一个视图，则该列包含"VIEW"的文本字样。

1.5.1 InnoDB存储引擎

第2章 MySQL基准测试

1. 基准测试(benchmark)是MySQL新手和专家都需要掌握的一项基本技能。简单地说，基准测试是针对系统设计的一种压力测试。

2.1 为什么需要基准测试

1. 因为基准测试是唯一方便有效的、可以学习系统在给定的工作负载下会发生什么的方法。基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握那些是重要的变化，或者观察系统如何处理不同的数据。基准测试可以在系统实际负载之外创造一些虚构场景进行测试。
2. 基准测试的一个主要问题在于其不是真实压力的测试。基准测试施加给系统的压力相对于真实压力来说，通常比较简单。真实压力是不可预期而且变化多端的，有时候情况过于复杂而难以解释。所以使用真实压力测试，可能难以从结果中分析出确切的结论。
3. 结论就是，我们只能进行大概的测试，来确定系统大致的余量有多少。

2.2 基准测试的策略

1. 基准测试有两种主要的策略：一是针对整个系统的整体测试，另外是单独测试MySQL。这两种策略也被称为集成式(full-stack)以及单组件式(single-component)基准测试。针对整个系统做集成式测试，而不是单独测试MySQL的原因主要有以下几点：
	1. 测试整个应用系统，包括Web服务器、应用代码、网络和数据库是非常有用的，因为用户关注的并不仅仅是MySQL本身的性能，而是应用整体的性能。
	2. MySQL并非总是应用的瓶颈，通过整体的测试可以揭示这一点。
	3. 只有对应用做整体测试，才能发现各部分之间的缓存带来的影响。
	4. 整体应用的集成式测试更能揭示应用的真实表现，而单独组件的测试很难做到这一点。
2. 另外一方面，应用的整体基准测试很难建立，甚至很难正确设置。如果基准测试的设计有问题，那么结果就无法反映真实的情况，从而基于此做的决策也就可能是错误的。
3. 不过，有时候不需要了解整个应用的情况，从而只需要关注MySQL的性能，至少在项目初期可以这样做。基于以下情况，可以选择只测试MySQL:
	1. 需要比较不同的schema或查询的性能
	2. 针对应用中某个具体问题的测试
	3. 为了避免漫长的基准测试，可以通过一个短期的基准测试，做快速的"周期循环"，来检测出某些调整后的效果。

2.2.1 测试何种指标

1. 在开始执行甚至是在设计基准测试之前，需要先明确测试的目标。请考虑以下指标，看看如何满足测试的需求。
	1. 吞吐量
		```
			吞吐量指的是单位时间内的事务处理数。这一直是经典的数据库应用测试指标。这类基准测试主要针对在线事务处理(OLTP)的吞吐量，非常适用于多用户的交互式应用。常用的测试单位是每秒事务数(TPS)，有些也采用每分钟事务数(TPM)。
		```
	2. 响应时间或延迟
		```
			这个指标用于测试任务所需的整体时间。根据具体的应用，测试的时间单位可能是微秒、毫秒、秒或者分钟。根据不同的时间单位可以计算出平均响应时间和所占百分比。因为最大、最小时间的值是不可重复的，因此，通常可以使用百分比响应时间(percentile response time)来替代最大响应时间。例如，如果95%的响应时间都是5ms，则表示任务在95%的时间段内都可以在5ms之内完成。
		```
	3. 并发性
		```
			并发性是一个非常重要又经常被误解和误用的指标。例如，它经常被表示为多少用户在同一时间浏览一个Web站点，经常使用的指标是多少个会话。然而,HTTP协议是无状态的，大多数用户只是简单地读取浏览器上显示地信息，这并不等同于Web服务器地并发性。而且，Web服务器地并发性也不等同于数据库地并发性，而仅仅是表示会话存储机制可以处理多少数据的能力。Web服务器地并发性更准确地度量指标，应该是在任意时间有多少同时发生地并发请求。
			在应用地不同环节都可以测量相应地并发性。Web服务器地高并发，一般也会导致数据库的高并发。注意不要将创建数据库连接和并发性搞混淆。一个设计良好的应用，同时可以打开成百上千个MySQL数据库服务器连接，但可能同时只有少数连接在执行查询。
			换句话说，并发性基准测试需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数。当并发性增加时，需要测量吞吐量是否下降，响应时间是否变长。
			并发性的测量完全不同于响应时间和吞吐量。他不像是一个结果，而更像是设置基准测试的一种属性。并发性测试通常不是为了测试应用能达到的并发性，而是为了测试应用在不同并发下的性能。
		```
	4. 可扩展性
		```
			在系统的业务压力下可能发生变化的情况下，测试扩展性就非常必要了。简单的说，可扩展性指的是，给系统增加一倍的工作，在理想情况下就能获得两倍的结果
		```
	5. 归根到底，应该测试那些对用户来说最重要的的指标。因此应该尽可能地去收集一些需求。

2.3 基准测试方法

1. 在讨论如何设计好的基准测试之前，先来看下如何避免一些常见的错误：
	1. 使用真实数据的子集而不是全集。
	2. 使用错误的数据分布
	3. 使用不真实的分布参数
	4. 在多用户场景，只做单用户的测试
	5. 在单服务器上测试分布式应用
	6. 与真实用户行为不匹配
	7. 反复执行同一个查询
	8. 没有检查错误
	9. 忽略了系统预热(warm up)的过程
	10. 使用默认的服务器配置
	11. 测试时间太短

2.3.1 设计和规划基准测试

1. 规划基准测试的第一步是提出问题并明确目标。然后决定是采用标准的基准测试，还是设计专用的测试。
2. 如果采用标准的基准测试，应该确认选择了合适的测试方案。
3. 设计专用的基准测试是很复杂的，往往需要一个迭代的过程。
	1. 首先需要获得生产数据集的快照，并且该快照很容易还原，以便进行后续的测试。
	2. 然后，针对数据运行查询。
	3. 可以在不同级别记录查询。
4. 即使不需要创建专用的基准测试，详细地写下测试规划也是必需的。
5. 应当建立将参数和结果文档化的规范，每一轮测试都必须进行详细记录。

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

1. 基准测试应该运行足够长的时间，这一点很重要。
2. 有时候无法确认测试需要运行多长的时间才足够。如果是这样，可以让测试一直运行，持续观察直到确认系统已经稳定。
3. 一个常见的错误地测试方式是，只执行一系列短期的测试，比如每次60s，并在此测试的基础上去总结系统的性能。

2.3.3 获取系统性能和状态

1. 在执行基准测试时，需要尽可能地收集被测试系统的信息。最好为基准测试建立一个目录，并且每执行一轮测试都创建单独的子目录，将测试结果、配置文件、测试指标、脚本和其他相关说明都保存在其中。

2.3.4 获得准确的测试结果

1. 获得准确测试结果的最好办法，是回答一些关于基准测试的基本问题：
	1. 是否选择了正确的基准测试？
	2. 是否为问题收集了相关的数据？
	3. 是否采用了错误地测试标准？
2. 接着，确认测试结果是否可重复
3. 最后，如果测试中出现异常结果，不要轻易当作坏数据点而丢弃，应该认真研究并找到产生这种结果的原因。

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

1. 一旦准备就绪，就可以着手基准测试，收集和分析数据了。
2. 通常来说，自动化基准测试是个好主意。

2.4 基准测试工具

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

2.4.1 集成式测试工具

1. 已有的集成式测试工具如下所示：
	1. ab:ab是一个Apache HTTP服务器基准测试工具。它可以测试HTTP服务器每秒最多可以处理多少请求。但其用途有限，只能针对单个URL进行尽可能快的压力测试。
	2. http_load:作用与ab类似，但其可以对多个URL进行随机测试，也可以定制，使其按照时间比率进行测试，而不仅仅是测试最大请求处理能力。
	3. JMeter:JMeter是一个JAVA应用程序，可以加在其他应用并测试其性能。

前三章太过于理论，像在读论文，读完这本书再读。

第4章 Schema与数据类型优化

1. 良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如，反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢。

4.1 选择优化的数据类型

1. MYSQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储那种类型的数据，下面几个简单的原则都有助于做出更好的选择。
	1. 更小的通常更好
		1. 一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
		2. 但是要确保没有低估要存储的值的范围，因为在shema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确认那个数据类型是最好的，就选择你认为不会超过范围的最小类型。
	2. 简单就好
		1. 简单数据类型的操作通常需要更少的CPU周期。
	3. 尽量避免NULL
		1. 很多表都包含可为NULL(空值)的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。
		2. 通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以(调优时)没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。
		3. 当然也有例外，例如值得一提的是，InnoDB使用单独的位(bit)存储NULL值，所以对于稀疏数据有很好的空间效率。但这一点不适用于MyISAM。
2. 在为列选择数据类型时，第一步需要确定合适的大类型:数字、字符串、时间等。这通常是很简单的，但是我们会提到一些特殊的不是那么直观的案例。
3. 下一步是选择具体类型。很多MYSQL的数据类型可以存储相同类型的数据，但是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间(磁盘和内存空间)不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

4.1.1 整数类型

1. 有两种类型的数字:整数(whole number)和实数(real number)。如果存储整数，可以使用这几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8，16，24，32，64位存储空间。它们可以存储的值的范围从-2^(N-1)到2^(N-1)-1，其中N是存储空间的位数。
2. 整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128~127。
3. 有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。
4. 你的选择决定MYSQL是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用64位的BIGINT整数，即使在32位环境也是如此。(一些聚合函数除外，它们使用DECIMAL或DOUBLE进行计算)。
5. MYSQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的:它不会限制值得合法范围，只是规定了MYSQL得一些交互工具用来显示字符得个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

4.1.2 实数类型

1. 实数是带有小数部分的数字，然而，它们不只是为了存储小数部分:也可以使用DECIMAL存储比BIGINT还大的整数。MYSQL即支持精确类型，也支持不精确类型。
2. FLOAT和DOUBLE类型支持使用标准的浮点计算进行近似计算。如果需要知道浮点运算是怎么计算的，则需要研究所使用的平台的浮点数的具体实现。
3. DECIMAL类型用于存储精确的小数。在MUSQL5.0和更高版本，DECIMAL类型支持精确计算。因为CPU不支持对DECIMAL的直接计算，所以在MYSQL5.0以及更高版本中，MYSQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点计算明显更快。
4. 浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MYSQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节保存9个数字)。例如，DECIMAL(18,9)，小数点两边将各存储9个数字，一共使用9个字节:小数点前后各用4个字节，小数点本身占1个字节。
5. MYSQL5.0和更高版本中的DECIMAL类型允许最多65个数字。
6. 有多种方法可以指定浮点列所需要的精度，这会使得MYSQL悄悄选择不同的数据类型，或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型，不指定精度。
7. 浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型;MYSQL使用DOUBLE作为内部浮点计算的类型。
8. 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的小数根据小数位乘以相应的倍数即可。这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

4.1.3 字符串类型

1. MYSQL支持多种字符串类型，每种类型还有很多变种。从MYQL4.1开始，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则(collation)。这些东西会在很大程度上影响性能。

VARCHAR和CHAR类型

1. VARCHAR和CHAR是两种主要的字符串类型。下面的描述假设使用的存储引擎是InnoDB和/或者MyISAM。
2. 先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意，存储引擎存储CHAR或者VARCHAR值得方式在内存中和在磁盘上可能不一样，所以MYSQL服务器从存储引擎读出得值可能需要转换为另一种存储格式。下面是关于两种类型得一些比较
	1. VARCHAR
		1. VARCHAR类型用于存储可变长字符串，是最常见得字符串数据类型。它比定长类型更节省空间，因为它仅使用必要得空间。有一种情况例外，如果MYSQL表使用ROW_FORMAT=FIXED创建得话，每一行都会使用定长存储，这会很浪费空间。
		2. VARCHAR需要使用1或2个额外字节记录字符串得长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设使用latin1字符集，一个VARCHAR(10)得列需要11个字节得存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。
		3. VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。
		4. **下面这些情况下使用VARCHAR是合适的：**
			1. 字符串列的最大长度比平均长度大得多
			2. 列的更新很少，所以碎片不是问题
			3. 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
		5. 在5.0或者更高版本，MYSQL在存储和检索时会保留末尾空格。但在4.1或更老版本，MYSQL会剔除末尾空间。
	2. CHAR
		1. CHAR类型是定长的:MYSQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MYSQL会删除所有的末尾空间(这在那个版本的MYSQL都是这样处理的)。CHAR值会根据需要采用空格进行填充以方便比较。
		2. CHAR适合存储很短的字符串，或者所有值都接近同一个长度。
	3. 数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串。
3. 与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串和常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。填充也不一样:MYSQL填充BINARY采用的是\0(零字节)而不是空格，在检索时也不会去填充值。
4. 但需要存储二进制数据，并且希望MYSQL使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势不仅仅体现在大小写敏感上。MYSQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就很快。

慷慨是不明智的

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

BLOB和TEXT类型

1. BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
2. 实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT,SAMLLTEXT,TEXT,MEDIUNTEXT,LONGTEXT;对应的二进制类型是TINYBLOB,SAMLLBLOB,BLOB,MEDIUNBLOB,LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
3. 与其他类型不同，MYSQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
4. BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
5. MYSQL对BLOB和TEXT进行排序与其他类型不同的是：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减少max_sort_length的配置，或者使用ORDER BY SUSTRING(column,length)
6. MYSQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

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

1. 有时候可以使用枚举值代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MYSQL在存储枚举时非常紧凑，会根据列表值得数量压缩到一个或两个字节中。**MYSQL在内部会将每个值在列表中得位置保存为整数，并在表的[.frm]文件中保存"数字-字符串"映射关系得查找表**，示例如下：
	```
		CREATE TABLE enum_test(
			e ENUM('fish','apple','dog') NOT NULL
		);
	```
2. 枚举字段是按照内部存储的整数而不是定义得字符串进行排序的，一种绕过这种限制的方式是按照需要的顺序来定义枚举值。另一种方式是可以在查询中使用FIELD()函数显式地指定排序顺序，但这会导致MYSQL无法利用索引消除排序。
3. 枚举最不好的地方是字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素，这样在MYSQL5.1就可以不用重建整个表来完成修改。

4.1.4 日期和时间类型

1. MYSQL可以使用许多类型来保存日期和时间值。例如YEAR和DATE。MySQL能存储的最小时间粒度为秒(MariaDB支持微秒级别的时间类型)。但是MYSQL也可以使用微秒级的粒度进行临时运算，稍后会展示怎么绕开这种存储限制。
2. 大多数时间类型都没有替代品，因此没有什么是最佳选择的问题。为一个问题是保存日期和时间的时候需要做什么。MYSQL提供两种相似的日期类型:DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但在某些场景，一个比另一个工作的更好。

DATATIME

1. 这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8字节的存储空间。
2. 默认情况下，MYSQL以一种可排序的、无歧义的格式显示DATETIME值，例如"2020-11-06 14:20:09"。这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP

1. 就像它的名字一样，TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多:只能表示从1970年到2038年。MYSQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP函数把日期转换为UNIX时间戳。
2. TIMESTAMP显示的值依赖于时区。如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。
3. TIMESTAMP列默认为NOT NULL，这和其它的数据类型不一样，所以，默认情况下，如果插入时没有指定TIMESTAMP列的值，MYSQL则设置这个列的值为当前时间，如果指定了值，则按指定值存储。
4. 如果要存储比秒更小粒度的日期和时间怎么办?MYSQL目前没有提供合适的数据类型，但是可以使用自己的存储格式:可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。这两种方式都可以，或者也可以使用MariaDB代替MYSQL。

4.1.5 位数据类型

1. MYSQL有少数存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

BIT

1. 在MYSQL5.0之前，BIT是TINYINT的同义词。但是在MYSQL5.0以及更新版本，这是一个特性完全不同的数据类型。下面我们将讨论BIT类型新的行为特性。
2. **可以使用BIT列在一列中存储一个或多个true/false值。**BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位，依次类推。BIT列的最大长度是64个位。
3. BIT的行为因存储引擎而异。MYISAM会打包存储所有的BIT列，所以17个单独的BIT列只需要17个位存储(假设没有可为NULL的列)，这样MYISAM只使用3个字节就能存储这17个BIT列。其它存储引擎例如MEMORY和InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。
4. MYSQL把BIT当作字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值得字符串，而不是ASCII码得"0"或"1"。然而，**在数字上下文得场景中检索时，结果将是字符串转换成得数字。如果需要记得这一点。**例如，如果存储一个值b'00111001'（二进制值等于57）到BIT(8)的列并且检索它，得到的内容是字符码为57的字符串。也就是说得到ASCII码为57的字符"9"。但是在数字上下文场景中，得到的是数字57：
	```SQL
		SELECT a,a + 0 FROM bittest;

		共输出两个值，分别为：9，57
	```
5. 上面SQL演示的现象是相当令人费解的，所以我们认为应该谨慎使用BIT类型。对于大部分应用，最好避免使用这种类型。
6. 如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串(空字符串)。

SET

1. 如果需要保存很多的true/false值，可以考虑合并这些列到一个SET数据类型，它在MYSQL内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MYSQL有像FIND_IN_SET()和FIELD()这样的函数，方便地在查询中使用。**它的主要缺点是改变列的定义的代价较高:需要ALTER TABLE，这对于大表来说是非常昂贵的操作(本章最后给出了解决办法)。一般来说，也无法在SET列上通过索引查找。**
2. 在整数列上进行按位操作
	1. 一种替代SET的方式是使用一个整数保证一系列的位。例如，可以把8个位包装到一个TINYINT中，并且按位操作来使用。可以在应用中为每个位定义名称常量来简化这个工作。
	2. 比起SET，这种方法主要的好处在于可以不使用ALTER TABLE改变字段代表的"枚举值"，缺点是查询语句更难写，并且更难理解。如果使用SET列，可以让MYSQL在列定义里存储位到值得映射关系；如果使用整数列，则可以在应用代码里村粗和这个对应关系。

4.1.6 选择标识符(identifier)

1. 为标识列(identifier column)选择合适的数据类型非常重要。一般来说更有可能用标识列与其它值进行比较，或者通过标识列寻找其它列。标识列也可能在另外得表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中得对应列一样的类型,在相关的表中使用相同的数据类型是个好主意，因为这些列很可能在关联中使用。
2. 当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MYSQL对这种类型怎么执行计算和比较。
3. 一旦选定了一种类型，要确保在所有关联表中使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。这种错误可能会很久以后才突然出现，那时候可能都已经忘记是在比较不同的数据类型。
4. 在可以满足值得范围得需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。
5. 下面是一些小技巧：
	1. 整数类型：
		1. 整数通常是标识符最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。
	2. ENUM和SET类型
		1. 对于标识列来说，ENUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态"定义表"来说可能没有问题。ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别。
	3. 字符串类型
		1. 如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。尤其是在MYISAM表里使用字符串作为标识列时要特别小心。MYISAM默认对字符串使用压缩索引，这会导致查询慢很多。
		2. 对于完全"随机"的字符串也许要多加注意，例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢。
			1. 因为插入值会随机的写到索引的不同位置，所以使得INSERT语句更慢。这会导致页分裂、磁盘随机访问，以及对于拥簇存储引擎产生拥簇索引碎片。
			2. SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
			3. 随机值导致缓存对所有类型的查询语句效果很差，因为会使得缓存赖以工作的访问局部性原理失效。如果整个数据集都一样的热，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。
	4. 如果存储UUID值，则应该移除"-"符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。
	5. UUID()生成的值与加密散列函数生成的值有不同的特征:UUID值虽然分布也不均匀，但还是会有一定的顺序。尽管如此，但还是不如递增的整数好用。

4.1.7 特殊类型数据

1. 某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子；本章的前面部分也演示过存储此类数据的一些选项。
2. 另一个例子是一个IPv4地址。人们经常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MYSQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法间转换。

4.2 MySQL shema设计中的缺陷

1. 虽然有一些普遍的好或坏的设计原则，但也有一些问题是由MySQL的实现机制导致的，这意味着有可能犯一些只在MySQL下发生的特定错误。本节我们讨论设计MySQL的shema的问题。这也许会帮助你避免这些错误，并且选择在MySQL特定实现下工作的更好的替代方案。
2. 太多的列
	1. MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。当我们研究一个CPU占用非常高的案例时，发现客户使用了非常宽的表(数千个字段)，然而只有一小部分列会实际用到，这时转换的代价就非常高。如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同。
3. 太多的关联
	1. 所谓的"实体-属性-值"(EAV)设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则，如果希望查询执行的快速且并发性好，单个查询最好在12个表内做关联。

4.3 范式和反范式

1. 对于任何给定的数据通常都有很多种方法，从完全的范式化到完全的反范式化，以及两者的折中。在范式化的数据库中，每个事实数据都会出现并且只出现一次。相反，在反范式的数据库中，信息是冗余的，可能会存储在多个地方。

4.3.1 范式的优点和缺点

1. 当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处。
	1. 范式化的更新操作通常比反范式化要快
	2. 当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
	3. 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
	4. 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或GROUP BY语句。
2. 范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。

4.3.2 反范式的优点和缺点

1. 反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。
2. 如果不需要关联表，则对大部分查询最差的情况-即使表没有使用索引-是全表扫描，当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。
3. 单独的表也能使用更有效的索引策略。关联查询主要问题是关联，使得需要在一个索引中又排序又过滤。如果采用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引(account_type,published)，就可以不通过关联写出相关查询，这将非常高效。

4.3.3 混用范式化和反范式化

1. 范式化和反范式化的schema各有优劣，怎么选择最佳的设计?
2. 事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可以使用部分范式化的shema、缓存表以及其他的技巧。
3. 最常见的反范式化数据的方法是复制或缓存，在不同的表中存储相同的特定列。在MYSQL5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。
4.  但是使用混用的方式存储数据，会使得更新的操作代价更高了，因为需要同时更新多张表。至于这会不会成为一个问题，需要考虑更新的频率以及更新的时长，并和执行SELECT查询的频率进行比较。
5.  另一个从父表冗余一些数据到子表的理由是排序的需要。例如，在范式化的schema里通过作者的名字对消息做排序的代价将会非常高，但是如果在message表中缓存author_name字段并且建好索引，则可以非常高效的完成排序。
6.  缓存衍生值也是有用的。如果需要显示每个用户发了多少信息，可以每次执行一个昂贵的子查询来计算并显示它;也可以在user表中建一个num_messages列，每当用户发送新消息时更新这个值。

4.4 缓存表和汇总表

1. 有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求时)。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地。
2. 术语：缓存表和汇总表的解释
	1. 缓存表：用来表示存储哪些可以比较简单的从schema其他表获取数据的表(例如，逻辑上冗余的数据)。
	2. 汇总表：则保存的是使用GROUP BY语句聚合数据的表(例如，数据不是逻辑上冗余的)
3. 在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。那个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引(这会更加高效)。
4. 当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。这就需要通过使用影子表来实现，影子表指的是一张在真是表背后创建的表。

4.4.1 物化视图

1. 略

4.4.2 计数器表

1. 如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器在WEB应用中很常见。使用独立的表可以帮助避免查询缓存失败，而且可以使用本节展示的一些更高级的技巧。
2. 应该让事情变得尽可能简单，假设有一个计数器，只有一行数据。

4.5 加快ALTER TABLE操作的速度

1. MYSQL的ALTER TABLE操作的性能对大表来说是个大问题。MYSQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。MYSQL5.1以及更新版本包含一些类型的"在线"操作的支持，这些功能不需要在整个操作过程中锁表。最近版本的InnoDB也支持通过排序来建索引，这使得建索引更快并且有一个紧凑的索引布局。
2. 一般而言，大部分ALTER TABLE操作将导致MYSQL服务中断。我们会展示一些在DDL操作时有用的技巧，但这些针对一些特殊的场景而言的。对常见的场景，能使用的技巧只有两种:一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换:另一种技巧是"影子拷贝"。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
3. **不是所有的ALTER TABLE操作都会引起表重建。**例如要修改表的某个字段的默认值。
	1. 实例：
		```
			ALTER TABLE sakila.film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL SET DEFAULT 5
		```
4. 理论上，MYSQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。然而MYSQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都将导致表重建。
5. 另一种方法是通过ALTER COLUMN操作来改变列的默认值:
	1. 示例:
		```
			ALTER TABLE sakila.film ALTER COLUMN rental_duration SET DEFAULT 5;
		```
6. 这个语句会直接修改.frm文件而不涉及表数据。所以，这个操作是非常快的。

4.5.1 只修改.frm文件

1. 从上面的例子我们看到修改表的.frm文件是很快的，但MYSQL有时会在没有必要的时候也重建表。如果宁愿冒一些风险，可以让MYSQL做一些其它类型的修改而不用重建表。

	下面我们要演示的技巧是不受官方支持的，也没有文档记录，并且也可能不能正常工作，采用这些技术需要自己承担风险。建议在执行之前首先备份数据。

2. 下面这些操作是有可能不需要重建表的:
	1. 移除(不是增加)一个列的AUTO_INCREMENT属性。
	2. 增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串值。
3. 基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像下面这样：
	1. 创建一张有相同结构的空表，并进行所需要的修改(例如增加ENUM常量)
	2. 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开
	3. 交换.frm文件
	4. 执行UNLOCK TABLES来释放第2步的读锁。

4.5.2 快速创建MYISAM索引

1. 为了高效地载入数据到MYISAM表中，有一个常用的技巧是先禁用索引，载入数据，然后重新启用索引:略
2. 略

4.6 总结

1. 良好的shema设计原则是普遍适用的，但MYSQL有它自己的实现细节要注意。概括来说，尽可能保持任何东西小而简单总是好的。MYSQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：
	1. 尽量避免过度设计。
	2. 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
	3. 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
	4. 注意可变长长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
	5. 尽量使用整型定义标识列。
	6. 避免使用MYSQL已经遗弃的特性，例如执行浮点数的精度，或者整数的显示宽度。
	7. 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT。