Schema的优化和索引 - 加速ALTER TABLE

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;

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!