MySQL会解析查询，并创建内部数据结构（解析树）,然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain） 优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供-一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行
优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等
对于SELECT语句，在解析查询之前，服务器会先检查查询缓存(Query Cache),如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集
MySQL在两个层面实现并发控制：服务器层与存储引擎层
在处理并发读或写时，可以通过实现一个由两种锁组成的系统来解决问题。这两种锁通常被称为共享锁和排他锁，或者称为读锁和写锁。读锁是共享的，或者说是相互不阻塞的，多个客户可以在同时读取同一数据。而写锁是排他的，同一时刻只能有一个用户能够写入，并防止其他用户读取正在写入的数据
锁粒度是指加锁的对象的大小。显然，锁的粒度越小，并发控制效率越高。锁的各种操作，包括获得锁、检查锁和释放锁等，都会增加系统开销。因此，如果系统花费大量时间来管理锁，而不是用来获取数据，就会影响系统性能
有两种常见的缩策略，表锁和行级锁。表锁开销较小，但是并发控制不好。行级锁可以很好地实现并发控制，但是开销比较大
所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。大多数商业数据库系统没有提供更多的选择,一般都是在表上施加行级锁(row-level lock)，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好的性能。而MySQL则提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度
事务将几个操作作为一个整体，要么全部执行，要么全部放弃。事务的四大特性ACID：
原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作一致性（consistency）：数据库总是从一个一致性的状态转换到另外一个一致性的状态，比如有一个事务包括四步，从零加一，从一加一，从二加一，从三加一，那么根据一致原则，最后的结果一定是0或者4，从一个状态转换到另一个状态隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低
READ UNCOMMITTED (未提交读)：在READ UNCOMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)READ COMITTED (提交读)：一个事务开始时，只能看见已经提交的修改，并且所做的修改对其他事务不可见。这种存在的一个问题叫不可重复读，就是指事务A读取某条记录之后，事务B对其进行了修改，这时当A再次读取该数据的时候就会发现与之前读取的结果不一样REPEATABLE READ (可重复读)：可重复读解决了，数据不可重复读的问题，但仍然不能解决幻读，所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。这是mysql的默认级别，mysql还可以通过MVCC+间隙锁解决幻读问题SERIALIZABLE (可串行化)：事务按照串行的方式执行，并在每行数据上加锁，可能产生大量的超时和锁争用问题四种隔离级别与可避免操作
Read uncommitted【级别最低，什么都避免不了】Read committed【可避免脏读】Repeatable read【可避免脏读，不可重复读】Serializable【可避免脏读，不可重复读，虚读】脏读、不可重复读与幻读(虚读)
脏读：一个事务读取到另外一个事务未提交的数据不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改幻读(虚读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象
存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘，称为预写式日志（Write-Ahead Logging），如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。MySQL提供了两种事务型的存储引擎: InnoDB和NDB Cluster，也有一些第三方存储引擎
MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制(MVCC)。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然大多引擎实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。典型的有乐观(optimistic) 并发控制和悲观(pessimistic) 并发控制
MVCC的实现，是通过保存数据在某个时间点的快照来实现的，且只在REPEATABLE READ 和READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCONMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁
在文件系统中，MySQL将每个数据库( 也可以称之为schema)保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的frm文件保存表的定义。可以使用SHOW TABLE STATUS LIKE ‘tablename’ \G 显示表的相关信息
Name：表名Engine：表使用的引擎Version：表版本信息Row_format：行的格式，一般包含可变长度的字段，如VARCHAR或BLOBRows：表中的行数Avg_row_ length：平均每行的字数Data_ length：表数据的大小Max_ data_ length：表的最大容量，该值和存储引擎有关Index_length：索引的大小Data_free：对于MyISAM表，表示已分配但目前没有使用的空间Auto_ increment：下一个AUTO_ INCREMENT 的值,Create_time：表的创建时间Update_ time： 表的最后修改时间Check_ time：使用CKECK TABLE命令或者myisamchk工具最后一次检查表的时间Col lation：表的默认字符集和字符列排序规则Checksum:：如果启用，保存的是整个表的实时校验和Create_options：创建表时指定的其他选项Comment ：创建表时的注释InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎
InnoDB采用MVCC来支持高并发，井且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ (可重复读),并且通过 间隙锁(next-key locking) 策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入
InnoDB内部做了很多优化，包括从磁盘读取数据时采的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够加速插人操作的插人缓冲区(insert buffer)等。后面将更详细地讨论这些内容
在MySQL 5.1 及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS) 等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。所以MySQL 5.1以后版本的MySQL已经默认不用MyISAM引擎了，相对于InnoDB那一点点的并发量的增加(需要一些条件)，这些缺点让我们不在默认引擎为MyISAM，百分之九十以上情况我们需要InnoDB，MyISAM所拥有的优点大多已能在InnoDB找到代替方案，其它引擎也一样，所以，如果不是非常特殊的要求，我们大多情况使用InnoDB引擎，MyISAM已有的特性
加锁与并发：MyISAM对整张表加锁，而不是针对行修复：对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丟失，而且修复操作是非常慢的索引：支持索引，且支持全文分词索引MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好，比如，数据只读，表比较小，可以忍受修复失败，的场景下。不要轻易相信 “MyISAM比InnoDB快” 之类的话，在很多场景下，这个结论是不对的，甚至InnoDB的速度会比MyISAM望尘莫及，比如使用到聚簇索引时
MySQL还有一些有特殊用途的存储引擎。在新版本中，有些可能因为一些原因已经不再支持，就不一一说明了。那么，引擎这么多，我们应该怎么选择呢，归纳为一句话：“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。" 轻易切换引擎，转则让事情复杂难搞，重则翻车跑路
基准测试(benchmark) 也译做标准检查程序，是MySQL新手和专家都需要掌握的一项基本技能。简单地说，基准测试是针对系统设计的一种压力测试。通常的目标是为了掌握系统的行为。对调优而言，准确的测试相当重要，只有测试正确才知道问题出在哪里，才能加以改进
基准测试有两种主要的策略：一是针对整个系统的整体测试，另外是单独测试MySQL。这两种策略也被称为集成式(ull-stack) 以及 单组件式(single-component) 基准测试。针对整个系统做集成式测试，而不是单独测试MySQL的原因主要有以下几点:
用户关注的是整体性能MySQL并非总是性能瓶颈做整体测试，才能发现各部分之间的缓存带来的问题设计测试条件时，应尽量使测试过程与真实应用的场景接近，基准测试方法的一常见错误
使用真实数据的子集而不是全集使用错误的数据分布在多用户场景中，只做单用户的测试在单服务器上测试分布式应用与真实用户行为不匹配反复执行同一个查询，导致全部或部分缓存没有检查错误忽略了系统预热(warm up)的过程使用默认的服务器配置测试时间太短收集的测试信息不够全性能瓶颈可能的影响因素
外部资源应用需要处理大量的数据在循环中执行昂贵的操作使用了低效的算法间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。有些幻影问题只在没有注意到的时候才发生，而且无法确认如何重现，诊断这样的问题往往要花费很多时间，此时应注意：
尽量不要用试错的方式来解决问题，如果一时无法定位，可能是测量的方式不正确，或者测量的点选择有误，或者使用的工具不合适确定单条查询问题还是服务器问题捕获尽可能多的诊断数据总体来说，解决性能问题的方法，首先是要澄清问题，然后选择合适的技术来解答这些问题。
良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如，反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢
MySQL支持的数据类型非常多，选择正确的數据类型对于获得高性能至关重要。有以下几条建议：
更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符比较操作代价更低尽量避免NULL：很多表都包含可为NULL (空值)的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理在为列选择数据类型时，第一步需要确定合适的大类型:数字、字符串、时间等，第二步就是选择类型的精度
MySQL整数可以指定宽度，如int(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说，int(20)和int(1)是相同的
因为需要额外空间和计算开销，所以尽量只在对小数进行计算的时候才使用decimal。在数据量比较大的时候，可以考虑使用bigint代替decimal，将存储的单位根据小数的位数乘以相应的倍数即可。例如：银行存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题
CHAR适合存储短的短的字符串，或者所有的值都接近同一长度。对于经常变更的数据，CHAR也比VARCHAR好，因为定长的CHAR不容易产生碎片。对于非常短的字符串，CHAR也比VARCHAR更好，因为VARCHAR还需要1或2个额外的字节存储字符串长度。VARCHAR适合变长范围比较大的数据
可以使用枚举替代常用的字符串类型，枚举可以把一些不重复的字符串存储成预定义的集合。枚举字段是按照内部存储的整数而不是字符串进行排序的。枚举不好的地方是当向枚举中增加字段的时候,需要使用ALTER TABLE语句来进行修改。所以，对将来可能会变的字符串，使用枚举不是个好的主意
通常应该尽量使用TIMESTAMP，它比DATETIME空间效率更高，如果需要将时间保存到毫秒级别，可以使用BIGINT
MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型
可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，bit(2)存储两个位。bit列最多存储64个位MySQL将BIT当作字符串类型，而不是数字类型。当检索bit(1)时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的"0"或"1"。然后，在数字上下文场景中检索时，结果将是为字符串转换成的数字
如果存储UUID值，则应该移除“-” 符号；或者更好的做法是，用UNHEX()函数转换。UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式
写得很烂的schema迁移程序，或者自动生成schema的程序，都会导致严重的性能浪费问题。有些程序存储任何东西都会使用很大的VARCHAR列，对象关系映射(ORM) 系统（以及使用它们的“框架”）是另一种常见的性能噩梦，一些ORM系统会存储任意类型的数据到任意类型的后端数据存储中，这通常意味着其没有设计使用更优的数据类型来存储这种设计对开发者很有吸引力，因为这使得他们可以用面向对象的方式工作，不需要考虑数据是怎么存储的。然而，“对开发者隐藏复杂性”的应用通常不能很好地扩展。我们建议在用性能交换开发人员的效率之前仔细考虑，并且总是在真实大小的数据集.上做测试，这样就不会太晚才发现性能问题
范式化的更新操作通常比反范式化要快
当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY 语句
范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在-一个表中本可以属于同一个索引
避免了关联
避免了随机I/O
更有效的使用索引策略
反范式的缺点是容易造成数据冗余，并且如果表的列数过多，使用的不多，会造成查询转换性能浪费
事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧
缓存表表示存储那些可以简单地从schema其他表获取数据的表。
汇总表保存的是使用GROUP BY语句聚合数据的表，使用汇总表的原因是，实时计算和统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么只能在某些索引上才能有效运行
计数器表是用来统计某个操作的次数的表，我们可以在一个表中定义一个名为cnt的字段来表示操作的次数，然后每次执行了操作之后将其加1。但是，加1需要更新操作来完成，每次更新的时候要获取记录的锁，因此并发效率不高。解决这个问题，我们可以再增加一些字段slot作为随机的槽，例如增加100个slot字段，每次执行操作的时候，我们使用随机数选择某个slot，并对其进行+1更新（只用锁住部分数据，因此效率比较高）。最后统计的时候将全部记录加起来即可
为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧：虽然写操作变得更慢了，但更显著地提高了读操作的性能
MySQL的ALTER TABLE 操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是，锁住表，然后用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。一般而言，大部分ALTER TABLE操作将导致MySQL服务中断，这里介绍两种DDL操作技巧，避免这种情况：
一是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换二是通过“影子拷贝”，创建一张新表，然后通过重命名和删表操作交换两张表及里面的数据索引(在MySQL中也叫做“键(key)") 是存储引擎用于快速找到记录的一种数据结构。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级。
数据库的索引类似于书的索引，实际的查找某个值的时候，先按照值进行查找，然后返回包含该值的数据行。索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也很重要，索引对多个列排序的依据是CREATE TABLE时定义索引的顺序，所以MySQL只能高效地使用索引的最左前缀列，这里要注意的是，创建一个包含多个列的索引，和创建多个只包含一列的索引是大不相同的
ORM工具能够产生符合逻辑的、合法的查询，除非只是生成非常基本的查询（例如仅是根据主键查询），否则它很难生成适合索引的查询
索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同
通常人们所说的索引。实际上很多存储引擎使用的是B+Tree。这里B-Tree索引适用于全键值、键值范围或键前缀查找，B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同
类型，以多列索引key(last_name, first_name, dob)为例：
全值匹配：指定查询的人的fitst_name, last_name和dob匹配最左前缀：查找指定了last_name的记录匹配列前缀：匹配某一列的值的开头部分，比如last_name以J开头匹配范围值：匹配某两个索引之间的值，例如Allen和Barrymore之间的人精确匹配某一列并范围匹配另一列：查找last_name为Allen，并且first_name以k开头的只访问索引的查询：B-Tree通常可以支持只访问索引的查询，即查询只需要访问索引，而无需访问数据行因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY 操作( 按顺序查找)。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDERBY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求
B-Tree的一些限制：
如果不是按照从最左列开始查找，则无法使用索引。例如无法查找只指定了first_name或者dob的记录不能跳过索引中的列：不能在查找的时候只指定了last_name和dob，那么dob不会使用索引如果查询的时候有某个列的查询范围，则其右边的所有列都无法使用索引优化查找。比如对last_name使用了like，那么first_name和dob将不会使用索引哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针，因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：
哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B) 上建立哈希索引,如果查询只有数据列A，则无法使用该索引哈希索引只支持等值比较查询，包括=、IN()、<=> (注意<>和<=>是不同的操作)。也不支持任何范围查询，例如WHERE price > 100访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候，存储引擎会把值存到链表中，所以此时必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行如果哈希冲突很多的话，一些索引维护操作的代价也会很高。冲突越多，代价越大因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。
MyISAM表支持空间索引，可以用作地理数据存储
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST 操作，而不是普通的WHERE条件操作
还有很多第三方的存储引擎使用不同类型的数据结构来存储索引，例如分型树索引等
索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，到目前为止可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用。总结下来索引共有三个优点：
索引大大减少了服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机I/O变为顺序I/O索引三星系统
索引将相关的记录放到一起则获得一星如果索引中的数据顺序和查找中的排序一致则获得二星如果索引中的列包含了查询中需要的全部列则获得三星对于小型的表，使用全表扫描更高效；对中到大型的表，使用索引非常有效。对于特大型的表，建立和使用索引的代价会随之增长。这种情况下可以使用分区来查出一组数据，而不是一条一条地匹配
EXPLAIN命令是查询表信息的工具，我们可以根据查询的信息做出正确的优化行为，所以这里有必要加一下它的使用介绍
id：执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置select_type：SELECT类型,可以为以下任何一种 SIMPLE:简单SELECT(不使用UNION或子查询)PRIMARY:最外面的SELECTUNION:UNION中的第二个或后面的SELECT语句DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询UNION RESULT:UNION 的结果SUBQUERY:子查询中的第一个SELECTDEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询DERIVED:导出表的SELECT(FROM子句的子查询) table：访问引用哪个表（引用某个查询，如“user”）type：联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行index_merge:该联接类型表示使用了索引合并优化方法unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)range:只检索给定范围的行,使用一个索引来选择行index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小ALL:对于每个来自于先前的表的行组合,进行完整的表扫描 possible_keys：揭示哪一些索引可能有利于高效的查找key：显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULLkey_len：显示MySQL决定使用的键长度。如果键是NULL,则长度为NULLref：显示使用哪个列或常数与key一起从表中选择行rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数filtered：显示了通过条件过滤出的行数的百分比估计值Extra：该列包含MySQL解决查询的详细信息 Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表如果查找中的列不是独立的，则MySQL不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数
索引很长的字符串会让索引变得大且慢。通常可以只索引开始部分的字符，这样可以节约索引空间，从而提高索引的效率。缺点是会降低索引的选择性。索引的选择性是指不重复的索引值和记录总数的比，显然越大越好。所以，我们需要选择足够长的前缀来保证选择性，同时又不能太长以降低索引空间
我们可以使用语句
select count(distinct left(col_name, 3)) / count(*) from tbl_name;来统计使用3个字符的前缀选择性，同理可以计算出4个，5个等的情况。最后，选择一个合理的前缀长度即可。选择了长度之后可以像下面这样设置指定长度的索引：
alter table add key(col_nane(4));很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“ 索引合并”(index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的
但在MySQL 5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合(union) ，AND条件的相交(intersection)，组合前两种情况的联合及相交。
索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕:
当出现服务器对多个索引做相交操作时(通常有多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。当服务器需要对多个索引做联合操作时(通常有多个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候更重要的是，优化器不会把这些计算到“查询成本”(cost) 中，优化器只关心随机页面读取。这会使得查询的成本被“ 低估”,导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL4.1或者更早的时代一样，将查询改写成UNION的方式往往更好如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_ switch 来关闭索引合并功能。也可以使用IGNORE INDEX 提示让优化器忽略掉某些索引
如果要对多个列建立一个索引，除了上面的问题之外，还应该考虑所建的索引中列的顺序。比如，对col1, col2两列数据建立索引，那么我们的顺序应被指定为(col1, col2)还是(col2, col1)呢。我们要先知道，在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，在没有特殊要求的情况下，我们可以使用选择性来解决这个问题，我们可以将选择性比较高的列作为索引的第一列，另一列作为第二列，以次类推
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree 索引和数据行
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引
因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。这里我们主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。
下图展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值，图中的索引列即为主键列 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远
聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候(反过来也一样)
聚集的数据有一些重要的优点:
可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快使用覆盖索引扫描的查询可以直接使用页节点中的主键值如果在设计表和查询时能充分利用上面的优点，那就能极大地提升性能。同时，聚簇索引也有一些缺点:
聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE 命令重新组织一下表更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置基于聚簇索引的表在插人新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂(page split)” 的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列二级索引访问需要两次索引查找，而不是一次最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值 这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作:两次B-Tree查找而不是一次。对于InnoDB，自适应哈希索引能够减少这样的重复工作
InnoDB和MyISAM的数据分布对比
聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和意外。来看看InnoDB和MyISAM是如何存储下面这个表的:
CREATE TABLE layout_test ( col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2) );假设该表的主键取值为1 ~ 10 000,按照随机顺序插入并使用OPTIMIZE TABLE 命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2的值是从1 ~ 100 之间随机赋值，所以有很多重复的值
MyISAM的数据分布
MyISAM 的数据分布非常简单，所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示 在行的旁边显示了行号（可理解为地址），从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行(MyISAM 并不总是使用图中的“行号”，而是根据定长还是变长的行使用不同策略)
这种分布方式很容易创建索引。下面显示的一系列图，隐藏了页的物理细节，只显示索引中的“节点”，索引中的每个叶子节点包含“行号”。下图显示了表的主键
这里忽略了一些细节，例如前一个B-Tree节点有多少个内部节点，不过这并不影响对非聚簇存储引擎的基本数据分布的理解
那col2列上的索引又会如何呢?有什么特殊的吗?回答是没什么区别，下图显示了col2列上的索引 事实上，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引
InnoDB的数据分布
因为InnoDB支持聚簇索引，所以使用非常不同的方式存储同样的数据。InnoDB如下图所示的方式存储数据 第一眼看上去，感觉该图和前面的MyISAM主键分布图没有什么不同，但再仔细看细节，会注意到该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储
聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列(在这个例子中是col2)。如果主键是一个列前缀索引，InnoDB 也会包含完整的主键列和剩下的其他列
还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB 二级索引的叶子节点中存储的不是“行指针”，而是主键值,并以此作为指向行的“指针” 这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB 在移动行时无须更新二级索引中的这个“指针”
下图显示了示例表的col2索引。每一个叶子节点都包含了索引列(这里是col2)，紧接着是主键值(col1)。并展示了B-Tree的叶子节点结构，但我们故意省略了非叶子节点这样的细节。InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针(下一级节点可以是非叶子节点，也可以是叶子节点)。这对聚簇索引和二级索引都适用 下图是描述InnoDB和MyISAM如何存放表的抽象图。从图中可以很容易看出InnoDB和MyISAM保存数据和索引的区别 在InnoDB表中按主键顺序插入行 如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键(surrogate key)作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_ INCREMENT 自增列。这样可以保证数据行是按顺序写人，对于根据主键做关联操作的性能也会更好 最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于1/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕:它使得聚簇索引的插人变得完全随机，这是最坏的情况，使得数据没有任何聚集特性，下面总结了一些缺点：
写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插人之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O因为写人是乱序的，InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE 来重建表并优化页的填充。不难看出，使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行
如果一个索引包含了所有需要查询的字段的值，就称之为覆盖索引。覆盖索引就是从索引中直接获取查询结果，要使用覆盖索引需要注意select查询列中包含在索引列中，此时不用第二次查询表，所以速度很快；where条件包含索引列或者复合索引的前导列；查询结果的字段长度尽可能少
使用延迟关联解决索引无法覆盖问题：下面的解决方法对效率的提升不是绝对的
SELECT * FROM products WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%'上面的SQL中要查询全部的列，而我们没有覆盖全部列的索引，因此没有覆盖索引。另外，like操作无法使用索引，因为like操作只有在匹配左前缀时才能使用索引
我们可以像下面这样解决问题：
SELECT * FROM products JOIN (SELECT prod_id FROM products WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%') AS t1 ON (t1.prod_id = products.prod_id)这里，需要先建立(actor, title, prod_id)索引。我们先在子查询中找到匹配的prod_id，然后跟外层中数据进行匹配来获取所有列值。当符合where条件的数据数量远小于actor过滤出的数据数量的时候，它的效率尤其高。因为，根据子查询的where过滤出数据之后才与外层查询关联，而后者使用actor读取出数据之后，再用title进行关联。前者需要读取的数据量更少
生成有序结果的两种方式：排序，按索引顺序扫描。当explain出的type为index时，说明使用索引扫描来进行排序。MySQL可以使用一个索引既满足排序，又满足查找。只有当索引的列顺序和ORDER BY子句顺序一致，且列的排序方向都一样时，才能用索引对结果做排序
MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。但重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现后也应删除。常见的错误有：
使用主键和唯一约束时与已有的所冲突，因为主键和唯一约束是通过索引来实现的，如果再定义索引就会冗余，例如：无意间给主键也加个索引若创建了索引(A,B)再创建索引(A)则冗余，而索引(B,A)和(B)不是，因为(B)不是最左前缀，故应该尽量扩展已有的索引而不是创建新索引InnoDB只有在访问行的时候才会对其加锁，而索引能够减少访问行的次数，所以索引能减少锁的数量
即使用正确的类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片
CHECK TABLE检查表是否损坏，ALTER TABLE innodb_tb1 ENGINE=INNODB；修复表records_in_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录，对于innodb不精确info()返回各种类型的数据，包括索引的基数可以使用SHOW INDEX FROM命令来查看索引的基数B-Tree索引可能会碎片化，这会降低查询的效率对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实际上是对一组底层表的句柄对象（Handler Object）的封装
适用场景：
表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据分区表的数据更容易维护分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备可以使用分区表来避免某些特殊的瓶颈如果需要，还可以备份和恢复独立的分区使用限制：
一个表最多只能有1024个分区在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来分区表中无法使用外键约束使用分区表
在数据量超大的时候，B-Tree索引就无法起作用了，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，将产生大量随机I/O
保证大数据量的可扩展性两个策略：
命题扫描数据，不要任何索引索引数据，并分离热点分区策略的问题：
NULL值会使分区过滤无效分区列和索引列不匹配选择分区的成本可能很高打开并锁住所有底层表的成本可能很高维护分区的成本可能很高所有分区都必须使用相同的存储引擎分区函数中可以使用的函数和表达式也有一些限制某些存储引擎不支持分区对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作对于MyISAM表，使用分区表时需要打开更多多的文件描述符查询优化
很重要的一点是要在WHERE条件中带入分区列
只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式是分区函数也不行
MySQL允许通过触发器、存储过程、函数的形式来存储代码，从5.1开始还可以在定时任务中存放代码，这个定时任务也被称为“事件”。存储过程和存储函数都被统称为“存储程序”
存储代码的优点：
它在服务器内部执行，离数据最近，另外在服务器上执行还可以节省带宽和网络延迟这是一种代码重用，可以方便地统一业务规则，保证某些行为总是一致，所以也可以为应用提供一定的安全性它可以简化代码的维护和版本更新可以帮助提升安全，比如提供更细粒度的权限控制服务器端可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗因为是在服务器端部署的，所以备份、维护都可以在服务器端完成可以在应用开发和数据库开发人员之间更好地分工存储代码的缺点：
MySQL本身没有提供好用的开发和调试工具较之应用程序的代码，存储代码效率要稍微差些存储代码可能会给应用程序代码的部署带来额外的复杂性因为存储程序都部署在服务器内，所以可能有安全隐患存储过程会给数据库服务器增加额外的压力，而数据库服务器的扩展性相比应用服务器要差很多MySQL并没有什么选项可以控制存储程序的资源消耗，所以在存储过程中的一个小错误，可能直接把服务器拖死存储代码在MySQL中的实现也有很多限制——执行计划缓存是连接级别的，游标的物化和临时表相同，异常处理也非常困难调试MySQL的存储过程是一件很困难的事情它和基于语句的二进投影日志复制合作得并不好基于以上原因阿里手册里都强制禁止使用存储过程方式操作SQL
存储过程和函数的限制：
优化器无法使用关键字DETERMINISTIC来优化单个查询中多次调用存储函数的情况优化器无法评估存储函数的执行成本每个连接都有独立的存储过程的执行计划缓存存储程序和复制是一组诡异组合触发器：可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作，可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发，可以使用触发器实现一些强制限制，或者某些业务逻辑，否则，就需要在应用程序中实现这些逻辑
触发器的注意和限制：
对每一个表的每一个事件，最多只能定义一个触发器只支持“基于行的触发”，也就是说，触发器是针对一条记录的，而不是针对整个SQL语句的，如果变更的数据集非常大的话，效率会很低触发器可以掩盖服务器背后的工作触发器可以掩盖服务器背后的工作，一个简单的SQL语句背后可能包含了很多看不见的工作触发器的问题也很难排查，如果某个性能问题和触发器相关，会很难分析和定位触发器可能导致死锁和锁等待触发器并不能一定保证更新的原子性触发器的用处：
实现一些约束、系统维护任务，以及更新反范式化数据的时候
记录数据变更日志
事件：类似于Linux的定时任务，指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码
当创建一个绑定变量SQL时，客户端向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄
可以更高效地执行大量的重复语句：
在服务器端只需要解析一次SQL语句在服务器端某些优化项的工作只需要执行一次，因为它会缓存一部分的执行计划以二进制的方式只发送参数和句柄，比起每次都发送ASC2码文本效率更高仅仅是参数——而不是整个查询语句——需要发送到服务器端，所以网络开销会更小MySQL在存储参数的时候，直接将其存放到缓存中，不再需要在内存中多次复制绑定变量相对也更安全。无须在应用程序中处理转义，一则更简单了，二则也大大减少了SQL注入和攻击的风险
最主要的用途就是在存储过程中使用，构建并执行“动态”的SQL语句
绑定变量的限制：
绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄
在5.1版本之前，绑定变量的SQL是不能使用查询缓存的
并不是所有的时候使用绑定变量都能获得更好的性能
如果总是忘记释放绑定变量资源，则在服务器端很容易发生资源“泄漏”
有些操作，比如BEGIN，无法在绑定变量中完成
用户自定义函数
用户自定义函数（UDF）必须事先编译好并动态链接到服务器上，这种平台相关性使得UDF在很多方面都很强大，但一个错误也很可能让服务器直接崩溃，甚至扰乱服务器的内存或者数据插件可以在MySQL中新增启动选项和状态值，还可以新增INFORMATION_SCHEMA表，或者在MySQL的后台执行任务等等
在5.1后支持的插件接口：
存储过程插件
后台插件，可以让程序在MySQL中运行，可以实现自己的网络监听、执行自己的定期任务
INFORMATION_SCHEMA插件，提供一个新的内存INFORMATION_SCHEMA表
全文解析插件，提供一种处理文本的功能，可以根据自己的需求来对一个文档进行分词
审计插件，在查询执行的过程中的某些固定点被调用，可以记录MySQL的事件日志
认证插件，既可可以在MySQL客户端也可在它的服务器端，可以使用这类插件来扩展MySQL的认证功能
MySQL查询缓存保存查询返回的完整结果，当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段
MySQL判断缓存命中的方法很简单：缓存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了如下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息
当判断缓存是否命中时，MySQL不会解析、“正规化”或者参数化查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。任何字符上的不同，例如空格、注释——都会导致缓存的不命中
当查询语句中有一些不确定的数据时，则不会被缓存，例如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存，只要包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，或者任何包含列级别权限的表，都不会被缓存
打开查询缓存对读和写操作都会带来额外的消耗：
读查询在开始之前必须先检查是否命中缓存
如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗
当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效，如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大系统消耗
对于需要消耗大量资源的查询通常都是非常适合缓存的
缓存未命中：
查询语句无法被缓存MySQL从未处理这个查询查询缓存的内存用完了查询缓存还没有完成预热查询语句之前从未执行过缓存失效操作太多了缓存参数配置：
query_cache_type，是否打开查询缓存query_cache_size，查询缓存使用的总内存空间query_cache_min_res_unit，在查询缓存中分配内存块时的最小单位，可以帮助减少由碎片导致的内存空间浪费query_cache_limit，MySQL能够缓存的最大查询结果query_cache_wlock_invalidate，如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果InnoDB和查询缓存
事务是否可以访问查询缓存取决于当前事务ID，以及对应的数据表上是否有锁如果表上有任何的锁，那么对这个表的任何查询语句都是无法被缓存的通用查询缓存优化：
用多个小表代替一个大表对查询缓存有好处批量写入时只需要做一次缓存失效，所以相比单条写入效率更好因为缓存空间太大，在过期操作的时候可能会导致服务器僵死，控制缓存空间的大小无法在数据库或者表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存对于写密集型的应用来说，直接禁用查询缓存可能会提高系统的性能因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用也会有好处配置MySQL正确地使用内存量对高性能是至关重要的，内存消耗分为两类：可以控制的内存和不可以控制的内存
配置内存：
确定可以使用的内存上限
确定每个连接MySQL需要使用多少内存
确定操作系统需要多少内存才够用
把剩下的内存全部给MySQL的缓存
MySQL保持一个连接（线程）只需要少量的内存，它还需要一个基本量的内存来执行任何给定查询，需要为高峰时期执行的大量查询预留好足够的内存，否则，查询执行可能因为缺乏内存而导致执行效率不佳或执行失败
跟查询一样，操作系统也需要保留足够的内存给它工作，如果没有虚拟内存正在交换（Paging）到磁盘，就是表明操作系统内存足够的最佳迹象
如果服务器只运行MySQL，所有不需要为操作系统以及查询处理保留的内存都可以用作MySQL缓存
大部分情况下最重要的缓存：
InnoDB缓冲池InnoDB日志文件和MyISAM数据的操作系统缓存MyISAM键缓存查询缓存无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存InnoDB缓冲池并不仅仅缓存索引：它还会缓存行的数据、自适应哈希索引、插入缓冲（Insert Buffer）、锁，以及其他内部数据结构，还使用缓冲池来帮助延迟写入，InnoDB严重依赖缓冲池
如果事先知道什么时候需要关闭InnoDB，可以在运行时修改innodb_max_dirty_pages_pct变量，将值改小，等待刷新纯种清理缓冲池，然后在脏页数量较少时关闭，可以监控the Innodb_buffer_pool_pages_dirty状态变量或者使用innotop来监控SHOW INNODB STATUS来观察脏页的刷新量
MyISAM的键缓存也被称为键缓冲，默认只有一个键缓存，但也可以创建多个，MyISAM自身只缓存索引，不缓存数据，最重要的配置项是key_buffer_size，不要超过索引的总大小，或者不超过操作系统缓存保留总内存的25%-50%，以更小的为准
了解MyISAM索引实际上占用多少磁盘空间，查询INFORMATION_SCHEMA表的INDEX_LENGTH字段，把它们的值相加，就可以得到索引存储占用空间
块大小也是很重要的（特别是写密集型负载），因为它影响了MyISAM、操作系统缓存，以及文件系统之间的交互，如果缓存块太小，可能会踫到写时读取
线程缓存保存那些当前没有与连接关联但是准备为后面新的连接服务的线程，当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接，当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存，如果没有空间的话，MySQL会销毁这个线程
thread_cache_size变量指定了MySQL可以保持在缓存中的线程数，一般不需要配置这个值，除非服务器会有很多连接请求
表缓存（Table Cache）和线程缓存的概念是相似的，但存储的对象代表的是表，缓存对象包含相关表.frm文件的解析结果，加上其他数据。表缓存可以重用资源，让服务器避免修改MyISAM文件头来标记表“正在使用中”，对InnoDB的重要性要小得多
表缓存的缺点是，当服务器有很多MyISAM表时，可能会导致关机时间较长，因为关机前索引块必须完成刷新，表都必须标记为不再打开
InnoDB数据字典（Data Dictionary），InnoDB自己的表缓存，当InnoDB打开一张表，就增加了一个对应的对象到数据字典
InnoDB没有将统计信息持久化，而是在每次打开表时重新计算，5.6以后可以打开innodb_use_sys_stats_table选项来持久化存储统计信息到磁盘
可以关闭InnoDB的innodb_stats_on_metadata选项来避免耗时的表统计信息刷新
如果可以，最好把innodb_open_files的值设置得足够大以使服务器可以保持所有的.ibd文件同时打开
InnoDB并发配置
InnoDB有自己的“线程调度器”控制线程怎么进入内核访问数据，以及它们在内核中一次可以做哪些事，最基本的限制并发的方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核
并发值 = CPU数量 * 磁盘数量 * 2，在实践中使用更小的值会更好一点
MyISAM并发配置
尽管MyISAM是表级锁，它依然可以一边读取，一边并发追加新行，这种情况下只能读取到查询开始时的所有数据，新插入的数据是不可见的，这样可以避免不一致读
通过设置concurrent_insert这个变量，可以配置MyISAM打开并发插入
让INSERT、REPLACE、DELETE、UPDATE语句的优先级比SELECT语句更低，设置low_priority_updates选项就可以
高质量闪存设备具备：
相比硬盘有更好的随机读写性能
相比硬盘有更好的顺序读写性能
相比硬盘能更好地支持并发
提升随机I/O和并发性
闪存的最重要特征是可以迅速完成多次小单位读取，但是写入更有挑战性。闪存不能在没有做擦除操作前改写一个单元（Cell），并且一次必须擦除一个大块。擦除周期是缓慢的，并且最终会磨损整个块
垃圾收集对理解闪存很重要。为了保持一些块是干净的并且可以被写入，设备需要回收脏块。这需要设备上有一些空闲空间
许多设备被填满后会开始变慢，速度下降是由于没有空闲块时必须等待擦写完成所造成的
固态存储最适合使用在任何有着大量随机I/O工作负载的场景下，随机I/O通常是由于数据大于服务器的内存导致的，闪存设备可能大大缓解这种问题
单线程工作负载也是另一个闪存的潜在应用场景
闪存也可以为服务器整合提供巨大的帮助
Flashcache，磁盘和内存技术的结合，适合以读为主的I/O密集型负载，并且工作集太大，用内存优化并不经济的情况
优化固态存储上的MySQL
增加InnoDB的I/O容量让InnoDB日志文件更大把一些文件从闪存转移到RAID禁用预读配置InnoDB刷新算法禁用双写缓冲的可能限制插入缓冲大小，插入缓冲设计来用于减少当更新行时不在内存中的非唯一索引引起的随机I/OInnoDB的页大小优化InnoDB页面校验（Checksum）的替代算法vmstat
vmstat 5，每隔5秒刷新一次procs，r列显示多少进程正在等待CPU，b列显示多少进程正在不可中断地休眠memory，swpd列显示多少块被换出到了磁盘，剩下的三个列显示了多少块是空闲的、多少块正在被用作缓冲，以及多少正在被用作操作系统的缓存swap，显示页面交换活动io，显示有多少块从块设备读取（ bi）和写出（bo）system，显示了每秒中断（in）和上下文切换（cs）的数量cpu，显示所有的CPU时间花费在各类操作的百分比iostat
iostats -dx 5，每5秒刷新
rrqm/s和wrqm/s，每秒合并的读和写请求，意味着操作系统从队列中拿出多个逻辑请求合并为一个请求到实际磁盘
r/s和w/s，每秒发送到设备的读和写请求
rsec/s和wsec/s，每秒读和写的扇区数
avgrq-sz，请求的扇区数
avgqu-sz，在设备队列中等待的请求数
await，磁盘排除上花费的毫秒数
svctm，服务请求花费的毫秒数，不包括排除时间
%util，至少有一个活跃请求所占时间的百分比
CPU密集型的机器，vmstat输出通常在us列会有一个很高的值，也可能在sy列有很高的值
I/O密集型工作负载下，vmstat会显示很多处理器在非中断休眠（b列）状态，并且wa这一列的值很高
发生内存交换的机器可能在swpd列有一个很高的值
MySQL支持两种复制方式：基于行的复制和基于语句的复制，都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制
复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的
通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过写复制来扩展写操作
复制解决的问题：
数据分布
负载均衡
备份
高可用性和故障切换
MySQL升级测试
复制如何工作
在主库上把数据更新记录到二进制日志（Binary Log）中
备库将主库上的日志复制到自己的中继日志（Relay Log）中
备库读取中继日志中的事件，将其重放到备库数据之上
基于语句的复制
5.0之前只支持基于语句的复制（也称为逻辑复制），主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍好处是实现相当简单，日志更加紧凑，不会占用太多带宽问题是基于语句的方式可能并不如其看起来那么便利，还存在一些无法被正确复制的SQL，更新必须是串行的这需要更多的锁基于行的复制
5.1开始支持，会将实际数据记录在二进制日志中，跟其他数据库的实现比较想像
好处是可以正确地复制每一行，一些语句可以被更加有效地复制
如果使用全表更新，则开销会很大，因为每一行的数据都会被记录到二进制日志中，这使得二进制日志事件非常庞大，并且会给主库上记录日志和复制增加额外的负载，更慢的日志记录则会降低并发度
基于行或基于语句：哪种更优
基于语句的复制模式的优点：当主备的模式不同时，逻辑复制能够在多种情况下工作；基于语句的方式执行复制的过程基本上就是执行SQL语句基于语句的复制模式的缺点：很多情况下通过基于语句的模式无法正确复制，如果正在使用触发器或者存储过程，就不要使用基于语句的复制模式，除非能够清楚地确定不会踫到复制的问题基于行的复制模式的优点：几乎没有基于行的复制模式无法处理的场景；可能减少锁的使用，并不要求这种强串行化是可重复的；会记录数据变更；占用更少的CPU；能够帮助更快地找到并解决数据不致的情况基于行的复制模式的缺点：无法判断执行了哪些SQL；无法知道服务器在做什么；在某些情况下，例如找不到要修改的行时，基于行的复制可能会导致复制停止复制文件
mysql-bin.index，二进制日志文件
mysql-relay-bin-index，中继日志的索引文件
master.info，保存备库连接到主库所需要的信息
relay-log.info，包含了当前备库复制的二进制日志和中继日志坐标
5.发送复制事件到其他备库：log_slave_updates，可以让备库变成其他服务器的主库
6.复制过滤选项
在主库上过滤记录到二进制日志中的事件在备库上过滤记录到中继日志的事件基本原则：
一个MySQL备库实例只能有一个主库
每个备库都必须有一个唯一的服务器ID
一个主库可以有多个备库
如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库
一主库多备库
主动-主动模式下的主主复制：auto_increment_increment和auto_increment_offset可以让MySQL自动为INSERT语句选择不互相冲突的值
主动-被动模式下的主主复制：其中一台服务器是只读的被动服务器
拥有备库的主主结构：增加了冗余，能够消除站点单点失效的问题
环形复制：每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库
分发主库事实上也是一个备库，提取和提供主库的二进制日志
树或金字塔形：减轻了主库的负担，但中间层出现的任何错误都会影响到多个服务器
定制的复制方案
选择性复制：配置replicate_wild_do_table
分离功能：OLTP、OLAP
数据归档：在备库上保留主库上删除过的数据
将备库用作全文检索
只读备库：read_only选项
模拟多主库复制
创建日志服务器：创建没有数据的日志服务器，更加容易重放并且/或者过滤二进制日志事件
优点：
云是一种将基础设施外包出去无须自己管理的方法
云一般是按照即用即付的方式支付
随着供应商发布新的服务和成本降低，云提供的价值越来越大
云能够帮助你轻松地准备好服务器和其他资源
云代表了对基础设施的另一种思考方式——作为通过API来定义和控制的资源——支持更多的自动化操作
2.缺点：
资源是共享并且不可预测的
无法保证容量和可用性
虚拟的共享资源导致排查故障更加困难
最常见的问题是保持它的进程的存活（alive）时间过长，或者在各种不同的用途下混合使用，而不是分别对不同类型的工作进行优化
如果用一个通用目的的Apache配置直接用于Web服务，最后很可能产生很多重量级的Apache进程
不要使用Apache来做静态内容服务，或者至少和动态服务使用不同的Apache实例
进程存活时间变短策略：
不要让Apache填鸭式地服务客户端
打开gzip压缩
不要为用于长距离连接的Apache配置启用Keep-Alive选项
被动缓存除了存储和返回数据外不做任何事情；主动缓存在访问未命中时做一些额外工作
应用可以缓存部分计算结果，所以应用层缓存可能比更低层次的缓存更有效，可以节省两方面的工作：获取数据以及基于这些数据进行计算，重点是缓存命中率可能更低，并且可能使用较多的内存
应用层缓存：
本地缓存
本地共享内存缓存
分布式内存缓存
磁盘上的缓存
缓存控制策略
TTL（time to live，存活时间）
显式失效，如果不能接受脏数据，那么进程在更新原始数据时需要同时使缓存失效
读时失效，在更改旧数据时，为了避免要同时失效派生出来的脏数据，可以在缓存中保存一些信息，当从缓存中读数据时可以利用这些信息判断数据是否已经失效
可以在后台预先请求一些页面，并将结果存为静态页面，好处：
应用代码没有复杂的命中和未命中处理路径
当未命中的处理路径慢得不可接受时，这种方案可以很好地工作
预生成内容可以避免在缓存未命中时导致的雪崩效应
建议
在生产实践中，对于大数据库来说，物理备份是必需的：逻辑备份太慢并受到资源限制，从逻辑备份中恢复需要很长时间
保留多个备份集
定期从逻辑备份（或者物理备份）中抽取数据进行恢复测试
保存二进制日志以用于基于故障时间点的恢复
完全不借助备份工具本身来监控备份和备份的过程
通过演练整个恢复过程来测试备份和恢复
对安全性要仔细考虑
如果可能，关闭MySQL做备份是最简单最安全的，需要考虑：锁时间、备份时间、备份负载、恢复时间
逻辑备份优点：
可以用编辑器或像grep和sed之类的命令查看和操作的普通文件
恢复非常简单
可能通过网络来备份和恢复
可以在类似Amazon RDS这样不能访问底层文件系统的系统中使用
非常灵活
与存储引擎无关
有助于避免数据损坏
逻辑备份的缺点：
必须由数据库服务器完成生成逻辑备份的工作
逻辑备份在某些场景下比数据库文件本身更大
无法保证导出后再还原出来的一定是同样的数据
从逻辑备份中还原需要MySQL加载和解释语句
物理备份优点：
基于文件的备份，只需要将需要的文件复制到其他地方即可
恢复简单
InnoDB和MyISAM的物理备份非常容易跨平台
物理备份缺点：
InnoDB的原始文件通常比相应的逻辑备份要大得多
物理备份不总是可以跨平台
除非经过测试，不要假定备份是正常的
建议混合使用物理和逻辑两种方式来做备份
MySQL备份需要考虑的几点：
非显著数据代码复制配置服务器配置选定的操作系统差异备份是对自上次全备份后所有改变的部分做备份，而增量备份则是自从任意类型的上次备份后所有修改做的备份
差异、增量备份的建议：
使用Percona XtraBackup和MySQL Enterprise Backup中的增量备份特性备份二进制日志，每次备份后FLUSH LOGS不要备份没有改变的表不要备份没有改变的行某些数据根本不需要备份备份所有的数据，然后发送到一个有去重特性的目的地数据一致性：当备份时，应该考虑是否需要数据在指定时间点一致
文件一致性：每个文件的内部一致性
从备库中备份最大的好处是可以不干扰主库，故意将一个备库延时一段时间对于某些灾难场景非常有用
生成逻辑备份
SQL导出：mysqldump方式
符号分隔文件备份：使用SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份
文件系统快照
支持快照的文件系统和设备包括FreeBSD的文件系统、ZFS文件系统、GNU/Linux的逻辑卷管理（LVM），以及许多的SAN系统和文件存储解决方案恢复步骤：
停止MySQL服务器
记录服务器的配置和文件权限
将数据从备份中移到MySQL数据目录
改变配置
改变文件权限
以限制访问模式重启服务器，等待完成启动
载入逻辑备份文件
检查和重放二进制日志
检测已经还原的数据
以完全权限重启服务器
备份和恢复工具
MySQL Enterprise BackupPercona XtraBackupmylvmbackupZmanda Recovery Managermydunpermysqldump开源的监控工具
Nagios
Zabbix
Zenoss
OpenNMS
Groundwork Open Source
MRTG
Cacti
Ganglia
Munin
商业监控系统
MySQL Enterprise Monitor
MONyog
New Relic
Circonus
Monitis
Splunk
Pingdom
Innotop的命令行监控
