MySQL优化方案

MySQL2019-11-21 11:40:31560条
MySQL优化方案:首先应该选择合适的存储引擎,一般常用的是MyISAM与INNODB。其次,在项目对mysql并发操作中经常出现阻塞、死锁等现象。在数据库表设计方面,对表的逻辑结构设计的时候,应该尽量采用三范式来减少冗余字段(减少内存浪费),与避免更新异常。当项目并发量、数据量到达一定数量以后,单数据库的IO瓶颈,优化提升的效率就不是那么明显,这时候就要采用集群然后负载均衡。

对于MySQL优化,首先应该选择合适的存储引擎,一般常用的是MyISAM与INNODB。

  • myisam不支持事务和外键,只支持表级锁,存储快,效率高,没有事务日志,故障恢复数据较麻烦;
  • innodb支持事务、外键、行级锁、表级锁,有事务日志,恢复数据较方便,它需要花费资源去处理事务,所以效率比myisam低,储存比较慢。

基于myisaminnodb各自的特点,需要事务支持,更新操作较多的项目,我们选用innodb;而查询操作较多的,我们选择myisam,比如说新闻门户网站。

其次,在项目对mysql并发操作中经常出现阻塞、死锁等现象。

对于死锁,我们应该:

  1. 尽量以固定的顺序访问表和行,因为一个先锁A 在去B ,另一个先锁B 在去A 就容易发生死锁;
  2. 大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小;
  3. 为表添加合理的索引,如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。

对于阻塞,我们可以通过在满足项目需求的同时,将表的数据量尽量缩小,SQL语句调优、提高代码质量等方式去避免。

然后在数据库表设计方面:

  1. 首先,应该了解清楚项目需求;
  2. 其次,对表的逻辑结构设计的时候,应该尽量采用三范式来减少冗余字段(减少内存浪费),与避免更新异常。但是,根据业务需求分析也可以在适当地方采取反范式的设计,采用空间换时间来提升查询效率。
  3. 然后,表的命名应该遵循可读性原则,在字段选择上根据需求选择合适的类型,遵循数字优先、日期次之、字符串最后的原则,同时在选择具体的字段类型上也要结合需求选择最佳:
  • 比如当是小数类型时,不是高精度的用floatdouble,要求高精度,一定用decimal(会多占一个字节);
  • 当是字符串类型时,最大长度比平均长度要大很多这样的列时尽量要varchar,用char会浪费很多空格空间;
  • 存储基本能确定长度,或者长度变动不大的字段时用char,因为char不需要花其他时间去计算长度,也不需要额外的字节去记录长度,比如:电话、性别、MD5加密字符串等。
  • 当是日期类型时,如果存储日期不需要时分秒的尽量用date类型,字节少占空间少;如果希望时间随时区改变的业务,选择TIMESTAMP类;如果不用存储范围太大的时间选择timestamp类型,因为比datetime字节少,空间占用少,而且还有额外特性。

表建好以后:

  • 应该根据业务需要,在更新较少,辨识度较高,查询较多等的列上建立合适的索引:常见的索引有主键索引、普通索引、唯一索引、组合索引、全文索引等; 索引的数据结构有B+treee索引和hash索引:
  1. B+treee索引是innodb引擎默认使用的数据结构,它通过一定的算法,将mysql表中数据分磁盘存放,少量的磁盘读取即可做到大量数据的遍历;
  2. Hash索引根据hash算法将数据,精确的存放,来查找某个数据的时候,根据相同的算法一次就能找到,等值查询,效率非常高,但hash索引只能等值查询,而且容易发生hash碰撞。
  • 在日常书写sql的时候,多用explain去分析执行计划, 进行SQL语句优化,避免索引失效。

常见的索引失效有:

  1. 在类型为数字索引列做算术运算;
  2. like前面有%(解决:使用全文索引)
  3. 避免使用is not null判断(解决:在设计表的字段时,尽量避免null字段,通常使用特殊的数据进行占位,比如int not null default 0string not null default '');
  4. or条件判断(or的两个条件都有索引的话能够命中索引;其中一个条件没有索引,那么or会索引失效。)
  5. 避免使用select * 
  6. exists优于in
  7. 避免在字符串或者日期类型上进行函数运算;
  8. 最左前缀原则。
  • 虽然explain分析了sql语句,还是会有漏网之鱼,这个时候就需要一定的日志辅助,让我们快速定位到有问题的sql,即慢查询定位;

当项目并发量、数据量到达一定数量以后,单数据库的IO瓶颈,优化提升的效率就不是那么明显,这时候就需要采用其他的办法,比如集群然后负载均衡(nginx反向代理与负载均衡),比较主流的解决方案是读写分离主从复制。 因为在海量数据项目中,查询的业务一般能占到百分之70以上(查询效率基本能体现项目的效率),增删改业务较少但是往往比较耗时,为了避免增删改来拉低查询效率,会采用多个数据库来完成负载高并发。 主流思路是:一主多从(主库用于增删改,从库用于查询)。它的运行流程如下:

  1. 应用程序对数据库进行增删改,主库将操作记录到binary(二进制文件);
  2. 每个从库有一个IO线程去读取主库的日志,将执行的日志文件缓存到relay.log(中继日志);
  3. 从库中SQL线程读取本库的中继日志,并执行,完成数据的复制。
评论啦~
暂无评论,快来抢沙发吧!