运维开发网

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

运维开发网 https://www.qedev.com 2020-11-29 08:32 出处:51CTO 作者:久曲健
数据库内功“心法”,你到了第几层?小鱼引导关注图文艺美女朋克服装街头拍摄作者/糖小幽1基础知识和关系模型发展人工管理(50年代,数据程序不独立,冗余大)文件系统(60年代,数据和程序分离,冗余,不易共享和修改)数据库系统分布式数据库系统(节点共享)面向对象数据库(数据库和面向对象设计结合,数据更复杂)2数据库定义相互关联的数据集合+管理和访问这些数据数据结构定义、操作机制,存储方式,数据冗余,一致

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

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

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

小鱼引导关注图

文艺美女朋克服装街头拍摄

作者/糖小幽

1

基础知识和关系模型

发展

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

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

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

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

  3. 数据库系统
  4. 分布式数据库系统(节点共享)
  5. 面向对象数据库(数据库和面向对象设计结合,数据更复杂)

    2

    数据库定义

  6. 相互关联的数据集合+管理和访问这些数据
  7. 数据结构定义、操作机制,存储方式,数据冗余, 一致性,访问方式,完整性,原子

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

    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 'innodbversion'

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>

    · 猜你喜欢的文章 ·

扫码领视频副本.gif

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号