死宅学数据库设计

学习自:阿里新零售数据库设计与实战

还不如在家学习数据库设计

1 不一般的 CRUD 

1.1 批量插入

(1)一条失败,全部回滚

改动前

执行插入 sql

INSERT INTO `t_dept` VALUES
(40, 'ACCOUNTING', 'NEW YORK'), (50, 'RESEARCH', 'DALLAS'),
(60, 'SALES', 'CHICAGO'), (70, 'OPERATIONS', 'BOSTON');

结果:报了个错,表数据没变

(2)有异常的记录插入失败,其他记录插入成功

引入 ignore 

INSERT IGNORE INTO `t_dept` VALUES
(40, 'ACCOUNTING', 'NEW YORK'), (50, 'RESEARCH', 'DALLAS'),
(60, 'SALES', 'CHICAGO'), (70, 'OPERATIONS', 'BOSTON');

结果

1.2 不存在就插入,存在就更新

INSERT IGNORE INTO `t_dept` VALUES
(40, 'ACCOUNTING', 'NEW YORK'), (50, 'KAKA', 'DALLAS'),
(60, 'SALES', 'CHICAGO'), (70, 'OPERATIONS', 'BOSTON')
ON DUPLICATE KEY UPDATE dname=VALUES(dname);

1.3 要不要使用子查询

MySQL 默认关闭了缓存,即每个子查询都是相关子查询,要循环执行多次

select empno from t_emp where sal >
(
    # where 语句每过滤一条数据,子查询就会执行一次
    select sal from t_emp where empno = 1
) and empno != 1

MyBatis 等持久层框架开启了缓存功能,其中的一级缓存会保存子查询的结果,所以可以放心使用子查询

结论:SQL 控制台不使用子查询,持久层框架中可以使用

1.4 通过连接临时表来替代子查询

select e.empno from t_emp e join
# 建一张临时表,最先被执行,而且只会查询一次
(select sal from t_emp where empno =1) t
on e.sal > t.sal and e.empno !=1 

2 常见问题解决

2.1 逻辑删除还是物理删除

物理删除:把数据从磁盘中删除,释放存储空间,缩小数据表的体积,有利于提高读写性能,

缺点:

(1)数据恢复难度大,还需要停机恢复

(2)会造成主键的不连续

核心业务表的数据不建议做物理删除,只做状态变更。如:订单作废,账号作废,优惠券作废等。为了即不删除数据,又能缩小数据表体积,可以新建一张历史记录表,定时把逻辑删除的记录转移到历史记录表。

2.2 千万记录快速分页

select id, name from t_test limit 1000000,100

上面的分页 sql 需要遍历到第 100 0000 条记录,再往后取 100 条,查询记录越靠后,速度越慢

优化1:利用主键索引来加速分页查询

select * from t_test where id >= 1000000 limit 100

# 不适用于主键不连续的记录
select * from t_test where id >= 1000000 and id <= 1000100

select t.id, t.name from t_test t join 
(select id from t_test limit 1000000,100) tmp on t.id = tmp.id;

优化2:业务上限定不可以查询早期数据

2.3 如何在线修改表结构

线上业务系统运行过程中随意删改字段,会造成重大事故。常规的做法是业务停机,维护表结构,通常在三更半夜进行。如:12306 每天晚上业务停机进行表维护,第二天 6 点才能正常使用 app 购票。但是,不影响正常业务的表结构是允许在线修改的。

修改表结构使用的是表级锁,修改表结构时,会停止一切数据的读写操作。数据越多,锁表时间越长。如果修改表结果失败,还要还原表结构,耗时更长。

2.3.1 PerconaTookit 工具

原理:

(1)复制 t_order 的表结构到 t_order_new

(2)然后对 t_order_new 做结构修改

(3)t_order 的数据操作同步到 t_order_new,旧数据也拷贝到 t_order_new

(4)数据都同步完后,移除 t_order,将 t_order_new 更名为 t_order

2.4 删除数据如何避免锁表

InnoDB 采用的是行级锁,删改数据时,MySQL 会锁住记录,此时其他事务不能执行写操作,但是可以读数据。

如何减少并发操作的锁冲突

(1)把复杂的 SQL 语句,拆分成多条简单的 SQL 语句,因为复杂的 SQL 语句耗时长,即锁住记录的时间长。

(2)优化 SQL 语句,减少 SQL 的查询时间

2.5 订单号和流水号的区别

订单号是订单的唯一编号,会被用来检索,所以为整数类型

流水号是打印在购物单据上的字符串,便于阅读,但是不用查询

 

 

 

 

 

 

 

 

 

 

 

 

 

 

相关推荐
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页