Mysql修改表结构、添加索引会锁表吗？

静看º一季花开花落

已于 2024-05-11 09:46:01 修改

阅读量978

点赞数 11

分类专栏：数据库文章标签： mysql 数据库

于 2024-05-10 16:57:31 首次发布

本文链接：https://blog.csdn.net/qq_36010886/article/details/138672016

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

前言

在MySQL5.7和MySQL8.0中，表结构修改和索引添加通常不会锁定整个表。但是，在某些情况下，MySQL可能需要锁定整个表。

MySQL5.7

在MySQL5.7中，对于表结构变更、索引添加，MySQL使用了一种称为“In-Place Alter”的技术。这种技术允许在不锁定表的情况下进行修改。In-Place Alter使用了一种称为“Fast Index Creation”的技术来优化索引添加，它可以在不拷贝原始表数据的情况下创建新索引。

**对于表结构修改，MySQL5.7使用了一种称为“Online DDL”的技术。Online DDL允许在不锁定表的情况下进行表结构修改。**当修改表结构时，MySQL会创建一个新表，并将原始表中的数据复制到新表中。在复制数据期间，原始表仍然可读和可写。完成数据复制后，MySQL会将新表重命名为原始表的名称，并删除原始表。此过程通常只需要几秒钟或几分钟，并且可以在应用程序运行期间执行。

虽然In-Place Alter和Online DDL可以减少对表的锁定，但它们并不适用于所有情况。例如，在进行某些类型的表结构修改时，MySQL可能需要锁定整个表。在这种情况下，In-Place Alter和Online DDL可能不适用。

此外，在MySQL5.7中，如果使用ALTER TABLE语句添加索引，将会锁定表。但是，如果使用CREATE INDEX语句添加索引，则不会锁定表。这是因为CREATE INDEX语句使用了“Concurrent Inserts”的技术，它允许在不锁定表的情况下进行索引添加。Concurrent Inserts在索引添加期间允许其他并发操作，从而减少了锁定的需求。

MySQL8.0

MySQL8.0在表结构修改和索引添加方面引入了一些新的功能。其中最重要的功能是“Invisible Indexes”和“In-Place Alter”升级。

Invisible Indexes

“Invisible Indexes”允许您创建不会影响查询执行计划的索引。这些索引对于实时修改和测试查询计划非常有用。此外，如果某个索引不再需要，您可以将其设置为“不可见”，而不是完全删除它。这可以减少表锁定的时间，因为在删除可见索引之前不需要锁定整个表。

In-Place Alter

“In-Place Alter”在MySQL8.0中进行了升级，可以处理更多类型的表结构修改。例如，现在可以在不锁定表的情况下添加、删除和更改列。如果使用In-Place Alter进行表结构修改，则MySQL将创建一个新的表，将新表中的数据与原始表中的数据进行同步，然后在删除原始表之前将新表重命名为原始表的名称。

小结

在MySQL8.0中，索引添加的行为与MySQL5.7相同。如果使用ALTER TABLE语句添加索引，则将锁定表。相反，如果使用CREATE INDEX语句添加索引，则不会锁定表。使用“Concurrent Inserts”技术添加索引可以避免表锁定，这使得索引添加变得更加高效。

**此外，在MySQL8.0中，还引入了“Instant DDL”功能。这种功能允许您在不锁定表的情况下执行某些DDL操作，例如添加、删除和更改列，以及更改表类型和重命名表。**这可以显著提高MySQL的可用性和可维护性。

转自：https://baijiahao.baidu.com/s?id=1760662405953282994&wfr=spider&for=pc

表结构存储

MySQL5.7

InnoDB

每个数据库有三种文件形式：opt、frm、ibd。

opt：包含了该数据库的各种属性，比如数据库的字符集和比较规则。
frm : 存放了表的结构,是一个二进制格式文件。
ibd：独立表空间，存放了表的数据和索引。
表结构就是该表的名称，表里面有多少列，每个列的数据类型、约束条件和索引，使用的字符集和比较规则等各种信息，这些信息都体现在了我们的建表语句中。

MyISAM

每个数据库有四种文件形式：opt、frm、MYD、MYI。
MYD：表数据文件。
MYI：表索引文件。

MySQL8.0

InnoDB

InnoDB存储引擎只有ibd文件。 MySQL8.0将frm文件的信息以及更多信息移动到叫做序列化字典信息(SerializedDictionary Information,SDI)中，SDI在InnoDB下是被写在了ibd文件内部。为了从ibd文件中提取SDI信息，oracle提供了一个应用程序ibd2sdi。 这个工具不需要下载，MySQL8自带的有，只要配置好环境变量即可使用。

MySQL8.0新特性：数据字段
1、 MySQL8.0增加了数据字段缓冲。字典对象高速缓存是一个共享的全局高速缓存，该高速缓存将先前访问的数据字典对象存储在内存中，以支持对象重用并最小化磁盘I /O，字典对象缓存使用基于 LRU的逐出策略从内存中逐出最近最少使用的对象
2、数据字典对象缓存
字典对象高速缓存是一个共享的全局高速缓存，该高速缓存将先前访问的数据字典对象存储在内存中，以支持对象重用并最小化磁盘I /O，字典对象缓存使用基于 LRU的逐出策略从内存中逐出最近最少使用的对象。
3、序列化字典信息（SDI）。
MySQL8.0不仅将元数据信息存储在数据字典表中，同时也冗余存储了一份在SDI中。对于非InnoDB表，SDI数据在后缀为.sdi的文件中，而对于innodb，SDI数据则直接存储与ibd中，主要是基于一个应用程序ibd2sdi。

新数据字典的局限性：
通过手动mkdir的方式在数据目录下创建库目录，这种方式是不会被数据库所识别到。DDL操作会花费更长的时间，因为之前的DDL操作是直接对.frm文件进行更改操作，只要写一个文件，现在是需要更新数据字典表，代表着需要将数据写到存储引擎、read log、undo log中。

其他

在线 DDL：https://juejin.cn/post/7360583337512271882?searchId=202405101639163447C73DA4D09D29DFD7
后续补充变更列说明…
1、查看数据库版本
select version();
2、创建索引
CREATE INDEX index_name ON table_name(column1, column2, …);
3、变更列
ALTER TABLE your_table_name MODIFY COLUMN column_name new_column_definition;

注意

**1、确保你的MySQL版本支持Instant DDL，DDL即时Instant 功能自MySQL 8.0.12版本引入。**此外，变更操作通常是在数据库的表级别执行的，所以在执行这些操作时，请确保选择合适的时机，避免对业务造成影响。
3、变更列长度。Varchar对于小于等于255字节以内的长度可以使用一个byte 存储。大于255个字节的长度则需要使用2个byte存储。转自：https://blog.csdn.net/JSWANGCHANG/article/details/132027990