MySQL查询优化系列讲座之调度和锁定[MySQL防范]

前面的部份主如果聚焦于若何让单独的查询履行的速度更快.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语句的影响.

[1] [2] [3] 下一页  

利用耽误插入操作

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语句来整理该数据表的碎片.

上一页  [1] [2] [3] 下一页  

锁的层次和并发性

　　前面谈论的调度疗养符答应你改变默许的调度战略.此中的大部份内容都是介绍利用这些疗养符来办理数据表层次（table-level）的锁惹起的问题,这都是MyISAM和MEMORY存储引擎用来管理数据表争用的问题的.

　　BDB和InnoDB存储引擎实现了差别层次的锁,所以其性能特点和对争用的管理是差别的.BDB引擎利用页面层次（page-level）的锁.InnoDB引擎利用数据行层次（row-level）的锁,但是只在必要的时刻利用（在很多情形下,比方当读取操作都完成的时刻,InnoDB大概根本就不利用锁）.

　　存储引擎利用的锁的层次对客户端的并发操作有很大的影响.假定两个客户端都但愿更新某个数据表中的一行.由于要履行更新,每个客户端都需求一个写入锁.关于MyISAM数据表,引擎会为第一个客户端分配一个锁,这会惹起第二个客户端阻塞,直到第一个客户端完成操作.关于BDB数据表,它可以实现更大的并发性:两个更新操作会同步举行,除非两个数据行都位于同一个页面中.在InnoDB数据表中,并发性更高;只要两个客户端没有更新同一行,两个更新操作就可以同时发生.

　　普通的法则是,锁的层次越渺小,并发性越好,因为只要客户端利用数据表的部份差别,那么利用表的客户端便可以更多.它实际表示着差别的存储引擎合适于差别的语句混合（mixes）:

　　· MyISAM检索的速度非常快.但是利用表层次的锁大概成为混合的检索和更新环境中的问题,分外是检索偏向于长时间运行的时刻.在这些条件下,更新大概需求等候好久才能举行.

　　· 当更新操作很多的时刻,BDB和InnoDB数据表可以供应更好的性能.由于锁在页面或数据行层次举行,表被锁定的范围较小.这会削减锁的争用,提高并发性.

　　在避免死锁（deadlock）方面,表层次的锁比渺小层次的锁更有上风.利用表层次的锁的时刻,死锁不会发生.服务器可以通过查看语句来检测需求的数据表,并提早锁定它们.而InnoDB和BDB数据表会发死活锁,因为这些存储引擎没有在事件开始的时刻分配全部必要的锁.作为替换,在事件处理的历程中,当检测到需求锁的时刻才分配.这便大概呈现两个语句获得了锁,接着试图进一步获得锁（需求多个锁）,但是这些锁却被对方保持着,等候对方释放.后来果是每个客户端都拥有一个锁,同时还需求操纵别的的客户端拥有的锁才能持续履行.这会招致死锁,服务器必须终止此中一个事件.

上一页  [1] [2] [3]