《高性能MySQL-第三版》学习笔记一

第1章 MySQL架构与历史

1.1 MySQL逻辑架构

• 表现层：是应用程序的用户界面（UI）部分，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
• 逻辑层：查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
• 数据层：负责MySQL中数据的存储和提取。
  

1.1.1 连接管理与安全性

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

1.1.2 优化与执行

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

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

1.2 并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。本章的目的是讨论MySQL在两个层面的并发控制：服务器层与存储引擎层。
以Unix系统的email box为例，典型的mbox文件格式是非常简单的。一个mbox邮箱中的所有邮件都串行在一起，彼此首尾相连。这种格式对于读取和分析邮件信息非常友好，同时投递邮件也很容易，只要在文件末尾附加新的邮件内容即可。但如果两个进程在同一时刻对同一个邮箱投递邮件，会发生什么情况？显然，邮箱的数据会被破坏，两封邮件的内容会交叉地附加在邮箱文件的末尾。设计良好的邮箱投递系统会通过锁（lock）来防止数据损坏。如果客户试图投递邮件，而邮箱已经被其他客户锁住，那就必须等待，直到锁释放才能进行投递。这种锁的方案在实际应用环境中虽然工作良好，但并不支持并发处理。因为在任意一个时刻，只有一个进程可以修改邮箱的数据，这在大容量的邮箱系统中是个问题。

1.2.1 读写锁

从邮箱中读取数据没有这样的麻烦，即使同一时刻多个用户并发读取也不会有什么问题。因为读取不会修改数据，所以不会出错。但如果某个客户正在读取邮箱，同时另外一个用户试图删除编号为25的邮件，会产生什么结果？结论是不确定，读的客户可能会报错退出，也可能读取到不一致的邮箱数据。所以，为安全起见，即使是读取邮箱也需要特别注意。
如果把上述的邮箱当成数据库中的一张表，把邮件当成表中的一行记录，就很容易看出，同样的问题依然存在。从很多方面来说，邮箱就是一张简单的数据库表。修改数据库表中的记录，和删除或者修改邮箱中的邮件信息，十分类似。

在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（writelock）。
读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL会通过锁定防止其他用户读取同一数据。

1.2.2 锁粒度

表锁（table lock）

表锁是MySQL中最基本的锁策略，并且是开销最小的策略。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。
尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁（row lock）

行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。
众所周知，在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。
行级锁只在存储引擎层实现，而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。在本章的后续内容以及全书中，所有的存储引擎都以自己的方式显现了锁机制。

1.3 事务

事务就是一组 原子性的SQL查询。事务内的语句，要么全部执行成功，要么全部执行失败。

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库有两张表：支票（checking）表和储蓄（savings）表。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么需要至少三个步骤：

1. 检查支票账户的余额高于200美元。
2. 从支票账户余额中减去200美元。
3. 在储蓄账户余额中增加200美元。
   上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。
   可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交事务将修改
   的数据持久保留，要么使用ROLLBACK撤销所有的修改。事务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;
   单纯的事务概念并不是故事的全部。试想一下，如果执行到第四条语句时服务器崩溃了，会发生什么？天知道，用户可能会损失200美元。再假如，在执行到第三条语句和第四条语句之间时，另外一个进程要删除支票账户的所有余额，那么结果可能就是银行在不知道这个逻辑的情况下白白给了Jane 200美元。除非系统通过严格的ACID测试，否则空谈事务的概念是不够的。

ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。一个运行良好的事务处理系统，必须具备这些标准特征。

原子性（atomicity）

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

一致性（consistency）

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

隔离性（isolation）

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

持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

事务的ACID特性可以确保银行不会弄丢你的钱。而在应用逻辑中，要实现这一点非常难，甚至可以说是不可能完成的任务。一个兼容ACID的数据库系统，需要做很多复杂但可能用户并没有觉察到的工作，才能确保ACID的实现。
就像锁粒度的升级会增加系统开销一样，这种事务处理过程中额外的安全性，也会需要数据库系统做更多的额外工作。一个实现了ACID的数据库，相比没有实现ACID的数据库，通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。用户可以根据业务是否需要事务处理，来选择合适的存储引擎。对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能。即使存储引擎不支持事务，也可以通过LOCKTABLES语句为应用提供一定程度的保护，这些选择用户都可以自主决定。

1.3.1 隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。
每种存储引擎实现的隔离级别不尽相同。
下面简单地介绍一下四种隔离级别。

READ UNCOMMITTED（读未提交）

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，会出现胀读问题。

READ COMMITTED（读已提交）

一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果，会出现不可重复读问题。

REPEATABLE READ（可重复读）

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

SERIALIZABLE（可串行化）

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

出现问题

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read) ：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

1.3.2 死锁

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

事务1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;
事务2
START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

如果凑巧，两个事务都执行了第一条UPDATE语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条UPDATE语句，却发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素介入才可能解除死锁。
为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，比如InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方式，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。 InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚 （这是相对比较简单的死锁回滚算法）。
锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。
死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

1.3.3 事务日志

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

1.3.4 MySQL中的事务

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

自动提交（AUTOCOMMIT）

MySQL默认采用自动提交（AUTOCOMMIT）模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式：

1或者ON表示启用，0或者OFF表示禁用。当AUTOCOMMIT=0时，所有的查询都是在一个事务中，直到显式地执行COMMIT提交或者ROLLBACK回滚，该事务结束，同时又开始了另一个新事务。修改AUTOCOMMIT对非事务型的表，比如MyISAM或者内存表，不会有任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，也可以说是相当于一直处于AUTOCOMMIT启用的模式。
另外还有一些命令，在执行之前会强制执行COMMIT提交当前的活动事务。典型的例子，在数据定义语言（DDL）中，如果是会导致大量数据改变的操作，比如ALTERTABLE，就是如此。另外还有LOCK TABLES等其他语句也会导致同样的结果。如果有需要，请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。
MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL能够识别所有的4个ANSI隔离级别，InnoDB引擎也支持所有的隔离级别。

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

MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表），在正常提交的情况下不会有什么问题。
但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。
在非事务型的表上执行事务相关操作的时候，MySQL通常不会发出提醒，也不会报错。有时候只有回滚的时候才会发出一个警告：“某些非事务型的表上的变更不能被回滚”。但大多数情况下，对非事务型表的操作都不会有提示。

隐式和显式锁定

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

LOCK TABLES和事务之间相互影响的话，情况会变得非常复杂，在某些MySQL版本中甚至会产生无法预料的结果。因此，本书建议，除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES之外，其他任何时候都不要显式地执行LOCK TABLES，不管使用的是什么存储引擎。

1.4 多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。
可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
前面说到不同存储引擎的MVCC实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。下面我们通过InnoDB的简化版行为来说明MVCC是如何工作的。
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT

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

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

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

INSERT

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

DELETE

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

UPDATE

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

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

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

1.5 MySQL的存储引擎

在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如创建一个名为MyTable的表，MySQL会在MyTable.frm文件中保存该表的定义。因为MySQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在Windows中，大小写是不敏感的；而在类Unix中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。
可以使用SHOW TABLE STATUS命令（在MySQL 5.0以后的版本中，也可以查询INFORMATION_SCHEMA中对应的表）显示表的相关信息。例如，对于mysql数据库中的user表：

mysql> SHOW TABLE STATUS LIKE 'user' \G
*************************** 1. row ***************************
Name: user
Engine: MyISAM
Row_format: Dynamic
Rows: 6
Avg_row_length: 59
Data_length: 356
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2002-01-24 18:07:17
Update_time: 2002-01-24 21:56:29
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.00 sec)

输出的结果表明，这是一个MyISAM表。输出中还有很多其他信息以及统计信息。下
面简单介绍一下每一行的含义。

Name

表名。

Engine

表的存储引擎类型。在旧版本中，该列的名字叫Type，而不是Engine。

Row_format

行的格式。对于MyISAM表，可选的值为Dynamic、Fixed或者Compressed。Dynamic的行长度是可变的，一般包含可变长度的字段，如VARCHAR或BLOB。Fixed的行长度则是固定的，只包含固定长度的列，如CHAR和INTEGER。Compressed的行则只在压缩表中存在，请参考第19页“MyISAM压缩表”一节。

Rows

表中的行数。对于MyISAM和其他一些存储引擎，该值是精确的，但对于InnoDB，该值是估计值。

Avg_row_length

平均每行包含的字节数。

Data_length

表数据的大小（以字节为单位）。

Max_data_length

表数据的最大容量，该值和存储引擎有关。

Index_length

索引的大小（以字节为单位）。

Data_free

对于MyISAM表，表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行，以及后续可以被INSERT利用到的空间。

Auto_increment

下一个AUTO_INCREMENT的值。

Create_time

表的创建时间。

Update_time

表数据的最后修改时间。

Check_time

使用CKECK TABLE命令或者myisamchk工具最后一次检查表的时间。

Collation

表的默认字符集和字符列排序规则。

Checksum

如果启用，保存的是整个表的实时校验和。

Create_options

创建表时指定的其他选项。

Comment

该列包含了一些其他的额外信息。对于MyISAM表，保存的是表在创建时带的注释。对于InnoDB表，则保存的是InnoDB表空间的剩余空间信息。如果是一个视图，则该列包含“VIEW”的文本字样。

1.5.1 InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期（short-lived）事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

InnoDB概览

InnoDB的数据存储在表空间（tablespace）中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。在MySQL 4.1以后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB也可以使用裸设备作为表空间的存储介质，但现代的文件系统使得裸设备不再是必要的选择。
InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ（可重复读），并且 通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的。InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。不过它的 二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小 。InnoDB的存储格式是平台独立的，也就是说可以将数据和索引文件从Intel平台复制到PowerPC或者Sun SPARC平台。
InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insert buffer）等。本书后面将更详细地讨论这些内容。
InnoDB的行为是非常复杂的，不容易理解。如果使用了InnoDB引擎，笔者强烈建议阅读官方手册中的“InnoDB事务模型和锁”一节。如果应用程序基于InnoDB构建，则事先了解一下InnoDB的MVCC架构带来的一些微妙和细节之处是非常有必要的。存储引擎要为所有用户甚至包括修改数据的用户维持一致性的视图，是非常复杂的工作。
作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份，Oracle提供的MySQL Enterprise Backup、Percona提供的开源的XtraBackup都可以做到这一点。
MySQL的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

1.5.2 MyISAM存储引擎

在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。尽管MyISAM引擎不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小、可以忍受修复（repair）操作，则依然可以继续使用MyISAM（但请不要默认使用MyISAM，而是应当默认使用InnoDB）。

存储

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。MyISAM表可以包含动态或者静态（长度固定）行。MySQL会根据表的定义来决定采用何种行格式。MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。
在MySQL 5.0中，MyISAM表如果是变长行，则默认配置只能处理256TB的数据，因为指向数据记录的指针长度是6个字节。而在更早的版本中，指针长度默认是4字节，所以只能处理4GB的数据。而所有的MySQL版本都支持8字节的指针。要改变MyISAM表指针的长度（调高或者调低），可以通过修改表的MAX_ROWS和AVG_ROW_LENGTH选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引，这可能需要很长的时间才能完成。

MyISAM特性

作为MySQL最早的存储引擎之一，MyISAM有一些已经开发出来很多年的特性，可以满足用户的实际需求。

加锁与并发

MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为并发插入，CONCURRENT INSERT）。

修复

对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误，如果有错误可以通过执行REPAIR TABLE mytable进行修复。另外，如果MySQL服务器已经关闭，也可以通过myisamchk命令行工具进行检查和修复操作。

索引特性

对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。

延迟更新索引键（Delayed Key Write）

创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（inmemory key buffer），只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键的特性，可以在全局设置，也可以为单个表设置。

MyISAM压缩表

如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。可以使用myisampack对MyISAM表进行压缩（也叫打包pack）。压缩表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。
以现在的硬件能力，对大多数应用场景，读取压缩表数据时的解压带来的开销影响并不大，而减少I/O带来的好处则要大得多。压缩时表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表（甚至也不解压行所在的整个页面）。

MyISAM性能

MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。
MyISAM有一些服务器级别的性能扩展限制，比如对索引键缓冲区（key cache）的Mutex锁，MariaDB基于段（segment）的索引键缓冲区机制来避免该问题。但MyISAM最典型的性能问题还是表锁的问题，如果你发现所有的查询都长期处于“Locked”状态，那么毫无疑问表锁就是罪魁祸首。

第4章 Schema与数据类型优化

4.1 选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

• 更小的通常更好
  一般情况下，应该尽量使用可以正确存储数据的最小数据类型(1)。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
  但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。。
• 简单就好
  简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。这里有两个例子：一个是应该使用MySQL内建的类型(2)而不是字符串来存储日期和时间，另外一个是应该用整型存储IP地址。稍后我们将专门讨论这个话题。
• 尽量避免NULL
  很多表都包含可为NULL（空值）的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性(3)。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。
  如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
  通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。
  当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据(4)有很好的空间效率。但这一点不适用于MyISAM。

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。这通常是很简单的，但是我们会提到一些特殊的不是那么直观的案例。
下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
例如，DATETIME和TIMESAMP列都可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。
本章只讨论基本的数据类型。MySQL为了兼容性支持很多别名，例如INTEGER、BOOL，以及NUMERIC。它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表时采用数据类型的别名，然后用SHOW CREATE TABLE检查，会发现MySQL报告的是基本类型，而不是别名。

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是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用64位的BIGINT整数，即使在32位环境也是如此。（一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算）。
MySQL可以为整数类型指定宽度，例如INT（11），对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。 对于存储和计算来说，INT（1）和INT（20）是相同的 。

4.1.2 实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算是怎么计算的，则需要研究所使用的平台的浮点数的具体实现。
DECIMAL类型用于存储精确的小数。在MySQL 5.0和更高版本，DECIMAL类型支持精确计算。MySQL 4.1以及更早版本则使用浮点运算来实现DECIAML的计算，这样做会因为精度损失导致一些奇怪的结果。在这些版本的MySQL中，DECIMAL只是一个“存储类型”。因为CPU不支持对DECIMAL的直接计算，所以在MySQL 5.0以及更高版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以 浮点运算明显更快 。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，DECIMAL（18,9）小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。
MySQL 5.0和更高版本中的DECIMAL类型允许最多65个数字。而早期的MySQL版本中这个限制是254个数字，并且保存为未压缩的字符串（每个数字一个字节）。然而，这些（早期）版本实际上并不能在计算中使用这么大的数字，因为DECIMAL只是一种存储格式；在计算中DECIMAL会转换为DOUBLE类型。
有多种方法可以指定浮点列所需要的精度，这会使得MySQL悄悄选择不同的数据类型，或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型，不指定精度。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型；MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。 但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可 。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

4.1.3 字符串类型

MySQL支持多种字符串类型，每种类型还有很多变种。这些数据类型在4.1和5.0版本发生了很大的变化，使得情况更加复杂。从MySQL 4.1开始，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则（collation）（更多关于这个主题的信息请参考第7章）。这些东西会很大程度上影响性能。

VARCHAR和CHAR类型

VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是怎么存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存储引擎是InnoDB和/或者MyISAM。如果使用的不是这两种存储引擎，请参考所使用的存储引擎的文档。
先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意，存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样，所以MySQL服务器从存储引擎读出的值可能需要转换为另一种存储格式。下面是关于两种类型的一些比较。

• VARCHAR
  VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。
  VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latin1字符集，一个VARCHAR（10）的列需要11个字节的存储空间。VARCHAR（1000）的列则需要1002个字节，因为需要2个字节存储长度信息。
  VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。
  下面这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
  在5.0或者更高版本，MySQL在存储和检索时会保留末尾空格。但在4.1或更老的版本，MySQL会剔除末尾空格。
  InnoDB则更灵活，它可以把过长的VARCHAR存储为BLOB，我们稍后讨论这个问题。
• CHAR
  CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。
  CHAR适合存储很短的字符串，或者所有值都接近同一个长度 。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，
  因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR（1）来存储只有Y和N的值，如果采用单字节字符集(5)只需要一个字节，但是VARCHAR（1）却需要两个字节，因为还有一个记录长度的额外字节。
  CHAR类型的这些行为可能有一点难以理解，下面通过一个具体的例子来说明。首先，我们创建一张只有一个CHAR（10）字段的表并且往里面插入一些值：

mysql> CREATE TABLE char_test( char_col CHAR(10));
mysql> INSERT INTO char_test(char_col) VALUES ('string1'), (' string2'), ('string3 ')

当检索这些值的时候，会发现string3末尾的空格被截断了。

数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串。Memory引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间(7)。不过，填充和截取空格的行为在不同存储引擎都是一样的，因为这是在MySQL服务器层进行处理的。
与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。填充也不一样：MySQL填充BINARY采用的是\0（零字节）而不是空格，在检索时也不会去掉填充值(8)。
当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

BLOB和TEXT类型

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

使用枚举（ENUM）代替字符串类型

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

mysql> CREATE TABLE enum_test(
-> e ENUM ('fish', 'apple', 'dog') NOT NULL
-> );
mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

这三行数据实际存储为整数，而不是字符串。可以通过在数字上下文环境检索看到这个双重属性：

如果使用数字作为ENUM枚举常量，这种双重性很容易导致混乱，例如ENUM（‘1’，‘2’,‘3’）。建议尽量避免这么做。
另外一个让人吃惊的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的：

一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显式地指定排序顺序，但这会导致MySQL无法利用索引消除排序。

4.1.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL能存储的最小时间粒度为秒（MariaDB支持微秒级别的时间类型）。但是MySQL也可以使用微秒级的粒度进行临时运算，我们会展示怎么绕开这种存储限制。
MySQL提供两种相似的日期类型：DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些场景，一个比另一个工作得好。让我们来看一下。

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年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
MySQL 4.1以及更新的版本按照DATETIME的方式格式化TIMESTAMP的值，但是MySQL 4.0以及更老的版本不会在各个部分之间显示任何标点符号。这仅仅是显示格式上的区别，TIMESTAMP的存储格式在各个版本都是一样的。
TIMESTAMP显示的值也依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。
因此，存储值为0的TIMESTAMP在美国东部时区显示为“1969-12-31 19:00:00”，与格林尼治时间差5个小时。有必要强调一下这个区别：如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。
TIMESTAMP也有DATETIME没有的特殊属性。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间(10)。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值（除非在UPDATE语句中明确指定了值）。你可以配置任何TIMESTAMP列的插入和更新行为。最后，TIMESTAMP列默认为NOT NULL，这也和其他的数据类型不一样。
除了特殊行为之外，通常也应该尽量使用TIMESTAMP ，因为它比DATETIME空间效率更高。有时候人们会将Unix时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所以我们不推荐这样做。
如果需要存储比秒更小粒度的日期和时间值怎么办？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。这两种方式都可以，或者也可以使用MariaDB替代MySQL。

4.1.6 选择标识符（identifier）

为标识列（identifier column）选择合适的数据类型非常重要。一般来说有可能用标识列与其他值进行比较（例如，在关联操作中），或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。
当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。例如，MySQL在内部使用整数存储ENUM和SET类型，然后在做比较操作时转换为字符串。
一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性(11)。混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。这种错误可能会很久以后才突然出现，那时候可能都已经忘记是在比较不同的数据类型。
在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。例如有一个state_id列存储美国各州的名字(12)，就不需要几千或几百万个值，所以不需要使用INT。TINYINT足够存储，而且比INT少了3个字节。如果用这个值作为其他表的外键，3个字节可能导致很大的性能差异。下面是一些小技巧。

整数类型

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

ENUM和SET类型

对于标识列来说，EMUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别。

字符串类型

如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。尤其是在MyISAM表里使用字符串作为标识列时要特别小心。MyISAM默认对字符串使用压缩索引，这会导致查询慢得多。在我们的测试中，我们注意到最多有6倍的性能下降。
对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢(13)：
因为插入值会随机地写到索引的不同位置，所以使得INSERT语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。关于这一点第5章有更多的讨论。
SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。
如果存储UUID值，则应该移除“-”符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY（16）列中。检索时可以通过HEX()函数来格式化为十六进制格式。
UUID()生成的值与加密散列函数例如SHA1()生成的值有不同的特征：UUID值虽然分布也不均匀，但还是有一定顺序的。尽管如此，但还是不如递增的整数好用。

4.1.7 特殊类型数据

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

4.3 范式和反范式

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

数据库的三大范式是指关系数据库设计中的三个规范化级别，用于规范化数据库中的数据结构，提高数据的一致性和减少数据冗余。这三大范式分别是：

1. 第一范式（1NF）：要求数据库 表中的每个字段都是原子性的 ，不可再分。也就是说，每个字段中的数据不能包含多个值或多个属性。如果一个字段中包含了多个值，就需要将其拆分成多个独立的字段。

2. 第二范式（2NF）：在满足第一范式的基础上，要求数据库表中的 非主键字段必须完全依赖于主键。也就是说 ，每个非主键字段必须完全依赖于主键，而不能依赖于其他非主键字段。如果存在部分依赖或传递依赖的情况，就需要将其拆分成多个独立的表。

3. 第三范式（3NF）：在满足第二范式的基础上，要求数据库表中的 非主键字段之间不能存在传递依赖关系 。也就是说，每个非主键字段之间应该是互相独立的，不能通过其他非主键字段推导出来。如果存在传递依赖的情况，就需要将其拆分成多个独立的表。

4.3.1 范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处：

• 范式化的更新操作通常比反范式化要快。
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。还是前面的例子：在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得一份唯一的部门列表，但是如果部门（DEPARTMENT）是一张单独的表，则只需要简单的查询这张表就行了。

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

4.3.2 反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。
如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O(14)。
单独的表也能使用更有效的索引策略。假设有一个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。如果是范式化的结构并且索引了发送日期字段published，这个查询也许看起来像这样：

mysql> SELECT message_text, user_name
-> FROM message
-> INNER JOIN user ON message.user_id=user.id
-> WHERE user.account_type='premiumv
-> ORDER BY message.published DESC LIMIT 10;

要更有效地执行这个查询，MySQL需要扫描message表的published字段的索引。对于每一行找到的数据，将需要到user表里检查这个用户是不是付费用户。如果只有一小部分用户是付费账户，那么这是效率低下的做法。
另一种可能的执行计划是从user表开始，选择所有的付费用户，获得他们所有的信息，并且排序。但这可能更加糟糕。
主要问题是关联，使得需要在一个索引中又排序又过滤。如果采用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引（account_type，published），就可以不通过关联写出这个查询。这将非常高效：

mysql> SELECT message_text,user_name
-> FROM user_messages
-> WHERE account_type='premium'
-> ORDER BY published DESC
-> LIMIT 10;

4.3.3 混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？
事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。
在我们的网站实例中，可以在user表和message表中都存储account_type字段，而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。
但是现在更新用户的账户类型的操作代价就高了，因为需要同时更新两张表。至于这会不会是一个问题，需要考虑更新的频率以及更新的时长，并和执行SELECT查询的频率进行比较。
另一个从父表冗余一些数据到子表的理由是排序的需要。例如，在范式化的schema里通过作者的名字对消息做排序的代价将会非常高，但是如果在message表中缓存author_name字段并且建好索引，则可以非常高效地完成排序。
缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（像很多论坛做的），可以每次执行一个昂贵的子查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

4.5 加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。
MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。 这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。
MySQL 5.1以及更新版本包含一些类型的“在线”操作的支持，这些功能不需要在整个操作过程中锁表。最近版本的InnoDB(15)也支持通过排序来建索引，这使得建索引更快并且有一个紧凑的索引布局。
一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL操作时有用的技巧，但这是针对一些特殊的场景而言的。对常见的场景，能使用的技巧只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
不是所有的ALTER TABLE操作都会引起表重建。例如，有两种方法可以改变或者删除一个列的默认值（一种方法很快，另外一种则很慢）。假如要修改电影的默认租赁期限，从三天改到五天。下面是很慢的方式：

mysql> ALTER TABLE sakila.film
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

SHOW STATUS显示这个语句做了1000次读和1000次插入操作。换句话说，它拷贝了整张表到一张新表，甚至列的类型、大小和可否为NULL属性都没改变。
理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都将导致表重建。
另外一种方法是通过ALTER COLUMN(16)操作来改变列的默认值：

mysql> ALTER TABLE sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;

这个语句会直接修改.frm文件而不涉及表数据。所以，这个操作是非常快的。

4.5.1 只修改.frm文件

从上面的例子我们看到修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表。
我们下面要演示的技巧是不受官方支持的，也没有文档记录，并且也可能不能正常工作，采用这些技术需要自己承担风险。建议在执行之前首先备份数据！
下面这些操作是有可能不需要重建表的：

• 移除（不是增加）一个列的AUTO_INCREMENT属性。
• 增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字串值。

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

1. 创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量）。
2. 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。
3. 交换.frm文件.
4. 执行UNLOCK TABLES来释放第2步的读锁。
   下面以给sakila.flm表的rating列增加一个常量为例来说明。当前列看起来如下：
   假设我们需要为那些对电影更加谨慎的父母们增加一个PG-14的电影分级：
   

mysql> CREATE TABLE sakila.film_new LIKE sakila.film;
mysql> ALTER TABLE sakila.film_new
-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14')
-> DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK;

注意，我们是在常量列表的末尾增加一个新的值。如果把新增的值放在中间，例如PG-13之后，则会导致已经存在的数据的含义被改变：已经存在的R值将变成PG-14，而已经存在的NC-17将成为R，等等。
接下来用操作系统的命令交换.frm文件：

/var/lib/mysql/sakila# mv film.frm film_tmp.frm
/var/lib/mysql/sakila# mv film_new.frm film.frm
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

再回到MySQL命令行，现在可以解锁表并且看到变更后的效果了：

mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating'\G
*************************** 1. row ***************************
Field: rating
Type: enum('G','PG','PG-13','R','NC-17','PG-14')

最后需要做的是删除为完成这个操作而创建的辅助表：

mysql> DROP TABLE sakila.film_new;

4.5.2 快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引：
mysql> ALTER TABLE test.load_data ENABLE KEYS;
– load the data
mysql> ALTER TABLE test.load_data ENABLE KEYS;
这个技巧能够发挥作用，是因为构建索引的工作被延迟到数据完全载入以后，这个时候已经可以通过排序来构建索引了。这样做会快很多，并且使得索引树(17)的碎片更少、更紧凑。
不幸的是，这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。
MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小超过了有效内存大小，载入操作就会变得越来越慢。
在现代版本的InnoDB版本中，有一个类似的技巧，这依赖于InnoDB的快速在线索引创建功能。这个技巧是，先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。Percona Server可以自动完成这些操作步骤。
也可以使用像前面说的ALTER TABLE的骇客方法来加速这个操作，但需要多做一些工作并且承担一定的风险。这对从备份中载入数据是很有用的，例如，当已经知道所有数据都是有效的并且没有必要做唯一性检查时就可以这么来操作。
再次说明，这是没有文档说明并且不受官方支持的技巧。若使用的话，需要自己承担风险，并且操作之
前一定要先备份数据。
下面是操作步骤：

1. 用需要的表结构创建一张表，但是不包括索引。
2. 载入数据到表中以构建.MYD文件。
3. 按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.MYI文件。
4. 获取读锁并刷新表。
5. 重命名第二张表的.frm和.MYI文件，让MySQL认为是第一张表的文件。
6. 释放读锁。
7. 使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。
   这个操作步骤对大表来说会快很多。

4.6 总结

良好的schema设计原则是普遍适用的，但MySQL有它自己的实现细节要注意。概括来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：

• 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
• 尽量使用整型定义标识列。
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
• 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT。

范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且能带来好处。第5章我们将看到更多的例子。预先计算、缓存或生成汇总表也可能获得很大的好处。Justin Swanhart的Flexviews工具可以帮助维护汇总表。
最后，ALTER TABLE是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整张表。我们展示了一些特殊的场景可以使用骇客方法；但是对大部分场景，必须使用其他更常规的方法，例如在备机执行ALTER并在完成后把它切换为主库。

第5章 创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构。
索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。

5.1 索引基础

在MySQL中，存储引擎先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如要运行下面的查询：
mysql> SELECT first_name FROM sakila.actor WHERE actor_id=5;
如果在actor_id列上建有索引，则MySQL将使用该索引找到actor_id为5的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的，下面将详细介绍。

5.1.1 索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
下面我们先来看看MySQL支持的索引类型，以及它们的优点和缺点。

B-Tree索引

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同 。图5-1展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。MyISAM使用的结构有所不同，但基本思想是类似的。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。
叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页（不同引擎的“指针”类型不同）。图5-1中仅绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率会非常高。
假设有如下数据表：

CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m', 'f') not null,
key(last_name, first_name, dob)
);

对于表中的每一行数据，索引中包含了last_name、frst_name和dob列的值，图5-2显示了该索引是如何组织数据的存储的。

请注意，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都一样，则根据他们的出生日期来排列顺序。
可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效。

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

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name），则MySQL只能使用索引的第一列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE last_name=‘Smith’ AND frst_name LIKE ‘J％’ AND dob=‘1976-12-23’，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
下面来看一个例子。假设有如下表：

CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
) ENGINE=MEMORY;

表中包含如下数据：

假设索引使用假想的哈希函数f()，它返回下面的值（都是示例数据，非真实数据）：

f('Arjen')= 2323
f('Baron')= 7437
f('Peter')= 8784
f('Vadim')= 2458

则哈希索引的数据结构如下：

注意每个槽的编号是顺序的，但是数据行不是。现在，来看如下查询：

mysql> SELECT lname FROM testhash WHERE fname='Peter';

MySQL先计算’Peter’的哈希值，并使用该值寻找对应的记录指针。因为f（‘Peter’）=8784，所以MySQL在索引中查找8784，可以找到指向第3行的指针，最后一步是比较第三行的值是否为’Peter’，以确保就是要查找的行。
因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

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

InnoDB引擎有一个特殊的功能叫做“ 自适应哈希索引 （adaptive hash index）”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。
创建自定义哈希索引 。如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。
思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。
下面是一个实例，例如需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身都很长。正常情况下会有如下查询：

mysql> SELECT id FROM url WHERE url="http://www.mysql.com";

若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

mysql> SELECT id FROM url WHERE url="http://www.mysql.com"
-> AND url_crc=CRC32("http://www.mysql.com");

这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找（在上面的案例中，索引值为1560514994）。即使有多个记录有相同的索引值，查找仍然很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回对应的行。
如果数据表非常大，CRC32()会出现大量的哈希冲突，则可以使用MD5()函数返回值的一部分来作为 自定义哈希函数 来返回整数hash值

select conv(right(md5('http://ww.mysql.com/'),16),16,10) as Hash

空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

5.2 索引的优点

索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用， 最常见的B-Tree索引，按照顺序存储数据 ，所以MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下三个优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机I/O变为顺序I/O。
   如何评价一个索引是否适合某个查询的“三星系统”（three-star system）：索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得“三星”。

5.3 高性能的索引策略

5.3.1 独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
例如，下面这个查询无法使用actor_id列的索引：
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
MySQL无法自动解析这个方程式。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。
下面是另一个常见的错误：
mysql> SELECT … WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

5.3.2 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。
第二个策略 可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。
为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较 。在示例数据库Sakila中并没有合适的例子，所以我们从表city中生成一个示例表，这样就有足够的数据进行演示：

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;

现在我们有了示例数据集。数据分布当然不是真实的分布；因为我们使用了RAND()，所以你的结果会与此不同，但对这个练习来说这并不重要。首先，我们找到最常见的城市列表：

注意到，上面每个值都出现了45～65次。现在查找到最频繁出现的城市前缀，先从3个前缀字母开始：

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀长度为7时比较合适：

计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：

通常来说（尽管也有例外情况），这个例子中如果前缀的选择性能够接近0.031，基本上就可用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性：

查询显示当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。
只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了，但如果数据分布很不均匀，可能就会有陷阱。如果观察前缀为4的最常出现城市的次数，可以看到明显不均匀：

如果前缀是4个字节，则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。如果有比这个随机生成的示例更真实的数据，就更有可能看到这种现象。例如在真实的城市名上建一个长度为4的前缀索引，对于以“San”和“New”开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。
在上面的示例中，已经找到了合适的前缀长度，下面演示一下如何创建前缀索引：

mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));

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

5.3.3 多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
为每个列创建独立的索引，从SHOW CREATE TABLE中很容易看到这种情况：

CREATE TABLE t (
	c1 INT,
	c2 INT,
	c3 INT,
	KEY(c1),
	KEY(c2),
	KEY(c3)
);

这种索引策略最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能 。
MySQL 5.0和更新版本引入了一种叫“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。例如，表film_actor在字段film_id和actor_id上各有一个单列索引。但对于下面这个查询WHERE条件，这两个单列索引都不是好的选择：

mysql> SELECT film_id, actor_id FROM sakila.film_actor
-> WHERE actor_id = 1 OR film_id = 1;
在老的MySQL版本中，MySQL对这个查询会使用全表扫描。除非改写成如下的两个查询UNION的方式：
mysql> SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1
-> UNION ALL
-> AND actor_id <> 1;

但在MySQL 5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：

mysql> EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor
-> WHERE actor_id = 1 OR film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY,idx_fk_film_id
key_len: 2,2
ref: NULL
rows: 29
Extra: Using union(PRIMARY,idx_fk_film_id); Using where

MySQL会使用这类技术优化复杂查询，所以在某些语句的Extra列中还可以看到嵌套操作。
索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：
当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL 4.1或者更早的时代一样，将查询改写成UNION的方式往往更好。
如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

5.3.4 选择合适的索引列顺序（本节内容适用于B-Tree索引；哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据）

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
所以多列索引的列顺序至关重要。在Lahdenmaki和Leach的“三星索引”系统中，列顺序也决定了一个索引是否能够成为一个真正的“三星索引”。
对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗?在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
以下面的查询为例：

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

是应该创建一个（staff_id，customer_id）索引还是应该颠倒一下顺序?可以跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。

mysql> SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
> COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
> COUNT(*)
> FROM payment\G
*************************** 1. row ***************************
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

customer_id的选择性更高，所以答案是将其作为索引列的第一列：

mysql> ALTER TABLE payment ADD KEY(customer_id, staff_id);

当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。
下面是一个我们遇到过的真实案例，在一个用户分享购买商品和购买经验的论坛上，这个特殊表上的查询运行得非常慢：

mysql> SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
-> FROM Message
-> WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
-> ORDER BY priority DESC, modifiedDate DESC

这个查询看似没有建立合适的索引，所以客户咨询我们是否可以优化。EXPLAIN的结果如下：

id: 1
select_type: SIMPLE
table: Message
type: ref
key: ix_groupId_userId
key_len: 18
ref: const,const
rows: 1251162
Extra: Using where

MySQL为这个查询选择了索引（groupId，userId），如果不考虑列的基数，这看起来是一个非常合理的选择。但如果考虑一下user ID和group ID条件匹配的行数，可能就会有不同的想法了：

mysql> SELECT COUNT(*), SUM(groupId = 10137),
-> SUM(userId = 1288826), SUM(anonymous = 0)
-> FROM Message\G
*************************** 1. row ***************************
count(*): 4142217
sum(groupId = 10137): 4092654
sum(userId = 1288826): 1288496
sum(anonymous = 0): 4141934

从上面的结果来看符合组（groupId）条件几乎满足表中的所有行，符合用户（userId）条件的有130万条记录——也就是说索引基本上没什么用。因为这些数据是从其他应用中迁移过来的，迁移的时候把所有的消息都赋予了管理员组的用户。这个案例的解决办法是修改应用程序代码，区分这类特殊用户和组，禁止针对这类用户和组执行这个查询。
从这个小案例可以看到经验法则和推论在多数情况是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。
最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

5.3.5 聚簇索引

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

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。本节我们主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。
图5-3展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。

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

聚簇索引优点：

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

聚簇索引也有一些缺点：

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

二级索引需要两次索引查找 : 二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。
这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

InnoDB和MyISAM的数据分布对比

在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储。
聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。
还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

在InnoDB表中按主键顺序插入行

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

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

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

在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。
从这个案例可以看出，使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

5.3.6 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引可以避免回表查询：InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些“额外”的主键列来覆盖查询。例如，sakila.actor使用InnoDB存储引擎，并在last_name字段有二级索引，虽然该索引的列不包括主键actor_id，但也能够用于对actor_id做覆盖查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

5.3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；
如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。
MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序(14)。
如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。
ORDERBY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

例如，Sakila示例数据库的表rental在列（rental_date，inventory_id，customer_id）上有名为rental_date的索引。

(rental_date, inventory_id, customer_id):
CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

MySQL可以使用rental_date索引为下面的查询做排序，从EXPLAIN中可以看到没有出现文件排序（filesort）操作：

mysql> EXPLAIN SELECT rental_id, staff_id FROM sakila.rental
-> WHERE rental_date = '2005-05-25'
-> ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
type: ref
possible_keys: rental_date
key: rental_date
rows: 1
Extra: Using where

即使ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。
还有更多可以使用索引做排序的查询示例。下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;

下面是一些不能使用索引做排序的查询：
下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;

下面这个查询的ORDER BY子句中引用了一个不在索引中的列：

... WHERE rental_date='2005-05-25' ORDER BY inventory_id，staff_id;

下面这个查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：

... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;

这个查询在y inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：

... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;

下面这个例子理论上是可以使用索引进行关联排序的，但由于优化器在优化时将film_actor表当作关联的第二张表，所以实际上无法使用索引：

5.3.8 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。
压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描——例如ORDER BY DESC——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。
测试表明，对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。
可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。

5.3.9 冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。
有时会在不经意间创建了重复索引，例如下面的代码：
CREATE TABLE test (
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
UNIQUE(ID),
INDEX(ID)
) ENGINE=InnoDB;
一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。
冗余索引和重复索引有一些不同。如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。因此索引（A，B）也可以当作索引（A）来使用（这种冗余只是对B-Tree索引来说的）。但是如果再创建索引（B，A），则不是冗余索引，索引（B）也不是，因为B不是索引（A，B）的最左前缀列。另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。
特殊的冗余索引：将一个索引扩展为（A，ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的 。

在前面的InnoDB的示例表中，因为二级索引的叶子节点包含了主键值，所以在列（A）上的索引就相当于在（A，ID）上的索引。如果有像WHERE A=5 ORDER BY ID这样的查询，这个索引会很有作用。但如果将索引扩展为（A，B），则实际上就变成了（A，B，ID），那么上面查询的ORDER BY子句就无法使用该索引做排序，而只能用文件排序了。

5.3.11 索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发性。
InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。在MySQL 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。

InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢很多。

5.4 索引案例学习

假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢?
出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分的排序，则WHERE条件中的age BETWEEN 18 AND 25就无法使用索引。如果MySQL使用某个索引进行范围查询，也就无法再使用另一个索引（或者是该索引的后续字段）进行排序了。如果这是很常见的WHERE条件，那么我们当然就会认为很多查询需要做排序操作（例如文件排序filesort）。

5.4.1 支持多种过滤条件

现在需要看看哪些列拥有很多不同的取值，哪些列在WHERE子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的，因为可以让MySQL更有效地过滤掉不需要的行。
country列的选择性通常不高，但可能很多查询都会用到。sex列的选择性肯定很低，但也会在很多查询中用到。所以考虑到使用的频率，还是建议在创建不同组合索引的时候将（sex，country）列作为前缀。
但根据传统的经验不是说不应该在选择性低的列上创建索引的吗？那为什么这里要将两个选择性都很低的字段作为索引的前缀列?我们的脑子坏了?
我们的脑子当然没坏。这么做有两个理由：第一点，如前所述几乎所有的查询都会用到sex列。前面曾提到，几乎每一个查询都会用到sex列，甚至会把网站设计成每次都只能按某一种性别搜索用户。更重要的一点是，索引中加上这一列也没有坏处，即使查询没有使用sex列也可以通过下面的“诀窍”绕过。
这个“诀窍”就是：如果某个查询不限制性别，那么可以通过在查询条件中新增AND SEX IN（‘m’,‘f’）来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。
这个“诀窍”在这类场景中非常有效，但如果列有太多不同的值，就会让IN()列表太长，这样做就不行了。
这个案例显示了一个基本原则：考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车去设计最完美的索引。
接下来，需要考虑其他常见WHERE条件的组合，并需要了解哪些组合在没有合适索引的情况下会很慢。（sex，country，age）上的索引就是一个很明显的选择，另外很有可能还需要（sex，country，region，age）和（sex，country，region，city，age）这样的组合索引。
这样就会需要大量的索引。如果想尽可能重用索引而不是建立大量的组合索引，可以使用前面提到的IN()的技巧来避免同时需要（sex，country，age）和（sex，country，region，age）的索引。如果没有指定这个字段搜索，就需要定义一个全部国家列表，或者国家的全部地区列表，来确保索引前缀有同样的约束（组合所有国家、地区、性别将会是一个非常大的条件）。
这些索引将满足大部分最常见的搜索查询，但是如何为一些生僻的搜索条件（比如has_pictures、eye_color、hair_color和education）来设计索引呢？这些列的选择性高、使用也不频繁，可以选择忽略它们，让MySQL多扫描一些额外的行即可。另一个可选的方法是在age列的前面加上这些列，在查询时使用前面提到过的IN()技术来处理搜索时没有指定这些列的场景。
你可能已经注意到了，我们一直将age列放在索引的最后面。age列有什么特殊的地方吗？为什么要放在索引的最后？我们总是尽可能让MySQL使用更多的索引列，因为查询只能使用索引的最左前缀，直到遇到第一个范围条件列。前面提到的列在WHERE子句中都是等于条件，但是age列则多半是范围查询（例如查找年龄在18～25岁之间的人）。
当然，也可以使用IN()来代替范围查询，例如年龄条件改写为IN（18，19，20，21，22，23，24，25），但不是所有的范围查询都可以转换。这里描述的基本原则是， 尽可能将需要做范围查询的列放到索引的后面 ，以便优化器能使用尽可能多的索引列。
前面提到可以在索引中加入更多的列，并通过IN()的方式覆盖那些不在WHERE子句中的列。但这种技巧也不能滥用，否则可能会带来麻烦。因为每额外增加一个IN()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能。考虑下面的WHERE子句：

WHERE eye_color IN('brown','blue','hazel')
AND hair_color IN('black','red','blonde','brown')
AND sex IN('M','F')

优化器则会转化成4×3×2=24种组合，执行计划需要检查WHERE子句中所有的24种组合。对于MySQL来说，24种组合并不是很夸张，但如果组合数达到上千个则需要特别小心。老版本的MySQL在IN()组合条件过多的时候会有很多问题。查询优化可能需要花很多时间，并消耗大量的内存。新版本的MySQL在组合数超过一定数量后就不再进行执行计划评估了，这可能会导致MySQL不能很好地利用索引。

5.4.2 避免多个范围条件

5.4.3 优化排序

下面这个查询通过ORDER BY和LIMIT偏移量的组合翻页到很后面的时候：

mysql> SELECT<cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000; 10;

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。
优化这类索引的另一个比较好的策略是使用 延迟关联

通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数。

下面这个查询显示了如何高效地使用（sex，rating）索引进行排序和分页：

mysql> SELECT <cols> FROM profiles INNER JOIN (
-> SELECT <primary key cols> FROM profiles
-> WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
-> ) AS x USING(<primary key cols>);

5.5 维护索引和表

5.5.1 找到并修复损坏的表

5.5.2 更新索引统计信息

5.5.3 减少索引和数据的碎片

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

三种类型的数据碎片:

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

新版本InnoDB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

应该通过一些实际测量而不是随意假设来确定是否需要消除索引和表的碎片化。Percona的XtraBackup有个–stats参数以非备份的方式运行，而只是打印索引和表的统计情况，包括页中的数据量和空余空间。这可以用来确定数据的碎片化程度。另外也要考虑数据是否已经达到稳定状态，如果你进行碎片整理将数据压缩到一起，可能反而会导致后续的更新操作触发一系列的页分裂和重组，这会对性能造成不良的影响（直到数据再次达到新的稳定状态）。

第6章 查询性能优化

6.1 为什么查询速度会慢

查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

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

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

查询不需要的记录

一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。最简单有效的解决方法就是在这样的查询后面加上LIMIT。

多表关联时返回全部列

总是取出全部列

重复查询相同的数据

如果你不太小心，很容易出现这样的错误——不断地重复执行相同的查询，然后每次都返回完全相同的数据。例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复查询这个数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

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

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下：

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

响应时间

响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁，等等。遗憾的是，我们无法把响应时间细分到上面这些部分，除非有什么办法能够逐个测量上面这些消耗，不过很难做到。一般最常见和重要的等待是I/O和锁等待，但是实际情况更加复杂。

扫描的行数和返回的行数

分析查询时，查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。
对于找出那些“糟糕”的查询，这个指标可能还不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。
理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多。例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。

扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。
在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。
如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，这也正是我们前一章讨论过的问题。现在应该明白为什么索引对于查询优化如此重要了。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。
例如，我们看看示例数据库Sakila中的一个查询案例：

mysql> SELECT *FROM sakila.film_actor WHERE film_id = 1;

这个查询将返回10行数据，从EXPLAIN的结果可以看到，MySQL在索引idx_fk_film_id上使用了ref访问类型来执行查询：

mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
Extra:

EXPLAIN的结果也显示MySQL预估需要访问10行数据。换句话说，查询优化器认为这种访问类型可以高效地完成查询。如果没有合适的索引会怎样呢？MySQL就不得不使用一种更糟糕的访问类型，下面我们来看看如果我们删除对应的索引再来运行这个查询：

mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where

正如我们预测的，访问类型变成了一个全表扫描（ALL），现在MySQL预估需要扫描5073条记录来完成这个查询。这里的“Using Where”表示MySQL将通过WHERE条件来筛选存储引擎返回的记录。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

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

上面这个例子说明了好的索引多么重要。好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。但也不是说增加索引就能让扫描的行数等于返回的行数。
例如下面使用聚合函数COUNT()的查询：

mysql> SELECT actor_id，COUNT（*） FROM sakila.film_actor GROUP BY actor_id;

这个查询需要读取几千行数据，但是仅返回200行结果。没有什么索引能够让这样的查询减少需要扫描的行数。
不幸的是，MySQL不会告诉我们生成结果实际上需要扫描多少行数据，而只会告诉我们生成结果时一共扫描了多少行数据。扫描的行数中的大部分都很可能是被WHERE条件过滤掉的，对最终的结果集并没有贡献。在上面的例子中，我们删除索引后，看到MySQL需要扫描所有记录然后根据WHERE条件过滤，最终只返回10行结果。理解一个查询需要扫描多少行和实际需要使用的行数需要先去理解这个查询背后的逻辑和思想。
如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：

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

6.3 重构查询的方式

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

设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。
但是这样的想法对于MySQL并不适用，MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。在某些版本的MySQL上，即使在一个通用服务器上，也能够运行每秒超过10万的查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。所以运行多个小查询现在已经不是大问题了。
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。

6.3.2 切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。例如，我们需要每个月运行一次下面的查询：

mysql> DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

那么可以用类似下面的办法来完成同样的工作：

rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
LIMIT 10000")
} while rows_affected > 0

一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法（如果是事务型引擎，很多时候小事务能够更高效）。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

6.3.3 分解关联查询

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

mysql> SELECT * FROM tag
-> JOIN tag_post ON tag_post.tag_id=tag.id
-> JOIN post ON tag_post.post_id=post.id
-> WHERE tag.tag='mysql';

可以分解成下面这些查询来代替：

mysql> SELECT * FROM tag_post WHERE tag_id=1';
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

到底为什么要这样做？乍一看，这样做并没有什么好处，原本一条查询，这里却变成多条查询，返回的结果又是一模一样的。事实上，用分解关联查询的方式重构查询有如下的优势：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了ID为123、567、9098的内容，那么第三个查询的IN()中就可以少几个ID。另外，对MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。我们后续将详细介绍这点。
• 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
• 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同的MySQL服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

6.4 查询执行的基础

MySQL执行一个查询的过程：

1. 客户端发送一条查询给服务器。
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。
   

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

当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。客户端像是“从消防水管喝水”（这是一个术语）。
多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。
当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。

查询状态

对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用SHOW FULL PROCESSLIST命令（该命令返回结果中的Command列就表示当前的状态）。在一个查询的生命周期中，状态会变化很多次。MySQL官方手册中对这些状态值的含义有最权威的解释，下面将这些状态列出来，并做一个简单的解释。

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

了解这些状态的基本含义非常有用，这可以让你很快地了解当前“谁正在持球”。在一个繁忙的服务器上，可能会看到大量的不正常的状态，例如statistics正占用大量的时间。这通常表示，某个地方有异常了，可以通过使用第3章的一些技巧来诊断到底是哪个环节出现了问题。

6.4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。
如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

6.4.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。

语法解析器和预处理

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。
预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
下一步预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置。

查询优化器

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来（成本计算公式）变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。

这个结果表示MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。

MySQL如何执行关联查询

MySQL认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联。
对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。在MySQL的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。
当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。
按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，然后回溯到上一个表，在MySQL中是通过嵌套循环的方式实现——正如其名“嵌套循环关联”。请看下面的例子中的简单查询：

mysql> SELECT tbl1.col1, tbl2.col2
-> FROM tbl1 INNER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6)

假设MySQL按照查询中的表顺序进行关联操作，我们则可以用下面的伪代码表示MySQL将如何完成这个查询：

outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
inner_iter = iterator over tbl2 where col3 = outer_row.col3
inner_row = inner_iter.next
while inner_row
output [ outer_row.col1, inner_row.col2 ]
inner_row = inner_iter.next
end
outer_row = outer_iter.netxt
end

上面的执行计划对于单表查询和多表关联查询都适用，如果是一个单表查询，那么只需完成上面外层的基本操作。对于外连接上面的执行过程仍然适用。例如，我们将上面查询修改如下：

mysql> SELECT tbl1.col1, tbl2.col2
-> FROM tbl1 LEFT OUTER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);

对应的伪代码如下，我们用黑体标示不同的部分：

outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
	inner_iter = iterator over tbl2 where col3 = outer_row.col3
	inner_row = inner_iter.next
	if inner_row
	while inner_row
	output [ outer_row.col1, inner_row.col2 ]
	inner_row = inner_iter.next
	end
else
	output [ outer_row.col1, NULL ]
	end
	outer_row = outer_iter.next
end

另一种可视化查询执行计划的方法是根据优化器执行的路径绘制出对应的“泳道图”。如图6-2所示，绘制了前面示例中内连接的泳道图，请从左至右，从上至下地看这幅图。

从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中，然后将这个临时表当作一个普通表对待（正如其名“派生表”）。MySQL在执行UNION查询时也使用类似的临时表，在遇到右外连接的时候，MySQL将其改写成等价的左外连接。简而言之，当前版本的MySQL会将所有的查询类型都转换成类似的执行计划。

执行计划

和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOWWARNINGS，就可以看到重构出的查询。
任何多表查询都可以使用一棵树表示，例如，可以按照图6-3执行一个四表的关联操作。

在计算机科学中，这被称为一颗平衡树。但是，这并不是MySQL执行查询的方式。正如我们前面章节介绍的，MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以，MySQL的执行计划总是如图6-4所示，是一棵左测深度优先的树。

关联查询优化器

关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。
下面的查询可以通过不同顺序的关联最后都获得相同的结果：

mysql> SELECT film.film_id, film.title, film.release_year, actor.actor_id,
-> actor.first_name, actor.last_name
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> INNER JOIN sakila.actor USING(actor_id);

容易看出，可以通过一些不同的执行计划来完成上面的查询。例如，MySQL可以从film表开始，使用film_actor表的索引film_id来查找对应的actor_id值，然后再根据actor表的主键找到对应的记录。Oracle用户会用下面的术语描述：“film表作为驱动表先查找fle_actor表，然后以此结果为驱动表再查找actor表”。这样做效率应该会不错，我们再使用EXPLAIN看看MySQL将如何执行这个查询：

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY
key_len: 2
ref: sakila.actor.actor_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: film
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.film_id
rows: 1
Extra:

这和我们前面给出的执行计划完全不同。MySQL从actor表开始（我们从上面的EXPLAIN结果的第一行输出可以看出这点），然后与我们前面的计划按照相反的顺序进行关联。这样是否效率更高呢？我们来看看，我们先使用STRAIGHT_JOIN关键字，按照我们之前的顺序执行，这里是对应的EXPLAIN输出结果：

mysql> EXPLAIN SELECT STRAIGHT_JOIN film.film_id...\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY,idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.actor_id
rows: 1
Extra:

我们来分析一下为什么MySQL会将关联顺序倒转过来：可以看到，关联顺序倒转后的第一个关联表只需要扫描很少的行数。在两种关联顺序下，第二个和第三个关联表都是根据索引查询，速度都很快，不同的是需要扫描的索引项的数量是不同的：

• 将film表作为第一个关联表时，会找到951条记录，然后对film_actor和actor表进行嵌套循环查询。
• 如果MySQL选择首先扫描actor表，只会返回200条记录进行后面的嵌套循环查询。

换句话说，倒转的关联顺序会让查询进行更少的嵌套循环和回溯操作。为了验证优化器的选择是否正确，我们单独执行这两个查询，并且看看对应的Last_query_cost状态值。我们看到倒转的关联顺序的预估成本为241，而原来的查询的预估成本为1 154。
这个简单的例子主要想说明MySQL是如何选择合适的关联顺序来让查询执行的成本尽可能低的。重新定义关联的顺序是优化器非常重要的一部分功能。不过有的时候，优化器给出的并不是最优的关联顺序。这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为的最优的关联顺序执行——不过老实说，人的判断很难那么精准。绝大多数时候，优化器做出的选择都比普通人的判断要更准确。

排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。
在第3章中我们已经看到MySQL如何通过索引进行排序。当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此。
如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。
MySQL有如下两种排序算法：

• 两次传输排序（旧版本使用）
  读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
  这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次数据传输的成本非常高。当使用的是MyISAM表的时候，成本可能会更高，因为MyISAM使用系统调用进行数据的读取（MyISAM非常依赖操作系统对数据的缓存）。不过这样做的优点是，在排序的时候存储尽可能少的数据，这就让“排序缓冲区”中可能容纳尽可能多的行数进行排序。
  单次传输排序（新版本使用）
  先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。这个算法只在MySQL 4.1和后续更新的版本才引入。因为不再需要从数据表中读取两次数据，对于I/O密集型的应用，这样做的效率高了很多。另外，相比两次传输
  排序，这个算法只需要一次顺序I/O读取所有的数据，而无须任何的随机I/O。缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间，而这些列对排序操作本身来说是没有任何作用的。因为单条排序记录很大，所以可能会有更多的排序块需要合并。
  很难说哪个算法效率更高，两种算法都有各自最好和最糟的场景。当查询需要所有列的总长度不超过参数max_length_for_sort_data时，MySQL使用“单次传输排序”，可以通过调整这个参数来影响MySQL排序算法的选择。关于这个细节，可以参考第8章“文件排序优化”。

MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。
在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句中的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort”。除此之外的所有情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下，在MySQL的EXPLAIN结果的Extra字段可以看到“Using temporary;Using filesort”。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。
MySQL 5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候，例如使用了LIMIT子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

6.4.4 查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。
相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。查询中的每一个表由一个handler的实例表示。前面我们有意忽略了这点，实际上，MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。
存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像“搭积木”一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条目的下一个条目的功能，有了这两个功能我们就可以完成全索引扫描的操作了。这种简单的接口模式，让MySQL的存储引擎插件式架构成为可能，但是正如前面的讨论，也给优化器带来了一定的限制。
并不是所有的操作都由handler完成。例如，当MySQL需要进行表锁的时候。handler可能会实现自己的级别的、更细粒度的锁，如InnoDB就实现了自己的行基本锁，但这并不能代替服务器层的表锁。正如我们第1章所介绍的，如果是所有存储引擎共有的特性则由服务器层实现，比如时间和日期函数、视图、触发器等。为了执行查询，MySQL只需要重复执行计划中的各个操作，直到完成所有的数据查询。

6.4.5 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。
如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
这样处理有两个好处：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。
结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。

6.5 MySQL查询优化器的局限性

6.5.1 关联子查询

MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。例如，我们希望找到Sakila数据库中，演员Penelope Guiness（他的actor_id为1）参演过的所有影片信息。很自然的，我们会按照下面的方式用子查询实现：
mysql> SELECT * FROM sakila.film
-> WHERE film_id IN(
-> SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);
因为MySQL对IN()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子
查询返回所有包含actor_id为1的film_id。一般来说，IN()列表查询速度很快，所以我们
会认为上面的查询会这样执行：

-- SELECT * FROM sakila.film-- SELECT GROUP_CONCAT(film_id) FROM
sakila.film_actor WHERE actor_id = 1;
-- Result:
1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

很不幸，MySQL不是这样做的。MySQL会将相关的外层表压到子查询中，它认为这样可以更高效率地查找到数据行。也就是说，MySQL会将查询改写成下面的样子：

SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);

这时，子查询需要根据film_id来关联外部表film，因为需要film_id字段，所以MySQL认为无法先执行这个子查询。通过EXPLAIN我们可以看到子查询是一个相关子查询（DEPENDENT SUBQUERY）（可以使用EXPLAIN EXTENDED来查看这个查询被改写成了什么样子）：

根据EXPLAIN的输出我们可以看到，MySQL先选择对file表进行全表扫描，然后根据返回的flm_id逐个执行子查询。如果是一个很小的表，这个查询糟糕的性能可能还不会引起注意，但是如果外层的表是一个非常大的表，那么这个查询的性能会非常糟糕。当然我们很容易用下面的办法来重写这个查询：

mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;

另一个优化的办法是使用函数GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表。有时这比上面的使用关联改写更快。因为使用IN()加子查询，性能经常会非常糟，所以通常建议使用EXISTS()等效的改写查询来获取更好的效率。下面是另一种改写IN()加子查询的办法：

mysql> SELECT * FROM sakila.film
-> WHERE EXISTS(
-> SELECT * FROM sakila.film_actor WHERE actor_id = 1
-> AND film_actor.film_id = film.film_id);

这里讨论的优化器的限制直到Oracle推出的MySQL 5.5都一直存在。MySQL的另一个分支MariaDB则在原有的优化器的基础上做了大量的改进，例如这里提到的IN()加子查询改进。

如何用好关联子查询

mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index

一般会建议使用左外连接（LEFT OUTER JOIN）重写该查询，以代替子查询。理论上，改写后MySQL的执行计划完全不会改变。我们来看这个例子：

mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***********************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row **********************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists

可以看到，这里的执行计划基本上一样，下面是一些微小的区别：

• 表flm_actor的访问类型一个是DEPENDENT SUBQUERY，而另一个是SIMPLE。这个不同是由于语句的写法不同导致的，一个是普通查询，一个是子查询。这对底层存储引擎接口来说，没有任何不同。
• 对film表，第二个查询的Extra中没有“Using where”，但这不重要，第二个查询的USING子句和第一个查询的WHERE子句实际上是完全一样的。
• 在第二个表film_actor的执行计划的Extra列有“Not exists”。这是我们前面章节中提到的提前终止算法（early-termination algorithm），MySQL通过使用“Not exists”优化来避免在表film_actor的索引中读取任何额外的行。这完全等效于直接编写NOTEXISTS子查询，这个执行计划中也是一样，一旦匹配到一行数据，就立刻停止扫描。

所以，从理论上讲，MySQL将使用完全相同的执行计划来完成这个查询。现实世界中，我们建议通过一些测试来判断使用哪种写法速度会更快。针对上面的案例，我们对两种写法进行了测试，表6-1中列出了测试结果。

我们的测试显示，使用子查询的写法要略微慢些！
不过每个具体的案例会各有不同，有时候子查询写法也会快些。例如，当返回结果中只有一个表中的某些列的时候。听起来，这种情况对于关联查询效率也会很好。具体情况具体分析，例如下面的关联，我们希望返回所有包含同一个演员参演的电影，因为一个电影会有很多演员参演，所以可能会返回一些重复的记录：

mysql> SELECT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
我们需要使用DISTINCT和GROUP BY来移除重复的记录：
mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);

但是，回头看看这个查询，到底这个查询返回的结果集意义是什么？至少这样的写法会让SQL的意义很不明显。如果使用EXISTS则很容易表达“包含同一个参演演员”的逻辑，而且不需要使用DISTINCT和GROUP BY，也不会产生重复的结果集，我们知道一旦使用了DISTINCT和GROUP BY，那么在查询的执行过程中，通常需要产生临时中间表。下面我们用子查询的写法替换上面的关联：

mysql> SELECT film_id FROM sakila.film
-> WHERE EXISTS(SELECT * FROM sakila.film_actor
-> WHERE film.film_id = film_actor.film_id);

再一次，我们需要通过测试来对比这两种写法，哪个更快一些。测试结果参考表6-2。

在这个案例中，我们看到子查询速度要比关联查询更快些。
通过上面这个详细的案例，主要想说明两点：一是不需要听取那些关于子查询的“绝对真理”，二是应该用测试来验证对子查询的执行计划和响应时间的假设。
最后，关于子查询我们需要提到的是一个MySQL的bug。在MYSQL 5.1.48和之前的版本中，下面的写法会锁住table2中的一条记录：

SELECT ... FROM table1 WHERE col = (SELECT ... FROM table2 WHERE ...);

如果遇到该bug，子查询在高并发情况下的性能，就会和在单线程测试时的性能相差甚远。这个bug的编号是46947，虽然这个问题已经被修复了，但是我们仍然要提醒读者：不要主观猜测，应该通过测试来验证猜想。

6.5.2 UNION的限制

有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表都存放到同一个临时表中，然后再取出前20行记录：

(SELECT first_name, last_name
FROM sakila.actor
ORDER BY last_name)
UNION ALL
(SELECT first_name, last_name
FROM sakila.customer
ORDER BY last_name)
LIMIT 20;

这条查询将会把actor中的200条记录和customer表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。可以通过在UNION的两个子查询中分别加上一个LIMIT20来减少临时表中的数据：

(SELECT first_name, last_name
FROM sakila.actor
ORDER BY last_name
LIMIT 20)
UNION ALL
(SELECT first_name, last_name
FROM sakila.customer
ORDER BY last_name
LIMIT 20)
LIMIT 20;

现在中间的临时表只会包含40条记录了，除了性能考虑之外，在这里还需要注意一点：从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要加上一个全局的ORDER BY和LIMIT操作。

6.5.3 索引合并优化

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

6.5.4 等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的IN()列表，而MySQL优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个表的某个列相关联。
那么优化器会将IN()列表都复制应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。在本书写作的时候，除了修改MySQL源代码，目前还没有什么办法能够绕过该问题（不过这个问题很少会碰到）。

6.5.5 并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是MySQL做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。

6.5.6 哈希关联

在本书写作的时候，MySQL并不支持哈希关联——MySQL的所有关联都是嵌套循环关联。不过，可以通过建立一个哈希索引来曲线地实现哈希关联。如果使用的是Memory存储引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联。可以参考第5章的“创建自定义哈希索引”部分。另外，MariaDB已经实现了真正的哈希关联。

6.5.7 松散索引扫描

由于历史原因，MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中很少数的几个，MySQL仍需要扫描这段索引中每一个条目。下面我们通过一个示例说明这点。假设我们有如下索引（a，b），有下面的查询：
mysql> SELECT … FROM tbl WHERE b BETWEEN 2 AND 3;
因为索引的前导字段是列a，但是在查询中只指定了字段b，MySQL无法使用这个索引，从而只能通过全表扫描找到匹配的行，如图6-5所示。

了解索引的物理结构的话，不难发现还可以有一个更快的办法执行上面的查询。索引的物理结构（不是存储引擎的API）使得可以先扫描a列第一个值对应的b列的范围，然后再跳到a列第二个不同值扫描对应的b列的范围。图6-6展示了如果由MySQL来实现这个过程会怎样。

注意到，这时就无须再使用WHERE子句过滤，因为松散索引扫描已经跳过了所有不需要的记录。
上面是一个简单的例子，除了松散索引扫描，新增一个合适的索引当然也可以优化上述查询。但对于某些场景，增加索引是没用的，例如，对于第一个索引列是范围条件，第二个索引列是等值条件的查询，靠增加索引就无法解决问题。
MySQL 5.0之后的版本，在某些特殊的场景下是可以使用松散索引扫描的，例如，在一个分组查询中需要找到分组的最大值和最小值：

mysql> EXPLAIN SELECT actor_id, MAX(film_id)
-> FROM sakila.film_actor
-> GROUP BY actor_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: range
possible_keys: NULL
key: PRIMARY
key_len: 2
ref: NULL
rows: 396
Extra: Using index for group-by

在EXPLAIN中的Extra字段显示“Using index for group-by”，表示这里将使用松散索引扫描，不过如果MySQL能写上“loose index probe”，相信会更好理解。
在MySQL很好地支持松散索引扫描之前，一个简单的绕过问题的办法就是给前面的列加上可能的常数值。在前面索引案例学习的章节中，我们已经看到这样做的好处了。
在MySQL 5.6之后的版本，关于松散索引扫描的一些限制将会通过“索引条件下推（index condition pushdown）”的方式解决。

6.5.8 最大值和最小值优化

对于MIN()和MAX()查询，MySQL的优化做得并不好。这里有一个例子：
mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name=‘PENELOPE’;
因为在first_name字段上并没有索引，因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描，那么理论上，当MySQL读到第一个满足条件的记录的时候，就是我们需要找的最小值了，因为主键是严格按照actor_id字段的大小顺序排列的。但是MySQL这时只会做全表扫描，我们可以通过查看SHOW STATUS的全表扫描计数器来验证这一点。一个曲线的优化办法是移除MIN()，然后使用LIMIT来将查询重写如下：
mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
-> WHERE first_name = ‘PENELOPE’ LIMIT 1;
这个策略可以让MySQL扫描尽可能少的记录数。如果你是一个完美主义者，可能会说这个SQL已经无法表达她的本意了。一般我们通过SQL告诉服务器我们需要什么数据，由服务器来决定如何最优地获取数据，不过在这个案例中，我们其实是告诉MySQL如何去获取我们需要的数据，通过SQL并不能一眼就看出我们其实是想要一个最小值。确实如此，有时候为了获得更高的性能，我们不得不放弃一些原则。

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

MySQL不允许对同一张表同时进行查询和更新。这其实并不是优化器的限制，如果清楚MySQL是如何执行查询的，就可以避免这种情况。下面是一个无法运行的SQL，虽然这是一个符合标准的SQL语句。这个SQL语句尝试将两个表中相似行的数量记录到字段cnt中：

mysql> UPDATE tbl AS outer_tbl
-> SET cnt = (
-> SELECT count(*) FROM tbl AS inner_tbl
-> WHERE inner_tbl.type = outer_tbl.type
-> );
ERROR 1093 (HY000): You can't specify target table 'outer_tbl' for update in
FROM
clause

可以通过使用生成表的形式来绕过上面的限制，因为MySQL只会把这个表当作一个临时表来处理。实际上，这执行了两个查询：一个是子查询中的SELECT语句，另一个是多表关联UPDATE，只是关联的表是一个临时表。子查询会在UPDATE语句打开表之前就完成，所以下面的查询将会正常执行：

mysql> UPDATE tbl
-> INNER JOIN(www.it-eboo
-> SELECT type, count(*) AS cnt
-> FROM tbl
-> GROUP BY type
-> ) AS der USING(type)
-> SET tbl.cnt = der.cnt;

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

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。下面将列举一些常见的提示，并简单地给出什么时候使用该提示。通过在查询中加入相应的提示，就可以控制该查询的执行计划。关于每个提示的具体用法，建议直接阅读MySQL官方手册。有些提示和版本有直接关系。可以使用的一些提示如下：

HIGH_PRIORITY和LOW_PRIORITY

这个提示告诉MySQL，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。
HIGH_PRIORITY用于SELECT语句的时候，MySQL会将此SELECT语句重新调度到所有正在等待表锁以便修改数据的语句之前。实际上MySQL是将其放在表的队列的最前面，而不是按照常规顺序等待。HIGH_PRIORITY还可以用于INSERT语句，其效果只是简单地抵消了全局LOW_PRIORITY设置对该语句的影响。
LOW_PRIORITY则正好相反：它会让该语句一直处于等待状态，只要队列中还有需要访问同一个表的语句——即使是那些比该语句还晚提交到服务器的语句。这就像一个过于礼貌的人站在餐厅门口，只要还有其他顾客在等待就一直不进去，很明显这容易把自己给饿坏。LOW_PRIORITY提示在SELECT、INSERT、UPDATE和DELETE语句中都可以使用。
这两个提示只对使用表锁的存储引擎有效， 千万不要在InnoDB或者其他有细粒度锁机制和并发控制的引擎中使用 。即使是在MyISAM中使用也要注意，因为这两个
提示会导致并发插入被禁用，可能会严重降低性能。
HIGH_PRIORITY和LOW_PRIORITY经常让人感到困惑。这两个提示并不会获取更多资源让查询“积极”工作，也不会少获取资源让查询“消极”工作。它们只是简单地控制了MySQL访问某个数据表的队列顺序。

DELAYED

这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。日志系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制：并不是所有的存储引擎都支持这样的做法；并且该提示会导致函数LAST_INSERT_ID()无法正常工作。

STRAIGHT_JOIN

这个提示可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。
当MySQL没能选择正确的关联顺序的时候，或者由于可能的顺序太多导致MySQL无法评估所有的关联顺序的时候，STRAIGHT_JOIN都会很有用。在后面这种情况，MySQL可能会花费大量时间在“statistics”状态，加上这个提示则会大大减少优化器的搜索空间。
可以先使用EXPLAIN语句来查看优化器选择的关联顺序，然后使用该提示来重写查询，再看看它的关联顺序。当你确定无论怎样的where条件，某个固定的关联顺序始终是最佳的时候，使用这个提示可以大大提高优化器的效率。但是在升级MySQL版本的时候，需要重新审视下这类查询，某些新的优化特性可能会因为该提示而失效。

SQL_SMALL_RESULT和SQL_BIG_RESULT

这两个提示只对SELECT语句有效。它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

SQL_BUFFER_RESULT

这个提示告诉优化器将查询结果放入到一个临时表，然后尽可能快地释放表锁。这和前面提到的由客户端缓存结果不同。当你没法使用客户端缓存的时候，使用服务器端的缓存通常很有效。带来的好处是无须在客户端上消耗太多的内存，还可以尽可能快地释放对应的表锁。代价是，服务器端将需要更多的内存。

SQL_CACHE和SQL_NO_CACHE

这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中。

SQL_CALC_FOUND_ROWS

严格来说，这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。它会让MySQL返回的结果集包含更多的信息。查询中加上该提示MySQL会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集。可以通过函数FOUND_ROW()获得这个值。（参阅后面的“SQL_CALC_FOUND_ROWS优化”部分，了解下为什么不应该使用该提示。）

FOR UPDATE和LOCK IN SHARE MODE

这也不是真正的优化器提示。这两个提示主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于INSERT…SELECT语句是不需要这两个提示的，因为对于MySQL 5.0和更新版本会默认给这些记录加上读锁。（可以禁用该默认行为，但不是个好主意，在后面关于复制和备份的章节中将解释这一点。）
唯一内置的支持这两个提示的引擎就是InnoDB。另外需要记住的是，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他地锁定行，因为行的版本信息保存在主键中。
糟糕的是，这两个提示经常被滥用，很容易造成服务器的锁争用问题，后面章节我们将讨论这点。应该尽可能地避免使用这两个提示，通常都有其他更好的方式可以实现同样的目的。

USE INDEX、IGNORE INDEX和FORCE INDEX

这几个提示会告诉优化器使用或者不使用哪些索引来查询记录（例如，在决定关联顺序的时候使用哪个索引）。在MySQL 5.0和更早的版本，这些提示并不会影响到优化器选择哪个索引进行排序和分组，在MyQL 5.1和之后的版本可以通过新增选项FOR ORDER BY和FOR GROUP BY来指定是否对排序和分组有效。FORCE INDEX和USE INDEX基本相同，除了一点：FORCE INDEX会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际上该索引用处不大。当发现优化器选择了错误的索引，或者因为某些原因（比如在不使用ORDER BY的时候希望结果有序）要使用另一个索引时，可以使用该提示。在前面关于如何使用LIMIT高效地获取最小值的案例中，已经演示过这种用法。
在MySQL 5.0和更新版本中，新增了一些参数用来控制优化器的行为：

optimizer_search_depth

这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于“Statistics”状态，那么可以考虑调低此参数。

optimizer_prune_level

该参数默认是打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

optimizer_switch

这个变量包含了一些开启/关闭优化器特性的标志位。例如在MySQL 5.1中可以通过这个参数来控制禁用索引合并的特性。
前两个参数是用来控制优化器可以走的一些“捷径”。这些捷径可以让优化器在处理非常复杂的SQL语句时，仍然可以很高效，但这也可能让优化器错过一些真正最优的执行计划。所以应该根据实际需要来修改这些参数。

6.7 优化特定类型的查询

本节介绍的多数优化技巧都是和特定的版本有关的，所以对于未来MySQL的版本未必适用。毫无疑问，某一天优化器自己也会实现这里列出的部分或者全部优化技巧。

6.7.1 优化COUNT()查询

COUNT()的作用

COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。 如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数 。
COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT（）的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。
我们发现一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT（*），这样写意义清晰，性能也会很好。

6.7.2 优化关联查询

• 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则 只需要在关联顺序中的第二个表的相应列上创建索引 。
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果等。

6.7.3 优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替，至少当前的MySQL版本需要这样。本章的前面章节已经详细介绍了这点。“尽可能使用关联”并不是绝对的，如果使用的是MySQL 5.6或更新的版本或者MariaDB，那么就可以直接忽略关于子查询的这些建议了。

6.7.4 优化GROUP BY和DISTINCT

在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。对于任何查询语句，这两种策略的性能都有可以提升的地方。可以通过使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。
如果需要对关联查询做分组（GROUP BY），并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。例如下面的查询效率不会很好：

mysql> SELECT actor.first_name, actor.last_name, COUNT(*)
-> FROM sakila.film_actor
-> INNER JOIN sakila.actor USING(actor_id)
-> GROUP BY actor.first_name, actor.last_name;
如果查询按照下面的写法效率则会更高：
mysql> SELECT actor.first_name, actor.last_name, COUNT(*)
-> FROM sakila.film_actor
-> INNER JOIN sakila.actor USING(actor_id)
-> GROUP BY film_actor.actor_id;

使用actor.actor_id列分组的效率甚至会比使用film_actor.actor_id更好。这一点通过简单的测试即可验证。
这个查询利用了演员的姓名和ID直接相关的特点，因此改写后的结果不受影响，但显然不是所有的关联语句的分组查询都可以改写成在SELECT中直接使用非分组列的形式的。甚至可能会在服务器上设置SQL_MODE来禁止这样的写法。

如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。

优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。可以通过EXPLAIN来观察其执行计划，特别要注意分组是否是通过文件排序或者临时表实现的。然后再去掉WITH ROLLUP子句看执行计划是否相同。也可以通过本节前面介绍的优化器提示来固定执行计划。
很多时候，如果可以，在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果。
最好的办法是尽可能的将WITH ROLLUP功能转移到应用程序中处理。

6.7.5 优化LIMIT分页

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。
一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT 1000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列 。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询：
mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50,5;
如果这个表非常大，那么这个查询最好改写成下面的样子：

mysql> SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);

这里的“ 延迟关联 ”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

延迟关联 ： 通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数

6.7.7 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询）。
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端）。

6.7.8 静态查询分析

Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康检查。它能检测出许多常见的问题，诸如我们前面介绍的内容。

6.7.9 使用用户自定义变量