Table of Contents
回复内容:
Home Backend Development PHP Tutorial [Mysql]一张1000W条记录的表中,修改表结构,为一个enum字段增加一个值,需要多久时间?

[Mysql]一张1000W条记录的表中,修改表结构,为一个enum字段增加一个值,需要多久时间?

Jun 06, 2016 pm 08:49 PM
java linux mysql php

Mysql5.5 表为InnoDB,字段为名为type,为普通字段,不被包含在任何索引/主键中。

type字段本来拥有两个值 'a' , 'b' ,我现在要为其加上值 'c' ,于是有了修改语句:

<code>ALTER TABLE `table` MODIFY COLUMN `type` enum('a','b','c');
</code>
Copy after login
Copy after login

我现在想知道我执行上面这条语句大约要消耗多久的时间?

我主要是想知道因为该字段不涉及索引,修改所消耗的时间跟该表的1000w记录有关系吗

大家帮我预估一下,我好有个停机时间的计划准备,谢谢!

回复内容:

Mysql5.5 表为InnoDB,字段为名为type,为普通字段,不被包含在任何索引/主键中。

type字段本来拥有两个值 'a' , 'b' ,我现在要为其加上值 'c' ,于是有了修改语句:

<code>ALTER TABLE `table` MODIFY COLUMN `type` enum('a','b','c');
</code>
Copy after login
Copy after login

我现在想知道我执行上面这条语句大约要消耗多久的时间?

我主要是想知道因为该字段不涉及索引,修改所消耗的时间跟该表的1000w记录有关系吗

大家帮我预估一下,我好有个停机时间的计划准备,谢谢!

最近在看高性能mysql(第三版),里面讲到了这个。 modify column会导致重建表(内部实现是建一个最新结构的表,然后导入数据,然后删除旧表,重命名新表为旧表名),所以会比较慢 使用alter table xxx alter column 则是直接修改.frm文件所以会很快。跟表数据有多少无关。

另一个做法其实原理一样,都是修改.frm文件: 1.创建一个新机构的新表 2.flush tables with read lock.关闭正在使用的表,并且禁止任何表被打开。 3.交换.frm文件 4.unlock tables释放第二步的锁。

最近几晚上看的,没亲自试过,话说我也该去测试下了

<code>static long getAlterTable(){

if(数据库没备份){
  do备份
  getAlterTable(); 
 }else{
   return test alter table time
 }
};
</code>
Copy after login

可以看下percona的在线修改表结构。支持在线修改。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Nordhold: Fusion System, Explained
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1667
14
PHP Tutorial
1273
29
C# Tutorial
1255
24
What is the significance of the session_start() function? What is the significance of the session_start() function? May 03, 2025 am 12:18 AM

session_start()iscrucialinPHPformanagingusersessions.1)Itinitiatesanewsessionifnoneexists,2)resumesanexistingsession,and3)setsasessioncookieforcontinuityacrossrequests,enablingapplicationslikeuserauthenticationandpersonalizedcontent.

Composer: The Package Manager for PHP Developers Composer: The Package Manager for PHP Developers May 02, 2025 am 12:23 AM

Composer is a dependency management tool for PHP, and manages project dependencies through composer.json file. 1) parse composer.json to obtain dependency information; 2) parse dependencies to form a dependency tree; 3) download and install dependencies from Packagist to the vendor directory; 4) generate composer.lock file to lock the dependency version to ensure team consistency and project maintainability.

What are the advantages of using MySQL over other relational databases? What are the advantages of using MySQL over other relational databases? May 01, 2025 am 12:18 AM

The reasons why MySQL is widely used in various projects include: 1. High performance and scalability, supporting multiple storage engines; 2. Easy to use and maintain, simple configuration and rich tools; 3. Rich ecosystem, attracting a large number of community and third-party tool support; 4. Cross-platform support, suitable for multiple operating systems.

What are the advantages of using Java for web applications that need to run on different servers? What are the advantages of using Java for web applications that need to run on different servers? May 03, 2025 am 12:13 AM

Java is suitable for developing cross-server web applications. 1) Java's "write once, run everywhere" philosophy makes its code run on any platform that supports JVM. 2) Java has a rich ecosystem, including tools such as Spring and Hibernate, to simplify the development process. 3) Java performs excellently in performance and security, providing efficient memory management and strong security guarantees.

What are the typical use cases for Linux versus Windows? What are the typical use cases for Linux versus Windows? May 03, 2025 am 12:01 AM

Linuxisidealforcustomization,development,andservermanagement,whileWindowsexcelsineaseofuse,softwarecompatibility,andgaming.Linuxoffershighconfigurabilityfordevelopersandserversetups,whereasWindowsprovidesauser-friendlyinterfaceandbroadsoftwaresupport

Linux Operations: Understanding the Core Functionality Linux Operations: Understanding the Core Functionality May 03, 2025 am 12:09 AM

Linux is a Unix-based multi-user, multi-tasking operating system that emphasizes simplicity, modularity and openness. Its core functions include: file system: organized in a tree structure, supports multiple file systems such as ext4, XFS, Btrfs, and use df-T to view file system types. Process management: View the process through the ps command, manage the process using PID, involving priority settings and signal processing. Network configuration: Flexible setting of IP addresses and managing network services, and use sudoipaddradd to configure IP. These features are applied in real-life operations through basic commands and advanced script automation, improving efficiency and reducing errors.

How does platform independence simplify deployment of Java applications? How does platform independence simplify deployment of Java applications? May 02, 2025 am 12:15 AM

Java'splatformindependenceallowsapplicationstorunonanyoperatingsystemwithaJVM.1)Singlecodebase:writeandcompileonceforallplatforms.2)Easyupdates:updatebytecodeforsimultaneousdeployment.3)Testingefficiency:testononeplatformforuniversalbehavior.4)Scalab

What role does Java play in the development of IoT (Internet of Things) devices, considering platform independence? What role does Java play in the development of IoT (Internet of Things) devices, considering platform independence? May 03, 2025 am 12:22 AM

JavaplaysasignificantroleinIoTduetoitsplatformindependence.1)Itallowscodetobewrittenonceandrunonvariousdevices.2)Java'secosystemprovidesusefullibrariesforIoT.3)ItssecurityfeaturesenhanceIoTsystemsafety.However,developersmustaddressmemoryandstartuptim

See all articles