当前位置：首页 > news >正文

数据库冗余存储设计对读取性能影响的测试

news 来源：原创 2024/9/21 18:48:38

两张表

CREATE TABLE `test1` (
  `a` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `b` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `c` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `d` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `e` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `f` varchar(100) NOT NULL DEFAULT '0',
  `g` varchar(100) NOT NULL DEFAULT '',
  `h` varchar(100) NOT NULL DEFAULT '',
  `i` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8

CREATE TABLE `test2` (
  `i` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `j` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `k` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `l` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `m` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `n` varchar(100) NOT NULL DEFAULT '0',
  `o` varchar(100) NOT NULL DEFAULT '',
  `p` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8

使用存储过程各自填充100万条数据，使两张表的数据长度均达到约230MB

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`my_test_data`()
begin
 declare i int;
 declare ss char(100);
    set i=100001;
    while(i<=1000000) do
    
    	set ss=concat(md5(rand()*16777015), md5(rand()*16777015));
    
        INSERT INTO test.test1 
        (b, c, d, e, f, g, h, i) 
        VALUES( rand()*16777015, rand()*16777015, rand()*16777015, rand()*16777015, ss, concat(md5(rand()*16777015), md5(rand()*16777015)), concat(md5(rand()*16777015), md5(rand()*16777015)), i);

        INSERT INTO test.test2 
        (j, k, l, m, n, o, p)
        VALUES( rand()*16777015, rand()*16777015, rand()*16777015, rand()*16777015, ss, concat(md5(rand()*16777015), md5(rand()*16777015)), concat(md5(rand()*16777015), md5(rand()*16777015)));
       
        set i=i+1;
    end while;
end

然后使用mysqlslap分别压测两天SQL语句

第一条：

select d, e from test1;

第二条：

select test2.l, test2.m from test1 inner join test2 on test1.i = test2.i;

命令：

mysqlslap.exe -h127.0.0.1 -uroot -p --concurrency=2 --iterations=1 --create-schema=test --query=D:\logs\wwj.sql --engine=innodb --number-of-queries=100

结果：

第一条sql：

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 43.953 seconds
Minimum number of seconds to run all queries: 43.953 seconds
Maximum number of seconds to run all queries: 43.953 seconds
Number of clients running queries: 2
Average number of queries per client: 50

第二条sql：

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 202.594 seconds
Minimum number of seconds to run all queries: 202.594 seconds
Maximum number of seconds to run all queries: 202.594 seconds
Number of clients running queries: 2
Average number of queries per client: 50

可以看到反范式设计使读取性能提高到范式设计的5倍。