zhe.ink 在线工具箱

Laravel不能使用mysql_real_escape_string如何过滤非法字符？

mysql_real_escape_string在php8不能用，mysqli_escape_string需要mysqli。

pdo也有一个过滤非法字符函数就是quote()，在laravel中用法如下。

DB::connection()->getPdo()->quote()

golang中mysql查询count

以下是使用标准库的示例代码

package main  
  
import (  
 "database/sql"  
 "fmt"  
)  
  
func main() {  
 // 创建数据库连接db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database_name")  
 if err != nil {  
 fmt.Println("数据库连接失败:", err)  
 return  
 }  
 defer db.Close()  
  
 // 查询数据行数量var count int  
 err = db.QueryRow("SELECT COUNT(*) FROM your_table_name").Scan(&count)  
 if err != nil {  
 fmt.Println("查询数据行数量失败:", err)  
 return  
 }  
  
 fmt.Println("数据行数量:", count)  
}

PHP面试问题：详细介绍mysql的隔离级别

MySQL数据库有四种隔离级别：

Read Uncommitted（读取未提交内容）。在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读。
Read Committed（读取提交内容）。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读，因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（可重读）。这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
Serializable（可串行化）。这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

PHP面试问题：php中两个事务嵌套时，会发生神情况？

在PHP中嵌套事务的行为取决于数据库的隔离级别和事务控制方式。需要根据具体情况进行评估和测试，以确保数据的一致性和正确性。

如果数据库的隔离级别为READ COMMITTED或SERIALIZABLE，并且事务是自动提交的，那么嵌套的事务会彼此独立执行。每个事务都会完整地执行，包括内部的每个SQL语句，不会相互影响。
如果数据库的隔离级别为READ COMMITTED或SERIALIZABLE，并且事务是手动控制的，那么可以在外部事务中控制内部事务的执行。如果外部事务回滚，内部事务也会回滚。如果外部事务提交，内部事务将继续执行并提交。
如果数据库的隔离级别为READ UNCOMMITTED或REPEATABLE READ，那么内部事务可以看到其他事务对数据库的修改，这可能会导致数据不一致的情况。
如果数据库的隔离级别为READ UNCOMMITTED或REPEATABLE READ，并且事务是手动控制的，那么可以在外部事务中控制内部事务的执行。如果外部事务回滚，内部事务也会回滚。如果外部事务提交，内部事务将继续执行并提交。

PHP面试问题：详细介绍mysql的事务

MySQL事务是一种数据库操作技术，它确保一系列操作要么全部成功，要么全部失败，保持数据的一致性。

事务是一系列数据库操作的逻辑单元，这些操作要么全部完成，要么全部失败。MySQL的事务具有以下四个特性：

一致性。事务的执行将使数据库从一个状态转变为另一个状态，保证数据的一致性。
持久性。一旦事务提交，则其所做的修改将永久保存到数据库中。
原子性。事务是一个原子操作单元，其对数据的修改要么全部执行，要么全部回滚，不会出现部分执行的情况。
隔离性。多个事务同时对数据库进行修改操作时，事务之间是相互隔离的，互不干扰。

PHP面试问题：如何使用PHP和MySQL上传视频？

可以按照以下步骤进行操作：

创建MySQL数据库和表：在MySQL中创建一个数据库，并创建一个包含用于存储文件路径的列的表。
连接MySQL数据库：在PHP中使用mysqli_connect函数或PDO等方法连接到MySQL数据库。
创建上传表单：在HTML中创建一个包含文件上传字段的表单，并设置表单的enctype属性为multipart/form-data，以便支持文件上传。
处理上传的文件：在PHP中使用$_FILES超全局变量来获取上传的文件信息。然后，将文件移动到服务器上的临时目录中，并获取文件的临时路径。
将文件路径保存到数据库：将临时路径保存到MySQL数据库中，以便稍后可以访问该文件。

<?php
// 连接到MySQL数据库
$conn = mysqli_connect("localhost", "username", "password", "database_name");

// 处理上传的文件
if (isset($_FILES['video'])) {
    $file = $_FILES['video'];
    $tempFilePath = $file['tmp_name'];
    
    // 将文件移动到服务器上的目录中
    $uploadDir = "uploads/";
    $newFilePath = $uploadDir . basename($tempFilePath);
    move_uploaded_file($tempFilePath, $newFilePath);
    
    // 将文件路径保存到数据库
    $sql = "INSERT INTO videos (file_path) VALUES ('$newFilePath')";
    mysqli_query($conn, $sql);
    
    echo "视频上传成功！";
}

// 关闭数据库连接
mysqli_close($conn);
?>

<!DOCTYPE html>
<html>
<head>
    <title>上传视频</title>
</head>
<body>
    <form method="post" enctype="multipart/form-data">
        <input type="file" name="video" accept="video/*">
        <input type="submit" value="上传视频">
    </form>
</body>
</html>

mysql配置文件中[client]下保存root和密码后，远程连接还需要账号密码吗？

在MySQL配置文件（my.cnf）的[client]部分保存root用户和密码的配置信息后，可以简化远程连接的操作，但仍然需要提供账号和密码。这是因为MySQL服务器在接受远程连接请求时，会根据配置文件中的设置进行验证，只有通过验证的用户才能获得访问权限。

在远程连接时，需要使用"mysql -u root -p"命令，并按照提示输入密码。如果配置文件中保存了root用户和密码的信息，则该信息将作为默认的用户名和密码，但仍然需要在连接时手动输入密码。

为了安全起见，建议不要在MySQL配置文件中保存root用户的密码，而是使用其他更安全的方式来管理远程连接的账号和密码。

mysql在docker容器下运行时的数据备份

本地备份命令改为docker命令

#mysqldump -h 127.0.0.1 $dbname > $backup_file

改成如下
docker exec mysql容器名 mysqldump $dbname > $backup_file

其中-u root -p password都应该在my.cnf配置文件中设置，如下

[mysqldump]
user=root
password=你的密码

mysql 定时数据备份的bash脚本

在MySQL中，可以使用mysqldump命令来备份数据，并使用cron（Linux中的计划任务）来定时执行备份脚本。

编写脚本之前应该先配置mysql的配置文件，my.cnf或mysqld.cnf，这是因为mysql不推荐在命令行中直接输入密码，增加如下配置项。

[mysqldump]
user=root
password=你的密码

bash#!/bin/bash

# 设置备份目录和文件名
backup_dir="/path/to/backup/directory"
date=$(date +%Y%m%d%H%M%S)
backup_file="$backup_dir/backup_$date.sql"

# 创建备份目录
mkdir -p $backup_dir

# 使用mysqldump命令备份数据
mysqldump dbname > $backup_file

在这个脚本中，你需要将/path/to/backup/directory替换为实际的备份目录路径。username和password分别替换为你的MySQL用户名和密码，dbname替换为你要备份的数据库名。

接下来，你可以使用cron来定时执行这个脚本。在Linux终端中输入以下命令打开cron配置文件：

bashcrontab -e

然后添加以下行来设置每天的固定时间进行备份（例如每天凌晨1点）：

bash0 1 * * * /path/to/backup.sh > /dev/null 2>&1

保存并退出。现在，每天凌晨1点，MySQL数据将会被备份到指定的目录中。

laravel中选择同一个连接中不同数据库的方法

DB::select('USE database_name');

然后执行相关命令。

mysql按表名前缀删除表的方法

DROP TABLE table_prefix%;

高性能Mysql阅读笔记

### 高性能Mysql

#### 1. MYSQL架构与历史

1. Mysql逻辑架构

第一层：连接、处理器

第二层：解析器、查询缓存、触发器、存储过程、视图、内置函数

第三层：存储引擎

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

2. 并发控制

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

3. 事务

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

隔离级别

READ UNCOMMITTED（未提交读）

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

READ COMMITTED（提交读）

大多数数据库系统的默认隔离级别都是READ COMMITTED（但MySQL不是）。READ COMMITTED满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

REPEATABLE READ（可重复读）

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

可重复读是MySQL的默认事务隔离级别。

SERIALIZABLE（可串行化）

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

死锁

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

事务日志

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

事务

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

第三方存储引擎也支持事务，比较知名的包括XtraDB和PBXT。

MySQL默认采用自动提交（AUTOCOMMIT）模式。

另外还有一些命令，在执行之前会强制执行COMMIT提交当前的活动事务。典型的例子，在数据定义语言（DDL）中，如果是会导致大量数据改变的操作，比如ALTER TABLE，就是如此。另外还有LOCK TABLES等其他语句也会导致同样的结果。如果有需要，请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。

MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

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

如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表），在正常提交的情况下不会有什么问题。

但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

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

隐式和显式锁定

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

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。不仅是MySQL，包括Oracle、PostgreSQL等其他数据库系统也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC没有一个统一的实现标准。

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

前面说到不同存储引擎的MVCC实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

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

SELECT

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

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

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

INSERT

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

DELETE

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

UPDATE

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

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

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

InnoDB

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

InnoDB表是基于聚簇索引建立的，我们会在后面的章节详细讨论聚簇索引。InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。InnoDB的存储格式是平台独立的，也就是说可以将数据和索引文件从Intel平台复制到PowerPC或者Sun SPARC平台。

作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份，Oracle提供的MySQL Enterprise Backup、Percona提供的开源的XtraBackup都可以做到这一点。MySQL的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，在MySQL 5.1之前也不支持索引。

Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。或者在一些需要更快速的INSERT操作的场合下也可以使用。

Archive引擎支持行级锁和专用的缓冲区，所以可以实现高并发的插入。在一个查询开始直到返回表中存在的所有行数之前，Archive引擎会阻止其他的SELECT执行，以实现一致性读。另外，也实现了批量插入在完成之前对读操作是不可见的。这种机制模仿了事务和MVCC的一些特性，但Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。

Blackhole引擎

Blackhole引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。但这种应用方式我们碰到过很多问题，因此并不推荐。

CSV引擎

CSV引擎可以将普通的CSV文件（逗号分割值的文件）作为MySQL的表来处理，但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可以将Excel等电子表格软件中的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL中打开使用。同样，如果将数据写入到一个CSV引擎表，其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据交换的机制，非常有用。

Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如Microsoft SQL Server和Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。MariaDB使用了它的一个后续改进版本，叫做FederatedX。

Memory引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表（以前也叫做HEAP表）是非常有用的。Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。Memory表的结构在重启以后还会保留，但数据会丢失。

用于查找（lookup）或者映射（mapping）表，例如将邮编和州名映射的表。

用于缓存周期性聚合数据（periodically aggregated data）的结果。

用于保存数据分析中产生的中间数据。

Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费（其中一些限制在Percona版本已经解决）。

Merge引擎

Merge引擎是MyISAM引擎的一个变种。Merge表是由多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃（参考第7章）。

NDB集群引擎

OLTP类引擎

Percona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本，已经包含在Percona Server和MariaDB中，它的改进点主要集中在性能、可测量性和操作灵活性方面。XtraDB可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件，并支持InnoDB的所有查询。

另外还有一些和InnoDB非常类似的OLTP类存储引擎，比如都支持ACID事务和MVCC。其中一个就是PBXT，由Paul McCullagh和Primebase GMBH开发。它支持引擎级别的复制、外键约束，并且以一种比较复杂的架构对固态存储（SSD）提供了适当的支持，还对较大的值类型如BLOB也做了优化。PBXT是一款社区支持的存储引擎，MariaDB包含了该引擎。

TokuDB引擎使用了一种新的叫做分形树（Fractal Trees）的索引数据结构。该结构是缓存无关的，因此即使其大小超过内存性能也不会下降，也就没有内存生命周期和碎片的问题。TokuDB是一种大数据（Big Data）存储引擎，因为其拥有很高的压缩比，可以在很大的数据量上创建大量索引。在本书写作时，这个引擎还处于早期的生产版本状态，在并发性方面还有很多明显的限制。目前其最适合在需要大量插入数据的分析型数据集的场景中使用，不过这些限制可能在后续版本中解决掉。

RethinkDB最初是为固态存储（SSD）而设计的，然而随着时间的推移，目前看起来和最初的目标有一定的差距。该引擎比较特别的地方在于采用了一种只能追加的写时复制B树（append-only copyon-write B-Tree）作为索引的数据结构。目前还处于早期开发状态，我们还没有测试评估过，也没有听说有实际的应用案例。

在Sun收购MySQL AB以后，Falcon存储引擎曾经作为下一代存储引擎被寄予期望，但现在该项目已经被取消很久了。Falcon的主要设计者Jim Starkey创立了一家新公司，主要做可以支持云计算的NewSQL数据库产品，叫做NuoDB（之前叫NimbusDB）。

如果数据量继续增长到10TB以上的级别，可能就需要建立数据仓库。Infobright是MySQL数据仓库最成功的解决方案。也有一些大数据库不适合Infobright，却可能适合TokuDB。

#### 2.MYSQL基准测试

为什么需要基准测试

验证基于系统的一些假设，确认这些假设是否符合实际情况。

重现系统中的某些异常行为，以解决这些异常。

测试系统当前的运行情况。如果不清楚系统当前的性能，就无法确认某些优化的效果如何。也可以利用历史的基准测试结果来分析诊断一些无法预测的问题。

模拟比当前系统更高的负载，以找出系统随着压力增加而可能遇到的扩展性瓶颈。

规划未来的业务增长。基准测试可以评估在项目未来的负载下，需要什么样的硬件，需要多大容量的网络，以及其他相关资源。这有助于降低系统升级和重大变更的风险。

测试应用适应可变环境的能力。例如，通过基准测试，可以发现系统在随机的并发峰值下的性能表现，或者是不同配置的服务器之间的性能表现。基准测试也可以测试系统对不同数据分布的处理能力。

测试不同的硬件、软件和操作系统配置。比如RAID 5还是RAID 10更适合当前的系统？如果系统从ATA硬盘升级到SAN存储，对于随机写性能有什么帮助？Linux 2.4系列的内核会比2.6系列的可扩展性更好吗？升级MySQL的版本能改善性能吗？为当前的数据采用不同的存储引擎会有什么效果？所有这类问题都可以通过专门的基准测试来获得答案。

证明新采购的设备是否配置正确。笔者曾经无数次地通过基准测试来对新系统进行压测，发现了很多错误的配置，以及硬件组件的失效等问题。因此在新系统正式上线到生产环境之前进行基准测试是一个好习惯，永远不要相信主机提供商或者硬件供应商的所谓系统已经安装好，并且能运行多快的说法。如果可能，执行实际的基准测试永远是一个好主意。

性能测试工具

sysbench

https://www.jianshu.com/p/a6864943f0e7

数据库测试套件中的dbt2 TPC-C测试

Percona的TPCC-MySQL测试工具

https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql，其中包含了一个README文件说明了如何编译。该工具使用很简单，但测试数据中的仓库数量很多，可能需要用到其中的并行数据加载工具来加快准备测试数据集的速度，否则这一步会花费很长时间。

### 服务器性能剖析

profiling

New Relic

https://docs.newrelic.com/docs/new-relic-solutions/get-started/intro-new-relic/

Xhprof

慢查询剖析报告

pt-query-digest

研究：Performance Schema

Percona Toolkit

http://www.percona.com）

### Schema与数据类型优化

如果需要学习数据库设计方面的基础知识，建议阅读Clare Churcher的Beginning Database Design（Apress出版社）一书。

当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据(4)有很好的空间效率。但这一点不适用于MyISAM。

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

但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

BLOB和TEXT

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

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

ENUM

另外一个让人吃惊的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的：

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素，这样在MySQL 5.1中就可以不用重建整个表来完成修改。

从上面的结果可以看到，当把列都转换成ENUM以后，关联变得很快。但是当VARCHAR列和ENUM列进行关联时则慢很多。在本例中，如果不是必须和VARCHAR列进行关联，那么转换这些列为ENUM就是个好主意。这是一个通用的设计实践，在“查找表”时采用整数主键而避免采用基于字符串的值进行关联。

DATETIME

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

TIMESTAMP

就像它的名字一样，TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多：只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。

BIT

BIT的行为因存储引擎而异。MyISAM会打包存储所有的BIT列，所以17个单独的BIT列只需要17个位存储（假设没有可为NULL的列），这样MyISAM只使用3个字节就能存储这17个BIT列。其他存储引擎例如Memory和InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。

MySQL把BIT当作字符串类型，而不是数字类型。当检索BIT（1）的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”。然而，在数字上下文的场景中检索时，结果将是位字符串转换成的数字。如果需要和另外的值比较结果，一定要记得这一点。例如，如果存储一个值b'00111001'（二进制值等于57）到BIT（8）的列并且检索它，得到的内容是字符码为57的字符串。也就是说得到ASCII码为57的字符“9”。

SET

如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MySQL有像FIND_IN_SET()和FIELD()这样的函数，方便地在查询中使用。它的主要缺点是改变列的定义的代价较高：需要ALTER TABLE，这对大表来说是非常昂贵的操作（但是本章的后面给出了解决办法）。一般来说，也无法在SET列上通过索引查找。

UUID

如果存储UUID值，则应该移除“-”符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY（16）列中。检索时可以通过HEX()函数来格式化为十六进制格式。UUID()生成的值与加密散列函数例如SHA1()生成的值有不同的特征：UUID值虽然分布也不均匀，但还是有一定顺序的。尽管如此，但还是不如递增的整数好用。

IP地址

另一个例子是一个IPv4地址。人们经常使用VARCHAR（15）列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

一个人有两块手表就永远不知道时间

物化视图

MySQL并不原生支持物化视图（我们将在第7章详细探讨支持这种视图的细节）。然而，使用Justin Swanhart的开源工具Flexviews（http://code.google.com/p/flexviews/），也可以自己实现物化视图。Flexviews比完全自己实现的解决方案要更精细，并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。它由下面这些部分组成：

变更数据抓取（Change Data Capture，CDC）功能，可以读取服务器的二进制日志并且解析相关行的变更。

一系列可以帮助创建和管理视图的定义的存储过程。

一些可以应用变更到数据库中的物化视图的工具。

加快ALTER TABLE操作的速度

也有一些工具可以帮助完成影子拷贝工作：例如，Facebook数据库运维团队（https://launchpad.net/mysqlatfacebook）的“online schema change”工具、Shlomi Noach的openark toolkit（http://code.openark.org/），以及Percona Toolkit（http://www.percona.com/software/）。如果使用Flexviews（参考4.4.1节），也可以通过其CDC工具执行无锁的表结构变更。

快速创建索引

在现代版本的InnoDB版本中，有一个类似的技巧，这依赖于InnoDB的快速在线索引创建功能。这个技巧是，先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。Percona Server可以自动完成这些操作步骤。

用需要的表结构创建一张表，但是不包括索引。

载入数据到表中以构建.MYD文件。

按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.MYI文件。

获取读锁并刷新表。

重命名第二张表的.frm和.MYI文件，让MySQL认为是第一张表的文件。

释放读锁。

使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。

这个操作步骤对大表来说会快很多。

### 创建高性能的索引

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

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

全值匹配

全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。

匹配最左前缀

前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。

匹配列前缀

也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。

匹配范围值

例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。

精确匹配某一列并范围匹配另外一列

前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头（比如Kim、Karl等）的人。即第一列last_name全匹配，第二列frst_name范围匹配。

只访问索引的查询

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

到这里读者应该可以明白，前面提到的索引列的顺序是多么的重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

'a' IS NULL ==> 'a' <=> NULL

'a' IS NOT NULL ==> NOT('a' <=> NULL)

哈希索引

在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

创建自定义哈希索引。如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。

思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数

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

-> AND url_crc=CRC32("http://www.mysql.com");

DELIMITER //

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN

SET NEW.url_crc=crc32(NEW.url);

END;

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN

SET NEW.url_crc=crc32(NEW.url);

END;

DELIMITER ;

如果采用这种方式，记住不要使用SHA1()和MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。SHA1()和MD5()是强加密函数，设计目标是最大限度消除冲突，但这里并不需要这样高的要求。简单哈希函数的冲突在一个可以接受的范围，同时又能够提供更好的性能。

如果数据表非常大，CRC32()会出现大量的哈希冲突，则可以考虑自己实现一个简单的64位哈希函数。这个自定义函数要返回整数，而不是字符串。一个简单的办法可以使用MD5()函数返回值的一部分来作为自定义哈希函数。这可能比自己写一个哈希算法的性能要差（参考第7章），不过这样实现最简单：

SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16),16 ,10) as HASH64;

空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

还有很多第三方的存储引擎使用不同类型的数据结构来存储索引。例如TokuDB使用分形树索引（fractal tree index），这是一类较新开发的数据结构，既有B-Tree的很多优点，也避免了B-Tree的一些缺点。如果通读完本章，可以看到很多关于InnoDB的主题，包括聚簇索引、覆盖索引等。多数情况下，针对InnoDB的讨论也都适用于TokuDB。

ScaleDB使用Patricia tries（这个词不是拼写错误），其他一些存储引擎技术如InfiniDB和Infobright则使用了一些特殊的数据结构来优化某些特殊的查询。

5.3　高性能的索引策略

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？

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

前缀索引和索引选择性

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

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

查询显示当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。

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

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

一个常见的场景是针对很长的十六进制唯一ID使用前缀索引。在前面的章节中已经讨论了很多有效的技术来存储这类ID信息，但如果使用的是打包过的解决方案，因而无法修改存储结构，那该怎么办？例如使用vBulletin或者其他基于MySQL的应用在存储网站的会话（SESSION）时，需要在一个很长的十六进制字符串上创建索引。此时如果采用长度为8的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。

多列索引

这种索引策略，一般是由于人们听到一些专家诸如“把WHERE条件里面的列都建上索引”这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

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

当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL 4.1或者更早的时代一样，将查询改写成UNION的方式往往更好。

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

选择合适的索引列顺序

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗?在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。

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

如果是从诸如pt-query-digest这样的工具的报告中提取“最差”查询，那么再按上述办法选定的索引顺序往往是非常高效的。如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：

mysql> SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,

> COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,

> COUNT(*)

> FROM payment\G

*************************** 1. row ***************************

staff_id_selectivity: 0.0001

customer_id_selectivity: 0.0373

COUNT(*): 16049customer_id的选择性更高，所以答案是将其作为索引列的第一列：

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

聚簇索引

聚簇索引(7)并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起(8)。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况，本章后面将详细介绍）。

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

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

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。

数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。

使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引也有一些缺点：

聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。

插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。

更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂（page split）”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。

聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

二级索引访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

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

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

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

注意到向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长；另一方面毫无疑问是由于页分裂和碎片导致的。

为了明白为什么会这样，来看看往第一个表中插入数据时，索引发生了什么变化。图5-10显示了插满一个页面后继续插入相邻的下一个页面的场景。

图5-10：向聚簇索引插入顺序的索引值

如图5-10所示，因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果（然而，二级索引页可能是不一样的）。

对比一下向第二个使用了UUID聚簇索引的表插入数据，看看有什么不同，图5-11显示了结果。

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

写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。

因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。

由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。

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

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。如果你的服务器版本还不支持innodb_autoinc_lock_mode参数，可以升级到新版本的InnoDB，可能对这种场景会工作得更好。

覆盖索引

通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢?如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中（这对于MyISAM尤其正确，因为MyISAM能压缩索引以变得更小）。

因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。对于某些存储引擎，例如MyISAM和Percona XtraDB，甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。

一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。

由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

这里索引无法覆盖该查询，有两个原因：

没有任何索引能够覆盖这个查询。因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。不过，理论上MySQL还有一个捷径可以利用：WHERE条件中的列是有索引可以覆盖的，因此MySQL可以使用该索引找到对应的actor并检查title是否匹配，过滤之后再读取需要的数据行。

MySQL不能在索引中执行LIKE操作。这是底层存储引擎API的限制，MySQL 5.5和更早的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于）。MySQL能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE查询，存储引擎就无法做比较匹配。这种情况下，MySQL服务器只能提取数据行的值而不是索引值来做比较。

也有办法可以解决上面说的两个问题，需要重写查询并巧妙地设计索引。先将索引扩展至覆盖三个数据列（artist，title，prod_id），然后按如下方式重写查询：

mysql> EXPLAIN SELECT *

-> FROM products

-> JOIN (

-> SELECT prod_id

-> FROM products

-> WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'

-> ) AS t1 ON (t1.prod_id=products.prod_id)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: <derived2>

...omitted...

*************************** 2. row ***************************

id: 1

select_type: PRIMARY

table: products

...omitted...

*************************** 3. row ***************************

id: 2

select_type: DERIVED

table: products

type: ref

possible_keys: ACTOR,ACTOR_2,IX_PROD_ACTOR

key: ACTOR_2

key_len: 52

ref:

rows: 11

Extra: Using where; Using index

我们把这种方式叫做延迟关联（deferred join），因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引，在FROM子句的子查询中找到匹配的prod_id，然后根据这些prod_id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好。

未来MySQL版本的改进

上面提到的很多限制都是由于存储引擎API设计所导致的，目前的API设计不允许MySQL将过滤条件传到存储引擎层。如果MySQL在后续版本能够做到这一点，则可以把查询发送到数据上，而不是像现在这样只能把数据从存储引擎拉到服务器层，再根据查询条件过滤。在本书写作之际，MySQL 5.6版本（未正式发布）包含了在存储引擎API上所做的一个重要的改进，其被称为“索引条件推送（index condition pushdown）”。这个特性将大大改善现在的查询执行方式，如此一来上面介绍的很多技巧也就不再需要了。

使用索引扫描来做排序

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

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

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

压缩（前缀压缩）索引

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

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

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

冗余和重复索引

冗余索引和重复索引有一些不同。如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。因此索引（A，B）也可以当作索引（A）来使用（这种冗余只是对B-Tree索引来说的）。但是如果再创建索引（B，A），则不是冗余索引，索引（B）也不是，因为B不是索引（A，B）的最左前缀列。另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引（A，B）而不是扩展已有的索引（A）。还有一种情况是将一个索引扩展为（A，ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

考虑一下前面“在InnoDB中按主键顺序插入行”一节提到的userinfo表。这个表有1000000行，对每个state_id值大概有20000条记录。在state_id列有一个索引对下面的查询有用，假设查询名为Q1：

mysql> SELECT count(*) FROM userinfo WHERE state_id=5;

一个简单的测试表明该查询的执行速度大概是每秒115次（QPS）。还有一个相关查询需要检索几个列的值，而不是只统计行数，假设名为Q2：

mysql> SELECT state_id, city, address FROM userinfo WHERE state_id=5;

对于这个查询，测试结果QPS小于10(17)。提升该查询性能的最简单办法就是扩展索引为（state_id，city，address），让索引能覆盖查询：

mysql> ALTER TABLE userinfo DROP KEY state_id,

-> ADD KEY state_id_2 (state_id, city, address);

索引扩展后，Q2运行得更快了，但是Q1却变慢了。如果我们想让两个查询都变得更快，就需要两个索引，尽管这样一来原来的单列索引是冗余的了。表5-3显示这两个查询在不同的索引策略下的详细结果，分别使用MyISAM和InnoDB存储引擎。注意到只有state_id_2索引时，InnoDB引擎上的查询Q1的性能下降并不明显，这是因为InnoDB没有使用索引压缩。

表5-3：使用不同索引策略的SELECT查询的QPS测试结果

有两个索引的缺点是索引成本更高。表5-4显示了向表中插入100万行数据所需要的时间。

可以看到，表中的索引越多插入速度会越慢。一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。可以通过写一些复杂的访问INFORMATION_SCHEMA表的查询来找，不过还有两个更简单的方法。可使用Shlomi Noach的common_schema中的一些视图来定位，common_schema是一系列可以安装到服务器上的常用的存储和视图（http://code.google.com/p/common-schema/）。这比自己编写查询要快而且简单。另外也可以使用Percona Toolkit中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复的索引。对于大型服务器来说，使用外部的工具可能更合适些；如果服务器上有大量的数据或者大量的表，查询INFORMATION_SCHEMA表可能会导致性能问题。

未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除(18)。有两个工具可以帮助定位未使用的索引。最简单有效的办法是在Percona Server或者MariaDB中先打开userstates服务器变量（默认是关闭的），然后让服务器正常运行一段时间，再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。

另外，还可以使用Percona Toolkit中的pt-index-usage，该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告。这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划——例如在某些情况有些类似的查询的执行方式不一样，这可以帮助你定位到那些偶尔服务质量差的查询，优化它们以得到一致的性能表现。该工具也可以将结果写入到MySQL的表中，方便查询结果。

索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发性。

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

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

案例

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

出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分的排序，则WHERE条件中的age BETWEEN 18 AND 25就无法使用索引。如果MySQL使用某个索引进行范围查询，也就无法再使用另一个索引（或者是该索引的后续字段）进行排序了。如果这是很常见的WHERE条件，那么我们当然就会认为很多查询需要做排序操作（例如文件排序filesort）。

支持多种过滤条件

现在需要看看哪些列拥有很多不同的取值，哪些列在WHERE子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的，因为可以让MySQL更有效地过滤掉不需要的行。country列的选择性通常不高，但可能很多查询都会用到。sex列的选择性肯定很低，但也会在很多查询中用到。所以考虑到使用的频率，还是建议在创建不同组合索引的时候将（sex，country）列作为前缀。

但根据传统的经验不是说不应该在选择性低的列上创建索引的吗？那为什么这里要将两个选择性都很低的字段作为索引的前缀列?我们的脑子坏了?

我们的脑子当然没坏。这么做有两个理由：第一点，如前所述几乎所有的查询都会用到sex列。前面曾提到，几乎每一个查询都会用到sex列，甚至会把网站设计成每次都只能按某一种性别搜索用户。更重要的一点是，索引中加上这一列也没有坏处，即使查询没有使用sex列也可以通过下面的“诀窍”绕过。

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

你可能已经注意到了，我们一直将age列放在索引的最后面。age列有什么特殊的地方吗？为什么要放在索引的最后？我们总是尽可能让MySQL使用更多的索引列，因为查询只能使用索引的最左前缀，直到遇到第一个范围条件列。前面提到的列在WHERE子句中都是等于条件，但是age列则多半是范围查询（例如查找年龄在18～25岁之间的人）。

当然，也可以使用IN()来代替范围查询，例如年龄条件改写为IN（18，19，20，21，22，23，24，25），但不是所有的范围查询都可以转换。这里描述的基本原则是，尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

前面提到可以在索引中加入更多的列，并通过IN()的方式覆盖那些不在WHERE子句中的列。但这种技巧也不能滥用，否则可能会带来麻烦。因为每额外增加一个IN()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能。考虑下面的WHERE子句：

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

我们不是挑剔：对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这个限制。

大表分页查询

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

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略。一个更好的办法是限制用户能够翻页的数量，实际上这对用户体验的影响不大，因为用户很少会真正在乎搜索结果的第10000页。

优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效地使用（sex，rating）索引进行排序和分页：

mysql> SELECT <cols> FROM profiles INNER JOIN (

-> SELECT <primary key cols> FROM profiles

-> WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10

-> ) AS x USING(<primary key cols>);

维护索引和表

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

可以通过设置innodb_force_recovery参数进入InnoDB的强制恢复模式来修复数据，更多细节可以参考MySQL手册。另外，还可以使用开源的InnoDB数据恢复工具箱（InnoDB Data Recovery Toolkit）直接从InnoDB数据文件恢复出数据（下载地址：http://www.percona.com/software/mysql-innodb-data-recovery-tools/）。

减少索引和数据的碎片

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

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

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

行碎片（Row fragmentation）

这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。

行间碎片（Intra-row fragmentation）

行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。

剩余空间碎片（Free space fragmentation）

剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

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

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于一些存储引擎如MyISAM，可以通过排序算法重建索引的方式来消除碎片。老版本的InnoDB没有什么消除碎片化的方法。不过最新版本InnoDB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

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

单行访问是很慢的。特别是在机械硬盘存储中（SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。

按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。

索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

第6章查询性能优化

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

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

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

多表关联时返回全部列

当然，查询返回超过需要的数据也不总是坏事。在我们研究过的许多案例中，人们会告诉我们说这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性，如果清楚这样做的性能影响，那么这种做法也是值得考虑的。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。实际上可以使用“快速上限估计”法来估算查询的响应时间，这是由TapioLahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers（Wiley出版社）一书提到的技术，限于篇幅，在这里不会详细展开。概括地说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。

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

在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。

使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。

从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

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

MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。别害怕这样做，好好衡量一下这样做是不是会减少工作量。稍后我们将通过本章的一个示例来展示这个技巧的优势。

不过，在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。例如，我们看到有些应用对一个数据表做10次独立的查询来返回10行数据，每个查询返回一条结果，查询10次！

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

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

rows_affected = 0

do {

rows_affected = do_query(

"DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)

LIMIT 10000")

} while rows_affected > 0

分解关联查询

mysql> SELECT * FROM tag

-> JOIN tag_post ON tag_post.tag_id=tag.id

-> JOIN post ON tag_post.post_id=post.id

-> WHERE tag.tag='mysql';

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

mysql> SELECT * FROM tag_post WHERE tag_id=1';

mysql> SELECT * FROM tag_post WHERE tag_id=1234;

mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

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

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了ID为123、567、9098的内容，那么第三个查询的IN()中就可以少几个ID。另外，对MySQL的查询缓存来说(6)，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。

将查询分解后，执行单个查询可以减少锁的竞争。

在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。我们后续将详细介绍这点。

可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。

更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多（本章后续我们将讨论这点）。

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

当希望MySQL能够以更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。一旦理解这一点，很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。

我们可以看到当向MySQL发送一个请求的时候，MySQL到底做了些什么

1. 客户端发送一条查询给服务器。

2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

5. 将结果返回给客户端。

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

统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。

执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精准，优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更小。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。

MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短，但是 MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以，这里我们看到根据执行成本来选择执行计划并不是完美的模型。

MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。

MySQL也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，例如，如果存在全文搜索的MATCH()子句，则在存在全文索引的时候就使用全文索引。即使有时候使用别的索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引。

MySQL不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本。

后面我们还会看到，优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

执行计划

和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询(18)。

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

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

mysql> UPDATE tbl AS outer_tbl

-> SET cnt = (

-> SELECT count(*) FROM tbl AS inner_tbl

-> WHERE inner_tbl.type = outer_tbl.type

-> );

ERROR 1093 (HY000): You can't specify target table 'outer_tbl' for update in FROM

clause

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

mysql> UPDATE tbl

-> INNER JOIN(www.it-eboo

-> SELECT type, count(*) AS cnt

-> FROM tbl

-> GROUP BY type

-> ) AS der USING(type)

-> SET tbl.cnt = der.cnt;

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

HIGH_PRIORITY和LOW_PRIORITY

这个提示告诉MySQL，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。HIGH_PRIORITY用于SELECT语句的时候，MySQL会将此SELECT语句重新调度到所有正在等待表锁以便修改数据的语句之前。实际上MySQL是将其放在表的队列的最前面，而不是按照常规顺序等待。HIGH_PRIORITY还可以用于INSERT语句，其效果只是简单地抵消了全局LOW_PRIORITY设置对该语句的影响。LOW_PRIORITY则正好相反：它会让该语句一直处于等待状态，只要队列中还有需要访问同一个表的语句——即使是那些比该语句还晚提交到服务器的语句。这就像一个过于礼貌的人站在餐厅门口，只要还有其他顾客在等待就一直不进去，很明显这容易把自己给饿坏。LOW_PRIORITY提示在SELECT、INSERT、UPDATE和DELETE语句中都可以使用。

这两个提示只对使用表锁的存储引擎有效，千万不要在InnoDB或者其他有细粒度锁机制和并发控制的引擎中使用。即使是在MyISAM中使用也要注意，因为这两个提示会导致并发插入被禁用，可能会严重降低性能。HIGH_PRIORITY和LOW_PRIORITY经常让人感到困惑。这两个提示并不会获取更多资源让查询“积极”工作，也不会少获取资源让查询“消极”工作。它们只是简单地控制了MySQL访问某个数据表的队列顺序。

STRAIGHT_JOIN

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

当MySQL没能选择正确的关联顺序的时候，或者由于可能的顺序太多导致MySQL无法评估所有的关联顺序的时候，STRAIGHT_JOIN都会很有用。在后面这种情况，MySQL可能会花费大量时间在“statistics”状态，加上这个提示则会大大减少优化器的搜索空间。

可以先使用EXPLAIN语句来查看优化器选择的关联顺序，然后使用该提示来重写查询，再看看它的关联顺序。当你确定无论怎样的where条件，某个固定的关联顺序始终是最佳的时候，使用这个提示可以大大提高优化器的效率。但是在升级MySQL版本的时候，需要重新审视下这类查询，某些新的优化特性可能会因为该提示而失效。

SQL_SMALL_RESULT和SQL_BIG_RESULT

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

SQL_BUFFER_RESULT

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

SQL_CACHE和SQL_NO_CACHE

这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中，下一章我们将详细介绍如何使用。

SQL_CALC_FOUND_ROWS

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

FOR UPDATE和LOCK IN SHARE MODE

这也不是真正的优化器提示。这两个提示主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于INSERT...SELECT语句是不需要这两个提示的，因为对于MySQL 5.0和更新版本会默认给这些记录加上读锁。（可以禁用该默认行为，但不是个好主意，在后面关于复制和备份的章节中将解释这一点。）

唯一内置的支持这两个提示的引擎就是InnoDB。另外需要记住的是，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他地锁定行，因为行的版本信息保存在主键中。

糟糕的是，这两个提示经常被滥用，很容易造成服务器的锁争用问题，后面章节我们将讨论这点。应该尽可能地避免使用这两个提示，通常都有其他更好的方式可以实现同样的目的。

USE INDEX、IGNORE INDEX和FORCE INDEX

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

在MySQL 5.0和更新版本中，新增了一些参数用来控制优化器的行为：

optimizer_search_depth

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

optimizer_prune_level

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

optimizer_switch

这个变量包含了一些开启/关闭优化器特性的标志位。例如在MySQL 5.1中可以通过这个参数来控制禁用索引合并的特性。

前两个参数是用来控制优化器可以走的一些“捷径”。这些捷径可以让优化器在处理非常复杂的SQL语句时，仍然可以很高效，但这也可能让优化器错过一些真正最优的执行计划。所以应该根据实际需要来修改这些参数。

6.7.5　优化LIMIT分页

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候(27)，例如可能是LIMIT 1000,20这样的查询，这时MySQL需要查询10 020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询：

mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果这个表非常大，那么这个查询最好改写成下面的样子：

mysql> SELECT film.film_id, film.description

-> FROM sakila.film

-> INNER JOIN (

-> SELECT film_id FROM sakila.film

-> ORDER BY title LIMIT 50, 5

-> ) AS lim USING(film_id);

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

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：

mysql> SELECT film_id, description FROM sakila.film

-> WHERE position BETWEEN 50 AND 54 ORDER BY position;

对数据进行排名的问题也与此类似，但往往还会同时和GROUP BY混合使用。在这种情况下通常都需要预先计算并存储排名信息。LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。例如，若需要按照租借记录做翻页，那么可以根据最新一条租借记录向后追溯，这种做法可行是因为租借记录的主键是单调增长的。首先使用下面的查询获得第一组结果：

mysql> SELECT * FROM sakila.rental

-> ORDER BY rental_id DESC LIMIT 20;

假设上面的查询返回的是主键为16049到16030的租借记录，那么下一页查询就可以从16030这个点开始：

mysql> SELECT * FROM sakila.rental

-> WHERE rental_id < 16030

-> ORDER BY rental_id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面，其性能都会很好。

其他优化办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。还可以使用Sphinx优化一些搜索操作，参考附录F可以获得更多相关信息。

在了解自定义变量的强大之前，我们再看看它自身的一些属性和限制，看看在哪些场景下我们不能使用用户自定义变量：

使用自定义变量的查询，无法使用查询缓存。

不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。

用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。

如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互（如果是这样，通常是代码bug或者连接池bug，这类情况确实可能发生）。

在5.0之前的版本，是大小写敏感的，所以要注意代码在不同MySQL版本间的兼容性问题。

不能显式地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同MySQL版本中也可能不一样。如果你希望变量是整数类型，那么最好在初始化的时候就赋值为0，如果希望是浮点型则赋值为0.0，如果希望是字符串则赋值为''，用户自定义变量的类型在赋值的时候会改变。MySQL的用户自定义变量是一个动态类型。

MySQL优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行。

赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。实际情况可能很让人困惑，后面我们将看到这一点。

赋值符号:=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。使用未定义变量不会产生任何语法错误，如果没有意识到这一点，非常容易犯错。

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();

SELECT @now;

当使用了INSERT ON DUPLICATE KEY UPDATE的时候，如果想知道到底插入了多少行数据，到底有多少数据是因为冲突而改写成更新操作的？Kerstian Köhntopp在他的博客上给出了一个解决这个问题的办法(30)。实现办法的本质如下：

INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)

ON DUPLICATE KEY UPDATE

c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );

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

确定取值的顺序

使用用户自定义变量的一个最常见的问题就是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。例如，在SELECT子句中进行赋值然后在WHERE子句中读取变量，则可能变量取值并不如你所想。下面的查询看起来只返回一个结果，但事实并非如此：

因为WHERE和SELECT是在查询执行的不同阶段被执行的。如果在查询中再加入ORDER BY的话，结果可能会更不同：

mysql> SET @rownum := 0;

mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt

-> FROM sakila.actor

-> WHERE @rownum <= 1

-> ORDER BY first_name;

这是因为ORDER BY引入了文件排序，而WHERE条件是在文件排序操作之前取值的，所以这条查询会返回表中的全部记录。解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段：

小测试：如果在上面的查询中再加上ORDER BY，那会返回什么结果？试试看吧。如果得出的结果出乎你的意料，想想为什么？再看下面这个查询会返回什么，下面的查询中ORDER BY子句会改变变量值，那WHERE语句执行时变量值是多少。

mysql> SET @rownum := 0;

mysql> SELECT actor_id, first_name, @rownum AS rownum

-> FROM sakila.actor

-> WHERE @rownum <= 1

-> ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);

这个最出人意料的变量行为的答案可以在EXPLAIN语句中找到，注意看在Extra列中的“Using where”、“Using temporary”或者“Using filesort”。

在上面的最后一个例子中，我们引入了一个新的技巧：我们将赋值语句放到LEAST()函数中，这样就可以在完全不改变排序顺序的时候完成赋值操作（在上面例子中，LEAST()函数总是返回0）。这个技巧在不希望对子句的执行结果有影响却又要完成变量赋值的时候很有用。这个例子中，无须在返回值中新增额外列。这样的函数还有GREATEST()、LENGHT()、ISNULL()、NULLIFL()、IF()和COALESCE()，可以单独使用也可以组合使用。例如，COALESCE()可以在一组参数中取第一个已经被定义的变量。

C.J. DATE的难题

C.J. DATE建议在使用数据库设计方法时尽量让SQL数据库符合传统关系数据库的要求。这也是根据关系模型设计SQL时的初衷，但坦白地说，在这一点上，MySQL远不如其他数据库管理系统做得好。所以如果按照C.J. DATE书中的建议编写的适合关系模型的SQL语句在MySQL中运行的效率并不高，例如编写一个多层的子查询。很不幸，这是因为MySQL本身的限制导致无法按照标准的模式运行。我们强烈建议你阅读这本书SQL and Relational Theory:How to Write Accurate SQL Code（http://shop.xreilly.com/product/0636920022879.do）（O'Reilly出版），它将改变你对SQL语句的认识。

BEGIN;

SELECT id FROM unsent_emails

LIMIT 10 FOR UPDATE;

-- result: 123, 456, 789

UPDATE unsent_emails

SET status = 'claimed', owner = CONNECTION_ID()

WHERE id IN(123, 456, 789);

COMMIT;

第7章　MySQL高级特性

7.1　分区表

在下面的场景中，分区可以起到非常大的作用：

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。

分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。

可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。

如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

一个表最多只能有1024个分区。

在MySQL 5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL 5.5中，某些场景中可以直接使用列来进行分区。

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。

分区表中无法使用外键约束。

现在已经有很多写好的UDF直接提供给MySQL使用，还有很多UDF的示例可供参考，以便完成自己的UDF。现在UDF最大的仓库是http://www.mysqludf.org。

下面是一个用户自定义函数NOW_USEC()的代码，这个函数在第10章中我们将用它来测量复制的速度：

7.11　分布式（XA）事务

通用查询缓存优化

库表结构的设计、查询语句、应用程序设计都可能会影响到查询缓存的效率。除了前文介绍的之外，这里还有一些要点需要注意：

用多个小表代替一个大表对查询缓存有好处。这个设计将会使得失效策略能够在一个更合适的粒度上进行。当然，不要让这个原则过分影响你的设计，毕竟其他的一些优势可能很容易就弥补了这个问题。

批量写入时只需要做一次缓存失效，所以相比单条写入效率更好。（另外需要注意，不要同时做延迟写和批量写，否则可能会因为失效导致服务器僵死较长时间。）

因为缓存空间太大，在过期操作的时候可能会导致服务器僵死。一个简单的解决办法就是控制缓存空间的大小（query_cache_size），或者直接禁用查询缓存。

无法在数据库或者表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存。你还可以通过修改会话级别的变量query_cache_type来控制查询缓存。

对于写密集型的应用来说，直接禁用查询缓存可能会提高系统的性能。关闭查询缓存可以移除所有相关的消耗。例如将query_cache_size设置成0，那么至少这部分就不再消耗任何内存了。

因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用也会有好处。如果你希望提高系统的并发，那么最好做一个相关的测试，对比打开和关闭查询缓存时候的性能差异。

如果不想所有的查询都进入查询缓存，但是又希望某些查询走查询缓存，那么可以将query_cache_type设置成DEMAND，然后在希望缓存的查询中加上SQL_CACHE。这虽然需要在查询中加入一些额外的语法，但是可以让你非常自由地控制哪些查询需要被缓存。相反，如果希望缓存多数查询，而少数查询又不希望缓存，那么你可以使用关键字SQL_NO_CACHE。

第8章　优化服务器设置

如果不知道当前使用的配置文件路径，可以尝试下面的操作：

$ which mysqld

/usr/sbin/mysqld

$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

Default options are read from the following files in the given order:

/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

让我们来看一些常用的变量和动态修改它们的效果。

key_buffer_size

设置这个变量可以一次性为键缓冲区（key buffer，也叫键缓存key cache）分配所有指定的空间。然而，操作系统不会真的立刻分配内存，而是到使用时才真正分配。例如设置键缓冲的大小为1GB，并不意味着服务器立刻分配1GB的内存。（我们下一章会讨论如何查看服务器的内存使用。）

MySQL允许创建多个键缓存，这一章后面我们会探讨这个问题。如果把非默认键缓存的这个变量设置为0，MySQL将丢弃缓存在该键缓存中的索引，转而使用默认键缓存，并且当不再有任何引用时会删除该键缓存。为一个不存在的键缓存设置这个变量，将会创建新的键缓存。对一个已经存在的键缓存设置非零值，会导致刷新该键缓存的内容。这会阻塞所有尝试访问该键缓存的操作，直到刷新操作完成。

table_cache_size

设置这个变量不会立即生效——会延迟到下次有线程打开表才有效果。当有线程打开表时，MySQL会检查这个变量的值。如果值大于缓存中的表的数量，线程可以把最新打开的表放入缓存；如果值比缓存中的表数小，MySQL将从缓存中删除不常使用的表。

thread_cache_size

设置这个变量不会立即生效——将在下次有连接被关闭时产生效果。当有连接被关闭时，MySQL检查缓存中是否还有空间来缓存线程。如果有空间，则缓存该线程以备下次连接重用；如果没有空间，它将销毁该线程而不再缓存。在这个场景中，缓存中的线程数，以及线程缓存使用的内存，并不会立刻减少；只有在新的连接删除缓存中的一个线程并使用后才会减少。（MySQL只在关闭连接时才在缓存中增加线程，只在创建新连接时才从缓存中删除线程。）

query_cache_size

MySQL在启动的时候，一次性分配并且初始化这块内存。如果修改这个变量（即使设置为与当前一样的值），MySQL会立刻删除所有缓存的查询，重新分配这片缓存到指定大小，并且重新初始化内存。这可能花费较长的时间，在完成初始化之前服务器都无法提供服务，因为MySQL是逐个清理缓存的查询，不是一次性全部删掉。

read_buffer_size

MySQL只会在有查询需要使用时才会为该缓存分配内存，并且会一次性分配该参数指定大小的全部内存。

read_rnd_buffer_size

MySQL只会在有查询需要使用时才会为该缓存分配内存，并且只会分配需要的内存大小而不是全部指定的大小。（max_read_rnd_buffer_size这个名字更能表达这个变量实际的含义。）

sort_buffer_size

MySQL只会在有查询需要做排序操作时才会为该缓存分配内存。然后，一旦需要排序，MySQL就会立刻分配该参数指定大小的全部内存，而不管该排序是否需要这么大的内存。

[mysqld]

# GENERAL

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

pid_file = /var/lib/mysql/mysql.pid

user = mysql

port = 3306

storage_engine = InnoDBdefault_storage_engine

# INNODB

innodb_buffer_pool_size = <value>

innodb_log_file_size = <value>

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

# MyISAM

key_buffer_size = <value>

# LOGGING

log_error = /var/lib/mysql/mysql-error.log

log_slow_queries = /var/lib/mysql/mysql-slow.logslow_query_log

# OTHER

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_type = 0

query_cache_size = 0

max_connections = <value>

thread_cache_size = <value>thread_cache

table_cache_size = <value>table_cache

open_files_limit = 65535

[client]

socket = /var/lib/mysql/mysql.sock

port = 3306

配置MySQL的I/O行为

8.8　完成基本配置

我们已经完成了服务器内核的旅程——希望你喜欢这个旅程！现在让我们回到示例配置，并且看下怎样修改剩下的配置。

我们已经讨论了怎样设置一般的选项，例如数据目录、InnoDB和MyISAM缓存、日志，还有其他的一些。让我们重温剩下的那些：

tmp_table_size和max_heap_table_size

这两个设置控制使用Memory引擎的内存临时表能使用多大的内存。如果隐式内存临时表的大小超过这两个设置的值，将会被转换为磁盘MyISAM表，所以它的大小可以继续增长。（隐式临时表是一种并非由自己创建，而是服务器创建，用于保存执行中的查询的中间结果的表。）

应该简单地把这两个变量设为同样的值。我们的示例配置文件中选择了32M。这可能不够，但是要谨防这个变量太大了。临时表最好呆在内存里，但是如果它们被撑得很大，实际上还是让它们使用磁盘比较好，否则可能会让服务器内存溢出。

假设查询语句没有创建庞大的临时表（通常可以通过合理的索引和查询设计来避免），那把这些变量设大一点，免得需要把内存临时表转换为磁盘临时表。这个过程可以在SHOW PROCESSLIST中看到。

可以查看服务器的SHOW STATUS计数器在某段时间内的变化，以此来查看创建临时表的频率以及是否是磁盘临时表。你不能判断一张（临时）表是先创建为内存表然后被转换为了磁盘表，还是一开始就创建的磁盘表（可能因为有BLOB字段），但是至少可以看到创建磁盘临时表有多频繁。仔细检查Created_tmp_disk_tables和Created_tmp_tables变量。

max_connections

这个设置的作用就像一个紧急刹车，以保证服务器不会因应用程序激增的连接而不堪重负。如果应用程序有问题，或者服务器遇到如连接延迟的问题，会创建很多新连接。但是如果不能执行查询，那打开一个连接没有好处，所以被“太多的连接”的错误拒绝是一种快速而代价小的失败方式。

把max_connections设置得足够高，以容纳正常可能达到的负载，并且要足够安全，能保证允许你登录和管理服务器。例如，若认为正常情况将有300或者更多连接，则可以设置为500或者更多。如果不知道将会有多少连接，500也不是一个不合理的起点。默认值是100，对大部分应用来说这都不够。

要时时小心可能遇到连接限制的突然袭击。例如，若重新启动应用服务器，可能没有把它的连接关闭干净，同时MySQL可能没有意识到它们已经被关闭了。当应用服务器重新开始运转，并试图打开到数据库的连接，就可能由于挂起的连接还没有超时，而使新连接被拒绝。

观察Max_used_connections状态变量随着时间的变化。这个是高水位标记，可以告诉你服务器连接是不是在某个时间点有个尖峰。如果这个值达到了max_connections，说明客户端至少被拒绝了一次，并且当它重现的时候，应该使用第3章中的技巧来抓取服务器的活动状态。

thread_cache_size

设置这个变量，可以通过观察服务器一段时间的活动，来计算一个有理有据的值。观察Threads_connected状态变量并且找到它在一般情况下的最大值和最小值。你也许希望把线程缓存设置得足够大，以在高峰和低谷时都足够，甚至可能更大方一些，因为就算设置得有点太大了，一般也不是大问题。你也许可以设置为波动范围两到三倍的大小。例如，若Threads_connected状态从150变化到175，可以设置线程缓存为75。但是也不用设置得非常大，因为保持大量等待连接的空闲线程并没有什么真正的用处。250的上限是个不错的估算值（或者256，如果你喜欢2的次方。）也可以观察Threads_created状态随着时间的变化。如果这个值很大或者一直增长，这是另一个线索，告诉你可能需要调大thread_cache_size变量。查看Threads_cached来看有多少线程已经在缓存中了。

一个相关的状态变量是Slow_launch_threads。这个状态如果是个很大的值，那么意味着某些情况延迟了连接分配新线程。这也是个线索，可能服务器有些问题了，但是不能明确地指出是哪出问题了。一般来说，可能是系统过载了，导致操作系统不能为新创建的线程调度CPU。这不是说你就需要增加线程缓存的大小了。你应该诊断这个问题并且修复它，而不是用缓存来掩盖问题，因为这还可能导致其他问题。

table_cache_size

这个缓存（或者在MySQL 5.1中被分成两个缓存区）应该被设置得足够大，以避免总是需要重新打开和重新解析表的定义。你可以通过观察Open_tables的值及其在一段时间的变化来检查该变量。如果你看到Opened_tables每秒变化很大，那么table_cache值可能不够大。隐式临时表也可能导致打开表的数量不断增长，即使表缓存并没有用满，所以这可能也没什么问题。

该问题的线索应该是Opened_tables不断地增长，即使Open_tables并不跟table_cache_size一样大。

虽然表缓存很有用，也不应该把这个变量设置得太大。表缓存可能在两种情况下适得其反。

首先，MySQL没有一个很有效的方法来检查缓存，所以如果真的太大了，可能效率会下降。在大部分情况下，不应该把它设置得大于10000，或者是10 240，如果喜欢使用2的N次方的话。(25)

第二个原因是有些类型的工作负载是不能缓存的。如果工作负载不是可缓存的，不管把缓存设置得多大，任何访问都无法在缓存命中，忘记缓存吧，把它设置为0！这可以避免情况变得更糟糕，缓存不命中比昂贵的缓存检查后再不命中还是要好的。什么类型的工作负载不是可缓存的？如果有几万或几十万张表，并且它们都很均匀地被使用，就不可能把它们全缓存了，最好把这个变量设得小一点。当系统上有数量非常多的并行应用而其中没有一个是非常忙碌的，有时候这是适当的。

这个值从max_connections的10倍开始设置是比较有道理的，但是再次说明，在大部分场景下最好保持在10000以下甚至更低。

还有其他一些类型的设置可能经常会包含在配置文件中，包括二进制日志以及复制设置。二进制日志对恢复到某个时间点，以及复制是非常有用的，另外复制还有一些它自己的设置。我们会在本书后面的章节中覆盖复制和备份的重要设置。

8.9　安全和稳定的设置

基本配置设置到位后，可能希望启用一些使服务器更安全和更可靠的设置。它们中的一些会影响性能，因为保证安全性和可靠性往往要付出一些代价。有些人意识到了：他们能阻止愚蠢的错误发生，比如把无意义的数据插入服务器，以及一些变动在日常操作中没有啥区别，只是在很边缘的情况防止糟糕的事情发生。

让我们首先来看看收集的一些对一般服务器都有用的配置项：

expire_logs_days

如果启用了二进制日志，应该打开这个选项，可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用，最终服务器的空间会被耗尽，导致服务器卡住或崩溃。我们建议把这个选项设置得足够从两个备份之前恢复（在最近的备份失败的情况下）。即使每天都做备份，还是建议留下7～14天的二进制日志。从我们的经验来看，当遇到一些不常见的问题时，你会感谢有这一两个星期的二进制日志。例如重搭一个备机再次尝试赶上主库。应该保持足够多的二进制日志，遇到这些情况时可以给自己一些呼吸的空间。

max_allowed_packet

这个设置防止服务器发送太大的包，也会控制多大的包可以被接收。默认值可能太小了，但设置得太大也可能有危险。如果设置得太小，有时复制上会出问题，通常表现为备库不能接收主库发过来的复制数据。你也许需要增加这个设置到16MB或者更大。这些文档里没有，但这个选项也控制在一个用户定义的变量的最大值，所以如果需要非常大的变量，要小心——如果超过这个变量的大小，它们可能被截断或者设置为NULL。

max_connect_errors

如果有时网络短暂抽风了，或者应用配置出现错误，或者有另外的问题，如权限，在短暂的时间内不断地尝试连接，客户端可能被列入黑名单，然后将无法连接，直到再次刷新主机缓存。这个选项的默认设置太小了，很容易导致问题。你也许希望增加这个值，实际上，如果知道服务器可以充分抵御蛮力攻击，可以把这个值设得非常大，以有效地禁用主机黑名单。

skip_name_resolve

这个选项禁用了另一个网络相关和鉴权认证相关的陷阱：DNS查找。DNS是MySQL连接过程中的一个薄弱环节。当连接服务器时，默认情况下，它试图确定连接和使用的主机的主机名，作为身份验证凭据的一部分。（就是说，你的凭据是用户名，主机名、以及密码——并不只是用户名和密码）但是验证主机来源，服务器需要执行DNS的正向和反向查找。要是DNS有问题就悲剧了，在某些时间点这是必然的事。当发生这样的情况时，所有事都会堆积起来，最终导致连接超时。为了避免这种情况，我们强烈建议设置这个选项，在验证时关闭DNS查找。然而，如果这么做，需要把基于主机名的授权改为用IP地址、通配符，或者特定主机名“localhost”，因为基于主机名的账号会被禁用。

sql_mode

这个设置可以接受多种多样的值来改变服务器行为。我们不建议只是为了好玩而改变这个值；最好在大多数情况下让MySQL像MySQL，不要尝试让它的行为像其他数据库服务器。（许多客户端和图形界面工具，除了MySQL还有它们自己的SQL方言，例如，若修改它用更符合ANSI的SQL，有些操作会没法做。）然而，有些选项值是很有用的，有些在具体情况可能是值得考虑的。建议查看文档中下面这些选项，并且考虑使用它们：STRICT_TRANS_TABLES、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_AUTO_VALUE_ON_ZERO、NO_ENGINE_SUBSTITUTION、NO_ZERO_DATE、NO_ZERO_IN_DATE和ONLY_FULL_GROUP_BY。

然而，要意识到对已经存在的应用修改这些设置值可不是个好主意，因为这么做可能让服务器跟应用预期不兼容。人们不经意间写的查询中应用的列不在GROUP BY中，或者使用聚合函数，这种情况非常常见，例如，若想打开ONLY_FULL_GROUP_BY选项，最好首先在开发或未上线服务器上做一下测试，一旦要在生产环境部署则必须确认所有地方都可以工作。

sysdate_is_now

这是另一个可能导致与应用预期向后不兼容的选项。但如果不是明确需要SYSDATE()函数的非确定性行为（非确定性行为可能会导致复制中断或者使得基于时间点的备份恢复结果不可信），那么你可能希望打开该选项以确保SYSDATE()函数有确定的行为。

下面的选项可以控制复制行为，并且对防止备库出问题非常有帮助：

read_only

这个选项禁止没有特权的用户在备库做变更，只接受从主库传输过来的变更，不接受从应用来的变更。我们强烈建议把备库设置为只读模式。

skip_slave_start

这个选项阻止MySQL试图自动启动复制。因为在不安全的崩溃或其他问题后，启动复制是不安全的，所以需要禁用自动启动，用户需要手动检查服务器，并确定它是安全的之后再开始复制。

slave_net_timeout

这个选项控制备库发现跟主库的连接已经失败并且需要重连之前等待的时间。默认值是一个小时，太长了。设置为一分钟或更短。

sync_master_info、sync_relay_log、sync_relay_log_info

这些选项，在MySQL 5.5以及更新版本中可用，解决了复制中备库长期存在的问题：不把它们的状态文件同步到磁盘，所以服务器崩溃后可能需要人来猜测复制的位置实际上在主库是哪个位置，并且可能在中继日志（Relay Log）里有损坏。这些选项使得备库崩溃后，更容易从崩溃中恢复。这些选项默认是不打开的，因为它们会导致备库额外的fsync()操作，可能会降低性能。如果有很好的硬件，我们建议打开这些选项，如果复制中出现fsync()造成的延时问题，就应该关闭它们。

Percona Server中有一种侵入性更小的方式来做这些工作，即打开innodb_overwrite_relay_log_info选项。这可以让InnoDB在事务日志中存储复制的位置，这是完全事务化的，并且不需要任何额外的fsync()操作。在崩溃恢复期间， InnoDB会检查复制的元信息文件，如果文件过期了就更新为正确的位置。

8.10　高级InnoDB设置

回到第1章我们讨论的InnoDB历史：首先是内建（built-in）的版本，然后有了两个有效版本，现在更新的版本再次变成了一个。更新的InnoDB代码有更多的功能和非常好的扩展性。如果正在使用MySQL 5.1，应该明确地配置MySQL忽略旧版本的InnoDB而使用新版的。这将极大地提升服务器性能。需要打开ignore_builtin_innodb选项，然后配置plugin_load选项把InnoDB作为插件打开。建议参考InnoDB文档中对应平台上的扩展语法(26)。

对于新版本的InnoDB，有一些新的选项可以用。如果启用，它们中有些对服务器性能相当重要，也有一些安全性和稳定性的选项，如下所示。

innodb

这个看似平淡无奇的选项实际上非常重要，如果把这个值设置为FORCE，只有在InnoDB可以启动时，服务器才会启动。如果使用InnoDB作为默认存储引擎，这一定是你期望的结果。你应该不会希望在InnoDB失败（例如因为错误的配置而导致的不可启动）的情况下启动服务器，因为写的不好的应用可能之后会连接到服务器，导致一些无法预知的损失和混乱。最好是整个服务器都失败，强制你必须查看错误日志，而不是以为服务器正常启动了。

innodb_autoinc_lock_mode

这个选项控制InnoDB如何生成自增主键值，某些情况下，例如高并发插入时，自增主键可能是个瓶颈。如果有很多事务等待自增锁（可以在SHOW ENGINE INNODB STATUS里看到），应该审视这个变量的设置。手册上已经详细解释了该选项的行为，在此我们就不再重复了。

innodb_buffer_pool_instances

这个选项在MySQL 5.5和更新的版本中出现，可以把缓冲池切分为多段，这可能是在高负载的多核机器上提升MySQL可扩展性最重要的一个方式了。多个缓冲池分散了工作压力，所以一些全局Mutex竞争就没有那么大了。

目前尚不清楚什么情况下应该选择多个缓冲池实例。我们运行过八个实例的基准，但是直到MySQL 5.5已经广泛部署了很长一段时间，我们依然不明白多个缓冲池实例的一些微妙之处。

我们不是暗示MySQL 5.5没有在生产环境广泛部署。只是对我们已经帮助解决过的大部分互斥锁相互争用的极端场景的用户来说，升级可能需要很多个月的时间来计划、验证，并执行。这些用户有时运行着高度定制化的MySQL版本，使得更加倍谨慎地对待升级。当越来越多的这类用户升级到MySQL 5.5，并以他们独特的方式进行压力验证，我们可能会学到关于多缓冲池的一些我们没见过的有趣的事情。也许直到那时，我们才可以说运行八个缓冲池实例是非常有益的。

值得注意的是Percona Server用了不同的方法来解决InnoDB互斥锁争用问题。相对于把缓冲池分成多个——一个在许多像InnoDB的系统下经过检验无可否认的方法——我们选择把一些全局Mutex拆分为更细、更专用的Mutex。我们的测试显示最好的方式是结合这两种方法，在Percona Server 5.5版本中已经可用了：多缓冲区和更细粒度的锁。

innodb_io_capacity

InnoDB曾经在代码里写死了假设服务器运行在每秒100个I/O操作的单硬盘上。默认值很糟糕。现在可以告诉InnoDB服务器有多大的I/O能力。InnoDB有时需要把这个设置得相当高（在像PCI-E SSD这样极快的存储设备上需要设置为上万）才能稳定地刷新脏页，原因解释起来相当复杂。

innodb_read_io_threads和innodb_write_io_threads

这些选项控制有多少后台线程可以被I/O操作使用。最近版本的MySQL里，默认值是4个读线程和4个写线程，对大部分服务器这都足够了，尤其是MySQL 5.5里面可以用操作系统原生的异步I/O以后。如果有很多硬盘并且工作负载并发很大，可以发现这些线程很难跟上，这种情况下可以增加线程数，或者可以简单地把这个选项的值设置为可以提供I/O能力的磁盘数量（即使后面是一个RAID控制器）。

innodb_strict_mode

这个设置让MySQL在某些条件下把警告改成抛错，尤其是无效的或者可能有风险的CREATE TABLE选项。如果打开这个设置，就必然会检查所有CREATE TABLE选项，因为它不会让你创建一些用起来比较爽（但是有隐患）的表。有时这有点悲观，过于严格了。当尝试恢复备份时可能就不希望打开这个选项了。

innodb_old_blocks_time

InnoDB有个两段缓冲池LRU（最近最少使用）链表，设计目的是防止换出长期使用很多次的页面。像mysqldump产生的这种一次性的（大）查询，通常会读取页面到缓冲池的LRU列表，从中读取需要的行，然后移动到下一页。理论上，两段LRU链表将阻止此页取代很长一段时间内都需要用到的页面被放入“年轻（Young）”子链表，并且只在它已被浏览过多次后将其移动到“年老（Old）”子链表。但是InnoDB默认没有配置为防止这种情况，因为页内有很多行，所以从页面读取的行的多次访问，会导致它立即被转移到“年老（Old）”子链表，对那些需要长时间缓存的页面带来换出的压力。

这个变量指定一个页面从LRU链表的“年轻”部分转移到“年老”部分之前必须经过的毫秒数。默认情况下它设置为0，将它设为诸如1000毫秒（一秒）这样的小一点的值，在我们的基准测试中已被证明非常有效。

第9章　操作系统和硬件优化

第10章　复制

MySQL支持两种复制方式：基于行的复制和基于语句的复制。基于语句的复制（也称为逻辑复制）早在MySQL 3.23版本中就存在，而基于行的复制方式在5.1版本中才被加进来。这两种方式都是通过在主库上记录二进制日志(2)、在备库重放日志的方式来实现异步的数据复制。这意味着，在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。

复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。除此之外，每个备库也会对主库增加一些负载（例如网络I/O开销），尤其当备库请求从主库读取旧的二进制日志文件时，可能会造成更高的I/O开销。另外锁竞争也可能阻碍事务的提交。最后，如果是从一个高吞吐量（例如5000或更高的TPS）的主库上复制到多个备库，唤醒多个复制线程发送事件的开销将会累加。

复制如何工作？

复制有三个步骤：

在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）。

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

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

基于语句的复制模式的优点

当主备的模式不同时，逻辑复制能够在多种情况下工作。例如，在主备上的表的定义不同但数据类型相兼容、列的顺序不同等情况。这样就很容易先在备库上修改schema，然后将其提升为主库，减少停机时间。基于语句的复制方式一般允许更灵活的操作。

基于语句的方式执行复制的过程基本上就是执行SQL语句。这意味着所有在服务器上发生的变更都以一种容易理解的方式运行。这样当出现问题时可以很好地去定位。

基于语句的复制模式的缺点

很多情况下通过基于语句的模式无法正确复制，几乎每一个安装的备库都会至少碰到一次。事实上对于存储过程，触发器以及其他的一些语句的复制在5.0和5.1的一系列版本中存在大量的Bug。这些语句的复制的方式已经被修改了很多次，以使其更好地工作。简单地说：如果正在使用触发器或者存储过程，就不要使用基于语句的复制模式，除非能够清楚地确定不会碰到复制问题。

基于行的复制模式的优点

几乎没有基于行的复制模式无法处理的场景。对于所有的SQL构造、触发器、存储过程等都能正确执行。只是当你试图做一些诸如在备库修改表的schema这样的事情时才可能导致复制失败。

这种方式同样可能减少锁的使用，因为它并不要求这种强串行化是可重复的。

基于行的复制模式会记录数据变更，因此在二进制日志中记录的都是实际上在主库上发生了变化的数据。你不需要查看一条语句去猜测它到底修改了哪些数据。在某种程度上，该模式能够更加清楚地知道服务器上发生了哪些更改，并且有一个更好的数据变更记录。另外在一些情况下基于行的二进制日志还会记录发生改变之前的数据，因此这可能有利于某些数据恢复。

在很多情况下，由于无须像基于语句的复制那样需要为查询建立执行计划并执行查询，因此基于行的复制占用更少的CPU。

最后，在某些情况下，基于行的复制能够帮助更快地找到并解决数据不一致的情况。举个例子，如果是使用基于语句的复制模式，在备库更新一个不存在的记录时不会失败，但在基于行的复制模式下则会报错并停止复制。

基于行的复制模式的缺点

由于语句并没有在日志里记录，因此无法判断执行了哪些SQL，除了需要知道行的变化外，这在很多情况下也很重要（这可能在未来的MySQL版本中被修复）。

log_slave_updates选项可以让备库变成其他服务器的主库。在设置该选项后，MySQL会将其执行过的事件记录到它自己的二进制日志中。这样它的备库就可以从其日志中检索并执行事件。

复制拓扑

一主库多备库

主动-主动模式下的主-主复制

多主库复制（multisource replication）特指一个备库有多个主库。不管之前你知道什么，但MySQL（和其他数据库产品不一样）现在不支持

主动-被动模式下的主-主复制

这是前面描述的主-主结构的变体，它能够避免我们之前讨论的问题。这也是构建容错性和高可用性系统的非常强大的方式，主要区别在于其中的一台服务器是只读的被动服务器

拥有备库的主-主结构

环形复制

主库、分发主库以及备库

选择性复制

为了利用访问局部性原理（locality of reference），并将需要读的工作集驻留在内存中，可以复制少量数据到备库中。如果每个备库只拥有主库的一部分数据，并且将读分配给备库，就可以更好地利用备库的内存。并且每个备库也只有主库一部分的写入负载，这样主库的能力更强并能保证备库延迟。

这个方案有点类似下一章我们会讨论到的水平数据划分，但它的优势在于主库包含了所有的数据集，这意味着无须为了一条写入查询去访问多个服务器。如果读操作无法在备库上找到数据，还可以通过主库来查询。即使不能从备库上读取所有数据，也可以移除大量的主库读负担。

最简单的方法是在主库上将数据划分到不同的数据库里。然后将每个数据库复制到不同的备库上。例如，若需要将公司的每一个部门的数据复制到不同的备库，可以创建名为sales、marketing、procurement等的数据库，每个备库通过选项replicate_wild_do_table选项来限制给定数据库的数据。下面是sales数据库的配置：

replicate_wild_do_table = sales.%

分离功能

许多应用都混合了在线事务处理（OLTP）和在线数据分析（OLAP）的查询。OLTP查询比较短并且是事务型的，OLAP查询则通常很大，也很慢，并且不要求绝对最新的数据。这两种查询给服务器带来的负担完全不同，因此它们需要不同的配置，甚至可能使用不同的存储引擎或者硬件。

一个常见的办法是将OLTP服务器的数据复制到专门为OLAP工作负载准备的备库上。这些备库可以有不同的硬件、配置、索引或者不同的存储引擎。如果决定在备库上执行OLAP查询，就可能需要忍受更大的复制延迟或降低备库的服务质量。这意味着在一个非专用的备库上执行一些任务时，可能会导致不可接受的性能，例如执行一条长时间运行的查询。

无须做一些特殊的配置，除了需要选择忽略主库上的一些数据，前提是能获得明显的提升。即使通过复制过滤器过滤掉一小部分的数据也会减少I/O和缓存活动。

数据归档

可以在备库上实现数据归档，也就是说可以在备库上保留主库上删除过的数据，在主库上通过delete语句删除数据是确保delete语句不传递到备库就可以实现。有两种通常的办法：一种是在主库上选择性地禁止二进制日志，另一种是在备库上使用replicate_ignore_db规则（是的，两种方法都很危险）。

第一种方法需要先将SQL_LOG_BIN设置为0，然后再进行数据清理。这种方法的好处是不需要在备库进行任何配置，由于SQL语句根本没有记录到二进制日志中，效率会稍微有所提升。最大缺点也正因为没有将在主库的修改记录下来，因此无法使用二进制日志来进行审计或者做按时间点的数据恢复。另外还需要SUPER权限。

第二种方法是在清理数据之前对主库上特定的数据库使用USE语句。例如，可以创建一个名为purge的数据库，然后在备库的my.cnf文件里设置replicate_ignore_db=purge并重启服务器。备库将会忽略使用了USE语句指定的数据库。这种方法没有第一种方法的缺点，但有另一个小小的缺点：备库需要去读取它不需要的事件。另外，也可能有人在purge数据库上执行非清理查询，从而导致备库无法重放该事件。

创建日志服务器

使用MySQL复制的另一种用途就是创建没有数据的日志服务器。它唯一的目的就是更加容易重放并且/或者过滤二进制日志事件。就如本章稍后所述，它对崩溃后重启复制很有帮助。同时对基于时间点的恢复也很有帮助，在第15章我们会讨论。

假设有一组二进制日志或中继日志——可能从备份或者一台崩溃的服务器上获取——希望能够重放这些日志中的事件，可以通过mysqlbinlog工具从其中提取出事件，但更加方便和高效的方法是配置一个没有任何数据的MySQL实例并使其认为这些二进制日志是它拥有的。如果只是临时需要，可以从http://mysqlsandbox.net上获得一个MySQL沙箱脚本来创建日志服务器。因为无须执行二进制日志，日志服务器也就不需要任何数据。它的目的仅仅是将数据提供给别的服务器（但复制账户还是需要的）。

我们来看看该策略是如何工作的（稍后会展示一些相关应用）。假设日志被命名为somelog-bin.000001、somelog-bin.000002，等等，将这些日志放到日志服务器的日志文件夹中，假设为/var/log/mysql。然后在启动服务器前编辑my.cnf文件，如下所示：

log_bin = /var/log/mysql/somelog-bin

log_bin_index = /var/log/mysql/somelog-bin.index

服务器不会自动发现日志文件，因此还需要更新日志的索引文件。下面这个命令可以在类UNIX系统上完成(14)。

# /bin/ls -1 /var/log/mysql/somelog-bin.[0-9]* > /var/log/mysql/somelog-bin.index

确保运行MySQL的账户能够读写日志索引文件。现在可以启动日志服务器并通过SHOW MASTER LOGS命令来确保其找到日志文件。

为什么使用日志服务器比用mysqlbinlog来实现恢复更好呢？有以下几个原因：

复制作为应用二进制日志的方法已经被大量的用户所测试，能够证明是可行的。mysqlbinlog并不能确保像复制那样工作，并且可能无法正确生成二进制日志中的数据更新。

复制的速度更快，因为无须将语句从日志导出来并传送给MySQL。

可以很容易观察到复制过程。

能够更方便处理错误。例如，可以跳过执行失败的语句。

更方便过滤复制事件。

有时候mysqlbinlog会因为日志记录格式更改而无法读取二进制日志。

我们已经讨论了MySQL复制的一些扩展技术，如Oracle GoldenGate，但对大多数工具我们都不熟悉，因此无法讨论太多。但是有两个我们需要指出来，第一个是Percona XtraDB Cluster的同步复制，我们会在第12章介绍，因为它比较适合在高可用性这一章讲述。另一个是Continuent的Tungsten Replicator （http://code.google.com/p/tungsten-replicator/）。

Tungsten是一个用Java编写的开源的中间件复制产品。它的功能和Oracle GoldenGate类似，并且看起来在未来发布的版本中将逐步增加许多复杂的特性。在写作本书时，它已经提供了一些特性，例如，在服务器间复制数据、自动数据分片、在备库并发执行更新（多线程复制）、当主库失败时提升备库、跨平台复制，以及多源复制（多个复制源到一个目标）。它是Tungsten数据库clustering suite的开源版本。

Tungsten同样实现了多主库集群，可以把写入指向集群中任意一台服务器。这种架构的实现通常都包含冲突发现与解决。这一点很难做到，并且不总是需要的。Tungsten的实现稍微做了点限制，不是所有的数据都能在所有的节点写入，每个节点被标记为记录系统，以接收特定的数据。例如，在西雅图的办公室可以拥有并写入它的数据，然后复制到休斯敦和巴尔的摩。在休斯敦和巴尔的摩本地可以实现低延迟读数据，但在这里Tungsten不允许写入数据，这样数据冲突就不存在了。当然休斯敦和巴尔的摩可以更新它们自己的数据，并被复制到其他地点。这种“记录系统”方案解决了人们需要在环形结构中频繁调整内建MySQL复制的问题。我们之前讨论的环形复制还远远不够安全或强健。

11. 可扩展的MySQL

向外扩展

1．按功能拆分

按功能拆分，或者说按职责拆分，意味着不同的节点执行不同的任务。我们之前已经提到了一些类似的实现，在前一章我们描述了如何为OLTP和OLAP工作负载设计不同的服务器。按功能拆分采取的策略比这些更进一步，将独立的服务器或节点分配给不同的应用，这样每个节点只包含它的特定应用所需要的数据。

这里我们显式地使用了“应用”一词。所指的并不是一个单独的计算机程序，而是相关的一系列程序，这些程序可以很容易地彼此分离，没有关联。例如，如果有一个网站，各个部分无须共享数据，那么可以按照网站的功能区域进行划分。门户网站常常把不同的栏目放在一起；在门户网站，可以浏览网站新闻、论坛，寻求支持和访问知识库，等等。这些不同功能区域的数据可以放到专用的MySQL服务器中

2．数据分片

在目前用于扩展大型MySQL应用的方案中，数据分片(7)是最通用且最成功的方法。它把数据分割成一小片，或者说一块，然后存储到不同的节点中。

数据分片在和某些类型的按功能划分联合使用时非常有用。大多数分片系统也有一些“全局的”数据不会被分片（例如城市列表或者登录数据）。全局数据一般存储在单个节点上，并且通常保存在类似memcached这样的缓存里。

事实上，大多数应用只会对需要的数据做分片——通常是那些将会增长得非常庞大的数据。假设正在构建的博客服务，预计会有1000万用户，这时候就无须对注册用户进行分片，因为完全可以将所有的用户（或者其中的活跃用户）放到内存中。假如用户数达到5亿，那么就可能需要对用户数据分片。用户产生的内容，例如发表的文章和评论，几乎肯定需要进行数据分片，因为这些数据非常庞大，并且还会越来越多。

大型应用可能有多个逻辑数据集，并且处理方式也可以各不相同。可以将它们存储到不同的服务器组上，但这并不是必需的。还可以以多种方式对数据进行分片，这取决于如何使用它们。下文我们会举例说明。

分片技术和大多数应用的最初设计有着显著的差异，并且很难将应用从单一数据存储转换为分片架构。如果在应用设计初期就已经预计到分片，那实现起来就容易得多。

许多一开始没有建立分片架构的应用都会碰到规模扩大的情形。例如，可以使用复制来扩展博客服务的读查询，直到它不再奏效。然后可以把服务器划分为三个部分：用户信息、文章，以及评论。可以将这些数据放到不同的服务器上（按功能划分），也许可以使用面向服务的架构，并在应用层执行联合查询。图11-6显示了从单台服务器到按功能划分的演变。

分片数据存储看起来像是优雅的解决方案，但很难实现。那为什么要选择这个架构呢？答案很简单：如果想扩展写容量，就必须切分数据。如果只有单台主库，那么不管有多少备库，写容量都是无法扩展的。对于上述缺点而言，数据分片是我们首选的解决方案。

3．选择分区键（partitioning key）

跨多个分片的查询比单个分片上的查询性能要差，但只要不涉及太多的分片，也不会太糟糕。最糟糕的情况是不知道需要的数据存储在哪里，这时候就需要扫描所有分片。

一个好的分区键常常是数据库中一个非常重要的实体的主键。这些键值决定了分片单元。例如，如果通过用户ID或客户端ID来分割数据，分片单元就是用户或者客户端。

确定分区键一个比较好的办法是用实体—关系图，或一个等效的能显示所有实体及其关系的工具来展示数据模型。尽量把相关联的实体靠得更近。这样可以很直观地找出候选分区键。当然不要仅仅看图，同样也要考虑应用的查询。即使两个实体在某些方面是相关联的，但如果很少或几乎不对其做关联操作，也可以打断这种联系来实现分片。

4．多个分区键

复杂的数据模型会使数据分片更加困难。许多应用拥有多个分区键，特别是存在两个或更多个“维度”的时候。换句话说，应用需要从不同的角度看到有效且连贯的数据视图。这意味着某些数据在系统内至少需要存储两份。

例如，需要将博客应用的数据按照用户ID和文章ID进行分片，因为这两者都是应用查询数据时使用比较普遍的方式。试想一下这种情形：频繁地读取某个用户的所有文章，以及某个文章的所有评论。如果按用户分片就无法找到某篇文章的所有评论，而按文章分片则无法找到某个用户的所有文章。如果希望这两个查询都落到同一个分片上，就需要从两个维度进行分片。

需要多个分区键并不意味着需要去设计两个完全冗余的数据存储。我们来看看另一个例子：一个社交网站下的读书俱乐部站点，该站点的所有用户都可以对书进行评论。该网站可以显示所有书籍的所有评论，也能显示某个用户已经读过或评论过的所有书籍。

假设为用户数据和书籍数据都设计了分片数据存储。而评论同时拥有用户ID和评论ID，这样就跨越了两个分片的边界。实际上却无须冗余存储两份评论数据，替代方案是，将评论和用户数据一起存储，然后把每个评论的标题和ID与书籍数据存储在一起。这样在渲染大多数关于某本书的评论的视图时无须同时访问用户和书籍数据存储，如果需要显示完整的评论内容，可以从用户数据存储中获得。

5．跨分片查询

大多数分片应用多少都有一些查询需要对多个分片的数据进行聚合或关联操作。例如，一个读书俱乐部网站要显示最受欢迎或最活跃的用户，就必须访问每一个分片。如何让这类查询很好地执行，是实现数据分片的架构中最困难的部分。虽然从应用的角度来看，这是一条查询，但实际上需要拆分成多条并行执行的查询，每个分片上执行一条。一个设计良好的数据库抽象层能够减轻这个问题，但类似的查询仍然会比分片内查询要慢并且更加昂贵，所以通常会更加依赖缓存。

一些语言，如PHP，对并行执行多条查询的支持不够好。普遍的做法是使用C或Java编写一个辅助应用来执行查询并聚合结果集。PHP应用只需要查询该辅助应用即可，例如Web服务或者类似Gearman的工作者服务。

6．分配数据、分片和节点

分片和节点不一定是一对一的关系，应该尽可能地让分片的大小比节点容量小很多，这样就可以在单个节点上存储多个分片。

保持分片足够小更容易管理。这将使数据的备份和恢复更加容易，如果表很小，那么像更改表结构这样的操作会更加容易。例如，假设有一个100GB的表，你可以直接存储，也可以将其划分为100个1GB的分片，并存储在单个节点上。现在假如要向表上增加一个索引，在单个100GB的表上的执行时间会比100个1GB分片上执行的总时间更长，因为1GB的分片更容易全部加载到内存中。并且在执行ALTER TABLE时还会导致数据不可用，阻塞1GB的数据比阻塞100GB的数据要好得多。

7．在节点上部署分片

需要确定如何在节点上部署数据分片。以下是一些常用的办法：

每个分片使用单一数据库，并且数据库名要相同。典型的应用场景是需要每个分片都能镜像到原应用的结构。这在部署多个应用实例，并且每个实例对应一个分片时很有用。

将多个分片的表放到一个数据库中，在每个表名上包含分片号（例如bookclub.comments_23）。这种配置下，单个数据库可以支持多个数据分片。

为每个分片使用一个数据库，并在数据库中包含所有应用需要的表。在数据库名中包含分片号（例如表名可能是bookclub_23.comments或者bookclub_23.users等），但表名不包括分片号。当应用连接到单个数据库并且不在查询中指定数据库名时，这种做法很常见。其优点是无须为每个分片专门编写查询，也便于对只使用单个数据库的应用进行分片。

每个分片使用一个数据库，并在数据库名和表名中包含分片号（例如表名可以是bookclub_23.comments_23）。

在每个节点上运行多个MySQL实例，每个实例上有一个或多个分片，可以使用上面提到的方式的任意组合来安排分片。

这在新应用中很容易实现，但对于已有的应用则有点困难。构建新应用时，查询模板并不是问题，我们倾向于使用每个分片一个数据库的方式，并把分片号写到数据库名和表名中。这会增加例如ALTER TABLE这类操作的复杂度，但也有如下一些优点：

如果分片全部在一个数据库中，转移分片会比较容易。

因为数据库本身是文件系统中的一个目录，所以可以很方便地管理一个分片的文件。

如果分片互不关联，则很容易查看分片的大小。

全局唯一表名可避免误操作。如果表名每个地方都相同，很容易因为连接到错误的节点而查询了错误的分片，或者是将一个分片的数据误导入另外一个分片的表中。

你可能想知道应用的数据是否具有某种“分片亲和性”。也许将某些分片放在一起（在同一台服务器，同一个子网，同一个数据中心，或者同一个交换网络中）可以利用数据访问模式的相关性，能够带来些好处。例如，可以按照用户进行分片，然后将同一个国家的用户放到同一个节点的分片上。

为已有的应用增加分片支持的结果往往是一个节点对应一个分片。这种简化的设计可以减少对应用查询的修改。分片对应用而言通常是一种颠覆性的改变，所以应尽可能简化它。如果在分片后，每个节点看起来就像是整个应用数据的缩略图，就无须去改变大多数查询或担心查询是否传递到期望的节点。

8．固定分配

固定分配的主要优点是简单，开销低，甚至可以在应用中直接硬编码。

但固定分配也有如下缺点：

如果分片很大并且数量不多，就很难平衡不同分片间的负载。

固定分片的方式无法自定义数据放到哪个分片上，这一点对于那些在分片间负载不均衡的应用来说尤其重要。一些数据可能比其他的更加活跃，如果这些热点数据都分配到同一个分片中，固定分配的方式就无法通过热点数据转移的方式来平衡负载。（如果每个分片的数据量切分得比较小，这个问题就没那么严重，根据大数定律，这样做会更容易将热点数据平均分配到不同分片。）

修改分片策略通常比较困难，因为需要重新分配已有的数据。例如，如果通过模10的哈希函数来进行分片，就会有10个分片。如果应用增长使得分片变大，如果要拆分成20个分片，就需要对所有数据重新哈希，这会导致更新大量数据，并在分片间转移数据。

正是由于这些限制，我们倾向于为新应用选择动态分配的方式。但如果是为已有的应用做分片，使用固定分配策略可能会更容易些，因为它更简单。也就是说，大多数使用固定分配的应用最后迟早要使用动态分配策略。

9．动态分配

另外一个选择是使用动态分配，将每个数据单元映射到一个分片。假设一个有两列的表，包括用户ID和分片ID。

CREATE TABLE user_to_shard (

user_id INT NOT NULL,

shard_id INT NOT NULL,

PRIMARY KEY (user_id)

);

这个表本身就是分区函数。给定分区键（用户ID）的值就可以获得分片号。如果该行不存在，就从目标分片中找到并将其加入到表中。也可以推迟更新——这就是动态分配的含义。

动态分配增加了分区函数的开销，因为需要额外调用一次外部资源，例如目录服务器（存储映射关系的数据存储节点）。出于效率方面的考虑，这种架构常常需要更多的分层。例如，可以使用一个分布式缓存系统将目录服务器的数据加载到内存中，因为这些数据平时改动很小。或者更普遍地，你可以直接向USERS表中增加一个shard_id列用于存储分片号。

动态分配的最大好处是可以对数据存储位置做细粒度的控制。这使得均衡分配数据到分片更加容易，并可提供适应未知改变的灵活性。

动态映射可以在简单的键—分片（key-to-shard）映射的基础上建立多层次的分片策略。例如，可以建立一个双重映射，将每个分片单元指定到一个分组中（例如，读书俱乐部的用户组），然后尽可能将这些组保持在同一个分片中。这样可以利用分片亲和性，避免跨分片查询。

如果使用动态分配策略，可以生成不均衡的分片。如果服务器能力不相同，或者希望将其中一些分片用于特定目的（例如归档数据），这可能会有用。如果能够做到随时重新平衡分片，也可以为分片和节点间维持一一对应的映射关系，这不会浪费容量。也有些人喜欢简单的每个节点一个分片的方式。（但是请记住，保持分片尽可能小是有好处的。）动态分配以及灵活地利用分片亲和性有助于减轻规模扩大而带来的跨分片查询问题。假设一个跨分片查询涉及四个节点，当使用固定分配时，任何给定的查询可能需要访问所有分片，但动态分配策略则可能只需要在其中的三个节点上运行同样的查询。这看起来没什么大区别，但考虑一下当数据存储增加到400个分片时会发生什么？固定分配策略需要访问400个分片，而动态分配方式依然只需要访问3个。

动态分配可以让分片策略根据需要变得很复杂。固定分配则没有这么多选择。

10．混合动态分配和固定分配

可以混合使用固定分配和动态分配。这种方法通常很有用，有时候甚至必须要混合使用。目录映射不太大时，动态分配可以很好胜任。但如果分片单元太多，效果就会变差。

以一个存储网站链接的系统为例。这样一个站点需要存储数百亿的行，所使用的分区键是源地址和目的地址URL的组合。（这两个URL的任意一个都可能有好几亿的链接，因此，单独一个URL并不适合做分区键）。但是在映射表中存储所有的源地址和目的地址URL组合并不合理，因为数据量太大了，每个URL都需要很多存储空间。

一个解决方案是将URL相连并将其哈希到固定数目的桶中，然后把桶动态地映射到分片上。如果桶的数目足够大——例如100万个——你就能把大多数数据分配到每个分片上，获得动态分配的大部分好处，而无须使用庞大的映射表。

11．显式分配

第三种分配策略是在应用插入新的数据行时，显式地选择目标分片。这种策略在已有的数据上很难做到。所以在为应用增加分片时很少使用。但在某些情况下还是有用的。

这个方法是把数据分片号编码到ID中，这和之前提到的避免主—主复制主键冲突策略比较相似。（详情请参阅“在主—主复制结构中写入两台主库”。）

例如，假设应用要创建一个用户3，将其分配到第11个分片中，并使用BIGINT列的高八位来保存分片号。这样最终的ID就是(11<<56)+3，即792633534417207299。应用可以很方便地从中抽取出用户ID和分片号，如下例所示。

现在假设要为该用户创建一条评论，并存储在同一个分片中。应用可以为该用户分配一个评论ID 5，然后以同样的方式组合5和分片号11。

这种方法的好处是每个对象的ID同时包含了分区键，而其他方法通常需要一次关联或查找来确定分区键。如果要从数据库中检索某个特定的评论，无须知道哪个用户拥有它；对象ID会告诉你到哪里去找。如果对象是通过用户ID动态分片的，就得先找到该评论的用户，然后通过目录服务器找到对应的数据分片。

另一个解决方案是将分区键存储在一个单独的列里。例如，你可能从不会单独引用评论5，但是评论5属于用户3。这种方法可能会让一些人高兴，因为这不违背第一范式；然而额外的列会增加开销、编码，以及其他不便之处。（这也是我们将两值存在单独一列的优点之一。）

显式分配的缺点是分片方式是固定的，很难做到分片间的负载均衡。但结合固定分配和动态分配，该方法就能够很好地工作。不再像之前那样哈希到固定数目的桶里并将其映射到节点，而是将桶作为对象的一部分进行编码。这样应用就能够控制数据的存储位置，因此可以将相关联的数据一起放到同样的分片中。

BoardReader（http://boardreader.com）使用了该技术的一个变种：它把分区键编码到Sphinx的文档ID内。这使得在分片数据存储中查找每个查询结果的关联数据变得容易，更多关于Sphinx的内容可以查阅附录F。

我们讨论了混合分配方式，因为在某些场景下它是有用的。但正常情况下我们并不推荐这样用。我们倾向于尽可能使用动态分配，避免显式分配。

12．重新均衡分片数据

如有必要，可以通过在分片间移动数据来达到负载均衡。举个例子，许多读者可能听一些大型图片分享网站或流行社区网站的开发者提到过用于分片间移动用户数据的工具。在分片间移动数据的好处很明显。例如，当需要升级硬件时，可以将用户数据从旧分片转移到新分片上，而无须暂停整个分片的服务或将其设置为只读。

然而，我们也应该尽量避免重新均衡分片数据，因为这可能会影响用户使用。在分片间转移数据也使得为应用增加新特性更加困难，因为新特性可能还需要包含针对重新均衡脚本的升级。如果分片足够小，就无须这么做；也可以经常移动整个分片来重新均衡负载，这比移动分片中的部分数据要容易得多（并且以每行数据开销来衡量的话，更有效率）。

一个较好的策略是使用动态分片策略，并将新数据随机分配到分片中。当一个分片快满时，可以设置一个标志位，告诉应用不要再往这里放数据了。如果未来需要向分片中放入更多数据，可以直接把标记位清除。

13．生成全局唯一ID

当希望把一个现有系统转换为分片数据存储时，经常会需要在多台机器上生成全局唯一ID。单一数据存储时通常可以使用AUTO_INCREMENT列来获取唯一ID。但涉及多台服务器时就不凑效了。以下几种方法可以解决这个问题：

使用auto_increment_increment和auto_increment_offset

这两个服务器变量可以让MySQL以期望的值和偏移量来增加AUTO_INCREMENT列的值。举一个最简单的场景，只有两台服务器，可以配置这两台服务器自增幅度为2，其中一台的偏移量设置为1，另外一台为2（两个都不可以设置为0）。这样一台服务器总是包含偶数，另外一台则总是包含奇数。这种设置可以配置到服务器的每一个表里。

这种方法简单，并且不依赖于某个节点，因此是生成唯一ID的比较普遍的方法。但这需要非常仔细地配置服务器。很容易因为配置错误生成重复数字，特别是当增加服务器需要改变其角色，或进行灾难恢复时。

全局节点中创建表

在一个全局数据库节点中创建一个包含AUTO_INCREMENT列的表，应用可以通过这个表来生成唯一数字。

使用memcached

在memcached的API中有一个incr()函数，可以自动增长一个数字并返回结果。另外也可以使用Redis。

批量分配数字

应用可以从一个全局节点中请求一批数字，用完后再申请。

使用复合值

可以使用一个复合值来做唯一ID，例如分片号和自增数的组合。具体参阅之前的章节。

使用GUID值

可以使用UUID()函数来生成全局唯一值。注意，尽管这个函数在基于语句的复制时不能正确复制，但可以先获得这个值，再存放到应用的内存中，然后作为数字在查询中使用。GUID的值很大并且不连续，因此不适合做InnoDB表的主键。具体参考“和InnoDB主键一致地插入行”。在5.1及更新的版本中还有一个函数UUID_SHORT()，能够生成连续的值，并使用64位代替了之前的128位。

如果使用全局分配器来产生唯一ID，要注意避免单点争用成为应用的性能瓶颈。

虽然memcached方法执行速度快（每秒数万个值），但不具备持久性。每次重启memcached服务都需要重新初始化缓存里的值。由于需要首先找到所有分片中的最大值，因此这一过程非常缓慢并且难以实现原子性。

14．分片工具

在设计数据分片应用时，首先要做的事情是编写能够查询多个数据源的代码。

如果没有任何抽象层，直接让应用访问多个数据源，那绝对是一个很差的设计，因为这会增加大量的编码复杂性。最好的办法是将数据源隐藏在抽象层中。这个抽象层主要完成以下任务：

连接到正确的分片并执行查询。

分布式一致性校验。

跨分片结果集聚合。

跨分片关联操作。

锁和事务管理。

创建新的数据分片（或者至少在运行时找到新分片）并重新平衡分片（如果有时间实现）。

你可能不需要从头开始构建分片结构。有一些工具和系统可以提供一些必要的功能或专门设计用来实现分片架构。

Hibernate Shards（http://shards.hibernate.org）是一个支持分片的数据库抽象层，基于Java语言的开源的Hibernate ORM库扩展，由谷歌提供。它在Hibernate Core 接口上提供了分片感知功能，所以应用无须专门为分片设计；事实上，应用甚至无须知道它正在使用分片。Hibernate Shards 通过固定分配策略向分片分配数据。另外一个基于Java的分片系统是HiveDB（http://www.hivedb.org）。

如果使用的是PHP语言，可以使用Justin Swanhart提供的Shard-Query系统（http://code.google.com/p/shard-query/），它可以自动分解查询，并发执行，并合并结果集。另外一些有同样用途的商用系统有ScaleBase（http://www.scalebase.com）、ScalArc（http://www.scalarc.com），以及dbShards（http://www.dbshards.com）。

Sphinx是一个全文检索引擎，虽然不是分片数据存储和检索系统，但对于一些跨分片数据存储的查询依然有用。Sphinx可以并行查询远程系统并聚合结果集。在附录F中会详细讨论Sphinx。

11.2.5　通过多实例扩展

一个分片较多的架构可能会更有效地利用硬件。我们的研究和经验表明MySQL并不能完全发挥现代硬件的性能。当扩展到超过24个CPU核心时，MySQL的性能开始趋于平缓，不再上升。当内存超过128GB时也同样如此，MySQL甚至不能完全发挥诸如Virident或Fusion-io卡这样的高端PCIe flash设备的I/O性能。

不要在一台性能强悍的服务器上只运行一个服务器实例，我们还有别的选择。你可以让数据分片足够小，以使每台机器上都能放置多个分片（这也是我们一直提倡的），每台服务器上运行多个实例，然后划分服务器的硬件资源，将其分配给每个实例。

这样做尽管比较烦琐，但确实有效。这是一种向上扩展和向外扩展的组合方案。也可以用其他方法来实现——不一定需要分片——但分片对于在大型服务器上的联合扩展具有天然的适应性。

1．MySQL Cluster（NDB Cluster）

MySQL Cluster是两项技术的结合：NDB数据库，以及作为SQL前端的MySQL存储引擎。NDB是一个分布式、具备容错性、非共享的数据库，提供同步复制以及节点间的数据自动分片。NDB Cluset存储引擎将SQL转换为NDB API调用，但遇到NDB不支持的操作时，就会在MySQL服务器上执行（NDB是一个键—值数据存储，无法执行类似联接或聚合的复杂操作）。

NDB是一个非常复杂的数据库，和MySQL几乎完全不同。在使用NDB时甚至可以不需要MySQL：你可以把它作为一个独立的键—值数据库服务器。它的亮点包括非常高的写入和按键查询吞吐量。NDB可以基于键的哈希自动决定哪个节点应该存储给定的数据。当通过MySQL来控制NDB时，行的主键就是键，其他的列是值。

因为它基于一些新的技术，并且集群具有容错性和分布式特性，所以管理NDB需要非常专业和特殊的技能。有许多动态变化的部分，还有类似升级集群或增加节点的操作必须正确执行以防止意外的问题。NDB是一项开源技术，但也可以从Oracle购买商业支持。商业支持中包括能够获得专门的集群管理产品Cluster Manager，可以自动执行一些枯燥且棘手的任务。（Severalnines同样提供了一个集群管理产品，参见http://www. severalnines.com）。

MySQL Cluster正在迅速地增加越来越多的特性和功能。例如在最近的版本中，它开始支持更多类型的集群变更而无须停机操作，并且能够在数据存储的节点上执行一些特定类型的查询，以减少数据传递给MySQL层并在其中执行查询的必要性。（这个特性已由关联下推（push-down join）更名为自适应查询本地化（adaptive query localization）。）

NDB曾经相对其他MySQL存储引擎具有完全不同的性能特性，但最近的版本更加通用化了。它正在成为越来越多应用的更好的解决方案，包括游戏和移动应用。我们必须强调，NDB是一项重要的技术，能够支持全球最大的关键应用，这些应用处于极高的负载下，具有非常严苛的延迟要求以及不间断要求。举个例子，世界上任何一个通过移动电话网络呼叫的电话使用的就是NDB，并且不是临时方案——对于许多移动电话提供商而言，它是一个主要的并且非常重要的数据库。

NDB需要一个快速且可靠的网络来连接节点。为了获得最好的性能，最好使用特定的高速连接设备。由于大多数情况下需要内存操作，因此服务器间需要大量的内存。

那么它有什么缺点呢？复杂查询现在支持得还不是很好，例如那些有很多关联和聚合的查询。所以不要指望用它来做数据仓库。NDB是一个事务型系统，但不支持MVCC，所以读操作也需要加锁，也不做任何的死锁检测。如果发生死锁，NDB就以超时返回的方式来解决。还有很多你应该知道的要点和警告，可以专门写一本书了。（有一些关于MySQL Cluster的书，但大多数都过时了，最好的办法是阅读手册。）

2．CIustrix

Clustrix（http://www.clustrix.com）是一个分布式数据库，支持MySQL协议，所以它可以直接替代MySQL。除了协议外，它是一个全新的技术，并非建立在MySQL的基础之上。它是一个完全支持ACID，支持MVCC的事务型SQL数据库，主要用于OLTP负载场景。Clustrix 在节点间进行数据分片以满足容错性，并对查询进行分发，在节点上并发执行，而不是将所有节点上取得的数据集中起来执行。集群可以在线扩展节点来处理更多的数据或负载。在某些方面Clustrix和MySQL Cluster很像；关键的不同点是，Clustrix是完全分布式执行并且缺少顶层的“代理”或者集群前端的查询协调器（query coordinator）。Clustrix本身能够理解MySQL协议，所以无须MySQL来进行协议转换。相比较而言， MySQL cluster是由三个部分组成的：MySQL，NDB集群存储引擎，以及NDB。

我们的实验评估和性能测试表明，Clustrix能够提供高性能和可扩展性。Clustrix看起来是一项比较有前景的技术，我们将继续观察和评估。

3．ScaleBase

ScaleBase（http://www.scalebase.com）是一个软件代理，处于应用和多个后端MySQL服务器之间。它会把发起的查询进行分裂，并将其分发到后端服务器并发执行，然后汇集结果返回给应用。不过在写作本书时，我们还没有使用该产品的经验。另外的竞争产品有ScaleArc（http://www.calearc.com）和dbShards（http://www.dbshards.com）。

4．GenieDB

GenieDB（http://www.geniedb.com）最开始用于地理上分布部署的NoSQL文档存储。现在它也有一个SQL层，可以通过MySQL存储引擎进行控制。它包含了很多技术，包括本地内存缓存、消息层，以及持久化磁盘数据存储。将这些技术汇集在一起，就可以使用松散的最终一致性，让应用在本地快速执行查询，或是通过分布式集群（会增加网络延迟）来保证最新的数据视图。

通过存储引擎实现的MySQL兼容层不能提供100％的MySQL特性，但对于支持类似Joomla!、WordPress，以及Drupal 这样的应用已经够用了。MySQL存储引擎的用处主要是使GenieDB能够结合存储引擎获得对ACID的支持，例如InnoDB。GenieDB本身并不是ACID数据库。

我们还没用应用过GenieDB，也没有看到任何生产环境部署。

5．Akiban

对Akiban（http://www.akiban.com）最好的描述应该是查询加速器。它通过存储物理数据来匹配查询模式，使得低开销的跨表关联操作成为可能。尽管类似反范式化（denormalization），但数据层并不是冗余的，所以这和预先计算关联并存储结果的方式是不同的。关联表中元组是互相交错的，所以能够按照关联顺序进行顺序扫描。这就要求管理员确定查询模式能够从所谓的“表组”（table grouping）技术中受益，并需要为查询优化设计表组。目前建议的系统架构是将Akiban配置为MySQL主库的备库，并用它来为可能较慢的查询提供服务。加速系数是一到两个数量级。但是我们还没有看到生产环境部署或者相关的实验评估。(10)11.2.7　向内扩展

处理不断增长的数据和负载最简单的办法是对不再需要的数据进行归档和清理。这种操作可能会带来显著的成效，具体取决于工作负载和数据特性。这种做法并不用来代替其他策略，但可以作为争取时间的短期策略，也可以作为处理大数据量的长期计划之一。

MySQL分区

MySQL 5.1本身提供了对表进行分区的功能，能够帮助把最近的数据留在内存中。第7章详细介绍了分区表。

基于时间的数据分区

如果应用不断有新数据进来，一般新数据总是比旧数据更加活跃。例如，我们知道博客服务的流量大多是最近七天发表的文章和评论。更新的大部分是相同的数据集。因此这些数据被完整地保留在内存中，使用复制来保证在主库失效时有一份可用的备份。其他数据则完全可以放到别的地方去。

我们也看到过这样一种设计，在两个节点的分片上存储用户数据。新数据总是进入“活跃”节点，该节点使用更大的内存和快速硬盘，另外一个节点存储旧数据，使用非常大（但比较慢）的硬盘。应用假设不太会需要旧数据。对于很多应用而言这是合理的假设，依靠10％的最新数据能够满足90％或更多的请求。

可以通过动态分片来轻松实现这种策略。例如，分片目录表可能定义如下：

CREATE TABLE users (

user_id int unsigned not null,

shard_new int unsigned not null,

shard_archive int unsigned not null,

archive_timestamp timestamp,

PRIMARY KEY (user_id)

);

通过一个归档脚本将旧数据从活跃节点转移到归档节点，当移动用户数据到归档节点时，更新archive_timestamp列的值。shard_new和shard_archive列记录存储数据的分片号。

在决定如何实现负载均衡时，应该考虑到这些因素。有许多负载均衡解决方案可以使用，从诸如Wackamole（http://www.backhand.org/wackamole/）这样基于端点的（peer-based）实现，到DNS、LVS（Linux Virtual Server，http://www.linuxvirtualserver.org）、硬件负载均衡器、TCP代理、MySQL Proxy，以及在应用中管理负载均衡。

在我们的客户中，最普遍的策略是使用硬件负载均衡器，大多是使用HAProxy（http://haproxy.1wt.eu），它看起来很流行并且工作得很好。还有一些人使用TCP代理，例如Pen（http://siag.nu/pen/）。但MySQL Proxy用得并不多。

复制上的读写分离：

比较常见的读/写分离方法如下：

基于查询分离

最简单的分离方法是将所有不能容忍脏数据的读和写查询分配到主动或主库服务器上。其他的读查询分配到备库或被动服务器上。该策略很容易实现，但事实上无法有效地使用备库，因为只有很少的查询能容忍脏数据。

基于脏数据分离

这是对基于查询分离方法的小改进。需要做一些额外的工作，让应用检查复制延迟，以确定备库数据是否太旧。许多报表类应用都使用这个策略：只需要晚上加载的数据复制到备库即可，它们并不关心是不是100％跟上了主库。

基于会话分离

另一个决定能否从备库读数据的稍微复杂一点的方法是判读用户自己是否修改了数据。用户不需要看到其他用户的最新数据，但需要看到自己的更新。可以在会话层设置一个标记位，表明做了更新，就将该用户的查询在一段时间内总是指向主库。这是我们通常推荐的策略，因为它是在简单和有效性之间的一种很好的妥协。

如果有足够的想象力，可以把基于会话的分离方法和复制延迟监控结合起来。如果用户在10秒前更新了数据，而所有备库延迟在5秒内，就可以安全地从备库中读取数据。但为整个会话选择同一个备库是一个很好的主意，否则用户可能会奇怪有些备库的更新速度比其他服务器要慢。

基于版本分离

这和基于会话的分离方法相似：你可以跟踪对象的版本号以及/或者时间戳，通过从备库读取对象的版本或时间戳来判断数据是否足够新。如果备库的数据太旧，可以从主库获取最新的数据。即使对象本身没有变化，但如果是顶层对象，只要下面的任何对象有变化，也可以增加版本号，这简化了脏数据检查（只需要检查顶层对象一处就能判断是否有更新）。例如，在用户发表了一篇新文章后，可以更新用户的版本。这样就会从主库去读取数据了。

基于全局版本/会话分离

这个办法是基于版本分离和基于会话分离的变种。当应用执行写操作时，在提交事务后，执行一次SHOW MASTER STATUS操作。然后在缓存中存储主库日志坐标，作为被修改对象以及/或者会话的版本号。当应用连接到备库时，执行SHOW SLAVE STATUS并将备库上的坐标和缓存中的版本号相对比。如果备库相比记录点更新，就可以安全地读取备库数据。

大多数读/写分离解决方案都需要监控复制延迟来决策读查询的分配，不管是通过复制或负载均衡器，或是一个中间系统。如果这么做，需要注意通过SHOW SLAVE STATUS得到的Seconds_behind_master列的值并不能准确地用于监控延迟。（详情参阅第10章）。Percona Toolkit中的pt-heartbeat工具能够帮助监控延迟，并维护元数据，例如二进制日志位置，这可以减轻之前我们讨论的一些策略存在的问题。

如果不在乎用昂贵的硬件来承载压力，也就可以不使用复制来扩展读操作，这样当然更简单。这可以避免在主备上分离读的复杂性。有些人认为这很有意义；也有人认为会浪费硬件。这种分歧是由于不同的目的引起的：你是只需要可扩展性，还是要同时具有可扩展性和高利用率？如果需要高利用率，那么备库除了保存数据副本外还需要承担其他任务，就不得不处理这些额外的复杂度。

第12章　高可用性

第13章云端的Mysql

第14章应用层优化

New Relic是一个很好的例子，它可以剖析Web应用的前端、应用以及后端。

一般来说，首先要做的事是测量。认真剖析每一层的问题。哪一层导致了大部分的响应时间？对这一层就要重点关注。如果用户的经验是大部分的时间消耗在浏览器的DOM渲染上面，MySQL只贡献总响应时间的一小部分，那么进一步优化查询语句绝对不可能明显地改善用户体验。在测量完成后，通常很容易理解应该在哪里投入精力。我们建议阅读Steve Souders的两本书（High Performance Web Sites和Even Faster Web Sites），并且建议使用New Relic工具。

第15章　备份与恢复

第16章　MySQL用户工具

Percona Toolkit

Percona Toolkit是MySQL管理员必备的工具包。它源自Baron早期的工具包Maatkit和Aspersa，很多人认为这两个工具应该是正式的MySQL部署必须强制要求使用的。Percona Toolkit包括许多针对类似日志分析、复制完整性检测、数据同步、模式和索引分析、查询建议和数据归档目的的工具。如果刚开始接触MySQL，我们建议首先学习这些关键的工具：pt-mysql-summary、pt-table-checksum、pt-table-sync和pt-query-digest。更多信息可参考http://www.percona.com/software/。Maatkit and Aspersa

这两个工具约从2006年以某种形式出现，两者都被认为是MySQL用户的基本工具。它们现在已经并入 Percona Toolkit。The openark kit

Shlomi Noach的openark kit（http://code.openark.org/forge/openark-kit）包含了可以用来做一系列管理任务的Python脚本。

MySQL Workbench工具集

MySQL Workbench工具集中的某些工具可以作为单独的Python脚本使用。可参考 https://launchpad.net/mysql-utilities。

除了这些工具外，还有其他一系列没有太正式包装和维护的工具。许多杰出的MySQL社区成员时不时地贡献工具，其中大多数托管在他们自己的网站或MySQL Forge（http:// forge.mysql.com）上。可以通过不时地查看Planet MySQL博客聚合器获取大量的信息（http://planet.mysql.com），但不幸的是这些工具没有一个集中的目录。

服务器本身也内置有一系列免费的附加组件和实用集可以使用；其中一些确实相当强大。common_schema

Shlomi Noach的common_schema项目（http://code.openark.org/forge/common_schema）是一套针对服务器脚本化和管理的强大的代码和视图。common_schema对于MySQL好比jQuery对于JavaScript。mysql-sr-lib

Giuseppe Maxia为MySQL创建了一个存储过程的代码库，可以在http://www.nongnu.org/mysql-sr-lib/找到。MySQL UDF仓库

Roland Bouman建立了一个MySQL自定义函数的收藏馆，可以在 http://www. mysqludf.org获取。MySQL Forge

在MySQL Forge上（http://forge.mysql.com），可以找到上百个社区贡献的程序、脚本、代码片断、实用集和技巧及陷阱。

16.4.1　开源的监控工具

下面是一些最受欢迎的开源集成监控系统。Nagios

Nagios（http://www.nagios.org）也许是开源世界中最流行的问题检测和告警系统。它周期性检测监控的服务器并将结果与默认或自定义的阈值相比较。如果结果超出了限制，Nagios会执行某个程序并且（或）把问题的告警发给某些人。Nagios的通信和告警系统可以将告警发给不同的联系人，改变告警，或根据一天中的时间和其他条件将其发送到不同的位置，并且对计划内的宕机可以特殊处理。Nagios同样理解服务之间的依赖，因此，如果是因为中间的路由层宕机或者主机本身宕机导致MySQL实例不可用，Nagios不会发送告警来烦你。

Nagios能将任何一个可执行文件以插件形式运行，只要给予其正确参数就可得到正确输出。因此，Nagios插件在多种语言中都存在，例如shell、Perl、Python、Ruby和其他脚本语言。就算找不到一个能真正满足你需求的插件，自己创建一个也很简单。一个插件只需要接收标准的参数，以一个合适的状态退出，然后选择性地打印Nagios捕获的输出。

然而，Nagios也有一些严重的缺点。即使你很了解它，也仍然难以维护。它将所有配置保存在文件而不是数据库中。文件有一个特别容易出错的语法，当系统增长和发展时，修改配置文件就很费事。Nagios可扩展性并不好；你可以很容易地写出监控插件，但这也就是你能够做的一切。最后，它的图形化、趋势化和可视化能力都有限。Nagios将一些性能和其他数据存储到MySQL服务器中，一般从中生成图形，但并不像其他一些系统那么灵活。因为不同“政见”的原因，使得上面所有的问题继续变得更糟。因为或真实、或臆测的涉及代码、参与者的问题，Nagios至少分化出了两个分支。两个分支的名字分别是Opsview（http://www.opsview.com）和Icinga（http://www.icinga.org）。它们比Nagios更受到人们的亲睐。

有一些专门介绍Nagios的书籍；我们倾向于Wolfgang Barth的Nagios System and Network Monitoring（No Starch出版公司）。Zabbix

Zabbix是一个同时支持监控和指标收集的完整系统。例如，它将所有配置和其他数据存储到数据库而不是配置文件中。它存储了比Nagios更多的数据类型，因而可以得到更好的趋势和历史报表。其网络画图和可视能力也比Nagios更强，配置更简单，更灵活，且更具可扩展性。可参考http://www.zabbix.com获取更多信息。Zenoss

Zenoss是用Python写的，拥有一个基于浏览器的用户界面，使用了Ajax，这使它更快和更高效。它可以自动发现网络上的资源，并将监控、告警、趋势、绘图和记录历史数据整合到了一个统一的工具中。Zenoss默认使用SNMP来从远程服务器上收集数据，但也可以使用SSH，并且支持Nagios插件。更多信息请参考http://www. zenoss.com。Hyperic HQ

Hyperic HQ是一个基于Java的监控系统，比起同级别的其他大部分系统，它更称得上是企业级监控。像Zenoss一样，它可以自动发现网络上的资源和支持Nagios插件，但它的逻辑组织和架构不同，有点“笨重”。更多信息可参考 http://www.hyperic.com。OpenNMS

OpenNMS也是用Java开发，有一个活跃的开发社区。它拥有常规的特性，例如监控和告警，但同样也增加了绘图和趋势功能。它的目标是高性能、可扩展、自动化和灵活。像Hyperic一样，它也致力于为大型和关键系统做企业级监控。更多信息请参考http://www.opennms.org。Groundwork Open Source

Groundwork Open Source用一个可移植的接口把Nagios和其他几个工具整合到了一个系统中。对于这个工具最好的描述是：如果你是Nagios、Cacti和其他几个工具方面的专家，并且花了许多时间将它们整合一起，那很可能你是在闭门造车。更多信息可参考 http://www.gwos.com。

相比于集所有功能于一身的系统，还有一系列软件专注于收集指标和画图以及可视化，而不是进行性能监控检查。他们中有很多是建立在RRDTool（http://www.rrdtool.org）之上，存储时序数据到轮询数据库（RRD）文件中。RRD文件自动聚集输入数据，对没有预期传送的输入值进行插值，并有强大的绘图工具可以生成漂亮有特色的图。有很多基于RRDTool的系统，下面是其中最受欢迎的几个。MRTG

Multi Router Traffic Grapher或称MRTG（http://oss.oetiker.ch/mrtg/），是典型的基于RRDTool的系统。最初是为记录网络流量而设计的，但同样可以扩展到用于对其他指标进行记录和绘图。Cacti

Cacti （http://www.cacti.net）可能是最流行的基于RRDTool的系统。它采用PHP网页来与RRDTool进行交互，并使用MySQL数据库来定义服务器、插件、图像等。因为是模板驱动，故而可以定义模板然后应用到系统上。Baron为MySQL和其他系统写了一组非常流行的模板；更多信息请参考http://code.google.com/p/mysql-cacti-templates/。这些也已经被移植到Munin、OpenNMS和Zabbix。Ganglia

Ganglia（http://ganglia.sourceforge.net）与Cacti类似，但是为监控集群和网格系统而设计，所以可以汇总查看许多服务器的数据，如果需要也可以细分查看单台服务器的详细数据。

Munin

Munin（http://munin.projects.linpro.no）收集数据并存入RRDTool中，然后以几个不同级别的粒度生成数据图。它从配置中生成静态HTML文件，因此可以很容易地浏览和查看趋势。定义一个图形较容易；只需要创建一个插件脚本，其命令行帮助输出有一些Munin可以识别的特别语法的画图指令。

基于RRDTool的系统有些限制，例如不能用标准查询语言来查询存储的数据，不能永久保留数据，存在某些数据不能轻松地使用简单计数器和标准数值表示的问题，需要预先定义指标和图形等。理想情况下，我们需要的监控系统可以接受任何发送给它的指标，而不需要预先进行定义，并且后续可以绘制任意需要的图形，也不需要预先进行定义。可能我们所看到的最接近的系统是Graphite（http://graphite.wikidot.com）。

16.4.2　商业监控系统

尽管我们知道许多MySQL用户热衷使用开源工具，但也有许多人愿意为合适的软件买单，只要这些软件可以让工作更好地完成，为他们节省时间，减少烦恼。下面是一些可以利用的商业选件。MySQL Enterprise Monitor

MySQL Enterprise Monitor包含在Oracle的MySQL支持服务中。它将监控、指标和画图、咨询服务和查询分析等特性整合到了一个工具中。通过在服务器上使用agent来监测状态计数器（也包含操作系统的关键指标）。它能以两种方式抓取查询：通过MySQL代理（MySQL Proxy），或使用合适的MySQL连接器，例如Java的Connector/J或PHP的MySQLi。尽管是为监控MySQL而设计的，但某种程度上也可以进行扩展。同样，这个工具也无法监控基础架构中所有的服务器和所有的服务。更多信息请参考http://www.mysql.com/products/enterprise/monitor.html。MONyog

MONyog（http://www.webyog.com）是一个运行在桌面上的基于浏览器且无agent的监控系统。它会启动一个HTTP服务器，然后就可以通过浏览器来使用此工具。New Relic

New Relic（http://newrelic.com）是一个托管式的软件即服务（Saas）的应用性能管理系统，它可以分析整个应用的性能，从应用代码（采用Ruby，PHP，Java和其他语言）到运行在浏览器上的JavaScript，到数据库的SQL调用，甚至是服务器的磁盘空间，CPU利用率和其它指标。Circonus

Circonus（https://circonus.com）是一个源于OmniTI的托管式的软件即服务（SaaS）的指标和告警系统。通过agent从一个或多个服务器上收集指标并转发到Circonus，然后就可以通过一个基于浏览器的仪表盘来查看。Monitis

Monitis（http://monitis.com）是另外一个云托管式的软件即服务（SaaS）的监控系统。它被设计成监控“一切”，这意味着它有点普遍性。它有一个入门级的免费版Monitor.us （http://mon.itor.us），也有支持MySQL的插件。Splunk

Splunk（http://www.splunk.com）是一个日志聚集器和搜索引擎，可以帮助获得环境中所有机器生成的数据并进行运营分析。Pingdom

Pingdom （http://www.pingdom.com）从世界的多个位置来监控网站的可用性和性能。实际上有许多像Pingdom一样的服务，我们并不需要特别推荐某一个这样的服务，但是我们确实建议使用一些外部的监控服务，以便让你在网站不可用时能够及时得到通知。很多类似的服务远不止Ping或获取网页。

还有许多其他的商业监控工具——我们可以凭印象列举出十几个或更多。对所有监控系统而言，要注意的一点是它们对服务器的影响。有些工具相当直白，因为它们由一些没有实际的大型高负载MySQL系统经验的公司设计。例如，我们不止一次通过禁止每分钟对所有的数据库执行一次SHOW TABLE STATUS的监控功能来解决突发事件。（这个命令在高I/O限制的系统上特别有破坏性。）频繁查询INFORMATION_SCHEMA表的工具也会导致负面影响。16.4.3　Innotop的命令行监控

有一些基于命令行的监测工具，它们大部分在某种方面模拟了UNIX中的top工具。其中最精致和最胜任的是innotop（http://code.google.com/p/innotop/），我们将详细探讨。此外，还有几个其他的工具，例如mtop（http://mtop.sourceforge.net）、mytop（http://jeremy.zawodny.com/mysql/mytop/）和一些基于网页的mytop克隆版本。

尽管mytop是MySQL上最原始的top克隆，但innotop比mytop拥有更多功能，这也是我们看重innotop的原因。

本书的作者之一Balon Schwartz编写了innotop。它展示了服务器正在发生事情的实时更新视图。别去理会它的名称，实际上它不仅仅用于监控InnoDB，还可以监控MySQL任何其他的方面。它也能同时监控多个MySQL 实例，极具可配置性和可扩展性。

它的功能特性包括以下这些：

事务列表可以显示InnoDB 当前的全部事务。

查询列表可以显示当前正在运行的查询。

可以显示当前锁和锁等待的列表。

以相对值显示服务器状态和变量的汇总信息。

有多种模式可用来显示InnoDB 内部信息，例如缓冲区、死锁、外键错误、I/O 活动情况、行操作、信号量，以及其他更多的内容。

复制监控，将主服务器和从服务器的状态显示在一起。

显示任意服务器变量的模式。

服务器组可以更方便地组织多台服务器。

在命令行脚本下可以使用非交互式模式。innotop的安装很容易，可以从操作系统的软件仓库安装，也可以从http://code.google.com/p/innotop/下载到本地，然后解压缩，运行标准的make install安装过程。

perl Makefile.PL

make install

一旦安装完成，就可以在命令行里执行innotop，然后它会引导你完成连接到MySQL实例的过程。引导过程会读取~/.my.cnf选项文件，这样，除了输入服务器的主机名和按几次Enter键之外，什么都不用做。连接完成以后，就处在T（InnoDB Transaction）模式了，这时，应该可看到InnoDB 事务列表，如图16-1 所示。

调用EXPLAIN

在查询中每个表在输出中只有一行。如果查询是两个表的联接，那么输出中将有两行。别名表单算为一个表，因此，如果把一个表与自己联接，输出中也会有两行。“表”的意义在这里相当广：可以是一个子查询，一个UNION结果，等等。稍后会看到为什么是这样。EXPLAIN有两个主要的变种。EXPLAIN EXTENDED看起来和正常的EXPLAIN的行为一样，但它会告诉服务器“逆向编译”执行计划为一个SELECT语句。可以通过紧接其后运行SHOW WARNINGS看到这个生成的语句。这个语句直接来自执行计划，而不是原SQL语句，到这点上已经变成一个数据结构。在大部分场景下它都与原语句不相同。你可以检测查询优化器到底是如何转化语句的。EXPLAIN EXTENDED在MySQL 5.0和更新版本中可用，在MySQL 5.1（稍后会做更多讨论）额外增加了一个filtered列。EXPLAIN PARTITIONS会显示查询将访问的分区，如果查询是基于分区表的话。它只在MySQL 5.1和更新版本中存在。

认为增加EXPLAIN时MySQL不会执行查询，这是一个常见的错误。事实上，如果查询在FROM子句中包括子查询，那么MySQL实际上会执行子查询，将其结果放在一个临时表中，然后完成外层查询优化。它必须在可以完成外层查询优化之前处理所有类似的子查询，这对于EXPLAIN来说是必须要做的(1)。这意味着如果语句包含开销较大的子查询或使用临时表算法的视图，实际上会给服务器带来大量工作。

要意识到EXPLAIN只是个近似结果，别无其他。有时候它是一个很好的近似，但在其他时候，可能与真相相差甚远。以下是一些相关的限制。EXPLAIN根本不会告诉你触发器、存储过程或UDF会如何影响查询。

它并不支持存储过程，尽管可以手动抽取查询并单独地对其进行EXPLAIN操作。

它并不会告诉你MySQL在查询执行中所做的特定优化。

它并不会显示关于查询的执行计划的所有信息（MySQL开发者会尽可能增加更多信息）。

它并不区分具有相同名字的事物。例如，它对内存排序和临时文件都使用“filesort”，并且对于磁盘上和内存中的临时表都显示“Using temporary”。

可能会误导。例如，它会对一个有着很小LIMIT的查询显示全索引扫描。（MySQL 5.1的EXPLAIN关于检查的行数会显示更精确的信息，但早期版本并不考虑LIMIT。）

select_type列

这一列显示了对应行是简单还是复杂SELECT（如果是后者，那么是三种复杂类型中的哪一种）。SIMPLE值意味着查询不包括子查询和UNION。如果查询有任何复杂的子部分，则最外层部分标记为PRIMARY，其他部分标记如下。

SUBQUERY

包含在SELECT列表中的子查询中的SELECT（换句话说，不在FROM子句中）标记为SUBQUERY。

DERIVEDDERIVED值用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的。

UNION

在UNION中的第二个和随后的SELECT被标记为UNION。第一个SELECT被标记就好像它以部分外查询来执行。这就是之前的例子中在UNION中的第一个SELECT显示为PRIMARY的原因。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。

UNION RESULT

用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT。

除了这些值，SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。DEPENDENT意味着SELECT依赖于外层查询中发现的数据；UNCACHEABLE意味着SELECT中的某些特性阻止结果被缓存于一个Item_cache中。（Item_cache未被文档记载；它与查询缓存不是一回事，尽管它可以被一些相同类型的构件否定，例如RAND()函数。）

table列

这一列显示了对应行正在访问哪个表。在通常情况下，它相当明了：它就是那个表，或是该表的别名（如果SQL中定义了别名）。

可以在这一列中从上往下观察MySQL的关联优化器为查询选择的关联顺序。例如，可以看到在下面的查询中MySQL选择的关联顺序不同于语句中所指定的顺序。

想起我们在第6章中展示的左侧深度优先（left-deep）树了吗？MySQL的查询执行计划总是左侧深度优先树。如果把这个计划放倒，就能按顺序读出叶子节点，它们直接对应于EXPLAIN中的行。之前的查询计划看起来如图D-1所示。

图D-1：查询执行计划与EXPLAIN中的行相对应的方式

派生表和联合

当FROM子句中有子查询或有UNION时，table列会变得复杂得多。在这些场景下，确实没有一个“表”可以参考到，因为MySQL创建的匿名临时表仅在查询执行过程中存在。

当在FROM子句中有子查询时，table列是<derivedN>的形式，其中N是子查询的id。这总是“向前引用”——换言之，N指向EXPLAIN输出中后面的一行。

当有UNION时，UNION RESULT的table列包含一个参与UNION的id列表。这总是“向后引用”，因为UNION RESULT出现在UNION中所有参与行之后。如果在列表中有超过20个id，table列可能被截断以防止太长，此时不可能看到所有的值。幸运的是，仍然可以推测包括哪些行，因为你可以看到第一行的id。在这一行和UNION RESULT之间出现的一切都会以某种方式被包含。

type列

MySQL用户手册上说这一列显示了“关联类型”，但我们认为更准确的说法是访问类型——换言之就是MySQL决定如何查找表中的行。下面是最重要的访问方法，依次从最差到最优。

ALL

人们所称的全表扫描，通常意味着MySQL必须扫描整张表，从头到尾，去找到需要的行。（这里也有个例外，例如在查询里使用了LIMIT，或者在Extra列中显示“Using distinct/not exists”。）

index

这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行，开销将会非常大。

如果在Extra列中看到“Using index”，说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销要少很多。

range

范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是带有BETWEEN或在WHERE子句里带有>的查询。

当MySQL使用索引去查找一系列值时，例如IN()和OR列表，也会显示为范围扫描。然而，这两者其实是相当不同的访问类型，在性能上有重要的差异。更多信息可以查看第5章的文章“什么是范围条件”。

此类扫描的开销跟索引类型相当。

ref

这是一种索引访问（有时也叫做索引查找），它返回所有匹配某个单个值的行。然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。ref_or_null是ref之上的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目。

eq_ref

使用这种索引查找，MySQL知道最多只返回一条符合条件的记录。这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到，它会将它们与某个参考值做比较。MySQL对于这类访问类型的优化做得非常好，因为它知道无须估计匹配行的范围或在找到匹配行后再继续查找。

const, system

当MySQL能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型。举例来说，如果你通过将某一行的主键放入WHERE子句里的方式来选取此行的主键，MySQL 就能把这个查询转换为一个常量。然后就可以高效地将表从联接执行中移除。

NULL

这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表。

possibIe_keys列

这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。

key列

这一列显示了MySQL 决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys列中，那么MySQL选用它是出于另外的原因——例如，它可能选择了一个覆盖索引，哪怕没有WHERE子句。

换句话说，possible_keys揭示了哪一个索引能有助于高效地行查找，而key显示的是优化采用哪一个索引可以最小化查询成本（更多详情请参阅第6章中关于优化的成本度量值）。下面就是一个例子。

id: 1

select_type: SIMPLE

table: film_actor

type: index

possible_keys: NULL

key: idx_fk_film_id

key_len: 2

ref: NULL

rows: 5143

Extra: Using index

key_len列

该列显示了MySQL在索引里使用的字节数。如果MySQL正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列。要记住，MySQL 5.5及之前版本只能使用索引的最左前缀。举例来说，sakila.film_actor的主键是两个SMALLINT列，并且每个SMALLINT列是两字节，那么索引中的每项是4字节。以下就是一个查询的示例：

基于结果中的key_len列，可以推断出查询使用唯一的首列——actor_id列，来执行索引查找。当我们计算列的使用情况时，务必把字符列中的字符集也考虑进去。

这个查询中平均长度为13字节，即为a列和b列的总长度。a列是3个字符，utf8下每一个最多为3字节，而b列是一个4字节整型。

MySQL并不总显示一个索引真正使用了多少。例如，如果对一个前缀模式匹配执行LIKE查询，它会显示列的完全宽度正在被使用。key_len列显示了在索引字段中可能的最大长度，而不是表中数据使用的实际字节数。在前面例子中MySQL总是显示13字节，即使a列恰巧只包含一个字符长度。换言之， key_len通过查找表的定义而被计算出，而不是表中的数据。

ref列

这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。下面是一个展示关联条件和别名组合的例子。注意，ref列反映了在查询文本中film表是如何以f为别名的。

rows列

这一列是MySQL估计为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的循环数目。也就是说它不是MySQL认为它最终要从表里读取出来的行数，而是MySQL为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数。（这个标准包括SQL里给定的条件，以及来自联接次序上前一个表的当前列。）

fiItered列

这一列是在MySQL 5.1里新加进去的，在使用EXPLAIN EXTENDED时出现。它显示的是针对表里符合某个条件（WHERE子句或联接条件）的记录数的百分比所做的一个悲观估算。如果你把rows列和这个百分比相乘，就能看到MySQL估算它将和查询计划里前一个表关联的行数。在写作本书的时候，优化器只有在使用ALL、index、range和index_merge访问方法时才会用这一估算。

Extra列

这一列包含的是不适合在其他列显示的额外信息。MySQL用户手册里记录了大多数可以在这里出现的值。其中许多在本书中已经提到过。

常见的最重要的值如下。“Using index”

此值表示MySQL将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。“Using where”

这意味着MySQL服务器将在存储引擎检索行后再进行过滤。许多WHERE条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带WHERE子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。“Using temporary”

这意味着MySQL在对查询结果排序时会使用一个临时表。“Using filesort”

这意味着MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。MySQL有两种文件排序算法，你可以在第6章读到相关内容。两种方式都可以在内存或磁盘上完成。EXPLAIN不会告诉你MySQL将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。“Range checked for each record (index map: N)”

这个值意味着没有好用的索引，新的索引将在联接的每一行上重新估算。N是显示在possible_keys列中素引的位图，并且是冗余的。

MySQL服务器本身使用了几种类型的锁。如果查询正在等待一个服务器级别的锁，那么可以在SHOW PROCESSLIST的输出中看到蛛丝马迹。除了服务器级别的锁，任何支持行级别锁的存储引擎，例如InnoDB，都实现了自己的锁。在MySQL 5.0和更早版本中，服务器层无法主动识别这些锁，它们往往对用户和数据库管理员不可见。在MySQL 5.1和后续版本中可见性有了提高。

锁等待可能发生在服务器级别或存储引擎级别。(1)（应用程序级别的锁可能也是一个问题，但我们在此只关注MySQL。）下面是MySQL服务器使用的几种类型的锁。

表锁

表可以被显式的读锁和写锁进行锁定。这些锁有许多的变种，例如本地读锁。你可以在MySQL手册LOCK TABLES部分了解到这些变种。除了这些显式的锁外，查询过程中还有隐式的锁。

全局锁

可以通过FLUSH TABLES WITH READ LOCK或设置read_only=1来获取单个全局读锁。它与任何表锁都冲突。

命名锁

命名锁是表锁的一种，服务器在重命名或删除一个表时创建。

字符锁

你可以用GET_LOCK()及其相关函数在服务器级别内锁住和释放任意一个字符串。

在接下来的章节中我们将更详细地查看每种类型的锁。

表锁

表锁既可以是显式的也可以是隐式的。显式的锁用LOCK TABLES创建。例如，如果在mysql会话中执行下列命令，将在sakila.film上获得一个显式的锁。

mysql8创建新用户和数据库并授权

```

create database 数据库名 character set utf8;

create user '用户名'@'%' identified with mysql_native_password by '密码';

create user '用户名'@'localhost' identified with mysql_native_password by '密码';

grant all privileges on 数据库名前缀.* to '用户名'@'%';

```

农历

五行

冲煞

彭祖

方位

宜

忌

吉神

凶神

极简任务管理 help

+ 0 0 0

Task Idea Collect