Mysql中大表添加索引的办法

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

Hash索引与 Btree索引的区别
http://database.51cto.com/art/201010/229525.htm

Creating Indexes/Sorting on very large tables in Mysql
http://li.angshan.blog.163.com/blog/static/131332289201203053128110/

MySQL load data infile - acceleration?
http://stackoverflow.com/questions/2463602/mysql-load-data-infile-acceleration

LOAD DATA INFILE – performance case study
http://venublog.com/2007/11/07/load-data-infile-performance/

一、进入控制台
mysql -uroot -pdsideal4r5t6y7u

二、备份
select * from t_resource_base into outfile '/tmp/t_resource_base.txt' fields terminated by ',' enclosed by '"';

在Linux中分割文件
mkdir /usr/local/prefix -p
参考：http://www.nowamagic.net/librarys/veda/detail/2495

split -a 2 -d -l 50000 /tmp/t_resource_base.txt /usr/local/prefix

三、删除索引或者创建索引
CREATE INDEX index_name ON table_name (column_list)

DROP INDEX index_name ON talbe_name

四、，执行导入(Load data infile)
优化MYSQL参数：http://www.jb51.net/article/47419.htm

MYSQL Bulk Data Loading for InnoDB Tables
http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
set sql_log_bin=0;

SET @innodb_additional_mem_pool_size=26214400;
set @innodb_buffer_pool_size=1073741824;
set @innodb_log_buffer_size=8388608;
set @innodb_log_file_size=268435456;

五、
mysql > use dsideal_db;
mysql > truncate table t_resource_base ;

六、
load data infile '/usr/local/prefix00' IGNORE into table dsideal_db.t_resource_base fields terminated by ',' enclosed by '"';
load data infile '/usr/local/prefix01' IGNORE into table dsideal_db.t_resource_base fields terminated by ',' enclosed by '"';
load data infile '/usr/local/prefix02' IGNORE into table dsideal_db.t_resource_base fields terminated by ',' enclosed by '"';
load data infile '/usr/local/prefix03' IGNORE into table dsideal_db.t_resource_base fields terminated by ',' enclosed by '"';
load data infile '/usr/local/prefix04' IGNORE into table dsideal_db.t_resource_base fields terminated by ',' enclosed by '"';
load data infile '/usr/local/prefix05' IGNORE into table dsideal_db.t_resource_base fields terminated by ',' enclosed by '"';

mysql > commit;

七、恢复现场
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;
set sql_log_bin=1;

=========================================================================================================================