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;
=========================================================================================================================
相关文章
- 优化sql案例 mysql关联查询的时候,因关联字段的排序规则不一致导致索引失效案例
- MySQL 普通索引、唯一索引和主索引
- MySQL 插入更新 ON DUPLICATE KEY UPDATE
- MySQL 5.7 基于组复制(MySQL Group Replication) - 运维小结
- mysql索引
- mysql学习笔记-- 多表查询之外键、表连接、子查询、索引
- Ubuntu下彻底卸载MySQL
- MySQL force Index 强制索引概述
- linux下彻底卸载mysql 图解教程
- MySQL索引连环18问
- mysql 查询指定数据库所有表, 指定表所有列, 指定列所有表 所有外键及索引, 以及索引的创建和删除
- MySQL 表解锁
- MySQL之添加联合唯一索引
- mysql 添加索引
- 2022-08-19 mysql/stonedb-索引优化专利交底书-改进点
- 2022-11-28 mysql列存储引擎-varchar(255) The result of adding data is not queried-问题记录
- 面试官:为什么Mysql索引用B+树,而Mongodb索引用B树?
- MySQL day5 日志管理
- Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements
- mysql千万级数据量根据索引优化查询速度
- MySQL性能优化浅析及线上案例
- mysql 子查询总结
- 对于mysql加索引,删除索引,添加列,删除列,修改列顺序的最佳办法测试
- mysql内置函数
- Mysql时间范围查询不走索引问题