缓存和数据库一致性/sql优化/主键外键索引

如何保证缓存与数据库的一致性

一般来说，如果允许缓存可以稍微的跟数据库偶尔有不一致的情况，也就是说如果你的系统不是严格要求 缓存 + 数据库 必须保持一致性的话，最好不要做这个方案。即：读请求和写请求串行化，串到一个内存队列里去。

串行化可以保证一定不会出现不一致的情况，但是它也会导致系统的吞吐量大幅度降低，用比正常情况下多几倍的机器去支撑线上请求。

Cache Aside Pattern
最经典的缓存+数据库读写的模式，就是 Cache Aside Pattern。

读的时候，先读缓存，缓存没有的话，就读数据库，然后取出数据后放入缓存，同时返回响应。更新的时候，先更新数据库，然后再删除缓存。

为什么是删除缓存，而不是更新缓存？

原因很简单，很多时候，在复杂点的缓存场景，缓存不单单是数据库中直接取出来的值。

比如可能更新了某个表的一个字段，然后其对应的缓存，是需要查询另外两个表的数据并进行运算，才能计算出缓存最新的值的。

另外更新缓存的代价有时候是很高的。是不是每次修改数据库的时候，都一定要将其对应的缓存更新一份？

也许有的场景是这样，但是对于比较复杂的缓存数据计算的场景，就不是这样了。

如果你频繁修改一个缓存涉及的多个表，缓存也频繁更新。但是问题在于，这个缓存到底会不会被频繁访问到？

举个栗子，一个缓存涉及的表的字段，在 1 分钟内就修改了 20 次，或者是 100 次，那么缓存更新 20 次、100 次；但是这个缓存在 1 分钟内只被读取了 1 次，有大量的冷数据。

实际上，如果你只是删除缓存的话，那么在 1 分钟内，这个缓存不过就重新计算一次而已，开销大幅度降低，用到缓存才去算缓存。

最初级的缓存不一致问题及解决方案

问题1：先更新数据库，再删除缓存。如果删除缓存失败了，那么会导致数据库中是新数据，缓存中是旧数据，数据就出现了不一致。

解决思路：先删除缓存，再更新数据库。如果数据库更新失败了，那么数据库中是旧数据，缓存中是空的，那么数据不会不一致。因为读的时候缓存没有，所以去读了数据库中的旧数据，然后更新到缓存中。

比较复杂的数据不一致问题分析

问题2：数据发生了变更，先删除了缓存，然后要去修改数据库，此时还没修改。一个请求过来，去读缓存，发现缓存空了，去查询数据库，查到了修改前的旧数据，放到了缓存中。随后数据变更的程序完成了数据库的修改。完了，数据库和缓存中的数据不一样了…

解决思路(1)：写请求先删除缓存，再去更新数据库，（异步等待段时间)再删除缓存（成功表示有脏数据出现）。

这种方案读取快速，但会出现短时间的脏数据。

解决思路(2)：写请求先修改缓存为指定值，再去更新数据库，再更新缓存。读请求过来后，先读缓存，判断是指定值后进入循环状态，等待写请求更新缓存。如果循环超时就去数据库读取数据，更新缓存。

这种方案保证了读写的一致性，但是读请求会等待写操作的完成，降低了吞吐量。

更新数据的时候，根据数据的唯一标识，将操作路由之后，发送到一个 jvm 内部队列中。

读取数据的时候，如果发现数据不在缓存中，那么将重新读取数据+更新缓存的操作，根据唯一标识路由之后，也发送同一个 jvm 内部队列中。

一个队列对应一个工作线程，每个工作线程串行拿到对应的操作，然后一条一条的执行。

这样的话，一个数据变更的操作，先删除缓存，然后再去更新数据库，但是还没完成更新。

此时如果一个读请求过来，读到了空的缓存，那么可以先将缓存更新的请求发送到队列中，此时会在队列中积压，然后同步等待缓存更新完成。

这里有一个优化点，一个队列中，其实多个更新缓存请求串在一起是没意义的，因此可以做过滤

如果发现队列中已经有一个更新缓存的请求了，那么就不用再放个更新请求操作进去了，直接等待前面的更新操作请求完成即可。

待那个队列对应的工作线程完成了上一个操作的数据库的修改之后，才会去执行下一个操作，也就是缓存更新的操作，此时会从数据库中读取最新的值，然后写入缓存中。

如果请求还在等待时间范围内，不断轮询发现可以取到值了，那么就直接返回；如果请求等待的时间超过一定时长，那么这一次直接从数据库中读取当前的旧值。

主键、外键、索引的区别

主键：唯一标识一条记录，不能有重复的，不允许为空。

​ 用来保证数据完整性。主键只能有一个。

外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值。

​ 用来和其他表建立联系用的。一个表可以有多个外键。

索引：该字段没有重复值，但可以有一个空值。

​ 是提高查询排序的速度。一个表可以有多个唯一索引。

SQL查询语句优化方法

优化sql语句来尽量使用已有的索引，避免全表扫描，从而提高查询效率。

1、在表中建立索引，优先考虑where、group by使用到的字段。

2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：

SELECT * FROM t -----优化方式：使用具体的字段代替*，只返回使用到的字段。

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：**如果是连续数值，可以用between代替。**如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

**如果是子查询，可以用exists代替。**如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

exist和in的区别：

select * from a where id in （select id from b） ;

select * from a where id exists （select id from b） ;

对于这样的sql查询同一个库，结果是一样的，但是查询速度对于不同情况，

差别较大；

**使用in ,sql语句是先执行子查询**，也就是先查询b表，在查a表，

**而使用exists是先查主表a ,再查字表b;** 对于主表数据较多时，我们

使用in速度比exist更快，反之，从表b较大时，使用exist插叙速度更快（都会使用索引）,

not in与not exists区别：not in 会进行全表扫描，不走索引，not exists会走索引。

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE ‘%li%’

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE ‘li%’

6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t2 WHERE score/10 = 9

SELECT * FROM t2 WHERE SUBSTR(username,1,2) = ‘li’

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9

SELECT * FROM t2 WHERE username LIKE ‘li%’

8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。