数据库（一）数据库优化方式

一、数据表切分和数据库切分
（一）纵向分割（分割列），也叫纵向分表，垂直切分
通常，随着开发过程的推进，系统中主表的字段数会越来越多。但是一个表的字段个数，是受数据库规范和性能限制的。例如，SQL Server数据库中一个表最多可以包含1024个字段，而实际应用中一般不能超过246个字段，每行数据可以存储8060字节，另外，对于大数据表来说，列的数量直接影响存取速度。数据的存储结构对行的存取更加优化，而不是列的存取。下面，提出几种分割存储列的情形。
案例1：
对于一个博客系统，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。
表中存在下面的字段时需要隔离出去：
图片对象
附件（文件等）
大量的备注信息
几乎不被查询的列
总而言之，纵向分割的意思就是，在主表中存下经常查询经常更新的列，而一些不太常用的信息等等就分出去，存在另一个表中（主表和从表的主键是一样的，一对一的关系）。但是，纵向分割不改变系统中数据的行的数目，也就是说数据的总条数是不变的，只是把每一条的部分属性分出去了。

（二）横向分割（行分割），也叫横向分区，水平切分。注意横向叫分区，纵向叫分表。
（1）按照时间分类：（对于时效性强的数据）
如果数据的时效性很强，我们可以认为所有数据中，20%近期更新的数据能够满足业务80%的需求。例如，如果我们有5年的历史数据，那么就可以认为其中在1年内（20%）更新过的数据（也可以是1年内创建的数据），能满足80%的业务需求；所以我们可以把这张表拆成两个表，分别存储20%和80%的数据，以达到提高效率的目的。如果两张表仍然没有有效的提高性能，还可以利用“二八”定律再次分割。

（2）按照索引分割：（通用的分割）
考虑一个情况：我有上亿个用户，我该怎么存储用户？
采用横向分割的方式，把用户按照id分成多少份，每一份存在一个表中，主表存的是id索引，甚至可以设计成多级的分区。这样的话，不仅分散了存储的要求，而且分散了单标访问次数。如果是一张表，那么100万次访问，这个表就是100万次的交互。分区以后，100万次访问，对于每一张表可能是一万次的交互，大大减少了表的交互次数。

（三）数据库分割
一般情况下，开发人员习惯于给每个项目配置一个数据库。但是实际上我们可以给一个应用程序更多的数据库实例。比如，在一个网络游戏服务器中，经常会有账户数据库（用于认证）、存储数据库（用于存储状态）、日志数据库（用于存储监控状态）、地图数据库(用于存储地图状态)等等。类比到ERP系统中，我们可以把许多项目共同的部分抽象出来，分别存储在不同的数据库实例中。例如，用户信息、部门信息、系统日志信息等可以定制成通用的数据库，每个软件项目都可以去使用。

二、读写分离
（一）目的
读写分离的目的是：
一、缓解读这个操作的压力，毕竟大量的请求都是从数据库中读操作，而不是写操作。所以一般的互联网公司的读请求都远远大于写请求。
二、进行负载均衡，缓解单机的压力。
三、消除读写锁冲突从而提升数据库的读写性能。

注意点：
为什么这里会说实时性要求不那么严格的业务使用读写分离呢？因为更新数据的时候，首先是在主机上进行写操作，主机更新数据后再把数据更新到所有的从机上面，从这个操作可以看出，数据更新的延时性是有的。
延时性的最佳的解决方案就是按业务来路由请求：
首先就是关键的业务，读和写的请求都路由向主机，也就说主机完成读写操作。
然后是非关键的业务采用读写分离：读操作路由给从机，写操作路由给主机，也就是丛机读，主机写。

（二）读写分离的一致性
1.强一致性和弱一致性：
强一致性可以理解为系统中的某个数据被成功更新后，后续任何对该数据的读取操作都将得到更新后的值；
弱一致性 （相当于异步）系统并不保证续进程或者线程的访问都会返回最新的更新过的值。系统在数据写入成功之后，不承诺立即可以读到最新写入的值，也不会具体的承诺多久之后可以读到。
最终一致性：最终一致性是弱一致性的一个特例，系统会保证在一定时间内，能够达到数据一致的状态。

2.CAP理论
（1）CAP指的是一致性，可用性和分区容忍性
a.一致性（Consistency）：所有的节点上的数据时刻保持同步，强一致性；
b.可用性（Availability）：一直可以正常的做读写操作。简单而言就是客户端一直可以正常访问并得到系统的正常响应。用户角度来看就是不会出现系统操作失败或者访问超时等问题。
c.分区容忍性（Partition tolerance）：指的分布式系统中的某个节点或者网络分区出现了故障的时候，整个系统仍然能对外提供满足一致性和可用性的服务。也就是说部分故障不影响整体使用。

（2）CAP理论指出，CAP的一致性，可用性和分区容忍性，最多只能同时满足两个。
由于分布式系统的分区容忍性是必需要满足的，那么就是一致性和可用性的取舍，而往往在设计的时候，放弃一致性的强一致性的要求，在一致性那里采用最终一致性（一种弱一致性），来满足高可用性的要求。
一致性：不完全满足，是最终一致性。
可用性：满足，高可用性。
分区容忍性：必须满足。

当然，也有的系统追求强一致性，那么肯定会导致可用性有一些问题。

（三）同步方式：
同步方式是读写分离具体实现最终一致性的方案：
1.半同步复制：
主从不一致的原因是延时引起的,所以要消除这个延时的影响，可以从主库进行CUD操作时进行规避，办法就是等主从同步完成之后，主库上的写请求再返回，就是大家常说的“半同步复制”semi-sync。
方案优点：利用数据库原生功能，比较简单
方案缺点：主库的写请求时延会增长，吞吐量会降低

2.缓存记录写key法
CUD操作（写操作，包括增，删，改三个操作）
将某个库上的某个key要发生写操作，记录在cache里，并设置“经验主从同步时间”的cache超时时间，例如500ms 。同时主机修改数据库，并且开始同步（保证在500ms内完成）。
R操作（读操作）
（1）先到cache里查看，对应库的对应key有没有相关数据
（2）如果cache hit，有相关数据，说明这个key上刚发生过写操作，此时需要将请求路由到主库读最新的数据
（3）如果cache miss，说明这个key上近期没有发生过写操作，此时将请求路由到从库，继续读写分离。
使用缓存的方式的话，成本低，但是缓存挂了就崩了，可用性不高。

3.使用中间件
思路和缓存是一样的，在主机写的一段时间内，如果有读的请求，路由会将请求路由到主机上。

（1）所有的读写都走数据库中间件，通常情况下，写请求路由到主库，读请求路由到从库。
（2）记录所有路由到主库的key，在主从同步时间窗口内（假设是500ms），如果有读请求访问中间件，此时有可能从库还是旧数据，就把这个key上的读请求路由到主库。
（3）经验主从同步时间过完后，对应key的读请求继续路由到从库。
中间件带来的好处就是能保证数据的绝对一致性，但同时也带来成本上升的问题。

三、使用缓存
缓存指将全部或部分数据加载到缓存，当所查找数据可以从缓存获取时直接查缓存，缓存未命中时再读数据库。
解决问题：
主要解决数据查询速度、及数据库压力问题。缓存查询速度通常是数据库查询速度的几十、上百、上千（本地缓存）倍。同时由于大量读流量被缓存分担，也间接降低了数据库负载。
缓存的方式，相比于读写分离的话，优点是成本要低很多，缺点是不具备高可用性。

难点：
高可用问题：如缓存挂了，数据库容易瞬间被压垮。
缓存刷新、数据一致性问题：需要设计保证当数据更新后，缓存数据及时进行刷新。
对长期有效的缓存数据，可以基于消息队列，当数据做了更新后发布更新消息，缓存刷新进程订阅消息 ，执行缓存刷新。可以只置相应数据缓存过期，由缓存读操作中封装数据刷新逻辑；或加锁主动刷新缓存。
对只某段时间内有效的数据，可以在数据缓存时即设置失效周期，过期及被清理（依赖缓存框架的能力），如客户资料。（采用同样的数据刷新机制，可以更新时判断是否已缓存，已缓存则异步服务调用置缓存失效）。

四、SQL语句上的优化
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

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

4.应尽量避免在 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

5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’
若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2