MySQL 逻辑架构
最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
第二层架构是MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。和GNU/Linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。
连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。 当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。
优化与执行
MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示(hint)优化器，影响它的决策过程。也可以请求优化器解释（explain)优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。 对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
存储引擎
InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期（short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。采用MVCC来支持高并发，并且实现了四个标准的隔离级别。并且通过间隙锁（next-key locking)策略防止幻读的出现。 间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
MyISAM存储引擎
在MySQL5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
数据类型
MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。
- 更小的通常更好： 一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
- 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。
- 尽量避免NULL： 如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节
整数类型
有两种类型的数字：整数（whole number)和实数（real number)。
整数
如果存储整数，可以使用这几种整数类型：
- TINYINT：8位存储空间
- SMALLINT：16位存储空间
- MEDIUMINT：24位存储空间
- INT：32位存储空间
- BIGINT：64位存储空间
存储的值的范围：-2(N-1)到2(N-1)，其中N是存储空间的位数。 整数类型有可选的UNSIGNED属性，表示不允许负值，可以使正数的上限提高一倍。 例如TINYINT.UNSIGNED可以存储的范围是0-255，而TINYINT的存储范围是-128-127。 有符号和无符号类型使用相同的存储空间，并具有相同的性能。MySQL可以为整数类型指定宽度，例如INT(11），对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说，INT（1）和INT（20）是相同的。
实数
实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
DECIMAL类型用于存储精确的小数，支持精确计算。可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。例如，DECIMAL（18，9）小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。 DECIMAL类型允许最多65个数字。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型。MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
字符串类型
MySQL支持多种字符串类型，每种类型还有很多变种。
VARCHAR
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。 VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，InnoDB则需要分裂页来使行可以放进页内。下面这些情况下使用VARCHAR是合适的：
- 字符串列的最大长度比平均长度大很多
- 列的更新很少，所以碎片不是问题
- 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR
CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。 CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。
对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1）来存储只有Y和N的值，如果采用单字节字符集生5只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。
BINARY和VARBINARY
与CHAR和VARCHAR类似的类型，它们存储的是二进制字符串。 二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。 填充也不一样：MySQL填充BINARY采用的是\0（零字节）而不是空格，在检索时也不会去掉填充值。 当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。
BLOB和TEXT类型
为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。 实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT,EONGTEXT；对应的二进制类型是TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB,LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部” 存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
日期和时间类型
DATETIME
这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。 默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值，例如“2008-01-16 22:37:08”。这是ANSI标准定义的日期和时间表示方法。
TIMESTAMP
TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多：只能表示从1970年到2038年。
除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。 有时候人们会将Unix时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所以我们不推荐这样做。
位数据类型
MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。
BIT
可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，BIT（2）存储2个位，依此类推。BIT列的最大长度是64个位。 在InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。 MySQL把BIT当作字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”。所以我们认为应该谨慎使用BIT类型。对于大部分应用，最好避免使用这种类型。 如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR（0）列。该列可以保存空值（NULL）或者长度为零的字符串（空字符串）。
SET
如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间。 它的主要缺点是改变列的定义的代价较高：需要ALTER TABLE，这对大表来说是非常昂贵的操作。一般来说，也无法在SET列上通过索引查找。
表设计
虽然有一些普遍的好或坏的设计原则，但也有一些问题是由MySQL的实现机制导致的，这意味着有可能犯一些只在MySQL下发生的特定错误。比如：
太多的列
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。转换的代价依赖于列的数量。
太多的关联
所谓的“实体-属性-值”（EAV)设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。 事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。
外键
外键限制会将约束放到MySQL中，这对于必须维护外键的场景，性能会更高。不过这也会带来额外的复杂性和额外的索引消耗，还会增加多表之间的交互，会导致系统中更多的锁和竞争。外键可以被看作是一个确保系统完整性的额外的特性，但是如果设计的是一个高性能的系统，那么外键就显得很臃肿了。很多人在更在意系统的性能的时候都不会使用外键，而是通过应用程序来维护。
简单的原则：
- 尽量避免过度设计，例如会导致极其复杂查询的表设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。
- 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
- 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
- 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
- 尽量使用整型定义标识列。
- 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
- 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT。
范式和反范式
范式的优点和缺点
当为性能问题而寻求帮助时，经常会被建议对数据库表进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处：
- 范式化的更新操作通常比反范式化要快。
- 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
- 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
- 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUPBY语句。
缺点是：
- 通常需要关联。稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，也许更多。
- 可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。
反范式的优点和缺点
反范式化的设计因为所有数据都在一张表中，可以很好地避免关联。 如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。
完全的范式化和完全的反范式化设计都是概念，在实际应用中经常需要混用，可能使用部分范式化的设计、缓存表，以及其他技巧。
MySQL基准测试
基准测试是针对系统设计的一种压力测试。通常的目标是为了掌握系统的行为。基准测试可以完成以下工作，
- 验证基于系统的一些假设，确认这些假设是否符合实际情况。 重现系统中的某些异常行为，以解决这些异常。
- 测试系统当前的运行情况。如果不清楚系统当前的性能，就无法确认某些优化的效果如何。也可以利用历史的基准测试结果来分析诊断一些无法预测的问题。
- 模拟比当前系统更高的负载，以找出系统随着压力增加而可能遇到的扩展性瓶颈。
- 规划未来的业务增长。基准测试可以评估在项目未来的负载下，需要什么样的硬件，需要多大容量的网络，以及其他相关资源。这有助于降低系统升级和重大变更的风险。
- 测试应用适应可变环境的能力。例如，通过基准测试，可以发现系统在随机的并发峰值下的性能表现，或者是不同配置的服务器之间的性能表现。基准测试也可以测试系统对不同数据分布的处理能力。
基准测试的策略
基准测试有两种主要的策略：一是针对整个系统的整体测试，另外是单独测试MySQL。 这两种策略也被称为集成式（full-stack）以及单组件式（single-component)基准测试。 针对整个系统做集成式测试，而不是单独测试MySQL的原因主要有以下几点：
- 测试整个应用系统，包括Web服务器、应用代码、网络和数据库是非常有用的，因为用户关注的并不仅仅是MySQL本身的性能，而是应用整体的性能。
- MySQL并非总是应用的瓶颈，通过整体的测试可以揭示这一点。
- 只有对应用做整体测试，才能发现各部分之间的缓存带来的影响。 整体应用的集成式测试更能揭示应用的真实表现，而单独组件的测试很难做到这一点。
集成式测试工具
http_load
可以通过一个输入文件提供多个URL，http_load在这些URL中随机选择进行测试。 也可以定制http_load，使其按照时间比率进行测试，而不仅仅是测试最大请求处理能力。
JMeter
JMeter是一个Java应用程序，可以加载其他应用并测试其性能。它虽然是设计用来测试Web应用的，但也可以用于测试其他诸如FTP服务器，或者通过JDBC进行数据库查询测试。 JMeter比ab和http load都要复杂得多。例如，它可以通过控制预热时间等参数，更加灵活地模拟真实用户的访问。JMeter拥有绘图接口（带有内置的图形化处理的功能），还可以对测试进行记录，然后离线重演测试结果。
单组件式测试工具
mysqlslap
mysqlslap可以模拟服务器的负载，并输出计时信息。测试时可以执行并发连接数，并指定SQL语句（可以在命令行上执行，也可以把SQL语句写入到参数文件中）。如果没有指定SQL语句，mysqlslap会自动生成查询schema的SELECT语句。
MySQL Benchmark Suite (sql-bench)
在MySQL的发行包中也提供了一款自己的基准测试套件，可以用于在不同数据库服务器上进行比较测试。它是单线程的，主要用于测试服务器执行查询的速度。结果会显示哪种类型的操作在服务器上执行得更快。
sysbench
一款多线程系统压测工具。它可以根据影响数据库服务器性能的各种因素来评估系统的性能。例如，可以用来测试文件I/O、操作系统调度器、内存分配和传输速度、POSIX线程，以及数据库服务器等。 sysbench支持Lua脚本语言，Lua对于各种测试场景的设置可以非常灵活。sysbench是全能测试工具，支持MySQL、操作系统和硬件的硬件测试。
参考
高性能MySQL
