Mysql中大表添加索引的辦法

標籤：blog   http   io   ar   os   sp   for   檔案   on   

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;

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

Mysql中大表添加索引的辦法