PHP利用load命令生成百（千）万数据时间对比

插入千万数据用于测试或者mysql性能的学习。
方案一般有insert批量插入，load；还有不需要其他语言支持的存储过程。
下面讲述利用PHP和load的方案：

数据库mysql_test表users，MyISAM引擎：

CREATE TABLE `users` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `uuid` CHAR(32) NOT NULL,
    `name` VARCHAR(32) NOT NULL COMMENT '用户名',
    `email` VARCHAR(32) NOT NULL COMMENT '邮箱',
    `pwd` VARCHAR(64) NOT NULL COMMENT '密码',
    `sex` ENUM('M','F','S') NOT NULL COMMENT '性别',
    `status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '\'不显示\', \'显示\', \'推荐\'',
    `created_at` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '创建时间',
    `remark` TEXT NULL COMMENT '备注',
    PRIMARY KEY (`id`)
)
COMMENT='用户表'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

不含(id之外的)索引：
=====start=====
1 run 100000 lines, using 0.568033 s
2 run 100000 lines, using 0.569032 s
3 run 100000 lines, using 0.571033 s
4 run 100000 lines, using 0.585034 s
5 run 100000 lines, using 0.596034 s
6 run 100000 lines, using 0.586033 s
7 run 100000 lines, using 0.590034 s
8 run 100000 lines, using 0.593034 s
9 run 100000 lines, using 0.600034 s
10 run 100000 lines, using 0.597034 s
total time 5.862335 s
=====end=====

含索引：

ALTER TABLE `users` ADD UNIQUE INDEX `uuid` (`uuid`);
ALTER TABLE `users` ADD INDEX `name_email_pwd` (`name`, `email`, `pwd`);

=====start=====
1 run 99998 lines, using 1.937110 s
2 run 99997 lines, using 2.644152 s
3 run 99995 lines, using 2.787159 s
4 run 99988 lines, using 2.875165 s
5 run 99984 lines, using 3.320189 s
6 run 99967 lines, using 3.676211 s
7 run 99969 lines, using 4.046231 s
8 run 99964 lines, using 4.304246 s
9 run 99965 lines, using 4.524259 s
10 run 99951 lines, using 4.865279 s
total time 34.987001 s
=====end=====
去掉uuid的索引，执行时间变为20s。
将引擎更换为InnoDB，速度变慢，尤其是含有多个索引时，速度有点无法忍受了。
所以索引越少插入越快。
MyISAM引擎比InnoDB快。

PHP代码：

<?php
namespace allen;

/**
 * php test.php
 * TRUNCATE `users`;
 * ALTER TABLE `users` ENGINE=InnoDB;
 * ALTER TABLE `users` ENGINE=MyISAM;
 * Class pdoTenMillion
 * @package allen
 */
class pdoTenMillion
{
    private $host = '127.0.0.1';
    private $post = 3306;
    private $user = 'root';
    private $pass = '';
    private $dbname = 'mysql_test';

    //You can use swap if you are in linux.
    private $path = './';
    //You can be changed times if you like.
    private $times = 100;


    private function generateFile($file)
    {
        $line = '';
        for ($i = 0; $i < 100000; $i++) {
            //100000 is better than 1000000
            $rand = mt_rand(1000000000, 9999999999);
            $r = mt_rand(0, 2);
            $uuid = '"uuid' . $rand . '"';
            $name = '"name' . $rand . '"';
            $emails = ['@qq.com', '@163.com', '@gmail.com'];
            $email = '"email' . $rand . $emails[$r] . '"';
            $pwd = '"' . md5($i) . '"';
            $sexes = ['M', 'F', 'S'];
            $sex = '"' . $sexes[$r] . '"';
            $status = '"' . $r . '"';
            $created_at = '"' . time() . '"';
            $remark = '"remark' . $rand . '",';
            $line .= "{$uuid},{$name},{$email},{$pwd},{$sex},{$status},{$created_at},{$remark}" . PHP_EOL;
        }
        return file_put_contents($file, $line, LOCK_EX);
    }

    private function pdo()
    {
        //You maybe need to set 'max_allowed_packet' in your mysql by using
        //'set global max_allowed_packet = 100*1024*1024'
        $db = new \PDO(
            "mysql:host={$this->host};port={$this->post};dbname={$this->dbname};charset=UTF8;",
            $this->user,
            $this->pass,
            array(\PDO::ATTR_PERSISTENT => true, \PDO::MYSQL_ATTR_LOCAL_INFILE => true)
        ) or die('mysql connect fail');
        $db->query("SET NAMES utf8;");
        return $db;
    }

    private function loadFile($file)
    {
        $db = $this->pdo();
        $sql = 'LOAD DATA LOCAL INFILE "' . $file . '" IGNORE INTO TABLE users fields terminated by "," enclosed by "\"" lines terminated by "\n"(`uuid`,`name`,`email`,`pwd`,`sex`,`status`,`created_at`,`remark`)';
        return $db->exec($sql);
    }

    public function insertFromFile()
    {
        //You maybe need to set 'memory_limit' for your php.
        //ini_set('memory_limit', '256M');
        set_time_limit(0);
        echo '=====start=====' . PHP_EOL;
        $start = microtime(true);
        for ($i = 1; $i <= $this->times; $i++) {
            $s1 = microtime(true);
            $file = $this->path . $i;
            $flag = true;
            if (!file_exists($file)) {
                $flag = $this->generateFile($file);
            }
            if (false !== $flag) {
                $count = $this->loadFile($file);
                $e1 = microtime(true);
                printf($i . ' run ' . $count . ' lines, using %f s' . PHP_EOL, $e1 - $s1);
            }
        }
        $end = microtime(true);
        printf('total time %f s' . PHP_EOL, $end - $start);
        echo '=====end=====' . PHP_EOL;
        for ($i = 1; $i <= $this->times; $i++) {
            //You can remove the temp file.
            //@unlink($this->path . $i);
        }
    }
}

$generator = new pdoTenMillion();
$generator->insertFromFile();

参考：
http://blog.csdn.net/zhengfeng2100/article/details/53487736
http://www.cnblogs.com/fanwencong/p/5765136.html
将$time修改为100，插入1000万条数据。
其实1000万条只含id主键索引，load插入需要80秒左右，要不到4分钟。