测试 mysql 单表全表扫描查询大数据测试

yakczh · November 18, 2013 · Last by luikore replied at November 20, 2013 · 6230 hits

环境 Intel(R) Core(TM) i3-3210 CPU @ 3.20GHz DDR3 mem 4g ST500DM002-1BD142 500g xp sp3 mysqld 5.6.13 建表

CREATE TABLE `test100w` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`subject` varchar(200) CHARACTER SET utf8 NOT NULL,
`content` text CHARACTER SET utf8 NOT NULL,
`ctime` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

插入测试数据

require "mysql2"

client = Mysql2::Client.new(
host: "127.0.0.1",
username: 'root',
password: 'xxxx',
database: 'bench',
port: 3306,
charset:'utf-8'
)

(100*10000).times do 
 puts "some"

rand =Random.new.rand(10000..99999)
now=Time.now.to_i
sql = "insert into  test100w (subject,content,ctime) values('测试标题_#{rand}','测试内容_#{rand}',#{now});";  
puts sql
results = client.query(sql)
end 

查询


require "mysql2"

#require 'random'
require 'benchmark'   

Benchmark.bmbm(10) do |t| 
     t.report{
client = Mysql2::Client.new(
host: "127.0.0.1",
username: 'root',
password: 'xxxx',
database: 'bench',
port: 3306,
charset:'utf-8'
)

rand =Random.new.rand(10000..99999)

sql="SELECT * FROM test100w where subject like '%#{rand}' "
puts sql
results = client.query(sql)
count=0
results.each do |row|
count=count+1
end
puts "#{count} rows "
}

end 

结果数据 100w 1.093750 200w 2.156250 400w 5.046875

大数据不是 1PB 起么?

You need to Sign in before reply, if you don't have an account, please Sign up first.