mysql存储引擎MYISAM和INNODB对比测试

测试源码：

1header('Content-type:text/html;charset=utf-8;');
2//for($num=0;$num<8;$num++){
3    insert();
4    insert(false);
5//}
6function insert($myisam =true){
7    $mysqli = new mysqli('127.0.0.1','root','sa','t100w');
8    $start = getMillisecond();
9    $table = 'users';
10    $type = 'innodb_group_h';
11    if($myisam){
12        $table.='_myisam';
13        $type = 'myisam_group_h';
14    }
15    $sql = ' SELECT count(*),gender FROM `'.$table.'` group by gender having count(*)>10000';
16    /*for($i = 0 ;$i<10000;$i++) {
17        $data = array(
18            'username' => rand_string(),
19            'password' => '123456',
20            'gender' => rand_gender(),
21            'mobile' => $i % 6 == 0 ? rand_number() : '',
22            'email' => $i % 7 == 0 ? rand_string(10) : '',
23            'actived' => $i % 3 == 0 ? 1 : 0,
24            'created' => date('Y-m-d H:i:s', time()),
25            'is_del' => 0
26        );
27
28        $sql = 'insert into '.$table.'(username,password,gender,mobile,email,actived,created,is_del) VALUES(';
29        $j=0;
30        foreach ($data as $d) {
31            if($j==7)
32                $sql .="'$d'";
33            else
34                $sql .= "'$d'".',';
35            $j++;
36        }
37        $sql .= ')';
38        $mysqli->query($sql);
39        unset($data);
40    }*/
41    $mysqli->query($sql);
42    $exec_time = (getMillisecond()-$start);
43    $time_sql = "insert into time_looks(engine_table,exec_time) VALUES('$type','$exec_time')";
44    $mysqli->query($time_sql);
45    $mysqli->close();
46}

测试比较（极快表示小于等于1MS）

insert比较

myisam:平均5300MS innodb:平均22300MS

可以看出myisam写入速度是innodb的3-4倍

大小比较：

innodb占据空间近2倍于myisam

select比较

普通where子句

sqL:SELECT `id`, `username`, `password`, `gender`, `mobile`, `email`, `actived`, `created`, `is_del` FROM `users` WHERE actived=1 

myisan:极快 innodb:慢

sqL:SELECT `id`, `username`, `password`, `gender`, `mobile`, `email`, `actived`, `created`, `is_del` FROM `users` WHERE actived=1 and gender=1

制定越精确的条件，innodb速度提高。

like速度比较

myisan:极快 innodb:慢

group by 、 distinct

 select count(*) from users group by gender;

innodb稍胜

 select distinct(gender) from users where 1;

myisam稍胜

having

 SELECT count(*),gender FROM `users` group by gender by count(*)>10000

myisam近2倍

GROUP BY,WHERE,HAVING之间的区别和用法 1.WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。 2.GROUP BY 子句用来分组 WHERE 子句的输出。 3.HAVING 子句用来从分组的结果中筛选行。

update比较