SQL优化【锁优化】

image.png

MySQL使用锁管理对表内容的争用 :

内部锁定在MySQL服务器内部执行,以管理多个线程对表内容的争用。
这种类型的锁定是内部的,因为它完全由服务器执行,并且不涉及其他程序。
参见 第8.11.1节“内部锁定方法”。

当服务器和其他程序锁定MyISAM表文件以相互协调哪个程序可以访问表时,发生外部锁定。
参见第8.11.5节“外部锁定”。

8.11.1内部锁定方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
本节讨论内部锁定; 即在MySQL服务器本身内执行锁定以管理多个会话对表内容的争用。
这种类型的锁定是内部的,因为它完全由服务器执行,并且不涉及其他程序。
对于其他程序在MySQL文件上执行的锁定,请参见第8.11.5节“外部锁定”。


行级锁定

表级锁定

选择锁定类型

行级锁定
MySQL使用行级锁定为InnoDB表,以支持由多个会话并发写入权限,使其适用于多用户,高并发,和OLTP应用程序。


为了避免在单个表上执行多个并发写入操作时发生死锁, InnoDB通过SELECT ... FOR UPDATE为每组预计要修改的行发布语句,即使数据更改语句稍后在事务中发生,也可以在事务启动时获取必要的锁。
如果交易修改或锁定多个表,请在每个交易中以相同的顺序发布适用的报表。
死锁会影响性能而不是表示严重的错误,因为它会 InnoDB自动 检测 死锁条件并回滚其中一个受影响的事务。


在高并发系统上,当大量线程等待相同的锁时,死锁检测会导致速度下降。
有时候,innodb_lock_wait_timeout 当死锁发生时,禁用死锁检测并依赖事务回滚的设置可能更有效 。
使用innodb_deadlock_detect 配置选项可以禁用死锁检测 。


行级锁定的优点:

当不同的会话访问不同的行时,更少的锁冲突。


回滚更少。


可能长时间锁定一行。


表级锁定
MySQL使用表级锁的MyISAM, MEMORY和MERGE 表,只允许一个会话更新一次这些表。
这种锁定级别使得这些存储引擎更适合于只读,主要读取或单用户应用程序。


这些存储引擎通过始终在查询开始时一次请求所有需要的锁,并始终以相同的顺序锁定表来避免 死锁。
权衡是这种策略降低了并发性; 其他要修改表的会话必须等到当前数据更改语句结束。


表级锁定的优点:

所需的内存相对较少(行锁定需要锁定每行或一组行的内存)

在大部分表格上使用时都很快,因为只涉及一个锁。


如果您经常GROUP BY 对大部分数据执行操作,或者必须频繁扫描整个表,则速度很快。

{———-}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
MySQL授予表写入锁定如下:

如果表上没有锁,请在其上写入锁。


否则,将锁定请求放入写入锁定队列中。


MySQL授予表读取锁定如下:

如果表上没有写入锁,请在其上放置一个读取锁。


否则,将锁定请求放入读锁定队列中。


表格更新优先于表格检索。
因此,当释放一个锁时,该锁可用于写入锁定队列中的请求,然后可用于读取锁定队列中的请求。
这确保了即使桌子上有大量活动时,表格的更新也不会“ 饿死 ”SELECT。
但是,如果有多个表的更新,则 SELECT语句会等待,直到没有更新。


有关更改读取和写入优先级的信息,请参见第8.11.2节“表锁定问题”。


您可以通过检查Table_locks_immediate和 Table_locks_waited状态变量来分析系统上的表锁争用 ,这些变量分别表示可以立即授予表锁请求的次数和需要等待的数量:

MySQL的> SHOW STATUS LIKE 'Table%';
+ ----------------------- + --------- +
| 变量名| 值|
+ ----------------------- + --------- +
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+ ----------------------- + --------- +
性能模式锁定表还提供锁定信息。
请参见 第25.11.12节“性能架构锁表”。


该MyISAM存储引擎支持并发插入,减少读者和作者之间的竞争给定表:如果一个MyISAM 表有数据文件的中间没有空闲块,行总是在数据文件的末尾插入。
在这种情况下,您可以自由地将一个表的并发INSERT和SELECT语句 混合使用 , MyISAM而无需锁定。
也就是说,你可以插入行到一个MyISAM表同时其他客户正在阅读它。
在表中间删除或更新的行可能会产生空洞。
如果存在孔,则同时插入将被禁用,但当所有孔已填充新数据时将自动再次启用插入。
要控制此行为,请使用 concurrent_insert系统变量。
请参见第8.11.3节“并发插入”。


如果您显式获取表锁 LOCK TABLES,您可以请求 READ LOCAL锁而不是 READ锁,以使其他会话在表锁定的情况下执行并发插入。


执行许多INSERT和 SELECT操作上的表 t1时并发的插入是不可能的,你可以插入行到一个临时表 temp_t1,并从临时表中的行更新真正的表:

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql>UNLOCK TABLES;
选择锁定类型
通常,在以下情况下,表锁优于行级锁:

该表的大多数语句都是读取。


该表的声明是读取和写入的混合,其中写入是对单个行的更新或删除,可以通过读取一个密钥来读取:

UPDATE tbl_nameSET column= valueWHERE unique_key_col= key_value;
DELETE FROM tbl_nameWHERE unique_key_col= key_value;
SELECT结合并发INSERT 语句,并且很少 UPDATE或者 DELETE语句。


许多扫描或GROUP BY整个表上的操作,没有任何作家。


对于更高级别的锁定,您可以通过支持不同类型的锁定来更轻松地调整应用程序,因为锁定开销低于行级锁定。


行级锁定以外的选项:

版本控制(例如在MySQL中用于并发插入的版本),可以在多个读者的同一时间拥有一个作者。
这意味着根据访问何时开始,数据库或表支持不同数据视图。
这个其他常见的术语是 “ 时间旅行, ” “ 上写副本, ” 或“ 按需复制。


在许多情况下,按需复制优于行级锁定。
但是,在最坏的情况下,它可以使用比使用普通锁更多的内存。


而不是使用行级锁,你可以使用应用程序级锁,如提供的 GET_LOCK()和 RELEASE_LOCK()在MySQL。
这些是咨询锁,因此它们只能与相互合作的应用程序一起工作。
参见 第12.20节“其他功能”。

8.11.2表锁定问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
InnoDB表使用行级锁定,以便多个会话和应用程序可以同时读取和写入同一个表,而不会让对方等待或产生不一致的结果。
对于这个存储引擎,避免使用该LOCK TABLES语句,因为它没有提供任何额外的保护,而是降低了并发性。
自动行级锁定使得这些表适合于最繁忙的数据库以及最重要的数据,同时还可以简化应用程序逻辑,因为您不需要锁定和解锁表。
因此, InnoDB存储引擎是MySQL中的默认设置。


MySQL除了为所有存储引擎使用表锁(而不是页锁,行锁或列锁) InnoDB。
锁定操作本身没有太多的开销。
但是因为只有一个会话可以同时写入一个表,为了获得这些其他存储引擎的最佳性能,请将它们主要用于经常查询且很少插入或更新的表。


性能考虑因素支持InnoDB

锁定性能问题的解决方法

性能考虑因素支持InnoDB
在选择是使用InnoDB或不同的存储引擎创建表时 ,请记住表锁定的以下缺点:

表锁定使许多会话可以同时从表中读取数据,但是如果会话要写入表中,它必须首先获得独占访问权,这意味着它可能必须先等待其他会话完成表。
在更新期间,想要访问此特定表的所有其他会话必须等到更新完成。


当会话正在等待时,表锁定会导致问题,因为磁盘已满并且在会话可继续之前需要有空闲空间。
在这种情况下,所有希望访问问题表的会话也会处于等待状态,直到有更多磁盘空间可用。


一个SELECT是需要长时间运行的语句防止其他会话更新表的同时,使其他场次出现缓慢或无响应。
虽然会话正在等待独占访问表以进行更新,但发出SELECT语句的其他会话 将排在其后面,即使对于只读会话也会降低并发性。


锁定性能问题的解决方法
以下各项介绍了一些避免或减少表锁定引起的争用的方法:

考虑将表切换到 InnoDB存储引擎,或者 CREATE TABLE ... ENGINE=INNODB在安装期间使用,或者使用ALTER TABLE ... ENGINE=INNODB现有的表。
有关此存储引擎的更多详细信息,请参见 第14章,InnoDB存储引擎。


优化SELECT语句以加快运行速度,以便锁定表的时间更短。
您可能需要创建一些汇总表来执行此操作。


启动mysqld的使用 --low-priority-updates。
对于仅使用表级锁(如存储引擎 MyISAM,MEMORY和 MERGE),这给所有的语句更新(修改),比表低优先级 SELECT的语句。
在这种情况下,SELECT 前面的场景中的第二个语句将在UPDATE语句之前执行,并且不会等待第一个语句 SELECT完成。


要指定在特定连接中发出的所有更新应该以低优先级完成,请将low_priority_updates 服务器系统变量设置 为1。


要给出特定的INSERT, UPDATE或 DELETE语句较低的优先级,请使用该LOW_PRIORITY 属性。


要给出特定的SELECT 声明更高的优先级,请使用该 HIGH_PRIORITY属性。
请参见 第13.2.9节“SELECT语法”。


以 系统变量的较低值 启动mysqld, max_write_lock_count以强制MySQL临时提升SELECT 在发生特定数量的表插入后等待表的所有语句的优先级。
这允许 READ在一定数量的锁定之后进行 WRITE锁定。


如果你有问题 INSERT联合 SELECT,可考虑改用MyISAM表,它支持并发SELECT和 INSERT报表。
(参见 第8.11.3节“并发插入”)。


如果你有问题,混合 SELECT和 DELETE报表,该 LIMIT选项 DELETE可能会有帮助。
请参见 第13.2.2节“删除语法”。


使用SQL_BUFFER_RESULTwith SELECT语句可以帮助缩短表锁的持续时间。
请参见 第13.2.9节“SELECT语法”。


通过允许查询针对一个表中的列运行,而将更新限制在不同表中的列中,将表内容拆分为单独的表格可能会有所帮助。


您可以将锁定代码更改 mysys/thr_lock.c为使用单个队列。
在这种情况下,写入锁和读取锁具有相同的优先级,这可能有助于某些应用程序。

8.11.3并发插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
该MyISAM存储引擎支持并发插入,减少读者和作者之间的竞争给定表:如果一个MyISAM表已经在数据文件中没有孔(中删除的行),一个 INSERT语句可以执行行添加到表的末尾同时 SELECT语句正在读取表格中的行。
如果有多个 INSERT语句,它们将按照顺序排列并执行,并与SELECT语句同时执行 。
并发的结果INSERT可能不会立即显示。


所述concurrent_insert系统变量可以被设置为修改并发插入处理。
默认情况下,该变量设置为AUTO(或1),并按前面所述处理并发插入。
如果 concurrent_insert设置为 NEVER(或0),则禁用并发插入。
如果变量设置为ALWAYS (或2),即使对于已删除行的表,也允许在表的末尾进行并发插入。
另请参阅concurrent_insert系统变量的说明。


如果您正在使用二进制日志,并发插入将转换为正常的插入CREATE ... SELECT或 INSERT ... SELECT语句。
这样做是为了确保您可以通过在备份操作期间应用日志来重新创建表的精确副本。
请参见第5.4.4节“二进制日志”。
另外,对于这些语句,在选定表上放置一个读锁,以便阻止插入到该表中。
结果是该表的并发插入必须等待。


有了LOAD DATA INFILE,如果你指定CONCURRENT 一个MyISAM满足并发插入的状态表(即,它包含在中间没有空闲块),其他会话可以从表中检索数据时LOAD DATA正在执行。
即使没有其他会话同时使用该表,该CONCURRENT选项的使用LOAD DATA也会影响一个位的性能。


如果指定HIGH_PRIORITY,则会覆盖该--low-priority-updates选项的效果( 如果服务器是使用该选项启动的)。
这也会导致并发插入不被使用。


为LOCK TABLE,之间的差READ LOCAL并且READ是 READ LOCAL允许非冲突性的 INSERT语句(并发插入),而锁被保持来执行。
但是,如果要在持有锁的同时使用服务器外部的进程来操作数据库,则无法使用此功能。

8.11.4元数据锁定

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
MySQL使用元数据锁定来管理对数据库对象的并发访问并确保数据一致性。
元数据锁定不仅适用于表格,还适用于架构,存储的程序(过程,函数,触发器和预定事件)以及表空间。


元数据锁定确实涉及一些开销,随着查询量的增加而增加。
元数据争用增加了多个查询尝试访问相同对象的次数越多。


元数据锁定不是表格定义缓存的替代,其互斥锁和锁定与互斥锁不同 LOCK_open。
以下讨论提供了有关元数据锁定工作原理的一些信息。


为确保事务可序列化,服务器不得允许一个会话在另一个会话中未完成显式或隐式启动事务中使用的表上执行数据定义语言(DDL)语句。
服务器通过获取事务内使用的表上的元数据锁,并推迟释放这些锁,直到事务结束为止。
表上的元数据锁可以防止对表的结构进行更改。
这种锁定方式意味着一个会话中的事务正在使用的表不能在其他会话中用于DDL语句,直到事务结束。


这个原则不仅适用于事务表,也适用于非事务表。
假设一个会话开始一个使用事务表t 和非事务表的事务nt,如下所示:

开始交易;
SELECT * FROM t;
SELECT * FROM nt;
服务器在两端都保存了元数据锁t, nt直到事务结束。
如果另一个会话尝试对任一表执行DDL或写入锁定操作,则会阻塞,直到事务结束时释放元数据锁。
例如,如果第二个会话尝试执行以下任何操作,则会阻止:

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;
The相同的行为适用于The LOCK TABLES ... READ。
也就是说,更新任何表(事务性或非事务性)的显式或隐式启动事务将被阻塞并被LOCK TABLES ... READ该表阻塞。


如果服务器为语句有效但在执行期间失败的语句获取元数据锁,则它不会提前释放锁。
锁释放仍然延迟到事务结束,因为失败的语句被写入二进制日志,并且锁保护日志一致性。


在自动提交模式下,每条语句实际上是一个完整的事务,因此为语句获取的元数据锁只保留在语句的末尾。


在PREPARE准备好声明后,即使在多语句事务中进行准备,也会释放在声明中获取的元数据锁定 。


8.11.5外部锁定
外部锁定是使用文件系统锁定来管理MyISAM多个进程对数据库表的争用。
外部锁定用于单个进程(如MySQL服务器)不能被认为是需要访问表的唯一进程的情况。
这里有些例子:

如果运行多个使用相同数据库目录的服务器(不推荐),则每台服务器都必须启用外部锁定。


如果使用myisamchk在表上执行表维护操作 MyISAM,则必须确保服务器未运行,或者服务器启用了外部锁定,以便根据需要锁定表文件以与myisamchk协调 访问表。
使用myisampack打包 MyISAM表格也是如此 。


如果服务器在启用外部锁定的情况下运行,则可以随时使用myisamchk进行读取操作,例如检查表。
在这种情况下,如果服务器尝试更新myisamchk正在使用的表, 服务器将在继续之前等待myisamchk完成。


如果使用myisamchk进行写入操作(如修复或优化表),或者如果使用 myisampack打包表,则 必须始终确保 mysqld服务器不使用表。
如果您不停止mysqld,至少 在运行myisamchk之前执行 mysqladmin flush-tables。
如果服务器和 myisamchk同时访问表,您的表可能会损坏。


在外部锁定生效的情况下,每个需要访问表的进程在继续访问表之前都会获取表文件的文件系统锁。
如果无法获取所有必需的锁,则会阻止进程访问表,直到获得锁(在当前持有锁的进程释放它们之后)。


外部锁定会影响服务器性能,因为服务器在访问表之前必须等待其他进程。


如果您运行单个服务器来访问给定的数据目录(通常情况下),并且在服务器运行时没有其他程序(如myisamchk)需要修改表,则不需要外部锁定。
如果只 使用其他程序读取表,则不需要外部锁定,但 如果服务器在myisamchk正在读取表格时更改表,myisamchk可能会报告警告 。


在禁用外部锁定的情况下,要使用 myisamchk,必须在执行myisamchk时停止服务器,否则在运行myisamchk之前锁定并刷新表。
(请参见第8.12.1节“系统因素”。
)为避免此要求,请使用CHECK TABLE 和REPAIR TABLE语句来检查和修复MyISAM表格。


对于mysqld,外部锁定由skip_external_locking系统变量的值控制 。
当这个变量被启用时,外部锁定被禁用,反之亦然。
外部锁定默认是禁用的。


使用--external-locking或 --skip-external-locking 选项可以在服务器启动时控制使用外部锁定。


如果确实使用外部锁定选项来启用对MyISAM来自多个MySQL进程的表的更新 ,则必须确保满足以下条件:

不要将查询缓存用于使用另一个进程更新的表的查询。


不要使用该--delay-key-write=ALL选项启动服务器,也不要 使用DELAY_KEY_WRITE=1任何共享表的表选项。
否则,可能会发生索引损坏。


满足这些条件的最简单方法是始终 --external-locking与--delay-key-write=OFF和 一起使用 --query-cache-size=0。
(这不是默认完成的,因为在许多设置中,混合使用上述选项很有用。)