MySQL两千万数据优化&迁移

最近有一张2000W条记录的数据表需要优化和迁移。2000W数据对于MySQL来说很尴尬，因为合理的创建索引速度还是挺快的，再怎么优化速度也得不到多大提升。不过这些数据有大量的冗余字段和错误信息，极不方便做统计和分析。所以我需要创建一张新表，把旧表中的数据一条一条取出来优化后放回新表；

一. 清除冗余数据，优化字段结构

2000W数据中，能作为查询条件的字段我们是预知的。所以将这部分数据单独创建新的字段，对于有规则的数据合理改变字段结构，比如身份证就是varchar(18)。对于不重要的数据我们合并后存在一个结构为text的字段。

对于一些有关联的数据我们需要计算，常见的比如身份证种能获取到准确的性别，出生地、生日、年龄。

二. 数据迁移

我们从数据库中取出一条旧数据，再通过计算处理后得到想要的新数据，最后将新数据插入新表。不过在获取新数据时遇到如下问题。

数据量太大，无法一次获取（2000W数据扔到内存挺可怕的）；

我们可以通过MySQL的limit语法分批获取。比如每次获取50000，SQL语句如下：

select * from table_name limit 15000000,50000;

通过这种方法能解决数据量太大的问题，但是随着limit的第一个参数越来越大，查询速度会慢的吓人（上面这条SQL执行会花35秒）。时间就是生命，于是我们开始优化SQL语句，优化后变成下面这样：

select * from table_name order by id desc limit 5000000,50000;

可通过二分法拆分2000W数据，当执行到1000W数据时，将数据倒序。优化后SQL执行效率显著提升，从35秒降到9秒；

不过还是很慢，时间就是生命……还好我们有自增ID（创建数据表第一条定律，一定要有自增字段），优化后的SQl如下：

1. select * from table_name where id 15000000 and id 15050000；

2. select * from table_name where id 15000000 limit 50000; 

为了直观演示，我写了两条功能一样的SQL。相比第一条，第二条的limit会导致SQL的索引命中变差，效率同样也会下降。第一条SQL的执行时间是2毫秒，第二条执行时间5毫秒（我取的平均值）。每次数据的查询速度直接从35秒降到2毫秒……

数据量太大并且数据无法预估，某些特殊数据会导致数据导入失败；

我们有三种方案去将新数据存入新表，分别如下：

一条一条插入数据；

开始肯定会想这种方案一定不行，因为每次插入都会有一次数据库IO操作。但是该方案有个好处是能及时发现有问题的数据，修改后再继续执行； 在Oracle中使用『绑定变量』能带来性能提升，正好MySQL也提供了『绑定变量』的功能。于是在不改变逻辑的情况下，尝试优化数据存储速度。代码如下：

public function actionTest(array $data)

 $mysqli = new mysqli("192.168.1.106", "username", "password", "test");

 $sql = "insert into table_name(name,identity) values (?,?)";

 $stmt = $connection- prepare($sql);

 $name = "";

 $identity = "";

 //使用绑定变量

 $stmt- bind_param("si", $name, $identity);

 foreach($data as $val)

 $name = $val[name];

 $identity = $val[card_id];

 //执行

 $stmt- execute();

 $stmt- close();

}

最后效果不怎么好，MySQL的『绑定变量』并没带来明显的速度提升，不过能有效的防止SQL注入；

一次插入50000条数据；

这是我最后选中的方案，一是能及时发现有问题的数据，二是导入数据非常稳定。就像支持断点续传一样，每一步都能看到效果。在执行脚本时，也能同步开始写分析逻辑；

组装成SQL文件，最后统一导入；

组装一个大的SQL文件，最后通过MySQL自带的工具导入也是极好的。但如果有一条SQL有问题，你可能需要重跑一次脚本。因为在9G大小的文本文件中修改一个符号是很痛苦的事情……

三. 总结

通过各种优化，最后将脚本执行时间缩短到了20分钟内。优化后数据质量得到了较高保证，下次将尝试2亿数据的优化 迁移……

PS：原文地址 http://blog.it2048.cn/article_2000w-data.html 之后我会陆续把自己博客迁移到云栖社区，希望大家关注！

华为大佬的“百万级”MySQL笔记，基础+优化+架构一键搞定 MySQL不用多说，大家都知道它是目前最为活跃热门的开源数据库，由于成本低，操作简易的特点，所以在互联网企业中被广泛使用，即使是头部的BATJ。由此可见，想要在互联网行业混得风生水起，或者说想要进入BATJ等一线互联网公司，那么熟练掌握MySQL必定是一块必要的敲门砖。
我又吊打面试官了，凭借MySQL海量数据优化（理论+实战） 朋友们，又见面了，上篇文章咱们讲到MySQL分库分表的方法，这篇文章咱们就针对上一篇文章模拟在MySQL中海量数据的优化方法，文章干货较多，建议三连。 提示：以下是本篇文章正文内容，案例仅供参考
MySQL优化 在我们的实际场景中经常会遇到sql查询较慢的问题,今天特地写一篇文章来聊聊我对于MySQL调优相关内容的知识,以及从哪些点去进行优化.
sibenx 写代码和写博客是工作和生活中最美好的两件事！ 个人博客： https://blog.it2048.cn