高性能Mysql（第三版）笔记

一、MySQL架构与历史

MySQL逻辑架构

• 最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等
• 第二层架构是MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数(例如，日期、时间、数学和加密函数)，所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等
• 第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。和GNU/Linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事务”或者。根据主键提取一行记录"等操作。但存储引擎不会去解析SQL性,不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求

优化与执行

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

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

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

并发控制

MySQL在两个层面实现并发控制：服务器层与存储引擎层

在处理并发读或写时，可以通过实现一个由两种锁组成的系统来解决问题。这两种锁通常被称为共享锁和排他锁，或者称为读锁和写锁。读锁是共享的，或者说是相互不阻塞的，多个客户可以在同时读取同一数据。而写锁是排他的，同一时刻只能有一个用户能够写入，并防止其他用户读取正在写入的数据

锁粒度是指加锁的对象的大小。显然，锁的粒度越小，并发控制效率越高。锁的各种操作，包括获得锁、检查锁和释放锁等，都会增加系统开销。因此，如果系统花费大量时间来管理锁，而不是用来获取数据，就会影响系统性能

有两种常见的缩策略，表锁和行级锁。表锁开销较小，但是并发控制不好。行级锁可以很好地实现并发控制，但是开销比较大

所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。大多数商业数据库系统没有提供更多的选择,一般都是在表上施加行级锁(row-level lock)，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好的性能。而MySQL则提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度

事务

事务将几个操作作为一个整体，要么全部执行，要么全部放弃。事务的四大特性ACID：

• 原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作
• 一致性（consistency）：数据库总是从一个一致性的状态转换到另外一个一致性的状态，比如有一个事务包括四步，从零加一，从一加一，从二加一，从三加一，那么根据一致原则，最后的结果一定是0或者4，从一个状态转换到另一个状态
• 隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的
• 持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中

隔离级别

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

• READ UNCOMMITTED (未提交读)：在READ UNCOMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)
• READ COMITTED (提交读)：一个事务开始时，只能看见已经提交的修改，并且所做的修改对其他事务不可见。这种存在的一个问题叫不可重复读，就是指事务A读取某条记录之后，事务B对其进行了修改，这时当A再次读取该数据的时候就会发现与之前读取的结果不一样
• REPEATABLE READ (可重复读)：可重复读解决了，数据不可重复读的问题，但仍然不能解决幻读，所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。这是mysql的默认级别，mysql还可以通过MVCC+间隙锁解决幻读问题
• SERIALIZABLE (可串行化)：事务按照串行的方式执行，并在每行数据上加锁，可能产生大量的超时和锁争用问题

四种隔离级别与可避免操作

• Read uncommitted【级别最低，什么都避免不了】
• Read committed【可避免脏读】
• Repeatable read【可避免脏读，不可重复读】
• Serializable【可避免脏读，不可重复读，虚读】

脏读、不可重复读与幻读(虚读)

• 脏读：一个事务读取到另外一个事务未提交的数据
• 不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改
• 幻读(虚读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

死锁

指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象

事务日志

存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘，称为预写式日志（Write-Ahead Logging），如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。MySQL提供了两种事务型的存储引擎: InnoDB和NDB Cluster，也有一些第三方存储引擎

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制(MVCC)。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然大多引擎实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。典型的有乐观(optimistic) 并发控制和悲观(pessimistic) 并发控制

MVCC的实现，是通过保存数据在某个时间点的快照来实现的，且只在REPEATABLE READ 和READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCONMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

MySQL的存储引擎

在文件系统中，MySQL将每个数据库( 也可以称之为schema)保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的frm文件保存表的定义。可以使用SHOW TABLE STATUS LIKE ‘tablename’ \G 显示表的相关信息

• Name：表名
• Engine：表使用的引擎
• Version：表版本信息
• Row_format：行的格式，一般包含可变长度的字段，如VARCHAR或BLOB
• Rows：表中的行数
• Avg_row_ length：平均每行的字数
• Data_ length：表数据的大小
• Max_ data_ length：表的最大容量，该值和存储引擎有关
• Index_length：索引的大小
• Data_free：对于MyISAM表，表示已分配但目前没有使用的空间
• Auto_ increment：下一个AUTO_ INCREMENT 的值,
• Create_time：表的创建时间
• Update_ time： 表的最后修改时间
• Check_ time：使用CKECK TABLE命令或者myisamchk工具最后一次检查表的时间
• Col lation：表的默认字符集和字符列排序规则
• Checksum:：如果启用，保存的是整个表的实时校验和
• Create_options：创建表时指定的其他选项
• Comment ：创建表时的注释

InnoDB 存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎

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

InnoDB内部做了很多优化，包括从磁盘读取数据时采的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够加速插人操作的插人缓冲区(insert buffer)等。后面将更详细地讨论这些内容

MyISAM 存储引擎

在MySQL 5.1 及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS) 等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。所以MySQL 5.1以后版本的MySQL已经默认不用MyISAM引擎了，相对于InnoDB那一点点的并发量的增加(需要一些条件)，这些缺点让我们不在默认引擎为MyISAM，百分之九十以上情况我们需要InnoDB，MyISAM所拥有的优点大多已能在InnoDB找到代替方案，其它引擎也一样，所以，如果不是非常特殊的要求，我们大多情况使用InnoDB引擎，MyISAM已有的特性

• 加锁与并发：MyISAM对整张表加锁，而不是针对行
• 修复：对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丟失，而且修复操作是非常慢的
• 索引：支持索引，且支持全文分词索引

MyISAM 性能

MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好，比如，数据只读，表比较小，可以忍受修复失败，的场景下。不要轻易相信 “MyISAM比InnoDB快” 之类的话，在很多场景下，这个结论是不对的，甚至InnoDB的速度会比MyISAM望尘莫及，比如使用到聚簇索引时

其它引擎

MySQL还有一些有特殊用途的存储引擎。在新版本中，有些可能因为一些原因已经不再支持，就不一一说明了。那么，引擎这么多，我们应该怎么选择呢，归纳为一句话：“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。" 轻易切换引擎，转则让事情复杂难搞，重则翻车跑路

二、MySQL 基准测试

基准测试(benchmark) 也译做标准检查程序，是MySQL新手和专家都需要掌握的一项基本技能。简单地说，基准测试是针对系统设计的一种压力测试。通常的目标是为了掌握系统的行为。对调优而言，准确的测试相当重要，只有测试正确才知道问题出在哪里，才能加以改进

基准测试完成的工作

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

基准测试的策略

基准测试有两种主要的策略：一是针对整个系统的整体测试，另外是单独测试MySQL。这两种策略也被称为集成式(ull-stack) 以及 单组件式(single-component) 基准测试。针对整个系统做集成式测试，而不是单独测试MySQL的原因主要有以下几点:

• 用户关注的是整体性能
• MySQL并非总是性能瓶颈
• 做整体测试，才能发现各部分之间的缓存带来的问题

测试何种指标

• 吞吐量：指单位时间内的事务处理数，常用的测试单位是每秒事务数（TPS），或每分钟事务数（TPM）
• 响应时间或者延迟：用于测试任务所需的整体时间，根据具体的应用，测试的时间单位可能是微秒、毫秒、秒或者分钟。通常使用百分比响应时间（percentile response time）来替代最大响应时间
• 并发性：需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数，在测试期间记录MySQL数据库的Threads_running状态值
• 可扩展性：给系统增加一倍的工作，在理想情况下就能获得两倍的效果（即吞吐量增加一倍），对于容量规范非常有用，可以提供其他测试无法提供的信息，来帮助发现应用的瓶颈

基准测试方法

设计测试条件时，应尽量使测试过程与真实应用的场景接近，基准测试方法的一常见错误

• 使用真实数据的子集而不是全集
• 使用错误的数据分布
• 在多用户场景中，只做单用户的测试
• 在单服务器上测试分布式应用
• 与真实用户行为不匹配
• 反复执行同一个查询，导致全部或部分缓存
• 没有检查错误
• 忽略了系统预热(warm up)的过程
• 使用默认的服务器配置
• 测试时间太短
• 收集的测试信息不够全

基准测试的一些建议

• 应该建立将参数和结果文档化的规范，每一轮测试都必须进行详细记录
• 基准测试应该运行足够长的时间，需要在稳定状态下测试并观察
• 在执行基准测试时，需要尽可能多地收集被测试系统的信息
• 自动化基准测试可以防止测试人员偶尔遗漏某些步骤，或者误操作，另外也有助于归档整个测试过程，可以选择shell、php、perl等
• 要尽可能使所有测试过程都自动化，包括装载数据、系统预热、执行测试、记录结果等

三、服务器性能剖析

性能优化简介

• 性能，为完成某件任务所需要的时间度量，性能即响应时间
• 如果目标是降低响应时间，就需要理解为什么服务器执行查询需要这么多时间，然后去减少或者消除那些对获得查询结果来说不必要的工作。无法测量就无法有效地优化
• 性能剖析（profiling）是测量和分析时间花费在哪里的主要方法，一般有两个步骤：测量任务所花费的时间，对结果进行统计和排序

对应用程序进行性能剖析

性能瓶颈可能的影响因素

• 外部资源
• 应用需要处理大量的数据
• 在循环中执行昂贵的操作
• 使用了低效的算法

诊断间歇性问题

间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。有些幻影问题只在没有注意到的时候才发生，而且无法确认如何重现，诊断这样的问题往往要花费很多时间，此时应注意：

• 尽量不要用试错的方式来解决问题，如果一时无法定位，可能是测量的方式不正确，或者测量的点选择有误，或者使用的工具不合适
• 确定单条查询问题还是服务器问题
• 捕获尽可能多的诊断数据

其它注意点

• 定义性能最有效的方法是响应时间
• 如果无法测量就无法有效地优化，所以性能优化工作需要基于高质量、全方位及完整的响应时间测量
• 测量的最佳开始点是应用程序，而不是数据库。即使问题出在底层的数据库，借助良好的测量也可以很容易地发现问题
• 大多数系统无法完整地测量，测量有时候也会有错误的结果
• 完整的测量会产生大量需要分析的数据，所以需要用到剖析器
• 有两种消耗时间的操作：工作或者等待
• 优化和提升是两回事。当继续提升的成本超过收益的时候，应当停止优化

总体来说，解决性能问题的方法，首先是要澄清问题，然后选择合适的技术来解答这些问题。

四、Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如，反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的數据类型对于获得高性能至关重要。有以下几条建议：

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少
• 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符比较操作代价更低
• 尽量避免NULL：很多表都包含可为NULL (空值)的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理

在为列选择数据类型时，第一步需要确定合适的大类型:数字、字符串、时间等，第二步就是选择类型的精度

整数类型

MySQL整数可以指定宽度，如int(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说，int(20)和int(1)是相同的

实数类型

因为需要额外空间和计算开销，所以尽量只在对小数进行计算的时候才使用decimal。在数据量比较大的时候，可以考虑使用bigint代替decimal，将存储的单位根据小数的位数乘以相应的倍数即可。例如：银行存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

字符串类型

CHAR适合存储短的短的字符串，或者所有的值都接近同一长度。对于经常变更的数据，CHAR也比VARCHAR好，因为定长的CHAR不容易产生碎片。对于非常短的字符串，CHAR也比VARCHAR更好，因为VARCHAR还需要1或2个额外的字节存储字符串长度。VARCHAR适合变长范围比较大的数据

可以使用枚举替代常用的字符串类型，枚举可以把一些不重复的字符串存储成预定义的集合。枚举字段是按照内部存储的整数而不是字符串进行排序的。枚举不好的地方是当向枚举中增加字段的时候,需要使用ALTER TABLE语句来进行修改。所以，对将来可能会变的字符串，使用枚举不是个好的主意

日期和时间类型

通常应该尽量使用TIMESTAMP，它比DATETIME空间效率更高，如果需要将时间保存到毫秒级别，可以使用BIGINT

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，bit(2)存储两个位。bit列最多存储64个位MySQL将BIT当作字符串类型，而不是数字类型。当检索bit(1)时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的"0"或"1"。然后，在数字上下文场景中检索时，结果将是为字符串转换成的数字

选择标识符

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

关于对象关系映射(ORM)系统

写得很烂的schema迁移程序，或者自动生成schema的程序，都会导致严重的性能浪费问题。有些程序存储任何东西都会使用很大的VARCHAR列，对象关系映射(ORM) 系统（以及使用它们的“框架”）是另一种常见的性能噩梦，一些ORM系统会存储任意类型的数据到任意类型的后端数据存储中，这通常意味着其没有设计使用更优的数据类型来存储这种设计对开发者很有吸引力，因为这使得他们可以用面向对象的方式工作，不需要考虑数据是怎么存储的。然而，“对开发者隐藏复杂性”的应用通常不能很好地扩展。我们建议在用性能交换开发人员的效率之前仔细考虑，并且总是在真实大小的数据集.上做测试，这样就不会太晚才发现性能问题

MySQL schema设计中的陷阱

• 太多的列：列太多，所需的列不多，容易造成转换问题
• 太多的关联：mysql限制最多关联61张表，单个查询建议在12个表以内
• 全能的枚举：注意防止过度使用枚举
• 变相的枚举：枚举（ENUM）和集合（SET）不要混用
• 非此发明(Not Invent Here)的NULL：尽量避免使用NULL，且建议尽可能地考虑替代方案，但如果除了NULL值，没有更好的代替值时，尽情使用NULL吧，因为强行使用代替值，可能会有造成一些程序问题

范式与反范式

范式的优点和缺点

• 范式化的更新操作通常比反范式化要快

• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据

• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快

• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY 语句

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

反范式的优点和缺点

• 避免了关联

• 避免了随机I/O

• 更有效的使用索引策略

反范式的缺点是容易造成数据冗余，并且如果表的列数过多，使用的不多，会造成查询转换性能浪费

事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧

缓存表、汇总表和计数表

缓存表表示存储那些可以简单地从schema其他表获取数据的表。

汇总表保存的是使用GROUP BY语句聚合数据的表，使用汇总表的原因是，实时计算和统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么只能在某些索引上才能有效运行

计数器表是用来统计某个操作的次数的表，我们可以在一个表中定义一个名为cnt的字段来表示操作的次数，然后每次执行了操作之后将其加1。但是，加1需要更新操作来完成，每次更新的时候要获取记录的锁，因此并发效率不高。解决这个问题，我们可以再增加一些字段slot作为随机的槽，例如增加100个slot字段，每次执行操作的时候，我们使用随机数选择某个slot，并对其进行+1更新（只用锁住部分数据，因此效率比较高）。最后统计的时候将全部记录加起来即可

更慢的写，更快的读

为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧：虽然写操作变得更慢了，但更显著地提高了读操作的性能

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE 操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是，锁住表，然后用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。一般而言，大部分ALTER TABLE操作将导致MySQL服务中断，这里介绍两种DDL操作技巧，避免这种情况：

• 一是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
• 二是通过“影子拷贝”，创建一张新表，然后通过重命名和删表操作交换两张表及里面的数据

五、创建高性能的索引

索引(在MySQL中也叫做“键(key)") 是存储引擎用于快速找到记录的一种数据结构。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级。

索引基础

数据库的索引类似于书的索引，实际的查找某个值的时候，先按照值进行查找，然后返回包含该值的数据行。索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也很重要，索引对多个列排序的依据是CREATE TABLE时定义索引的顺序，所以MySQL只能高效地使用索引的最左前缀列，这里要注意的是，创建一个包含多个列的索引，和创建多个只包含一列的索引是大不相同的

ORM工具能够产生符合逻辑的、合法的查询，除非只是生成非常基本的查询（例如仅是根据主键查询），否则它很难生成适合索引的查询

索引的类型

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

B-Tree索引

通常人们所说的索引。实际上很多存储引擎使用的是B+Tree。这里B-Tree索引适用于全键值、键值范围或键前缀查找，B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同

类型，以多列索引key(last_name, first_name, dob)为例：

• 全值匹配：指定查询的人的fitst_name, last_name和dob
• 匹配最左前缀：查找指定了last_name的记录
• 匹配列前缀：匹配某一列的值的开头部分，比如last_name以J开头
• 匹配范围值：匹配某两个索引之间的值，例如Allen和Barrymore之间的人
• 精确匹配某一列并范围匹配另一列：查找last_name为Allen，并且first_name以k开头的
• 只访问索引的查询：B-Tree通常可以支持只访问索引的查询，即查询只需要访问索引，而无需访问数据行

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

B-Tree的一些限制：

• 如果不是按照从最左列开始查找，则无法使用索引。例如无法查找只指定了first_name或者dob的记录
• 不能跳过索引中的列：不能在查找的时候只指定了last_name和dob，那么dob不会使用索引
• 如果查询的时候有某个列的查询范围，则其右边的所有列都无法使用索引优化查找。比如对last_name使用了like，那么first_name和dob将不会使用索引

哈希索引

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针，因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

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

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

空间数据索引(R-Tree)

MyISAM表支持空间索引，可以用作地理数据存储

全文索引

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

还有很多第三方的存储引擎使用不同类型的数据结构来存储索引，例如分型树索引等

索引的优点

索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，到目前为止可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用。总结下来索引共有三个优点：

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

索引三星系统

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

对于小型的表，使用全表扫描更高效；对中到大型的表，使用索引非常有效。对于特大型的表，建立和使用索引的代价会随之增长。这种情况下可以使用分区来查出一组数据，而不是一条一条地匹配

高性能的索引策略

MySQL EXPLAIN

EXPLAIN命令是查询表信息的工具，我们可以根据查询的信息做出正确的优化行为，所以这里有必要加一下它的使用介绍

• id：执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
• select_type：SELECT类型,可以为以下任何一种
  • SIMPLE:简单SELECT(不使用UNION或子查询)
  • PRIMARY:最外面的SELECT
  • UNION:UNION中的第二个或后面的SELECT语句
  • DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
  • UNION RESULT:UNION 的结果
  • SUBQUERY:子查询中的第一个SELECT
  • DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
  • DERIVED:导出表的SELECT(FROM子句的子查询)
• table：访问引用哪个表（引用某个查询，如“user”）
• type：联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
  • system:表仅有一行(=系统表)。这是const联接类型的一个特例
  • const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
  • eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型
  • ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取
  • ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行
  • index_merge:该联接类型表示使用了索引合并优化方法
  • unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高
  • index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range:只检索给定范围的行,使用一个索引来选择行
  • index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小
  • ALL:对于每个来自于先前的表的行组合,进行完整的表扫描
• possible_keys：揭示哪一些索引可能有利于高效的查找
• key：显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
• key_len：显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL
• ref：显示使用哪个列或常数与key一起从表中选择行
• rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数
• filtered：显示了通过条件过滤出的行数的百分比估计值
• Extra：该列包含MySQL解决查询的详细信息
  • Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行
  • Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行
  • range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用
  • Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行
  • Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息
  • Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果
  • Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户
  • Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描
  • Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表

独立的列

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

前缀索引和索引的选择性

索引很长的字符串会让索引变得大且慢。通常可以只索引开始部分的字符，这样可以节约索引空间，从而提高索引的效率。缺点是会降低索引的选择性。索引的选择性是指不重复的索引值和记录总数的比，显然越大越好。所以，我们需要选择足够长的前缀来保证选择性，同时又不能太长以降低索引空间

我们可以使用语句

select count(distinct left(col_name, 3)) / count(*) from tbl_name;

来统计使用3个字符的前缀选择性，同理可以计算出4个，5个等的情况。最后，选择一个合理的前缀长度即可。选择了长度之后可以像下面这样设置指定长度的索引：

alter table add key(col_nane(4));

多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“ 索引合并”(index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的

但在MySQL 5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合(union) ，AND条件的相交(intersection)，组合前两种情况的联合及相交。

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

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

如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_ switch 来关闭索引合并功能。也可以使用IGNORE INDEX 提示让优化器忽略掉某些索引

选择合适的索引顺序

如果要对多个列建立一个索引，除了上面的问题之外，还应该考虑所建的索引中列的顺序。比如，对col1, col2两列数据建立索引，那么我们的顺序应被指定为(col1, col2)还是(col2, col1)呢。我们要先知道，在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，在没有特殊要求的情况下，我们可以使用选择性来解决这个问题，我们可以将选择性比较高的列作为索引的第一列，另一列作为第二列，以次类推

聚簇索引

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

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

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。这里我们主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。

下图展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值，图中的索引列即为主键列

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

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

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

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

如果在设计表和查询时能充分利用上面的优点，那就能极大地提升性能。同时，聚簇索引也有一些缺点:

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

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值
这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作:两次B-Tree查找而不是一次。对于InnoDB，自适应哈希索引能够减少这样的重复工作

InnoDB和MyISAM的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和意外。来看看InnoDB和MyISAM是如何存储下面这个表的:

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

假设该表的主键取值为1 ~ 10 000,按照随机顺序插入并使用OPTIMIZE TABLE 命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2的值是从1 ~ 100 之间随机赋值，所以有很多重复的值

MyISAM的数据分布

MyISAM 的数据分布非常简单，所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示

在行的旁边显示了行号（可理解为地址），从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行(MyISAM 并不总是使用图中的“行号”，而是根据定长还是变长的行使用不同策略)

这种分布方式很容易创建索引。下面显示的一系列图，隐藏了页的物理细节，只显示索引中的“节点”，索引中的每个叶子节点包含“行号”。下图显示了表的主键

这里忽略了一些细节，例如前一个B-Tree节点有多少个内部节点，不过这并不影响对非聚簇存储引擎的基本数据分布的理解

那col2列上的索引又会如何呢?有什么特殊的吗?回答是没什么区别，下图显示了col2列上的索引

事实上，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引

InnoDB的数据分布

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

第一眼看上去，感觉该图和前面的MyISAM主键分布图没有什么不同，但再仔细看细节，会注意到该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储

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

还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB 二级索引的叶子节点中存储的不是“行指针”，而是主键值,并以此作为指向行的“指针” 这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB 在移动行时无须更新二级索引中的这个“指针”

下图显示了示例表的col2索引。每一个叶子节点都包含了索引列(这里是col2)，紧接着是主键值(col1)。并展示了B-Tree的叶子节点结构，但我们故意省略了非叶子节点这样的细节。InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针(下一级节点可以是非叶子节点，也可以是叶子节点)。这对聚簇索引和二级索引都适用

下图是描述InnoDB和MyISAM如何存放表的抽象图。从图中可以很容易看出InnoDB和MyISAM保存数据和索引的区别

在InnoDB表中按主键顺序插入行
如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键(surrogate key)作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_ INCREMENT 自增列。这样可以保证数据行是按顺序写人，对于根据主键做关联操作的性能也会更好
最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于1/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕:它使得聚簇索引的插人变得完全随机，这是最坏的情况，使得数据没有任何聚集特性，下面总结了一些缺点：

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

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

覆盖索引

如果一个索引包含了所有需要查询的字段的值，就称之为覆盖索引。覆盖索引就是从索引中直接获取查询结果，要使用覆盖索引需要注意select查询列中包含在索引列中，此时不用第二次查询表，所以速度很快；where条件包含索引列或者复合索引的前导列；查询结果的字段长度尽可能少

使用延迟关联解决索引无法覆盖问题：下面的解决方法对效率的提升不是绝对的

 SELECT * FROM products WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%'

上面的SQL中要查询全部的列，而我们没有覆盖全部列的索引，因此没有覆盖索引。另外，like操作无法使用索引，因为like操作只有在匹配左前缀时才能使用索引

我们可以像下面这样解决问题：

SELECT * FROM products 
    JOIN (SELECT prod_id FROM products 
          WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%')
AS t1 ON (t1.prod_id = products.prod_id)

这里，需要先建立(actor, title, prod_id)索引。我们先在子查询中找到匹配的prod_id，然后跟外层中数据进行匹配来获取所有列值。当符合where条件的数据数量远小于actor过滤出的数据数量的时候，它的效率尤其高。因为，根据子查询的where过滤出数据之后才与外层查询关联，而后者使用actor读取出数据之后，再用title进行关联。前者需要读取的数据量更少

按索引扫描来排序

生成有序结果的两种方式：排序，按索引顺序扫描。当explain出的type为index时，说明使用索引扫描来进行排序。MySQL可以使用一个索引既满足排序，又满足查找。只有当索引的列顺序和ORDER BY子句顺序一致，且列的排序方向都一样时，才能用索引对结果做排序

冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。但重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现后也应删除。常见的错误有：

• 使用主键和唯一约束时与已有的所冲突，因为主键和唯一约束是通过索引来实现的，如果再定义索引就会冗余，例如：无意间给主键也加个索引
• 若创建了索引(A,B)再创建索引(A)则冗余，而索引(B,A)和(B)不是，因为(B)不是最左前缀，故应该尽量扩展已有的索引而不是创建新索引

索引和锁

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少访问行的次数，所以索引能减少锁的数量

维护索引和表

即使用正确的类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片

• CHECK TABLE检查表是否损坏，ALTER TABLE innodb_tb1 ENGINE=INNODB；修复表
• records_in_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录，对于innodb不精确
• info()返回各种类型的数据，包括索引的基数
• 可以使用SHOW INDEX FROM命令来查看索引的基数
• B-Tree索引可能会碎片化，这会降低查询的效率

六、查询性能优化

为什么查询速度会慢

• 如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快
• 查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端

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

两个分析步骤

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

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

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

MySQL是否在扫描额外的记录

• 查询开销三个指标：响应时间、扫描的行数、返回的行数
• 响应时间：服务时间和排队时间之和，“快速上限估计”法
• 扫描的行数：较短的行的访问速度更快，内存中的行也比磁盘中的行的访问 速度要快得多
• 访问类型：EXPLAIN中的type列反应了访问类型；通过增加合适的索引
• 三种方式应用WHERE条件：在索引中使用WHERE条件来过滤不匹配的记录；使用索引覆盖扫描（Extra中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果；从数据表中返回数据，然后过滤不满足条件的记录（Extra中出现Using Where）
• 需要扫描大量数据但只返回少数的行的优化技巧：使用索引覆盖扫描，改变库表结构，重写复杂的查询

重构查询的方式

• MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效
• 切分查询，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果，可以避免锁住很多数据、占满事务日志、耗尽系统资源、阻塞很多小的但重要的查询
• 分解关联查询优势：
  • 让缓存的效率更高
  • 将查询分解后，执行单个查询可以减少锁的竞争
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
  • 查询本身效率也可能会有所提升
  • 可以减少冗余记录的查询
  • 相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联
• 分解关联查询的场景
  • 当应用能够方便地缓存单个查询的结果的时候当
  • 可以将数据分布到不同的MySQL服务器上的时候
  • 当能够使用IN()的方式代替关联查询的时候
  • 当查询中使用同一个数据表的时候

查询执行的基础

• 查询执行路径
  • 客户端发送一条查询给服务器
  • 服务器先检查查询缓存，如果命中则立刻返回，否则进入下一阶段
  • 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
  • MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
  • 将结果返回给客户端
• MySQL客户端和服务器之间的通信协议是“半双工”的，无法将一个消息切成小块独立来发送，没法进行流量控制，一旦一端开始发生消息，另一端要接收完整个消息才能响应它
• MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力
• 查询状态，SHOW FULL PROCESSLIST命令查看：
  • Sleep，线程正在等待客户端发送新的请求
  • Query，线程正在执行查询或者正在将结果发送给客户端
  • Locked，在MySQL服务器层，该线程正在等待表锁
  • Analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划
  • Copying to tmp table [on disk]，线程正在执行查询，并且将其结果集都复制到一个临时表中，要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作
  • Sorting result，线程正在对结果集进行排序
  • Sending data，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据
• 语法解析器和预处理，通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”，解析器将使用MySQL语法规则验证和解析查询，预处理器则根据一些MySQL规则进一步检查解析树是否合法
• 查询优化器，找到最好的执行计划，使用基本成本的优化器，将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个，使用SHOW STATUS LIKE ‘Last_query_cost’;查看需要多少个数据页的随机查找
• 导致MySQL查询优化器选择错误的原因：
  • 统计信息不准确，Innodb不能维护一个数据表的行数的精确统计信息执行计划中的成本估算不等同于实际执行的成本
  • MySQL的最优可能和你想的最优不一样
  • MySQL从不考虑其他并发执行的查询
  • MySQL也并不是任何时候都是基于成本的优化
  • MySQL不会考虑不受其控制的操作的成本
  • 优化器有时候无法去估算所有可能的执行计划
• MySQL能处理的优化类型
  • 重新定义关联表的顺序
  • 将外链接转化成内链接
  • 使用等价变换规则
  • 优化COUNT()、MIN()和MAX()，在EXPLAIN中可以看到“Select tables optimized away”
  • 预估并转化为常数表达式，当检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理
  • 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，就可以使用索引返回需要的数据，而无须查询对应的数据行
  • 子查询优化
  • 提前终止查询，在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询
  • 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上
  • 列表IN()的比较，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件
• 在服务器层有查询优化器，却没有保存数据和索引的统计信息，统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息
• 在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联
• 对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询
• MySQL对任何关联都执行“嵌套循环关联”操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止
• 全外连接就无法通过嵌套循环和回溯的方式完成，当发现关联表中没有找到任何匹配行的时候，则可能是因为关联恰好从一个没有任何匹配的表开始，MySQL不支持全外连接
• 关联查询优化器，会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树，如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行树的成本，最后返回一个最优的执行计划
• 如果有超过n个表的关联，那么需要检查n的阶乘关联顺序，称为“搜索空间”，搜索空间的增长速度非常快
• 无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序
• 当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，MySQL将这个过程称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此

MySQL查询优化器的局限性

• 关联子查询：MySQL的子查询实现得非常糟糕，最糟糕的一类查询是WHERE条件中包含IN()的子查询语句，使用GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表，或者使用EXISTS()来改写
• UNION的限制：有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上
• MySQL无法利用多核特性来并行执行查询
• MySQL不支持哈希关联，MariaDB已经实现了哈希关联
• MySQL不支持松散索引扫描，5.0后版本在分组查询中需要找到分组的最大值和最小值时可以使用松散索引扫描
• 对于MIN()和MAX()查询，MySQL的优化做得并不好

查询优化器的提示

• HIGH_PRIORITY和LOW_PRIORITY，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些
• DELAYED，对INSERT和REPLACE有效，会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入，并不是所有的存储引擎都支持，并且该提示会导致函数LAST_INSERT_ID()无法正常工作
• STRAIGHT_JOIN，可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联，第二个用法则是固定其前后两个表的关联顺序
• SQL_SMALL_RESULT和SQL_BIG_RESULT，只对SELECT语句有效，它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序
• 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，告诉优化器使用或者不使用哪些索引来查询记录
• MySQL5.0后新增的用来控制优化器行为的参数：
  • optimizer_search_depth，控制优化器在穷举执行时的限度
  • optimizer_prune_level，让优化器会根据需要扫描的行数来决定是否跳过某些执行计划
  • optimizer_switch，包含了一些开启/关闭优化器特性的标志位

优化特定类型的查询

• 优化COUNT()查询
  • COUNT()是一个特殊的函数，有两种非常不同的作用：可以统计某个列值的数量，也可以统计行数，在统计列值时要求列值是非空的（不统计NULL）
  • COUNT(*)并不是会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数，当MySQL确认括号内的表达值不可能为空时，实际上就是在统计行数
  • MyISAM的COUNT()函数只有没有任何WHERE条件下的COUNT(*)才非常快
  • 使用近似值，如EXPLAIN出来的优化器估算行数
  • 使用索引覆盖
  • 使用汇总表
  • 使用外部缓存系统
• 优化关联查询
  • 确保ON或者USING子句中的列上有索引
  • 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列
  • 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方
• 优化子查询
  • 尽可能使用关联查询代替，如果使用MySQL5.6以上或MariaDB则可以忽略这个建议
• 优化GROUP BY和DISTINCT
  • 使用索引优化
  • 当无法使用索引时，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组
  • 尽可能的将WITH ROLLUP（超级聚合）功能移动应用程序中处理
• 优化LIMIT分页
  • 最简单的办法是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);
  • offset会导致MySQL扫描大量不需要的行然后再抛弃掉，如果可以记录上次取数据的位置，下次就可以直接从该记录的位置开始扫描，可以避免使用offset
  • 使用预先计算的汇总表，或者关联到一个冗余表
• 优化UNION查询
  • 通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用，经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中
  • 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL

七、MySQL高级特性

分区表

• 对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实际上是对一组底层表的句柄对象（Handler Object）的封装

• 适用场景：

  • 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
  • 分区表的数据更容易维护
  • 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
  • 可以使用分区表来避免某些特殊的瓶颈
  • 如果需要，还可以备份和恢复独立的分区

• 使用限制：

  • 一个表最多只能有1024个分区
  • 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区
  • 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
  • 分区表中无法使用外键约束

• 使用分区表

• 在数据量超大的时候，B-Tree索引就无法起作用了，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，将产生大量随机I/O

• 保证大数据量的可扩展性两个策略：

  • 命题扫描数据，不要任何索引
  • 索引数据，并分离热点

• 分区策略的问题：

  • NULL值会使分区过滤无效
  • 分区列和索引列不匹配
  • 选择分区的成本可能很高
  • 打开并锁住所有底层表的成本可能很高
  • 维护分区的成本可能很高
  • 所有分区都必须使用相同的存储引擎
  • 分区函数中可以使用的函数和表达式也有一些限制
  • 某些存储引擎不支持分区
  • 对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作
  • 对于MyISAM表，使用分区表时需要打开更多多的文件描述符

• 查询优化

  • 很重要的一点是要在WHERE条件中带入分区列

  • 只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式是分区函数也不行

视图

• 视图本身是一个虚拟表，不存放任何数据，返回的数据是MySQL从其他表中生成的
• MySQL使用两种算法：合并算法（MERGE）和临时表算法（TEMPTABLE），会尽可能地使用合并算法
• 如果视图中包含GROUP BY、DISTINCT、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，MySQL都将使用临时表算法来实现视图
• 可更新视图（updatable view）是指可以通过更新这个视图来更新视图涉及的相关表，CHECK OPTION表示任何通过视图更新的行，都必须符合视图本身的WHERE条件定义
• 在重构schema的时候可以使用视图，使得在修改视图底层表结构的时候，应用代码还可能继续不报错运行
• MySQL中不支持物化视图（指将视图结果数据存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中）
• 不会保存视图定义的原始SQL语句

外键约束

• 使用外键是有成本的，通常要求每次在修改数据时都要在另外一张表中多执行一次查找操作
• 如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高得多，在相关数据的删除和更新上，也比在应用中维护要更高效
• 外键会带来很大的额外消耗

在MySQL内部存储代码

• MySQL允许通过触发器、存储过程、函数的形式来存储代码，从5.1开始还可以在定时任务中存放代码，这个定时任务也被称为“事件”。存储过程和存储函数都被统称为“存储程序”

• 存储代码的优点：

  • 它在服务器内部执行，离数据最近，另外在服务器上执行还可以节省带宽和网络延迟
  • 这是一种代码重用，可以方便地统一业务规则，保证某些行为总是一致，所以也可以为应用提供一定的安全性
  • 它可以简化代码的维护和版本更新
  • 可以帮助提升安全，比如提供更细粒度的权限控制
  • 服务器端可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗
  • 因为是在服务器端部署的，所以备份、维护都可以在服务器端完成
  • 可以在应用开发和数据库开发人员之间更好地分工

• 存储代码的缺点：

  • MySQL本身没有提供好用的开发和调试工具
  • 较之应用程序的代码，存储代码效率要稍微差些
  • 存储代码可能会给应用程序代码的部署带来额外的复杂性
  • 因为存储程序都部署在服务器内，所以可能有安全隐患
  • 存储过程会给数据库服务器增加额外的压力，而数据库服务器的扩展性相比应用服务器要差很多
  • MySQL并没有什么选项可以控制存储程序的资源消耗，所以在存储过程中的一个小错误，可能直接把服务器拖死
  • 存储代码在MySQL中的实现也有很多限制——执行计划缓存是连接级别的，游标的物化和临时表相同，异常处理也非常困难
  • 调试MySQL的存储过程是一件很困难的事情
  • 它和基于语句的二进投影日志复制合作得并不好

  基于以上原因阿里手册里都强制禁止使用存储过程方式操作SQL

• 存储过程和函数的限制：

  • 优化器无法使用关键字DETERMINISTIC来优化单个查询中多次调用存储函数的情况
  • 优化器无法评估存储函数的执行成本
  • 每个连接都有独立的存储过程的执行计划缓存
  • 存储程序和复制是一组诡异组合

• 触发器：可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作，可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发，可以使用触发器实现一些强制限制，或者某些业务逻辑，否则，就需要在应用程序中实现这些逻辑

• 触发器的注意和限制：

  • 对每一个表的每一个事件，最多只能定义一个触发器
  • 只支持“基于行的触发”，也就是说，触发器是针对一条记录的，而不是针对整个SQL语句的，如果变更的数据集非常大的话，效率会很低
  • 触发器可以掩盖服务器背后的工作
  • 触发器可以掩盖服务器背后的工作，一个简单的SQL语句背后可能包含了很多看不见的工作
  • 触发器的问题也很难排查，如果某个性能问题和触发器相关，会很难分析和定位
  • 触发器可能导致死锁和锁等待
  • 触发器并不能一定保证更新的原子性

• 触发器的用处：

  • 实现一些约束、系统维护任务，以及更新反范式化数据的时候

  • 记录数据变更日志

• 事件：类似于Linux的定时任务，指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码

游标

• MySQL在服务器端提供提供只读的、单向的游标，而且只能在存储过程或者更底层的客户端API中使用，指向的对象都是存储在临时表中而不是实际查询到的数据，所以总是只读的
• 会带来额外的性能开销
• 不支持客户端的游标

绑定变量

• 当创建一个绑定变量SQL时，客户端向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄

• 可以更高效地执行大量的重复语句：

  • 在服务器端只需要解析一次SQL语句
  • 在服务器端某些优化项的工作只需要执行一次，因为它会缓存一部分的执行计划
  • 以二进制的方式只发送参数和句柄，比起每次都发送ASC2码文本效率更高
  • 仅仅是参数——而不是整个查询语句——需要发送到服务器端，所以网络开销会更小
  • MySQL在存储参数的时候，直接将其存放到缓存中，不再需要在内存中多次复制

• 绑定变量相对也更安全。无须在应用程序中处理转义，一则更简单了，二则也大大减少了SQL注入和攻击的风险

• 最主要的用途就是在存储过程中使用，构建并执行“动态”的SQL语句

• 绑定变量的限制：

  • 绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄

  • 在5.1版本之前，绑定变量的SQL是不能使用查询缓存的

  • 并不是所有的时候使用绑定变量都能获得更好的性能

  • 如果总是忘记释放绑定变量资源，则在服务器端很容易发生资源“泄漏”

  • 有些操作，比如BEGIN，无法在绑定变量中完成

• 用户自定义函数

  • 用户自定义函数（UDF）必须事先编译好并动态链接到服务器上，这种平台相关性使得UDF在很多方面都很强大，但一个错误也很可能让服务器直接崩溃，甚至扰乱服务器的内存或者数据

插件

• 插件可以在MySQL中新增启动选项和状态值，还可以新增INFORMATION_SCHEMA表，或者在MySQL的后台执行任务等等

• 在5.1后支持的插件接口：

  • 存储过程插件

  • 后台插件，可以让程序在MySQL中运行，可以实现自己的网络监听、执行自己的定期任务

  • INFORMATION_SCHEMA插件，提供一个新的内存INFORMATION_SCHEMA表

  • 全文解析插件，提供一种处理文本的功能，可以根据自己的需求来对一个文档进行分词

  • 审计插件，在查询执行的过程中的某些固定点被调用，可以记录MySQL的事件日志

  • 认证插件，既可可以在MySQL客户端也可在它的服务器端，可以使用这类插件来扩展MySQL的认证功能

字符集和校对

• 字符集是一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节来表示英文字母。“校对”是指一组用于某个字符集的排序规则
• 每种字符集都可能有多种校对规则，并且都有一个默认的校对规则，每个校对规则都是针对某个特定的字符集的，和其他的字符集没有关系
• MySQL有很多的选项用于控制字符集，这些选项和字符集很容易混淆，只有基于字符的值才真正的“有”字符集的概念
• MySQL的两类设置：创建对象时的默认设置、服务器和客户端通信时的设置
• 如果比较的两个字符串的字符集不同，MySQL会先将其转成同一个字符集再进行比较
• 一些需要注意的地方：
  • 诡异的character_set_database设置，当改变默认数据库的时候，这个变量也会跟着变，所以当连接到MySQL实例上又没有指定要使用的数据库时，默认值会和character_set_server相同
  • LOAD DATA INFILE，当使用时，数据库总是将文件中的字符按照字符集character_set_database来解析
  • SELECT INTO OUTFILE，MySQL会将结果不做任何转码地写入文件
  • 嵌入式转义序列，MySQL会根据character_set_client的设置来解析转义序列
• 某些字符集和校对规则可能会需要更多的CPU操作，可能会消耗更多的内存和存储空间，甚至还会影响索引的正常使用
  • 不同的字符集和校对规则之间的转换可能会带来额外的系统开销
  • 只有排序查询要求的字符集与服务器数据的字符集相同的时候，才能使用索引进行排序
  • 为了能够适应各种字符集，包括客户端字符集、在查询中显式指定的字符集，MySQL会在需要的时候进行字符集转换

全文索引

• MyISAM的全文索引作用对象是一个“全文集合”，这可能是某个数据表的一列，也可能是多个列
• 可以根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引
• 在使用全文索引进行排序的时候，MySQL无法再使用索引排序，如果不想使用文件排序的话，就不要在查询中使用ORDER BY子句
• 在布尔搜索中，用户可以在查询中自定义某个被搜索的词语的相关性，可能通过一些前缀修饰符来定制搜索
• 全文索引在INSERT、UPDATE、DELETE中的操作代价很大
• 全文索引会影响索引选择、WHERE子句、ORDER BY等：
  • 如果查询中使用了MATCH AGAINST子句，而对应列上又有可用的全文索引，那么MySQL就一定会使用这个全文索引
  • 全文索引只能用作全文搜索匹配
  • 全文索引不存储索引列的实际值，也就不可能用作索引覆盖扫描
  • 除了相关性排序，全文索引不能用作其他的排序
• 全文索引的配置和优化：
  • 经常使用OPTIMIZE TABLE来减少碎片，如果是I/O密集型的定期进行全文索引重建
  • 保证索引缓存足够大
  • 提供一个好的停用词表
  • 忽略一些太短的单词
  • 导入大量数据时，最好通过命令DISABLE KEYS来禁用全文索引，然后导入结束后使用ENABLE KEYS来建立全文索引
  • 如果数据集特别大，则需要对数据进行手动分区，然后将数据分布到不同的节点，再做并行的搜索

分布式（XA）事务

• XA事务中需要有一个事务协调器来保证所有的事务参与者都完成了准备工作。如果协调器收到所有的参与者都准备好的消息，就会告诉所有的事务可以提交了，MySQL在这个XA事务过程中扮演一个参与者的角色，而不是协调者
• 因为通信延迟和参与者本身可能失败，所以外部XA事务比内部消耗会更大

查询缓存

• MySQL查询缓存保存查询返回的完整结果，当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段

• MySQL判断缓存命中的方法很简单：缓存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了如下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息

• 当判断缓存是否命中时，MySQL不会解析、“正规化”或者参数化查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。任何字符上的不同，例如空格、注释——都会导致缓存的不命中

• 当查询语句中有一些不确定的数据时，则不会被缓存，例如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存，只要包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，或者任何包含列级别权限的表，都不会被缓存

• 打开查询缓存对读和写操作都会带来额外的消耗：

  • 读查询在开始之前必须先检查是否命中缓存

  • 如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗

  • 当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效，如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大系统消耗

• 对于需要消耗大量资源的查询通常都是非常适合缓存的

• 缓存未命中：

  • 查询语句无法被缓存
  • MySQL从未处理这个查询
  • 查询缓存的内存用完了
  • 查询缓存还没有完成预热
  • 查询语句之前从未执行过
  • 缓存失效操作太多了

• 缓存参数配置：

  • query_cache_type，是否打开查询缓存
  • query_cache_size，查询缓存使用的总内存空间
  • query_cache_min_res_unit，在查询缓存中分配内存块时的最小单位，可以帮助减少由碎片导致的内存空间浪费
  • query_cache_limit，MySQL能够缓存的最大查询结果
  • query_cache_wlock_invalidate，如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果

• InnoDB和查询缓存

  • 事务是否可以访问查询缓存取决于当前事务ID，以及对应的数据表上是否有锁
  • 如果表上有任何的锁，那么对这个表的任何查询语句都是无法被缓存的

• 通用查询缓存优化：

  • 用多个小表代替一个大表对查询缓存有好处
  • 批量写入时只需要做一次缓存失效，所以相比单条写入效率更好
  • 因为缓存空间太大，在过期操作的时候可能会导致服务器僵死，控制缓存空间的大小
  • 无法在数据库或者表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存
  • 对于写密集型的应用来说，直接禁用查询缓存可能会提高系统的性能
  • 因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用也会有好处

八、优化服务器设置

MySQL配置的工作原理

• 任何打算长期使用的设置都应该写到全局配置文件，而不是在命令行特别指定，命令行指定重启服务后，配置将消失
• 常用变量和动态修改它们的效果：
  • key_buffer_size，可以一次性为键缓冲区（key buffer，也叫键缓存key cache）分配所有指定的空间
  • table_cache_size，不会立即生效——会延迟到下次有线程打开表才有效果，如果值大于缓存中表的数量，线程可以把最新打开的表放入缓存，如果比缓存中的表数小，将从缓存中删除不常使用的表
  • thread_cache_size，不会立即生效——将在下次有连接被关闭时产生效果，检查缓存中是否还有空间来缓存线程，如果有空间，则缓存该线程以备下次连接征用，如果没空间，将销毁该线程而不再缓存
  • query_cache_size，一次性分配并初始化这块内存
  • read_buffer_size，只在有查询需要使用时才会为该缓存分配内存
  • read_rnd_buffer_size，只在有查询需要使用时才会为该缓存分配内存，并且只会分配需要的内存大小而不是全部指定的大小
  • sort_buffer_size，只会在有查询需要做排序时才会为该缓存分配内存
• 对于连接级别的设置，不要轻易地在全局级别增加它们的值，除非确认这样做是对的
• 设置变量时请小心，并不是值越大就越好，而且如果设置的值太高，可能更容易导致问题：可能会由于内存不足导致服务器内存交换，或者超过地址空间
• 不要期望通过建立一套基准测试方案，然后不断迭代地验证对配置项的修改来找到最佳配置方案，而要把时间花在检查备份、监控执行计划的变动之类的事情上，可能会更有意义

什么不该做

• 不要根据一些“比率”来调优：例如缓存命中率跟缓存是否过大或过小没有关系
• 不要使用调优脚本
• 不要相信很流行的内存消耗公式

创建MySQL配置文件

• MySQL编译的默认设置并不都是靠谱的，虽然其中大部分都比较合适
• 从一个比默认值大一点但不是大得很离谱的安全值开始是比较好的，MySQL的内存利用率并不总是可以预测的：它可能依赖很多的因素，例如查询的复杂性和并发性
• 配置服务器的首选途径：了解它内部做了什么，以及参数之间如何相互影响，然后再决定怎么配置
• open_files_limit，在Linux系统上设置得尽可能大，如果参数不够大，将会踫到24号错误“打开的文件太多（too many open files）”
• 每隔60秒查看状态变量的增量变化：mysqladmin extended-status ri60

配置内存使用

• 配置MySQL正确地使用内存量对高性能是至关重要的，内存消耗分为两类：可以控制的内存和不可以控制的内存

• 配置内存：

  • 确定可以使用的内存上限

  • 确定每个连接MySQL需要使用多少内存

  • 确定操作系统需要多少内存才够用

  • 把剩下的内存全部给MySQL的缓存

• MySQL保持一个连接（线程）只需要少量的内存，它还需要一个基本量的内存来执行任何给定查询，需要为高峰时期执行的大量查询预留好足够的内存，否则，查询执行可能因为缺乏内存而导致执行效率不佳或执行失败

• 跟查询一样，操作系统也需要保留足够的内存给它工作，如果没有虚拟内存正在交换（Paging）到磁盘，就是表明操作系统内存足够的最佳迹象

• 如果服务器只运行MySQL，所有不需要为操作系统以及查询处理保留的内存都可以用作MySQL缓存

• 大部分情况下最重要的缓存：

  • InnoDB缓冲池
  • InnoDB日志文件和MyISAM数据的操作系统缓存
  • MyISAM键缓存
  • 查询缓存
  • 无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存

• InnoDB缓冲池并不仅仅缓存索引：它还会缓存行的数据、自适应哈希索引、插入缓冲（Insert Buffer）、锁，以及其他内部数据结构，还使用缓冲池来帮助延迟写入，InnoDB严重依赖缓冲池

• 如果事先知道什么时候需要关闭InnoDB，可以在运行时修改innodb_max_dirty_pages_pct变量，将值改小，等待刷新纯种清理缓冲池，然后在脏页数量较少时关闭，可以监控the Innodb_buffer_pool_pages_dirty状态变量或者使用innotop来监控SHOW INNODB STATUS来观察脏页的刷新量

• MyISAM的键缓存也被称为键缓冲，默认只有一个键缓存，但也可以创建多个，MyISAM自身只缓存索引，不缓存数据，最重要的配置项是key_buffer_size，不要超过索引的总大小，或者不超过操作系统缓存保留总内存的25%-50%，以更小的为准

• 了解MyISAM索引实际上占用多少磁盘空间，查询INFORMATION_SCHEMA表的INDEX_LENGTH字段，把它们的值相加，就可以得到索引存储占用空间

• 块大小也是很重要的（特别是写密集型负载），因为它影响了MyISAM、操作系统缓存，以及文件系统之间的交互，如果缓存块太小，可能会踫到写时读取

• 线程缓存保存那些当前没有与连接关联但是准备为后面新的连接服务的线程，当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接，当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存，如果没有空间的话，MySQL会销毁这个线程

• thread_cache_size变量指定了MySQL可以保持在缓存中的线程数，一般不需要配置这个值，除非服务器会有很多连接请求

• 表缓存（Table Cache）和线程缓存的概念是相似的，但存储的对象代表的是表，缓存对象包含相关表.frm文件的解析结果，加上其他数据。表缓存可以重用资源，让服务器避免修改MyISAM文件头来标记表“正在使用中”，对InnoDB的重要性要小得多

• 表缓存的缺点是，当服务器有很多MyISAM表时，可能会导致关机时间较长，因为关机前索引块必须完成刷新，表都必须标记为不再打开

• InnoDB数据字典（Data Dictionary），InnoDB自己的表缓存，当InnoDB打开一张表，就增加了一个对应的对象到数据字典

• InnoDB没有将统计信息持久化，而是在每次打开表时重新计算，5.6以后可以打开innodb_use_sys_stats_table选项来持久化存储统计信息到磁盘

• 可以关闭InnoDB的innodb_stats_on_metadata选项来避免耗时的表统计信息刷新

• 如果可以，最好把innodb_open_files的值设置得足够大以使服务器可以保持所有的.ibd文件同时打开

配置MySQL的I/O行为

• InnoDB I/O配置
  • InnoDB不仅允许控制怎么恢复，还允许控制怎么打开和刷新数据（文件），这会对恢复和整体性能产生巨大的影响
  • 对于常见的应用，最重要的一小部分内容是InnoDB日志文件大小、InnoDB怎样刷新它的日志缓冲，以及InnoDB怎样执行I/O
  • 整体的日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group两个参数，对写性能非常重要
  • 通常不需要把日志缓冲区设置得非常大，推荐的范围是1MB-8MB，除非要写很多相当大的BLOB记录
  • 可以通过检查SHOW INNODB STATUS的输出中LOG部分来监控InnoDB的日志和日志缓冲区的I/O性能，通过观察Innodb_os_log_written状态变量来查看InnodDB对日志文件写出了多少数据。日志文件的全部大小，应该足够容纳服务器一个小时的活动内容
  • 如果和持久相比更在乎性能，可以修改innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度
  • 使用innodb_flush_method选项可以配置InnoDB如何跟文件系统相互作用
  • InnoDB用表空间并不只是存储表和索引，还保存了回滚日志、插入缓冲（Insert Buffer）、双写缓冲（Doublerite Buffer）及其他内部数据结构
  • 为了控制写入速度，可以设置innodb_max_purge_lag变量为一个大于0的值，这个值表示InnoDB开始延迟后面的语句更新数据之前，可以等待被清除的最大的事务数量
  • 双写缓冲是表空间的一个特殊的保留区域，在一些连续的块中足够保存100个页，本质上是一个最近写回的页面的备份拷贝，当InnoDB从缓冲池刷新页面到磁盘时，首先把它们写（或者刷新）到双写缓冲，然后再把它们写到其所属的数据区域中，这可以保证每个页面的写入都是原子并且持久化的
  • 设置innodb_doublewrite为0来关闭双写缓冲
  • sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘
  • 二进制日志，如果希望使用expire_logs_days选项来自动清理旧的二进制日志，就不要用rm命令去删
• MyISAM的I/O配置
  • MyISAM通常每次写操作之后就把索引变更刷新磁盘，批量操作会更快一些
  • 通过设置delay_key_write变量，可以延迟索引的写入，修改的键缓冲块直到表被关闭才会刷新
  • myisam_recover选项控制MyISAM怎样寻找和修复错误
  • 内存映射使得MyISAM直接通过操作系统的页面缓存访问.MYD文件，避免系统调用的开销，5.1后可以通过myisam_use_mmap选项打开内存映射

配置MySQL并发

• InnoDB并发配置

  • InnoDB有自己的“线程调度器”控制线程怎么进入内核访问数据，以及它们在内核中一次可以做哪些事，最基本的限制并发的方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核

  • 并发值 = CPU数量 * 磁盘数量 * 2，在实践中使用更小的值会更好一点

• MyISAM并发配置

  • 尽管MyISAM是表级锁，它依然可以一边读取，一边并发追加新行，这种情况下只能读取到查询开始时的所有数据，新插入的数据是不可见的，这样可以避免不一致读

  • 通过设置concurrent_insert这个变量，可以配置MyISAM打开并发插入

  • 让INSERT、REPLACE、DELETE、UPDATE语句的优先级比SELECT语句更低，设置low_priority_updates选项就可以

基于工作负载的配置

• 当服务器满载情况下运行时，请尝试记录所有的查询语句，因为这是最好的方式来查看哪种类型的查询语句占用资源最多，同时创建processlist快照，通过state或者command字段来聚合它们
• 优化BLOB和TEXT场景
  • BLOB有几个限制使得服务器对它的处理跟其他类型不一样，不能在内存临时表中存储BLOB值，效率很低
  • 通过SUBSTRING()函数把值转换为VARCHAR
  • 让临时表更快一些：放在基于内存的文件系统
  • 如果使用的是InnoDB，也可以调大InnoDB日志缓冲大小
  • 大字段在InnoDB里可能浪费大量空间
  • 扩展存储禁用了自适应哈希，因为需要完整地比较列的整个长度，才能发现是不是正确的数据
  • 太长的值可能使得查询中作为WHERE条件不能使用索引
  • 如果一张表里有很多大字段，最好是把它们组合起来单独存到一个列里面
  • 有时候可以把大字段用COMPRESS()压缩后再存为BLOB，或者发送到MySQL前在应用程序中进行压缩
• 优化排序（Filesorts）：当MySQL必须排序BLOG或TEXT字段时，它只会使用前缀，然后忽略剩下部分的值

完成基本配置

• tmp_table_size和max_heap_table_size，这两个设置控制使得Memory引擎的内存临时表能使用多大的内存
• max_connections，这个设置的作用就像一个紧急刹车，以保证服务器不会因应用程序激增的连接而不堪重负，设置得以容纳正常可能达到的负载，并且要足够安全，能保证允许你登录和管理服务器
• thread_cache_size，可以通过观察服务器一段时间的活动，来计算一个有理有据的值，250的上限是一个不错的估算值
• table_cache_size，应该被设置得足够大，以避免总是需要重新打开和重新解析表的定义，可能通过观察Open_tables的值及其在一段时间的变化来检查该变量

安全和稳定的设置

• expire_logs_days，如果启用了二进制日志，应该打开这个选项，可以让服务器在指定的天数之后清理旧的二进制日志
• max_allowed_packet，防止服务器发送太大的包，也会控制多大的包可以被接收
• max_connect_errors，如果知道服务器可以充分抵御蛮力攻击，可以把这个值设得非常大，以有效地禁用主机黑名单
• skip_name_resolve，禁用了另一个网络相关和鉴权谁相关的陷阱：DNS查找
• sql_mode，不建议修改
• sysdate_is_now，可能导致与应用预期向后不兼容的选项
• read_only，禁止没有特权的用户在备库做变更，只接受从主库传输过来的变更，不接受从应用来的变更，可以把备库设置为只读模式
• skip_slave_start，阻止MySQL试图自动启动复制
• slave_net_timeout，控制备库发现跟主库的连接已经失败并且需要重连之前等待的时间，设置为一分钟或更短
• sync_master_info、sync_relay_log、sync_relay_log_info，5.5以后版本可用，解决了复制中备库长期存在的问题：不把它们的状态文件同步到磁盘，所以服务器崩溃后可能需要人来猜测复制的位置实际上在主库是哪个位置，并且可能在中继日志（Relay Log）里有损坏

高级InnoDB设置

• innodb，如果设置为FORCE，只有在InnoDB可以启动时，服务器才会启动
• innodb_autoinc_lock_mode，控制InnoDB如何生成自增主键值
• innodb_buffer_pool_instances，在5.5以后，可以把缓冲池切分为多段，在高负载的多核机器上提升MySQL可扩展性的一个重要方式
• innodb_io_capacity，有时需要把这个设置得相当高，才能稳定地刷新脏页
• innodb_read_io_threads和innodb_write_io_threads，控制有多少后台线程可以被I/O操作使用
• innodb_strict_mode，让MySQL在某些条件下把警告改成抛错，尤其是无效的或者可能有风险的CREATE TABLE选项
• innodb_old_blocks_time，指定一个页面从LRU链表的“年轻”部分转移到“年老”部分之前必须经过的毫秒数，默认为0，设置为1000毫秒（1秒）非常有效

九、操作系统和硬件优化

什么限制了MySQL的性能

• 当数据可以放在内存中或者可以从磁盘中以足够快的速度读取时，CPU可能出现瓶颈，把大量的数据集完全放到大容量的内存中，以现在的硬件条件完全是可行的
• I/O瓶颈，一般发生在工作所需的数据远远超过有效内存容量的时候，如果应用程序是分布在网络上的，或者如果有大量的查询和低延迟的要求，瓶颈可能转移到网络上

如何为MySQL选择CPU

• 可以通过检查CPU利用率来判断是否是CPU密集型的工作负载，还需要看看CPU使用率和大多数重要的查询的I/O之间的平衡，并注意CPU负载是否分配均匀
• 当遇到CPU密集型的工作时，MySQL通常可以从更快的CPU中获益，但还依赖于负载情况和CPU数量
• MySQL复制也能在高速CPU下工作得非常好，而多CPU对复制的帮助却不大
• 多CPU在联机事务处理（OLTP）系统的场景中非常有用，在这样的环境中，并发可能成为瓶颈

平衡内存和磁盘资源

• 配置大量内存最终目的是避免磁盘I/O，最关键的是平衡磁盘的大小、速度、成本和其他因素，以便为工作负载提供高性能的表现
• 设计良好的数据库缓存（如InnoDB缓冲池），其效率通常超过操作系统的缓存，因为操作系统缓存是为通用任务设计的
• 数据库服务器同时使用顺序和随机I/O，随机I/O从缓存从受益最多
• 每个应用程序都有一个数据的“工作集”——就是这个工作确实需要用到的数据
• 工作集包括数据和索引，所以应该采用缓存单位来计数，一个缓存单位是存储引擎工作的数据最小单位
• 找到一个良好的内存/磁盘比例最好的方式是通过试验和基准测试
• 硬盘选择考虑因素：存储容量、传输速度、访问时间、主轴转速、物理尺寸
• MySQL如何扩展到多个磁盘上取决于存储引擎和工作负载，InnoDB能很好地扩展到多个硬盘驱动器，然而，MyISAM的表锁限制其写的可扩展性，因此写繁重的工作加在MyISAM上，可能无法从多个驱动器中收益

固态存储

• 高质量闪存设备具备：

  • 相比硬盘有更好的随机读写性能

  • 相比硬盘有更好的顺序读写性能

  • 相比硬盘能更好地支持并发

  • 提升随机I/O和并发性

• 闪存的最重要特征是可以迅速完成多次小单位读取，但是写入更有挑战性。闪存不能在没有做擦除操作前改写一个单元（Cell），并且一次必须擦除一个大块。擦除周期是缓慢的，并且最终会磨损整个块

• 垃圾收集对理解闪存很重要。为了保持一些块是干净的并且可以被写入，设备需要回收脏块。这需要设备上有一些空闲空间

• 许多设备被填满后会开始变慢，速度下降是由于没有空闲块时必须等待擦写完成所造成的

• 固态存储最适合使用在任何有着大量随机I/O工作负载的场景下，随机I/O通常是由于数据大于服务器的内存导致的，闪存设备可能大大缓解这种问题

• 单线程工作负载也是另一个闪存的潜在应用场景

• 闪存也可以为服务器整合提供巨大的帮助

• Flashcache，磁盘和内存技术的结合，适合以读为主的I/O密集型负载，并且工作集太大，用内存优化并不经济的情况

• 优化固态存储上的MySQL

  • 增加InnoDB的I/O容量
  • 让InnoDB日志文件更大
  • 把一些文件从闪存转移到RAID
  • 禁用预读
  • 配置InnoDB刷新算法
  • 禁用双写缓冲的可能
  • 限制插入缓冲大小，插入缓冲设计来用于减少当更新行时不在内存中的非唯一索引引起的随机I/O
  • InnoDB的页大小
  • 优化InnoDB页面校验（Checksum）的替代算法

为备库选择硬件

• 通常需要跟主库差不多的配置

RAID性能优化

• RAID可以帮助做冗余、扩展存储容量、缓存，以及加速
• RAID 0：如果只是简单的评估成本和性能，是成本最低和性能最高的RAID配置
• RAID 1：在很多情况下提供很好的读性能，并且在不同的磁盘间冗余数据，所以有很好的冗余性，非常适合用来存放日志或者类似的工作
• RAID 5：通过分布奇偶校验把数据分散到多个磁盘，如果任何一个盘的数据失效，都可以从奇偶校验块中重建，但如果有两个磁盘失效了，则整个卷的数据无法恢复，最经济的冗余配置。随机写是昂贵的，存放数据或者日志是一种可接受的选择，或者是以读为主的业务
• RAID 10：对数据存储是个非常好的选择，由分片的镜像组成，对读和写都有良好的扩展性
• RAID 50：由条带化的RAID 5组成

SAN和NAS

• SAN（Storage Area Network）和NAS（Network-Attached Storage）是两个外部文件存储设备加载到服务器的方法，访问SAN设备时通过块接口，NAS设备通过基于文件的协议来访问
• SAN允许服务器访问非常大量的硬盘驱动器，并且通常配置大容量智能高速缓存来缓冲写入
• 哪些工作放在SAN上不合适：执行大量的随机I/O的单线程任务
• SAN的应用：
  • 备份，可以只备份SAN
  • 简化容量规划
  • 存储整合还是服务器整合
  • 高可用
  • 服务器之间的交互
  • 成本

使用多磁盘卷

• 二进制日志和数据文件分离的真正的优势，是减少事故中同时丢失数据和日志文件的可能性
• 如果有很多磁盘，投入一些给事务日志可能会从中受益

网络配置

• 在生产服务器上启用skip_name_resolve是个好主意，损坏或缓慢的DNS解析对许多应用程序都是个问题，对MySQL尤严重，如果启用skip_name_resolve选项，MySQL将不会做任何DNS查找的工作
• 可以通过MySQL的back_log选项控制MySQL的传入TCP连接队列的大小，在每秒有很多连接创建和销毁的环境中，默认值50是不够的
• 网络物理隔离也是很重要的因素，尽可能避免实时的跨数据中心的操作是明智的

选择操作系统

• 一般的企业级部署MySQL大多在类UNIX操作系统上

选择文件系统

• 如果可能，最好使用日志文件系统，如ext3、ext4、XFS、ZFS或者JFS
• 可以调整文件系统的预读行为，因为这可能也是多余的

选择磁盘队列调度策略

• 在GUN/Linux上，队列调度决定了到块设备的请求实际上发送到底层设备的顺序，默认情况下使用cfq（Completely Fair Queueing，完全公平排队）策略，在MySQL的工作负载类型下，cfq会导致很差的响应时间，因为会在队列中延迟一些不必要的请求
• cfq之外的两个选项都适合服务器级的硬件，noop调度适合没有自己的调度算法的设备，deadline则对RAID控制器和直接使用的磁盘都工作良好

线程

• MySQL每个连接使用一个线程，另外还有内部处理线程、特殊用途的线程，以及所有存储引擎创建的线程
• MySQL确实需要内核级线程的支持，而不只是用户级线程，这样才能更有效地使用多个CPU，另外也需要有效的同步原子

内存交换区

• 内存交换对MySQL性能影响是很糟糕的，它破坏了缓存在内存的目的，并且相对于使用很小的内存做缓存，使用交换区的性能更差
• 在GNU/Linux上，可以用vmstat来监控内存交换，最好查看si和so列报告的内存交换I/O活动，这比看swpd列报告的交换区利用率更重要，最佳为0
• 设置/proc/sys/vm/swappiness为一个很小的值
• 修改存储引擎怎么读取和写入数据，使用innodb_flush_method=0_DIRECT减轻I/O压力
• 使用MySQL的memlock配置项，可以把MySQL锁定在内存

操作系统状态

• vmstat

  • vmstat 5，每隔5秒刷新一次
  • procs，r列显示多少进程正在等待CPU，b列显示多少进程正在不可中断地休眠
  • memory，swpd列显示多少块被换出到了磁盘，剩下的三个列显示了多少块是空闲的、多少块正在被用作缓冲，以及多少正在被用作操作系统的缓存
  • swap，显示页面交换活动
  • io，显示有多少块从块设备读取（ bi）和写出（bo）
  • system，显示了每秒中断（in）和上下文切换（cs）的数量
  • cpu，显示所有的CPU时间花费在各类操作的百分比

• iostat

  • iostats -dx 5，每5秒刷新

  • rrqm/s和wrqm/s，每秒合并的读和写请求，意味着操作系统从队列中拿出多个逻辑请求合并为一个请求到实际磁盘

  • r/s和w/s，每秒发送到设备的读和写请求

  • rsec/s和wsec/s，每秒读和写的扇区数

  • avgrq-sz，请求的扇区数

  • avgqu-sz，在设备队列中等待的请求数

  • await，磁盘排除上花费的毫秒数

  • svctm，服务请求花费的毫秒数，不包括排除时间

  • %util，至少有一个活跃请求所占时间的百分比

• CPU密集型的机器，vmstat输出通常在us列会有一个很高的值，也可能在sy列有很高的值

• I/O密集型工作负载下，vmstat会显示很多处理器在非中断休眠（b列）状态，并且wa这一列的值很高

• 发生内存交换的机器可能在swpd列有一个很高的值

十、复制

复制概述

• MySQL支持两种复制方式：基于行的复制和基于语句的复制，都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制

• 复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的

• 通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过写复制来扩展写操作

• 复制解决的问题：

  • 数据分布

  • 负载均衡

  • 备份

  • 高可用性和故障切换

  • MySQL升级测试

• 复制如何工作

  • 在主库上把数据更新记录到二进制日志（Binary Log）中

  • 备库将主库上的日志复制到自己的中继日志（Relay Log）中

  • 备库读取中继日志中的事件，将其重放到备库数据之上

配置复制

• 在每台服务器上创建复制帐号
  • 用来监控和管理复制的帐号需要REPLICATION CLIENT权限，并且针对这两种目的使用同一个帐号更加容易
  • 如果在主库上建立了帐号，然后从主库将数据克隆到备库时，备库也就设置好了——变成主库所需要的配置
• 配置主库和备库
  • 必须明确地指定一个唯一的服务器ID
  • 有时候只开启了二进制日志，但却没有开启log_slave_updates，可能会踫到一些奇怪的现象
  • 如果可能的话，最好使用read_only配置选项，会阻止任何没有特权权限的线程修改数据
• 通知备库连接到主库并从主库复制数据
• 推荐的复制配置
  • sync_binlog =1，在提交事务前会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件
  • 如果无法容忍服务器崩溃导致表损坏，推荐使用InnoDB
  • 推荐明确指定二进制日志的名字，log_bin=/var/lib/mysql/mysql-bin
  • 在备库上为中继日志指定绝对路径，relay_log
  • 如果正在使用5.5并且不介意额外的fsync()导致的性能开销，最好设置：sync_master_info，sync_relay_log，sync_relay_log_info

复制的原理

• 基于语句的复制

  • 5.0之前只支持基于语句的复制（也称为逻辑复制），主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍
  • 好处是实现相当简单，日志更加紧凑，不会占用太多带宽
  • 问题是基于语句的方式可能并不如其看起来那么便利，还存在一些无法被正确复制的SQL，更新必须是串行的这需要更多的锁

• 基于行的复制

  • 5.1开始支持，会将实际数据记录在二进制日志中，跟其他数据库的实现比较想像

  • 好处是可以正确地复制每一行，一些语句可以被更加有效地复制

  • 如果使用全表更新，则开销会很大，因为每一行的数据都会被记录到二进制日志中，这使得二进制日志事件非常庞大，并且会给主库上记录日志和复制增加额外的负载，更慢的日志记录则会降低并发度

• 基于行或基于语句：哪种更优

  • 基于语句的复制模式的优点：当主备的模式不同时，逻辑复制能够在多种情况下工作；基于语句的方式执行复制的过程基本上就是执行SQL语句
  • 基于语句的复制模式的缺点：很多情况下通过基于语句的模式无法正确复制，如果正在使用触发器或者存储过程，就不要使用基于语句的复制模式，除非能够清楚地确定不会踫到复制的问题
  • 基于行的复制模式的优点：几乎没有基于行的复制模式无法处理的场景；可能减少锁的使用，并不要求这种强串行化是可重复的；会记录数据变更；占用更少的CPU；能够帮助更快地找到并解决数据不致的情况
  • 基于行的复制模式的缺点：无法判断执行了哪些SQL；无法知道服务器在做什么；在某些情况下，例如找不到要修改的行时，基于行的复制可能会导致复制停止

• 复制文件

  • mysql-bin.index，二进制日志文件

  • mysql-relay-bin-index，中继日志的索引文件

  • master.info，保存备库连接到主库所需要的信息

  • relay-log.info，包含了当前备库复制的二进制日志和中继日志坐标

• 5.发送复制事件到其他备库：log_slave_updates，可以让备库变成其他服务器的主库

• 6.复制过滤选项

  • 在主库上过滤记录到二进制日志中的事件
  • 在备库上过滤记录到中继日志的事件

复制拓扑

• 基本原则：

  • 一个MySQL备库实例只能有一个主库

  • 每个备库都必须有一个唯一的服务器ID

  • 一个主库可以有多个备库

  • 如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库

• 一主库多备库

• 主动-主动模式下的主主复制：auto_increment_increment和auto_increment_offset可以让MySQL自动为INSERT语句选择不互相冲突的值

• 主动-被动模式下的主主复制：其中一台服务器是只读的被动服务器

• 拥有备库的主主结构：增加了冗余，能够消除站点单点失效的问题

• 环形复制：每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库

• 分发主库事实上也是一个备库，提取和提供主库的二进制日志

• 树或金字塔形：减轻了主库的负担，但中间层出现的任何错误都会影响到多个服务器

• 定制的复制方案

  • 选择性复制：配置replicate_wild_do_table

  • 分离功能：OLTP、OLAP

  • 数据归档：在备库上保留主库上删除过的数据

  • 将备库用作全文检索

  • 只读备库：read_only选项

  • 模拟多主库复制

  • 创建日志服务器：创建没有数据的日志服务器，更加容易重放并且/或者过滤二进制日志事件

复制和容量规划

• 写操作通常是复制的瓶颈，并且很难使用复制来扩展写操作
• 在构建一个大型应用时，有意让服务器不被充分使用，这应该是一种聪明并且蔓延的方式，尤其在使用复制的时候，有多余容量的服务器可以更好地处理负载尖峰，也有更多能力处理慢速查询和维护工作，并且能够更好地跟上复制

复制管理和维护

• 在主库上，可以使用SHOW MASTER STATUS命令来查看当前主库的二进制日志位置和配置
• 从库上，使用SHOW SLAVE STATUS

十一、扩展的MySQL

什么是可扩展性

• 可扩展性表明了当需要增加资源以执行更多工作时系统能够获得划算的等同提升（equal bang for the buck）的能力，缺乏扩展能力的系统在达到收益递减的转折点后，将无法进一步增长
• 可扩展性就是能够通过增加资源来提升容量的能力

扩展MySQL

• 规划可扩展性最困难的部分是估算需要承担的负载到底有多少，还需要大致正确地估计日程表，需要知道底线在哪里
• 可以做的准备工作：优化性能、购买性能更强的硬件
• 向上扩展（垂直扩展）意味着购买更多性能强悍的硬件
• 向外扩展（横向扩展、水平扩展）：复制、拆分、数据分片
  • 按功能拆分（按职责拆分），不同的节点执行不同的任务
  • 数据分片，把数据分割成一小片，或者一小块，然后存储到不同的节点中
  • 选择分区键（partitioning key）
  • 多个分区键
  • 跨分片查询，使用C或Java编写一个辅助应用来执行查询并聚合结果集，也可以借助汇总表来执行
  • 分配数据、分片和节点
• 通过多实例扩展
• 通过集群扩展
  • MySQL Cluster（NDB Cluster）
  • Clustrix
  • ScaleBase
  • GenieDB
  • Akiban
• 向内扩展，对不再需要的数据进行归档和清理
• 保持活跃数据独立

负载均衡

• 在一个服务器集群中尽可能地平均负载量，通常在服务器前端设置一个负载均衡器

十二、高可用性

什么是高可用性

• 高可用性不是绝对的，只有相对更高的可用性，100%的可用性是不可能达到的
• 可用性每提高一点，所花费的成本都会远超之前，可用性的效果和开销的比例并不是线性的

导致宕机的原因

• 运行环境问题，最普遍的问题是磁盘空间耗尽
• 性能问题，最普遍的原因是运行很糟糕的SQL，或服务器BUG或错误的行为
• 糟糕的Schema和索引设计
• 复制问题通常由于主备数据不一致导致
• 数据丢失通常由于DROP TABLE的误操作导致，并总是伴随着缺少可用备份的问题

如何实现高可用性

• 可以通过同时进行以下两步来获得高可用性
  • 可以尝试避免导致宕机的原因来减少宕机时间
  • 尽量保证在发生宕机时能够快速恢复
• 提升平均失效时间（MTBF）
  • 对系统变更管理的缺失是所有导致宕机的事件中最普遍的原因
  • 缺少严格的评估
  • 没有正确地监控MySQL的相关信息
• 降低平均恢复时间（MTTR）
  • 所有的宕机事件都是由多方面的失效联合在一起导致的，可以通过利用合适的方法确保单点的安全来避免

避免单点失效

• 系统中任何不冗余的部分都是一个可能失效的单点
• 可以采用两种方法来为系统增加冗余：增加空余容量和重复组件
• 共享存储或磁盘复制
  • 能够为数据库服务器和存储解耦合，通常使用的是SAN
  • 两个优点：可以避免除存储外的其他任何组件失效所引起的数据丢失，并为非存储组件建立冗余提供可能
• MySQL同步复制
  • 当使用同步复制时，主库上的事务只有在至少一个备库上提交后才能认为其执行完成
  • 完成了两个目标：当服务器崩溃时没有提交的事务会丢失，并且至少有一个备库拥有实时的数据副本
  • MySQL Cluster
  • Percona XtraDB Cluster
• 基于复制的冗余
  • 复制管理器是使用标准MySQL复制来创建冗余的工具

故障转移和故障恢复

• 冗余一点也不会增加可用性或减少宕机，和故障转移结合可以帮助更快地恢复，故障转移最重要的部分就是故障恢复
• 提升备库或切换角色
• 虚拟IP地址或IP接管
• 中间件解决方案，可以使用代理、端口转发、网络地址转换或者硬件负载均衡来实现故障转移和故障恢复
• 在应用中处理故障转移

十三、云端的MySQL

云的优点、缺点和相关误解

• 优点：

  • 云是一种将基础设施外包出去无须自己管理的方法

  • 云一般是按照即用即付的方式支付

  • 随着供应商发布新的服务和成本降低，云提供的价值越来越大

  • 云能够帮助你轻松地准备好服务器和其他资源

  • 云代表了对基础设施的另一种思考方式——作为通过API来定义和控制的资源——支持更多的自动化操作

• 2.缺点：

  • 资源是共享并且不可预测的

  • 无法保证容量和可用性

  • 虚拟的共享资源导致排查故障更加困难

MySQL在云端的经济价值

• 云托管比较适合尚处于初级阶段的企业，或者那些持续接触新概念并且本质上是以适用为主的企业
• 大量使用的策略是尽可能又快又便宜地开发和发布应用
• 运行不是很重要的基础设施

云中的MySQL的可扩展性和高可用性

• 数据库通常是一个应用系统中主要或唯一的有状态并且持久化的组件
• MySQL并不具备在一个无共享集群中的对等角色服务器之间迁移的能力

四种基础资源

• CPU通常少且慢
• 内在大小受限制
• I/O的吞吐量、延迟以及一致性受到限制
• 网络性能还比较好

MySQL在云主机上的性能

• 需要高并发的工作负载并不是非常适合云计算
• 那些需要大量I/O的工作负载在云中并不总是表现很好

MySQL数据库即服务（DBaaS）

• 将数据库本身作为云资源

十四、应用层优化

• 常见问题
  • 什么东西在消耗系统中每台主机的CPU、磁盘、网络，以及内存资源？这些值是否合理？如果不合理，对应用程序做基本的检查，看什么占用了资源
  • 应用真是需要所有获取到的数据吗？
  • 应用在处理本应由数据库处理的事情吗，或者反过来？
  • 应用执行了太多的查询？
  • 应用执行的查询太少了？
  • 应用创建了没必要的MySQL连接吗？
  • 应用对一个MySQL实例创建连接的次数太多了吗？
  • 应用做了太多的“垃圾”查询？
  • 应用使用了连接池吗？这既可能是好事，也可能是坏事
  • 应用是否使用长连接？
  • 应用是否在不使用的时候还保持连接撕开？

Web服务器问题

• 最常见的问题是保持它的进程的存活（alive）时间过长，或者在各种不同的用途下混合使用，而不是分别对不同类型的工作进行优化

• 如果用一个通用目的的Apache配置直接用于Web服务，最后很可能产生很多重量级的Apache进程

• 不要使用Apache来做静态内容服务，或者至少和动态服务使用不同的Apache实例

• 进程存活时间变短策略：

  • 不要让Apache填鸭式地服务客户端

  • 打开gzip压缩

  • 不要为用于长距离连接的Apache配置启用Keep-Alive选项

缓存

• 被动缓存除了存储和返回数据外不做任何事情；主动缓存在访问未命中时做一些额外工作

• 应用可以缓存部分计算结果，所以应用层缓存可能比更低层次的缓存更有效，可以节省两方面的工作：获取数据以及基于这些数据进行计算，重点是缓存命中率可能更低，并且可能使用较多的内存

• 应用层缓存：

  • 本地缓存

  • 本地共享内存缓存

  • 分布式内存缓存

  • 磁盘上的缓存

• 缓存控制策略

  • TTL（time to live，存活时间）

  • 显式失效，如果不能接受脏数据，那么进程在更新原始数据时需要同时使缓存失效

  • 读时失效，在更改旧数据时，为了避免要同时失效派生出来的脏数据，可以在缓存中保存一些信息，当从缓存中读数据时可以利用这些信息判断数据是否已经失效

• 可以在后台预先请求一些页面，并将结果存为静态页面，好处：

  • 应用代码没有复杂的命中和未命中处理路径

  • 当未命中的处理路径慢得不可接受时，这种方案可以很好地工作

  • 预生成内容可以避免在缓存未命中时导致的雪崩效应

MySQL的替代品

• 搜索：Lucene和Sphinx
• 简单的键值存储：Redis
• 非结构化数据：Hadoop

十五、备份与恢复

为什么要备份

• 灾难恢复
• 人们改变想法
• 审计
• 测试

定义恢复需求

• 规划备份和恢复策略时，有两个重要的需求：恢复点目标（PRO）和恢复时间目标（RTO）

设计MySQL备份方案

• 建议

  • 在生产实践中，对于大数据库来说，物理备份是必需的：逻辑备份太慢并受到资源限制，从逻辑备份中恢复需要很长时间

  • 保留多个备份集

  • 定期从逻辑备份（或者物理备份）中抽取数据进行恢复测试

  • 保存二进制日志以用于基于故障时间点的恢复

  • 完全不借助备份工具本身来监控备份和备份的过程

  • 通过演练整个恢复过程来测试备份和恢复

  • 对安全性要仔细考虑

• 如果可能，关闭MySQL做备份是最简单最安全的，需要考虑：锁时间、备份时间、备份负载、恢复时间

• 逻辑备份优点：

  • 可以用编辑器或像grep和sed之类的命令查看和操作的普通文件

  • 恢复非常简单

  • 可能通过网络来备份和恢复

  • 可以在类似Amazon RDS这样不能访问底层文件系统的系统中使用

  • 非常灵活

  • 与存储引擎无关

  • 有助于避免数据损坏

• 逻辑备份的缺点：

  • 必须由数据库服务器完成生成逻辑备份的工作

  • 逻辑备份在某些场景下比数据库文件本身更大

  • 无法保证导出后再还原出来的一定是同样的数据

  • 从逻辑备份中还原需要MySQL加载和解释语句

• 物理备份优点：

  • 基于文件的备份，只需要将需要的文件复制到其他地方即可

  • 恢复简单

  • InnoDB和MyISAM的物理备份非常容易跨平台

• 物理备份缺点：

  • InnoDB的原始文件通常比相应的逻辑备份要大得多

  • 物理备份不总是可以跨平台

• 除非经过测试，不要假定备份是正常的

• 建议混合使用物理和逻辑两种方式来做备份

• MySQL备份需要考虑的几点：

  • 非显著数据
  • 代码
  • 复制配置
  • 服务器配置
  • 选定的操作系统

• 差异备份是对自上次全备份后所有改变的部分做备份，而增量备份则是自从任意类型的上次备份后所有修改做的备份

• 差异、增量备份的建议：

  • 使用Percona XtraBackup和MySQL Enterprise Backup中的增量备份特性
  • 备份二进制日志，每次备份后FLUSH LOGS
  • 不要备份没有改变的表
  • 不要备份没有改变的行
  • 某些数据根本不需要备份
  • 备份所有的数据，然后发送到一个有去重特性的目的地

• 数据一致性：当备份时，应该考虑是否需要数据在指定时间点一致

• 文件一致性：每个文件的内部一致性

• 从备库中备份最大的好处是可以不干扰主库，故意将一个备库延时一段时间对于某些灾难场景非常有用

管理和备份二进制日志

• expire_log_days变量MySQL定期清理日志

备份数据

• 生成逻辑备份

  • SQL导出：mysqldump方式

  • 符号分隔文件备份：使用SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份

• 文件系统快照

  • 支持快照的文件系统和设备包括FreeBSD的文件系统、ZFS文件系统、GNU/Linux的逻辑卷管理（LVM），以及许多的SAN系统和文件存储解决方案

从备份中恢复

• 恢复步骤：

  • 停止MySQL服务器

  • 记录服务器的配置和文件权限

  • 将数据从备份中移到MySQL数据目录

  • 改变配置

  • 改变文件权限

  • 以限制访问模式重启服务器，等待完成启动

  • 载入逻辑备份文件

  • 检查和重放二进制日志

  • 检测已经还原的数据

  • 以完全权限重启服务器

• 备份和恢复工具

  • MySQL Enterprise Backup
  • Percona XtraBackup
  • mylvmbackup
  • Zmanda Recovery Manager
  • mydunper
  • mysqldump

十六、MySQL用户工具

接口工具

• MySQL Workbench
• SQLyog
• phpMyAdmin
• Adminer

命令行工具集

• Percona Toolkit
• Maatkit and Aspersa
• The openark kit
• MySql workbench

SQL实用集

• common_schema
• mysql-sr-lib
• MySQL UDF仓库
• MySQL Forge

监测工具

• 开源的监控工具

  • Nagios

  • Zabbix

  • Zenoss

  • OpenNMS

  • Groundwork Open Source

  • MRTG

  • Cacti

  • Ganglia

  • Munin

• 商业监控系统

  • MySQL Enterprise Monitor

  • MONyog

  • New Relic

  • Circonus

  • Monitis

  • Splunk

  • Pingdom

• Innotop的命令行监控