mysql的性能改进与数据迁移策略

Question

当我们不得不修改包含数十亿行的表中的一列时，如何关注性能？

Answer 1

大表中的DML操作确实是一项非常繁琐的工作，在执行这些操作时需要进行适当的分析和良好的迁移策略。假设在您的MYSQL数据库中，您有一个包含6亿行的巨型表，有一些模式操作，如添加唯一键，更改一列，甚至添加多一列到它是一个非常麻烦的过程，这将需要几个小时的处理，有时会有服务器超时。为了克服这一点，一个人必须提出非常好的迁移计划，我在下面写下其中之一。

1)假设有一个表Orig_X，我必须在其中添加一个默认值为0的新列colNew。

2)创建除了具有新的列colNew之外的作为Orig_X的复制品的虚表Dummy_X。

3)使用以下设置将数据从Orig_X插入到Dummy_X。

4)自动提交被设置为零，这样在每个insert语句之后都不会提交数据，这会影响性能。

5)二进制日志设置为零，因此不会在这些日志中写入任何数据。

6)在插入数据机器人之后，特征被设置为1。

 SET AUTOCOMMIT = 0;
 SET sql_log_bin = 0;

Insert into Dummy_X(col1, col2, col3, colNew)
Select col1, col2, col3, from Orig_X;
SET sql_log_bin = 1;
SET AUTOCOMMIT = 1;

7)现在可以使用新插入的列创建主键，该列现在是主键的一部分。

8)现在可以创建所有唯一密钥。9)我们可以通过发出以下命令来检查服务器的状态

SHOW MASTER STATUS

10)发出刷新日志也很有帮助，这样MySQL就会清除旧日志。

11)为了提高运行类似类型的查询的性能，比如上面的insert语句，应该启用查询缓存变量。

SHOW VARIABLES LIKE 'have_query_cache';
query_cache_type = 1

上面是大表迁移策略的步骤，下面是提高数据库/查询性能的步骤。1)删除表中任何不必要的索引，特别注意唯一索引，因为这些索引在禁用更改缓冲时。如果您没有理由使用唯一索引，请不要使用唯一索引，而应使用常规索引。

2)如果批量加载新表，则延迟创建除主键以外的任何索引。如果在加载数据之后一次性创建它们，那么InnoDB能够应用预排序和大容量加载过程，这既更快，而且通常会产生更紧凑的索引。

3)更大的内存实际上有助于性能优化。如果SHOW ENGINE INNODB STATUS显示缓冲池和内存下的任何读取/秒，并且空闲缓冲区的数量(也在缓冲池和内存下)为零，那么您可以从更多的读取中受益(假设您已经在服务器上正确地调整了innodb_buffer_pool_size的大小)。

4)通常，您的数据库表在每次插入后都会被重新索引。对于您的数据库来说，这是一些繁重的工作，但是当您的查询被包装在一个事务中时，直到处理完整个块之后，表才会被重新索引。节省了大量的工作。

5)大多数MySQL服务器都启用了查询缓存。这是提高性能的最有效方法之一，由数据库引擎悄悄处理。当同一查询被多次执行时，将从缓存中获取结果，这是相当快的。

6)使用EXPLAIN关键字可以让您深入了解MySQL正在做什么来执行查询。这可以帮助您发现查询或表结构中的瓶颈和其他问题。EXPLAIN查询的结果将显示哪些索引正在被使用，表是如何被扫描和排序的，等等。

7)如果您的应用程序包含许多联接查询，则需要确保您联接的列在两个表上都有索引。这会影响MySQL在内部优化联接操作的方式。

8)在每个表中都有一个id列，它是主键、AUTO_INCREMENT和一种INT风格。也最好是无符号的，因为该值不能是负数。

9)即使您的用户表具有唯一的username字段，也不要将其作为主键。作为主键的VARCHAR字段速度较慢。通过在内部引用所有用户的id，您将在代码中有一个更好的结构。

10)通常情况下，当您从脚本执行查询时，它将等待该查询的执行完成，然后才能继续。您可以通过使用无缓冲查询来更改这一点。这节省了产生大型结果集的SQL查询的大量内存，而且您可以在检索到第一行之后立即开始处理结果集，因为您不必等到执行完完整的SQL查询。

11)对于数据库引擎，磁盘可能是最重要的瓶颈。保持设备更小、更紧凑通常有助于提高性能，从而减少磁盘传输量。

12) MySQL中的两个主要存储引擎是MyISAM和InnoDB。每种方法都有自己的优点，cons.MyISAM对读取繁重的应用程序很好，但当有大量写入时，它的伸缩性不是很好。即使您正在更新一行中的一个字段，整个表也会被锁定，并且在该查询完成之前，任何其他进程都无法读取该表。MyISAM在计算SELECT COUNT(*)时速度非常快，queries.InnoDB的类型往往是一个更复杂的存储引擎，对于大多数小型应用程序来说，它可能比MyISAM慢。但它支持基于行的锁定，可伸缩性更好。它还支持一些更高级的功能，如事务。

Answer 2

pt-online-schema-change就是一个这样的工具。