开发者社区

文档建议反馈控制台

最新优惠活动

文章/答案/技术大牛

发布

为InnoDB的ALTER TABLE优化MySQL

您好！您的问题是关于优化MySQL中InnoDB的ALTER TABLE。以下是我的答案：

InnoDB是MySQL数据库中的一个存储引擎，它支持事务处理和行级锁定，并且提供了高效的数据存储和检索。ALTER TABLE是MySQL中用于修改表结构的命令，例如添加、删除或修改列、添加或删除索引等。

优化InnoDB的ALTER TABLE可以提高数据库性能，减少数据丢失的风险，并提高数据恢复的速度。以下是一些建议：

使用在线DDL工具，例如pt-online-schema-change或gh-ost，这些工具可以在不锁定表的情况下执行ALTER TABLE操作。
使用ALGORITHM=INPLACE选项，这可以在不锁定表的情况下执行ALTER TABLE操作。
使用ALGORITHM=COPY选项，这可以在不锁定表的情况下执行ALTER TABLE操作，并且可以在执行过程中检查数据的完整性。
使用LOCK=NONE选项，这可以在不锁定表的情况下执行ALTER TABLE操作。
使用LOW_PRIORITY选项，这可以在不影响其他查询的情况下执行ALTER TABLE操作。
使用pt-online-schema-change工具，这是一个强大的工具，可以在不锁定表的情况下执行ALTER TABLE操作，并且可以在执行过程中检查数据的完整性。

总之，优化InnoDB的ALTER TABLE可以提高数据库性能，减少数据丢失的风险，并提高数据恢复的速度。您可以使用上述建议来优化您的ALTER TABLE操作。

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

有帮助

没帮助

相关·内容

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

https://blog.csdn.net/wzy0623/article/details/53908035 MySQL的alter table性能在表很大的时候会出现问题。...不是所有的alter table操作都会导致重建表。例如，可以通过两种方式创建或去掉列的默认值（一种快、一种慢）。...MySQL没有使用这种优化，而是任何modify column都会导致表重建。...（alter table可以使用alter column、modify column、change column来修改列，每个命令做的事情都不一样）。...基本的技巧是为想要的表结构创建一个.frm文件来替代现有的.frm文件，步骤如下：创建一个布局完全一样的空表，但是想改动的地方除外（例如添加enum的常量）。

1.4K3 1

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

---一、前言MySQL 8.0.29之前，在线 DDL 操作中即时添加列只能添加在表的最后一列，对于在某个具体列后面快速添加列很不方便，MySQL 8.0.29 扩展了对 ALTER TABLE …...2、即时删除列MySQL 8.0.29 开始，ALTER TABLE … ALGORITHM=INSTANT支持删除某列。...例如我们添加一个超出行大小限制的列：mysql> ALTER TABLE sbtest1 ADD COLUMN pad6 varchar(4990),ALGORITHM=INSTANT;ERROR 1118...初始值为 0。上面的操作中我们对表 sbtest1 进行了多次 ALTER TABLE ... ALGORITHM=INSTANT 。.../data/mysqld.sock --backup图片这是因为为了支持 ALTER TABLE … ALGORITHM=INSTANT 的新特性，InnoDB redo log 格式对于所有DML操作都发生了变化

9898 0

加快alter table的操作速度

01 加快alter table的操作速度 MySQL中的alter table操作对于大表来讲，是一个比较严重的问题，MySQL执行大部分alter table的操作步骤是：用新的结构创建一张表...一般针对这种大表的更新，常见的操作技巧无外乎两种情况，一种是在一台测试机器上执行alter table操作，然后和线上的的主库进行切换，另外一种是通过创建一张新表，然后通过重命名和删表操作来交换两张表。...当然，还有一小部分alter table的操作是不需要重建表的，例如下面的操作，我们已知需要给col_name字段添加一个默认值5，SQL如下： alter table test modify column...还有一种是用alter语法快速创建MyISAM索引的操作，我们知道MyISAM采用的是表锁，在使用alter table语法导入数据的时候有一种有效的方法，如下： alter table test...如果使用的是innodb存储引擎，在导入数据的时候可以先删除唯一索引，然后进行alter操作，最后在重新创建删除掉的索引。如果你使用的是Percona MySQL，那么服务本身就会提供这样的操作。

2K1 0

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

---- 一、前言 MySQL 8.0.29 之前，在线 DDL 操作中即时添加列只能添加在表的最后一列，对于在某个具体列后面快速添加列很不方便，MySQL 8.0.29 扩展了对 ALTER TABLE...例如我们添加一个超出行大小限制的列： mysql> ALTER TABLE sbtest1 ADD COLUMN pad6 varchar(4990),ALGORITHM=INSTANT; ERROR...初始值为 0。上面的操作中我们对表 sbtest1 进行了多次 ALTER TABLE ... ALGORITHM=INSTANT 。...ALTER TABLE … ALGORITHM=INSTANT 的新特性，InnoDB redo log 格式对于所有 DML 操作都发生了变化。...三、小结 ALTER TABLE … ALGORITHM=INSTANT 的新特性，可以在表的任一位置添加一列或多列，也可以快速的删除某列，极大的提高了在线 DDL 的效率。

2K2 0

小白学习MySQL - InnoDB支持optimize table？

MySQL数据库中进行表空间整理，可以用的一种操作就是optimize table， OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [...的确，官方文档提到了，对于InnoDB的表，不支持optimize table， OPTIMIZE TABLE using online DDL is not supported for InnoDB...mysql 17M Aug 1 13:53 t_per.ibd 因此，从严格的意义讲，说InnoDB不支持optimize table，其实不太准确，如官方文档所说，InnoDB引擎的表，optimize...table会自动转成alter table ... force，相当于做了recreate和analyze， For InnoDB tables, OPTIMIZE TABLE is mapped...因此，对InnoDB，optimize table封装了其他操作，但对执行者来说，optimize table是执行成功的，因为他做了重建表和更新索引统计信息并释放空间的操作。

1.7K2 0

Mysql Innodb 性能优化

概述 innodb 作为最主流使用的 mysql 存储引擎，尤其在新版本的 mysql 中 MyISAM 存储引擎被废除，更加提升了 innodb 如日中天的地位。...那么，作为 mysql 的使用者，如何优化 innodb 使之发挥更强大的性能，就成为了必修课。 2....参数优化 2.1. innodb_buffer_pool_size innodb_buffer_pool_size 参数指的是内存中缓存 innodb 表的索引、数据的插入时缓冲，这是 innodb 参数优化的首要参数...innodb_buffer_pool_size 默认值是 8M，如果 mysql 所用的是专用的 DB 服务器，那么可以把这个数字设置为实际物理内存的 70% 到 80%，这个参数不能动态改变，所以配置时需要考虑...2.3. innodb_file_per_table 是否为每个表使用独立的文件存储，参数为 0 或 1，1 是开启，0 是关闭。通常来说还是建议将这一项开启的。

1.6K5 0

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

有没有一种途径能让我们随时能了解 alter table 语句的执行状况呢？答案是肯定的，今天我们就来一起学习下 MySQL alter table 语句进度评估。...stage/innodb/alter table (insert)：对 ALTER TABLE 操作添加的每个索引重复此阶段。...stage/innodb/alter table (log apply index)：此阶段包括在 ALTER TABLE 运行时生成的 DML 日志的应用。...stage/innodb/alter table (end)：包括刷新阶段之后出现的所有剩余工作，例如重新应用在 ALTER TABLE 运行时在 table 上执行的 DML。...2 示例下面我们通过实验来试试 alter table 的进度查询首先启用 stage/innodb/alter%： mysql> UPDATE performance_schema.setup_instruments

1.1K2 0

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

案例会话一会话二会话三 select sleep(1800),id from sbtest1 where id=1; 注：休眠1800秒后查询id=1的记录 alter table sbtest1...MariaDB 10.3版本里，可以通过Alter table ......参考官方文档：https://mariadb.com/kb/en/wait-and-nowait/ 在MySQL 8.0.35版本里，并没有提供相关语法，这对于你不熟悉数据库业务的情况下，执行DDL上线是非常危险的...= 3336 mysql_table = 'sbtest1' ddl_sql = 'alter table sbtest1 add column city varchar(30)' table_alteration...) table_alteration.alter_table(ddl_sql) 当检测到sbtest1表有未执行完的事务时，打印出正在执行的SQL，并给出提示：这对于你刚加入一家公司，不熟悉数据库业务情况下

3391 0

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

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

3.2K8 0

Mysql InnoDB行锁优化建议

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定要更高一些，但是在整体并发处理能力方面是要远远优于MyISAM的表级锁定的。...当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了但是当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不比MyISAM高，甚至可能会更差建议：...（1）尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定（2）合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能地缩小锁定范围，避免造成不必要的锁定而影响其他...Query的执行（3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录（4）尽量控制事务的大小，减少锁定的资源量和锁定时间长度（5）在业务环境允许的情况下...，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本

1.4K5 0

第17问：如何评估 alter table 的进度？

问题我们执行 alter table 语句后，经常面临“跑又跑不完，杀又不敢杀”的窘境。如果能评估 alter table 的进度就幸福多了。...实验 MySQL官方已经给出了文档：https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html...还是按照之前实验 11 的技巧，快速造一些数据：重复执行 insert，让表中有足够数据：我们来跑一个alter table：在另一个 session 中，执行 SQL 查看进度：看起来 SQL...我们来看看评估的主要原理：在这张表里，MySQL 提供了如下信息： DDL 语句运行的当前阶段当前阶段的开始时间和结束时间，当前阶段未结束时，结束时间为当前时间父事件 ID，语句运行的各个阶段，会具有相同的父事件...ID 工作量评估，MySQL 将 DDL 的运行过程拆成一个一个任务包，这里提供了已经完成的任务包数量和估算的任务包总数量，两者的比值即为当前进度（注意：这里的时间是当前阶段的时间，而工作量评估是整个语句的工作量

1.1K2 0

MySQL优化 InnoDB 事务管理

今天我们来聊聊如何优化InnoDB事务管理？要优化InnoDB事务管理，请在事务功能的性能开销与服务器的工作负载之间找到理想的平衡。...默认的MySQL设置AUTOCOMMIT=1 可能会对繁忙的数据库服务器造成性能限制。...对于仅包含一条SELECT语句的事务，打开AUTOCOMMIT有助于 InnoDB识别只读事务并对其进行优化。避免在插入，更新或删除大量行之后执行回滚。...考虑COMMIT在大数据更改操作期间定期发布语句，可能将单个删除或更新分解为对较少行数进行操作的多个语句。...如果可以承受因意外退出而导致的一些最新提交事务的丢失，可以将innodb_flush_log_at_trx_commit 参数设置为0。InnoDB尽管不能保证刷新，但还是尝试每秒刷新一次日志。

5154 0

MySQL InnoDB索引介绍及优化

我们来看看在数据库中的索引：全表扫描 VS 索引扫描以字典为例，全表扫描就是如果我们查找某个字时，那么通读一遍新华字典，然后找到我们想要找到的字而跟全表扫描相对应的就是索引查找，索引查找就是在表的索引部分找到我们想要找的数据具体位置...，然后才能确定是否将所有数据返回右边索引扫描：索引查找是根据首字母排序找到D开头的Dev,如果首字母相同，那么再根据第二个字母排序找到，以此类推，我们找到ID为100008，然后回表查出ID为100008...的数据结论：因此索引(对应InnoDB)的索引值对应的是主键ID 二、如何找到索引对应的值 InnoDB引擎主要根据 (1)B+tree (2)二分查找法 ?...如上图InnoDB表是聚簇表，意思是InnoDB本身是一张大的索引组织表，也是一个根据主键排序的大索引的B+树结构，我们在InnoDB里面另外建立自己想要索引的表的字段聚簇索引就意味着InnoDB表本身...，因此在DML中，插入等操作不再是普通的插入，MySQL将它封装成了一个事务，连着索引项的排序表一起操作因此，我们应当严格控制表上的索引数量，否则容易影响数据库的性能总结索引维护如下： 1、索引维护由数据库自动完成

9831 0

MySQL中Myisam、InnoDB碎片优化

表类型为Myisam，已建立一个索引，所以应该是产生了大量碎片，使用 Optimize table 表名优化后大小变为2.19M，少了很多，同时可以看出该表上的索引建的多余，因为插入操作比查询操作要多很多...借此延伸下MYSQL中Myisam、InnoDB碎片优化方式： Myisam清理碎片 OPTIMIZE TABLE table_name InnoDB碎片优化 if you frequently delete...The best way to do this is using ‘alter table’ without actually changing anything: ALTER TABLE foo ENGINE...=InnoDB; This is what MySQL does behind the scenes if you run ‘optimize table’ on an InnoDB table....什么是mysql碎片?怎样知道表的碎片有多大呢?

1.3K1 0

MySql InnoDB 存储引擎表优化

一、InnoDB 表存储优化 1、OPTIMIZE TABLE 适时的使用 OPTIMIZE TABLE 语句来重组表，压缩浪费的表空间。这是在其它优化技术不可用的情况下最直接的方法。...MySQL 5.7.10版本，InnoDB XA事务的两阶段提交是默认支持的，不能设置禁用innodb_support_xa。...innodb_log_write_ahead_size 的值可以设置为 InnoDB 日志文件块大小的倍数(2n)。最小的值为(512)。设置为最小值时 Write-ahead 不会发生。...七、InnoDB DDL 操作优化许多DDL操作，如表和索引的(CREATE, ALTER, 及DROP 语句) 可以在线执行。...这种情景下，一系列如 DROP TABLE 及 CREATE TABLE 语句会执行的很快。因为主键InnoDB表的存储结构是高度整合的，主键的变更会引起整张表的重构。

3502 0

MySQL 8.0 InnoDB 的统计信息机制优化

图片 from 通义万相前言ＭySQL 的InnoDB引擎会维护着用户表每个索引的统计信息，来帮助查询优化器选择最优的执行计划，详细的来说，key的分布情况能决定多表join的顺序，也能够决定查询使用哪一个索引...控制表多少比例的行被修改后自动更新统计信息，默认是10%，也可以在create 或者alter table 时通过 STATS_AUTO_RECALC 语法来指定比率。...具体Analyze的代码路径为: sql_admin.cc:ql_cmd_analyze_table::execute sql_admin.cc:mysql_admin_table handler.cc...这里有一个问题，如果采样率设置为10%，那采样只是读前10%的记录。更科学的做法是在整棵索引树上均匀的采样。在8.0.19之后的版本中 InnoDB引擎层实现 sample。...from http://mysql.taobao.org/monthly/2020/03/08/ ，有删改。 #MySQL 8.0 #优化

2871 0

Mysql优化系列（1）--Innodb引擎下mysql自身配置优化

2.之所以选用innodb作为存储引擎的考虑目前来说，InnoDB是为Mysql处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。...3.下面是对线上mysql5.6版本的数据库的配置进行的优化分析记录： 1）内存利用方面： innodb_buffer_pool_size 这个是Innodb最重要的参数，和MyISAM的key_buffer_size...，它用于ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 等命令时需要的内存。...下my.cnf的优化配置，能使mysql性能大大提升： # For advice on how to change settings please see # http://dev.mysql.com/...如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。

2.5K6 0

MySQL实战优化之InnoDB整体架构

2. undo 日志文件：保证更新后的数据可以回滚假设 id = 10 这行数据的name原来是 zhangsan, 现在需要更新为 lisi，那么此时需要先把更新的原来的值 name = 'zhangsan...因为磁盘上的依旧是 name 的值是zhangsan，内存中的已经是lisi所以说是脏数据。 4. Redo Log buffer:防止Mysql服务宕机后，保证数据不被丢失。...所谓的redo日志，就是记录下来对数据做了什么修改，比如对 id = 10 这行记录修改了name字段的值为lisi，这就是一条日志。...5. binglong日志 binglog日志是mysql server 服务的日志，叫做归档日志，他里面记录的是逻辑行的日志，类似于对 srtudent 表中的 id = 10 这行数据做了更新操作...总结innodb存储引擎做一次跟新的架构原理：大家通过一次更新数据的流程，就可以清晰地看到，InnoDB存储引擎主要就是包含了一些buffer pool redo log buffer等内存里的缓存数据

2020 0

Mysql学习笔记（十) Innodb内存优化

我们可以通过调整innodb buffer pool的大小改变young和old的分配比例，控制脏缓存的刷新，使用多个innodb缓存池的方法优化innodb的性能。...所以innodb_buffer_pool_size越大，缓存的命中率越高，io越少，性能也就越好。在专用的数据库服务器上，可以将其值设置为80%，但是要避免设置的过大而导致页交换。...我们也可以通过 show status like 'Innodb_buffer_pool_%'; 来查看mysql innodb的缓存情况。 ?...5.调整缓存池数量，减少内部缓存池数据结构的争用 mysql的内部线程都buffer pool的访问是互斥的，所以高并发情况下有一定问题。...innodb_io_capacity表示磁盘系统的io能力，默认值为200，表示每秒完成的io次数。对于固态硬盘可以适当增加，能力比较弱的磁盘可以适当降低。

1.2K3 0

为 Zabbix 优化 MySQL

考虑到这一点，我将提出一些优化，这将有助于你的 MySQL 最好的使用磁盘（必然的将帮助你的 Zabbix 最好的利用 MySQL）和可用的硬件资源。...注意，大部分的调优对任何高性能 MySQL 设置是通用的，尽管有些是明确适用于 Zabbix，因为你可以以放宽一些参数为代价获取更大的影响，最糟糕的情况是，丢失收集数据的 1s，从会议期间讨论，对任何人来说没有什么大不了的...如果没有，那就是说，如果你没有使用 AIO 并且不准备使用，仅仅设置这些值为 8 即可。...tmpdir - 有时候指定 tmpdir 为 /dev/shm 是一个好注意，以至于 on-disk temporary 表实际是写入内存中，但是在 MySQL 5.5 有一个重要的警告：如果你这样做了...除了我上面已经提到的优化（它们应该大大增加你的写吞吐量），我将鼓励你尝试分片（如果你还没有使用分片）或者是通过 BASH subpartitions，正如我们在 partitioning in some

1.7K3 0

点击加载更多

扫码

添加站长进交流群

领取专属 10元无门槛券

手把手带您无忧上云

扫码加入开发者社群

相关资讯

热门标签

活动推荐

运营活动

活动名称

广告关闭