应用开发实践之关系型数据库(以MySql为例)小结

本文主要是对现在事情中使用到的DB相关知识点的总结,应用开发领会到以下深度基本足以应对一样平常需求,再深入下去更偏向于DB自己的理论、调优和运维实践。
不在本文重点关注讨论的内容(可能会提到一些):

  • 详细的DQL、DML、DDL、DCL等语法
  • 基础性的观点,如主键、索引、存储历程(注:阿里巴巴规范中克制使用存储历程)等
  • 团结查询,我小我私家不太喜欢在应用中写过于庞大的SQL,性能和后续维护容易泛起问题
  • 可能会用到的详细DB特征,如oracle的DATA GUARD
    有一些属于基础知识或语法然则常用的信息,也会列一下,如join的用法。

一、基础

1. ACID

DB的四大特征,这里简朴归纳综合下不详细睁开。

  • 原子性(Atomicity):事务操作中的多条SQL,要么所有乐成要么所有失败,失败后回滚纰谬原有数据造成任何影响。
  • 一致性(Consistency):事务最先前和竣事后,数据库的完整性没有被损坏。如触发器、约束、级联回滚
  • 隔离性(Isolation):多个事务支持并发读写。详细隔离级别见后文。
  • 持久性(Durability):事务竣事后,修改是永远的,不丢失。

2. 范式

这里睁开讲比较庞大,实践中很少用到,一样平常知足1NF即可。
高一级必知足低一级。

  1. 1NF:每个属性都不能再分,即表的列是最原子的
  2. 2NF:在1NF基础上,消除非主属性对键的部门依赖。这里不注释非主属性和键的寄义,可以简朴以为是指不存在列A可以通过列B来获取,如“学生姓名-学号”这种y=f(x)的函数关系。
  3. 3NF:在2NF的基础之上,消除了非主属性对于码的传递函数依赖
  4. BCNF:对于关系模式R,若是每一个函数依赖的决议因素都包罗键,则R属于BCNF范式
    有兴趣可以参考:范式通俗明白:1NF、2NF、3NF和BNCF

二、事务

3. 事务的隔离级别

3.1 读征象

读征象是伴生于差异的隔离级别泛起的。读征象的场景都是在多个事务并发执行的条件下可能泛起的:

  • 脏读 —— 一个事务读取了另一个未提交事务执行历程中的数据。此时另一个事务可能会由于提交失败而回滚。
  • 不能重复读 —— 一个事务执行历程中多次查询统一条数据但返回了差异查询效果。这说明在事务执行历程中,数据被其他事务修改并提交了。
  • 幻读 —— 事务1先行查询了某种数据,在修改或插入提交之前,事务2对此类数据举行了插入或删除并提交,导致了事务1对预期效果的数目转变。

3.2 隔离级别

  • 未提交读(read uncommited):允许另外一个事务可以看到这个事务未提交的数据。
  • 提交读(read commited):保证一个事务提交后才气被另外一个事务读取,而不能读取未提交的数据。
  • 可重复读(repeatable read):保持读锁和写锁一直到事务提交,但不提供局限锁,因此不能制止幻读。
  • 可序列化(serializable):价值最高但最可靠的事务隔离级别,事务被处置为顺序执行。

3.3 隔离级别与读征象

差异的隔离级别可以防止读征象。

隔离级别 脏读 不能重复读 幻影读
未提交读 可能发生 可能发生 可能发生
提交读 可能发生 可能发生
可重复读 可能发生
可序列化

注:为什么提交读不能制止不能重复读?假设A事务需要读取两次变量a,第一次读取时a=10,执行历程中a被事务B修改酿成了20,那么A第二次读时a与第一次的效果差异。

3.4 查看DB的隔离级别

// 查看当前会话
select @@tx_isolation;
// 查看当前系统
select @@global.tx_isolation;

MySql 5.7.14-ALISQL版默认是提交读。

4. 事务流传性(Spring)

在多个含有事务方式的相互挪用时,事务若何在这些方式间流传。
spring支持7种事务流传行为:

  • propagation_requierd:若是当前没有事务,就新建一个事务;否则加入到这个已有事务中,这是最常见的选择。
  • propagation_supports:支持当前事务,若是没有当前事务,就以非事务方式执行。
  • propagation_mandatory:使用当前事务,若是没有当前事务,就抛出异常。
  • propagation_required_new:新建事务,若是当前存在事务,把当前事务挂起。
  • propagation_not_supported:以非事务方式执行操作,若是当前存在事务,就把当前事务挂起。
  • propagation_never:以非事务方式执行操作,若是当前事务存在则抛出异常。
  • propagation_nested:若是当前存在事务,则在嵌套事务内执行。若是当前没有事务,则执行与propagation_required类似的操作

Spring默认是propagation_requierd。
为了便于明白,将以上几种流传行为分类:

流传性的类型 当前不在事务中 当前在事务中 备注
propagation_requierd 新建一个事务 加入到当前事务 最常见的选择
propagation_supports 非事务执行 加入当前事务
propagation_mandatory 抛异常 加入当前事务
propagation_required_new 新建事务 挂起当前事务
propagation_not_supported 非事务执行 挂起当前事务
propagation_never 非事务执行 抛异常
propagation_nested 新建事务 嵌套事务内执行

事务挂起

指当前方式不再受所属的事务控制直到该方式竣事。好比A方式起了一个事务,挪用B方式时B挂起事务,那么B的所有DB操作都不再受A方式的事务控制,直到B执行竣事。

事务嵌套

嵌套的事务可以自力于当前事务提交或回滚。

三、性能与优化

5. 执行计划

确认SQL在现实执行时的执行情形,如是否走上索引、走了哪个索引、扫描行数、执行顺序(如多个select级联查询)

查看方式

explain XXX

解读

MySql: MySQL_执行计划详细说明

6. 索引相关

6.1 群集/非群集索引

  • 群集索引:逻辑上和物理上都是延续的,如主键,一样平常一个表只有一个群集索引
  • 非群集索引:逻辑上是延续的但物理上不是

以Mysql的InnoDB为例:
主键是群集索引。
唯一索引、通俗索引、前缀索引等都是二级索引(辅助索引)。

连系B+树的知识,对于群集索引,索引数据和存储数据是在一起的,好比id-age这个纪录。
对于非群集索引,只有索引数据,定位详细的纪录需要通过索引来找,也即通过索引找到id,再通过id找到id-age这条纪录。

6.2 笼罩索引

查询条件和效果所有在一个索引中,MySql不需要通过二级索引查到主键后再查一遍数据就可以返回查询数据。笼罩索引可以大大提升查询效率,举例

select a, b from table_x where c = XXX order by d;

其中a、b、c、d所有在索引中,那么这就是笼罩索引。

对于做不到笼罩索引的查询,查到主键后还要回到数据表中把数据查询出来,则称为__回表__。

6.3 索引有序性

对于团结索引,确立(a, b, c)相当于确立(a), (a,b), (a,b,c)。
在这个索引下,遵照”最左前缀原理“,即先按a排序,再按b排序,最后按c排序。
若是缺失了前一列,如where b = xxx,则走不上索引。
若是某一列不是等值匹配,如where a>10 and b = 1,则只能部门走上索引,b走不上索引。非等值匹配有<、>、!=、IN、LIKE等。

更完整的可以参考mysql组合索引的有序性

6.4 建立了索引但没有走上的缘故原由

  1. 使用了<、>、!=、IN、LIKE等(非最左的like,也即like ‘xxx%’是可以的)
  2. 使用or毗邻查询子句
  3. 预期使用团结索引,但现实上没有凭据最左前缀原理排序(见上文7.3节)
  4. 字符串类型没有使用引号
  5. 全表扫描比走索引快
  6. where子句中包罗了函数或表达式
    为什么你建立的数据库索引没有生效,索引失效的条件!

7. 行锁和表锁

select…for update,走上索引(含主键)是行锁,没走上就是表锁。然则若是索引匹配过多,也会酿成表锁。
[转载&整理&链接]mysql 通过测试’for update’,深入领会行锁、表锁、索引

8. 索引的B+树

https://www.cnblogs.com/tiancai/p/9024351.html
https://www.jianshu.com/p/9bd572b0a0d4
https://www.jianshu.com/p/23524cc57ca4

简朴归纳综合一下:
B树的中心节点和叶子节点都有不止一个要害字(key)。B树泛起的目的是削减磁盘臂移动的开销从而,只管削减读写的次数。
B+树与B树的差异在于,B+树的数据都在叶子节点上,中心件节点没有数据。
应用:由于B树最左前缀匹配的特征,若是用左模糊查询(like “%xxx”)是走不上索引的。

四、应用开发

9. 分页查询

查询第N页(下标从1最先)数据,每页巨细PageSize

// 先获取相符条件的总数
select count(1) from tableA where XXX
// 查询该页
// 偏移量,可选 offset = (pageSize-1) * N
// 行数 rows = pageSize
select row1, ..., rowN from tableA where XXX limit offset, rows

10. Join

10.1 语法

SELECT Table1.Row1, Table1.Row2, Table2.Row1
FROM Table1
INNER JOIN Table2
ON Table1.Row2 = Table2.Row2
ORDER BY Table1.Row1

10.2 种类

inner join( = join),都匹配才返回
left join,左表全返回不管右表有没有匹配
right join,右表全返回不管左表有没有匹配
full join,全返回,左表右表无论对方匹配都返回所有行

11. MyBatis缓存

MyBatis缓存分为两级:一级缓存,SqlSession级别;二级缓存,SqlSessionFactory级别。和通常命名习惯相反,二级缓存的作用局限大于一级缓存,缘故原由是,SqlSession是由SqlSessionFactory建立的。

MyBatis默认开启一级缓存,不开启二级缓存。一级缓存生效于统一个SqlSession,当这个session没有做任何update操作且查询完全相同时,会返回一样的数据。
此时,在并发环境下,很有可能会发生这种情形:在一台服务器A上延续查询两次,两次属于统一个SqlSession;中心另一个服务器B对表做了更新,A看到的第二次查询效果仍然是旧的。

关于缓存的细节,如若何判断“统一次查询”、缓存有效期、SqlSession原理,可以自行查阅。推荐mybatis中文官网,有许多原理的先容。
在实践中,spring和mybatis整合以后每次查询都市刷新sqlSession,即一级缓存是无效的。
MyBatis缓存系列
单独提一下,二级缓存的readOnly默以为false,统一条数据在内存中每个工具都是自力的,可修改相互不影响。可参考若何明白Mybatis二级缓存设置中的readOnly?

swupdate 之 readback handler

12. mybatis和hibernate

我在事情中绝大多数时间都用mybatis+spring/springboot写持久层,只有一个应用由于使用SpringDataJPA才对hibernate才做了一些领会。
看了一些资料,领会到二者在写法以外,性能的差异主要在于多表查询这个场景,hibernate会比mybatis慢一些,缘故原由是

hibernate为了保证POJO的数据完整性,需要将关联的数据加载,需要额外地查询更多的数据。

MyBatis和Hibernate相比,优势在那里? – 郑沐兴的回覆 – 知乎
此外,JPA若是想运行原生sql,可以使用EntityManager。

13. 水平扩展与垂直扩展

13.1 水平扩展——分库分表一样平常思绪

  • 按某一字段将一张表分片,如userId。分片方式:
    • 第X位到Y位的值
    • 字段hash值
    • 特殊值特殊处置,如某KA(Key Account要害客户)数据量较大,单独一个分表

13.2 水平扩展——历史库

按日期准时同步迁徙及清算线上数据
查询需要凭据日期路由到线上库或历史库

13.3 水平扩展——按营业拆表

按营业,已处置数据及未处置数据拆分。如已受理未申请单和已完结申请单离开保留。

13.4 垂直扩展

提供更多、更强、容量更大的硬件资源。

13.5 FailOver

在计算机术语中,故障转移(英语:failover),即当流动的服务或应用意外终止时,快速启用冗余或备用的服务器、系统、硬件或者网络接替它们事情。 故障转移(failover)与交流转移操作基本相同,只是故障转移通常是自动完成的,没有忠告提醒手动完成,而交流转移需要手动举行。 ——wiki

FailOver是从应用层面做的,不是单纯DB层面。

13.5.1 靠山

单库架构,一旦库挂掉整个服务不能用;
主备架构,切换时有时间延迟;
FailOver从漫衍上来看仍然是主备架构,然则增加了系统自动切换恢复能力。

13.5.2 头脑

和去IOE是一致的,用大量相对廉价的硬件,拆分服务,削减单点,提升整体的可用性。

13.5.3 交互模式

仅举两个最典型的例子,详细场景需要连系硬件能力和应用架构综合剖析。

13.5.3.1 记账型

特点:

  • 主备准实时同步,Failover库平时不做读写
  • 主备库表结构一致,Failover库不一定和主备库的表一致(可能会少一些不需要用到的表)
  • 账户型数据保持最终一致性即可

方案:

  • 按比列拆表拆库,降低单个库挂掉时影响用户数
  • 正常事情时,主备准实时同步,Failover库不读写
  • 主库发生异常时,切换到备库读,Failover库纪录操作信息。同时,营业操作只管分流到不依赖相关库到支路上。
  • 主库恢复时,不再写入Failover,将Failover库和主库内容做merge,回写主库,主库再同步备库

注:可以接纳双写、基于读库(上文中所述,行使oracle的data guard、mysql的replication等)、异步新闻等保证主备一致。

13.5.3.2 买卖流水型

特点:

  • 数据保证建立,不保证推进。即买卖下单失败,重新下单
  • failover库买卖号与主库通过某些位隔离,不重复

方案:

  • 和“记账型”类似,Failover库数据推进营业完成即可
  • 可以不回写failover时代的数据,依赖中心件读failover库中数据

13.6 读写星散

为领会决读大于多于写的场景下数据库瓶颈的一种架构模式。同样需要连系详细营业不能生搬硬套。
主要是一写多读的架构,在主库挂掉的场景下有可能需要思量使用paxos算法来决议新的主库。
在做读写星散前,可以先思量缓存是否能解决当前场景的问题。

五、运维

14. binlog

纪录DB操作(不含查询)及其他执行信息的二进制日志。
可以参考下面两篇文章简朴领会下。
【原创】研发应该懂的binlog知识(上)
【原创】研发应该懂的binlog知识(下)

六、其他话题

15. 琐屑的话题

想起来就补一些。

15.1 列的默认值

对于有默认值的非空列,若是在insert语句中指明晰这一列且值为null,插入仍然会报错,此时不会取默认值。让该列取默认值的方式是,不让该列泛起在insert语句中。

15.2 索引下推

MySql5.6做的优化之一,可以在like查询中提高性能。行使查询子句中能确定的查询条件,削减一次查询匹配到的索引,从而削减回表查询的数据。

16. 延伸话题

可以自行研究的话题,限于笔者接触局限和篇幅,不睁开来写。

  • 索引确立实践,是否越多越好,应该怎么选择索引列
  • hibernate和mybaits的区别,最大区别是mybatis需要手写sql,用一定的事情量更大的灵活性,利于优化和多表团结查询
  • redo log、undo log,与DB自己的星散
  • 以下内容可能被滥用,我在现实事情中几乎没有用到,有兴趣可以自行领会。
    • 触发器
    • union
    • 视图
  • 全表扫描时发生的filesort原理

附:”点评“ 《阿里巴巴JAVA开发手册》之MySql规范部门

开发中遵守一些事先约定好的规范,有助于提升研发效率(无论是小我私家照样团队内部或团队之间),制止犯一些重复错误,也有助于后续的维护。对于《阿里巴巴JAVA开发手册》中的规范,限于篇幅并没有写明缘故原由,笔者基于自己的开发履历举行一些点评,供参考。
本来是想针对《阿里巴巴JAVA开发手册》MySql规范部门这一部门补一下点评的,然则发现前两天新出的泰山版已经补上许多说明,没必要逐一点评,直接下载来看就好:https://files.cnblogs.com/files/wuyuegb2312/《Java开发手册(泰山版)》.pdf.zip

可以看出,前面一部门有许多规范都是和Java OOP相关联的。对于部门条目,是之前没注意到的,单独拉出来点评下。

count(*)和count(1)

【强制】不要使用 count(列名)或 count(常量)来替换 count(),count()是 SQL92 界说的标
准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。
说明:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。

官方文档提到,InnoDB下count(*)和count(1)是没有区别的:

InnoDB handles SELECT COUNT() and SELECT COUNT(1) operations in the same way. There is no performance difference.
但思量到其他实现对count(
)有优化(如MyISAM,条件是没有WHERE和GROUP BY子句,直接取缓存的总数),再思量到用其他DB的情形,统一起见一直用count(*)就好了。
更详细的剖析可以看 为什么阿里巴巴克制使用 count(列名)或 count(常量)来替换 count(*)

禁用外键

【强制】不得使用外键与级联,一切外键观点必须在应用层解决。
说明:(观点注释)学生表中的 student_id 是主键,那么成绩表中的student_id 则为外键。若是更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于单机低并发,不适合漫衍式、高并发集群;级联更新是强壅闭,存在数据库更新风暴的风险;外键影响数据库的插入速率。

克制使用外键,在本例中并不是不允许在成绩表中存放student_id字段,只是不设置成为外键即可,更新由应用层来做。

原创文章,作者:28x29新闻网,如若转载,请注明出处:https://www.28x29.com/archives/7367.html