三、低效率的索引导致mysql的行锁升级为表锁

最新推荐文章于 2024-06-02 11:55:15 发布

风zi

最新推荐文章于 2024-06-02 11:55:15 发布

阅读量977

点赞数 3

分类专栏：数据库问题

本文链接：https://blog.csdn.net/qq_35241080/article/details/88412133

版权

数据库问题专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一、建立一张测试表–student；

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(255) DEFAULT NULL,
  `stu_age` int(11) DEFAULT NULL,
  `stu_email` varchar(255) DEFAULT NULL,
  `stu_height` double DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--插入数据
BEGIN;
INSERT INTO `student` VALUES (1, '张三', 14, 'zhangsan@email', 180, 1);
INSERT INTO `student` VALUES (2, '李四', 15, 'lishi@', 152, 2);
INSERT INTO `student` VALUES (3, '王五', 17, 'wangwu@126.com', 177, 3);
INSERT INTO `student` VALUES (4, '赵六', 18, 'zhaoliu@126.com', 156, 4);
INSERT INTO `student` VALUES (5, '田七', 16, 'tianqi@126.com', 179, 2);
INSERT INTO `student` VALUES (6, '王八', 88, 'wangba@126.com', 133, 3);
INSERT INTO `student` VALUES (7, '刘九', 34, 'liujiu@163.com', 193, 3);
COMMIT;

二、关闭mysql自动提交事物

mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

三、在不创建索引的情况下测试，行锁是否基于索引实现


--查看数据
mysql> select * from student;
+----+----------+---------+-----------------+------------+----------+
| id | stu_name | stu_age | stu_email       | stu_height | class_id |
+----+----------+---------+-----------------+------------+----------+
|  1 | 张三     |      14 | zhangsan@email  |        180 |        1 |
|  2 | 李四     |      15 | lishi@          |        152 |        2 |
|  3 | 王五     |      17 | wangwu@126.com  |        177 |        3 |
|  4 | 赵六     |      18 | zhaoliu@126.com |        156 |        4 |
|  5 | 田七     |      16 | tianqi@126.com  |        179 |        2 |
|  6 | 王八     |      88 | wangba@126.com  |        133 |        3 |
|  7 | 刘九     |      34 | liujiu@163.com  |        193 |        3 |
+----+----------+---------+-----------------+------------+----------+

--此时是只有主键索引
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

3.2 打开两个mysql终端

3.2.1创建session1

--开启事物
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where class_id=2 for update;
+----+----------+---------+----------------+------------+----------+
| id | stu_name | stu_age | stu_email      | stu_height | class_id |
+----+----------+---------+----------------+------------+----------+
|  2 | 李四     |      15 | lishi@         |        152 |        2 |
|  5 | 田七     |      16 | tianqi@126.com |        179 |        2 |
+----+----------+---------+----------------+------------+----------+
2 rows in set (0.00 sec)

3.2.2创建session2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where class_id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
--此处可以查看到查询失败

可以得出结论：行锁是建立在索引字段的基础上，如果行锁定的列不是索引列则会升级为表锁，我这两个测试的查询条件有重复字段，其实用两个值来测试也是同样的效果。

四、创建索引的情况下进行测试

主要测试两个方面：一是索引列的数据重复率过高的情况下；二是索引列数据重复率过低。

为class_id创建索引

--创建索引
mysql> create index class_id_index on student(class_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看索引
mysql> show index from student;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY        |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | class_id_index |            1 | class_id    | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

索引重复过多的情况下-----查看当前数据状态，class_id重复数据过多

mysql> select * from student;
+----+----------+---------+-----------------+------------+----------+
| id | stu_name | stu_age | stu_email       | stu_height | class_id |
+----+----------+---------+-----------------+------------+----------+
|  1 | 张三     |      14 | zhangsan@email  |        180 |        1 |
|  2 | 李四     |      15 | lishi@          |        152 |        1 |
|  3 | 王五     |      17 | wangwu@126.com  |        177 |        1 |
|  4 | 赵六     |      18 | zhaoliu@126.com |        156 |        2 |
|  5 | 田七     |      16 | tianqi@126.com  |        179 |        2 |
|  6 | 王八     |      88 | wangba@126.com  |        133 |        2 |
|  7 | 刘九     |      34 | liujiu@163.com  |        193 |        2 |
+----+----------+---------+-----------------+------------+----------+

索引情况下创建session1

--开启事物
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
--正常查询
mysql> select * from student where class_id=1 for update;
+----+----------+---------+----------------+------------+----------+
| id | stu_name | stu_age | stu_email      | stu_height | class_id |
+----+----------+---------+----------------+------------+----------+
|  1 | 张三     |      14 | zhangsan@email |        180 |        1 |
|  2 | 李四     |      15 | lishi@         |        152 |        1 |
|  3 | 王五     |      17 | wangwu@126.com |        177 |        1 |
+----+----------+---------+----------------+------------+----------+
3 rows in set (0.00 sec)

索引查询情况下创建session2-----查询失败

--开启事物
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
--由于索引列的数据重复过多，导致索引失效，从而造成session1的行锁升级为表锁至sessino2的查询失败
mysql> select * from student where class_id=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结论：索引列数据重复过多情况下，会导致索引失效，行锁变表锁。

索引列数据值唯一的情况下进行测试

5.1 数据情况

mysql> select * from student;
+----+----------+---------+-----------------+------------+----------+
| id | stu_name | stu_age | stu_email       | stu_height | class_id |
+----+----------+---------+-----------------+------------+----------+
|  1 | 张三     |      14 | zhangsan@email  |        180 |        1 |
|  2 | 李四     |      15 | lishi@          |        152 |       11 |
|  3 | 王五     |      17 | wangwu@126.com  |        177 |        2 |
|  4 | 赵六     |      18 | zhaoliu@126.com |        156 |        3 |
|  5 | 田七     |      16 | tianqi@126.com  |        179 |        4 |
|  6 | 王八     |      88 | wangba@126.com  |        133 |       33 |
|  7 | 刘九     |      34 | liujiu@163.com  |        193 |       22 |
+----+----------+---------+-----------------+------------+----------+

5.2 创建session1查询数据

mysql> begin; --开启事物
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where class_id=1 for update; --查询数据锁定
+----+----------+---------+----------------+------------+----------+
| id | stu_name | stu_age | stu_email      | stu_height | class_id |
+----+----------+---------+----------------+------------+----------+
|  1 | 张三     |      14 | zhangsan@email |        180 |        1 |
+----+----------+---------+----------------+------------+----------+
1 row in set (0.00 sec)

5.3 创建session2查询数据

mysql> begin;  --开启事物
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where class_id=2 for update; --查询数据
+----+----------+---------+----------------+------------+----------+
| id | stu_name | stu_age | stu_email      | stu_height | class_id |
+----+----------+---------+----------------+------------+----------+
|  3 | 王五     |      17 | wangwu@126.com |        177 |        2 |
+----+----------+---------+----------------+------------+----------+
1 row in set (0.00 sec)

发现二者在查询数据时互不干扰。

5.4 在session1 的事物基础内修改数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where class_id=1 for update;
+----+----------+---------+----------------+------------+----------+
| id | stu_name | stu_age | stu_email      | stu_height | class_id |
+----+----------+---------+----------------+------------+----------+
|  1 | 张三     |      14 | zhangsan@email |        180 |        1 |
+----+----------+---------+----------------+------------+----------+
1 row in set (0.00 sec)

--上面是session1的操作。
--修改数据
mysql> update student set stu_height=134 where class_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
--修改成功后的结果
mysql> select * from student where class_id=1;
+----+----------+---------+----------------+------------+----------+
| id | stu_name | stu_age | stu_email      | stu_height | class_id |
+----+----------+---------+----------------+------------+----------+
|  1 | 张三     |      14 | zhangsan@email |        134 |        1 |
+----+----------+---------+----------------+------------+----------+
1 row in set (0.00 sec)

总结：

行锁是建立在索引的基础上。
普通索引的数据重复率过高导致索引失效，行锁升级为表所

上述sql 中的 for update 是给当前语句加上排他锁

来源：https://www.cnblogs.com/zyy1688/p/9983122.html

风zi

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
三、低效率的索引导致mysql的行锁升级为表锁

一、建立一张测试表–student；DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `stu_name` varchar(255) DEFAULT NULL, `stu_age` int(11) DEFAULT NULL, `stu_ema...
复制链接

扫一扫

专栏目录