MySQL中ALTER TABLE时的性能问题

  当对于一个大表进行ALTER TABLE的时候,性能问题就产生了。MySQL大部分改动的步骤如下:根据新的表结构创建一个空表,从旧表中把数据取出来插入到新表中,在删除旧表。这是个非常漫长的过程。许多人ALTER TABLE之后,都有等待1小时或者1天的痛苦经历。

  MySQL AB已经开始提升这方面的性能了。一些即将到来的特性是支持"在线"的操作,而不会去锁定表。InnoDB的开发者也在积极努力开发以排序来创建索引。MyISAM已经支持这一特性了,结果就是索引更快并且压缩了索引布局。

  并不是所有的ALTER TABLE都会使表重建。举个例子,你可以用两个方法更改或者删除列默认值(一个快,一个慢)。如果你想更改一个film的租赁期限为原来的3天到现在的5天。方法如下:

  mysql> ALTER TABLE sakila.film
  -> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

  用SHOW  STATUS来监控这个语句,它做了1000次读和1000次插入。换句话说就是复制一个表到新表中。即使这个列的类型,大小,是否为NULL都没有改变。

  原理上来说,MySQL可以跳过创建新表。这个默认的值实际存储在.frm文件中。因此你可以更改它而没有必要接触表。MySQL也不会做优化,然而任何的MODIFY COLUMN都会导致表的重建。

  你可以使用ALTER COLUMN来修改:

  mysql> ALTER TABLE sakila.film
  -> ALTER COLUMN rental_duration SET DEFAULT 5;

  这个语句修改了.frm文件而没有去操作表。结果就是速度非常之快。

  仅仅修改.frm文件

  我们发现修改标的.frm文件速度非常快并且当它不能那么做的时候,MySQL有的时候会重建表。如果你愿意承担一部分风险,你可以告诉MySQL做一些类型的修改而不去重建表。

  你可以不用重建表来做如下类型的操作:

  移除列的AUTO_INCREMENT属性。

  添加,移除,更改ENUM和SET。如果你移除了一个常量并且一些行包含这个值,查询语句返回这个值将会是空字符串

  基本的技术就是创建一个.frm文件并且拷贝它到以存在表的.frm文件的位置。步骤如下:

  创建一个空表,当然表布局必须是准确的。除了一些更改的项。

  执行FLUSH TABLES WITH READ LOCK.这一步将关闭使用的所有的表并且防止了这些表被打开。

  交换.frm文件。

  执行UNLOCK TABLES释放读锁。

  为了做例子,我们给skila.film表的rating列添加一个常量。当前的列如下:

  mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating';
  +--------+------------------------------------+------+-----+---------+-------+
  | Field  | Type                               | Null | Key | Default | Extra |
  +--------+------------------------------------+------+-----+---------+-------+
  | rating | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
  +--------+------------------------------------+------+-----+---------+-------+

  我们添加一个PG-14到这列中。

  mysql> CREATE TABLE sakila.film_new LIKE sakila.film;
  mysql> ALTER TABLE sakila.film_new
  -> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14')
  -> DEFAULT 'G';
  mysql> FLUSH TABLES WITH READ LOCK;

  注意一下我们实在最后添加这个PG-14的,而不是在中间,那样做的话就会修改现有的值了,R值变为PG-14,NC-17变为R依此类推。

  现在连交换.frm文件,操作系统命令如下

  root:/var/lib/mysql/sakila# mv film.frm film_tmp.frm
  root:/var/lib/mysql/sakila# mv film_new.frm film.frm
  root:/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

  在回到MySQL提示符,我们要解锁,再来看看更改的结果。

  mysql> UNLOCK TABLES;
  mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating'G
  *************************** 1. row ***************************
  Field: rating
  Type: enum('G','PG','PG-13','R','NC-17','PG-14')

  最后一步要做的就是删除我们已创建的表。

  mysql> DROP TABLE sakila.film_new;

  快速的创建MyISAM索引

  高效的读取MyISAM表一般的技巧是,关闭键,读取数据,重新启用键。

  mysql> ALTER TABLE test.load_data DISABLE KEYS;
  -- load the data
  mysql> ALTER TABLE test.load_data ENABLE KEYS;

  这样可以工作的原因是MyISAM延迟了创建键值直到数据读取之后,重要的是,它可以有序的创建索引。结果就非床快并且无碎片和压缩的索引树。

  不过,对于唯一索引这种方法就不行了。因为DISABLE KEYS仅仅应用于非唯一的索引。MyISAM在内存中创建唯一索引并且读取每一行来校验唯一性。一旦索引大小超出了内存大小,读取会极度缓慢。

  前一部分所说的ALTER TABLE的技巧,可以加速这个过程,前提是你需要多做一点工作和承担一部分风险。这对于备份来说很有用。比如,当你发现所有的数据是无效的并且不需要做唯一性检查。

  你需要做的步骤如下:

  创建一个期望的表。但是不要有任何的索引。

  读取数据来创建MYD文件。

  创建另一个空表,这次要包含索引。这会创建.frm和.MYI文件。

  用个读锁来刷新表。

  对第二个表的.frm和.MYI进行重命名。因此MySQL会把它们当作第一个表使用。

  释放读锁。

  使用REPAIR TABLE来创建表的索引。会以排序的方式创建索引,包括了唯一索引。

  这个方法对于大表来说,速度格外的快。

posted on 2010-03-11 21:13  一江水  阅读(3874)  评论(0编辑  收藏  举报