三、非効率なインデックスによりmysqlのロー・ロックがテーブル・ロックにアップグレードされる
49965 ワード
一、テストテーブル–studentを作成する.
二、mysqlを閉じて自動的に物事を提出する
三、インデックスを作成しないで、行ロックがインデックスに基づいて実現されるかどうかをテストする
3.2 mysql端末を2つ開く
3.2.1セッション1の作成
3.2.2セッション2の作成
ロー・ロックはインデックス・フィールドに基づいて構築され、ローがロックされたカラムがインデックス・カラムでない場合はテーブル・ロックにアップグレードされます.私の2つのテストのクエリー条件には重複フィールドがありますが、実際には2つの値でテストしても同じ効果があります.
四、索引を作成した場合のテスト
主なテストの2つの側面:1つは、インデックス列のデータ重複率が高すぎる場合です.2つ目は、インデックス・カラムのデータ重複率が低すぎることです.はclass_idインデックス作成 インデックスが重複している場合---現在のデータステータスを表示します.class_id重複データが多すぎる インデックスの場合session 1 を作成インデックスクエリの場合session 2-----クエリ失敗
結論:インデックス列データが重複しすぎると、インデックスが失効し、行ロックがテーブルロックに変わります.インデックス列のデータ値が一意である場合のテスト 5.1データ状況
5.2 session 1クエリーデータの作成
5.3 session 2クエリーデータの作成
両者はデータのクエリ中に互いに干渉しないことが分かった.
5.4 session 1の物事の基礎内でデータを修正する
まとめ:ロー・ロックは、インデックスに基づいて構築されます. 通常のインデックスのデータ重複率が高すぎるとインデックスが失効し、ロー・ロックはテーブルの にアップグレードされます.
上記のsqlのfor updateは、現在の文に排他ロックを付けるものです.
ソース:https://www.cnblogs.com/zyy1688/p/9983122.html
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, '[email protected]', 177, 3);
INSERT INTO `student` VALUES (4, ' ', 18, '[email protected]', 156, 4);
INSERT INTO `student` VALUES (5, ' ', 16, '[email protected]', 179, 2);
INSERT INTO `student` VALUES (6, ' ', 88, '[email protected]', 133, 3);
INSERT INTO `student` VALUES (7, ' ', 34, '[email protected]', 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端末を2つ開く
3.2.1セッション1の作成
--
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セッション2の作成
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
--
ロー・ロックはインデックス・フィールドに基づいて構築され、ローがロックされたカラムがインデックス・カラムでない場合はテーブル・ロックにアップグレードされます.私の2つのテストのクエリー条件には重複フィールドがありますが、実際には2つの値でテストしても同じ効果があります.
四、索引を作成した場合のテスト
主なテストの2つの側面:1つは、インデックス列のデータ重複率が高すぎる場合です.2つ目は、インデックス・カラムのデータ重複率が低すぎることです.
--
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)
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 |
+----+----------+---------+-----------------+------------+----------+
--
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)
--
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
結論:インデックス列データが重複しすぎると、インデックスが失効し、行ロックがテーブルロックに変わります.
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 session 1クエリーデータの作成
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 session 2クエリーデータの作成
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 session 1の物事の基礎内でデータを修正する
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