mysql索引添加缓慢_mysql 中创建索引很慢，怎么解决

最新推荐文章于 2024-08-29 04:01:44 发布

灵台方寸心

最新推荐文章于 2024-08-29 04:01:44 发布

阅读量7.1k

点赞数 1

文章标签： mysql索引添加缓慢

本文链接：https://blog.csdn.net/weixin_30806145/article/details/113153402

版权

引用

如题，我现在有一张表。。。里面的数据大概就是 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

等　级：

#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)

等　级：

#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

等　级：

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

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

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

ACMAIN_CHM

(acmain)

等　级：

更多勋章 #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

等　级：

#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

等　级：

#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

等　级：

#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)

等　级：

更多勋章 #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

等　级：

#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)

等　级：

#27楼得分：0回复于：2011-09-22 09:28:12索引创建慢，主要跟磁盘IO有关系。

你把varchar(N)换成char(N)，应该会快些。另外，调大key-buffer之类的参数试试。

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

wwwwb

等　级：

#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 的回复: