三、低效率的索引导致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_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
--此处可以查看到查询失败

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

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

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

  1. 为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)

  1. 索引重复过多的情况下-----查看当前数据状态,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 |
+----+----------+---------+-----------------+------------+----------+
  1. 索引情况下创建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)
  1. 索引查询情况下创建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

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

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

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)

总结:

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

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

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

已标记关键词 清除标记
相关推荐
<p> <b><span style="background-color:#FFE500;">【超实用课程内容】</span></b> </p> <p> <br /> </p> <p> <br /> </p> <p> 本课程内容包含讲解<span>解读Nginx基础知识,</span><span>解读Nginx核心知识带领学员进</span>高并发环境下Nginx性能优化实战,让学生能够快速将所学融合到企业应用中。 </p> <p> <br /> </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <b><br /> </b> </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <b><span style="background-color:#FFE500;">【课程如何观看?】</span></b> </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> PC端:<a href="https://edu.csdn.net/course/detail/26277"><span id="__kindeditor_bookmark_start_21__"></span></a><a href="https://edu.csdn.net/course/detail/27216">https://edu.csdn.net/course/detail/27216</a> </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 移动端:CSDN 学院APP(注意不是CSDN APP哦) </p> <p style="font-family:Helvetica;color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 本课程为录播课,课程永久有效观看时长,大家可以抓紧时间学习后一起讨论哦~ </p> <p style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <br /> </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <strong><span style="background-color:#FFE500;">【学员专享增值服务】</span></strong> </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> <b>源码开放</b> </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 课件课程案例代码完全开放给你,你可以根据所学知识,自修改优化 </p> <p class="ql-long-24357476" style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;"> 下载方式:电脑登录<a href="https://edu.csdn.net/course/detail/26277"></a><a href="https://edu.csdn.net/course/detail/27216">https://edu.csdn.net/course/detail/27216</a>,播放页面右侧点击课件进资料打包下载 </p> <p> <br /> </p> <p> <br /> </p> <p> <br /> </p>
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页