mysql 分表问题：性能没有提升

[等待逆袭]

综述：分表后，insert 10W数据性能没有提升，以下是我的测试过程 mysql5.5
1、测试表
CREATE TABLE `user` (
`id` varchar(32) NOT NULL,
`username` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`firstName` varchar(255) DEFAULT NULL,
`lastName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=innodb
同时创建了user1,user2,user3的3个分表；建表语句都是一样的
2、测试过程
BEGIN
declare v int default 0;
declare v_age int ;
set v_age = floor(1+rand()*99);

if v_age>0 and v_age <=30 THEN
insert into user1(id,age)
values (REPLACE (uuid(),'-',''),v_age);
ELSEIF v_age>30 and v_age <=60 THEN
insert into user2(id,age)
values (REPLACE (uuid(),'-',''),v_age);
ELSEIF v_age<=100 THEN
insert into user3(id,age)
values (REPLACE (uuid(),'-',''),v_age);
end if;
END
3、使用mysqlslap进行测试
--------------分表测试-------------
D:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlslap -h127.0.0.1 -uroot -proot
--concurrency=100 --iterations=1 --create-schema=test --query="call init_user_sp
lit" --number-of-queries=100000 --debug-info
Benchmark
Average number of seconds to run all queries: 27.946 seconds
Minimum number of seconds to run all queries: 27.946 seconds
Maximum number of seconds to run all queries: 27.946 seconds
Number of clients running queries: 100
Average number of queries per client: 1000
---------------end---------------
-------------单表测试------------------
D:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlslap -h127.0.0.1 -uroot -proot
--concurrency=100 --iterations=1 --create-schema=test --query="insert into user(
id,age) values(REPLACE (uuid(),'-',''),floor(1+rand()*99))" --number-of-queries=
100000 --debug-info
Benchmark
Average number of seconds to run all queries: 26.650 seconds
Minimum number of seconds to run all queries: 26.650 seconds
Maximum number of seconds to run all queries: 26.650 seconds
Number of clients running queries: 100
Average number of queries per client: 1000
------------end----------------------
总结：性能表还慢了1s,不知道实际哪里没有设置对

[ACMAIN_CHM]

检查一下 explain select ... 看一下执行计划。

[等待逆袭]

引用 1 楼 ACMAIN_CHM 的回复:

检查一下 explain select ... 看一下执行计划。

explain select * from user 总表解析


explain select * from user1 字表解析

[等待逆袭]

自己顶一顶，

[等待逆袭]

引用 1 楼 ACMAIN_CHM 的回复:

检查一下 explain select ... 看一下执行计划。

mysql分表要想提高insert的效率必须把表分到不同磁盘，因为我在win7的测试，电脑只有一块硬盘