• 如何最快地实现 ALTER TABLE


    如果您不了解ALTER TABLE的语法,可以先参考:

    http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

    使用ALTER TABLE 可以修改一张表的结构。比如,添加或者删除一个字段,创建或者删除一个索引,修改一个字段的类型,或者重命名一个字段,或者表的一些元信息。我们还可以通过ALTER TABLE来修改表引擎。

    在大多数情况下,ALTER TABLE通过建立一个原表的拷贝,并在拷贝上修改,然后删除原表,最后修改拷贝的名称为原表名来实现。在执行ALTER TABLE的时候是不能更新(update or insert)数据的, 但是其他的session是可以read的。

    下面来具体看看如何最快的执行ALTER TABLE(这儿不考虑 分区/MySQL Cluster/表空间)

    在某些特定的情况下,ALTER TABLE 是不需要建立临时拷贝的,所以是很快的。

    1. 重命名一个字段或者索引

    2. 修改一个字段的默认值

    3. 添加新的枚举值到enum的末尾 或者 添加新的set值到set的末尾

    4. 仅仅重命名表,而不修改其余任何参数

    其他的情况,MySQL会建立一个临时表来执行ALTER TABLE。

    对于MyISAM表,增大如下的变量可以加快ALTER TABLE的执行过程。

    1. myisam_sort_buffer_size MySQL会在REPAIR TABLE索引排序或者使用ALTER TABLE/CREATE INDEX创建索引的时候申请缓冲的大小。

    2. myisam_max_sort_file_size MySQL在使用REPAIR TABLE/ALTER TABLE/LOAD DATA重建MyISAM索引的时候允许申请的最大临时文件的大小,如果超过这个大小,MySQL将会使用很慢的key cache。

    对于MyISAM表,还可以通过批量的建立索引来加快整个ALTER TABLE执行过程。通过在ALTER TABLE之前 运行 ALTER TABLE ... DISABLE KEYS/在ALTER TABLE之后运行ALTER TABLE ... ENABLE KEY来激活这个特性。但是个该特性对于主键和唯一索引不起作用。

    在数据量比较大的时候,采用dump data/drop table/recreate table/load data的方式来修改表结构,会比ALTER TABLE快很多,当然这要看具体的应用,因为ALTER TABLE的过程中,可以提供继续read, 而采用dump data的方式则不行。

    根据

    http://everythingmysql.ning.com/profiles/blogs/whats-faster-than-alter

    上的测试结果,80W条记录,约5G的数据量,采用dump data可以比ALTER TABLE直接修改快47%.

    采用dump data的方式基本步骤如下:

    1. 使用 select * from tablename into outfile "/tmp/data"; 保存数据到临时文件 /tmp/data

    2. drop table tablename;

    3. create table tablename (...);

    4. 如果是 MyISAM, alter table tablename disable keys;

    5. load data infile "/tmp/data" into tablename;

    6. 如果是 MyISAM, alter tabe tablename enable keys;

    最后,需要特别说明一下,有些时候,不一定需要最快,但应该选择最适合、最可靠的方式。

    参考:

    1. http://venublog.com/2010/01/04/performance-comparison-of-repair-by-sorting-or-by-keycache/

    2. http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

    3. http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

    4. http://everythingmysql.ning.com/xn/detail/3993569%3ABlogPost%3A171

  • 相关阅读:
    转 子查询
    260@365
    线程池
    转 nio    netty
    正则表达式匹配标签内的内容
    express接受ajax的发送post请求
    如何查看代码使用率
    ajax传文件用express的multer接住
    ajax 的post方法 的content-type设置和express里应用body-parser
    webpack4 es6转换
  • 原文地址:https://www.cnblogs.com/xiaotengyi/p/3639602.html
Copyright © 2020-2023  润新知