数据库优化-SQL调优-为什么分库分表

一、为什么要分库分表？

减小数据库的负担，提高数据库的效率，缩短查询时间。

说白了，就是数据库扛不住了，需要解决。

---

二、数据库出现性能瓶颈表现方式

1. 在高并发的情况下，连接数不够了，大量请求都需要操作数据库，请求处于阻塞状态。
2. 操作数据变慢，数据库处理数据的效率除了问题， SQL 查询非常耗时。
3. 存储出现问题，比如单机存储的数据量太大了，存储的问题也可能会导致性能的问题。

三、数据库优化

从硬件方面来说，比如 CPU，内存，磁盘，网络等等，我们可以通过提高硬件，来解决这个问题。但是带来的收益和成本投入比例太高。所以重点还是从软件方面入手。

软件层面包括：SQL 调优、表结构优化、读写分离、数据库集群、分库分表等；

3.1、SQL 调优

调优的工具：explain 使用这个命令来查看 SQL 语句的执行计划，通过观察执行结果很容易就知道该 SQL 语句是不是全表扫描、有没有命中索引。

返回有一列叫“type”，常见取值有ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好），ALL 代表这条 SQL 语句全表扫描了，需要优化。一般来说需要达到range 级别及以上。

3.1.1 优化索引

1. 要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 比如要按照表字段name进行检索，那么就应该在姓名字段上创建索引，如果经常要按照部门和岗位级别进行检索，那么就应该在部门和岗位级别这两个字段上创建索引。
3. 一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
4. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
5. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
6. 不要在建立的索引的数据列上进行下列操作：避免对索引字段进行计算操作、避免在索引字段上使用not，<>，!=、避免在索引列上使用IS NULL和IS NOT NULL、避免在索引列上出现数据类型转换、避免在索引字段上使用函数、避免建立索引的列中使用空值。

3.1.2 优化where子句

1. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

    	如：select id from t where num is null
    	
    	应改为：select id from t where num=0  （设置num为非空，默认值0） 
   

2. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3. 应尽量避免在 where 子句中使用 or 来连接条件，如果两个一个有列索一个没有，将导致引擎放弃使用索引而进行全表扫描。

    	如：select id from t where num=10 or num=20 
    
     	应改为：select id from t where num=10  union all  select id from t where num=20
   

4. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

    	如：select id from t where num/2=100    
    
    	应改为：select id from t where num=100*2  
   

5. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

    	如：select id from t where substring(name,1,3)='abc'    --  查询name以abc开头的id    
    	
    	应改为：select id from t where name like 'abc%' 
   

6. 应尽量避免在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

7. in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in 了。

    	如：select id from t where num in(1,2,3)    
    	
    	应改为：select id from t where num between 1 and 3 
   

8. 尽量避免使用前导模糊查询，下面的查询也将导致全表扫描：

   	select id from t where name like '%abc%' 
   

3.1.3 优化字段属性

1. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
2. INNODB用VARCHAR比较好，定长的CHAR的优势只有在定长表（表中所有字段都是定长的）中才能体现。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

3.1.4 其他优化

1. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
2. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
3. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
4. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
5. 尽量将多条SQL语句压缩到一句SQL中，每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。
6. 避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤。
7. 使用表的别名，当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
8. 规范所有关键字的书写,select ,update,delete,要么全大写要不全小写。

3.2、表结构优化

3.2.1 选择合适的数据类型

1. 使用可以存下你的数据的最小的数据类型。
2. 使用简单的数据类型。int要比varchar类型在mysql处理上更简单。
3. 尽可能的使用not null定义字段。
4. 尽量少用text类型，非用不可时最好考虑分表。

3.2.2 适度冗余

为什么我们要冗余?这样做是会增大每条记录的大小，降低每条记录中可存放数据的条数。
但是在有些场景下我们仍然还是不得不这样做，举个栗子：

“user”表中有 user_id、nickname 等字段，“order”表中有order_id、user_id等字段，如果想拿到用户昵称怎么办？一般情况是通过 join 关联表操作，在查询订单表时关联查询用户表，从而获取导用户昵称。

但是随着业务量增加，订单表和用户表肯定也是暴增，这时候通过两个表关联数据就比较费力了，为了取一个昵称字段而不得不关联查询几十上百万的用户表，其速度可想而知。

这个时候可以尝试将 nickname 这个字段加到 order 表中（order_id、user_id、nickname），这种做法通常叫做数据库表冗余字段。这样做的好处展示订单列表时不需要再关联查询用户表了。

冗余字段的做法也有一个弊端，如果这个字段更新会同时涉及到多个表的更新，因此在选择冗余字段时要尽量选择不经常更新的字段。

3.3、读写分离、架构优化、数据库集群

对于数据库的服务，我们可以对它的架构进行优化。

如果只有一台数据库的服务器，我们可以运行多个实例，做集群的方案，做负载均衡。

或者基于主从复制实现读写分离，让写的服务都访问 master 服务器，读的请求都访问从服务器，slave 服务器自动 master 主服务器同步数据。

或者在数据库前面加一层缓存，让请求优先从缓存取数据减少数据库访问，达到减少数据库的压力，提升访问速度的目的。

3.4、分库分表详解

我们的系统从小到大，从简单到复杂，一般会经历这几个环节：

1. 单应用单数据库：一个系统包含多个基础功能模块，连接一个数据库，部署一个包。
2. 多应用单数据库：代码量越来越大，架构也变得越来越臃肿，现在随着系统访问压力逐渐增加，系统拆分就势在必行了。根据业务拆分系统，一个子服务打一个包，所有子服务共用一个数据库。
3. 多应用多数据库：随着业务推广力度加大，数据库终于成为了瓶颈，这个时候多个服务共享一个数据库基本不可行了。我们需要将每个服务相关的表拆出来单独建立一个数据库，这其实就是“分库”了。

3.4.1、分库

单数据库的能够支撑的并发量是有限的，拆成多个库可以使服务间不用竞争，提升服务的性能。

这种多个子系统共用一个 DB 的架构，会出现一些问题。第一个就是所有的业务系统都共用一个 DB，无论是从性能还是存储的角度来说，都是满足不了需求的。

随着我们的业务继续膨胀，我们又会增加更多的系统来访问核心数据库，但是一个物理数据库能够支撑的并发量是有限的，所有的业务系统之间还会产生竞争，最终会导致应用的性能下降，甚至拖垮业务系统。

所以，分库其实是我们在解决系统性能问题的过程中，对系统进行拆分的时候带来的一个必然的结果。现在的微服务架构也是一样的，只拆应用不拆分数据库，不能解决根本的问题。

3.4.2、分表

当我们对原来一个数据库的表做了分库以后，其中一些表的数据还在以一个非常快的增长，增长到一定阶段数据库查询效率就会出现明显下降。

所以，在分库之后，还需要进一步进行分表。当然，我们最开始想到的可能是在一个数据库里面拆分数据，分区或者分表，到后面才是切分到多个数据库中。

分表主要是为了减少单张表的大小，解决单表数据量带来的性能问题。

如何分表呢？ 分表有几个维度，一是水平切分和垂直切分，二是单库内分表和多库内分表。
垂直切分：基于表或字段划分，表结构不同。我们有单库的分表，也有多库的分库。
水平切分：基于数据划分，表结构相同，数据不同。也有同库的水平切分和多库的切分。

就拿用户表（user）来说，表中有7个字段：id,name,age,sex,nickname,description，如果 nickname 和 description 不常用，我们可以将其拆分为另外一张表：用户详细信息表，这样就由一张用户表拆分为了用户基本信息表+用户详细信息表，两张表结构不一样相互独立。

但是从这个角度来看垂直拆分并没有从根本上解决单表数据量过大的问题，因此我们还是需要做一次水平拆分。
举个简单的例子：比如我们把user表中id 为奇数的：1，3，5，7……放在 user1， id 为偶数的：2，4，6，8……放在 user2中，这样的拆分办法就是水平拆分了。
水平拆分的方式也很多，除了上面说的按照 id 拆表，还可以按照时间维度取拆分，比如订单表，可以按每日、每月等进行拆分。

以上的都是在一个数据库中进行分割，称之为单库拆分，那么多库拆分是什么样子的呢？

拿水平拆分为例，每张表都拆分为了多个子表，多个子表存在于同一数据库中。
比如下图用户表拆分为用户1表、用户2表。

在一个数据库中将一张表拆分为几个子表在一定程度上可以解决单表查询性能的问题，但是也会遇到一个问题：单数据库存储瓶颈。

所以在业界用的更多的还是将子表拆分到多个数据库中。比如下图中，用户表拆分为两个子表，两个子表分别存在于不同的数据库中。

一句话总结：分表主要是为了减少单张表的大小，解决单表数据量带来的性能问题。

3.4.3、分库分表带来的复杂性

既然分库分表这么好，那我们是不是在项目初期就应该采用这种方案呢？不要激动，冷静一下，分库分表的确解决了很多问题，但是也给系统带来了很多复杂性，下面简要说一说。

1、跨库关联查询
比如查询在合同信息的时候要关联客户数据，由于是合同数据和客户数据是在不同的数据库，那么我们肯定不能直接使用 join 的这种方式去做关联查询。
解决方案：

• 字段冗余：把需要关联的字段放入主表中，避免 join 操作；
• 数据抽象：通过ETL等将数据汇合聚集，生成新的表；
• 全局表：比如一些基础表可以在每个数据库中都放一份；
• 应用层组装：将基础数据查出来，通过应用程序计算组装；

2、分布式事务
比如在一个贷款的流程里面，合同系统登记了数据，放款系统也必须生成放款记录，如果两个动作不是同时成功或者同时失败，就会出现数据一致性的问题。如果在一个数据库里面，我们可以用本地事务来控制，但是在不同的数据库里面就不行了。所以分布式环境里面的事务，我们也需要通过一些方案来解决
解决方案：

• 全局事务（比如 XA 两阶段提交；应用、事务管理器™、资源管理器(DB)）
• 基于可靠消息服务的分布式事务
• 柔性事务 TCC（Try-Confirm-Cancel）tcc-transaction
• 最大努力通知，通过消息中间件向其他系统发送消息（重复投递+定期校对）

3、排序、分页、函数计算问题
跨节点多库进行查询时，会出现 limit 分页，order by 排序的问题。
解决方案：
先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终得到结果。

4、全局主键避重问题
MySQL 的数据库里面字段有一个自增的属性，Oracle 也有 Sequence 序列。如果是一个数据库，那么可以保证 ID 是不重复的，但是水平分表以后，每个表都按照自己的规律自增，肯定会出现 ID 重复的问题，这个时候我们就不能用本地自增的方式了。
解决方案：

• UUID（Universally Unique Identifier 通用唯一识别码），UUID 标准形式包含 32 个 16 进制数字，分为 5 段，形式为 8-4-4-4-12 的 36 个字符
• 基于数据库自增单独维护一张 ID表，把序号维护在数据库的一张表中。这张表记录了全局主键的类型、位数、起始值，当前值。当其他应用需要获得全局 ID 时，先 for update 锁行，取到值+1 后并且更新后 返回。并发性比较差。
• 雪花算法 Snowflake（64bit）4）雪花算法 Snowflake（64bit）
• Redis 缓存

四、总结

如果出现数据库问题不要着急分库分表，先看一下使用常规手段是否能够解决。

分库分表会给系统带来巨大的复杂性，不是万不得已建议不要提前使用。作为系统架构师可以让系统灵活性和可扩展性强，但是不要过度设计和超前设计。