往期专题请查看www.zhaibibei.cn
这是一个坚持Oracle,Python,MySQL原创内容的公众号
减少key_buffer_size参数大小
innodb_buffer_pool_size参数大小
关闭查询缓存
因为MyISAM不支持事务,所以当转化为InnoDB需要注意事务相关的参数影响
当在交互界面处理事务时,请确保在结束时commit或rollbacks事务
请确保开发的应用在结束时commit事务,并且可以在异常时rollback事务
减少大数据量的回滚,因为回滚是个高消耗的动作
如果需要使用INSERT插入大量的数据,请批量进行commit,而不是最后提交一次,若发生错误需回滚,请使用truncate而不是rollback
如果有大量的连续的DML操作,考虑设置autocommit=0,这样可减少I/O消耗并且可在错误时及时回滚
在跑报表等长时间的SQL时,可以设置autocommit=1,这是默认值
在InnoDB中我们可能会遇到死锁,一般情况下我们对于死锁无需关注,MySQL会自己处理,不过如果我们在error日志中发现大量的死锁,就需要我们检查应用并进行相应的处理
InnoDB比MyISAM消耗更多的磁盘空间
设置innodb_file_per_table参数开启独立表空间(5.6开始为默认值)
设置innodb_file_format参数为Barracuda以支持表压缩等特性
我们可以根据如下规范来操作
对于每张表必须要一个主键,用来提高查询效率,注意主键不要频繁的修改
在建表语句中定义主键,而不是后面使用alter table来定义
慎重确定字段类型,数字类型最优先,其次再是字符类型
如果没有主键可定义,可使用自增列来定义主键,根据表的数据量来决定自增列的最小类型
如果表中主键是长的数据类型,例如varchar,考虑新增一个无符号的自增列作为主键,并将原主键转换为UNIQUE NOT NULL索引
最好使用可以用来和别的表关联的列做主键
如果我们建表不定义主键,MySQL会默认建立一个隐藏自增主键,不过他说6字节的,可能会造成空间浪费,而且不能用于查询
我们可以使用如下命令直接转换
ALTER TABLE table_name ENGINE=InnoDB;
注意不要转换mysql数据库的系统表,否则将导致数据库无法启动
我们也可以通过克隆的方式来进行
首先新建一个结构相同的InnoDB表
使用如下命令查看原表结构
SHOW CREATE TABLE table_name\G
注意将ENGINE=MyISAM改成ENGINE=INNODB
之后使用如下语句导入数据
INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns
导入后将克隆的表重命名为原表
提速措施
增加InnoDB内存到物理内存的80%
增加InnoDB的日志文件
我们可以先导入数据再建索引
可暂时禁用唯一约束来提高速度,但要保证导入数据的唯一性
如果数据很大,我们可采取分批导入的方式一次只导入一部分数据
请保留2倍的空间用来转换,如果空间不够则会回滚,并且会非常慢
注意不要删除 ibdata文件
https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html
联系客服