mysql的表增加索引_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;

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

原文：http://www.cnblogs.com/littlehb/p/4121718.html