[草稿]数据库设计的一些好思路

[TOC]

01、如何理解“把计算放到应用层”?

高并发的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到应用层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，涉及CPU计算的还是放到应用服务器上吧。
举个例子：

SELECT * FROM orders WHERE inserttime < CURDATE();

应该优化为：

$curDate = date('Y-m-d');
$res = mysql_query('select * from orders where inserttime < $curDate');

02、如何理解“把外键概念放在应用层解决”?

外键是一种用于建立和加强两个表数据之间链接的特殊约束，通常用来约束和控制数据的完整性，但它的缺点也很明显：

外键增加了表结构变更及数据迁移的复杂性；
外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表；
大数据高并发业务场景会严重影响表连接及sql性能，若外键未创建索引将引发死锁；

其实完全可以在代码层面上实现外键的功能。比如删除主表和子表的订单数据，代码上可以先删除订单子表数据，再删除订单主表数据，在数据库层面把它拆成2个delete语句来实现。

03、为何要“禁止大表使用子查询”?

大表子查询会产生临时表，消耗较多内存与CPU，极大影响数据库性能，且一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低
因此应当尽量避免子查询，如果子查询不可避免，请在子查询中过滤掉尽可能多的行。

04、为何要“禁止跨库查询，避免跨表查询”?

跨表查询存在如下缺点：

随着单表数据量的不断增长，跨表查询性能日渐低下，跨库就更不用提了；
跨库查询，不利于日后的解耦拆分；

解决方案：

将单表查询做成api接口，可提供更高的效率；
代码层面实现跨表查询的功能。比如在CRM系统中，若要查询某个商家的所有订单，一般通过join商家和订单表来实现，如下如示：

select * from orders where supplierid in (select id from suppliers where name='海底捞')
select o.* from orders o inner join suppliers s on o.supplierid = s.id where s.name='海底捞'

可以通过重构代码，先通过商家名查询商家id，再通过商家id查询订单表，如下所示：

supplierids = select id from suppliers where name='海底捞'
select * from orders where supplierid in (supplierids)

还有一个跨库JOIN的解决思路：字段冗余

这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免join查询。
举例：“订单表”中保存“卖家Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。
限制：字段冗余能带来便利，是一种“空间换时间”的体现。但其适用场景也比较有限，比较适合依赖字段较少的情况。最复杂的还是数据一致性问题，这点很难保证，需要在业务代码层面上去保证。当然，也需要结合实际业务场景来看一致性的要求。就像上面例子，如果卖家修改了Name之后，是否需要在订单信息中同步更新呢？

05、如何避免在where子句中使用IN或OR来连接条件?

where条件中的in在逻辑上相当于or，所以语法分析器会将num in ('0','1')转化为num='0' or num='1'来执行。

OR策略：先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。实际没有利用num上索引，并且完成时间还要受tempdb数据库性能的影响。如果不产生大量重复值，可考虑用UNION ALL替代IN和OR，把子句拆开，拆开的子句应包含索引。

可将：SELECT id FROM t WHERE num in (10,20) 或者：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

06、利用延迟关联或者子查询优化超多分页场景

SELECT * FROM 表1 WHERE age > 20 LIMIT 1000000,10

这条语句需要load 100w数据，然后基本上全部丢弃，只取10条当然比较慢。

MySQL并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联：

SELECT a.* FROM 表1 a, (SELECT id FROM 表1 WHERE age > 20 LIMIT 1000000,10) b WHERE a.id = b.id

这样虽然也load了100w的数据，但是由于索引覆盖，要查询的所有字段都在索引中，所以速度会很快。

而在代码层面

程序端保留当前页的最小id、最大id（id是主键），降序情况下，
每次提取下一页的数据时，id < min_id order by id desc limit 100; 
每次提取上一页的数据时，id > max_id order by id desc limit 100;

07、读多写少场景下的 redis缓存与从库读写分离

数据库层面，如果是读多写少，针对于单个实例很难支撑的情况下，可以借助于只读实例。只读实例可以实现在线弹性的扩展读能力，读的业务请求可以实现隔离，例如可以把轻分析型以及拖数据类型在只读实例内完成。
如果业务的访问都用数据库支撑的话成本高昂，缓存可以代替一部分关系型数据库在读方面的请求。基于原理的设计以及成本方面考虑，缓存的读性能比关系型数据库好，性价比较高。

08、使用汇总表代替大表count

大表count是消耗资源的操作，甚至会拖慢整个库，查询性能问题无法解决的，应从产品设计上进行重构
例如某个大表需要频繁count的话，可以考虑使用汇总表，可参考：大表频繁count的改进

09、分库分表之取模分片

关于分库分表的最佳实践，可参考：分库分表最佳实践
至于取模分片，假设做了128张分表，如下示例找出 userid=130 对应的具体分表名称，比如 user_shard_002

DECLARE @userid bigint = 130
DECLARE @shardid varchar(4)
SET @shardid = @userid % 128
SELECT @shardid, 'user_shard_'+right('000'+@shardid,3)

10、基于自增特性的ID生成器

当数据库数据量达到一定量级后，需要进行数据拆分、分库分表操作，而利用数据库自有的自增特性产生的主键ID则实现简单，也可以保证唯一性、递增性
设计思路如下：

--.1.创建一张种子表
CREATE TABLE seed(id bigint identity(1,1) PRIMARY KEY, is_deleted bit);

--.2.写入一条新数据(状态初始为0)，并取出当前session新插入行的自增id值作为新的id种子，由于没有读取种子表，所以效率很高
INSERT INTO seed(is_deleted) VALUES(0);
SELECT @@IDENTITY, @@IDENTITY % 128 + 1; 

--.3.先标记这条数据的状态为1，再删除状态为1的所有数据，由于这张表正常情况只有3条数据，所以效率很高
UPDATE seed SET is_deleted = 0 WHERE id = @@IDENTITY
DELETE FROM seed WHERE is_deleted = 0 AND id < @@IDENTITY - 2

11、数据库心跳监测的设计

待补充...

数据库设计的一些好思路