Mysql innodb myisam引擎聚簇索引主键比较长且有过长字段实验

最新推荐文章于 2023-05-06 00:09:09 发布

原创最新推荐文章于 2023-05-06 00:09:09 发布 · 94 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

231 篇文章

订阅专栏

本文探讨了使用PHP通过MyISAM和InnoDB引擎在MySQL中分别插入10000条数据的性能差异，并分析了查询速度和表结构优化对查询成本的影响。

数据准备

建立innodb 和myisam 表
利用php连接mysql,规则插入10000条数据,
利用php连接mysql,不规则插入10000条数据

建表MySQL

create table t1(
id char(64) not null,
ver int(11) not null default 0,
c1 varchar(3000),
c2 varchar(3000),
c3 varchar(3000),
primary key  (id),
key idver (id,ver)
) engine myisam charset utf8;

create table t2(
id char(64) not null,
ver int(11) not null default 0,
c1 varchar(3000),
c2 varchar(3000),
c3 varchar(3000),
primary key  (id),
key idver (id,ver)
) engine innodb charset utf8;

插入数据脚本

t1表插入的PHP代码

<?php
$conn = mysqli_connect("localhost","root","","test");
$time_start = microtime_float();

$str = str_repeat('m',3000);
for($i=1;$i<=10000;$i++) {
   $id = md5($i).md5(time());
   $sql = "insert into t1 values ('$id',$i,'$str' , '$str' , '$str')";
   $conn->query($sql);
}

$time_end = microtime_float();
echo 'insert cost:' , ($time_end - $time_start) , " seconds\n";
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

t2表将上面脚本里面的t1改成t2即可

<?php
$conn = mysqli_connect("localhost","root","","test");
$time_start = microtime_float();

$str = str_repeat('m',3000);
for($i=1;$i<=10000;$i++) {
   $id = md5($i).md5(time());
   $sql = "insert into t2 values ('$id',$i,'$str' , '$str' , '$str')";
   $conn->query($sql);
}

$time_end = microtime_float();
echo 'insert cost:' , ($time_end - $time_start) , " seconds\n";
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

时间

myisam

╰─$ php test.php
insert cost:2.1865890026093 seconds

innodb

╰─$ php test.php
insert cost:8.7707788944244 seconds

测试查询

myisam

set profiling=1;

select id from t1 order by id;

select id from t1 order by id,ver;

show profiles;

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00806300 | select id from t1 order by id     |
|        2 | 0.00877300 | select id from t1 order by id,ver |
+----------+------------+-----------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看出 myisam 没多大区别

innodb

select id from t2 order by id;

select id from t2 order by id,ver;

show profiles;

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00806300 | select id from t1 order by id     |
|        2 | 0.00877300 | select id from t1 order by id,ver |
|        3 | 0.07648800 | select id from t2 order by id     |
|        4 | 0.00538300 | select id from t2 order by id,ver |
+----------+------------+-----------------------------------+
4 rows in set, 1 warning (0.00 sec)

可以看出 innodb (Query_ID 3和4) 差别还是挺大的

删除 innodb中过长字段 c1 c2 c3

alter table t2 drop column c1;

alter table t2 drop column c2;

alter table t2 drop column c3;

select id from t2 order by id;

select id from t2 order by id,ver;

show profiles;

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00806300 | select id from t1 order by id     |
|        2 | 0.00877300 | select id from t1 order by id,ver |
|        3 | 0.07648800 | select id from t2 order by id     |
|        4 | 0.00538300 | select id from t2 order by id,ver |
|        5 | 0.00003900 | alter table drop column c1        |
|        6 | 4.43217200 | alter table t2 drop column c1     |
|        7 | 1.61069100 | alter table t2 drop column c2     |
|        8 | 0.92016800 | alter table t2 drop column c3     |
|        9 | 0.00013600 | show create table t2              |
|       10 | 0.00475000 | select id from t2 order by id     |
|       11 | 0.00464300 | select id from t2 order by id,ver |
+----------+------------+-----------------------------------+
11 rows in set, 1 warning (0.00 sec)

可以看出 innodb (Query_ID 10和11) 几乎没差别