mysql 中 创建索引很慢，怎么解决

[androidmvc]

如题，我现在 有一张表。。。里面的数据大概就是 800w 条左右，当然以后也可能会更多，这个表会频繁的更新！

我现在的处理是：每次更新 都会先truncate 这张表(因为里面的数据 已经不需要了)，然后我会 drop index ！

这样之后我会去 insert 数据 。。。。(这些数据量 有可能过千万。。级别的) ，之后就是为数据建立索引！

建立索引的过程很慢。。。800w 数据 现在执行了 半个小时 还没有完成。。。。。建立索引的 字段的类型是 varchar。。。

(这里只能是varhcar 之前有试过 建立多列索引。。那样需要建立 6 列 。。耗时 大概 1 小时左右！如果数据量更大 那么时间

会更长。。。) 有没有更好的解决办法。。或者 怎么优化一下mysql 数据库。。

[qinchende]

不知道楼主用的MySQL版本如何？

我建议最好升级MySQL到最新的5.5.x，实践证明5.5.x在索引的处理上跟5.1.x 5.0.x比，那就是火箭跟乌龟的区别！！！

[androidmvc]

[Quote=引用 33 楼 qinchende 的回复:]

不知道楼主用的MySQL版本如何？

我建议最好升级MySQL到最新的5.5.x，实践证明5.5.x在索引的处理上跟5.1.x 5.0.x比，那就是火箭跟乌龟的区别！！！
[/Quote]

好吧。。我下载个。。5.5试试 。。但是我之前安装5.5的时候。。貌似执行不了。truncate table table_name 的操作。。。

[androidmvc]

[Quote=引用 32 楼 wfevgch 的回复:]

1 我不了解你表里的业务逻辑，red_ball是什么值，如果可以的话，是否能通过小表用编号维护red_ball，这样实际数据就可以编号，这样效率会提高很多，一般情况下，我们不应该在字符串上建索引，实在没办法，也只能用char类型，而不是varchar

2 关于innodb_flush_log_at_trx_commit，我不知道你的实际环境，你不妨做个实验，一个简单的循环insert百万数……
[/Quote]

我明白你的意思：

你第一条件建议 就是建议我按照 数据库范式 来做！ 现在我的 red_ball 类型已经是char 了。。

关于第二条件建议 我还真没有使用 循环一个 insert 来做！！！因为我现在导入数据 是使用load data local infile 在mysql 控制台我也看不到速度！！！ 只能通过navicat 这样的恐惧 来导入。。

通过相同的时间 来比较 文件导入的进度！！！

不过很感谢你的建议！ 不知道有没有更好的办法。。比如像 33 楼所说。。。。

[聪明的一休]

关于innodb_flush_log_at_trx_commit的试验你不妨做一下（限麻烦的话，可以联系我给你测试代码）
因为innodb_flush_log_at_trx_commit的效果太明显，我也想知道你的环境到底有什么问题。
如果想用insert语句的话，你可以用mysqldump把表的insert语句导出来执行。

不过我个人觉得文件导入的话，最终也是应该生成insert语句，而且是支持批处理的，就是insert *** values (),(),()...；理论上效率应该更高。


[Quote=引用 35 楼 wiloveryou123456789 的回复:]

引用 32 楼 wfevgch 的回复:

1 我不了解你表里的业务逻辑，red_ball是什么值，如果可以的话，是否能通过小表用编号维护red_ball，这样实际数据就可以编号，这样效率会提高很多，一般情况下，我们不应该在字符串上建索引，实在没办法，也只能用char类型，而不是varchar

2 关于innodb_flush_log_at_trx_commit，我不知道你的实际环境，……
[/Quote]

[wwwwb]

直接在用MYSQL -uroot -p<123.sql 导入试试

[androidmvc]

[Quote=引用 25 楼 wwwwb 的回复:]

直接在用MYSQL -uroot -p<123.sql 导入试试
[/Quote]

mysql -u root -p root XXX.txt ..我的数据 是纯数据没有sql 语句 txt 里面全部是纯数据！！！能这样导入么？

[iihero]

索引创建慢，主要跟磁盘IO有关系。
你把varchar(N)换成char(N)，应该会快些。另外，调大key-buffer之类的参数试试。

[wwwwb]

哦，你是用SELECT INTO OUTFILE
只能用LOAD DATA INIFILE才行，你在命令行下执行LOAD DATA INIFILE试试

[androidmvc]

[Quote=引用 27 楼 iihero 的回复:]

索引创建慢，主要跟磁盘IO有关系。
你把varchar(N)换成char(N)，应该会快些。另外，调大key-buffer之类的参数试试。
[/Quote]
key-buffer
这个参数应该在哪里调整？？？mysql.ini? 还是？？。。。。

[androidmvc]

[Quote=引用 28 楼 wwwwb 的回复:]

哦，你是用SELECT INTO OUTFILE
只能用LOAD DATA INIFILE才行，你在命令行下执行LOAD DATA INIFILE试试
[/Quote]

我现在执行导入的步骤是这样的：
先建立了 表 create table insert_database（red_ball char(50）);
然后设置 innodb_flush_log_at_trx_commit = 0;
然后建立index create index redball_insertdatabase on insert_database(red_ball);
然后执行导入:load data local infile 'XXX.txt' into table Insert_database fields terminated by '\t' lines terminated by '\r\n'（我是从 9:42分执行的 导入 操作）

到现在为止 还在执行。。

[androidmvc]

[Quote=引用 28 楼 wwwwb 的回复:]

哦，你是用SELECT INTO OUTFILE
只能用LOAD DATA INIFILE才行，你在命令行下执行LOAD DATA INIFILE试试
[/Quote]

正如我所说 先建立索引 设置 innodb_flush_log_at_trx_commit = 0; 然后执行导入:load data local infile 'XXX.txt' into table Insert_database fields terminated by '\t' lines terminated by '\r\n'

这个过程很慢 至少是从 10：12 到现在为止 一直 在执行 。。。（我在mysql 控制台也看不到进度！！）


然而 如果 我们不建立索引 设置 innodb_flush_log_at_trx_commit = 0; 然后执行导入:load data local infile 'XXX.txt' into table Insert_database fields terminated by '\t' lines terminated by '\r\n'


也就 三四分钟的事情 ！！貌似 设不设置 innodb_flush_log_at_trx_commit 没有太大的 关系！！！

但是我知道如果先导入了 数据在建立 索引 这个过程很耗时！！！前面那种方式我知道是在 导入数据的时候就添加好索引！！！(但是第一种方式我也看不到 执行进度，在mysql 控制台黑屏 。。看不到 如果 用navicat 这个工具来 执行导入 貌似 不管执行哪种方式的导入 速度都很慢！！！)

顺便说一下我的目的吧。。我是想把 数据导入之后 做一个统计查询！！！就这样。。如果不建立索引 我知道

查询 速度很慢！！！但是建立索引 又是一个很耗时的过程 。。。有没有一种方式 不建立索引 也能提高查询和 统计的速度的。。。。

目前的统计语句是这样的。。。

第一个sql ：统计总的 记录条数(用于分页)

select count(*) from (select rb.red_ball,count(1) from insert_database rb group by rb.red_ball having count(1)=1) as temp


第二个 sql:就是 查询语句 在分页上面使用

select rb.red_ball,count(1) from insert_database rb group by rb.red_ball having count(1)=1

limit x,x

还有一个导出 的：
select * from (select rb.red_ball,count(1) from insert_database rb group by rb.red_ball having count(1)=1) as temp into outfile 'xxx.txt' fields terminated by '\t' lines terminated by '\r\n'


又没办法实现不是建立索引能提高 查询和 统计速度的方式。。？ 期待 大牛出现！

[seai]

一、大表建索引在不变的硬件条件下，没有飞速提升的可能；
二、将表拆为多个小表，速度就能上去；
三、需要评估是否对查询带来合并等其他影响；

[androidmvc]

[Quote=引用 36 楼 wfevgch 的回复:]

关于innodb_flush_log_at_trx_commit的试验你不妨做一下（限麻烦的话，可以联系我给你测试代码）
因为innodb_flush_log_at_trx_commit的效果太明显，我也想知道你的环境到底有什么问题。
如果想用insert语句的话，你可以用mysqldump把表的insert语句导出来执行。

不过我个人觉得文件导入的话，最终也是应该生成insert语句……
[/Quote]


也许你需要为我做一下测试了！！！

我这个文件是 104M 数据量大概800w 。 我昨天又测试了一个24M 的文件 大概数据时 130w 左右

我分别问他们这两个文件 导入 和 建立索引！

导入就是使用的load data local infile 'XXX.txt' into table Insert_database fields terminated by '\t' lines terminated by '\r\n' 这种方式。。。

然后我推测了一下
mysql 版本 5.5
24M 130w 导入耗时 将近 1 分钟 建立索引耗时 1分钟多些

140M 800w 导入耗时 将近 8 分钟 建立索引耗时 8分钟左右(都是在公司做的测试)

(以上 的测试结果只是我自己得到的结果，但是我回到家用自己的电脑测得时候 导入的时间 耗时 都差不多 但是 建立索引的时候 竟然 耗时 27分钟。。。。我那个时候 让他建立者索引。自己在玩dota 是不是 cpu 的占用和 内存的使用也会影响建立索引的速度呢？)

麻烦你一下 能给个 邮箱么？我把那个文件发给你。。。帮我看一下。。。到目前为止 我所能做的 提高 建立索引的速度 和 导入的 速度 也只有这么多。想不到其他的办法了。也许你说的innodb_flush_log_at_trx_commit = 0 会有效果，但是 我这边貌似 和他没有什么关系。。。对了 表结构是 innodb 的。。。

[wwwwb]

上传到www.access911.net/csdn
，用WINRAR压缩

[zhuzhuzhu333]

试试在varchar(50)字段的前面几位建立索引，50的长度没必要~

[daodaoyu222]

大数据量，处理，mysql 不行的，特别是插入删除什么的。

如果你的系统是mysql的，那就没办了，

如果你只是用mysql 来处理一下数据，或分析得到结果什么的，

你可以换用mssql处理 很快的。800万数据，导入，索引几分钟搞定。

我刚转过来。

[androidmvc]

[Quote=引用 38 楼 wiloveryou123456789 的回复:]

引用 36 楼 wfevgch 的回复:

关于innodb_flush_log_at_trx_commit的试验你不妨做一下（限麻烦的话，可以联系我给你测试代码）
因为innodb_flush_log_at_trx_commit的效果太明显，我也想知道你的环境到底有什么问题。
如果想用insert语句的话，你可以用mysqldump把表的insert语句导出来执行。

不过我个人觉……
[/Quote]

好了接贴吧。。。那个文件太大！！！ 不做处理了，谢谢这个 朋友！！！

[徐明勋]

将数据按照索引排好的顺序导出，然后在导入，然后在建索引

[孙成]

直接不生成log 不行吗？

[紫威剩人阚雷]

我也遇到索引问题