数据库内功“心法”,你到了第几层?

软件测试君2021-01-09 13:19:08

作者/糖小幽

1


基础知识和关系模型


发展

1. 人工管理(50 年代,数据程序不独立,冗余大)

2. 文件系统(60 年代,数据和程序分离,冗余,不易共享和修改)

3. 数据库系统

4. 分布式数据库系统(节点共享)

5. 面向对象数据库(数据库和面向对象设计结合,数据更复杂)

2

数据库定义


1. 相互关联的数据集合+管理和访问这些数据

2. 数据结构定义、操作机制,存储方式,数据冗余, 一致性,访问方式,完整性,原子

性,并发访问异常,安全性等等

3

系统层次


硬件?操作系统?数据库管理系统?数据库应用系统

物理层(系统)?逻辑层(dba)?视图层(数据使用者)

4

数据库通用结构


5

三个世界


示例:

图书借阅: 学生 + 图书 + 借阅

学生成绩: 学生 + 成绩 + 课程

数据模型: 以什么样的数据结构存储什么样的数据

概念&实现: 层次型, 网络型, 关系型

实体映射关系:

6

数据库设计


范式是设计的一种理念,不是设计完成后检验是否满足的标准!

常用 概念

侯选关键字:又叫侯选码,惟一标识一行数据,其真子集不能是侯选关键字,一个表可以存

在多个侯选关键字,如用户表的 username,userid

主关键字:又叫主键,主码,被选中的用来区分其它行的侯选关键字,一个表只有一个主关

键字

部分依赖:(A,B)->C,D,如 A->C,则 C 部分依赖 A

传递依赖:A->B->C,则 C 传递依赖 A

7

常用范式


第一范式: 数据库的字段是单一属性,不可再分

第二范式: 任何非关键字段不能部分依赖任一侯选关键字(即必须完全依赖)

第三范式: 任何非关键字段不能传递依赖任一侯选关键字

BC 范式: 任何字段都不能传递依赖任一侯选关键字

8

数据库查询


3 个学习重点:

a. 所有 sql 的关键字,哪个先执行,哪个后执行?

b. 禁用 select *

c. 尽可能不用嵌套查询

d. 谓词下推

9


基本操作


Select 选择操作(字段, 内置函数(count ,distinct, string 函数,时间函数,ifnull),条件语句,

判断等等)

From 投影操作

Where 条件过滤( 一条一条过滤)

Order by 排序

Limit top ….等等

窍门:先组表(投影),再过滤,再排序,最后选择

查询学号大于 5000 的学生信息,年级数显示为高中低三个年级,返回学号最大的三个学生

select id, name,
(case
when grade <= 4 then '低年级'
when grade > 4 and grade <= 6 then '中年级'
else '高年级'
end) as 年级
from students
where id > 5000
order by id desc
limit 3


关联


Left  (outer)  join

没有年级归属的学生:

select *
from students
left join grades
on students.grade = grades.id
where grades.id is NULL

Right  (outer)  join


Inner join

有年级归属的学生

select *
from students
inner join grades
on students.grade = grades.id


求差集

select *
from students
left join grades
on students.grade = grades.id
where grades.id is NULL
union
select *
from students
right join grades
on students.grade = grades.id
where students.id is NULL


Full  joinl (mysql  不支持) )

select *
from students
left join grades
on students.grade = grades.id
union
select *
from students
right join grades
on students.grade = grades.id

Cross n join  笛卡尔积


查询缺考的学生和课程:

select students.name as student, courses.name as teacher
from students cross join courses
left join scores on students.id=scores.student_id and courses.id=scores.course_id
where student_id is NULl and course_id is NULL

自连接


涨工资的人和月:

select a.name, CONCAT(b.`month`, "月(", b.salary, ")", '-->',a.`month`, "月(", a.salary, ")")
from salary as a
left join salary as b
on a.`name` = b.`name`
where a.`month` = b.`month` +1 and a.salary > b.salary


地图中:

http://sqlzoo.net/wiki/Self_join

有公交车直达的两个站

SELECT * FROM route R1, route R2
WHERE R1.num=R2.num AND R1.company=R2.company

功用公交站的公交车

SELECT * FROM route R1, route R2
WHERE R1.stop=R2.stop and R1.pos!=R2.pos

直达车:

SELECT *
FROM route R1, route R2, stops S1, stops S2
WHERE R1.num=R2.num AND R1.company=R2.company
AND R1.stop=S1.id AND R2.stop=S2.id
AND S1.name='玉桃园'
AND S2.name='新街口豁口'

聚合( ( groupby &having) )


本质:构建表中表(组),并对组做统计,组的过滤用 having

Where 和 having 区别:where 作用于表和视图,having 作用于组

查询每个年级各自的学生个数,按总数大小排序:

select count(*) as num, grade
from students
group by grade
order by num desc


查询高中低三个年级中总人数超过 50000 的:

select count(*) as num,
(case
when grade <= 4 then '低年级'
when grade > 4 and grade <= 6 then '中年级'
else '高年级'
end) as 年级
from students
group by 年级
having num > 50000

查询任意一门成绩不及格的学生的所有课程的平均分

select avg(score)
from scores
group by student_id
having
sum(
case
when score < 60 then 1
else 0
end
) > 0

查询每个课程的平均分和不及格人数和(课程难易和挂科难易):

select avg(score) as avg,
sum(
case
when score < 60 then 1
else 0
end) as fails
from scores
group by course_id

10

索引


人与人之差,有时比类人猿和原人之差还远——鲁迅

用好索引就是蓝魔基尼,不加索引就是三轮车


示例表:articles


索引就像字典的目录,书的页数是主键(物理索引),目录是逻辑索引,可以建立多个索引(按

笔画查询,按拼音查询,按。。。查询);


注意:

聚集索引(物理索引排序一致)可以提高多列检索速度

非聚集索引(逻辑索引)对单列检索很快


例子:

覆盖索引(使用关键字 explain):

16.888s

使用索引:

24s

不使用索引:

28s


索引类型


普通索引

Mysql中普通索引即 B-tree 索引,默认索引


唯一索引

索引列必须唯一,可以为空,算法 hash


全文索引

Mysql引擎必须为 MyISAM,InnoDB5.6 以上


单列和多列

多个单列索引与单个多列索引的查询效果不同,mysql 选择一个限制最为严格的索引执行


组合索引

最左原则:由于 mysql 索引文件在 b-tree 中,(title, content, time)相当于建立了三个索引:

(title)

(title, content)

(title, content, time)

窍门,where 语句中使用最频繁的字段在左边(按照业务需要)


聚集&非聚集


优化索引


a. NULL 值不会包含在索引中,多列索引中任何一列包含 NULL,索引对于这列无效——设

置默认值

b. 索引值尽可能短:varchar(255)这样的字段如果长短可控,索引应该设置尽可能短;

c. 索引列排序:where 过滤如果使用过索引,order by 则不再使用这个索引

下面两个 sql 查到数据是不同的:

select title
from articles
where id>-1
limit 5
select title
from articles
limit 5

d. Like: 不推荐 like 操作,如果用的话,like “%XXX%”不用索引,like “XXX%”使用个索引

e. 函 数 : 不 要 在 索 引 列 使 用 函 数 !!! where YEAR(adddate)<2016 ? 改 为

adddate<’2016-01-01′

索引支持<,<=,=,>,>=,between,in,以及某些时候的 like(不以通配符%或_开头的情

形)

11


事务和ACID


支持事务的数据库,必须保证 ACID:

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位,事务中的操作 要么都发生,要么都不发生。

一致性(Consistency)

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。(共享数据和临近数据)

隔离性(Isolation)

事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数

据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。

持久性(Durability)

持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操

作和数据库故障不应该对其有任何影响。


示例:

transaction.py


死锁


Mysql的锁


表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最

低。

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也

最高。

页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,

并发度一般。

MyAsim 只支持表级锁,InnoDB 支持行级锁,默认 raw-level lock,明确指定主键后行锁,否

则表锁


Inno DB 包含:buffer pool 缓冲池 redo log buffer 重做日志缓冲池 additional memory pool

额外内存池

缓冲池是用来存放各种数据的缓存,InnoDB 存储引擎的工作方式是将数据库文件按页(每

页 16K)读取到缓冲池,按照(LRU)的算法保留在缓冲池中的缓存数据


判断是否 innodb ( 默认的 InnoDB 存储引擎的后台线程有 7 个,4 个 IO thread ,1 个 master thread 1 个锁监控 thread

1 个错误监控 thread,IO thread 的数量由配置文件的 innodb_file_io_threads 参数控制,默认是 4

):

show engine innodb status
show variables like 'innodb_version'
show variables like 'innodb_%io_threads'

示例


锁的示例需要在命令行中执行:

(明确指定主键,并且有此笔资料,row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and name=”粉笔” FOR UPDATE;

(明确指定主键,若查无此笔资料,无 lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

(无主键,table lock)

SELECT * FROM products WHERE name='粉笔' FOR UPDATE;

(主键不明确,table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

(主键不明确,table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

死锁


广义:任何资源竞争问题

a. 任务分配:老板分配一个工作,员工 A 刚完成,老板又分配另一个工作,员工完成后去

办公室等老板,老板却在工地等员工;

b. A 打开别墅的大门,进入卧室,然后从卧室出来后锁上卧室的门,准备出去;B 进入别

墅后,锁上别墅大门,想要走进卧室;这时 A 出不去别墅,B 进不去卧室;

c. 生产消费者模型


Mysql 中死锁原因:同时为共享资源加锁


示例:

set autocommit=0;
update products set name='aaa' where id=2;

解决死锁


Mysql应急操作

检查是否死锁:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
show PROCESSLIST

存储过程


数据库端的,编译后的 sql 语句块

call p_score_of_student()
call p_score_of_course(2)

视图


只读的,权限控制的,虚拟表

用处:用户级别的,只提供业务层面数据,只读,最小选择权限

v_score_of_student
select *
from v_score_of_student

触发器


由 Insert, update, delete 触发,分为 before 和 after,可以自定义触发内容

不建议使用触发器,由事务和存储过程代替


方法

SELECT sp_getdate('2009-06-23 00:00:00')


小编也不易,希望大家多多支持,随意打赏


 -  End  -

<Guess u will love>

· 猜你喜欢的文章 ·

?


【特大福利-开源测试框架】AutoMagic自动化测试平台

性能测试指标评估必读

没那么多测试机,手机适配性测试该如何做?

我不是计算机专业的,软件测试工程师的简历要怎么写?

Postman实战


作者@糖小幽

图片@来源于网络

商务合作请联系微信:sofeicoffee


· 看完要点赞并转发到朋友圈哦 ·