锁定与等待

锁机制是影响查询性能的另一个重要因素。当有多个用户并发访问数据库中某一资源的时候，为了保证并发访问的一致性，数据库必须通过锁机制来协调这些访问。

我们可以认为查询的时间开销主要包括两部分，即查询本身的计算时间和查询开始前的等待时间，所以说索引影响的是前者，而锁机制影响的是后者。

 

减少表锁定等待

MySQL 为 MyISAM 类型表提供了表级别的锁定。MyISAM 的表锁定可以允许多个线程同时读取数据，比如 select 查询，它们之间是不需要锁等待的。但是对于更新操作（如 update 操作），它会排斥对当前表的所有其它查询，包括 select 查询。除此之外，更新操作有着默认的高优先级，这意味着当表锁释放后，更新操作将先获得锁定，然后才轮到读取操作。也就是说，如果有很多 update 操作排着长队，那么对当前表的 select 查询必须等到所有的更新都完成之后才能开始。

 

在一些特定的情况下，慢速查询对于整体性能的影响是非常严重的，举个例子。

 

CREATE TABLE `count_t` ( 
`id` INT NOT NULL AUTO_INCREMENT , 
`count` INT NOT NULL DEFAULT '0' , PRIMARY KEY (`id`)
) ENGINE = MyISAM;

 我们为这个表填充了10万行记录，其中 count 字段为 0 到 999 的随机整数。现在我们需要一个比较慢速的 update 操作，如下所示：

 

接下来，我们编写 PHP 程序用来执行这个 update 操作，然后用 ab 模拟 10 个并发用户来请求这个 PHP 程序，这使得刚才的 update 操作语句会被执行 10 次，目的是让这个数据表忙于长时间的 update 操作。

在启动 ab 后不久，我们马上在 MySQL 命令行中发起一个 select 查询，如下所示：

 

mysql> select * from count_t where id=1;

 

这个查询并没有返回结果，而像是陷入了沉思。这时我们在另一个 MySQL 命令行会话中监视所有的线程状态，因为我们想知道到底发生了什么。 show processlist 的结果如下所示：

 

 

我们看到除了 show processlist 本身的线程之外，还有几个 update 线程和 1 个 select 线程，其中只有一个 update 线程的状态为 Updating ，而其它的几个 update 线程 和 1个 select 线程都处于 Locked 状态，这意味着它们正在等待表锁的释放、

终于，select 查询结束了，结果如下所示：

 

 

行锁定带来了什么

如果你的站点主要依靠用户创造内容，那么频繁的数据更新在所难免，它将会给 select 等读取操作带来很大的影响，这时候可以使用行锁来解决问题，MySQL 在 Innodb 类型表中提供了行锁的支持，将刚才的 count_t 表转为 Innodb 类型。

 

 我们同样用 ab 模拟 10 个并发用户来执行 update 操作，同时我们监视 MySQL 线程，如下所示：

 

可以看到有多个 update 线程在执行，它们的状态都为 updating 。这时候我们在 MySQL 命令行中执行一个 select 查询，如下所示：

 

查询瞬间返回结果，用时 0.00 秒。即便还有多个 update 操作在执行，select 查询还是迅速地获得结果。update 和 select 来自不同的线程，并且针对不同的记录行，所以它们可以轻松地并发进行，这就是行锁真正带给我们的惊喜。

 

行锁定真的好吗？

 

我们再来模拟一个更加接近实际情况的场景，为此，我们用 PHP 编写了以下程序：

 

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "runoob";
 
// 创建链接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查链接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
} 

$sql = "UPDATE `count_t` SET `count`=`count`+1";
$conn->query($sql);

$conn->close();

 这段程序做了两件事情，首先它执行一次慢速的 update 操作，然后执行 10000 次快速的 select 查询。我们用 ab 模拟 20 个并发，每个用户请求一次程序，通过使用 MyISAM 和 Innodb 两种不同类型的锁定机制，我们来分别记录总执行时间。

首先是 MyISAM 类型表，测试结果的关键部分如下所示：

 

 

接下来是 Innodb 类型表，结果如下所示：

 
 使用 Innodb 的行锁定在这里并没有比 MyISAM 的表锁定表现得更好，反而还略有不如。在压力测试的时候，我们通过 show PROCESSLIST 来监视线程状态，当使用 MyISAM 类型表的时候，我们看到除了一个正在 Updating 的 update 线程之外，其它的 update 线程都在 Locked，而使用 Innodb 行锁定的时候，我们看到所有的 update 线程的状态都是 Updating，可为什么它的总时间还不如前者呢 ？

这里我们需要清楚的是，锁定只是一种逻辑层面的约束，即便是同时拥有 Updating 的状态，也不能加速这些 update 操作的总时间，因为磁盘的物理写操作最终还是依次进行的。