MySQL 5.7-8.5.7 Optimizing InnoDB DDL Operations

最新推荐文章于 2024-11-10 22:27:03 发布

KING BOMB

最新推荐文章于 2024-11-10 22:27:03 发布

阅读量66

点赞数

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

原文链接：https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-ddl-operations.html

版权

Mysql 专栏收录该内容

142 篇文章 2 订阅

订阅专栏

本文介绍了在MySQL中使用InnoDB存储引擎时，如何高效地执行DDL操作，如CREATE、ALTER和DROP。在线DDL支持允许在不中断服务的情况下添加二级索引，加快表和索引的创建速度。建议使用TRUNCATE TABLE而非DELETE FROM来清空表，尤其当涉及外键约束时。此外，主键应在创建表时一并设定，避免后续修改带来的全表重组成本。

摘要由CSDN通过智能技术生成

Many DDL operations on tables and indexes (CREATE, ALTER, and DROP statements) can be performed online. See Section 14.13, “InnoDB and Online DDL” for details.
对表和索引的许多DDL操作(CREATE、ALTER和DROP语句)都可以在线执行。详见14.13节“InnoDB and Online DDL”。
Online DDL support for adding secondary indexes means that you can generally speed up the process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.
在线DDL支持添加二级索引，这意味着通常可以通过创建没有二级索引的表，然后在加载数据后添加二级索引，从而加快创建和加载表及其关联索引的过程。
Use TRUNCATE TABLE to empty a table, not DELETE FROM tbl_name. Foreign key constraints can make a TRUNCATE statement work like a regular DELETE statement, in which case a sequence of commands like DROP TABLE and CREATE TABLE might be fastest.
使用TRUNCATE TABLE来清空表，而不是DELETE FROM tbl_name。外键约束可以使TRUNCATE语句像常规DELETE语句一样工作，在这种情况下，DROP TABLE和CREATE TABLE等命令序列可能是最快的。
Because the primary key is integral to the storage layout of each InnoDB table, and changing the definition of the primary key involves reorganizing the whole table, always set up the primary key as part of the CREATE TABLE statement, and plan ahead so that you do not need to ALTER or DROP the primary key afterward.
因为主键是不可或缺的每个InnoDB表的存储布局,和改变主键的定义涉及重组整个表,总是设置主键作为CREATE table语句的一部分,和提前计划,这样你不需要改变或删除主键