问题:请教 关于 load data local infile 10G左右的问题
导入10G左右文本 到导入3G左右速度就特别慢load data local infile 'h:/0000001/1111.txt' ignore into table `sgk_www_renhe_cn` character set utf8 fields terminated by ',' enclosed by '\'' lines terminated by '\r\n' (`username`,`word`,`email`); 导入SQL命令数据库表结构
CREATE TABLE `1111` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(40) NOT NULL,
`word` varchar(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`,`word`,`email`) USING BTREE,
) ENGINE=MyISAM AUTO_INCREMENT=104904 DEFAULT CHARSET=utf8;我加了联合唯一索引 是不想要重复数据 这个必须 事后在弄重复数据 没法弄
机器配置
8核心I7处理器 8G内存我的问题是
根据我的配置 请问如何设置呢
网上说了要优化my.ini php.ini 如何设置请指教 PHP.INT太长 需要的时候再贴出来my.ini 代码
[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="H:/phpStudy2013/MySQL/"
datadir="H:/phpStudy2013/MySQL/data/"
character-set-server=utf8
default-storage-engine=MyISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=512query_cache_size=0
table_cache=256
tmp_table_size=18Mthread_cache_size=8
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=35M
key_buffer_size=25M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256Kinnodb_additional_mem_pool_size=2Minnodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1Minnodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=8
导入10G左右文本 到导入3G左右速度就特别慢load data local infile 'h:/0000001/1111.txt' ignore into table `sgk_www_renhe_cn` character set utf8 fields terminated by ',' enclosed by '\'' lines terminated by '\r\n' (`username`,`word`,`email`); 导入SQL命令数据库表结构
CREATE TABLE `1111` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(40) NOT NULL,
`word` varchar(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`,`word`,`email`) USING BTREE,
) ENGINE=MyISAM AUTO_INCREMENT=104904 DEFAULT CHARSET=utf8;我加了联合唯一索引 是不想要重复数据 这个必须 事后在弄重复数据 没法弄
机器配置
8核心I7处理器 8G内存我的问题是
根据我的配置 请问如何设置呢
网上说了要优化my.ini php.ini 如何设置请指教 PHP.INT太长 需要的时候再贴出来my.ini 代码
[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="H:/phpStudy2013/MySQL/"
datadir="H:/phpStudy2013/MySQL/data/"
character-set-server=utf8
default-storage-engine=MyISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=512query_cache_size=0
table_cache=256
tmp_table_size=18Mthread_cache_size=8
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=35M
key_buffer_size=25M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256Kinnodb_additional_mem_pool_size=2Minnodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1Minnodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=8
导入的数据越多 需要进行唯一判断的数据也就越多建议尽可能的加大key_buffer_size,以使索引尽可能的装入内存
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 3889168384 |
+-----------------+------------+
1 row in set (0.00 sec)mysql> show global status like 'key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 353751052 |
| Key_reads | 32652 |
+-------------------+-----------+
2 rows in set (0.00 sec)mysql> show global status like 'key_blocks_u%'
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 1869162 |
+-------------------+---------+
2 rows in set (0.00 sec)mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 57 |
+-------------------------+-------+
3 rows in set (0.00 sec)mysql> show global status like 'sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
4 rows in set (0.00 sec)mysql> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 10 |
+---------------+-------+
1 row in set (0.00 sec)我 load data local infile 10G 查询得到的 请指点