mysql 已存在大量数据的情况下,快速创建索引方案
最开始设计表的时候,没有设计好索引,后面数据量多了之后,达到3百万数据,查询效率超慢,想要快速建立索引。此时,直接运行建立索引的语句,由于数据量巨大了,数据库直接崩溃。因此需要用其他方式建立索引。需要建索引的表名为 network_assets_blend,以下为解决方案。方案一 (速度稍慢,操作复杂,整个过程2到3小时,但不影响系统使用)1.设置MySQL文件导出权限。/data/mysql/c
最开始设计表的时候,没有设计好索引,后面数据量多了之后,达到3百万数据,查询效率超慢,想要快速建立索引。此时,直接运行建立索引的语句,由于数据量巨大了,数据库直接崩溃。因此需要用其他方式建立索引。需要建索引的表名为 network_assets_blend,以下为解决方案。
方案一 (速度稍慢,操作复杂,整个过程2到3小时,但不影响系统使用)
1.设置MySQL文件导出权限。/data/mysql/conf/my.cof 文件加配置 secure_file_priv='' ,重启 mysql 。
2.导出大数据量表内所有数据为txt文件。select * from network_assets_blend into outfile 'my.txt'; 文件的生成目录一般在mysql安装路径下的data文件夹,如果找不到,可以按文件名my.txt检索一下路径 ,一般8分钟以内能生成 。
3.建立一张临时表结构与大数据量表结构一致,create table text_assets like network_assets_blend;
在新建的在这张空表内,继续建立 ssjg 和kfdk 的索引。
4.导入数据到临时表。load data infile 'my.txt' into table text_assets; 导入数据 (10分钟20万数据的速度,200万数据大概2小时)
5.导完过后,删掉表network_assets_blend 或者将表名改为其他表名,作为备份。
把数据到位的text_assets 表名改为 network_assets_blend ,索引已生成。
方案二 (速度快,操作简单,整个过程20分钟左右,但这段时间内表内数据事务未提交,没数据,影响系统与此表有关的功能的使用)
1.备份 network_assets_blend 表数据(右击鼠标-》转储sql文件-》结构和数据),导出sql文件,大概6分钟,
2.截断表,
3.建立 ssjg 和kfdk 的索引
4.用可以打开大文件的工具打开备份数据文件 ,删掉建表语句和其他无关语句,只留下插入数据的语句。(重要,一定要删掉建表的语句,只留插入数据语句)
5.运行sql文件将备份数据导入。(速度快仅需要20分钟不到)
方案三(方案二的保守版,比方案二保险,误操作的话,改回表名就ok,不影响数据)
1.备份 network_assets_blend 表数据(右击鼠标-》转储sql文件-》结构和数据),导出sql文件,大概6分钟,
2.修改表名为 copy_network_assets_blend。(相当于备份此表,发生错误可把表名改回来,不影响数据)
3.新建空表 create table network_assets_blend like copy_network_assets_blend; (此语句把表结构复制)
3.为空表network_assets_blend追加 ssjg 和kfdk 的索引。
4.用可以打开大文件的工具打开备份数据文件 ,删掉建表语句和其他无关语句,只留下插入数据的语句。(重要,一定要删掉建表的语句,只留插入数据语句)
5.运行sql文件将备份数据导入。(速度快仅需要20分钟不到)
更多推荐
所有评论(0)