题外话: 中华文化博大进深, 从学 Java 到数据库, 无一不体现出同一组件鱼和熊掌不可兼得的要义. 自然, 编程中安全和效率也很难同时做到完美, 这一次 InnoDB 和 MyISAM 又让我大开眼界.
好了, Talk is cheap,show you the code:
测试环境: Mysql 5.7.20-log,IDEA 2018
首先创建两张表: testinnodb,testmyisam,sql 如下:
1. 同时大批量插入数据(百万级, million), 小编采用了存储过程, 代码及测试结果如下:
下面代码在 IDEA 上运行即可:
同时插入 100W 条数据, MyISAM 耗时 38s 左右, 而 InnoDB 却耗时 76 分钟 4s 左右, 很明显可以看出 MyISAM 在处理速度上完胜 InnoDB, 但是如果实际项目中使用, 由于涉及到数据安全 (或者事物安全) 问题, 大多数公司还是选择了 InnoDB, 较少公司使用 MyISAM(得力于其在业务层的严格控制). 但 MyISAM 依然可以被我们使用在日志数据分析, 实验等环境中.
2. 再看其在删改查方面的对比
改查耗时
删除耗时
其实对比下来, 差距并没有插入数据那样夸张, 对于大多数要求事物安全的公司来说还是可以接受的.
PS: 你可以使用 mysql 插件 profile 来显示最近执行命令的持续时长, 用法如下:
mysql 默认是关闭 profiles 的, 你需要开启它,
查看是否开启命令: show variables like '%pro%';
小编已经把他开启了, 所以显示为 ON, 默认为 OFF.
开启命令: set profiling=1;
关闭命令: set profiling=0;
查询最近使用命令时长: show profiles;
根据 Query ID 查询单个命令详情: show profile for query 1;
根据 Query ID 查询单个命令 memory,source,cpu 等详情: show profile cpu for query 1; 或者 show profile all for query 1;
测试 Over, 接下来总结一下:
1.InnoDB 支持事物, 外键等高级的数据库功能, MyISAM 不支持. 需要注意的是, InnDB 行级锁也不是绝对的, 例如 mysql 执行一个未定范围的 sql 时, 也还是会锁表, 例如 sql 中 like 的使用
2. 效率, 明显 MyISAM 在插入数据的表现是 InnoDB 所远远不及的, 在删改查, 随着 InnoDB 的优化, 差距渐渐变小
3. 行数查询, InnoDB 不保存行数, 也就是 select 的时候, 要扫描全表, MyISAM 只需读取保存的行数即可, 这也是 MyISAM 查询速度快的一个因素.
4. 索引, InnoDB 会自动创建 Auto_Increment 类型字段的索引, 一般习惯应用于主键, 即主键索引(只包含该字段), 而 MyISAM 可以和其他字段创建联合索引.
除此之外, MyISAM 还支持全文索引(FULLTEXT_INDEX), 压缩索引, InnoDB 不支持.
备注: MyISAM 的索引和数据是分开的, 并且索引是有压缩的, 内存使用率就对应提高了不少. 能加载更多索引, 而 Innodb 是索引和数据是紧密捆绑的, 没有使用压缩从而会造成 Innodb 比 MyISAM 体积庞大不小.
InnoDB 存储引擎被完全与 MySQL 服务器整合, InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池. InnoDB 存储它的表&索引在一个表空间中, 表空间可以包含数个文件(或原始磁盘分区). 这与 MyISAM 表不同, 比如在 MyISAM 表中每个表被存在分离的文件中. InnoDB 表可以是任何尺寸, 即使在文件尺寸被限制为 2GB 的操作系统上.
5. 服务器数据备份. InnoDB 必须导出 SQL 来备份, LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的, 解决方法是首先把 InnoDB 表改成 MyISAM 表, 导入数据后再改成 InnoDB 表, 但是对于使用的额外的 InnoDB 特性 (例如外键) 的表不适用.
备注: 而且 MyISAM 应对错误编码导致的数据恢复速度快. MyISAM 的数据是以文件的形式存储, 所以在跨平台的数据转移中会很方便. 在备份和恢复时可单独针对某个表进行操作.
InnoDB 是拷贝数据文件, 备份 binlog, 或者用 mysqldump, 支持灾难恢复(仅需几分钟),MyISAM 不支持, 遇到数据崩溃, 基本上很难恢复, 所以要经常进行数据备份.
6. 锁的支持.**MyISAM 只支持表锁. InnoDB 支持表锁, 行锁 行锁大幅度提高了多用户并发操作的新能. 但是 InnoDB 的行锁, 只是在 WHERE 的主键是有效的, 非主键的 WHERE 都会锁全表的
使用场景建议:
1)可靠性高或者要求事务处理, 则使用 InnoDB. 这个是必须的.
2)表更新和查询都相当的频繁, 并且表锁定的机会比较大的情况指定 InnoDB 数据引擎的创建.
对比之下, MyISAM 的使用场景:
1)做很多 count 的计算的. 如一些日志, 调查的业务表.
2)插入修改不频繁, 查询非常频繁的.
MySQL 能够允许你在表这一层应用数据库引擎, 所以你可以只对需要事务处理的表格来进行性能优化, 而把不需要事务处理的表格交给更加轻便的 MyISAM 引擎. 对于 MySQL 而言, 灵活性才是关键.
引擎原理分析
MyISAM 索引结构: MyISAM 索引用的 B+ tree 来储存数据, MyISAM 索引的指针指向的是键值的地址, 地址存储的是数据. B+Tree 的数据域存储的内容为实际数据的地址, 也就是说它的索引和实际的数据是分开的, 只不过是用索引指向了实际的数据, 这种索引就是所谓的非聚集索引
主索引如下:
辅助索引如下:
因此, 过程为: MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引, 如果指定的 Key 存在, 则取出其 data 域的值, 然后以 data 域的值为地址, 根据 data 域的值去读取相应数据记录.
InnoDB 引擎的索引结构:
也是 B+Treee 索引结构. Innodb 的索引文件本身就是数据文件, 即 B+Tree 的数据域存储的就是实际的数据, 这种索引就是聚集索引. 这个索引的 key 就是数据表的主键, 因此 InnoDB 表数据文件本身就是主索引.
InnoDB 的辅助索引数据域存储的也是相应记录主键的值而不是地址, 所以当以辅助索引查找时, 会先根据辅助索引找到主键, 再根据主键索引找到实际的数据. 所以 Innodb 不建议使用过长的主键, 否则会使辅助索引变得过大.
建议使用自增的字段作为主键, 这样 B+Tree 的每一个结点都会被顺序的填满, 而不会频繁的分裂调整, 会有效的提升插入数据的效率.
主索引如下:
辅助索引如下:
上图, 可以看到叶节点包含了完整的数据记录. 这种索引叫做聚集索引. 因为 InnoDB 的数据文件本身要按主键聚集, 所以 InnoDB 要求表必须有主键(MyISAM 可以没有), 如果没有显式指定, 则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键, 如果不存在这种列, 则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键, 这个字段长度为 6 个字节, 类型为长整形.
而且, 与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址. 换句话说, InnoDB 的所有辅助索引都引用主键作为 data 域.
因此, 过程为: 将主键组织到一棵 B + 树中, 而行数据就储存在叶子节点上, 若使用 "where id = 13" 这样的条件查找主键, 则按照 B + 树的检索算法即可查找到对应的叶节点, 之后获得行数据. 若对 Name 列进行条件搜索, 则需要两个步骤: 第一步在辅助索引 B + 树中检索 Name, 到达其叶子节点获取对应的主键. 第二步使用主键在主索引 B + 树种再执行一次 B + 树检索操作, 最终到达叶子节点即可获取整行数据.
两种索引数据查找过程如下:
来源: http://stor.51cto.com/art/201809/582821.htm