本文详细介绍了数据库设计中的范式理论，以及如何在实践中应用以减少冗余和提高效率。涵盖了MySQL优化技巧、分库分表原则、ID生成策略（如分布式ID、Redis自增ID等），以及用户中台架构的选择和数据库一致性管理方法。
数据库三大范式
1 确保每列原子性
2 确保表中每一列都和主键相关
3 确保每列都和主键列直接相关，而不是间接相关（减少冗余数据的产生，但并不适合所有场景）
数据库设计
1 对地址采用国家、省、市、区等用多个字段存储方便后续使用
2 选择数字类型和文本类型尽量充足
3 每张表最好增加逻辑删除字段
4 表的索引数量适中（虽然可以提高select效率但会降低update和insert的效率）
5 设计表，适度冗余字段，减少join操作（join效率低）
（如果按照三大范式那么就得在订单表中添加商品id，在订单表中添加商品名称这样即可避免在订单查询的页面想要显示商品名称去查询订单和商品表）
6 对查询频繁的字段建立索引
7 设计表的时候，字段和表添加注释
8 表加上创建和修改时间以及创建人
9 尽量把所有的列定义为NOT NULL 减少非空字段
（not null效率更高）
（null其实是占空间的可以，这样设计可以节省空间）
10 数据库过大可以分库分表
mysql优化
1 避免使用select * 指明具体字段节省性能
2 选择合适的字段类型以及长度够用就行，不要什么字符串类型都是var（255）
例如 身份证，手机号码等都是定长的
3 不使用order by rand（）很消耗性能
可以根据条件去查询 过滤 然后随机返回不要直接使用
4 不要使用select count（*）from count会造成全表扫描
5 count（*/1/字段）的区别
1/*作用一样但是1的效率更高
count（字段）不统计为null的记录
count（主键）最快
6 优化分页方式提高分页查询效率，直接使用limit分页，页数越大查询速度越慢。
可以记录下每次查询的最大主键id来限制下一次的起点位置
比如 第一次查询记录下的id是5 那么下次sql则为select id from table where id>5 limit 1,5;
7 如果只更改一两个字段那么就不要update所有字段
8 避免在where的子句中对字段进行非空判断（is not null）是不走索引的直接进行全表扫描
9 尽量不适用%前缀模糊查询--会导致索引失效
10 对慢sql查询进行优化：尽量避免全表扫描，在where及order by涉及的列上建立索引
11 尽量避免在where子句中对字段进行表达式操作
算术运算会导致索引失效 高阶shardingjdbc关联查询也会导致关联查询结果异常
12 避免导致索引失效的场景发生
13 避免隐式类型转换 会导致索引失效
14 关注范围查询语句 导致部分索引失效的情况
对于联合索引来说，如果存在范围查询比如 between、>、<等条件时会导致后面的索引字段失败
15 大数量的表关联查询
关联前应尽量过滤数据量，可以减少集合量级，提高查询速度
16 大量数据的插入和更新使用批处理
用户中台架构设计
流量不是很高的情况下可以直接Mysql+Redis 性能瓶颈Mysql
第二版：mysql进行读写分离，读的压力给从节点，写的压力给主节点,读的压力比较大可以考虑布多台机器
从库做横向扩容（主从节点性能最好差不多，从节点性能低了可能导致主从延迟比较高）
缺点 redis容量容易不足
第三版：mysql读写分离+redis哨兵 redis读的压力给从节点，写的压力给主节点。
sentinel担当哨兵如果主节点挂了可让从节点替换上去 redis容量容易不足
第四版：mysql读写分离+redis集群 机器成本高
用户数据存储设计
读多写少和写多读少的属性可以进行分开存储做到冷热分离
使用关系型数据库 结构化明显 技术成本低 比如Mysql
分表（所有分表都放在一个数据库）
可以联查表查询 可以事务操作 但数据库连接有限
分表+分库（所有分表分散在不同数据库中）
数据库连接充足 不能联表查询 跨数据库事务操作不了
数据库连接数充足则进行分表 不充足则分库分表
缓存和数据库之间的一致性问题
常用策略：
写时加载缓存：不推荐，数据一旦写入混乱，得依靠下一次的写操作去恢复
读时候加载缓存：写操作只需要将数据更新完毕后删除缓存，交给读请求去处理缓存的同步
，但在进行数据更新过程中可能会读取到老数据有数据同步问题
数据同步问题：
1.延迟双删：数据库采用主从结构时对主数据库进行写入，对从数据库读时进行加载缓存但由于主从同步需要时间所以此时缓存不一定干净需要间隔一段时间后删除
实时性强，一致性弱
2.订阅Binlog日志进行数据同步
分布式ID
唯一标识 无规律,随机性强适用于用户id、订单id、消息id（聊天记录）
分布式ID生成器
用户id生成策略
设置三个值id阈值 初始化值 开始的值 步长
假设分别为150 100 100 50
那么这个发号器的id段为100到150，当id段用完时则数据库方面则将开始的值变为阈值，id阈值则增加步长的值
本地Id的生成
在初始化id生成器bean时就对数据库的数据进行读取，实现InitializingBean对afterPropertiesSet进行重写，使用Semaphore进行访问数量限制
// bean的初始化时会回调到这里 初始化bean的时候执行
@Override
public void afterPropertiesSet() throws Exception {
List<IdGeneratePO> idGeneratePOList = idGenerateMapper.selectList(null);
for (IdGeneratePO idGeneratePO : idGeneratePOList) {
tryUpdateMysqlRecord(idGeneratePO);
semaphoreMap.put(idGeneratePO.getId(), new Semaphore(1));
}
}对分布式id配置记录进行更新
先去对数据库进行更新，成功则将数据放在本地缓存中。更新失败则进行三次重试
/**
* @param idGeneratePO
* 更新分布式id配置记录
*/
private void tryUpdateMysqlRecord(IdGeneratePO idGeneratePO) {
int updateResult = idGenerateMapper.updateNewIdCountAndVersion(idGeneratePO.getId()
, idGeneratePO.getVersion());
if (updateResult > 0) {
localIdBOHandler(idGeneratePO);
return;
}
// 更新失败进行重试
for (int i = 0; i < 3; i++) {
idGeneratePO = idGenerateMapper.selectById(idGeneratePO.getId());
int update = idGenerateMapper.updateNewIdCountAndVersion(idGeneratePO.getId(), idGeneratePO.getVersion());
if (update > 0) {
localIdBOHandler(idGeneratePO);
return;
}
}
throw new RuntimeException("表id段占用失败，竞争过于激烈:" + idGeneratePO.getId());
}将拿到的数据放入本地缓存
private static void localIdBOHandler(IdGeneratePO idGeneratePO) {
long currentStart = idGeneratePO.getCurrentStart();
long nextThreshold = idGeneratePO.getNextThreshold();
long currentNum = currentStart;
if (idGeneratePO.getIsSeq() == SEQ_ID) {
LocalSeqIdBO localSeqIdBO = new LocalSeqIdBO();
AtomicLong atomicLong = new AtomicLong(currentNum);
localSeqIdBO.setId(idGeneratePO.getId());
localSeqIdBO.setCurrentNum(atomicLong);
localSeqIdBO.setCurrentStart(currentStart);
localSeqIdBO.setNextThreshold(nextThreshold);
localSeqIdBOMap.put(localSeqIdBO.getId(), localSeqIdBO);
} else {
LocalUnSeqIdBO localUnSeqIdBO = new LocalUnSeqIdBO();
localUnSeqIdBO.setId(idGeneratePO.getId());
localUnSeqIdBO.setCurrentStart(currentStart);
localUnSeqIdBO.setNextThreshold(nextThreshold);
long start = idGeneratePO.getCurrentStart();
long end = idGeneratePO.getNextThreshold();
ArrayList<Long> idList = new ArrayList<>();
for (long i = start; i < end; i++) {
idList.add(i);
}
// 洗牌算法打乱id
Collections.shuffle(idList);
ConcurrentLinkedDeque<Long> idQueue = new ConcurrentLinkedDeque<>();
idQueue.addAll(idList);
localUnSeqIdBO.setIdQueue(idQueue);
localUnSeqIdBOMap.put(localUnSeqIdBO.getId(), localUnSeqIdBO);
}
}拿到有序id
/**
* @param id
* @return
* 拿到有序id
*/
@Override
public Long getSeqId(Integer id) {
if (id == null) {
LOG.info("id错误:{}", id);
return null;
}
LocalSeqIdBO localSeqIdBO = localSeqIdBOMap.get(id);
if (localSeqIdBO == null) {
LOG.info("localSeqIdBO为{}", localSeqIdBO);
return null;
}
this.refreshLocalSeqId(localSeqIdBO);
long andIncrement = localSeqIdBO.getCurrentNum().getAndIncrement();
if (andIncrement > localSeqIdBO.getNextThreshold()) {
LOG.info("seqId 已 发 完");
return null;
}
return andIncrement;
}有序id的刷新机制：
当使用大于百分之75时即会进行刷新，semaphore没有拿到令牌也无法进行更新操作防止过多请求的进入，使用多线程加快效率。同时记得释放semaphore
/**
* @param localSeqIdBO 刷新本地有序id段
*/
private void refreshLocalSeqId(LocalSeqIdBO localSeqIdBO) {
long step = localSeqIdBO.getNextThreshold() - localSeqIdBO.getCurrentStart();
if (localSeqIdBO.getCurrentNum().get() - localSeqIdBO.getCurrentStart() > step * Update_RATE) {
Semaphore semaphore = semaphoreMap.get(localSeqIdBO.getId());
if (semaphore == null) {
LOG.info("[SeqId] 没有semaphore，id={}", localSeqIdBO.getId());
return;
}
boolean status = semaphore.tryAcquire();
if (status) {
// 异步进行同步id操作
LOG.info("本地id同步操作");
threadPoolExecutor.execute(new Runnable() {
@Override
public void run() {
try {
IdGeneratePO idGeneratePO = idGenerateMapper.selectById(localSeqIdBO.getId());
tryUpdateMysqlRecord(idGeneratePO);
LOG.info("本地id同步完成");
} catch (Exception e) {
LOG.info("error:", e);
} finally {
semaphoreMap.get(localSeqIdBO.getId()).release();
}
}
});
}
}
}无序id的刷新机制也一样
/**
* @param localUnSeqIdBO
* 刷新本地无序id段
*/
private void refreshLocalUnSeqId(LocalUnSeqIdBO localUnSeqIdBO) {
Long start = localUnSeqIdBO.getCurrentStart();
Long end = localUnSeqIdBO.getNextThreshold();
int size = localUnSeqIdBO.getIdQueue().size();
// 剩余空间不足则进行刷新
if ((end - start) * 0.25 > size) {
Semaphore semaphore = semaphoreMap.get(localUnSeqIdBO.getId());
if (semaphore == null) {
LOG.info("[UnSeqId] 没有semaphore，id={}", localUnSeqIdBO.getId());
return;
}
boolean status = semaphore.tryAcquire();
if (status) {
threadPoolExecutor.execute(new Runnable() {
@Override
public void run() {
try {
IdGeneratePO idGeneratePO = idGenerateMapper.selectById(localUnSeqIdBO.getId());
tryUpdateMysqlRecord(idGeneratePO);
LOG.info("无序id同步完成");
} catch (Exception e) {
LOG.info("error:", e);
} finally {
semaphoreMap.get(localUnSeqIdBO.getId()).release();
}
}
});
}
}
}
其他方案
UUID机制:结合机器网卡、当地时间、一个随机数生成UUID
优点:本地生成、生成简单、性能好、没有高可用风险
缺点：长度过长、无序不可读、MySQL的查询不方便，且进行大量写操作易造成叶子节点裂变
适合业务场景简单、没有大的并发量、无需太过考虑后续维护性
Redis自增Id:Redis所有命令操作都是单线程的，
本身提供像incr和increby这样的自增原子命令，所以能保证生成的Id肯定是唯一有序的
优点:不依赖数据库、灵活方便、且性能优于数据库，数字ID天然排序，对分页或者需要排序的结果很有帮助
缺点:存放在Redis中,如果内存淘汰策略选择不当，会导致丢失，不支持非连续性id生成
数据库自增ID：使用数据库id自增策略，如mysql的auto_increment.并可以使用两台数据库分别设置不同的步长
生成不重复的策略来实现高可用
优点：数据库生成的ID绝对有序，高可用实现方式简单
缺点：需要独立部署数据库实例，成本高，有性能瓶颈
Twitter的雪花算法:一串数字代表不同含义
最高位为0 （二进制补码中0为正数）41bit时间戳 10bit机器标识 12bit计数序列号
优点：高性能、低延迟、按时间有序、一般不会造成id碰撞
缺点：需要独立开发部署、依赖机器时钟
百度UidGenerator：底层也是基于雪花算法去实现
美团Leaf：是美团开源分布式ID生成器，能保证全局唯一性，趋势递增、单调递增、信息安全
也需要依赖关系数据库、ZOOKeeper等中间件
415
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
