开发者社区

文档建议反馈控制台

最新优惠活动

文章/答案/技术大牛

发布

Mysql: ALTER table而不锁定表？

Mysql: ALTER table而不锁定表是指在进行表结构修改操作时，尽量减少对表的锁定时间，以提高数据库的并发性能和可用性。在传统的ALTER TABLE操作中，会对被修改的表进行锁定，导致其他用户无法对该表进行读写操作，从而影响系统的正常运行。

为了避免表锁定带来的问题，Mysql引入了一些技术和优化策略，使得ALTER TABLE操作可以在不锁定表的情况下进行。下面是一些常用的方法：

Online DDL（在线DDL）：Mysql 5.6版本及以上引入了Online DDL功能，可以在进行表结构修改的同时，允许其他用户对表进行读写操作。Online DDL通过使用临时表和重定向机制来实现，保证了数据的一致性和完整性。
InnoDB引擎：使用InnoDB作为存储引擎可以提供更好的并发性能和事务支持。InnoDB引擎支持在线DDL操作，可以在不锁定表的情况下进行表结构修改。同时，InnoDB引擎还提供了一些优化参数，如innodb_online_alter_log_max_size和innodb_online_alter_log_trigger参数，可以进一步优化在线DDL操作的性能和效果。
PT Online Schema Change工具：Percona Toolkit是一个开源的MySQL工具集，其中包含了PT Online Schema Change工具，可以实现在不锁定表的情况下进行表结构修改。该工具通过创建一个与原表结构相同的临时表，并通过触发器将数据同步到临时表中，最后再将临时表重命名为原表，完成表结构修改。

总结起来，Mysql提供了多种方法来实现在不锁定表的情况下进行ALTER TABLE操作，以提高数据库的并发性能和可用性。通过使用在线DDL功能、选择合适的存储引擎（如InnoDB）、借助第三方工具（如PT Online Schema Change），可以有效地减少对表的锁定时间，提高系统的稳定性和性能。

页面内容是否对你有帮助？

有帮助

没帮助

相关·内容

什么是MySQL alter表？

⭐本文介绍⭐ 当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。开始本章教程前让我们先创建一张表，表名为：testalter_tbl。...命令及 DROP 子句来删除以上创建表的 i 字段： mysql> ALTER TABLE testalter_tbl DROP i; 如果数据表中只剩余一个字段则无法使用DROP来删除字段。...MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型: mysql> ALTER TABLE testalter_tbl ADD...mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100; 如果你不设置默认值，MySQL会自动设置该字段默认为...尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl： mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl; ALTER

6701 0

alter table锁表，MySQL出现Waiting for table metadata lock的场景浅析及解决方案

另外一个session 对A表进行alter，出现waiting for table metadata lock ---- MySQL版本为5.6.12。...在进行alter table操作时，有时会出现Waiting for table metadata lock的等待场景。...一般alter table的操作过程中（见下图），在after create步骤会获取metadata 独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行...（当然，也并不是所有类型的alter操作都能online的，具体可以参见官方手册：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html...总之，alter table的语句是很危险的，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。

3.2K8 0

加速MySQL的alter table操作（书摘备查）

https://blog.csdn.net/wzy0623/article/details/53908035 MySQL的alter table性能在表很大的时候会出现问题。...MySQL执行大部分更改操作都是新建一个需要的结构的空表，然后把所有老的数据插入到新表，最后删除旧表。这会耗费很多时间，尤其是在内存紧张，而表很大并有很多索引的时候。...不是所有的alter table操作都会导致重建表。例如，可以通过两种方式创建或去掉列的默认值（一种快、一种慢）。...Handler_write 1000 理论上，MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中，因此可以不接触表而更改它。...用读取锁刷新该表。重命名第2个表的.frm和.MYI文件，这样MySQL就可以把它们用在第1个表上。释放读锁。使用repair table创建表的索引。

1.4K3 1

新特性解读 | MySQL8.0 ALTER TABLE …

---一、前言MySQL 8.0.29之前，在线 DDL 操作中即时添加列只能添加在表的最后一列，对于在某个具体列后面快速添加列很不方便，MySQL 8.0.29 扩展了对 ALTER TABLE …...--report-interval=2 --threads=10 prepare1、任一位置即时添加列原始表结构如下：图片在表sbtest1任一位置即时添加列：k列后面添加k2列：mysql> ALTER...而在8.0.29之前的版本，仅支持在表最后一列即时添加列，不支持在表任一位置即时添加列，如下8.0.27中操作：图片可以看到，同样2千万行的表通过默认的ALGORITHM在任一位置添列耗时7分22秒，而...2、即时删除列MySQL 8.0.29 开始，ALTER TABLE … ALGORITHM=INSTANT支持删除某列。...三、小结ALTER TABLE … ALGORITHM=INSTANT 的新特性，可以在表的任一位置添加一列或多列，也可以快速的删除某列，极大的提高了在线 DDL 的效率。

1K8 0

MySQL表锁定问题

lock tables 命令是为当前线程锁定表.这里有2种类型的锁定，一种是读锁定，用命令 lock tables tablename read;另外一种是写锁定，用命令lock tables tablename...write.下边分别介绍： 1. lock table 读锁定如果一个线程获得在一个表上的read锁，那么该线程和所有其他线程只能从表中读数据，不能进行任何写操作。...0 rows affected (0.00 sec) mysql> 对user表加读锁定。...表，第6时刻将不会被阻塞，这是因为INNODB表是事务型的，对于事务表，例如InnoDB和BDB，–single-transaction是一个更好的选项，因为它不根本需要锁定表 2. lock table...sec) 对user表加写锁定。

1.9K2 0

MySQL 修改数据表 ALTER

修改列定义 ALTER table 表名 MODIFY 字段名列定义位置[FIRST | AFTER 字段名]; 修改列名称 ALTER table 表名 CHANGE 原字段名新字段名列定义...位置; 数据表更名 ALTER table 表名 RENAME [ TO | AS ] 新名称; RENAME table 表名 TO 新名称; 添加单列 ALTER TABLE 表名 ADD [COLUMN...] 字段名列定义位置; 添加约束 ALTER TABLE 表名 ADD PRIMARY KEY (字段); ALTER TABLE 表名 ADD UNIQUE (字段); ALTER TABLE 表名...ADD FOREIGN KEY (字段) REFERENCES 父表(字段); ALTER TABLE ALTER 字段 SET DEFAULT 值; 删除约束 ALTER TABLE 表名 ALTER...字段 DROP DEFAULT; ALTER TABLE 表名 DROP PRIMARY KEY; 删除唯一约束 SHOW INDEXES FROM 表名; ALTER TABLE 表名 DROP 索引

3.7K6 0

MySql表table相关

文章目录[隐藏] 修改表名删除表修改表名 # rename rename table 旧表名 to 新表名; rename table my_table to new_table; # alter...alter table 旧表名 rename [as] 新表名 alter table mytable rename as new_table; 删除表 drop 1、drop是DDL，会隐式提交...3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。...2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。...使用optimize table table_name会立刻释放磁盘空间

5832 0

新特性解读 | MySQL8.0 ALTER TABLE … ALGORITHM=INSTANT

---- 一、前言 MySQL 8.0.29 之前，在线 DDL 操作中即时添加列只能添加在表的最后一列，对于在某个具体列后面快速添加列很不方便，MySQL 8.0.29 扩展了对 ALTER TABLE...列： mysql> ALTER TABLE sbtest1 ADD COLUMN k2 int(10) AFTER k,ALGORITHM=INSTANT; 可以看到，2千万的表在任一位置即时添加列在秒级内完成...分22秒，而 ALGORITHM=INSTANT 仅支持默认在最后一列即时添加列。...2、即时删除列 MySQL 8.0.29 开始，ALTER TABLE … ALGORITHM=INSTANT 支持删除某列。...三、小结 ALTER TABLE … ALGORITHM=INSTANT 的新特性，可以在表的任一位置添加一列或多列，也可以快速的删除某列，极大的提高了在线 DDL 的效率。

2.1K2 0

SQL ALTER TABLE 语句- 灵活修改表结构和数据类型

SQL ALTER TABLE 语句 SQL ALTER TABLE 语句用于在现有表中添加、删除或修改列，也可用于添加和删除各种约束。...ALTER TABLE - 添加列要在表中添加列，请使用以下语法： ALTER TABLE 表名 ADD 列名数据类型; 以下 SQL 向 "Customers" 表添加了一个 "Email" 列：...ALTER TABLE 表名 DROP COLUMN 列名; 以下 SQL 从 "Customers" 表中删除了 "Email" 列： ALTER TABLE Customers DROP COLUMN...Email; ALTER TABLE - 重命名列要在表中重命名列，请使用以下语法： ALTER TABLE 表名 RENAME COLUMN 旧名 TO 新名; ALTER TABLE - 修改数据类型...要更改表中列的数据类型，请使用以下语法：对于 SQL Server / MS Access： ALTER TABLE 表名 ALTER COLUMN 列名数据类型; 对于 MySQL / Oracle

7581 0

MySQL 实战笔记第04期：alter table 语句进度评估

有没有一种途径能让我们随时能了解 alter table 语句的执行状况呢？答案是肯定的，今天我们就来一起学习下 MySQL alter table 语句进度评估。...2 示例下面我们通过实验来试试 alter table 的进度查询首先启用 stage/innodb/alter%： mysql> UPDATE performance_schema.setup_instruments...在此示例中，将 middle_name 列添加到 employee 示例数据库的 employee 表中。...mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name; Query OK,...，则 events_stages_current 会返回一个空集，这种情况下可以查询 events_stages_history 表以了解操作是否已经完成。

1.2K2 0

MySQL 8.0.35目前仍不支持ALTER TABLE ...NOWAIT功能

首先明确一个问题，Alter更改表结构，会加什么锁？答：MDL（元数据表锁），就如同我们打开一个EXCEL文档时，用户A正在翻阅，此时用户B要增加一列。...案例会话一会话二会话三 select sleep(1800),id from sbtest1 where id=1; 注：休眠1800秒后查询id=1的记录 alter table sbtest1...MariaDB 10.3版本里，可以通过Alter table ......= 3336 mysql_table = 'sbtest1' ddl_sql = 'alter table sbtest1 add column city varchar(30)' table_alteration...) table_alteration.alter_table(ddl_sql) 当检测到sbtest1表有未执行完的事务时，打印出正在执行的SQL，并给出提示：这对于你刚加入一家公司，不熟悉数据库业务情况下

3531 0

MySQL技能完整学习列表5、数据库操作——1、创建数据库和表——2、修改表结构（ALTER TABLE）

创建数据库和表操作 MySQL创建数据库的操作相对直接明了。以下是详细步骤和示例：首先，您需要具有适当的权限才能在MySQL中创建数据库。...修改表结构（ALTER TABLE） MySQL中的ALTER TABLE语句用于修改现有表的结构。使用此语句，您可以添加、删除或修改列，更改表的字符集或排序规则，重命名表，以及进行其他结构更改。...以下是ALTER TABLE语句的一些常见用法和示例： 1、添加新列：您可以使用ADD子句向表中添加新列。...例如，要在students表的lastname列上添加索引，可以使用以下语句： ALTER TABLE students ADD INDEX (lastname); 8、修改表的字符集和排序规则如果您需要更改表的字符集或排序规则...使用ALTER TABLE语句时，请确保在执行任何结构更改之前备份数据库或表。这些更改可能会影响数据完整性或查询性能，并且在某些情况下可能需要重新构建表或重新索引数据。

4501 0

MYSQL 5.7 到底 OPTIMIZE Table 塞不塞 DML

问题的起因是另外某篇文字中，对于optimize table 理解不深，说出optimize table 时会和 vacuum full 一样阻塞表的DML操作，马上就有好友指出，你说的不对，并拿出evidence...所以借此篇，1来证明optimize table 不阻塞DML 2 表示对好友lmongo的感谢，有一个能指出你错误，并大胆友善说出来的人，不多，要感谢。...然后对一个有上千万数据库的表进行optimize , alter table engine = innodb; alter table force；的操作。...而我为什么会有这个错误的概念，主要还是对MYSQL 的部分知识停留在MYSQL 5.6 或更早的知识记忆中。...另外如果是大表，需要做optimize table 时如果表中包含大量的索引，也可以直接先清理一些，不常用的索引，加速optimize table 的速度。

3.2K3 0

MySQL中使用表分区(table的partition)

建表语句如下： create table comment_partition( id int not null auto_increment, commenterIpv4 char(30),...,'2016-06-07 21-36'),(13,'127.0.0.1',4,'看了看了','2016-06-08 19-38'); 插入数据后mysql会根据不同的blog的值将记录放到不同的区中，如下为查看我们查询时...mysql从哪个分区查询我们的数据(在mysql的命令窗口中输入如下命令)： explain partitions select id,blog from comment_partition where

1.2K3 0

MySQL 索引

通过使用索引，数据库可以更快速地定位和检索数据，而不必扫描整个表。 2.为什么使用索引？提高 SELECT 操作性能的最佳方法是在查询中测试的一个或多个列上创建索引。...5.修改索引 MySQL 通过ALTER TABLE语句修改索引的属性。...6.删除索引 MySQL 使用DROP INDEX或者ALTER TABLE DROP INDEX语句删除索引。...DEFAULT：由 MySQL 自动选择适当的锁定级别。 NONE：不锁定表，允许其他会话并发访问。 SHARED：获取共享锁，允许其他会话读取表，但不允许写入。...NONE、SHARED 和 EXCLUSIVE 锁定选项通常用于 MyISAM 存储引擎。InnoDB 存储引擎通常会使用行级锁定，而不需要显式指定锁定选项。

3002 0

MySQL 不停机不锁表主从搭建

MySQL主从搭建分几种场景无业务数据，初始化搭建有业务数据，临时扩展初始化的搭建很简单，不涉及数据业务，所以锁表、停机等都不影响，但是更多时候是业务到一定阶段，才会涉及到横向扩展，需要做主从，...读写分离等来提升服务性能这个时候，数据业务不能中断，又需要快速进行扩展提升性能，只能在不停机、不停服务的情况下扩展，就需要用到下面介绍的这种方法来做数据库主从当然还是有前提条件，如果你原本MySQL...TRANSACTION 语句，开启单一事务，此时加锁，仅仅是为了获取准确的master-data中的binlogfile和pos信息，在开启事务后，锁已经释放了，所以对业务影响很小通过以上两个参数，可以在不长时间锁表的情况下获取准确的...to master_host='ip',master_user='slave',master_port=port,master_password='password',master_log_file='mysql-bin.file

2.9K1 0

MySQL之库表操作详述

2.表级锁定数据更新时锁定整个表：其锁定机制是表级锁定，也就是对表中的一个数据进行操作都会将这个表锁定，其他人不能操作这个表，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能...　　5，表的修改alter table 语法： 1....修改表名 ALTER TABLE 表名 RENAME 新表名 2....删除字段 ALTER TABLE 表名 DROP 字段名; 4....TABLE 表名 CHANGE 旧字段名新字段名新数据类型 [完整性约束条件…];#这一句除了改了字段名，还改了数据类型、完整性约束等等的内容 5.增加复合主键　　　 alter table 表名

6861 0

MySQL 锁机制（上） -- 全局锁与表级锁

按照锁定范围分按照锁定范围，mysql 的锁可以分为：全局锁 — 锁定整个 mysql 的全局执行表级锁 — 锁定单个表行级锁 — 锁定单条或多条行记录行级锁又可以进一步细分为：记录锁 —...意向锁就是我们这里说的“某些行已经加了锁”的状态标识，所有的共享锁加锁前都要对表加意向共享锁，排它锁加锁前，都要对表加意向排它锁，而意向锁之间不互斥。 3.2.1....表锁也同样可以通过 unlock tables 命令来解锁。由于 innodb 支持行锁，而表锁锁定范围过大，通常是不被使用的。 5.2....因此，在执行 alter table 语句时，一定要检查是否此时表上有事务或慢查询在执行。...MariaDB 为 alter table 语句添加了超时参数，来解决这个问题： ALTER TABLE tbl_name NOWAIT add column … ALTER TABLE tbl_name

2.1K1 0

mysql or条件可以使用索引而避免全表

在某些情况下，or条件可以避免全表扫描的。本文使用mysql版本是5.7x 1 .where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。...1)myisam表： CREATE TABLE IF NOT EXISTS `t_myisam` ( `id` int(1) NOT NULL AUTO_INCREMENT,...; insert into t_myisam (uid,aNum) values(3,33); insert into t_myisam (uid,aNum) values(4,44); mysql...> explain select * from t_myisam where id=1 or uid =2; 2)innodb表： CREATE TABLE IF NOT EXISTS `t_innodb...mysql> explain select * from t_myisam2 where id=1 or uid =2; 3.

1.5K2 0

技术分享 | 什么情况下 MySQL 连查询都能被阻塞？

本文操作环境为 MySQL 8.0。 1MySQL 表级锁有两种实现服务器（SERVER）层：本层的锁定主要是元数据锁（metadata lock，MDL）。...对于因 FLUSH TABLE 等待而导致的表不可读写的场景，通过上述视图/表是不一定有数据的。大致会有以下两种情况： Waiting for table flush：可以按如下方式寻找源头。...这种情况主要出现于因 FLUSH TABLES 而等待后，执行 DDL 语句如 ALTER TABLE。...5总结以下情况会导致表对象不可读写：因 Waiting for table metadata lock 而导致的表对象不可读写。...因 Waiting for table flush 而导致的表对象不可读写。本文关键字：#MySQL# #锁# #SQL# #MDL#

3731 0

点击加载更多

扫码

添加站长进交流群

领取专属 10元无门槛券

手把手带您无忧上云

扫码加入开发者社群

相关资讯

热门标签

活动推荐

运营活动

活动名称

广告关闭