Create Table 和 Insert 的效率问题，求高手解答

Question

Create 和 Insert 代码如下： 
 
CREATE TABLE 
 output_distinct_fvids5
(outid        bigint(20) unsigned primary key AUTO_INCREMENT,
fvid         bigint(20) unsigned,
srvolid      bigint(20) unsigned,
pdvid bigint(20) unsigned,
targetmid bigint(20) unsigned,
createtime datetime,
accesstime datetime,
modifytime datetime,
statustime datetime,
archivetime datetime,
dirname text,
filename     text,
index output_distinct_fvids5(fvid)
 ) ENGINE=MYISAM, MAX_ROWS = 200000000INSERT ignore into output_distinct_fvids5
(fvid,srvolid,pdvid,targetmid,createtime,accesstime,modifytime,statustime,dirname,filename) 
SELECT ntf.fvid, ntf.sourcevolid,ntf.pdvid, ntf.repositorymid,ntf.createtime, ntf.lastaccesstime, ntf.lastmodifytime, ntf.statustime,dn.displayname, ntf.displayname 
 FROM activedirname dn, newtech_file ntf,ss.machine m 
 WHERE dn.dvid=ntf.pdvid 
 AND m.stid = 12 
 AND ntf.sourcevolid = 5
 GROUP BY ntf.fvid ORDER BY ntf.ntid 想做一个多窗口命令行的程序，之后一个窗口就会Create一个这样类似的temp表。 现在单纯一个临时表output_distinct_fvids5就有百万--千万级的记录 执行的效率很低，并且另外多窗口2，3创建临时表失败， 请问大牛们为什么，有什么解决方案呢？ 
没有太多积分，不能慷慨解囊请海涵～

Answer 1

CREATE TABLE 
 output_distinct_fvids5
-------->CREATE TEMPORARY TABLE 
 output_distinct_fvids5

Answer 2

现在单纯一个临时表output_distinct_fvids5就有百万--千万级的记录 执行的效率很低-----------------------
这个肯定的啦
假如你每行记录占1K，那你想想这过程的磁盘IO多少吧（包括从旧表读和写进新表2部分的IO）

Answer 3

 哦，我忘记讲了，表名字是不会重复的，每个表名output_distinct_fvids[num] 都由程序生成。 因此我怀疑是数据库容量和执行效率的原因。

Answer 4

 抓取错误异常贴出来，这样好分析很多--------  在EventViewer那里面嘛？ 

Answer 5

想做一个多窗口命令行的程序,并且另外多窗口2，3创建临时表失败
-------------------------
程序里面抓不到吗？或者你看看mysql的data目录下err后缀错误日志里面是否有相关错误信息没有

Answer 6

你的每个窗口是使用一个单独的connection 来是共同的？如果是单独的你可以用 create temporary table ...来创建临时表，这具临时表是基于connection的。

Answer 7

 程序里面抓不到吗？ 或者你看看mysql的data目录下err后缀错误日志里面是否有相关错误信息没有
------
 
 我在两个 cmd console里面运行了那两条查询语句， err日志的没有信息，但是eventviewer有一个mysqld-nt的一个错误：Event Type: Error
Event Source: Application Error
Event Category: (100)
Event ID: 1000
Date: 8/31/2009
Time: 10:09:37 PM
User: N/A
Computer: NWT-VM-ARKCUST
Description:
Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x001df2aa.For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
这个是mysql的溢出错误吗？

Answer 8

这个错误信息已经是操作系统级的了。很难看出什么。如果你的ERR日志中什么都没有就比较奇怪了。你的1,2,3窗口的建表语句是什么？没成功你捕捉到的错误信息是什么？

Answer 9

每个cmd 窗口都是同样的Win32 console application, 每个app都是独立的connection.又见你回帖了，回答过我好多问题了，谢过啦～

Answer 10

>你的1,2,3窗口的建表语句是什么？没成功你捕捉到的错误信息是什么?
建表语句都是在0楼的那个Create Table。 
没有出错信息，1窗口create OK, 2窗口同样的Create， 只是select count(*) from that_table 返回0。 至于是创建失败还是创建成功0条记录 我还不清楚，客户没有提供更多的信息。
我估计是创建失败，我有客户数据库的copy。 在同时run create 2 table，目前还没有结果，目前no response... mysqld-nt出错。 
over。。

Answer 11

那比较复杂了，我试着模拟你的情况，1#窗口当然是OK，2#就会说mysql> CREATE TABLE
    ->  output_distinct_fvids5
    -> (outid        bigint(20) unsigned primary key AUTO_INCREMENT,
    -> fvid         bigint(20) unsigned,
    -> srvolid      bigint(20) unsigned,
    -> pdvid        bigint(20) unsigned,
    -> targetmid    bigint(20) unsigned,
    -> createtime    datetime,
    -> accesstime    datetime,
    -> modifytime    datetime,
    -> statustime    datetime,
    -> archivetime    datetime,
    -> dirname            text,
    -> filename     text,
    -> index output_distinct_fvids5(fvid)
    ->  ) ENGINE=MYISAM, MAX_ROWS = 200000000;
ERROR 1050 (42S01): Table 'output_distinct_fvids5' already exists
mysql>
建议你先想办法做测试得到第一手资料。否则很多情况都是不制定的。特别用户的话基本上不可信。

Answer 12

  表名字是不会重复的，每个表名output_distinct_fvids[num] 都由程序生成，也就是说窗口1创建outpu....ds5,窗口2 创建outpu....ds6这样的。 
  我单独的从一个窗口运行create,都可以创建成功，记录都很大，每个执行之后所用的时间大概10+mins 的样子。 mysql> select count(*) from output_distinct_fvids5; 
+----------+
| count(*) |
+----------+
|  1678689 |
+----------+
1 row in set (0.05 sec)mysql> select count(*) from output_distinct_fvids6; 
+----------+
| count(*) |
+----------+
|   954055 |
+----------+
1 row in set (0.09 sec)
  

Answer 13

本帖最后由 ACMAIN_CHM 于 2009-09-01 14:43:26 编辑

  

Answer 14

抱歉没有表达清楚：
表单独建是成功的，那些select count(*) 是单独创建的结果，我给你看一下数据量有多大。
但是多窗口同时创建是no response 

Answer 15

哦，明白了。建议你可以 show processlist 看一下各个窗口的状态。估计是这些窗口同时查一个表，是不是表被锁定了。其它进程在等待中。

Answer 16

好象上面楼主说每个窗口创建一个单独表和单独连接对其操作的喔 
-----  OK，请看 INSERT Query语句中有newtech_file 的一个表， 这个表存放了所有的文件，activedirname表如名字所见存放的是文件对应的path。  现在要求做一个命令行程序，把newtech_file中的文件按照volume（有Volid = srcvolid参照）输出每个文件的path， 每个卷下面有很多很多文件记录，设计是把这些文件记录插入到临时表中， 再从临时表里取出一条做具体的其他处理（这些不用关心）。 所以我说一个程序也就是一个窗口创建了一个单独的链接。
  那么运行命令程序一次（也就是开一个窗口）只能处理一个volume，然后客户就要求能不能同时处理多个volume啊，这样快点弄完？好办，不用多线程，就是多开几个窗口，每次程序创建临时表命名成不同的名字的就可以了。 那么这个窗口创建了单独的表。
   
  那么好，具体来说， 同时打开WindowA,B，同时运行AAA.exe
  CMD window A               |         window B
  # AAA.exe                  |         #AAA.exe
   Create Table A            |          Create Table B            
 
 窗口B的没有结果，不知道使你清楚了没有？

Answer 17

清楚了你的现象。
但需要看你的 show processlist 

Answer 18

请允许我改掉一部分的user name 和 DB name, 调整一部分格式
mysql> show processlist;
| Id | User    | Host                                 | db          | Command     | Time | State                                                          | Info
                                                         |
+----+---------+--------------------------------------+-------------+-------------+------+-------|  4 | user | demo.com:2641 | user_sss | Sleep       |   58 |    | NULL|
|  5 | user | demo.com:2645 | user_sss | Sleep       |   57 |    | NULL|
|  6 | user | demo.com:2646 | user_sss | Sleep       |   64 |    | NULL|
|  7 | user | demo.com:2658 | user_sa  | Sleep       |   57 |    | NULL|
|  8 | user | demo.com:2666 | user_sa  | Sleep       |   60 |    | NULL|
|  9 | user | demo.com:2668 | user_sss | Sleep       |   59 |    | NULL|
| 10 | user | demo.com:2669 | user_sa  | Sleep       |   49 |    | NULL|
| 11 | user | demo.com:2670 | user_sa  | Sleep       |   43 |    | NULL|
| 12 | user | demo.com:2672 | user_sss | Sleep       |   59 |    | NULL|
| 13 | replicate| localhost:2677                       | NULL        | Binlog Dump 
| 56 | Has sent all binlog to slave; waiting for binlog to be updated | NULL|
| 16 | user | demo.com:2686 | user_sss | Sleep       |   38 |    | NULL|
| 17 | user | demo.com:2688 | user_sa  | Query       |   49 | Copying to tmp table                                           | SELECT DISTINCT v.volid,v.displayname   FROM
 newtech_file a, user_sss.volume v,user_sss |
| 18 | user | demo.com:2699 | user_sa  | Sleep       |   43 |    | NULL|
| 19 | user | demo.com:2700 | user_sa  | Sleep       |   42 |    | NULL|
| 20 | user | demo.com:2702 | user_sa  | Sleep       |   42 |    | NULL|
| 21 | user | demo.com:2703 | user_sa  | Sleep       |   42 |    | NULL|
| 22 | user | demo.com:2704 | user_sa  | Sleep       |   42 |    | NULL|
| 23 | user | demo.com:2705 | user_sa  | Sleep       |   42 |    | NULL|
| 24 | user | demo.com:2706 | user_sa  | Sleep       |   42 |    | NULL|
| 25 | user | demo.com:2707 | user_sa  | Sleep       |   42 |    | NULL|
| 26 | user | demo.com:2708 | user_sa  | Sleep       |   42 |    | NULL|
| 27 | user | demo.com:2709 | user_sa  | Sleep       |   41 |    | NULL|
| 28 | user | demo.com:2710 | user_sa  | Sleep       |   41 |    | NULL|
| 29 | user | demo.com:2711 | user_sa  | Sleep       |   41 |    | NULL|
| 30 | user | demo.com:2712 | user_sa  | Sleep       |   41 |    | NULL|
| 31 | user | demo.com:2713 | user_sa  | Sleep       |   41 |    | NULL|
| 32 | user | demo.com:2714 | user_sa  | Sleep       |   41 |    | NULL|
| 33 | user | demo.com:2715 | user_sa  | Sleep       |   41 |    | NULL|
| 34 | user | demo.com:2716 | user_sa  | Sleep       |   41 |    | NULL|
| 35 | user | demo.com:2717 | user_sa  | Sleep       |   41 |    | NULL|
| 36 | user | demo.com:2718 | user_sa  | Sleep       |   40 |    | NULL|
| 37 | user | demo.com:2719 | user_sa  | Sleep       |   40 |    | NULL|
| 38 | user | demo.com:2720 | user_sa  | Sleep       |   40 |    | NULL|
| 39 | user | demo.com:2721 | user_sa  | Sleep       |   40 |    | NULL|
| 40 | user | demo.com:2722 | user_sa  | Sleep       |   40 |    | NULL|
| 41 | user | demo.com:2723 | user_sa  | Sleep       |   40 |    | NULL|
| 42 | user | demo.com:2724 | user_sa  | Sleep       |   40 |    | NULL|
| 43 | user | demo.com:2725 | user_sa  | Sleep       |   40 |    | NULL|
| 44 | user | demo.com:2726 | user_sa  | Sleep       |   40 |    | NULL|
| 45 | user | demo.com:2727 | user_sa  | Sleep       |   40 |    | NULL|
| 46 | user | demo.com:2728 | user_sa  | Sleep       |   40 |    | NULL|
| 47 | user | demo.com:2729 | user_sa  | Sleep       |   39 |    | NULL|
| 48 | user | demo.com:2732 | user_sss | Sleep       |   39 |    | NULL|
| 49 | user | demo.com:2733 | user_sss | Sleep       |   39 |    | NULL|
| 50 | user | demo.com:2734 | user_sss | Sleep       |   31 |    | NULL|
| 51 | user | demo.com:2735 | user_sss | Sleep       |   39 |    | NULL|
| 52 | user | demo.com:2737 | user_sss | Sleep       |   38 |    | NULL|
| 53 | user | demo.com:2738 | user_sss | Sleep       |   38 |    | NULL|
| 54 | user | demo.com:2739 | user_sss | Sleep       |   38 |    | NULL|
| 55 | user | demo.com:2740 | user_sss | Sleep       |   38 |    | NULL|
| 56 | user | demo.com:2741 | user_sss | Query       |    0 | freeing items                                                  | SELECT displayname FROM machine_info WHERE mid=1000452000                                        |
| 57 | user | demo.com:2742 | user_sss | Sleep       |   37 |    | NULL|
| 58 | user | demo.com:2743 | user_sss | Sleep       |    0 |    | NULL|
| 59 | user | demo.com:2744 | user_sss | Sleep       |   34 |    | NULL|
| 60 | user | demo.com:2745 | user_sss | Sleep       |   36 |    | NULL|
| 61 | user | demo.com:2746 | user_sss | Sleep       |   35 |    | NULL|
| 62 | user | demo.com:2756 | user_sss | Query       |    0 | freeing items                                                  | select hostname from machine where mid='NULL'                                                    |
| 63 | user | localhost:2759                       | user_sa  | Query       
|    0 | NULL                                                           | show processlist|
+----+---------+--------------------------------------+-------------+-------------+------+-------
58 rows in set (0.00 sec)

Answer 19

确认你上面的状态是在你几个窗口“死机”的时候的吗？
如果是，能否指出哪几个process 是窗口1,2,3 ?

Answer 20

确认你上面的状态是在你几个窗口“死机”的时候的吗？ 
----
这个是我现在在自己的环境上测试的，并不是客户那里“死机”实时的结果。
我的这个还在运行，跑了有7个小时了，估计明天差不多，从processlist上看，好像能运行出来结果，明天我再更新吧~
这么晚了还回复 太感谢了~ 

Answer 21

昨天窗口1，2 都有运行出结果了，并没有出现query死住的情况。在我的环境下再现不了

Answer 22

对，我问他们要log去。还有这个帖子我就先不结了，如果有问题我还在这个上面问。还望斑竹回答～