(9)MySQL进阶篇SQL优化(InnoDB锁记录锁)

database

1.概述

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则InnoDB将使用表锁!在实际应用程序中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。

2. InnoDB行锁实现方式

2.1InnoDB存储引擎的表在不使用索引时使用表锁例子

创建一个临时表:

MySQL [(none)]> CREATE TABLE goods.tab_no_index (ID INT,Name VARCHAR(50));

Query OK, 0 rows affected (0.02 sec)

插入三条测试数据:

MySQL [(none)]> INSERT INTO goods.tab_no_index (ID,`Name`) VALUES (1,"1"),(2,"2"),(3,"3");

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

session_1

session_2

1)先设置事务T1提交类型为事务非自动提交。

1先设置事务T2提交类型为事务非自动提交。

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

2在当前事务T1中查询tab_no_index表的数据行ID=1数据。

2在当前事务T2中查询tab_no_index表的数据行ID=2数据。

MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=1;

+------+------+

| ID | Name |

+------+------+

| 1 | 1 |

+------+------+

1 row inset (0.00 sec)

MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=2;

+------+------+

| ID | Name |

+------+------+

| 2 | 2 |

+------+------+

1 row inset (0.00 sec)

3在当前事务T1中为tab_no_index表的数据行ID=1加上排他锁。

 

MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=1 FOR UPDATE;

+------+------+

| ID | Name |

+------+------+

| 1 | 1 |

+------+------+

1 row inset (0.00 sec)

 

3在当前事务T2中为tab_no_index表的数据行ID=2加上排他锁,会发生阻塞超时。

MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=2 FOR UPDATE;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

从上述表格的例子来看,session_1只给ID=1数据行加了排他锁,但session_2在请求ID=2的数据行排他锁时,却出现了锁等待!

2.2InnoDB存储引擎的表在使用索引时使用行锁例子

创建一个临时表:

MySQL [(none)]> CREATE TABLE goods.tab_with_index (ID INT,Name VARCHAR(50));

Query OK, 0 rows affected (0.02 sec)

建立临时表ID列索引:

MySQL [(none)]> ALTER TABLE goods.tab_with_index ADD INDEX Index_ID(ID);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

插入三条测试数据:

MySQL [(none)]> INSERT INTO goods.tab_with_index (ID,`Name`) VALUES (1,"1"),(2,"2"),(3,"3");

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

session_1

session_2

1先设置事务T1提交类型为事务非自动提交。

1先设置事务T2提交类型为事务非自动提交。

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

2在当前事务T1中查询tab_with_index表的数据行ID=1数据。

2在当前事务T2中查询tab_with_index表的数据行ID=2数据。

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1;

+------+------+

| ID | Name |

+------+------+

| 1 | 1 |

+------+------+

1 row inset (0.00 sec)

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=2;

+------+------+

| ID | Name |

+------+------+

| 2 | 2 |

+------+------+

1 row inset (0.00 sec)

3在当前事务T1中为tab_with_index表的数据行ID=1加上排他锁。

 

 

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 FOR UPDATE;

+------+------+

| ID | Name |

+------+------+

| 1 | 1 |

+------+------+

1 row inset (0.00 sec)

 

 

3在当前事务T2中为tab_with_index表的数据行ID=2加上排他锁,却并没有发生阻塞超时。

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=2 FOR UPDATE;

+------+------+

| ID | Name |

+------+------+

| 2 | 2 |

+------+------+

1 row inset (0.00 sec)

该示例同样跟2小节示例一样,只是ID列加了索引,而session_2在请求ID=2的数据行却没有阻塞!

2.3小结

通过以上两个示例可以了解到:
●在ID列没有建立索引的情况下,InnoDB没有使用到行锁,而是使用到表锁。
●在ID列建立索引的情况下,InnoDB使用到行锁,而是没有使用到表锁。
也就是说,InnoDB存储引擎的表列如果在没有加索引情况下查询,使用到是表锁而不是行锁,会产生阻塞情况,这在并发情况下是灾难的。

4.记录锁

由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同记录行,但是如果是使用相同的索引键,是会出现锁冲突的。下面我们通过两个示例来了解下。

4.1InnoDB存储引擎使用相同索引键的阻塞例子

这个示例还是沿用tab_with_index表做演示,ID是非聚集索引列,Name列没有索引,有以下数据:

MySQL [(none)]> SELECT * FROM goods.tab_with_index;

+------+------+

| ID | Name |

+------+------+

| 1 | 1 |

| 1 | 2 |

| 3 | 3 |

+------+------+

3 rows inset (0.00 sec)

session_1

session_2

1先设置事务T1提交类型为事务非自动提交。

1先设置事务T2提交类型为事务非自动提交。

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

2在当前事务T1中为tab_with_index表的数据行ID=1 AND `Name`="1" 加上排他锁。

 

 

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1
AND `Name`="1" FOR UPDATE;

+------+------+

| ID | Name |

+------+------+

| 1 | 1 |

+------+------+

1 row inset (0.00 sec)

 

 

2在当前事务T2中为tab_with_index表的数据行ID=1 AND `Name`="2" 加上排他锁,发生阻塞超时。

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1
AND `Name`="2" FOR UPDATE;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

3在当前事务T2中为tab_with_index表插入以下一行数据,并没有发生阻塞超时。

MySQL [(none)]> INSERT INTO goods.tab_with_index (ID,`Name`) VALUES (4,"4");

Query OK, 1 row affected (0.00 sec)

从上面示例可以看到,当session_1锁定事务中ID=1记录行时,会阻止session_2事务获取该记录行,而当插入一条ID=4数据时,却没有发生阻塞,成功插入!也就是说当索引数据加上记录锁时,会阻止其他事务对该表该行数据(例如ID=1记录行)进行插入,更新和删除操作。

4.2InnoDB存储引擎的表使用不同索引的阻塞例子

这个示例还是沿用tab_with_index表做演示,ID为主键索引列,Name为非聚集索引列,有以下数据:

MySQL [(none)]> SELECT * FROM goods.tab_with_index;

+----+------+

| ID | Name |

+----+------+

| 1 | 1 |

| 2 | 2 |

+----+------+

2 rows inset (0.00 sec)

session_1

session_2

1先设置事务T1提交类型为事务非自动提交。

1先设置事务T2提交类型为事务非自动提交。

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

2在当前事务T1中为tab_with_index表的数据行ID=1 加上排他锁。

 

 

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 FOR UPDATE;

+----+------+

| ID | Name |

+----+------+

| 1 | 1 |

+----+------+

1 row inset (0.00 sec)

 

 

2由于tab_with_index表ID=1的记录行在session_1事务中被锁定,当在session_2事务查询Name=2’记录行时,因为该记录行并不属于ID=1范围记录行之中,所以可以获得tab_with_index表的锁。

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE `Name`="2" FOR UPDATE;

+----+------+

| ID | Name |

+----+------+

| 2 | 2 |

+----+------+

1 row inset (0.00 sec)

 

 

3同理,由于访问的Name=1’记录已经被 session_1事务中被锁定,所以只能等待获得tab_with_index表的锁。

MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE `Name`="1" FOR UPDATE;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。

5.总结

其实在现实生产环境,即便在条件中使用了索引字段,但是决定是否使用索引来检索数据记录行是由MySQL执行计划来决定的,所以如果MySQL认为全表扫描效率更高,那么它就会优先执行全表扫描操作。比如一些很小的表,哪怕您在条件中使用了索引列,它也不会使用索引,这种情况下InnoDB将会使用表锁,而不是使用行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。还有一种情况在之前索引章节也有说过,如果检索值的数据类型与索引字段值不同,虽然MySQL能够进行数据类型转换,但是却不会使用索引,从而导致InnoDB使用表锁。例如tab_with_index表的name字段有索引,但是name字段是varchar类型的,如果where条件中值是int等值类型,那么就不是和varchar类型进行比较,而会对name进行类型转换,从而要不全表或遍历索引树扫描获取记录行,如下面语句:

-- 全表或遍历索引树扫描

EXPLAIN SELECT * FROM goods.tab_with_index WHERE `Name`=1;

-- 走索引扫描

EXPLAIN SELECT * FROM goods.tab_with_index WHERE `Name`="1";


参考文献:
深入浅出MySQL大全

以上是 (9)MySQL进阶篇SQL优化(InnoDB锁记录锁) 的全部内容, 来源链接: utcz.com/z/535603.html

回到顶部