首页 > 其他分享 >ddl锁表统计

ddl锁表统计

时间:2023-02-09 18:33:19浏览次数:43  
标签:varchar 锁表 Query 统计 ddl table NULL root localhost

官方文档:

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

 

虽然官方统计了大部分DDL操作时,是否允许DML,但并不是很全,或者有些支持DML,但仅仅只支持查询,不支持增删改操作。

版本:MySQL 5.7

 

场景一、

修改 varchar 字段长度时,在 utf8mb4 字符集下,varhcar 长度从【1-63】长度变为 【64及以上】长度时,会锁表,只能查询。反之亦然

State:copy to tmp table

 

场景二、int 类型个性为 decimal(10,2),会锁表,只能查询。

State:copy to tmp table

MySQL [(none)]> show processlist;
+----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+
| Id | User | Host            | db    | Command | Time | State                           | Info                                      |
+----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+
|  9 | root | localhost:39614 | ceshi | Sleep   | 1604 |                                 | NULL                                      |
| 12 | root | localhost:39620 | NULL  | Query   |    3 | Waiting for table metadata lock | select * from ceshi.t1 limit 1 for update |
| 13 | root | localhost:39622 | NULL  | Query   |    0 | starting                        | show processlist                          |
| 17 | root | localhost:39630 | ceshi | Query   |    5 | copy to tmp table               | alter table t1 modify age delimal(10,2)   |
+----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+

 

 

场景三、

varchar 长度 60 -> 100 ,同时新增加一个 varchar 字段。会锁表,只能查询。

MySQL [(none)]>   show processlist;
+----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+
| Id | User | Host            | db    | Command | Time | State             | Info                                                                    |
+----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+
|  9 | root | localhost:39614 | ceshi | Sleep   | 1903 |                   | NULL                                                                    |
| 12 | root | localhost:39620 | NULL  | Query   |    0 | starting          | show processlist                                                        |
| 13 | root | localhost:39622 | NULL  | Sleep   |  299 |                   | NULL                                                                    |
| 17 | root | localhost:39630 | ceshi | Query   |    6 | copy to tmp table | alter table t1 modify name varchar(100),add column describ varchar(100) |
+----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+
4 rows in set (0.000 sec)

 

标签:varchar,锁表,Query,统计,ddl,table,NULL,root,localhost
From: https://www.cnblogs.com/nanxiang/p/17106668.html

相关文章