使用调度和锁定进行MySQL查询优化

DBA在日常工作中经常会遇到如何让单独的查询执行的速度更快。MySQL还允许你改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。这一部分讲解MySQL的默认的调度策略和可以用来影响这些策略的选项。它还谈到了并发性插入操作的使用和存储引擎锁定层次对客户端的并发性的影响。为了讨论的方便，我们把执行检索(SELECT)的客户端称为”读取者”，把执行修改操作(DELETE、INSERT、REPLACE或UPDATE)的客户端称为”写入者”。

MySQL的默认的调度策略可用总结如下：

◆写入操作优先于读取操作。

◆对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。

◆对某张数据表的多个读取操作可以同时地进行。

MyISAM和MEMORY存储引擎借助于数据表锁来实现这样的调度策略。当客户端访问某张表的时候，首先必须获取它的锁。当客户端完成对表的操作的时候，锁就会被解除。通过LOCK TABLES和UNLOCK TABLES语句来显式地获取或释放锁是可行的，但是在通常情况下，服务器的锁管理器会自动地在需要的时候获取锁，在不再需要的时候释放锁。获取的锁的类型依赖于客户端是写入还是读取操作。

对某张表进行写入操作的客户端必须拥有独占的(排他的)访问权的锁。操作在进行的过程中，该数据表处于不一致的(inconsistent)状态，因为数据记录在删除、添加或修改的时候，数据表上的索引也可能需要更新以相互匹配。这个数据表在变化的过程中，如果允许其它的客户端访问，会出现问题。非常明显，允许两个客户端同时写入一张数据表是不利的，因为这样的操作会很快使数据表中的信息成为一堆无用的垃圾。但是允许客户端读取变化之中的数据表也不好，因为正在读取的位置中的数据可能正在变化(修改)，读取的结果可能不是真实的。

对某张表执行读取操作的客户端必须获取一个锁，防止在读取的过程中，其它的客户端写入或改变表。但是这个锁不需要独占的访问权。读取操作不会改变数据，因此没有理由让某个读取者阻止其它的读取者访问这张表。因此读取锁允许其它的客户端在同一时刻读取这张表。

MySQL提供了几个语句调节符，允许你修改它的调度策略：

◆LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。

◆HIGH_PRIORITY关键字应用于SELECT和INSERT语句。

◆DELAYED关键字应用于INSERT和REPLACE语句。

LOW_PRIORITY和HIGH_PRIORITY调节符影响那些使用数据表锁的存储引擎(例如MyISAM和MEMORY)。DELAYED调节符作用于MyISAM和MEMORY数据表。

改变语句调度的优先级

LOW_PRIORITY关键字影响DELETE、INSERT、LOAD DATA、REPLACE和UPDATE语句的执行调度。通常情况下，某张数据表正在被读取的时候，如果有写入操作到达，那么写入者一直等待读取者完成操作(查询开始之后就不能中断，因此允许读取者完成操作)。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞(block)，因为默认的调度策略是写入者优先于读取者。当***个读取者完成操作的时候，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。

如果写入操作是一个LOW_PRIORITY(低优先级)请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。理论上，这种调度修改暗示着，可能存在LOW_PRIORITY写入操作永远被阻塞的情况。如果前面的读取操作在进行的过程中一直有其它的读取操作到达，那么新的请求都会插入到LOW_PRIORITY写入操作之前。

SELECT查询的HIGH_PRIORITY(高优先级)关键字也类似。它允许SELECT插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。另外一种影响是，高优先级的SELECT在正常的SELECT语句之前执行，因为这些语句会被写入操作阻塞。

如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么请使用–low-priority-updates选项来启动服务器。通过使用INSERT HIGH_PRIORITY来把INSERT语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。

#p#

使用延迟插入操作

DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候，服务器把数据行放入一个队列中，并立即给客户端返回一个状态信息，这样客户端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据表中读取数据，队列中的数据就会被保持着，直到没有读取者为止。接着服务器开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时，服务器还要检查是否有新的读取请求到达和等待。如果有，延迟数据行队列就被挂起，允许读取者继续操作。当没有读取者的时候，服务器再次开始插入延迟的数据行。这个过程一直进行，直到队列空了为止。

感觉上LOW_PRIORITY和DELAYED是相似的，两者都允许数据行插入操作被延迟，但是它们对客户端操作的影响却有很大的差异。LOW_ PRIORITY强迫客户端等待，直到那些数据行可以被插入数据表。DELAYED允许客户端继续操作，服务器在内存中缓冲那些数据行，直到自己有时间处理它们。

如果其它的客户端可能运行很长的SELECT语句并且你不希望阻塞，等待插入操作完成的时候，INSERT DELAYED就非常有用处了。客户端提交INSERT DELAYED的时候可能处理得很快，因为服务器只是简单地把要插入的数据行排队。

但是，你也必须知道正常的INSERT与INSERT DELAYED行为之间的一些其它的差异。如果INSERT DELAYED语句包含语法错误，客户端会得到一个错误，但是却无法得到其它一些在正常情况下可以使用的信息。例如，当语句返回的时候，你无法依赖(得到)AUTO_INCREMENT(自动增长)值。同样，你无法得到***索引的副本数量。发生这种情况的原因在于插入操作在真正地被执行之前已经返回了状态信息。另一种可能出现的情况是，由于INSERT DELAYED语句的数据行都在内存中排队，当服务器崩溃或者使用kill -9退出的时候，数据行可能丢失(正常情况下，kill -TERM终止命令不会导致这种情况，因为服务器在退出之前会把数据行插入表中)。

使用并发的插入操作

MyISAM存储引擎有一条例外的规则，它允许读取者阻塞写入者。这种现象发生在MyISAM数据表中间没有”空洞”(可能是删除或更新数据行的结果)的情况下。当数据表没有”空洞”的时候，任何INSERT语句必然在末尾而不是中部添加数据行。在这种情况下，MySQL允许其它客户端在读取数据的同时向数据表添加数据行。这就是”并发性插入操作”，因为它们同时发生，检索并没有被阻塞。

如果你希望使用并发性插入操作，请注意下面一些事项：

◆在INSERT语句中不要使用LOW_PRIORITY调节符。它会引起INSERT经常被读取者阻塞，因此阻碍了并发性插入操作的执行。

◆如果读取者需要显式地锁定数据表以执行并发性插入操作，就应该使用LOCK TABLES … READ LOCAL，而不是LOCK TABLES … READ。LOCAL关键字会获取一个锁，允许并发性操作继续进行，因为它只能应用于数据表中已有的数据行，不会阻塞那些添加到末尾的新数据行。

◆LOAD DATA操作应该使用CONCURRENT调节符，允许该数据表上的SELECT语句同时执行。

◆中间包含了”空洞”的MyISAM数据表不能使用并发性插入操作。但是，你可以使用OPTIMIZE TABLE语句来整理该数据表的碎片。

锁的层次和并发性

前面讨论的调度调节符允许你改变默认的调度策略。其中的大部分内容都是介绍使用这些调节符来解决数据表层次(table-level)的锁引起的问题，这都是MyISAM和MEMORY存储引擎用来管理数据表争用的问题的。

BDB和InnoDB存储引擎实现了不同层次的锁，所以其性能特征和对争用的管理是不同的。BDB引擎使用页面层次(page-level)的锁。InnoDB引擎使用数据行层次(row-level)的锁，但是只在必要的时候使用(在很多情况下，例如当读取操作都完成的时候，InnoDB可能根本就不使用锁)。

存储引擎使用的锁的层次对客户端的并发操作有很大的影响。假设两个客户端都希望更新某个数据表中的一行。由于要执行更新，每个客户端都需要一个写入锁。对于MyISAM数据表，引擎会为***个客户端分配一个锁，这会引起第二个客户端阻塞，直到***个客户端完成操作。对于BDB数据表，它可以实现更大的并发性：两个更新操作会同步进行，除非两个数据行都位于同一个页面中。在InnoDB数据表中，并发性更高;只要两个客户端没有更新同一行，两个更新操作就能同时发生。

一般的规则是，锁的层次越细微，并发性越好，因为只要客户端使用数据表的部分不同，那么使用表的客户端就可以更多。它实际暗示着不同的存储引擎适合于不同的语句混合(mixes)：

◆MyISAM检索的速度非常快。但是使用表层次的锁可能成为混合的检索和更新环境中的问题，特别是检索倾向于长时间运行的时候。在这些条件下，更新可能需要等待很久才能进行。

◆当更新操作很多的时候，BDB和InnoDB数据表可以提供更好的性能。由于锁在页面或数据行层次进行，表被锁定的范围较小。这会减少锁的争用，提高并发性。

在防止死锁(deadlock)方面，表层次的锁比细微层次的锁更有优势。使用表层次的锁的时候，死锁不会发生。服务器可以通过查看语句来检测需要的数据表，并提前锁定它们。而InnoDB和BDB数据表会发生死锁，因为这些存储引擎没有在事务开始的时候分配所有必要的锁。作为代替，在事务处理的过程中，当检测到需要锁的时候才分配。这就可能出现两个语句获取了锁，接着试图进一步获取锁(需要多个锁)，但是这些锁却被对方保持着，等待对方释放。其结果是每个客户端都拥有一个锁，同时还需要利用其它的客户端拥有的锁才能继续执行。这会导致死锁，服务器必须终止其中一个事务。

【编辑推荐】