数据库架构

如何设计一个关系型数据库

首先将其划分为存储部分和对存储进行逻辑管理的程序实例，存储部分类似一个文件系统，程序实例将包含数据逻辑关系转为物理存储关系的存储管理模块，优化存储效率的缓存模块，将sql语句进行解析的sql解析模块，记录操作的日志管理模块，进行多用户管理的权限划分模块，容灾模块，优化数据查询的索引模块，使数据库支持并发的锁模块。

索引

为什么使用索引

当查询大量数据，如果进行全表扫描，查询非常慢，所以需要更高效的机制来避免全表扫描。

什么样的信息能成为索引

主键，唯一键，普通键等能让数据具备区分性

索引的数据结构

主流是B+树，还有hash结构和BitMap，其中Mysql不支持BitMap，基于innodb和mysm引擎的mysql不显示支持hash

• 二叉查找树O(logn)

缺点：插入数据有可能变为线性二叉树-》树旋转 检索深度每增加1，就会发生一次io，检索性能降低。-》要使树变得矮一些，每个节点数据存储多一些，

• B树O(logn)

说明：前四条规则用来限制孩子数，最后一条规则限制节点关键字数量及大小。通过合并，分裂，上移下移来使树不变成线性

• B+树O(logn)-主流

B+树只存索引信息，内部节点比B树更小，储存时，盘块所能容纳的关键字数量更多，一次性读入内存的关键字更多，减少io次数

• Hash

• BitMap（位图索引）

密集索引和稀疏索引

innodb有且只有一个聚集索引，数据文件和索引绑在一起，必须要有主键，通过主键索引效率很高，但是辅助索引需要查两次。

myisam是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的，因此myisam适合增删改。

如何定位并优化慢查询sql

• 根据慢日志

• 根据explain

  

  

1
2
3

explain select count(id) from person_info ;

key-account 不是主键id，原因是密集索引的叶子索引把其他列的数据也存放在叶子节点中，查询效率比稀疏索引低

• 修改sql或者尽量让sql走索引

联合索引最左原则的成因

最左匹配原则：

1. mysql会一直向右匹配直到遇到范围查询就停止匹配，比如a=3 and b=2 and c>3 and d=5，如果建立(a,b,c,d)索引，d是用不到索引的，如果建立的是(a,b,d,c)索引则都可以用到，且a，b，d的顺序是可以任意调整的

2. =和in是可以乱序的

成因：

mysql创建复合索引时，首先对第一个字段进行排序，在第一个字段的基础上，再对第二个字段进行排序，依次类推，所以自能最左匹配

索引建立的越多越好吗

• 数据量小的表不需要索引，建立增加额外开销
• 数据变更需要维护索引，更多的索引意味着更多的维护成本
• 更多的索引意味着需要更多的空间

锁

MyISAM和InnoDB的锁的区别是什么

myisam：

读写、写读-myisam在进行查询的时候，会加一个表级别的读锁；在进行增删改的时候会加一个表级别的写锁。在读锁未被释放，加写锁时会被阻塞，直到读锁被释放为止。

读读-可以同时进行，读锁又叫共享锁。（上排他锁，for update）

写写-不能同时写，写锁又叫排他锁

innodb：

二段锁：加锁解锁两个步骤，先对同一个事务里的一批操作做加锁，commit的时候再进行解锁

1
2
3
4
5

set autocommit=0；// 支持事务，需要关闭事务自动提交
先读后写
session1：select * from person_info where id=1;
session12: update  person_info set title ="test" where id=1;//本来应该阻塞，竟然成功
//实际innodb对select做了改进，并未上读锁

表级锁和索引无关，行级锁和索引有关，当不走索引，表会被锁住

锁的力度越细，代价越高。表级锁只要在表的头部上锁，行级锁需要扫描到某行后对其加锁，开销更大

• MyISAM默认用的是表级锁，不支持行级锁

• InnoDB默认用的是行级锁，也支持表级锁

MyISAM适合的场景

• 频繁执行全表count语句
• 对数据的增删改的频率不高，查询频繁
• 没有事务

InnoDB适合的场景

• 增删改查频繁
• 需要事务

数据库锁的分类

自动锁包括myasm表锁，select for update，lock in share mode;//显示锁

dml锁增删改，ddl锁alter

• 悲观锁：操作数据会发生并发冲突，在数据处理过程将数据锁定，依靠数据库提供的锁机制实现。缺点：增加开销，降低并行性

• 乐观锁：认为不会发生并发冲突，到数据提交更新时才检查是否冲突，通过版本号和时间戳实现

有一条比较好的建议，可以减小乐观锁力度，最大程度的提升吞吐率，提高并发能力！如下：

1
2

//修改商品库存
update item set quantity=quantity - 1 where id = 1 and quantity - 1 > 0

事务的四大特性

原子性:事务包含的全部操作要么全部执行要么全部不执行 一致性:事务要确保数据库的状态从一个一致的状态转为另一个一致的状态(转帐) 隔离性:多个事务并发执行时,一个事务的执行不应该影响其他事务 持久性:一个事务的提交,他对数据库的修改 应该永久地保存在数据库中

事务的隔离级别以及各级别下的并发访问问题

1

select @@tx_isolation

事务并发访问引起的问题以及如何避免

• 更新丢失-mysql所有事务隔离级别在数据库层面上均可避免

• 脏读（读未提交）-解决：将隔离级别设置为读已提交read-committed就可避免

1

set session transaction isolation level read uncommitted;//读未提交

• 不可重复读-解决：将隔离级别设置为可重复读repeatable-read

1
2
3
4
5

session1 一直查询，余额为1300
session2 update balance=300，未提交
session1 查询，余额为1300
session2提交
session1查询变为1600

-幻读（事务a查询若干条数据，事务b插入或修改了事务a的结果集，导致事务a像出现幻觉一样）-解决：将事务隔离级别设置为serializable

1
2
3
4
5
6
7
8
9

session a：  select * from account lock in share mode;//查出3条数据

(rr级别下:无法插入,等待session a 提交,mysql的innodb在rr级别下避免了幻读)
sessionb: insert into account;


(rc级别下：竟然更新了4条数据)
sesion a: update account set balance =100;

出于性能考虑，事务级别越高，降低数据库的并发度越低。 oracle默认读已提交read-committed；mysql的默认为repeatable-read；

innodb可重复读rr级别如何避免幻读

表象:快照读(非阻塞读)–伪mvcc 内在:行锁+gap锁

当前读和快照读

当前读,加了锁的增删改查,也就是读取最新数据

快照读用于提升并发性能,基于多版本并发控制及mvcc,mvcc是行级锁的变种,在很多情况下避免了加锁操作,开销更低. 读取的有可能是历史版本(rr级别下,快照读取决快照创建的时机),在serializable下无效

1
2
3
4
5
6
7
8
9
10
11
12
13

s1:select * from A where id =2;// balance=1000
s2:update A set balance=600 where id=2;

rc级别下:
s1:select * from A where id=2;//balance=600;
s1:select * from A where id=2 lock in share mode;//balance=600
rc级别下当前读和快照读读到的数据版本是一样的

rr级别下:
s1:select * from A where id=2;//balance=1000;
s1:select * from A where id=2 lock in share mode;//balance=600
rr级别下快照读有可能读到数据的历史版本

rc,rr级别下的InnoDB的非阻塞读如何实现

修改过程:先加上排他锁,然后拷贝一份到undo log中

readview决定可见性

取出的trx_id小于当前活跃事务Id

rc级别下,每次快照读都会创建一个新的快照 rr级别下,快照读读的是第一次创建的快照

next-key锁(行锁+gap锁)

gap锁,防止同一事务的两次当前读出现幻读,在rr级别以上才有

Gap锁用在非唯一索引或者不走索引的当前读中

关键语法

group by

• 满足:select的列要么是group by的条件要么是统计相关的函数

• 列函数对于group by字句定义的每个组各返回一个结果

  

1
2
3
4
5
6
7
8
9
10

#查询所有同学的学号,选课数,总成绩
select student_id,count(course_id),sum(score)
from score
group by student_id

#查询所有同学的学号,姓名,选课数,总成绩
select s.student_id,t.name,count(course_id),sum(score)
from score s,student t
where s.student_id = t.student_id
group by student_id

having

1
2
3
4
5
6
7
8
9
10
11
12

#查询平均成绩大于60分的同学的学号和平均成绩
select student_id,avg(score)
from score
group by student_id
having avg(score)>60

#查询没有学全所有课的同学的学号,姓名
select s.student_id,t.name
from score s,student t
where s.student_id=t.student_id
group by student_id
having count(s.course_id)<(select count(*) from course group by course_id)

统计相关:count,sum,max,min,avg

#