Best practices for MySQL index, delete index, add column, delete column, modify column order

1, the first Data training Xltrabackup backup, be prepared, remember to remember!

2.
Mysql-uroot-pd********--export CSV file use dsideal_db; MariaDB [dsideal_db]> SELECT * fromT_resource_info into OUTFILE"/usr/local/mysql/t_resource_info.txt"Fields TERMINATED by','Optionally enclosed by'"'LINES TERMINATED by'\ n'; Query OK,1582463Rows Affected (29.97sec) 3, slicing CSV file, so batch import faster, more convenient, refer to here: http://www.nowamagic.net/librarys/veda/detail/2495, but can not use by capacity Ah, be sure to press the line. said the log too danale not come back, penalty wall an hour! mkdir/usr/local/huanghai-Psplit-A2-d-l50000/usr/local/mysql/t_resource_info.txt/usr/local/huanghai/prefix2-3 seconds. 3, empty the original table, modify the field, anyway there is backup, not afraid of
Truncate T_resource_info;
　　ALTER TABLE t_resource_info add huanghai_test int;

4, optimize the environment configuration, ready to start importing set autocommit=0; SET Unique_checks=0; SET Foreign_key_checks=0;Setsql_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; load Data infile'/usr/local/huanghai/prefix00'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix01'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix02'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix03'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix04'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix05'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix06'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix07'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix08'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix09'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix10'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix11'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix12'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix13'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix14'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix15'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix16'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix17'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix18'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; load Data infile'/usr/local/huanghai/prefix19'IGNORE into table Dsideal_db.t_resource_info_huanghai-terminated by','Enclosed by'"'; commit;5, recovery site set autocommit=1; SET Unique_checks=1; SET Foreign_key_checks=1;Setsql_log_bin=1;

6, recommended the use of Python3 to develop a program, so convenient to concatenate up, this is the best way I can think of, basically no risk, the speed of the basic use of disk maximum Io, do not recommend the use of modified frm and other violent methods, that for the last append field may also line, for the field in the middle of the
May be a disaster, and there is no way to program, this approach is developed with Python3, basically no difficulty.

Best practices for MySQL index, delete index, add column, delete column, modify column order