MySQL优化方案

吴宇 2019-11-21 24℃ 0条

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线程读取本库的中继日志,并执行,完成数据的复制。
标签: mysql

非特殊说明,本博所有文章均为博主原创。