Innodb vs MYISAM

背景

世界杯什么的开幕式么……

一封邮件飞了过来

1
2
3
4
5
6
7
8
9
2018-06-15 00:07:26,479 [ERROR] [startQuertz_Worker-3] o.q.c.JobRunShell:211 Job DEFAULT.validateOrderTask threw an unhandled Exception:
org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'validateOrder' on target class [class com.air.tqb.quarz.F6MonitorJob] failed; nested exception is org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: CREATE TABLE tm_monitor_order_20180615 ( `pk_id` int(11) NOT NULL COMMENT '主键id', `storage_id` int(11) NOT NULL COMMENT '仓库id', `part_id` int(11) NOT NULL COMMENT '材料id', `order_no` varchar(25) NOT NULL COMMENT '批次单号', `number` decimal(18,2) NOT NULL COMMENT '库存数', `avg_price` decimal(18,6) NOT NULL COMMENT '含税价格', `avg_price_no_tax` decimal(18,6) NOT NULL COMMENT '除税价格', `supplier_id` bigint(20) unsigned DEFAULT NULL COMMENT '供应商id', `creator` bigint(20) unsigned NOT NULL COMMENT '创建人', `creationtime` datetime NOT NULL COMMENT '创建日期(库龄)', `modifiedtime` timestamp NULL DEFAULT NULL COMMENT '修改时间', `modifier` bigint(20) unsigned DEFAULT NULL COMMENT '修改人', `id_own_org` bigint(20) unsigned NOT NULL COMMENT '对应门店id', `id_source_bill` bigint(20) unsigned DEFAULT NULL COMMENT '来源单ID', `version` bigint(20) NOT NULL DEFAULT '0' COMMENT '乐观锁版本号', PRIMARY KEY (`pk_id`), KEY `IDX_PART_ID` (`part_id`), KEY `IDX_ORG` (`id_own_org`), KEY `IDX_STORAGE_ID` (`storage_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='批次主表备份'; INSERT INTO tm_monitor_order_20180615 (pk_id, storage_id, part_id, order_no, number, avg_price, avg_price_no_tax, supplier_id, creator, creationtime, modifiedtime, modifier, id_own_org, id_source_bill, version) SELECT pk_id, storage_id, part_id, order_no, number, avg_price, avg_price_no_tax, supplier_id, creator, creationtime, modifiedtime, modifier, id_own_org, id_source_bill, version FROM ts_storage_partinfo_order
### Cause: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Statement cancelled due to timeout or client request; nested exception is com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
at

还是处理一下邮件吧!

分析

创建该表的目的是用来监控期末期初数据 也可以作为部分报表的输入

那么该表插入需要超时花了多少时间呢???

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<update id="createOrderMonitorTable" timeout="600">
CREATE TABLE tm_monitor_order_${suffix} (
`pk_id` int(11) NOT NULL COMMENT '主键id',
`storage_id` int(11) NOT NULL COMMENT '仓库id',
`part_id` int(11) NOT NULL COMMENT '材料id',
`order_no` varchar(25) NOT NULL COMMENT '批次单号',
`number` decimal(18,2) NOT NULL COMMENT '库存数',
`avg_price` decimal(18,6) NOT NULL COMMENT '含税价格',
`avg_price_no_tax` decimal(18,6) NOT NULL COMMENT '除税价格',
`supplier_id` bigint(20) unsigned DEFAULT NULL COMMENT '供应商id',
`creator` bigint(20) unsigned NOT NULL COMMENT '创建人',
`creationtime` datetime NOT NULL COMMENT '创建日期(库龄)',
`modifiedtime` timestamp NULL DEFAULT NULL COMMENT '修改时间',
`modifier` bigint(20) unsigned DEFAULT NULL COMMENT '修改人',
`id_own_org` bigint(20) unsigned NOT NULL COMMENT '对应门店id',
`id_source_bill` bigint(20) unsigned DEFAULT NULL COMMENT '来源单ID',
`version` bigint(20) NOT NULL DEFAULT '0' COMMENT '乐观锁版本号',
PRIMARY KEY (`pk_id`),
KEY `IDX_PART_ID` (`part_id`),
KEY `IDX_ORG` (`id_own_org`),
KEY `IDX_STORAGE_ID` (`storage_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='批次主表备份';
INSERT INTO tm_monitor_order_${suffix}
(pk_id, storage_id, part_id, order_no, number, avg_price, avg_price_no_tax, supplier_id, creator, creationtime, modifiedtime, modifier, id_own_org, id_source_bill, version)
SELECT
pk_id,
storage_id,
part_id,
order_no,
number,
avg_price,
avg_price_no_tax,
supplier_id,
creator,
creationtime,
modifiedtime,
modifier,
id_own_org,
id_source_bill,
version
FROM ts_storage_partinfo_order
</update>

小伙伴用了600s都没能正确的产生 发生了事务回滚【要记得创建表等ddl无法回滚】

那么此时将产生一个表【但是数据为空】

再屡屡我们的需求:

我们对于该表的定位基本一次插入永不更新~【纯查询】

那么是否有必要使用innodb呢?

I have a table with 17 million rows. I need to grab 1 column of that table and insert it all into another table. Here’s what I did:

INSERT IGNORE INTO table1(name) SELECT name FROM main WHERE ID < 500001

InnoDB executes in around 3 minutes and 45 seconds

However, MyISAM executes in just below 4 seconds. Why the difference?

I see everyone praising InnoDB but honestly I don’t see how it’s better for me. It’s so much slower. I understand that it’s great for integrity and whatnot, but many of my tables will not be updated (just read). Should I even bother with InnoDB?

有人问了如上问题 其实innodb具有事务 那么必然需要大量的redolog来保证 同时为了维持较好的性能 innodb会使用较多的内存来缓存对应的数据【其消耗更多的磁盘空间】

MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance

那么我们可以简单认为oltp应用使用innodb更合适 而olap应用使用myisam更加合适【或许还有其他适合压缩型】

对于上述不会更新的表 我重建了之后如下

8f1c27c138ae35d7a347f71c45310f415a5.jpg

同时我们比较一下表占用的大小

比如

26355396582a241e8863c14752dd47e5c69.jpg

但是前一天备份大小如下

c3130cedbb4f492a579b4cc7fa4b44fee95.jpg

似乎更小嘛【innodb】===》不过没有数据噢(侧面说明了rows不准确)不过从733M缩小到了590M 同时时间减少了如此多【超过600s===》17s】

方案

考虑在创建表的时候使用如下

ENGINE = MyISAM

同时注意合理使用校验集~