mysql建索引需要注意什么意思_mysql建索引要注意了

昨天看到现场有一张表索引建的不对，调整了一下，并发性提高了很多。

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