数据库优化

MySQL数据库在发展周期中所面临的问题及优化方案，暂且抛开前端应用不说，大致分为以下五个阶段：

阶段一：数据库表设计

项目立项后，开发部门根据产品部门需求开发项目。

开发工程师在开发项目初期会对表结构设计。对于数据库来说，表结构设计很重要，如果设计不当，会直接影响到用户访问网站速度，用户体验不好！这种情况具体影响因素有很多，例如慢查询（低效的查询语句）、没有适当建立索引、数据库堵塞（锁）等。当然，有测试部门的团队，会做产品测试，找Bug。

1、数据类型选择（选择合适的数据类型）

数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题：

• 数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。

• 字符类型：非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。

• 时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。

• ENUM & SET：对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。

• LOB类型：强烈反对在数据库中存放 LOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点Java的营销专业人员来写 Java 代码一样。

    总结：
    a.使用较小的数据类型解决问题；
    b.使用简单的数据类型(mysql处理int要比varchar容易)；
    c.尽可能的使用not null 定义字段；
    d.尽量避免使用text类型，非用不可时最好考虑分表；
  

2、字符编码

字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

• 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间
• 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费
• MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率

3、适当拆分（减少数据库访问压力）

随着业务量越来越大，单台数据库服务器性能已无法满足业务需求，该考虑增加服务器扩展架构了。主要思想是分解单台数据库负载，突破磁盘I/O性能，热数据存放缓存中，降低磁盘I/O访问频率。

3.1、主从复制与读写分离

在生产环境中，业务系统通常读多写少，可部署一主多从架构，主数据库负责写操作，并做双机热备，多台从数据库做负载均衡，负责读操作。主流的负载均衡器：LVS、HAProxy、Nginx。

怎么来实现读写分离呢？大多数企业是在代码层面实现读写分离，效率高。另一个种方式通过代理程序实现读写分离，企业中应用较少，会增加中间件消耗。主流中间件代理系统有MyCat、Atlas等。

在这种MySQL主从复制拓扑架构中，分散单台负载，大大提高数据库并发能力。如果一台从服务器能处理1500 QPS，那么3台就能处理4500 QPS，而且容易横向扩展。

有时，面对大量写操作的应用时，单台写性能达不到业务需求。就可以做双向复制（双主），但有个问题得注意：两台主服务器如果都对外提供读写操作，就可能遇到数据不一致现象，产生这个原因是程序有同时操作两台数据库几率，同时的更新操作会造成两台数据库数据发生冲突或者不一致。

可设置每个表ID字段自增唯一：auto_increment_increment和auto_increment_offset，也可以写算法生成随机唯一。
官方近两年推出的MGR（多主复制）集群也可以考虑下。

3.2、 分库

分库是根据业务将数据库中相关的表分离到不同的数据库中，例如web、bbs、blog等库。如果业务量很大，还可将分离后的数据库做主从复制架构，进一步避免单库压力过大。

3.3、分表

有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

数据量的日剧增加，数据库中某个表有几百万条数据，导致查询和插入耗时太长，怎么能解决单表压力呢？你应该考虑把这个表拆分成多个小表，来减轻单个表的压力，提高处理效率，此方式称为分表。

分表技术比较麻烦，要修改程序代码里的SQL语句，还要手动去创建其他表，也可以用merge存储引擎实现分表，相对简单许多。分表后，程序是对一个总表进行操作，这个总表不存放数据，只有一些分表的关系，以及更新数据的方式，总表会根据不同的查询，将压力分到不同的小表上，因此提高并发能力和磁盘I/O性能。

分表分为垂直拆分和水平拆分：

垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。

把不常用的字段单独放在同一个表中；
把大字段独立放入一个表中；
把关联密切的字段放一个表中；

这样做的好处是非常明显的，具体包括：拆分后业务清晰，拆分规则明确、系统之间整合或扩展容易、数据维护简单。

水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。表的水平拆分用于解决数据表中数据过大的问题。

一般地，将数据平分到N张表中的常用方法包括以下两种：

（1）对ID进行hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值；
（2）针对不同的hashID将数据存入不同的表中；

表的水平拆分会带来一些问题和挑战，包括跨分区表的数据查询、统计及后台报表的操作等问题，但也带来了一些切实的好处：

（1）表分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度；
（2）表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
（3）需要把数据存放到多个数据库中，提高系统的总体可用性(分库，鸡蛋不能放在同一个篮子里)。	

3.4、分区

分区就是把一张表的数据根据表结构中的字段（如range、list、hash等）分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能。

注：增加缓存、分库、分表和分区主要由程序猿或DBA来实现。

上面几点的优化都是为了减少每条记录的存储空间大小，让每个数据库中能够存储更多的记录条数，以达到减少 IO 操作次数，提高缓存命中率。下面这个优化建议可能很多开发人员都会觉得不太理解，因为这是典型的反范式设计，而且也和上面的几点优化建议的目标相违背。

4、适度冗余

为什么我们要冗余？这不是增加了每条数据的大小，减少了每个数据块可存放记录条数吗？确实，这样做是会增大每条记录的大小，降低每条记录中可存放数据的条数，但是在有些场景下我们仍然还是不得不这样做：

被频繁引用且只能通过 Join 2张（或者更多）大表的方式才能得到的独立小字段。

这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新

5、尽量使用 NOT NULL

NULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。
很多人觉得 NULL 会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，也是一个很好的表结构设计优化习惯。

6、表的范式的优化

一般情况下，表的设计应该遵循三大范式。

阶段二：数据库部署

是时候运维工程师出场了，项目上线。

项目初期访问量一般是寥寥无几，此阶段Web+数据库单台部署足以应对在1000左右的QPS（每秒查询率）