当前位置:首页 > 编程教程 > mysql技术文章 > MySQL中修改表结构时需要注意的一些地方

关于MySQL中修改表结构时注意事项

  • 发布时间:
  • 作者:码农之家
  • 点击:103

这篇文章主要知识点是关于MySQL、表结构、的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

PHP和MySQL Web开发
PHP和MySQL Web开发中文扫描第4版
  • 类型:PHP网站开发大小:51.6 MB格式:PDF作者:威利,汤姆森
立即下载

Tags:MySQL 表结构 

MySQL中修改表结构时需要注意的一些地方

MySql 在修改表结构的时候可能会中断产品的正常运行影响用户体验,甚至更坏的结果,丢失数据。不是所有的数据库管理员、程序员、系统管理员都非常了解Mysql能避免这种情况。DBA会经常碰到这种生产中断的情况,当升级脚本修改了应用层和数据库层,或者缺乏经验的管理员、开发在不是很了解Mysql内部工作机制的情况下修改了规范文件。

真相是:

  • 直接修改表结构的过程中会锁表(在5.6版本之前)
  • 在线的数据定义语言在5.6版本不总是在线的而且也会锁表
  • 就算使用Percona工具包(在线修改定义文件)也会有若干个步骤会锁表

Percona MySQL  服务器开发团队鼓励用户在计划或者执行数据库迁移的时候先和我们沟通。我们的目标是基于用户给出的各种情况给出最佳的方案。旨在避免锁表当用户对非常大的表执行DDL,以确保应用能像平常一样正常运行,同时也在努力改善响应时间或增加系统功能。最差的情况是确保那些经不起当机的系统在黄金交易时间正常运行。

我们使用的大多数安装包仍然小于Mysql5.6,这需要我们不停尝试新的安装环境来把数据库迁移造成的损失降到最低。这可能需要一个能“在线修改规范定义文件”的工具来升级或者修改规范文件。Mysql5.6解决这一问题的做法是通过减少重建表和锁表的场景,但这个方法不能覆盖所有的可能的操作,例如当修改一列的数据类型时必然需要全表重构。Przemys?aw和 Malkowski在去年尽可能详尽的讨论了Mysql5.6运行中修改定义。

  •     随着 MySQL 5.7的新功能, 我们寻求不会锁表的DDL操作 例如; 表优化 和 索引重命名. (More info)


对于Mysql5.6的用户,最好的建议是回顾一下数矩阵来熟悉在MYSQL之外执行定义的更改,好消息是我们很擅长解决这一问题。

说实话,锁表操作会经常被忽视,在操作30M大小的表时我们更倾向于直接修改,但是30G,300G的表就要考虑一下了。当使用率不高或者对锁定时间要求不是很高的的系统来说直接操作也许更好。可是,我们常常会遇到一个需要立即执行的SQL,或者因为性能问题需要紧急增加一个索引来减少加载时间。


是否需要在系统在线期修改表定义

上面提到,在线修改表定义是工作流中的一个模块。通常是不错的解决方案,但也会遇到不能使用的场合,例如:当某个表使用了触发器。了解pt-osc在我们项目中的工作过程很重要,让我们来看一下源代码:
 

复制代码 代码如下:
[moore@localhost]$ egrep 'Step' pt-online-schema-change
# 步骤 1: 创建一个新表
 
# 步骤 2: 修改清空表. 这应该比较快,
# Step 3: 创建触发器来捕获原始表的改变 <--(锁定元数据)
 
# Step 4: 复制数据.
# Step 5: 重命名表: <--(锁定元数据
 
# Step 6: 更新外键 如果是子表.
 
# Step 7: 删除旧表.

 

 我把上面第三步到第五步高亮出来,这是锁表可能引起系统停机的时间。但步骤六设计外键更新是一个循环的操作,是避免在更新关系的时候隐含地重建表。有很多方法可以确保表的完整性约束,在pt-osc的说明文档中详细说明了,在开始之前预览你的表结构包括约束,并知道怎样把修改表定义所造成的影响降到最低。


最近,我们通知了一个拥有高并发高事务量系统的用户运行pt-osc在大型数据表上。这件事对于他们来说很平常,几小时后我们的客服被告知该客户遇到了最大连接数超过的问题。这个问题是如何产生的呢?当pt-osc运行到步骤五的时候会尝试去锁定数据并重命名原表和隐藏表,然而这不会在开启事务的时候立即执行,因此这条线程会被排在重命名后面。这表现在用户应用上就是系统停机。数据库无法开启新的连接并且所有的线程都被阻塞在重命名命令之后。

 

MySQL中修改表结构时需要注意的一些地方    5.5.3版本的说明,当开启一个事务时会锁定它会用到的所有表的数据(不依赖于存储引擎),并在事务提交的时候释放锁。这样做确保了在开启事务期间不能修改表的定义。


长远来看我们可以采用一些新的技术来避免这种情况,例如non-default pt-osc的选项,换言之就是不会删除原表把数据换到新表。这种联合脱离了隐藏表和触发器,我们应该鼓励将重命名操作变得原子化。

校订:2.2版本的percona工具新增了一个变量–tries  和变量–set-vars 共同被部署,解决了各种pt-osc操作可能会锁表的情况。pt-osc (–set-vars)默认会设置如下的会话变量当连接到数据库服务器的时候。

   

复制代码 代码如下:
wait_timeout=10000
    innodb_lock_wait_timeout=1
    lock_wait_timeout=60

 


当使用 –tries 我们可以颗粒化地鉴别操作,尝试次数、在尝试的间隔等待。这种组合可以确保pt-osc在合适的时机杀掉自己的等待会话进程,确保线程堆栈的空闲,并提供给我们循环操作来获取管理因触发器、重命名、修改外键而造成的锁。

   

复制代码 代码如下:
–tries swap_tables:5:0.5,drop_triggers:5:0.5

 

说明文档在这里http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change–tries

它阐述了即便使用了诸如pt-osc之类的工具,充分了解你想解决的问题是很重要。下面的流程图会帮助你当你了解修改了MYSQL数据库的结构的注意事项。请仔细阅读建议尽管有些图上未标出,例如磁盘空间,IO加载等。

MySQL中修改表结构时需要注意的一些地方

选择合适的DDL操作

确保能清楚了解在修改表结构对你的系统会产生何种影响,并选择合适的方法来使这种影响降到最低。有时这意味着需要将改动延期直到系统到了不常使用的时候或者使用能在操作期间不锁表的工具。当你表中有触发器的时候一般直接修改表结构。

  • -大多数情况下pt-osc正是我们所需要的
  • -在很多案例中pt-osc是需要的,但是用法需要稍作调整
  • -在少数情况下pt-osc不是很合适,我们需要考虑本地阻塞修改,或者采用转移的操作改成在副本集中复制。

 

以上就是本次给大家分享的关于java的全部知识点内容总结,大家还可以在下方相关文章里找到相关文章进一步学习,感谢大家的阅读和支持。

mysql 相关电子书
学习笔记
网友NO.347287

MySQL利用procedure analyse()函数优化表结构

简介 procedure analyse() 函数是MySQL内置的对MySQL字段值进行统计分析后给出建议的字段类型。 语法 procesure analyse(max_elements,max_memory) max_elements 指定每列非重复值的最大值,当超过这个值的时候,MySQL不会推荐enum类型。 max_memory analyse() 为每列找出所有非重复值所采用的最大内存大小。 实战演练 # 对t1表所有的列进行分析wing@3306show create table t1;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(16) DEFAULT NULL, `score` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-----------------------------------------……

网友NO.822127

MySQL修改表结构操作命令总结

表的结构如下: 复制代码 代码如下: mysql show create table person; | person | CREATE TABLE `person` ( `number` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `birthday` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 删除列: 复制代码 代码如下: ALTER TABLE person DROP COLUMN birthday; 添加列: 复制代码 代码如下: ALTER TABLE person ADD COLUMN birthday datetime; 修改列,把number修改为bigint: 复制代码 代码如下: ALTER TABLE person MODIFY number BIGINT NOT NULL; 或者是把number修改为id,类型为bigint: 复制代码 代码如下: ALTER TABLE person CHANGE number id BIGINT; 添加主键: 复制代码 代码如下: ALTER TABLE person ADD PRIMARY KEY (id); 删除主键: 复制代码 代码如下: ALTER TABLE person DROP PRIMARY KEY; 添加唯一索引: 复制代码 代码如下: ALTER TABLE person ADD UNIQUE name_unique_index (`name`); 为name这一列创建了唯一索引,索引的名字是name_unique_index. 添加普通索引……

网友NO.422071

mysql mysqldump只导出表结构或只导出数据的实现方法

mysql mysqldump 只导出表结构 不导出数据 mysqldump --opt -d 数据库名 -u root -p xxx.sql 备份数据库 #mysqldump 数据库名 数据库备份名 #mysqldump -A -u用户名 -p密码 数据库名数据库备份名 #mysqldump -d -A --add-drop-table -uroot -p xxx.sql 1.导出结构不导出数据 mysqldump --opt -d 数据库名 -u root -p xxx.sql 2.导出数据不导出结构 mysqldump -t 数据库名 -uroot -p xxx.sql 3.导出数据和表结构 mysqldump 数据库名 -uroot -p xxx.sql 4.导出特定表的结构 mysqldump -uroot -p -B 数据库名 --table 表名 xxx.sql 导入数据: 由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了: #mysql 数据库名 文件名 #source /tmp/xxx.sql ……

网友NO.568880

mysql 复制表结构和数据实例代码

在mysql数据库开发中,我们有时候需要复制或拷贝一张表结构和数据到例外一张表,这个时候我们可以使用create ... select ... from语句来实现,本文章向大家介绍mysql复制表结构和数据一个简单实例, 比如现在有一张表,我们要将该表复制一份,以备以后使用,那么如何使用mysql语句来实现呢?其实我们可以直接使用create ... select ... from语句来实现,具体实现方法请看下面实例。 我们先来创建一张Topic表,创建Topic表的SQL语句如下: mysql CREATE TABLE Topic( - TopicID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, - Name VARCHAR(50) NOT NULL, - InStock SMALLINT UNSIGNED NOT NULL, - OnOrder SMALLINT UNSIGNED NOT NULL, - Reserved SMALLINT UNSIGNED NOT NULL, - Department ENUM('Classical', 'Popular') NOT NULL, - Category VARCHAR(20) NOT NULL, - RowUpdate TIMESTAMP NOT NULL - ); 向Topic表中插入数据: mysql INSERT INTO Topic (Name, InStock, OnOrder, Reser……

<
1
>

电子书 编程教程 PC软件下载 安卓软件下载

Copyright 2018-2020 xz577.com 码农之家

本站所有电子书资源不再提供下载地址,只分享来路

免责声明:网站所有作品均由会员网上搜集共同更新,仅供读者预览及学习交流使用,下载后请24小时内删除

版权投诉 / 书籍推广 / 赞助:QQ:520161757