# __ .______ __ __ .______ .___________. ______ ______ .___ ___. # | | | _ \ | | | | | _ \ | | / | / __ \ | \/ | # | | | |_) | | |__| | | |_) | `---| |----` | ,----'| | | | | \ / | # | | | ___/ | __ | | ___/ | | | | | | | | | |\/| | # | | | | | | | | | | | | __ | `----.| `--' | | | | | # |__| | _| |__| |__| | _| |__| (__) \______| \______/ |__| |__| # ""$o o$"" ""$o o$" o "$""""o "o $" o""" $" "$o "$o" $o " $ o$" "$o $$$o$$$$o$$$$ $" "oooo o "" ""$$$$$$$$""o"" oo oooo" "$$$$$$oo"oo$$$o" o$$$$oo" o$$$o "o$$$$$$$ "$ $$$$$$$$$oo o$$$$$$$$$o"$" $ $$$ $$$$$$ o$$$$$$ "$$o"o $ $$$$o $$$$$$ $$$$$$$ $$$$o"o $ $$$$$ $$$$$" "$$$$$ $$$$$$ $ $o""""" """" """ """"""$" $ o$$$$$"""$$$$$"$$$$$""$$$$$ooo"o $ o"$o $$$$$$$$oo$$$$$$$$o $$"" $ oo$ "$$$$$$$$$$$$$$$$$$$$" o" o $oo o$$$"$ $$o"o $$$$$$$"" "$$$$$$$ o$$ $$$$o IPHPT BUG o$$$$" $ $$$$ o "$$$$$oo o$$$$$$ "o$$$$ $ $$$$$ o$$"" $ $$$$$o" "$$$$$$$$$$$$$ o o$$$$$o$ "" $$ $$" $ $$$" o"o$$$$$$$$$$$$ " "$$$ $ $$o o$$ "o $$ " $$$$$$$$$$$"o "$$ $ $$$ $$$ oo$ $ o""$$""$$$o " $"o$o $$$o o$$$$ o$$$"o"$oo$$$$o" o $o $$$$$oo$ $$$$o $$$$ $$$$ $$$$" $ $$$$$"" $$ o$$$ """$$$$"o" "$$$o "$$$o $$$" o """ $ $$$oo $$$$o" $$ o$$$"o" """"$ o$$$ o$" $$$ $ "$"" o$"o"$$o$$$$ "$$"o" o$$ "$oo $ " $$o $ "oo$"o$$$"o$o"$$$$o" o" $$$ ""$o $$ $$$o "o$$o$"$$"$$o$$o$$"$$o" $$$ ""o $$$ ""$$$ $$$$$$ $$$$ $" $$$$ $$ $$$$ $$$$"$$$o$ $"" $$$ $$$$ "$$$ """ $$$$ $$"" "$$ oo$" $ooo $ "$$ 【转】MySQL两千万数据优化&迁移   -  叶落山城秋

【转】MySQL两千万数据优化&迁移

MySQL两千万数据优化&迁移

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

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

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

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

二. 数据迁移

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

  1. 数据量太大,无法一次获取(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毫秒……

     2 . 数据量太大并且数据无法预估,某些特殊数据会导致数据导入失败;

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

          1. 一条一条插入数据;

          开始肯定会想这种方案一定不行,因为每次插入都会有一次数据库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注入;

          2 . 一次插入50000条数据;

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

          3 . 组装成SQL文件,最后统一导入;

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

三. 总结

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

原文地址: blog.it2048.cn/article_2000w-data.html


欢迎转载,但请附上原文地址哦,尊重原创,谢谢大家 本文地址: https://www.iphpt.com/detail/40/
本站(PHP --> Golang)已重构,代码开源

当你能力不能满足你的野心的时候,你就该沉下心来学习