int和binary的插入速度

问题描述

我阅读了帖子:How列数会影响性能吗?.看来，列数会显着降低插入速度.所以我创建了两个表:第一个有 100 个 tinyint 列和 100 个 smallint 列，第二个有一个二进制 (100) 列和一个二进制 (200) 列.所以这两个表具有相同的行长.

I read the the post: How number of columns affects performance ?. It seems that the number of columns will slow down the insert speed dramatically. So I created two table:The first with with 100 tinyint columns and 100 smallint columns, the second with one binary(100) column and one binary(200) column. So these two table have same row length.

更特别:

CREATE TABLE 'users'(

   'c0' tinyint(4) not null default '0',

   'd0' smallint(6) not null default '0',

   .....

   'c99' tinyint(4) not null default '0',

   'd99' smallint(6) not null default '0'

) ENGINE = InnoDB default CHARSET = utf8

CREATE TABLE 'users2'(

   'c0' binary(100) not null default '\0 *100',

   'd0' binary(200) not null default '\0 * 200'

) ENGINE = InnoDB default CHARSET = utf8

然后我从 mysql 工作台运行了以下两个程序.

Then I ran the following two procedure from mysql workbench.

create procedure insert1()

begin

    declare v_max int default 1000;
    declare v_counter int default 0;
    while v_counter < v_max do
         insert into user (c0, d0, c1, d1....c99, d99) values (0,0,0.....0);
         set v_counter = v_counter + 1;
    end while;
end

create procedure insert2()

begin

    declare v_max int default 1000;
    declare v_counter int default 0;
    while v_counter < v_max do
         insert into users2 (c0, d0) values (0x0000...00, 0x000....00);
         set v_counter = v_counter + 1;
    end while;
end

结果是:

调用 insert1():0.999 秒

call insert1(): 0.999 sec

调用 insert2():3.479 秒

call insert2(): 3.479 sec

由于这两个表的行长相同，而第一个表的列数更多(200 列)，因此我预计第一个表的插入速度应该比第二个表慢.有人可以帮助解释为什么会发生这种情况吗?提前致谢！

Since these two tables have the same row length, and the first one have more columns (200 columns), I expect the insert speed for the 1st table should be slower than the second one.Can someone help explain why this happens? Thank you in advance!

推荐答案

您必须将 binary(100) 更改为 binary(4)，将 binary(200) 更改为 binary(6).我听说 binary(n) 用于 n = 1 字节，而不是二进制数字.

you must change binary(100) to binary(4), binary(200) to binary(6).I've heard that binary(n) is for n = 1 byte ,not binary digit.

这篇关于int和binary的插入速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！