Skip to content

数据库快速上手

TIP

最近的工作逐渐从前端角色转变为全栈角色,恶补后端知识

库表篇

6大范式

1NF

保证字段的原子性,即字段的含义不能笼统,无法再进行拆分,比如姓名字段就只放姓名,而不是姓名、年龄一起放

2NF

保证所有字段依赖于主键,即所有字段与主键是直接依赖性,比如学生表里不需要成绩字段,可以将成绩拆分到成绩表中。 即可以通过主键id就可以确定其他字段的含义,一张表只对应一个业务属性 任何字段不能和主键没关系

3NF

保证每一列字段不能和主键之外的字段有直接关系,即学生表中, 同时存在学院id,学院院长两个字段,两者有直接依赖关系,那么应该是拆分单独的表去存储,仅存储学院id即可, 由学院id去关联学院表 确保除了主键外,每个字段不会互相依赖 任何字段不能和非主键字段有关系

BCNF

本质是对3NF的补充,要求联合主键中每个主键也不能存在直接依赖的关系。 联合主键是用于对数据唯一性进行确定时用的多个主键 联合主键之间互相不能有关系

4NF

解决了多值依赖的问题,即一个字段的确定需要多个字段才能决定,最经典的就是用户角色权限的设计了,如果不拆分用户表、角色表、用户角色表、权限表、角色权限表的情况下, 将用户、角色、权限都放在一个表中就会产生数据冗余 个人理解也等于是第一范式的表概念下的原子化设计

5NF

表字段的数据之间不能存在连接依赖。设计上就是将表最小化拆分设计,再重新组合查询后仍然保持数据完整性和一致性。 不对其做过多理解

后记

  • 之前生产环境中的排单中的表设计就有问题,应该将工单、产品、工序、项目拆分四个列 作为联合索引,额外增加代理键作为主键,所有的业务键都不应该作为主键使用
  • 通过复合索引、联合主键优化具备唯一属性的多个列,通过代理键优化Join、查询的性能消耗
  • 在具备代理键后,就可以通过拆分表,将机台表拆分出来,就无需通过单个字段通过字符串来冗余存储多个机台ID值

索引篇

功能类型

普通索引、唯一索引、主键索引、全文索引、空间索引 全文和空间不展开,空间用于地理; 全文用于大文本

  1. 主键索引默认会在确定主键后自动创建
  2. 唯一索引也可以是联合索引,需要确定组合之后的唯一值
  3. 空间索引用于空间地理
  4. 普通索引即Non-Clusetered类型

存储方式

  1. 聚簇索引, Mysql仅有一个,SqlServer不限制, 通常主键会自动创建对应的主键索引就是该类型
  2. 非聚簇索引,也称非聚集索引、二次索引、辅助索引、次级索引

常用索引的创建方式

普通索引、联合

sql
CREATE INDEX IDX_HRS_AutomaticSchedulingData_LotNo
  ON dbo.HRS_AutomaticSchedulingData(LotNo);
CREATE INDEX IDX_HRS_AutomaticSchedulingData_LotNo_Product
  ON dbo.HRS_AutomaticSchedulingData(LotNo,Product);

主键索引

主键会自动创建,无需关心

唯一索引、联合唯一
sql
CREATE INDEX IDX_HRS_AutomaticSchedulingData_LotNo
  ON dbo.HRS_AutomaticSchedulingData(LotNo);
CREATE UNIQUE INDEX IDX_HRS_AutomaticSchedulingData_LotNo
  ON dbo.HRS_AutomaticSchedulingData(LotNo);

唯一的特性就是单列不重复,或者联合索引时 组合起来的值保证不重复

前缀索引

Sql Server不支持前缀索引,可以通过冗余字段,将该额外字段的取值为对应字典的LEFT(Column,10)来存储,后续查询时通过对应截断来查询

sql
ALTER TABLE dbo.YourTable
ADD PrefixColumn AS LEFT(YourColumn, 10) PERSISTED;

SELECT * FROM dbo.YourTable WHERE LEFT(YourColumn, 10) = 'abc1234567';

回表

SELECT的时候 索引里没有要的全部列就要回主键补数据,比如SLECT * 就一定会导致这个问题 Sql Server中非聚集索引叶子节点存储的是 索引键+对应行的聚集索引键。 如果查询时的所有列都能从非聚集索引或聚集索引中获取,就不在需要回表(Key LookUp) 当Select的字段不在非聚集索引且也不在聚集索引中才会发生回表 想要解决这类问题还可以通过覆盖索引解决

Mysql回表

SqlServer对于回表是有优化的,只要查询的列在非聚集索引或者聚集索引能获取就不会发生回表。 Mysql即使聚集索引获取不到而非聚集索引能获取到,也会发生回表,Mysql会在非聚集索引获取后再通过聚集索引找到对应的行数据 Sqlserver的聚集索引就是数据本身,并不一样 所以Mysql中药避免回表还需要覆盖索引解决问题

sql
Select A,B,C from XXX where C = 'xx'

CREATE INDEX idx_coveer on XXX(C,A,B)

主键有序

根据有序特性,应该使用自增、雪花ID、以及一直以来被谣传的UUID来作为主键,只是需要使用的是采用Unix时间戳以及时间有序性的V7版本

注意点

主键索引

主键索引是在对应主键为有序数据时才能起到最大作用,如自增ID,但即使如此也应该使用主键索引来达到优化性能的目的

联合索引、最左原则

想要命中联合索引的前提就是要遵循命中最左原则,即A、B、C三个字段的联合索引,不管如何查询,至少包含有A的条件时索引才生效 Mysql、Mssql都有优化机制处理最左原则,只有当索引无法匹配时才需要手动调整,所以尽可能使用联合索引覆盖匹配条件即可

全文索引

全文索引对于中文的支持并不友好,且对于硬盘使用过高,更推荐通过应用层如:MeiliSearch、ElasticSearch来解决

唯一索引快慢

唯一索引之所以快就是因为一旦查到结果就不会走完整索引了,而之所以慢是因为要保证唯一性在插入数据时要完整检查表是否有重复数据

原则

sql
select * from xx where name = xx

表中建立了主键索引和name的辅助索引, 但实际执行过程会先找到name的辅助索引找到对应的聚簇索引,再聚簇索引找到对应行数据 此时就产生了两次查询,这就是回表问题

原因是在数据库中的存储结果中,聚簇索引和行数据存储在一起, 而辅助索引通常单独存储并指向聚簇索引

要避免回表就要使用主键作为查询条件,否则要明确结果字段,避免使用*.

实际情况也要根据业务建立联合索引覆盖非主键字段的查询,避免辅助索引

  1. 经常被作为查询条件的字段根据业务增加索引
  2. 表的主外键和连表字段一定要建立索引,增加连表性能
  3. 建立索引的查询条件字段,其区分性要足够高,否则没有意义,比如这个索引是一个分类字段,仅区分A和B,那么建立索引就没有意义
  4. 联合索引应遵守最左原则
  5. 通常将根据范围取值、排序、分组的字段建立索引,因为这样索引是有序的
  6. 尽量使用联合索引,其效率比单索引高
  7. 值经常会写操作、重复值、带函数查询都不适合索引
  8. 索引数量不是越多越好,会增加写操作开销,控制在五个以下

回避点

  1. 避免OR查询,尽量使用子查询或者联表或者IN来代替
  2. 模糊匹配不要用%开头,如果必须要则走全文索引
  3. 避免索引字段的函数计算,一定要涉及函数放在 = 后面
  4. 对比多个字段之间的查询业务,可以拆分为联表或者临时表代替
  5. 尽量避免反范围查询条件,如IS NULL/IS NOT NULL/<> 之类

优化判断

通过Navicat的解释语句可以看语句是否走索引和查询结果时间,判断索引:

  1. Clustered Index Seek即为 索引扫描,性能最好
  2. Clustered Index Scan 全表扫描(整个聚簇索引扫描)
  3. Table Scan 缺失主键、堆表扫描
  4. Index Scan 非全表扫描,但扫了整个非聚集索引,仅次于Index Seek

Index Scan的情况大多是因为存在大量重复值、其次是联合索引没有命中

sql
-- 判断是否要走索引扫描 要低于百分之5 排单表示例
SELECT COUNT(DISTINCT machineId) * 1.0 / COUNT(*) FROM HRS_SchedulingMachineTime;
-- 0.02

SELECT COUNT(DISTINCT rowId) * 1.0 / COUNT(*) FROM HRS_SchedulingMachineTime;
-- 0.09

当这个结果低于百分之5时,优化器认为扫索引页比Seek划算,而不是索引的问题

建立原则

联合索引不是万能的,根据频次进行建立,比如此时有A/B/C三个查询条件 联合索引优化固定组合,单索引弥补零散条件,避免一刀切 核心就是根据高频出现的条件建立查询条件,并且杜绝过多的索引影响性能

  1. A、B、C 组合索引
  2. 高频的A+B 或者B+C索引
  3. 单独的高频A索引

事务篇

事务是基于数据库连接而非表, 连接本身会被工作线程维护,所以事务本质就是一个线程在执行,而并发事务指的就是多个线程并发执行

本质就是对于的数据的读写操作的隔离级别,保证数据的一致性,越高的隔离级别对于数据越安全,但性能也随之下降

术语解释

脏读

读到别人还没提交的数据,对方随时可能回滚。

不可重复读

在同一个事务里,两次读同一行得到不同结果(因为别人已提交修改)

幻读

在同一个事务里,两次范围查询得到不同行数/行集(别人已提交插入或删除)

脏写

把别人还没提交的修改再次修改,一旦对方回滚,你的修改也白搭

不可重复写

两个事务先后读同一行,各自基于读到的值做更新,后提交的把先提交的覆盖掉

幻写

两个事务基于同一个范围条件做写操作,后提交的写操作影响了先提交的范围结果,造成业务逻辑错误

而脏读、幻读、不可重复读本质就是线程安全问题,所以需要通过锁机制来解决,同时产生了不同的事务隔离级别

事务隔离级别

越往后的隔离级别性能越差,Mysql默认可重复读,Mssql默认读已提交

隔离级别解决读异常解决写异常
Read Uncommitted脏写 √
Read Committed脏读 √脏写 √
Repeatable Read脏读 √ 不可重复读 √脏写 √ 不可重复写 √(MySQL 靠锁)
Serializable全部 √全部 √

读未提交 Read uncommitted(RU)

脏读、不可重复读、幻读都会发生

基于写互斥锁,当一个事务开始写时,另外一个事务也来写同一个数据,为了防止意外则先需要获取写互斥锁,只有先拿到锁的事务才能写数据并排斥其他来获取锁的事务

写虽然互斥了,但读不是,也就是当事务没提交前,读这个数据都是未提交的数据,因此就会导致脏读、不可重复读、幻读问题

这个级别由于写互斥锁解决了脏写的问题

读已提交 Read committed(RC)

可能不可重复读、幻读, 解决了脏读 仍然存在写互斥锁,并引入MVCC版本并发控制阻止被事务操作的数据, A没提交前,B读到的仍然是提交前的数据,A提交后B再读则是提交后的数据

可重复读 Repeatable read(PR)

可能幻读, 解决了不可重复读、脏读

仍然基于MVCC版本并发控制(Mssql中是快照隔离)机制,但在一次事务中每次查询都只会拿到第一次的结果,保证多次读取数据一致,也就是读的一致性 而幻读则是,AB两个事务中,A事务修改了数据后,B事务可能是新增数据,那么A事务中再去读数据则可能读到B事务提交的新数据

序列化、串行 Searializeable

所有问题都不存在

本质就是表级别的锁,所有的操作都变为单线程了,当然也变卡了

锁篇

表锁与行锁

Mysql

如果一条Sql能命中索引执行,那么就有可能加的是行锁,否则是表锁

场景实际加的锁原因
索引过滤后仍需要回表访问其它列行锁 + 间隙锁(Next-Key)满足 RR 幻读保护,锁住索引区间。
使用了索引前模糊匹配 LIKE '%abc'全表扫描 → 表锁索引无法用于过滤,退化为全表。
隐式类型转换 WHERE varchar_col = 123全表扫描 → 表锁列被隐式转换,索引失效。
OR 条件跨列 WHERE a=1 OR b=2可能全表扫描 → 表锁优化器放弃索引合并。
索引列上使用函数/计算 WHERE DATE(create_time)=...全表扫描 → 表锁索引列被包裹函数,无法走索引。

Sql server

场景实际加的锁原因
查询条件不可 SARGable(函数、隐式转换、LIKE '%xx')表/页锁执行计划走 Scan,触发锁升级阈值。
统计信息过时表/页锁优化器误判选择 Scan。
显式提示 WITH (TABLOCK)强制表锁人为指定。
锁升级阈值触发(>5000 行)行锁 → 页锁或表锁即使走了索引,也可能升级。

死锁

sql
-- A事务:竹子向熊猫转账
UPDATE `zz_account` SET balance = balance - 888 WHERE user_name = "竹子";
UPDATE `zz_account` SET balance = balance + 888 WHERE user_name = "熊猫";

-- B事务:熊猫向竹子转账
UPDATE `zz_account` SET balance = balance - 666 WHERE user_name = "熊猫";
UPDATE `zz_account` SET balance = balance + 666 WHERE user_name = "竹子";

本质就是不同线程(事务)之间的竞态问题,A事务中第一条语句锁了 竹子的数据,B事务锁了熊猫的数据, 此时不管是A还是B执行第二条语句时,都由于对方的锁未释放导致死锁

死锁处理

SELECT * FROM sys.dm_tran_locks;

机制MySQL (InnoDB)SQL Server
检测方式wait-for graph + 可选锁超时仅 wait-for graph
默认动作死锁检测优先;若关闭检测,则等 innodb_lock_wait_timeout 超时立即检测并回滚
回滚粒度默认回滚整条语句(可配置为整事务)总是回滚整个事务
锁超时参数innodb_lock_wait_timeout(默认 50 s)LOCK_TIMEOUT 仅影响普通阻塞,不用于死锁
错误码1213(deadlock)1205(deadlock victim)

技巧总结篇

  1. 杜绝使用*的查询语句
  2. 联表控制在5张表内,表关联越多,索引不可控风险越大
  3. 多表查询时使用小表驱动大表
  4. 分组前有明确条件时一定使用Where过滤提升分组性能
  5. 避免like左模糊和全模糊,有关索引失效
  6. 避免null、is not、 !<>之类的反范围的判断,但在Mssql中null关键字不影响,仍然可以命中索引

还有很多可以参考的技巧,待实际运用记录...