昨天看到现场有一张表索引建的不对,调整了一下,并发性提高了很多。
CREATE TABLE `test` (
`version_id` varchar(40) COLLATE utf8_bin NOT NULL,
`type` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`version_num` int(11) NOT NULL,
`tenant_id` varchar(40) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`version_id`),
KEY `ind_pvr_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
select * from test;
mysql> select * from test;
+-------------------+-----------------+-------------+-------------------+
| version_id | type | version_num | tenant_id |
+-------------------+-----------------+-------------+-------------------+
| 000145d60f732601 | contact | 11667 | 0001456d8d47ee01 |
| 000147f2c61da46ef | contact | 2 | 000147f2c422716ea |
| 000149523343d105 | contact | 11667 | 0001456d8d47ee01 |
| 0001495242eba85b | group | 6238 | 0001456d8d47ee01 |
| 00014a9bf5157c01 | wb_msg | 1 | 0001456d8d47ee01 |
| 00014a9bf515a602 | wb_msg_type | 7 | 0001456d8d47ee01 |
| 00014d539a816e26 | home_panel | 3 | 0001456d8d47ee01 |
| 00014d539a817b27 | home_item | 10 | 0001456d8d47ee01 |
| 00014f45536b2625c | collect_message | 2 | 0001456d8d47ee01 |
+-------------------+-----------------+-------------+-------------------+
session1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set version_num = 11668 where type='contact' and tenant_id='0001456d8d47ee01';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
session2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set version_num = 2 where type='wb_msg' and tenant_id='0001456d8d47ee01';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
--mysql加锁是按照索引来加的,如果只按tenant_id加索引,tenant_id=0001456d8d47ee01则锁了很多记录,影响并发。
DROP INDEX ind_pvr_tenant_id;
CREATE INDEX ind_pvr_tenant_id_TYPE ON test(tenant_id,TYPE);
session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test set version_num = 11668 where type='contact' and tenant_id='0001456d8d47ee01'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test set version_num = 2 where type='wb_msg' and tenant_id='0001456d8d47ee01'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0