MySQL底层引擎性能对比及调优

引擎测试实验：
首先我准备了数据库mysql（5.7.20版本，默认InnoDB引擎），两张MySQL表（test1，test2）：
create table test1(
id int(11) not null auto_increment,
c1 varchar(32),
c2 varchar(32),
c3 varchar(32),
primary key (id)
);
create table test2(
id int(11) not null auto_increment,
c1 varchar(32),
c2 varchar(32),
c3 varchar(32),
primary key (id)
)engine=MyISAM;

image.png

新增耗时测试代码：

public class test {
    public static void main(String[] args) {        Date t1 = new Date();
        Statement stmt = JDBCUtil.getStatement(JDBCUtil.getConnection());        for(int i=1;i<=1000;i++){            String sql = "INSERT INTO test1 " +                    "VALUES ("+i+", '"+ CharacterUtils.getRandomString(32)+"','"+ CharacterUtils.getRandomString(32)+"','"+ CharacterUtils.getRandomString(32)+"')";            try {
                stmt.execute(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }        Date t2 = new Date();
        long m = t2.getTime() - t1.getTime();
        System.out.println("耗时:"+ m+"毫秒" );
    }
}

耗时:55737    毫秒
我们将test1改成test2，结果：
耗时:568    毫秒

查找耗时测试：
我们在Navicat完成，分别执行：
SELECT * from test1
SELECT * from test2
其结果分别是：

test1查询结果

test2查询结果

更新耗时测试：
String sql = "UPDATE test2 set c1 = 'ccc"+i+"' where id = " + i;

耗时:34978    毫秒
我们将test1改成test2，结果：
耗时:537    毫秒

删除耗时测试：
[SQL]
DELETE from test1

受影响的行: 1000
时间: 0.045s

[SQL]
DELETE from test2

受影响的行: 1000
时间: 0.003s

总括一下：

从数据上看，MyISAM的性能要比InnoDB快很多，但为什么5.5版本后mysql默认引擎会改成InnoDB？我们看看InnoDB特点和两个引擎的对比：

InnoDB特点：
1）、支持事务
2）、数据多版本读取（InnoDB+MyISAM+ISAM）
3）、锁定机制的改进
4）、实现外键

InnoDB与MyISAM区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin transaction和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

如何选择

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB；

3. 系统崩溃后，MyISAM恢复起来更困难，能否接受；

4.读写分离优化时，可以读库用MyISAM，写库用InnoDB；

5.MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

作者：Michael孟良
链接：https://www.jianshu.com/p/6d95da22070e