【MySQL】一些数据库调优策略

一、数据库调优措施

1.1、调优目标

• 吞吐量更大
• 响应速度更快

1.2、如何定位调优问题

• 用户的反馈（主要）
• 日志分析（主要）
• 服务器资源使用监控
• 数据库内部状况监控

1.3、调优步骤

第一步、选择合适的数据库管理系统

  如果对事务性处理以及安全性要求较高的话，可以采用SQL server、Oracle等商业的数据库产品。也可以采用开源的MySQL，事务处理采用InnoDB，非事务处理采用MyISAM。

第二步、优化表设计

  数据表的设计直接影响了后续的SQL查询语句。在使用MySQL的情况下，我们还可以根据不同表的使用需求，选择不同的存储引擎。此外，还有以下的优化原则：

• 表的结构遵循三范式的原则。这样可以减少冗余字段，减少更新、插入以及删除时的异常情况。
• 当查询比较多的时候，可以使用反范式化进行优化 ，增加冗余字段以提高查询效率。
• 表字段的数据类型选择。如果字段采用数值类型就不用字符类型，定长字符用CHAR，变长字符用VARCHAR。

第三步、优化逻辑查询

  SQL的查询优化，分为逻辑查询优化以及物理查询优化，其中，逻辑查询优化通过改变SQL语句的内容让SQL执行更加高效，方式有查询重写和语句等价变换。
  对于SQL的查询重写，包括子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除。

第四步、优化物理查询

  在确定了逻辑查询优化之后，采用物理优化技术（如索引），主要是索引的创建及使用。

第五步、使用Redis或Memcached作为缓存

  Redis和Memcached都可以将数据存放到内存中。Redis支持持久化，可以把数据保存到硬盘上，而Memcached仅仅是内存存储，不支持持久化。
  在对于查询响应要求高的场景，可以考虑内存数据库。

第六步、库级优化

• 读写分离
    如果读和写的业务量都很大，并且都在同一个数据库服务器中进行操作，那么数据库的性能就会出现瓶颈，此时可以采用读写分离的方式降低主数据库的负载，即可以使用主数据库来处理写操作，使用从数据库来处理读操作。

• 数据分片
    当数据量级达到千万级以上时，可以把一个数据库分成多份放到不同的数据库服务器上，减少对单一数据库服务器的压力。这就是分库分表。

二、优化MySQL服务器

• 优化服务器硬件
    如合理分布磁盘IO、配置多处理器等
• 优化MySQL参数
    如innodb_buffer_pool_size、key_buffer_size等

三、优化数据库结构

  数据库的结构设计需要考虑数据冗余、查询和更新的速度、字段的数据类型。

3.1、拆分表：冷热数据分离

  拆分表的思路是：把一个包含很多字段的表拆分成2个或者多个相对较小的表。这是由于有些字段的操作频率很高，而有些字段的操作频率很低，如果放在一个表里，每次查询都要读取整个记录，消耗资源较多。
  冷热数据分离的目的有：

• 减少磁盘IO，保证热数据的内存缓存命中率。
• 更有效的利用缓存，避免读入无用的冷数据。

3.2、增加中间表

  把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，从而提高查询效率。

• 首先，分析经常联合查询表中的字段；
• 然后，利用这些字段创建中间表，并将原来联合查询的表的数据插入中间表中；
• 最后，利用中间表来查询。

如果用户信息发生更改，出现中间表与原表信息不一致的问题怎么办？

  清空整张表的数据后，重新添加数据。或者使用视图，但是视图始终是对SQL语句的封装，并没有提升查询效率。

3.3、增加冗余字段

  相当于用空间换时间。牺牲一部分数据库空间，加快数据查询效率。

###33.4、优化数据类型

  优先选择符合存储需要的最小的数据类型。
  原因：列的字段越大，建立索引时需要的空间也就越大，那么一页中能够存储的索引节点数量就少，在遍历时所需要的IO次数也就越多，索引的性能就越差。

3.5、优化插入记录的速度

  对于使用 MyISAM的表：

• 禁用索引
• 禁用唯一性检查
• 使用批量插入
• 使用LOAD DATA INFILE导入

  对于使用InnoDB的表

• 禁用唯一性检查
• 禁用外键检查
• 禁止自动提交

3.6、使用非空约束

  在设计字段时，尽量使用非空约束

  优势：

• 可以省去对NULL值字段的检查，提高存储效率
• 非空字段也容易创建索引，索引NULL列需要额外的空间来保存，所以使用非空约束，可以节省空间。

3.7、分析表、检查表与优化表

  分析表主要分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费。

总结
  上述七个方法有利有弊：

• 修改数据类型，节省存储空间的同时，要考虑到数据不能超过取值范围
• 增加冗余字段时，要确保数据的一致性。
• 把大表拆分，意味着查询会增加新的连接，从而增加额外的开销和运维成本。

四、大表优化

  当MySQL单表记录数过大时，数据库CURD性能会明显下降。

4.1、限定查询的范围

4.2、读写分离

4.3、垂直拆分

• 垂直分库：数据库中数据表过多，将关联的数据表部署在同一个数据库上。
• 垂直分表：数据库中列过多，将一张数据表拆分成多张数据表，把经常一起使用的列放到同一张表里。

优缺点：

• 优点：可以使列数据变小，在查询时减少读取的块数、减少IO次数。并且，垂直分区可以简化表的结构，易于维护。
• 缺点：主键会出现冗余，需要管理冗余列，并会引起JOIN操作。

4.4、水平拆分

  水平分表仅是解决了单一表数据过大的问题，但是数据还是在一台机器上，并不能提升SQL的并发能力，所以水平拆分最好分库，来实现分布式。
  数据库分片的常见方案：

• 客户端代理
• 中间件代理