「高性能MySQL」读书笔记

第一章 MySQL架构与历史

1.1 MySQL逻辑架构

• 第一层负责连接处理、授权认证、安全等等
• 第二层是MySQL核心服务，包括查询解析、分析、优化、缓存以及所有的内置函数(例如，日期、时间、数学和加密函数)，所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。
• 第三层包含存储引擎，存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事物”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。

当客户端(应用)连接到MySQL服务器时,服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。如果使用了安全套接字(SSL)的方式连接，还可以使用X.509证书认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限(例如，是否允许客户端对world数据库的Country表执行SELECT语句)。

1.1.2 优化与执行

MySQL会解析查询，并创建内部数据结构(解析树)，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等，用户可以通过特殊的关键字提示(hint)优化器，影响它的决策过程。也可以请求优化器解释(explain) 优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置,使应用尽可能高效运行。

优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如，某些存储引擎的某种索引,可能对一些特定的查询有优化。

对于SELECT语句，在解析查询之前，服务器会先检查查询缓存(Query Cache),如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

1.2 并发控制

1.2.1 读写锁

在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁(shared lock)和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。

读锁是共享的，或者说是相互不阻塞的。多个客户端在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的，一个写锁会阻塞其他的写锁和读锁，

1.2.2 锁粒度

• 表锁
  表锁是MySQL中最基本的锁策略，开销最小，它会锁定整张表，一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。

• 行锁
  行级锁可以最大程度地支持并发处理，同时锁开销也最大。

1.3 事物

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执
行成功，要么全部执行失败。

1.3.1 隔离级别

1.3.2 死锁

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

1.3.3 事物日志

1.3.4 MySQL中的事物

MySQL中提供了两种事物型存储引擎：InnoDB和NDB Cluster。

• 自动提交(AUTOCOMMIT)
  MySQL默认采用自动提交模式，也就是说，如果不是显式地开始一个事物，则每个查询都被当作一个事物执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启动或者禁用自动提交模式：
  
  1或者ON代表启动，0或者OFF代表禁用。当AUTOCOMMIT=0时，所有的查询都是在一个事物中，直到显示地执行COMMIT提交或者ROLLBACK回滚，该事物结束，同时又开始了一个新事物。修改AUTOCOMMIT对非事物型的表，比如MyISAM或者内存表，不会有任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，相当于一直处于AUTOCOMMIT启用的模式。

• 隐式和显式锁定
  隐式锁定：
  InnoDB采用的是两阶段锁定协议（two-phase locking protocol）。在事物执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或ROLLBACK的时候才会释放，并且所有的锁是在同一时刻释放。这里的锁定是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。

显式锁定：

 select ... lock in share mode
 select ... for update

1.4 多版本并发控制

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

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。这里存储的并不是实际的时间值，而是系统版本号。每开始一个新的事物，系统版本号会自动递增。事物开始时刻的系统版本号会作为事物的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

• SELECT
  InnoDB会根据以下两个条件检查每行记录: .

  • InnoDB只查找版本早于当前事务版本的数据行(也就是，行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
  • 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到
    的行，在事务开始之前未被删除。

  只有符合.上述两个条件的记录，才能返回作为查询结果。

• INSERT
  InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

• DELETE
  InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

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

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

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

1.5 MySQL的存储引擎 TODO

第二章 MySQL基准测试 TODO

第三章 服务器性能剖析 TODO

第四章 Schema与数据类型优化

4.1 选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。

• 更小的通常更好
  一般情况下，尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPPU周期也更少。
• 简单就好
  简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符串操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。应该使用MySQL内建的类型而不是字符串类型来存储日期和时间；应该用整型存储IP地址。
• 尽量避免NULL
  如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用跟多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可以导致固定大小的索引变成可变大小的索引。

4.1.1 整数类型

有两种类型的数字:整数(whole number)和实数(real number)。如果存储整数，可以使用这几种整数类型: 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(11)，对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，INT(1) 和INT(20)是相同的。

4.1.2 实数类型

实数是带有小数部分的数字，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

4.1.3 字符串类型

• VARCHAR和CHAR类型

  • VARCHAR
    varchar类型用于存储可变字符串，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅使用必要的空间（例如越短的字符串使用越少的空间）。
    varchar需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。
    varchar节省了内存空间，所以对性能也有帮助。但是由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作。
  • CHAR
    char类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储char值时，MySQL会删除所有的末尾空格。char值会根据需要采用空格进行填充以方便比较。
    char适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。对于非常短的列，char也比varchar更好，因为定长的char类型不容易产生碎片，对于非常短的队列，char比varchar在存储空间上也更有效率。
    与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。填充也不一样: MySQL填充BINARY采用的是\0 (零字节)而不是空格，在检索时也不会去掉填充值。

• BLOB和TEXT类型
  BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
  实际上，它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT, SMALLTEXT,TEXT，MEDIUMTEXT， LONGTEXT ;对应的二进制类型是TINYBLOB，SMALL.BLOB， BLOB,MEDIUMBLOB，LONGBLOB。 BLOB 是SMALLBLOB的同义词，TEXT 是SMALLTEXT的同义词。

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

BLOB存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

MySQL在对BLOB和TEXT类型进行排序时只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用ORDER BY SUSTRING(column,length)。

MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

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

枚举值实际存储为整数，而不是字符串。枚举字段是按照内部存储的整数而不是定义的字符串进行排序。

4.1.4 日期和时间类型

• DATETIME
  这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值。

• TIMESTAMP
  TIMESTAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间：只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
  TIMESTAMP显示的值依赖于时区。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置和这个列的值为当前时间。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值（除非在UPDATE语句中明确指定了值）。TMIESTAMP列默认为NOT NULL。通常应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

4.1.5 位数据类型

• BIT
  可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义包含单个位的字段，BIT(2)存储2个位，BIT列的最大长度是64个位。
  MySQL把BIT当作字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是
  一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”。然而，在数字上下文的场景中检索时，结果将是位字符串转换成的数字。如果需要和另外的值比较结果，一定要记得这一点。例如，如果存储一个值b’ 00111001’ (二进制值等于57)到BIT(8)的列并且检索它，得到的内容是字符码为57的字符串。也就是说得到ASCII码为57的字符“9”。 但是在数字上下文场景中，得到的是数字57
• SET
  如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。

4.1.6 选择标识符

为标识列(identifier column)选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较(例如，在关联操作中)，或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。

• 整数类型
  整数通常是标识列最好的选择，因为它们很开并且可以使用AUTO_INCRMENT。
• ENUM和SET类型
  对于标识列来说，EMUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别。
• 字符串类型
  避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。

如果存储UUID值，则应该移除“-”符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。

4.2 MySQL schema设计中的陷阱

• 太多的列
  MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。转换的代价依赖于列的数量。
• 太多的关联
  MySQL限制了每个关联操作最多只能有61张表，但是EAV（实体-属性-值）数据库需要许多自关联，许多EAV数据库最后超过了这个限制。事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。
• 全能的枚举
  防止过度使用枚举（ENUM）。当需要对枚举值进行修改时需要进行alter table操作，
• 变相的枚举
• 非此发明(Not Invent Here)的NULL

4.3 范式和反范式

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

4.3.1 范式的优点和缺点

通常对schema进行范式化设计，特别是写密集的场景，来提升性能。

优点：

• 范式化的更新操作通常比反范式化更快
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

缺点：

• 查询的时候通常需要关联。

4.3.2 反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。单独的表也能使用更有效的索引策略。

4.4 缓存表和汇总表 TODO

第五章 创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构。

5.1 索引基础

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。

5.1.1 索引的类型 （TODO 详细的索引结构）

在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

• B-Tree索引
  B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
  
  B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点(图示并未画出)开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，他们的指针指向的是被索引的数据，而不是其他的节点页（不同引擎的“指针”类型不同）。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

B-Tree索引 适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

• 全值匹配
  全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。
• 匹配最左前缀
  前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第-一列。
• 匹配列前缀
  也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。
• 匹配范围值
  例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另外一列
  前面提到的索引也可用于查找所有姓为Allen,并且名字是字母K开头(比如Kim、Karl等)的人。即第一列last_ name 全匹配，第二列first_ name 范围匹配。
• 只访问索引的查询
  B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。

• 不能跳过索引中的列。

• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

• 哈希索引
  哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
  只有Memory引擎支持哈希索引，也是Memory引擎表的默认索引类型。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制:

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B).上建立哈希索引，如果查询只有数据列A,则无法使用该索引。
• 哈希索引只支持等值比较查询，包括=、IN(). <=> (注意<>和<=>是不同的操作)。也不支持任何范围查询，例如WHERE price > 100。
• 访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低(哈希冲突很多)的列，上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每–行，找到并删除对应行的引用，冲突越多，代价越大。
• 空间数据索引（R-Tree）
• 全文索引

5.2 索引的优点

1. 索引大大减少了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机I/O变为顺序I/O

5.3 高性能的索引策略

5.3.1 独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

select actor_id from actor where actor_id + 1 = 5;

这里的actor_id就无法使用索引。

explain的用法详见：MySQL explain 应用详解

1,SIMPLE,actor,index,idx_actor_last_name,182,200,100,Using where; Using index

5.3.2 前缀索引和索引选择性

有时候需要索引很长的字符列，一个策略时模拟哈希索引，另一个策略时索引开始的部分字符。

索引开始的部分字符，可以大大节约索引空间，从而提高索引效率，但是也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从$\frac{1}{\#T}$到1之间。索引的选择性越高则查询效率越高，因为选择性搞的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

5.3.3 多列索引

一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

5.3.4 选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT等子句的查询需求。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性(整体基数)，也和查询条件的具体值有关，也就是和值的分布有关。

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时,它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。

InnoDB通过主键聚集数据。如果没有定义主键，InnoDB会选择-一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

优点：

• 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

• 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂(pagesplit)”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找。

当主键采用UUID时，因为新行的主键不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点：

• 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
• 因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
• 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。
  在把这些随机值载入到聚簇索引以后，也许需要做-.次OPTIMIZETABLE来重建表并优化页的填充。

5.3.6 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。覆盖索引在查询的时候只需要扫描索引而无须回表，优点如下：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中(这对于 MyISAM尤其正确，因为MyISAM能压缩索引以变得更小)。
• 因为索引是按照列值顺序存储的(至少在单个页内是如此)，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。对于某些存储引擎，例如MyISAM和Percona XtraDB,甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

5.3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。

MySQL可以使用同一个索引既满足排序，又用于查找行。

只有当索引的列顺序和ORDER BY字句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求;否则MySQL都需要执行排序操作，而无法利用索引排序。

有一种情况下ORDERBY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量,就可以“弥补”索引的不足。

5.3.8 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提升性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”， 第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance" 这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

5.3.9 冗余和重复索引

MySQL允许在相同列上创建多个索引，需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。

冗余索引和重复索引不同。如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

注意MySQL的唯一限制和主键限制都是通过索引实现的。

一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致打到了内存瓶颈的时候。

5.3.10 未使用的索引

5.3.11 索引和锁

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了: InnoDB已经锁住了这些行，到适当的时候才释放。在MySQL 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。

InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。

5.4 索引案例学习

5.4.1 支持多种过滤条件

一个基本原则:考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车去设计最完美的索引。

如果想尽可能重用索引而不是建立大量的组合索引，可以使用IN()来避免。但不能滥用IN()，因为每额外增加一个IN()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能。

5.4.2 避免多个范围条件

5.4.3 优化排序

对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。

5.5 维护索引和表

维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

5.5.1 找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。可以尝试运行CHECK TABLE来检查是否发生了表损坏。可以使用REPAIR TABLE命令来修复损坏的表。如果存储引擎不支持REPAIR TABLE命令，也可通过一个不做任何操作的ALTER操作来重建表，例如修改表的存储引擎为当前的引擎。

5.5.2 更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。第一个API是records_ in_ range()， 通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎，该接口返回精确值，例如MyISAM ;但对于另一些存储引擎则是一个估算值，例如InnoDB。

第二个API是info()，该接口返回各种类型的数据，包括索引的基数(每个键值有多少条记录)。

如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息解决这个问题。

InnoDB会在表首次打开，或者执行ANALYZE TABLE, 抑或表的大小发生非常大的变化(大小变化超过十六分之一或者新插入了20亿行都会触发)的时候计算索引的统计信息。

5.5.3 减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。

根据设计，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多倍;对于索引覆盖扫描这一点更加明显。

表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片。

• 行碎片(Row fragmentation)
  这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
• 行间碎片(Intra-row fragmentation)
  行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
• 剩余空间碎片(Free space fragmentation)
  剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。

对于那些不支持OPTIMIZE TABLE的存储引擎，可以通过一个不做任何操作的ALTER TABLE操作来重建表。只需要将表的存储引擎修改为当前的引擎即可。

第六章 查询性能优化

6.1 为什么查询速度会慢

如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。执行包含了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待(互斥等待)等操作，尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作.上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

6.2 慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。

排查步骤：

1. 确认应用程序是否在检索大量超过需要的数据。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

6.2.1 是否向数据库请求了不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。

典型案例：

• 查询不需要的记录
• 多表关联时返回全部列
• 总是取出全部列
• 重复查询相同的数据

6.2.2 MySQL是否在扫描额外的记录

对于MySQL，最简单的衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

没有哪个指标能够完美地衡量查询的开销，但它们大致反映了MySQL在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

• 响应时间
  响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁，等等。
  可以使用“快速上限估计”法来估算查询的响应时间，以此来判断一个查询的响应时间是否合理。

• 扫描的行数和返回的行数
  理想情况下扫描的行数和返回的行数应该是相同的。但实际上这种情况并不多。扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。

• 扫描的行数和访问类型
  MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。

在EXPLAIN语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从多到少。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为:

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描(在 Extra列中出现了Usingindex)来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录(在Extra列中出现Using Where)。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

MySQL不会告诉我们生成结果实际上需要扫描多少行数据，而只会告诉我们生成结果时一共扫描了多少行数据。扫描的行数中的大部分都很可能是被WHERE条件过滤掉的，对最终的结果集并没有贡献。

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：

• 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
• 改变库表结构。例如使用单独的汇总表。
• 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

6.3 重构查询的方式

6.3.1 一个复杂查询还是多个简单查询

设计查询的时候需要考虑是否需要将一个复杂的查询分成多个简单的查询。

6.3.2 切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。例如可以分批多次清理数据。

6.3.3 分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

用分解关联查询的方式重构查询有如下的优势：

• 让缓存的效率更高。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身的效率也可能会有所提升。
• 可以减少冗余记录的查询。
• 相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

6.4 查询执行的基础

6.4.1 MySQL客户端/服务器通信协议

MySQL客户端和服务器之间的通信协议是“半双工”的，在任何时刻，要么是由服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。所以我们无法也无须将一个消息切成小块独立来发送。这也意味着没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。

客户端用一个单独的数据包将查询传给服务器。可以通过max_allowed_packet参数控制数据包的上限。

一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。

多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。

查询状态
对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。

• Sleep
  线程正在等待客户端发送新的请求。
• Query
  线程正在执行查询或者正在将结果发送给客户端。
• Locked
  在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现。
• Analyzing and statistics
  线程正在收集存储引擎的统计信息，并生成查询的执行计划。
• Copying to tmp table [on disk]
  线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY 操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“ondisk"标记，那表示MySQL正在将一个内存临时表放到磁盘上。
• Sorting result
  线程正在对结果集进行排序。
• Sending data
  这表示多种情况:线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

6.4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL 会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析,不用生成执行计划,不会被执行。

6.4.3 查询优化处理

• 语法解析器和预处理
  首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。

预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看是否有歧义。

下一步预处理器会验证权限。

• 查询优化器
  一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。

有很多种原因会导致MySQL优化器选择错误的执行计划：

• 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。
• 执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精准，优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更小。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。
• MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短，但是MySQL只是基于成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。
• MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
• MySQL也并不是任何时候都是基于成本的优化。
• MySQL不会考虑不受其控制的操作的成本。
• 优化器有时候无法去估算所有可能的执行时间。

MySQL优化策略分为两种：

• 静态优化：直接对解析树进行分析，并完成优化。不依赖于特别的数值，在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。
• 动态优化：和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估。

静态优化只需要做一次，动态优化则在每次执行时都需要重新评估，有时候甚至在查询的执行过程中也会重新优化。

MySQL能够处理的优化类型：

• 重新定义关联表的顺序

• 将外连接转化成内连接

• 使用等价变换规则

• 优化COUNT()、MIN()和MAX()

• 预估并转化为常数表达式

• 覆盖索引扫描

• 子查询优化

• 提前终止查询

• 等值传播

• 列表IN()的比较

• 数据和索引的统计信息
  在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按照不同的格式存储统计信息）。

因为服务器层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

• MySQL如何执行关联查询
  MySQL认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联。

当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。

• 执行计划
  MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

• 关联查询优化器
  MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。

关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本，最后返回一个最优的执行计划。

• 排序优化
  尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，这个过程统一称为文件排序(filesort)，即使完全是内存排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并(merge)，最后返回排序结果。

MySQL有两种排序方法：

1. 两次传输排序（旧版本使用）
   读取行指针和需要排序的字段，并对其进行排序，然后再根据排序结果读取所需要的数据行。
2. 单次传输排序（新版本使用）
   先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

6.4.4 查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。查询计划是一个数据结构（指令树）。MySQL只是简单地根据执行计划给出的指令逐步执行。查询中的每一个表由一个handler的实例表示。MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。

6.4.5 返回结果给客户端

如果查询可被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果返回给客户端是一个增量、逐步返回的过程。当生成结果时就可以开始向客户端逐步返回结果集了。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。

6.5 MySQL查询优化器的局限性

6.5.1 关联子查询

MySQL的子查询实现得非常糟糕。最糟糕的一类查询时WHERE条件中包含IN()的子查询语句。IN()查询一般考虑是不是可以用EXISTS()查询代替。

6.5.2 UNION的限制

6.5.3 索引合并优化

在MySQL5.0和更新的版本中，当WHERE子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

6.5.4 等值传递

6.5.5 并行执行

MySQL无法利用多核特性来并行执行查询。

6.5.6 哈希关联

MySQL不支持哈希关联

6.5.7 松散索引扫描

通常，MySQL的索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中很少数的几个，MySQL仍需要扫描这段索引中每一个条目。

6.5.8 最大值和最小值优化

6.5.9 在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。

6.6 查询优化器的提示（hint）

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。

6.7 优化特性类型的查询

6.7.1 优化COUNT()查询

• COUNT()的作用
  COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。

MyISAM只有没有任何WHERE条件的COUNT(*)才非常快。

6.7.2 优化关联查询

6.7.4 优化GROUP BY和DISTINCT

在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。

6.7.5 优化LIMIT分页

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。

select film_id, description
from film
order by title
limit 50,5;

可以改写成如下（title字段上有索引）：

select film_id, description
from film
         inner join (
    select film_id
    from film
    order by title
    limit 50,5
) as lim using (film_id);

6.7.6 优化SQL_CALC_FOUND_ROWS

分页的时候，一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示（hint），这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。

6.7.7 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，一遍优化器可以充分利用这些条件进行优化。除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL， 这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的
数据做唯一性检查。

6.7.8 静态查询分析

6.7.9 使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。