Innodb vs MYISAM

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

一封邮件飞了过来

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

还是处理一下邮件吧！

分析

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

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

<updateid="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无法回滚】

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

再屡屡我们的需求：

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

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

有人问了如上问题 其实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更加合适【或许还有其他适合压缩型】

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

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

比如

但是前一天备份大小如下

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

方案

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

ENGINE = MyISAM

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