调试易

你的INSERT语句是什么？
你的表结构是什么？你的机器配置是什么？
。问题说明越详细，回答也会越准确！参见如何提问。（提问的智慧）

机器是自己的老PC（Pentium 4 + 1.5G）
mysql新安装的，配置文件没有做任何更改。表用的是innodb引擎，只有两个字段id和value, 我是把 insert into test_table values(id, value) 循环执行1000次，但每次的id和values是不一样的。

我还是把代码贴出来吧
[code=C++]inline SimpleResult query_write(Connection& con, const char* stmt)
{
return con.query().execute(stmt);
}void test()
{
Connection con("test", "localhost", "root", "mmm");

for (int i=1; i<=1000; ++i)
{
char buf[64] = {0};
sprintf(buf, "insert into test values (%d, %d)", i, i+100);
clock_t t = clock();
query_write(con, buf);
}}int main()
{
time_t t = time(0);
try
{
test();
}
catch(const std::exception& ex)
{
printf("%s\n", ex.what());
}
printf("OK, used %d sec\n", time(0) - t);
}
[/code]

不好意思，上面那个没格式化好SimpleResult query_write(Connection& con, const char* stmt)
{
return con.query().execute(stmt);
}void test()
{
Connection con("test", "localhost", "root", "mmm");

for (int i=1; i<=1000; ++i)
{
char buf[64] = {0};
sprintf(buf, "insert into test.test values (%d, %d)", i, i+100);
clock_t t = clock();
query_write(con, buf);
}}int main()
{
time_t t = time(0);
try
{
test();
}
catch(const std::exception& ex)
{
printf("%s\n", ex.what());
}
printf("OK, used %d sec\n", time(0) - t);
}

不要用C或者其它程序，直接在你的MYSQL中用存储过程来做。 或者1000条，用一条SQL语句试一下就可以了。insert into test.test values 
(1,101),
(2,102),
(3,103),
(4,104),
(5,105),
(6,106),
(7,107),
(8,108),
(9,109),
(10,110),
(11,111),
(12,112),
(13,113),
(14,114),
(15,115),
(16,116),
(17,117),
(18,118),
(19,119),
(20,120),
(21,121),
(22,122),
(23,123),
(24,124),
(25,125),
(26,126),
(27,127),
(28,128),
(29,129),
(30,130),
(31,131),
(32,132),
(33,133),
(34,134),
(35,135),
(36,136),
(37,137),
(38,138),
(39,139),
(40,140),
(41,141),
(42,142),
(43,143),
(44,144),
(45,145),
(46,146),
(47,147),
(48,148),
(49,149),
(50,150),
(51,151),
(52,152),
(53,153),
(54,154),
(55,155),
(56,156),
(57,157),
(58,158),
(59,159),
(60,160),
(61,161),
(62,162),
(63,163),
(64,164),
(65,165),
(66,166),
(67,167),
(68,168),
(69,169),
(70,170),
(71,171),
(72,172),
(73,173),
(74,174),
(75,175),
(76,176),
(77,177),
(78,178),
(79,179),
(80,180),
(81,181),
(82,182),
(83,183),
(84,184),
(85,185),
(86,186),
(87,187),
(88,188),
(89,189),
(90,190),
(91,191),
(92,192),
(93,193),
(94,194),
(95,195),
(96,196),
(97,197),
(98,198),
(99,199),
(100,200),
(101,201),
(102,202),
(103,203),
(104,204),
(105,205),
(106,206),
(107,207),
(108,208),
(109,209),
(110,210),
(111,211),
(112,212),
(113,213),
(114,214),
(115,215),
(116,216),
(117,217),
(118,218),
(119,219),
(120,220),
(121,221),
(122,222),
(123,223),
(124,224),
(125,225),
(126,226),
(127,227),
(128,228),
(129,229),
(130,230),
(131,231),
(132,232),
(133,233),
(134,234),
(135,235),
(136,236),
(137,237),
(138,238),
(139,239),
(140,240),
(141,241),
(142,242),
(143,243),
(144,244),
(145,245),
(146,246),
(147,247),
(148,248),
(149,249),
(150,250),
(151,251),
(152,252),
(153,253),
(154,254),
(155,255),
(156,256),
(157,257),
(158,258),
(159,259),
(160,260),
(161,261),
(162,262),
(163,263),
(164,264),
(165,265),
(166,266),
(167,267),
(168,268),
(169,269),
(170,270),
(171,271),
(172,272),
(173,273),
(174,274),
(175,275),
(176,276),
(177,277),
(178,278),
(179,279),
(180,280),
(181,281),
(182,282),
(183,283),
(184,284),
(185,285),
(186,286),
(187,287),
(188,288),
(189,289),
(190,290),
(191,291),
(192,292),
(193,293),
(194,294),
(195,295),
(196,296),
(197,297),
(198,298),
(199,299),
(200,300),
(201,301),
(202,302),
(203,303),
(204,304),
(205,305),
(206,306),
(207,307),
(208,308),
(209,309),
(210,310),
(211,311),
(212,312),
(213,313),
(214,314),
(215,315),
(216,316),
(217,317),
(218,318),
(219,319),
(220,320),
(221,321),
(222,322),
(223,323),
(224,324),
(225,325),
(226,326),
(227,327),
(228,328),
(229,329),
(230,330),
(231,331),
(232,332),
(233,333),
(234,334),
(235,335),
(236,336),
(237,337),
(238,338),
(239,339),
(240,340),
(241,341),
(242,342),
(243,343),
(244,344),
(245,345),
(246,346),
(247,347),
(248,348),
(249,349),
(250,350),
(251,351),
(252,352),
(253,353),
(254,354),
(255,355),
(256,356),
(257,357),
(258,358),
(259,359),
(260,360),
(261,361),
(262,362),
(263,363),
(264,364),
(265,365),
(266,366),
(267,367),
(268,368),
(269,369),
(270,370),
(271,371),
(272,372),
(273,373),
(274,374),
(275,375),
(276,376),
(277,377),
(278,378),
(279,379),
(280,380),
(281,381),
(282,382),
(283,383),
(284,384),
(285,385),
(286,386),
(287,387),
(288,388),
(289,389),
(290,390),
(291,391),
(292,392),
(293,393),
(294,394),
(295,395),
(296,396),
(297,397),
(298,398),
(299,399),
(300,400),
(301,401),
(302,402),
(303,403),
(304,404),
(305,405),
(306,406),
(307,407),
(308,408),
(309,409),
(310,410),
(311,411),
(312,412),
(313,413),
(314,414),
(315,415),
(316,416),
(317,417),
(318,418),
(319,419),
(320,420),
(321,421),
(322,422),
(323,423),
(324,424),
(325,425),
(326,426),
(327,427),
(328,428),
(329,429),
(330,430),
(331,431),
(332,432),
(333,433),
(334,434),
(335,435),
(336,436),
(337,437),
(338,438),
(339,439),
(340,440),
(341,441),
(342,442),
(343,443),
(344,444),
(345,445),
(346,446),
(347,447),
(348,448),
(349,449),
(350,450),
(351,451),
(352,452),
(353,453),
(354,454),
(355,455),
(356,456),
(357,457),
(358,458),
(359,459),
(360,460),
(361,461),
(362,462),
(363,463),
(364,464),
(365,465),
(366,466),
(367,467),
(368,468),
(369,469),
(370,470),
(371,471),
(372,472),
(373,473),
(374,474),
(375,475),
(376,476),
(377,477),
(378,478),
(379,479),
(380,480),
(381,481),
(382,482),
(383,483),
(384,484),
(385,485),
(386,486),
(387,487),
(388,488),
(389,489),
(390,490),
(391,491),
(392,492),
(393,493),
(394,494),
(395,495),
(396,496),
(397,497),
(398,498),
(399,499),
(400,500),
(401,501),
(402,502),
(403,503),
(404,504),
(405,505),
(406,506),
(407,507),
(408,508),
(409,509),
(410,510),
(411,511),
(412,512),
(413,513),
(414,514),
(415,515),
(416,516),
(417,517),
(418,518),
(419,519),
(420,520),
(421,521),
(422,522),
(423,523),
(424,524),
(425,525),
(426,526),
(427,527),
(428,528),
(429,529),
(430,530),
(431,531),
(432,532),
(433,533),
(434,534),
(435,535),
(436,536),
(437,537),
(438,538),
(439,539),
(440,540),
(441,541),
(442,542),
(443,543),
(444,544),
(445,545),
(446,546),
(447,547),
(448,548),
(449,549),
(450,550),
(451,551),
(452,552),
(453,553),
(454,554),
(455,555),
(456,556),
(457,557),
(458,558),
(459,559),
(460,560),
(461,561),
(462,562),
(463,563),
(464,564),
(465,565),
(466,566),
(467,567),
(468,568),
(469,569),
(470,570),
(471,571),
(472,572),
(473,573),
(474,574),
(475,575),
(476,576),
(477,577),
(478,578),
(479,579),
(480,580),
(481,581),
(482,582),
(483,583),
(484,584),
(485,585),
(486,586),
(487,587),
(488,588),
(489,589),
(490,590),
(491,591),
(492,592),
(493,593),
(494,594),
(495,595),
(496,596),
(497,597),
(498,598),
(499,599),
(500,600),
(501,601),
(502,602),
(503,603),
(504,604),
(505,605),
(506,606),
(507,607),
(508,608),
(509,609),
(510,610),
(511,611),
(512,612),
(513,613),
(514,614),
(515,615),
(516,616),
(517,617),
(518,618),
(519,619),
(520,620),
(521,621),
(522,622),
(523,623),
(524,624),
(525,625),
(526,626),
(527,627),
(528,628),
(529,629),
(530,630),
(531,631),
(532,632),
(533,633),
(534,634),
(535,635),
(536,636),
(537,637),
(538,638),
(539,639),
(540,640),
(541,641),
(542,642),
(543,643),
(544,644),
(545,645),
(546,646),
(547,647),
(548,648),
(549,649),
(550,650),
(551,651),
(552,652),
(553,653),
(554,654),
(555,655),
(556,656),
(557,657),
(558,658),
(559,659),
(560,660),
(561,661),
(562,662),
(563,663),
(564,664),
(565,665),
(566,666),
(567,667),
(568,668),
(569,669),
(570,670),
(571,671),
(572,672),
(573,673),
(574,674),
(575,675),
(576,676),
(577,677),
(578,678),
(579,679),
(580,680),
(581,681),
(582,682),
(583,683),
(584,684),
(585,685),
(586,686),
(587,687),
(588,688),
(589,689),
(590,690),
(591,691),
(592,692),
(593,693),
(594,694),
(595,695),
(596,696),
(597,697),
(598,698),
(599,699),
(600,700),
(601,701),
(602,702),
(603,703),
(604,704),
(605,705),
(606,706),
(607,707),
(608,708),
(609,709),
(610,710),
(611,711),
(612,712),
(613,713),
(614,714),
(615,715),
(616,716),
(617,717),
(618,718),
(619,719),
(620,720),
(621,721),
(622,722),
(623,723),
(624,724),
(625,725),
(626,726),
(627,727),
(628,728),
(629,729),
(630,730),
(631,731),
(632,732),
(633,733),
(634,734),
(635,735),
(636,736),
(637,737),
(638,738),
(639,739),
(640,740),
(641,741),
(642,742),
(643,743),
(644,744),
(645,745),
(646,746),
(647,747),
(648,748),
(649,749),
(650,750),
(651,751),
(652,752),
(653,753),
(654,754),
(655,755),
(656,756),
(657,757),
(658,758),
(659,759),
(660,760),
(661,761),
(662,762),
(663,763),
(664,764),
(665,765),
(666,766),
(667,767),
(668,768),
(669,769),
(670,770),
(671,771),
(672,772),
(673,773),
(674,774),
(675,775),
(676,776),
(677,777),
(678,778),
(679,779),
(680,780),
(681,781),
(682,782),
(683,783),
(684,784),
(685,785),
(686,786),
(687,787),
(688,788),
(689,789),
(690,790),
(691,791),
(692,792),
(693,793),
(694,794),
(695,795),
(696,796),
(697,797),
(698,798),
(699,799),
(700,800),
(701,801),
(702,802),
(703,803),
(704,804),
(705,805),
(706,806),
(707,807),
(708,808),
(709,809),
(710,810),
(711,811),
(712,812),
(713,813),
(714,814),
(715,815),
(716,816),
(717,817),
(718,818),
(719,819),
(720,820),
(721,821),
(722,822),
(723,823),
(724,824),
(725,825),
(726,826),
(727,827),
(728,828),
(729,829),
(730,830),
(731,831),
(732,832),
(733,833),
(734,834),
(735,835),
(736,836),
(737,837),
(738,838),
(739,839),
(740,840),
(741,841),
(742,842),
(743,843),
(744,844),
(745,845),
(746,846),
(747,847),
(748,848),
(749,849),
(750,850),
(751,851),
(752,852),
(753,853),
(754,854),
(755,855),
(756,856),
(757,857),
(758,858),
(759,859),
(760,860),
(761,861),
(762,862),
(763,863),
(764,864),
(765,865),
(766,866),
(767,867),
(768,868),
(769,869),
(770,870),
(771,871),
(772,872),
(773,873),
(774,874),
(775,875),
(776,876),
(777,877),
(778,878),
(779,879),
(780,880),
(781,881),
(782,882),
(783,883),
(784,884),
(785,885),
(786,886),
(787,887),
(788,888),
(789,889),
(790,890),
(791,891),
(792,892),
(793,893),
(794,894),
(795,895),
(796,896),
(797,897),
(798,898),
(799,899),
(800,900),
(801,901),
(802,902),
...
(999,1099),
(1000,1100);

有时候是不能用存储过程的。不然我何必insert 1000次，用多值的insert就好了。现在的情况是我需要把网络上的数据插入到mysql中，它传多少我就得插入多少，如果它一次性传得太多而这边的insert又处理不过来的话，程序就卡在那里了。

首先分清，是你的程序慢，还是MYSQL慢。

如果存储过程，或者多值插入的速度不慢，则问题不在MYSQL，而在你的程序配置上。比如驱动，代码。分析问题先从中一劈两半，减少需要分析的范围。

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 1000;
 
  WHILE v1 > 0 DO
        insert into test.test values (v1, v1); 
    SET v1 = v1 - 1;
  END WHILE;
END;call dowhile();
用这个存储过程试试你的插入速度。

多值插入很快，不到1秒。
不过程序需要从网络获取insert的数据，所以只能单条insert来测试。我把这些语句放在test.sql里， 然后用source 命令执行insert into test.test values (1, 101);
insert into test.test values (2, 102);
insert into test.test values (3, 103);
insert into test.test values (4, 104);
insert into test.test values (5, 105);
insert into test.test values (6, 106);
insert into test.test values (7, 107);
insert into test.test values (8, 108);
insert into test.test values (9, 109);
insert into test.test values (10, 110);
insert into test.test values (11, 111);
insert into test.test values (12, 112);
insert into test.test values (13, 113);
...
insert into test.test values(999, 1999);
这样也消耗了20秒的时间。

是不是因为innodb表支持事务的特性，导致了每次update或者insert的时候多了很多额外的操作，使得查询非常慢？

show create table  test.test ;

插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：连接：(3) 
发送查询给服务器：(2) 
分析查询：(2) 
插入记录：（1x记录大小） 
插入索引：（1x索引） 
关闭：(1) 
从你的描述来看，可以用INSERT DELAYED语句加快速度。

test表的建立语句：CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
我查了下网上的资料，都说如果有大规模的update和insert的话，用innodb效率高些。
但是我这个1000次insert，我把test表换成myisam引擎后反而在1秒内完成。这是我弄错了么？

你从哪下的资料？！根本没有这种说法！INNODB也没 这么慢，delete from  `test` 所有记录删除，后再试。

多谢 ACMAIN_CHM 兄一直不耐烦的解答～不早了，该休息了。

用的innodb引擎吗？
如果是请在my.ini中
配置
innodb_flush_log_at_trx_commit = 2 
或
innodb_flush_log_at_trx_commit = 0

从网络获取insert的数据？？？可以先存在表里 等数据量到一定数直接批量传过去。
批量执行比一条条执行效率高！

你把 insert values() 
     insert values() ...改成
    insert Values(),()...这样的形式

你用myisam还是innodb？两者性能差距特别大，innodb差不多是myisam的10倍（因为innodb是行级锁定）
不过你这个速度也实在是太慢了。
你把你的create table贴出来

配置
innodb_flush_log_at_trx_commit = 2  
等于
innodb_flush_log_at_trx_commit = 1这样速度就上去了.我机器大约一秒可以插入5500条数据.

按6楼的格式拼好test.sql,然后用source命令执行
其实就是insert的方式问题，mysql的帮助文档已经写得很清楚了
(至于怎么拼好test.sql，LZ应该会了吧？)

如果插入的数据很多（例如：100W条），可以批量插入（例如：每2000条数据拼好test.sql，执行Source命令）