数据库内功“心法”，你到了第几层？

作者/糖小幽

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

when grade > 4 and grade

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

when grade > 4 and grade

else '高年级'

end) as 年级

from students

group by 年级

having num > 50000

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

select avg(score)

from scores

group by student_id

having

sum(

case

when score

else 0

end

) > 0

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

select avg(score) as avg,

sum(

case

when score

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)

adddate

索引支持,>=,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 -

♫