MYSQL大表改字段慢问题的解决

骑猪看日落 2024-03-23 20:51 30阅读 0赞

![cf1925f6ee5a30d4cd4a56d5f82bcb0f.jpeg][]

本文介绍了MYSQL大表改字段慢问题的解决，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

Mysql如何加快大表的ALTER TABLE操作速度

MYSQL的ALTER TABLE操作的性能对大表来说是个大问题。MYSQL执行大部分修改表结构操作的方法是用新的表结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。

一般而言，大部分ALTER TABLE操作将导致MYSQL服务中断。对常见的场景，能使用的技巧只有两种：

*  一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；
 *  另外一种技巧就是“影子拷贝”。影子拷贝技巧是用要求的表结构创建一张新表，然后通过重命名和删表操作交换两张表。

不是所有的ALTER TABLE操作都会引起表重建。例如，有两种方法可以改变或删除一个列的默认值（一种方法很快，另一种则很慢）。

假如要修改电影的默认租赁期限，从三天改到五天。下面是很慢的方式：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql&gt; </code><code>ALTER</code> <code>TABLE</code> <code>film </code><code>modify</code> <code>column</code> <code>rental_duration tinyint(3) </code><code>not</code> <code>null</code> <code>default</code> <code>5;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

SHOW STATUS显示这个语句做了1000次读和1000次插入操作。换句话说，它拷贝了整张表到一张新表，甚至列的类型、大小和可否为null属性都没有改变。

理论上，MYSQL可以跳过创建新表的吧步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。然而MYSQL还没有采用这种优化的方法，所以MODIFY COLUMN操作都将导致表重建。

另外一种方法是通过ALTER COLUMN操作来改变列的默认值；

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql&gt; </code><code>ALTER</code> <code>TABLE</code> <code>film </code><code>ALTER</code> <code>COLUMN</code> <code>rental_duration </code><code>set</code> <code>DEFAULT</code> <code>5;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

这个语句会直接修改.frm文件而不涉及表数据。所以这个操作是非常快的。

### 只修改.frm文件 ###

从上面的例子我们看到修改表的.frm文件是很快的，但MYSQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MYSQL做一些其他类型的修改而不用重建表。

注意 下面要演示的技巧是不受官方支持的，也没有文档记录，并且也可能不能正常工作，采用这些技术需要自己承担风险。>建议在执行之前首先备份数据！

下面这些操作是有可能不需要重建表的：

*  移除（不是增加）一个列的AUTO\_INCREMENT属性。
 *  增加、移除，或更改ENUM和SET常亮。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串。

步骤：

*  创建一张有相同结构的空表，并进行所需要的修改(例如：增加ENUM常量)。
 *  执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。
 *  交换.frm文件。
 *  执行UNLOCK TABLES 来释放第二步的读锁。

下面以给film表的rating列增加一个常量为例来说明。当前列看起来如下：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql&gt; SHOW COLUMNS </code><code>FROM</code> <code>film </code><code>LIKE</code> <code>'rating'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <th>Field</th> 
   <th>Type</th> 
   <th>Null</th> 
   <th>Key</th> 
   <th>Default</th> 
   <th>Extra</th> 
  </tr> 
  <tr> 
   <td>rating</td> 
   <td>enum('G','PG','PG-13','R','NC-17')</td> 
   <td>YES</td> 
   <td></td> 
   <td>G</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

假设我们需要为那些对电影更加谨慎的父母们增加一个PG-14的电影分级：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>mysql&gt; </code><code>CREATE</code> <code>TABLE</code> <code>film_new </code><code>like</code> <code>film;</code></p> <p><code>mysql&gt; </code><code>ALTER</code> <code>TABLE</code> <code>film_new </code><code>modify</code> <code>column</code> <code>rating ENUM(</code><code>'G'</code><code>,</code><code>'PG'</code><code>,</code><code>'PG-13'</code><code>,</code><code>'R'</code><code>,</code><code>'NC-17'</code><code>,</code><code>'PG-14'</code><code>) </code><code>DEFAULT</code> <code>'G'</code><code>;</code></p> <p><code>mysql&gt; FLUSH TABLES </code><code>WITH</code> <code>READ</code> <code>LOCK;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

注意，我们是在常量列表的末尾增加一个新的值。如果把新增的值放在中间，例如：PG-13之后，则会导致已经存在的数据的含义被改变：已经存在的R值将变成PG-14，而已经存在的NC-17将成为R，等等。

接下来用操作系统的命令交换.frm文件：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>/var/lib/mysql/sakila# mv film.frm film_tmp.frm</code></p> <p><code>/var/lib/mysql/sakila# mv film_new.frm film.frm</code></p> <p><code>/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm</code></p> </td> 
  </tr> 
 </tbody> 
</table>

再回到Mysql命令行，现在可以解锁表并且看到变更后的效果了：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>mysql&gt; UNLOCK TABLES;</code></p> <p><code>mysql&gt; SHOW COLUMNS </code><code>FROM</code> <code>film </code><code>like</code> <code>'rating'</code><code>\G</code></p> </td> 
  </tr> 
 </tbody> 
</table>

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

> Field: rating  
> Type: enum('G','PG','PG-13','R','NC-17','PG-14')

最后需要做的是删除为完成这个操作而创建的辅助表：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql&gt; </code><code>DROP</code> <code>TABLE</code> <code>film_new;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

到此这篇关于MYSQL大表改字段慢问题的解决的文章就介绍到这了,希望帮到你。

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][Link 1]

[cf1925f6ee5a30d4cd4a56d5f82bcb0f.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/19a4e1085b9846edb1fa0457973bf0b7.png
[Link 1]: https://www.weidianyuedu.com