MySQL在比较大的表上创建索引的一种方式（中断时间短）_runyon

http://blog.sina.com.cn/u/1221906221

首页博文目录关于我

个人资料

runyon

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：0

博客访问：3,832
关注人气：32
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

MySQL在比较大的表上创建索引的一种方式（中断时间短）

(2017-03-08 09:19:43)

标签：

mysql

大表

创建索引

分类：编程

昨天在一个500w级别的MyISM表上建立联合索引，等了很久没有成功。后面通过下面的方式实现了，记录一下（ps：数据库名和表名都打码了）：

1、创建new_foo表，和原来的表结构一样，但是加上了要创建的索引。

2、将原来的表数据导入到数据文件，这个挺快的，十几秒就搞定了。

select * from foo into outfile 'foo.data';

Query OK, 4862189 rows affected (11.95 sec)

3、将数据文件的数据导入到new_foo表。这个步骤比较慢，用了16分钟。

这两个设置据说可以提高效率（针对MyISM表）：

SET global myisam_max_sort_file_size = 1024*1024*1024*4;

SET global myisam_sort_buffer_size = 1024*1024*1024*4;

然后加载：

mysql> load data infile '/usr/local/mysql/data/foodb/foo.data' ignore into table new_foo;

Query OK, 4862181 rows affected (16 min 57.15 sec)

Records: 4862189 Deleted: 0 Skipped: 8 Warnings: 0

4、将foo和new_foo两张表重命名：

rename table foo to foo_bak_20170307;

rename table new_foo to foo;

理论上，导入数据后创建索引的速度应该比先创建索引再导入数据要快。所以昨天的现象可能是一个特例，但上面的操作还有一个意义，就是中断服务的时间会比较短。缺点的话就是存在数据不一致的可能（期间foo表有新增、删除可能），所以需要foo表有timestamp字段。即使foo表有timestamp字段，如果期间将记录逻辑删除了，修改会丢失。就看如何取舍了。对数据一致性要求没那么高，或者不存在逻辑删除操作且有timestamp字段的表，可以考虑采用这种方式。

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：DevOP经验谈：遇到诡异问题切记排查是否是系统故障

后一篇：面向对象和面向过程的一个比喻和一些随想

新浪BLOG意见反馈留言板　欢迎批评指正