logo头像

不忘初心 方得始终

MySQL优化

本文于1206天之前发表,文中内容可能已经过时。

引言

数据库在每个项目中都会用到,现在又分为两个门派,一种关系型数据库,常见的有MySQL、SQL Server、Oracle、DB2等。另一种是非关系型数据库,也就是NOSQL( Not Only SQL),常见的NOSQL数据库有Redis 、MongoDB、Cassandra等。数据库的优化直接影响到网站的性能,在这里记录一下MySQL的优化。

关于MySQL

MySQL 是一个跨平台的开源关系型数据库管理系统,目前 MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。比如淘宝、京东等知名公司也都在使用。

MySQL的存储引擎有分为很多种。MyISAM、InnoDB等。每个引擎的特性都不一样,可以在不同的情况下选择不同的存储引擎。

MySQL的优化

对于一个小项目来说,MySQL优化与否可能没有那么重要,带来的优化效果也没有那么明显。但是如果面对的是一个千万级的大表、千万级甚至上亿的数据量时,优化是必不可少的。那么要从如下几方面来做优化:

存储引擎

一般情况可以选择MyISAM存储引擎,如果需要事务支持必须使用InnoDB存储引擎。

MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

命名规则

本着约定优先于配置(Convention Over Configuration)的原则,表的命名规则一样很重要。

  • MySQL数据库、表、字段等名称统一使用小写,单词间用_下划线分隔。
  • 表名和字段名不宜过长(不超过64个字符)。
  • 建议数据库统一设置编码为utf8,不仅仅是为了应付数据库间导入导出过程中、因编码格式不统一而导致的恼人的乱码问题,也是因为utf8是一种万国码(Unicode)。

语句+索引

索引的合理建立和查询语句的优化可以迅速提升数据库性能。

设计阶段就需要预计QPS(Query Per Second)及数据规模,参考业务场景对数据的要求,合理设计表结构(参考mysql在线DDL问题),甚至违反设计范式做到适当冗余。生产环境分析慢日志,优化语句。索引的设计需要知道索引是怎么用的,比如innodb的加锁机制。

垃圾查询拖慢性能。不合理的schema设计也会导致数据存取慢。索引的作用不必多说,但如innodb下,错的索引带来的可能不只是查询变慢。

MySQL语句优化是我们最常见也是开发过程中最需要注意的。各种关键字的使用场合、多表之间的关联(据说阿里的要求是关联表不超多三个)、索引的合理使用、批量插入、批量更新、批量删除、临时表的使用等等。

缓存

当数据库的压力太大时可以将一部分压力转嫁到缓存(我常用的是Redis),其流程如下:



复制及读写分离

这个是大多数场景下都是必须的。因为复制可以实现备份、高可用、负载均衡。

其中读写分离可以在应用层做,效率高,也可以用三方工具,如360的atlas。

切分

切分包括垂直切分水平切分,实现方式上又包括分库、分表。

  • 垂直切分保证业务的独立性,防止不同业务争抢资源,毕竟业务是有优先级的。
  • 水平切分主要用于突破单机瓶颈。除了主主外,只有切分能真正做到将负载分配下去。
  • 切分后也可对不同片数据进行不同优化。如按时间切分,超过一定时间数据不允许修改,就可以引入压缩了,数据传输及读取减少很多。
  • 根据业务垂直切分。业务内部分库、分表。一般都需要修改应用。除分表外,其余实现不是很复杂。有第三方组件可用,但通用高效又灵活的方式,还是自己写client。
  • 垂直切分一般都要做,只不过业务粒度大小而已。
  • 分库有是经常用的,就算当前压力小,也尽量分出几个逻辑库出来。等规模上去了,很方便就迁移扩展。
  • 水平拆分有一定难度,但如果将来一定会到这个规模,又可能用到,建议越早做越好。因为对应用的改动较大,而且迁移成本高。

总结

MySQL总结可以说是: 优化SQL优化结构优化存储

对于MySQL的优化我还需要进一步提高,从表的设计建立到后期的维护考虑的问题有很多,每一步都需要注意。没有DBA,只有自己来实现。

微信打赏

赞赏是不耍流氓的鼓励