The new MySQL5.7 feature changes the index name without locking the table
MySQL5.7 does not lock the table when changing the index name. The test procedure is as follows:
Click (here) to collapse or open
Mysql > select count (*) from T_USER_INFO
+-+
| | count (*) |
+-+
| | 7147528 |
+-+
Click (here) to collapse or open
Mysql > select version ()
+-+
| | version () |
+-+
| | 5.7.18-log |
+-+
Click (here) to collapse or open
Mysql > show create table T_USER_INFO\ G
* * 1. Row *
Table: T_USER_INFO
Create Table: CREATE TABLE `Tunable USERINFO` (
`NAME` varchar (50) DEFAULT NULL
`PHONE` varchar (50) DEFAULT NULL
`CARD_ ID`varchar (50) DEFAULT NULL
KEY `USER_ name` (`USER_ name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec) now open two windows and enter the MySQL command line at the same time
Window one:
Click (here) to collapse or open
Mysql > alter table T_USER_INFO rename index idx_user_info_name to idx_user_info_nm; window 2:
Click (here) to collapse or open
Mysql > select * from T_USER_INFO where user_name='rz19930119'; executes the statement that changes the index name first, and executes the second query before the statement finishes. The query statement can be executed normally before the index name is changed.
Conclusion: 1. MySQL5.7 changing the index will not lock the table (although this is a chicken rib feature, because the index name is generally not changed).
2. MySQL5.7 adds the syntax of alter table tablename rename index idxname1 to idxname2. In versions prior to 5.7, the index name can only be changed by deleting it before creating it.
