《高性能MySQL》读书笔记（1~6章）

第1章 MySQL架构与历史

MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

1.1 MySQL逻辑架构

• 顶层服务层并非MySQL独有,大多数基于网络对外提供服务的中间件都有这一层,主要是连接处理、授权认证、安全等。
• 第二层是MySQL的服务器层,大多数核心服务功能都在这一层,包括解析、优化、查询、缓存以及所有内置函数,所有跨存储引擎的功能（即存储引擎共有的功能）都在这一层实现:存储过程、触发器、视图。
• 第三层是存储引擎层,不同的存储引擎向上提供相同的api的各自实现,接口屏蔽了存储引擎之间的差异,存储引擎不会解析SQL,只是简单的响应上层的请求。

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程。

当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

1.1.2 优化与执行

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

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

1.2 并发控制

本章的目的是讨论MySQL在两个层面的并发控制：服务器层与存储引擎层。

1.2.1 读写锁

1.2.2 锁粒度

一种理想的锁方式是，尽量只锁定需要修改的资源，而不是所有资源。锁定的数据量越小，并发程度越高。 不过，会因此增加系统开销。

锁策略：在锁开销和数据安全性间寻求平衡。

表锁

• 是MySQl最基本的锁策略也是开销最小的策略,它会锁定整张表；
• 特定场景，性能良好，写优先；
• 服务器会为ALTER TABLE之类的语句加表锁,忽略具体表存储引擎的锁机制。

行级锁

• 行级锁可以最大程度的支持并发处理；
• 行级锁只在存储引擎层实现(InnoDB、XtraDB等)。
  
  

1.3 事务

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

事务的四个标准特征（ACID） ：

• 原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作；
• 一致性（consistency）数据库总是从一个一致性的状态转换到另外一个一致性的状态；
• 隔离性（isolation）DBMS保证并发执行的多个事务之间互相不受影响；
• 持久性（durability）一旦事务提交，则其所做的修改就会永久保存到数据库中 。

---

原子性：记录之前的版本，允许回滚。

一致性：事务开始和结束之间的中间状态不会被其他事务看到。

隔离性：适当的破坏一致性来提升性能与并行度，例如：最终一致~=读未提交。

持久性：每一次的事务提交后就会保证不会丢失。

[如何理解数据库事务中的一致性的概念？ - 沈询的回答 - 知乎]
https://www.zhihu.com/question/31346392/answer/156411587

---

一个实现了ACID的数据库，相比没有实现ACID的数据库，通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能。

1.3.1 隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

• READ UNCOMMITTED（未提交读）

  事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，也被称为脏读（Dirty Read），这个级别会导致很多问题，很少使用；

• READ COMMITTED（提交读）

  大多数数据库系统默认的隔离级别(但MySQL不是)。满足隔离性的简单定义：一个事务从开始直到提交之前，所做修改对其他事务都是不可见的，叫不可重复读，因为两次执行同样的查询，可能得到不一样的结果。不可重复读：事务A查询数据之后，数据被事务B修改，导致事务A两次读取数据不一致。

• REPEATABLE READ（可重复读）

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

• SERIALIZABLE（可串行化）

  最高级别，通过强制事务串行执行，避免了幻读问题，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题 ，很少使用。

https://zhuanlan.zhihu.com/p/118658549

---

脏读：读取未提交数据
A事务读取了B事务还未提交的修改值，B在提交前进行了回滚，那么A事务读取到的数据就是脏数据。

不可重复读：前后多次读取，数据内容不一致
事务A进行了两次数据读取，两次读取中间事务B提交了数据修改，导致A的两次读取到的数据不一致，即为不可重复读。

幻读：前后多次读取，数据总量不一致
事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

---

1.3.2 死锁

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

为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。

• 越复杂的系统，比如InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。

• 还有一种解决方式，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。

InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回滚算法）。

死锁的产生有双重原因：

• 有些是因为真正的数据冲突，这种情况通常很难避免，
• 但有些则完全是由于存储引擎的实现方式导致的。

1.3.3 事务日志

事务日志可以帮助提高事务的效率。

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

1.3.4 MySQL中的事务

MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster，另外还有一些第三方存储引擎也支持事务，比较知名的包括XtraDB和PBXT。

自动提交

mysql默认采用自动提交模式。也就是说，如果不是显示地开始一个事务，则每个查询都当做一个事务执行提交操作。 可通过AUTOCOMMIT变量来启用/取消。对于非事务型表没有影响。

MySQL可以通过set transaction isolation level设置隔离级别，新的级别在下一个事务开始时生效，配置文件设置整个库的，也可只改变当前会话的隔离级别set session transaction isolation level read committed;

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

MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。

在非事务型的表上执行事务相关操作的时候，MySQL通常不会发出提醒，也不会报错。有时候只有回滚的时候才会发出一个警告：“某些非事务型的表上的变更不能被回滚”。

隐式和显式锁定

InnoDB会根据隔离级别在需要的时候自动加锁。虽然有显示锁定语句，但不要使用。

1.4 多版本并发控制

MySQL大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能考虑，实现了多版本并发控制(MVCC)，在很多情况下避免了加锁操作，因此开销更低。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。不同的MVCC实现方式不同,典型的有乐观并发控制和悲观并发控制。

InnoDB的MVCC简化版原理：

每行记录后有两个隐藏列,一个列保存了行的创建时间、一个保存行的过期时间(删除时间),两列存储的都不是实际时间值而是系统版本号。 每开始一个新的事务,系统版本号都会递增,事务开始时刻的系统版本号会作为事务的版本号。下面看一下在REPEATABLE READ 隔离级别下，MVCC具体是如何操作的。

• SELECT

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

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

  • b. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

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

• INSERT

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

• DELETE

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

• UPDATE

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

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

1.5 MySQL的存储引擎

在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录。创建表时，MysQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如创建一个名为MyTable的表，MySQL会在MyTable.frm文件中保存该表的定义。因为MySQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在Windows中，大小写是不敏感的；而在类Unix中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。

1.5.1 InnoDB存储引擎

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

InnoDB概览

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份。MySQL的其他存储引擎不支持热备份。

1.5.2 MyISAM存储引擎

MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

存储

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为护展名。MyISAM表可以包含动态或者静态（长度固定）行。MySQL会根据表的定义来决定采用何种行格式。

特性

• 加锁与并发：MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录。
• 修复：命令有check table mytable，repair table mytable。
• 索引的特性：对于MyISAM表，即使BOLB和TEXT等长字段，也可以基于前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
• 延迟更新索引键：DELAY_KEY_WRITE选项，不会立刻清理缓存区，只有在清理缓存区或者关闭表的时候才会将对应的索引块写入到磁盘。

MyISAM压缩表

如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。

压缩表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩），压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘1/0，从而提升查询性能。压缩表也支持索引，但索引也是只读的。压缩时表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表（甚至也不解压行所在的整个页面）。

MyISAM性能

MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。MyISAM有一些服务器级别的性能扩展限制，比如对索引键缓冲区（key cache）的Mutex锁，MariaDB基于段（segment）的索引键缓冲区机制来避免该问题。但MyISAM最典型的性能问题还是表锁的问题。

1.5.3 MySQL内建的其他存储引擎

Archive引擎：是一个针对高速插入和压缩做了优化的简单引擎。

CSV引擎：CSV引擎可以将普通的CSV文件（逗号分割值的文件）作为MysQL的表来处理，但这种表不支持萦引。

Federated引擎：访问其他MySQL服务器的代理，创建远程mysql的客户端连接将查询传输到远程服务器执行，提取发送需要的数据，默认禁用。

Memory引擎：快速访问不会被修改的数据，数据保存在内存、不需要磁盘IO，表结构重启后还在但数据没了 。

1.5.4 第三方存储引擎

Infobright是最有名的面向列的存储引擎。在非常大的数据量（数十TB）时，该引擎工作良好。Infobright是为数据分析和数据仓库应用设计的。

1.5.5 选择合适的引擎

除非需要用到某些InnoDB不具备的特性，且无办法可以替代，否则优先选择InnoDB引擎。

例如，如果要用到全文索引，建议优先考虑InnoDB加上Sphinx的组合，而不是使用支持全文索引的MyISAM。当然，如果不需要用到InnoDB的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。

除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。

如果应用需要不同的存储引擎，请先考虑以下几个因素。

• 事务
  如果应用需要事务支持，那么InnoDB（或者XtraDB）是目前最稳定并且经过验证的选择。如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。一般日志型的应用比较符合这一特性。

• 备份

  备份的需求也会影响存储引擎的选择。如果可以定期地关闭服务器来执行备份，那么备份的因素可以忽略。反之，如果需要在线热备份，那么选择InnoDB就是基本的要求。

• 崩溃恢复
  数据量比较大的时候，系统崩溃后如何快速地恢复是一个需要考虑的问题。相对而言，MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢。因此，即使不需要事务支持，很多人也选择InnoDB引擎，这是一个非常重要的因素。

• 特有的特性
  最后，有些应用可能依赖一些存储引擎所独有的特性或者优化，比如很多应用依赖聚簇索引的优化。另外，MySQL中也只有MyISAM支持地理空间搜索。如果一个存储引擎拥有一些关键的特性，同时却又缺乏一些必要的特性，那么有时候不得不做折中的考虑，或者在架构设计上做一些取舍。某些存储引擎无法直接支持的特性，有时候通过变通也可以满足需求。

日志型应用

日志型应用的插入速度有很高的要求，数据库不能成为瓶颈。MyISAM或者Archive存储引擎对这类应用比较合适，因为它们开销低，而且插入速度非常快。

但如果需要对记录的日志做分析报表，生成报表的SQL很有可能会导致插入效率明显降低，有两种解决办法：

• 一种解决方法，是利用MySQL内置的复制方案将数据复制一份到备库，然后在备库上执行比较消耗时间和CPU的查询。
• 另外一种方法，在日志记录表的名字中包含年和月的信息，比如web_logs_2012_01或者weblogs_2012_jan，这样可以在已经没有插入操作的历史表上做频繁的查询操作，而不会干扰到最新的当前表上的插入操作。

只读或者大部分情况下只读的表

如果不介意MyISAM的崩溃恢复问题，选用MyISAM引擎是合适的。

一个值得推荐的方式，是在性能测试环境模拟真实的环境，运行应用，然后拔下电源模拟崩溃测试。对崩溃恢复的第一手测试经验是无价之宝，可以避免真的碰到成溃时手足无措。

不过，不要轻易相信"MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。

订单处理

如果涉及订单处理，那么支持事务就是必要选项。半完成的订单是无法用来吸引用户的。另外一个重要的考虑点是存储引擎对外键的支持情况。InnoDB是订单处理类应用的最佳选择。

CD-ROM应用

如果要发布一个基于CD-ROM或者DVD-ROM并且使用MySQL数据文件的应用，可以考虑使用MyISAM表或者MyISAM压缩表。MyISAM压缩表比未压缩的表要节约很多空间，但压缩表是只读的。在某些应用中这可能是个大问题。但如果数据放到只读介质的场景下，压缩表的只读特性就不是问题，就没有理由不采用压缩表了。

大数据量

什么样的数据量算大？我们创建或者管理的很多InnoDB数据库的数据量在3~5TB之间，或者更大，这是单台机器上的量，不是一个分片（shard）的量。在这样的数据量下，如果采用MyISAM，崩溃后的恢复就是一个噩梦。
如果数据量继续增长到10TB以上的级别，可能就需要建立数据仓库。Infobright是MysQL数据仓库最成功的解决方案。也有一些大数据库不适合Infobright，却可能适合TokuDB。

1.5.6 转换表引擎

有很多种方法可以将表的存储引擎转换成另外一种引擎。我们将讲述其中的三种方法。

ALTER TABLE

将表从一个引擎修改为另一个引擎最简单的办法是使用ALTER TABLE语句。下面的语句mytable的引擎改为InnoDB：

mysql> ALTER TABLE mytable ENGINE = InnoDB;

上述语法可以适用任何存储引擎。但有一个问题：需要执行很长时间。MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁。所以，在繁忙的表上执行此操作要特别小心。

如果转换表的存储引擎，将会失去和原引擎相关的所有特性。例如，如果将一张InnoDB表转换为MyISAM，然后再转换回InnoDB，原InnoDB表上所有的外键将丢失。

导出与导入

为了更好地控制转换的过程，可以使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表名，即使它们使用的是不同的存储引擎。同时要注意mysqldump默认会自动在CREATE TABLE语句前加上DROP TABLE语句，不注意这一点可能会导致数据丢失。

创建与查询（CREATE和SELECT）

第三种转换的技术综合了第一种方法的高效和第二种方法的安全。不需要导出整个表的数据，而是先创建一个新的存储引擎的表，然后利用INSERT……SELECT语法来导数据：

数据量不大的话，这样做工作得很好。如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操作，以避免大事务产生过多的undo，假设有主键字段id，重复运行以下语句（最小值x和最大值y进行相应的替换）将数据导入到新表：

这样操作完成以后，新表是原表的一个全量复制，原表还在，如果需要可以删除原表。如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。

Percona Toolkit提供了一个pt-online-schema-change的工具（基于Facebook的在线schema变更技术），可以比较简单、方便地执行上述过程，避免手工操作可能导致的失误和烦琐。

第2章 MySQL基准测试

基准测试是针对系统设计的一种压力测试。

基准测试可以完成以下工作，或者更多：

• 验证基于系统的一些假设，确认这些假设是否符合实际情况。
• 重现系统中的某些异常行为，以解决这些异常。
• 测试系统当前的运行情况。
• 模拟比当前系统更高的负载，以找出系统随着压力增加而可能遇到的扩展性瓶颈。
• 测试不同的硬件、软件和操作系统配置。
• 证明新采购的设备是否配置正确。

2.2 基准测试的策略

基准测试有两种主要的策略：一是针对整个系统的整体测试，另外是单独测试MySQL。这两种策略也被称为集成式（full-stack）以及单组件式（single-component）基准测试。

2.2.1 测试何种指标

• 吞吐量：指单位时间内事务处理数。常用的测试单位是每秒事务数(TPS),有些也采用每分钟事务(TPM)。
• 响应时间或延迟：用于测试任务所需的整体时间。最大响应时间通常意义不大，通常可以使用百分比响应时间（percentile response time）来替代最大响应时间。例如，如果95%的响应时间都小于等于5毫秒，则表示任务在95%的时间段内都可以在5毫秒之内完成。（将响应时间升序排序，95%处的时间就是95%百分比响应时间）
• 并发性：web服务器的并发性也不等于数据库的并发性。web服务器的并发性更准确的度量指标，应该是在任意时间有多少同时发生的并发请求。
• 可扩展性：可扩展性指的是，给系统增加一倍的工作，在理想情况下就能获得两倍的结果（即吞吐量增加一倍）。或者说，给系统增加一倍的资源（比如两倍的CPU数），就可以获得两倍的吞吐量。大多数系统是无法做到如此理想的线性扩展的。

2.3 基准测试方法

常见错误有：

• 忽略了系统预热（warm up）的过程。例如系统重启后马上进行测试。
• 使用错误的数据分布。例如使用均匀分布的数据测试，而系统的真实数据有很多热点区域（随机生成的测试数据通常无法模拟真实的数据分布）。
• 测试时间太短。

2.3.1 设计和规划基准测试

规划基准测试的第一步是提出问题并明确目标。然后决定是采用标准的基准测试，还是设计专用的测试。

设计专用的基准测试是很复杂的，往往需要一个选代的过程。首先需要获得生产数据集的快照，并且该快照很容易还原，以便进行后续的测试。

更好的办法是选择一个有代表性的时间段，比如高峰期的一个小时，或者一整天，记录生产系统上的所有查询。

即使不需要创建专用的基准测试，详细地写下测试规划也是必需的。测试规划应该记录测试数据、系统配置的步骤、如何测量和分析结果，以及预热的方案等。

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

一个常见的错误的测试方式是，只执行一系列短期的测试，比如每次60秒，并在此测试的基础上去总结系统的性能。

基准测试应该运行足够长的时间，这一点很重要。

2.3.3 获取系统性能和状态

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

需要记录的数据包括系统状态和性能指标，诸如CPU使用率、磁盘IO、网络流量统计、SHOW GLOBAL STATUS计数器等。

下面是一个收集MySQL测试数据的shell脚本：

mysql -e 需要开启root权限，意为执行sql操作；

date + 表示以指定格式输出date；

awk 前的 | 作为管道，awk作为过滤器，其中$1意为 以空格为分割符的第一个字段；

sql语句中的 ‘\G’ 意为将查询结果的每个字段打印到单独的行。

2.3.4 获得准确的测试结果

基于MySQL的默认配置的测试没有什么意义，因为默认配置是基于消耗很少内存的极小应用的。

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

下面是一个非常简单的shell脚本，演示了如何从前面的数据采集脚本采集到的数据中抽取时间维度信息。脚本的输入参数是采集到的数据文件的名字。

• 任何在BEGIN之后列出的操作（在{}内）将在Unix awk开始扫描输入之前执行，而END之后列出的操作将在扫描完全部的输入之后执行。因此，通常使用BEGIN来显示变量和预置（初始化）变量，使用END来输出最终结果。

• /pattern/ 表示过滤正则，如果该行匹配正则表达式pattern，则进行后面的操作。

• “$@”别偶是传给脚本的所以参数列表

2.3.6 绘图

前面写的脚本的输出都可以定制作为gnuplot或者R绘图的数据来源。

2.4 基准测试工具

2.4.1 集成式测试工具

ab、http_load、JMeter.

2.4.2 单组件式测试工具

有一些有用的工具可以测试MySQL和基于MySQL的系统的性能。2.5节将演示如何利用这些工具进行测试。

• MysQL Benchmark Suite（sql-bench）
  在MysQL的发行包中也提供了一款自己的基准测试套件，可以用于在不同数据库服务器上进行比较测试。它是单线程的，主要用于测试服务器执行查询的速度。结果会显示哪种类型的操作在服务器上执行得更快。

• Percona’s TPCC-MySQL Tool

  作者开发的一个类似TPC-C的基准测试工具集，其中有部分是专门为MySQL测试开发的。在评估大压力下MySQL的一些行为时，我们经常会利用这个工具进行测试（简单的测试，一般会采用sysbench替代）。

• sysbench

  sysbench是一款多线程系统压测工具。它可以根据影响数据库服务器性能的各种因素来评估系统的性能。例如，可以用来测试文件I/O、操作系统调度器、内存分配和传输速度、POSIX线程，以及数据库服务器等。
  sysbench支持Lua脚本语言，Lua对于各种测试场景的设置可以非常灵活。sysbench是我们非常喜欢的一种全能测试工具，支持MySQL，操作系统和硬件的硬件测试。
  
  
  
  

第3章 服务器性能剖析

3.1 性能优化简介

性能，为完成某件任务所需要的时间度量，性能即响应时间 。

性能优化就是在一定的工作负载下尽可能地降低响应时间。提升每秒查询量，其实只是吞吐量优化，可以看作性能优化的副产品。

如果目标是降低响应时间，就需要理解为什么服务器执行查询需要这么多时间，然后去减少或者消除那些对获得查询结果来说不必要的工作。无法测量就无法有效地优化。

性能剖析（profiling）是测量和分析时间花费在哪里的主要方法，一般有两个步骤：测量任务所花费的时间，对结果进行统计和排序 。

3.2 对应用程序进行性能剖析

性能瓶颈可能有很多影响因素：

• 外部资源，比如调用了外部的Web服务或者搜索引擎。
• 应用需要处理大量的数据，比如分析一个超大的XML文件。
• 在循环中执行昂贵的操作，比如滥用正则表达式。
• 使用了低效的算法，比如使用暴力搜索算法来查找列表中的项。

New Relic是一款优秀性能剖析工具，会插入到应用程序中进行性能剖析，将收集到的数据发送到一个基于Web的仪表盘，使用仪表盘可以更容易利用面向响应时间的方法分析应用性能。

3.3 剖析MySQL查询

3.3.1 剖析服务器负载

1. 慢查询日志：5.1后long_query_time为0可以捕获所有的查询，查询的响应时间单位可以做到微秒级
2. 生成剖析报告：pt-query-digest

3.3.2 剖析单条查询

• SHOW PROFILES;

  开启它可以让MySQL收集在执行语句的时候所使用的资源。 默认的是关闭的，但是会话级别可以开启这个功能。

• SHOW [GLOBAL] STATUS;

  显示每个服务器变量的名字和值 ，返回一些计数器。

3.4 诊断间歇性问题

间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。

尽量不要用试错的方式来解决问题，如果一时无法定位，可能是测量的方式不正确，或者测量的点选择有误，或者使用的工具不合适

3.4.1 确定单条查询问题还是服务器问题

• 使用SHOW GLOBAL STATUS

• 使用SHOW PROCESSLIST

  该命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态帮助识别出有问题的查询语句等。

• 使用查询日志

• 理解发现的问题：使得gnuplot或R，或其他绘图工具将结果绘制成图形

3.4.2 捕获诊断数据

1. 诊断触发器

   在问题出现时能够捕获数据，使用 pt-stalk工具 。

2. 需要收集什么样的数据

   尽可能收集所有能收集的数据，但只在需要的时间段内收集，工具有oprofile、strace、tcpdump、GDB堆栈跟踪。建议使用pt-collect。

3. 解释结果数据

   pt-mysql-summary、pt-summary输出结果打包，pt-sift得到样本汇总信息，pt-pmp 可以将类似的堆栈跟踪输出合并到一起，然后通过sort|uniq|sort将最常见的条目在最前面输出。

sort | uniq -c | sort -nk可以将相同的记录合并并根据数量排序。

第4章 Schema与数据类型优化

4.1 选择优化的数据类型

• 更小的通常更好。
  应该尽量使用可以正确存储数据的最小类型，更小的数据类型通常更快，因为他们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期更少。
• 简单就好
  更简单的数据类型的操作通常需要更少的CPU周期。例如，整型数字比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较相对整型数字比较更复杂。比如，应使用INTERGER存储IP地址（inet_aton）。
• 尽量避免NULL
  通常情况下，最好指定列为NOT NULL。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引，索引统计和值比较非常复杂，可为NULL的列会使用更多的存储空间，当可谓NULL的列被索引时，每个索引记录需要一个额外的字节。但是把可为NULL的列改成NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该避免设计成可为NULL的列。

4.1.1 整数类型

整数类型	占用空间	范围
TINYINT	8	[-27,27-1]
SMALLINT	16	[-215,215-1]
MEDIUMINT	24	[-223,223-1]
INT	32	[-231,231-1]
BIGINT	64	[-263,263-1]

整数类型有可选的UNSIGNED属性。

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MysQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INI(2)是相同的。

4.1.2 实数类型

实数是带有小数部分的数字，可以使用DECIMAL存储比BIGINT还大的整数。DECIMAL类型用于存储精确的小数，支持精确计算。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算，运算更快，空间更小。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

4.1.3 字符串类型

VARCHAR和CHAR类型

varchar

varchar可用于存储可变长字符串，是最常见的字符串数据类型。比定长类型更节省空间，因为它仅使用必要的空间。

varchar需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节标识，否则使用2个字节。

varchar合适的情况：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不用的字节数进行存储。

InnoDB可以把过长的varchar存储为BLOB。

char

char类型是定长的：mysql总是根据定义的字符串长度分配足够的空间。当存储char值时，mysql会删除所有的末尾空格。char值会根据需要采用空格进行填充以方便比较。

char适合存储很短的字符串，或者所有值都接近同一个长度。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT;对应的二进制类型是TINYBOLB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。BOLB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

与其他类型不同，mysql把每个blob和text当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text值太大时，InnoDB会使用专门的"外部"存储区域来进行存储，此时每个值在行内需要1~4个字节存储指针，然后在外部存储区域存储实际的值。

BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

mysql对blob和text列进行排序与其他类型是不同的：它值对每个列的最前max_sort_length(默认是1024)字节而不是整个字符串做排序。

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

有时候可以使用枚举列代替常用的字符串类型。mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存"数字-字符串"映射关系的"查找表"。

枚举字段是按照存储的整数而不是定义的字符串进行排序的。一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显示地指定排序顺序

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用alter table。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素。

使用ENUM可以让表的大小缩小一部分。但是当VARCHAR列和ENUM列进行关联时则慢很多，不过值得。

4.1.4 日期和时间类型

mysql可以使用许多类型来保存日期和时间值，例如YEAR和DATE。mysql能存储的最小时间粒度为秒(MariaDB支持微妙级别的时间类型)。但是mysql也可以使用微秒级的粒度进行临时计算。

mysql提供两种相似的日期类型：DATETIME和TIMESTAMP。各有特点。

DATETIME

这个类型能保存大范围的值，从1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

TIMESTAMP

TIMESTAMP类型保存了1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多；只能表示从1970年到2038年。mysql提供了from_unixtime()函数把unix时间戳转换为日期，并提供了unix_timestamp()函数把日期转换为unix时间戳。

默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，mysql则设置这个列的值为当前时间。在更新一行记录时，mysql默认也会更新第一个TIMESTAMP列的值（除非在update语句中明确指定了值）。

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

4.1.5 位数据类型

• BIT

• SET

  如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。

• 在整数列上进行按位操作

  一种替代SET的方式是使用一个整数包装一系列的位。例如，可以把8个位包装到一个TINYINT中，并且按位操作来使用。

谨慎使用BIT类型。因为其内部显示 字符串/数字 混乱，令人费解。

如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值（NULL）或者长度为零的字符串（空字符串）。

4.2 MySQL schema设计中的陷阱

错误的设计：

• 太多的列

  MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。

• 太多的关联

  一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

• 过度枚举

• 使用特殊数字表示未知值，而不用NULL
  
  

4.3 范式和反范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

4.3.1 范式的优点和缺点

范式的优点：

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

范式化设计的缺点是通常需要关联，可能代价昂贵，也可能使一些索引策略无效。

4.3.2 反范式的优点和缺点

优点：

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。

如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。

缺点：

冗余的多余数据，更新更慢。

表大，放到内存中，占用大，容易挤出热数据 。

4.3.3 混用范式化和反范式化

在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。

4.4 缓存表和汇总表

术语“缓存表”来表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据）。而术语“汇总表”时，则保存的是使用group by语句聚合数据的表。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。

当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。这就需要通过使用“影子表”来实现，“影子表”指的是一张在真实表“背后”创建的表。当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。例如，如果需要重建my_summary，则可以先创建my-summary_new，然后填充好数据，最后和真实表做切换：

4.4.2 计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。

假设有一个计数器表，只有一行数据，记录网站的点击次数，只能串行执行。

要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行进行更新。

为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。需要额外的维护任务。

缺点是写操作变慢了，但显著提高了读操作的性能。

4.5 加快ALTER TABLE操作的速度

对常见的场景，能使用的技巧只有两种：

• 一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；
• 另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

也有一些工具可以帮助完成影子拷贝工作：例如，"online schema change"工具、openark toolkit 以及Percona Toolkit。

不是所有的ALTER TABLE操作都会引起表重建。例如，有两种方法可以改变或者删除一个列的默认值（一种方法很快，另外一种则很慢），假如要修改电影的默认租赁期限，从三天改到五天。

• 下面是很慢的方式：

• 理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的 .frm 文件中，所以可以直接修改这个文件而不需要改动表本身。

  另外一种方法是通过ALTER COLUMN操作来改变列的默认值：

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

4.5.2 快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引。（这个办法对唯一索引无效）

4.6 总结

良好的schema设计原则是尽可能保持任何东西小而简单：

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

混合使用范式反范式。预先计算、缓存或生成汇总表也可能获得很大的好处。

第5章 创建高性能的索引

索引（在MySQL中也叫做“键（key）"）是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，“最优”的素引有时比一个“好的”索引性能要好两个数量级。

5.1 索引基础

5.1.1 索引的类型

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的素引。同一种索引底层实现也不一定相同。

B-Tree索引

当人们谈论素引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，实际多为B+Tree。InnoDB使用的就是B+Tree。

建立在B+Tree结构上的索引如下：

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜索。

示例：

假设有如下数据表，

B+Tree索引树中的部分条目示例，

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

B-Tree索引适用于全键值、键值范围或键前缀查找。

可以使用B-Tree索引的查询类型：

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

B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效，MySQL中只有Memory引擎显式支持哈希索引。

哈希索引限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 只支持等值比较查询，不支持任何范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高 。

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引（adaptive hash index）"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree素引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。该功能可关闭。

创建自定义哈希索引。如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引。可以手动维护也可以使用触发器维护哈希值。

空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。开源数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

5.2 索引的优点

1.三个优点：

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

2.如何评价一个索引是否适合某个查询的“三星系统”：

• 索引将相关的记录放到一起则获得一星
• 如果索引中的数据顺序和查找中的排序一致则获得二星
• 如果索引中的列包含了查询中需要的全部列则获得三星

5.3 高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。

5.3.1 独立的列

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

例如，下面这个查询无法使用actor_id列的索引：

mysql> SELECT actor id FROM sakila.actor WHERE actor_id + 1 = 5;

5.3.2 前缀索引和索引选择性

有时候需要索引很长的字符列， 这会让索引变得大且慢。
通常可以索引开始的部分字符，这样可以大大节约索引空间， 从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性最高，为1。

前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。计算合适的前缀长度的一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

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

5.3.3 多列索引

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

“索引合并”策略：如果每个列只有独立索引，查询WHERE子句使用了多个索引列的OR/AND，MySQL会自动将结果进行联合（union）/相交(intersection)。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

• 当出现服务器对多个索引做交互操作时(通常有多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时(通常有多个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 更重要的是，优化器不会把这些计算到"查询成本"中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能影响查询的并发性，但如果是单独运行这样的查询则会往往忽略对并发性的影响。通常来说，还不如像在MySQL4.1或者更早时代一样，将查询改写成UNION的方式往往更好。

5.3.4 选择合适的索引列顺序

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

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。

如何选择索引的列顺序：

• 经验法则：当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。
• 可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

【注意】不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况（在某些条件值的基数比正常值高的时候）可能会摧毁整个应用的性能。

5.3.5 聚簇索引

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

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。

聚簇索引的数据分布：

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候（反过来也一样）。

聚集的数据有一些重要的优点：

• 可以把相关数据保存在一起。减少磁盘I/O次数。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

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

一级索引：索引和数据存储在一起，都存储在同一个B+tree中的叶子节点。一般主键索引都是一级索引。

二级索引：二级索引树的叶子节点存储的是主键而不是数据。也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。

InnoDB和MyISAM的数据分布对比

表如下：

CREATE TABLE layout_test{
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1), 
	KEY(col2)
};

MyISAM的数据分布

MyISAM按照数据插入的顺序存储在磁盘上，如下：

MyISAM表layout_test的col2列索引的分布：

MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。索引中的每个叶子节点包含“行号”。

InnoDB的数据分布

因为InnoDB支持聚簇索引，所以使用非常不同的方式存储同样的数据：

在InnoDB中，聚簇索引“就是"表，所以不像MyISAM那样需要独立的行存储。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC10的回滚指针以及所有的剩余列（在这个例子中是col2），如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。

这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

上面的分布图只展示了叶子节点，其中非叶子节点包含了索引列和一个指向下级节点的指针。

InnoDB和MyISAM如何存放表的抽象图：

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

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键（surrogate key）作为主键，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写人，性能更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用，因为它使得聚簇索引的插入变得完全随机，InnoDB频繁地做页分裂。

使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

顺序主键并不是绝对更优，高并发时，在InnoDB中按主键顺序插入可能会造成明显的间隙锁争用，以及AUTO_INCREMENT锁争用。

5.3.6 覆盖索引

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

覆盖索引是非常有用的工具，能够极大地提高性能。

优点：

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

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

当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN的Extra列可以看到"Using index"的信息。

延迟关联

假如索引覆盖了 where 条件中的字段，但不是整个查询涉及的字段，还是会回表获取数据行。

select * from products where actor='SEAN' and title like  '%APOLLO%'

可以使用延迟关联（延迟了对列的访问）解决：

SELECT
	*
FROM
	products
JOIN (
	SELECT
		product_id
	FROM
		products
	WHERE
		actor = 'SEAN'
	AND title LIKE '%APOLLO%'
) t1 ON t1.product_id = products.id

虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好。

5.3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：

• 通过排序操作；
• 或者按索引顺序扫描。

如果EXPLAIN出来的type列的值为"index"，则说明MysQL使用了索引扫描来做排序。

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

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

只有当索引的列顺序和ORDERBY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDERBY子句引用的字段全部为第一个表时，才能使用索引做排序。

ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则MysQL都需要执行排序操作，而无法利用索引排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。示例见书，略。

使用索引做排序的一个最重要的用法是当查询同时有ORDER BY和LIMIT子句的时候。

5.3.8 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少素引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是"perform"，第二个值是"performance"，那么第二个值的前缀压缩后存储的是类似"7，ance"这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描，倒序扫描效果差。

• CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，
• I/O密集型应用，对某些查询带来的好处会比成本多很多。

可以在CREATE TABLE语句中指定PACK KEYS参数来控制索引压缩的方式。

5.3.9 冗余和重复索引

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

冗余索引和重复索引有一些不同。 如果创建了索引(A, B)， 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引。 因此索引(A, B) 也可以当作索引 (A) 来使用（这种冗余只是对B-Tree索引来说的）。

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

索引增多，查询可能变快，但维护索引的成本变高，比如表中的索引越多插入速度会越慢。

解决冗余索引和重复索引的方法很简单，删除这些索引就可以。但首先要做的是找出这样的索引。可以使用Percona Toolkit中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复的索引。

5.3.10 未使用的索引

可能还会有一些服务器永远不用的索引，建议考虑删除。

可以使用PerconaToolkit中的pt-index-usage，该读取查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告。可以找出哪些索引是未使用的。

5.3.11 索引和锁

索引可以让查询锁定更少的行。

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放（InnoDB可以在服务器端过滤掉行后就释放锁）。示例见书，略。

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

5.4 索引案例学习

假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢？

出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制素引和查询的设计。

5.4.1 支持多种过滤条件

让多个查询都会用到的列放在索引的最前列。

考虑到使用的频率，建议在创建不同组合索引的时候将（sex，country）列作为前缀，即便这两列选择性不高。

这么做有两个理由：

• 第一点，几乎所有的查询都会用到sex列。

• 更重要的一点是，素引中加上这一列也没有坏处，即使查询没有使用sex列也可以通过下面的“诀窍”绕过。

  这个“诀窍”就是：如果某个查询不限制性别，那么可以通过在查询条件中新增AND SEX IN（‘m’，‘f’）来让MysQL选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。这个“诀窍”在这类场景中非常有效，但如果列有太多不同的值，就会让IN()列表太长，这样做就不行了。

基本原则：尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

范围查询可以使用IN()代替，比如age BETWEEN 18 AND 25 可以用IN(18,19,20,21, 22,23,24,25)代替，不过IN()也不能滥用，因为每额外增加一个IN()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能。

这两种访问效率是不同的。对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这个限制。

5.4.2 避免多个范围条件

假设我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户：

WHERE eye_color IN("brown' ,'blue','hazel')
AND hair_color IN('black', 'red', 'blonde', 'brown')
AND sex IN('M" ,'F')
AND last_online > DATE SUB(NOW(), INTERVAL 7 DAY)
AND age BETWEEN 18 AND 25

这个查询有一个问题：它有两个范围条件，last_online列和age列，MySQL可以使用last_online列索引或者age列索引，但无法同时使用它们。

这里考虑如果我们无法把age字段转换为一个IN()的列表。解决办法：

我们能够将其中的一个范围查询转换为一个简单的等值比较。为了实现这一点，我们需要事先计算好一个active列，这个字段由定时任务来维护。当用户每次登录时，将对应值设置为1，并且将过去连续七天未曾登录的用户的值设置为0。

5.4.3 优化排序

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

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

即使有索引，如果用户界面上需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。

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

LIMIT [offset, ] nums ：表示在偏移量offset处取nums行记录。

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。

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

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 找到并修复损坏的表

表损坏原因：

• 对于MyISAM存储引擎，表损坏通常是系统崩溃导致的。
• 其他的引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。
• InnoDB一般不会出现损坏。InnoDB的设计保证了它并不容易被损坏。如果发生损坏，一般要么是数据库的硬件问题例如内存或者磁盘问题（有可能），要么是由于数据库管理员的错误例如在MySQL外部操作了数据文件（有可能），抑或是InnoDB本身的缺陷（不太可能）。

表损坏后果：

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。

检查：

如果你遇到了古怪的问题——例如一些不应该发生的错误——可以尝试运行CHECK TABLE来检查是否发生了表损坏（注意有些存储引擎不支持该命令；而有些引擎则支持以不同的选项来控制完全检查表的方式）。

CHECK TABLE通常能够找出大多数的表和索引的错误。

修复：

• 可以使用REPAIR TABLE命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。

• 也可通过一个不做任何操作（no-op）的ALTER操作来重建表。

  mysql> ALTER TABLE innodb_tbl ENGINE=INNODB;
  

• 此外，也可以使用一些存储引擎相关的离线工具，例如myisamchk；或者将数据导出一份，然后再重新导人。

不过，如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。在这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。

5.5.2 更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。

• 第一个API是records_in_range()，通过向存储引擎传人两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎，该接口返回精确值，例如MyISAM；但对于另一些存储引擎则是一个估算值，例如InnoDB。

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

如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。

MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息解决这个问题。

可以使用SHOW INDEX FROM命令来查看索引的基数（Cardinality）。索引列的基数（Cardinality）显示了存储引擎估算索引列有多少个不同的取值。

InnoDB的统计信息值

InnoDB的统计信息值得深入研究。InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。

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

InnoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX，抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新。

有可能给服务器带来很多额外压力。

5.5.3 减少索引和数据的碎片

索引碎片

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

如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多倍；对于索引覆盖扫描这一点更加明显。

数据碎片

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

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

对于MyISAM表，这三类碎片化都可能发生。但InnoDB不会出现短小的行碎片InnoDB会移动短小的行并重写到一个片段中。

解决办法

可以通过执行OPTIMIZE TABLE或者导出再导人的方式来重新整理数据。这对多数存储引擎都是有效的。

• 对于一些存储引擎如MyISAM，可以通过排序算法重建索引的方式来消除碎片。
• 最新版本InnoDB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

应该通过一些实际测量而不是随意假设来确定是否需要消除索引和表的碎片化。

5.6 总结

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

• 单行访问是很慢的。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
• 按顺序访问范围数据是很快的，这有两个原因。
  • 第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）；
  • 第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。
• 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

总的来说，编写查询语句时应该①尽可能选择合适的索引以避免单行查找、②尽可能地使用数据原生顺序从而避免额外的排序操作，并③尽可能使用索引覆盖查询。这与本章开头提到的“三星”评价系统是一致的。

不过，并不是一定要满足这三个要求，有些查询也无法满足（一列正序，另一列倒序）。

如何判断一个系统创建的索引是合理的呢？

一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询（第3章中介绍了如何测量），然后检查这些查询的schema，SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机1/0访问数据，或者是有太多回表查询那些不在索引中的列的操作。

如果一个查询无法从所有可能的索引中获益，则应该：

• 看看是否可以创建一个更合适的索引来提升性能。
• 如果不行，也可以看看是否可以重写该查询，将其转化成一个能够高效利用现有索引或者新创建索引的查询。这也是下一章要介绍的内容。

对于诊断时抓不到的查询，那就不是问题。

第6章 查询性能优化

前面的章节我们介绍了如何设计最优的库表结构、如何建立最好的索引，对于高性能来说还需要合理的设计查询。

查询优化、索引优化、库表结构优化需要齐头并进。

本章将从查询设计的一些基本原则开始——这也是在发现查询效率不高的时候首先需要考虑的因素。

6.1 为什么查询速度会慢

编写快速的查询真正重要是响应时间。

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

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

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

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

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。

对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。浪费了服务器、网络资源。

一些典型案例：

• 查询不需要的记录

  一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。

• 多表关联时返回全部列

  如果你想查询所有在电影Academy Dinosaur中出现的演员，千万不要按下面的写法编写查询：

  mysql> SELECT * FROM sakila.actor
  -> INNER JOIN sakila.film actor USING(actor_id)
  -> INNER JOIN sakila.film USING(film_id)
  -> WHERE sakila.film.title = 'Academy Dinosaur';
  

  这将返回这三个表的全部数据列。正确的方式应该是像下面这样只取需要的列：

  mysql> SELECT sakila.actor.* FROM sakila.actor...;
  

• 总是取出全部列

  使用SELECT *时考虑是否真的需要返回全部列。取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。

  当然，查询返回超过需要的数据也有好处，比如

  • 能提高相同代码片段的复用性，简化开发。
  • 便于应用程序使用某种缓存机制。

• 重复查询相同的数据

  不断地重复执行相同的查询，然后每次都返回完全相同的数据。可以用缓存来解决该问题。

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

第二部时看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下：

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

这三个指标都会记录到MySQL的慢日志中。

响应时间

响应时间是两个部分之和：服务时间和排队时间。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。实际上可以使用“快速上限估计”法来估算查询的响应时间。

概括地说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来。

扫描的行数和返回的行数

这在一定程度上能够说明该查询找到需要的数据的效率高不高。

不过，这个指标可能还不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

理想情况下扫描的行数和返回的行数应该是相同的。但实际并不是。

扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。MySQL有好几种访问方式可以查找并返回一行结果。

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

EXPLAIN中的type种类：

• all：全表扫描
• index：
  • Extra中有Using Index，表示索引覆盖，即所有数据均可从索引树获取。
  • Extra中无Using Index，表示按照索引顺序扫描全表。
• range：索引范围扫描， 对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询 。
• ref： 使用非唯一索引（非主/unique键）或非唯一索引前缀进行的查找，可能有多行。
• eq_ref：唯一索引（主/unique键）查询，对于每个索引键，表中只有一条记录与之匹配，并且使用了多表关联。
• const：该表最多有一个匹配行。与 eq_ref 的区别是，eq_ref 使用了关联。
• system：该表只有一行。

其他见 https://blog.csdn.net/lilongsy/article/details/95184594

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

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

好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。

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

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

6.3 重构查询的方式

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

MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效 。

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

MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。

6.3.2 切分查询

切分查询，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果，可以避免锁住很多数据、占满事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

例如：每月运行一次删除操作，删除三个月以前的所有信息。可以切分为 一次删除操作删除三个月以前的1w条信息，直至全部删完。

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 WHERE tag='mysql'; 
mysql> SELECT * FROM tag_post WHERE tag_id=1234; 
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

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

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。另外，对MySQL的查询缓存来说进，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
• 执行单个查询可以减少锁的竞争。
• 查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
• 这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联（本章后续我们将讨论这点）。

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：

• 当应用能够方便地缓存单个查询的结果的时候。
• 当可以将数据分布到不同的MySQL服务器上的时候。
• 当能够使用IN()的方式代替关联查询的时候。
• 当查询中使用同一个数据表的时候。
  
  

6.4 查询执行的基础

ySql.assets\1620378289725.png)]

MySQL执行查询的过程：

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

还有很多的例外情况，例如，当查询使用绑定变量后，执行路径会有所不同。

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

MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。没法进行流量控制。

当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。

MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力。连接或查询时可以选择是否使用缓存。

查询状态

对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。 SHOW FULL PROCESSLIST命令查看状态（Command列表示当前的状态）。MysQL官方手册中对这些状态值的含义有最权威的解释，简介如下：

• Sleep，线程正在等待客户端发送新的请求
• Query，线程正在执行查询或者正在将结果发送给客户端
• Locked，在MySQL服务器层，该线程正在等待表锁
• Analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划
• Copying to tmp table [on disk]，线程正在执行查询，并且将其结果集都复制到一个临时表中，要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作
• Sorting result，线程正在对结果集进行排序
• Sending data，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

6.4.2 查询缓存（Query Cache）

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果。

6.4.3 查询优化处理

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

这个过程中任何错误（例如语法错误）都可能终止查询。

语法解析器和预处理

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等。

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

下一步预处理器会验证权限，这通常很快。

查询优化器

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

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

成本是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。

导致MySQL查询优化器选择错误的原因：

• 统计信息不准确，Innodb不能维护一个数据表的行数的精确统计信息
• 执行计划中的成本估算不等同于实际执行的成本
• MySQL的最优可能和你想的最优不一样
• MySQL从不考虑其他并发执行的查询
• MySQL也并不是任何时候都是基于成本的优化
• MySQL不会考虑不受其控制的操作的成本
• 优化器有时候无法去估算所有可能的执行计划

MySQL的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。

• 静态优化是一种“编译时优化”，可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。
• 动态优化是“运行时优化"，和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估。

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

下面是一些MySQL能够处理的优化类型：

• 重新定义关联表的顺序
• 将外连接转化成内连接
• 使用等价变换规则
• 优化COUNT()、MIN()和MAX()，在EXPLAIN中可以看到“Select tables optimized away”
• 预估并转化为常数表达式，当检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理
• 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，就可以使用索引返回需要的数据，而无须查询对应的数据行
• 子查询优化
• 提前终止查询，在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询
• 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上
• 列表IN()的比较，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件

数据和索引的统计信息

统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。

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

MySQL如何执行关联查询

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

对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。在MySQL的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

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

另一种可视化查询执行计划的方法是根据优化器执行的路径绘制出对应的“泳道图"。

全外连接就无法通过嵌套循环和回溯的方式完成，当发现关联表中没有找到任何匹配行的时候，则可能是因为关联恰好从一个没有任何匹配的表开始，MySQL不支持全外连接。

执行计划

MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。

MySQL的执行计划总是如图6-4所示，是一棵左侧深度优先的树。

关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

实际上，MySQL通过预估需要读取的数据页来选择，读取的数据页越少越好。

不过有的时候，优化器给出的并不是最优的关联顺序。这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为的最优的关联顺序执行。不过，绝大多数时候，优化器做出的选择都比普通人的判断要更准确。

如果有超过n个表的关联，那么需要检查n的阶乘关联顺序，称为“搜索空间”，搜索空间的增长速度非常快。当搜索空间非常大的时候，优化器不可能逐一评估每一种关联顺序的成本。这时，优化器选择使用“贪婪”搜索的方式查找“最优”的关联顺序。优化器积累了很多“启发式”的优化策略来加速执行计划的生成，因此，偶尔会选择不是最优的执行计划。

排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。

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

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

MySQL有如下两种排序算法：

• 两次传输排序（旧版本使用）
  读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
  这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次数据传输的成本非常高。

• 单次传输排序（新版本使用）
  先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

  相比两次传输排序，这个算法只需要一次顺序IO读取所有的数据，而无须任何的随机1/0。缺点是可能额外占用大量空间。

MysQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。

6.4.4 查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构。

MySQL再查询执行阶段只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为"handler APl"的接口。MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。

6.4.5 返回结果给客户端

即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。

如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。

MySQL将结果集返回客户端是一个逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。

这样处理有两个好处：

• 服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。
• 另外，这样的处理也让MySQL客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送。

6.5 MySQL查询优化器的局限性

6.5.1 关联子查询

MySQL的子查询实现得非常糟糕，最糟糕的一类查询是WHERE条件中包含IN()的子查询语句，使用GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表，或者使用EXISTS()来改写。

不过，5.6版本后可以忽略该局限性。

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的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据：

(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;

6.5.5 并行执行

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

6.5.6 哈希关联

直至5.5，MySQL都不支持哈希关联——MySQL的所有关联都是嵌套循环关联。不过，可以通过建立一个哈希索引来曲线地实现哈希关联。如果使用的是Memory存储引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联。。另外，MariaDB已经实现了真正的哈希关联。

6.5.7 松散索引扫描

详细见https://www.cnblogs.com/novice-dxx/p/11955920.html

MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个素引。5.0后版本在分组查询中需要找到分组的最大值和最小值时可以使用松散索引扫描。

6.5.8 最大值和最小值优化

对于MIN()和MAX()查询，MySQL的优化做得并不好。这里有一个例子：

mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';

因为在first_name字段上并没有索引，因此MySQL将会进行一次全表扫描。一个曲线的优化办法是移除MIN()，然后使用LIMIT来将查询重写如下：

mysql> SELECT actor id FROM sakila. actor USE INDEX(PRIMARY)
	->WHERE first name = 'PENELOPE' LIMIT 1;

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

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

可以通过使用生成表的形式来绕过上面的限制，因为MySQL只会把这个表当作一个临时表来处理。

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

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。具体用法见官方手册。简介：

• HIGH_PRIORITY和LOW_PRIORITY

  这个提示告诉 MySQL，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。

  这两个提示只对使用表锁的存储引擎有效，千万不要在InnodB或者其他有细粒度锁机制和并发控制的引擎中使用。

• DELAYED

  延迟写。对INSERT和REPLACE有效，会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入，并不是所有的存储引擎都支持，并且该提示会导致函数LAST_INSERT_ID()无法正常工作

• STRAIGHT_JOIN

  可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联，第二个用法则是固定其前后两个表的关联顺序。

  当你确定无论怎样的 where条件，某个固定的关联顺序始终是最佳的时候，使用这个提示可以大大提高优化器的效率。

• 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

  会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集，可以通过函数FOUND_ROW()获得这个值。不应该使用。

• FOR UPDATE和LOCK IN SHARE MODE

  主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效，仅InnoDB支持。使用该提示会对符合查询条件的数据行加锁。这两个提示经常被滥用，很容易造成服务器的锁争用问题，应该避免使用。

• USE INDEX、IGNORE INDEX和FORCE INDEX

  告诉优化器使用或者不使用哪些索引来查询记录。在MyQL5.1和之后的版本可以通过新增选项FOR ORDER BY和 FOR GROUP BY来指定是否对排序和分组有效。

  FORCE INDEX和 USE INDEX基本相同，除了一点：FORCE INDEX 会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际上该索引用处不大。

MySQL5.0后新增的用来控制优化器行为的参数：

• optimizer_search_depth，控制优化器在穷举执行时的限度。
• optimizer_prune_level，让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。
• optimizer_switch，包含了一些开启/关闭优化器特性的标志位。

在 MySQL版本升级的时候，你设置的"优化器提示"很可能会让新版的优化策略失效。

Percona Toolkit中的pt-upgrade工具，就可以检查在新版本中运行的SQL是否与老版本一样，返回相同的结果。

6.7 优化特定类型的查询

本节介绍的多数优化技巧都是和特定的版本有关的。

6.7.1 优化COUNT()查询

COUNT()的作用

COUNT()是一个特殊的函数,有两种非常不同的作用:

• 它可以统计某个列值的数量

  在统计列值时要求列值是非空的(不统计NULL),如果在COUNT()的括号中指定了列或者列的表达式,则统计的就是这个表达式有值的结果数。

• 也可以统计行数

  COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时,实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。

MyISAM的COUNT()函数只有没有任何WHERE条件下的COUNT(*)才非常快，即只有没有任何WHERE条件的COUNT(*)才非常快，因为此时无须实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。

如果MySQL知道某列col不可能为NULL值，那么MySQL内部会将COUNT(col)表达式优化为COUNT(*)。

使用近似值

有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低。

更复杂的优化

• 使用索引覆盖
• 使用汇总表
• 使用外部缓存系统，比如Memcached

6.7.2 优化关联查询

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

6.7.3 优化子查询

尽可能使用关联查询代替，如果使用MySQL5.6以上或MariaDB则可以忽略这个建议。

6.7.4 优化GROUP BY和DISTINCT

在很多场景下，MySQL都使用同样的办法优化这两种查询，事实上，MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。

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

如果需要对关联查询做分组（GROUP BY），并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。

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

优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合（汇总）。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。

最好的办法是尽可能的将WITH ROLLUP功能转移到应用程序中处理。

6.7.5 优化LIMIT分页

在偏移量非常大的时候，例如可能是LIMIT 1000, 20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。

优化方法：

• 索引覆盖扫描 + 延迟关联

  尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。（示例见5.4.3 优化排序）

• 使用书签代替OFFSET

  LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

• 使用预先计算的汇总表，或者关联到一个冗余表。

6.7.6 优化SQL_CALC_FOUND_ROWS

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

实际上，MySQL只有在扫描了所有满足条件的行以后，才会知道行数，所以加上这个提示以后，不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，所以该提示的代价可能非常高。

替代设计：

• 一个更好的设计是将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么我们每次查询时都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么我们就显示“下一页”按钮，否则就说明没有更多的数据，也就无须显示“下一页”按钮了。

• 另一种做法是先获取并缓存较多的数据——例如，缓存1000条——然后每次分页都从这个缓存中获取。如果结果集少于1000，就可以在页面上显示所有的分页链接，因为数据都在缓存中，所以这样做性能不会有问题。如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。

• 有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值（实际上Google的搜索结果总数也是个近似值），当需要精确结果的时候，再单独使用COUNT(*)来满足需求，这时如果能够使用索引覆盖扫描则也会快得多。

6.7.7 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手动地将WHERE，LIMIT，ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化）。

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。

事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端）。

6.7.9 使用用户自定义变量

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

可以使用下面的SET和SELECT语句来定义它们：

mysql> SET @one := 1; 
mysql> SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor); 
mysql> SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

然后可以在任何可以使用表达式的地方使用这些自定义变量：

mysql> SELECT ... WHERE col <= @last_week;

哪些场景下我们不能使用用户自定义变量：

• 使用自定义变量的查询，无法使用查询缓存。
• 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
• 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
• 如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互（如果是这样，通常是代码bug或者连接池bug，这类情况确实可能发生）。
• 不能显式地声明自定义变量的类型。用户自定义变量的类型在赋值的时候会改变。MySQL的用户自定义变量是一个动态类型。
• MySQL优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行。
• 赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。实际情况可能很让人困惑。
• 赋值符号：=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。使用未定义变量不会产生任何语法错误，如果没有意识到这一点，非常容易犯错。

用户自定义变量使用场景：

• 优化排名语句

  利用其左值特性。

• 避免重复查询刚刚更新的数据

• 统计更新和插入的数量

  ON DUPLICATE KEY UPDATE用于向数据库中插入（更新）一条记录：

  • 若该数据的主键值 UNIQUE KEY 已经在表中存在，则执行更新操作, 即UPDATE 后面的操作。

  • 否则插入一条新的记录。

  当使用了INSERT ON DUPLICATE KEY UPDATE的时候，如果想知道到底插入了多少行数据，到底有多少数据是因为冲突而改写成更新操作的。解决办法：

  INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
  ON DUPLICATE KEY UPDATE c1 = VALUES(c1)+(o*( @x := @x +1 ));
  

  当每次由于冲突导致更新时对变量@x自增一次。然后通过对这个表达式乘以0来让其不影响要更新的内容。另外，MySQL的协议会返回被影响的总行数，所以不需要单独统计这个值。

• 编写偷懒的UNION

  假设需要编写一个UNION查询，其第一个子查询作为分支条件先执行，如果找到了匹配的行，则跳过第二个分支。

  在某些业务场景中确实会有这样的需求，比如先在一个频繁访问的表中查找“热”数据，找不到再去另外一个较少访问的表中查找“冷”数据。

  完成的查询如下：

  SELECT GREATEST(@found := -1, id) AS id, 'users' AS which tbl 
  FROM users WHERE id = 1
  UNION ALL 
  	SELECT id, 'users archived'
  	FROM users archived WHERE id -1 AND @found IS NULL 
  UNION ALL 
  	SELECT 1, 'reset' FROM DUAL WHERE (@found := NULL) IS NOT NULL;
  

6.8 案例学习

查询优化基础原则：

• 尽量少做事，可以的话就不要做任何事情。
• 尽可能快地完成需要做的事情。
• 某些查询是无法优化的，考虑使用不同的查询或者不同的策略去实现相同的目的。需要的时候，尽可能让应用程序完成一些计算。

参考：
《高性能MySQL 第3版》
https://zhuanlan.zhihu.com/p/36905207
https://www.oysterqaq.com/archives/1125
https://blog.csdn.net/u011067966/article/details/104157699