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

from http://topic.csdn.net/u/20110921/16/7d335c53-4d88-4fca-b150-b1d165fa588a.html

引用

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

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

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

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

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

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



对我有用[0]丢个板砖[0]引用举报管理TOP回复次数：36 


wfevgch
(聪明的一休)

等　级：

#1楼 得分：0回复于：2011-09-21 17:02:02首先查询字段用char会好些

你数据量大，不妨试一下，先建索引，当然insert速度会慢，你把innodb_flush_log_at_trx_commit置为0后进行insert，比较一下速度。


对我有用[0]丢个板砖[0]引用举报管理TOP精华推荐：求SSAS中的计算成员中属性成员的使用


wIloverYou123456789
(wIloverYou123456789)

等　级：

#2楼 得分：0回复于：2011-09-21 17:04:25引用 1 楼 wfevgch 的回复:

首先查询字段用char会好些

你数据量大，不妨试一下，先建索引，当然insert速度会慢，你把innodb_flush_log_at_trx_commit置为0后进行insert，比较一下速度。


我知道。。我是先 insert 之后 才去建立的 index 。。。。

你的意思是先建立 index 然后调整 mysql 的 innodb_flush_log_at_trx_commit 。。。这样一来 在执行 

insert into 数据的操作 速度 会 提高么？


对我有用[0]丢个板砖[0]引用举报管理TOP精华推荐：MySQL--- FAQ（2008-10-30更新）


wwwwb


等　级：
9
23
23
#3楼 得分：0回复于：2011-09-21 17:04:391种方法：用SELECT INTO OUTFILE导出记录，
TRUNCATE 此TABLE，建立索引，用LOAD DATA INIFILE再导入


对我有用[0]丢个板砖[0]引用举报管理TOP精华推荐：用C#/.NET挑战一下VFP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#4楼 得分：0回复于：2011-09-21 17:13:44引用 3 楼 wwwwb 的回复:

1种方法：用SELECT INTO OUTFILE导出记录，
TRUNCATE 此TABLE，建立索引，用LOAD DATA INIFILE再导入


我现在就是这么做的。。。。into file 。。。导入数据不慢，也就是三四分钟的事情。。问题在于我导入之后 要执行一些查询 和统计 ，这个过程很忙！！我要优化的就是 查询和统计。。为了解决这个问题。。我想到的就是去建立索引！但是 为800w 条数据建立索引 是个很慢的过程 ，希望能在建立索引的时候 提高速度和效率！


对我有用[0]丢个板砖[0]引用举报管理TOP精华推荐：MDX 如何 翻译 转换 为 SQL 脚本 


WWWWA
(aaaa)

等　级：
2
12
15
#5楼 得分：0回复于：2011-09-21 17:16:10我现在就是这么做的：
你的步骤是这样？先建立索引
RUNCATE 此TABLE，建立索引，用LOAD DATA INIFILE再导入


对我有用[0]丢个板砖[0]引用举报管理TOP精华推荐：关于项目相互引用的问题


wIloverYou123456789
(wIloverYou123456789)

等　级：

#6楼 得分：0回复于：2011-09-21 17:20:06引用 5 楼 wwwwa 的回复:

我现在就是这么做的：
你的步骤是这样？先建立索引
RUNCATE 此TABLE，建立索引，用LOAD DATA INIFILE再导入


是的。。你的理解是对的。。我就是这么做的！！！但是 。导入速度还可以。。问题就是建立索引。。然后查询 和统计！！！ 。。这是问题的关键！！


对我有用[0]丢个板砖[0]引用举报管理TOP


wwwwb


等　级：
9
23
23
#7楼 得分：0回复于：2011-09-21 17:25:17问题就是建立索引：再问一次，你是在导入数据前建立的索引还是导入后建立的，仔细看看我的建议

用SELECT INTO OUTFILE导出记录，TRUNCATE 此TABLE，建立索引，用LOAD DATA INIFILE再导入


对我有用[0]丢个板砖[0]引用举报管理TOP


ACMAIN_CHM
(acmain)

等　级：
2
6
更多勋章 #8楼 得分：0回复于：2011-09-21 17:28:47你的索引是什么？ 贴一下你的create table 和 create index 


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#9楼 得分：0回复于：2011-09-21 17:29:37引用 7 楼 wwwwb 的回复:

问题就是建立索引：再问一次，你是在导入数据前建立的索引还是导入后建立的，仔细看看我的建议

用SELECT INTO OUTFILE导出记录，TRUNCATE 此TABLE，建立索引，用LOAD DATA INIFILE再导入


我是 truncate table 之后 就 load data inifile 。。。然后 create index  

。。你的意思 是 让我的 create index 在 load data inifile 之前？ 。。好吧我去试试。。看看效果如何 。。

现在按照我之前的做法 。。index 还没有建立完全。。。


对我有用[0]丢个板砖[0]引用举报管理TOP


wwwwb


等　级：
9
23
23
#10楼 得分：0回复于：2011-09-21 17:30:58。你的意思 是 让我的 create index 在 load data inifile 之前？
对，3楼就建议了，要仔细看看别人的回复哦


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#11楼 得分：0回复于：2011-09-21 17:32:51引用 7 楼 wwwwb 的回复:

问题就是建立索引：再问一次，你是在导入数据前建立的索引还是导入后建立的，仔细看看我的建议

用SELECT INTO OUTFILE导出记录，TRUNCATE 此TABLE，建立索引，用LOAD DATA INIFILE再导入


create table insert_database (red_ball varchar(50));

create index redball_insertdatabase on insert_database (red_ball);


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#12楼 得分：0回复于：2011-09-21 17:33:38引用 10 楼 wwwwb 的回复:

。你的意思 是 让我的 create index 在 load data inifile 之前？
对，3楼就建议了，要仔细看看别人的回复哦



我需要设置 innodb_flush_log_at_trx_commit 么？？


对我有用[0]丢个板砖[0]引用举报管理TOP


wwwwb


等　级：
9
23
23
#13楼 得分：0回复于：2011-09-21 17:34:11导入数据速度如何？数据量多大？


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#14楼 得分：0回复于：2011-09-21 17:35:38引用 13 楼 wwwwb 的回复:

导入数据速度如何？数据量多大？


数据量 大概就是 800w 条数据。。。文件时一个txt 文件。。大概 140M 左右。。当然有可能 以后的文件。会更大！！


对我有用[0]丢个板砖[0]引用举报管理TOP


wwwwb


等　级：
9
23
23
#15楼 得分：0回复于：2011-09-21 17:36:41导入数据试试速度


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#16楼 得分：0回复于：2011-09-21 17:53:18引用 14 楼 wiloveryou123456789 的回复:

引用 13 楼 wwwwb 的回复:

导入数据速度如何？数据量多大？


数据量 大概就是 800w 条数据。。。文件时一个txt 文件。。大概 140M 左右。。当然有可能 以后的文件。会更大！！


要设置 innodb_flush_log_at_trx_commit 。。这个 参数么？？

怎么设置呢？在哪里 设置？配置文件？？


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#17楼 得分：0回复于：2011-09-21 17:59:48引用 15 楼 wwwwb 的回复:

导入数据试试速度


这样做。。导入的时候速度很慢！！！ (前提是我没有 设置 innodb_flush_log_at_trx_commit 不知道设置之后会不会有提高？)


对我有用[0]丢个板砖[0]引用举报管理TOP


ACMAIN_CHM
(acmain)

等　级：
2
6
更多勋章 #18楼 得分：0回复于：2011-09-21 18:14:58你的语句是什么？


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#19楼 得分：0回复于：2011-09-21 18:19:37引用 18 楼 acmain_chm 的回复:

你的语句是什么？


create table insert_database (red_ball varchar(50));

create index redball_insertdatabase on insert_database (red_ball); ..

这是建立表的 语句 和 。。创建索引的 语句。。。


load data local infile 'XX.txt' into table Insert_database fields terminated by '\t' lines terminated by '\r\n'

这是导入文件的语句。。。

现在按照 15 楼说的。。。导入速度很慢 我用navicat 工具导入。。他的速度 是这样的。。200 秒 执行了 当然文件的 4% ！！！ 速度缓慢啊。。(我没有设置 innodb_flush_log_at_trx_commit 不知道如果设置了 会是什么结果)

我之前是 先导入文件在创建索引。。。四分钟左右搞定！！！但是这样。。创建索引的时候很慢。。。




对我有用[0]丢个板砖[0]引用举报管理TOP


wfevgch
(聪明的一休)

等　级：

#20楼 得分：0回复于：2011-09-21 18:30:18先建索引，再执行set GLOBAL innodb_flush_log_at_trx_commit = 0;再倒入数据；


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#21楼 得分：0回复于：2011-09-21 18:41:00引用 20 楼 wfevgch 的回复:

先建索引，再执行set GLOBAL innodb_flush_log_at_trx_commit = 0;再倒入数据；


我现在是按照。。这种方式 搞的。。。。貌似 速度也是和 innodb_flush_log_at_trx_commit 默认设置没有太大

的差别。。。 如果设置。。innodb_flush_log_at_trx_commit = 2； 倒是速度上有了一点点提高。。

但是提高也不大！！！


我刚才测试的结果 
innodb_flush_log_at_trx_commit = 1 耗时 200 秒 插入了整个文件的 4% 左右！！

innodb_flush_log_at_trx_commit = 0 耗时 200 秒 插入了整个文件的 5% 左右！！

innodb_flush_log_at_trx_commit = 2 耗时 200 秒 插入了整个文件的 11% 左右！！





对我有用[0]丢个板砖[0]引用举报管理TOP


wfevgch
(聪明的一休)

等　级：

#22楼 得分：0回复于：2011-09-21 18:47:14这不可能吧，你是用的innodb吗？


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#23楼 得分：0回复于：2011-09-21 18:51:40引用 22 楼 wfevgch 的回复:

这不可能吧，你是用的innodb吗？


你说 的是。。数据库 类型么？

我不太清楚。。我对mysql 不太熟悉。。。怎么看数据库类型呢？ 或者表类型呢？


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#24楼 得分：0回复于：2011-09-21 18:59:10我刚才查看了一下。。表的结构。。是 innodb 类型的。。


对我有用[0]丢个板砖[0]引用举报管理TOP


wwwwb


等　级：
9
23
23
#25楼 得分：0回复于：2011-09-22 08:32:35直接在用MYSQL -uroot -p<123.sql 导入试试


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#26楼 得分：0回复于：2011-09-22 09:21:52引用 25 楼 wwwwb 的回复:

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


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


对我有用[0]丢个板砖[0]引用举报管理TOP


iihero
(iihero)

等　级：
3
2
#27楼 得分：0回复于：2011-09-22 09:28:12索引创建慢，主要跟磁盘IO有关系。
你把varchar(N)换成char(N)，应该会快些。另外，调大key-buffer之类的参数试试。


对我有用[0]丢个板砖[0]引用举报管理TOP


wwwwb


等　级：
9
23
23
#28楼 得分：0回复于：2011-09-22 09:28:25哦，你是用SELECT INTO OUTFILE
只能用LOAD DATA INIFILE才行，你在命令行下执行LOAD DATA INIFILE试试


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#29楼 得分：0回复于：2011-09-22 09:37:20引用 27 楼 iihero 的回复:

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

key-buffer
这个参数应该在哪里调整？？？mysql.ini? 还是？？。。。。


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#30楼 得分：0回复于：2011-09-22 09:42:32引用 28 楼 wwwwb 的回复:

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


我现在执行导入的步骤是这样的：
先建立了 表 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分执行的 导入 操作）

到现在为止 还在执行。。 


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#31楼 得分：0回复于：2011-09-22 10:27:39引用 28 楼 wwwwb 的回复:

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


正如我所说 先建立索引 设置 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'


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




对我有用[0]丢个板砖[0]引用举报管理TOP


wfevgch
(聪明的一休)

等　级：

#32楼 得分：0回复于：2011-09-22 14:11:431 我不了解你表里的业务逻辑，red_ball是什么值，如果可以的话，是否能通过小表用编号维护red_ball，这样实际数据就可以编号，这样效率会提高很多，一般情况下，我们不应该在字符串上建索引，实在没办法，也只能用char类型，而不是varchar

2 关于innodb_flush_log_at_trx_commit，我不知道你的实际环境，你不妨做个实验，一个简单的循环insert百万数据量，innodb_flush_log_at_trx_commit不同的设置，执行时间上差别很大，是几十分钟和几十秒的区别


对我有用[0]丢个板砖[0]引用举报管理TOP


qinchende
(cd.NET)

等　级：

#33楼 得分：0回复于：2011-09-22 17:41:59不知道楼主用的MySQL版本如何？

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


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#34楼 得分：0回复于：2011-09-22 17:49:58引用 33 楼 qinchende 的回复:

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

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


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


对我有用[0]丢个板砖[0]引用举报管理TOP


wIloverYou123456789
(wIloverYou123456789)

等　级：

#35楼 得分：0回复于：2011-09-22 17:53:08引用 32 楼 wfevgch 的回复:

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

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


我明白你的意思：

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

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

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

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


对我有用[0]丢个板砖[0]引用举报管理TOP


wfevgch
(聪明的一休)

等　级：

#36楼 得分：0回复于：2011-09-22 18:22:31关于innodb_flush_log_at_trx_commit的试验你不妨做一下（限麻烦的话，可以联系我给你测试代码）
因为innodb_flush_log_at_trx_commit的效果太明显，我也想知道你的环境到底有什么问题。
如果想用insert语句的话，你可以用mysqldump把表的insert语句导出来执行。

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



引用 35 楼 wiloveryou123456789 的回复:

引用 32 楼 wfevgch 的回复:

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

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