《Java架构师的第一性原理》41存储之MySQL第12篇数据库最佳实践

1 5个MySQL使用问题

1)关于count(*)

知识点:MyISAM会直接存储总行数,InnoDB则不会,需要按行扫描。

潜台词是,对于select count(*) from t; 如果数据量大,MyISAM会瞬间返回,而InnoDB则会一行行扫描。

实践:数据量大的表,InnoDB不要轻易select count(*),性能消耗极大。

常见坑:只有查询全表的总行数,MyISAM才会直接返回结果,当加了where条件后,两种存储引擎的处理方式类似。

启示:不管哪种存储引擎,都要建立好索引。

2)关于全文索引

知识点:MyISAM支持全文索引,InnoDB5.6之前不支持全文索引。

实践:不管哪种存储引擎,在数据量大并发量大的情况下,都不应该使用数据库自带的全文索引,会导致小量请求占用大量数据库资源,而要使用《索引外置》的架构设计方法。

启示:大数据量+高并发量的业务场景,全文索引,MyISAM也不是最优之选。

3)关于事务

知识点:MyISAM不支持事务,InnoDB支持事务。
实践:事务是选择InnoDB非常诱人的原因之一,它提供了commit,rollback,崩溃修复等能力。在系统异常崩溃时,MyISAM有一定几率造成文件损坏,这是非常烦的。但是,事务也非常耗性能,会影响吞吐量,建议只对一致性要求较高的业务使用复杂事务。
画外音:Can‘t open file ‘XXX.MYI‘. 碰到过么?
小技巧:MyISAM可以通过lock table表锁,来实现类似于事务的东西,但对数据库性能影响较大,强烈不推荐使用。

4)关于外键

知识点:MyISAM不支持外键,InnoDB支持外键。
实践:不管哪种存储引擎,在数据量大并发量大的情况下,都不应该使用外键,而建议由应用程序保证完整性。

5)关于行锁与表锁

知识点:MyISAM只支持表锁,InnoDB可以支持行锁。
分析
MyISAM:执行读写SQL语句时,会对表加锁,所以数据量大,并发量高时,性能会急剧下降。
InnoDB:细粒度行锁,在数据量大,并发量高时,性能比较优异。
实践:网上常常说,select+insert的业务用MyISAM,因为MyISAM在文件尾部顺序增加记录速度极快。楼主的建议是,绝大部分业务是混合读写,只要数据量和并发量较大,一律使用InnoDB。
常见坑
InnoDB的行锁是实现在索引上的,而不是锁在物理行记录上。潜台词是,如果访问没有命中索引,也无法使用行锁,将要退化为表锁。
画外音:Oracle的行锁实现机制不同。

启示:InnoDB务必建好索引,否则锁粒度较大,会影响并发。

MySQL备份

MySQL的备份分两大类

(1)物理备份(Physical Backup)

(2)逻辑备份(Logical Backup)

什么是物理备份?以拷贝文件的方式备份数据库内容,典型的,可以备份数据库的:

  • 数据文件/目录

  • 配置文件/目录

  • 日志文件/目录

物理备份有什么特点?一般来说,物理备份:

(1)速度比较快,它只是单纯的文件拷贝备份;

(2)大小比较小,文件往往是经过压缩的;

(3)往往要求数据库实例处于离线状态(offline),不允许数据库文件变更,以保证数据一致性;

画外音:即使企业版支持所谓的在线物理备份,也会加很大的锁,备份期间数据库可用性较差。

(4)备份的可移植性往往比较差,数据恢复时,必须是几乎相同的硬件体系结构;

画外音:windows下物理备份,不能直接应用恢复到Linux下的MySQL。

 

具体怎么实施物理备份呢?

既然是备份文件/目录,常见的文件工具就能搞定,例如:cp,scp,tar,rsync等。

画外音:还有一些MySQL工具,要么是企业版的,要么不支持所有存储引擎(例如:只支持MyISAM引擎的mysqlhotcopy)。

什么是逻辑备份?

备份数据库的逻辑结构,典型的玩法是,把数据库备份为一系列SQL语句:

  • create database;

  • create table;

  • create index;

  • insert …;

逻辑备份有什么特点?

一般来说,逻辑备份:

(1)通过访问MySQL服务,来获得数据库的结构信息与数据内容;

(2)速度比较慢,除了要将数据库信息转化为逻辑格式(logical format),很可能还要传回客户端备份程序;

(3)备份粒度更细,全库,单库,全表,单表都可以备份,所有存储引擎都能备份(包含MEMORY引擎);

(4)往往不能备份数据库日志,数据库配置文件;

(5)因为备份的是逻辑格式,可移植性非常好;

画外音:windows下备份,也能快速应用到Linux。

(6)备份时,数据库实例必须处于在线状态(online);

画外音:在线备份和离线备份,更多被称为“热备(hot)”和“冷备(cold)”。 

具体怎么实施逻辑备份呢?

常见的玩法是:

(1)mysqldump

(2)select … into outfile

画外音:再次强调,MEMORY引擎也可以逻辑备份。

99 直接读这些牛人的原文

58到家数据库30条军规解读

58到家MySQL军规升级版

MySQL千万级大表在线变更表结构

 

《Java架构师的第一性原理》41存储之MySQL第12篇数据库最佳实践

上一篇:VirtualBox搭建Android-x86


下一篇:android手机获取手机号